MySQL基礎查詢練習 前提準備 使用ddl語句創建資料庫 student表格: create table student( id int PRIMARY KEY COMMENT 'id主鍵', `name` varchar(20) COMMENT '名稱', gender TINYINT(2) C ...
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