mysql-常见sql查询

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

无论是测试过程中，还是我们面试过程中，都会遇到需要手动写sql查询语句，今天小编借着整理总结的机会给大家一下常见的sql查询sql，其中包括 group by ，order by ，limit ，聚合函数，having，子查询，连表查询 等常见查询等。

一、测试数据准备

1. 创建学生信息表，主要有”学生id，学生姓名，生日，性别“ 字段，其中学生id 为主键

# 创建学生信息表
create table student.stu (
sid VARCHAR(20) not null,
sname VARCHAR(45) not null,
birthDate VARCHAR(10) not null,
sex VARCHAR(10) null,
PRIMARY key (sid)
)

# 插入数据
INSERT INTO student.stu
VALUE
( '0001', '张三', '1990-01-01', '男' ),
( '0002', '李四', '1991-02-01', '女' ),
( '0003', '王五', '1992-03-01', '男' ),
( '0004', '赵六', '1993-04-04', '男' ),
( '0005', '冯七', '1991-08-11', '男' )

2. 创建学生成绩表，其中有学生id，课程id，成绩，无主键。

# 创建学生信息表
create table student.score(
sid VARCHAR(20) not null,
courseId int  not null,
score float  not null
)

# 插入数据
INSERT INTO student.score
VALUE
	( '0001', 001, 80 ),
	( '0001', 002, 90 ),
	( '0001', 003, 90 ),
	( '0002', 001, 99 ),
	( '0002', 002, 58 ),
	( '0002', 003, 55 ),
	( '0003', 001, 100 ),
	( '0003', 002, 61 ),
	( '0003', 003, 70 ),
	( '0004', 001, 0 ),
	( '0004', 002, 56 ),
	( '0004', 003, 55 ),
	( '0005', 001, 99 ),
	( '0005', 002, 88 ),
	( '0005', 003, 87 );

3. 创建课程表，其中有课程id，课程名称，老师名称

# 创建学生课程表
create table student.course(
courseId int  not null,
cname VARCHAR(20)  not null,
tname VARCHAR(20) not null,
PRIMARY key (courseId)
)

# 插入数据
insert into student.course values(001, '语文','王老师'),(002, '数学','李老师'),(003, '英语','赵老师')

二、常见查询

1. 汇总分析类

适用场景：该类问题总要是要对数据进行汇总查询，eg：查询总数，累计值，平均数，最大值，最小值等，该类问题主要需要用到一些聚合函数，eg：count(),sum()，avg()，max(),min()等，用如下题目举例：

题目1：查询课程为002的总成绩
查询sql：

select sum(a.score),a.courseId from student.score as a where a.courseId = '002';

查询结果：

查询思路解析：

1. 确认查询内容： 查询的内容为：课程为002的总成绩，需要用到聚合函数sum()
2. 确认再哪张表读取数据： 首先根据题目可确定，通过单表score 表即可获得查询条件中的学生成绩，故为 单表查询-
3. 确认查询条件：课程号为 002

题目2：查询选了课程号为002的学生总数
查询sql：

select count(*),a.courseId  from student.score as a where a.courseId = '002';

查询结果：

查询思路解析：

1. 确认查询内容： 查询的内容为：学生总数，需要用到聚合函数 count()
2. 确认再哪张表读取数据： 首先根据题目可确定，通过单表score 表即查询条件中的 课程为002 的所有学生id，故为 单表查询-
3. 确认查询条件：选择课程号为 002

2. 分组查询类

适用场景：需要对一类数据进行统计时，就需要使用到分组（group by），同时分组经常和聚合函数一起使用，如果需要需要分组后再次进行过滤的，需要使用到 having ，详细请见下题。

题目3：查询各科成绩最高和最低分
查询sql：

select a.courseId,max(a.score),min(a.score) from student.score as a group by a.courseId;

查询结果：

查询思路解析：

1. 确认查询内容： 查询的内容为：各科成绩的最高分和最低分，需要用到 max() ,min() 函数。
2. 确认再哪张表读取数据： 首先根据题目可确定，通过单表score 表即可 查询内容+ 查询条件中 获得个科成绩，故为 单表查询-
3. 确认查询条件：需要使用科目id进行分组 group by ，无其他条件，古用不到过滤条件

题目4：查询每门课程被选修的成绩大于60 的 学生人数
查询sql：

select a.courseId,count(a.sid) from student.score as a where a.score > 60 group by a.courseId 

查询结果：

查询思路解析：

1. 确认查询内容： 查询的内容为：学生人数，是一个汇总数据，古需要用到 count（）
2. 确认再哪张表读取数据： 首先根据题目可确定，通过单表score 表即可获得 查询内容+ 查询条件中 ，个科成绩，故为 单表查询-
3. 确认查询条件：需要使用科目id进行分组 group by ，同时过滤条件为:成绩大于 60

**题目4：查询平均成绩大于60分的学生id **
查询sql：

select a.sid from student.score as a group by a.sid having avg(a.score) > 60 ;

查询结果：

查询思路解析：

1. 确认查询内容： 查询的内容为：学生id
2. 确认再哪张表读取数据： 首先根据题目可确定，通过单表score 表即可获得 ”查询内容 + 查询条件“ 中的 学生id，学生成绩，故为 单表查询-
3. 确认查询条件：平均成绩大于60 ，记需要使用聚合函数avg(), 运算符 >
4. 确认是否需要分组： 需要使用 学生id分组
   该类需要分组后再进行聚合值过滤的查询问题，需要使用having 进行查询， where 过滤无法实现

3. 排序查询

适用场景：需要对数据进行排序时，或者获取前拍数据和后排数据时，需要使用排序 order by 排序语句，同时课配合使用limit 获取部分数据，同时order by 默认是升序排列，如需要降序时，使用desc。

题目5：查询出courseId 等于002 的学生学号和成绩，且按着降序排序
查询sql：

select a.sid,score from student.score as a where a.courseId = 002 order by a.score desc 

查询结果：

查询思路解析：

1. 确认查询内容： 查询的内容为：学生id，学生成绩
2. 确认再哪张表读取数据： 首先根据题目可确定，通过单表score 表即可获得 ”查询内容 + 查询条件“ 中的 学生id，学生成绩，故为 单表查询-
3. 确认查询条件：按成绩降序排列
4. 确认是否需要分组：无需分组

题目6：查询出courseId 等于002 的最后两名学生学号和成绩
查询sql：

select a.sid,score from student.score as a where a.courseId = 002 order by a.score  limit 2

查询结果：

查询思路解析：

1. 确认查询内容： 查询的内容为：学生id，学生成绩
2. 确认再哪张表读取数据： 首先根据题目可确定，通过单表score 表即可获得 ”查询内容 + 查询条件“ 中的 学生id，学生成绩，故为 单表查询-
3. 确认查询条件：按成绩降序排列且找到最后两名
4. 确认是否需要分组：无需分组

4. 复杂查询-连表/子查询

**题目7 ：查询所有有过不及格（低于60分） 成绩的学生姓名（不重复）和具体成绩
查询sql：

SELECT DISTINCT
	( a.sid ),a.sname ,t.score
FROM
	student.stu AS a
	left JOIN student.score AS t ON a.sid = t.sid 
WHERE
	t.score < 60;

查询结果：

查询思路解析：

1. 确认查询内容： 查询的内容为：学生id，学生姓名
2. 确认再哪张表读取数据：
   2.1首先根据题目可确定，如果想 ”查询内容 + 查询条件“ 中的 学生id，学生姓名，故以及 学生成绩 必要熊score 表和 stu表，故为连表查询，
   2.2 同时需要考虑用哪张表作为主表，因为stu表作为学生信息表，同时stu表的sid 作为主键，故使用stu表作为主表查询。
   2.3 确认链接连接条件：a.sid = t.sid
3. 确认查询条件：成绩小于60
4. 确认是否需要分组：无需分组

**题目8 ：查询2门以上（含2门） 不及格（低于60分）的学生姓名
查询sql：

SELECT
	a.sid, t.sname
FROM
	student.score AS a 
	INNER  join student.stu as t on a.sid = t.sid
WHERE
	a.score < 60 GROUP BY a.sid HAVING count( a.courseId ) > 2;

查询结果：

查询思路解析：

1. 确认查询内容： 查询的内容为：学生id，学生姓名
2. 确认再哪张表读取数据：
   2.1首先根据题目可确定，如果想 ”查询内容 + 查询条件“ 中的 学生姓名，故以及 学生成绩 必要熊score 表和 stu表，故为连表查询，
   2.2 同时需要考虑用哪张表作为主表-使用内连接时，课不考虑主表
   2.3 确认链接连接条件：a.sid = t.sid
3. 确认查询条件：成绩小于60 并且成绩小于60的科目大于等于2门，此时需要分组后再进聚合筛选，故需要用到having
4. 是否需要聚合：需要使用学生id 聚合

查询sql2：使用子查询

SELECT
	t.sname,t.sid
FROM
	student.stu AS t 
WHERE
	t.sid IN (
	SELECT
		a.sid 
	FROM
		student.score AS a 
	WHERE
		a.score < 60 GROUP BY a.sid HAVING count( a.courseId ) > 2	
) ;

查询思路解析：

1. 确认查询内容： 查询的内容为：学生id，学生姓名
2. 确认再哪张表读取数据：
   2.1首先根据题目可确定，如果想 ”查询内容 + 查询条件“ 中的 学生姓名，故以及 学生成绩 必要熊score 表和 stu表，故为连表查询，
   2.2 如需要使用子查询，需要先查询出所有两门以上不及格的学生id，
   2.3 使用子查询中的学生id再主表stu表中查询出学生姓名。
3. 确认查询条件：
   3.1内表查询条件：成绩小于60 并且成绩小于60的科目大于等于2门，此时需要分组后再进聚合筛选，故需要用到having
   3.2主表查询条件： 学生id 包含再子表查询条件内。
4. 是否需要聚合：内表聚合：需要使用学生id 聚合

**题目9 ：查询没有选择过王老师的课的所有学生 **
因再开始的时候，没有设定该该情况，古暂时新插入了一个周八的数据。
查询sql2：使用子查询

INSERT INTO student.stu
VALUE
	( '0007', '周八', '1990-01-01', '男' );```
**查询sql：**

```sql
SELECT
	* 
FROM
	student.stu AS a 
WHERE
	a.sid NOT IN (
	SELECT
		b.sid 
	FROM
		student.score AS b
		LEFT JOIN student.course AS c ON b.courseId = c.courseId 
	WHERE
	c.tname = "王老师")

查询结果：

查询思路解析：

1. 确认查询内容： 查询的内容为：学生姓名
2. 确认再哪张表读取数据：
   2.1首先根据题目可确定，如果想 ”查询内容 + 查询条件“ 中的 学生姓名，老师姓名，因为stu表和course表没有直接联系，故需要使用到中间表score，为3表连表查询
   2.2 子查询中：根据score 表中courseId 和 course 表中的courseId 关联查量表，查询出选秀了王老师课程的学生id
   2.3 子查询条件：查询出选秀了王老师课程的学生id
3. 外层查询条件：查询学生di 不在子查询中查询的结果数据
4. 是否需要聚合：不需要

**题目10 ：查询选过王老师的课并且及格的所有学生姓名 **
查询sql2：使用等值查询

SELECT
	c.sname,
	a.tname,
  b.score	
FROM
	student.course AS a,
	student.score AS b,
	student.stu AS c 
WHERE
	a.courseId = b.courseId 
	AND b.sid = c.sid 
	AND a.tname = '王老师'
	and b.score > 60;

查询结果：

查询思路解析：

1. 确认查询内容： 查询的内容为：学生姓名
2. 确认再哪张表读取数据：
   2.1首先根据题目可确定，如果想 ”查询内容 + 查询条件“ 中的 学生姓名，老师姓名，因为stu表和course表没有直接联系，故需要使用到中间表score，为3表连表查询
   2.2 根据3张表的关联条件把关联条件卸载where条件中，等同于inner join
3. 过滤条件：成绩大于60
4. 是否需要聚合：不需要

总结

小编简单给大家整理一下编写查询sql的思路，供大家参考

1. 确定查询内容是什么？ 即：select 后面的查询内容
2. 查询内容从哪张表李获取？
   如果查询的内容不在同一张表中，确定是需要表链接查询还是需要子查询进项查询。
   需要注意使用哪张表作为主表
3. 确定查询条件什么？ 即：where后面的查询
4. 确定时否需要分组，分组后是否需要进行条件过滤
   如需要分组后，使用聚合函数进行过滤，需要使用having ，having 和where 可以同时使用，语法顺序为：where ... group by ... having ...
5. 是否需要排序
6. 是否需要限定输出数据的数量。

查询基本语法：

select <列明>
from
 <表名1> AS [别名1]
 inner/left/right join
 <表名2> AS [别名2]
 on
 [别名1].外键列 = [别名2].主键列
 where <筛选条件表达式>
 group by <分组列名>
 order by <排序列明> [asc 或 desc]
 limt [开始的行数], [查询记录条数]