MySQL8.0 练习1

一,有这样一张学生成绩表如图,按要求完成操作.

1.根据上图格式,在数据库中创建一个学生成绩表,并添加数据.使用sql语句完成.
2.查询每个人的总成绩并按从高到低排名(要求显示字段：姓名，总成绩，学号)
3.查询每个人的平均成绩（要求显示字段: 学号，姓名，平均成绩）
4.查询各门课程的平均成绩（要求显示字段：课程，平均成绩）
5.查询学生数学成绩的排名,从高到低显示（要求显示字段：学号，姓名，成绩
6.查询学生成绩,按照如下格式展示, 小于60分为C,60-79为B,80-100为A

7.只查询每门成绩都及格的学生(要求显示字段: 学号，姓名，课程，最高成绩)
8.查询出选择课程数量大于3的学生(显示学生学号,姓名,课程总数)

-- 1.根据上图格式,在数据库中创建一个学生成绩表,并添加数据.使用sql语句完成.
-- 创建一个学生表
CREATE TABLE t_stu
(stu_num INT,stu_name VARCHAR(15),SUBJECT VARCHAR(15),stu_score INT)
-- 插入数据
INSERT INTO t_stu (stu_num,stu_name,SUBJECT,stu_score) 
VALUES (1,"张三","语文",70),(1,"张三","数学",80),(1,"张三","英语",66),
(2,"李四","语文",50),(2,"李四","数学",75),(2,"李四","英语",80),
(3,"王五","语文",77),(3,"王五","数学",55),(3,"王五","英语",88),(3,"王五","物理",90)

-- 2.查询每个人的总成绩并按从高到低排名(要求显示字段：姓名，总成绩，学号)
SELECT stu_name,SUM(stu_score)总成绩,stu_num
FROM t_stu
GROUP BY stu_num
ORDER BY SUM(stu_score) DESC

-- 3.查询每个人的平均成绩（要求显示字段: 学号，姓名，平均成绩）
SELECT stu_num,stu_name,AVG(stu_score)平均成绩
FROM t_stu
GROUP BY stu_num

-- 4.查询各门课程的平均成绩（要求显示字段：课程，平均成绩）
SELECT SUBJECT ,AVG(stu_score)平均成绩
FROM t_stu
GROUP BY SUBJECT

-- 5.查询学生数学成绩的排名,从高到低显示（要求显示字段：学号，姓名，成绩
SELECT stu_num,stu_name,stu_score 数学成绩
FROM t_stu
WHERE SUBJECT = '数学'
ORDER BY stu_score DESC

-- 6.查询学生成绩,按照如下格式展示, 小于60分为C,60-79为B,80-100为A
SELECT stu_name,SUBJECT,LEVEL
(CASE WHEN stu_score>=80 AND stu_score<100 THEN "A"
      WHEN stu_score>=60 AND stu_store<=79 THEN 'B'
      ELSE 'C'
      END)LEVEL
      FROM t_stu   

-- 7.只查询每门成绩都及格的学生(要求显示字段: 学号，姓名，课程，最高成绩)
Select * 
From t_score
Where stu_num Not In (Select stu_num
                    Form t_score 
                    Where stu_score <60)


-- 8.查询出选择课程数量大于3的学生(显示学生学号,姓名,课程总数)
SELECT stu_num,stu_name,COUNT(*)课程数量
FROM t_stu
GROUP BY stu_num
HAVING 课程数量>3

二,对该表实现分页数据查询,每页显示3条数据
分别写出sql语句查询第一页,第二页,第三页数据.

SELECT* 
FROM t_stu 
LIMIT 3

SELECT*
FROM  t_stu
LIMIT 3,3

SELECT*
FROM  t_stu
LIMIT 6,3

三.一道SQL语句面试题，关于group by
表内容：
2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负
如果要生成下列结果, 该如何写sql语句?

                         胜 负 
               2005-05-09 2 2
               2005-05-10 1 2

CREATE TABLE t_record(ddate DATE,result CHAR(1))
INSERT INTO t_record(ddate,result)
VALUES('2005-05-09','胜'),('2005-05-09','胜'),('2005-05-09','负'),('2005-05-09','负'),
('2005-05-10','胜'),('2005-05-10','负'),('2005-05-10','负')

SELECT ddate,SUM(CASE WHEN result = '胜' THEN 1 ELSE 0 END)胜,
SUM(CASE WHEN result ='负' THEN 1 ELSE 0 END)负 
FROM t_record 
GROUP BY ddate

四.表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列。

-- 创建表t_compare,便于题目验证
CREATE TABLE t_compare(A INT,B INT,C INT)
-- 插入数据
INSERT INTO t_compare (A,B,C)
VALUES(1,2,3),(3,2,1),(5,6,8)
-- 查询
SELECT (CASE WHEN a>b THEN a ELSE b END),
(CASE WHEN b>c THEN b ELSE c END)
FROM t_compare

五.请取出student表中日期(stu_reg_time字段)为当天的所有记录?

*思路一：使用datediff（）将两个日期相减，结果为0 就是同一天
Select* 
From student
Where datediff （reg_time,now()）=0

*思路二：使用日期格式化函数，将两个日期格式化，比较是否相等
Select* 
From student
Where date_format(reg_time,”%Y-%m-%d”= “2021-2-21”