常见sql语句分析

目录

数据库表

使用到的技术

1.表连接：

2.模糊查询

 3.子查询

4.聚合函数

5.group by 和having 

6.order by

7.case when then

题目

1.查询"01"课程比"02"课程成绩高的学生的信息及课程分数

2.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

3.查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)

4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

5.查询"李"姓老师的数量

6.询学过"张三"老师授课的同学的信息

7.查询没学过"张三"老师授课的同学的信息

8.查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

9.查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

10.检索"01"课程分数小于60，按分数降序排列的学生信息

11.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

说明

---

数据库表

使用到的技术

1.表连接：

以student表和score表为例

例：SELECT s1.*,s2.* FROM student s1 JOIN score s2 ON s1.s_id=s2.s_id

这条语句将这两个表的信息组合成为了一个表

2.模糊查询

以teacher表为例

用法：SELECT * FROM teacher WHERE t_name LIKE '条件'

介绍：1、当条件是下划线时，有几个下划线就代表查询字符长度为下划线的数量的字符

例：select * from teacher where t_name like '__'

当下划线数量为2时，就代表查询名字由两个字组成的老师的信息，查到3条记录。

2、当条件是 %李% 时,代表查询名字中含有李的老师的信息。

例：select * from teacher where t_name like '%李%'

 3、当条件是 李% 时,代表查询名字中姓李的老师的信息。

例：select * from teacher where t_name like '李%'

 4、当条件是 %三 时,代表查询名字中最后一个字是三的老师的信息。

例：select * from teacher where t_name like '%三'

 5、teacher表

 

当条件是 %三_ 时,代表查询名字中倒数第二个字是三的老师的信息。

当条件是 _三% 时,代表查询名字中第二个字是三的老师的信息。

 3.子查询

将一个查询块嵌入另一个中，称嵌套查询。上层查询块称父查询，下层查询块称子查询。

 1、使用in或者not in来表示，表示在符合条件的或者不符合条件的

2、使用比较运算符（<,>,<=,>=），可以用来比较大小

例：第七题

 特点：

(1)允许多层嵌套，求解顺序：由内向外。

(2)对用IN或比较运算符连接的子查询，其SELECT子句只能有一个列表达式，且左边列表达式和右边SELECT中的列表达式含义要相同。

4.聚合函数

常用的聚合函数:

1. count(col): 表示求指定列的总行数
2. max(col): 表示求指定列的最大值
3. min(col): 表示求指定列的最小值
4. sum(col): 表示求指定列的和
5. avg(col): 表示求指定列的平均值

例：第5题（count（col）求总数，sum（col）求和），第三题（avg（col）求平均值）

• ifnull函数: 表示判断指定字段的值是否为null，如果为空使用自己提供的值。
• 例ifnull（s_score，0）表示成绩为null的用0代替

聚合函数的特点：

• 聚合函数默认忽略字段为null的记录 要想列值为null的记录也参与计算，必须使用ifnull函数对null值做替换。

5.group by 和having 

通过使用GROUP BY 子句，可以让SUM 和 COUNT 这些函数对属于一组的数据起作用。 
当你指定 GROUP BY region 时， 属于同一个region（地区）的一组数据将只能返回一行值． 
也就是说，表中所有除region（地区）外的字段，只能通过 SUM, COUNT等聚合函数运算后返回一个值． 

HAVING子句可以让我们筛选成组后的各组数据． 
WHERE子句在聚合前先筛选记录．也就是说作用在GROUP BY 子句和HAVING子句前． 
而 HAVING子句在聚合后对组记录进行筛选。 

例：第三题

6.order by

使用order by，一般是用来，依照查询结果的某一列（或多列）属性，进行排序（升序：ASC；降序：DESC；默认为升序）。

当排序列含空值时：

ASC：排序列为空值的元组最后显示。

DESC：排序列为空值的元组最先显示。

例：第十题

7.case when then

case when语句，用于计算条件列表并返回多个可能结果表达式之一。

CASE 具有两种格式：简单 CASE 函数将某个表达式与一组简单表达式进行比较以确定结果。 CASE 搜索函数计算一组布尔表达式以确定结果。  两种格式都支持可选的 ELSE 参数。

可以对分数进行分等级

例：

SELECT s_id,c_id,(CASE WHEN s_score >=90 THEN '优秀'
WHEN s_score >=60 THEN '及格'
ELSE '不及格' END) score FROM score

可以与聚合函数结合使用

例：第十一题

题目

1.查询"01"课程比"02"课程成绩高的学生的信息及课程分数

分析：根据题目要求可以看到需要查询学生信息和课程分数，而学生信息在student表中，课程分数在score表中，这两个表都有s_id这一列，于是可以根据s_id对这两个表进行表连接。

SELECT s.*,sc1.s_score AS s_score1,sc2.s_score AS s_score2 FROM student s 
JOIN (SELECT * FROM score WHERE c_id=1) sc1 
ON s.s_id=sc1.s_id
JOIN (SELECT * FROM score WHERE c_id = 2) sc2
ON s.s_id=sc2.s_id
WHERE sc1.s_score>sc2.s_score

这里使用到了别名（as可省略），和表连接（join），连接条件是两个表都有的列的值相等。

2.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

本题使用到了group by函数，根据s_id字段进行求平均值的判断

提供了两种方法：第一种先将学生编号和学生姓名和平均成绩查询出来，再分组，将where写在最后

SELECT s1.s_id,s1.s_name, avg_score FROM student s1 JOIN 
(SELECT s_id,AVG(IFNULL(s_score,0)) avg_score FROM score GROUP BY s_id) s2
ON s1.s_id=s2.s_id
WHERE s2.avg_score>=60

第二种在查询出来先判断，将having写在括号中，跟上面的where查询出来的结果一样

SELECT s.s_id,s.s_name,ROUND(avg_score, 2) AS avg_score
FROM student s
INNER JOIN (
	SELECT s_id,AVG(s_score) AS avg_score
	FROM score
	GROUP BY s_id
	HAVING avg_score >= 60
) t1
ON s.s_id = t1.s_id;

3.查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)

SELECT s1.s_id,s1.s_name, IFNULL(ROUND(s2.avg_score,2),0) avg_score FROM student s1 JOIN 
(SELECT s_id,AVG(s_score) avg_score FROM score GROUP BY s_id) s2
ON s1.s_id=s2.s_id
WHERE s2.avg_score<60 OR s2.avg_score IS NULL

4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

这里使用到了

SELECT s.s_id,s.s_name,IFNULL(cnt_course, 0) AS cnt_course,IFNULL(sum_score, 0) AS sum_score
FROM student s
LEFT JOIN (
	SELECT s_id,COUNT(*) AS cnt_course,SUM(s_score) AS sum_score
	FROM score
	GROUP BY s_id
) t1
ON s.s_id = t1.s_id;

5.查询"李"姓老师的数量

查询姓李的老师，就是查询以李开头的，后面的字符长度没有要求，所以用 李%

SELECT COUNT(t_name) FROM teacher WHERE t_name LIKE '李%'

6.询学过"张三"老师授课的同学的信息

这里使用到了子查询，括号中表示查询学过张三老师授课的学生的s_id

用in代表s_id是在括号的语句查询到的s_id中的

SELECT s1.* FROM student s1 WHERE s1.s_id IN (
SELECT s2.s_id FROM score s2 JOIN course c
ON s2.c_id=c.c_id JOIN teacher t 
ON c.t_id=t.t_id WHERE t_name='张三')

7.查询没学过"张三"老师授课的同学的信息

这里使用到了子查询，括号中表示查询学过张三老师授课的学生的s_id

用not in代表s_id不是在括号的语句查询到的s_id中的

SELECT s1.* FROM student s1 WHERE s1.s_id NOT IN (
SELECT s2.s_id FROM score s2 JOIN course c
ON s2.c_id=c.c_id JOIN teacher t 
ON c.t_id=t.t_id WHERE t_name='张三')

8.查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

SELECT s.*
FROM student s
WHERE s_id IN (
	SELECT s_id
	FROM score
	WHERE c_id IN (1,2)
	GROUP BY s_id
	HAVING COUNT(*) = 2
);

9.查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

SELECT s.*
FROM student s
WHERE s.s_id IN(
SELECT s_id 
FROM score 
WHERE c_id=1
AND s_id 
NOT IN (
SELECT s_id 
FROM score 
WHERE c_id=2
) 
)

10.检索"01"课程分数小于60，按分数降序排列的学生信息

SELECT s.*,t.s_score FROM student s JOIN (
SELECT s_id,s_score FROM score WHERE c_id=1 AND s_score<60) t
ON s.s_id=t.s_id
ORDER BY t.s_score DESC

11.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT s.s_id,s.s_name,
SUM(CASE c_id WHEN 1 THEN s_score ELSE 0 END) yuwen,
SUM(CASE c_id WHEN 2 THEN s_score ELSE 0 END) shuxue,
SUM(CASE c_id WHEN 3 THEN s_score ELSE 0 END) yingyu,
AVG(IFNULL(s_score,0)) avgscore
FROM student s JOIN 
score t
ON s.s_id=t.s_id 
GROUP BY s.s_id
ORDER BY avgscore DESC

说明

由于时间关系，先写这些，后面有时间再进行补充