一、實驗目的
- 學習SQL語言的定義、操縱功能
- 熟悉通過SQL語言對數據庫進行查詢操作，包括單表查詢、多表查詢、嵌套查詢、集合查詢
二、實驗軟件
MySQL
三、實驗內容和要求
給定四個關聯表，其定義和數據加載如下：
學生表 Student
create table Student(Sid varchar(6), Sname varchar(10), Sdate datetime, Ssex varchar(10));
insert into Student values('01' , '趙雷' , '1999-01-01' , '男');
insert into Student values('02' , '錢電' , '1999-12-21' , '男');
insert into Student values('03' , '孫風' , '1999-05-20' , '男');
insert into Student values('04' , '李云' , '1999-08-06' , '男');
insert into Student values('05' , '周梅' , '2000-12-01' , '女');
insert into Student values('06' , '吳蘭' , '2001-03-01' , '女');
insert into Student values('07' , '鄭竹' , '1998-07-01' , '女');
insert into Student values('08' , '王菊' , '1999-01-20' , '女');
選課表 SC
create table SC(Sid varchar(10), Cid varchar(10), score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
課程表 Course
create table Course(Cid varchar(10),Cname varchar(10),Tid varchar(10));
insert into Course values('01' , '語文' , '02');
insert into Course values('02' , '數學' , '01');
insert into Course values('03' , '英語' , '03');
教師表 Teacher
create table Teacher(Tid varchar(10),Tname varchar(10));
insert into Teacher values('01' , '張三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
四張表之間的關聯很簡單：
試按以下操作要求完成SQL語言設計。
- 查詢" 01 “課程比” 02 “課程成績高的學生的信息及對應” 01 “和” 02 "課程分數。要求輸出結構如下圖所示：
SELECT s.Sid, s.Sname, s.Sdate,s.Ssex,sc1.score AS score_01, sc2.score AS score_02
FROM Student s
JOIN SC sc1 ON s.Sid = sc1.Sid AND sc1.Cid = '01'
JOIN SC sc2 ON s.Sid = sc2.Sid AND sc2.Cid = '02'
WHERE sc1.score > sc2.score;
- 查詢平均成績大于等于 60 分的學生信息，輸出其編號Sid、姓名Sname和平均成績Avg_score三列內容。
SELECT s.Sid, s.Sname, AVG(sc.score) AS Avg_score
FROM Student s
JOIN SC sc ON s.Sid = sc.Sid
GROUP BY s.Sid, s.Sname
HAVING AVG(sc.score) >= 60;
3.查詢在 SC 表存在成績的學生信息，輸出Sid、Sname、Sdate和Ssex四列內容。
SELECT s.Sid, s.Sname, s.Sdate, s.Ssex
FROM Student s
JOIN SC sc ON s.Sid = sc.Sid;
- 查詢Student表中所有同學的學生編號Sid、學生姓名Sname和對應選課總數、所有課程的總成績(沒成績的顯示為 null )。要求輸出結構如下圖所示：
SELECT s.Sid, s.Sname, COUNT(sc.Cid) AS '選課總數', SUM(sc.score) AS '總成績'
FROM Student s
LEFT JOIN SC sc ON s.Sid = sc.Sid
GROUP BY s.Sid, s.Sname;
- 查詢「李」姓老師的數量。
SELECT COUNT(*) FROM Teacher t WHERE t.Tname LIKE '李%';
6.查詢學過「張三」老師授課的學生信息，輸出其Sid、Sname、Sdate和Ssex四列內容。
SELECT s.Sid, s.Sname, s.Sdate, s.Ssex
FROM Student s
JOIN SC sc ON s.Sid = sc.Sid
JOIN Course c ON sc.Cid = c.Cid
JOIN Teacher t ON c.Tid = t.Tid
WHERE t.Tname = '張三';
7.查詢選課表SC內沒有選修課程表Course里所列所有課程的學生信息，輸出其Sid、Sname、Sdate和Ssex四列內容。
SELECT S.Sid, S.Sname, S.Sdate, S.Ssex
FROM Student S
WHERE S.Sid NOT IN (SELECT DISTINCT SC.SidFROM SCWHERE SC.Cid NOT IN (SELECT C.CidFROM Course C)
);
8.查詢和" 01 "號學生學習的課程完全相同的其他學生信息，輸出其學號Sid內容。
WITH CourseList_01 AS (SELECT Cid FROM SC WHERE Sid = '01'
)
SELECT DISTINCT s2.Sid
FROM Student s2
JOIN SC sc2 ON s2.Sid = sc2.Sid
WHERE sc2.Cid IN (SELECT Cid FROM CourseList_01)
GROUP BY s2.Sid
HAVING COUNT(DISTINCT sc2.Cid) = (SELECT COUNT(*) FROM CourseList_01)
AND s2.Sid != '01';
9.查詢至少有一門課與學號為" 01 "的同學所學相同的學生信息，輸出其Sid、Sname、Sdate和Ssex四列內容。
SELECT DISTINCT s.Sid, s.Sname, s.Sdate, s.Ssex
FROM Student s
JOIN SC sc ON s.Sid = sc.Sid
WHERE EXISTS (SELECT 1 FROM SC sc_refWHERE sc_ref.Sid = '01' AND sc_ref.Cid = sc.Cid
)
AND s.Sid != '01';
10.查詢沒學過"張三"老師講授的任一門課程的學生姓名。
SELECT s.Sname
FROM Student s
WHERE s.Sid NOT IN (SELECT sc.SidFROM SC scJOIN Course c ON sc.Cid = c.CidJOIN Teacher t ON c.Tid = t.TidWHERE t.Tname = '張三'
);
11.檢索" 01 "課程分數小于 60并按分數降序排列的學生信息，輸出其Sid、Sname、Sdate和Ssex四列內容。
SELECT s.Sid, s.Sname, s.Sdate, s.Ssex
FROM Student s
JOIN SC sc ON s.Sid = sc.Sid
WHERE sc.Cid = '01' AND sc.score < 60
ORDER BY sc.score DESC;
- 按平均成績從高到低顯示學生的課程成績以及平均成績。要求輸出結構如下圖所示：
SELECT s.Sid,MAX(CASE WHEN sc.Cid = '01' THEN sc.score ELSE NULL END) AS score_01,MAX(CASE WHEN sc.Cid = '02' THEN sc.score ELSE NULL END) AS score_02,MAX(CASE WHEN sc.Cid = '03' THEN sc.score ELSE NULL END) AS score_03,AVG(sc.score) AS 'avg(score)'
FROM Student s
JOIN SC sc ON s.Sid = sc.Sid
GROUP BY s.Sid
HAVING COUNT(sc.Cid) > 0
ORDER BY AVG(sc.score) DESC;
13.查詢出只選修兩門課程的學生學號和姓名兩列信息
SELECT s.Sid, s.Sname
FROM Student s
JOIN SC sc ON s.Sid = sc.Sid
GROUP BY s.Sid, s.Sname
HAVING COUNT(DISTINCT sc.Cid) = 2;
14.查詢本月過生日的學生信息，輸出其Sid、Sname、Sdate和Ssex四列內容。
SELECT Sid, Sname, Sdate, Ssex
FROM Student
WHERE MONTH(Sdate) = MONTH(CURRENT_DATE()) AND YEAR(Sdate) = YEAR(CURRENT_DATE());
四、實驗出現的問題及解決方案
-
執行時間過長，尤其是涉及到大量數據的查詢。
—— 盡量避免全表掃描，使用更精確的篩選條件。 -
在嘗試進行一些復雜集合查詢時，如查詢選修了某一特定組合課程的學生，發現直接用SQL實現邏輯較為復雜且效率低下。
