常用数据库比较全面的实战及语法_第一题学生表:temp_dw_student_2022 字段:name 学生姓名grades1 数据

目录

一、前言

二、数据准备

三、 实战

四、经验

五、问题及处理

---

一、前言

sql的核心语法无法增删改查，排序分组，核心和重点在于对多表联合查询的组合及应用，下面就以一个常用的学生表的例子去实战和让大家巩固掌握下多表查询的语法和精妙。

常用函数总结：

二、数据准备

准备如下两张表提供学习和使用：

1、学生表student

2、成绩表

三、 实战

规则：查啥select后先跟啥，主键关联找条件

1.查询所有学生的数学成绩，显示学生姓名 name, 分数， 由高到低（排序order by用法）

语句：

SELECT
	s.`name`,
	g.score 
FROM
	student s,
	grade g 
WHERE
	s.id = g.id 
	AND kemu = '数学' 
ORDER BY
	score DESC

结果：

2.统计每个学生的总成绩,显示字段:姓名，总成绩（sum用法）

语句：

SELECT
	s.`name`,
	SUM(g.score)
FROM
	student s,
	grade g 
WHERE
	s.id = g.id 
GROUP BY `name`

结果：

3.统计每个学生的总成绩(由于学生可能有重复名字),显示字段:学生 id，姓 名，总成绩

语句：

SELECT
	s.id,
	s.`name`,
	sum_score 
FROM
	student s,
( SELECT g.id, SUM( g.score ) AS sum_score FROM grade g GROUP BY id ) a
WHERE
	s.id = a.id 
ORDER BY
	sum_score

结果：

4.列出各门课程成绩最好的学生， 要求显示字段: 学号，姓名,科目，成绩 第一步先 group by 找出单科最好成绩，作为第一张表

语句：

SELECT
	s.id,
	s.`name`,
	g.kemu,
	max_score 
FROM
	student s,
	grade g,
	( SELECT g.kemu, MAX( g.score ) AS max_score FROM grade g GROUP BY kemu ) a 
WHERE
	s.id = g.id 
	AND g.kemu = a.kemu 
	AND g.score = a.max_score

结果：

5.列出各门课程成绩最好的 2 位学生， 要求显示字段: 学号，姓名, 科目，成 绩 （包含成绩相同的并列）

语句：

SELECT
	g1.id,
	s.`name`,
	g1.kemu,
	g1.score 
FROM
	grade g1,
	student s 
WHERE
	( SELECT count(*) FROM grade g2 WHERE g1.kemu = g2.kemu AND g2.score > g1.score ) < 2 
	AND s.id = g1.id 
ORDER BY
	g1.kemu,
	g1.score DESC

结果：

 6.计算每个人的平均成绩， 要求显示字段: 学号，姓名，平均成绩

语句：

SELECT
	s.id,
	s.`name`,
	avg_score 
FROM
	student s,
	( SELECT g2.id, AVG( g2.score ) AS avg_score FROM grade g2 GROUP BY id ) a 
WHERE
	s.id = a.id

结果：

7.计算每个人的成绩，总分数，平均分，要求显示:学号，姓名，语文，数学， 英语，总分，平均分 

语句：

SELECT
	s.id AS 学号,
	s.`name` AS 姓名,
	SUM( CASE WHEN g.kemu = '语文' THEN g.score ELSE 0 END ) AS 语文,
	SUM( CASE WHEN g.kemu = '数学' THEN g.score ELSE 0 END ) AS 数学,
	SUM( CASE WHEN g.kemu = '英语' THEN g.score ELSE 0 END ) AS 英语,
	SUM( g.score ) AS 总分,
	SUM( g.score ) / COUNT(g.score ) AS 平均分
FROM
	student s,
	grade g 
WHERE
	s.id = g.id
GROUP BY s.id

结果：

8.列出各门课程的平均成绩，要求显示字段:课程，平均成绩

语句：

SELECT
	kemu,
	AVG( score ) 
FROM
	grade 
GROUP BY
	kemu

 结果：

9.列出数学成绩的排名， 要求显示字段:学号，姓名，成绩，排名

9.1 分数都不同

语句：

SELECT
	s.id,
	s.`name`,
	b.score AS 数学分数,
	@paiming := @paiming + 1 AS 排名 
FROM
	( SELECT g.score, g.id FROM grade g WHERE g.kemu = '数学' ORDER BY score DESC ) AS b,
	( SELECT @paiming := 0 ) r,
	student s 
WHERE
	s.id = b.id

结果：

9.2 分数相同存在名称并列情况

语句：

SELECT
	s.id,
	s.`name`,
	b.score AS 数学分数,
	(
	CASE
			
			WHEN @temp = b.score THEN
			@paiming 
			WHEN @temp := b.score THEN
			@paiming := @paiming + 1 
			WHEN @temp = 0 THEN
			@paiming := @paiming + 1 
		END 
		) AS 排名 
	FROM
		( SELECT g.score, g.id FROM grade g WHERE g.kemu = '数学' ORDER BY score DESC ) AS b,
		( SELECT @temp := 0, @paiming := 0 ) r,
		student s 
WHERE
	s.id = b.id

 结果：

10.列出语文成绩前 3 名的学生(要求显示字段:学号，姓名, 科目，成绩)

语句：

SELECT
	a.id,
	a.NAME,
	b.kemu,
	b.score 
FROM
	student a,
	grade b 
WHERE
	a.id = b.id 
	AND kemu = '语文' 
ORDER BY
	score DESC 
	LIMIT 3

 结果：

11. 查询语文成绩第 2 和第 3 名的学生

语句：

SELECT
	a.id,
	a.NAME,
	b.kemu,
	b.score 
FROM
	student a,
	grade b 
WHERE
	a.id = b.id 
	AND kemu = '语文' 
ORDER BY
	score DESC 
	LIMIT 1,
	2

结果：

12.查询第 3 名到后面所有的学生数学成绩

语句：

SELECT
	a.id,
	a.NAME,
	b.kemu,
	b.score 
FROM
	student a,
	grade b 
WHERE
	a.id = b.id 
	AND kemu = '数学' 
ORDER BY
	score DESC 
	LIMIT 3,
	1000

 结果：

13.统计英语课程少于 80 分的，显示 学号 id, 姓名，科目，分数

语句：

SELECT
	s.id,
	s.`name`,
	g.kemu,
	g.score 
FROM
	student s,
	grade g 
WHERE
	s.id = g.id 
	AND g.kemu = '英语' 
	AND g.score < 80

 结果：

统计每门课程不及格、一般、优秀，课程不及格（<60) 一般（60<=x<=80) 优秀（>80）的人数：

语句：

(SELECT COUNT(*) FROM grade WHERE score<60 AND kemu=g.kemu )AS 不及格,
(SELECT COUNT(*) FROM grade WHERE score BETWEEN 60 AND 80 AND kemu=g.kemu )AS 一般,
(SELECT COUNT(*) FROM grade WHERE score>60 AND kemu=g.kemu )AS 优秀
FROM grade g GROUP BY kemu

结果：

14.查找每科成绩前 2 名，显示 id, 姓名，科目，分数

14.1 如果第二名有重复，不显示情况

SELECT
	s.id,
	s.NAME,
	g1.kemu,
	g1.score 
FROM
	student s,
	grade g1 
WHERE
	s.id = g1.id 
	AND ( SELECT COUNT(*) FROM grade g2 WHERE g1.kemu = g2.kemu AND g2.score >= g1.score )<= 2 
ORDER BY
	g1.kemu,
	g1.score DESC

结果：

 14.2 如果第二名有重复，显示的情况

语句：

SELECT
	s.id,
	s.name,
	g1.kemu,
	g1.score 
FROM
	student s,
	grade g1 
WHERE
	s.id = g1.id 
	AND ( SELECT COUNT(*) FROM grade g2 WHERE g1.kemu = g2.kemu AND g2.score > g1.score )< 2 
ORDER BY
	g1.kemu,
	g1.score DESC

 结果：

15. 每门课都大于 80 分的学生姓名

15.1 解决办法一: having

SELECT
 
s.`name`
 
FROM
 
student s,
 
( SELECT id FROM grade GROUP BY id HAVING MIN( score )> 80 ) g
 
WHERE
 
s.id = g.id

15.2 解决办法二:not in

SELECT
 
s.`name`
 
FROM
 
student s
 
WHERE
 
`name` NOT IN (
 
SELECT
 
s.`name`
 
FROM
 
student s,
 
( SELECT id FROM grade GROUP BY id HAVING MIN( score )<= 80 ) g
 
WHERE
 
s.id = g.id)

 结果：

四、经验

下面是一些掌握多表查询语法和精妙的技巧：

1. 熟悉表连接的不同类型（内连接、左连接、右连接和全连接），并了解它们的使用场景和区别。

2. 彻底理解关系型数据库的原则，包括主键、外键、约束和索引等。

3. 熟悉 SQL 查询语句的基础语法和关键字，如 SELECT、FROM、WHERE、GROUP BY、HAVING 和 ORDER BY 等。

4. 熟悉子查询语句的写法和使用场景，尤其是在多表查询时，可以通过子查询解决一些复杂的查询需求。

5. 熟悉 SQL 函数和聚合函数的使用，如 SUM、AVG、COUNT、MAX 和 MIN 等，这些函数可以在多表查询中进行计算和筛选。

6. 当表结构较复杂时，可以使用表别名来简化 SQL 查询语句，让查询语句更加清晰易懂。

7. 在实际开发中，可以通过编写 SQL 查询的实践来掌握多表查询的语法和精妙，例如通过实现一些复杂统计分析等功能。

五、问题及处理

多表查询指的是查询两个或多个表中的数据。在处理多表查询问题时，我们需要将多个表中的数据关联起来进行查询。

我们可以使用以下方法处理多表查询问题：

1. 使用 JOIN 连接查询。JOIN 是一种将两个或多个表中的数据关联起来进行查询的方法。它可以使用 INNER JOIN、LEFT JOIN、RIGHT JOIN 等不同的连接方式，根据需要选择合适的连接方式。这种方法可以通过使用 ON 子句来指定连接条件，并通过使用 SELECT 子句来选择需要查询的列。

2. 使用子查询。子查询是一个在主查询中嵌套的查询，它可以返回一个结果集，然后将这个结果集与主查询的结果集进行连接。这种方法可以使用 WHERE 子句来指定子查询，然后将其放在主查询中的 SELECT、FROM 或 WHERE 子句中。

3. 使用 UNION 运算符。UNION 运算符可以将两个或多个 SELECT 语句的结果集合并成一个结果集。这种方法可以使用 UNION ALL 运算符来包含重复行。

在处理多表查询问题时，我们需要注意以下问题：

1. 连接条件必须准确。如果连接条件不正确，可能会导致查询结果错误或不完整。

2. 查询语句应该尽量简洁。如果查询语句过于复杂，可能会导致查询效率低下。

3. 应该使用合适的索引。如果没有正确的索引，可能会导致查询效率低下。

4. 应该使用合适的工具和技术。如果使用不合适的工具和技术，可能会导致查询效率低下。