MySQL基础练习题

MySQL基础查询练习

前提准备

使用ddl语句创建数据库

student表格：

create table student(
	id int PRIMARY KEY COMMENT \'id主键\',
	`name` varchar(20) COMMENT \'名称\',
	gender TINYINT(2) COMMENT \'性别 1男 2女 3保密\',
	age TINYINT UNSIGNED COMMENT \'年龄\',
	birthday date COMMENT \'出生日期\',
	createTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT \'创建时间\',
	updateTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT \'更新时间\' on update CURRENT_TIMESTAMP,
	isdel bit(1) DEFAULT 0 COMMENT \'是否删除 0未删除 1已删除 默认未删除\'
);

成绩表格score:

create table score (
	id int PRIMARY KEY COMMENT \'id主键\',
	sid int COMMENT \'学生id\',
	course VARCHAR(20) COMMENT \'课程名称\',
	score DECIMAL(4,1) COMMENT \'成绩\'
);

因为下面插入数据没默认写id的值，所以id为自增的列。

添加自增：

alter table student
MODIFY column id int  auto_increment COMMENT \'id 主键\';

alter table score
MODIFY column id int  auto_increment COMMENT \'id 主键\';

初始化数据

数据下载地址：https://qyboke.lanzoue.com/iuhpB0n0rfij

score表：

student表格：

答题

3.查询学生张三的所有成绩：

SELECT s.name,sc.course,sc.score from student s ,score sc where s.id = sc.sid and s.name = \'李云\';

4.查询平均成绩大于60的学生信息：

SELECT DISTINCT s.* from student s ,score sc where s.id = sc.sid and sc.score > 60;

5.查询有成绩的学生信息:

SELECT DISTINCT s.* FROM student s right JOIN score sc on s.id = sc.sid;

6. 查询不及格的学生信息：

SELECT DISTINCT s.* ,sc.course, sc.score
FROM student s right JOIN score sc 
on s.id = sc.sid
where sc.score < 60;

7. 查询各科成绩最高分、最低分和平均分:

SELECT DISTINCT score.course, max(score) over(PARTITION by score.course) \'最高分\',
min(score) over(PARTITION by score.course) \'最低分\',
avg(score) over(PARTITION by score.course) \'平均分\'
FROM score

8. 查询各科成绩第一名的记录:

SELECT DISTINCT sc.course, max(score) over(PARTITION by sc.course) \'最高分\'
FROM score sc left JOIN student s
on sc.sid = s.id

9. 查询男生、女生人数:

SELECT DISTINCT student.gender, count(student.gender) over(PARTITION by student.gender)
from student

10. 查询 1990 年出生的学生名单：

SELECT * from student where YEAR(birthday) = 1990;

11. 查询平均成绩大于等于 70 的所有学生的姓名、出生日期和平均成绩:

SELECT DISTINCT s.name,s.birthday,avg(sc.score) over(PARTITION by s.`name`) 
from score sc left JOIN student s
on sc.sid = s.id

12. 查询mbatis 成绩低于60的学生信息:

SELECT s.*,sc.course,sc.score
FROM score sc LEFT JOIN student s
on sc.sid = s.id
where sc.course = \'mybatis\' and sc.score < 60;

13. 查询学生成绩前三名的记录:

SELECT ROW_NUMBER() over() as \'成绩总分排名\', sco.name,sco.allSco
FROM (
	SELECT DISTINCT s.`name` ,sum(sc.score) over(PARTITION by s.`name`) \'allSco\'
from score sc LEFT JOIN student s
on sc.sid = s.id ORDER BY allSco desc LIMIT 3
) as sco

14. 查询学生信息及年龄:

SELECT stu.name,stu.birthday,(YEAR(CURRENT_DATE) - YEAR(stu.birthday)) \'年龄\'
from student stu

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

SELECT DISTINCT s.`name` ,sum(sc.score) over(PARTITION by s.`name`) \'allScore\',avg(sc.score) over(PARTITION by s.name) \'平均成绩\'
from score sc LEFT JOIN student s
on sc.sid = s.id ORDER BY allScore desc

16. 查询未成年的学生信息:

SELECT stu.name, stu.birthday, (year(CURRENT_DATE) - year(birthday)) \'age\'
from student stu
where (year(CURRENT_DATE) - year(birthday)) < 18

17. 查询姓张的 学生信息:

SELECT *
FROM student
where name like \'张%\'

18. 查询本月过生日的学生信息：

SELECT *
FROM student
where MONTH(CURRENT_DATE) = MONTH(birthday)

19. 查询本周过生日的学生信息

SELECT *
FROM student
WHERE WEEKOFYEAR(CURRENT_DATE) = WEEKOFYEAR(birthday)

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

SELECT stu.name , sc.course ,sc.score, RANK() over(PARTITION by sc.course ORDER BY sc.score desc)
from score sc LEFT JOIN student stu
on sc.sid = stu.id

来源：https://www.cnblogs.com/beishanqingyun/p/17106196.html
本站部分图文来源于网络，如有侵权请联系删除。