《MySQL初级系列教程-第二章-DQL、约束、表关系》

电玩女神 2022-09-04 05:54 151阅读 0赞

# 《MySQL初级系列教程-第二章-DQL、约束、表关系》 #

# 一、DQL查询语句 #

## 1.1 排序 ##

*  准备数据：

use db02;
    
    drop table if exists student ;
    
    CREATE TABLE student (  
        id int, -- 编号 
        `name` varchar(20), -- 姓名 
        age int, -- 年龄 
        sex char(1), -- 性别 
        address varchar(100), -- 地址 
        math int, -- 数学 
        english int -- 英语 
    );  
    
    -- 插入数据
    insert into student values(1,'小明',24,'男','湖北武汉',90,100);
    insert into student values(2,'小红',25,'女','湖南长沙',88,69);
    insert into student values(3,'小龙',26,'男','江西南昌',78,80);
    insert into student values(4,'小丽',24,'女','安徽合肥',95,80);
    insert into student values(5,'张三',19,'男','福建福州',80,90);
    insert into student values(6,'李四',24,'男','广东广州',100,95);
    insert into student values(7,'王五',24,'男','河南郑州',90,95);

> 排序本身不会影响到表中的记录位置，只是查询结果变成有序的。默认是升序，从小到大。 数字和字符都有大小的。 汉字默认按拼音顺序

*  语法：

SELECT * FROM 表名 ORDER BY 字段名 [ASC/DESC]

升序: ASC（默认值）

降序: DESC

### 1.1.1 单列排序 ###

-- 查询所有数据,使用年龄升序排序
    select * from student order by age asc;
    
    -- 排序默认是升序(asc),因此asc也可以不写
    select * from student order by age asc;
    
    -- 查询所有数据,使用年龄降序排序
    select * from student order by age desc;

### 1.1.2 组合排序 ###

*  语法：

SELECT * FROM 表名 ORDER BY 字段名1 [ASC/DESC]，字段名2[ASC/DESC] 先按字段名1进行排序，如果按1排序值相同，再按字段名2进行排序

-- 查询所有数据大于20岁的学生,在年龄降序排序的基础上,如果年龄相同再以数学成绩升序排序
    select * from student where age > 22 order by age desc, math asc;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center]

### 1.1.3 按照拼音排序 ###

select * from student order by name asc;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 1]

## 1.2 聚合函数 ##

之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个结果值。聚合函数会忽略空值。

### 1.2.1 五个聚合函数 ###

<table> 
 <thead> 
  <tr> 
   <th>SQL中的聚合函数</th> 
   <th>作用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>count</td> 
   <td>统计个数，如果这一列有NULL，null不会参与统计</td> 
  </tr> 
  <tr> 
   <td>max</td> 
   <td>找这一列中的最大值，一般是数值类型进行操作。</td> 
  </tr> 
  <tr> 
   <td>min</td> 
   <td>找这一列中的最小值</td> 
  </tr> 
  <tr> 
   <td>sum</td> 
   <td>求这一列的总和</td> 
  </tr> 
  <tr> 
   <td>avg</td> 
   <td>求这一列的平均，返回值小数average</td> 
  </tr> 
 </tbody> 
</table>

### 1.2.2 语法： ###

SELECT 聚合函数(字段名) FROM 表

*  示例：

-- 查询学生人数总数
    select count(id) sum from student;
    
    -- 查询年龄大于24的总数
    select count(*) from student where age > 24;
    
    -- 查询数学成绩总分
    select sum(math) from student;
    
    -- 查询学生的平均年龄
    select avg(age) from student;
    
    -- 查询数学成绩最高的分数
    select max(age) from student;
    
    -- 查询数学成绩最低分
    select min(math) from student;

## 1.3 分组 ##

可以将表中的数据按照某个字段分组，分成不同的组之后再使用聚合函数进行计算；

*  语法：

SELECT * FROM 表名 WHERE 条件 GROUP BY 字段名 [HAVING 条件] GROUP BY 分组 HAVING 分组以后得到结果再进行过滤

*  `GROUP BY`如何分组的？

将分组字段结果中相同内容作为一组，如按性别将学生分成2组。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 2]

分组一般搭配聚合函数一起使用

例如：求每个性别的下的数学总成绩，我们通过上述图也能理解，聚合之后查询全部数据是毫无意义的

-- 求每个性别的下的数学总成绩
    
    select sex,sum(math) 数学总成绩 from student group by sex;

*  效果如下：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 3]

实际上是将每组的math进行求和,返回每组统计的结果

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 4]

*  查询男女各多少人

1.  查询所有数据,按性别分组。
2.  统计每组人数

select sex, count(*) from student group by sex;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 5]

*  查询年龄大于23岁的人，按性别分组，统计每组的人数

1.  先查询年龄出年龄大于23岁的人。
2.  再分组。
3.  最后统计每组的人数

select sex, count(*) from student where age>23 group by sex;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 6]

*  查询年龄大于23岁的人，按性别分组，统计每组的人数，并只显示性别人数大于2的数据
 *  以下代码是否正确？

SELECT sex, COUNT(*) FROM student WHERE age > 23 GROUP BY sex WHERE COUNT(*) >2;

![在这里插入图片描述][e2da629c62644da097cf38ab5f38a348.png_pic_center]

> 上面的SQL语句有三处地方有问题：
> 
> 1）where后面是不能再跟where语句的
> 
> 2）在where后面是不可以写聚合函数的
> 
> 3）分组后面不能使用where语句

*  正确写法：

SELECT sex, COUNT(*) FROM student WHERE age > 23 GROUP BY sex having COUNT(*) >2;

![在这里插入图片描述][ed5bee771c224db18629e58c33284c78.png_pic_center]

### 1.3.1 having与where的区别 ###

<table> 
 <thead> 
  <tr> 
   <th>子名</th> 
   <th>作用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>where 子句</td> 
   <td>先过滤掉行上的一些数据，再进行分组操作。(先过滤再分组)</td> 
  </tr> 
  <tr> 
   <td>having子句</td> 
   <td>先分组后得到的结果上再进行过滤的操作。(先分组再过滤)</td> 
  </tr> 
 </tbody> 
</table>

1.  where子句后不能使用聚合函数
2.  后面可以使用聚合函数

## 1.4 limit语句 ##

*  作用：默认情况下查询所有行，限制查询记录的条数
 *  语法：

select * from table LIMIT offset,length

*  offset：跳过多少条记录，默认是0
 *  length：返回多少条记录

案例：查询学生表中数据，从第3条开始显示，显示3条。

select * from student limit 2, 3;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 7]

### 1.4.1 LIMIT的使用场景： ###

分页：比如我们登录京东，淘宝，返回的商品信息可能有几万条，不是一次全部显示出来。是一页显示固定的条数。

假设我们一每页显示5条记录的方式来分页。

-- 每页显示3条
    
    -- 第一页：跳过0条，显示3条
    select * from student3 limit 0,3;
    
    -- 如果第1个参数是0，可以省略
    select * from student3 limit 3;
    
    -- 第二页：跳过3条，显示3条
    select * from student3 limit 3,3;
    
    -- 第三页：跳过6条，显示3条(如果没有这么多记录，有多少条显示多少条)
    select * from student3 limit 6,3;
    
    -- 公式: select * from student limit (当前页-1)*页大小,页大小

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 8]

# 二、数据库备份 #

## 2.1 备份的应用场景 ##

在服务器进行数据传输、数据存储和数据交换，就有可能产生数据故障。比如发生意外停机或存储介质损坏。这时，如果没有采取数据备份和数据恢复手段与措施，就会导致数据的丢失，造成的损失是无法弥补与估量的。

## 2.2 备份与还原的语句 ##

### 2.2.1 备份格式： ###

`mysqldump.exe`文件，在bin文件夹下

*  语法：

mysqldump –u用户名 –p密码 数据库名>文件名

> 生成的SQL语句，先删除原有的表，再创建表，再插入记录

![在这里插入图片描述][dd97ae5d89174f96bfbff314e7585fd4.png_pic_center]

### 2.2.2 还原格式： ###

*  注：这个操作必须登录mysql客户端
 *  语法：

source 导入的sql文件;
    
    source d:/db01.sql

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 9]

# 三、数据库约束 #

## 3.1 数据库约束的概述 ##

### 3.1.1 约束的作用： ###

一般在创建表的时候给表的字段添加各种约束，从而保证输入到表中的数据是正确的。保证数据的正确性，完整性和有效性。违反约束的数据是不能添加到表中去的。如果表已经存在，并且表中已经有数据，添加约束的时候如果表中的数据已经违反了现在要添加的约束，约束会添加失败。

### 3.1.2 约束种类： ###

<table> 
 <thead> 
  <tr> 
   <th>约束名</th> 
   <th>约束关键字</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>主键</td> 
   <td>primary key</td> 
  </tr> 
  <tr> 
   <td>唯一</td> 
   <td>unique</td> 
  </tr> 
  <tr> 
   <td>默认</td> 
   <td>default</td> 
  </tr> 
  <tr> 
   <td>非空</td> 
   <td>not null</td> 
  </tr> 
  <tr> 
   <td>外键</td> 
   <td>foreign key … references</td> 
  </tr> 
 </tbody> 
</table>

## 3.2 主键约束 ##

### 3.2.1 主键的作用 ###

用来唯一标识表中的每一行记录，在创建表的时候，每张表都应该**创建一个主键**，只要有主键就有主键约束。

### 3.2.2 主键列的选择 ###

通常不建议使用与业务相关的字段做为主键，如：身份证。往往会单独创建一个字段来做为主键，主键应该是没有含义。主键是给程序员编程使用的，不是给最终用户使用。

### 3.2.3 联合主键 ###

主键可以不只一个字段，如果有多个字段组成的主键，称为联合主键。

### 3.2.4 主键的特点： ###

*  1）非空：必须要有值
 *  2）唯一：同一张表中不能出现重复

### 3.2.5 创建主键方式： ###

*  1）在创建表的时候添加主键：

字段名 字段类型 PRIMARY KEY
    
    create table t1(
    	id int primary key,				-- 指定id列为主键
        city varchar(20)
    );
    
    insert into t1 values(1,'四川成都');
    
    -- ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' 违反主键约束
    insert into t1 values(1,'浙江杭州');

![在这里插入图片描述][341a6dec3e8c49e183be3296d0715a1f.png_pic_center]

*  2）**在已有表中添加主键**

ALTER TABLE 表名 ADD PRIMARY KEY(字段名);
    
    alter table t1 add primary key(id);

### 3.2.6 删除主键 ###

-- 语法:
    alter table 表名 drop primary key;
    
    -- 删除t1表的主键约束
    alter table t1 drop primary key;
    
    -- 查看表详情
    desc t1;
    
    -- 给t1表的id列添加一个主键约束
    alter table t1 add primary key (id);
    
    -- 查看表详情
    desc t1;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 10]

### 3.2.7 主键自增 ###

主键如果让我们自己添加很有可能重复，我们通常希望在每次插入新记录时，数据库自动生成主键字段的值，在最大值加1做为主键值。自增长必须是整数类型，而且必须是主键可以使用。

*  主键自增的语法：

字段名 字段类型 PRIMARY KEY AUTO_INCREMENT

create table t2(
    	id int primary key auto_increment,		-- 主键并且自增
        city varchar(20)
    );
    
    -- 查看表详情
    desc t2;
    
    -- 插入数据
    -- 错误
    insert into t2 values('黑龙江哈尔滨');
    
    -- 执行几次
    insert into t2 (city) values('云南昆明');
    insert into t2 (city) values('甘肃兰州');
    insert into t2 (city) values('青海西宁');
    insert into t2 (city) values('山西太原');
    insert into t2 (city) values('辽宁沈阳');
    insert into t2 (city) values('内蒙古呼和浩特');
    
    select * from t2;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 11]

*  修改主键自增的起始值

-- 将主键的起始值设置为100
    
    alter table t2 auto_increment = 100;
    
    
    insert into t2(city) values('山东济南');
    
    -- 第二种写法
    insert into t2 values(NULL,'吉林长春');

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 12]

## 3.3 唯一约束 ##

概念：这一列的值不能重复

*  语法：

字段名 字段类型 UNIQUE

### 3.3.1 添加实现唯一约束 ###

-- 创建学生表st3, 包含字段(id, name),name这一列设置唯一约束,不能出现同名的学生
    
    create table t3 (
    	id int primary key auto_increment,
    	city varchar(20) unique
    );
    
    -- 添加一个同名的学生
    insert into t3 (city) values('河北石家庄');
    
    -- ERROR 1062 (23000): Duplicate entry '河北石家庄' for key 'city'
    insert into t3 (city) values('河北石家庄');
    
    desc t3;
    
    select * from t3;

## 3.4 非空约束 ##

概念：这一列的值必须输入，不能为空

*  语法：

字段名 字段类型 NOT NULL
    
    create table t4(
    	id int,
    	province varchar(30),
    	city varchar(20) not null			-- 非空约束
    );
    
    insert into t4 values(1,'江苏','南京');
    
    -- ERROR 1048 (23000): Column 'city' cannot be null city列不能为空
    insert into t4 values(2,'江苏',null);
    
    -- ERROR 1364 (HY000): Field 'city' doesn't have a default value city列没有默认值
    insert into t4(id,province) values(2,'江苏');

## 3.5 默认值 ##

概念：如果某一列没有输入值，使用默认值。如果输入了，则使用输入的值。

*  语法：

字段名 字段类型 default 默认值
    
    create table t5(
    	id int,
    	province varchar(20),
    	city varchar(20) default '乌鲁木齐'			-- 如果该列没填,默认为乌鲁木齐
    );
    
    -- 如果填了以真实的值为准
    insert into t5 values(1,'西藏','拉萨');
    
    -- 没有填默认为'乌鲁木齐'
    insert into t5(id,province) values(2,'新疆');
    
    -- 也可以使用MySQL提供的default关键字
    insert into t5 values(3,'新疆',default);
    
    -- 查询表数据
    select * from t5;

## 3.6 外键约束 ##

### 3.6.1 单表的缺点 ###

创建一个员工表包含如下列(id, name, age, dep\_name,dep\_location),id主键并自动增长,添加5条数据

CREATE TABLE employee (  
    	id INT PRIMARY KEY AUTO_INCREMENT,  
    	name VARCHAR(30),  
    	age INT,  
    	dept_name VARCHAR(30),  
    	dept_location VARCHAR(30)  
    );  
    
    -- 添加数据 
    INSERT INTO employee (name, age, dept_name, dept_location) VALUES 
    ('小明', 25,'研发部', '贵州贵阳');  
    INSERT INTO employee (name, age, dept_name, dept_location) VALUES 
    ('小龙', 24,'研发部', '贵州贵阳');  
    INSERT INTO employee (name, age, dept_name, dept_location) VALUES 
    ('小红', 20,'研发部', '贵州贵阳');  
    
    INSERT INTO employee (name, age, dept_name, dept_location) VALUES 
    ('小兰', 26,'销售部', '宁夏银川');  
    INSERT INTO employee (name, age, dept_name, dept_location) VALUES 
    ('小陈', 28,'销售部', '宁夏银川');  
    INSERT INTO employee (name, age, dept_name, dept_location) VALUES 
    ('小赵', 18,'销售部', '宁夏银川');

*  以上数据表的缺点:
    
    1.  大量冗余数据出现：研发部、销售部、地址等信息出现了多次
    2.  会出现删除异常，如果研发部一个人都没有那么研发部就不存在了

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 13]

*  解决方案：

把这一张表拆分成两张表，一张表保存员工，另一张表保存部门。两个表之间通过一个外键建立联系。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 14]

-- 主表: 部门表
    create table dept(
    	id int primary key auto_increment,
    	dept_name varchar(20),
    	dept_location varchar(20)
    );
    
    -- 添加部门信息
    insert into dept (dept_name,dept_location) values ('研发部','贵州贵阳'),('销售部', '宁夏银川');
    
    -- 如果存在就把这个表删除
    drop table if exists employee;
    
    -- 员工表
    create table employee(
    	id int primary key auto_increment,
    	name varchar(20),
    	age int,
    	dept_id int 	-- 外键的数据类型与主表中的主键相同
    );
    
    -- 添加员工信息
    INSERT INTO employee (name, age,dept_id) VALUES 
    ('小明', 25,1),
    ('小龙', 24,1),
    ('小红', 20,1),
    ('小兰', 26,2),
    ('小陈', 28,2),
    ('小赵', 18,2);

*  **问题**：当我们在employee的dept\_id里面输入不存在的部门，数据依然可以添加，但是并没有对应的部门，实际应用中不能出现这种情况。employee的dept\_id中的数据只能是dept表中存在的id

-- 插入3号部门的员工已经可以正常执行
    INSERT INTO employee (name, age,dept_id) VALUES ('小黄',23,3);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 15]

### 3.6.2 什么是外键约束 ###

部门与员工之间是1对多的关系，一个部门对应多个员工，一个员工属于一个部门。部门是1方，员工是多方。

*  主表： 是1方，部门表
 *  从表： 是多方，员工表
 *  什么是外键：外键出现在从表中，被主表的主键约束的那一列外键

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 16]

### 3.6.3 创建约束的语法 ###

*  新建表时增加外键：

FOREIGN KEY(外键字段) REFERENCES 主表(主键)
    
    -- 删除员工表(从表)
    drop table employee;
    
    -- 创建员工表(id,name,age,dep_id)
    
    create table employee(
    	id int primary key auto_increment,
    	name varchar(20),
    	age int,
    	dept_id int, -- 外键的数据类型与主表中的主键相同
    	foreign key (dept_id) references dept(id)		-- 本表的dept_id列依赖于dept表的id列
    );
    
    /* ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db01`.`employee`, CONSTRAINT `emp_dept_id_fk` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`)) */
    
    -- 违反外键约束
    INSERT INTO employee (name, age,dept_id) VALUES ('小黄',23,3);
    
    -- 添加3号部门
    insert into dept values(3,'行政部','陕西西安');
    
    -- 再次添加员工并指定为三号部门
    INSERT INTO employee (name, age,dept_id) VALUES ('小黄',23,3);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 17]

*  已有表增加外键：

ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY (外键字段) REFERENCES 主表(主键)
    
    alter table employee add constraint emp_dept_id_fk foreign key(dept_id) references dept(id);
    
    -- 查看表的建表语句
    show create table employee;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 18]

### 3.6.4 删除外键 ###

*  语法：

ALTER TABLE 表名 DROP FOREIGN KEY 约束名;
    
    alter table employee drop foreign key emp_dept_id_fk;
    
    -- 查看表的建表语句
    show create table employee;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 19]

### 3.6.5 外键的级联 ###

*  出现新的问题：要把部门表中的id值2，改成20，能不能直接更新呢？

-- ERROR 1451 (23000): Cannot delete or update a parent row
    update dept set id=20 where id=2;

*  要删除部门id等于1的部门, 能不能直接删除呢？

-- ERROR 1451 (23000): Cannot delete or update a parent row
    delete from dept where id=1;

*  什么是级联操作：

在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作

<table> 
 <thead> 
  <tr> 
   <th>级联操作语法</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>ON UPDATE CASCADE</td> 
   <td>级联更新</td> 
  </tr> 
  <tr> 
   <td>ON DELETE CASCADE</td> 
   <td>级联删除</td> 
  </tr> 
 </tbody> 
</table>

-- 删除外键约束-- 删除外键约束
    alter table employee drop FOREIGN KEY emp_dept_id_fk;
    
    -- 添加外键约束，级联更新和级联删除
    alter table employee add constraint emp_dept_id_fk foreign key (dept_id) references dept(id) on update cascade on delete cascade;
    
    select * from employee;
    
    select * from dept;
    
    -- 把部门表中id等于1的部门改成id等于10
    update dept set id=10 where id=1;
    
    -- 删除部门号是2的部门
    delete from dept where id=2;

## 3.7 数据约束小结 ##

<table> 
 <thead> 
  <tr> 
   <th>约束名</th> 
   <th>关键字</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>主键</td> 
   <td>primary key</td> 
   <td>唯一，非空</td> 
  </tr> 
  <tr> 
   <td>默认</td> 
   <td>default</td> 
   <td>没有输入值，使用默认值</td> 
  </tr> 
  <tr> 
   <td>非空</td> 
   <td>not null</td> 
   <td>必须输入</td> 
  </tr> 
  <tr> 
   <td>唯一</td> 
   <td>unique</td> 
   <td>不能重复</td> 
  </tr> 
  <tr> 
   <td>外键</td> 
   <td>foregin key (外键) references 主表(主键)</td> 
   <td>外键在从表 主表：1方 从表：多方</td> 
  </tr> 
 </tbody> 
</table>

# 四、表与表之间的关系 #

## 4.1 表关系的概念 ##

现实生活中，实体与实体之间肯定是有关系的，比如：老公和老婆，部门和员工，老师和学生等。那么我们在设计表的时候，就应该体现出表与表之间的这种关系！

## 4.2 一对多 ##

一对多（1:n） 例如：班级和学生，部门和员工，客户和订单，分类和商品

一对多建表原则: 在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 20]

## 4.3 多对多 ##

多对多（m:n） 例如：老师和学生，学生和课程，用户和角色

多对多关系建表原则：需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 21]

### 4.3.1 SQL实现代码： ###

-- 学生表
    create table s1(
    	id int primary key,
        name varchar(30)
    );
    
    insert into s1 values(1,'小明');
    insert into s1 values(2,'小龙');
    insert into s1 values(3,'小红');
    
    -- 课程表
    create table course(
    	id int primary key,
        name varchar(30)
    );
    
    insert into course values(1,'Java');
    insert into course values(2,'C++');
    insert into course values(3,'MySQL');
    
    -- 学生选课表(中间表)
    create table stu_course(
    	s_id int,
        c_id int,
        foreign key s_id references s1(id),
        foreign key c_id references course(id)
    );
    
    -- 代表小明选修了Java课程
    insert into course values(1,1);
    
    -- 代表小明选修了MySQL课程
    insert into course values(1,3);
    
    -- 代表小龙选修了Java课程
    insert into course values(2,1);
    
    -- 代表小龙选修了C++课程
    insert into course values(2,2);
    
    -- 代表小红选修了C++课程
    insert into course values(3,2);
    
    -- 代表小红选修了Java课程
    insert into course values(3,1);

## 4.4 一对一 ##

一对一（1:1） 在实际的开发中应用不多，因为一对一可以创建成一张表。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 22]

### 4.4.1 SQL实现代码： ###

-- 先创建主表
    
    -- 学生表
    create table s2 (
    	id int primary key,
    	name varchar(20),
    );
    
    -- 个人信息表
    create table info(
    	id int primary key, -- 主键
    	age int,
    	addr varchar(20),
    	foreign key (id) references s2(id)	-- 创建外键
    );
    
    insert into s2 values(1,'小明');
    insert into s2 values(2,'小龙');
    insert into s2 values(3,'小红');
    
    -- 小明的年龄为23岁,籍贯为江西
    insert into info values(1,23,'江西');
    
    -- 小明的年龄为25岁,籍贯为广西
    insert into info values(2,25,'广西');
    
    -- 小明的年龄为34岁,籍贯为山西
    insert into info values(3,34,'山西');

## 4.5 表与表之间的关系小结 ##

<table> 
 <thead> 
  <tr> 
   <th>表与表的关系</th> 
   <th>关系的维护</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>一对多</td> 
   <td>通过从表中外键来维护</td> 
  </tr> 
  <tr> 
   <td>多对多</td> 
   <td>通过中间表，将两个一对多加到一起变成了一个多对多</td> 
  </tr> 
  <tr> 
   <td>一对一</td> 
   <td>1. 外键添加约束 2. 从表的主键又是外键</td> 
  </tr> 
 </tbody> 
</table>

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center]: /images/20220829/7a02edc4d9314206994968ac5aaafac3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20220829/d5239b583dc04d07b7ae7fee5ac9208a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20220829/f2373edbba7b474d8a158f8d21f53e45.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20220829/960bb50039e84fb79aeb49ed13f81cc7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20220829/47a24bed00b44a84bc888d169b65f12d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 5]: /images/20220829/7f0f56c053864845a1e383e05b582e33.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 6]: /images/20220829/d289b5a9710a490eb5c0208c1c326a9f.png
[e2da629c62644da097cf38ab5f38a348.png_pic_center]: /images/20220829/ab4c1798faab4be0a48c31e4369bbc7c.png
[ed5bee771c224db18629e58c33284c78.png_pic_center]: /images/20220829/e62f1ea95ea5472f81ac341e809dbcb0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 7]: /images/20220829/f1a53109ce294c29b71cee1f102ffc33.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 8]: /images/20220829/cd43d11d5aad436dafe8ab30d49c2d88.png
[dd97ae5d89174f96bfbff314e7585fd4.png_pic_center]: /images/20220829/d8a089dc38844ccfb378f3716753d53f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 9]: /images/20220829/9aa2d0138b154ed7b5f5ba2deb5f3e8b.png
[341a6dec3e8c49e183be3296d0715a1f.png_pic_center]: /images/20220829/1fcd3bdd68b74b68ac64719f11c1e335.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 10]: /images/20220829/636c0afd58c847fcb2bffdbf80ee52d2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 11]: /images/20220829/f8f27dcfa4ed49458e7b3bb122694272.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 12]: /images/20220829/ea236c74abc1451abc6ad4d880f500cf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 13]: /images/20220829/84b9747629e04a1492ba65bcd5faa37f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 14]: /images/20220829/54d7c0edbb314769bd554463e05bb96a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 15]: /images/20220829/328d5bbb42894eeda6b3e8840e1ba082.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 16]: /images/20220829/9ee5e3bee1b44f6fabf4ac1175bc7796.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 17]: /images/20220829/e7681defe451499e926d828adcd333ec.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 18]: /images/20220829/ef547eeb77834ce6876e90a4db0781cc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 19]: /images/20220829/06786bb09c734cd5b1a879c25fa6cd19.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 20]: /images/20220829/7cf9bac6a8e34238800ce590c9cdcf5f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 21]: /images/20220829/6d9d7a95c5094aa184c2be212bc8f8f5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70_pic_center 22]: /images/20220829/a4dff7848f5443469830dacac6ce9cab.png