mysql

Mysql

表 :

​ 行：数据记录

​ 列：字段名，数据类型，约束条件

DQL	select	查询语句
DML	insert update delete	表中增删改语句
DDL	create drop alter	表结构增删改
TCL	commit rollback	事务控制语句
DCL	grant revoke	数据库权限控制

导入数据：source xxx.sql

查看表结构： desc 表名

查看当前使用的数据库名： select database();

查看Mysql版本号： select version();

查看建表语句：show create table 表名;

select 语句执行顺序： from–>where–>group by --> having -->select–>order by–>limit

between…… and…… 数字：闭区间，字符：左闭右开

多个order by 条件时，使用逗号分割，当前边条件一致时按照后边条件排序

mysql> select * from EMP order by SAL desc,ENAME asc;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|    99 | nu_nij | NULL      | NULL | NULL       |    NULL |    NULL |   NULL |
+-------+--------+-----------+------+------------+---------+---------+--------+

分组函数，自动忽略 null

mysql> select count(COMM) from EMP;
+-------------+
| count(COMM) |
+-------------+
|           4 |
+-------------+

# 不需要加条件
select count(COMM) from EMP where COMM is not null;

mysql> select count(COMM) from EMP where COMM is not null;
+-------------+
| count(COMM) |
+-------------+
|           4 |
+-------------+
1 row in set (0.04 sec)

count	计数
sum	求和
avg	平均值
max	最大值
min	最小值

mysql> select count(COMM) from EMP;
+-------------+
| count(COMM) |
+-------------+
|           4 |
+-------------+
1 row in set (0.10 sec)

有null 参与运算 ，结果一定是null

mysql> select * ,(SAL+COMM)*12 as total from EMP;
+-------+--------+-----------+------+------------+---------+---------+--------+----------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO | total    |
+-------+--------+-----------+------+------------+---------+---------+--------+----------+
|    99 | nu_nij | NULL      | NULL | NULL       |    NULL |    NULL |   NULL |     NULL |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |     NULL |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 | 22800.00 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 | 21000.00 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |     NULL |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 | 31800.00 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |     NULL |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |     NULL |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |     NULL |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |     NULL |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 | 18000.00 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |     NULL |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |     NULL |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |     NULL |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |     NULL |
+-------+--------+-----------+------+------------+---------+---------+--------+----------+
15 rows in set (0.00 sec)

空处理函数：ifnull(COMM,0): ifnull(可能为空的字段,当作什么处理)

mysql> select * ,(SAL+ifnull(COMM,0))*12 as total from EMP;
+-------+--------+-----------+------+------------+---------+---------+--------+----------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO | total    |
+-------+--------+-----------+------+------------+---------+---------+--------+----------+
|    99 | nu_nij | NULL      | NULL | NULL       |    NULL |    NULL |   NULL |     NULL |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |  9600.00 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 | 22800.00 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 | 21000.00 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 | 35700.00 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 | 31800.00 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 | 34200.00 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 | 29400.00 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 | 36000.00 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 | 60000.00 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 | 18000.00 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 | 13200.00 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 | 11400.00 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 | 36000.00 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 | 15600.00 |
+-------+--------+-----------+------+------------+---------+---------+--------+----------+
15 rows in set (0.01 sec)

分组函数不能直接出现在where后(group by 在where后执行)

mysql> select SAL,ENAME from EMP where SAL>avg(SAL);
ERROR 1111 (HY000): Invalid use of group function

mysql> select SAL,ENAME from EMP where SAL>(select avg(SAL) from EMP);
+---------+-------+
| SAL     | ENAME |
+---------+-------+
| 2975.00 | JONES |
| 2850.00 | BLAKE |
| 2450.00 | CLARK |
| 3000.00 | SCOTT |
| 5000.00 | KING  |
| 3000.00 | FORD  |
+---------+-------+
6 rows in set (0.07 sec)

count(*) 和count(comm) 区别：

count(*) ：统计总记录条数

count(comm)：统计comm字段中不为null的记录条数

mysql> select count(*) from EMP;
+----------+
| count(*) |
+----------+
|       15 |
+----------+
1 row in set (0.05 sec)

mysql> select count(COMM) from EMP;
+-------------+
| count(COMM) |
+-------------+
|           4 |
+-------------+
1 row in set (0.00 sec)

group by:按照某个字段或者某些字段进行分组

having:对分组后的数据进行过滤

# 找出每个岗位的最高薪资
mysql> select JOB,max(SAL) from EMP  group by JOB;
+-----------+----------+
| JOB       | max(SAL) |
+-----------+----------+
| NULL      |     NULL |
| CLERK     |  1300.00 |
| SALESMAN  |  1600.00 |
| MANAGER   |  2975.00 |
| ANALYST   |  3000.00 |
| PRESIDENT |  5000.00 |
+-----------+----------+
6 rows in set (0.00 sec)

找出工资高于平均工资的用户

mysql> select ENAME,SAL from EMP where SAL > (select avg(SAL) from EMP);
+-------+---------+
| ENAME | SAL     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+
6 rows in set (0.06 sec)

group by 出现时 select后只允许出现参加分组的字段和分组函数

计算岗位平均薪资

mysql> select JOB,AVG(SAL) from EMP group by JOB;
+-----------+-------------+
| JOB       | AVG(SAL)    |
+-----------+-------------+
| NULL      |        NULL |
| CLERK     | 1037.500000 |
| SALESMAN  | 1400.000000 |
| MANAGER   | 2758.333333 |
| ANALYST   | 3000.000000 |
| PRESIDENT | 5000.000000 |
+-----------+-------------+
6 rows in set (0.01 sec)

找出每个部门不同岗位的最高薪资

mysql> select DEPTNO,JOB,MAX(SAL) from EMP group by DEPTNO,JOB order by DEPTNO;
+--------+-----------+----------+
| DEPTNO | JOB       | MAX(SAL) |
+--------+-----------+----------+
|   NULL | NULL      |     NULL |
|     10 | CLERK     |  1300.00 |
|     10 | MANAGER   |  2450.00 |
|     10 | PRESIDENT |  5000.00 |
|     20 | ANALYST   |  3000.00 |
|     20 | CLERK     |  1100.00 |
|     20 | MANAGER   |  2975.00 |
|     30 | CLERK     |   950.00 |
|     30 | MANAGER   |  2850.00 |
|     30 | SALESMAN  |  1600.00 |
+--------+-----------+----------+	
10 rows in set (0.01 sec)	

distinct ：去除重复字段，前边不能有任何字段，代表后边所有字段联合去重

查询岗位数量

select count(distinct JOB) from EMP;

# 错误
mysql> select distinct count(JOB) from EMP;
+------------+
| count(JOB) |
+------------+
|         14 |
+------------+
1 row in set (0.00 sec)
# 正确
mysql> select count(distinct JOB) from EMP;
+---------------------+
| count(distinct JOB) |
+---------------------+
|                   5 |
+---------------------+
1 row in set (0.00 sec)

连接查询

内连接：

​ 等值连接

​ 非等值连接

​ 自连接

外连接：

​ 左外连接

​ 右外连接

全连接：

内连接：两张表平等

外连接：一张主表，一张副表

查询每个员工的部门名

mysql> select e.ENAME,d.DNAME from EMP as e join DEPT as d on e.DEPTNO = d.DEPTNO;
+--------+------------+
| ENAME  | DNAME      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)

找出每个员工的员工名，薪资，薪资等级

mysql> select e.ENAME,e.SAL,s.GRADE from EMP as e join SALGRADE as s on e.sal between s.LOSAL and s.HISAL;
+--------+---------+-------+
| ENAME  | SAL     | GRADE |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+
14 rows in set (0.00 sec)

查询员工名及对应的上级领导名

# 内连接 没有上级领导的字段忽略
mysql> select e2.ENAME,e.ENAME,e2.MGR from EMP as e join EMP as e2 on e.MGR=e2.EMPNO;
+--------+-------+------+
| ENAME  | ENAME | MGR  |
+--------+-------+------+
| SMITH  | FORD  | 7902 |
| ALLEN  | BLAKE | 7698 |
| WARD   | BLAKE | 7698 |
| JONES  | KING  | 7839 |
| MARTIN | BLAKE | 7698 |
| BLAKE  | KING  | 7839 |
| CLARK  | KING  | 7839 |
| SCOTT  | JONES | 7566 |
| TURNER | BLAKE | 7698 |
| ADAMS  | SCOTT | 7788 |
| JAMES  | BLAKE | 7698 |
| FORD   | JONES | 7566 |
| MILLER | CLARK | 7782 |
+--------+-------+------+
13 rows in set (0.00 sec)
# 外连接 没有上级领导的字段自动补NULL
mysql> select e.ENAME,e2.ENAME,e2.MGR from EMP as e left join EMP as e2 on e.MGR=e2.EMPNO;
+--------+-------+------+
| ENAME  | ENAME | MGR  |
+--------+-------+------+
| nu_nij | NULL  | NULL |
| SMITH  | FORD  | 7566 |
| ALLEN  | BLAKE | 7839 |
| WARD   | BLAKE | 7839 |
| JONES  | KING  | NULL |
| MARTIN | BLAKE | 7839 |
| BLAKE  | KING  | NULL |
| CLARK  | KING  | NULL |
| SCOTT  | JONES | 7839 |
| KING   | NULL  | NULL |
| TURNER | BLAKE | 7839 |
| ADAMS  | SCOTT | 7566 |
| JAMES  | BLAKE | 7839 |
| FORD   | JONES | 7839 |
| MILLER | CLARK | 7839 |
+--------+-------+------+
15 rows in set (0.00 sec)

找出每个员工所属部门及对应工资等级

# 内连接：忽略NULL
mysql> select e.ENAME,d.DNAME,s.GRADE from EMP e join DEPT d on e.DEPTNO=d.DEPTNO join SALGRADE s on e.SAL between s.LOSAL and s.HISAL;
+--------+------------+-------+
| ENAME  | DNAME      | GRADE |
+--------+------------+-------+
| SMITH  | RESEARCH   |     1 |
| ALLEN  | SALES      |     3 |
| WARD   | SALES      |     2 |
| JONES  | RESEARCH   |     4 |
| MARTIN | SALES      |     2 |
| BLAKE  | SALES      |     4 |
| CLARK  | ACCOUNTING |     4 |
| SCOTT  | RESEARCH   |     4 |
| KING   | ACCOUNTING |     5 |
| TURNER | SALES      |     3 |
| ADAMS  | RESEARCH   |     1 |
| JAMES  | SALES      |     1 |
| FORD   | RESEARCH   |     4 |
| MILLER | ACCOUNTING |     2 |
+--------+------------+-------+
14 rows in set (0.00 sec)

# 左外连接 NULL数据补齐
mysql> select e.ENAME,d.DNAME,s.GRADE from EMP e left join DEPT d on e.DEPTNO=d.DEPTNO left join SALGRADE s on e.SAL between s.LOSAL and s.HISAL;
+--------+------------+-------+
| ENAME  | DNAME      | GRADE |
+--------+------------+-------+
| nu_nij | NULL       |  NULL |
| SMITH  | RESEARCH   |     1 |
| ALLEN  | SALES      |     3 |
| WARD   | SALES      |     2 |
| JONES  | RESEARCH   |     4 |
| MARTIN | SALES      |     2 |
| BLAKE  | SALES      |     4 |
| CLARK  | ACCOUNTING |     4 |
| SCOTT  | RESEARCH   |     4 |
| KING   | ACCOUNTING |     5 |
| TURNER | SALES      |     3 |
| ADAMS  | RESEARCH   |     1 |
| JAMES  | SALES      |     1 |
| FORD   | RESEARCH   |     4 |
| MILLER | ACCOUNTING |     2 |
+--------+------------+-------+
15 rows in set (0.00 sec)

查询所有员工的上级领导 ，部门名称，工资等级

mysql> select e.ENAME,e2.ENAME LEADER,e.SAL,d.DNAME,s.GRADE from EMP e left join DEPT d on e.DEPTNO=d.DEPTNO left join SALGRADE s on e.SAL between s.LOSAL an
d s.HISAL left join EMP e2 on e.MGR=e2.EMPNO;
+--------+--------+---------+------------+-------+
| ENAME  | LEADER | SAL     | DNAME      | GRADE |
+--------+--------+---------+------------+-------+
| nu_nij | NULL   |    NULL | NULL       |  NULL |
| SMITH  | FORD   |  800.00 | RESEARCH   |     1 |
| ALLEN  | BLAKE  | 1600.00 | SALES      |     3 |
| WARD   | BLAKE  | 1250.00 | SALES      |     2 |
| JONES  | KING   | 2975.00 | RESEARCH   |     4 |
| MARTIN | BLAKE  | 1250.00 | SALES      |     2 |
| BLAKE  | KING   | 2850.00 | SALES      |     4 |
| CLARK  | KING   | 2450.00 | ACCOUNTING |     4 |
| SCOTT  | JONES  | 3000.00 | RESEARCH   |     4 |
| KING   | NULL   | 5000.00 | ACCOUNTING |     5 |
| TURNER | BLAKE  | 1500.00 | SALES      |     3 |
| ADAMS  | SCOTT  | 1100.00 | RESEARCH   |     1 |
| JAMES  | BLAKE  |  950.00 | SALES      |     1 |
| FORD   | JONES  | 3000.00 | RESEARCH   |     4 |
| MILLER | CLARK  | 1300.00 | ACCOUNTING |     2 |
+--------+--------+---------+------------+-------+
15 rows in set (0.00 sec)

查出每个部门薪资的平均值的薪资等级

mysql> select t.*,s.GRADE from (select DEPTNO ,AVG(SAL) avg from EMP group by DEPTNO) t left join SALGRADE s on t.avg between s.LOSAL and s.HISAL;
+--------+-------------+-------+
| DEPTNO | avg         | GRADE |
+--------+-------------+-------+
|   NULL |        NULL |  NULL |
|     20 | 2175.000000 |     4 |
|     30 | 1566.666667 |     3 |
|     10 | 2916.666667 |     4 |
+--------+-------------+-------+
4 rows in set (0.00 sec)

计算每个部门的平均薪水等级

# 子查询 效率低
mysql> select avg(t.grade) from (select e.DEPTNO,e.SAL,s.GRADE grade from EMP e left join SALGRADE s on e.SAL between s.LOSAL and s.HISAL) t group by t.DEPTN
O;
+--------------+
| avg(t.grade) |
+--------------+
|         NULL |
|       2.8000 |
|       2.5000 |
|       3.6667 |
+--------------+
4 rows in set (0.00 sec)

# 直接查结果 效率高 
mysql> select e.DEPTNO,avg(s.GRADE) from EMP e left join SALGRADE s on e.SAL between s.LOSAL and s.HISAL group by e.DEPTNO;
+--------+--------------+
| DEPTNO | avg(s.GRADE) |
+--------+--------------+
|   NULL |         NULL |
|     20 |       2.8000 |
|     30 |       2.5000 |
|     10 |       3.6667 |
+--------+--------------+
4 rows in set (0.01 sec)

union 两张表结果连接

第一个结果查询列必须和第二个查询结果列数量保持一致

建表时复制其他表的数据

mysql> create table if not exists st2 as select * from student;
Query OK, 3 rows affected (0.13 sec)
Records: 3  Duplicates: 0  Warnings: 0


mysql> select * from st2 ;
+------+----------+------+---------+------------+
| no   | name     | sex  | classno | birth      |
+------+----------+------+---------+------------+
|    1 | zhangsan | 1    | 0021    | 2018-02-04 |
|    2 | zhangsan | 1    | 0021    | 2018-02-04 |
|    3 | lisi     | 0    | 0022    | 1900-08-12 |
+------+----------+------+---------+------------+
3 rows in set (0.00 sec)    

把查询结果插入到数据表中[insert into st2 select * from st2 where id>2;]

mysql> insert into st2 select * from st2 where id>2;
ERROR 1054 (42S22): Unknown column 'id' in 'where clause'
mysql> insert into st2 select * from st2 where no>2;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from st2;
+------+----------+------+---------+------------+
| no   | name     | sex  | classno | birth      |
+------+----------+------+---------+------------+
|    1 | zhangsan | 1    | 0021    | 2018-02-04 |
|    2 | zhangsan | 1    | 0021    | 2018-02-04 |
|    3 | lisi     | 0    | 0022    | 1900-08-12 |
|    3 | lisi     | 0    | 0022    | 1900-08-12 |
+------+----------+------+---------+------------+
4 rows in set (0.00 sec)

更新语句

mysql> update DEPT set LOC='SHANGHAI',DNAME='RENSHIBU' where DEPTNO=10;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from DEPT;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | RENSHIBU   | SHANGHAI |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

删除

mysql> select * from st2;
+------+----------+------+---------+------------+
| no   | name     | sex  | classno | birth      |
+------+----------+------+---------+------------+
|    1 | zhangsan | 1    | 0021    | 2018-02-04 |
|    2 | zhangsan | 1    | 0021    | 2018-02-04 |
+------+----------+------+---------+------------+
2 rows in set (0.00 sec)

mysql> delete from st2 where no =1;
Query OK, 1 row affected (0.04 sec)

truncate 表被截断，不可回滚，永久丢失

mysql> select * from st2;
+------+----------+------+---------+------------+
| no   | name     | sex  | classno | birth      |
+------+----------+------+---------+------------+
|    2 | zhangsan | 1    | 0021    | 2018-02-04 |
+------+----------+------+---------+------------+
1 row in set (0.00 sec)

mysql> truncate table st2 ;
Query OK, 0 rows affected (0.13 sec)

mysql> select * from st2;
Empty set (0.00 sec)

约束：

建表时候，保证表中数据 合法性，有效性，完整性

非空约束

唯一约束：可以为null 不能重复

主键约束：既不能为null 也不能重复

外键约束

检查约束（oracle 中的check，mysql暂不支持）

列级约束：每个字段单独添加

表级约束：多个字段联合添加

联合唯一约束（表级约束）

from sqlalchemy import create_engine, Column, Integer, String, UniqueConstraint
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class MyTable(Base):
    __tablename__ = 'my_table'

    id = Column(Integer, primary_key=True)
    field1 = Column(String)
    field2 = Column(String)

    # 定义联合唯一约束
    __table_args__ = (
        UniqueConstraint('field1', 'field2'),
    )

# 创建引擎和表
engine = create_engine('sqlite:///example.db')
Base.metadata.create_all(engine)

主键约束：不能空，也不能重复（列级约束）

主键约束：primary key

主键字段：带有主键约束的字段

主键值：插入在主键字段下的数据

主键作用：（数据库设计三范式：任何一张表都应该有主键）

主键记录上的值是这行记录在这张表上的唯一标识

主键分类：

字段划分：

  单一主键：推荐使用

  复合主键：违背三范式

性质划分：

  自然主键：自然数

  业务主键：银行卡号（不推荐使用）

auto_increment：自增（1开始）

外键约束 ：

foreign key (classno) references t_class(cno)

外键约束：foreign key

外键字段：主键或唯一字段

外简值：

先删子表，再删父表

先加父表，再加子表

先创建父表，再创建子表

# 父表
create table t_class(
    cno int ,
    cname varchar(255),
    primary key (con)
);

# 子表
create table t_student(
    sno int ,
    sname varchar(255),
    classno int ,
    primary key (con),
    foreign key (classno) references t_class(cno)
);

---

sql 语句中，子查询位于主查询内部

mysql> select e.ENAME ,t.* from (select DEPTNO,max(SAL) as sal from EMP group by DEPTNO) t jo
MP e o    -> in EMP e on e.DEPTNO=t.DEPTNO and e.SAL=t.sal;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'jo
in EMP e on e.DEPTNO=t.DEPTNO and e.SAL=t.sal' at line 1
mysql> SELECT e.ENAME, t.*
FROM (
        -> FROM (
    ->     SELECT DEPTNO, MAX(SAL) AS sal
    ->     FROM EMP
    ->     GROUP BY DEPTNO
    -> ) t
    -> JOIN EMP e ON e.DEPTNO = t.DEPTNO AND e.SAL = t.sal;
+-------+--------+---------+
| ENAME | DEPTNO | sal     |
+-------+--------+---------+
| BLAKE |     30 | 2850.00 |
| SCOTT |     20 | 3000.00 |
| KING  |     10 | 5000.00 |
| FORD  |     20 | 3000.00 |
+-------+--------+---------+
4 rows in set (0.00 sec)

事务（Transcation）

一个事务是一个完整的业务逻辑单元，不可再分

和事务相关的只有 DML语句（insert delete update）

事务是为了保证数据的完整性

事务四特性：

原子性：事务是最小的工作单元 ，不可再分

一致性：多条DML 同时成功，同时失败

隔离性：事务之间相互隔离

持久性：最终数据必须保存在硬盘上，事务才算成功

事务隔离级别

读未提交：存在脏读问题（当前事务读到其他事务未提交数据）

读已提交：解决了脏读，存在 不可重复读问题（一个事务中，还没有提交，两次读取的数据不一样【其它事务在期间提交更改数据导致数据不一致】）

可重复读：解决不可重复读问题，存在 幻读 问题（读取到数据幻象）【Mysql默认隔离级别】

串行化：解决所有问题，效率低，需要事务排队

隔离级别	脏读	不可重复读	幻读
读未提交	√	√	√
读已提交	×	√	√
可重复读	×	×	√
串行化	×	×	×

Mysql 事务默认是执行一条就自动提交的，执行完一条DML就自动提交

关闭自动提交需要使用 ：start transcation 开启事务

快照读：正常sql语句（MVCC（多版本并发控制），执行第一个sql语句后，创建一个READ VIEW,后续sql语句都用READ VIEW 在undo log版本链中找事务开始时的数据 ，避免了幻读）

当前读：在正常sql语句后加 for update (读到的永远是最新的)（对查询范围内的数据加锁，不允许其他事物对此范围内数据进行增删改，select查询出来的数据不允许并发，只能排队，从而解决幻读）

在可重复读隔离级别下，如果在一个事务中，读取数据全部采用快照，或者全部采用当前读方式可以一定程度上解决幻读问题，但是，如果混合使用快照和当前读就会出现幻读。

尽量在事务开启状态下，使用 当前读 加锁解决幻读

select…… for update 加的锁叫 next-key lock ,称为 间隙锁+记录锁

​ 间隙锁：防插入

​ 记录锁：防删除

查看全局隔离级别

mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+
1 row in set (0.00 sec)

修改全局隔离级别

mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| READ-COMMITTED                 |
+--------------------------------+
1 row in set (0.00 sec)

查看当前会话事务隔离级别

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

修改当前会话隔离级别

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@TRANSACTION_ISOLATION;
+-------------------------+
| @@TRANSACTION_ISOLATION |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+
1 row in set (0.00 sec)

索引：（目录）一种提高检索效率的提前排好序的数据结构，是解决sql慢查询的一种方式

原理：(缩小扫描范围)

​ 通过B+Tree 缩小扫描范围，索引底层进行了排序，分区，索引会携带数据在表中的物理地址，最终通过索引检索到数据后，获取到关联的物理地址，通过物理地址定位数据，效率是最高的

索引应用场景：

使用：

​ 频繁查询的字段

​ 大表

​ 需要排序或者分组的字段

​ 外键关联的字段

不使用：

​ 频繁更新的表

​ 小表

​ 唯一性很差的字段

全表扫描：

索引检索：（效率较高）

主键 和 具有unique约束 的字段会自动添加索引**

索引分类：

按数据类型：

​ B+树索引： InnoDB存储引擎

​ Hash索引: 仅支持 Memory 存储引擎

按物理存储：

​ 聚集索引：索引和表数据存在一起，一张表只能有一个聚集索引 (优点：数据存储在索引树的叶子节点上，可以减少一次查询；缺点：对数据进行增删改时需要更新索引树，增加系统开销)

​ 非聚集索引：索引和表中数据分开存储，索引独立于表空间，一张表可以有多个非聚集索引

​ 二级索引（辅助索引）：属于非聚集索引，所有非主键索引都是二级索引 使用select * 并且where条件使用的是二级索引 ，会出现回表现象，即查询到叶子节点上的对应主键索引后会利用主键索引二次回到原数据表中进行查找

​ 覆盖索引：构建一个复合索引，堵盖查询语句中所有的字段，查询到的索引不需要进行回表操作

​ 索引下推：一般Mysql优化器自动处理，不需要干预。减少回表次数 ，基于多列联合索引，索引层面过滤数据，不需要全查出来

​

按字段特性：

​ 普通索引

​ 全文索引：仅支持InnoDB和MyISAM,要求字段类型都是文本内容才可以采用

​ 主键索引：主键自动添加索引

​ 唯一索引：有unique约束字段会自动添加索引

按字段个数：

​ 单列索引

​ 联合索引（复合索引/组合索引）

索引优缺点：

​ 优点：

​ 提高查询效率

​ 加速排序

​ 减少磁盘I/O

​ 缺点：

​ 占据额外磁盘空间

​ 增加增删改操作的性能损耗

​ 资源消耗较大

索引优化：

sql优化：

1. sql 查询优化：最低成本，效果显著
1. 库表结构优化
1. 系统配置优化
1. 硬件优化

sql 性能查询工具

show global status like 'Com_select';
show global status like 'Com_insert';
show global status like 'Com_update';
show global status like 'Com_delete';
# 查看所有Com开始的状态
show global status like 'Com_______';

反映从mysql 服务器启动时刻，所有select 查询总数（了解Mysql服务器长期执行情况，帮助我们了解性能瓶颈）

Com_select：次数过高，表示该数据库是读密集型数据库

Com_select：次数很少，同时Com_insert，Com_update，Com_delete，次数很高，表示该数据库是写密集型数据库

慢查询日志：(默认关闭)

开启方式：

​ my.ini 文件/ my.cnf(mac ,linux)

​ 日志存在 /data/***-slow.log文件里

将查询较慢的DQL 语句记录下来，便于定位需要优化的语句

show variables like 'slow_query_log';

查看一个sql语句 执行过程中的具体耗时情况

# 查看是否支持profile 操作
select @@have_profiling;
# 查看是否开启profile
select @@profiling
# 打开profile
set profiling = 1;
# 查看所有select 语句(包括其他命令)执行耗时情况
show profiles;
# 查询某个sql语句在每个执行阶段的详细耗时情况
show profile for query 19;
# 查询某个sql语句在每个执行阶段cpu的详细耗时情况
show profile cpu for query 19;

explain (查看DQL 语句执行计划)

​ **最左前缀原则：**如果给一个表的三个字段添加联合索引时，三个条件按顺序，那么在select查询时候，where 后边必须有三个中的第一个（创建索引时第一个）字段参与，否则复合索引失效。

如果三个条件中间断开会导致部分使用索引

所以 条件虽好是和创建顺序保持一致（顺序乱可以，但是不能断开）

当使用范围查找时，范围查找右侧的列不允许使用索引，如果范围条件中有等号，索引依旧可以生效

索引失效：

​ 模糊查询时第一个通配符使用%，索引失效

​ 索引列参与运算，索引失效

​ 查询条件中有or ,只要 or 条件中 有未添加索引的字段，索引失效

​ 当符合条件的记录在整个表中占比较大 (is null , is not null)，索引失效

​ 索引列是字符串类型，但是查询时省略了单引号，索引失效

指定索引：

​ 当一个字段上既有单列索引，又有复合索引，使用sql提示类要求sql语句执行时指定使用某个索引

​ use index(索引名)：只是建议，底层mysql会根据实际情况考虑是否使用

​ ignore index(索引名)：忽略该索引

​ force index(索引名)：强行使用该索引

覆盖索引：select 后的字段，这些字段尽可能是索引所覆盖的字段，这样可以避免回表

前缀索引：

索引创建原则

limit 优化：

​ 越往后取越慢，使用 覆盖索引+子查询，查询出临时表（索引列），使用查询出来的索引结果和原表做表连接

主键优化：

主键设计原则：

​ 主键值不要太长：二级索引叶子节点存储的是主键值，主键值太长会引起索引占用空间较大

​ 尽量使用auto_increment 生成主键，尽量不适用uuid做主键，因为uuid不是顺序插入的

​ 最好不使用业务主键，业务主键会导致主键值频繁修改，主键值不建议修改，聚集索引一定会重新排序

​ 在插入数据时，主键值最好是顺序插入的，不要乱序插入，因为乱序插入会导致B+树的叶子节点频繁的进行页分裂和页合并操作，效率较低

insert 优化：

​ 批量插入，数据量较大时，不要一条一条插入，可以批量插入，建议一次插入数据不超过1000条

​ mysql默认是自动提交事务的，只要执行一条DML语句就自动提交一次，因此，当数据量较大时，建议手动开启事务，手动提交事务，不建议使用数据库自动提交机制

​ 主键值建议采用顺序插入，顺序插入比乱序插入效率高

​ 超大量数据插入可以使用mysql提供的load指令，load指令可以将CSV文件中的数据批量导入数据库表中，并且效率高

​

# load 指令过程
mysql --local-infile -uroot -p

set global local_infile = 1;

# use database;
# create table load_test……
load data local infile '1.csv' into table load_test fields terminated by ',' lines terminated by '\n';

​

count 优化：

​ count(主键)：每个主键取出，累加

​ count（常量值）：取到每个常量值，累加

​ count(字段)：取出字段的每个值，判断是否为null,不是null 的累加

​ count(*):不用取值，底层mysql做了优化，直接统计行数，效率最高

**如统计一张表中的数据行数：建议使用count(*)**

注意：

​ 对于InnoDB 存储引擎来说，count 计数的实现原理就是将一张表中的每一条记录取出，然后累加，如果想要提高效率，可以自己使用其他程序来实现，例如向没一张表中插入记录时，在redis中维护一个总行数，这样执行获取总行数时，直接从redis中获取，效率最高

​ 对于MyISAM存储引擎来说，当select 语句没有where 条件时，获取总行数效率极高，不需要统计，因为MyISAM存储引擎维护了一个单独的总行数

update 优化:

​ 当存储引擎是InnoDb时，表的行级锁是针对索引添加的锁，如索引失效，或者不是索引列，会提升为表

Mysql 为什么采用B+树作为索引的数据结构，而不是B树？

1. 非叶子节点可以存储更多的键值，阶数可以更大 ，更矮更胖，磁盘I/O次数减少，数据查询效率高

2. 所有数据有序存储在叶子节点上，让范围查找，分组查找效率更高

3. 数据页之间，数据记录之间采用链表链接，让升序降序更加方便操作

如果一张表没有主键索引，哪还会创建B+树吗？

​ 默认使用一个内置的聚集索引，这个聚集索引是基于表的物理存储顺序构建的，通常使用B+树实现

InnoDB 主键上的索引都是聚集索引

MyISAM 任意字段上的索引都是非聚集索引

# 查看表添加的索引信息
show index from 表名;

查看执行计划 [看 type]

mysql> explain select * from EMP where SAL >=3000;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | EMP   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

给字段创建索引

mysql> CREATE INDEX emp_sal_index on EMP(SAL);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0


# 执行计划
mysql> explain select * from EMP where SAL >=3000;
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | EMP   | NULL       | range | emp_sal_index | emp_sal_index | 9       | NULL |    3 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

删除索引

mysql> drop index emp_sal_index on EMP;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

视图 只能以DQL创建视图

create table emp_bak as select * from EMP;
# 创建视图
create view myview as select EMPNO,ENAME,SAL from emp_bak;
# 更新视图(原表数据也会修改)
update myview set ENAME='hehe',SAL=1 where EMPNO=7369;
# 删除视图
drop view if exists myview;

DBA 命令

1. 导出数据库中的数据

   Dos 窗口执行，不需要登录数据库管理系统

   mysqldump demo > E:\Desktop\demo.sql -u root -p123456
   

2. 导入数据

   create database demo;
   use demo;
   source E:\Desktop\demo.sql
   

建库建表语句放到一个sql文件里执行

export MYSQL_PWD='123456'
mysql -uroot < sql.sql

sql.sql

create database if not exists test02;
use test02;
DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;
 
CREATE TABLE DEPT
       (DEPTNO int(2) not null ,
    DNAME VARCHAR(14) ,
    LOC VARCHAR(13),
    primary key (DEPTNO)
    );
CREATE TABLE EMP
       (EMPNO int(4)  not null ,
    ENAME VARCHAR(10),
    JOB VARCHAR(9),
    MGR INT(4),
    HIREDATE DATE  DEFAULT NULL,
    SAL DOUBLE(7,2),
    COMM DOUBLE(7,2),
    primary key (EMPNO),
    DEPTNO INT(2) 
    )
    ;
 
CREATE TABLE SALGRADE
      ( GRADE INT,
    LOSAL INT,
    HISAL INT );
 
 
 
 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
20, 'RESEARCH', 'DALLAS'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
30, 'SALES', 'CHICAGO'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
40, 'OPERATIONS', 'BOSTON'); 
commit;
  
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7369, 'SMITH', 'CLERK', 7902,  '1980-12-17'
, 800, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7499, 'ALLEN', 'SALESMAN', 7698,  '1981-02-20'
, 1600, 300, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7521, 'WARD', 'SALESMAN', 7698,  '1981-02-22'
, 1250, 500, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7566, 'JONES', 'MANAGER', 7839,  '1981-04-02'
, 2975, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7654, 'MARTIN', 'SALESMAN', 7698,  '1981-09-28'
, 1250, 1400, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7698, 'BLAKE', 'MANAGER', 7839,  '1981-05-01'
, 2850, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7782, 'CLARK', 'MANAGER', 7839,  '1981-06-09'
, 2450, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7839, 'KING', 'PRESIDENT', NULL,  '1981-11-17'
, 5000, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7844, 'TURNER', 'SALESMAN', 7698,  '1981-09-08'
, 1500, 0, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7876, 'ADAMS', 'CLERK', 7788,  '1987-05-23'
, 1100, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7900, 'JAMES', 'CLERK', 7698,  '1981-12-03'
, 950, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7902, 'FORD', 'ANALYST', 7566,  '1981-12-03'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7934, 'MILLER', 'CLERK', 7782,  '1982-01-23'
, 1300, NULL, 10); 
commit;
  
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
1, 700, 1200); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
2, 1201, 1400); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
3, 1401, 2000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
4, 2001, 3000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
5, 3001, 9999); 
commit;

数据库三范式（解决数据冗余）

1. 任何一张表都有主键，并且每一个字段原子性不可再分
2. 建立在1基础上，所有非主键字段完全依赖主键，不能产生部分依赖 [复合主键会出现不满足2要求]
3. 建立在2 基础上，不能产生传递依赖

查询每个部门最大薪资人员名称

mysql> select e.ENAME,t.DEPTNO,t.max_sal from EMP e join (select max(SAL) max_sal,DEPTNO from EMP group by DEPTNO) t on e.SAL=t.max_sal and e.DEPTNO=t.DEPTNO
;
+-------+--------+---------+
| ENAME | DEPTNO | max_sal |
+-------+--------+---------+
| BLAKE |     30 | 2850.00 |
| SCOTT |     20 | 3000.00 |
| KING  |     10 | 5000.00 |
| FORD  |     20 | 3000.00 |
+-------+--------+---------+
4 rows in set (0.00 sec)

哪些人的薪水在部门平均薪水之上

mysql> select e.ENAME,e.DEPTNO,e.SAL,d.avg from EMP e join ( select avg(SAL) avg ,DEPTNO from EMP group by DEPTNO) d on e.SAL > d.avg and e.DEPTNO=d.DEPTNO;
+-------+--------+---------+-------------+
| ENAME | DEPTNO | SAL     | avg         |
+-------+--------+---------+-------------+
| FORD  |     20 | 3000.00 | 2175.000000 |
| SCOTT |     20 | 3000.00 | 2175.000000 |
| JONES |     20 | 2975.00 | 2175.000000 |
| BLAKE |     30 | 2850.00 | 1566.666667 |
| ALLEN |     30 | 1600.00 | 1566.666667 |
| KING  |     10 | 5000.00 | 2916.666667 |
+-------+--------+---------+-------------+
6 rows in set (0.00 sec)

取得部门中所有人的平均的薪水等级

mysql> select t.DEPTNO,avg(t.grade) from (select e.*,d.GRADE grade from EMP e join SALGRADE d on e.SAL between d.LOSAL and d.HISAL) t group by DEPTNO;
+--------+--------------+
| DEPTNO | avg(t.grade) |
+--------+--------------+
|     20 |       2.8000 |
|     30 |       2.5000 |
|     10 |       3.6667 |
+--------+--------------+
3 rows in set (0.00 sec)
# 不用临时表
mysql> select e.DEPTNO,avg(s.GRADE) from EMP e join SALGRADE s on e.SAL between s.LOSAL and s.HISAL group by e.DEPTNO;
+--------+--------------+
| DEPTNO | avg(s.GRADE) |
+--------+--------------+
|     20 |       2.8000 |
|     30 |       2.5000 |
|     10 |       3.6667 |
+--------+--------------+
3 rows in set (0.00 sec)

不用max 取得最高薪水

mysql> select * from EMP order  by SAL DESC limit 1;
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
+-------+-------+-----------+------+------------+---------+------+--------+
1 row in set (0.00 sec)

# 自连接
mysql> select * from EMP where SAL not in (select distinct b.SAL from EMP a join EMP b on a.SAL > b.SAL);
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
+-------+-------+-----------+------+------------+---------+------+--------+
1 row in set (0.00 sec)

取得平均薪水最高的部门编号

mysql> select DEPTNO ,avg(SAL) avg from EMP group by DEPTNO order by avg desc limit 1;
+--------+-------------+
| DEPTNO | avg         |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+
1 row in set (0.00 sec)




mysql> select DEPTNO ,avg(SAL) avgsal from EMP group by DEPTNO having avgsal = (select max(t.avg) from (select DEPTNO ,avg(SAL) avg from EMP group by DEPTNO)
 t );
+--------+-------------+
| DEPTNO | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+
1 row in set (0.00 sec)

求平均薪水的等级最低的部门的部门名称

mysql> select d.DNAME from DEPT d join (select b.DEPTNO from SALGRADE t join (select DEPTNO,avg(SAL) sal from EMP group
by DEPTNO) b on b.sal between t.LOSAL and t.HISAL order by t.GRADE limit 1) q on q.DEPTNO =d.DEPTNO ;
+-------+
| DNAME |
+-------+
| SALES |
+-------+
1 row in set (0.00 sec)

取得比普通员工(员工编号没有在MGR字段出现)的最高薪水还要高的领导人的名字

mysql> select ENAME from EMP where EMPNO in (select distinct MGR from EMP where MGR) and SAL >(select MAX(SAL) from EMP where EMPNO not in (select distinct MGR from EMP where MGR));
+-------+
| ENAME |
+-------+
| JONES |
| BLAKE |
| CLARK |
| SCOTT |
| KING  |
| FORD  |
+-------+
6 rows in set (0.01 sec)

取得每个薪水等级有多少员工

mysql> select g.GRADE,count(g.GRADE) from EMP e join SALGRADE g on e.SAL between g.LOSAL and g.HISAL group by g.GRADE;
+-------+----------------+
| GRADE | count(g.GRADE) |
+-------+----------------+
|     1 |              3 |
|     3 |              2 |
|     2 |              3 |
|     4 |              5 |
|     5 |              1 |
+-------+----------------+
5 rows in set (0.00 sec)

找出每个员工和对应上级领导的名字（left join）

mysql> select a.ENAME ,b.ENAME from EMP a left join EMP b on a.MGR=b.EMPNO;
+--------+-------+
| ENAME  | ENAME |
+--------+-------+
| nu_nij | NULL  |
| SMITH  | FORD  |
| ALLEN  | BLAKE |
| WARD   | BLAKE |
| JONES  | KING  |
| MARTIN | BLAKE |
| BLAKE  | KING  |
| CLARK  | KING  |
| SCOTT  | JONES |
| KING   | NULL  |
| TURNER | BLAKE |
| ADAMS  | SCOTT |
| JAMES  | BLAKE |
| FORD   | JONES |
| MILLER | CLARK |
+--------+-------+
15 rows in set (0.00 sec)

找出受雇日期早于直属上级领导的所有员工的员工编号，姓名，部门名

mysql> select a.EMPNO, a.ENAME,c.DNAME from EMP a join EMP b on a.HIREDATE < b.HIREDATE and a.MGR =b.EMPNO join DEPT c on c.DEPTNO=a.DEPTNO;
+-------+-------+------------+
| EMPNO | ENAME | DNAME      |
+-------+-------+------------+
|  7369 | SMITH | RESEARCH   |
|  7499 | ALLEN | SALES      |
|  7521 | WARD  | SALES      |
|  7566 | JONES | RESEARCH   |
|  7698 | BLAKE | SALES      |
|  7782 | CLARK | ACCOUNTING |
+-------+-------+------------+
6 rows in set (0.00 sec)

列出部门名称，和这些部门的员工信息，并同时列出没有员工的部门

mysql> select e.*,d.DEPTNO,d.DNAME from EMP e right join DEPT d on d.DEPTNO=e.DEPTNO;
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO | DEPTNO | DNAME      |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |     10 | ACCOUNTING |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |     10 | ACCOUNTING |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |     10 | ACCOUNTING |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |     20 | RESEARCH   |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |     20 | RESEARCH   |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |     20 | RESEARCH   |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |     20 | RESEARCH   |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |     20 | RESEARCH   |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |     30 | SALES      |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |     30 | SALES      |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |     30 | SALES      |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |     30 | SALES      |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |     30 | SALES      |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |     30 | SALES      |
|  NULL | NULL   | NULL      | NULL | NULL       |    NULL |    NULL |   NULL |     40 | OPERATIONS |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+
15 rows in set (0.00 sec)

列出至少有5个员工的所有部门

mysql> select DEPTNO,count(DEPTNO) cnt from EMP group by DEPTNO having cnt >= 5;
+--------+-----+
| DEPTNO | cnt |
+--------+-----+
|     20 |   5 |
|     30 |   6 |
+--------+-----+
2 rows in set (0.00 sec)

列出比‘SMITH’薪资高的员工信息

mysql> select * from EMP where SAL >(select SAL from EMP where ENAME='SMITH');
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
13 rows in set (0.00 sec)

列出所有办事员‘CLERK’ 的姓名，部门名称，部门人数

mysql> select e.ENAME,d.DNAME,c.cnt from EMP e join DEPT d on e.DEPTNO=d.DEPTNO and e.JOB='CLERK' join (select DEPTNO,count(DEPTNO) cnt from EMP g
roup by DEPTNO) c on c.DEPTNO=e.DEPTNO;
+--------+------------+-----+
| ENAME  | DNAME      | cnt |
+--------+------------+-----+
| SMITH  | RESEARCH   |   5 |
| ADAMS  | RESEARCH   |   5 |
| JAMES  | SALES      |   6 |
| MILLER | ACCOUNTING |   3 |
+--------+------------+-----+
4 rows in set (0.00 sec)

列出最低薪金大于1500的各种工作 及从事此工作的全部雇员人数

mysql> select a.cnt, b.JOB from (select count(JOB) cnt,JOB from EMP group by JOB) a right join (select JOB from EMP group by JOB having min(SAL) > 1500) b on a.JOB=b.JOB;
+------+-----------+
| cnt  | JOB       |
+------+-----------+
|    3 | MANAGER   |
|    2 | ANALYST   |
|    1 | PRESIDENT |
+------+-----------+
3 rows in set (0.00 sec)

列出薪金高于在30部门工作的所有人员的薪金的员工姓名，薪金，部门名称

mysql> select e.ENAME,e.SAL,d.DNAME from EMP e join DEPT d on e.SAL > (select MAX(SAL) from EMP group by DEPTNO having DEPTNO=30) and d.DEPTNO=e.DEPTNO;
+-------+---------+----------+
| ENAME | SAL     | DNAME    |
+-------+---------+----------+
| JONES | 2975.00 | RESEARCH |
| SCOTT | 3000.00 | RESEARCH |
| KING  | 5000.00 | RENSHIBU |
| FORD  | 3000.00 | RESEARCH |
+-------+---------+----------+
4 rows in set (0.00 sec)

列出在每个部门工作的员工数量，平均工资，平均服务期限，没有员工的部门，部门人数0

NOW() :当前时间

ifnull（判断字段，默认值）

TimeStampDiff(间隔类型，前一个日期，后一个日期)

​ 间隔类型：YEAR ,MONTH,DAY ,HOUR,MINUTE,SECOND,WEEK,QUARTER(季度)

mysql> select d.DEPTNO,count(e.ENAME),ifnull(avg(e.SAL),0),avg(TimeStampDiff(YEAR,e.HIREDATE,NOW())) from EMP e right join DEPT d on e.DEPTNO=d.D
EPTNO group by d.DEPTNO;
+--------+----------------+----------------------+-------------------------------------------+
| DEPTNO | count(e.ENAME) | ifnull(avg(e.SAL),0) | avg(TimeStampDiff(YEAR,e.HIREDATE,NOW())) |
+--------+----------------+----------------------+-------------------------------------------+
|     10 |              3 |          2916.666667 |                                   42.3333 |
|     20 |              5 |          2175.000000 |                                   40.4000 |
|     30 |              6 |          1566.666667 |                                   42.5000 |
|     40 |              0 |             0.000000 |                                      NULL |
+--------+----------------+----------------------+-------------------------------------------+
4 rows in set (0.00 sec)

列出所有员工姓名，部门名称，工资

mysql> select d.DNAME,e.ENAME,e.SAL from EMP e join DEPT d on e.DEPTNO=d.DEPTNO;
+----------+--------+---------+
| DNAME    | ENAME  | SAL     |
+----------+--------+---------+
| RESEARCH | SMITH  |  800.00 |
| SALES    | ALLEN  | 1600.00 |
| SALES    | WARD   | 1250.00 |
| RESEARCH | JONES  | 2975.00 |
| SALES    | MARTIN | 1250.00 |
| SALES    | BLAKE  | 2850.00 |
| RENSHIBU | CLARK  | 2450.00 |
| RESEARCH | SCOTT  | 3000.00 |
| RENSHIBU | KING   | 5000.00 |
| SALES    | TURNER | 1500.00 |
| RESEARCH | ADAMS  | 1100.00 |
| SALES    | JAMES  |  950.00 |
| RESEARCH | FORD   | 3000.00 |
| RENSHIBU | MILLER | 1300.00 |
+----------+--------+---------+
14 rows in set (0.00 sec)

列出所有部门详细信息和人数

mysql> select d.*,count(e.ENAME) from EMP e right join DEPT d on e.DEPTNO=d.DEPTNO group by d.DEPTNO;
+--------+------------+----------+----------------+
| DEPTNO | DNAME      | LOC      | count(e.ENAME) |
+--------+------------+----------+----------------+
|     10 | RENSHIBU   | SHANGHAI |              3 |
|     20 | RESEARCH   | DALLAS   |              5 |
|     30 | SALES      | CHICAGO  |              6 |
|     40 | OPERATIONS | BOSTON   |              0 |
+--------+------------+----------+----------------+
4 rows in set (0.00 sec)

列出各种工作的最低工资即对应雇员名

mysql> select e.ENAME,b.JOB,b.msal from EMP e join (select JOB,min(SAL) msal from EMP group by JOB) b on e.JOB=b.JOB and e.SAL=b.msal;
+--------+-----------+---------+
| ENAME  | JOB       | msal    |
+--------+-----------+---------+
| SMITH  | CLERK     |  800.00 |
| WARD   | SALESMAN  | 1250.00 |
| MARTIN | SALESMAN  | 1250.00 |
| CLARK  | MANAGER   | 2450.00 |
| SCOTT  | ANALYST   | 3000.00 |
| KING   | PRESIDENT | 5000.00 |
| FORD   | ANALYST   | 3000.00 |
+--------+-----------+---------+
7 rows in set (0.00 sec)

列出各部门 MANAGER 的最低薪金

+----------+--------+
| min(SAL) | DEPTNO |
+----------+--------+
|  2975.00 |     20 |
|  2850.00 |     30 |
|  2450.00 |     10 |
+----------+--------+
3 rows in set (0.00 sec)

里出所有员工的年工资，按年薪从低到高排序

mysql> select *,ifnull(SAL*12,0) ysal from EMP order by ysal;
+-------+--------+-----------+------+------------+---------+---------+--------+----------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO | ysal     |
+-------+--------+-----------+------+------------+---------+---------+--------+----------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |  9600.00 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 | 11400.00 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 | 13200.00 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 | 15000.00 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 | 15000.00 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 | 15600.00 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 | 18000.00 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 | 19200.00 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 | 29400.00 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 | 34200.00 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 | 35700.00 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 | 36000.00 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 | 36000.00 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 | 60000.00 |
+-------+--------+-----------+------+------------+---------+---------+--------+----------+
14 rows in set (0.00 sec)

求出员工 领导的薪水超过3000的员工姓名与领导

mysql> select a.ENAME 'yuangong',b.ENAME 'lingdao' from EMP a join EMP b on a.MGR=b.EMPNO and b.SAL >3000;
+----------+---------+
| yuangong | lingdao |
+----------+---------+
| JONES    | KING    |
| BLAKE    | KING    |
| CLARK    | KING    |
+----------+---------+
3 rows in set (0.00 sec)

查询结果转大写:ucase，upper

mysql> select upper(ename) from EMP;
+--------------+
| upper(ename) |
+--------------+
| NU_NIJ       |
| SMITH        |
| ALLEN        |
| WARD         |
| JONES        |
| MARTIN       |
| BLAKE        |
| CLARK        |
| SCOTT        |
| KING         |
| TURNER       |
| ADAMS        |
| JAMES        |
| FORD         |
| MILLER       |
+--------------+
15 rows in set (0.00 sec)

mysql> select ucase(ename) from EMP;
+--------------+
| ucase(ename) |
+--------------+
| NU_NIJ       |
| SMITH        |
| ALLEN        |
| WARD         |
| JONES        |
| MARTIN       |
| BLAKE        |
| CLARK        |
| SCOTT        |
| KING         |
| TURNER       |
| ADAMS        |
| JAMES        |
| FORD         |
| MILLER       |
+--------------+
15 rows in set (0.00 sec)

查询结果转小写 lower，lcase

mysql> select lower(ename) from EMP;
+--------------+
| lower(ename) |
+--------------+
| nu_nij       |
| smith        |
| allen        |
| ward         |
| jones        |
| martin       |
| blake        |
| clark        |
| scott        |
| king         |
| turner       |
| adams        |
| james        |
| ford         |
| miller       |
+--------------+
15 rows in set (0.00 sec)

mysql> select lcase(ename) from EMP;
+--------------+
| lcase(ename) |
+--------------+
| nu_nij       |
| smith        |
| allen        |
| ward         |
| jones        |
| martin       |
| blake        |
| clark        |
| scott        |
| king         |
| turner       |
| adams        |
| james        |
| ford         |
| miller       |
+--------------+
15 rows in set (0.00 sec)

查询结果中截取 substr（字段名，开始(从1开始)，截取长度）

mysql> select substr(ENAME,1,2) from EMP;
+-------------------+
| substr(ENAME,1,2) |
+-------------------+
| nu                |
| SM                |
| AL                |
| WA                |
| JO                |
| MA                |
| BL                |
| CL                |
| SC                |
| KI                |
| TU                |
| AD                |
| JA                |
| FO                |
| MI                |
+-------------------+
15 rows in set (0.00 sec)

获取结果长度

length：统计字节长度（一个中文2字节）

char_length:统计字符长度

mysql>  select length(ENAME),ENAME from EMP;
+---------------+--------+
| length(ENAME) | ENAME  |
+---------------+--------+
|             6 | nu_nij |
|             5 | SMITH  |
|             5 | ALLEN  |
|             4 | WARD   |
|             5 | JONES  |
|             6 | MARTIN |
|             5 | BLAKE  |
|             5 | CLARK  |
|             5 | SCOTT  |
|             4 | KING   |
|             6 | TURNER |
|             5 | ADAMS  |
|             5 | JAMES  |
|             4 | FORD   |
|             6 | MILLER |
+---------------+--------+
15 rows in set (0.00 sec)

mysql>  select char_length(ENAME),ENAME from EMP;
+--------------------+--------+
| char_length(ENAME) | ENAME  |
+--------------------+--------+
|                  6 | nu_nij |
|                  5 | SMITH  |
|                  5 | ALLEN  |
|                  4 | WARD   |
|                  5 | JONES  |
|                  6 | MARTIN |
|                  5 | BLAKE  |
|                  5 | CLARK  |
|                  5 | SCOTT  |
|                  4 | KING   |
|                  6 | TURNER |
|                  5 | ADAMS  |
|                  5 | JAMES  |
|                  4 | FORD   |
|                  6 | MILLER |
+--------------------+--------+
15 rows in set (0.00 sec)

查询结果拼接 concat

mysql> select concat(ENAME,sal,'123') from EMP;
+-------------------------+
| concat(ENAME,sal,'123') |
+-------------------------+
| NULL                    |
| SMITH800.00123          |
| ALLEN1600.00123         |
| WARD1250.00123          |
| JONES2975.00123         |
| MARTIN1250.00123        |
| BLAKE2850.00123         |
| CLARK2450.00123         |
| SCOTT3000.00123         |
| KING5000.00123          |
| TURNER1500.00123        |
| ADAMS1100.00123         |
| JAMES950.00123          |
| FORD3000.00123          |
| MILLER1300.00123        |
+-------------------------+
15 rows in set (0.00 sec)

去除参与拼接字符串中的前后空白字符 trim

mysql> select concat(trim(' a   sd a s           '),ename) from EMP;
+----------------------------------------------+
| concat(trim(' a   sd a s           '),ename) |
+----------------------------------------------+
| a   sd a snu_nij                             |
| a   sd a sSMITH                              |
| a   sd a sALLEN                              |
| a   sd a sWARD                               |
| a   sd a sJONES                              |
| a   sd a sMARTIN                             |
| a   sd a sBLAKE                              |
| a   sd a sCLARK                              |
| a   sd a sSCOTT                              |
| a   sd a sKING                               |
| a   sd a sTURNER                             |
| a   sd a sADAMS                              |
| a   sd a sJAMES                              |
| a   sd a sFORD                               |
| a   sd a sMILLER                             |
+----------------------------------------------+
15 rows in set (0.01 sec)

生成0-1随机数 rand()

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.8497881794812048 |
+--------------------+
1 row in set (0.01 sec)

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.2676997531835919 |
+--------------------+
1 row in set (0.00 sec)

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.7891342582079901 |
+--------------------+
1 row in set (0.00 sec)
# 后边加个固定值就可以重复获取某一个随机数
mysql> select rand(2);
+--------------------+
| rand(2)            |
+--------------------+
| 0.6555866465490187 |
+--------------------+
1 row in set (0.01 sec)

mysql> select rand(2);
+--------------------+
| rand(2)            |
+--------------------+
| 0.6555866465490187 |
+--------------------+
1 row in set (0.00 sec)

四舍五入 round(数值，保留位数)

mysql> select round(3);
+----------+
| round(3) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

mysql> select round(3.1);
+------------+
| round(3.1) |
+------------+
|          3 |
+------------+
1 row in set (0.00 sec)

mysql> select round(3.6);
+------------+
| round(3.6) |
+------------+
|          4 |
+------------+
1 row in set (0.00 sec)

mysql> select round(3.66,1);
+---------------+
| round(3.66,1) |
+---------------+
|           3.7 |
+---------------+
1 row in set (0.00 sec)

mysql> select round(3.66,2);
+---------------+
| round(3.66,2) |
+---------------+
|          3.66 |
+---------------+
1 row in set (0.00 sec)

mysql> select round(3.66,3);
+---------------+
| round(3.66,3) |
+---------------+
|          3.66 |
+---------------+
1 row in set (0.00 sec)

舍位 truncate（数值，保留位数）

mysql> select truncate(9.99,2);
+------------------+
| truncate(9.99,2) |
+------------------+
|             9.99 |
+------------------+
1 row in set (0.00 sec)

mysql> select truncate(9.99999,2);
+---------------------+
| truncate(9.99999,2) |
+---------------------+
|                9.99 |
+---------------------+
1 row in set (0.00 sec)

向上取整（ceil），向下取整(floor)

# 向上取整
mysql> select ceil(4.44);
+------------+
| ceil(4.44) |
+------------+
|          5 |
+------------+
1 row in set (0.01 sec)

mysql> select ceil(4.1);
+-----------+
| ceil(4.1) |
+-----------+
|         5 |
+-----------+
1 row in set (0.00 sec)
# 向下取整
mysql> select floor(111.1);
+--------------+
| floor(111.1) |
+--------------+
|          111 |
+--------------+
1 row in set (0.00 sec)

mysql> select floor(111.9);
+--------------+
| floor(111.9) |
+--------------+
|          111 |
+--------------+
1 row in set (0.00 sec)

获取当前时间

# 获取select语句执行时间节点
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-07-05 06:32:46 |
+---------------------+
1 row in set (0.00 sec)

# 获取sysdate（）执行时间节点
mysql> select sysdate();
+---------------------+
| sysdate()           |
+---------------------+
| 2024-07-05 06:32:51 |
+---------------------+
1 row in set (0.00 sec)


mysql> select sysdate(),sleep(2),sysdate();
+---------------------+----------+---------------------+
| sysdate()           | sleep(2) | sysdate()           |
+---------------------+----------+---------------------+
| 2024-07-05 06:34:34 |        0 | 2024-07-05 06:34:36 |
+---------------------+----------+---------------------+
1 row in set (2.00 sec)

mysql> select now(),sleep(2),now();
+---------------------+----------+---------------------+
| now()               | sleep(2) | now()               |
+---------------------+----------+---------------------+
| 2024-07-05 06:34:51 |        0 | 2024-07-05 06:34:51 |
+---------------------+----------+---------------------+
1 row in set (2.01 sec)

mysql> select now(),sleep(2),sysdate();
+---------------------+----------+---------------------+
| now()               | sleep(2) | sysdate()           |
+---------------------+----------+---------------------+
| 2024-07-05 06:35:06 |        0 | 2024-07-05 06:35:08 |
+---------------------+----------+---------------------+
1 row in set (2.00 sec)

获取当前日期 单独年月日时分秒

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2024-07-05 |
+------------+
1 row in set (0.00 sec)

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 06:36:50  |
+-----------+
1 row in set (0.00 sec)

mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
|        2024 |
+-------------+
1 row in set (0.00 sec)

mysql> select month(now());
+--------------+
| month(now()) |
+--------------+
|            7 |
+--------------+
1 row in set (0.01 sec)

mysql> select day(now());
+------------+
| day(now()) |
+------------+
|          5 |
+------------+
1 row in set (0.00 sec)

mysql> select hour(now());
+-------------+
| hour(now()) |
+-------------+
|           6 |
+-------------+
1 row in set (0.00 sec)

mysql> select minute(now());
+---------------+
| minute(now()) |
+---------------+
|            37 |
+---------------+
1 row in set (0.01 sec)

mysql> select second(now());
+---------------+
| second(now()) |
+---------------+
|            52 |
+---------------+
1 row in set (0.00 sec)

获取执行时间间隔指定时间的时间 (add_date(时间，interval 时长 时间单位))

​ 时长为负数时为向前计算间隔时间

mysql> select date_add(now(),interval 10 day);
+---------------------------------+
| date_add(now(),interval 10 day) |
+---------------------------------+
| 2024-07-15 06:39:50             |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select date_add(now(),interval 10 hour);
+----------------------------------+
| date_add(now(),interval 10 hour) |
+----------------------------------+
| 2024-07-05 16:39:55              |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select date_add(now(),interval -10 hour);
+-----------------------------------+
| date_add(now(),interval -10 hour) |
+-----------------------------------+
| 2024-07-04 20:42:12               |
+-----------------------------------+
1 row in set (0.00 sec)

# 支持复合单位 ，最多两个基本单位拼接
# 三年两个月之后
mysql> select date_add(now(),interval '10,2' year_month);
+--------------------------------------------+
| date_add(now(),interval '10,2' year_month) |
+--------------------------------------------+
| 2034-09-05 06:44:52                        |
+--------------------------------------------+
1 row in set (0.00 sec)

格式化日期类型字符串 date_format(日期，格式)

%Y : 四位年

%y : 两位年

%m : 月

%d : 日

%H : 时

%i : 分

%s : 秒

mysql> select date_format(now(),'%Y-%m-%d');
+-------------------------------+
| date_format(now(),'%Y-%m-%d') |
+-------------------------------+
| 2024-07-05                    |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select date_format(now(),'%Y %m %d');
+-------------------------------+
| date_format(now(),'%Y %m %d') |
+-------------------------------+
| 2024 07 05                    |
+-------------------------------+
1 row in set (0.00 sec)

非标准日期格式化 str_to_date(日期，格式)

mysql> insert into t_student (id,birth) values(1,'10-10-1999');
ERROR 1292 (22007): Incorrect date value: '10-10-1999' for column 'birth' at row 1、

mysql> insert into t_student (id,birth) values(1,str_to_date('10-10-1999'));
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'str_to_date'

mysql> insert into t_student (id,birth) values(1,str_to_date('10-10-1999','%d-%m-%Y'));
Query OK, 1 row affected (0.03 sec)

mysql> select * from t_student;
+------+------------+
| id   | birth      |
+------+------------+
|    1 | 1999-10-10 |
+------+------------+
1 row in set (0.01 sec)

计算两个日期时间差

mysql> select datediff('2020-12-12','2021-12-12');
+-------------------------------------+
| datediff('2020-12-12','2021-12-12') |
+-------------------------------------+
|                                -365 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> select timediff('2020-12-12 12:12:12','2021-12-12 12:12:01');
+-------------------------------------------------------+
| timediff('2020-12-12 12:12:12','2021-12-12 12:12:01') |
+-------------------------------------------------------+
| -838:59:59                                            |
+-------------------------------------------------------+
1 row in set, 1 warning (0.01 sec)

if 判断

mysql> select * from t_stu;
+------+------+
| id   | sex  |
+------+------+
|    1 |    0 |
|    2 |    1 |
|    3 |    0 |
+------+------+
3 rows in set (0.00 sec)

mysql> select id,if(sex=0,'nan','nv') from t_stu;
+------+----------------------+
| id   | if(sex=0,'nan','nv') |
+------+----------------------+
|    1 | nan                  |
|    2 | nv                   |
|    3 | nan                  |
+------+----------------------+
3 rows in set (0.00 sec)

数据库md5()

mysql> select md5('124312413431');
+----------------------------------+
| md5('124312413431')              |
+----------------------------------+
| 089c41c7e4de8b53f9c8472c6ea68623 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select md5('124312413431');
+----------------------------------+
| md5('124312413431')              |
+----------------------------------+
| 089c41c7e4de8b53f9c8472c6ea68623 |
+----------------------------------+
1 row in set (0.00 sec)

# 直接往数据库里插入md5字符串
mysql> create table t_d ( id int, des varchar(32));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t_d (id,des) values(1,md5('i like you'));
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_d;
+------+----------------------------------+
| id   | des                              |
+------+----------------------------------+
|    1 | 6ece875294d9caabb6655a1704e63d98 |
+------+----------------------------------+
1 row in set (0.00 sec)

---

text ,enum ,set (建表时的类型)

Text:

tinytext	255
text	65535
mediumtext	1677215
longtext	4294967295或4GB字符

enum:

​ 语法：字段名 enum(‘值1’,‘值2’，……)

​ 只能插入指定的枚举值

set:

​ 语法:字段名 set (‘值1’,‘值2’，……) 值不可重复

​ 只能插入指定的值

---

修改表名

alter table 表名 rename 新名;

新增字段

alter table 表名 add 字段名 字段描述

mysql> alter table t_stu add name varchar(255) comment 'name' not null;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t_stu;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                      |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_stu | CREATE TABLE `t_stu` (
  `id` int DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  `name` varchar(255) NOT NULL COMMENT 'name'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

修改字段名

alter table 表名 change 字段名 新字段名 新字段描述

mysql> alter table t_stu change name xingming varchar(50) not null;
Query OK, 3 rows affected (0.15 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show create table t_stu;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                          |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_stu | CREATE TABLE `t_stu` (
  `id` int DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  `xingming` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

修改字段数据类型

alter table 表名  modify column 字段名 数据类型

mysql> alter table t_stu modify column xingming varchar(255) null;
Query OK, 3 rows affected (0.19 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show create table t_stu;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                               |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_stu | CREATE TABLE `t_stu` (
  `id` int DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  `xingming` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

删除字段

alter table 表名 drop 字段名

mysql> alter table t_stu drop xingming;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t_stu;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                       |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| t_stu | CREATE TABLE `t_stu` (
  `id` int DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

---

约束

​ 非空约束

​ 检查约束（mysql8+）

mysql> create table if not exists check_test( id int auto_increment primary key not null, sex enum('0','1') not null, age int not null , check(age>0) );
Query OK, 0 rows affected (0.06 sec)

mysql> desc check_test;
+-------+---------------+------+-----+---------+----------------+
| Field | Type          | Null | Key | Default | Extra          |
+-------+---------------+------+-----+---------+----------------+
| id    | int           | NO   | PRI | NULL    | auto_increment |
| sex   | enum('0','1') | NO   |     | NULL    |                |
| age   | int           | NO   |     | NULL    |                |
+-------+---------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> insert into check_test (sex,age)values('1',10);
Query OK, 1 row affected (0.02 sec)

mysql> insert into check_test (sex,age)values('1',-10);
ERROR 3819 (HY000): Check constraint 'check_test_chk_1' is violated.
mysql> insert into check_test (sex,age)values(1,10);
Query OK, 1 row affected (0.03 sec)

mysql> select * from check_test;
+----+-----+-----+
| id | sex | age |
+----+-----+-----+
|  1 | 1   |  10 |
|  2 | 0   |  10 |
+----+-----+-----+
2 rows in set (0.00 sec)

mysql> insert into check_test (sex,age)values(1,9);
Query OK, 1 row affected (0.01 sec)

mysql> select * from check_test;
+----+-----+-----+
| id | sex | age |
+----+-----+-----+
|  1 | 1   |  10 |
|  2 | 0   |  10 |
|  3 | 0   |   9 |
+----+-----+-----+
3 rows in set (0.00 sec)

mysql> insert into check_test (sex,age)values('1',9);
Query OK, 1 row affected (0.02 sec)

mysql> select * from check_test;
+----+-----+-----+
| id | sex | age |
+----+-----+-----+
|  1 | 1   |  10 |
|  2 | 0   |  10 |
|  3 | 0   |   9 |
|  4 | 1   |   9 |
+----+-----+-----+
4 rows in set (0.00 sec)

​ 唯一约束

​ 主键约束

​ 外键约束

查询结果带行号 （row_number() over (order by ……)）

select *, row_number() over (order by price desc ) from Book;

行转列问题(数据透视)

case when then else end group by

select 
	name ,
	max(case when '数学' then score else 0 end) as '数学' ,
	max(case when '语文' then score else 0 end) as '语文' 
	max(case when '英语' then score else 0 end) as '英语' 
from student 
group by name;

高级

存储过程（数据库内置的操作流程，降低网络通信开销）

​ 优点：速度快

​ 缺点：移植性差，编写难度大，维护性差

创建存储过程

# 创建一个名为p1的存储过程
create procedure p1()
begin
select * from emp;
end;

调用存储过程

# 调用p1存储过程
call p1();

查看存储过程

show create procedure p1;

删除存储过程

drop procedure if exists p1;

系统变量

用户变量（@@标识）

局部变量（只在村存储过程中用）

---

存储引擎：

show engines \G;

MyISAM：不支持事务

  三个文件表示一个表：

     格式文件：.frm

     数据文件：.MYD        

     索引文件：.MYI

  可被压缩，可转换成只读表，提高效率

---

InnoDB:(默认，最安全) 支持事务，外键，行锁

表结构存储在 .frm 文件中

数据存储在tablespace 表空间中（逻辑概念），无法被压缩，无法转换成只读

提供崩溃自动恢复机制

有较好的并发性和数据完整性，使用大多数应用场景，尤其是事务支持的应用

支持外键完整性，支持级联删除，级联更新

---

MyISAM ：

​ 支持全文索引，表锁

​ 不支持事务

​ 应用于读密集型应用

---

Memory:

​ 表存储在内存中

​ 读写性能高，但重启服务器数据会丢失

​ 适用于快速读写的临时数据集，缓存和临时表等场景

      | Null | Key | Default | Extra          |

±------±--------------±-----±----±--------±---------------+
| id | int | NO | PRI | NULL | auto_increment |
| sex | enum(‘0’,‘1’) | NO | | NULL | |
| age | int | NO | | NULL | |
±------±--------------±-----±----±--------±---------------+
3 rows in set (0.00 sec)

mysql> insert into check_test (sex,age)values(‘1’,10);
Query OK, 1 row affected (0.02 sec)

mysql> insert into check_test (sex,age)values(‘1’,-10);
ERROR 3819 (HY000): Check constraint ‘check_test_chk_1’ is violated.
mysql> insert into check_test (sex,age)values(1,10);
Query OK, 1 row affected (0.03 sec)

mysql> select * from check_test;
±—±----±----+
| id | sex | age |
±—±----±----+
| 1 | 1 | 10 |
| 2 | 0 | 10 |
±—±----±----+
2 rows in set (0.00 sec)

mysql> insert into check_test (sex,age)values(1,9);
Query OK, 1 row affected (0.01 sec)

mysql> select * from check_test;
±—±----±----+
| id | sex | age |
±—±----±----+
| 1 | 1 | 10 |
| 2 | 0 | 10 |
| 3 | 0 | 9 |
±—±----±----+
3 rows in set (0.00 sec)

mysql> insert into check_test (sex,age)values(‘1’,9);
Query OK, 1 row affected (0.02 sec)

mysql> select * from check_test;
±—±----±----+
| id | sex | age |
±—±----±----+
| 1 | 1 | 10 |
| 2 | 0 | 10 |
| 3 | 0 | 9 |
| 4 | 1 | 9 |
±—±----±----+
4 rows in set (0.00 sec)

​	唯一约束

​	主键约束

​	外键约束



查询结果带行号 （row_number() over (order by ……)）

```sql
select *, row_number() over (order by price desc ) from Book;

行转列问题(数据透视)

case when then else end group by

select 
	name ,
	max(case when '数学' then score else 0 end) as '数学' ,
	max(case when '语文' then score else 0 end) as '语文' 
	max(case when '英语' then score else 0 end) as '英语' 
from student 
group by name;

高级

存储过程（数据库内置的操作流程，降低网络通信开销）

​ 优点：速度快

​ 缺点：移植性差，编写难度大，维护性差

创建存储过程

# 创建一个名为p1的存储过程
create procedure p1()
begin
select * from emp;
end;

调用存储过程

# 调用p1存储过程
call p1();

查看存储过程

show create procedure p1;

删除存储过程

drop procedure if exists p1;

系统变量

用户变量（@@标识）

局部变量（只在村存储过程中用）

---

存储引擎：

show engines \G;

MyISAM：不支持事务

  三个文件表示一个表：

     格式文件：.frm

     数据文件：.MYD        

     索引文件：.MYI

  可被压缩，可转换成只读表，提高效率

---

InnoDB:(默认，最安全) 支持事务，外键，行锁

表结构存储在 .frm 文件中

数据存储在tablespace 表空间中（逻辑概念），无法被压缩，无法转换成只读

提供崩溃自动恢复机制

有较好的并发性和数据完整性，使用大多数应用场景，尤其是事务支持的应用

支持外键完整性，支持级联删除，级联更新

---

MyISAM ：

​ 支持全文索引，表锁

​ 不支持事务

​ 应用于读密集型应用

---

Memory:

​ 表存储在内存中

​ 读写性能高，但重启服务器数据会丢失

​ 适用于快速读写的临时数据集，缓存和临时表等场景