CREATE TABLE COURSE (
CN varchar(32) NOT NULL COMMENT ‘课程编号’,
CNAME varchar(32) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT ‘课程表name’,
TN varchar(32) DEFAULT NULL COMMENT ‘教师编号’,
PRIMARY KEY (CN) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE SC (
SN varchar(32) DEFAULT NULL COMMENT ‘学生编号’,
CN varchar(32) DEFAULT NULL COMMENT ‘课程编号’,
SCORE int(8) DEFAULT NULL COMMENT ‘分数’
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE STUDENT (
SN varchar(32) CHARACTER SET utf8mb4 NOT NULL COMMENT ‘student_no’,
SNAME varchar(32) CHARACTER SET utf8mb4 NOT NULL COMMENT ‘姓名’,
SAGE datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT ‘年龄’,
SSEX varchar(12) DEFAULT NULL COMMENT ‘性别’,
PRIMARY KEY (SN) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE TEACHER (
TN varchar(32) DEFAULT NULL COMMENT ‘老师编号’,
TNAME varchar(32) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT ‘老师姓名’
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO COURSE(CN, CNAME, TN) VALUES (‘001’, ‘企业管理’, ‘1’);
INSERT INTO COURSE(CN, CNAME, TN) VALUES (‘002’, ‘马克思’, ‘2’);
INSERT INTO COURSE(CN, CNAME, TN) VALUES (‘003’, ‘OO&UML’, ‘3’);
INSERT INTO COURSE(CN, CNAME, TN) VALUES (‘004’, ‘数据库’, ‘4’);
INSERT INTO SC(SN, CN, SCORE) VALUES (‘1’, ‘002’, 43);
INSERT INTO SC(SN, CN, SCORE) VALUES (‘2’, ‘002’, 90);
INSERT INTO SC(SN, CN, SCORE) VALUES (‘3’, ‘002’, 32);
INSERT INTO SC(SN, CN, SCORE) VALUES (‘4’, ‘002’, 23);
INSERT INTO SC(SN, CN, SCORE) VALUES (‘5’, ‘002’, 76);
INSERT INTO SC(SN, CN, SCORE) VALUES (‘6’, ‘002’, 98);
INSERT INTO SC(SN, CN, SCORE) VALUES (‘1’, ‘003’, 65);
INSERT INTO SC(SN, CN, SCORE) VALUES (‘2’, ‘003’, 99);
INSERT INTO SC(SN, CN, SCORE) VALUES (‘3’, ‘003’, 63);
INSERT INTO SC(SN, CN, SCORE) VALUES (‘4’, ‘003’, 25);
INSERT INTO SC(SN, CN, SCORE) VALUES (‘5’, ‘003’, 18);
INSERT INTO SC(SN, CN, SCORE) VALUES (‘1’, ‘001’, 100);
INSERT INTO SC(SN, CN, SCORE) VALUES (‘2’, ‘001’, 89);
INSERT INTO SC(SN, CN, SCORE) VALUES (‘3’, ‘001’, 43);
INSERT INTO SC(SN, CN, SCORE) VALUES (‘1’, ‘004’, 1);
INSERT INTO SC(SN, CN, SCORE) VALUES (‘2’, ‘004’, 100);
INSERT INTO SC(SN, CN, SCORE) VALUES (‘3’, ‘004’, 98);
INSERT INTO SC(SN, CN, SCORE) VALUES (‘4’, ‘004’, 97);
INSERT INTO SC(SN, CN, SCORE) VALUES (‘5’, ‘004’, 96);
INSERT INTO SC(SN, CN, SCORE) VALUES (‘7’, ‘004’, 11);
INSERT INTO SC(SN, CN, SCORE) VALUES (‘8’, ‘004’, 96);
INSERT INTO SC(SN, CN, SCORE) VALUES (‘6’, ‘004’, 22);
INSERT INTO STUDENT(SN, SNAME, SAGE, SSEX) VALUES (‘1’, ‘张三’, ‘2011-04-13 01:13:34’, ‘1’);
INSERT INTO STUDENT(SN, SNAME, SAGE, SSEX) VALUES (‘2’, ‘李四’, ‘2012-04-13 01:13:34’, ‘1’);
INSERT INTO STUDENT(SN, SNAME, SAGE, SSEX) VALUES (‘3’, ‘韩梅梅’, ‘2013-04-13 01:13:34’, ‘0’);
INSERT INTO STUDENT(SN, SNAME, SAGE, SSEX) VALUES (‘4’, ‘jim’, ‘2014-04-13 01:13:34’, ‘1’);
INSERT INTO STUDENT(SN, SNAME, SAGE, SSEX) VALUES (‘5’, ‘lily’, ‘2020-04-15 03:31:05’, ‘0’);
INSERT INTO STUDENT(SN, SNAME, SAGE, SSEX) VALUES (‘6’, ‘lily’, ‘2016-04-13 01:13:34’, ‘0’);
INSERT INTO STUDENT(SN, SNAME, SAGE, SSEX) VALUES (‘7’, ‘AA’, ‘2011-04-13 01:13:34’, ‘0’);
INSERT INTO STUDENT(SN, SNAME, SAGE, SSEX) VALUES (‘8’, ‘AA’, ‘2020-04-15 03:31:17’, ‘1’);
INSERT INTO TEACHER(TN, TNAME) VALUES (‘1’, ‘叶平’);
INSERT INTO TEACHER(TN, TNAME) VALUES (‘2’, ‘李文’);
INSERT INTO TEACHER(TN, TNAME) VALUES (‘3’, ‘潘潘’);
INSERT INTO TEACHER(TN, TNAME) VALUES (‘4’, ‘五方’);
– 1、查询“001”课程比“002”课程成绩高的所有学生的学号；
SELECT a.sn FROM (select SN,SCORE from SC where cn=‘001’ )as a , (select SN,SCORE from SC where cn=‘002’)as b where a.sn =b.sn and a.score>b.score；
– 2.查询平均成绩大于60分的同学的学号和平均成绩；
select sn,AVG(score) from SC group by sn having AVG(score)>60;
– 3、查询所有同学的学号、姓名、选课数、总成绩；
select SC.SN,count(SC.CN) ,SUM(SC.SCORE), STUDENT.SNAME FROM SC, STUDENT WHERE STUDENT.sn=SC.sn group by SC.sn,STUDENT.SNAME
select SC.SN, count(SC.CN) ,SUM(SC.SCORE), STUDENT.SNAME FROM SC LEFT JOIN STUDENT ON SC.SN = STUDENT.SN group by SC.SN, STUDENT.SNAME
– 4、查询姓“李”的老师的个数；
select count(*) from TEACHER where Tname like ‘李%’;
– 5 查询没学过“叶平”老师课的同学的学号、姓名；
select STUDENT.SN, STUDENT.Sname from STUDENT where STUDENT.SN not in (select SC.SN from TEACHER, COURSE, SC where TEACHER.TN = COURSE.TN and SC.CN = COURSE.CN AND TEACHER.TNAME =‘叶平’)
– 6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
SELECT STUDENT.SN, STUDENT.Sname from STUDENT, SC WHERE STUDENT.SN =SC.SN AND SC.CN =‘001’ AND EXISTS (SELECT * from STUDENT, SC SC1 WHERE STUDENT.SN =SC1.SN AND SC1.CN =‘002’ AND STUDENT.SN = SC1.SN)
– 7查询学过“叶平”老师所教的所有课的同学的学号、姓名；
– 8 查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
SELECT a.SN, a.Sname FROM (SELECT STUDENT.SN, STUDENT.Sname, SC.SCORE from STUDENT, SC WHERE STUDENT.SN =SC.SN AND SC.CN =‘002’)a, (SELECT STUDENT.SN, STUDENT.Sname, SC.SCORE from STUDENT, SC WHERE STUDENT.SN =SC.SN AND SC.CN =‘001’ )b where a.SN = b.SN and a.SCORE <b.SCORE
– 9、查询所有课程成绩小于60分的同学的学号、姓名；
SELECT STUDENT.SN, STUDENT.Sname, SC.SCORE from STUDENT, SC WHERE STUDENT.SN =SC.SN and SCORE <60;
– 10 查询没有学全所有课的同学的学号、姓名；
SELECT STUDENT.SN, STUDENT.Sname , COUNT(SC.CN) from STUDENT, SC WHERE STUDENT.SN = SC.SN GROUP BY STUDENT.SN, STUDENT.Sname HAVING COUNT(SC.CN)<( SELECT COUNT(*) FROM COURSE )
– 11 查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；
– 13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；
UPDATE SC INNER join (SELECT CN, AVG(SCORE) AS AVGSCORE FROM SC GROUP BY CN HAVING CN = (select COURSE.CN from TEACHER, COURSE where TEACHER.TN = COURSE.TN AND TEACHER.TNAME =‘叶平’))a on SC.CN =a.cn set SCORE = a.AVGSCORE
– 14、查询和“1”号的同学学习的课程完全相同的其他同学学号和姓名；
– [ERROR 有超集问题]SELECT SN, COUNT(CN) FROM SC WHERE CN IN (select CN FROM SC WHERE SC.SN =‘1’) GROUP BY SN HAVING COUNT(*) =(select COUNT(CN) FROM SC WHERE SC.SN =‘1’)
select sn, count(cn) from SC where sn<>‘6’ and sn in (select sn from SC where cn in(select cn from SC where sn=‘6’)) group by sn having count(cn)=(select count(cn) from SC where sn=‘6’)
– 15、删除学习“叶平”老师课的SC表记录；
delete from SC where SC.CN =(select COURSE.CN from TEACHER, COURSE where TEACHER.TN = COURSE.TN AND TEACHER.TNAME =‘叶平’)
– 18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
SELECT L.CN 课程id,L.score 最高分, R.SCORE 最低分 FROM SC AS L, SC AS R
WHERE L.score = (SELECT MAX( IL.score ) FROM SC AS IL ,STUDENT as IM WHERE IM.SN =IL.SN AND L.CN = IL.CN GROUP BY IL.CN )
AND R.SCORE = (SELECT MIN( IR.score ) FROM SC AS IR WHERE R.CN = IR.CN GROUP BY IR.CN );
SELECT SC.CN 课程id,MAX(SC.score) 最高分, MIN(SC.SCORE) 最低分 FROM SC GROUP BY SC.CN;
– 19、按各科平均成绩从低到高和及格率的百分数从高到低顺序（1.先获取各科的平均成绩，2.获取各科的及格率，3.排序）
select SC.CN AS ‘课程号’, MAX(COURSE.CNAME) as ‘课程名’ ,IFNULL(avg(SC.SCORE), 0) AS ‘平均成绩’ , 100* SUM(CASE WHEN IFNULL(SC.SCORE, 0)>=60 THEN 1 ELSE 0 END )/COUNT() AS ‘及格百分数’ from SC, COURSE where SC.CN=COURSE.CN GROUP BY SC.CN
order by 100 SUM(CASE WHEN IFNULL(SC.SCORE, 0)>=60 THEN 1 ELSE 0 END )/COUNT(*) desc;
– 20.查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（001），马克思（002），OO&UML （003），数据库（004）
select avg(SC.SCORE), COURSE.CN, max(COURSE.CNAME) from SC, COURSE where SC.CN=COURSE.CN group by COURSE.CN
select SUM(CASE WHEN CN=‘001’ then SCORE else 0 end )/ SUM(case when CN=‘001’ then 1 else 0 end) as ‘企业管理平均分’,100* SUM(CASE WHEN CN=‘001’ AND SCORE>60 then 1 else 0 end )/ SUM(case when CN=‘001’ then 1 else 0 end) as ‘企业管理及格百分数’
FROM SC
– 21 查询不同老师所教不同课程平均分从高到低显示
select max(t.TNAME), max(t.TN), avg(s.SCORE) FROM TEACHER t, SC s, COURSE c where t.TN = c.TN and s.CN =c.CN GROUP BY s.CN
– 22、查询如下课程成绩第 2 名到第 4 名的学生成绩单：企业管理（001），马克思（002），UML （003），数据库（004）[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩
– 查询 企业管理（001） 第 2 名到第 4 名的学生成绩单
select * from SC where CN=‘002’ order by SCORE desc LIMIT 1, 3;
– 23 统计列印各科成绩,各分数段人数: 课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
SELECT s.CN as ‘课程id’, c.CNAME as ‘课程名称’, SUM(case when s.SCORE >85 then 1 else 0 end ) as ‘[100-85]’, SUM(case when s.SCORE <85 and s.SCORE >70 then 1 else 0 end ) as ‘[85-70]’ FROM SC s, COURSE c where s.CN =c.CN GROUP BY s.CN , c.CNAME
– 24 查询学生平均成绩及其名次
select 1+( select count(平均成绩) from (select SN, AVG(SCORE) as ‘平均成绩’ from SC group by SN ) AS T1 WHERE T1.平均成绩>T2.平均成绩) AS ‘名次’, T2.SN AS ‘学号’, 平均成绩 from (select SN, AVG(SCORE) as ‘平均成绩’ from SC group by SN) as T2 ORDER BY 平均成绩 DESC;
– 25、查询各科成绩前三名的记录:(不考虑成绩并列情况)
select S1.CN, S1.SCORE, COUNT(S2.SCORE) AS RANK FROM SC AS S1 inner join SC AS S2 ON S1.CN =S2.CN AND S1.SCORE >= S2.SCORE
GROUP BY S1.CN, S1.SCORE ORDER BY S1.CN ASC , RANK DESC;
select S1.* , S2.* FROM SC AS S1 inner join SC AS S2 ON S1.CN =S2.CN AND S1.SCORE >=S2.SCORE
– 意思就是：我们要查询的这个人，在这一门中分数比他高的少于3个人,那他就是前三名。括号里面是查询在这一门中分数比他高的人的数量。
SELECT a.CN,a.score FROM SC a WHERE (SELECT COUNT(cn) FROM SC WHERE CN=a.CN AND a.score<score) < 3 ORDER BY cn ASC,score DESC
– 26、查询每门课程被选修的学生数
select COUNT(SN), CN from SC GROUP BY CN;
– 27、查询出只选修了一门课程的全部学生的学号和姓名
select COUNT(SC.CN), SC.SN, STUDENT.SNAME from SC ,STUDENT WHERE SC.SN =STUDENT.SN GROUP BY SC.SN, STUDENT.SNAME HAVING count(SC.CN)=1;
– 28、查询男生、女生人数
SELECT COUNT(SN), SSEX from STUDENT GROUP BY SSEX HAVING SSEX =1 ;
SELECT COUNT(SN), SSEX from STUDENT GROUP BY SSEX HAVING SSEX =0 ;
– 29、查询姓“张”的学生名单
SELECT * FROM STUDENT WHERE SNAME LIKE ‘张%’;
– 30、查询同名同性学生名单，并统计同名人数
select SNAME, SSEX, count() from STUDENT group by SNAME, SSEX having count()>1;
select s1.* from STUDENT s1, STUDENT s2 where s1.SN !=s2.SN and s1.SNAME =s2.SNAME and s1.SSEX =s2.SSEX – 垃圾写法
– 31、2013年出生的学生名单(注：Student表中Sage列的类型是datetime)
select * from STUDENT where SAGE > ‘20130101000000’ and SAGE < ‘20140101000000’;
– 32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
select CN, avg(SCORE) from SC GROUP BY CN ORDER BY avg(SCORE), CN DESC;
– 33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
SELECT SC.SN, STUDENT.SNAME, AVG(SC.SCORE) FROM SC, STUDENT WHERE SC.SN = STUDENT.SN GROUP BY SC.SN, STUDENT.SNAME HAVING AVG(SC.SCORE)> 85;
– 34、查询课程名称为“数据库”，且分数低于60的学生姓名和分数
SELECT IFNULL(SC.SCORE, 0) as sco, STUDENT.SNAME FROM COURSE, SC, STUDENT WHERE COURSE.CN = SC.CN AND SC.SN =STUDENT.SN AND COURSE.CNAME =‘数据库’ AND SC.SCORE < 60;
– 35、查询所有学生的选课情况；
SELECT * FROM COURSE, SC, STUDENT WHERE COURSE.CN = SC.CN AND SC.SN =STUDENT.SN
– 36查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
SELECT DISTINCT STUDENT.SN, STUDENT.SNAME, COURSE.CNAME, SC.SCORE FROM COURSE, SC, STUDENT WHERE COURSE.CN = SC.CN AND SC.SN =STUDENT.SN
– 37、查询不及格的课程，并按课程号从大到小排列
select DISTINCT CN from SC where SC.SCORE<60 ORDER BY CN DESC;
– 38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；
SELECT STUDENT.* FROM SC, STUDENT WHERE SC.SN =STUDENT.SN AND SC.CN =‘003’ AND SC.SCORE> 80;
– 39、求选了课程的学生人数
SELECT COUNT(*) FROM (SELECT DISTINCT SN FROM SC GROUP BY SN) AS B;
– 40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩
SELECT STUDENT.SNAME, SC.SCORE FROM TEACHER , SC, COURSE, STUDENT WHERE TEACHER.TN = COURSE.TN AND SC.CN = COURSE.CN AND STUDENT.SN =SC.SN AND TEACHER.TNAME = ‘叶平’ AND SC.SCORE IN (SELECT MAX(SCORE) FROM SC WHERE CN = SC.CN )
– 41、查询各个课程及相应的选修人数
SELECT COUNT(*), CN FROM SC GROUP BY CN;
– 42、查询不同课程, 成绩相同的学生的学号、课程号、学生成绩
SELECT SC.* FROM SC, SC AS SC1 WHERE SC.SCORE = SC1.SCORE AND SC.CN != SC1.CN;
– 43、查询每门功成绩最好的前两名
SELECT a.CN,a.score FROM SC a WHERE (SELECT COUNT(cn) FROM SC WHERE CN=a.CN AND a.score<score)< 3 ORDER BY cn ASC,score DESC
– 45、检索至少选修两门课程的学生学号
SELECT COUNT(CN), SN FROM SC GROUP BY SN HAVING COUNT(CN)>1
– 46、查询全部学生都选修的课程的课程号和课程名
SELECT SC.CN, COURSE.CNAME FROM COURSE, SC WHERE COURSE.CN = SC.CN GROUP BY SC.CN, COURSE.CNAME HAVING COUNT() =(select count() from STUDENT)
– 47、查询没学过“叶平”老师讲授的任一门课程的学生姓名
SELECT * FROM STUDENT WHERE SN NOT IN (
SELECT STUDENT.SN FROM TEACHER , SC, COURSE, STUDENT WHERE TEACHER.TN = COURSE.TN AND SC.CN = COURSE.CN AND STUDENT.SN =SC.SN AND TEACHER.TNAME = ‘叶平’ )
SELECT * FROM STUDENT S1 WHERE NOT EXISTS (
SELECT STUDENT.SN FROM TEACHER , SC, COURSE, STUDENT WHERE TEACHER.TN = COURSE.TN AND SC.CN = COURSE.CN AND STUDENT.SN =SC.SN AND TEACHER.TNAME = ‘叶平’ and S1.SN = STUDENT.SN)
– 48、查询两门以上不及格课程的同学的学号及其平均成绩
select SC.SN , AVG(SC.SCORE) from SC where SC.SN in (select S1.SN from SC AS S1 where SCORE <60 GROUP BY S1.SN HAVING COUNT(*) >1) GROUP BY SC.SN
– 49、检索“004”课程分数小于60，按分数降序排列的同学学号
SELECT SC.SN , SC.SCORE FROM SC WHERE SC.CN=‘004’ AND SC.SCORE<60 ORDER BY SC.SCORE DESC;
– 50、删除“002”同学的“001”课程的成绩
delete from SC where CN =‘001’ AND CN =‘002’
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
