# 多表查询
创建学院表
create table college(
id int auto_increment primary key,
college_name varchar(50) not null comment '学院名'
)engine=innodb;
insert into college values
(null, "制药工程学院"),
(null, "药学院"),
(null, "健康管理与生物技术学院"),
(null, "食品学院"),
(null, "换妆品与艺术设计学院"),
(null, "软件学院");
create table speciality(
id int auto_increment primary key,
speciality_name varchar(50) not null comment "专业名"
)engine=innodb;
insert into speciality values
(null, "化学制药"),
(null, "药品生产技术"),
(null, "药学"),
(null, "健康管理"),
(null, "老年保健"),
(null, "餐饮管理"),
(null, "食品质量"),
(null, "视觉设计"),
(null, "艺术设计"),
(null, "软件技术"),
(null, "大数据技术");
create table college_speciality(
id int auto_increment primary key,
college_id int unsigned not null comment '学院ID',
speciality_id int unsigned not null comment '专业ID'
)engine=innodb;
insert into college_speciality values
(null, 1, 1),
(null, 1, 2),
(null, 2, 3),
(null, 3, 4),
(null, 3, 5),
(null, 4, 6),
(null, 4, 7),
(null, 5, 8),
(null, 5, 9),
(null, 6, 10),
(null, 6, 11);
查询 软件学院 下有什么专业
1. 查询 软件学院 的学院ID
select id from college where college_name = "软件学院";
结果: id 6
2. 查询学院专业表, 查询学院ID为6的专业ID
select speciality_id from college_speciality where college_id = 6;
结果 10 11
3. 查询专业表, 查询专业ID为 10 或者 11 的专业名
select speciality_name from speciality where id = 10 or id = 11;
+-----------------+
| speciality_name |
+-----------------+
| 软件技术 |
| 大数据技术 |
+-----------------+
查询 老年保健 专业输入哪个学院
1. 查询 老年保健 专业的ID
select id from speciality where speciality_name = "老年保健";
结果: 5
2. 查询专业ID为5, 对应的学院ID
select college_id from college_speciality where speciality_id = 5;
结果: 3
3. 查询学院ID为3的学院名
select college_name from college where id = 3;
结果: 健康管理与生物技术学院
2. 查询专业ID为5, 对应的学院ID
select college_id from college_speciality where speciality_id = 5;
结果: 3
3. 查询学院ID为3的学院名
select college_name from college where id = 3;
结果: 健康管理与生物技术学院
select
字段
from
表名1
join 表2 on 关联字段
join 表3 on 关联字段
join 表4 on 关联字段
join 表5 on 关联字段
select
college.college_name,
speciality.speciality_name
from
college
join college_speciality on college.id = college_speciality.college_id
join speciality on speciality.id = college_speciality.speciality_id;
+-----------------------------------+--------------------+
| college_name | speciality_name |
+-----------------------------------+--------------------+
| 制药工程学院 | 化学制药 |
| 制药工程学院 | 药品生产技术 |
| 药学院 | 药学 |
| 健康管理与生物技术学院 | 健康管理 |
| 健康管理与生物技术学院 | 老年保健 |
| 食品学院 | 餐饮管理 |
| 食品学院 | 食品质量 |
| 换妆品与艺术设计学院 | 视觉设计 |
| 换妆品与艺术设计学院 | 艺术设计 |
| 软件学院 | 软件技术 |
| 软件学院 | 大数据技术 |
+-----------------------------------+--------------------+
select
*
from
college
join college_speciality on college.id = college_speciality.college_id
join speciality on speciality.id = college_speciality.speciality_id;
+----+-----------------------------------+----+------------+---------------+----+--------------------+
| id | college_name | id | college_id | speciality_id | id | speciality_name |
+----+-----------------------------------+----+------------+---------------+----+--------------------+
| 1 | 制药工程学院 | 1 | 1 | 1 | 1 | 化学制药 |
| 1 | 制药工程学院 | 2 | 1 | 2 | 2 | 药品生产技术 |
| 2 | 药学院 | 3 | 2 | 3 | 3 | 药学 |
| 3 | 健康管理与生物技术学院 | 4 | 3 | 4 | 4 | 健康管理 |
| 3 | 健康管理与生物技术学院 | 5 | 3 | 5 | 5 | 老年保健 |
| 4 | 食品学院 | 6 | 4 | 6 | 6 | 餐饮管理 |
| 4 | 食品学院 | 7 | 4 | 7 | 7 | 食品质量 |
| 5 | 换妆品与艺术设计学院 | 8 | 5 | 8 | 8 | 视觉设计 |
| 5 | 换妆品与艺术设计学院 | 9 | 5 | 9 | 9 | 艺术设计 |
| 6 | 软件学院 | 10 | 6 | 10 | 10 | 软件技术 |
| 6 | 软件学院 | 11 | 6 | 11 | 11 | 大数据技术 |
+----+-----------------------------------+----+------------+---------------+----+--------------------+
查询 软件学院 下有什么专业
select
*
from
college
join college_speciality on college.id = college_speciality.college_id
join speciality on speciality.id = college_speciality.speciality_id
where college.college_name = "软件学院";
+----+--------------+----+------------+---------------+----+-----------------+
| id | college_name | id | college_id | speciality_id | id | speciality_name |
+----+--------------+----+------------+---------------+----+-----------------+
| 6 | 软件学院 | 10 | 6 | 10 | 10 | 软件技术 |
| 6 | 软件学院 | 11 | 6 | 11 | 11 | 大数据技术 |
+----+--------------+----+------------+---------------+----+-----------------+
select
speciality.speciality_name
from
college
join college_speciality on college.id = college_speciality.college_id
join speciality on speciality.id = college_speciality.speciality_id
where college.college_name = "软件学院";
+-----------------+
| speciality_name |
+-----------------+
| 软件技术 |
| 大数据技术 |
+-----------------+
查询 老年保健 专业输入哪个学院
select
*
from
college
join college_speciality on college.id = college_speciality.college_id
join speciality on speciality.id = college_speciality.speciality_id
where speciality.speciality_name = "老年保健";
+----+-----------------------------------+----+------------+---------------+----+-----------------+
| id | college_name | id | college_id | speciality_id | id | speciality_name |
+----+-----------------------------------+----+------------+---------------+----+-----------------+
| 3 | 健康管理与生物技术学院 | 5 | 3 | 5 | 5 | 老年保健 |
+----+-----------------------------------+----+------------+---------------+----+-----------------+
select
college.college_name
from
college
join college_speciality on college.id = college_speciality.college_id
join speciality on speciality.id = college_speciality.speciality_id
where speciality.speciality_name = "老年保健";
+-----------------------------------+
| college_name |
+-----------------------------------+
| 健康管理与生物技术学院 |
+-----------------------------------+
从哪个表开始?
select
college.id,
college.college_name,
college_speciality.id,
college_speciality.college_id,
college_speciality.speciality_id,
speciality.id,
speciality.speciality_name
from
college
join college_speciality on college.id = college_speciality.college_id
join speciality on speciality.id = college_speciality.speciality_id;
+----+-----------------------------------+----+------------+---------------+----+--------------------+
| id | college_name | id | college_id | speciality_id | id | speciality_name |
+----+-----------------------------------+----+------------+---------------+----+--------------------+
| 1 | 制药工程学院 | 1 | 1 | 1 | 1 | 化学制药 |
| 1 | 制药工程学院 | 2 | 1 | 2 | 2 | 药品生产技术 |
| 2 | 药学院 | 3 | 2 | 3 | 3 | 药学 |
| 3 | 健康管理与生物技术学院 | 4 | 3 | 4 | 4 | 健康管理 |
| 3 | 健康管理与生物技术学院 | 5 | 3 | 5 | 5 | 老年保健 |
| 4 | 食品学院 | 6 | 4 | 6 | 6 | 餐饮管理 |
| 4 | 食品学院 | 7 | 4 | 7 | 7 | 食品质量 |
| 5 | 换妆品与艺术设计学院 | 8 | 5 | 8 | 8 | 视觉设计 |
| 5 | 换妆品与艺术设计学院 | 9 | 5 | 9 | 9 | 艺术设计 |
| 6 | 软件学院 | 10 | 6 | 10 | 10 | 软件技术 |
| 6 | 软件学院 | 11 | 6 | 11 | 11 | 大数据技术 |
+----+-----------------------------------+----+------------+---------------+----+--------------------+
select
college.id,
college.college_name,
college_speciality.id,
college_speciality.college_id,
college_speciality.speciality_id,
speciality.id,
speciality.speciality_name
from
speciality
join college_speciality on speciality.id = college_speciality.speciality_id
join college on college.id = college_speciality.college_id;
+----+-----------------------------------+----+------------+---------------+----+--------------------+
| id | college_name | id | college_id | speciality_id | id | speciality_name |
+----+-----------------------------------+----+------------+---------------+----+--------------------+
| 1 | 制药工程学院 | 1 | 1 | 1 | 1 | 化学制药 |
| 1 | 制药工程学院 | 2 | 1 | 2 | 2 | 药品生产技术 |
| 2 | 药学院 | 3 | 2 | 3 | 3 | 药学 |
| 3 | 健康管理与生物技术学院 | 4 | 3 | 4 | 4 | 健康管理 |
| 3 | 健康管理与生物技术学院 | 5 | 3 | 5 | 5 | 老年保健 |
| 4 | 食品学院 | 6 | 4 | 6 | 6 | 餐饮管理 |
| 4 | 食品学院 | 7 | 4 | 7 | 7 | 食品质量 |
| 5 | 换妆品与艺术设计学院 | 8 | 5 | 8 | 8 | 视觉设计 |
| 5 | 换妆品与艺术设计学院 | 9 | 5 | 9 | 9 | 艺术设计 |
| 6 | 软件学院 | 10 | 6 | 10 | 10 | 软件技术 |
| 6 | 软件学院 | 11 | 6 | 11 | 11 | 大数据技术 |
+----+-----------------------------------+----+------------+---------------+----+--------------------+
select
college.id,
college.college_name,
college_speciality.id,
college_speciality.college_id,
college_speciality.speciality_id,
speciality.id,
speciality.speciality_name
from
college_speciality
join college on college.id = college_speciality.college_id
join speciality on speciality.id = college_speciality.speciality_id;
+----+-----------------------------------+----+------------+---------------+----+--------------------+
| id | college_name | id | college_id | speciality_id | id | speciality_name |
+----+-----------------------------------+----+------------+---------------+----+--------------------+
| 1 | 制药工程学院 | 1 | 1 | 1 | 1 | 化学制药 |
| 1 | 制药工程学院 | 2 | 1 | 2 | 2 | 药品生产技术 |
| 2 | 药学院 | 3 | 2 | 3 | 3 | 药学 |
| 3 | 健康管理与生物技术学院 | 4 | 3 | 4 | 4 | 健康管理 |
| 3 | 健康管理与生物技术学院 | 5 | 3 | 5 | 5 | 老年保健 |
| 4 | 食品学院 | 6 | 4 | 6 | 6 | 餐饮管理 |
| 4 | 食品学院 | 7 | 4 | 7 | 7 | 食品质量 |
| 5 | 换妆品与艺术设计学院 | 8 | 5 | 8 | 8 | 视觉设计 |
| 5 | 换妆品与艺术设计学院 | 9 | 5 | 9 | 9 | 艺术设计 |
| 6 | 软件学院 | 10 | 6 | 10 | 10 | 软件技术 |
| 6 | 软件学院 | 11 | 6 | 11 | 11 | 大数据技术 |
+----+-----------------------------------+----+------------+---------------+----+--------------------+
select
college.id,
college.college_name,
college_speciality.id,
college_speciality.college_id,
college_speciality.speciality_id,
speciality.id,
speciality.speciality_name
from
college_speciality
join speciality on speciality.id = college_speciality.speciality_id
join college on college.id = college_speciality.college_id;
+----+-----------------------------------+----+------------+---------------+----+--------------------+
| id | college_name | id | college_id | speciality_id | id | speciality_name |
+----+-----------------------------------+----+------------+---------------+----+--------------------+
| 1 | 制药工程学院 | 1 | 1 | 1 | 1 | 化学制药 |
| 1 | 制药工程学院 | 2 | 1 | 2 | 2 | 药品生产技术 |
| 2 | 药学院 | 3 | 2 | 3 | 3 | 药学 |
| 3 | 健康管理与生物技术学院 | 4 | 3 | 4 | 4 | 健康管理 |
| 3 | 健康管理与生物技术学院 | 5 | 3 | 5 | 5 | 老年保健 |
| 4 | 食品学院 | 6 | 4 | 6 | 6 | 餐饮管理 |
| 4 | 食品学院 | 7 | 4 | 7 | 7 | 食品质量 |
| 5 | 换妆品与艺术设计学院 | 8 | 5 | 8 | 8 | 视觉设计 |
| 5 | 换妆品与艺术设计学院 | 9 | 5 | 9 | 9 | 艺术设计 |
| 6 | 软件学院 | 10 | 6 | 10 | 10 | 软件技术 |
| 6 | 软件学院 | 11 | 6 | 11 | 11 | 大数据技术 |
+----+-----------------------------------+----+------------+---------------+----+--------------------+
别名 as
select
college.id as c_id,
college.college_name,
college_speciality.id as c_s_id,
college_speciality.college_id,
college_speciality.speciality_id,
speciality.id as s_id,
speciality.speciality_name
from
college_speciality
join speciality on speciality.id = college_speciality.speciality_id
join college on college.id = college_speciality.college_id;
+------+-----------------------------------+--------+------------+---------------+------+--------------------+
| c_id | college_name | c_s_id | college_id | speciality_id | s_id | speciality_name |
+------+-----------------------------------+--------+------------+---------------+------+--------------------+
| 1 | 制药工程学院 | 1 | 1 | 1 | 1 | 化学制药 |
| 1 | 制药工程学院 | 2 | 1 | 2 | 2 | 药品生产技术 |
| 2 | 药学院 | 3 | 2 | 3 | 3 | 药学 |
| 3 | 健康管理与生物技术学院 | 4 | 3 | 4 | 4 | 健康管理 |
| 3 | 健康管理与生物技术学院 | 5 | 3 | 5 | 5 | 老年保健 |
| 4 | 食品学院 | 6 | 4 | 6 | 6 | 餐饮管理 |
| 4 | 食品学院 | 7 | 4 | 7 | 7 | 食品质量 |
| 5 | 换妆品与艺术设计学院 | 8 | 5 | 8 | 8 | 视觉设计 |
| 5 | 换妆品与艺术设计学院 | 9 | 5 | 9 | 9 | 艺术设计 |
| 6 | 软件学院 | 10 | 6 | 10 | 10 | 软件技术 |
| 6 | 软件学院 | 11 | 6 | 11 | 11 | 大数据技术 |
+------+-----------------------------------+--------+------------+---------------+------+--------------------+
别名 省略 as
select
college.id c_id,
college.college_name,
college_speciality.id c_s_id,
college_speciality.college_id,
college_speciality.speciality_id,
speciality.id s_id,
speciality.speciality_name
from
college_speciality
join speciality on speciality.id = college_speciality.speciality_id
join college on college.id = college_speciality.college_id;
+------+-----------------------------------+--------+------------+---------------+------+--------------------+
| c_id | college_name | c_s_id | college_id | speciality_id | s_id | speciality_name |
+------+-----------------------------------+--------+------------+---------------+------+--------------------+
| 1 | 制药工程学院 | 1 | 1 | 1 | 1 | 化学制药 |