mysql常用命令

mysql命令

1
2
3
4
5
show tables from databasename;#在当前数据库中查看其他数据库中内容
select database();#显示当前操作数据库
select version();-- 查看当前数据库版本
show databases;#查看所有已建数据库
show tables;#查看当前数据库中所有表

cmd命令

1
2
mysql --version//查看当前数据库版本
mysql -V//同上

mysql的语法规范

  1. 不区分大小写,但建议关键字大写,表名、列名小写
  2. 每条命令用分号结尾
  3. 注释

    1. 单行注释:#注释内容
    2. 单行注释:– 注释内容
    3. 多行注释:/ 注释内容 /

查看mysql字符集

1
show VARIABLES like 'character%';

mysql 排序后加序号

1
2
3
4
5
6
7
SET @i := 0;
SELECT @i := @i + 1 AS `order`,e.* FROM employees e WHERE hiredate=
(
SELECT MAX(hiredate) FROM employees
);
或者照下面做
select ROW_NUMBER() OVER (ORDER BY a.hiredate ASC) AS XUHAO,a.* from employees a;

看个栗子

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT * FROM employees WHERE employees.hire_date=
(
SELECT hh.hire_date FROM (
SELECT ROW_NUMBER() OVER (ORDER BY e.hire_date ASC) AS orde,e.hire_date FROM
employees e GROUP BY e.`hire_date`
)
hh WHERE hh.orde+2=(
SELECT COUNT(hh.orde) FROM (
SELECT ROW_NUMBER() OVER (ORDER BY e.hire_date ASC) AS `orde`,e.hire_date,COUNT(*) FROM
employees e GROUP BY e.`hire_date`
) hh
)
);

查看及设置当前的隔离级别

1
2
3
4
5
SELECT @@transaction_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
#设置当前会话的隔离级别
SET GLOBAL SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
#设置全局的隔离级别

查看所有表中加的锁

1
show open tables;#加了锁的表只能处理当前表,直到释放锁
Donate comment here