CREATE DATABASE student;
USE student;
SELECT * FROM student;
-- 学生表
CREATE TABLE student(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20),
sage INT,
ssex CHAR(1)
);
INSERT INTO student (sname,sage,ssex) VALUES ('张三',18,0);
INSERT INTO student (sname,sage,ssex) VALUES ('李四',19,1);
INSERT INTO student (sname,sage,ssex) VALUES ('王二',20,0);
INSERT INTO student (sname,sage,ssex) VALUES ('麻子',21,1);
INSERT INTO student (sname,sage,ssex) VALUES ('刘一',21,1);
INSERT INTO student (sname,sage,ssex) VALUES ('陈二',22,0);
INSERT INTO student (sname,sage,ssex) VALUES ('赵三',19,0);
INSERT INTO student (sname,sage,ssex) VALUES ('王五',20,0);
DELETE FROM student WHERE sid=8;
-- 教师表
CREATE TABLE teacher(
tid INT PRIMARY KEY AUTO_INCREMENT,
tname VARCHAR(20)
);
INSERT INTO teacher (tname) VALUES ('李');
INSERT INTO teacher (tname) VALUES ('潘');
INSERT INTO teacher (tname) VALUES ('王');
-- 课程表
CREATE TABLE course(
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(20),
tid INT,
CONSTRAINT fk_course_teach FOREIGN KEY (tid) REFERENCES teacher(tid)
);
INSERT INTO course (cname,tid) VALUES ('语文',1);
INSERT INTO course (cname,tid) VALUES ('英语',1);
INSERT INTO course (cname,tid) VALUES ('数学',2);
INSERT INTO course (cname,tid) VALUES ('政治',2);
INSERT INTO course (cname,tid) VALUES ('历史',3);
-- 成绩表
CREATE TABLE score(
sid INT,
cid INT,
score INT,
CONSTRAINT fk_score_stu FOREIGN KEY (sid) REFERENCES student(sid),
CONSTRAINT fk_score_course FOREIGN KEY (cid) REFERENCES course(cid)
);
-- drop table score;
INSERT INTO score (sid,cid,score) VALUES (1,1,60);
INSERT INTO score (sid,cid,score) VALUES (1,2,65);
INSERT INTO score (sid,cid,score) VALUES (1,3,70);
INSERT INTO score (sid,cid,score) VALUES (1,4,20);
INSERT INTO score (sid,cid,score) VALUES (2,1,71);
INSERT INTO score (sid,cid,score) VALUES (2,2,63);
INSERT INTO score (sid,cid,score) VALUES (2,3,82);
INSERT INTO score (sid,cid,score) VALUES (2,4,64);
INSERT INTO score (sid,cid,score) VALUES (3,1,52);
INSERT INTO score (sid,cid,score) VALUES (3,2,90);
INSERT INTO score (sid,cid,score) VALUES (3,4,82);
INSERT INTO score (sid,cid,score) VALUES (3,5,61);
INSERT INTO score (sid,cid,score) VALUES (4,1,76);
INSERT INTO score (sid,cid,score) VALUES (4,2,85);
INSERT INTO score (sid,cid,score) VALUES (4,3,93);
INSERT INTO score (sid,cid,score) VALUES (4,4,53);
INSERT INTO score (sid,cid,score) VALUES (4,5,57);
INSERT INTO score (sid,cid,score) VALUES (5,3,41);
INSERT INTO score (sid,cid,score) VALUES (5,4,53);
INSERT INTO score (sid,cid,score) VALUES (5,5,55);
INSERT INTO score (sid,cid,score) VALUES (5,1,43);
INSERT INTO score (sid,cid,score) VALUES (5,2,56);
INSERT INTO score (sid,cid,score) VALUES (6,5,60);
INSERT INTO score (sid,cid,score) VALUES (7,1,60);
INSERT INTO score (sid,cid,score) VALUES (9,1,76);
SELECT * FROM score WHERE sid=4
-- delete from score where sid=5 and cid in (5)
-- 1.麻子的老师信息
-- <1>
SELECT * FROM teacher WHERE tid IN(
SELECT DISTINCT tid FROM course WHERE cid IN(SELECT cid FROM score WHERE sid = (
SELECT sid FROM student WHERE sname = '麻子'
)
)
);
-- 做的<2>
SELECT DISTINCT t.tid,t.tname FROM teacher t INNER JOIN course c ON t.tid=c.`tid`
INNER JOIN score sc ON sc.cid= c.cid INNER JOIN student s ON s.sid=sc.sid
WHERE s.sname='麻子'
-- <3>
SELECT DISTINCT t.`tid`,t.`tname` FROM
(SELECT sid,sname FROM student WHERE sname='麻子')AS s
INNER JOIN score sc ON sc.`sid`=s.`sid`
INNER JOIN course c ON c.`cid`=sc.`cid`
INNER JOIN teacher t ON t.`tid`=c.`tid`;
-- <4>
SELECT DISTINCT t.* FROM student s,score sc,course c,teacher t WHERE s.`sid`=sc.`sid` AND
c.`cid`=sc.`cid` AND t.`tid`= c.`tid` AND s.`sname`='麻子';
-- 2.求所有学生每个人的平均成绩大于60
-- 先求出每个人的平均成绩
-- <1>
SELECT sid,AVG(score) FROM score GROUP BY sid HAVING AVG(score)>=60
-- <2>
SELECT s.sid,s.sname,AVG(sc.score) FROM student s,score sc WHERE s.sid = sc.sid GROUP BY s.sid,s.`sname` HAVING AVG(sc.score)>=60
-- 3.查询所有同学的学号、姓名、选课数、总成绩
-- <1>
SELECT s.sid,s.sname,COUNT(sc.cid),SUM(sc.score) FROM student s,score sc WHERE s.sid = sc.sid GROUP BY s.sid,s.sname
-- <2>
SELECT s.sid,s.sname,COUNT(sc.cid),SUM(sc.score) FROM student s INNER JOIN score sc ON s.sid=sc.sid GROUP BY s.sid,s.sname;
-- 4.查询姓李的老师的个数
SELECT COUNT(*) FROM teacher WHERE tname LIKE '李%';
-- 5.查询没学过李老师课程的学生的姓名和编号
-- <1>
SELECT * FROM student WHERE sid NOT IN(
SELECT sid FROM score WHERE cid IN(
SELECT cid FROM course WHERE tid IN
(SELECT tid FROM teacher WHERE tname='李')
)
)
-- <2>
SELECT * FROM student s WHERE s.sid NOT IN(
SELECT DISTINCT sc.cid FROM
(SELECT tid FROM teacher WHERE tname LIKE '李%')t
INNER JOIN course c ON c.tid=t.tid
INNER JOIN score sc ON sc.cid=c.cid
)
-- <3>
SELECT * FROM student WHERE sid NOT IN(
SELECT sc.`sid` FROM
(SELECT tid FROM teacher WHERE tname LIKE '李%')t
,course c,score sc,student s WHERE t.tid=c.tid AND
c.cid = sc.cid AND sc.sid=s.sid
)
-- 6.查询“某1”课程比“某2”课程成绩高的所有学生的学号;
SELECT sid,sname FROM student WHERE sid IN(
SELECT a.sid FROM
(SELECT sc.sid,sc.score FROM score sc,course c WHERE (c.cid=sc.cid) AND c.cname='语文' ) AS a,
(SELECT sc.sid,sc.score FROM score sc,course c WHERE (c.cid=sc.cid) AND c.cname='英语' ) AS b
WHERE a.sid=b.sid AND a.score>b.score
)
SELECT sid,sname FROM student WHERE sid IN
(
SELECT a.sid FROM
(SELECT s.sid,s.score FROM score s RIGHT JOIN course c ON (s.cid=c.cid) WHERE c.cname='语文') AS a,
(SELECT s.sid,s.score FROM score s RIGHT JOIN course c ON (s.cid=c.cid) WHERE c.cname='英语') AS b
WHERE a.sid=b.sid AND a.score>b.score
)
-- 7.查询学过“```”并且也学过编号“```”课程的同学的学号、姓名;
SELECT a.sid,a.sname FROM
(SELECT s.sname,s.sid FROM student s,course c,score sc WHERE s.sid=sc.sid AND sc.cid=c.cid AND c.cname='语文')AS a,
(SELECT s.sname,s.sid FROM student s,course c,score sc WHERE s.sid=sc.sid AND sc.cid=c.cid AND c.cname='英语')AS b
WHERE a.sid=b.sid
SELECT a.sid,a.sname FROM
(SELECT s.sname,s.sid FROM student s,course c,score sc
WHERE cname='语文'AND sc.sid=s.sid AND sc.cid=c.cid) a,
(SELECT s.sname,s.sid FROM student s,course c,score sc
WHERE cname='英语'AND sc.sid=s.sid AND sc.cid=c.cid) b
WHERE a.sid=b.sid;
-- 这个没懂
SELECT student.sid,student.sname
FROM
student,score sc
WHERE
student.sid=sc.sid -- AND sc.cid='1'
AND NOT EXISTS
( SELECT * FROM score WHERE 1=1
AND score.sid=sc.sid
AND score.cid=2);
-- 7.查询学过“李”老师所教的所有课的同学的学号、姓名;
SELECT sid,sname FROM student WHERE sid IN(
SELECT sc.sid FROM course c,score sc,teacher t WHERE c.`tid`=t.`tid` AND c.`cid`=sc.`cid` AND t.`tname`='李' GROUP BY sc.`sid` HAVING COUNT(sc.`cid`)=(SELECT COUNT(c.`cid`) FROM teacher t,course c WHERE t.`tid`=c.`tid` AND t.`tname`='李' )
)
SELECT sid,sname FROM student WHERE sid IN
(
SELECT sid
-- ,COUNT(score.cid)
FROM score,course,teacher WHERE
score.cid=course.cid AND teacher.tid=course.tid AND
teacher.tname='李'
GROUP BY sid
HAVING COUNT(score.cid)=
(
SELECT COUNT(cid) FROM course,teacher
WHERE teacher.tid=course.tid AND tname='李'
)
)
-- 1.有哪些列
-- 2.用到哪些表
-- 3.条件是什么
-- 8.查询课程编号“”的成绩比课程编号“”课程低的所有同学的学号、姓名;
-- 13.查询和“1”号的同学学习的课程完全相同的其他同学学号和姓名;
SELECT stu.sid,stu.sname,COUNT(s.cid) num FROM student stu
INNER JOIN score s ON stu.sid = s.sid
AND s.cid IN
(SELECT sc.cid FROM score sc WHERE sc.sid = 1)
GROUP BY stu.sid,stu.sname
HAVING COUNT(s.cid) =
(
SELECT COUNT(1) num FROM score sc WHERE sc.sid = 1
)
-- 14.删除学习“李”老师课的score表记录;
DELETE FROM score WHERE cid IN
(
SELECT c.cid FROM teacher t,course c WHERE t.`tid`=c.`tid` AND t.`tname`='李'用
)
-- 16.按平均成绩从高到低显示所有学生的“语文”、“数学”、“英语”三门的课程成绩;
SELECT sid,语文,英语,政治 FROM
(
SELECT sc.sid,
MAX(CASE sc.cid WHEN 1 THEN sc.score ELSE 0 END) '语文',
MAX(CASE sc.cid WHEN 2 THEN sc.score ELSE 0 END) '英语',
MAX(CASE sc.cid WHEN 4 THEN sc.score ELSE 0 END) '政治',
CONVERT(AVG(sc.score),SIGNED) AS score
FROM student s LEFT JOIN score sc ON (sc.sid=s.sid) WHERE sc.cid IN
(SELECT cid FROM course WHERE cname IN('语文','英语','政治'))
GROUP BY sc.sid
)AS a ORDER BY score DESC;
SELECT sid,语文,英语,政治 FROM
(
SELECT sc.sid,
MAX(CASE sc.cid WHEN 1 THEN sc.score ELSE 0 END) '语文',
MAX(CASE sc.cid WHEN 2 THEN sc.score ELSE 0 END) '英语',
MAX(CASE sc.cid WHEN 4 THEN sc.score ELSE 0 END) '政治',
CONVERT(AVG(score),SIGNED) score
FROM student s LEFT JOIN score sc ON (s.sid=sc.sid) WHERE cid IN
(SELECT cid FROM course WHERE cname IN ('语文','英语','政治'))
GROUP BY sc.sid
) AS a ORDER BY score DESC
-- 17.按如下形式显示:学生ID,语文,英语,数学,政治,历史,有效课程数,有效平均分;
SELECT sid,语文,数学,英语,政治,历史,num,score FROM
(
SELECT sc.sid,COUNT(sc.cid) num,
MAX(CASE sc.cid WHEN 1 THEN sc.score ELSE 0 END) '语文',
MAX(CASE sc.cid WHEN 2 THEN sc.score ELSE 0 END) '英语',
MAX(CASE sc.cid WHEN 3 THEN sc.score ELSE 0 END) '数学',
MAX(CASE sc.cid WHEN 4 THEN sc.score ELSE 0 END) '政治',
MAX(CASE sc.cid WHEN 5 THEN sc.score ELSE 0 END) '历史',
CONVERT(AVG(score),SIGNED) score
FROM student s LEFT JOIN score sc ON (s.sid=sc.sid) WHERE cid IN
(SELECT cid FROM course WHERE cname IN ('语文','英语','政治','数学','历史'))
GROUP BY sc.sid
) AS a ORDER BY score DESC
-- 18.查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分;
SELECT cid,AVG(score),MAX(score),MIN(score) FROM score GROUP BY cid;
-- 19.按各科平均成绩从低到高和及格率的百分数从高到低顺序???
SELECT * FROM
(
SELECT cid,AVG(score) score FROM score GROUP BY cid
) AS a ORDER BY score ASC
-- 每科及格人数,每科总人数
SELECT * FROM
(
SELECT a.cid,jg,zs,CONVERT((jg/zs)*100,SIGNED) 'jgl' FROM
(SELECT cid,COUNT(*) 'jg' FROM score WHERE score>60 GROUP BY cid) a,
(SELECT cid,COUNT(*) 'zs' FROM score GROUP BY cid) b
WHERE a.cid=b.cid
) AS c ORDER BY jgl DESC
SELECT cid,AVG(score) a FROM score GROUP BY cid ORDER BY a ASC
SELECT * FROM
(
SELECT a.cid,jg,zs,CONVERT((jg/zs)*100,SIGNED) 'jgl' FROM
(SELECT cid,COUNT(*) 'jg' FROM score WHERE score>60 GROUP BY cid) AS a,
(SELECT cid,COUNT(*) 'zs' FROM score GROUP BY cid) AS b
WHERE a.cid=b.cid
) AS c ORDER BY jgl DESC
-- 20.查询每门课程被选修的学生数
SELECT cid,COUNT(sid) FROM score GROUP BY cid;
SELECT cid,COUNT(sid) FROM score GROUP BY cid
-- 21.查询出只选修了一门课程的全部学生的学号和姓名
SELECT s.sid,s.sname FROM score sc,student s WHERE
s.sid=sc.sid GROUP BY s.sid HAVING COUNT(sc.cid)=1
SELECT sid,sname FROM student WHERE sid IN
(
SELECT sid FROM score GROUP BY sid HAVING COUNT(cid)=1
)
-- 22.查询每门课程的平均成绩,结果按平均成绩升序排列,
-- 平均成绩相同时,按课程号降序排列
SELECT c.cid,CONVERT(AVG(sc.score),SIGNED) pj FROM course c,score sc
WHERE c.cid=sc.cid GROUP BY c.cid ORDER BY pj ASC,cid DESC
SELECT cid,CONVERT(AVG(score),SIGNED) score FROM score
GROUP BY cid ORDER BY score ASC,cid DESC;
-- 23.查询选修“李”老师所授课程的学生中,成绩最高的学生姓名及其成绩
-- 列:学生姓名,成绩
-- 条件:成绩最高,李老师
-- 表:成绩,老师,学生
SELECT sc.*
FROM score sc,teacher t,student s,course c
WHERE sc.cid = c.cid AND c.tid= t.tid AND t.tname='李' AND (sc.sid=s.sid)
AND sc.score=(SELECT MAX(score) FROM score WHERE sc.cid=cid)
SELECT s.*
FROM score s LEFT JOIN course c ON (s.cid=c.cid)
LEFT JOIN teacher t ON (c.tid=t.tid)
WHERE t.tname='李' AND s.score=
(SELECT MAX(score) FROM score WHERE cid=s.cid)
-- 24.查询每门课程成绩最好的前两名
-- select * from score where cid=1
-- SELECT COUNT(score) FROM score WHERE cid = 1 AND score >= 60
-- select * from score group by cid;
SELECT a.sid, a.cid, a.score FROM score AS a WHERE
(
SELECT COUNT(score) FROM score AS b
WHERE b.cid = a.cid AND b.score >= a.score
) <= 2
ORDER BY a.cid ASC, a.score DESC
SELECT a.sid,a.cid,a.score FROM score AS a LEFT JOIN score AS b ON a.cid=b.cid AND a.score>=b.score
GROUP BY a.cid,a.sid,a.score
HAVING COUNT(a.cid)>=5
ORDER BY a.cid,a.score DESC;
SELECT t0.sid AS "姓名",t1.score AS "分数排行" FROM score t0
RIGHT JOIN (SELECT score FROM score GROUP BY score
ORDER BY score DESC LIMIT 2) t1 ON t0.score
= t1.score
-- 25.统计每门课程的学生选修人数(超过0人的课程才统计)。
-- 要求输出课程号和选修人数,查询结果按人数降序排列,
-- 若人数相同,按课程号升序排列
SELECT cid,COUNT(sid) AS c FROM score GROUP BY cid HAVING COUNT(sid)>0 ORDER BY c DESC,cid ASC;
-- 26.检索至少选修两门课程的学生学号
SELECT sid,COUNT(cid) FROM score GROUP BY sid HAVING COUNT(cid)>=2;
-- 27.查询两门以上不及格课程的同学的学号及其平均成绩
SELECT sid,AVG(score) FROM score WHERE sid IN
(
SELECT sid FROM score WHERE score<60
GROUP BY sid HAVING COUNT(cid)>2
)
-- 28.检索“”课程分数小于90的同学学号,按分数降序排列.
SELECT * FROM score WHERE cid=1 AND score<90 ORDER BY score DESC
USE student;
SELECT * FROM student;
-- 学生表
CREATE TABLE student(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20),
sage INT,
ssex CHAR(1)
);
INSERT INTO student (sname,sage,ssex) VALUES ('张三',18,0);
INSERT INTO student (sname,sage,ssex) VALUES ('李四',19,1);
INSERT INTO student (sname,sage,ssex) VALUES ('王二',20,0);
INSERT INTO student (sname,sage,ssex) VALUES ('麻子',21,1);
INSERT INTO student (sname,sage,ssex) VALUES ('刘一',21,1);
INSERT INTO student (sname,sage,ssex) VALUES ('陈二',22,0);
INSERT INTO student (sname,sage,ssex) VALUES ('赵三',19,0);
INSERT INTO student (sname,sage,ssex) VALUES ('王五',20,0);
DELETE FROM student WHERE sid=8;
-- 教师表
CREATE TABLE teacher(
tid INT PRIMARY KEY AUTO_INCREMENT,
tname VARCHAR(20)
);
INSERT INTO teacher (tname) VALUES ('李');
INSERT INTO teacher (tname) VALUES ('潘');
INSERT INTO teacher (tname) VALUES ('王');
-- 课程表
CREATE TABLE course(
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(20),
tid INT,
CONSTRAINT fk_course_teach FOREIGN KEY (tid) REFERENCES teacher(tid)
);
INSERT INTO course (cname,tid) VALUES ('语文',1);
INSERT INTO course (cname,tid) VALUES ('英语',1);
INSERT INTO course (cname,tid) VALUES ('数学',2);
INSERT INTO course (cname,tid) VALUES ('政治',2);
INSERT INTO course (cname,tid) VALUES ('历史',3);
-- 成绩表
CREATE TABLE score(
sid INT,
cid INT,
score INT,
CONSTRAINT fk_score_stu FOREIGN KEY (sid) REFERENCES student(sid),
CONSTRAINT fk_score_course FOREIGN KEY (cid) REFERENCES course(cid)
);
-- drop table score;
INSERT INTO score (sid,cid,score) VALUES (1,1,60);
INSERT INTO score (sid,cid,score) VALUES (1,2,65);
INSERT INTO score (sid,cid,score) VALUES (1,3,70);
INSERT INTO score (sid,cid,score) VALUES (1,4,20);
INSERT INTO score (sid,cid,score) VALUES (2,1,71);
INSERT INTO score (sid,cid,score) VALUES (2,2,63);
INSERT INTO score (sid,cid,score) VALUES (2,3,82);
INSERT INTO score (sid,cid,score) VALUES (2,4,64);
INSERT INTO score (sid,cid,score) VALUES (3,1,52);
INSERT INTO score (sid,cid,score) VALUES (3,2,90);
INSERT INTO score (sid,cid,score) VALUES (3,4,82);
INSERT INTO score (sid,cid,score) VALUES (3,5,61);
INSERT INTO score (sid,cid,score) VALUES (4,1,76);
INSERT INTO score (sid,cid,score) VALUES (4,2,85);
INSERT INTO score (sid,cid,score) VALUES (4,3,93);
INSERT INTO score (sid,cid,score) VALUES (4,4,53);
INSERT INTO score (sid,cid,score) VALUES (4,5,57);
INSERT INTO score (sid,cid,score) VALUES (5,3,41);
INSERT INTO score (sid,cid,score) VALUES (5,4,53);
INSERT INTO score (sid,cid,score) VALUES (5,5,55);
INSERT INTO score (sid,cid,score) VALUES (5,1,43);
INSERT INTO score (sid,cid,score) VALUES (5,2,56);
INSERT INTO score (sid,cid,score) VALUES (6,5,60);
INSERT INTO score (sid,cid,score) VALUES (7,1,60);
INSERT INTO score (sid,cid,score) VALUES (9,1,76);
SELECT * FROM score WHERE sid=4
-- delete from score where sid=5 and cid in (5)
-- 1.麻子的老师信息
-- <1>
SELECT * FROM teacher WHERE tid IN(
SELECT DISTINCT tid FROM course WHERE cid IN(SELECT cid FROM score WHERE sid = (
SELECT sid FROM student WHERE sname = '麻子'
)
)
);
-- 做的<2>
SELECT DISTINCT t.tid,t.tname FROM teacher t INNER JOIN course c ON t.tid=c.`tid`
INNER JOIN score sc ON sc.cid= c.cid INNER JOIN student s ON s.sid=sc.sid
WHERE s.sname='麻子'
-- <3>
SELECT DISTINCT t.`tid`,t.`tname` FROM
(SELECT sid,sname FROM student WHERE sname='麻子')AS s
INNER JOIN score sc ON sc.`sid`=s.`sid`
INNER JOIN course c ON c.`cid`=sc.`cid`
INNER JOIN teacher t ON t.`tid`=c.`tid`;
-- <4>
SELECT DISTINCT t.* FROM student s,score sc,course c,teacher t WHERE s.`sid`=sc.`sid` AND
c.`cid`=sc.`cid` AND t.`tid`= c.`tid` AND s.`sname`='麻子';
-- 2.求所有学生每个人的平均成绩大于60
-- 先求出每个人的平均成绩
-- <1>
SELECT sid,AVG(score) FROM score GROUP BY sid HAVING AVG(score)>=60
-- <2>
SELECT s.sid,s.sname,AVG(sc.score) FROM student s,score sc WHERE s.sid = sc.sid GROUP BY s.sid,s.`sname` HAVING AVG(sc.score)>=60
-- 3.查询所有同学的学号、姓名、选课数、总成绩
-- <1>
SELECT s.sid,s.sname,COUNT(sc.cid),SUM(sc.score) FROM student s,score sc WHERE s.sid = sc.sid GROUP BY s.sid,s.sname
-- <2>
SELECT s.sid,s.sname,COUNT(sc.cid),SUM(sc.score) FROM student s INNER JOIN score sc ON s.sid=sc.sid GROUP BY s.sid,s.sname;
-- 4.查询姓李的老师的个数
SELECT COUNT(*) FROM teacher WHERE tname LIKE '李%';
-- 5.查询没学过李老师课程的学生的姓名和编号
-- <1>
SELECT * FROM student WHERE sid NOT IN(
SELECT sid FROM score WHERE cid IN(
SELECT cid FROM course WHERE tid IN
(SELECT tid FROM teacher WHERE tname='李')
)
)
-- <2>
SELECT * FROM student s WHERE s.sid NOT IN(
SELECT DISTINCT sc.cid FROM
(SELECT tid FROM teacher WHERE tname LIKE '李%')t
INNER JOIN course c ON c.tid=t.tid
INNER JOIN score sc ON sc.cid=c.cid
)
-- <3>
SELECT * FROM student WHERE sid NOT IN(
SELECT sc.`sid` FROM
(SELECT tid FROM teacher WHERE tname LIKE '李%')t
,course c,score sc,student s WHERE t.tid=c.tid AND
c.cid = sc.cid AND sc.sid=s.sid
)
-- 6.查询“某1”课程比“某2”课程成绩高的所有学生的学号;
SELECT sid,sname FROM student WHERE sid IN(
SELECT a.sid FROM
(SELECT sc.sid,sc.score FROM score sc,course c WHERE (c.cid=sc.cid) AND c.cname='语文' ) AS a,
(SELECT sc.sid,sc.score FROM score sc,course c WHERE (c.cid=sc.cid) AND c.cname='英语' ) AS b
WHERE a.sid=b.sid AND a.score>b.score
)
SELECT sid,sname FROM student WHERE sid IN
(
SELECT a.sid FROM
(SELECT s.sid,s.score FROM score s RIGHT JOIN course c ON (s.cid=c.cid) WHERE c.cname='语文') AS a,
(SELECT s.sid,s.score FROM score s RIGHT JOIN course c ON (s.cid=c.cid) WHERE c.cname='英语') AS b
WHERE a.sid=b.sid AND a.score>b.score
)
-- 7.查询学过“```”并且也学过编号“```”课程的同学的学号、姓名;
SELECT a.sid,a.sname FROM
(SELECT s.sname,s.sid FROM student s,course c,score sc WHERE s.sid=sc.sid AND sc.cid=c.cid AND c.cname='语文')AS a,
(SELECT s.sname,s.sid FROM student s,course c,score sc WHERE s.sid=sc.sid AND sc.cid=c.cid AND c.cname='英语')AS b
WHERE a.sid=b.sid
SELECT a.sid,a.sname FROM
(SELECT s.sname,s.sid FROM student s,course c,score sc
WHERE cname='语文'AND sc.sid=s.sid AND sc.cid=c.cid) a,
(SELECT s.sname,s.sid FROM student s,course c,score sc
WHERE cname='英语'AND sc.sid=s.sid AND sc.cid=c.cid) b
WHERE a.sid=b.sid;
-- 这个没懂
SELECT student.sid,student.sname
FROM
student,score sc
WHERE
student.sid=sc.sid -- AND sc.cid='1'
AND NOT EXISTS
( SELECT * FROM score WHERE 1=1
AND score.sid=sc.sid
AND score.cid=2);
-- 7.查询学过“李”老师所教的所有课的同学的学号、姓名;
SELECT sid,sname FROM student WHERE sid IN(
SELECT sc.sid FROM course c,score sc,teacher t WHERE c.`tid`=t.`tid` AND c.`cid`=sc.`cid` AND t.`tname`='李' GROUP BY sc.`sid` HAVING COUNT(sc.`cid`)=(SELECT COUNT(c.`cid`) FROM teacher t,course c WHERE t.`tid`=c.`tid` AND t.`tname`='李' )
)
SELECT sid,sname FROM student WHERE sid IN
(
SELECT sid
-- ,COUNT(score.cid)
FROM score,course,teacher WHERE
score.cid=course.cid AND teacher.tid=course.tid AND
teacher.tname='李'
GROUP BY sid
HAVING COUNT(score.cid)=
(
SELECT COUNT(cid) FROM course,teacher
WHERE teacher.tid=course.tid AND tname='李'
)
)
-- 1.有哪些列
-- 2.用到哪些表
-- 3.条件是什么
-- 8.查询课程编号“”的成绩比课程编号“”课程低的所有同学的学号、姓名;
-- 13.查询和“1”号的同学学习的课程完全相同的其他同学学号和姓名;
SELECT stu.sid,stu.sname,COUNT(s.cid) num FROM student stu
INNER JOIN score s ON stu.sid = s.sid
AND s.cid IN
(SELECT sc.cid FROM score sc WHERE sc.sid = 1)
GROUP BY stu.sid,stu.sname
HAVING COUNT(s.cid) =
(
SELECT COUNT(1) num FROM score sc WHERE sc.sid = 1
)
-- 14.删除学习“李”老师课的score表记录;
DELETE FROM score WHERE cid IN
(
SELECT c.cid FROM teacher t,course c WHERE t.`tid`=c.`tid` AND t.`tname`='李'用
)
-- 16.按平均成绩从高到低显示所有学生的“语文”、“数学”、“英语”三门的课程成绩;
SELECT sid,语文,英语,政治 FROM
(
SELECT sc.sid,
MAX(CASE sc.cid WHEN 1 THEN sc.score ELSE 0 END) '语文',
MAX(CASE sc.cid WHEN 2 THEN sc.score ELSE 0 END) '英语',
MAX(CASE sc.cid WHEN 4 THEN sc.score ELSE 0 END) '政治',
CONVERT(AVG(sc.score),SIGNED) AS score
FROM student s LEFT JOIN score sc ON (sc.sid=s.sid) WHERE sc.cid IN
(SELECT cid FROM course WHERE cname IN('语文','英语','政治'))
GROUP BY sc.sid
)AS a ORDER BY score DESC;
SELECT sid,语文,英语,政治 FROM
(
SELECT sc.sid,
MAX(CASE sc.cid WHEN 1 THEN sc.score ELSE 0 END) '语文',
MAX(CASE sc.cid WHEN 2 THEN sc.score ELSE 0 END) '英语',
MAX(CASE sc.cid WHEN 4 THEN sc.score ELSE 0 END) '政治',
CONVERT(AVG(score),SIGNED) score
FROM student s LEFT JOIN score sc ON (s.sid=sc.sid) WHERE cid IN
(SELECT cid FROM course WHERE cname IN ('语文','英语','政治'))
GROUP BY sc.sid
) AS a ORDER BY score DESC
-- 17.按如下形式显示:学生ID,语文,英语,数学,政治,历史,有效课程数,有效平均分;
SELECT sid,语文,数学,英语,政治,历史,num,score FROM
(
SELECT sc.sid,COUNT(sc.cid) num,
MAX(CASE sc.cid WHEN 1 THEN sc.score ELSE 0 END) '语文',
MAX(CASE sc.cid WHEN 2 THEN sc.score ELSE 0 END) '英语',
MAX(CASE sc.cid WHEN 3 THEN sc.score ELSE 0 END) '数学',
MAX(CASE sc.cid WHEN 4 THEN sc.score ELSE 0 END) '政治',
MAX(CASE sc.cid WHEN 5 THEN sc.score ELSE 0 END) '历史',
CONVERT(AVG(score),SIGNED) score
FROM student s LEFT JOIN score sc ON (s.sid=sc.sid) WHERE cid IN
(SELECT cid FROM course WHERE cname IN ('语文','英语','政治','数学','历史'))
GROUP BY sc.sid
) AS a ORDER BY score DESC
-- 18.查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分;
SELECT cid,AVG(score),MAX(score),MIN(score) FROM score GROUP BY cid;
-- 19.按各科平均成绩从低到高和及格率的百分数从高到低顺序???
SELECT * FROM
(
SELECT cid,AVG(score) score FROM score GROUP BY cid
) AS a ORDER BY score ASC
-- 每科及格人数,每科总人数
SELECT * FROM
(
SELECT a.cid,jg,zs,CONVERT((jg/zs)*100,SIGNED) 'jgl' FROM
(SELECT cid,COUNT(*) 'jg' FROM score WHERE score>60 GROUP BY cid) a,
(SELECT cid,COUNT(*) 'zs' FROM score GROUP BY cid) b
WHERE a.cid=b.cid
) AS c ORDER BY jgl DESC
SELECT cid,AVG(score) a FROM score GROUP BY cid ORDER BY a ASC
SELECT * FROM
(
SELECT a.cid,jg,zs,CONVERT((jg/zs)*100,SIGNED) 'jgl' FROM
(SELECT cid,COUNT(*) 'jg' FROM score WHERE score>60 GROUP BY cid) AS a,
(SELECT cid,COUNT(*) 'zs' FROM score GROUP BY cid) AS b
WHERE a.cid=b.cid
) AS c ORDER BY jgl DESC
-- 20.查询每门课程被选修的学生数
SELECT cid,COUNT(sid) FROM score GROUP BY cid;
SELECT cid,COUNT(sid) FROM score GROUP BY cid
-- 21.查询出只选修了一门课程的全部学生的学号和姓名
SELECT s.sid,s.sname FROM score sc,student s WHERE
s.sid=sc.sid GROUP BY s.sid HAVING COUNT(sc.cid)=1
SELECT sid,sname FROM student WHERE sid IN
(
SELECT sid FROM score GROUP BY sid HAVING COUNT(cid)=1
)
-- 22.查询每门课程的平均成绩,结果按平均成绩升序排列,
-- 平均成绩相同时,按课程号降序排列
SELECT c.cid,CONVERT(AVG(sc.score),SIGNED) pj FROM course c,score sc
WHERE c.cid=sc.cid GROUP BY c.cid ORDER BY pj ASC,cid DESC
SELECT cid,CONVERT(AVG(score),SIGNED) score FROM score
GROUP BY cid ORDER BY score ASC,cid DESC;
-- 23.查询选修“李”老师所授课程的学生中,成绩最高的学生姓名及其成绩
-- 列:学生姓名,成绩
-- 条件:成绩最高,李老师
-- 表:成绩,老师,学生
SELECT sc.*
FROM score sc,teacher t,student s,course c
WHERE sc.cid = c.cid AND c.tid= t.tid AND t.tname='李' AND (sc.sid=s.sid)
AND sc.score=(SELECT MAX(score) FROM score WHERE sc.cid=cid)
SELECT s.*
FROM score s LEFT JOIN course c ON (s.cid=c.cid)
LEFT JOIN teacher t ON (c.tid=t.tid)
WHERE t.tname='李' AND s.score=
(SELECT MAX(score) FROM score WHERE cid=s.cid)
-- 24.查询每门课程成绩最好的前两名
-- select * from score where cid=1
-- SELECT COUNT(score) FROM score WHERE cid = 1 AND score >= 60
-- select * from score group by cid;
SELECT a.sid, a.cid, a.score FROM score AS a WHERE
(
SELECT COUNT(score) FROM score AS b
WHERE b.cid = a.cid AND b.score >= a.score
) <= 2
ORDER BY a.cid ASC, a.score DESC
SELECT a.sid,a.cid,a.score FROM score AS a LEFT JOIN score AS b ON a.cid=b.cid AND a.score>=b.score
GROUP BY a.cid,a.sid,a.score
HAVING COUNT(a.cid)>=5
ORDER BY a.cid,a.score DESC;
SELECT t0.sid AS "姓名",t1.score AS "分数排行" FROM score t0
RIGHT JOIN (SELECT score FROM score GROUP BY score
ORDER BY score DESC LIMIT 2) t1 ON t0.score
= t1.score
-- 25.统计每门课程的学生选修人数(超过0人的课程才统计)。
-- 要求输出课程号和选修人数,查询结果按人数降序排列,
-- 若人数相同,按课程号升序排列
SELECT cid,COUNT(sid) AS c FROM score GROUP BY cid HAVING COUNT(sid)>0 ORDER BY c DESC,cid ASC;
-- 26.检索至少选修两门课程的学生学号
SELECT sid,COUNT(cid) FROM score GROUP BY sid HAVING COUNT(cid)>=2;
-- 27.查询两门以上不及格课程的同学的学号及其平均成绩
SELECT sid,AVG(score) FROM score WHERE sid IN
(
SELECT sid FROM score WHERE score<60
GROUP BY sid HAVING COUNT(cid)>2
)
-- 28.检索“”课程分数小于90的同学学号,按分数降序排列.
SELECT * FROM score WHERE cid=1 AND score<90 ORDER BY score DESC