前言
多表查询是在数据库中同时操作多个表来获取所需的数据。它允许我们根据相关性将不同的表连接起来,以便进行更复杂和有针对性的数据检索。
在多表查询中,我们通常使用一种称为“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。
根据子查询结果的不同分为:
-
标量子查询(子查询结果为单个值[一行一列])
-
列子查询(子查询结果为一列,但可以是多行)
-
行子查询(子查询结果为一行,但可以是多列)
-
表子查询(子查询结果为多行多列[相当于子查询结果是一张表])
子查询可以书写的位置:
- where之后
- from之后
- select之后
标量子查询
子查询返回的结果是单个值(数字、字符串、日期等),最简单的形式,这种子查询称为标量子查询。
常用的操作符: = <> > >= < <=
案例1:查询"教研部"的所有员工信息
可以将需求分解为两步:
- 查询 “教研部” 部门ID
- 根据 “教研部” 部门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.查询"方东白"的入职日期
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 | 不在指定的集合范围之内 |
案例:查询"教研部"和"咨询部"的所有员工信息
分解为以下两步:
- 查询 “销售部” 和 “市场部” 的部门ID
- 根据部门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
案例:查询与"韦一笑"的入职日期及职位都相同的员工信息
可以拆解为两步进行:
- 查询 “韦一笑” 的入职日期 及 职位
- 查询与"韦一笑"的入职日期及职位相同的员工信息
-- 查询"韦一笑"的入职日期 及 职位
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” 之后的员工信息 , 及其部门信息
分解为两步执行:
- 查询入职日期是 “2006-01-01” 之后的员工信息
- 基于查询到的员工信息,在查询对应的部门信息
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
总结
在数据库中,多表查询是一种非常重要和常见的技术,用于从多个相关的表中检索数据。以下是多表查询的一些关键总结:
-
连接操作:多表查询通过连接操作将多个表关联起来。常见的连接类型包括内连接、左连接、右连接和全连接。连接操作基于关联条件,将满足条件的行合并在一起,形成一个结果集。
-
关联条件:关联条件用于指定连接两个表的方式。通常使用主键和外键之间的关联关系作为条件,例如,两个表中有相同值的字段。关联条件的选择对查询的结果产生重要影响。
-
查询语句:多表查询使用SQL语言编写。在查询中,需要选择要返回的列以及要连接的表。可以使用JOIN关键字来指定表之间的连接方式,并使用ON关键字定义关联条件。
-
表别名:当查询涉及多个表时,为了简化语句并提高可读性,可以为表分配别名。别名是对表的简短命名,将其用作查询中的引用。
-
查询结果:多表查询的结果是一个包含合并数据的单个结果集。该结果集可以包含来自不同表的列和行,满足连接条件的行将被返回。
-
性能优化:多表查询在处理大型数据库时可能导致性能问题。为了优化查询,可以使用适当的索引、优化连接条件、使用合适的连接类型以及避免不必要的重复数据等策略。
-
数据完整性:多表查询时,需要确保所选的关联字段具有一致和准确的值。这需要在数据库设计阶段正确设置主键和外键,并进行数据验证,以确保数据的完整性和一致性。