1. 首页首页
  2. 科技前沿科技前沿

MYSQL练习题54道(包含尽可能多的解决方法及思路)

科技前沿 • • 阅读 55

MYSQL练习题54道(包含尽可能多的解决方法及思路)MYSQL 练习题 包含尽可能多的解决方法 下面的表和题目来自于网上 解题方法和思路及遇到的问题是本人在练习过程中遇到的 在这里分享给大家 为了更好的熟悉 sql 语句 希望大家在自己做练习的时候 可以用尽量多的方法来解决问题 在这篇 MYAQL 练习题当中 暂时不考虑查询的速度等问题

大家好，我是讯享网，很高兴认识大家。

MYSQL练习题（包含尽可能多的解决方法)

下面的表和题目来自于网上，解题方法和思路及遇到的问题是本人在练习过程中遇到的，在这里分享给大家。为了更好的熟悉sql语句，希望大家在自己做练习的时候，可以用尽量多的方法来解决问题，在这篇MYAQL练习题当中，暂时不考虑查询的速度等问题，在下面的语句中还有不足之处，请大家与我一起讨论如何更正，以及有更好的思路也请与我一起学习进步。(下面非红色字体的大都是有错误的）

–创建teacher表

DROP TABLE IF EXISTS teacher; CREATE TABLE teacher( tid INT NOT NULL PRIMARY KEY, tname VARCHAR(20) NOT NULL )DEFAULT CHARSET = 'utf8mb4'; INSERT INTO teacher(tid,tname)VALUES(1,'张老师'); INSERT INTO teacher(tid,tname)VALUES(2,'王老师'); INSERT INTO teacher(tid,tname)VALUES(3,'李老师'); INSERT INTO teacher(tid,tname)VALUES(4,'赵老师'); INSERT INTO teacher(tid,tname)VALUES(5,'刘老师'); INSERT INTO teacher(tid,tname)VALUES(6,'向老师'); INSERT INTO teacher(tid,tname)VALUES(7,'李文静'); INSERT INTO teacher(tid,tname)VALUES(8,'叶平');

讯享网

–创建student表

讯享网 DROP TABLE IF EXISTS student; CREATE TABLE student( sid INT NOT NULL PRIMARY KEY, sname VARCHAR(20) NOT NULL, sage DATETIME NOT NULL, ssex CHAR(2) NOT NULL )DEFAULT CHARSET = 'utf8mb4'; INSERT INTO student(sid,sname,sage,ssex) VALUES(1,'张三','1980-1-23','男'); INSERT INTO student(sid,sname,sage,ssex) VALUES(2,'李四','1982-12-12','男'); INSERT INTO student(sid,sname,sage,ssex) VALUES(3,'张飒','1981-9-9','男'); INSERT INTO student(sid,sname,sage,ssex) VALUES(4,'莉莉','1983-3-23','女'); INSERT INTO student(sid,sname,sage,ssex) VALUES(5,'王弼','1982-6-21','男'); INSERT INTO student(sid,sname,sage,ssex) VALUES(6,'王丽','1984-10-10','女'); INSERT INTO student(sid,sname,sage,ssex) VALUES(7,'刘香','1980-12-22','女');

–创建course表

DROP TABLE IF EXISTS course; CREATE TABLE course( cid INT NOT NULL PRIMARY KEY, cname VARCHAR(20) NOT NULL, tid INT NOT NULL )DEFAULT CHARSET = 'utf8mb4'; --增加外键 ALTER TABLE course ADD CONSTRAINT fk_course_teacher FOREIGN KEY(tid) REFERENCES teacher(tid); --表的结构 SHOW INDEX FROM course; insert into course(cid,cname,tid) values(1,'企业管理',3); insert into course(cid,cname,tid) values(2,'马克思',1); insert into course(cid,cname,tid) values(3,'UML',2); insert into course(cid,cname,tid) values(4,'数据库',5); insert into course(cid,cname,tid) values(5,'物理',8);

–创建sc表

讯享网DROP TABLE IF EXISTS sc; CREATE TABLE sc( sid int NOT NULL, cid int NOT NULL, score int NOT NULL )DEFAULT CHARSET = 'utf8mb4'; ALTER TABLE sc ADD CONSTRAINT fk_sc_course FOREIGN KEY(cid) REFERENCES course(cid); ALTER TABLE sc ADD CONSTRAINT fk_sc_student FOREIGN KEY(sid) REFERENCES student(sid); INSERT INTO sc(sid,cid,score)VALUES(1,1,80); INSERT INTO sc(sid,cid,score)VALUES(1,2,86); INSERT INTO sc(sid,cid,score)VALUES(1,3,83); INSERT INTO sc(sid,cid,score)VALUES(1,4,89); INSERT INTO sc(sid,cid,score)VALUES(2,1,50); INSERT INTO sc(sid,cid,score)VALUES(2,2,36); INSERT INTO sc(sid,cid,score)VALUES(2,3,43); INSERT INTO sc(sid,cid,score)VALUES(2,4,59); INSERT INTO sc(sid,cid,score)VALUES(3,1,50); INSERT INTO sc(sid,cid,score)VALUES(3,2,96); INSERT INTO sc(sid,cid,score)VALUES(3,3,73); INSERT INTO sc(sid,cid,score)VALUES(3,4,69); INSERT INTO sc(sid,cid,score)VALUES(4,1,90); INSERT INTO sc(sid,cid,score)VALUES(4,2,36); INSERT INTO sc(sid,cid,score)VALUES(4,3,88); INSERT INTO sc(sid,cid,score)VALUES(4,4,99); INSERT INTO sc(sid,cid,score)VALUES(5,1,90); INSERT INTO sc(sid,cid,score)VALUES(5,2,96); INSERT INTO sc(sid,cid,score)VALUES(5,3,98); INSERT INTO sc(sid,cid,score)VALUES(5,4,99); INSERT INTO sc(sid,cid,score)VALUES(6,1,70); INSERT INTO sc(sid,cid,score)VALUES(6,2,66); INSERT INTO sc(sid,cid,score)VALUES(6,3,58); INSERT INTO sc(sid,cid,score)VALUES(6,4,79); INSERT INTO sc(sid,cid,score)VALUES(7,1,80); INSERT INTO sc(sid,cid,score)VALUES(7,2,76); INSERT INTO sc(sid,cid,score)VALUES(7,3,68); INSERT INTO sc(sid,cid,score)VALUES(7,4,59); INSERT INTO sc(sid,cid,score)VALUES(7,5,89);

1、查询课程1的成绩比课程2的成绩高的所有学生的学号；

SELECT a.sid FROM (SELECT sid,score FROM sc WHERE cid = 01) a , (SELECT sid,score FROM sc WHERE cid = 02) b WHERE a.score>b.score AND a.sid = b.sid; --这个方法很重要 SELECT * FROM (SELECT * FROM sc WHERE cid = 01) a JOIN (SELECT * FROM sc WHERE cid = 02) b ON a.sid = b.sid WHERE a.score>b.score; --用子查询 SELECT a.sid FROM (SELECT * FROM sc WHERE cid = 01) a, (SELECT * FROM sc WHERE cid = 02) b WHERE a.score > b.score AND a.sid = b.sid; SELECT a.sid FROM sc a,(SELECT * FROM sc WHERE cid = 01) b, (SELECT * FROM sc WHERE cid = 02) c WHERE b.sid = c.sid AND b.score > c.score AND a.sid = b.sid AND a.sid = c.sid AND a.cid = b.cid;

–SELECT a.*
–FROM sc a
–INNER JOIN sc b ON a.cid = b.cid AND a.sid = b.sid
–WHERE (SELECT score FROM sc WHERE cid =01) > ALL(SELECT score FROM sc WHERE cid --=02)
–GROUP BY a.sid;(这里有没有好的方法实现，这个方法不对）

2、查询平均成绩大于60分的同学的学号和平均成绩；还有其他方法吗

讯享网SELECT a.sid,ROUND(AVG(a.score),2) AS `平均成绩` FROM sc a GROUP BY a.sid HAVING `平均成绩` > 60;

3、查询所有同学的学号、姓名、选课数、总成绩；

SELECT b.sid,b.sname,COUNT(a.cid) AS `选课数`,SUM(a.score) AS `总成绩` FROM sc a LEFT JOIN student b ON a.sid = b.sid GROUP BY b.sid,b.sname; SELECT b.sid,b.sname,COUNT(a.cid) AS `选课数`,SUM(a.score) AS `总成绩` FROM sc a, student b WHERE a.sid = b.sid GROUP BY b.sid,b.sname;

4、查询姓“李”的老师的个数；

讯享网SELECT COUNT(DISTINCT tid) AS `数量` FROM teacher WHERE tname LIKE '李%';

5、查询没学过“叶平”老师课的同学的学号、姓名；

SELECT sid,sname FROM student WHERE sid NOT IN (SELECT sid FROM sc WHERE cid IN (SELECT cid FROM course WHERE tid IN (SELECT tid FROM teacher WHERE tname = '叶平')));

– SELECT a.*
– FROM student a
– LEFT JOIN sc b ON a.sid = b.sid
– LEFT JOIN course c ON b.cid = c.cid
– LEFT JOIN teacher d ON c.tid = d.tid
– GROUP BY a.sid
– HAVING d.tname !=‘叶平’ AND 为什么 Unkidwn column ‘d.tname’ in ‘having clause’

讯享网SELECT a.*,d.tname FROM student a LEFT JOIN sc b ON a.sid = b.sid LEFT JOIN course c ON b.cid = c.cid LEFT JOIN teacher d ON c.tid = d.tid GROUP BY a.sid HAVING a.sid NOT IN (SELECT sid FROM sc WHERE cid IN (SELECT cid FROM course WHERE tid IN (SELECT tid FROM teacher WHERE tname = '叶平'))); SELECT e.* FROM (SELECT * FROM student a NATURAL JOIN sc b NATURAL JOIN course c NATURAL JOIN teacher d ) e WHERE e.tname != '叶平' AND e.sid NOT IN (SELECT f.sid FROM (SELECT * FROM student a NATURAL JOIN sc b NATURAL JOIN course c NATURAL JOIN teacher d ) f WHERE f.tname = '叶平') GROUP BY e.sid;

– SELECT e.*
– FROM (SELECT * FROM student a
– NATURAL JOIN sc b
– NATURAL JOIN course c
– NATURAL JOIN teacher d ) e
– WHERE e.tname != ‘叶平’ AND e.sid NOT IN (SELECT e.sid FROM e WHERE e.tname = ‘叶平’)
– GROUP BY e.sid;这个方法也不行

SELECT a.* FROM student a WHERE a.sid NOT IN (SELECT sid FROM sc b,course c,teacher d WHERE b.cid = c.cid AND c.tid = d.tid AND d.tname = '叶平') ; SELECT a.* FROM student a WHERE a.sid NOT IN (SELECT sid FROM sc b LEFT JOIN course c ON b.cid = c.cid LEFT JOIN teacher d ON c.tid = d.tid WHERE d.tname = '叶平') ;

6、查询同时学过课程1和课程2的同学的学号、姓名；

讯享网SELECT a.sid,a.sname FROM student a LEFT JOIN sc b ON a.sid = b.sid WHERE b.cid IN (01,02) GROUP BY a.sid,a.sname; SELECT c.sid,c.sname FROM student c WHERE c.sid IN (SELECT a.sid FROM sc a LEFT JOIN sc b ON a.sid = b.sid WHERE a.cid = 01 AND b.cid = 02); SELECT a.sid,a.sname FROM student a,sc b WHERE a.sid = b.sid AND b.cid IN (01,02) GROUP BY a.sid,a.sname; SELECT sid,sname FROM student WHERE sid IN (SELECT sid FROM sc WHERE cid =01) AND sid IN (SELECT sid FROM sc WHERE cid = 02); SELECT a.sid,a.sname FROM student a, (SELECT sid FROM sc WHERE cid =01) b, (SELECT sid FROM sc WHERE cid =02) c WHERE a.sid = b.sid AND a.sid = c.sid; SELECT a.sid,a.sname FROM student a LEFT JOIN (SELECT sid FROM sc WHERE cid =01) b ON a.sid = b.sid LEFT JOIN (SELECT sid FROM sc WHERE cid =02) c ON a.sid = c.sid;

7、查询学过“叶平”老师所教课程的所有同学的学号、姓名；

SELECT a.sid,a.sname FROM student a LEFT JOIN sc b ON a.sid = b.sid LEFT JOIN course c ON b.cid = c.cid LEFT JOIN teacher d ON c.tid = d.tid WHERE d.tname = '叶平' GROUP BY a.sid,a.sname; SELECT sid,sname FROM student WHERE sid IN (SELECT sid FROM sc WHERE cid IN (SELECT cid FROM course WHERE tid IN (SELECT tid FROM teacher WHERE tname = '叶平'))); SELECT a.sid,a.sname FROM student a LEFT JOIN sc b ON a.sid = b.sid LEFT JOIN course c ON b.cid = c.cid LEFT JOIN teacher d ON c.tid = d.tid GROUP BY a.sid HAVING a.sid IN (SELECT sid FROM sc WHERE cid IN (SELECT cid FROM course WHERE tid IN (SELECT tid FROM teacher WHERE tname = '叶平'))); SELECT e.* FROM (SELECT * FROM student a NATURAL JOIN sc b NATURAL JOIN course c NATURAL JOIN teacher d ) e WHERE e.tname != '叶平' AND e.sid IN (SELECT f.sid FROM (SELECT * FROM student a NATURAL JOIN sc b NATURAL JOIN course c NATURAL JOIN teacher d ) f WHERE f.tname = '叶平') GROUP BY e.sid; SELECT a.* FROM student a WHERE a.sid IN (SELECT sid FROM sc b,course c,teacher d WHERE b.cid = c.cid AND c.tid = d.tid AND d.tname = '叶平') ;

8、查询 课程编号1的成绩 比 课程编号2的成绩 高的所有同学的学号、姓名；

讯享网SELECT a.sid,a.sname FROM student a LEFT JOIN sc b ON a.sid = b.sid AND b.cid = 01 LEFT JOIN sc c ON a.sid = c.sid AND c.cid = 02 WHERE b.score > c.score; SELECT a.sid,a.sname FROM student a,sc b,sc c WHERE a.SId = b.SId AND a.SId = c.SId AND b.CId = 01 AND c.CId = 02 AND b.score > c.score; SELECT a.sid,a.sname FROM student a, (SELECT sid,score FROM sc WHERE cid = 01 ) b, (SELECT sid,score FROM sc WHERE cid = 02 ) c WHERE b.score > c.score AND a.sid = b.sid AND a.sid = c.sid; SELECT d.sid,d.sname FROM (SELECT a.sid,a.sname FROM student a LEFT JOIN sc b ON a.sid = b.sid AND b.cid = 01 LEFT JOIN sc c ON a.sid = c.sid AND c.cid = 02 WHERE b.score > c.score) d;

9、查询所有课程成绩小于60分的同学的学号、姓名；（这里我不太清楚，所有课程成绩是按照全部5门课程来计算，还是按照每个同学所学的课程来计算，所以在下面给出了两种题目的结果）

SELECT a.sid,a.sname,COUNT(a.sname) AS `不及格数` FROM student a LEFT JOIN sc b ON a.sid = b.sid WHERE b.score < 60 GROUP BY a.sid,a.sname HAVING `不及格数` = (SELECT COUNT(*) FROM course); SELECT a.sid,a.sname FROM student a,(SELECT COUNT(*) AS `课程总数` FROM course) b, (SELECT sid,COUNT(*) AS `不及格数` FROM sc WHERE score < 60 GROUP BY sid) c WHERE a.sid IN (SELECT sid FROM sc WHERE score<60 ) AND b.`课程总数` = c.`不及格数` AND a.sid = c.sid; 这地方容易Operand should contain 1 column(s) SELECT sid,sname FROM student WHERE sid IN (SELECT Count(DISTINCT cid) FROM sc WHERE score<60 GROUP BY sid HAVING COUNT(DISTINCT cid) = (SELECT SUM(DISTINCT cid) FROM course));

– 这地方容易Subquery returns more than 1 row(外层查询结果是要求COUNT(DISTINCT cid)为一条数据)
– SELECT a.sid,a.sname,COUNT(DISTINCT c.cid) AS 总课程数,(SELECT COUNT(DISTINCT cid) FROM sc WHERE score < 60 GROUP BY sid) AS 不及格数
– FROM student a
– LEFT JOIN sc b ON a.sid = b.sid
– LEFT JOIN course c ON b.cid = c.cid
– GROUP BY a.sid,a.sname

讯享网SELECT sid,sname FROM student WHERE sid IN (SELECT Count(DISTINCT cid) FROM sc WHERE score<60 GROUP BY sid HAVING COUNT(DISTINCT cid) = (SELECT SUM(DISTINCT cid) FROM course));

还有CASE WHEN 方法和求差集

按照每个同学所学的课程来计算

SELECT e.sid,e.sname,COUNT(DISTINCT e.cid) AS `总课程数`,d.`不及格数` FROM (SELECT a.sid,a.sname,b.cid FROM student a LEFT JOIN sc b ON a.sid = b.sid LEFT JOIN course c ON b.cid = c.cid ) e,(SELECT sid,COUNT(*) AS `不及格数` FROM sc WHERE score < 60 GROUP BY sid) d WHERE e.sid = d.sid GROUP BY e.sid,e.sname,d.`不及格数` HAVING d.`不及格数` = `总课程数`; SELECT sid,sname FROM student WHERE sid NOT IN (SELECT sid FROM sc WHERE score>60); SELECT a.sid,a.sname,d.`不及格数` FROM student a LEFT JOIN sc b ON a.sid = b.sid LEFT JOIN course c ON b.cid = c.cid LEFT JOIN (SELECT sid,COUNT(DISTINCT cid) AS `不及格数` FROM sc WHERE score < 60 GROUP BY sid) d ON d.sid = a.sid LEFT JOIN (SELECT sid,COUNT(DISTINCT cid) AS `总课程数` FROM sc GROUP BY sid) e ON a.sid = e.sid WHERE d.`不及格数` = e.`总课程数` GROUP BY sid; SELECT a.sid,a.sname,e.`总课程数`,d.`不及格数` FROM student a LEFT JOIN sc b ON a.sid = b.sid LEFT JOIN course c ON b.cid = c.cid LEFT JOIN (SELECT sid,COUNT(DISTINCT cid) AS `不及格数` FROM sc WHERE score < 60 GROUP BY sid) d ON d.sid = a.sid LEFT JOIN (SELECT sid,COUNT(DISTINCT cid) AS `总课程数` FROM sc GROUP BY sid) e ON e.sid = a.sid GROUP BY a.sid,a.sname HAVING e.`总课程数` = d.不及格数; SELECT a.sid,a.sname FROM (SELECT sid,sname FROM student )a LEFT JOIN (SELECT * FROM sc )b ON a.sid = b.sid LEFT JOIN (SELECT cid FROM course )c ON b.cid = c.cid LEFT JOIN (SELECT sid,COUNT(DISTINCT cid) AS `及格数` FROM sc WHERE score > 60 GROUP BY sid) d ON d.sid = a.sid LEFT JOIN (SELECT sid,COUNT(DISTINCT cid) AS `总课程数` FROM sc GROUP BY sid) e ON a.sid = e.sid WHERE d.`及格数` = e.`总课程数` GROUP BY a.sid;

还可以将所有的SELECT的子表当作一个大表进行查询，下面方法同理

– SELECT sid,sname
– FROM student
– WHERE sid IN
– (SELECT sid FROM sc WHERE score<60)
– AND (SELECT sid,COUNT(DISTINCT cid) FROM sc a GROUP BY a.sid)

今天在sql测试的时候发现了这个错误：Operand should contain 1 column(s)。
原因是in条件后面有多个字段，in后面只能有一个字段。
– SELECT sid,sname
– FROM student
– WHERE sid IN
– (SELECT sid,Count(DISTINCT cid) FROM sc WHERE score<60 GROUP BY sid HAVING COUNT(DISTINCT cid) = (SELECT COUNT(DISTINCT cid) FROM course))
– AND (SELECT sid,COUNT(DISTINCT cid) FROM sc a GROUP BY a.sid) ;

10、查询所有课程成绩大于60分的同学的学号、姓名；思路同上题

讯享网 SELECT a.sid,a.sname,COUNT(a.sname) AS `全部及格数` FROM student a LEFT JOIN sc b ON a.sid = b.sid WHERE b.score > 60 GROUP BY a.sid,a.sname HAVING `全部及格数` = (SELECT COUNT(*) FROM course); SELECT a.sid,a.sname FROM student a,(SELECT COUNT(*) AS `课程总数` FROM course) b, (SELECT sid,COUNT(*) AS `全部及格数` FROM sc WHERE score > 60 GROUP BY sid) c WHERE a.sid IN (SELECT sid FROM sc WHERE score>60 ) AND b.`课程总数` = c.`全部及格数` AND a.sid = c.sid; 这地方容易Operand should contain 1 column(s) SELECT sid,sname FROM student WHERE sid IN (SELECT Count(DISTINCT cid) FROM sc WHERE score>60 GROUP BY sid HAVING COUNT(DISTINCT cid) = (SELECT SUM(DISTINCT cid) FROM course));

按照每个同学所学的课程来计算
SELECT e.sid,e.sname,COUNT(DISTINCT e.cid) AS 总课程数,d.及格数
FROM (SELECT a.sid,a.sname,b.cid FROM student a
LEFT JOIN sc b ON a.sid = b.sid
LEFT JOIN course c ON b.cid = c.cid
) e,(SELECT sid,COUNT(*) AS 及格数 FROM sc WHERE score > 60 GROUP BY sid) d
WHERE e.sid = d.sid
GROUP BY e.sid,e.sname,d.及格数
HAVING d.及格数 = 总课程数;

SELECT sid,sname FROM student WHERE sid NOT IN (SELECT sid FROM sc WHERE score<60); SELECT a.sid,a.sname,e.`总课程数`,d.`及格数` FROM student a LEFT JOIN sc b ON a.sid = b.sid LEFT JOIN course c ON b.cid = c.cid LEFT JOIN (SELECT sid,COUNT(DISTINCT cid) AS `及格数` FROM sc WHERE score > 60 GROUP BY sid) d ON d.sid = a.sid LEFT JOIN (SELECT sid,COUNT(DISTINCT cid) AS `总课程数` FROM sc GROUP BY sid) e ON e.sid = a.sid GROUP BY a.sid,a.sname HAVING e.`总课程数` = d.及格数; SELECT a.sid,a.sname,d.`及格数` FROM student a LEFT JOIN sc b ON a.sid = b.sid LEFT JOIN course c ON b.cid = c.cid LEFT JOIN (SELECT sid,COUNT(DISTINCT cid) AS `及格数` FROM sc WHERE score > 60 GROUP BY sid) d ON d.sid = a.sid LEFT JOIN (SELECT sid,COUNT(DISTINCT cid) AS `总课程数` FROM sc GROUP BY sid) e ON a.sid = e.sid WHERE d.`及格数` = e.`总课程数` GROUP BY a.sid ; SELECT a.sid,a.sname FROM (SELECT sid,sname FROM student )a LEFT JOIN (SELECT * FROM sc )b ON a.sid = b.sid LEFT JOIN (SELECT cid FROM course )c ON b.cid = c.cid LEFT JOIN (SELECT sid,COUNT(DISTINCT cid) AS `及格数` FROM sc WHERE score > 60 GROUP BY sid) d ON d.sid = a.sid LEFT JOIN (SELECT sid,COUNT(DISTINCT cid) AS `总课程数` FROM sc GROUP BY sid) e ON a.sid = e.sid WHERE d.`及格数` = e.`总课程数` GROUP BY a.sid;

11、查询没有学全所有课的同学的学号、姓名；

讯享网SELECT a.sid,a.sname,COUNT(DISTINCT b.cid) AS `课程总数` FROM student a LEFT JOIN sc b ON a.sid = b.sid GROUP BY a.sid,a.sname HAVING `课程总数`< (SELECT COUNT(cid) FROM course); 这里刚开始按学过全部课程的做的，只能这么改了 SELECT sid,sname FROM student WHERE sid NOT IN (SELECT a.sid FROM student a LEFT JOIN sc b ON a.sid = b.sid RIGHT JOIN (SELECT cid,SUM(cid) AS `总数` FROM course GROUP BY cid ORDER BY `总数` DESC LIMIT 0,1) c ON c.cid = b.cid); SELECT sid, sname FROM student WHERE sid NOT IN (SELECT sid FROM sc, course AS c WHERE sc.cid = c.cid GROUP BY sc.sid HAVING COUNT(sc.cid) = (SELECT COUNT(cid) FROM course));

12、查询至少有一门课程与学号为1的同学所学课程相同的同学的学号和姓名；

SELECT a.sid,a.sname FROM student a LEFT JOIN sc b ON a.sid = b.sid WHERE cid IN (SELECT cid FROM sc WHERE sid = 01) AND a.sid !=01 GROUP BY a.sid,a.sname; SELECT a.sid,a.sname FROM student a LEFT JOIN sc b ON a.sid = b.sid LEFT JOIN sc c ON a.sid = c.sid WHERE c.cid = b.cid AND a.sid !=01 GROUP BY a.sid,a.sname; SELECT sid,sname FROM student WHERE sid IN (SELECT sid FROM sc WHERE cid IN (SELECT cid FROM sc WHERE cid = 01)) AND sid != 01; SELECT a.sid,a.sname FROM student a ,(SELECT cid FROM sc WHERE sid = 01)b , (SELECT cid FROM sc) c WHERE b.cid = c.cid AND a.sid !=01 GROUP BY a.sid,a.sname; SELECT a.sid,a.sname FROM student a LEFT JOIN (SELECT sid,cid FROM sc WHERE cid = 01 GROUP BY sid) b ON a.sid = b.sid WHERE a.sid != 01;

13、把“sc1”表中“刘老师”所教课的成绩都更改为此课程的平均成绩；
创建测试表

讯享网INSERT INTO sc1 SELECT * FROM sc; SHOW CREATE TABLE sc1; SELECT * FROM sc1; -- mysql出现“ You can't specify target table '表名' for update in FROM clause”解决方法 -- 不能先select出同一表中的某些值，再update这个表(在同一语句中) UPDATE sc1 SET score = (SELECT AVG(score) FROM sc GROUP BY cid HAVING cid IN (SELECT cid FROM course WHERE tid IN (SELECT tid FROM teacher WHERE tname ='刘老师'))) WHERE cid IN (SELECT cid FROM course WHERE tid IN (SELECT tid FROM teacher WHERE tname = '刘老师')); UPDATE sc1 SET score = (SELECT AVG(score) FROM sc GROUP BY cid HAVING cid IN(SELECT cid FROM course a,teacher b WHERE a.tid = b.tid AND b.tname = '刘老师')) WHERE cid IN (SELECT cid FROM course c ,teacher d WHERE c.tid = d.tid AND tname = '刘老师'); UPDATE sc1 SET score = (SELECT AVG(score) FROM sc a LEFT JOIN course b ON a.cid = b.cid LEFT JOIN teacher c ON b.tid = c.tid WHERE tname = '刘老师') WHERE cid IN (SELECT cid FROM course d LEFT JOIN teacher e ON d.tid = e.tid WHERE tname ='刘老师'); 还原sc1表的数据 TRUNCATE TABLE sc1; INSERT INTO sc1 SELECT * FROM sc;

14、查询和2号同学学习的课程完全相同的其他同学学号和姓名；（这道题大家有好的想法可以告诉我）
课程完全相同 转化为 课程号的总和、均值完全相同 从而可以 使用聚合函数

SELECT b.sid, b.sname FROM sc a, student b WHERE a.sid = b.sid AND a.sid != 2 GROUP BY b.sid HAVING SUM(a.cid) = (SELECT SUM(cid) FROM sc WHERE sid = 2) AND AVG(a.cid) = (SELECT AVG(cid) FROM sc WHERE sid = 2); SELECT s.sid,s.sname FROM student s WHERE s.sid IN ( SELECT a.`sid` FROM sc a WHERE a.sid != 2 GROUP BY a.`sid` HAVING COUNT(*) = (SELECT COUNT(DISTINCT b.`cid`) FROM sc b WHERE b.sid = 2) AND EXISTS (SELECT c.`cid` FROM sc c WHERE c.`sid` =2) )

– SELECT a.,b. FROM student a
– NATURAL JOIN sc b
– WHERE b.cid = ALL(SELECT cid FROM sc WHERE sid = 02);

15、删除学习“叶平”老师课的sc1表记录；

讯享网DELETE FROM sc1 WHERE cid = (SELECT cid FROM course WHERE tid IN (SELECT tid FROM teacher WHERE tname = '叶平')); TRUNCATE TABLE sc1; INSERT INTO sc1 SELECT * FROM sc; 这里过于简单就不再继续写其他方法了

16、向sc1表中插入一些记录，这些记录要求符合以下条件：
–将没有课程5成绩同学的该成绩补齐, 其成绩取所有学生的课程2的平均成绩；

INSERT INTO sc1 SELECT sid, 5, (SELECT AVG(score) FROM sc1 WHERE cid = 2) FROM student WHERE sid NOT IN (SELECT sid FROM sc WHERE cid = 5); SELECT * FROM sc1 TRUNCATE TABLE sc1; INSERT INTO sc1 SELECT * FROM sc;

17、按平均分从高到低显示所有学生的如下统计报表：
–学号,企业管理,马克思,UML,数据库,物理,课程数,平均分；
思路：CASE WHEN THEN ELSE END 的用法，在分组后，要提取每个组中的特定值（非聚合值）时用到 + CASE对分组中每一列都会运算，所以注意加上MAX，否则会输出NULL

讯享网SELECT sid AS 学号, MAX(CASE WHEN cid = 1 THEN score END) AS 企业管理, MAX(CASE WHEN cid = 2 THEN score END) AS 马克思, MAX(CASE WHEN cid = 3 THEN score END) AS UML, MAX(CASE WHEN cid = 4 THEN score END) AS 数据库, MAX(CASE WHEN cid = 5 THEN score END) AS 物理, COUNT(cid) AS 课程数, AVG(score) AS 平均分 FROM sc GROUP BY sid ORDER BY AVG(score) DESC;

18、查询各科成绩最高分和最低分：以如下形式显示：课程号，最高分，最低分；

SELECT cid,MAX(score) AS `最高分`,MIN(score) AS `最低分` FROM sc GROUP BY cid 19、查询课程号、课程名、各科平均成绩、及格率，按各科平均成绩从低到高和及格率的百分数从高到低顺序； SELECT a.cid,b.cname,IFNULL(AVG(score),0) AS `平均成绩`, 100*(SUM(CASE WHEN score>=60 THEN 1 ELSE 0 END)/COUNT(score)) AS `及格率` FROM sc a,course b WHERE a.cid = b.cid GROUP BY a.cid ORDER BY `平均成绩` ASC,`及格率` DESC;

20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（001），马克思（002），UML （003），数据库（004）；


讯享网

讯享网SELECT AVG(CASE WHEN cid = 1 THEN score END) AS 平均成绩1, AVG(CASE WHEN cid = 2 THEN score END) AS 平均成绩2, AVG(CASE WHEN cid = 3 THEN score END) AS 平均成绩3, AVG(CASE WHEN cid = 4 THEN score END) AS 平均成绩4, 100 * SUM(CASE WHEN cid = 1 AND score >= 60 THEN 1 ELSE 0 END) / SUM(CASE WHEN cid = 1 THEN 1 ELSE 0 END) AS 及格率1, 100 * SUM(CASE WHEN cid = 2 AND score >= 60 THEN 1 ELSE 0 END) / SUM(CASE WHEN cid = 2 THEN 1 ELSE 0 END) AS 及格率2, 100 * SUM(CASE WHEN cid = 3 AND score >= 60 THEN 1 ELSE 0 END) / SUM(CASE WHEN cid = 3 THEN 1 ELSE 0 END) AS 及格率3, 100 * SUM(CASE WHEN cid = 4 AND score >= 60 THEN 1 ELSE 0 END) / SUM(CASE WHEN cid = 4 THEN 1 ELSE 0 END) AS 及格率4 FROM sc;

21、查询不同老师所教不同课程平均分, 从高到低显示
–张老师 数据库 88；

SELECT c.tname,b.cname,AVG(a.score) AS `平均分` FROM sc a,course b,teacher c WHERE a.cid = b.cid AND b.tid = c.tid GROUP BY c.tname ORDER BY `平均分` DESC;

22、查询如下课程成绩均在第3名到第6名之间的学生的成绩：
–[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩；

讯享网SELECT a.sid AS 学生ID, b.sname AS 学生姓名, MAX(CASE WHEN a.cid = 1 THEN score END) AS 企业管理, MAX(CASE WHEN a.cid = 2 THEN score END) AS 马克思, MAX(CASE WHEN a.cid = 3 THEN score END) AS UML, MAX(CASE WHEN a.cid = 4 THEN score END) AS 数据库, AVG(score) AS 平均成绩 FROM sc a, student b WHERE a.sid = b.sid AND a.sid IN (SELECT sid FROM (SELECT sid FROM sc WHERE cid = 01 ORDER BY score DESC LIMIT 2,4) AS c1) AND a.sid IN (SELECT sid FROM (SELECT sid FROM sc WHERE cid = 02 ORDER BY score DESC LIMIT 2,4) AS c1) AND a.sid IN (SELECT sid FROM (SELECT sid FROM sc WHERE cid = 03 ORDER BY score DESC LIMIT 2,4) AS c1) AND a.sid IN (SELECT sid FROM (SELECT sid FROM sc WHERE cid = 04 ORDER BY score DESC LIMIT 2,4) AS c1) GROUP BY a.sid,b.sname;

23、统计打印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]；

SELECT a.cid,b.cname,MAX(CASE WHEN a.cid = 01 THEN score ELSE 0 END) AS 企业管理, MAX(CASE WHEN a.cid = 2 THEN score ELSE 0 END) AS 马克思, MAX(CASE WHEN a.cid = 3 THEN score ELSE 0 END) AS UML, MAX(CASE WHEN a.cid = 4 THEN score ELSE 0 END) AS 数据库, MAX(CASE WHEN a.cid = 5 THEN score ELSE 0 END) AS 物理, SUM(CASE WHEN score > 85 AND score <=100 THEN 1 ELSE 0 END) AS `[100-85]`, SUM(CASE WHEN score > 70 AND score <=85 THEN 1 ELSE 0 END) AS `[85-70]`, SUM(CASE WHEN score > 60 AND score <=70 THEN 1 ELSE 0 END) AS `[70-60]`, SUM(CASE WHEN score <=60 THEN 1 ELSE 0 END) AS `[<60]` FROM sc a,course b WHERE a.cid = b.cid GROUP BY a.cid;

24、查询学生平均分及其名次；

讯享网SELECT a.sid,@i:=@i+1 AS `不保留空缺排名`, @k:=(CASE WHEN @avg_score = a.avg_s THEN @k ELSE @i END)AS `保留空缺排名`, @avg_score := avg_s AS `平均分` FROM (SELECT sid,ROUND(AVG(score),2) AS avg_s FROM sc GROUP BY sid ORDER BY avg_s DESC)a, (SELECT @avg_score:=0,@i:=0,@k:=0)b;

25、查询各科成绩前三名的记录:
– SELECT sid,cid,score
– FROM sc a,-- SELECT sid,cid,score
– FROM sc a,
– (SELECT cid,COUNT(cid) FROM sc ) c
– WHERE a.cid = c.cid
– (SELECT cid,COUNT(cid) FROM sc ) c
– WHERE a.cid = c.cid;

这个不太对，学号为01时只查出了两名同学
SELECT a.sid,a.cid,a.score
FROM sc a
WHERE (SELECT COUNT(1) FROM sc b WHERE a.cid = b.cid AND b.score >= a.score)<4
ORDER BY a.cid ASC ,a.score DESC;

没看出来哪里错了,结果只能查出来前三门课程的成绩，不是因为最后一门课程只有一名学生选修
SELECT b.sid,b.cid,b.score,b.排名
FROM (SELECT a.sid,a.cid,a.score,@i:=@i+1 AS 排名 FROM sc a,(SELECT @i:=0) c
WHERE a.cid = 01
ORDER BY a.score DESC) b
WHERE 排名 BETWEEN 1 AND 3
UNION
SELECT b.sid,b.cid,b.score,b.排名
FROM (SELECT a.sid,a.cid,a.score,@j:=@j+1 AS 排名 FROM sc a,(SELECT @j:=0) c
WHERE a.cid = 02
ORDER BY a.score DESC) b
WHERE 排名 BETWEEN 1 AND 3
UNION
SELECT b.sid,b.cid,b.score,b.排名
FROM (SELECT a.sid,a.cid,a.score,@k:=@k+1 AS 排名 FROM sc a,(SELECT @k:=0) c
WHERE a.cid = 03
ORDER BY a.score DESC) b
WHERE 排名 BETWEEN 1 AND 3
UNION
SELECT b.sid,b.cid,b.score,b.排名
FROM (SELECT a.sid,a.cid,a.score,@i:=@i+1 AS 排名 FROM sc a,(SELECT @i:=0) c
WHERE a.cid = 04
ORDER BY a.score DESC) b
WHERE 排名 BETWEEN 1 AND 3
UNION
SELECT b.sid,b.cid,b.score,b.排名
FROM (SELECT a.sid,a.cid,a.score,@j:=@j+1 AS 排名 FROM sc a,(SELECT @j:=0) c
WHERE a.cid = 05
ORDER BY a.score DESC) b
WHERE 排名 BETWEEN 1 AND 3;

SELECT a.cid,a.sid,a.score,count(a.cid) FROM sc a LEFT JOIN sc b ON a.cid=b.cid AND a.score>=b.score GROUP BY a.cid,a.sid,a.score HAVING count(a.cid)>=5 OR count(a.cid) =1 ORDER BY a.cid,a.score DESC; SELECT a.* FROM sc a LEFT JOIN sc b ON a.cid=b.cid AND a.score<b.score GROUP BY a.sid,a.cid,a.score HAVING COUNT(b.cid)< 3 ORDER BY a.cid,a.score DESC;

26、查询每门课程被选修的学生数；

讯享网SELECT cid,COUNT(sid) FROM sc GROUP BY cid;

27、查询出只选修了一门课程的全部学生的学号和姓名；

SELECT a.sid,a.sname,COUNT(b.cid) AS `选修课程数` FROM student a,sc b WHERE a.sid = b.sid GROUP BY a.sid,a.sname HAVING `选修课程数` = 1; SELECT a.sid,a.sname,COUNT(b.cid) AS `选修课程数` FROM student a LEFT JOIN sc b ON a.sid = b.sid GROUP BY a.sid,a.sname HAVING `选修课程数` = 1; SELECT a.sid,a.sname FROM student a LEFT JOIN sc b ON a.sid = b.sid LEFT JOIN (SELECT sid,COUNT(cid) AS `选修课程数` FROM sc GROUP BY sid) c ON a.sid = c.sid WHERE c.`选修课程数`=1 GROUP BY a.sid,a.sname;

28、查询男生、女生人数；

讯享网SELECT ssex,COUNT(sid) AS `人数` FROM student GROUP BY ssex; SELECT SUM(CASE WHEN ssex = '男' THEN 1 ELSE 0 END) AS `男生人数`, SUM(CASE WHEN ssex = '女' THEN 1 ELSE 0 END) AS `女生人数` FROM student; SELECT a.ssex,COUNT(a.ssex) AS 人数 FROM student a LEFT JOIN student b ON a.Ssex = b.Ssex AND a.sid = b.sid GROUP BY a.ssex;

29、查询姓“张”的学生名单；

SELECT * FROM student WHERE sname LIKE '张%';

30、查询同名同性学生名单，并统计同名人数；

讯享网SELECT a.sid,a.sname,COUNT(a.sid) FROM student a LEFT JOIN student b ON a.sid !=b.sid WHERE a.sname = b.sname GROUP BY a.sid,a.sname; SELECT sname, COUNT(sname) FROM student GROUP BY sname HAVING COUNT(sname) > 1;

31、1981年出生的学生名单(注：student表中sage列的类型是datetime)；

SELECT * FROM student WHERE YEAR(sage) = '1981'; SELECT * FROM student WHERE (DATE_FORMAT(sage,'%Y') ='1981');

32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；

讯享网SELECT cid,ROUND(AVG(score),2) AS `平均成绩` FROM sc GROUP BY cid ORDER BY `平均成绩` ASC,cid DESC;

33、查询平均成绩大于80的所有学生的学号、姓名和平均成绩；

SELECT a.sid,a.sname,ROUND(AVG(b.score),2) AS `平均成绩` FROM student a LEFT JOIN sc b ON a.sid = b.sid GROUP BY a.sid,a.sname HAVING `平均成绩` > 80; SELECT a.sid, a.sname, AVG(b.score) AS `平均成绩` FROM student a,sc b WHERE a.sid = b.sid GROUP BY a.sid,a.sname HAVING `平均成绩`>80; SELECT sid,sname FROM student WHERE sid IN (SELECT sid FROM sc GROUP BY sid HAVING AVG(score) > 80); SELECT * FROM student a, (SELECT sid,AVG(score) AS `平均成绩` FROM sc GROUP BY sid) b WHERE a.sid = b.sid AND `平均成绩` >80;

34、查询 数据库 分数 低于60的学生姓名和分数；

讯享网SELECT sid,sname FROM student WHERE sid IN (SELECT sid FROM sc WHERE score < 60 AND cid in (SELECT cid FROM course WHERE cname = '数据库')); SELECT a.sid,a.sname FROM student a LEFT JOIN sc b ON a.sid = b.sid LEFT JOIN course c ON b.cid = c.cid WHERE b.score < 60 AND c.cname = '数据库';

35、查询所有学生的选课情况；

SELECT a.sid,a.sname,b.cid,c.cname FROM student a LEFT JOIN sc b ON a.sid = b.sid LEFT JOIN course c ON b.cid = c.cid ORDER BY a.sid ASC; SELECT sc.sid 学号,sname 姓名,cname 课程, sc.cid 课号 FROM sc,student,course WHERE sc.sid=student.sid and sc.cid=course.cid ORDER BY sc.sid;

36、查询成绩在70分以上的学生姓名、课程名称和分数；

讯享网SELECT a.sname,c.cname,b.score FROM student a LEFT JOIN sc b ON a.sid = b.sid LEFT JOIN course c ON b.cid = c.cid WHERE b.score > 70; SELECT a.sname,c.cname, b.score FROM student a,sc b,course c WHERE a.sid=b.sid and b.cid=c.cid AND b.score>70;

37、查询不及格的课程，并按课程号从大到小排列；

SELECT cid FROM sc WHERE score < 60 GROUP BY cid ORDER BY cid DESC;

38、查询课程编号为3且课程成绩在80分以上的学生的学号和姓名；

讯享网SELECT a.sid,a.sname FROM student a LEFT JOIN sc b ON a.sid = b.sid WHERE b.cid = 03 AND b.score >80; SELECT a.sid,a.sname FROM student a,sc b WHERE a.sid=b.sid AND b.score>80 AND b.cid = 03;

39、求选了课程的学生人数；

SELECT COUNT(DISTINCT sid) FROM sc;

40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩；

讯享网SELECT e.* FROM (SELECT * FROM student a NATURAL JOIN sc b NATURAL JOIN course c NATURAL JOIN teacher d ) e WHERE e.tname = '叶平' ORDER BY e.score DESC LIMIT 0,1; SELECT e.* FROM (SELECT * FROM student a NATURAL JOIN sc b NATURAL JOIN course c NATURAL JOIN teacher d ) e WHERE e.tname = '叶平' AND e.score >= (SELECT MAX(f.score) FROM (SELECT * FROM student a NATURAL JOIN sc b NATURAL JOIN course c NATURAL JOIN teacher d ) f WHERE f.tname = '叶平'); SELECT e.* FROM (SELECT * FROM student a NATURAL JOIN sc b NATURAL JOIN course c NATURAL JOIN teacher d ) e WHERE e.tname = '叶平' AND e.score >= ALL(SELECT f.score FROM (SELECT * FROM student a NATURAL JOIN sc b NATURAL JOIN course c NATURAL JOIN teacher d ) f WHERE f.tname = '叶平'); SELECT * FROM student a,sc b,course c,teacher d WHERE a.sid=b.sid AND b.cid=c.cid AND c.tid=d.tid AND d.tname ='叶平' AND b.score=(SELECT max(b.score) FROM sc WHERE b.cid = c.cid);

41、查询各个课程及相应的选修人数；

SELECT cid,COUNT(sid) AS `选修人数` FROM sc GROUP BY cid;

42、查询不同课程成绩相同的学生的学号、课程号、学生成绩；

讯享网SELECT a.sid,a.cid,a.score FROM sc a LEFT JOIN sc b ON a.sid = b.sid WHERE a.cid != b.cid AND a.score = b.score; SELECT DISTINCT b.sid,b.cid,b.score FROM sc a,sc b WHERE a.cid != b.cid AND a.score = b.score AND a.sid = b.sid;

43、查询每门课程成绩最好的前两名的学生ID；

SELECT a.sid,a.cid,a.score FROM sc a WHERE (SELECT COUNT(1) FROM sc b WHERE a.cid = b.cid AND b.score >= a.score)<=2 ORDER BY a.cid ASC ,a.score DESC;

没看出来哪里错了,结果只能查出来前三门课程的成绩，不是因为最后一门课程只有一名学生选修
SELECT b.sid,b.cid,b.score,b.排名
FROM (SELECT a.sid,a.cid,a.score,@i:=@i+1 AS 排名 FROM sc a,(SELECT @i:=0) c
WHERE a.cid = 01
ORDER BY a.score DESC) b
WHERE 排名 BETWEEN 1 AND 2
UNION
SELECT b.sid,b.cid,b.score,b.排名
FROM (SELECT a.sid,a.cid,a.score,@j:=@j+1 AS 排名 FROM sc a,(SELECT @j:=0) c
WHERE a.cid = 02
ORDER BY a.score DESC) b
WHERE 排名 BETWEEN 1 AND 2
UNION
SELECT b.sid,b.cid,b.score,b.排名
FROM (SELECT a.sid,a.cid,a.score,@k:=@k+1 AS 排名 FROM sc a,(SELECT @k:=0) c
WHERE a.cid = 03
ORDER BY a.score DESC) b
WHERE 排名 BETWEEN 1 AND 2
UNION
SELECT b.sid,b.cid,b.score,b.排名
FROM (SELECT a.sid,a.cid,a.score,@i:=@i+1 AS 排名 FROM sc a,(SELECT @i:=0) c
WHERE a.cid = 04
ORDER BY a.score DESC) b
WHERE 排名 BETWEEN 1 AND 2
UNION
SELECT b.sid,b.cid,b.score,b.排名
FROM (SELECT a.sid,a.cid,a.score,@j:=@j+1 AS 排名 FROM sc a,(SELECT @j:=0) c
WHERE a.cid = 05
ORDER BY a.score DESC) b
WHERE 排名 BETWEEN 1 AND 2;

讯享网SELECT a.cid,a.sid,a.score,count(a.cid) FROM sc a LEFT JOIN sc b ON a.cid=b.cid AND a.score>=b.score GROUP BY a.cid,a.sid,a.score HAVING count(a.cid)>6 OR count(a.cid) =1 ORDER BY a.cid,a.score DESC;

44、统计每门课程的学生选修人数(至少有2人选修的课程才统计)。要求输出课程号和选修人数，
–查询结果按人数降序排列，若人数相同，按课程号升序排列；

SELECT cid,COUNT(sid) AS `选修人数` FROM sc GROUP BY cid HAVING `选修人数`>=2 ORDER BY `选修人数` DESC,cid ASC; 45、检索至少选修了5门课程的学生学号； SELECT sid,COUNT(cid) AS `选修课程数` FROM sc GROUP BY sid HAVING `选修课程数`>=5; SELECT sid FROM sc GROUP BY sid HAVING count(1) >= 5; 46、查询全部学生都选修的课程的课程号和课程名； SELECT a.sid,a.sname,COUNT(b.cid) AS 选修课数 FROM student a LEFT JOIN sc b ON a.sid = b.sid LEFT JOIN course c ON b.cid = c.cid GROUP BY a.sid,a.sname HAVING 选修课数 IN (SELECT count(1) FROM course); SELECT a.* FROM student a WHERE a.sid IN (SELECT b.sid FROM sc b GROUP BY b.sid HAVING COUNT(*) = (SELECT COUNT(*) FROM course));

47、查询没学过“叶平”老师讲授的任一门课程的学生姓名；

讯享网SELECT sid, sname FROM student WHERE sid NOT IN( SELECT sid FROM sc WHERE cid IN (SELECT a.cid FROM course a, teacher b WHERE a.tid = b.tid AND b.tname = '叶平')); SELECT a.sid, a.sname FROM student a WHERE a.sid NOT IN (SELECT c.sid FROM sc b, student c, course d, teacher e WHERE b.sid = c.sid AND b.cid = d.cid AND d.tid = e.tid AND e.tname = '叶平'); SELECT a.sid,a.sname FROM student a LEFT JOIN sc b ON a.sid = b.sid LEFT JOIN course c ON b.cid = c.cid LEFT JOIN teacher d ON c.tid = d.tid GROUP BY a.sid HAVING a.sid NOT IN (SELECT sid FROM sc WHERE cid IN (SELECT cid FROM course WHERE tid IN (SELECT tid FROM teacher WHERE tname = '叶平')));

48、查询两门以上不及格课程的同学的学号及其平均成绩；

SELECT sid,AVG(score),COUNT(1) AS `不及格课程数` FROM sc WHERE score < 60 GROUP BY sid HAVING `不及格课程数`>2; SELECT sid,AVG(score), SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS `不及格课程数` FROM sc GROUP BY sid HAVING 不及格课程数>2;

49、检索4号课程分数大于60的同学学号，按分数降序排列；

讯享网SELECT sid FROM sc WHERE cid = 04 AND score < 60 ORDER BY score DESC;

50、删除2号同学的课程1的成绩；

DELETE FROM sc1 WHERE cid = 01 AND sid = 02; TRUNCATE TABLE sc1; INSERT INTO sc1 SELECT * FROM sc;

51、查询成绩最好的课程；

讯享网SELECT cid,AVG(score) AS `平均成绩` FROM sc GROUP BY cid ORDER BY `平均成绩` DESC LIMIT 0,1; 52、查询最受欢迎的老师(选修学生最多的老师)； SELECT b.cname,c.tname,COUNT(a.sid) AS `学生数` FROM sc a LEFT JOIN course b ON a.cid = b.cid LEFT JOIN teacher c ON b.tid = c.tid GROUP BY b.cname,c.tname ORDER BY `学生数` DESC LIMIT 0,1; SELECT b.cname,c.tname,COUNT(a.sid) AS `学生数` FROM sc a,course b, teacher c WHERE a.cid = b.cid AND b.tid = c.tid GROUP BY b.cname,c.tname ORDER BY `学生数` DESC LIMIT 0,1;

53、查询教学质量最好的老师；

SELECT c.tname, b.cname, AVG(score) AS `平均分` FROM sc a, course b, teacher c WHERE a.cid = b.cid AND b.tid = c.tid GROUP BY c.tname, b.cname ORDER BY `平均分` DESC LIMIT 0,1; SELECT c.tname, b.cname, AVG(score) AS `平均分` FROM sc a LEFT JOIN course b ON a.cid = b.cid LEFT JOIN teacher c ON b.tid = c.tid GROUP BY c.tname, b.cname ORDER BY `平均分` DESC LIMIT 0,1;

54、查询需要补考的各科学生清单；

讯享网SELECT c.cname,a.sid,a.sname,b.score FROM student a LEFT JOIN sc b ON a.sid = b.sid LEFT JOIN course c ON b.cid = c.cid WHERE b.score < 60; SELECT c.cname,a.sid,a.sname,b.score FROM student a,sc b,course c WHERE a.sid = b.sid AND c.cid = b.cid AND b.score <60;

总结：

编写查询语句思路
1、首先确定最终输出结果的列，包括几个方面：A、首先这些列来自于一个
表、还是多个表，如果是多个表则可能用到多表查询的（等值连接、不等值
连接、外连接、自连接）；B、这些列是直接的表的字段还是对表字段的函数
、运算、分组、查询（则用到列的子查询）；B、列是否需要别名
2、确定输出的列和列的条件是否来自多表，如果来自多表则用多表查询
3、确定输出的字段对应的条件是单纯的数值还是要通过子查询才能获得、如
果用用到子查询，则where 子句要用到子查询。
4、根据输出的结果和条件判断是否要用到group by（但凡是有分类、统计
分组、最大、最小、平均、每个等字眼，则用到group by）
5、在根据输出的条件中是否关心分组之后的处理，如过滤，条件限定，如果有
则要用到having （如果分组过滤后比较的是一个不确定的条件，则having中
要用到子查询）
6、最后再根据输出格式要求确定是否需要排序，如果需要，则用order by
如果对你有帮助别忘了点个赞哦

小讯
上一篇 2025-03-20 19:44
下一篇 2025-03-19 12:44

相关推荐

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请联系我们，一经查实，本站将立刻删除。
如需转载请保留出处：https://51itzy.com/kjqy/45466.html