SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
? `Sid` varchar(6) NOT NULL DEFAULT '',
? `Sname` varchar(10) DEFAULT NULL,
? `Sage` datetime DEFAULT NULL,
? `Ssex` varchar(10) DEFAULT NULL,
? PRIMARY KEY (`Sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
? `Tid` varchar(10) NOT NULL DEFAULT '',
? `Tname` varchar(10) DEFAULT NULL,
? PRIMARY KEY (`Tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
? `Cid` varchar(10) NOT NULL DEFAULT '',
? `Tid` varchar(10) NOT NULL DEFAULT '',
? `Cname` varchar(10) DEFAULT NULL,
? PRIMARY KEY (`Cid`),
? CONSTRAINT `course_ibfk_1` FOREIGN KEY (`Tid`) REFERENCES `teacher` (`Tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
? `Sid` varchar(10) NOT NULL DEFAULT '',
? `Cid` varchar(10) NOT NULL DEFAULT '',
? `score` decimal(18,1) DEFAULT NULL,
? CONSTRAINT `sc_ibfk_1` FOREIGN KEY (`Sid`) REFERENCES `student` (`Sid`),
? CONSTRAINT `sc_ibfk_2` FOREIGN KEY (`Cid`) REFERENCES `course` (`Cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into Student values('01' , '趙雷' , '1990-01-01' , '男');
insert into Student values('02' , '錢電' , '1990-12-21' , '男');
insert into Student values('03' , '孫風(fēng)' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吳蘭' , '1992-03-01' , '女');
insert into Student values('07' , '鄭竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
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);
insert into Course values('01' , '語文' , '02');
insert into Course values('02' , '數(shù)學(xué)' , '01');
insert into Course values('03' , '英語' , '03');
insert into Teacher values('01' , '張三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
1. 查詢" 01 "課程比" 02 "課程成績高的學(xué)生的信息及課程分?jǐn)?shù)
2. 查詢平均成績大于等于 60 分的同學(xué)的學(xué)生編號和學(xué)生姓名和平均成績
3. 查詢在 SC 表存在成績的學(xué)生信息
4. 查詢所有同學(xué)的學(xué)生編號敢艰、學(xué)生姓名业簿、選課總數(shù)腥寇、所有課程的總成績(沒成績的顯示為 null )
4.1 查有成績的學(xué)生信息
5. 查詢「李」姓老師的數(shù)量
6. 查詢學(xué)過「張三」老師授課的同學(xué)的信息
7. 查詢沒有學(xué)全所有課程的同學(xué)的信息
8. 查詢至少有一門課與學(xué)號為" 01 "的同學(xué)所學(xué)相同的同學(xué)的信息
9. 查詢和" 01 "號的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的信息
10. 查詢沒學(xué)過"張三"老師講授的任一門課程的學(xué)生姓名
11. 查詢兩門及其以上不及格課程的同學(xué)的學(xué)號爷抓，姓名及其平均成績
13. 按平均成績從高到低顯示所有學(xué)生的所有課程的成績以及平均成績
14. 查詢各科成績最高分、最低分和平均分诱告，以如下形式顯示：課程 ID恒削，課程 name羡藐，最高分揖曾，最低分落萎，平均分亥啦，及格率，中等率练链，優(yōu)良率翔脱，優(yōu)秀率(及格為>=60，中等為：70-80兑宇，優(yōu)良為：80-90碍侦，優(yōu)秀為：>=90）。
15. 按平均成績進(jìn)行排序隶糕，顯示總排名和各科排名，Score 重復(fù)時保留名次空缺
15.1 按平均成績進(jìn)行排序站玄，顯示總排名和各科排名枚驻，Score 重復(fù)時合并名次
17. 統(tǒng)計(jì)各科成績各分?jǐn)?shù)段人數(shù)：課程編號，課程名稱株旷，[100-85]再登，[85-70]，[70-60]晾剖，[60-0] 及所占百分比
18. 查詢各科成績前三名的記錄
20. 查詢出只選修兩門課程的學(xué)生學(xué)號和姓名
22. 查詢名字中含有「風(fēng)」字的學(xué)生信息
24. 查詢 1990 年出生的學(xué)生名單
33. 成績不重復(fù)锉矢，查詢選修「張三」老師所授課程的學(xué)生中，成績最高的學(xué)生信息及其成績
34. 成績有重復(fù)的情況下齿尽，查詢選修「張三」老師所授課程的學(xué)生中沽损，成績最高的學(xué)生信息及其成績
40. 查詢各學(xué)生的年齡，只按年份來算
41. 按照出生日期來算循头，當(dāng)前月日 < 出生年月的月日則绵估，年齡減一
42. 查詢本周過生日的學(xué)生
43. 查詢下周過生日的學(xué)生
44. 查詢本月過生日的學(xué)生
45. 查詢下月過生日的學(xué)生
46.查詢所有課程成績都大于80分的學(xué)生名字
47.查詢所有課程成績都大于80分的學(xué)生名字，不顯示重復(fù)名字卡骂。
查詢學(xué)生的最高成績国裳。
SELECT sid,score from sc group by sid ;
#1. 查詢" 01 "課程比" 02 "課程成績高的學(xué)生的信息及課程分?jǐn)?shù)
select s.* ,a.score as score_01,b.score as score_02 FROM
student s,
(SELECT Sid,score from sc WHERE sc.Cid = "01") a,
(SELECT Sid,score from sc WHERE sc.Cid = "02") b
where a.Sid = b.Sid and a.score> b.score and s.Sid = a.Sid;
SELECT a.*,b.score score1 from
(SELECT Sid,score from sc WHERE sc.Cid = "01") a,
(SELECT Sid,score from sc WHERE sc.Cid = "02") b
where a.sid = b.sid and a.score > b.score;
#2. 查詢平均成績大于等于60分的同學(xué)的學(xué)生編號和學(xué)生姓名和平均成績
SELECT sid,AVG(score) as avgg from sc GROUP BY sid? having AVG(score) >60;
SELECT Sname,sc.sid,AVG(score) as avgg from sc,student s WHERE sc.Sid = s.Sid GROUP BY sid? having AVG(score) >60;
#3. 查詢在 SC 表存在成績的學(xué)生信息
SELECT * from student where sid in(SELECT Sid from sc WHERE score is not null);
SELECT s.*,sc.score FROM student s,sc WHERE s.Sid = sc.sid and sc.score is not null group BY sc.sid;
#4. 查詢所有同學(xué)的學(xué)生編號、學(xué)生姓名全跨、選課總數(shù)缝左、所有課程的總成績(沒成績的顯示為 null )
這道題得用到left join或者right join，不能用where連接浓若，因?yàn)轭}目說了要求有顯示為null的渺杉，where是inner join，不會出現(xiàn)null七嫌，在這道題里會查不出第08號學(xué)生少办。
select? count(sid) as xuankezongshu, sum(score) as xuankezongfen from sc GROUP BY Sid;
select? s.*,count(sc.sid) as xuankezongshu, sum(score) as xuankezongshu from? student s, sc WHERE s.Sid = sc.sid GROUP BY sc.Sid;
select? s.*,count(sc.sid) as xuankezongshu, sum(score) as xuankezongshu from? student s LEFT JOIN sc on s.Sid = sc.sid GROUP BY sc.Sid;
4.1 查有成績的學(xué)生信息? ***************************
select s.*,a.xuankezongshu,a.xuankezongfen,a.score from student s,
(SELECT sid,score,count(sid) as xuankezongshu, sum(score) as xuankezongfen from? sc where score is not NULL GROUP BY sid) a
WHERE s.sid = a.sid
select s.sid, s.sname, count(*) as 選課總數(shù), sum(score) as 總成績,
? ? sum(case when cid = 01 then score else null end) as score_01,
? ? sum(case when cid = 02 then score else null end) as score_02,
? ? sum(case when cid = 03 then score else null end) as score_03
from student as s, sc
where s.sid = sc.sid
group by s.sid
5. 查詢「李」姓老師的數(shù)量
SELECT count(tid) from teacher t WHERE t.tname LIKE "李%";
6. 查詢學(xué)過「張三」老師授課的同學(xué)的信息
SELECT * from student WHERE sid in (SELECT sid FROM sc where cid in (SELECT cid from course WHERE tid in ( select tid from teacher t WHERE t.Tname = "張三" )));
select * from student where sid in (
? ? select sid from sc, course, teacher
? ? where sc.cid = course.cid
? ? and course.tid = teacher.tid
? ? and tname = '張三'
)
7. 查詢沒有學(xué)全所有課程的同學(xué)的信息
select * from student where sid in (select sid from sc group by sid having count(cid) < 3)
SELECT s.*,b.xuankeshu from student s,
(
select sid,count(*) as xuankeshu from sc group by sid HAVING xuankeshu <
(select count(*) coursecount from (SELECT count(cid) from sc group by cid) a)
) b
where s.sid = b.sid;
9. 查詢和" 01 "號的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的信息
參考地址：https://blog.csdn.net/u010452388/article/details/80150985
