2.6 分组查询：group by

什么是分组查询？

在实际的应用中，可能有这样的需求，需要先进行分组，然后对每一组的数据进行操作。

如：

计算每个部门的工资和

计算每个工作岗位的平均薪资

找出每个工作岗位的最高薪资

这个时候我们需要使用分组查询，怎么进行分组查询呢？

关键字的执行顺序

select
  ...
from
  ...
where
  ...
group by
  ...
order by
  ...

结论：select语句中，如果有group by语句，select后面只能跟参加分组的字段，以及分组函数，其它的一律不能跟。

例：找出每个工作岗位的工资和

mysql> select job, sum(sal) from emp group by job order by sal;
+-----------+----------+
| job       | sum(sal) |
+-----------+----------+
| CLERK     |  4150.00 |
| SALESMAN  |  5600.00 |
| MANAGER   |  8275.00 |
| ANALYST   |  6000.00 |
| PRESIDENT |  5000.00 |
+-----------+----------+
5 rows in set (0.00 sec)
mysql> select job, sum(sal) from emp group by job order by sum(sal);
+-----------+----------+
| job       | sum(sal) |
+-----------+----------+
| CLERK     |  4150.00 |
| PRESIDENT |  5000.00 |
| SALESMAN  |  5600.00 |
| ANALYST   |  6000.00 |
| MANAGER   |  8275.00 |
+-----------+----------+
5 rows in set (0.00 sec)

例：找出每个部门的最高薪资

mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
mysql> select * from emp order by deptno;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.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 |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
mysql> select deptno,max(sal) from emp group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
|     20 |  3000.00 |
|     30 |  2850.00 |
+--------+----------+
3 rows in set (0.00 sec)

实例：找出“每个部门，不同工作岗位”的最高薪资

mysql> select deptno,job,max(sal) from emp group by deptno,job;
+--------+-----------+----------+
| deptno | job       | max(sal) |
+--------+-----------+----------+
|     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 |
+--------+-----------+----------+
9 rows in set (0.00 sec)

1. having

having可以对分完组之后的数据进一步过滤；having必须和group by联合使用。

having不能单独使用，having不能代替where

优化策略：where和having，优先选择where，where实在完成不了了，再选择having。

实例：找出每个部门最高薪资，要求显示最高薪资大于3000的

mysql> select deptno,max(sal) from emp group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
|     20 |  3000.00 |
|     30 |  2850.00 |
+--------+----------+
3 rows in set (0.00 sec)
mysql> select deptno,max(sal) from emp where sal >3000 group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
+--------+----------+
1 row in set (0.00 sec)
mysql> select deptno,max(sal) from emp group by deptno having max(sal)>3000;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
+--------+----------+
1 row in set (0.00 sec)

无法使用的where的情况：找出每个部门平均薪资，要求显示平均薪资高于2500的。

mysql> select deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)
mysql> select deptno,avg(sal) from emp group by deptno having avg(sal)>2500;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+
1 row in set (0.00 sec)

2. 总结

select 
  ...
from
  ...
where
  ...
group by
  ...
having
  ...
order by
  ...

执行顺序？

from

where

group by

having

select

order by

实例：找出每个岗位的平均薪资，要求显示平均薪资大于1500的，除MANAGER岗位之外，要求按照平均薪资降序排。

mysql>  select job,avg(sal) from emp where job <> 'MANAGER' group by job having avg(sal) >1500 order by avg(sal) desc;
mysql>  select job,avg(sal) from emp where job not in ('MANAGER') group by job having avg(sal) >1500 order by avg(sal) desc;
mysql> select job,avg(sal) from emp where job != 'MANAGER' group by job having avg(sal)>1500 order by avg(sal) desc;    //三句功能一样
+-----------+-------------+
| job       | avg(sal)    |
+-----------+-------------+
| PRESIDENT | 5000.000000 |
| ANALYST   | 3000.000000 |
+-----------+-------------+

3. 去重distinct

注意：原表数据不会被修改，只是查询结果去重

mysql> select job from emp;
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| SALESMAN  |
| MANAGER   |
| SALESMAN  |
| MANAGER   |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
| SALESMAN  |
| CLERK     |
| CLERK     |
| ANALYST   |
| CLERK     |
+-----------+
14 rows in set (0.00 sec)
mysql> select distinct job from emp;
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
+-----------+
5 rows in set (0.00 sec)

这样编写是错误的，语法错误：select ename, distinct job from emp;

注意：distinct只能出现在所有字段的最前方

例：distinct出现在job,deptno两个字段之前，表示两个字段联合起来去重

mysql> select job, deptno from emp;
+-----------+--------+
| job       | deptno |
+-----------+--------+
| CLERK     |     20 |
| SALESMAN  |     30 |
| SALESMAN  |     30 |
| MANAGER   |     20 |
| SALESMAN  |     30 |
| MANAGER   |     30 |
| MANAGER   |     10 |
| ANALYST   |     20 |
| PRESIDENT |     10 |
| SALESMAN  |     30 |
| CLERK     |     20 |
| CLERK     |     30 |
| ANALYST   |     20 |
| CLERK     |     10 |
+-----------+--------+
14 rows in set (0.00 sec)
mysql> select distinct job, deptno from emp;
+-----------+--------+
| job       | deptno |
+-----------+--------+
| CLERK     |     20 |
| SALESMAN  |     30 |
| MANAGER   |     20 |
| MANAGER   |     30 |
| MANAGER   |     10 |
| ANALYST   |     20 |
| PRESIDENT |     10 |
| CLERK     |     30 |
| CLERK     |     10 |
+-----------+--------+
9 rows in set (0.00 sec)

例：去除重复之后统计一下工作岗位的数量

mysql> select count(distinct job) from emp;
+---------------------+
| count(distinct job) |
+---------------------+
|                   5 |
+---------------------+
1 row in set (0.01 sec)

2.7 连接查询

1. 连接查询简介

（1）什么是连接查询

从一张表中单独查询，称为单表查询。

emp表和dept表联合起来查询数据，从emp表中取员工名字，从dept表中取部门名字；这种跨表查询，多张表联合起来查询数据，被称为连接查询。

（2）连接查询的分类

根据语法年代的分类：

SQL92：1992年的时候出现的语法

SQL99：1999年的时候出现的语法

我们这里重点学习SQL99.(这个过程中简单演示一个SQL92的例子)

根据表连接的方式分类：

内连接：等值连接、非等值连接、自连接

外连接：左外连接（左连接）、右外连接（右连接）、全连接（不讲）

（3）当两张表进行连接查询时，没有任何条件的限制会发生什么现象？

案例：查询每个员工所在部门名称？

mysql> select ename,deptno from emp;
+--------+--------+
| ename  | deptno |
+--------+--------+
| SMITH  |     20 |
| ALLEN  |     30 |
| WARD   |     30 |
| JONES  |     20 |
| MARTIN |     30 |
| BLAKE  |     30 |
| CLARK  |     10 |
| SCOTT  |     20 |
| KING   |     10 |
| TURNER |     30 |
| ADAMS  |     20 |
| JAMES  |     30 |
| FORD   |     20 |
| MILLER |     10 |
+--------+--------+
14 rows in set (0.00 sec)
mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)
mysql> select ename, dname from emp,dept;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | ACCOUNTING |
| SMITH  | RESEARCH   |
| SMITH  | SALES      |
| SMITH  | OPERATIONS |
| ALLEN  | ACCOUNTING |
| ALLEN  | RESEARCH   |
| ALLEN  | SALES      |
| ALLEN  | OPERATIONS |
| WARD   | ACCOUNTING |
| WARD   | RESEARCH   |
| WARD   | SALES      |
| WARD   | OPERATIONS |
| JONES  | ACCOUNTING |
| JONES  | RESEARCH   |
| JONES  | SALES      |
| JONES  | OPERATIONS |
| MARTIN | ACCOUNTING |
| MARTIN | RESEARCH   |
| MARTIN | SALES      |
| MARTIN | OPERATIONS |
| BLAKE  | ACCOUNTING |
| BLAKE  | RESEARCH   |
| BLAKE  | SALES      |
| BLAKE  | OPERATIONS |
| CLARK  | ACCOUNTING |
| CLARK  | RESEARCH   |
| CLARK  | SALES      |
| CLARK  | OPERATIONS |
| SCOTT  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| SCOTT  | SALES      |
| SCOTT  | OPERATIONS |
| KING   | ACCOUNTING |
| KING   | RESEARCH   |
| KING   | SALES      |
| KING   | OPERATIONS |
| TURNER | ACCOUNTING |
| TURNER | RESEARCH   |
| TURNER | SALES      |
| TURNER | OPERATIONS |
| ADAMS  | ACCOUNTING |
| ADAMS  | RESEARCH   |
| ADAMS  | SALES      |
| ADAMS  | OPERATIONS |
| JAMES  | ACCOUNTING |
| JAMES  | RESEARCH   |
| JAMES  | SALES      |
| JAMES  | OPERATIONS |
| FORD   | ACCOUNTING |
| FORD   | RESEARCH   |
| FORD   | SALES      |
| FORD   | OPERATIONS |
| MILLER | ACCOUNTING |
| MILLER | RESEARCH   |
| MILLER | SALES      |
| MILLER | OPERATIONS |
+--------+------------+
56 rows in set (0.00 sec)

最终查询结果条数，是两张表条数的乘积，这种现象被称为：笛卡尔积现象。

（4）怎么避免笛卡尔积现象

连接时加条件，满足这个条件的记录被筛选出来

select 
  ename, dname 
from 
  emp, dept
where
  emp.deptno = dept.deptno;

或者

select 
  emp.ename, dept.dname 
from 
  emp, dept
where
  emp.deptno = dept.deptno;

代码如下：

mysql> select emp.ename, dept.dname from emp, dept where emp.deptno = dept.deptno;
mysql> select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno;  //两个结果一样
+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| JONES  | RESEARCH   |
| SCOTT  | RESEARCH   |
| ADAMS  | RESEARCH   |
| FORD   | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| TURNER | SALES      |
| JAMES  | SALES      |
+--------+------------+
14 rows in set (0.01 sec)

思考：最终查询的结果条数是14条，但是匹配的过程中，匹配的次数减少了吗？

还是56次，只不过进行了四选一。次数没有减少。

注意：表的连接次数越多效率越低，所以尽量避免表的连接次数。

2. 内连接：inner

（1）内连接之等值连接

案例：查询每个员工所在部门名称，显示员工名和部门名

SQL92语法：

select 
  e.ename,d.dname
from
  emp e, dept d
where
  e.deptno = d.deptno;

sql92的缺点：结构不清晰，表的连接条件，和后期进一步筛选的条件，都放到了where后面。

SQL99语法：

inner可以省略（带着inner可读性更好）

mysql> select
    -> e.ename,d.dname
    -> from
    -> emp e
    -> inner join            //inner可以省略
    -> dept d
    -> on
    -> e.deptno = d.deptno;  // 条件是等量关系，所以被称为等值连接。
+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| JONES  | RESEARCH   |
| SCOTT  | RESEARCH   |
| ADAMS  | RESEARCH   |
| FORD   | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| TURNER | SALES      |
| JAMES  | SALES      |
+--------+------------+
14 rows in set (0.00 sec)

sql99优点：表连接的条件是独立的，连接之后，如果还需要进一步筛选，再往后继续添加where

  select 
    ...
  from
    a
  join
    b
  on
    a和b的连接条件
  where
    筛选条件

（2）非等值连接

案例：找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级

select 
  e.ename, e.sal, s.grade
from
  emp e
join
  salgrade s
on
  e.sal between s.losal and s.hisal; // 条件不是一个等量关系，称为非等值连接。

mysql> select e.ename, e.sal, s.grade from emp e join salgrade 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)

（3）内连接之自连接

实例：查询员工的上级领导，要求显示员工名和对应的领导名

mysql> select empno, ename, mgr from emp limit 5;
+-------+--------+------+
| empno | ename  | mgr  |
+-------+--------+------+
|  7369 | SMITH  | 7902 |
|  7499 | ALLEN  | 7698 |
|  7521 | WARD   | 7698 |
|  7566 | JONES  | 7839 |
|  7654 | MARTIN | 7698 |
+-------+--------+------+
5 rows in set (0.00 sec)

mysql> select a.ename as '员工名', b.ename as '领导名' from emp a join emp b on a.mgr = b.empno;
+--------+--------+
| 员工名      | 领导名     |
+--------+--------+
| SMITH  | FORD   |
| ALLEN  | BLAKE  |
| WARD   | BLAKE  |
| JONES  | KING   |
| MARTIN | BLAKE  |
| BLAKE  | KING   |
| CLARK  | KING   |
| SCOTT  | JONES  |
| TURNER | BLAKE  |
| ADAMS  | SCOTT  |
| JAMES  | BLAKE  |
| FORD   | JONES  |
| MILLER | CLARK  |
+--------+--------+
13 rows in set (0.00 sec)

以上就是内连接中的：自连接，技巧：一张表看做两张表。

13条记录没有KING，因为KING没有领导

3. 外连接

内连接的特点：完成能够匹配上这个条件的数据查询出来。

mysql> select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| JONES  | RESEARCH   |
| SCOTT  | RESEARCH   |
| ADAMS  | RESEARCH   |
| FORD   | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| TURNER | SALES      |
| JAMES  | SALES      |
+--------+------------+
14 rows in set (0.00 sec)
mysql> select e.ename,d.dname from emp e right join dept d on e.deptno = d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| JONES  | RESEARCH   |
| SCOTT  | RESEARCH   |
| ADAMS  | RESEARCH   |
| FORD   | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| TURNER | SALES      |
| JAMES  | SALES      |
| NULL   | OPERATIONS |
+--------+------------+
15 rows in set (0.00 sec)

right代表什么：表示将join关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表。在外连接当中，两张表连接，产生了主次关系。

（1）（左外连接）

outer是可以省略的，带着可读性强。不管左连接还是右连接的outer都可以省略

select 
  e.ename,d.dname
from
  dept d 
left outer join   //outer可以省略
  emp e
on
  e.deptno = d.deptno;

注意：

带有right的是右外连接，又叫做右连接。

带有left的是左外连接，又叫做左连接。

任何一个右连接都有左连接的写法。

任何一个左连接都有右连接的写法。

思考：外连接的查询结果条数一定是 >= 内连接的查询结果条数的嘛？正确

实例：查询每个员工的上级领导，要求显示所有员工的名字和领导名

mysql> select a.ename, b.ename from emp a left join emp b on a.mgr=b.empno;
+--------+-------+
| ename  | ename |
+--------+-------+
| 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 |
+--------+-------+
14 rows in set (0.00 sec)

4. 多张表连接

select 
  ...
from
  a
join
  b
on
  a和b的连接条件
join
  c
on
  a和c的连接条件
right join
  d
on
  a和d的连接条件

一条SQL中内连接和外连接可以混合。都可以出现！

案例：找出每个员工的部门名称以及工资等级，要求显示员工名、部门名、薪资、薪资等级

mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  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 |
+-------+--------+-----------+------+------------+---------+---------+--------+

mysql> select * from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

mysql> select e.ename, e.sal, 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  | sal     | dname      | grade |
+--------+---------+------------+-------+
| SMITH  |  800.00 | RESEARCH   |     1 |
| ALLEN  | 1600.00 | SALES      |     3 |
| WARD   | 1250.00 | SALES      |     2 |
| JONES  | 2975.00 | RESEARCH   |     4 |
| MARTIN | 1250.00 | SALES      |     2 |
| BLAKE  | 2850.00 | SALES      |     4 |
| CLARK  | 2450.00 | ACCOUNTING |     4 |
| SCOTT  | 3000.00 | RESEARCH   |     4 |
| KING   | 5000.00 | ACCOUNTING |     5 |
| TURNER | 1500.00 | SALES      |     3 |
| ADAMS  | 1100.00 | RESEARCH   |     1 |
| JAMES  |  950.00 | SALES      |     1 |
| FORD   | 3000.00 | RESEARCH   |     4 |
| MILLER | 1300.00 | ACCOUNTING |     2 |
+--------+---------+------------+-------+
14 rows in set (0.00 sec)

5. 子查询

select语句中嵌套select语句，被嵌套的select语句称为子查询。

子查询都可以出现在哪里呢？

  select
    ..(select).
  from
    ..(select).
  where
    ..(select).

（1）where子句中的子查询

案例：找出比最低工资高的员工姓名和工资

mysql> select ename,sal from emp where sal>min(sal);
ERROR 1111 (HY000): Invalid use of group function

错误原因：where子句中不能直接使用分组函数。

实现思路：

第一步：查询最低工资是多少

mysql> select ename, min(sal) from emp;
+-------+----------+
| ename | min(sal) |
+-------+----------+
| SMITH |   800.00 |
+-------+----------+
1 row in set (0.00 sec)

第二步：找出大于800的

mysql> select sal from emp where sal > 800;
+---------+
| sal     |
+---------+
| 1600.00 |
| 1250.00 |
| 2975.00 |
| 1250.00 |
| 2850.00 |
| 2450.00 |
| 3000.00 |
| 5000.00 |
| 1500.00 |
| 1100.00 |
|  950.00 |
| 3000.00 |
| 1300.00 |
+---------+

第三步合并：

mysql> select ename,sal from emp where sal>(select min(sal) from emp);
+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
13 rows in set (0.00 sec)

（2）from子句中的子查询

注意：from后面的子查询，可以将子查询的查询结果当做一张临时表。（技巧）

案例：找出每个岗位的平均工资的薪资等级。

mysql> select * from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)

第一步：找出每个工作岗位的平均工资

mysql> select avg(sal) from emp group by job;
+-------------+
| avg(sal)    |
+-------------+
| 3000.000000 |
| 1037.500000 |
| 2758.333333 |
| 5000.000000 |
| 1400.000000 |
+-------------+
5 rows in set (0.00 sec)

第二步：克服心理障碍，把以上的查询结果就当做一张真实存在的表t。

mysql> select t.*, s.grade from t join salgrade s on t.avg(sal) between s.losal and s.hisal;
ERROR 1146 (42S02): Table 'bjpowernode.t' doesn't exist

mysql> select t.*, s.grade
    -> from (select job,avg(sal) as avgsal from emp group by job) t
    -> join salgrade s
    -> on t.avgsal between s.losal and s.hisal;
+-----------+-------------+-------+
| job       | avgsal      | grade |
+-----------+-------------+-------+
| CLERK     | 1037.500000 |     1 |
| SALESMAN  | 1400.000000 |     2 |
| ANALYST   | 3000.000000 |     4 |
| MANAGER   | 2758.333333 |     4 |
| PRESIDENT | 5000.000000 |     5 |
+-----------+-------------+-------+
5 rows in set (0.00 sec)

（3）select后面出现的子查询（这个内容不需要掌握，了解即可！！！）

实例：找出每个员工的部门名称，要求显示员工名，部门名

mysql> select e.ename, (select d.dname from dept d where e.deptno=d.deptno) as dname from emp e;
+--------+------------+
| 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)

6. 合并查询：union

案例：查询工作岗位是MANAGER和SALESMAN的员工

mysql> select ename, job from emp where job='MANAGER' or job='SALESMAN';
mysql> select ename,job from emp where job in ('MANAGER', 'SALESMAN');
mysql> select ename, job from emp where job='MANAGER'
    -> union
    -> select ename, job from emp where job='SALESMAN';   //三条语句结果一样
+--------+----------+
| ename  | job      |
+--------+----------+
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| JONES  | MANAGER  |
| MARTIN | SALESMAN |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.01 sec)

union的效率要高一些。对于表连接来说，每连接一次新表，则匹配的次数满足笛卡尔积，成倍的翻…

但是union可以减少匹配的次数。在减少匹配次数的情况下，还可以完成两个结果集的拼接。

比如：

a 连接 b 连接 c

a 10条记录

b 10条记录

c 10条记录

匹配次数是：1000

a 连接 b一个结果：10 * 10 --> 100次

a 连接 c一个结果：10 * 10 --> 100次

使用union的话是：100次 + 100次 = 200次。（union把乘法变成了加法运算）

注意事项：

错误：union在进行结果集合并的时候，要求两个结果集的列数相同。

select ename,job from emp where job = 'MANAGER'
union
select ename from emp where job = 'SALESMAN';
ERROR 1222 (21000): The used SELECT statements have a different number of columns

MYSQL可以，oracle语法严格，不可以，报错。要求：结果集合并时列和列的数据类型也要一致。

mysql> select ename,job from emp where job = 'MANAGER'
    -> union
    -> select ename,sal from emp where job = 'SALESMAN';
+--------+---------+
| ename  | job     |
+--------+---------+
| JONES  | MANAGER |
| BLAKE  | MANAGER |
| CLARK  | MANAGER |
| ALLEN  | 1600    |
| WARD   | 1250    |
| MARTIN | 1250    |
| TURNER | 1500    |
+--------+---------+

7. 分页函数：limit

作用：显示部分查询结果，通常使用在分页查询当中。

完整用法：limit startIndex, length

实例：按照薪资降序，取出排名在前5名的员工

mysql> select ename,sal from emp order by sal desc limit 5;
mysql> select ename,sal from emp order by sal desc limit 0,5;   //第一个参数为起始索引，默认从0开始  第二个参数为长度
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
5 rows in set (0.01 sec)

注意：mysql当中limit在order by之后执行！

实例：取出工资排名在[3-5]名的员工

mysql> select ename,sal from emp order by sal desc limit 2,3;  //下标2开始，长度为3
+-------+---------+
| ename | sal     |
+-------+---------+
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
3 rows in set (0.00 sec)

分页

每页显示3条记录

第1页：limit 0,3

第2页：limit 3,3

第3页：limit 6,3

第4页：limit 9,3

第pageNo页：limit (pageNo - 1) * pageSize , pageSize

2.8 DQL语句的总结

select 
  ...
from
  ...
where
  ...
group by
  ...
having
  ...
order by
  ...
limit
  ...

第3章 DDL语言：数据定义语言

DDL：数据定义语言：create（增）、drop（删）、alter（改）、truncate。主要是对表结构进行操作。

3.1 MySQL的数据类型

类型	作用
varchar	可变长的字符串类型，根据实际长度东忒分配空间。优点：节省空间。缺点：需要动态分配空间，速度慢。最长255
char	定长字符串类型，不管实际的数据长度是多少，分配固定长度的空间去存储数据。优缺点与上面相反。最长255

int	整型。等同于java的int。最长11
bigint	长整型。等同于java中的long。
float	单精度浮点型
double	双精度浮点型

date	短日期类型
datetime	长日期类型

clob	Character Large OBject:CLOB。字符大对象，最多存储4G的字符串。超过255个字符的采用CLOB存储。比如：存储一篇文章，一篇说明。

blob	Binary Large OBject；二进制大对象，用来存储图片、声音、视频等流媒体数据。 BLOB类型字段上插入数据时，需要使用IO流。

varchar和char我们应该怎么选择？

性别字段选：性别是固定长度字符串，选择char。

姓名字段选：每个人名字长度不同，选择varchar。

例如：t_movie 电影表，存储电影信息

编号         名字     故事情节        上映日期         时长       海报        类型
no(bigint)  name(varchar) history(clob)   playtime(date)    time(double)  image(blob)     type(char)
------------------------------------------------------------------------------------------------------------------
10000      哪吒       ..........        2019-10-11       2.5         ....        '1'
10001      悟空传         ..........          2019-11-11       1.5        ....         '2'
....

3.2 表的创建：create

建表的语法格式：create table 表名(字段名1 数据类型, 字段名2 数据类型, 字段名3 数据类型);

create table 表名(
  字段名1 数据类型, 
  字段名2 数据类型, 
  字段名3 数据类型
);

表名：建议以t_ 或者 tbl_开始，可读性强。见名知意。

字段名：见名知意。

表名和字段名都属于标识符。

3.3 表的删除：drop

drop table t_student;             //注意：当这张表不存在的时候会报错！
drop table if exists t_student;   //两句都可以，推荐这句

实例：创建一个学生表，学号、姓名、年龄、性别、邮箱地址

mysql> create table t_student(
    -> no int,
    -> name varchar(32),
    -> sex char(1),
    -> age int(3),
    -> email varchar(255)
    -> );
Query OK, 0 rows affected (0.01 sec)

3.4 快速创建表

了解内容，将查询结果当做一张表新建，实现表集数据的快速复制

mysql> create table emp2 as select * from emp;  //两张表的数据一模一样
Query OK, 14 rows affected (0.01 sec)
Records: 14  Duplicates: 0  Warnings: 0

mysql> create table mytable as select empno,ename from emp where job='MANAGER';
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from mytable;
+-------+-------+
| empno | ename |
+-------+-------+
|  7566 | JONES |
|  7698 | BLAKE |
|  7782 | CLARK |
+-------+-------+
3 rows in set (0.00 sec)

3.5 快速删除表中的数据：truncate

delete from dept_bak;      //这种删除数据的方式比较慢。

delete原理：表中数据被删除了，但是数据在硬盘上的真实存储空间不会被释放！

缺点：删除效率比较低；优点：支持回滚（即可恢复）

回滚实例

mysql> select * from dept_bak;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from dept_bak;
Query OK, 4 rows affected (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from dept_bak;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

truncate原理：表被一次截断，物理删除。

缺点：不支持回滚。优点：效率高，快速。

语法：truncate table dept_bak;

mysql> truncate table dept_bak;          //删数据，不是删表
Query OK, 0 rows affected (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from dept_bak;          //回滚也没用
Empty set (0.00 sec)

truncate删除速度很快，但是数据不可恢复。

注意：truncate是删除表中的数据，表还在。删除表用drop

3.6 修改表结构：alter

意思：添加一个字段，删除一个字段，修改一个字段！！！

第一：实际开发中，需求一旦确定，表一旦设计完成，很少对表结构修改。因为开发进行中修改结果成本较高，对应的java代码需要进行大量修改。这个责任应该由设计人员来承担！

第二：由于修改表结构操作很少，我们不需要掌握，如果真要修改，可以使用工具！

修改表结构操作不需要写到java程序中，也不是java程序员的范畴。

第4章 DML语言：数据操作语言

DML：数据操作语言：insert（增）、update（删）、delete（改）。主要是操作表中数据的操作

4.1 DML增：insert

语法格式：insert into 表名(字段名1,字段名2,字段名3…) values(值1,值2,值3);

注意：字段名和值要一一对应，即数量要对应。数据类型要对应。

mysql> insert into t_student(no,name,sex,age,email) values(1,'zhangsan','m',20,'zhangsan@123.com');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t_student;
+------+----------+------+------+------------------+
| no   | name     | sex  | age  | email            |
+------+----------+------+------+------------------+
|    1 | zhangsan | m    |   20 | zhangsan@123.com |
+------+----------+------+------+------------------+
1 row in set (0.00 sec)

mysql> insert into t_student(email,name,sex,age,no) values('lisi@123.com','lisi','m',22,2);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t_student;
+------+----------+------+------+------------------+
| no   | name     | sex  | age  | email            |
+------+----------+------+------+------------------+
|    1 | zhangsan | m    |   20 | zhangsan@123.com |
|    2 | lisi     | m    |   22 | lisi@123.com     |
+------+----------+------+------+------------------+
2 rows in set (0.00 sec)

mysql> insert into t_student(no) values(3);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_student;
+------+----------+------+------+------------------+
| no   | name     | sex  | age  | email            |
+------+----------+------+------+------------------+
|    1 | zhangsan | m    |   20 | zhangsan@123.com |
|    2 | lisi     | m    |   22 | lisi@123.com     |
|    3 | NULL     | NULL | NULL | NULL             |
+------+----------+------+------+------------------+
3 rows in set (0.00 sec)

mysql> insert into t_student(name) values('wangwu');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t_student;
+------+----------+------+------+------------------+
| no   | name     | sex  | age  | email            |
+------+----------+------+------+------------------+
|    1 | zhangsan | m    |   20 | zhangsan@123.com |
|    2 | lisi     | m    |   22 | lisi@123.com     |
|    3 | NULL     | NULL | NULL | NULL             |
| NULL | wangwu   | NULL | NULL | NULL             |
+------+----------+------+------+------------------+
4 rows in set (0.00 sec)

注意：insert语句执行成功，必然多一条记录。若没有给定其它字段值，默认为NULL。

（1）设置默认值：defalut

drop table if exists t_student;
create table t_student(
    no int,
    name varchar(32),
    sex char(1) default 'm',   //default设置默认值   默认性别：'m'
    age int(3),
    email varchar(255)
    );

查看表的数据类型

mysql> desc t_student;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| no    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(32)  | YES  |     | NULL    |       |
| sex   | char(1)      | YES  |     | m       |       |
| age   | int(3)       | YES  |     | NULL    |       |
| email | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> insert t_student(no) values(1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_student;
+------+------+------+------+-------+
| no   | name | sex  | age  | email |
+------+------+------+------+-------+
|    1 | NULL | m    | NULL | NULL  |
+------+------+------+------+-------+
1 row in set (0.00 sec)
mysql> insert into t_student values(2);
ERROR 1136 (21S01): Column count doesn't match value count at row 1

注意：前面字段名省略的话，等于都写上了！所以值也要都写上！

mysql> insert into t_student values(2);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into t_student values(2, 'lisi', 'f', 20, 'lisi@123.com');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t_student;
+------+------+------+------+--------------+
| no   | name | sex  | age  | email        |
+------+------+------+------+--------------+
|    1 | NULL | m    | NULL | NULL         |
|    2 | lisi | f    |   20 | lisi@123.com |
+------+------+------+------+--------------+
2 rows in set (0.00 sec)

（2）数字格式化：format()函数

格式：format(数字, ‘格式’)

mysql> select ename,sal from emp limit 5;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
+--------+---------+
5 rows in set (0.00 sec)
mysql> select ename, format(sal,'$999,999') as sal from emp limit 5;
+--------+-------+
| ename  | sal   |
+--------+-------+
| SMITH  | 800   |
| ALLEN  | 1,600 |
| WARD   | 1,250 |
| JONES  | 2,975 |
| MARTIN | 1,250 |
+--------+-------+
5 rows in set, 5 warnings (0.00 sec)

（3）插入日期：str_to_date()

str_to_date：将varchar类型转换成date类型

date_format：将date类型转换成varchar类型

注意：标识符全部小写，单词之间使用下划线。

drop table if exists t_user;                  //删除表
create table t_user(                          //重新创建表
    id int,
    name varchar(32),
    birth date
    );

mysql> desc t_user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
| birth | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> insert into  t_user(id,name,birth) values(1,'zs','01-10-1990');  //插入数据
ERROR 1292 (22007): Incorrect date value: '01-10-1990' for column 'birth' at row 1

出问题了：原因是类型不匹配。数据库birth是date类型，这里给了一个字符串varchar。

可以使用str_to_date函数进行类型转换，将字符串转换成日期类型date

语法格式：str_to_date('字符串日期', '日期格式')

mysql的日期格式

  %Y  年
  %m  月
  %d  日
  %h  时
  %i  分
  %s  秒

mysql> insert into  t_user(id,name,birth) values(1,'zs',str_to_date('01-10-1990','%d-%m-%Y'));
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_user;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
|    1 | zs   | 1990-10-01 |
+------+------+------------+
1 row in set (0.00 sec)

str_to_date函数把字符串varchar转换成日期date类型，通常在插入insert时使用

注意：如果你提供的日期字符串是这个格式：%Y-%m-%d，可以直接插入，str_to_date函数就不需要了

mysql> insert into t_user(id,name,birth) values(2, 'lisi', '1990-10-01');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t_user;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
|    1 | zs   | 1990-10-01 |
|    2 | lisi | 1990-10-01 |
+------+------+------------+
2 rows in set (0.00 sec)

查询的时候可以以某个特定的日期格式展示。date_format函数将日期类型转换成特定格式的字符串。

语法： date_format(日期类型数据, ‘日期格式’)

mysql>  select id,name,date_format(birth,'%m/%d/%y') as birth from t_user;
+------+------+----------+
| id   | name | birth    |
+------+------+----------+
|    1 | zs   | 10/01/90 |
|    2 | lisi | 10/01/90 |
+------+------+----------+
2 rows in set (0.00 sec)

mysql> select id,name,birth from t_user;
    +------+----------+------------+
    | id   | name     | birth      |
    +------+----------+------------+
    |    1 | zhangsan | 1990-10-01 |
    |    2 | lisi     | 1990-10-01 |
    +------+----------+------------+

以上语句进行了默认格式转换，自动将数据库中的date类型转换成varchar类型。默认的日期格式：’%Y-%m-%d’

（4）date和datetime类型区别

date是短日期：只包括年月日信息。

datetime是长日期：包括年月日时分秒信息。

mysql短日期默认格式：%Y-%m-%d

mysql长日期默认格式：%Y-%m-%d %h:%i:%s

mysql> create table t_user(     //创建表
    -> id int,
    -> name varchar(32),
    -> birth date,
    -> create_time datetime    //长日期类型
    -> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t_user(id,name,birth,create_time) values(1,'zhangsan','1990-10-01','2021-09-14 15:49:50');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_user values(1,'zhangsan','1990-10-01','2021-09-14 15:49:50');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t_user;
+------+----------+------------+---------------------+
| id   | name     | birth      | create_time         |
+------+----------+------------+---------------------+
|    1 | zhangsan | 1990-10-01 | 2021-09-14 15:49:50 |
|    1 | zhangsan | 1990-10-01 | 2021-09-14 15:49:50 |
+------+----------+------------+---------------------+
2 rows in set (0.00 sec)

（5）获取系统当前时间：new()

now() 函数获取的时间带有时分秒信息！是datetime类型。

mysql> insert into t_user values(3,'lisi','1990-10-01',now());
Query OK, 1 row affected (0.01 sec)
mysql> select * from t_user;
+------+----------+------------+---------------------+
| id   | name     | birth      | create_time         |
+------+----------+------------+---------------------+
|    1 | zhangsan | 1990-10-01 | 2021-09-14 15:49:50 |
|    1 | zhangsan | 1990-10-01 | 2021-09-14 15:49:50 |
|    3 | lisi     | 1990-10-01 | 2021-11-25 15:50:02 |
+------+----------+------------+---------------------+
3 rows in set (0.00 sec)

（6）一次插入多条数据

语法：insert into t_user(字段名1,字段名2) values(), (), (), ();

mysql> insert into t_user values
    -> (1, 'zs', '1980-10-11', now()),
    -> (2, 'ls', '1981-10-11', now()),
    -> (3, 'ww', '1982-10-11', now());
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from t_user;
+------+------+------------+---------------------+
| id   | name | birth      | create_time         |
+------+------+------------+---------------------+
|    1 | zs   | 1980-10-11 | 2021-11-29 20:55:54 |
|    2 | ls   | 1981-10-11 | 2021-11-29 20:55:54 |
|    3 | ww   | 1982-10-11 | 2021-11-29 20:55:54 |
+------+------+------------+---------------------+
3 rows in set (0.00 sec)

（7）select查询结果插入表

用insert将查询到的结果插入表中，很少用到（了解内容）

drop table if exists dept_bak;                 //删除表
create table dept_bak as select * from dept;   //创建表

mysql> select * from dept_bak;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)
mysql> insert into dept_bak select * from dept;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> select * from dept_bak;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
8 rows in set (0.00 sec)

4.2 DML改：update

语法格式：update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3… where 条件;

注意：没有条件限制会导致所有数据全部更新。

mysql> update t_user set name = 'jack',birth = '2000-10-11' where id = 3;  //修改id=3的数据
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from t_user;
+------+----------+------------+---------------------+
| id   | name     | birth      | create_time         |
+------+----------+------------+---------------------+
|    1 | zhangsan | 1990-10-01 | 2021-09-14 15:49:50 |
|    1 | zhangsan | 1990-10-01 | 2021-09-14 15:49:50 |
|    3 | jack     | 2000-10-11 | 2021-11-25 15:50:02 |
+------+----------+------------+---------------------+
mysql> update t_user set create_time = now() where id =1;   //修改id=1的数据
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0
mysql> select * from t_user;
+------+----------+------------+---------------------+
| id   | name     | birth      | create_time         |
+------+----------+------------+---------------------+
|    1 | zhangsan | 1990-10-01 | 2021-11-25 15:54:20 |
|    1 | zhangsan | 1990-10-01 | 2021-11-25 15:54:20 |
|    3 | jack     | 2000-10-11 | 2021-11-25 15:50:02 |
+------+----------+------------+---------------------+
3 rows in set (0.00 sec)

4.3 DML删：delete

语法格式： delete from 表名 where 条件;

注意：没有条件，整张表的数据会全部删除！

mysql> delete from t_user where id = 3;   //删除id=3的数据
Query OK, 1 row affected (0.01 sec)
mysql> select * from t_user;
+------+----------+------------+---------------------+
| id   | name     | birth      | create_time         |
+------+----------+------------+---------------------+
|    1 | zhangsan | 1990-10-01 | 2021-11-25 15:54:20 |
|    1 | zhangsan | 1990-10-01 | 2021-11-25 15:54:20 |
+------+----------+------------+---------------------+
2 rows in set (0.00 sec)
mysql> insert into t_user(id) values(2);   //插
Query OK, 1 row affected (0.01 sec)
mysql> select * from t_user;
+------+----------+------------+---------------------+
| id   | name     | birth      | create_time         |
+------+----------+------------+---------------------+
|    1 | zhangsan | 1990-10-01 | 2021-11-25 15:54:20 |
|    1 | zhangsan | 1990-10-01 | 2021-11-25 15:54:20 |
|    2 | NULL     | NULL       | NULL                |
+------+----------+------------+---------------------+
3 rows in set (0.00 sec)
mysql> delete from t_user;             //删全表
Query OK, 3 rows affected (0.01 sec)
mysql> select * from t_user;
Empty set (0.00 sec)

【B站老杜】mysql详解(中)