数据库基础知识点----多表查询

---

前言

多表查询是在数据库中同时操作多个表来获取所需的数据。它允许我们根据相关性将不同的表连接起来，以便进行更复杂和有针对性的数据检索。

在多表查询中，我们通常使用一种称为“JOIN”的操作来连接表。JOIN操作根据指定的关联条件将两个表中的数据合并，生成一个临时的结果集，然后在该结果集上执行其他查询操作。

---

多表查询

多表查询：查询时从多张表中获取所需数据

单表查询的SQL语句：select 字段列表 from 表名;

那么要执行多表查询，只需要使用逗号分隔多张表即可，如： select 字段列表 from 表1, 表2;

笛卡尔积、交叉连接

将两张表中的数据两两组合，得到的结果就是交叉连接的结果，也称为笛卡尔积。

集合A：{a,b}

集合B：{1,2,3}

集合A x 集合B = {a1,a2,a3,b1,b2,b3}

select * from 表1,表2; 
select * from 表1 cross join 表2; 
select * from 表1 inner join 表2;

以上三种方式都能将两张表中的数据互相组合，其中有很多无效数据。

笛卡尔积：笛卡尔乘积是指在数学中，两个集合(A集合和B集合)的所有组合情况。

在多表查询时，需要消除无效的笛卡尔积，只保留表关联部分的数据

内连接

内连接：相当于查询A、B交集部分数据

内连接从语法上可以分为：

• 隐式内连接

• 显式内连接

隐式内连接语法：

select  字段列表   from   表1 , 表2   where  条件 ... ;

显式内连接语法：

select  字段列表   from   表1  [ inner ]  join 表2  on  连接条件 ... ;

在交叉连接的基础上，筛选出相关联的数据

select * from 表1,表2 where 表1.字段 = 表2.字段; 
select * from 表1 inner join 表2 on 表1.字段 = 表2.字段; 

-- 查询所有图书详情和类型名 
select * from 图书详情表 t1,图书类型表 t2 where t1.类型编号=t2.类型编号; 

select * from 图书详情表 t1 inner join 图书类型表 t2 on t1.类型编号=t2.类型编号;

注意：

• 通常是通过主表的主键字段关联从表的外键字段

• 如果两张表中关联的字段名一致，一定要通过"表名.字段名"进行区分，通常还会给表重命名

多表查询时给表起别名：

• tableA as 别名1 , tableB as 别名2 ;

• tableA 别名1 , tableB 别名2 ;

• 注意事项:

  一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段。

• 如果使用inner join，带条件时需要加入where子句;如果使用逗号隔开多个表，条件使用and拼接在最后

• 内连接只会显示两张表中有关联的数据

外连接

保证一张表中的数据完整显示的情况下，关联另一张表中的数据，没有关联的用null表示

左连接

-- 完整显示表1中的数据，关联表2中的数据 

select * from 表1 left join 表2 on 表1.字段 = 表2.字段; 

-- 最终会显示表1中的所有数据，关联表2中的数据

右连接

-- 完整显示表1中的数据，关联表2中的数据 

select * from 表2 right join 表1 on 表1.字段 = 表2.字段; 

-- 最终会显示表1中的所有数据，关联表2中的数据

注意事项：

左外连接和右外连接是可以相互替换的，只需要调整连接查询时SQL语句中表的先后顺序就可以了。而我们在日常开发使用时，更偏向于左外连接。

表的复用

可以在一个查询中，将一张表使用多次。一定要将表重命名。

id_card	name	sex
1	张三	男
2	张三	女
3	李白	男

-- 查询同名的人 

select * from 表 t1,表 t2 where t1.name=t2.name and t1.id_card!=t2.id_card

嵌套查询

SQL语句中嵌套select语句，称为嵌套查询，又称子查询。

SELECT  *  FROM   t1   WHERE  column1 =  ( SELECT  column1  FROM  t2 ... );

子查询外部的语句可以是insert / update / delete / select 的任何一个，最常见的是 select。

根据子查询结果的不同分为：

1. 标量子查询（子查询结果为单个值[一行一列]）

2. 列子查询（子查询结果为一列，但可以是多行）

3. 行子查询（子查询结果为一行，但可以是多列）

4. 表子查询（子查询结果为多行多列[相当于子查询结果是一张表]）

子查询可以书写的位置：

1. where之后
2. from之后
3. select之后

标量子查询

子查询返回的结果是单个值(数字、字符串、日期等)，最简单的形式，这种子查询称为标量子查询。

常用的操作符： = <> > >= < <=

案例1：查询"教研部"的所有员工信息

可以将需求分解为两步：

1. 查询 “教研部” 部门ID
2. 根据 “教研部” 部门ID，查询员工信息

-- 1.查询"教研部"部门ID
select id from tb_dept where name = '教研部';    
-- 查询结果：2

-- 2.根据"教研部"部门ID, 查询员工信息
select * from tb_emp where dept_id = 2;

-- 合并出上两条SQL语句
select * from tb_emp where dept_id = (select id from tb_dept where name = '教研部');

案例2：查询在 “方东白” 入职之后的员工信息

可以将需求分解为两步：

1. 查询 方东白 的入职日期
2. 查询 指定入职日期之后入职的员工信息

-- 1.查询"方东白"的入职日期
select entrydate from tb_emp where name = '方东白';     #查询结果：2012-11-01
-- 2.查询指定入职日期之后入职的员工信息
select * from tb_emp where entrydate > '2012-11-01';

-- 合并以上两条SQL语句
select * from tb_emp where entrydate > (select entrydate from tb_emp where name = '方东白');

列子查询

子查询返回的结果是一列(可以是多行)，这种子查询称为列子查询。

常用的操作符：

操作符	描述
IN	在指定的集合范围之内，多选一
NOT IN	不在指定的集合范围之内

案例：查询"教研部"和"咨询部"的所有员工信息

分解为以下两步：

1. 查询 “销售部” 和 “市场部” 的部门ID
2. 根据部门ID, 查询员工信息

-- 1.查询"销售部"和"市场部"的部门ID
select id from tb_dept where name = '教研部' or name = '咨询部';    #查询结果：3,2
-- 2.根据部门ID, 查询员工信息
select * from tb_emp where dept_id in (3,2);

-- 合并以上两条SQL语句
select * from tb_emp where dept_id in (select id from tb_dept where name = '教研部' or name = '咨询部');

行子查询

子查询返回的结果是一行(可以是多列)，这种子查询称为行子查询。

常用的操作符：= 、<> 、IN 、NOT IN

案例：查询与"韦一笑"的入职日期及职位都相同的员工信息

可以拆解为两步进行：

1. 查询 “韦一笑” 的入职日期 及 职位
2. 查询与"韦一笑"的入职日期及职位相同的员工信息

-- 查询"韦一笑"的入职日期 及 职位
select entrydate , job from tb_emp where name = '韦一笑';  #查询结果： 2007-01-01 , 2
-- 查询与"韦一笑"的入职日期及职位相同的员工信息
select * from tb_emp where (entrydate,job) = ('2007-01-01',2);

-- 合并以上两条SQL语句
select * from tb_emp where (entrydate,job) = (select entrydate , job from tb_emp where name = '韦一笑');

表子查询

子查询返回的结果是多行多列，常作为临时表，这种子查询称为表子查询。

案例：查询入职日期是 “2006-01-01” 之后的员工信息 , 及其部门信息

分解为两步执行：

1. 查询入职日期是 “2006-01-01” 之后的员工信息
2. 基于查询到的员工信息，在查询对应的部门信息

select * from emp where entrydate > '2006-01-01';

select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;

练习

-- 创建学生信息库 studb;
drop database if EXISTS studb;
create database studb;
use studb;

-- 学生表student
drop table if EXISTS student;
create table student(
	stu_id int not null primary key auto_increment comment '学号',
	stu_name varchar(20) not null comment '姓名',
	stu_sex char(1) comment '性别'
);


-- 课程表course
drop table if EXISTS course;
create table course(
	c_id varchar(20) not null primary key comment '课程号',
	c_name varchar(20) not null comment '课程名',
	c_redit int not null comment '学分'
);



-- 成绩表score
drop table if EXISTS score;
create table score(
	s_no int not null primary key auto_increment comment '成绩编号',
	stu_id int not null comment '学号',
	c_id varchar(20) not null comment '课程号',
	cj int not null comment '成绩',
	foreign key(stu_id) REFERENCES student(stu_id),
	FOREIGN key(c_id) REFERENCES course(c_id)
);




insert into student values(1001,'张晓红','女');
insert into student values(null,'张伟','男');
insert into student values(null,'肖怀伟','男');
insert into student values(null,'卢宇鹏','男');
insert into student values(null,'白思琪','女');
insert into student values(null,'黄鹏','男');
insert into student values(null,'吕思源','女');

insert into course values('c9001','高等数学',8);
insert into course values('c9002','大学英语',8);
insert into course values('c9003','思修',8);
insert into course values('c9004','大学体育',4);


insert into score values(null,'1001','c9003','88');
insert into score values(null,'1001','c9001','79');
insert into score values(null,'1002','c9001','84');
insert into score values(null,'1002','c9003','68');
insert into score values(null,'1003','c9002','78');
insert into score values(null,'1003','c9003','90');
insert into score values(null,'1003','c9004','69');
insert into score values(null,'1004','c9003','55');
insert into score values(null,'1004','c9004','54');
insert into score values(null,'1005','c9003','68');
insert into score values(null,'1005','c9004','74');
insert into score values(null,'1005','c9002','72');

select * from course;
select * from student;
select * from score;



-- 查询每位学生的学号、姓名、所学课程名，考试成绩,对字段重命名
SELECT
	st.stu_id 学号,
	stu_name 姓名,
	c_name 课程名,
	cj 成绩 
FROM
	student st,
	course c,
	score sc 
WHERE
	st.stu_id = sc.stu_id 
	AND c.c_id = sc.c_id

-- 查询所有女生的学号、姓名、学习的课程名,成绩
SELECT
	* 
FROM
	student st
	INNER JOIN score sc ON st.stu_id = sc.stu_id
	INNER JOIN course c ON c.c_id = sc.c_id 
WHERE
	stu_sex = '女'

-- 查询参加了’高等数学‘考试的学生学号、姓名、成绩
SELECT
	st.stu_id,
	stu_name,
	c_name,
	cj 
FROM
	student st,
	course c,
	score sc 
WHERE
	st.stu_id = sc.stu_id 
	AND c.c_id = sc.c_id 
	AND c_name = '高等数学'

-- 查询没有参加考试的学生
SELECT
	st.stu_id,
	stu_name 
FROM
	student st
	LEFT JOIN score sc ON st.stu_id = sc.stu_id 
WHERE
	cj IS NULL
	
-- 查询每门课程的平均成绩、总成绩、最高分、最低分
SELECT
	c.c_id,
	c_name,
	avg( cj ),
	sum( cj ),
	max( cj ),
	min( cj ) 
FROM
	course c,
	score s 
WHERE
	c.c_id = s.c_id 
GROUP BY
	c.c_id
	
-- 查询每个学生的学号、姓名、总分、平均分,显示没有考试的学生
SELECT
	st.stu_id,
	stu_name,
	sum( cj ),
	avg( cj ) 
FROM
	student st
	LEFT JOIN score sc ON st.stu_id = sc.stu_id 
GROUP BY
	st.stu_id
	
-- 查询姓“张”的同学的学号、姓名、考试课程、成绩
SELECT
	st.stu_id,
	stu_name,
	c_name,
	cj 
FROM
	student st,
	score sc,
	course c 
WHERE
	st.stu_id = sc.stu_id 
	AND c.c_id = sc.c_id 
	AND stu_name like '张%'

-- 查询没有及格(60分以下)的学生及其课程名、成绩
SELECT
	stu_id,
	c_name,
	cj 
FROM
	score s,
	course c 
WHERE
	s.c_id = c.c_id 
	AND cj < 60
	
-- 查询男生和女生的平均成绩
SELECT
	stu_sex,
	avg( cj ) 
FROM
	score sc,
	student st 
WHERE
	sc.stu_id = st.stu_id 
GROUP BY
	stu_sex
	
-- 查询总分大于160分的同学的学号、姓名、总分
SELECT
	st.stu_id,
	stu_name,
	sum( cj ) 
FROM
	student st,
	score sc 
WHERE
	st.stu_id = sc.stu_id 
GROUP BY
	st.stu_id 
HAVING
	sum( cj )> 160
	
-- 查询平均分大于80的课程名、平均分
SELECT
	c.c_id,
	c_name,
	avg( cj ) 
FROM
	score s,
	course c 
WHERE
	s.c_id = c.c_id 
GROUP BY
	c.c_id 
HAVING
	avg( cj )> 80

-- 查询参加了3门考试的同学的学号、姓名、课程名、成绩,按成绩降序
-- 查询参加了3门考试的学生编号,将查询出的结果与其他表进行关联查询
select st.stu_id,stu_name,c_name,cj 
from student st,score sc ,course c,
(select stu_id from score group by stu_id having count(cj)=3) temp 
where st.stu_id=sc.stu_id and c.c_id=sc.c_id and st.stu_id=temp.stu_id 
order by st.stu_id ,cj desc



-- 教师排课功能
-- 教师与课程之间属于多对多关系


-- 添加教师表teacher
create table teacher(
	t_id varchar(20) not null primary key,
	t_name varchar(20) not null
)

-- 添加授课表
create  table teach(
	id int not null primary key auto_increment,
	t_id varchar(20) not null,
	c_id varchar(20) not null
)


-- INSERT
insert into teacher values('t001','吴彦祖');
insert into teacher values('t002','易烊千玺');
insert into teacher values('t003','刘德华');
insert into teacher values('t005','李宇春');
insert into teacher values('t004','邓超');

-- 
insert into teach() values(0,'t001','c9001');
insert into teach() values(0,'t001','c9002');
insert into teach() values(0,'t001','c9004');
insert into teach() values(0,'t002','c9003');
insert into teach() values(0,'t002','c9001');
insert into teach() values(0,'t003','c9002');
insert into teach() values(0,'t003','c9003');
insert into teach() values(0,'t003','c9004');
insert into teach() values(0,'t005','c9001');
insert into teach() values(0,'t005','c9002');



-- 查询每个教师的姓名及其所教课程
SELECT
	t_name,
	c_name 
FROM
	teacher t1,
	course c,
	teach t2 
WHERE
	t1.t_id = t2.t_id 
	AND t2.c_id = c.c_id
	
-- 查询每个教师所教课程数量
SELECT
	t_name,
	count( c_id ) 
FROM
	teach t1
	RIGHT JOIN teacher t2 ON t1.t_id = t2.t_id 
GROUP BY
	t2.t_id
	
-- 查询没有排课的教师姓名
SELECT
	t_name 
FROM
	teacher t1
	LEFT JOIN teach t2 ON t1.t_id = t2.t_id 
WHERE
	c_id IS NULL
	
-- 查询授课3门的教师的姓名及其所授课程
SELECT
	t_name,
	c_name 
FROM
	teacher t1,
	teach t2,
	course c,
	( SELECT t_id FROM teach GROUP BY t_id HAVING count( c_id )= 3 ) temp 
WHERE
	t1.t_id = temp.t_id 
	AND t1.t_id = t2.t_id 
	AND c.c_id = t2.c_id

-- 查询每个教师所带学生数量，不显示没有学生的教师
SELECT
	t2.t_id,
	t_name,
	count( DISTINCT stu_id ) 
FROM
	teach t,
	teacher t2,
	score s 
WHERE
	t.t_id = t2.t_id 
	AND t.c_id = s.c_id 
GROUP BY
	t2.t_id;


-- 查询每个教师所教课程的平均分
SELECT
	t_id,
	avg( cj ) 
FROM
	score s,
	course c,
	teach t 
WHERE
	s.c_id = c.c_id 
	AND t.c_id = c.c_id 
GROUP BY
	t_id

-- 查询每个同学的姓名、课程名、每门成绩、按成绩降序
select stu_name,c_name,cj from student s1,score s2,course c where s1.stu_id=s2.stu_id and c.c_id=s2.c_id
order by s1.stu_id,cj desc

-- 查询平均分最高的课程名及其授课教师
select max(avg) from 
(select c_id,avg(cj) avg from score group by c_id) temp

-- 视图
-- 将某个查询得到的结果临时保存为 一张表
create view myview as
select c_id,avg(cj) avg from score group by c_id


select c_name,t_name from 
teach t,teacher t2,course c,(select c_id from myview where avg =(select max(avg) from myview))temp
where t.c_id=temp.c_id and c.c_id=t.c_id and t.t_id=t2.t_id


-- 查询每个学生的学号、姓名、所学课程、成绩，要包含没有考试的学生
create view stu_score as 
SELECT
	st.stu_id,
	stu_name,
	c_name,
	cj 
FROM
	student st
	LEFT JOIN score sc ON st.stu_id = sc.stu_id
	LEFT JOIN course c ON sc.c_id = c.c_id
	
select * from stu_score


-- 行列转换。将stu_score表中的数据，输出为"学号、姓名、课程1、课程2..."格式
select stu_id,stu_name,
sum(if(c_name='思修',cj,null)) as '思修',
avg(if(c_name='高等数学',cj,null)) as '高等数学',
max(if(c_name='大学英语',cj,null)) as '大学英语',
min(if(c_name='大学体育',cj,null)) as '大学体育'
from stu_score group by stu_id

---

总结

在数据库中，多表查询是一种非常重要和常见的技术，用于从多个相关的表中检索数据。以下是多表查询的一些关键总结：

1. 连接操作：多表查询通过连接操作将多个表关联起来。常见的连接类型包括内连接、左连接、右连接和全连接。连接操作基于关联条件，将满足条件的行合并在一起，形成一个结果集。

2. 关联条件：关联条件用于指定连接两个表的方式。通常使用主键和外键之间的关联关系作为条件，例如，两个表中有相同值的字段。关联条件的选择对查询的结果产生重要影响。

3. 查询语句：多表查询使用SQL语言编写。在查询中，需要选择要返回的列以及要连接的表。可以使用JOIN关键字来指定表之间的连接方式，并使用ON关键字定义关联条件。

4. 表别名：当查询涉及多个表时，为了简化语句并提高可读性，可以为表分配别名。别名是对表的简短命名，将其用作查询中的引用。

5. 查询结果：多表查询的结果是一个包含合并数据的单个结果集。该结果集可以包含来自不同表的列和行，满足连接条件的行将被返回。

6. 性能优化：多表查询在处理大型数据库时可能导致性能问题。为了优化查询，可以使用适当的索引、优化连接条件、使用合适的连接类型以及避免不必要的重复数据等策略。

7. 数据完整性：多表查询时，需要确保所选的关联字段具有一致和准确的值。这需要在数据库设计阶段正确设置主键和外键，并进行数据验证，以确保数据的完整性和一致性。