• 删除外键约束

• 外键约束的注意事项

• 多表关系设计

• • 多对多关系

• 多表查询

• • 外连接查询

• 右外连接

• 子查询(Sub Query)

• • 什么是子查询

• 单行子查询

• 多行子查询

• 数据库事务控制(TCL)

• • 什么是事务

• MySQL事务操作

• • 手动提交事务

• 手动提交事务的流程

• • 手动控制事务的转账案例

• 事务的四大特性 ACID

• MySQL事务的隔离级别

• • 数据并发访问

• 并发访问产生的问题

• 隔离级别相关命令

• 设置隔离级别

• 索引

• • 什么是索引

• 索引的作用

• 索引的底层实现

• Hash结构

• Tree结构

• BTree结构

• 常见的索引的分类

| | |

| --- | --- |

[](()SQL分类

====================================================================

数据查询语言（Data Query Language，DQL）：DQL主要用于数据的查询，其基本结构是使用select子句，from子句和where子句的组合来查询一条或多条数据

数据操作语言（Data Manipulation Language，DML）：DML主要用于对数据 增加、修改和删除的操作，其主要包括：

INSERT：增加数据 updeta：修改数据 delect：删除数据

数据定义语言（Data Definition Language，DDL）：DDL主要用针对是数据库对象（数据库、表、索引、视图、触发器、存储过程、函数）进行创建、修改和删除操作。其主要包括：

CREATE：创建 alter：修改 drop：删除

数据控制语言（Data Control Language，DCL）：DCL用来授予或回收访问数 据库的权限，其主要包括：

GRANT：授予用户某种权限 REVOKE：回收授予的某种权限

事务控制语言（Transaction Control Language，TCL）：TCL用于数据库的事务管理。其主要包括：

START TRANSACTION：开启事务 SET TRANSACTION：设置事务的属性

COMMIT：提交事务 ROLLBACK：回滚事务

| | |

| --- | --- |

| | |

[](()DDL操作

====================================================================

[](()查看所有的数据库

---

show databases;

[](()创建数据库

---

create database aa;

[](()删除数据库

---

drop database aa;

| | |

| --- | --- |

| | |

[](()浮点数类型

====================================================================

注意事项：

浮点数类型的宽度不会自动扩充，使用时需要指定长度

如：score double(4,1) 总体长度是4 精度为1位

| | |

| --- | --- |

| | |

[](()字符串类型

====================================================================

CHAR和VARCHAR类型相似，均用于存于较短的字符串，主要的不同之处在于存储方式。CHAR类型长度固定，VARCHAR类型的长度可变

因为VARCHAR类型能够根据字符串的实际长度来动态改变所占字节的大小，所以在不能明确该字段具体需要多少字符时推荐使用VARCHAR类型，这样可以大大地节约磁盘空间、提高存储效率。

注意事项：

CHAR和VARCHAR表示的是字符的个数，而不是字节的个数

| | |

| --- | --- |

| | |

[](()日期和时间类型

======================================================================

IMESTEMP类型的数据指定方式与DATETIME基本相同，两者不同之处在于以下几点：

1. 数据的取值范围不同，TIMESTEMP类型的取值范围更小

2. 如果我们对TIMESTAMP类型的字段没有明确赋值，或是被赋与了NULL值， MySQL会自动将该字段赋值为系统当前的日期与时间

| | |

| --- | --- |

| | |

[](()DDL创建数据库表

========================================================================

create table 表名(

字段一 数据类型,

字段二 数据类型(长度)

);

注意: 最后一列不要加 , 号

需要长度的类型必需添加 不需要忽略

| | |

| --- | --- |

| | |

[](()DDL查看数据库表

========================================================================

-- 显示当前数据库的所有数据库表

show tables;

-- 显示当前数据表的结构 desc 表名

desc category;

-- 查看完整的建表语句 show create table 表名

show create table category;

[](()DDL删除数据库表

========================================================================

-- 删除当前数据库中的表 drop table 表名;

drop table category;

[](()DDL修改数据库表

========================================================================

[](()修改表名

---

rename table 旧表名 to 新表名

[](()向表中添加字段 关键字 add

---

alert table 表名 add 字段名称 字段类型

[](()修改表中字段 关键字 modify | change

---

alert table 表名 modify 字段名称 字段类型;

[](()Change: 修改字段

alter table 表名 change 旧列名 新列名 类型(长度);

[](()删除表中字段 关键字 drop

---

alter table 表名 drop 列名;

| | |

| --- | --- |

| | |

[](()DML操作表中数据

========================================================================

SQL中的DML 用于对表中的数据进行增删改操作

[](()插入数据

---

-- 插入部分字段或全部字段

insert into 表名 （字段名1，字段名2...） values(字段值1，字段值2...);

-- 插入全部的字段

insert into 表名 values(字段值1，字段值2...);

注意事项：

1. 值与字段必须要对应，个数相同&数据类型相同

2. 值的数据大小，必须在字段指定的长度范围内

3. varchar char date类型的值必须使用单引号，或者双引号包裹。

4. 如果要插入空值，可以忽略不写，或者插入null

5. 如果插入指定字段的值，必须要上写列名

6. 字符串日期类型支持 "1256-12-23" "1256/12/23" "1256.12.23"

[](()更改数据

---

update 表名 set 字段名=值 where 条件(如: 字段名=值)

update 表名 set 字段名=值 -- 慎重: 不加条件更改所有

需求: 将sid为1的学生 ssex改为女

SQL实现

update stu set ssex='女' where sid = 1;

[](()删除数据

---

delete from 表名 where 条件(如: 字段名=值)

delete from 表名 -- 慎重: 删除表中所有的数据

truncate table 表名; -- 慎重: 删除表中所有的数据

需求1: 删除sid为5的学生信息

SQL实现

delete from stu where sid = 5;

需求2: 将 stu表数据全部删除

SQL实现

delete from stu;

truncate table stu; -- 推荐使用

从最终的结果来看，虽然使用TRUNCATE操作和使用DELETE操作都可以删除表中的全部记录，但是两者还是有很多区别的，其区别主要体现在以下几个方面：

(1)DELETE为数据操作语言DML；TRUNCATE为数据定义语言DDL。

(2) DELETE操作是将表中所有记录一条一条删除直到删除完；TRUNCATE操作 则是保留了表的结构，重新创建了这个表，所有的状态都相当于新表。因此， TRUNCATE操作的效率更高。

(3)DELETE操作可以回滚；TRUNCATE操作会导致隐式提交，因此不能回滚（后 面会讲解事务的提交和回滚）。

(4)DELETE操作执行成功后会返回已删除的行数（如删除4行记录，则会显示“Affected rows：4”）；截断操作不会返回已删除的行量，结果通常是“Affected rows：0”。DELETE操作删除表中记录后，再次向表中添加新记录时，对于设 置有自增约束字段的值会从删除前表中该字段的最大值加1开始自增； TRUNCATE操作会重新从1开始自增。

| | |

| --- | --- |

| | |

[](()表的约束

===================================================================

约束的作用

对表中数据的一种限制约束, 从而保证数据的正确性, 有效性, 和完整性. 违反约束的不正确数据是无法插入到表中的

[](()主键约束

---

添加主键约束

语法格式

字段名 字段类型 primary key

需求1: 创建一个带有主键的emp表 字段 eid int ename varchar(10) esex char(1)

SQl实现: 方式一

create table emp(

eid int primary key, -- 设置主键 非空唯一

ename varchar(10),

esex char(1)

);

-- 查看表结构

desc emp;

测试主键的唯一 非空约束

-- 插入正常数据

insert into emp values(1,"宋江","男");

-- 插入一条数据, 主键为空

insert into emp values(null,"李逵","男");

-- Column 'eid' cannot be NULL 主键不能为空

-- 插入一条数据, 主键重复

insert into emp values(1,"孙二娘","女");

-- Duplicate entry '1' for key 'emp.PRIMARY' 主键冲突

[](()删除主键约束

---

-- 使用DDL 删除表中的主键约束

alter table emp drop primary key;

-- 查看表结构

desc emp;

-- 删除唯一约束(了解)

-- 添加了唯一约束为 eid int not null, 通过设置字段属性删除唯一约束

alter table emp modify eid int;

[](()主键自增

---

自己添加数据可能出现重复, 我们通常希望在每次插入新记录时, 数据库自动生成主键字段的值

语法格式

关键字

auto_increment

表示自增长(字段类型必须为整数类型)

需求: 为emp表eid字段添加主键约束, 并设置为自增

1. 创建主键自增的emp表

drop table emp;

create table emp(

eid int primary key auto_increment,

ename varchar(10),

esex char(1)

);

[](()非空约束

---

语法格式

字段名 字段类型 not null

需求: emp表 eid 主键约束,自增 ename 非空约束

SQL实现

-- 删除存在的emp表

drop table emp;

create table emp(

eid int primary key auto_increment,

ename varchar(10) not null,

esex char(1)

);

-- 查看emp表结构

desc emp;

[](()唯一约束

---

语法格式

字段名 字段类型 unique

需求: emp表 eid 主键约束,自增 ename 非空约束 esex唯一约束

SQL实现

-- 删除存在的emp表

drop table emp;

create table emp(

eid int primary key auto_increment,

ename varchar(10) not null,

esex char(1) unique

);

-- 测试唯一约束 添加两个性别为男

insert into emp values(default,'zs','男');

[](()默认值约束

---

语法格式

字段名 字段类型 default '值'

需求: emp表 eid 主键约束,自增 ename 非空约束 esex默认值'男'

SQL实现

-- 删除存在的emp表

drop table emp;

create table emp(

eid int primary key auto_increment,

ename varchar(10) not null,

esex char(1) default '男'

);

-- 查看emp表结构

desc emp;

[](()检查约束

---

语法格式

字段名 字段类型 check(字段='值' or 字段='值')

字段名 字段类型 check(字段>n or 字段

字段名 字段类型 check(字段>n and 字段

需求: emp表 eid 主键约束,自增 ename 非空约束 esex默认值'男'并检查 男|女

SQL实现

-- 删除存在的emp表

drop table emp;

create table emp(

eid int primary key auto_increment,

ename varchar(10) not null,

esex char(1) default '男' check(esex='男' or esex='女')

);

-- 查看emp表结构

desc emp;

| | |

| --- | --- |

| | |

[](()DQL查询表中数据

========================================================================

数据查询语言（Data Query Language，DQL）：DQL主要用于数据的查询，

其基本结构是使用SELECT子句，FROM子句和WHERE子句的组合来查询一条或多条数据

DQL是实际开发过程中使用最多的

[](()简单查询

---

查询不会对数据库中的数据进行修改, 只是一种显示数据的方式

语法格式

select 字段名 from 表名

需求1: 查询emp中所有的数据

-- 将要查询的字段全部列出

select eid ,ename, sex, salary, hire_date, dept_name from emp;

--查询所有的字段可以使用 * , * 代表所有的字段

select * from emp;

需求2: 查询emp表中所有的记录, 只显示eid和ename字段

select eid, ename from emp;

需求3: 查询所有的员工信息, 将字段名显示为中文

select

eid as '编号', -- as可以省略

ename as '姓名',

sex as '性别',

salary as '薪资',

hire_date as '入职日期',

dept_name as '部门名称'

from emp;

需求4: 查询有多少个部门

-- 查询所有的部门(出现重复部门)

select dept_name from emp;

-- 使用去重查询 关键字 distinct

select distinct dept_name from emp;

将所有的员工薪资加1000显示

-- 支持算术运算符 + - * / % 的运算

select eid, ename, salary, salary+1000 from emp;

[](()条件查询

---

·如果查询语句中没有设置条件, 就会查询所有的行信息

·在实际应用中, 一定要指定查询的条件, 对记录进行过滤

语法格式

select 列名 from 表名 where 条件

需求1: 精确查询

#1 查询员工姓名为黄蓉的员工信息

#2 查询薪水价格为5000的员工信息

#3 查询薪水价格不是5000的所有员工信息

#4 查询薪水价格大于6000元的所有员工信息

#5 查询薪水价格在5000到10000之间所有员工信息

#6 查询薪水价格是3600或7200或者20000的所有员工信息

SQL实现

#1 查询员工姓名为黄蓉的员工信息

select * from emp where ename = '黄蓉';

#2 查询薪水价格为5000的员工信息

select * from emp where salary = 5000;

#3 查询薪水价格不是5000的所有员工信息

select * from emp where salary != 5000;

select * from emp where salary <> 5000;

#4 查询薪水价格大于6000元的所有员工信息

select * from emp where salary > 6000;

#5 查询薪水价格在5000到10000之间所有员工信息

select * from emp where salary >= 5000 and salary <= 10000;

select * from emp where salary between 5000 and 10000;

#6 查询薪水价格是3600或7200或者20000的所有员工信息

select * from emp where salary = 3600 or salary = 7200 or salary = 20000;

select * from emp where salary in (3600, 7200, 20000);

需求2: 模糊查询

#1 查询含有'八'字的所有员工信息

#2 查询以'孙'字开头的所有员工信息

#3 查询第二个字为'兔'的所有员工信息

#4 查询没有部门的员工信息

#5 查询有部门的员工信息

SQL实现

#1 查询含有'八'字的所有员工信息

select * from emp where ename like '%八%';

#2 查询以'孙'字开头的所有员工信息

select * from emp where ename like '孙%';

#3 查询第二个字为'兔'的所有员工信息

select * from emp where ename like '_兔%';

#4 查询没有部门的员工信息

select * from emp where dept_name is null;

#5 查询有部门的员工信息

select * from emp where dept_name is not null;

[](()排序

---

通过order by子语句, 可以将查询出的结果进行排序(排序只是显示效果, 并不会影 响真实的数据

select 字段名 from 表名 [where 条件] order by 字段名[asc | desc];

-- asc 升序(默认)

-- desc 降序

1)单列排序

· 只按照某一个字段进行排序

需求1: 查询所有的员工信息, 使用saraly进行排序

SQL实现

-- 升序排序(默认 asc)

select * from emp order by salary;

-- 降序排序(desc)

select * from emp order by salary desc;

组合排序

·同时对多个字段进行排序, 如果第一个字段相同, 就按照第二个字段排序, 以此类推

需求2: 查询所有的员工信息, 使用salary升序排列,salary相同按照入职日期降序排序

SQL实现

-- 组合排序

select * from emp order by salary, hire_date desc;

[](()函数

---

MySQL中提供了大量函数来简化用户对数据库的操作，比如字符串的处理、日期的运算、数值的运算等等。使用函数可以大大提高SELECT语句操作数据库的能力，同时也给数据的转换和处理提供了方便。

函数只是对查询结果中的数据进行处理，不会改变数据库中数据表的值。MySQL中的函数主要分为单行函数和多行函数两大类

单行函数

单行函数是指对每一条记录的值进行计算，并得到相应的计算结果，然后返回给用户，也就是说，每条记录作为一个输入参数，经过函数计算得到每条记录的计算结果。常用的单行函数主要包括字符串函数、数值函数、日期与时间函数、流程函数以及其他函数

使用单行函数, 是对行中字段的操作, 操作多少行, 返回多少行数据

多行函数

我们之前做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用多行函数查询是纵向查询，它是对某一列的值进行计算，然后返回一个单一的值

·多行函数会忽略null空值

·多行函数也称为分组函数, 聚合函数

[](()单行函数

-- 字符串函数的操作

#1 查询emp表所有数据, 将eid, ename, sex显示格式为 编号: x 姓名: xx 性别: x

#2 查询emp表所有数据, 将ename第二个字符都换为 某

#3 查询emp表所有数据, 显示ename的长度

#4 查询emp表所有数据, 将 ename有英文的改为都是大写

#5 查询emp表所有数据, 将 ename有英文的改为都是小写

#6 查询emp表所有数据, ename只显示姓

SQL实现

#1 查询emp表所有数据, 将eid, ename, sex显示格式为 编号: x 姓名: xx 性别: x

select concat('编号:', eid), concat('姓名:', ename), concat('性别:', sex) from emp;

#2 查询emp表所有数据, 将ename第二个字符都换为 某

select eid, insert(ename, 2, 1, 某'), sex from emp;

#3 查询emp表所有数据, 显示ename的长度

select eid, ename, length(ename), sex from emp;

#4 查询emp表所有数据, 将 ename有英文的改为都是大写

select eid, ename, upper(ename), sex from emp;

#5 查询emp表所有数据, 将 ename有英文的改为都是小写

select eid, ename, lower(ename), sex from emp;

#6 查询emp表所有数据, ename只显示姓

select eid, ename, substring(ename, 1, 1), sex from emp;

MySQL提供的一张虚拟表中进行演示，该表名为“dual”，是MySQL为了满足用“SELECT ??? from???”的习惯而增设的一张虚拟表。 在使用dual表时，如果没有where子句，则可以省略“from dual”, 没有实际意义, 查询的字段不属于任何表的时候, 就可以使用dual这张虚拟表

-- 数值函数的操作

select abs(-1), ceil(3.2), floor(3.7), round(3.5), mod(10,3), pi(), pow(2,5), sqrt(25) from dual;

select abs(-1), ceil(3.2), floor(3.7), round(3.5), mod(10,3), pi(), pow(2,5), sqrt(25);

-- 日期与时间函数的操作

select curdate(), curtime(), now(), sysdate();

select curdate(), curtime(), now(), sleep(2), sysdate();

-- 流程函数的操作

#1 查询emp表所有数据, 薪资 >= 10000 高工资 <10000 低工资

#2 查询emp表所有数据, 计算出员工的年薪 薪资*12 加年终奖(每人30k)

#3 查询emp表所有数据, 薪资 >= 15000 优秀 >=9000 坚持住 >=5000 加油哦

= 3000 加把劲 其他 努力奋斗吧骚年

#1 查询emp表所有数据, 薪资 >= 10000 高工资 其他 低工资

select eid, ename, salary, if(salary >= 10000, '高工资', '低工资') from emp;

#2 查询emp表所有数据, 计算出员工的年薪 薪资*12 加年终奖(每人30k)

select eid, ename, salary, salary * 12+30000 '年薪' from emp; -- 需要考虑null

select eid, ename, salary, ifnull(salary, 0) * 12+30000 '年薪' from emp;

#3 查询emp表所有数据, 薪资 >=3000 加把劲 >=5000 加油哦 >=9000 坚持住 >= 15000 优秀 其他 不及格

select eid, ename, salary,

case

when salary >= 15000 then '优秀'

when salary >= 9000 then '坚持住'

when salary >= 5000 then '加油哦'

when salary >= 3000 then '加把劲'

else '努力奋斗吧骚年'

end

from emp;

[](()多行函数

---

多行函数会忽略null空值

·多行函数也称为分组函数, 聚合函数

需求:

#1 查询员工的总数

#2 查看员工总薪水、最高薪水、最小薪水、薪水的平均值, 显示为总薪水, ...

#3 查询薪水大于4000员工的个数

#4 查询部门为'教学部'的所有员工的个数

#5 查询部门为'市场部'所有员工的平均薪水

#6 查询部门的个数

SQL实现

#1 查询员工的总数

-- 使用某个字段查询，聚合函数会忽略null, 需要注意为null的字段

select count(eid) from emp;

-- 所有字段匹配查询

select count(*) form emp;

-- 增加一列

select 1 from emp;

-- 效率更高推荐使用

select count(1) from emp;

#2 查看员工总薪水、最高薪水、最小薪水、薪水的平均值, 显示为总薪水, ...

select

sum(salary) '总薪水',

max(salary) '最高薪水',

min(salary) '最小薪水',

avg(salary) '平均薪水'

from emp;

#3 查询薪水大于4000员工的个数

select count(1) from emp where salary > 4000;

#4 查询部门为'教学部'的所有员工的个数

select count(1) from emp where dept_name = '教学部';

#5 查询部门为'市场部'所有员工的平均薪水

select avg(salary) from emp where dept_name = '市场部';

#6 查询部门的个数

select dept_name from emp; -- 9个

select count(dept_name) from emp; -- 8个

-- 部门去重之后， 统计个数

select count(distinct dept_name) from emp; -- 3个

[](()分组

---

·分组查询指的是使用group by语句, 对查询的信息进行分组, 相同数据作为一组

语法格式

select 分组字段/聚合函数 from 表名 group by 分组字段 [having 条件]

需求1: 通过性别分组

SQL实现

按照性别分组查询

select * from emp group by sex; -- 能查到结果, 但是没有意义

select sex from emp group by sex; -- 正确操作

需求1:

#1 查询每个部门的名称

#2 查询每个部门的平均薪资

#3 查询每个部门的平均薪资, 部门名称不能为空

SQL实现

#1 查询每个部门的名称

select dept_name from emp group by dept_name;

#2 查询每个部门的平均薪资

select dept_name, avg(salary) from emp group by dept_name;

#3 查询每个部门的平均薪资, 部门名称不能为空

select dept_name from emp where dept_name is not null group by dept_name;

需求2:

#4 查询每个部门的平均薪资, 只显示平均工资在4000以上的

SQL实现

select dept_name, avg(salary) from emp where avg(salary) > 4000 group by dept_name;

-- Invalid use of group function 报错

需要在分组后, 对数据进行过滤, where的作用是在分组前过滤

2. select语句的执行顺序

from -- where -- group by -- having –- select -- order by

3)分组操作中的having子语句, 适用于对分组后的数据进行过滤的, 作用类似于 where

#4 查询每个部门的平均薪资, 只显示平均工资在4000以上的

select dept_name, avg(salary) from emp group by dept_name having avg(salary) > 4000;

[](()where 和 having的区别

group by 和 having练习

#1 统计每个部门中的最小工资, 列出最小工资小于2000的部门名称

#2 统计平均工资大于3000的部门名称

#3 统计人数小于4的部门的平均工资

#4 统计每个部门最高工资, 排除最高工资小于3000的部门

#1 统计每个部门中的最小工资, 列出最小工资小于4000的部门名称

-- 每个部门的最小工资

select dept_name, min(salary) from emp group by dept_name;

select dept_name, min(salary) from emp group by dept_name having min(salary) < 4000;

#2 统计平均工资大于6000的部门名称

-- 每个部门的平均工资

select dept_name, max(salary) from emp group by dept_name;

select dept_name, max(salary) from emp group by dept_name having max(salary) > 6000;

#3 统计人数小于4个人部门的平均工资

-- 每个部门的平均工资和人数

select dept_name, avg(salary), count(1) from emp group by dept_name;

select dept_name, avg(salary), count(1) from emp group by dept_name having count(1) < 4;

#4 统计每个部门最高工资, 排除最高工资小于10000的部门

-- 每个部门的最高工资

select dept_name, max(salary) from emp group by dept_name

select dept_name, max(salary) from emp group by dept_name having max(salary) >= 10000;

[](()limit关键字

---

作用:

·limit是限制的意思, 限制返回的查询结果的函数(通过limit之sing查询多少行数据)

·limit 语法是 MySql的方言, 用来完成分页

语法结构

select 字段1, 字段2 ... from 表名 limit offset, length;

参数说明

1. offset 起始行数, 从0开始, 如果省略则默认从0开始, 0代表MySQL中第一条数据

2. length 返回的行数

需求1:

#1 查询emp表中的前5条数据

#2 查询emp表中 从第4条开始, 查询6条

SQL实现

#1 查询emp表中的前5条数据

select * from emp limit 5; -- 不指定从哪行还是, 默认从0开始

select * from emp limit 0, 5;

#2 查询emp表中 从第4条开始, 查询6条

select * from emp limit 3, 6; -- 从0开始, 所以第四条数据为3

| | |

| --- | --- |

| | |

[](()多表

=================================================================

多表的概述

实际开发中, 一个项目通常需要很多张表才能完成

例如一个商城项目的数据库, 需要很多张表: 如 用户表, 分类表, 商品表. 订单表.

[](()创建外键约束

---

语法格式:

1. 新建表时添加外键约束

constraint 外键约束名称 foreign key(外键字段名) references 主表名(主键字段);

2. 为已创建好的表添加外键约束

alter table 从表名 add constraint 外键约束名称 foreign key(外键字段名)

references 主表名(主键字段名);

需求: 为employee表的 dep_id字段添加外键约束

1. 为已经创建好的employee表添加外键约束

alter table employee add constraint emp_dep_fk foreign key (dep_id) references department(dep_id);

2. 新创建employee表时, 为dep_id添加外键约束

create table employee(

eid int primary key auto_increment,

ename varchar(10),

age int,

dep_id int,

-- 添加外键约束

constraint emp_dep_fk foreign key(dep_id) references department(dep_id)

);

[](()删除外键约束

---

语法格式:

alter table 从表名 drop foreign key 外键约束的名称

需求: 删除employee表中的外键约束

alter table employee drop foreign key emp_dep_fk;

[](()外键约束的注意事项

---

从表的外键类型必须和主表的主键类型保持一致

2. 添加从表数据时

从表中添加的外键值, 必须在主表的主键中存在

3)删除和变更数据主表数据时

先删除从表中的数据或将外键设置为null, 再删除主表中的数据

-- 删除部门表中主键为1的部门信息

delete from department where dep_id = 1;

-- 报错信息如下

-- Cannot delete or update a parent row: a foreign key constraint fails

| | |

| --- | --- |

| | |

[](()多表关系设计

=====================================================================

实际的开发过程中, 一个项目通常需要很多张表才能完成. 例如: 一个商城项目就需要分类表(category), 商品表(products), 订单表(orders)等多张表. 而且这些表的数据之间存在一定的关系, 接下来我们一起学习以下多表设计方面的知识

一对多关系(常见)

·一对多关系(1 : n)

例如: 班级和学生, 部门和员工, 客户和订单, 类别和商品

·一对多建表原则

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

[](()多对多关系

---

多对多关系(m : n)

例如: 老师和学生, 学生和课程, 用户和角色

·多对多关系建表原则

多对多的关系不能直接处理, 需要创建第三张表, 也称为中间表, 中间表至少两个字段,这两个字段分别作为外键指向各自一方的主键, 实际就是将多对多拆分为两个一对多

[](()多表查询

===================================================================

使用语法

select 字段名 from 表1 cross join 表2;

隐式内连接

from子句后面直接写多个表名 使用where指定连接条件的 这种连接方式是隐式内连接. 使用where条件过滤无用的数据

语法格式

select 字段名 from 表1, 表2 where 连接条件;

显示内连接

使用 inner join …on 这种方式, 就是显式内连接

语法格式

select 字段名 from 表1 [inner] join 右表 on 条件

[](()外连接查询

---

使用 left outer join , outer 可以省略

语法格式

select 字段名 from 表1 left join 表2 on 条件;

左外连接的特点:

1. 以左表为主, 左表中的数据全部显示

2. 右表匹配到数据就显示匹配到的数据

3. 右表没有匹配的数据显示为null

使用左外连接查询每个分类下的商品名称, 商品个数

思路分析

-- 1.查询哪几张表 category products

-- 2.表之间的连接条件 主表主键 = 从表外键

-- 3.查询条件 每个类别 需要分组

-- 4.查询的字段 类别名称 商品数量

select c.cname, count(p.cid) from category c left join products p on c.cid = p.cid

group by c.cname;

[](()右外连接

---

使用 right outer join, outer可以省略

语法格式

select 字段名 from 表1 right join 表2 on 条件;

右外连接的特点:

1. 以右表为主, 右表中的数据全部显示

2. 左表匹配到数据就显示匹配到的数据

3. 左表没有匹配的数据显示为null

| | |

| --- | --- |

| | |

[](()子查询(Sub Query)

=============================================================================

[](()什么是子查询

---

1)子查询概念

一条select查询语句的结果, 作为另一条select语句的一部分

2. 子查询的特点

子查询一般作为父查询的查询条件使用

子查询必须放在小括号中使用

SQL语句含有多个select，先执行子查询，再执行外查询

[](()单行子查询

---

单行子查询: 查询出的结果为一列一行(一个数据) 如: 最高, 最低, 平均等,

可以使用判断符号 如: > | < | = | != 等

语法格式

select 字段 from 表 where 字段 判断符号 (子查询)

需求:

#1 查询价格最高的商品信息

#2 查询化妆品分类下的 商品名称 商品价格

#3 查询小于平均价格的商品信息

SQL实现

#1 查询价格最高的商品信息

-- 1.查询出商品最高的价格

select max(price) from products; -- 5000

select * from products where price = 5000;

-- 2.将查询出的最高价格作为条件查询, 获取商品信息

select * from products where price = (select max(price) from products);

#2 查询化妆品分类下的 商品名称 商品价格

-- 1. 查询化妆品分类的cid

select cid from category where cname = '化妆品'; --c003

select pname, price from products where cid = 'c003';

-- 2. 将查询出的化妆品分类cid作为条件, 查询 商品名称 商品价格

select pname, price from products

where cid = (select cid from category where cname = '化妆品');

#3 查询小于平均价格的商品信息

-- 1.查询出商品平均价格

select avg(price) from products; -- 1866.6667

-- 2.将查询出的商品平均价格作为条件, 查询商品信息

select * from products

where price < (select avg(price) from products);

[](()多行子查询

====================================================================

多行子查询: 查询出的结果为一列多行(多个数据) 如: 化妆品类别下的商品cid, , 可以使用判断符号 如: in all any

In : 等于任意一个

使用方式:

in(值1, 值2 ...)

all: 所有

使用方式:

如: 字段 > |

any: 任意一个

使用方式

如: 字段 > | < any(值1, 值2 ...) 大于任意一个值

如: 字段 = any(值1, 值2 ...) 等于任意一个值 效果等同于 in