掌握约束的使用
掌握表关系及建表原则
重点掌握多表查询操作
掌握事务操作

1、约束

上面表中可以看到表中数据存在一些问题：

id 列一般是用标示数据的唯一性的，而上述表中的id为1的有三条数据，并且 马花疼 没有id进行标示
柳白 这条数据的age列的数据是3000，而人也不可能活到3000岁
马运 这条数据的math数学成绩是-5，而数学学得再不好也不可能出现负分
柳青 这条数据的english列（英文成绩）值为null，而成绩即使没考也得是0分

针对上述数据问题，我们就可以从数据库层面在添加数据的时候进行限制，这个就是约束。

1.1 约束的概念

约束的概念
- 约束是作用于表中列上的规则，用于限制加入表的数据
- 对表中的数据进行限定，保证数据的正确性、有效性、完整性！
  1.2 约束的分类
  | 约束 | 说明 | | —- | —- | | PRIMARY KEY | 主键约束 | | PRIMARY KEY AUTO_INCREMENT | 主键、自动增长 | | UNIQUE | 唯一约束 | | NOT NULL | 非空约束 | | FOREIGN KEY | 外键约束 | | FOREIGN KEY ON UPDATE CASCADE | 外键级联更新 | | FOREIGN KEY ON DELETE CASCADE | 外键级联删除 |
非空约束：关键字是 NOT NULL
保证列中所有的数据不能有null值。
例如：id列在添加 马花疼 这条数据时就不能添加成功。
唯一约束：关键字是 UNIQUE
保证列中所有数据各不相同。
例如：id列中三条数据的值都是1，这样的数据在添加时是绝对不允许的。
主键约束：关键字是 PRIMARY KEY
主键是一行数据的唯一标识，要求非空且唯一。一般我们都会给没张表添加一个主键列用来唯一标识数据。
例如：上图表中id就可以作为主键，来标识每条数据。那么这样就要求数据中id的值不能重复，不能为null值。
检查约束：关键字是 CHECK
保证列中的值满足某一条件。
例如：我们可以给age列添加一个范围，最低年龄可以设置为1，最大年龄就可以设置为300，这样的数据才更合理些。

注意：MySQL不支持检查约束。这样是不是就没办法保证年龄在指定的范围内了？从数据库层面不能保证，以后可以在java代码中进行限制，一样也可以实现要求。
默认约束：关键字是 DEFAULT
保存数据时，未指定值则采用默认值。
例如：我们在给english列添加该约束，指定默认值是0，这样在添加数据时没有指定具体值时就会采用默认给定的0。
外键约束：关键字是 FOREIGN KEY
外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性。

1.3 非空约束

概念
- 非空约束用于保证列中所有数据不能有NULL值
  1.3.1 建表时添加非空约束
```sql — 标准语法 CREATE TABLE 表名( 列名数据类型 NOT NULL, 列名数据类型, … );

— 创建student4表 CREATE TABLE student4( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) NOT NULL — 给name添加非空约束 );

— 添加数据 INSERT INTO student4 VALUES (NULL,’张三’),(NULL,’李四’); — 添加null值，会报错 INSERT INTO student4 VALUES (NULL,NULL);

<a name="f3P7v"></a>
### 1.3.2 删除非空约束
```sql
-- 标准语法
ALTER TABLE 表名 MODIFY 列名 数据类型;
-- 删除非空约束
ALTER TABLE student4 MODIFY NAME VARCHAR(20);

1.3.3 建表后单独添加非空约束

-- 标准语法
ALTER TABLE 表名 MODIFY 列名 数据类型 NOT NULL;

-- 添加非空约束
ALTER TABLE student4 MODIFY NAME VARCHAR(20) NOT NULL;

1.4 唯一约束

概念
- 唯一约束用于保证列中所有数据各不相同
  1.4.1 建表时添加唯一约束
```sql — 标准语法 CREATE TABLE 表名( 列名数据类型 UNIQUE, 列名数据类型, … );

— 创建student3表 CREATE TABLE student3( id INT PRIMARY KEY AUTO_INCREMENT, tel VARCHAR(20) UNIQUE — 给tel列添加唯一约束 );

— 添加数据 INSERT INTO student3 VALUES (NULL,’18888888888’),(NULL,’18666666666’); — 添加重复数据，会报错 INSERT INTO student3 VALUES (NULL,’18666666666’);

— 查询student3数据表 SELECT * FROM student3; — student3表详细 DESC student3;

<a name="S9Vu9"></a>
### 1.4.2 删除唯一约束
```sql
-- 标准语法
ALTER TABLE 表名 DROP INDEX 列名;

-- 删除唯一约束
ALTER TABLE student3 DROP INDEX tel;

1.4.3 建表后单独添加唯一约束

-- 标准语法
ALTER TABLE 表名 MODIFY 列名 数据类型 UNIQUE;

-- 添加唯一约束
ALTER TABLE student3 MODIFY tel VARCHAR(20) UNIQUE;

1.5 主键约束

概念主键是一行数据的唯一标识，
- 主键约束包含：非空和唯一两个功能
- 一张表只能有一个列作为主键(并不只能是一个字段,可以是多个字段组成一个主键:联合主键)
  1.5.1 建表时添加主键约束
```sql — 标准语法 CREATE TABLE 表名( 列名数据类型 PRIMARY KEY, 列名数据类型, … );

— 创建student表 CREATE TABLE student( id INT PRIMARY KEY — 给id添加主键约束 );

— 添加数据 INSERT INTO student VALUES (1),(2); — 主键默认唯一，添加重复数据，会报错 INSERT INTO student VALUES (2); — 主键默认非空，不能添加null的数据 INSERT INTO student VALUES (NULL);

— 查询student表 SELECT * FROM student; — 查询student表详细 DESC student;

<a name="X7ikH"></a>
### 1.5.2 删除主键
```sql
-- 标准语法
ALTER TABLE 表名 DROP PRIMARY KEY;

-- 删除主键
ALTER TABLE student DROP PRIMARY KEY;

1.5.3 建表后单独添加主键

-- 标准语法
ALTER TABLE 表名 MODIFY 列名 数据类型 PRIMARY KEY;

-- 添加主键
ALTER TABLE student MODIFY id INT PRIMARY KEY;

1.6 主键自动增长约束

1.6.1 建表时添加主键自增约束

-- 标准语法
CREATE TABLE 表名(
    列名 数据类型 PRIMARY KEY AUTO_INCREMENT,
    列名 数据类型,
    ...
);

-- 创建student2表
CREATE TABLE student2(
    id INT PRIMARY KEY AUTO_INCREMENT    -- 给id添加主键自增约束
);

-- 添加数据
INSERT INTO student2 VALUES (1),(2);
-- 添加null值，会自动增长
INSERT INTO student2 VALUES (NULL),(NULL);

-- 查询student2表
SELECT * FROM student2;
-- student2表详细
DESC student2;

1.6.2 删除自动增长

-- 标准语法
ALTER TABLE 表名 MODIFY 列名 数据类型;

-- 删除自动增长
ALTER TABLE student2 MODIFY id INT;

1.6.3 建表后单独添加自动增长

-- 标准语法
ALTER TABLE 表名 MODIFY 列名 数据类型 AUTO_INCREMENT;

-- 添加自动增长
ALTER TABLE student2 MODIFY id INT AUTO_INCREMENT;

1.7 默认约束

概念
- 保存数据时，未指定值则采用默认值

1.7.1 创建表时添加默认约束

-- 创建表时添加默认约束
CREATE TABLE 表名(
   列名 数据类型 DEFAULT 默认值,
   …
);

1.7.2 建完表后添加默认约束

-- 建完表后添加默认约束
ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值;

1.7.3 删除约束

ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;

1.8 约束练习

根据需求，为表添加合适的约束

-- 员工表
CREATE TABLE emp (
    id INT,  -- 员工id，主键且自增长
    ename VARCHAR(50), -- 员工姓名，非空且唯一
    joindate DATE,  -- 入职日期，非空
    salary DOUBLE(7,2),  -- 工资，非空
    bonus DOUBLE(7,2)  -- 奖金，如果没有将近默认为0
);

上面一定给出了具体的要求，我们可以根据要求创建这张表，并为每一列添加对应的约束。建表语句如下：

DROP TABLE IF EXISTS emp;

-- 员工表
CREATE TABLE emp (
  id INT PRIMARY KEY, -- 员工id，主键且自增长
  ename VARCHAR(50) NOT NULL UNIQUE, -- 员工姓名，非空并且唯一
  joindate DATE NOT NULL , -- 入职日期，非空
  salary DOUBLE(7,2) NOT NULL , -- 工资，非空
  bonus DOUBLE(7,2) DEFAULT 0 -- 奖金，如果没有奖金默认为0
);

通过上面语句可以创建带有约束的 emp 表，约束能不能发挥作用呢。接下来我们一一进行验证，先添加一条没有问题的数据

INSERT INTO emp(id,ename,joindate,salary,bonus) values(1,'张三','1999-11-11',8800,5000);

验证数据

-- 验证主键约束，非空且唯一
INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'张三','1999-11-11',8800,5000);


-- 验证非空约束
INSERT INTO emp(id,ename,joindate,salary,bonus) values(3,null,'1999-11-11',8800,5000);


-- 验证唯一约束
INSERT INTO emp(id,ename,joindate,salary,bonus) values(3,'李四','1999-11-11',8800,5000);

-- 验证默认约束
-- 注意：默认约束只有在不给值时才会采用默认值。如果给了null，那值就是null值。
INSERT INTO emp(id,ename,joindate,salary) values(3,'王五','1999-11-11',8800);

-- 验证自动增长
INSERT INTO emp(ename,joindate,salary,bonus) values('赵六','1999-11-11',8800,null);
INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'赵六2','1999-11-11',8800,null);
INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'赵六3','1999-11-11',8800,null);

1.9 外键约束

1.9.1 概述

外键约束概念
- 外键是某个表中的一列，它包含在另一个表的主键中。
- 外键也是索引的一种，是通过一张表中的一列指向另一张表中的主键，来对两张表进行关联。
- 一张表可以有一个外键，也可以存在多个外键，与多张表进行关联。
建表时添加外键约束
- 为什么要有外键约束
外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性，并且减少数据冗余。

-- 创建db2数据库
CREATE DATABASE db2;
-- 使用db2数据库
USE db2;

-- 创建user用户表
CREATE TABLE USER(
    id INT PRIMARY KEY AUTO_INCREMENT,    -- id
    NAME VARCHAR(20) NOT NULL             -- 姓名
);
-- 添加用户数据
INSERT INTO USER VALUES (NULL,'张三'),(NULL,'李四'),(NULL,'王五');

-- 创建orderlist订单表
CREATE TABLE orderlist(
    id INT PRIMARY KEY AUTO_INCREMENT,    -- id
    number VARCHAR(20) NOT NULL,          -- 订单编号
    uid INT                               -- 订单所属用户
);
-- 添加订单数据
INSERT INTO orderlist VALUES (NULL,'lhl001',1),(NULL,'lhl002',1),
(NULL,'lhl003',2),(NULL,'lhl004',2),
(NULL,'lhl005',3),(NULL,'lhl006',3);

-- 添加一个订单，但是没有所属用户。这合理吗？
INSERT INTO orderlist VALUES (NULL,'lhl007',8);
-- 删除王五这个用户，但是订单表中王五还有很多个订单呢。这合理吗？
DELETE FROM USER WHERE NAME='王五';

-- 所以我们需要添加外键约束，让两张表产生关系

1.9.2 建表时添加外键约束

 -- booktype表
 -- 字段被引用为外键的表为主表,也称被参照表
create table booktype(
 id int primary key,-- 被引用字段必须为主表的主键
 name varchar(10) unique not null
)
-- Book表
-- 外键所在表为从表(也称参照表,对应参照完整性),创建表时添加外键设置前提是主表已经存在(即组成外键的索引存在)
-- 格式: CONSTRAINT fk_从表名_外键字段名 FOREIGN KEY (从表即本表外键字段名) REFERENCES 主表名(主表被引用字段)
-- 注意:1215 - Cannot add foreign key constraint --无法添加外键约束
-- 1215原因:表级原因:
-- 1.父表不存在,先创建父表，再创建子表
-- 2.其中有一个表是MyISAM引擎(只会建立索引,不会产生外键)的表,要使用外键约束,必须设置表的引擎类型为InnoDB
-- 1215原因:字段级原因(字段设置不严格):
-- 两个字段,主表被引用的字段和从表引用的外键字段
#1.两个字段的类型和字符长度不严格匹配,及有无符号(signed,unsigned)也要严格匹配
#2.设置外键的字段没有建立起索引或在主表中不是主键
#3.外键的名字不能重复,字段名称要唯一
#4.字段是否为null的设置是否一致
#5.确保Charset字符编码设置和Collate在表级和字段级上的一致
#6.两个字段设置的默认值是否一致及填充项是否一致(eg:一个字段default设置为0,另一个为null也不可行)
#7.声明语法错误
 create table Book(
  bid int primary key,
    bname varchar(10) unique not null,
    price float,
    btypeID int,
  CONSTRAINT fk_Book_btypeID FOREIGN KEY (btypeID) REFERENCES booktype(id)
)

1.9.3 删除外键约束

-- 标准语法
ALTER TABLE 表名 DROP FOREIGN KEY 外键名;

-- 删除外键
ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;

1.9.4 建表后添加外键约束

-- 标准语法
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);

-- 添加外键约束
ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id);

只有存储引擎为INNODB才能建立外键，而且不能是临时表表关联字段的编码和排序格式一致外表和从表的列必须同时都有相同类型的索引外表和从表的列必须同时都有相同的数据类型外键关联的字段类型长度要一致

sql 1452 Cannot add or update a child row:a foreign key constraint fails 原因：设置的外键和对应的另一个表的主键值不匹配。解决方法：找出不匹配的值修改。或者清空两表数据

数据的关系比较多的是使用外键去关联的，那直接插入数据到添加了外键的表的时候，那就会出现一个问题找不到这个外键值而报错。这里有两个解决办法： 1、先主表插入数据,再表插入数据集 2、先禁用外键约束 ALTER TABLE 表名 NOCHECK CONSTRAINT 外键约束名
然后插入数据
然后再启用约束
ALTER TABLE 表名 CHECK CONSTRAINT 外键约束名

1.9.5外键的级联更新和级联删除(了解)

什么是级联更新和级联删除
- 当我想把user用户表中的某个用户删掉，我希望该用户所有的订单也随之被删除
- 当我想把user用户表中的某个用户id修改，我希望订单表中该用户所属的订单用户编号也随之修改
添加级联更新和级联删除 ```sql — 添加外键约束，同时添加级联更新标准语法 ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON UPDATE CASCADE;

— 添加外键约束，同时添加级联删除标准语法 ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON DELETE CASCADE;

— 添加外键约束，同时添加级联更新和级联删除标准语法 ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON UPDATE CASCADE ON DELETE CASCADE;

— 删除外键约束 ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;

— 添加外键约束，同时添加级联更新和级联删除 ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id) ON UPDATE CASCADE ON DELETE CASCADE;

— 将王五用户的id修改为5 订单表中的uid也随之被修改 UPDATE USER SET id=5 WHERE id=3;

— 将王五用户删除订单表中该用户所有订单也随之删除 DELETE FROM USER WHERE id=5;


<a name="dQLds"></a>
# 2、数据库设计
<a name="htnQK"></a>
## 2.1  数据库设计简介

-  软件的研发步骤 

![image-20210724130925801.png](https://cdn.nlark.com/yuque/0/2022/png/26775128/1650087952791-e0f6b0c9-972c-4d72-b5ef-37dbba8d37a4.png#clientId=ue2af9a06-5570-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=256&id=u03579c3f&name=image-20210724130925801.png&originHeight=230&originWidth=754&originalType=binary&ratio=1&rotation=0&showTitle=false&size=24841&status=done&style=none&taskId=u597b3ef6-3f76-473e-92f8-ca8f2a328d0&title=&width=838)

-  数据库设计概念 
   - 数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS，为这个业务系统构造出最优的数据存储模型。
   - 建立数据库中的表结构以及表与表之间的关联关系的过程。
   - 有哪些表？表里有哪些字段？表和表之间有什么关系？
-  数据库设计的步骤 
   -  需求分析（数据是什么? 数据具有哪些属性? 数据与属性的特点是什么） 
   -  逻辑分析（通过ER图对数据库进行逻辑建模，不需要考虑我们所选用的数据库管理系统）<br />如下图就是ER(Entity/Relation)图： 

![image-20210724131210759.png](https://cdn.nlark.com/yuque/0/2022/png/26775128/1650088015876-ee118b49-29d3-4125-b3ed-5445eda48c9d.png#clientId=ue2af9a06-5570-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=522&id=u9c1efe80&name=image-20210724131210759.png&originHeight=593&originWidth=973&originalType=binary&ratio=1&rotation=0&showTitle=false&size=420547&status=done&style=none&taskId=ue782bb7a-74fd-44b0-81cf-6f8153c1755&title=&width=857)

   -  物理设计（根据数据库自身的特点把逻辑设计转换为物理设计） 
   -  维护设计（1.对新的需求进行建表；2.表优化） 
-  表关系 
   -  一对一 
      - 如：用户 和 用户详情
      - 一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能

![image-20210724133015129.png](https://cdn.nlark.com/yuque/0/2022/png/26775128/1650088116640-b4066f29-dac0-4ed2-9db8-cfa44e90f984.png#clientId=ue2af9a06-5570-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=416&id=u1c67b09c&name=image-20210724133015129.png&originHeight=520&originWidth=613&originalType=binary&ratio=1&rotation=0&showTitle=false&size=206759&status=done&style=none&taskId=ue1d472af-541e-4645-a6f7-f82338c605e&title=&width=490.4)<br />上图左边是用户的详细信息，而我们真正在展示用户信息时最长用的则是上图右边红框所示，所以我们会将详细信息查分成两周那个表。 

   -  一对多 
      -  如：部门 和 员工 
      -  一个部门对应多个员工，一个员工对应一个部门。如下图： 

         ![image-20210724133443094.png](https://cdn.nlark.com/yuque/0/2022/png/26775128/1650088137333-354c2a67-66dc-4d32-904d-8374304e00aa.png#clientId=ue2af9a06-5570-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=169&id=u430cc7c8&name=image-20210724133443094.png&originHeight=211&originWidth=604&originalType=binary&ratio=1&rotation=0&showTitle=false&size=35791&status=done&style=none&taskId=ub32c4413-a33a-4e77-bc73-a26fa00226a&title=&width=483.2)

   -  多对多 
      -  如：商品 和 订单 
      -  一个商品对应多个订单，一个订单包含多个商品。如下图：  

![image-20210724133704682.png](https://cdn.nlark.com/yuque/0/2022/png/26775128/1650088182950-5a9cdc03-842f-445c-9a03-1dca0aa79c7b.png#clientId=ue2af9a06-5570-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=520&id=ub177df6a&name=image-20210724133704682.png&originHeight=650&originWidth=860&originalType=binary&ratio=1&rotation=0&showTitle=false&size=167618&status=done&style=none&taskId=u4d81ca5d-351f-49f3-9521-255fa998812&title=&width=688)
<a name="P7tHp"></a>
## 2.2  表关系(一对多)

-  一对多 
   - 部门 和 员工；一个部门对应多个员工，一个员工对应一个部门。
   - 用户和订单。一个用户可以有多个订单！
   - 商品分类和商品。一个分类下可以有多个商品！
-  实现方式
   - 在多的一方，建立外键约束，来关联一的一方主键
-  案例<br />我们还是以 `员工表` 和 `部门表` 举例: 

![image-20210724134145803.png](https://cdn.nlark.com/yuque/0/2022/png/26775128/1650088224421-1af15eb6-5b74-42e6-81e6-5b2b5f2f3ef8.png#clientId=ue2af9a06-5570-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=194&id=u41b710ac&name=image-20210724134145803.png&originHeight=243&originWidth=1044&originalType=binary&ratio=1&rotation=0&showTitle=false&size=16563&status=done&style=none&taskId=u14d56796-f3cb-4cde-abc7-e276526ce31&title=&width=835.2)<br />经过分析发现，员工表属于多的一方，而部门表属于一的一方，此时我们会在员工表中添加一列（dep_id），指向于部门表的主键（id）： <br />![image-20210724134318685.png](https://cdn.nlark.com/yuque/0/2022/png/26775128/1650088235021-b5c0059b-fb1f-40fb-a8f4-20644440cd79.png#clientId=ue2af9a06-5570-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=198&id=u77204123&name=image-20210724134318685.png&originHeight=248&originWidth=1076&originalType=binary&ratio=1&rotation=0&showTitle=false&size=82266&status=done&style=none&taskId=u3cd99416-e71e-42a5-b390-318b34be48f&title=&width=860.8)<br />SQL演示 ：

```sql
-- 删除表
DROP TABLE IF EXISTS tb_emp;
DROP TABLE IF EXISTS tb_dept;

-- 部门表
CREATE TABLE tb_dept(
    id int primary key auto_increment,
    dep_name varchar(20),
    addr varchar(20)
);
-- 员工表 
CREATE TABLE tb_emp(
    id int primary key auto_increment,
    name varchar(20),
    age int,
    dep_id int,

    -- 添加外键 dep_id,关联 dept 表的id主键
    CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES tb_dept(id)    
);

查看表结构模型图：

2.3 表关系(多对多)

多对多
- 商品和订单；一个商品对应多个订单，一个订单包含多个商品
- 学生和课程。一个学生可以选择多个课程，一个课程也可以被多个学生选择！
实现原则
- 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
案例
我们以 订单表 和 商品表 举例：

经过分析发现，订单表和商品表都属于多的一方，此时需要创建一个中间表，在中间表中添加订单表的外键和商品表的外键指向两张表的主键：

建表语句如下：

-- 删除表
DROP TABLE IF EXISTS tb_order_goods;
DROP TABLE IF EXISTS tb_order;
DROP TABLE IF EXISTS tb_goods;

-- 订单表
CREATE TABLE tb_order(
    id int primary key auto_increment,
    payment double(10,2),
    payment_type TINYINT,
    status TINYINT
);

-- 商品表
CREATE TABLE tb_goods(
    id int primary key auto_increment,
    title varchar(100),
    price double(10,2)
);

-- 订单商品中间表
CREATE TABLE tb_order_goods(
    id int primary key auto_increment,
    order_id int,
    goods_id int,
    count int
);

-- 建完表后，添加外键
alter table tb_order_goods add CONSTRAINT fk_order_id FOREIGN key(order_id) REFERENCES tb_order(id);
alter table tb_order_goods add CONSTRAINT fk_goods_id FOREIGN key(goods_id) REFERENCES tb_goods(id);

查看表结构模型图：

2.4 表关系(一对一)

一对一
- 用户和用户详情
- 人和身份证。一个人只有一个身份证，一个身份证只能对应一个人！
  
  一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能
实现方式
- 在任意一方加入外键，关联另一方主键，并且设置外键为唯一(UNIQUE)
案例
- 我们以 用户表 举例：

而在真正使用过程中发现 id、photo、nickname、age、gender 字段比较常用，此时就可以将这张表查分成两张表。

建表语句如下：

create table tb_user_desc (
    id int primary key auto_increment,
    city varchar(20),
    edu varchar(10),
    income int,
    status char(2),
    des varchar(100)
);

create table tb_user (
    id int primary key auto_increment,
    photo varchar(100),
    nickname varchar(50),
    age int,
    gender char(1),
    desc_id int unique,
    -- 添加外键
    CONSTRAINT fk_user_desc FOREIGN KEY(desc_id) REFERENCES tb_user_desc(id)    
);

查看表结构模型图：

2.5 数据库设计案例

根据下图设计表及表和表之间的关系：

经过分析，我们分为 专辑表 曲目表 短评表 用户表 4张表。

一个专辑可以有多个曲目，一个曲目只能属于某一张专辑，所以专辑表和曲目表的关系是一对多。

一个专辑可以被多个用户进行评论，一个用户可以对多个专辑进行评论，所以专辑表和用户表的关系是 多对多

一个用户可以发多个短评，一个短评只能是某一个人发的，所以用户表和短评表的关系是一对多。

3、连接查询（多表查询）

3.1 概念

多表查询顾名思义就是从多张表中一次性的查询出我们想要的数据。

3.2 数据准备
SQL语句 ```sql — 创建user表 CREATE TABLE USER( id INT PRIMARY KEY AUTO_INCREMENT, — 用户id NAME VARCHAR(20), — 用户姓名 age INT — 用户年龄 ); — 添加数据 INSERT INTO USER VALUES (1,’张三’,23); INSERT INTO USER VALUES (2,’李四’,24); INSERT INTO USER VALUES (3,’王五’,25); INSERT INTO USER VALUES (4,’赵六’,26);

— 订单表 CREATE TABLE orderlist( id INT PRIMARY KEY AUTO_INCREMENT, — 订单id number VARCHAR(30), — 订单编号 uid INT, — 外键字段 CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id) ); — 添加数据 INSERT INTO orderlist VALUES (1,’lhl001’,1); INSERT INTO orderlist VALUES (2,’lhl002’,1); INSERT INTO orderlist VALUES (3,’lhl003’,2); INSERT INTO orderlist VALUES (4,’lhl004’,2); INSERT INTO orderlist VALUES (5,’lhl005’,3); INSERT INTO orderlist VALUES (6,’lhl006’,3); INSERT INTO orderlist VALUES (7,’lhl007’,NULL);

— 商品分类表 CREATE TABLE category( id INT PRIMARY KEY AUTO_INCREMENT, — 商品分类id NAME VARCHAR(10) — 商品分类名称 ); — 添加数据 INSERT INTO category VALUES (1,’手机数码’); INSERT INTO category VALUES (2,’电脑办公’); INSERT INTO category VALUES (3,’烟酒茶糖’); INSERT INTO category VALUES (4,’鞋靴箱包’);

— 商品表 CREATE TABLE product( id INT PRIMARY KEY AUTO_INCREMENT, — 商品id NAME VARCHAR(30), — 商品名称 cid INT, — 外键字段 CONSTRAINT cp_fk1 FOREIGN KEY (cid) REFERENCES category(id) ); — 添加数据 INSERT INTO product VALUES (1,’华为手机’,1); INSERT INTO product VALUES (2,’小米手机’,1); INSERT INTO product VALUES (3,’联想电脑’,2); INSERT INTO product VALUES (4,’苹果电脑’,2); INSERT INTO product VALUES (5,’中华香烟’,3); INSERT INTO product VALUES (6,’玉溪香烟’,3); INSERT INTO product VALUES (7,’计生用品’,NULL);

— 中间表 CREATE TABLE us_pro( upid INT PRIMARY KEY AUTO_INCREMENT, — 中间表id uid INT, — 外键字段。需要和用户表的主键产生关联 pid INT, — 外键字段。需要和商品表的主键产生关联 CONSTRAINT up_fk1 FOREIGN KEY (uid) REFERENCES USER(id), CONSTRAINT up_fk2 FOREIGN KEY (pid) REFERENCES product(id) );

— 添加数据 INSERT INTO us_pro VALUES (NULL,1,1); INSERT INTO us_pro VALUES (NULL,1,2); INSERT INTO us_pro VALUES (NULL,1,3); INSERT INTO us_pro VALUES (NULL,1,4); INSERT INTO us_pro VALUES (NULL,1,5); INSERT INTO us_pro VALUES (NULL,1,6); INSERT INTO us_pro VALUES (NULL,1,7); INSERT INTO us_pro VALUES (NULL,2,1); INSERT INTO us_pro VALUES (NULL,2,2); INSERT INTO us_pro VALUES (NULL,2,3); INSERT INTO us_pro VALUES (NULL,2,4); INSERT INTO us_pro VALUES (NULL,2,5); INSERT INTO us_pro VALUES (NULL,2,6); INSERT INTO us_pro VALUES (NULL,2,7); INSERT INTO us_pro VALUES (NULL,3,1); INSERT INTO us_pro VALUES (NULL,3,2); INSERT INTO us_pro VALUES (NULL,3,3); INSERT INTO us_pro VALUES (NULL,3,4); INSERT INTO us_pro VALUES (NULL,3,5); INSERT INTO us_pro VALUES (NULL,3,6); INSERT INTO us_pro VALUES (NULL,3,7); INSERT INTO us_pro VALUES (NULL,4,1); INSERT INTO us_pro VALUES (NULL,4,2); INSERT INTO us_pro VALUES (NULL,4,3); INSERT INTO us_pro VALUES (NULL,4,4); INSERT INTO us_pro VALUES (NULL,4,5); INSERT INTO us_pro VALUES (NULL,4,6); INSERT INTO us_pro VALUES (NULL,4,7);


- 架构器图解

![image.png](https://cdn.nlark.com/yuque/0/2022/png/26775128/1650096518606-83d24433-27a3-4401-816f-f247e17282e1.png#clientId=ue2af9a06-5570-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=641&id=JYnSr&name=image.png&originHeight=680&originWidth=801&originalType=binary&ratio=1&rotation=0&showTitle=false&size=52517&status=done&style=none&taskId=u48e1adc9-75f5-490c-a237-50baddfd153&title=&width=754.7999877929688)<br />![image.png](https://cdn.nlark.com/yuque/0/2022/png/26775128/1650098017759-be8e1585-6c68-4c5a-90c9-2f3bebbbb4cd.png#clientId=ue2af9a06-5570-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=578&id=MSTob&name=image.png&originHeight=722&originWidth=1046&originalType=binary&ratio=1&rotation=0&showTitle=false&size=79393&status=done&style=none&taskId=ub439877d-53bc-4cc3-b3c5-4900ab2a140&title=&width=836.8)


<a name="mccZD"></a>
## 3.3 笛卡尔积(交叉连接)

- 有两张表，获取这两个表的所有组合情况
- 笛卡尔积有些数据对我们的查询条件来说是无效数据，为了避免笛卡尔积，可以在 WHERE 加入有效的连接条件。 
```sql
SELECT * FROM tb1 CROSS JOIN tb2;

-- 简写
SELECT * FROM tb1,tb2;

SQL演示

select * from emp , dept;  -- 从emp和dept表中查询所有的字段数据

结果如下：

从上面的结果我们看到有一些无效的数据，如 孙悟空 这个员工属于1号部门，但也同时关联的2、3、4号部门。所以我们要通过限制员工表中的 dep_id 字段的值和部门表 did 字段的值相等来消除这些无效的数据，

select * from emp , dept where emp.dep_id = dept.did;

执行后结果如下：

上面语句就是连接查询，那么多表查询都有哪些呢？

3.4 连接查询分类

连接查询

内连接查询：相当于查询AB交集数据
外连接查询
- 左外连接查询：相当于查询A表所有数据和交集部门数据
- 右外连接查询：相当于查询B表所有数据和交集部分数据
子查询：查询语句中嵌套了查询语句。我们就将嵌套查询称为子查询
自关联查询：同一张表中有数据关联。可以多次查询这同一个表

3.5 内连接查询
查询原理
- 内连接查询的是两张表有交集的部分数据(有主外键关联的数据)
图解

image (6).png

3.5.1 显式内连接

-- 标准语法
SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 条件;

-- 查询用户信息和对应的订单信息
SELECT * FROM USER INNER JOIN orderlist ON user.id=orderlist.uid;
SELECT * FROM USER JOIN orderlist ON user.id=orderlist.uid;

-- 查询用户信息和对应的订单信息，起别名
SELECT * FROM USER u JOIN orderlist o ON u.id=o.uid;

-- 查询用户姓名，年龄。和订单编号
SELECT
    u.`name`,    -- 姓名
    u.`age`,    -- 年龄
    o.`number`    -- 订单编号
FROM
    USER u          -- 用户表
JOIN
    orderlist o     -- 订单表
ON 
    u.`id` = o.`uid`;

3.5.2 隐式内连接

-- 标准语法
SELECT 列名 FROM 表名1,表名2 WHERE 条件;

-- 查询用户姓名，年龄。和订单编号
SELECT
    u.`name`,    -- 姓名
    u.`age`,    -- 年龄
    o.`number`    -- 订单编号
FROM
    USER u,        -- 用户表
    orderlist o     -- 订单表
WHERE
    u.`id`=o.`uid`;

3.6 外连接查询

3.6.1 左外连接

查询原理
- 查询左表的全部数据，和左右两张表有交集部分的数据
图解

左连接.png

基本演示 ```sql — 标准语法 SELECT 列名 FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 条件;

— 查询所有用户信息，以及用户对应的订单信息 SELECT u.name, — 姓名 u.age, — 年龄 o.number — 订单编号 FROM USER u — 用户表 LEFT OUTER JOIN orderlist o — 订单表 ON u.id=o.uid;

<a name="LjXlt"></a>
### 3.6.2 右外连接

- 查询原理
   - 查询右表的全部数据，和左右两张表有交集部分的数据
- 图解

![image (9).png](https://cdn.nlark.com/yuque/0/2022/png/26775128/1650132287682-facb18a8-b38a-4020-b9e5-23c5601716af.png#clientId=u5b16b37c-3d86-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=186&id=u0f6c2137&name=image%20%289%29.png&originHeight=233&originWidth=300&originalType=binary&ratio=1&rotation=0&showTitle=false&size=19565&status=done&style=none&taskId=uf2460b8c-f4fa-4b77-ab09-2520adf4712&title=&width=240)

- 基本演示
```sql
-- 基本语法
SELECT 列名 FROM 表名1 RIGHT [OUTER] JOIN 表名2 ON 条件;

-- 查询所有订单信息，以及订单所属的用户信息
SELECT
    u.`name`,    -- 姓名
    u.`age`,    -- 年龄
    o.`number`    -- 订单编号
FROM
    USER u          -- 用户表
RIGHT OUTER JOIN
    orderlist o     -- 订单表
ON
    u.`id`=o.`uid`;

3.6.3 左右外连接区别

左外连接和右外连接的区别：

左外连接也称左连接。以左表为基表，在FROM子句中使用关键字“LEFT OUTER JOIN”或关键字“LEFT JOIN”来连接俩张表。

右外连接也称右连接。以右表为基表，在FROM子句中使用关键字“RIGHT OUTER JOIN”或关键字“RIGHT JOIN”来连接俩张表。

3.6.4 内连接与外连接区别

内连接：inner join(等值连接) 只返回两个表中联结字段相等的数据

外连接：返回包括左/右表中的所有记录和右/左表中联结字段相等的记录

3.7 子查询

谁的工资比 Abel 高?

子查询介绍
- 查询语句中嵌套了查询语句。我们就将嵌套查询称为子查询！
- 子查询在主查询之前一次执行完成。子查询的结果被主查询使用
  3.7.1 子查询-结果是单行单列的
可以作为条件，使用运算符进行判断！
基本演示 ```sql — 标准语法 SELECT 列名 FROM 表名 WHERE 列名=(SELECT 聚合函数(列名) FROM 表名 [WHERE 条件]);

— 查询年龄最高的用户姓名 SELECT MAX(age) FROM USER; — 查询出最高年龄 SELECT NAME,age FROM USER WHERE age=26; — 根据查询出来的最高年龄，查询姓名和年龄 SELECT NAME,age FROM USER WHERE age = (SELECT MAX(age) FROM USER);


<a name="gNmgt"></a>
### 3.7.2 子查询-结果是多行单列的

- 可以作为条件，使用运算符in或not in进行判断！
- 基本演示
```sql
-- 标准语法
SELECT 列名 FROM 表名 WHERE 列名 [NOT] IN (SELECT 列名 FROM 表名 [WHERE 条件]); 

-- 查询张三和李四的订单信息
SELECT id FROM USER WHERE NAME='张三' OR NAME='李四';   -- 查询张三和李四用户的id
SELECT number,uid FROM orderlist WHERE uid=1 OR uid=2; -- 根据id查询订单
SELECT number,uid FROM orderlist WHERE uid IN (SELECT id FROM USER WHERE NAME='张三' OR NAME='李四');

3.7.3 子查询-结果是多行多列的

-- 标准语法
SELECT 列名 FROM 表名 [别名],(SELECT 列名 FROM 表名 [WHERE 条件]) [别名] [WHERE 条件];

-- 查询订单表中id大于4的订单信息和所属用户信息
SELECT * FROM USER u,(SELECT * FROM orderlist WHERE id>4) o WHERE u.id=o.uid;

3.8 多表查询练习

查询用户的编号、姓名、年龄。订单编号

/*
分析：
  用户的编号、姓名、年龄  user表     订单编号 orderlist表
  条件：user.id = orderlist.uid
*/
SELECT
  t1.`id`,    -- 用户编号
  t1.`name`,    -- 用户姓名
  t1.`age`,    -- 用户年龄
  t2.`number`    -- 订单编号
FROM
  USER t1,       -- 用户表
  orderlist t2   -- 订单表
WHERE
  t1.`id` = t2.`uid`;

查询所有的用户。用户的编号、姓名、年龄。订单编号

/*
分析：
  用户的编号、姓名、年龄 user表     订单编号 orderlist表
  条件：user.id = orderlist.uid
  查询所有用户，使用左外连接
*/
SELECT
  t1.`id`,    -- 用户编号
  t1.`name`,    -- 用户姓名
  t1.`age`,    -- 用户年龄
  t2.`number`    -- 订单编号
FROM
  USER t1        -- 用户表
LEFT OUTER JOIN
  orderlist t2   -- 订单表
ON
  t1.`id` = t2.`uid`;

查询所有的订单。用户的编号、姓名、年龄。订单编号

/*
分析：
  用户的编号、姓名、年龄 user表     订单编号 orderlist表
  条件：user.id = orderlist.uid
  查询所有订单，使用右外连接
*/
SELECT
  t1.`id`,    -- 用户编号
  t1.`name`,    -- 用户姓名
  t1.`age`,    -- 用户年龄
  t2.`number`    -- 订单编号
FROM
  USER t1         -- 用户表
RIGHT OUTER JOIN
  orderlist t2    -- 订单表
ON
  t1.`id` = t2.`uid`;

查询用户年龄大于23岁的信息。显示用户的编号、姓名、年龄。订单编号

/*
分析：
  用户的编号、姓名、年龄 user表     订单编号 orderlist表
  条件：user.age > 23 AND user.id = orderlist.uid
*/
/*
select
  t1.`id`,    -- 用户编号
  t1.`name`,    -- 用户姓名
  t1.`age`,    -- 用户年龄
  t2.`number`    -- 订单编号
from
  user t1,     -- 用户表
  orderlist t2 -- 订单表
where
  t1.`age` > 23
  and
  t1.`id` = t2.`uid`;
*/
SELECT
  t1.`id`,    -- 用户编号
  t1.`name`,    -- 用户姓名
  t1.`age`,    -- 用户年龄
  t2.`number`    -- 订单编号
FROM
  USER t1       -- 用户表
LEFT OUTER JOIN
  orderlist t2  -- 订单表
ON
  t1.`id` = t2.`uid`
WHERE
  t1.`age` > 23;

查询张三和李四用户的信息。显示用户的编号、姓名、年龄。订单编号

/*
分析：
  用户的编号、姓名、年龄 user表     订单编号 orderlist表
  条件：user.id = orderlist.uid AND user.name IN ('张三','李四');
*/
SELECT
  t1.`id`,    -- 用户编号
  t1.`name`,    -- 用户姓名
  t1.`age`,    -- 用户年龄
  t2.`number`    -- 订单编号
FROM
  USER t1,        -- 用户表
  orderlist t2    -- 订单表
WHERE
  t1.`id` = t2.`uid`
  AND
  -- (t1.`name` = '张三' OR t1.`name` = '李四');
  t1.`name` IN ('张三','李四');

查询商品分类的编号、分类名称。分类下的商品名称

/*
分析：
  商品分类的编号、分类名称 category表     分类下的商品名称 product表
  条件：category.id = product.cid
*/
SELECT
  t1.`id`,    -- 分类编号
  t1.`name`,    -- 分类名称
  t2.`name`    -- 商品名称
FROM
  category t1,    -- 商品分类表
  product t2        -- 商品表
WHERE
  t1.`id` = t2.`cid`;

查询所有的商品分类。商品分类的编号、分类名称。分类下的商品名称

/*
分析：
  商品分类的编号、分类名称 category表     分类下的商品名称 product表
  条件：category.id = product.cid
  查询所有的商品分类，使用左外连接
*/
SELECT
  t1.`id`,    -- 分类编号
  t1.`name`,    -- 分类名称
  t2.`name`    -- 商品名称
FROM
  category t1    -- 商品分类表
LEFT OUTER JOIN
  product t2    -- 商品表
ON
  t1.`id` = t2.`cid`;

查询所有的商品信息。商品分类的编号、分类名称。分类下的商品名称

/*
分析：
  商品分类的编号、分类名称 category表     分类下的商品名称 product表
  条件：category.id = product.cid
  查询所有的商品信息，使用右外连接
*/
SELECT
  t1.`id`,    -- 分类编号
  t1.`name`,    -- 分类名称
  t2.`name`    -- 商品名称
FROM
  category t1    -- 商品分类表
RIGHT OUTER JOIN
  product t2    -- 商品表
ON
  t1.`id` = t2.`cid`;

查询所有的用户和所有的商品。显示用户的编号、姓名、年龄。商品名称

/*
分析：
  用户的编号、姓名、年龄 user表   商品名称 product表   中间表 us_pro
  条件：us_pro.uid = user.id AND us_pro.pid = product.id
*/
SELECT
  t1.`id`,    -- 用户编号
  t1.`name`,    -- 用户名称
  t1.`age`,    -- 用户年龄
  t2.`name`    -- 商品名称
FROM
  USER t1,    -- 用户表
  product t2,    -- 商品表
  us_pro t3    -- 中间表
WHERE
  t3.`uid` = t1.`id`
  AND
  t3.`pid` = t2.`id`;

查询张三和李四这两个用户可以看到的商品。显示用户的编号、姓名、年龄。商品名称

/*
分析：
  用户的编号、姓名、年龄 user表   商品名称 product表   中间表 us_pro
  条件：us_pro.uid = user.id AND us_pro.pid = product.id AND user.name IN ('张三','李四')
*/
SELECT
  t1.`id`,    -- 用户编号
  t1.`name`,    -- 用户名称
  t1.`age`,    -- 用户年龄
  t2.`name`    -- 商品名称
FROM
  USER t1,    -- 用户表
  product t2,    -- 商品表
  us_pro t3    -- 中间表
WHERE
  (t3.`uid` = t1.`id` AND t3.`pid` = t2.`id`)
  AND
  -- (t1.`name` = '张三' or t1.`name` = '李四');
  t1.`name` IN ('张三','李四');

3.9 自关联查询

自关联查询介绍
- 同一张表中有数据关联。可以多次查询这同一个表！
自关联查询演示 ```sql — 创建员工表 CREATE TABLE employee( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20), mgr INT, salary DOUBLE ); — 添加数据 INSERT INTO employee VALUES (1001,’孙悟空’,1005,9000.00), (1002,’猪八戒’,1005,8000.00), (1003,’沙和尚’,1005,8500.00), (1004,’小白龙’,1005,7900.00), (1005,’唐僧’,NULL,15000.00), (1006,’武松’,1009,7600.00), (1007,’李逵’,1009,7400.00), (1008,’林冲’,1009,8100.00), (1009,’宋江’,NULL,16000.00);

— 查询所有员工的姓名及其直接上级的姓名，没有上级的员工也需要查询 / 分析：员工姓名 employee表直接上级姓名 employee表条件：employee.mgr = employee.id 查询左表的全部数据，和左右两张表交集部分数据，使用左外连接 / SELECT t1.name, — 员工姓名 t1.mgr, — 上级编号 t2.id, — 员工编号 t2.name — 员工姓名 FROM employee t1 — 员工表 LEFT OUTER JOIN employee t2 — 员工表 ON t1.mgr = t2.id;

<a name="Mh3G6"></a>
## 3.10  多表查询综合（1）

- 环境准备：

```sql
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
DROP TABLE IF EXISTS job;
DROP TABLE IF EXISTS salarygrade;

-- 部门表
CREATE TABLE dept (
  did INT PRIMARY KEY PRIMARY KEY, -- 部门id
  dname VARCHAR(50), -- 部门名称
  loc VARCHAR(50) -- 部门所在地
);

-- 职务表，职务名称，职务描述
CREATE TABLE job (
  id INT PRIMARY KEY,
  jname VARCHAR(20),
  description VARCHAR(50)
);

-- 员工表
CREATE TABLE emp (
  id INT PRIMARY KEY, -- 员工id
  ename VARCHAR(50), -- 员工姓名
  job_id INT, -- 职务id
  mgr INT , -- 上级领导
  joindate DATE, -- 入职日期
  salary DECIMAL(7,2), -- 工资
  bonus DECIMAL(7,2), -- 奖金
  dept_id INT, -- 所在部门编号
  CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
  CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
-- 工资等级表
CREATE TABLE salarygrade (
  grade INT PRIMARY KEY,   -- 级别
  losalary INT,  -- 最低工资
  hisalary INT -- 最高工资
);

-- 添加4个部门
INSERT INTO dept(did,dname,loc) VALUES 
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');

-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');


-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);


-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

需求
1. 查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述 ```sql /* 分析：
  1. 员工编号，员工姓名，工资信息在emp 员工表中
  2. 职务名称，职务描述信息在 job 职务表中
  3. job 职务表和 emp 员工表是一对多的关系 emp.job_id = job.id */ — 方式一：隐式内连接 SELECT emp.id, emp.ename, emp.salary, job.jname, job.description FROM emp, job WHERE emp.job_id = job.id;

— 方式二：显式内连接 SELECT emp.id, emp.ename, emp.salary, job.jname, job.description FROM emp INNER JOIN job ON emp.job_id = job.id;



   2.  查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置 
```sql
/*
    分析：
        1. 员工编号，员工姓名，工资 信息在emp 员工表中
        2. 职务名称，职务描述 信息在 job 职务表中
        3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id

        4. 部门名称，部门位置 来自于 部门表 dept
        5. dept 和 emp 一对多关系 dept.id = emp.dept_id
*/

-- 方式一 ：隐式内连接
SELECT
    emp.id,
    emp.ename,
    emp.salary,
    job.jname,
    job.description,
    dept.dname,
    dept.loc
FROM
    emp,
    job,
    dept
WHERE
    emp.job_id = job.id
    and dept.id = emp.dept_id
;

-- 方式二 ：显式内连接
SELECT
    emp.id,
    emp.ename,
    emp.salary,
    job.jname,
    job.description,
    dept.dname,
    dept.loc
FROM
    emp
INNER JOIN job ON emp.job_id = job.id
INNER JOIN dept ON dept.id = emp.dept_id

查询员工姓名，工资，工资等级

/*
分析：
 1. 员工姓名，工资 信息在emp 员工表中
 2. 工资等级 信息在 salarygrade 工资等级表中
 3. emp.salary >= salarygrade.losalary  and emp.salary <= salarygrade.hisalary
*/
SELECT
emp.ename,
emp.salary,
t2.*
FROM
emp,
salarygrade t2
WHERE
emp.salary >= t2.losalary
AND emp.salary <= t2.hisalary

查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

/*
分析：
 1. 员工编号，员工姓名，工资 信息在emp 员工表中
 2. 职务名称，职务描述 信息在 job 职务表中
 3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id

 4. 部门名称，部门位置 来自于 部门表 dept
 5. dept 和 emp 一对多关系 dept.id = emp.dept_id
 6. 工资等级 信息在 salarygrade 工资等级表中
 7. emp.salary >= salarygrade.losalary  and emp.salary <= salarygrade.hisalary
*/
SELECT
emp.id,
emp.ename,
emp.salary,
job.jname,
job.description,
dept.dname,
dept.loc,
t2.grade
FROM
emp
INNER JOIN job ON emp.job_id = job.id
INNER JOIN dept ON dept.id = emp.dept_id
INNER JOIN salarygrade t2 ON emp.salary BETWEEN t2.losalary and t2.hisalary;

查询出部门编号、部门名称、部门位置、部门人数 ```sql /* 分析：
1. 部门编号、部门名称、部门位置来自于部门 dept 表
2. 部门人数: 在emp表中按照dept_id 进行分组，然后count(*)统计数量
3. 使用子查询，让部门表和分组后的表进行内连接 / — 根据部门id分组查询每一个部门id和员工数 select dept_id, count() from emp group by dept_id;

SELECT dept.id, dept.dname, dept.loc, t1.count FROM dept, ( SELECT dept_id, count(*) count FROM emp GROUP BY dept_id ) t1 WHERE dept.id = t1.dept_id




<a name="H6Xni"></a>
# 4、视图
<a name="zuc6Y"></a>
## 4.1 视图的概念

- 视图是一种虚拟存在的数据表
- 这个虚拟的表并不在数据库中实际存在
- 作用是将一些比较复杂的查询语句的结果，封装到一个虚拟表中。后期再有相同复杂查询时，直接查询这张虚拟表即可
- 说白了，**视图就是将一条SELECT查询语句的结果封装到了一个虚拟表中**，所以我们在创建视图的时候，工作重心就要放在这条SELECT查询语句上

<a name="ttHat"></a>
## 4.2 视图的好处

- 简单 
   - 对于使用视图的用户不需要关心表的结构、关联条件和筛选条件。因为这张虚拟表中保存的就是已经过滤好条件的结果集
- 安全 
   - 视图可以设置权限 , 致使访问视图的用户只能访问他们被允许查询的结果集
- 数据独立 
   - 一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响

<a name="ajY0j"></a>
## 4.3 视图数据准备

```sql
-- 创建country表
CREATE TABLE country(
  id INT PRIMARY KEY AUTO_INCREMENT,
  country_name VARCHAR(30)
);
-- 添加数据
INSERT INTO country VALUES (NULL,'中国'),(NULL,'美国'),(NULL,'俄罗斯');

-- 创建city表
CREATE TABLE city(
  id INT PRIMARY KEY AUTO_INCREMENT,
  city_name VARCHAR(30),
  cid INT, -- 外键列。关联country表的主键列id
  CONSTRAINT cc_fk1 FOREIGN KEY (cid) REFERENCES country(id)
);
-- 添加数据
INSERT INTO city VALUES (NULL,'北京',1),(NULL,'上海',1),(NULL,'纽约',2),(NULL,'莫斯科',3);

4.4 视图的创建

创建视图语法

-- 标准语法
CREATE VIEW 视图名称 [(列名列表)] AS 查询语句;

普通多表查询，查询城市和所属国家

-- 普通多表查询，查询城市和所属国家
SELECT
t1.*,
t2.country_name
FROM
city t1,
country t2
WHERE
t1.cid = t2.id;

-- 经常需要查询这样的数据，就可以创建一个视图

创建视图基本演示

-- 创建一个视图。将查询出来的结果保存到这张虚拟表中
CREATE
VIEW
city_country
AS
    SELECT t1.*,t2.country_name FROM city t1,country t2 WHERE t1.cid=t2.id;

创建视图并指定列名基本演示

-- 创建一个视图，指定列名。将查询出来的结果保存到这张虚拟表中
CREATE
VIEW
city_country2 (city_id,city_name,cid,country_name) 
AS
    SELECT t1.*,t2.country_name FROM city t1,country t2 WHERE t1.cid=t2.id;

4.5 视图的查询

查询视图语法

-- 标准语法
SELECT * FROM 视图名称;

查询视图基本演示

-- 查询视图。查询这张虚拟表，就等效于查询城市和所属国家
SELECT * FROM city_country;

-- 查询指定列名的视图
SELECT * FROM city_country2;

-- 查询所有数据表，视图也会查询出来
SHOW TABLES;

查询视图创建语法

-- 标准语法
SHOW CREATE VIEW 视图名称;

查询视图创建语句基本演示

SHOW CREATE VIEW city_country;

4.6 视图的修改

修改视图表中的数据

-- 标准语法
UPDATE 视图名称 SET 列名=值 WHERE 条件;

-- 修改视图表中的城市名称北京为北京市
UPDATE city_country SET city_name='北京市' WHERE city_name='北京';

-- 查询视图
SELECT * FROM city_country;

-- 查询city表,北京也修改为了北京市
SELECT * FROM city;

-- 注意：视图表数据修改，会自动修改源表中的数据

修改视图表结构

-- 标准语法
ALTER VIEW 视图名称 [(列名列表)] AS 查询语句;

-- 查询视图2
SELECT * FROM city_country2;

-- 修改视图2的列名city_id为id
ALTER
VIEW
    city_country2 (id,city_name,cid,country_name)
AS
    SELECT t1.*,t2.country_name FROM city t1,country t2 WHERE t1.cid=t2.id;

4.7 视图的删除

删除视图

-- 标准语法
DROP VIEW [IF EXISTS] 视图名称;

-- 删除视图
DROP VIEW city_country;

-- 删除视图2，如果存在则删除
DROP VIEW IF EXISTS city_country2;

4.8 视图的总结

视图是一种虚拟存在的数据表
这个虚拟的表并不在数据库中实际存在
说白了，视图就是将一条SELECT查询语句的结果封装到了一个虚拟表中，所以我们在创建视图的时候，工作重心就要放在这条SELECT查询语句上
视图的好处
- 简单
- 安全
- 数据独立

5、事务

5.1 概念

数据库的事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令。
事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败。
事务是一个不可分割的工作逻辑单元。

这些概念不好理解，接下来举例说明，如下图有一张表

张三和李四账户中各有100块钱，现李四需要转换500块钱给张三，具体的转账操作为

第一步：查询李四账户余额
第二步：从李四账户金额 -500
第三步：给张三账户金额 +500

现在假设在转账过程中第二步完成后出现了异常第三步没有执行，就会造成李四账户金额少了500，而张三金额并没有多500；这样的系统是有问题的。如果解决呢？使用事务可以解决上述问题

从上图可以看到在转账前开启事务，如果出现了异常回滚事务，三步正常执行就提交事务，这样就可以完美解决问题。

5.2 语法

开启事务
```
START TRANSACTION;
或者  
BEGIN;
```

提交事务
```
commit;
```

回滚事务
```
rollback;
```

5.3 数据准备

环境准备 ```sql DROP TABLE IF EXISTS account;

— 创建账户表 CREATE TABLE account( id int PRIMARY KEY auto_increment, name varchar(10), money double(10,2) );

— 添加数据 INSERT INTO account(name,money) values(‘张三’,1000),(‘李四’,1000);


<a name="tJoEQ"></a>
##  5.4 不加事务演示问题 
```sql
-- 转账操作
-- 1. 查询李四账户金额是否大于500

-- 2. 李四账户 -500
UPDATE account set money = money - 500 where name = '李四';

出现异常了...  -- 此处不是注释，在整体执行时会出问题，后面的sql则不执行
-- 3. 张三账户 +500
UPDATE account set money = money + 500 where name = '张三';

整体执行结果肯定会出问题，我们查询账户表中数据，发现李四账户少了500。

5.5 管理事务演示

```sql — 开启事务 BEGIN; — 转账操作 — 1. 查询李四账户金额是否大于500

— 2. 李四账户 -500 UPDATE account set money = money - 500 where name = ‘李四’;

出现异常了… — 此处不是注释，在整体执行时会出问题，后面的sql则不执行 — 3. 张三账户 +500 UPDATE account set money = money + 500 where name = ‘张三’;

— 提交事务 COMMIT;

— 回滚事务 ROLLBACK;

<br />上面sql中的执行成功进选择执行提交事务，而出现问题则执行回滚事务的语句。以后我们肯定不可能这样操作，而是在java中进行操作，在java中可以抓取异常，没出现异常提交事务，出现异常回滚事务。 

<a name="HVsbJ"></a>
## 5.6 事务的提交方式
提交方式

- 自动提交(MySQL默认为自动提交)
- 手动提交

修改提交方式

- 查看提交方式
```sql
-- 查看提交方式
-- 1代表自动提交    0代表手动提交
SELECT @@autocommit;

修改提交方式
```
-- 修改为手动提交
set @@autocommit = 0;
```
说明： mysql中事务是自动提交的。也就是说我们不添加事务执行sql语句，语句执行完毕会自动的提交事务。可以通过下面语句查询默认提交方式：查询到的结果是1 则表示自动提交，结果是0表示手动提交。当然也可以通过下面语句修改提交方式

5.7 事务的四大特征（ACID）

原子性（Atomicity）:
- 事务是不可分割的最小操作单位，要么同时成功，要么同时失败；因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响
一致性（Consistency） :事务完成时，必须使所有的数据都保持一致状态
- 一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态
- 拿转账来说，假设张三和李四两者的钱加起来一共是2000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是2000，这就是事务的一致性
隔离性（Isolation） :多个事务之间，操作的可见性
- 隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离
持久性（Durability）：
- 事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

5.8 事物的隔离级别

隔离级别的概念
- 多个客户端操作时 ,各个客户端的事务之间应该是隔离的，相互独立的 , 不受影响的。
- 而如果多个事务操作同一批数据时，则需要设置不同的隔离级别 , 否则就会产生问题。
- 我们先来了解一下四种隔离级别的名称 , 再来看看可能出现的问题
四种隔离级别 | 1 | 读未提交 | read uncommitted | | —- | —- | —- | | 2 | 读已提交 | read committed | | 3 | 可重复读 | repeatable read | | 4 | 串行化 | serializable |
可能引发的问题 | 问题 | 现象 | | —- | —- | | 脏读 | 是指在一个事务处理过程中读取了另一个未提交的事务中的数据 , 导致两次查询结果不一致 | | 不可重复读 | 是指在一个事务处理过程中读取了另一个事务中修改并已提交的数据, 导致两次查询结果不一致 | | 幻读 | select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入。或不存在执行delete删除，却发现删除成功 |
查询数据库隔离级别
```
-- 标准语法
SELECT @@TX_ISOLATION;
```
修改数据库隔离级别 ```sql — 标准语法 SET GLOBAL TRANSACTION ISOLATION LEVEL 级别字符串;

— 修改数据库隔离级别为read uncommitted SET GLOBAL TRANSACTION ISOLATION LEVEL read uncommitted;

— 查看隔离级别 SELECT @@TX_ISOLATION; — 修改后需要断开连接重新开

<a name="MiTiF"></a>
## 5.9 事务隔离级别演示
脏读的问题

- 窗口1
```sql
-- 查询账户表
select * from account;

-- 设置隔离级别为read uncommitted
set global transaction isolation level read uncommitted;

-- 开启事务
start transaction;

-- 转账
update account set money = money - 500 where id = 1;
update account set money = money + 500 where id = 2;

-- 窗口2查询转账结果 ,出现脏读(查询到其他事务未提交的数据)

-- 窗口2查看转账结果后，执行回滚
rollback;

窗口2 ```sql — 查询隔离级别 select @@tx_isolation;

— 开启事务 start transaction;

— 查询账户表 select * from account;

解决脏读的问题和演示不可重复读的问题

   - 窗口1
```sql
-- 设置隔离级别为read committed
set global transaction isolation level read committed;

-- 开启事务
start transaction;

-- 转账
update account set money = money - 500 where id = 1;
update account set money = money + 500 where id = 2;

-- 窗口2查看转账结果，并没有发生变化(脏读问题被解决了)

-- 执行提交事务。
commit;

-- 窗口2查看转账结果，数据发生了变化(出现了不可重复读的问题，读取到其他事务已提交的数据)

窗口2 ```sql — 查询隔离级别 select @@tx_isolation;

— 开启事务 start transaction;

— 查询账户表 select * from account;


- 解决不可重复读的问题
   - 窗口1
```sql
-- 设置隔离级别为repeatable read
set global transaction isolation level repeatable read;

-- 开启事务
start transaction;

-- 转账
update account set money = money - 500 where id = 1;
update account set money = money + 500 where id = 2;

-- 窗口2查看转账结果，并没有发生变化

-- 执行提交事务
commit;

-- 这个时候窗口2只要还在上次事务中，看到的结果都是相同的。只有窗口2结束事务，才能看到变化(不可重复读的问题被解决)

窗口2 ```sql — 查询隔离级别 select @@tx_isolation;

— 开启事务 start transaction;

— 查询账户表 select * from account;

— 提交事务 commit;

— 查询账户表 select * from account;

幻读的问题和解决

   - 窗口1
```sql
-- 设置隔离级别为repeatable read
set global transaction isolation level repeatable read;

-- 开启事务
start transaction;

-- 添加一条记录
INSERT INTO account VALUES (3,'王五',1500);

-- 查询账户表，本窗口可以查看到id为3的结果
SELECT * FROM account;

-- 提交事务
COMMIT;

窗口2 ```sql — 查询隔离级别 select @@tx_isolation;

— 开启事务 start transaction;

— 查询账户表，查询不到新添加的id为3的记录 select * from account;

— 添加id为3的一条数据，发现添加失败。出现了幻读 INSERT INTO account VALUES (3,’测试’,200);

— 提交事务 COMMIT;

— 查询账户表，查询到了新添加的id为3的记录 select * from account;


   - 解决幻读的问题
```sql
/*
    窗口1
*/
-- 设置隔离级别为serializable
set global transaction isolation level serializable;

-- 开启事务
start transaction;

-- 添加一条记录
INSERT INTO account VALUES (4,'赵六',1600);

-- 查询账户表，本窗口可以查看到id为4的结果
SELECT * FROM account;

-- 提交事务
COMMIT;



/*
    窗口2
*/
-- 查询隔离级别
select @@tx_isolation;

-- 开启事务
start transaction;

-- 查询账户表，发现查询语句无法执行，数据表被锁住！只有窗口1提交事务后，才可以继续操作
select * from account;

-- 添加id为4的一条数据，发现已经存在了，就不会再添加了！幻读的问题被解决
INSERT INTO account VALUES (4,'测试',200);

-- 提交事务
COMMIT;

5.10 隔离级别总结

	隔离级别	名称	出现脏读	出现不可重复读	出现幻读	数据库默认隔离级别
1	read uncommitted	读未提交	是	是	是
2	read committed	读已提交	否	是	是	Oracle / SQL Server
3	repeatable read	可重复读	否	否	是	MySQL
4	serializable	串行化	否	否	否

注意：隔离级别从小到大安全性越来越高，但是效率越来越低 , 所以不建议使用READ UNCOMMITTED 和 SERIALIZABLE 隔离级别.

MySQL 进阶

1、约束

1.1 约束的概念

1.2 约束的分类

1.3 非空约束

1.3.1 建表时添加非空约束

1.3.3 建表后单独添加非空约束

1.4 唯一约束

1.4.1 建表时添加唯一约束

1.4.3 建表后单独添加唯一约束

1.5 主键约束

1.5.1 建表时添加主键约束

1.5.3 建表后单独添加主键

1.6 主键自动增长约束

1.6.1 建表时添加主键自增约束

1.6.2 删除自动增长

1.6.3 建表后单独添加自动增长

1.7 默认约束

1.7.1 创建表时添加默认约束

1.7.2 建完表后添加默认约束

1.7.3 删除约束

1.8 约束练习

1.9 外键约束

1.9.1 概述

1.9.2 建表时添加外键约束

1.9.3 删除外键约束

1.9.4 建表后添加外键约束

1.9.5外键的级联更新和级联删除(了解)

2.3 表关系(多对多)

2.4 表关系(一对一)

2.5 数据库设计案例

3、连接查询（多表查询）

3.1 概念

3.2 数据准备

3.4 连接查询分类

3.5 内连接查询

3.5.1 显式内连接

3.5.2 隐式内连接

3.6 外连接查询

3.6.1 左外连接

3.6.3 左右外连接区别

3.6.4 内连接与外连接区别

3.7 子查询

3.7.1 子查询-结果是单行单列的

3.7.3 子查询-结果是多行多列的

3.8 多表查询练习

3.9 自关联查询

4.4 视图的创建

4.5 视图的查询

4.6 视图的修改

4.7 视图的删除

4.8 视图的总结

5、事务

5.1 概念

5.2 语法

5.3 数据准备

5.5 管理事务演示

5.7 事务的四大特征（ACID）

5.8 事物的隔离级别

5.10 隔离级别总结