mysql之SQL练习

常见面试题

学生表：student(学号,学生姓名,出生年月,性别)

成绩表：score(学号,课程号,成绩)

课程表：course(课程号,课程名称,教师号)

教师表：teacher(教师号,教师姓名)

1查询学生总成绩排名

SELECT
	stu_no,
	sum(score_prize) AS total_prize
FROM
	score
GROUP BY
	stu_no
ORDER BY
	sum(score_prize) DESC ;
1
2
3
4
5
6
7
8
9

– stu_no|total_prize|
– ------±----------+
– 0001 | 269.0|
– 0002 | 237.0|
– 0003 | 224.0|

2 查询平均成绩大于60的学生学号和平均成绩

SELECT
	stu_no,
	avg(score_prize) AS avg_score
FROM
	score
GROUP BY
	stu_no
HAVING
	avg(score_prize) > 60 ;
1
2
3
4
5
6
7
8
9

– stu_no|avg_score|
– ------±--------+
– 0001 | 89.66667|
– 0002 | 79.00000|
– 0003 | 74.66667|

3 查询本月生日的学生

备注：关于date_format方法说明

SELECT	MONTH(now()) ; -- 10
SELECT date_format(now(), '%Y%m'); -- 202210 

SELECT date_format(now(),'%Y'); -- 2022 年
SELECT date_format(now(),'%y'); -- 22
SELECT date_format(now(),'%M'); -- October 月
SELECT date_format(now(),'%m'); -- 10
SELECT date_format(now(),'%d'); -- 26 日
SELECT date_format(now(),'%D'); -- 26th
SELECT date_format(now(),'%H'); -- 15 时 24小时
SELECT date_format(now(),'%h'); -- 03 12小时
SELECT date_format(now(),'%i'); -- 07 分钟
SELECT date_format(now(),'%S'); -- 05 分钟
SELECT date_format(now(),'%s'); -- 15 分钟
1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- 方法一：通过month方法
SELECT
	stu_no
FROM
	student
WHERE
	MONTH(stu_born_date) = MONTH(now());
-- 方法二：通过date_format方法

SELECT
	stu_no
FROM
	student
WHERE
	date_format(stu_born_date, '%Y%m') = date_format(now(), '%Y%m');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

– stu_no|
– ------+
– 0004 |

4 查询课程’0001’分数小于60，分数降序排列学生信息

SELECT
	t1.stu_no AS stuNo,
	t1.stu_name AS stuName
FROM
	student AS t1
LEFT JOIN score AS t2 ON
	t1.stu_no = t2.stu_no
WHERE
	t2.course_no = '0001'
	AND t2.score_prize > 60
ORDER BY
	t2.score_prize DESC ;
1
2
3
4
5
6
7
8
9
10
11
12

stuNo|stuName|
-----±------+
0001 |黑寡妇 |
0002 |钢铁侠 |
0003 |美国队长 |

5 查询不同老师所教不同课程平均分从高到低显示

SELECT
	t1.teacher_no,
	avg(t3.score_prize) AS avgScore
FROM
	teacher AS t1
INNER JOIN course AS t2 
ON
	t1.teacher_no = t2.teacher_no
INNER JOIN score AS t3 ON
	t2.course_no = t3.course_no
GROUP BY
	t1.teacher_no
ORDER BY
	avg(t3.score_prize) DESC ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

– teacher_no|avgScore|
– ----------±-------+
– 0002 |85.66667|
– 0003 |84.33333|
– 0001 |73.33333|

6 查询课程名称是 数学，并且分数高于60 的学生姓名和分数

SELECT
	t1.stu_name,
	t2.score_prize
FROM
	student AS t1
INNER JOIN score AS t2 ON
	t1.stu_no = t2.stu_no
INNER JOIN course AS t3 ON
	t3.course_no = t2.course_no
WHERE
	t2.score_prize > 60
	AND t3.course_name = '数学';
1
2
3
4
5
6
7
8
9
10
11
12

stu_name|score_prize|
--------±----------+
黑寡妇 | 90.0|
钢铁侠 | 90.0|
美国队长 | 77.0|

7 查询任何一门课程成绩在70分以上的姓名、课程名称和分数

SELECT
	t1.stu_name AS stuName ,
	t3.course_name AS courseName ,
	t2.score_prize AS scorePrize
FROM
	student AS t1
INNER JOIN score AS t2 ON
	t2.stu_no = t1.stu_no
INNER JOIN course AS t3 ON
	t3.course_no = t2.course_no
WHERE
	t2.score_prize > 70
;
1
2
3
4
5
6
7
8
9
10
11
12
13

– stuName|courseName|scorePrize|
– -------±---------±---------+
– 黑寡妇 |语文 | 80.0|
– 黑寡妇 |数学 | 90.0|
– 黑寡妇 |英语 | 99.0|
– 钢铁侠 |数学 | 90.0|
– 钢铁侠 |英语 | 77.0|
– 美国队长 |数学 | 77.0|
– 美国队长 |英语 | 77.0|

8 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT
	t1.stu_no AS stuNo,
	t1.stu_name AS stuName,
	avg(t2.score_prize) AS avgScore
FROM
	student AS t1
LEFT JOIN score AS t2 
ON
	t1.stu_no = t2.stu_no
WHERE
	t2.score_prize < 80
GROUP BY
	t1.stu_no
HAVING
	count(t1.stu_no) >= 2 ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

– stuNo|stuName|avgScore|
– -----±------±-------+
– 0002 |钢铁侠 |73.50000|
– 0003 |美国队长 |74.66667|

9 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

SELECT
	a.stu_no ,
	b.score_prize ,
	a.course_no
FROM
	score AS a
INNER JOIN score AS b 
ON
	a.stu_no = b.stu_no
WHERE
	a.score_prize = b.score_prize
	AND a.course_no != b.course_no ;
1
2
3
4
5
6
7
8
9
10
11
12

– stu_no|score_prize|course_no|
– ------±----------±--------+
– 0003 | 77.0|0002 |
– 0003 | 77.0|0003 |

10 查询课程编号为“0001”的课程比“0002”的课程成绩高的所有学生的学号

SELECT
	t1.stu_no
FROM
	(
	SELECT
		stu_no ,
		score_prize
	FROM
		score
	WHERE
		course_no = '0001') AS t1
INNER JOIN 
(
	SELECT
		stu_no,
		score_prize
	FROM
		score
	WHERE
		course_no = '0002') AS t2 
ON
	t1.stu_no = t2.stu_no
WHERE
	t1.score_prize < t2.score_prize ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

stu_no|
------+
0001 |
0002 |
0003 |

11 查询学过编号为“0001”的课程并且也学过编号为“0002”的课程的学生的学号、姓名

SELECT
	a.stu_no
FROM
	(
SELECT
		stu_no,
		score_prize
FROM
		score
WHERE
		course_no = '0001') AS a
INNER JOIN 
(
SELECT
		stu_no,
		score_prize
FROM
		score
WHERE
		course_no = '0002') AS b
ON
	a.stu_no = b.stu_no
INNER JOIN student c ON
	c.stu_no = a.stu_no	;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

stu_no|
------+
0001 |
0002 |
0003 |

12 查询学过“蔡太师”老师所教的所有课的同学的学号、姓名

SELECT
	a.stu_name ,
	a.stu_no ,
	b.course_no ,
	d.teacher_no ,
	d.teacher_name
FROM
	student AS a
INNER JOIN score AS b
ON
	a.stu_no = b.stu_no
INNER JOIN course c ON
	b.course_no = c.course_no
INNER JOIN teacher d ON
	d.teacher_no = c.teacher_no
WHERE
	d.teacher_name = '蔡太师';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

– stu_name|stu_no|course_no|teacher_no|teacher_name|
– --------±-----±--------±---------±-----------+
– 黑寡妇 |0001 |0002 |0001 |蔡太师 |
– 钢铁侠 |0002 |0002 |0001 |蔡太师 |
– 美国队长 |0003 |0002 |0001 |蔡太师 |

13 查询没有学过“蔡太师”老师所教的所有课的同学的学号、姓名

SELECT stu_no ,stu_name 
FROM
	student
WHERE
	stu_no NOT IN
(
	SELECT
		stu_no
	FROM
		score
	WHERE
		course_no =
(
		SELECT
			course_no
		FROM
			course
		WHERE
			teacher_no =(
			SELECT
				teacher_no
			FROM
				teacher
			WHERE
				teacher_name = '蔡太师'
)
));
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

– stu_no|stu_name|
– ------±-------+
– 0004 |灭霸 |
– 0005 |金刚狼 |

14 查询选修“蔡太师”老师所授课程的学生中成绩最高的学生姓名及其成绩

SELECT
	a.stu_name ,
	b.score_prize
FROM
	student AS a
INNER JOIN score AS b ON
	a.stu_no = b.stu_no
INNER JOIN course AS c ON
	c.course_no = b.course_no
INNER JOIN teacher AS d ON
	d.teacher_no = c.teacher_no
WHERE
	d.teacher_name = '蔡太师'
ORDER BY
	b.score_prize DESC
LIMIT 1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

– stu_name|score_prize|
– --------±----------+
– 黑寡妇 | 90.0|

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

SELECT
	a.stu_no ,
	avg(a.score_prize)
,
	max(CASE WHEN b.course_name = '语文' THEN a.score_prize ELSE NULL END) AS '语文',
	max(CASE WHEN b.course_name = '数学' THEN a.score_prize ELSE NULL END) AS '数学',
	max(CASE WHEN b.course_name = '英语' THEN a.score_prize ELSE NULL END) AS '英语'
FROM
	score AS a
INNER JOIN course AS b 
ON
	a.course_no = b.course_no
GROUP BY
	a.stu_no ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

– stu_no|avg(a.score_prize)|语文 |数学 |英语 |
– ------±-----------------±—±—±—+
– 0001 | 86.00000|90.0|80.0|88.0|
– 0002 | 86.33333|90.0|70.0|99.0|
– 0003 | 71.33333|77.0|77.0|60.0|

16 查询学生平均成绩及其名次

SELECT
	t1.stu_no ,
	avg(t1.score_prize) AS avg_score_prize,
	ROW_NUMBER () OVER (
ORDER BY
	avg(score_prize) DESC ) AS num
FROM
	score AS t1
GROUP BY
	t1.stu_no ;
1
2
3
4
5
6
7
8
9
10

– stu_no|avg_score_prize|num|
– ------±--------------±–+
– 0001 | 89.66667| 1|
– 0002 | 79.00000| 2|
– 0003 | 74.66667| 3|

17 按各科成绩进行排序，并显示排名

SELECT
	a.course_no ,
	ROW_NUMBER () OVER
 (PARTITION BY a.course_no
ORDER BY
	b.score_prize) rans
FROM
	course AS a
INNER JOIN score b
ON
	a.course_no = b.course_no 
	;
1
2
3
4
5
6
7
8
9
10
11
12

– course_no|rans|
– ---------±—+
– 0001 | 1|
– 0001 | 2|
– 0001 | 3|
– 0002 | 1|
– 0002 | 2|
– 0002 | 3|
– 0003 | 1|
– 0003 | 2|
– 0003 | 3|

18 查询每门功成绩最好的前两名学生姓名

-- 思路
-- 1 按照每科成绩排名
-- 2 使用窗口函数增加排名一列
-- 3 找出排名1,2的学号
-- 4 使用学号连接表
SELECT
	a.course_no,
	b.stu_name ,
	a.score_prize,
	a.ranking
FROM
	(
	SELECT
		course_no,
		stu_no,
		score_prize,
		ROW_NUMBER () OVER(PARTITION BY course_no
	ORDER BY
		score_prize desc )  ranking
	FROM
		score s 
) AS a
INNER JOIN student AS b ON
	a.stu_no = b.stu_no
WHERE
	a.ranking <3;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

– course_no|stu_name|score_prize|ranking|
– ---------±-------±----------±------+
– 0001 |黑寡妇 | 80.0| 1|
– 0002 |黑寡妇 | 90.0| 1|
– 0003 |黑寡妇 | 88.0| 2|
– 0003 |钢铁侠 | 99.0| 1|
– 0002 |钢铁侠 | 90.0| 2|
– 0001 |美国队长 | 77.0| 2|

19 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

SELECT
	a.course_no,
	b.stu_name ,
	a.score_prize,
	a.ranking
FROM
	(
	SELECT
		course_no,
		stu_no,
		score_prize,
		ROW_NUMBER () OVER(PARTITION BY course_no
	ORDER BY
		score_prize desc )  ranking
	FROM
		score s 
) AS a
INNER JOIN student AS b ON
	a.stu_no = b.stu_no
WHERE
a.ranking IN (2,3);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

– course_no|stu_name|score_prize|ranking|
– ---------±-------±----------±------+
– 0001 |美国队长 | 77.0| 2|
– 0001 |钢铁侠 | 70.0| 3|
– 0002 |钢铁侠 | 90.0| 2|
– 0002 |美国队长 | 77.0| 3|
– 0003 |黑寡妇 | 88.0| 2|
– 0003 |美国队长 | 60.0| 3|

20 查询各科成绩前三名的记录（不考虑成绩并列情况）

SELECT
	a.course_no,
	b.stu_name ,
	a.score_prize,
	a.ranking
FROM
	(
	SELECT
		course_no,
		stu_no,
		score_prize,
		ROW_NUMBER () OVER(PARTITION BY course_no
	ORDER BY
		score_prize desc )  ranking
	FROM
		score s 
) AS a
INNER JOIN student AS b ON
	a.stu_no = b.stu_no
WHERE
a.ranking < 4 ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

– course_no|stu_name|score_prize|ranking|
– ---------±-------±----------±------+
– 0001 |黑寡妇 | 80.0| 1|
– 0002 |黑寡妇 | 90.0| 1|
– 0003 |黑寡妇 | 88.0| 2|
– 0003 |钢铁侠 | 99.0| 1|
– 0002 |钢铁侠 | 90.0| 2|
– 0001 |钢铁侠 | 70.0| 3|
– 0001 |美国队长 | 77.0| 2|
– 0002 |美国队长 | 77.0| 3|
– 0003 |美国队长 | 60.0| 3|

21 编写一个SQL查询，查找学生表中所有重复的学生名。

-- 编写一个SQL查询，查找学生表中所有重复的学生名。
-- 1.看到“找重复”的关键字眼，首先要用分组函数（group by），再用聚合函数中的计数函数count()给姓名列计数。
-- 2. 分组汇总后，生成了一个如下的表。从这个表里选出计数大于1的姓名，就是重复的姓名。
-- 创建一个辅助表，将姓名列进行行分组汇总
-- 选出辅助表中计数大于1的姓名
-- 结合前两步，将“创建辅助表”的步骤放入子查询

SELECT
	stu_name
FROM
	(
	SELECT
		stu_name,
		count(stu_name) AS num
	FROM
		student
	GROUP BY
		stu_name
) AS t1
WHERE
	num > 1;
-- stu_name|
-- --------+
-- 黑寡妇     |	
-- 方法二：
-- 这时候有的同学可能会想，为什么要这么麻烦创建一个子查询，不能用这个语句（将count放到where字句中）直接得出答案吗？

SELECT
	stu_name
FROM
	student
GROUP BY
	stu_name
WHERE
	count(stu_name) > 1;
-- SQL 错误 [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 
-- 'WHERE¶ count(stu_name) > 1' at line 7
-- where字句无法与聚合函数一起使用。因为where子句的运行顺序排在第二，运行到where时，表还没有被分组。

SELECT
	stu_name
FROM
	student
GROUP BY
	stu_name
HAVING
	count(stu_name) > 1;
-- stu_name|
-- --------+
-- 黑寡妇     |	
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

22 找出语文课中成绩第二高的学生成绩

现在有“课程表”，记录了学生选修课程的名称以及成绩。

现在需要找出语文课中成绩第二高的学生成绩。如果不存在第二高成绩的学生，那么查询应返回 null。

-- 1 找出所有选修了“语文”课的学生成绩
-- 2 查找语文课程成绩的第二名
-- 3 考虑到成绩可能有一样的值，所以使用distinct 成绩进行成绩去重。
-- 方法一
SELECT
	max(DISTINCT score_prize)
FROM
	score
WHERE
	course_no = '0001'
	AND
      score_prize < (
	SELECT
		max(DISTINCT score_prize)
	FROM
		score
	WHERE
		course_no = '0001');
-- max(DISTINCT score_prize)|
-- -------------------------+
--                      70.0|	
-- 方法二
-- limit n子句表示查询结果返回前n条数据      
-- offset n表示跳过x条语句

SELECT
	ifnull(
(SELECT max(DISTINCT score_prize) FROM score
WHERE score_prize<(SELECT max(score_prize) FROM score WHERE course_no = '0001')
AND course_no = '0001')
, NULL) AS '语文课第二名成绩';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

23 查询多表数据

24 查询不在表中数据

select a.Name as Customers
from Customers as a
left join Orders as b
on a.Id=b.CustomerId
where b.CustomerId is null;
1
2
3
4
5
6

‘查询没有学过“蔡太师”老师所教的所有课的同学的学号、姓名’ 这道题看一下

25 查询薪水涨幅

-- 知道如何将“薪水涨幅“指标定义为入职薪水-当前薪水。
-- 多久没有涨过工资了
SELECT
	m.emp_no,
	cursalary-hiresalary AS salaryIncrease
FROM
	(
	SELECT
		emp_no,
		emp_salary AS cursalary
	FROM
		gbk_salary
	WHERE
		emp_end_date = '2004-01-01') AS m
LEFT JOIN 
(
	SELECT
		a.emp_no,
		emp_salary AS hiresalary
	FROM
		gbk_emp AS a
	LEFT JOIN gbk_salary AS b 
ON
		a.emp_no = b.emp_no
	WHERE
		a.emp_hire = b.emp_start_date
		AND a.emp_no IN 
(
		SELECT
			emp_no
		FROM
			gbk_salary
		WHERE
			emp_end_date = '2004-01-01')) AS n
ON
	m.emp_no = n.emp_no
ORDER BY
	salaryIncrease;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

26 如何比较相邻日期

需求：下面是某个公司的每天营业额，表名‘日销’,日期表示这一列数据类型日期，找出所有比前一天营业额高的数据；

方案一：交叉连接

分析

1、使用交叉连接将两个表所有数据组合，进行笛卡尔积

2、datediff函数

SELECT DATEDIFF(‘2022-09-10’,‘2022-09-09’); – 1天
SELECT DATEDIFF(‘2022-09-10’,‘2022-09-01’); – 9天

SELECT
	*
FROM
	gbk_sales a
CROSS JOIN gbk_sales b
ON
	DATEDIFF(a.date, b.date)= 1
WHERE
	a.amount > b.amount;
1
2
3
4
5
6
7
8
9

方案二：lag函数

SELECT
	id,
	old_value
FROM
	(
	SELECT
		a.id,
		a.amount old_value,
		LAG (a.amount,
		1,
		0) OVER
(
	ORDER BY
		a.date ) AS new_value
	FROM
		gbk_sales AS a) tmp
WHERE
	tmp.old_value > new_value;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

27 如何交换数据

需求：小明是一所学校的老师，她有一张 ‘学生表’，平时用来存放座位号和学生的信息。其中，座位号是连续递增的。总的座位数是偶数。

思路：

1、理清换座位逻辑

查询目的是改变相邻学生的座位号。为了理清逻辑，在原表中插入一列叫做‘奇偶数’，对应表示“座位号”的值是“奇数”还是“偶数”。

1）如果原来座位号是奇数的学生，换座位后，这名学生的座位号变为“座位号+1”；

2）如果原来座位号是偶数的学生，换座位后，这名学生的座位号变为“座位号-1”；

2、如何判断座位号是奇数，还是偶数

sql求余函数：mod(n,m) ，返回n除以m的余数。比如mod(8,2) 的结果是0。

如果n除以2的余数是0，说明n是偶数，否则是奇数。

case      
when mod(座位号, 2) != 0  then  '奇数'      
when mod(座位号, 2)  = 0  then  '偶数'
end
1
2
3
4

28 行列互换

需求：输出行列互换的结构

准备数据

CREATE TABLE temp_user(
    name varchar(100),
    course varchar(100),
    grade decimal
);

INSERT INTO temp_user values('宋江','java',89),
('宋江','PHP',99),
('宋江2','c++',89),
('宋江','c#',79),
('宋江2','java',69)
;
1
2
3
4
5
6
7
8
9
10
11
12

行转列

核心：case when 或者if,这两种判断条件，满足条件当做一列

SELECT
	name,
	sum(CASE
		WHEN course = 'java' THEN grade
		ELSE 0
	END) AS 'java',
	sum(CASE
		WHEN course = 'c' THEN grade
		ELSE 0
	END) AS 'c',
	sum(CASE
		WHEN course = 'c++' THEN grade
		ELSE 0
	END) AS 'c++',
	sum(CASE
		WHEN course = 'c#' THEN grade
		ELSE 0
	END) AS 'c#',
	sum(CASE
		WHEN course = 'php' THEN grade
		ELSE 0
	END) AS php
FROM
	temp_user a
GROUP BY
	name;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

列转换行

核心：union 或者union all这两个把结果集合并起来，每次查询学生名称和科目其中一列，

然后再把它们组合，这样结果集只有学生名称，科目成绩，对应科目

SELECT a.name,'java' AS course,a.grade FROM temp_user a
UNION all
SELECT b.name,'c++' AS course,b.grade FROM temp_user b
UNION all
SELECT c.name,'c' AS course,c.grade FROM temp_user c
UNION all
SELECT d.name,'c#' AS course,d.grade FROM temp_user d
UNION ALL
SELECT e.name,'php' AS course,e.grade FROM temp_user e;
1
2
3
4
5
6
7
8
9

29 如何找出最小的n个数

准备工作

USE school;

CREATE TABLE temp_student(
    name varchar(100) comment '姓名',
    id decimal comment '学号',
    class varchar(100) comment '年级',
    start_date date comment '入学时间',
    age decimal comment '年龄',
    major varchar(100) comment '专业'
);

INSERT INTO temp_student VALUES 
('小赵',1,'一班','2016-09-01',19,'计算机'),
('小钱',2,'一班','2017-09-01',21,'计算机'),
('小孙',3,'二班','2017-09-01',19,'金融'),
('小李',4,'三班','2017-09-01',17,'计算机'),
('小周',5,'三班','2017-09-01',20,'计算机'),
('小吴',6,'三班','2017-09-01',18,'计算机');

CREATE TABLE temp_score(
    s_no decimal comment '学号',
    c_no decimal comment '课程号',
    grade decimal comment '分数'
);

INSERT INTO temp_score VALUES 
(1,1,90),
(2,1,70),
(2,2,84),
(3,1,90),
(3,3,80),
(4,1,90),
(4,2,90),
(5,1,85),
(6,2,70)
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

需求：

1、获取2017年入学的计算机专业年龄最小的三位同学名单（姓名，年龄）

2、统计每个班级同学各科成绩平均分大于80分的人数和人数占比

对于需求1分析

1、2017年入学

2、计算机专业

3、年龄最小

4、三位同学

代码实现

SELECT
	name,
	age
FROM
	temp_student s
WHERE
	s.start_date LIKE '2017%'
	AND s.major = '计算机'
ORDER BY
	age
LIMIT 0,
3;
1
2
3
4
5
6
7
8
9
10
11
12

需求2：详细描述

1、每位同学平均成绩

2、评分大于80分的人数

3、平均分大于80分的人数占比

4、输出结果：班级，平均分大于80人数，平均分大于80分的人数占比

1、每位同学平均成绩

涉及到“每个”的时候，就要想到里的分组汇总了。按学号分组（group by），然后求平均成绩（avg函数），把所得结果看做临时表。

SELECT
		s_no,
		avg(grade) AS avg_grade
	FROM
		temp_score
1
2
3
4
5

30 分析用户满意度

https://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==&mid=2649250215&idx=1&sn=87b817c44d0c95083344084d626724c6&chksm=835fd197b4285881c5df0aeeeacd029bba5cde339fd9325fd8e51b94520f70bcfc628b7be835&scene=21#wechat_redirect

31 分析用户行为

https://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==&mid=2649250630&idx=1&sn=7f8ae341c43e89fedf26f46e38512710&chksm=835fd376b4285a600cac1bb334789e14319788dd41d23782420eaa771a2dc06fa0860523b11e&scene=21#wechat_redirect

32 如何分析中位数

https://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==&mid=2649250411&idx=1&sn=bfea35f0e33f75a59d2f93c163e99cfb&chksm=835fd25bb4285b4d085e80c08270692cd95e4c8b1f4fa99dde15cc9d5f7c5f55b8ad475946aa&scene=21#wechat_redirect

33 学员续费如何分析

https://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==&mid=2649250589&idx=1&sn=6d4ef28e01a128c58a6aaef495173ebd&chksm=835fd32db4285a3b844b6fee4b22caeb72e4c5c2e6e525fb7f0547cf0f9b6a1b91c219867074&scene=21#wechat_redirect

34 每门课程中成绩最好的学生

https://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==&mid=2649247873&idx=1&sn=b859bb39a5c6144e8396f2d716ad3186&chksm=835fd8b1b42851a7e80f3dd4fb48dc78474641f4b789cd926fb0414c1b4018a8883607d5508f&scene=21#wechat_redirect

35 双十一如何分析

https://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==&mid=2649248589&idx=1&sn=86d758bb95c9a977e7f0a9c96f3a4575&chksm=835fdb7db428526bb122b5eb14e82de81b545afc31bab09f1eda1bb20585f82d07cdc565556d&scene=21#wechat_redirect

36 如何分析游戏

https://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==&mid=2649248294&idx=1&sn=03fb5d12c897ceff13d1cbcefce280b8&chksm=835fda16b42853009652edae32f67084a8c5fbf5bdf4a73a36e87bdeb9c79213b807b7146455&scene=21#wechat_redirect

37 你每月消费多少

https://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==&mid=2649250071&idx=1&sn=bfe928b7dd8bd5ce9f37281852912dba&chksm=835fd127b4285831e57fb3d0dbe41775a6c77dba0cec178d95b3daf3816a5d73d9caebc3024d&scene=21#wechat_redirect

38 你的平均薪水是多少

https://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==&mid=2649250510&idx=1&sn=4ab7c4f405083d63c523d7a0032d0ba9&chksm=835fd2feb4285be8a1e2cfc0f691905cef8cd06a518dabc2da8f326db3d4a14e7642e0dcb74f&scene=21#wechat_redirect

39 如何找出连续出现n次的内容

https://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==&mid=2649250661&idx=1&sn=b017344c701fbfa02a87a88a1a2207cd&chksm=835fd355b4285a43d6c55c593f83bbc7aea1bb370df8f52210bb3b3f7f5c5f304c272d863a04&scene=21#wechat_redirect

https://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==&mid=2649247313&idx=1&sn=e3d87d81f7655857f54752c71f67a710&chksm=835fc661b4284f7740433c15404e3e476033ae27494db87eef8036c7571a112472ba3d5d6008&scene=21#wechat_redirect

40 滴滴求职真题

http://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==&mid=2649248343&idx=1&sn=2215dba864923c5fad70c495e39bf428&chksm=835fda67b428537138aa6bac527703db6594c8dbf647dfcd3d00ec0c0a624d34904f212bbe92&scene=21#wechat_redirect

41 打车业务问题分析

http://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==&mid=2649248633&idx=1&sn=6b1eeeaaba7c06850f5427ed05b3cc5b&chksm=835fdb49b428525f76a816bed8dbb887fc4f5d1b720228654b596a9d72979d8c168b29b93ffa&scene=21#wechat_redirect

42 如何分析复杂业务

https://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==&mid=2649248480&idx=1&sn=a33d035eb7ff20c742eb8b7f4be75b58&chksm=835fdad0b42853c6a419e7df84f598fad8b5af911faf70f786e9562d92f74060494366f4e570&scene=21#wechat_redirect

43 如何分析红包领取情况

https://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==&mid=2649248760&idx=1&sn=490d334204815d80ff53c1d622ca5fc3&chksm=835fdbc8b42852dedf8dd62e5e14b1b4e1059a5b2599f4d8947e0d37aced226e6e882e13301a&scene=21#wechat_redirect