老杜Mysql 基础教程笔记

JHC000000

于 2024-07-09 10:17:14 发布

阅读量924

点赞数 25

文章标签： mysql 笔记 adb

本文链接：https://blog.csdn.net/CXY00000/article/details/140288104

版权

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)
);

存储引擎：

show engines \G;

MyISAM：不支持事务

  三个文件表示一个表：

     格式文件：.frm

     数据文件：.MYD        

     索引文件：.MYI

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

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

表结构存储在 .frm 文件中

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

提供崩溃自动恢复机制

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

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 开启事务

索引：（目录）

原理：(缩小扫描范围)

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





  不能随意添加，维护需要成本

  数据经常被修改，数据量较少 不适合用索引

  数据量大，字段很少DML操作 ，经常出现在where子句后边 适合用索引

全表扫描：

索引检索：（效率较高）

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

查看执行计划 [看 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

索引分类：

单一索引

复合索引

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

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

索引失效：

模糊查询时第一个通配符使用%

视图只能以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 命令

导出数据库中的数据

Dos 窗口执行，不需要登录数据库管理系统
```
mysqldump demo > E:\Desktop\demo.sql -u root -p123456
```

导入数据

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

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

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