|
目录
前言多表查询笛卡尔积、交叉连接内连接外连接左连接右连接表的复用嵌套查询标量子查询列子查询行子查询表子查询
练习总结
前言
多表查询是在数据库中同时操作多个表来获取所需的数据。它允许我们根据相关性将不同的表连接起来，以便进行更复杂和有针对性的数据检索。
在多表查询中，我们通常使用一种称为“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_cardnamesex1张三男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之后
标量子查询
子查询返回的结果是单个值(数字、字符串、日期等)，最简单的形式，这种子查询称为标量子查询。
常用的操作符： = > >= < '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 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关键字定义关联条件。 表别名：当查询涉及多个表时，为了简化语句并提高可读性，可以为表分配别名。别名是对表的简短命名，将其用作查询中的引用。 查询结果：多表查询的结果是一个包含合并数据的单个结果集。该结果集可以包含来自不同表的列和行，满足连接条件的行将被返回。 性能优化：多表查询在处理大型数据库时可能导致性能问题。为了优化查询，可以使用适当的索引、优化连接条件、使用合适的连接类型以及避免不必要的重复数据等策略。 数据完整性：多表查询时，需要确保所选的关联字段具有一致和准确的值。这需要在数据库设计阶段正确设置主键和外键，并进行数据验证，以确保数据的完整性和一致性。
|