SQL應用的訣竅之課程系統設計

08-11

SQL應用的訣竅之課程系統設計

來自專欄互聯網數據分析-阿墨

（一）系統分析需求

1、設計環境

設計工具：MYSQL8.0和Navicat Premium

設計語言：SQL

面向對象：需求者

2、功能要求

實驗選課系統分為教師，學生及系統管理員三類用戶。

學生的功能包括選課、退選、查詢選課信息等
教師的功能包括學生成績錄入，查詢實驗信息等
管理員的功能包括新建教師、學生賬戶，添加課程信息

（二）系統建模

1、E-R圖的設計

E-R圖是用於確定要在資料庫中保存什麼信息和確認各種信息之間存在什麼關係。

學生表：

教師表：

課程表：

成績表：

現在利用表與表之間的聯繫（即三種聯繫，如1:1,1:n,m:n），將這幾個表聯繫起來，形成E-R圖，如下：

2、關係模型

將上述E-R圖轉換為關係模型，得到結果如下：

（其中加粗且加下劃線顯示的字體為關係的主鍵）

學生表：STUDENT(SID,SNAME,SAGE,SSEX)
課程表：COURSE(CID,CNAME,TID)
成績表：SC(SID,CID,SCORE)
教師表：TEACHER(TID,TNAME)

3、欄位理解

學生表：

SID代表學號，SNAME代表學生姓名，SAGE代表學生年齡，SSEX代表學生性別

課程表：

CID代表課程編號，CNAME代表課程名字，TID代表教師編號，

成績表：

SID代表學號,CID代表課程編號,SCORE代表成績

教師表：

TID代表教師編號，TNAME代表教師姓名

（三）系統設計

1、創建資料庫

CREATE DATABASE School_NEW;

2、使用資料庫

USE School_NEW;

3、創建數據表

1）創建學生表

CREATE TABLE STUDENT

( SID INTEGER PRIMARY KEY,

SNAME VARCHAR(50) NOT NULL ,

SAGE DATETIME ,

SSEX CHAR(4) ,

CHECK(SSEX=男 or SSEX=女)

);

2）創建教師表

CREATE TABLE TEACHER

( TID INTEGER PRIMARY KEY,

TNAME VARCHAR(50) NOT NULL

);

3）創建課程表

CREATE TABLE COURSE

( CID VARCHAR(50) PRIMARY KEY,

CNAME varchar(50) NOT NULL,

TID INTEGER,

FOREIGN KEY (TID) REFERENCES TEACHER(TID)

);

4）創建成績表

CREATE TABLE SC

( SID INTEGER ,

CID VARCHAR(50) ,

SCORE INTEGER ,

PRIMARY KEY (SID,CID) ,

FOREIGN KEY (SID) REFERENCES STUDENT(SID) ,

FOREIGN KEY (CID) REFERENCES COURSE(CID) ,

CHECK(SCORE>=0 AND SCORE<=100)

);

4、插入數據

1)學生表

INSERT INTO STUDENT

VALUES ( 1001,趙毅,1980-01-01,男),

( 1002,王爾,1980-12-13,女),

( 1003,錢三,1980-03-03,女),

( 1004,李斯,1981-08-09,男),

( 1005,周午,1989-03-08,男),

( 1006,武柳,1980-10-01,女),

( 1007,鄭啟,1989-04-07,女),

( 1008,王芭,1981-06-12,女),

( 1009,陳玖,1980-07-04,女),

( 1010,王石,1980-09-05,男);

2）教師表

INSERT INTO TEACHER

VALUES ( 101,猴子),

( 102,朱穎),

( 103,劉菲),

( 104,孫南);

3）課程表

INSERT INTO COURSE

VALUES ( A01,企業管理,101),

( A02,馬克思,102),

( A03,UML,103),

( A04,資料庫,104);

4）成績表

INSERT INTO SC VALUES

( 1001,A01,80 ),( 1001,A02,85 ),( 1001,A03,90 ),( 1001,A04,70 ),

( 1002,A01,85 ),( 1002,A02,85 ),( 1002,A03,95 ),( 1002,A04,75 ),

( 1003,A01,70 ),( 1003,A02,75 ),( 1003,A03,70 ),( 1003,A04,70 ),

( 1004,A01,90 ),( 1004,A02,95 ),( 1004,A03,90 ),( 1004,A04,90 ),

( 1005,A01,82 ),( 1005,A02,83 ),( 1005,A03,94 ),( 1005,A04,75 ),

( 1006,A01,60 ),( 1006,A02,65 ),( 1006,A03,60 ),( 1006,A04,60 ),

( 1007,A01,70 ),( 1007,A02,NULL ),( 1007,A03,50 ),( 1007,A04,70 ),

( 1008,A01,59 ),( 1008,A02,55 ),( 1008,A03,70 ),( 1008,A04,80 ),

( 1009,A01,50 ),( 1009,A02,NULL ),( 1009,A03,65 ),( 1009,A04,NULL ),

( 1010,A01,NULL ),( 1010,A02,NULL ),( 1010,A03,NULL ),( 1010,A04,NULL );

（四）創建查詢

1、查詢課程編號為「A01」的課程比「A02」的課程成績高的所有學生的學號。

法一：

SELECT SID FROM sc AS S1

WHERE SCORE > ALL (SELECT COALESCE(SCORE,0) FROM sc AS S2

WHERE S1.SID=S2.SID

AND CID = A02)

AND CID = A01法二：

SELECT A.SID

FROM (SELECT SID,COALESCE(SCORE,0) AS SCORE FROM SC WHERE CID=A01) AS A,

(SELECT SID,COALESCE(SCORE,0) AS SCORE FROM SC WHERE CID=A02) AS B

WHERE A.SCORE > B.SCORE

AND A.SID=B.SID;

2、查詢平均成績大於60分的學生的學號和平均成績

SELECT SID,AVG(COALESCE(SCORE,0)) FROM sc

GROUP BY SID

HAVING AVG(COALESCE(SCORE,0)) > 60

3、查詢所有學生的學號、姓名、選課數、總成績

SELECT sc.SID 學號,SNAME 姓名,COUNT(CID) 選課數,SUM(SCORE) 總成績

FROM student JOIN sc ON student.SID = sc.SID

GROUP BY sc.SID,SNAME

4、查詢姓「猴」的老師的個數

SELECT COUNT(TID) 姓「猴」的老師的人數 FROM teacher

WHERE TNAME LIKE 猴%

5、查詢沒學過「猴子」老師課的學生的學號、姓名

SELECT DISTINCT student.SID,SNAME FROM student JOIN sc ON student.SID=sc.SID

WHERE CID = ALL (SELECT CID FROM course

WHERE TID != (SELECT TID FROM teacher

WHERE TNAME = 猴子))

6、查詢學過「猴子」老師所教的所有課的同學的學號、姓名

SELECT DISTINCT student.SID,SNAME FROM student JOIN sc ON student.SID=sc.SID

WHERE CID = ALL (SELECT CID FROM course

WHERE TID =ALL (SELECT TID FROM teacher

WHERE TNAME = 猴子))

7、查詢學過編號為「A01」的課程並且也學過編號為「A02」的課程的學生的學號、姓名

SELECT SID,SNAME FROM student AS S1

WHERE SID = ALL(SELECT SID FROM sc AS S2

WHERE S1.SID=S2.SID

AND CID=A01 AND CID=A02)

8、查詢課程編號為「A02」的總成績

SELECT SUM(SCORE) 課程編號為「A02」的總成績 FROM sc

WHERE CID=A02

9、查詢所有課程成績小於60分的學生的學號、姓名

-- 查詢所有"課程成績小於60分"的學生：

SELECT SID,SNAME FROM student AS S1

WHERE SID IN (SELECT DISTINCT SID FROM sc AS S2

WHERE S1.SID=S2.SID

AND COALESCE(SCORE,0) < 60

GROUP BY SID)

-- 查詢"所有課程成績小於60分"的學生：

SELECT SID,SNAME FROM student AS S1

WHERE SID IN (SELECT DISTINCT SID FROM sc AS S2

WHERE S1.SID=S2.SID

AND COALESCE(SCORE,0) < 60

GROUP BY SID

HAVING COUNT(COALESCE(SCORE,0)) = 4)

10、查詢沒有學全所有課的學生的學號、姓名

SELECT SID,SNAME FROM student AS S1

WHERE SID IN (SELECT SID FROM sc AS S2

WHERE S1.SID=S2.SID

GROUP BY SID

HAVING COUNT(CID)<4)

11、查詢至少有一門課與學號為「1001」的學生所學課程相同的學生的學號和姓名

SELECT DISTINCT student.SID,SNAME FROM student JOIN sc ON student.SID=sc.SID

WHERE CID >= ANY ( SELECT sc.CID

FROM sc JOIN student ON student.SID=sc.SID

WHERE sc.SID = 1001)

AND student.SID != 1001;

12、：查詢和「1001」號同學所學課程完全相同的其他同學的學號

SELECT DISTINCT student.SID,SNAME

FROM student JOIN sc ON student.SID=sc.SID

WHERE CID >= ALL ( SELECT sc.CID

FROM sc JOIN student ON student.SID=sc.SID

WHERE sc.SID = 1001)

AND student.SID != 1001;

13、把「SC」表中「猴子」老師教的課的成績都更改為此課程的平均成績

(需要先創建一個sc表的複製表sc_copy)

法一：

SET @AVG_SCORE := (SELECT AVG(COALESCE(SCORE,0)) AS AVG_SCORE FROM course

JOIN (teacher,sc) ON (sc.CID=course.CID AND teacher.TID=course.TID)

WHERE TNAME=猴子);

UPDATE sc_copy

SET SCORE = @AVG_SCORE

WHERE CID IN (SELECT sc.CID FROM course

JOIN (teacher,sc) ON (sc.CID=course.CID AND teacher.TID=course.TID)

WHERE TNAME=猴子);

法二：

UPDATE sc_copy

SET SCORE = (SELECT AVG(COALESCE(SCORE,0)) FROM course

JOIN (teacher,sc) ON (sc.CID=course.CID AND teacher.TID=course.TID)

WHERE TNAME=猴子)

WHERE CID = (SELECT DISTINCT CID FROM course

JOIN teacher ON teacher.TID=course.TID

WHERE TNAME=猴子);

測試：

SELECT * FROM sc_copy

ORDER BY CID,SID;

14、查詢和「1002」號的同學學習的課程完全相同的其他同學學號和姓名

SELECT DISTINCT student.SID,SNAME

FROM student JOIN sc ON student.SID=sc.SID

WHERE CID >= ALL ( SELECT sc.CID

FROM sc JOIN student ON student.SID=sc.SID

WHERE sc.SID = 1002)

AND student.SID != 1002;

15、刪除學習「猴子」老師課的SC表記錄

（為避免數據丟失，複製一個新表sc_copy)

DELETE FROM sc_copy

WHERE CID = (SELECT DISTINCT CID FROM course

JOIN teacher ON teacher.TID=course.TID

WHERE TNAME=猴子);

16、查詢沒有上過編號為「003」課程的學生的學號、編號為002的課程的平均成績

SELECT SID,

(SELECT AVG(COALESCE(SCORE,0)) FROM sc WHERE CID=A02 GROUP BY CID)

AS AVG_SCORE

FROM student

WHERE SID NOT IN

(SELECT sc.SID FROM student JOIN sc ON student.SID=sc.SID WHERE CID=A03);

17、按平均成績從高到低顯示所有學生的「資料庫」、「企業管理」、「英語」三門的課程成績，按如下形式顯示：學生ID，資料庫，企業管理，英語，有效課程數，有效平均分

SELECT SID AS 學生ID,

(SELECT SCORE FROM sc

WHERE sc.SID=t.SID AND CID=(SELECT DISTINCT CID FROM course

WHERE CNAME=資料庫)) as 資料庫,

(SELECT SCORE FROM sc

WHERE sc.SID=t.SID AND CID=(SELECT DISTINCT CID FROM course

WHERE CNAME=企業管理)) as 企業管理,

(SELECT SCORE FROM sc

WHERE sc.SID=t.SID AND CID=(SELECT DISTINCT CID FROM course

WHERE CNAME=馬克思)) as 馬克思,

COUNT(*) 有效課程數,AVG(COALESCE(SCORE,0)) 有效平均分

FROM sc AS t

GROUP BY SID

ORDER BY AVG(COALESCE(SCORE,0)) DESC;

18、查詢各科成績最高和最低的分：以如下的形式顯示：課程ID，最高分，最低分

(由於缺考沒有分數，缺考的不計算在內)

SELECT CID AS 課程ID,MAX(SCORE) AS 最高分,MIN(SCORE) AS 最低分 FROM sc

GROUP BY CID

19、按各科平均成績從低到高和及格率的百分數從高到低排列，以如下形式顯示：

課程號課程名平均成績及格百分數

SELECT sc.CID 課程號,CNAME 課程名,AVG(COALESCE(SCORE,0)) 平均成績,

CONCAT(ROUND(SUM(CASE WHEN COALESCE(SCORE,0)>=60

THEN 1 ELSE 0

END )*100/COUNT(COALESCE(SCORE,0)),2),%) AS 及格百分數

FROM sc JOIN course ON sc.CID=course.CID

GROUP BY sc.CID

ORDER BY 平均成績,及格百分數 DESC;

20、查詢如下課程平均成績和及格率的百分數（用1行顯示），其中企業管理為A01，馬克思為A02，UML為A03，資料庫為004

SELECT (CASE WHEN CNAME=企業管理 THEN (CONCAT(AVG_SC, , ,YES_PER))

ELSE 0 END ) AS 企業管理,

(CASE WHEN CNAME=馬克思 THEN (CONCAT(AVG_SC, , ,YES_PER))

ELSE 0 END ) AS 馬克思,

(CASE WHEN CNAME=UML THEN (CONCAT(AVG_SC, , ,YES_PER))

ELSE 0 END ) AS UML,

(CASE WHEN CNAME=資料庫 THEN (CONCAT(AVG_SC, , ,YES_PER))

ELSE 0 END ) AS 資料庫

FROM (SELECT sc.CID,CNAME,AVG(COALESCE(SCORE,0)) AS AVG_SC,

CONCAT(ROUND(SUM(CASE WHEN COALESCE(SCORE,0)>=60 THEN 1 ELSE 0

END )*100/COUNT(COALESCE(SCORE,0)),2),%) AS YES_PER

FROM sc JOIN course ON sc.CID=course.CID

GROUP BY sc.CID) AS SCA;

21、查詢不同老師所教不同課程平均分從高到低顯示

SELECT TNAME,CNAME,AVG(COALESCE(SCORE,0)) AS 平均分

FROM course C JOIN (sc,teacher T) ON (C.CID=sc.CID AND C.TID=T.TID)

GROUP BY TNAME,CNAME

ORDER BY AVG(COALESCE(SCORE,0)) DESC;

22、查詢如下課程成績第3名到第6名的學生成績單，其中企業管理為A01，馬克思為A02，UML為A03，資料庫為A04，以如下形式顯示：

學生ID學生姓名企業管理馬克思UML資料庫平均成績

SELECT s.SID 學生ID,SNAME 學生姓名,

(SELECT SCORE FROM sc

WHERE sc.SID=s.SID AND CID=(SELECT DISTINCT CID FROM course

WHERE CNAME=企業管理)) AS 企業管理,

(SELECT SCORE FROM sc

WHERE sc.SID=s.SID AND CID=(SELECT DISTINCT CID FROM course

WHERE CNAME=馬克思)) AS 馬克思,

(SELECT SCORE FROM sc

WHERE sc.SID=s.SID AND CID=(SELECT DISTINCT CID FROM course

WHERE CNAME=UML)) AS UML,

(SELECT SCORE FROM sc

WHERE sc.SID=s.SID AND CID=(SELECT DISTINCT CID FROM course

WHERE CNAME=資料庫)) AS 資料庫,

AVG(COALESCE(SCORE,0)) 平均成績

FROM sc s JOIN course ON s.CID=course.CID

JOIN student ON s.SID=student.SID

GROUP BY s.SID

ORDER BY 平均成績 DESC LIMIT 2,4;

23、使用分段[100-85],[85-70],[70-60],[<60]來統計各科成績，分別統計各分數段人數：課程ID和課程名稱

SELECT S.CID,CNAME,

SUM(CASE WHEN COALESCE(SCORE,0)>=85 AND COALESCE(SCORE,0)<=100

THEN 1 ELSE 0 END) AS [85,100],

SUM(CASE WHEN COALESCE(SCORE,0)>=70 AND COALESCE(SCORE,0)<85

THEN 1 ELSE 0 END) AS [70,85),

SUM(CASE WHEN COALESCE(SCORE,0)>=60 AND COALESCE(SCORE,0)<70

THEN 1 ELSE 0 END) AS [60,70),

SUM(CASE WHEN COALESCE(SCORE,0)<60

THEN 1 ELSE 0 END) AS [<60]

FROM sc S JOIN course C ON S.CID=C.CID

GROUP BY S.CID,CNAME;

24、查詢學生平均成績及其名次

SELECT SID,SNAME,平均成績 ,@COU:=@COU+1 名次

FROM (SELECT S.SID,SNAME,AVG(COALESCE(SCORE,0)) 平均成績

FROM student S JOIN sc ON S.SID=sc.SID

GROUP BY sc.SID

ORDER BY 平均成績 DESC) AS NEW_AVG,

(SELECT @COU:=0) AS STA;

25、查詢各科成績前三名的記錄（不考慮成績並列情況）

SELECT S1.SID AS 學生ID , S1.CID AS 課程ID , S1.SCORE AS 分數 FROM SC S1

WHERE (SELECT COUNT(1)+1 FROM sc S2

WHERE S1.CID=S2.CID AND S2.SCORE > S1.SCORE

)<=3 AND S1.SCORE IS NOT NULL

ORDER BY CID,SCORE DESC;

(點擊見代碼詳解)

26、查詢每門課程被選修的學生數

SELECT CID,COUNT(SID) 學生數 FROM sc

GROUP BY CID;

27、查詢出只選修了一門課程的全部學生的學號和姓名

SELECT S.SID,SNAME

FROM student S JOIN sc ON S.SID=sc.SID

GROUP BY S.SID

HAVING COUNT(CID) = 1

28、查詢男生、女生人數

SELECT SSEX 性別 , COUNT(SID) 人數 FROM student

GROUP BY SSEX

29、查詢姓「王」的學生名單

SELECT * FROM student

WHERE SNAME LIKE 王%

30、查詢同名同性學生名單並統計同名人數

SELECT *,COUNT(*) FROM student

GROUP BY SNAME,SSEX

HAVING COUNT(*)>1

31、1981年出生的學生名單（註：student表中sage列的類型是datetime）

SELECT * FROM student

WHERE EXTRACT(YEAR FROM SAGE)=1981

32、查詢平均成績大於80的所有學生的學號、姓名和平均成績

SELECT S.SID 學號,S.SNAME 姓名,AVG(COALESCE(SCORE,0)) 平均成績

FROM student S JOIN sc ON S.SID=sc.SID

GROUP BY S.SID

HAVING AVG(COALESCE(SCORE,0)) > 80

33、查詢每門課程的平均成績，結果按平均成績升序排序，平均成績相同時，按課程號降序排列

SELECT CID,AVG(COALESCE(SCORE,0)) 平均成績 FROM sc

GROUP BY CID

ORDER BY AVG(COALESCE(SCORE,0)),CID DESC

34、查詢課程名稱為「企業管理」且分數低於60的學生姓名和分數

SELECT SNAME 姓名,SCORE 分數

FROM sc JOIN student S ON sc.SID=S.SID

JOIN course C ON sc.CID=C.CID

JOIN teacher T ON C.TID=T.TID

WHERE CNAME=企業管理 AND COALESCE(SCORE,0)<60

35、查詢所有學生的選課情況

SELECT S.SID,SNAME,C.CID,CNAME

FROM sc JOIN student S ON S.SID=sc.SID

JOIN course C ON C.CID=sc.CID

36、查詢任何一門課程成績在70分以上的姓名、課程名稱和分數

SELECT SNAME 姓名,CNAME 課程名稱,SCORE 分數

FROM sc JOIN student S ON sc.SID=S.SID

JOIN course C ON sc.CID=C.CID

JOIN teacher T ON C.TID=T.TID

WHERE SCORE>70

37、查詢不及格的課程並按課程號從大到小排列

SELECT CID,COALESCE(SCORE,NULL) 成績 FROM sc

WHERE COALESCE(SCORE,0)<60

ORDER BY CID DESC

38、查詢課程編號為A03且課程成績在80分以上的學生的學號和姓名

SELECT S.SID 學號,SNAME 姓名

FROM sc JOIN student S ON sc.SID=S.SID

WHERE CID=A03 AND SCORE>80

39、查詢選了課程(至少一門)的學生人數

SELECT COUNT(DISTINCT SID) 人數 FROM sc

WHERE CID >= ANY (SELECT CID FROM course)

40、查詢選修「猴子」老師所授課程的學生中成績最高的學生姓名及其成績

SELECT SNAME 姓名,SCORE 成績

FROM sc JOIN student S ON sc.SID=S.SID

JOIN course C ON sc.CID=C.CID

JOIN teacher T ON C.TID=T.TID

WHERE TNAME=猴子

ORDER BY SCORE DESC LIMIT 1

41、查詢各個課程及相應的選修人數

SELECT CID,COUNT(SID) 人數 FROM sc

GROUP BY CID

42、查詢有2門不同課程成績相同的學生的學號、課程號、學生成績

SELECT DISTINCT S1.SID,S2.SCORE

from sc S1 ,sc S2

where COALESCE(S1.SCORE,0)=COALESCE(S2.SCORE,0)

and S1.CID<>S2.CID

ORDER BY SID;

43、查詢每門課程成績最好的前兩名

SELECT S1.SID AS 學生ID , S1.CID AS 課程ID , S1.SCORE AS 分數 FROM SC S1

WHERE (SELECT COUNT(1)+1 FROM sc S2

WHERE S1.CID=S2.CID AND S2.SCORE > S1.SCORE

)<=2 AND S1.SCORE IS NOT NULL

ORDER BY CID,SCORE DESC;

44、統計每門課程的學生選修人數(超過10人的課程才統計)。要求輸出課程號和選修人數，查詢結果按人數降序排序，若人數相同，按課程號升序排序

SELECT CID,COUNT(SID) 人數 FROM sc

GROUP BY CID

HAVING COUNT(SID)>=10

ORDER BY COUNT(SID),CID DESC

45、查詢至少選修兩門課程的學生學號

SELECT SID FROM sc

GROUP BY SID

HAVING COUNT(CID)>=2

46、查詢全部學生都選修的課程的課程號和課程名

SELECT CID 課程號,CNAME 課程名 FROM course

WHERE CID IN (SELECT CID FROM sc

GROUP BY CID

HAVING COUNT(SID) = (SELECT COUNT(*)

FROM student))

47、查詢沒學過「猴子」老師講授的任一門課程的學生姓名

SELECT SNAME FROM student S

WHERE SID NOT IN (SELECT sc.SID FROM sc JOIN course C ON sc.CID=C.CID

JOIN teacher T ON C.TID=T.TID

WHERE TNAME=猴子)

48、查詢兩門以上不及格課程的同學的學號及其平均成績

SELECT SID 學號,AVG(COALESCE(SCORE,0)) 平均成績 FROM sc

WHERE SID IN (SELECT SID FROM sc

WHERE COALESCE(SCORE,0)<60

GROUP BY SID

HAVING COUNT(*)>2)

GROUP BY SID

49、檢索課程編號為「004」且分數小於60的學生學號，結果按按分數降序排列

SELECT SID,SCORE FROM sc

WHERE CID=A04 AND COALESCE(SCORE,0)<60

ORDER BY SCORE DESC

50、刪除學生編號為「002」的課程編號為「001」的成績

DELETE FROM sc

WHERE CID=A02 AND SID=1001;

（五）總結

通過上面的各種查詢，我們對這個資料庫有了充分的了解，同時也能夠更好地去應用這個資料庫。不過，為滿足一些基本的需求，我們可以將上面的查詢創建為視圖、存儲過程或事務等，這些都可以在很大程度上既滿足需求又提高工作效率。比如，「將每位同學各科成績排列出來」，首先建立一個查詢，得到此需求的結果；然後在此查詢的基礎上創建一個視圖。這時，只要一個人照著SELECT語句的語法寫一個簡單的查詢：SELECT * FROM 視圖名 ，就可以得到上面需求的結果了。又比如，可以創建一個存儲過程來實現對新來學生的記錄或在校學生的轉校等，也可以創建一個事務來對學生表進行定時更新等。不過，這些操作的基礎都是建立在查詢操作之上的，所以掌握好查詢，才能更有效的滿足人們的需求。

參考：

https://blog.csdn.net/shuxiao9058/article/details/7525447

https://zhuanlan.zhihu.com/p/38354000