HackMD50道MySQL練習題
建立資料表
學生表
create table student(
sno varchar(10) primary key,
sname varchar(20),
sage int(2),
ssex varchar(5)
);
教師表
create table teacher(
tno varchar(10) primary key,
tname varchar(20)
);
課程表
create table course(
cno varchar(10),
cname varchar(20),
tno varchar(20),
constraint pk_course primary key (cno,tno)
);
成績表
create table sc(
sno varchar(10),
cno varchar(10),
score float(4,2),
constraint pk_sc primary key (sno,cno)
);
插入數據
學生表
insert into student values ('s001','張三',23,'男');
insert into student values ('s002','李四',23,'男');
insert into student values ('s003','吳鵬',25,'男');
insert into student values ('s004','琴沁',20,'女');
insert into student values ('s005','王麗',20,'女');
insert into student values ('s006','李波',21,'男');
insert into student values ('s007','劉玉',21,'男');
insert into student values ('s008','蕭蓉',21,'女');
insert into student values ('s009','陳蕭曉',23,'女');
insert into student values ('s010','陳美',22,'女');
insert into student values ('s011','王麗',24,'女');
insert into student values ('s012','蕭蓉',20,'女');
教師表
insert into teacher values ('t001', '劉陽');
insert into teacher values ('t002', '諶燕');
insert into teacher values ('t003', '胡明星');
課程表
insert into course values ('c001','J2SE','t002');
insert into course values ('c002','Java Web','t002');
insert into course values ('c003','SSH','t001');
insert into course values ('c004','Oracle','t001');
insert into course values ('c005','SQL SERVER 2005','t003');
insert into course values ('c006','C#','t003');
insert into course values ('c007','JavaScript','t002');
insert into course values ('c008','DIV+CSS','t001');
insert into course values ('c009','PHP','t003');
insert into course values ('c010','EJB3.0','t002');
成績表
insert into sc values ('s001','c001',78.9);
insert into sc values ('s002','c001',80.9);
insert into sc values ('s003','c001',81.9);
insert into sc values ('s004','c001',50.9);
insert into sc values ('s005','c001',59.9);
insert into sc values ('s001','c002',82.9);
insert into sc values ('s002','c002',72.9);
insert into sc values ('s003','c002',82.9);
insert into sc values ('s001','c003',59);
insert into sc values ('s006','c003',99.8);
insert into sc values ('s002','c004',52.9);
insert into sc values ('s003','c004',20.9);
insert into sc values ('s004','c004',59.8);
insert into sc values ('s005','c004',50.8);
insert into sc values ('s002','c005',92.9);
insert into sc values ('s001','c007',78.9);
insert into sc values ('s001','c010',78.9);
練習題
-
查詢學生表的 前10條資料
-
查詢成績表所有成績的最低分,平均分,總分
-
查詢老師 “諶燕” 所帶的課程設數量
-
查詢所有老師所帶 的課程 數量
-
查詢姓”張”的學生名單
-
查詢課程名稱為'Oracle'且分數低於60 的學號和分數
-
查詢所有學生的選課 課程名稱
-
查詢任何一門課程成績在70 分以上的學生姓名.課程名稱和分數
-
查詢不及格的課程,並按課程號從大到小排列 學號,課程號,課程名,分數
-
查詢沒學過”諶燕”老師講授的任一門課程的學號,學生姓名
-
查詢兩門以上不及格課程的同學的學號及其平均成績
-
檢索'c004'課程分數小於60,按分數降序排列的同學學號
-
查詢'c001'課程比'c002'課程成績高的所有學生的學號
-
查詢平均成績大於60 分的同學的學號和平均成績
-
查詢所有同學的學號.姓名.選課數.總成績
-
查詢姓”劉”的老師的個數
-
查詢只學”諶燕”老師所教的課的同學的學號:姓名
-
查詢學過”c001″並且也學過編號”c002″課程的同學的學號.姓名
-
查詢學過”諶燕”老師所教的所有課的同學的學號:姓名
-
查詢課程編號”c004″的成績比課程編號”c001″和”c002″課程低的所有同學的學號.姓名
-
查詢所有課程成績小於60 分的同學的學號.姓名
-
查詢沒有學課的同學的學號.姓名
-
查詢與學號為”s001″一起上過課的同學的學號和姓名
-
查詢跟學號為”s005″所修課程完全一樣的同學的學號和姓名
-
查詢各科成績最高和最低的分 顯示:課程ID,最高分,最低分
-
按各科平均成績和及格率的百分數 照平均從低到高顯示
-
查詢每個課程的老師及平均分從高到低顯示 老師名稱,課程名稱,平均分數
-
統計列印各科成績,各分數段人數:課程ID,課程名稱,verygood[100-86], good[85-71], bad[<60]
-
查詢各科成績前三名的記錄:(不考慮成績並列情況)
-
查詢每門課程被選修的學生數
-
查詢出只選修了兩門課程的全部學生的學號和姓名
-
查詢男生.女生人數
32-1. 查詢每個課程的男生女生總數
-
查詢同名同姓學生名單,並統計同名人數
-
查詢年紀最小跟最大的學生名單(注:Student 表中Sage 列的型別是int)
-
查詢每門課程的平均成績,結果按平均成績升序排列,平均成績相同時,按課程號降序排列
-
查詢平均成績大於85 的所有學生的學號.姓名和平均成績
-
查詢課程編號為c001 且課程成績在80 分以上的學生的學號和姓名
-
檢索每課程第二高分的學號 分數(考慮成績並列)
-
求選了課程的學生人數
-
查詢選修”諶燕”老師所授課程的學生中,成績最高的學生姓名及其成績
-
查詢不同課程成績有相同的學生的學號.課程號.學生成績
-
所有課程排名成績(不考慮並列) 學號,課程號,排名,成績 照課程,排名排序
-
所有課程排名成績(考慮並列) 學號,課程號,排名,成績 照課程,排名排序
-
做所有學生顯示學生名稱,課程名稱,成績,老師名稱的視圖
-
查詢上過所有老師教的課程的學生 學號,學生名
-
查詢包含數字的課程名
-
查詢只有英文的課程名
-
查詢所有學生的平均成績 並排名 , 學號,學生名,排名,平均成績(不考慮並列) 對平均成績高到低及學號低到高排序
-
查詢所有學生的平均成績 並排名 , 學號,學生名,排名,平均成績(考慮並列) 對平均成績高到低及學號低到高排序
-
查詢課程有學生的成績是其他人成績兩倍的學號 學生名
僅供練習，題目有多種解法，以下不一定為正確解法
解答
1-50道MySQL練習題解答
- 查詢學生表的 前10條資料
SELECT *
FROM student
LIMIT 0,10
- 查詢成績表所有成績的最低分,平均分,總分
SELECT MIN(score),AVG(score),SUM(score)
FROM sc
- 查詢老師 “諶燕” 所帶的課程設數量
SELECT COUNT(*)
FROM course LEFT JOIN teacher USING(tno)
WHERE tname='諶燕'
- 查詢所有老師所帶 的課程 數量
SELECT tname,COUNT(*)
FROM course LEFT JOIN teacher USING(tno)
GROUP BY tno
- 查詢姓”張”的學生名單
SELECT sname
FROM student
WHERE sname LIKE '張%'
- 查詢課程名稱為'Oracle'且分數低於60 的學號和分數
SELECT sno,score
FROM sc LEFT JOIN course USING(cno)
WHERE cname = 'Oracle' AND score <60
- 查詢所有學生的選課 課程名稱
SELECT sname,cname
FROM sc LEFT JOIN student USING(sno) LEFT JOIN course USING(cno)
- 查詢任何一門課程成績在70 分以上的學生姓名.課程名稱和分數
SELECT sname,cname,score
FROM sc LEFT JOIN student USING(sno) LEFT JOIN course USING(cno)
WHERE score >= 70
- 查詢不及格的課程,並按課程號從大到小排列 學號,課程號,課程名,分數
SELECT sno,cno,cname,score
FROM sc LEFT JOIN student USING(sno) LEFT JOIN course USING(cno)
WHERE score < 60
ORDER BY cno DESC
- 查詢沒學過”諶燕”老師講授的任一門課程的學號,學生姓名
SELECT sno,sname
FROM student
WHERE sno NOT IN(SELECT DISTINCT sno
FROM sc LEFT JOIN student USING(sno) LEFT JOIN course USING(cno) LEFT JOIN teacher USING(tno)
WHERE tname IN ('諶燕'))
- 查詢兩門以上不及格課程的同學的學號及其平均成績
SELECT sno,AVG(score)
FROM sc
WHERE score < 60
GROUP BY sno
HAVING COUNT(score) >= 2
- 檢索'c004'課程分數小於60,按分數降序排列的同學學號
SELECT sno,score
FROM sc
WHERE score < 60 AND cno='c004'
ORDER BY score DESC
- 查詢'c001'課程比'c002'課程成績高的所有學生的學號
SELECT a.sno
FROM sc a,sc b
WHERE a.sno = b.sno AND a.cno='c001' AND b.cno='c002' AND a.score > b.score
- 查詢平均成績大於60 分的同學的學號和平均成績
SELECT sno,AVG(score)
FROM sc
GROUP BY sno
HAVING AVG(score)>60
- 查詢所有同學的學號.姓名.選課數.總成績
SELECT sno,sname,COUNT(cno),SUM(score)
FROM sc LEFT JOIN student USING(sno)
GROUP BY sno
- 查詢姓”劉”的老師的個數
SELECT COUNT(*)
FROM teacher
WHERE tname LIKE '劉%'
- 查詢只學”諶燕”老師所教的課的同學的學號:姓名
SELECT DISTINCT sno,sname
FROM sc LEFT JOIN student USING(sno)
WHERE sno NOT IN (SELECT sno FROM sc
WHERE cno NOT IN(SELECT cno FROM course LEFT JOIN teacher USING(tno) WHERE tname='諶燕'))
- 查詢學過”c001″並且也學過編號”c002″課程的同學的學號.姓名
SELECT sno,sname
FROM student
WHERE sno IN(SELECT a.sno
FROM sc a,sc b
WHERE a.sno=b.sno AND a.cno='c001' AND b.cno='c002')
- 查詢學過”諶燕”老師所教的所有課的同學的學號:姓名
SELECT sno,sname
FROM sc x LEFT JOIN student USING(sno) LEFT JOIN course USING(cno) LEFT JOIN teacher USING(tno)
WHERE cno IN(SELECT cno FROM course LEFT JOIN teacher USING(tno) WHERE tname='諶燕')
GROUP BY sno
HAVING COUNT(*)=(SELECT COUNT(*) FROM course LEFT JOIN teacher USING(tno) WHERE tname='諶燕')
- 查詢課程編號”c004″的成績比課程編號”c001″和”c002″課程低的所有同學的學號.姓名
SELECT a.sno
FROM sc a,sc b,sc c
WHERE a.sno = b.sno AND b.sno = c.sno AND a.cno='c001'
AND b.cno='c004' AND c.cno='c002' AND a.score > b.score AND c.score > b.score
- 查詢所有課程成績小於60 分的同學的學號.姓名
SELECT DISTINCT sno
FROM sc x
WHERE 60 > ALL(SELECT score FROM sc y WHERE x.sno=y.sno)
- 查詢沒有學課的同學的學號.姓名
SELECT sno,sname
FROM student
WHERE sno NOT IN(SELECT DISTINCT sno FROM sc)
- 查詢與學號為”s001″一起上過課的同學的學號和姓名
SELECT DISTINCT sc.sno,student.sname
FROM sc RIGHT JOIN student USING(sno)
WHERE cno IN(SELECT DISTINCT cno
FROM sc
WHERE sno='s001')
AND sc.sno <> 's001'
- 查詢跟學號為”s005″所修課程完全一樣的同學的學號和姓名
SELECT sno,sname
FROM sc x LEFT JOIN student USING(sno)
WHERE cno IN(SELECT cno FROM sc WHERE sno = 's005')
GROUP BY sno
HAVING COUNT(cno)=(SELECT COUNT(*) FROM sc WHERE sno = 's005')
AND (SELECT COUNT(*) FROM sc WHERE sno = 's005') = ALL(SELECT COUNT(cno) FROM sc y WHERE x.sno=y.sno )
AND sno <> 's005';
SELECT sno,sname
FROM sc LEFT JOIN student USING(sno)
GROUP BY sno
HAVING GROUP_CONCAT(cno ORDER BY cno) =(SELECT GROUP_CONCAT(cno ORDER BY cno)FROM sc WHERE sno='s005')
AND sno <> 's005';
- 查詢各科成績最高和最低的分 顯示:課程ID,最高分,最低分
SELECT cno,MAX(score),MIN(score)
FROM sc
GROUP BY cno
- 按各科平均成績和及格率的百分數 照平均從低到高顯示
SELECT cno,AVG(score),CONCAT(ROUND(COALESCE(count,0)/tcount,2)*100,'%')AS passing
FROM sc LEFT JOIN (SELECT cno,COUNT(*) AS count FROM sc WHERE score > 60 GROUP BY cno)AS pass USING(cno)
LEFT JOIN (SELECT cno,COUNT(*) AS tcount FROM sc GROUP BY cno)AS total USING(cno)
GROUP BY cno
ORDER BY AVG(score)
- 查詢每個課程的老師及平均分從高到低顯示 老師名稱,課程名稱,平均分數
SELECT tname,cname,AVG(score)
FROM sc LEFT JOIN course USING(cno) LEFT JOIN teacher USING(tno)
GROUP BY tno,cno
ORDER BY AVG(score) DESC
- 統計列印各科成績,各分數段人數:課程ID,課程名稱,verygood[100-86],good[85-71],bad[<60]
SELECT cno,cname,COALESCE(verygoodc,0)verygood, COALESCE(goodc,0)good, COALESCE(badc,0)bad
FROM sc LEFT JOIN (SELECT cno,COUNT(*) verygoodc FROM sc WHERE score BETWEEN 86 AND 100 GROUP BY cno)AS verygoodsc USING(cno)
LEFT JOIN (SELECT cno,COUNT(*) goodc FROM sc WHERE score BETWEEN 71 AND 85 GROUP BY cno)AS goodsc USING(cno)
LEFT JOIN (SELECT cno,COUNT(*) badc FROM sc WHERE score < 60 GROUP BY cno)AS badsc USING(cno)
LEFT JOIN course USING(cno)
GROUP BY cno
或
SELECT sc.cno,course.cname,
SUM(CASE WHEN sc.score BETWEEN 86 AND 100 THEN 1 ELSE 0 END)verygood,
SUM(CASE WHEN sc.score BETWEEN 71 AND 85 THEN 1 ELSE 0 END)good,
SUM(CASE WHEN sc.score < 60 THEN 1 ELSE 0 END)bad
FROM sc,course
WHERE sc.cno=course.cno
GROUP BY cno
- 查詢各科成績前三名的記錄:(不考慮成績並列情況)
SELECT *
FROM sc x
WHERE (SELECT COUNT(*) FROM sc y WHERE x.cno = y.cno AND x.score < y.score)<3
ORDER BY cno,score DESC
- 查詢每門課程被選修的學生數
SELECT cno,COUNT(*)
FROM sc
GROUP BY cno
- 查詢出只選修了兩門課程的全部學生的學號和姓名
SELECT sno,sname
FROM sc LEFT JOIN student USING(sno)
GROUP BY sno
HAVING COUNT(*)=2
- 查詢男生.女生人數
SELECT ssex,COUNT(*)
FROM student
GROUP BY ssex
32-1. 查詢每個課程的男生女生總數
SELECT cno,cname,COALESCE(boy,0)AS boy,COALESCE(girl,0)AS girl
FROM course
LEFT JOIN (SELECT cno,COUNT(*)AS boy FROM `course` JOIN sc USING(cno) JOIN student USING(sno)
GROUP BY cno,ssex HAVING ssex = '男' ORDER BY cno)AS cb USING(cno)
LEFT JOIN (SELECT cno,COUNT(*)AS girl FROM `course` JOIN sc USING(cno) JOIN student USING(sno)
GROUP BY cno,ssex HAVING ssex = '女' ORDER BY cno)AS cg USING(cno)
- 查詢同名同姓學生名單,並統計同名人數
SELECT sname,COUNT(*)
FROM student x
GROUP BY sname
HAVING COUNT(*)>1
- 查詢年紀最小跟最大的學生名單(注:Student 表中Sage 列的型別是int)
SELECT *
FROM student
WHERE sno IN(SELECT sno FROM student x WHERE (SELECT COUNT(*) FROM student y WHERE x.sage > y.sage)<1)
OR sno IN(SELECT sno FROM student x WHERE (SELECT COUNT(*) FROM student y WHERE x.sage < y.sage)<1)
- 查詢每門課程的平均成績,結果按平均成績升序排列,平均成績相同時,按課程號降序排列
SELECT cno,AVG(score)
FROM sc LEFT JOIN student USING(sno)
GROUP BY cno
ORDER BY AVG(score),cno DESC
- 查詢平均成績大於85 的所有學生的學號.姓名和平均成績
SELECT sno,sname,AVG(score)
FROM sc LEFT JOIN student USING(sno)
GROUP BY sno
HAVING AVG(score) > 75
- 查詢課程編號為c001 且課程成績在80 分以上的學生的學號和姓名
SELECT sno,sname
FROM sc LEFT JOIN student USING(sno)
WHERE cno='c001' AND score>=80
- 檢索每課程第二高分的學號 分數(考慮成績並列)
SELECT *
FROM (SELECT * FROM sc GROUP BY score,cno)AS sca
WHERE (SELECT COUNT(*) FROM (SELECT * FROM sc GROUP BY score,cno)AS scb WHERE sca.cno=scb.cno AND sca.score < scb.score)=1
ORDER BY cno
notice :
課程人數要多於一人 否則不顯示此課程
- 求選了課程的學生人數
SELECT COUNT(*)
FROM (SELECT DISTINCT sno FROM sc)AS scount
- 查詢選修”諶燕”老師所授課程的學生中,成績最高的學生姓名及其成績
SELECT sname,score
FROM sc x LEFT JOIN student USING(sno) LEFT JOIN course USING(cno) LEFT JOIN teacher USING(tno)
WHERE tname='諶燕' AND score = (SELECT MAX(score)
FROM sc x LEFT JOIN student USING(sno) LEFT JOIN course USING(cno) LEFT JOIN teacher USING(tno)
WHERE tname='諶燕')
- 查詢不同課程成績有相同的學生的學號.課程號.學生成績
SELECT DISTINCT x.sno,x.cno,x.score
FROM sc x,sc y
WHERE x.sno=y.sno AND x.cno <> y.cno AND x.score=y.score
- 所有課程排名成績(不考慮並列) 學號,課程號,排名,成績 照課程,排名排序
SELECT sc.sno,sc.cno,
CASE WHEN @pre_parent_code=sc.cno THEN @curRank:=@curRank+1
WHEN @pre_parent_code:=sc.cno THEN @curRank:=1
ELSE @curRank:=1
END AS rank,sc.score
FROM (select @curRank:=0,@pre_parent_code:='') r,sc
ORDER BY sc.cno,sc.score DESC
- 所有課程排名成績(考慮並列) 學號,排名,成績,課程號 照課程,排名排序
SELECT sc.sno,
CASE WHEN @pre_parent_code=sc.cno
THEN (CASE WHEN @prefontscore=sc.score THEN @curRank WHEN @prefontscore:=sc.score THEN @curRank:=@curRank+1 END)
WHEN @prefontscore:=sc.score THEN @curRank:=1 END AS rank ,sc.score,@pre_parent_code:=sc.cno AS cno
FROM (SELECT @curRank:=0,@pre_parent_code:='',@prefontscore:=NULL) r,sc
ORDER BY sc.cno,sc.score DESC
notice :
此解答的課程編號賦值必須放在CASE判斷後欄位
- 做所有學生顯示學生名稱,課程名稱,成績,老師名稱的視圖
SELECT sname,cname,score,tname
FROM student LEFT JOIN sc USING(sno) LEFT JOIN course USING(cno) LEFT JOIN teacher USING(tno)
- 查詢上過所有老師教的課程的學生 學號,學生名
SELECT sno,sname
FROM sc LEFT JOIN course USING(cno) LEFT JOIN student USING(sno)
GROUP BY sno
HAVING GROUP_CONCAT(DISTINCT tno ORDER BY tno) = (SELECT GROUP_CONCAT(tno ORDER BY tno) FROM teacher)
- 查詢包含數字的課程名
SELECT cname
FROM course
WHERE cname REGEXP '[0-9]'
- 查詢只有英文的課程名
SELECT cname
FROM course
WHERE cname REGEXP '^([a-z]|[A-Z])+$'
- 查詢所有學生的平均成績 並排名 , 學號,學生名,排名,平均成績(不考慮並列) 對平均成績高到低及學號低到高排序
SELECT scc.sno,scc.sname,@curRank:=@curRank+1 AS rank,scc.avgscore
FROM(SELECT sc.sno,student.sname,AVG(sc.score)AS avgscore
FROM sc LEFT JOIN student USING(sno)
GROUP BY sc.sno)AS scc,(SELECT @curRank:=0) AS r
ORDER BY scc.avgscore DESC,sno
- 查詢所有學生的平均成績 並排名 , 學號,學生名,排名,平均成績(考慮並列) 對平均成績高到低及學號低到高排序
SELECT scavg.sno,scavg.sname,CASE WHEN @prevRank=scavg.avgscore THEN @curRank
WHEN @prevRank:=scavg.avgscore THEN @curRank:=@curRank+1 END AS rank,scavg.avgscore
FROM (SELECT sno,sname,AVG(score) AS avgscore FROM sc LEFT JOIN student USING(sno) GROUP BY sno)AS scavg ,
(SELECT @curRank:=0,@prevRank:=NULL) AS r
ORDER BY scavg.avgscore DESC,scavg.sno
- 查詢課程有學生的成績是其他人成績兩倍的學號 學生名
SELECT sno,sname
FROM sc x LEFT JOIN student USING(sno)
WHERE (SELECT COUNT(*) FROM sc y WHERE x.sno <> y.sno AND x.cno = y.cno AND x.score/2 > y.score) > 0
