USE school;
-- 班級表
CREATE TABLE class(cid TINYINT PRIMARY KEY AUTO_INCREMENT,caption VARCHAR(20)
);INSERT INTO class(caption) VALUES("三年二班"),("一年三班"),("三年一班");SELECT * FROM class;-- 老師表
CREATE TABLE teacher(tid TINYINT PRIMARY KEY AUTO_INCREMENT,tname VARCHAR(20)
);INSERT INTO teacher(tname) VALUES("波多"),("蒼空"),("飯島");SELECT * FROM teacher;-- 學生表
CREATE TABLE student(sid TINYINT PRIMARY KEY AUTO_INCREMENT,sname VARCHAR(20),gender VARCHAR(10),class_id TINYINT,FOREIGN KEY (class_id) REFERENCES class(cid)
);INSERT INTO student(sname,gender,class_id) VALUES("鋼蛋","女",1),("鐵錘","女",1),("山炮","男",2);SELECT * FROM student;-- 課程表
CREATE TABLE course(cid TINYINT PRIMARY KEY AUTO_INCREMENT,cname VARCHAR(20),teacher_id TINYINT,FOREIGN KEY (teacher_id) REFERENCES teacher(tid)
);
ALTER TABLE course MODIFY cid TINYINT, DROP PRIMARY KEY;
ALTER TABLE course ADD CONSTRAINT xx FOREIGN KEY (cid) REFERENCES class(cid);
DESC course;
SHOW CREATE TABLE course;
INSERT INTO course(cname,teacher_id) VALUES("生物",1),("體育",1),("物理",2);SELECT * FROM course;
-- 成績表
CREATE TABLE score(sid TINYINT PRIMARY KEY AUTO_INCREMENT,student_id TINYINT,course_id TINYINT,number INT,FOREIGN KEY (student_id) REFERENCES student(sid),FOREIGN KEY (course_id) REFERENCES course(cid)
);INSERT INTO score(student_id, course_id, number) VALUES(1,1,60),(1,2,59),(2,2,100);SELECT * FROM score;DELETE FROM score WHERE sid=6;# 二、操作表
#
# 1、自行創建測試數據
#
# 2、查詢“生物”課程比“物理”課程成績高的所有學生的學號；
SELECT B.student_id FROM
(SELECT score.student_id,score.number FROM score INNER JOIN course ON score.course_id = course.cid WHERE course.cname="生物") as AINNER JOIN
(SELECT score.student_id,score.number FROM score INNER JOIN course ON score.course_id = course.cid WHERE course.cname="物理") as BON A.student_id=B.student_id AND A.number>B.number;# 3、查詢平均成績大于60分的同學的學號和平均成績；
INSERT INTO score(student_id, course_id, number) VALUES(2,3,56),(3,1,46),(3,2,59),(3,3,71),(4,1,90),(4,2,27);
SELECT student_id,avg(number) 平均成績 FROM score GROUP BY student_id HAVING avg(number)>60;
# 4、查詢所有同學的學號、姓名、選課數、總成績；
SELECT student.sid,student.sname,count(student.class_id) 選課數,sum(number) 總成績FROM student INNER JOIN score ON student.sid = score.student_id GROUP BY sname ORDER BY 總成績;# 5、查詢姓“波”的老師的個數；
SELECT count(*) 波老師個數 FROM teacher WHERE tname LIKE "波%";
INSERT INTO teacher(tname) VALUES ("波大");
# 6、查詢沒學過“葉平”老師課的同學的學號、姓名；-- 得到所有同學學過的課程及其對應的老師,然后對應篩選
INSERT INTO teacher(tname) VALUES("葉平");
INSERT INTO course(cname, teacher_id) VALUES("歷史",5);
INSERT INTO score(student_id, course_id, number) VALUES(1,5,81);SELECT student.sid,student.sname,A.tname FROM scoreINNER JOIN student ON score.student_id = student.sidINNER JOIN (SELECT DISTINCT course.cid,course.teacher_id,course.cname,teacher.tname FROMcourse INNER JOIN teacher ON course.teacher_id = teacher.tid) AS A ON score.course_id = A.cidGROUP BY A.tname HAVING A.tname!="葉平";# 7、查詢學過“1”并且也學過編號“2”課程的同學的學號、姓名；
INSERT INTO student(sname,gender,class_id) VALUES("張三","男",3);
INSERT INTO score(student_id, course_id, number) VALUES(5,2,63);SELECT B.sid 學號,B.sname 姓名 FROM(SELECT student.sid,student.sname FROM score INNER JOIN student ON score.student_id = student.sid
WHERE course_id=1) AS AINNER JOIN(SELECT student.sid,student.sname FROM score INNER JOIN student ON score.student_id = student.sid
WHERE course_id=2) AS B ON A.sid = B.sid;# 8、查詢學過“葉平”老師所教的所有課的同學的學號、姓名；
SELECT * FROM student; SELECT * FROM score;
INSERT INTO student(sname, gender, class_id) VALUES("王五","男",3);
INSERT INTO score(student_id, course_id, number) VALUES(8,5,93);SELECT student.sid 學號,student.sname 姓名 FROM score INNER JOIN studentON score.student_id = student.sid AND course_id=5;# 9、查詢課程編號“2”的成績比課程編號“1”課程低的所有同學的學號、姓名；
INSERT INTO student(sname, gender, class_id) VALUES("趙六","女",2);
INSERT INTO score(student_id, course_id, number) VALUES(9,2,65),(9,1,70);
UPDATE score SET number = 65 WHERE sid=21;SELECT * FROM student WHERE(SELECT number FROM score WHERE student.sid=score.student_id AND score.course_id=2)<(SELECT number FROM score WHERE student.sid=score.student_id AND score.course_id=1);# 10、查詢有課程成績小于60分的同學的學號、姓名；
SELECT student.sid,student.sname FROM studentINNER JOIN score ON student.sid = score.student_id WHERE score.number<60 GROUP BY student.sname;
INSERT INTO student(sname, gender, class_id) VALUES("鋼镚","男",2);
INSERT INTO score(student_id, course_id, number) VALUES(4,3,48);
# 11、查詢沒有學全所有課的同學的學號、姓名；
-- 測試
SELECT count(cid) 總課程數 FROM course;
SELECT count(course_id) study_course FROM score GROUP BY student_id;
SELECT * FROM (SELECT count(cid) 總課程數 FROM course) AS AINNER JOIN(SELECT count(course_id) study_course FROM score GROUP BY student_id) AS BON A.總課程數 = B.study_course;-- 正確答案
SELECT student.sid,student.sname,count(course_id) 學習課程數 FROM score INNER JOIN studentON score.student_id = student.sid GROUP BY student_idHAVING count(course_id)=(SELECT count(cid) FROM course);# 12、查詢至少有一門課與學號為“001”的同學所學相同的同學的學號和姓名；
#
# 13、查詢至少學過學號為“001”同學所有一門課的其他同學學號和姓名；
#
# 14、查詢和“2”號的同學學習的課程完全相同的其他同學學號和姓名；# 15、刪除學習“葉平”老師課的SC表記錄；
#
# 16、向SC表中插入一些記錄，這些記錄要求符合以下條件：①沒有上過編號“002”課程的同學學號；②插入“002”號課程的平均成績；
#
# 17、按平均成績從低到高顯示所有學生的“語文”、“數學”、“英語”三門的課程成績，按如下形式顯示： 學生ID,語文,數學,英語,有效課程數,有效平均分；
#
# 18、查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分；
SELECT course_id,max(number) 最高分,min(number) 最低分 FROM score GROUP BY course_id;INSERT INTO score(student_id, course_id, number) VALUES(2,1,76);
# 19、按各科平均成績從低到高和及格率的百分數從高到低順序；-- 分析求出平均成績并計算及格率
SELECT avg(number) 課程平均成績 FROM score GROUP BY course_id;
SELECT count(student_id) 各科不及格人數 FROM score WHERE number>60 GROUP BY course_id;
SELECT count(student_id) 各科總人數 FROM score GROUP BY course_id;-- 答案如下
SELECT A.課程平均成績,B.各科不及格人數/C.各科總人數 AS 及格率 FROM(SELECT course_id,avg(number) 課程平均成績 FROM score GROUP BY course_id) AS AINNER JOIN(SELECT course_id,count(student_id) 各科不及格人數 FROM score WHERE number>60 GROUP BY course_id) AS BINNER JOIN(SELECT course_id,count(student_id) 各科總人數 FROM score GROUP BY course_id) AS CON A.course_id = B.course_id AND A.course_id = C.course_id ORDER BY A.課程平均成績;# 20、課程平均分從高到低顯示（顯示任課老師）；
SELECT teacher.tname,course.cname FROM teacher,course WHERE course.teacher_id = teacher.tid;
SELECT avg(number) 課程平均分 FROM teacher,score GROUP BY course_id;
-- 答案如下
SELECT A.tname 任課老師,B.課程平均分 FROM(SELECT teacher.tname,course.cname,course.cid FROM teacher,course WHERE course.teacher_id = teacher.tid) AS AINNER JOIN(SELECT avg(number) 課程平均分,teacher.tname,score.course_id FROM teacher,score GROUP BY course_id) AS BON A.cid=B.course_id ORDER BY B.課程平均分 DESC;# 21、查詢各科成績前三名的記錄:(不考慮成績并列情況)
SELECT * FROM score ORDER BY course_id;# 22、查詢每門課程被選修的學生數；-- 分析 根據成績根據課程進行分類,按照學生ID統計次數
SELECT score.course_id 課程ID,count(student_id) 課程人次 FROM score GROUP BY course_id;# 23、查詢出只選修了一門課程的全部學生的學號和姓名；
SELECT student.sid,student.sname FROM(SELECT * FROM score GROUP BY student_id HAVING count(student_id)=1) AS AINNER JOIN student ON A.student_id = student.sid;
# 24、查詢男生、女生的人數；
SELECT boy.男,girl.女 FROM(SELECT count(gender) 男 FROM student WHERE gender="男") as boy,(SELECT count(gender) 女 FROM student WHERE gender="女") as girl;# 25、查詢姓“張”的學生名單；
SELECT sid,sname FROM student WHERE sname LIKE "張%";
# 26、查詢同名同姓學生名單，并統計同名人數；
SELECT sname 姓名,count(sname) 同名人數 FROM student GROUP BY sname;
# 27、查詢每門課程的平均成績，結果按平均成績升序排列，平均成績相同時，按課程號降序排列；
SELECT A.cid 課程號,B.課程平均分 FROM(SELECT teacher.tname,course.cname,course.cid FROM teacher,course WHERE course.teacher_id = teacher.tid) AS AINNER JOIN(SELECT avg(number) 課程平均分,teacher.tname,score.course_id FROM teacher,score GROUP BY course_id) AS BON A.cid=B.course_id ORDER BY B.課程平均分 DESC;# 28、查詢平均成績大于85的所有學生的學號、姓名和平均成績；
SELECT student.sid 學號,student.sname 姓名,avg(score.number) 平均成績 FROMstudent INNER JOIN score ON student.sid = score.student_id GROUP BY sname;# 29、查詢課程名稱為“生物”，且分數低于60的學生姓名和分數；
SELECT course.cname 課程,student.sname 姓名,score.number 分數 FROM score INNER JOIN student INNER JOIN courseON score.student_id = student.sid AND score.course_id=course.cidWHERE course.cname="生物" AND score.number<60;# 30、查詢課程編號為3且課程成績在80分以上的學生的學號和姓名；
SELECT student.sid 學號,student.sname 姓名 FROM score INNER JOIN studentON score.student_id = student.sid AND score.course_id=3 AND score.number>80;# 31、求選了課程的學生人數
INSERT INTO student(sname, gender, class_id) VALUES("李四","男",3);
INSERT INTO student(sname, gender, class_id) VALUES("勝七","女",2);
INSERT INTO score(student_id, course_id, number) VALUES(7,1,74);
-- 答案如下
SELECT count(A.student_id) 選課人數 FROM (SELECT DISTINCT student_id FROM score) AS A;# 32、查詢選修“蒼空”老師所授課程的學生中，成績最高的學生姓名及其成績；
SELECT student.sname 姓名,max(number) 成績 FROM score INNER JOIN student INNER JOIN course INNER JOIN teacherON score.student_id=student.sid AND score.course_id=course.cid AND course.teacher_id=teacher.tidWHERE teacher.tname="蒼空";# 33、查詢各個課程及相應的選修人數；
SELECT score.course_id 課程ID,count(student_id) 課程人次 FROM score GROUP BY course_id;
# 34、查詢不同課程但成績相同的學生的學號、課程號、學生成績；
#
# 35、查詢每門課程成績最好的前兩名；
#
# 36、檢索至少選修兩門課程的學生學號；-- 分析 根據學生ID進行分組，統計課程出現次數,篩選課程次數大于等于2的學生
SELECT student_id FROM score GROUP BY student_id HAVING count(course_id)>=2;
# 37、查詢全部學生都選修的課程的課程號和課程名；
SELECT score.student_id 學生ID,course.cname 所選課程,course.cid 課程ID FROM score INNER JOIN courseON score.course_id = course.cid ORDER BY 學生ID;# 38、查詢沒學過“葉平”老師講授的任一門課程的學生姓名；
#
# 39、查詢兩門以上不及格課程的同學的學號及其平均成績；
SELECT A.student_id 學號,B.平均成績 FROM(SELECT student_id FROM score WHERE number<60 GROUP BY student_id HAVING count(number)>=2) AS A
INNER JOIN(SELECT student_id,avg(number) 平均成績 FROM score GROUP BY student_id) AS B ON A.student_id=B.student_id;# 40、檢索“1”課程分數小于60，按分數降序排列的同學學號；
SELECT student_id FROM score WHERE course_id=1 AND number<60;
# 41、刪除“2”同學的“1”課程的成績；-- 此題已答
)