mysql中大于会使用索引么 mysql大于等于怎么写

转载

attitude 2024-07-25 10:26:32

文章标签 mysql中大于会使用索引么 mysql 学习数据库子查询 文章分类 MySQL 数据库

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

第四章多表查询

一、笛卡尔积

如果我们的查询条件相对比较复杂，需要涉及多张表进行查询，如果是两张无关的表联合查询，列出所有的可能的结果，如下图：

mysql中大于会使用索引么 mysql大于等于怎么写_子查询

查询的过程大致如下：

1、选取一张表，我们称之为【驱动表】，从驱动表中开始查询，找到满足条件的数据（如果没有条件就依次全部取出）。

2、根据从驱动表查询的这条数据，以及其他条件，去第二张【被驱动表】中查询，并将结果进行拼接。

3、依次类推，从驱动表获取第二条数据，使用该数据和条件，再次查询【被驱动表】进行查询。

4、整个过程，会查询【驱动表】一次，查询【被驱动表】多次。

mysql中大于会使用索引么 mysql大于等于怎么写_数据库_02

对于没有【条件约束】的两张表进行关联查询，如select * from t1,t2，就是从t1中一条条的选取数据，然后全量匹配t2的所有数据，形成一个大的集合，集合的数据量是两表数据量的乘积，我们称之为【笛卡尔积】（这个就是我们数学中所看到的排列组合），如下：

mysql中大于会使用索引么 mysql大于等于怎么写_学习_03

没有连接条件时，我们必须列举所有的可能性，就会产生上边的一张大表，如果两个表的数据量变大，比如每张表1000条数据，那笛卡尔积，就会扩张到1百万，如果是三张表关联，就必须再乘以1000。

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

mysql中大于会使用索引么 mysql大于等于怎么写_学习_04

于是，我们的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

原始数据:

mysql中大于会使用索引么 mysql大于等于怎么写_数据库_05

通俗讲就是根据条件，找到表 A 和表 B 的数据的交集。

mysql中大于会使用索引么 mysql大于等于怎么写_学习_06

二、外连接（常用）

内连接和外连接的区别：

对于【内连接】中的两个表，若【驱动表】中的记录在【被驱动表】中找不到与之匹配的记录，则该记录不会被加入到最后的结果集中。
对于【外连接】中的两个表，即使【驱动表】中的记录在【被驱动表】中找不到与之匹配的记录，也要将该记录加入到最后的结果集中，针对不同的【驱动表的选择】，又可以将外连接分为【左外连接】和【右外连接】。
所以我们可以得出以下结论：
对于左外连接查询的结果会包含左表的所有数据
对于右外连接查询的结果会包含右表的所有数据
外连接的关键字是【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】：

mysql中大于会使用索引么 mysql大于等于怎么写_mysql中大于会使用索引么_07

我们可以使用一个图形来形容左外连接的效果：

mysql中大于会使用索引么 mysql大于等于怎么写_mysql中大于会使用索引么_08

#（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中大于会使用索引么 mysql大于等于怎么写_学习_09

我们可以使用一个图形来形容左外连接的效果：

mysql中大于会使用索引么 mysql大于等于怎么写_子查询_10

三、全连接

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 不会去重

在这个结果中，不管是左表还是右表，所有的数据都被包含在了结果集当中：

mysql中大于会使用索引么 mysql大于等于怎么写_子查询_11

我们可以使用一个图形来形容左外连接的效果：

mysql中大于会使用索引么 mysql大于等于怎么写_数据库_12

小知识：阿里规约有这么一条【强制】超过三个表禁止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离不开大量的练习，有点像语言的语感一样，练得多了自然而然就知道怎么写了。
注：答案在文档下方，先自己尝试去完成。

查询‘01’号学生的姓名和各科成绩。难度：两颗星
查询各个学科的平均成绩，最高成绩。难度：两颗星
查询每个同学的最高成绩及科目名称。难度：四颗星
查询所有姓张的同学的各科成绩。难度：两颗星
查询每个课程最高分的同学信息。难度：五颗星
查询名字中含有“张”和‘李’字的学生信息和各科成绩。难度：两颗星
查询平均成绩及格的同学的信息。难度：三颗星
将学生按照总分数进行排名。难度：三颗星
查询数学成绩的最高分、最低分、平均分。难度：两颗星
将各科目按照平均分排序。难度：两颗星
查询老师的信息和他所带科目的平均分。难度：三颗星
查询被“张楠”和‘‘李子豪’教的课程的最高分和平均分。难度：三颗星
查询每个同学的最好成绩的科目名称。难度：五颗星
查询所有学生的课程及分数。难度：一颗星
查询课程编号为1且课程成绩在60分以上的学生的学号和姓名。难度：两颗星
查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩。难度：三颗星
查询有不及格课程的同学信息。难度：四颗星
求每门课程的学生人数。难度：两颗星
查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列。难度：两颗星
查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩。难度：三颗星
查询有且仅有一门课程成绩在90分以上的学生信息；难度：三颗星
查询出只有三门课程的全部学生的学号和姓名。难度：三颗星
查询有不及格课程的课程信息。难度：三颗星
检索至少选修5门课程的学生学号。难度：三颗星
查询没有学全所有课程的同学的信息。难度：四颗星
查询学全所有课程的同学的信息。难度：四颗星
查询各学生都选了多少门课。难度：两颗星
查询课程名称为”java”，且分数低于60的学生姓名和分数。难度：三颗星
查询学过”张楠”老师授课的同学的信息。难度：四颗星
查询没学过“张楠”老师授课的同学的信息。难度：五颗星

练习题答案
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 = '张楠');

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。