Mysql入门学习第四章(学习DQL语句)

上一章中学习了对单表的操作,这一章学习的多表关联的查询。

第四章 多表查询

一、笛卡尔积

如果我们的查询条件相对比较复杂,需要涉及多张表进行查询,如果是两张无关的表联合查询,列出所有的可能的结果,如下图:
在这里插入图片描述
查询的过程大致如下:
1、选取一张表,我们称之为【驱动表】,从驱动表中开始查询,找到满足条件的数据(如果没有条件就依次全部取出)。
2、根据从驱动表查询的这条数据,以及其他条件,去第二张【被驱动表】中查询,并将结果进行拼接。
3、依次类推,从驱动表获取第二条数据,使用该数据和条件,再次查询【被驱动表】进行查询。
4、整个过程,会查询【驱动表】一次,查询【被驱动表】多次。
在这里插入图片描述
对于没有【条件约束】的两张表进行关联查询,如select * from t1,t2,就是从t1中一条条的选取数据,然后全量匹配t2的所有数据,形成一个大的集合,集合的数据量是两表数据量的乘积,我们称之为【笛卡尔积】(这个就是我们数学中所看到的排列组合),如下:
在这里插入图片描述
没有连接条件时,我们必须列举所有的可能性,就会产生上边的一张大表,如果两个表的数据量变大,比如每张表1000条数据,那笛卡尔积,就会扩张到1百万,如果是三张表关联,就必须再乘以1000。

但是很明显,如果两个表没有任何的关系,我们也不会连接两张表进行查询的,在上边的案例中,很明显有一个关联条件就是部门id,两张表的部门id一致,才是我们想要的结果,如下:

在这里插入图片描述
于是,我们的sql就可以写成这个样子:

select e.id,e.name,e.dept_id,d.dept_name from employee e,dept e where e.dept_id = d,id

多表连接的方式有四种,内连接、外链接(左外连接,右外连接),全连接,我们将依次讲解。

二、内连接

在我们刚才的sql当中,使用逗号分割两张表进行查询(employee e,dept e),mysql经过优化默认就等效与内链接,内连接使用关键字 【inner join】 或 【join】 来连接两张表。内连接中,【驱动表】是系统优化后自动选取的,会将执行计划中【扫描次数少】的表选做【驱动表】。

**注意:**使用【join】关键字后要使用【on】来确定连接条件,而不是【where】。但是,在内连接中的where和on效果是等价的,但是一定要明确【on】用来声明连接条件,【where】是整理的帅选条件。

以下三条sql等效:

SELECT * from teacher t ,course c where c.t_id = t.id
SELECT * from teacher t join course c on c.t_id = t.id
SELECT * from teacher t inner join course c on c.t_id = t.id

原始数据:
在这里插入图片描述

通俗讲就是根据条件,找到表 A 和 表 B 的数据的交集。
在这里插入图片描述
二、外连接(常用)
内连接和外连接的区别:

  • 对于【内连接】中的两个表,若【驱动表】中的记录在【被驱动表】中找不到与之匹配的记录,则该记录不会被加入到最后的结果集中。
  • 对于【外连接】中的两个表,即使【驱动表】中的记录在【被驱动表】中找不到与之匹配的记录,也要将该记录加入到最后的结果集中,针对不同的【驱动表的选择】,又可以将外连接分为【左外连接】和【右外连接】。
    所以我们可以得出以下结论:
  • 对于左外连接查询的结果会包含左表的所有数据
  • 对于右外连接查询的结果会包含右表的所有数据
    外连接的关键字是【outter join】 也可以省略outter,连接条件一样需要使用【on】关键字:

#(1)左连接(左外连接)
以下是左外连接的一条sql(等效):

SELECT * from course c left outer join  teacher on t c.t_id = t.id
SELECT * from course c left join on teacher t c.t_id = t.id

我们可以看到,在左表course中第五条数据中的php课程并没有与之匹配的老师,但是结果php课程也显示出来了,同时将不能匹配其他表的字段都置空 【Null】:
在这里插入图片描述
我们可以使用一个图形来形容左外连接的效果:
在这里插入图片描述
#(2)右连接(右外连接)
以下是左外连接的一条sql(等效):

SELECT * from course c left outer join teacher t on c.t_id = t.id
SELECT * from course c left join teacher t on c.t_id = t.id
~~~sql
SELECT * from course c left outer join teacher t on c.t_id = t.id
SELECT * from course c left join teacher t on c.t_id = t.id

我们可以看到,在右表teacher中第五条数据并没有与之匹配的左表数据,但是结果也显示出来了,同时将不能匹配其他表的字段都置空 【Null】:
在这里插入图片描述

我们可以使用一个图形来形容左外连接的效果:
在这里插入图片描述

三、全连接

mysql中并不支持全连接,但是有些数据库是支持的,比如oracle,使用【full outer join】关键字,sql如下

SELECT * from teacher t full outer join course c on c.t_id = t.id
SELECT * from teacher t full join course c on c.t_id = t.id

虽然我的 MySQL 不支持上面这种方式,可以用其他方式替代解决,如下。

SELECT * from teacher t right outer join course c on c.t_id = t.id
union
SELECT * from teacher t left outer join course c on c.t_id = t.id

union 和union all 区别 :
union 会进行去重操作 去除相同信息列
union all 不会去重
在这个结果中,不管是左表还是右表,所有的数据都被包含在了结果集当中:
在这里插入图片描述
我们可以使用一个图形来形容左外连接的效果:
在这里插入图片描述
小知识:阿里规约有这么一条【强制】超过三个表禁止join。需要join的字段,数据类型必须绝对一致; 多表关联查询时,保证被关联的字段需要有索引。说明:即使双表join也要注意表索引、SQL性能。

四、子查询

按照结果集的行列数不同,子查询可分为以下几类:

  • 标量子查询:结果集只有一行一列 (又称为单行子查询)
  • 列子查询: 结果集只有一列多行
  • 行子查询: 结果集只有一行多列
  • 表子查询: 结果集一般为多行多列
    #1、where/having 型子查询
    在where性的子查询中,我们可以使用(标量子查询,列子查询,行子查询)
    (1)查询比连宇栋年龄大的所有的学生 (标量子查询)
select * from student where age > (
	select age from student where name = '连宇栋'
);

(2)查询有一门学科分数大于九十分的学生信息 (列子查询)

select * from student where id in(
	select distinct s_id from scores where score > 90
)

(3)在多条件中,如果行子查询的结果正好满足条件,可以简化书写,如下:

以下的语句是为了写而写,不是最优的选择:

-- 查询男生且是年龄大学的学生信息
select * from student 
where gander = '男' and age = (
	select max(age) from student  
	GROUP BY gander having gander = '男'
)

select * from student 
where (gander,age) = (
	select gander,max(age) from student  
	GROUP BY gander having gander = '男'
)

总结:

  • where 型子查询,如果是 where 列 =(内层 sql) 则内层 sql 返回的必须是单行单列,单个值。
  • where 型子查询,如果是 where 列 in(内层 sql) 则内层 sql 返回的必须是单列,可以多行。

#2、from 型子查询
在学习 from 子查询之前,需要理解一个概念:查询结果集在结构上可以当成表看,那就可以当成临时表对他进行再次查询,所以他支持的就是表子查询:
取排名数学成绩前五名的学生,正序排列。

select * from (
select s.id,s.name sname,r.score,c.name cname from student s 
left join scores r on s.id = r.s_id  
left join course c on r.c_id = c.id 
where c.name = '数学' 
order by  r.score desc
limit 5) t order by  t.score 

3、SELECT型子查询
在select关键字后的子查询仅仅支持标量子查询。

select *,1 as a,2,3,4,5,6 from student;

例子:查询每个老师的代课的数目

select t.id,t.name,count(*) `代课的数量` from teacher t left join course c 
on t.id = c.t_id group by t.id,t.name;

select t.id,t.name,(
	select count(*) from course c where c.t_id = t.id
) as `代课的数量` 
from teacher t;

4、exists型子查询:
表示判断子查询是否有返回值(true/false),有则返回true,没有返回false,这类子查询使用的不是很多。

例子:查询有员工的部门

select * from teacher t where exists (
	select * from course c where c.t_id = t.id
);

五、sql大练兵

写sql离不开大量的练习,有点像语言的语感一样,练得多了自然而然就知道怎么写了。
注:答案在文档下方,先自己尝试去完成。

  1. 查询‘01’号学生的姓名和各科成绩。 难度:两颗星
  2. 查询各个学科的平均成绩,最高成绩。 难度:两颗星
  3. 查询每个同学的最高成绩及科目名称。 难度:四颗星
  4. 查询所有姓张的同学的各科成绩。 难度:两颗星
  5. 查询每个课程最高分的同学信息。 难度:五颗星
  6. 查询名字中含有“张”和‘李’字的学生信息和各科成绩 。 难度:两颗星
  7. 查询平均成绩及格的同学的信息。 难度:三颗星
  8. 将学生按照总分数进行排名。 难度:三颗星
  9. 查询数学成绩的最高分、最低分、平均分。 难度:两颗星
  10. 将各科目按照平均分排序。 难度:两颗星
  11. 查询老师的信息和他所带科目的平均分。 难度:三颗星
  12. 查询被“张楠”和‘‘李子豪’教的课程的最高分和平均分。 难度:三颗星
  13. 查询每个同学的最好成绩的科目名称。 难度:五颗星
  14. 查询所有学生的课程及分数。 难度:一颗星
  15. 查询课程编号为1且课程成绩在60分以上的学生的学号和姓名。 难度:两颗星
  16. 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩。 难度:三颗星
  17. 查询有不及格课程的同学信息。 难度:四颗星
  18. 求每门课程的学生人数。 难度:两颗星
  19. 查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列。 难度:两颗星
  20. 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩。 难度:三颗星
  21. 查询有且仅有一门课程成绩在90分以上的学生信息; 难度:三颗星
  22. 查询出只有三门课程的全部学生的学号和姓名。难度:三颗星
  23. 查询有不及格课程的课程信息 。 难度:三颗星
  24. 检索至少选修5门课程的学生学号。难度:三颗星
  25. 查询没有学全所有课程的同学的信息 。难度:四颗星
  26. 查询学全所有课程的同学的信息。难度:四颗星
  27. 查询各学生都选了多少门课。难度:两颗星
  28. 查询课程名称为”java”,且分数低于60的学生姓名和分数。 难度:三颗星
  29. 查询学过”张楠”老师授课的同学的信息 。 难度:四颗星
  30. 查询没学过“张楠”老师授课的同学的信息 。 难度:五颗星

练习题答案
1、查询‘01’号学生的姓名和各科成绩。 难度:两颗星

select s.id,s.name sname,c.name cname,r.score from student s
left join scores r on r.s_id = s.id
left join course c on c.id = r.c_id
where s.id = 1;

2、查询各个学科的平均成绩,最高成绩。 难度:两颗星

select c.id,c.name,avg(r.score),max(r.score) from course c
left join scores r on c.id = r.c_id 
group by c.id,c.name;

3、查询每个同学的最高成绩及科目名称。 难度:四颗星

select t.id,t.name,c.id,c.name,r.score from
(select s.id,s.name,(
	select max(score) from scores r where r.s_id = s.id
) score from student s)  t 
left join scores r on r.s_id = t.id and r.score = t.score
left join course c on r.c_id = c.id;

4、查询所有姓张的同学的各科成绩。 难度:两颗星

select s.id,s.name sname,c.name cname,r.score from student s
left join scores r on r.s_id = s.id
left join course c on c.id = r.c_id
where s.name like '张%';

5、查询每个课程最高分的同学信息。 难度:五颗星

select * from student s where id in
(
    select distinct r.s_id from
        (
        select c.id,c.name,max(score) score from student s
        left join scores r on r.s_id = s.id
        left join course c on c.id = r.c_id 
        group by c.id,c.name
        ) t 
    left join scores r on t.id = r.c_id and t.score = r.score
);

6、查询名字中含有“张”和‘李’字的学生信息和各科成绩 。 难度:两颗星

select s.id,s.name sname,c.name cname,r.score from student s
left join scores r on r.s_id = s.id
left join course c on c.id = r.c_id
where s.name like '%张%' or s.name like '%李%';

7、查询平均成绩及格的同学的信息。 难度:三颗星

select * from student s where id in (
  select r.s_id from scores r 
	group by r.s_id 
	having avg(r.score)>60
);

8、将学生按照总分数进行排名。 难度:三颗星

select s.id,s.name sname,sum(r.score) score from student s
left join scores r on r.s_id = s.id
group by s.id,s.name order by score desc;

9、查询数学成绩的最高分、最低分、平均分。 难度:两颗星

select c.name,max(score),min(score),avg(score) from course c
left join scores r on c.id = r.c_id
where c.name = '数学';

10、将各科目按照平均分排序。 难度:两颗星

select c.id,c.name,avg(score) score from course c
left join scores r on c.id = r.c_id
group by c.id,c.name order by score desc;

11、查询老师的信息和他所带科目的平均分。 难度:三颗星

select t.id,t.name,c.id,c.name,avg(r.score) 
from teacher t
left join course c on t.id = c.t_id 
left join scores r on r.c_id = c.id
group by t.id,t.name,c.id,c.name;

12、查询被“张楠”和‘‘李子豪’教的课程的最高分和平均分。 难度:三颗星

select t.id,t.name,c.id,c.name,avg(r.score) 
from teacher t
left join course c on t.id = c.t_id 
left join scores r on r.c_id = c.id
group by t.id,t.name,c.id,c.name
having t.name in ('张楠','李子豪');

13、查询每个同学的最好成绩的科目名称。 难度:五颗星

select t.id,t.sname,r.c_id,c.id,c.name,t.score  from
(select s.id,s.name sname,max(r.score) score 
from student s
left join scores r on r.s_id = s.id
group by s.id,s.name) t
left join scores r on r.s_id = t.id and r.score = t.score
left join course c on r.c_id = c.id ;

14、查询所有学生的课程及分数。 难度:一颗星

select s.id,s.name sname,c.id,c.name cname,r.score from student s
left join scores r on r.s_id = s.id
left join course c on c.id = r.c_id;

15、查询课程编号为1且课程成绩在60分以上的学生的学号和姓名。 难度:两颗星

select * from student s where id in
(
	select r.s_id from scores r where r.c_id = 1 and r.score > 60
);

16、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩。 难度:三颗星

select s.id,s.name,t.score from student s 
left join (
	select r.s_id ,avg(r.score) score from scores r group by r.s_id 
) t on s.id = t.s_id;

17、查询有不及格课程的同学信息。 难度:四颗星

– 什么叫有不及格 —》最低分数的科目如果不及格

select * from student s where id in (
	select r.s_id from scores r group by r.s_id 
	HAVING min(r.score) < 60
);

18、求每门课程的学生人数。 难度:两颗星

select c.id,c.name, t.number from course c
left join 
(select r.c_id,count(*) number from scores r group by r.c_id) t
on c.id = t.c_id;
select c.id,c.name,count(*) from course c 
left join scores r on c.id = r.c_id
group by c.id,c.name;

19、查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列。 难度:两颗星

select c.id,c.name,avg(score) score from course c 
left join scores r on c.id = r.c_id 
group by c.id,c.name 
order by score desc,c.id asc;

20、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩。 难度:三颗星

select s.id,s.name,t.score from student s 
right join (
	select r.s_id,avg(score) score from scores r 
	group by r.s_id having score >= 70
) t on s.id = t.s_id;
select s.id,s.name sname, avg(r.score) score from student s
left join scores r on r.s_id = s.id
left join course c on c.id = r.c_id
group by s.id,s.name having avg(r.score) > 70;

21、查询有且仅有一门课程成绩在90分以上的学生信息; 难度:三颗星

select * from student s where id in (
	select r.s_id from scores r where r.score > 90
	group by r.s_id having count(*) = 1
);
select s.id,s.name,s.gander from student s 
left join scores r on s.id = r.s_id 
where r.score > 90
group by s.id,s.name,s.gander having count(*) = 1;

22、查询出只有三门课程的全部学生的学号和姓名。难度:三颗星

select * from student s where id in (
	select r.s_id from scores r group by r.s_id having count(*) = 3
);
select s.id,s.name,s.gander from student s 
left join scores r on s.id = r.s_id 
group by s.id,s.name,s.gander having count(*) = 3;

23、查询有不及格课程的课程信息 。 难度:三颗星

select * from course c where id in (
	select r.c_id from scores r group by r.c_id
	HAVING min(r.score) < 60
);
select r.c_id,c.name from course c
left join scores r on c.id = r.c_id
group by r.c_id,c.name HAVING min(r.score) < 60;

24、检索至少选修5门课程的学生学号。难度:三颗星

select * from student s where s.id in (
	select r.s_id from scores r group by r.s_id having count(*) >= 5
);
select s.id,s.name from student s 
left join scores r on s.id = r.s_id
group by s.id,s.name having count(*) >= 5;

25、查询没有学全所有课程的同学的信息 。难度:四颗星

select s.id,s.name,count(*) number from student s 
left join scores r on s.id = r.s_id
group by s.id,s.name having number < (
	select count(*) from course
);

26、查询学全所有课程的同学的信息。难度:四颗星

select s.id,s.name,count(*) number from student s 
left join scores r on s.id = r.s_id
group by s.id,s.name having number = (
	select count(*) from course
);

27、 查询各学生都选了多少门课。难度:两颗星

select s.id,s.name,count(*) number from student s 
left join scores r on s.id = r.s_id
group by s.id,s.name;

28、查询课程名称为”java”,且分数低于60的学生姓名和分数。 难度:三颗星

select s.id,s.name,r.score from student s 
left join scores r on s.id = r.s_id
left join course c on r.c_id = c.id
where c.name = 'java' and r.score < 60;

29、查询学过”张楠”老师授课的同学的信息 。 难度:四颗星

select s.id,s.name from student s 
left join scores r on r.s_id = s.id
left join course c on c.id = r.c_id
left join teacher t on c.t_id = t.id 
where t.name = '张楠';

30、查询没学过“张楠”老师授课的同学的信息 。 难度:五颗星

select * from student where id not in
(select distinct s.id from student s 
left join scores r on r.s_id = s.id
left join course c on c.id = r.c_id
left join teacher t on c.t_id = t.id 
where t.name = '张楠');
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值