4、多表查询、自关联、子查询

等值连接

select st.name, sc.score from students as st, scores as sc WHERE st.studentNo = sc.studentno;

内连接

# 单连接 - 查询学生信息表每个学生的成绩
select * from students inner join scroes on students.studentNo=scroes.studentno;

# 双连接 - 查询学生信息及学生课对应的成绩
select st.name, co.name, sc.score from students as st 
inner join scores as sc on sc.studentno=st.studentNo 
inner join courses as co on co.courseNo=sc.courseNo;

区别

等值连接和内连接都是取交集，
等值连接：先全部查询，产生临时表-笛卡儿积，然后再用where筛选，性能较差
内连接：连接一张表的过程中，进行判断条件，符合条件的才生成，性能较等值查询好

练习

练习1.查询王昭君的成绩，要求显示姓名、课程名、成绩
select st.name, co.name, sc.score from students as st
inner join scores as sc on sc.courseno=st.studentNo
inner join courses as co on co.courseNo=sc.courseNo
where st.name='王昭君';

练习2 查询男生中最高成绩，要求显示姓名、课程名、成绩
select st.name, co.name, sc.score from students as st
inner join scores as sc on sc.courseno=st.studentNo
inner join courses as co on co.courseNo=sc.courseNo
where sex='男' order by sc.score desc limit 1;

左连接

join前面的表称之位左表，join后面的表称之为右表，

左连接即基于左表，将右表满足条件的数据才能显示，而左表是全部显示

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WyQ1PAW6-1571232111461)(images/1571149011181.png)]

例1：查询所有学生的成绩，包括没有成绩的学生（内连接是取交集）

select * from students as stu
left join scores as sc on stu.studentNo=sc.studentNo;

例2：查询所有学生的成绩，包括没有成绩的学生，需要显示课程名

select * from students as stu
left join scores as sc on stu.studentNo=sc.studedntNo 
left join coures as co on co.courseNo=sc.courseNo;

右连接

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-R6huMNzG-1571232111462)(images/1571149026169.png)]

例3：查询所有课程的成绩，包括没有成绩的课程

select * from scores as sc
right join courses as co on sc.coursesNo=co.couresNo; 

自关联

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kkwdIZIq-1571232111463)(images/1571149517038.png)]

一张表存 省、市、村，覆盖上下级关系

准备练习数据

create table areas(
	aid int primary key, 
	atitle varchar(20), 
	pid int
);

insert into areas values ('130000', '河北省', NULL), ('130100', '石家庄市', '130000'), ('130400', '邯郸市', '130000'), ('130600', '保定市', '130000'), ('130700', '张家口市', '130000'), ('130800', '承德市', '130000'), ('410000', '河南省', NULL), ('410100', '郑州市', '410000'), ('410300', '洛阳市', '410000'), ('410500', '安阳市', '410000'), ('410700', '新乡市', '410000'), ('410800', '焦作市', '410000');

添加区县数据
insert into areas values ('410101', '中原区', '410100'), ('410102', '二七区', '410100'), ('410103', '金水区', '410100');

练习1：查询所有省

select count(*) from areas where pid is null;

练习2：查询郑州市所有区

# 方法1
select * from areas as a1
inner join areas as a2 on a1.aid=a2.pid
-- left join areas as a3 on a3.pid=a2.aid
where a1.atitle='郑州市';

# 方法2
select * from areas as a1
left join areas as a2 on a1.aid=a2.pid
where a1.atitle='郑州市';

练习3：查询河南省所有区县

select * from areas as a1
left join areas as a2 on a1.aid=a2.pid
left join areas as a3 on a3.pid=a2.aid
where a1.atitle='河南省';

子查询

在一个select语句中，嵌入了另外一个select语句，那么被嵌入的select语句被称之位子查询语句

主查询

• 主要查询的对象，第一个条select语句

主查询和子查询的关系

• 子查询是嵌入到主查询中
• 子查询是辅助主查询的，要么充当条件，要么充当数据源
• 自查询也可以是一条完整的语句，独立存在

子查询分类

• 标量子查询：子查询返回的结果是一个数据(一行一数据)
• 列子查询：返回的结果是一列(一行多列)
• 行子查询：返回的结果是一行(一行多列)
• 表级子查询：返回的结果是多行多列

标量子查询

查询班级学生的平均年龄
select avg(age) from students;

查询大于平均年龄的学生
select * from student where age > (select avg(age) from students);

列子查询

查询18岁的学生成绩，显示成绩
select * from scores where studentNo in 
(select st.studentNo from students as st where st.age=18);

表级子查询

查询数据库和系统测试的课程成绩
# 方法1
select * from scores where courseNo in  
(select courseNo from courses where name in ('数据库', '系统测试'));

# 方法2
select * from scores s right join     
(select * from courses where name in ('数据库','系统测试')) 
c on s.courseNo = c.courseNo

子查询特定关键词

in 范围

any|some 任意一个

all 所有

用法：主查询 where 条件 关键词 (列子查询)

select * from students where age = any (select age from students where age between 18 and 20)

返回目录