老杜 mySql自学笔记day2_mysqlunion的优点-优快云博客

本文链接：https://blog.youkuaiyun.com/m0_67946241/article/details/126858979

本文详细介绍了MySQL中的链接查询，包括内连接、外连接、自连接以及如何避免笛卡尔积现象。同时讲解了子查询的应用，如在where、from和select子句中的使用。此外，还探讨了union操作的优势和limit在分页查询中的重要作用。内容涵盖表结构创建、数据插入、更新及删除，以及约束和外键的概念，适合MySQL初学者参考学习。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一.链接查询

1.什么是链接查询

2.没有限制条件的链接查询，会产生笛卡尔积现象

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

三.union合并查询结果集

2.Union的优点

3.Union的使用限制

四.limit（非常重要，以后分页查询全靠它了）

1.limit作用：将查询结果集的一部分取出来。通常使用在分页查询当中

4.date和datetime两个类型的区别

5.将查询结果插入到一张表当中，insert相关的！！！【了解内容】

八.修改update

九.删除

1.删除delect

2.快速删除表中的数据？【truncate比较重要，必须掌握】

4.主键约束（primary key，简称PK）

十.外键约束（非常重要）

1.相关简介

2.实际应用

准备的材料表格：

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 dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

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

小知识点：

关于查询结果集进行去重

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

mysql> select distinct job from emp; // distinct关键字去除重复记录

语法

distinct只能出现在所有字段的最前方。

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

mysql> select distinct job,deptno from emp;

联合去重，当两个字段的属性都相同的时候才会去重

统计一下工作岗位的数量

select count(distinct job) from emp;
+---------------------+
| count(distinct job) |
+---------------------+
|                   5 |
+---------------------+

一.链接查询

1.什么是链接查询

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

链接查询的分类

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

外连接：
左外连接（左连接）
右外连接（右连接）

全连接（不讲，很少用到）

2.没有限制条件的链接查询，会产生笛卡尔积现象

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

emp表

mysql> select ename,deptno from emp;

dept表

mysql> select * from dept;

链接查询（没有任何的限制条件）

select ename,dname from emp, dept;

结果

+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | ACCOUNTING |
| SMITH  | RESEARCH   |
| SMITH  | SALES      |
| SMITH  | OPERATIONS |
| ALLEN  | ACCOUNTING |
| ALLEN  | RESEARCH   |
| ALLEN  | SALES      |
| ALLEN  | OPERATIONS |
...
56 rows in set (0.00 sec)

14 * 4 = 56

当两张表进行连接查询，没有任何条件限制的时候，最终查询结果条数，是
两张表条数的乘积，这种现象被称为：笛卡尔积现象。（笛卡尔发现的，这是
一个数学现象。）

3.如何避免笛卡尔积的现象

进行条件限制（两个表当中的部门编号相等的情况下输出）

对表起别名，可以增加效率，也可以增加可读性

select 
	e.ename,d.dname 
from 
	emp e, dept d
where
	e.deptno = d.deptno; //SQL92语法。

+--------+------------+
| 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条，但是匹配的过程中，匹配的次数减少了吗？
还是56次，只不过进行了四选一。次数没有减少。

注意：通过笛卡尔积现象得出，表的连接次数越多效率越低，尽量避免表的
连接次数。

4.内连接等值链接

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

emp e和dept d表进行连接。条件是：e.deptno = d.deptno

SQL92语法（太老，不用了）：

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

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

语句解读：首先涉及到两个表from emp e inner join dept d 同时对表起了别名，使用别名搜索职员表当中的职员名 select e.name,d.name，当职员表和部门表的部门编号相同的时候on e.deptno = d.depton，两个表进行链接。

 select
     e.ename,d.dname
     from
     emp e
     inner join
    dept d
     on
     e.deptno = d.deptno; // 条件是等量关系，所以被称为等值连接。
//inner可以省略（带着inner可读性更好！！！一眼就能看出来是内连接）
+--------+------------+
| 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)

SQL99语法（常用的）：
select
…
from
表a
join
表b
on
a和b的连接条件
where
筛选条件

省略on时候的结果

 select
 e.ename,d.dname
 from
 emp e
 inner join
dept d;
        +--------+------------+
        | ename  | dname      |
        +--------+------------+
        | SMITH  | OPERATIONS |
        | SMITH  | SALES      |
        | SMITH  | RESEARCH   |
        | SMITH  | ACCOUNTING |
        | ALLEN  | OPERATIONS |
        | ALLEN  | SALES      |
        | ALLEN  | RESEARCH   |
        | ALLEN  | ACCOUNTING |
        | WARD   | OPERATIONS |
        | WARD   | SALES      |
        | WARD   | RESEARCH   |
        | WARD   | ACCOUNTING |
        | JONES  | OPERATIONS |
        | JONES  | SALES      |
        | JONES  | RESEARCH   |
        | JONES  | ACCOUNTING |
        | MARTIN | OPERATIONS |
        | MARTIN | SALES      |
        | MARTIN | RESEARCH   |
        | MARTIN | ACCOUNTING |
        | ADAMS  | RESEARCH   |
        | ADAMS  | ACCOUNTING |
        | JAMES  | OPERATIONS |
        | JAMES  | SALES      |
        | JAMES  | RESEARCH   |
        | JAMES  | ACCOUNTING |
        | FORD   | OPERATIONS |
        | FORD   | SALES      |
        | FORD   | RESEARCH   |
        | FORD   | ACCOUNTING |
        | MILLER | OPERATIONS |
        | MILLER | SALES      |
        | MILLER | RESEARCH   |
        | MILLER | ACCOUNTING |
        +--------+------------+
        56 rows in set (0.00 sec)

和没有where是一样的，直接以笛卡尔积的形式进行表述。

5.内连接之非等值链接

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

解题思路：1.现将我们需要的信息筛选出来

emp表当中的信息（提供名字和工资）

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 |

salgarde表中的信息（提供薪资和薪资等级）

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

写出结果

select 
e.ename, e.sal, s.grade
from
emp e
inner 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 |
+--------+---------+-------+

注意：回顾关键字

on e.sal between s.losal and s.hisal;在这两个值之间的e.sal

6.内连接之自连接（自己链接自己）

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

emp表当中的员工编号和领导标号是一致的，因为领导也是员工

所有员工的员工编号和领导编号

mysql> select empno,ename,mgr from emp;
+-------+--------+------+
| empno | ename  | mgr  |
+-------+--------+------+
|  7369 | SMITH  | 7902 |
|  7499 | ALLEN  | 7698 |
|  7521 | WARD   | 7698 |
|  7566 | JONES  | 7839 |
|  7654 | MARTIN | 7698 |
|  7698 | BLAKE  | 7839 |
|  7782 | CLARK  | 7839 |
|  7788 | SCOTT  | 7566 |
|  7839 | KING   | NULL |
|  7844 | TURNER | 7698 |
|  7876 | ADAMS  | 7788 |
|  7900 | JAMES  | 7698 |
|  7902 | FORD   | 7566 |
|  7934 | MILLER | 7782 |
+-------+--------+------+

当员工编号等于领导编号的时候，进行合并输出

 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)

提示：将一个表看成两个表，类似于一个类的两个对象

运功当中没有king，king没有领导

7.外链接

外连接与内连接的区别

什么是外连接，和内连接有什么区别？

内连接：假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。 AB两张表没有主副之分，两张表是平等的。

外连接：假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。

外连接的分类：

左外连接（左连接）：表示左边的这张表是主表。

右外连接（右连接）：表示右边的这张表是主表。

emp表格

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 |
+-------+--------+-----------+------+------------+---------+---------+--------+

dept表格

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

内连接：（A和B连接，AB两张表没有主次关系。平等的。）

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      |
+--------+------------+

外连接（右外连接）：

select 
e.ename,d.dname
from
emp e 
right outer join //outer是可以省略的
dept d
on
e.deptno = d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| MILLER | ACCOUNTING |
| KING   | ACCOUNTING |
| CLARK  | ACCOUNTING |
| FORD   | RESEARCH   |
| ADAMS  | RESEARCH   |
| SCOTT  | RESEARCH   |
| JONES  | RESEARCH   |
| SMITH  | RESEARCH   |
| JAMES  | SALES      |
| TURNER | SALES      |
| BLAKE  | SALES      |
| MARTIN | SALES      |
| WARD   | SALES      |
| ALLEN  | SALES      |
| NULL   | OPERATIONS |
+--------+------------+

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

外连接（左外连接）：

select 
e.ename,d.dname
from
dept d 
left outer join 
emp e
on
e.deptno = d.deptno;

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

两种外连接的运行结果

+--------+------------+
| ename  | dname      |
+--------+------------+
| MILLER | ACCOUNTING |
| KING   | ACCOUNTING |
| CLARK  | ACCOUNTING |
| FORD   | RESEARCH   |
| ADAMS  | RESEARCH   |
| SCOTT  | RESEARCH   |
| JONES  | RESEARCH   |
| SMITH  | RESEARCH   |
| JAMES  | SALES      |
| TURNER | SALES      |
| BLAKE  | SALES      |
| MARTIN | SALES      |
| WARD   | SALES      |
| ALLEN  | SALES      |
| NULL   | OPERATIONS |
+--------+------------+

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

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

外连接最重要的特点是：主表的数据无条件的全部查询出来。

案例：找出哪个部门没有员工

select 
	d.*
from
	emp e
right join
	dept d
on
	e.deptno = d.deptno
where
	e.empno is null;

+--------+------------+--------+
| DEPTNO | DNAME      | LOC    |
+--------+------------+--------+
|     40 | OPERATIONS | BOSTON |
+--------+------------+--------+

右外连接，dept为主表，emp为副标，副表当中没有与主表匹配的数据的话，生成空数据。

d*表示查询所有选中的行数据

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

select 
	a.ename as '员工名', b.ename as '领导名'
from
	emp a
left join
	emp b
on
	a.mgr = b.empno; 

+--------+--------+
| 员工名      | 领导名     |
+--------+--------+
| 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  |
+--------+--------+

现在和之前相比多了King的领导为Null ,因为右表为副表，没有与之对应的数据，就会自动添加null

8.三张表如何链接

语法：
select
…
from
a
join
b
on
a和b的连接条件
join
c
on
a和c的连接条件
right join
d
on
a和d的连接条件

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

每加入一个新表的信息就使用join 表格 in 加入的筛选信息

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

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 |
+--------+---------+------------+-------+

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


	select 
		e.ename '员工',d.dname,s.grade,e1.ename '领导'
	from
		emp e
	join
		dept d
	on
		e.deptno = d.deptno
	join
		salgrade s
	on
		e.sal between s.losal and s.hisal
	left join  //这个left指代的是最开始的表格emp e
		emp e1
	on
		e.mgr = e1.empno;

	+--------+------------+-------+-------+
	| 员工      | dname      | grade | 领导    |
	+--------+------------+-------+-------+
	| SMITH  | RESEARCH   |     1 | FORD  |
	| ALLEN  | SALES      |     3 | BLAKE |
	| WARD   | SALES      |     2 | BLAKE |
	| JONES  | RESEARCH   |     4 | KING  |
	| MARTIN | SALES      |     2 | BLAKE |
	| BLAKE  | SALES      |     4 | KING  |
	| CLARK  | ACCOUNTING |     4 | KING  |
	| SCOTT  | RESEARCH   |     4 | JONES |
	| KING   | ACCOUNTING |     5 | NULL  |
	| TURNER | SALES      |     3 | BLAKE |
	| ADAMS  | RESEARCH   |     1 | SCOTT |
	| JAMES  | SALES      |     1 | BLAKE |
	| FORD   | RESEARCH   |     4 | JONES |
	| MILLER | ACCOUNTING |     2 | CLARK |
	+--------+------------+-------+-------+

二.子查询

1.什么是子查询

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

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

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

3.where子句中的子查询

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

	select 
		ename,sal
	from
		emp 
	where
		sal > min(sal);

ERROR 1111 (HY000): Invalid use of group function

where子句中不能直接使用分组函数。

实现思路：
第一步：查询最低工资是多少

select min(sal) from emp;
		+----------+
		| min(sal) |
		+----------+
		|   800.00 |
		+----------+

第二步：找出>800的m

select ename,sal from emp where sal > 800;

第三步：合并

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 |
		+--------+---------+

4.from子句中的子查询

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

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

第一步：找出每个岗位的平均工资（按照岗位分组求平均值）

	select job,avg(sal) from emp group by job;
	+-----------+-------------+
	| job       | avgsal      |
	+-----------+-------------+
	| ANALYST   | 3000.000000 |
	| CLERK     | 1037.500000 |
	| MANAGER   | 2758.333333 |
	| PRESIDENT | 5000.000000 |
	| SALESMAN  | 1400.000000 |
	+-----------+-------------+表t

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

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

t表和s表进行表连接，条件：t表avg(sal) between s.losal and s.hisal;

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.select后面出现的子查询（这个内容不需要掌握，了解即可！！！）

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

select 
	e.ename,e.deptno,(select d.dname from dept d where e.deptno = d.deptno) as dname 
from 
	emp e;
+--------+--------+------------+
| ename  | deptno | dname      |
+--------+--------+------------+
| SMITH  |     20 | RESEARCH   |
| ALLEN  |     30 | SALES      |
| WARD   |     30 | SALES      |
| JONES  |     20 | RESEARCH   |
| MARTIN |     30 | SALES      |
| BLAKE  |     30 | SALES      |
| CLARK  |     10 | ACCOUNTING |
| SCOTT  |     20 | RESEARCH   |
| KING   |     10 | ACCOUNTING |
| TURNER |     30 | SALES      |
| ADAMS  |     20 | RESEARCH   |
| JAMES  |     30 | SALES      |
| FORD   |     20 | RESEARCH   |
| MILLER |     10 | ACCOUNTING |
+--------+--------+------------+
//select d.dname from dept d where e.deptno = d.deptno 获得一列数据,名称所对应的部门

select子查询的限制

select 
	e.ename,e.deptno,(select dname from dept) as dname
from
	emp e;
//错误：ERROR 1242 (21000): Subquery returns more than 1 row

注意：对于select后面的子查询来说，这个子查询只能一次返回1条结果，多于1条，就报错了。！

三.union合并查询结果集

1.案例：找出工作岗位是SALESMAN和MANAGER的员工

第一种：select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
第二种：select ename,job from emp where job in('MANAGER','SALESMAN');
+--------+----------+
| ename  | job      |
+--------+----------+
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| JONES  | MANAGER  |
| MARTIN | SALESMAN |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| TURNER | SALESMAN |
+--------+----------+

使用union进行链接

select ename,job from emp where job = 'MANAGER'
union
select ename,job from emp where job = 'SALESMAN';

+--------+----------+
| ename  | job      |
+--------+----------+
| JONES  | MANAGER  |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
+--------+----------+

2.Union的优点

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把乘法变成了加法运算）

3.Union的使用限制

select ename,job from emp where job = 'MANAGER'
union
select ename from emp where job = 'SALESMAN';
//错误的：union在进行结果集合并的时候，要求两个结果集的列数相同。

数据类型也要求一致

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    |
+--------+---------+
// MYSQL可以，oracle语法严格 ，不可以，报错。要求：结果集合并时列和列的数据类型也要一致。

四.limit（非常重要，以后分页查询全靠它了）

1.limit作用：将查询结果集的一部分取出来。通常使用在分页查询当中

百度搜索默认：一页显示10条记录。
分页的作用是为了提高用户的体验，因为一次全部都查出来，用户体验差。
可以一页一页翻页看。

注意：limit是mysql特有的，其他数据库中没有，不通用。（Oracle中有一个相同的机制，叫做rownum）

2.limit如何使用

limit startIndex, length

startIndex表示起始位置，从0开始，0表示第一条数据。
length表示取几个

案例：取出工资前5名的员工（思路：降序取前5个）

    select ename,sal from emp order by sal desc;
    取前5个：
        select ename,sal from emp order by sal desc limit 0, 5;
        select ename,sal from emp order by sal desc limit 5;

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

select 
	ename,sal
from
	emp
order by 
	sal desc
limit 5; //取前5

select 
	ename,sal
from
	emp
order by 
	sal desc
limit 0,5;

+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+

limit在mysql当中的执行位置

select		5
	...
from			1
	...		
where			2
	...	
group by		3
	...
having		4
	...
order by		6
	...
limit			7
	...;

limit可以说是最后的筛选条件了

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

select 
	ename,sal
from
	emp
order by
	sal desc
limit
	2, 3;

2表示起始位置从下标2开始，就是第三条记录。
3表示长度。

+-------+---------+
| ename | sal     |
+-------+---------+
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+

3.分页应用

分页案例：

每页显示3条记录：

第1页：0, 3

第2页：3, 3

第3页：6, 3

第4页：9, 3

第5页：12, 3

每页显示pageSize条记录

记公式：
limit (pageNo-1)*pageSize , pageSize

4.关于DQL语句的大总结：

书写顺序

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

执行顺序？
1.from
2.where
3.group by
4.having
5.select
6.order by
7.limit…

六.表

1.创建表DDL

建表的语法格式：(建表属于DDL语句，DDL包括：create drop alter)

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

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

表名：建议以t_ 或者 tbl_开始，可读性强。见名知意。
字段名：见名知意。
表名和字段名都属于标识符。

2.关于MySQL当中字段的数据类型

以下只说常见的 int 整数型(java中的int)

bigint 长整型(java中的long)

float 浮点型(java中的float double)

char 定长字符串(String) 最长225

varchar 可变长字符串(StringBuffer/StringBuilder) 最长225 可以自动调节字符串的长度

date 日期类型（对应Java中的java.sql.Date类型）短日期类型

datetime 长日期类型

BLOB 二进制大对象（存储图片、视频等流媒体信息）

Binary Large OBject （对应java中的Object）

CLOB 字符大对象（存储较大文本，比如，可以存储4G的字符串。主要是小说一类的数据）

Character Large OBject（对应java中的Object） ......

char 和varchar的优缺点和选择

char

优点：不需要动态分配空间，速度快。
缺点：使用不当可能会导致空间的浪费。

varchar

优点：不需要动态分配空间，速度快。
缺点：使用不当可能会导致空间的浪费。

char和varchar怎么选择？

在实际的开发中，当某个字段中的数据长度不发生改变的时候，是定长的，例如：性别、生日等都是采用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’
…

创建学生表：

学生信息包括：

学号、姓名、性别、班级编号、生日

学号：bigint 姓名：varchar 性别：char 班级编号：int 生日：char

create table t_student(
	no int,
	name varchar(32),
	sex char(1),
	age int(3),
	email varchar(255)
);

删除表：

drop table t_student;

表存在的话进行删除，不存在的话进行报错。

3.表的复制

语法： create table 表名 as select语句;

将查询结果当做表创建出来。

复制emp当中的全部内容

mysql> create table emp2 as select * from emp;

复制指定的数据

create table mytable as select empno,ename from emp where job = 'MANAGER';
+-------+-------+
| empno | ename |
+-------+-------+
|  7566 | JONES |
|  7698 | BLAKE |
|  7782 | CLARK |
+-------+-------+

4.对于表结构的修改

什么是对表结构的修改
添加一个字段，删除一个字段，修改一个字段！！！

设计好之后，对表结构的修改是很少的，修改表结构就是对之前的设计进行了否定，即使需要修改表结构，我们也可以直接使用工具操作。修改表结构的语句不会出现在Java代码当中。出现在java代码当中的sql包括：insert delete update select（这些都是表中的数据操作。）

对表结构的修改需要使用：alter（属于DDL语句）

DDL包括：create drop alter

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

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

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

七.插入数据

1.insert （DML）

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

字段名要与数据一一对应，数据名与数据类型对应

插入四条数据

insert into t_student(no,name,sex,age,email) values(1,'zhangsan','m',20,'zhangsan@123.com');

insert into t_student(email,name,sex,age,no) values('lisi@123.com','lisi','f',20,2);

insert into t_student(no) values(3);

select * from t_student;
+------+----------+------+------+------------------+
| no   | name     | sex  | age  | email            |
+------+----------+------+------+------------------+
|    1 | zhangsan | m    |   20 | zhangsan@123.com |
|    2 | lisi     | f    |   20 | lisi@123.com     |
|    3 | NULL     | NULL | NULL | NULL             |
+------+----------+------+------+------------------+
insert into t_student(name) values('wangwu');// 除name字段之外，剩下的所有字段自动插入NULL
select * from t_student;
+------+----------+------+------+------------------+
| no   | name     | sex  | age  | email            |
+------+----------+------+------+------------------+
|    1 | zhangsan | m    |   20 | zhangsan@123.com |
|    2 | lisi     | f    |   20 | lisi@123.com     |
|    3 | NULL     | NULL | NULL | NULL             |
| NULL | wangwu   | NULL | NULL | NULL             |
+------+----------+------+------+------------------+

需要注意的地方： 当一条insert语句执行成功之后，表格当中必然会多一行记录。即使多的这一行记录当中某些字段是NULL，后期也没有办法在执行 insert语句插入数据了，只能使用update进行更新。

简化了之后的插入，将不用指定字段类型

// 注意：前面的字段名省略的话，等于都写上了！所以值也要都写上！
insert into t_student values(2, 'lisi', 'f', 20, 'lisi@123.com');

自从有了Navicat等工具软件之后，数据库的管理便方便了很多，但一些操作依旧需要理解，插入数据时可能依旧要用到，还是建议自己敲一遍的。

一次插入多行数据

导入数据的笨办法就是一次插入多条数据

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

insert into t_student
	(no,name,sex,age,email)
values
	(3,'rose','m',10,'rose@123.com'),(4,'laotie','m',15,'laotie@123.com');

2.fromat使数字格式化

格式化之前

select ename,sal from emp;
	+--------+---------+
	| ename  | sal     |
	+--------+---------+
	| SMITH  |  800.00 |
	| 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 |
	+--------+---------+

格式化之后：format(数字, ‘格式’)

select ename,sal from emp;
	+--------+---------+
	| ename  | sal     |
	+--------+---------+
	| SMITH  |  800.00 |
	| 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 |
	+--------+---------+

3.insert插入日期

str_to_date：将字符串varchar类型转换成date类型
date_format：将date类型转换成具有一定格式的varchar字符串类型。

删除旧表

drop table if exists t_student; // 当这个表存在的话删除。

创建新表

drop table if exists t_user;
create table t_user(
	id int,
	name varchar(32),
	birth date 
);

//注意：生日可以使用date数据类型，也可以使用char数据类型，例如char（20）

生日：1990-10-11 （10个字符）

注意命名规范：

所有的标识符都是全部小写，单词和单词之间使用下划线进行衔接。

表的结构

desc t_user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
| birth | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

插入数据

insert into t_user(id,name,birth) values(1, 'zhangsan', '01-10-1990') // 1990年10月1日

问题：出现了类型不符的问题，数据库当中的类型是date，我们插入的类型是char类型

方法:使用str_to_date函数进行数据类型的转换

方法格式：str_to_date(‘字符串日期’, ‘日期格式’)

日期格式：

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

正确的代码

insert into t_user(id,name,birth) values(1, 'zhangsan', str_to_date('01-10-1990','%d-%m-%Y'));

但如果你是用的字符串是如下格式的话，你讲不用使用函数。他会自动完成转换

	insert into t_user(id,name,birth) values(2, 'lisi', '1990-10-01');

date_format函数怎么用？
date_format(日期类型数据, ‘日期格式’)
这个函数通常使用在查询日期方面。设置展示的日期格式。

查询的时候可以以某个特定的日期格式展示吗？

使用date_format函数

select id,name,date_format(birth, '%m/%d/%Y') as birth from t_user;
+------+----------+------------+
| id   | name     | birth      |
+------+----------+------------+
|    1 | zhangsan | 10/01/1990 |
|    2 | lisi     | 10/01/1990 |
+------+----------+------------+

mysql有默认的日期输出格式：’%Y-%m-%d’

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

输出出来的数据进行了默认的日期格式化，自动将数据库中的date类型转换成varchar类型。

查询表格当中的数据

select id,name,date_format(birth,'%Y/%m/%d') as birth from t_user;
+------+----------+------------+
| id   | name     | birth      |
+------+----------+------------+
|    1 | zhangsan | 1990/10/01 |
|    2 | lisi     | 1990/10/01 |
+------+----------+------------+

java中的日期格式
yyyy-MM-dd HH:mm:ss SSS

4.date和datetime两个类型的区别

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

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

类似于浮点数与双精度浮点数的区别

删除旧表创建新表

drop table if exists t_user;
create table t_user(
	id int,
	name varchar(32),
	birth date,
	create_time datetime
);

插入数据

insert into t_user(id,name,birth,create_time) values(1,'zhangsan','1990-10-01','2020-03-18 15:49:50');

在mysql当中怎么获取系统当前时间

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

insert into t_user(id,name,birth,create_time) values(2,'lisi','1991-10-01',now());

5.将查询结果插入到一张表当中，insert相关的！！！【了解内容】

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   |
	+--------+------------+----------+

//很少用！查的结果刚好符合表的结构才能查

insert into dept_bak select * from dept; 

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   |
+--------+------------+----------+

八.修改update

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

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

对id=2的出生日期进行修改，名字也进行了更新但是内容没有改变

update t_user set name = 'jack', birth = '2000-10-11' where id = 2;
+------+----------+------------+---------------------+
| id   | name     | birth      | create_time         |
+------+----------+------------+---------------------+
|    1 | zhangsan | 1990-10-01 | 2020-03-18 15:49:50 |
|    2 | jack     | 2000-10-11 | 2020-03-18 15:51:23 |
+------+----------+------------+---------------------+

对id=2的创建时间进行修改

update t_user set name = 'jack', birth = '2000-10-11', create_time = now() where id = 2;
+------+----------+------------+---------------------+
| id   | name     | birth      | create_time         |
+------+----------+------------+---------------------+
|    1 | zhangsan | 1990-10-01 | 2020-03-18 15:49:50 |
|    2 | jack     | 2000-10-11 | 2022-09-16 18:08:32 |
+------+----------+------------+---------------------+

更新所有对指定的字段的所有内容进行更改

	update t_user set name = 'abc';
+------+------+------------+---------------------+
| id   | name | birth      | create_time         |
+------+------+------------+---------------------+
|    1 | abc  | 1990-10-01 | 2020-03-18 15:49:50 |
|    2 | abc  | 2000-10-11 | 2022-09-16 18:08:32 |
+------+------+------------+---------------------+

九.删除

1.删除delect

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

注意：没有条件，整张表的数据会全部删除！没有条件的情况下默认是全选的

关于这一点，删除，查询更改都是一样的，没有条件约束，默认选中表中所有的数据

实例：

//删除id = 2的数据
delete from t_user where id = 2;

//插入数据
insert into t_user(id) values(2);

delete from t_user; // 删除所有！

怎么删除大表中的数据？（重点）

	truncate table 表名; // 表被截断，不可回滚。永久丢失。

删除表？

	drop table 表名; // 这个通用。
	drop table if exists 表名; // oracle不支持这种写法。

2.快速删除表中的数据？【truncate比较重要，必须掌握】

删除的数据较慢

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

truncate语句删除数据的原理（delete属于DML语句！！！）

表中的数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放！！！
这种删除缺点是：删除效率比较低。
这种删除优点是：支持回滚，后悔了可以再恢复数据！！！

delete语句删除数据的原理

这种删除效率比较高，表被一次截断，物理删除。
这种删除缺点：不支持回滚。
这种删除优点：快速。

用法：truncate table 表名; （这种操作属于DDL操作）

大表：拥有大量数据的表，上亿条记录
删除的时候，使用delete，也许需要执行1个小时才能删除完！效率较低。
可以选择使用truncate删除表中的数据。只需要不到1秒钟的时间就删除结束。效率较高。
但是使用truncate之前，必须仔细询问客户是否真的要删除，并警告删除之后不可恢复！

truncate是删除表中的数据，表还在！
 删除表操作

drop table 表名; // 这不是删除表中的数据，而是把表删除。

九.约束（非常重要！！！！）

1.什么是约束？有哪些约束类型？

约束的作用

在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的
合法性、有效性、完整性。
常见的约束有哪些呢？？？
   非空约束(not null)：约束的字段不能为NULL
   唯一约束(unique)：约束的字段不能重复
   主键约束(primary key)：约束的字段既不能为NULL，也不能重复（简称PK）
   外键约束(foreign key)：...（简称FK）
   检查约束(check)：注意Oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束。

主要学习前四个

2.非空约束（not null）

非空约束not null约束的字段不能为NULL。

not null只有列级约束，没有表级约束！

如果有名为t_vip的表格进行删除

drop table if exists t_vip;

如何使用约束

在创建表格的时候使用

create table t_vip(
	id int,
	name varchar(255) not null  
);

添加相应的元素进行测试


insert into t_vip(id,name) values(1,'zhangsan');

insert into t_vip(id,name) values(2,'lisi');

insert into t_vip(id) values(3);
ERROR 1364 (HY000): Field 'name' doesn't have a default value

注意：not null约束只有列级约束。没有表级约束。

3.唯一约束(unique)

唯一性约束unique约束的字段不能重复，但是可以为NULL。

案例：给一个列添加unique

删除和创建表格

drop table if exists t_vip;
create table t_vip(
	id int,
	name varchar(255) unique,
	email varchar(255)
);

添加和查询数据

insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(2,'lisi','lisi@123.com');
insert into t_vip(id,name,email) values(3,'wangwu','wangwu@123.com');
select * from t_vip;
+------+----------+------------------+
| id   | name     | email            |
+------+----------+------------------+
|    1 | zhangsan | zhangsan@123.com |
|    2 | lisi     | lisi@123.com     |
|    3 | wangwu   | wangwu@123.com   |
+------+----------+------------------+

插入重复是数据

insert into t_vip(id,name,email) values(4,'wangwu','wangwu@sina.com');
ERROR 1062 (23000): Duplicate entry 'wangwu' for key 'name'

添加值为null的数据

insert into t_vip(id) values(4);
insert into t_vip(id) values(5);
+------+----------+------------------+
| id   | name     | email            |
+------+----------+------------------+
|    1 | zhangsan | zhangsan@123.com |
|    2 | lisi     | lisi@123.com     |
|    3 | wangwu   | wangwu@123.com   |
|    4 | NULL     | NULL             |
|    5 | NULL     | NULL             |
+------+----------+------------------+

新需求：name和email两个字段联合起来具有唯一性！！！！

	drop table if exists t_vip;
	create table t_vip(
		id int,
		name varchar(255) unique, 
		email varchar(255) unique
	);
//约束直接添加在列后面是列级约束

这样创建表示：name具有唯一性，email具有唯一性。各自唯一。我们的需求是两者都一致才具有唯一性。

需求要求这两条数据可以同时插入，各自唯一会报错

insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(2,'zhangsan','zhangsan@sina.com');

约束没有添加在列的后面，这种约束被称为表级约束。

drop table if exists t_vip;
		create table t_vip(
			id int,
			name varchar(255),
			email varchar(255),
			unique(name,email) 
		);
		insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
		insert into t_vip(id,name,email) values(2,'zhangsan','zhangsan@sina.com');
		select * from t_vip;

用法：放到表的末尾

unique（字段名，字段名，.........）

name和email两个字段联合起来唯一！！！测试！！！！

insert into t_vip(id,name,email) values(3,'zhangsan','zhangsan@sina.com');
ERROR 1062 (23000): Duplicate entry 'zhangsan-zhangsan@sina.com' for key 'name'

什么时候使用表级约束呢？
需要给多个字段联合起来添加某一个约束的时候，需要使用表级约束。

 unique 和not null可以联合吗？

drop table if exists t_vip;
	create table t_vip(
		id int,
		name varchar(255) not null unique
	);

	mysql> desc t_vip;
	+-------+--------------+------+-----+---------+-------+
	| Field | Type         | Null | Key | Default | Extra |
	+-------+--------------+------+-----+---------+-------+
	| id    | int(11)      | YES  |     | NULL    |       |
	| name  | varchar(255) | NO   | PRI | NULL    |       |
	+-------+--------------+------+-----+---------+-------+

可以联合，并且被联合约束的字段自动变成主键（注意：oracle中不一样！）

测试：报错name不可以为null也不可以重复

insert into t_vip(id,name) values(1,'zhangsan');

insert into t_vip(id,name) values(2,'zhangsan');
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 't_vip.name'

insert into t_vip(id) values(2); 
ERROR 1364 (HY000): Field 'name' doesn't have a default value

4.主键约束（primary key，简称PK）

主键的相关术语：

主键约束：就是一种约束。（PK）
主键字段：该字段上添加了主键约束，这样的字段叫做：主键字段（ID）
主键值：主键字段中的每一个值都叫做：主键值。（1）

主键的分类？

根据主键字段的字段数量来划分：

单一主键：（推荐的，常用的。）

复合主键：多个字段联合起来添加一个主键约束（复合主键不建议使用，因为复合主键违背三范式。）

根据主键性质来划分：

自然主键：主键值最好就是一个和业务没有任何关系的自然数。（这种方式是推荐的）

业务主键：主键值和系统的业务挂钩，例如：拿着银行卡的卡号做主键，拿着身份证号码作为主键。（不推荐用）

最好不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候，主键值可能也需要随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键值重复。

主键的作用：

表的设计三范式中有要求，第一范式就要求任何一张表都应该有主键。

主键的作用：主键值是这行记录在这张表当中的唯一标识。（就像一个人的身份证号码一样。）

主键的特点：

不能为NULL，也不能重复。（not null + unique）

一张表的主键约束只能有1个。（必须记住）

怎么给一张表添加主键约束呢？

	drop table if exists t_user;
	create table t_user(
		id int primary key,  
		username varchar(255),
		email varchar(255)
	);

一个字段做主键叫做单一主键，单一主键是列级约束

添加信息

	insert into t_user(id,username,email) values(1,'zs','zs@123.com');
	insert into t_user(id,username,email) values(2,'ls','ls@123.com');
	insert into t_user(id,username,email) values(3,'ww','ww@123.com');
	select * from t_user;
	+----+----------+------------+
	| id | username | email      |
	+----+----------+------------+
	|  1 | zs       | zs@123.com |
	|  2 | ls       | ls@123.com |
	|  3 | ww       | ww@123.com |
	+----+----------+------------+

进行测试

	insert into t_user(id,username,email) values(1,'jack','jack@123.com');
	ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

	insert into t_user(username,email) values('jack','jack@123.com');
	ERROR 1364 (HY000): Field 'id' doesn't have a default value

证明主键元素确实具有不可重复和不能为null两种特性

以下内容是演示以下复合主键，不需要掌握：

drop table if exists t_vip;
	// id和name联合起来做主键：复合主键！！！！
	create table t_vip(
		id int,
		name varchar(255),
		email varchar(255),
		primary key(id,name)
	);

插入两条数据

insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(1,'lisi','lisi@123.com');

//错误：不能重复

错误，不能够重复

在实际开发中不建议使用：复合主键。建议使用单一主键！
因为主键值存在的意义就是这行记录的身份证号，只要意义达到即可，单一主键可以做到。
复合主键比较复杂，不建议使用！！！

一个表中主键约束能加两个吗？

drop table if exists t_vip;
	create table t_vip(
		id int primary key,
		name varchar(255) primary key
	);
ERROR 1068 (42000): Multiple primary key defined

 结论：一张表，主键约束只能添加1个。（主键只能有1个。）

主键值建议使用：
int
bigint
char
等类型。

 不建议使用：varchar来做主键。主键值一般都是数字，一般都是定长的！

根据功能划分为自然主键和业务主键

多使用和业务没有关系的自然主键，少使用和业务有关的业务主键

mysql提供主键值自增：（非常重要。）

drop table if exists t_vip;
	create table t_vip(
		id int primary key auto_increment, / /auto_increment表示自增，从1开始，以1递增！
		name varchar(255)
	);
	insert into t_vip(name) values('zhangsan');
	insert into t_vip(name) values('zhangsan');
	insert into t_vip(name) values('zhangsan');
	insert into t_vip(name) values('zhangsan');
	insert into t_vip(name) values('zhangsan');
	insert into t_vip(name) values('zhangsan');
	insert into t_vip(name) values('zhangsan');
	insert into t_vip(name) values('zhangsan');

id int primary key auto_increment 将一个int型的数据设置为自增主键

	select * from t_vip;

	+----+----------+
	| id | name     |
	+----+----------+
	|  1 | zhangsan |
	|  2 | zhangsan |
	|  3 | zhangsan |
	|  4 | zhangsan |
	|  5 | zhangsan |
	|  6 | zhangsan |
	|  7 | zhangsan |
	|  8 | zhangsan |
	+----+----------+

提示:Oracle当中也提供了一个自增机制，叫做：序列（sequence）对象。

十.外键约束（非常重要）

1.相关简介

外键约束涉及到的相关术语：
外键约束：一种约束（foreign key）
外键字段：该字段上添加了外键约束
外键值：外键字段当中的每一个值。

用法：foreign key(classno) references t_class(cno)

在创建表的时候放到表的末尾，classno与父表的cno字段进行绑定，不能有超出cno范围的数据。如果cno当中的数据只有101和102那么classno也只能有101和102，对于关联性十分强的数据可以用这种设计模式。

2.实际应用

业务背景：请设计数据库表，用来维护学生和班级的信息？

第一种方案：一张表存储所有数据

		no(pk)			name			classno			classname
		-------------------------------------------------------------------------------------------
		1					zs1				101				北京大兴区经济技术开发区亦庄二中高三1班
		2					zs2				101				北京大兴区经济技术开发区亦庄二中高三1班
		3					zs3				102				北京大兴区经济技术开发区亦庄二中高三2班
		4					zs4				102				北京大兴区经济技术开发区亦庄二中高三2班
		5					zs5				102				北京大兴区经济技术开发区亦庄二中高三2班
		缺点：冗余。【不推荐】

缺点：冗余。【不推荐】

第二种方案：两张表（班级表和学生表）

		t_class 班级表
		cno(pk)		cname
		--------------------------------------------------------
		101		北京大兴区经济技术开发区亦庄二中高三1班
		102		北京大兴区经济技术开发区亦庄二中高三2班

		t_student 学生表
		sno(pk)		sname				classno(该字段添加外键约束fk)
		------------------------------------------------------------
		1				zs1				101
		2				zs2				101
		3				zs3				102
		4				zs4				102
		5				zs5				102

将以上表的建表语句写出来： t_student中的classno字段引用t_class表中的cno字段，此时t_student表叫做子表。t_class表叫做父表，被引用数据的表是父表。

顺序要求：

删除数据的时候，先删除子表，再删除父表。

添加数据的时候，先添加父表，在添加子表。

创建表的时候，先创建父表，再创建子表。

删除表的时候，先删除子表，在删除父表。

外键的作用

当cno字段没有任何约束的时候，可能会导致数据无效。可能出现一个103，但是103班级不存在。所以为了保证cno字段中的值都是101和102，需要给cno字段添加外键约束。
那么：cno字段就是外键字段。cno字段中的每一个值都是外键值。

创建表格

	drop table if exists t_student;
	drop table if exists t_class;

	create table t_class(
		cno int,
		cname varchar(255),
		primary key(cno)
	);

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

添加数据

	insert into t_class values(101,'北京大兴区经济技术开发区亦庄二中高三1班');
	insert into t_class values(102,'北京大兴区经济技术开发区亦庄二中高三2班');

	insert into t_student values(1,'zs1',101);
	insert into t_student values(2,'zs2',101);
	insert into t_student values(3,'zs3',102);
	insert into t_student values(4,'zs4',102);
	insert into t_student values(5,'zs5',102);
	insert into t_student values(6,'zs6',102);
	select * from t_class;
+-----+------------------------------------------------------------+
| cno | cname                                                      |
+-----+------------------------------------------------------------+
| 101 | 北京大兴区经济技术开发区亦庄二中高三1班                    |
| 102 | 北京大兴区经济技术开发区亦庄二中高三2班                    |
+-----+------------------------------------------------------------+
	select * from t_student;
+-----+-------+---------+
| sno | sname | classno |
+-----+-------+---------+
|   1 | zs1   |     101 |
|   2 | zs2   |     101 |
|   3 | zs3   |     102 |
|   4 | zs4   |     102 |
|   5 | zs5   |     102 |
|   6 | zs6   |     102 |
+-----+-------+---------+

插入不属于父表的数据

	insert into t_student values(7,'lisi',103);
	ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bjpowernode`.INT `t_student_ibfk_1` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))

外键值可以为NULL？

外键可以为NULL。

外键字段引用其他表的某个字段的时候，被引用的字段必须是主键吗？

注意：被引用的字段不一定是主键，但至少具有unique约束。

 总结：

外键约束的作用：保证子表t_student字段上的cno的数据安全，一旦加了外键约束字段中的数据就不能随便写了，只能书写来自父表上绑定的字段数据。

还有一个表的约束的知识点，但基本不会用到