MySQL多表查询（通俗易懂）--持续更新

前置概念

注意：在下面sql语句中，关键词没有用大写，主要为了通俗易懂，大家在写的时候应该还是要大写的。

---

1.数据库表不规范设计的缺陷引出

首先我们创建一个数据库test_db

然后创建一个员工表：employee

• 字段有
  • e_name 员工名称
  • e_age 员工年龄
  • dep_name部门名称
  • dep_location部门位置

# 1.创建test_db数据库
create database test_db;

# 2.使用test_db数据库
use test_db;

# 3.创建员工表
create table employee(
	e_name varchar(20) comment '员工名称',
	e_age int comment '员工年龄',
	dep_name varchar(20) comment '部门名称',
	dep_location varchar(20) comment '部门位置'
);

# 4.员工表添加数据
insert into employee values('CodingGuan',13,'研发部','广州');
insert into employee values('刘诗诗',14,'运营部','广州');
insert into employee values('范冰冰',15,'销售部','深圳');
insert into employee values('刘德华',16,'研发部','广州');
insert into employee values('张学友',17,'销售部','深圳');
insert into employee values('张三',18,'运营部','广州');

查看员工表数据

select * from employee;

问题

• 一个表中出现了两类数据：部门数据和员工数据
• 出现重复数据：没创建一个角色的时候都需要写重复的部门名称以及部门位置

解决方案

• 通过数据库设计范式的第一范式
  • 数据库设计范式作用：建立冗余较小结构合理的数据库，设计数据库时候遵循的规矩
• 在这里我们遵循数据库第一范式
  • 第一范式的概念：原子性，做到列不可以拆分位置，每个字段都是不可再分的最小数据单元

在满足第一范式我们还需要满足第二范式及第三范式，这个知识大家可以后面通过博客内容进行了解，这里不做过多描述。

---

所以我们将上面的表拆分为：员工表、部门表

• 从中还引出一个特点
• 1个部门有很多员工，员工属于一个部门，一对多的关系
• 员工是跟随部门的存在所存在，所以：部门-主表；员工:从表

---

2.不规范改进

部门表

• 部门编号：id
• 部门名称：dep_name
• 部门位置：dep_location

员工表

• 员工编号：id
• 员工名称：emp_name
• 员工年龄：emp_age
• 部门编号：dep_id（注意：外键约束中，约束的数据类型应该一致）

sql语句

# 1.删除刚刚创建的employee表
drop table employee;

# 2.创建部门表（部门的位置还可以在分）
create table department(
	id int primary key auto_increment comment '部门编号',
	dep_name varchar(20) comment '部门名称',
	dep_location varchar(20) comment '部门位置'
);

知识补充：创建、添加、修改外键约束的语法

• 创建表的时候添加外键约束

constraint[可以省略] 外键约束名称(可以省略) foreign key (本表中要建立约束的外键字段) references 主表表名(主表字段)

• 创建表完成后在添加外键

alter table 从表 add constraint[可以省略] 外键约束名称(可以省略) foreign key (本表中要建立约束的外键字段) references 主表表名(主表字段)

• 删除根据外键名称外键约束

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

# 3.创建员工表
create table employee(
	id int primary key auto_increment comment '员工编号',
	emp_name varchar(20) comment '员工名称',
	emp_age int comment '员工年龄',
	dep_id int comment '外键约束：部门编号',
	CONSTRAINT dep_id_for foreign key(dep_id) references department(id)
);

注意：

• 由于建立了约束，所以我们在添加数据的时候，应该先添加部门表的数据，然后再去添加员工表的数据。
• 因为两个表建立了约束关系，主表拥有的数据从表才能拥有，从表不能添加主表以外的数据

# 4.添加部门表数据
insert into department values(1,'研发部','广州');
insert into department values(2,'运营部','广州');
insert into department values(3,'销售部','深圳');

# 5.员工表添加数据
insert into employee values(1,'CodingGuan','13',1);
insert into employee values(2,'刘诗诗','14',2);
insert into employee values(3,'范冰冰','15',3);
insert into employee values(4,'刘德华','16',1);
insert into employee values(5,'张学友','17',3);
insert into employee values(6,'张三','18',2);

 员工表

select * from employee;

部门表

select * from department;

 这样我们的数据就可以比较的清晰，但是存在一些缺陷，例如dep_location部门位置还可以在衍生出一个表，这些交给大家去实现了，还有数据结构不规范等。

---

3.级联删除

• 我们在没有使用级练删除之前，我们删除上面两个表的数据，因遵循的规则是：先删除员工表的数据，当部门下没有员工数据的时候，在删除部门数据
• 如果直接删除部门数据，就会报错

例如我们直接删除部门表中研发部的数据：

就会报外键约束的错误：a foreign key constraint fails

# 删除研发部数据
delete from department where dep_name = '研发部';

 所以我们应该删除员工数据，在删除部门数据

例如下面操作：

# 先删除员工数据
delete from employee where dep_id = 1;

# 然后删除研发部数据
delete from department where dep_name = '研发部';

如果我们感觉这种删除的方式有点费劲，想删除部门数据的数据的时候，就删除员工的数据，这时级联删除就出来了

级联删除概念：

• 删除主表数据的同时,也删除掉从表数据

• 在添加表约束语句后面添加on delete cascade

• cascade(n.)级联

例如

我们先删除刚刚创建的外键约束

# 删除员工表和部门表的约束
alter table employee drop foreign key dep_id_for;

然后我们添加外键约束同时标识为级联删除

alter table employee add constraint dep_id_for foreign key (dep_id) references department(id) on delete cascade;

 

在刚刚表数据基础上我们直接删除运营部数据

delete from department where dep_name = '运营部';

可以看到，我们轻而易举的就删除了数据，这就是级联删除的用法。

---

4.多表查询

在了解多表查询的时候，我们先了解表和表之间有什么关系。

在表和表的关系中存在着三种关系

• 一对一
• 一对多
• 多对多

下面我们对着三种关系进行了解

---

一对一

• 在一对一关系表中，例如我们一个人对应一个身份证。
• 建表原则
• 主表的主键和从表的外键（唯一），使用unique唯一关键字

# 创建身份证表
create table person_id_card(
	card_number varchar(20) UNIQUE comment '身份证号码'
);

# 创建一个人表
create table person(
	id int primary key auto_increment comment '人编号', 
	name varchar(20) comment '人名字',
	card_number varchar(20) UNIQUE,
	CONSTRAINT card_number_for FOREIGN key (card_number) REFERENCES person_id_card(card_number)
);

# 插入身份证
insert into person_id_card values('44192939999929299');

# 插入员工
INSERT into  person values(1,'张三','44192939999929299');

---

一对多

• 例如我们一个部门有多个员工。一个班级有多名学生。
• 建表规则：在从表（多的一方）设立一个字段，指向主表的字段

---

多对多关系

例如

• 我们老师教很多学生，学生有很多老师。
• 一个学生可以上很多课，一门课，也有很多学生学习。
• 建表规则：创建第三张表，第三张表设立两个字段，分别指向各表的主键。

---

---

多表查询相关操作

数据准备

创建分类表和商品表

# 多表查询
# 创建分类表. 一个分类有很多商品。分类:主表.  商品:从表
create table category(
	c_id varchar(30) primary key  comment '分类编号',
	c_name varchar(30) comment '分类名称'
);
show tables;
# 创建商品表
create table product(
	p_id varchar(32) primary key  comment '商品编号',
	p_name varchar(30) comment '商品名称',
	p_price int comment '商品价格',
	flag varchar(2) comment '是否上架标记：1>上架；0>下架',
	category_id varchar(30) comment '主表约束字段',
	constraint c_id_for foreign key (category_id) REFERENCES category(c_id)
);

# 分类表添加数据
insert into category values ('c001','家电');
insert into category values ('c002','鞋服');
insert into category values ('c003','化妆品');
insert into category values ('c004','汽车');


# 商品表添加数据

INSERT INTO product VALUES('p001','小米电视机',5000,'1','c001');
INSERT INTO product VALUES('p002','格力空调',5000,'1','c001');
INSERT INTO product VALUES('p003','美的冰箱',5000,'1','c001');
INSERT INTO product VALUES('p004','篮球鞋',800,'1','c002');
INSERT INTO product VALUES('p005','运动裤',200,'1','c002');
INSERT INTO product  VALUES('p006','T恤',300,'1','c002');
INSERT INTO product  VALUES('p007','冲锋衣',2000,'1','c002');
INSERT INTO product  VALUES('p008','神仙水',800,'1','c003');
INSERT INTO product VALUES('p009','大宝',200,'1','c003');

默认情况下我们直接进行两个表查询，就会出现笛卡尔积现象

• 笛卡尔积现象简单的说就是：A表XB表 = 数据集合

例如：我们查询商品表和分类表

# 笛卡尔积：两张表数据直接查询，获取AxB表的数据（冗余）
select * from category,product;

 我们肯定不要这种数据，所以我们就出现了了多表查询一些操作，例如内连接、外连接等等。

---

内连接

• 在内连接中，就是通过指定的条件匹配两张表的数据，匹配上就显示匹配不上就显示
• 内连接一般分为两种
  • 隐式内链接：用SQL92写法
    • 特点：用where指定连接的条件
  • 显示内连接：用SQL99写法
    • 特点：用表1 [inner] join 表2 on的方式指定连接的条件

---

我们首先看隐式内链接：

例如：

• 查询所有商品名称p_name、商品价格p_price，并且显示商品分类的名称c_name
• 商品表的分类编号p_category_id和分类表的分类编号c_id对应，根据这个进行多表连接

select p.p_name,p.p_price,c.c_name
from category c,product p 
where c.c_id = p.category_id;

---

现在我们一般使用显示内连接：

语法格式：

select 字段名

from 左表  [inner] join 右表

on 条件;

注意：inner可以省略

还是跟上面一样：

例如：

• 查询所有商品名称p_name、商品价格p_price，并且显示商品分类的名称c_name
• 商品表的分类编号p_category_id和分类表的分类编号c_id对应，根据这个进行多表连接

# 显示内链接 表1 [inner] join 右表 on 条件
select p.p_name,p.p_price,c.c_name
from category c inner join product p
on c_id = p.category_id;

---

 左外连接

• 左外连接就是，左边不管匹配不匹配都全部显示，右边是匹配的就显示，匹配不上就不显示
• 语法： 表1 left [inner] join 表2 on 条件

例如：

• 我们查询在分类下，每个商品都个数有多少
  • 分类是左表，显示所有分类
  • 商品是右表，因为商品有分类才记录，没有分类就不记录

# 左外链接 左边全部数据，右边部分数据
# 查询每个分类下商品的个数 count(p.p_name) 会输出对应记录数
select c.c_name,count(p.p_name)
from product p right join category c
on p.category_id = c.c_id
group by c.c_name;

---

右外连接

• 右外连接就是，右边不管匹配不匹配都全部显示，左边匹配到右表就显示，没有匹配就不显示。
• 语法：表1 right [inner] join 表2 on 条件
• 我们可以把上面的换个思想应用就可以

---

子查询

• 自查询简单来说就是将一个select查询出来的结果给另一个select语句使用
• 例如：select * from 表 where 字段 > (select ... from ...)这样的，用一个小括号包着的

通过案例来了解下：

例如：子查询查询出最高的商品信息。

• 查询商品最贵的(MAX(p_price)) 的商品编号给另一条select语句使用

select * from product
where p_price = (select (MAX(p_price)) from product);

例如：查询化妆品分类下的商品名称和商品价格

select p_name,p_price,category_id
from product 
where category_id = (select c_id from category where c_name='化妆品');

例如： 查询商品价格小于平均价格的商品信息

select * from product 
where p_price < (select AVG(p_price) from product);

例如：查询商品中，价格大于500的商品信息，商品名称，商品价格，商品所属分类名称

select p.p_name,p.p_price,c.c_name
from product p join category c
on p.category_id = c.c_id
where p_id in (select p_id from product where p_price>500);

select p.p_name,p.p_price,c.c_name
from product p JOIN (select * from category) c
on p.category_id = c.c_id
where p.p_price>500;

基本内容就到这里，后续将继续推送多表查询的相关例子。