MySQL第十二周

最新推荐文章于 2024-05-30 14:44:15 发布

ZHANG y_

最新推荐文章于 2024-05-30 14:44:15 发布

阅读量24

点赞数

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/ZHANG_____LE/article/details/130748703

版权

# 多表查询

创建学院表

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;

结果：健康管理与生物技术学院

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;

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;

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;

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;

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;

别名 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;