1.表结构详情
--1.学生表
Student(SID,Sname,Sage,Ssex) --SID 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
--2.课程表
Course(CID,Cname,TID) --CID --课程编号,Cname 课程名称,TID 教师编号
--3.教师表
Teacher(TID,Tname) --TID 教师编号,Tname 教师姓名
--4.成绩表
SC(SID,CID,score) --SID 学生编号,CID 课程编号,score 分数
2.建表与数据导入
create table Student(SID varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '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' , '女');
create table Course(CID varchar(10),Cname nvarchar(10),TID varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
create table Teacher(TID varchar(10),Tname nvarchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
create table SC(SID varchar(10),CID varchar(10),score decimal(18,1));
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);
3.SQL
-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
-- 01课程,02课程同时存在
-- 查询学生的01课程,02课程(三张表:学生表,01课程表,02课程表)
SELECT * FROM student s,sc sc1,sc sc2
WHERE s.sid=sc1.sid AND s.sid=sc2.sid
AND sc1.cid=01 AND sc2.cid=02
-- 查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT * FROM student s,sc sc1,sc sc2
WHERE s.sid=sc1.sid AND s.sid=sc2.sid
AND sc1.cid=01 AND sc2.cid=02
AND sc1.score>sc2.score
-- reference
SELECT s.*,sc1.score ,sc2.score
FROM student s,sc sc1,sc sc2
WHERE s.sid=sc1.sid AND s.sid=sc2.sid
AND sc1.cid='01' AND sc2.cid='02'
AND sc1.score>sc2.score
-- 01课程,02课程不同时存在
SELECT * FROM student s
LEFT JOIN sc sc1 ON s.sid=sc1.sid AND sc1.cid='01'
LEFT JOIN sc sc2 ON s.sid=sc2.sid AND sc2.cid='02'
WHERE IFNULL(sc1.score,0) > IFNULL(sc2.score,0)
-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
-- 学生都存在成绩
-- 查询学生和成绩
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid
-- 查询学生和成绩平均成绩
SELECT s.sid,s.sname,AVG(sc.score) FROM student s,sc sc
WHERE s.sid=sc.sid GROUP BY s.sid
-- 平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT s.sid,s.sname,AVG(sc.score) FROM student s,sc sc
WHERE s.sid=sc.sid GROUP BY s.sid
HAVING AVG(sc.score)>=60
ORDER BY s.sid
-- DECIMAL(5,2)，则该字段可以存储-999.99~999.99，最大值为999.99。
-- 也就是说D表示的是小数部分长度，(M-D)表示的是整数部分长度
-- CAST(源数据)别名 功能-->起别名
-- reference
SELECT a.SID , a.Sname , CAST(AVG(b.score) AS DECIMAL(18,2)) avg_score
FROM Student a , sc b
WHERE a.SID = b.SID
GROUP BY a.SID , a.Sname
HAVING CAST(AVG(b.score) AS DECIMAL(18,2)) >= 60
ORDER BY a.SID
-- 学生不存在成绩
-- 查询学生与成绩
SELECT * FROM student s LEFT JOIN sc sc ON s.sid=sc.sid
-- 查询学生与平均成绩
SELECT *,AVG(sc.score) FROM student s LEFT JOIN sc sc ON s.sid=sc.sid
GROUP BY s.sid
-- 平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT s.sid,s.sname,IFNULL(AVG(sc.score),0) FROM student s LEFT JOIN sc sc ON s.sid=sc.sid
GROUP BY s.sid
HAVING IFNULL(AVG(sc.score),0)>60
-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
-- 5.1、查询所有有成绩的SQL
-- 查询学生与成绩表
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid
-- 查询学生与成绩表 选课总数
SELECT *,COUNT(*) FROM student s,sc sc WHERE s.sid=sc.sid GROUP BY s.sid
-- 查询学生与成绩表 选课总数 所有课程的总成绩
SELECT s.sid,s.sname,COUNT(sc.cid) 选课总数,SUM(sc.score)总成绩
FROM student s,sc sc
WHERE s.sid=sc.sid
GROUP BY s.sid
-- reference
SELECT a.SID 学生编号 , a.Sname 学生姓名 , COUNT(b.CID) 选课总数, SUM(score) 所有课程的总成绩
FROM Student a , SC b
WHERE a.SID = b.SID
GROUP BY a.SID,a.Sname
ORDER BY a.SID
-- 5.2、查询所有(包括有成绩和无成绩)的SQL
SELECT s.sid,s.sname,COUNT(sc.cid) 选课总数,SUM(IFNULL(sc.score,0))总成绩
FROM student s LEFT JOIN sc sc
ON s.sid=sc.sid
GROUP BY s.sid
-- reference
SELECT a.SID 学生编号 , a.Sname 学生姓名 , COUNT(b.CID) 选课总数, SUM(score) 所有课程的总成绩
FROM Student a LEFT JOIN SC b
ON a.SID = b.SID
GROUP BY a.SID,a.Sname
ORDER BY a.SID
-- 6、查询"李"姓老师的数量
-- 方法1
SELECT COUNT(*) FROM teacher t WHERE tname LIKE '李%'
SELECT COUNT(tname) FROM teacher t WHERE tname LIKE '李_'
-- 方法2
-- reference
SELECT COUNT(Tname) 李姓老师的数量 FROM Teacher WHERE LEFT(Tname,1) = '李'
-- 7、查询学过"张三"老师授课的同学的信息
-- 7、查询学过"张三"老师授课的同学的信息
-- 老师授课
SELECT * FROM teacher t,course c WHERE t.tid=c.tid
-- 张三老师授课
SELECT * FROM teacher t,course c WHERE t.tid=c.tid AND t.tname='张三'
-- 学生与成绩表
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid
-- 学过"张三"老师授课的同学的信息
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid
AND sc.cid IN(SELECT c.cid FROM teacher t,course c
WHERE t.tid=c.tid AND t.tname='张三')
-- 方式二(推荐)
-- 查询学过"张三"老师授课的同学的信息
-- 四张表 老师,课程,学生,成绩
-- 去除笛卡尔积
-- 课程表关联老师 c.tid=t.tid
-- 成绩表关联学生 sc.sid=s.sid
-- 成绩表关联课程 sc.cid=c.cid
-- 查询老师授课的同学的信息
SELECT * FROM student s,sc sc,teacher t,course c
WHERE sc.sid=s.sid AND c.tid=t.tid AND sc.cid=c.cid
-- 查询学过"张三"老师授课的同学的信息
SELECT s.* FROM student s,sc sc,teacher t,course c
WHERE sc.sid=s.sid AND c.tid=t.tid AND sc.cid=c.cid
AND t.tname='张三'
-- reference
SELECT DISTINCT Student.* FROM Student , SC , Course , Teacher
WHERE Student.SID = SC.SID AND SC.CID = Course.CID AND Course.TID = Teacher.TID
AND Teacher.Tname = '张三'
ORDER BY Student.SID
-- 8、查询没学过"张三"老师授课的同学的信息
-- 查询学过"张三"老师授课的同学的信息
SELECT * FROM student s,sc sc,teacher t,course c
WHERE sc.sid=s.sid AND c.tid=t.tid AND sc.cid=c.cid
AND t.tname='张三'
-- 错误 方式一:
-- 这样只是在查询 李四,王五老师授课的信息 该生有没有被张三老师教过并没有查询
SELECT * FROM student s,sc sc,teacher t,course c
WHERE sc.sid=s.sid AND c.tid=t.tid AND sc.cid=c.cid
AND (t.tname='李四' OR t.tname='王五')
SELECT * FROM student s,sc sc,teacher t,course c
WHERE sc.sid=s.sid AND c.tid=t.tid AND sc.cid=c.cid
AND t.tname IN('李四','王五')
SELECT * FROM student s,sc sc,teacher t,course c
WHERE sc.sid=s.sid AND c.tid=t.tid AND sc.cid=c.cid
AND t.tname NOT IN('张三')
-- 方式二:
SELECT * FROM student s2
WHERE s2.sid NOT IN(SELECT s.sid FROM student s,sc sc,teacher t,course c
WHERE sc.sid=s.sid AND c.tid=t.tid AND sc.cid=c.cid
AND t.tname='张三')
-- reference
SELECT m.* FROM Student m
WHERE SID NOT IN (SELECT DISTINCT SC.SID FROM SC , Course , Teacher
WHERE SC.CID = Course.CID AND Course.TID = Teacher.TID AND Teacher.Tname = '张三')
ORDER BY m.SID
-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
-- 查询学生课程信息(两张表)
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid
-- 查询学过01,02课程的学生课程信息
-- 这样查询的是学过01或02课程的信息
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid IN(01,02)
-- 查询学过编号为"01"或编号为"02"的课程的同学的信息
SELECT * FROM student s
WHERE s.sid IN(SELECT s.sid FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid IN(01,02))
-- 总结 思路很重要 不先想清楚 写了也白写
-- 思路 先找出学过01课程的学生 再学过01课程的学生中再找学过02课程的学生
-- 学过01课程的学生
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid=01
-- 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT s.* FROM student s,sc sc
WHERE s.sid=sc.sid AND sc.cid=02
AND s.sid IN(SELECT sc.sid FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid=01)
SELECT s.* FROM student s,sc sc
WHERE s.sid=sc.sid AND sc.cid=02
AND EXISTS(SELECT 1 FROM sc sc2 WHERE sc2.sid=sc.sid AND sc2.cid=01)
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid=01
SELECT *,9 FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid=01
-- 方法1
-- reference
SELECT Student.* FROM Student , SC
WHERE Student.SID = SC.SID AND SC.CID = '01'
AND EXISTS (SELECT 1 FROM SC SC_2 WHERE SC_2.SID = SC.SID AND SC_2.CID = '02') ORDER BY Student.SID
-- 方法2
-- reference
SELECT Student.* FROM Student , SC
WHERE Student.SID = SC.SID AND SC.CID = '02'
AND EXISTS (SELECT 1 FROM SC SC_2 WHERE SC_2.SID = SC.SID AND SC_2.CID = '01') ORDER BY Student.SID
-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
-- 查询学过01课程的学生
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid='01' -- 123456
-- 学过编号为"02"的课程的同学的信息
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid='02' -- 123457
-- 没有学过编号为"02"的课程的同学的信息
-- 错误:SELECT * FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid not in (02)
SELECT * FROM student s WHERE sid
NOT IN(SELECT s.sid FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid=02)
-- 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid='01'
AND s.sid IN(
SELECT s.sid FROM student s WHERE sid
NOT IN(SELECT s.sid FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid=02))
-- 改进
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid='01'
AND s.sid NOT IN(
SELECT s.sid FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid='02')
-- 再改进(新增成绩表2 用来记录02课程的信息 共用前面的学生表
-- 这里的共用仅为表的引用 而非结果集 结果集相互独立 不受影响)
-- 用 AND等条件连接的子句 都是并列同时执行 如果有括弧 如in() 则每次先执行括弧里面的子句
-- 将结果作为整体条件 与前面的条件并列
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid='01'
AND s.sid NOT IN(
SELECT s.sid FROM sc sc2 WHERE s.sid=sc2.sid AND sc2.cid='02')
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid='02' -- 123457
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid='01' -- 123456
-- 6
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid='01'
AND NOT EXISTS(
SELECT s.sid FROM sc sc2 WHERE s.sid=sc2.sid AND sc2.cid='02')
-- 12345
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid='01'
AND EXISTS(
SELECT s.sid FROM sc sc2 WHERE s.sid=sc2.sid AND sc2.cid='02')
-- reference
-- 方法1
SELECT Student.* FROM Student , SC WHERE Student.SID = SC.SID AND SC.CID = '01'
AND NOT EXISTS (
SELECT 1 FROM SC SC_2 WHERE SC_2.SID = SC.SID AND SC_2.CID = '02') ORDER BY Student.SID
-- 这里 SELECT 后的变量可以任意更改 仅仅为了满足语法需求
-- 方法2
SELECT Student.* FROM Student , SC WHERE Student.SID = SC.SID AND SC.CID = '01'
AND Student.SID NOT IN (
SELECT SC_2.SID FROM SC SC_2 WHERE SC_2.SID = SC.SID AND SC_2.CID = '02') ORDER BY Student.SID
-- 11、查询没有学全所有课程的同学的信息
-- 分析 分为两种情况 修了课程的学生和没修课程的学生 当然可以用外连接统一到一起查询
-- 1查询课程数量
SELECT COUNT(*) FROM course c
-- 2查询学生所修课程数量
SELECT *,COUNT(*) FROM student s,sc sc WHERE s.sid=sc.sid GROUP BY s.sid
-- 3查询所修课程数小于总课程数的学生
SELECT *,COUNT(*) FROM student s,sc sc WHERE s.sid=sc.sid GROUP BY s.sid
HAVING COUNT(*)<3
SELECT *,COUNT(*) 课程总数 FROM student s,sc sc WHERE s.sid=sc.sid GROUP BY s.sid
HAVING 课程总数<(SELECT COUNT(*) FROM course c)
SELECT *,COUNT(*) 课程总数 FROM student s,sc sc WHERE s.sid=sc.sid GROUP BY s.sid
HAVING 课程总数<ALL(SELECT COUNT(*) FROM course c)
-- 改进
SELECT * FROM student s LEFT JOIN sc sc ON s.sid=sc.sid GROUP BY s.sid
HAVING COUNT(*)<(SELECT COUNT(*)FROM course c)
-- reference
SELECT Student.* FROM Student LEFT JOIN SC ON Student.SID = SC.SID
GROUP BY Student.SID , Student.Sname , Student.Sage , Student.Ssex
HAVING COUNT(CID) < (SELECT COUNT(CID) FROM Course)
-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
-- 所有学生的课程信息
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid
-- "01"的同学所学的课程
SELECT *,sc.cid FROM student s,sc sc WHERE s.sid=sc.sid AND s.sid='01'
SELECT s.* FROM student s,sc sc WHERE s.sid=sc.sid
AND sc.cid IN(SELECT sc.cid FROM student s,sc sc WHERE s.sid=sc.sid AND s.sid='01')
GROUP BY s.sid
-- 改进
SELECT DISTINCT s.* FROM student s,sc sc WHERE s.sid=sc.sid
AND sc.cid IN(SELECT sc.cid FROM student s,sc sc WHERE s.sid=sc.sid AND s.sid='01')
-- 再改进
SELECT DISTINCT s.* FROM student s,sc sc WHERE s.sid=sc.sid
AND sc.cid IN(SELECT sc2.cid FROM sc sc2 WHERE sc2.sid='01')
-- ref
SELECT DISTINCT Student.* FROM Student , SC WHERE Student.SID = SC.SID
AND SC.CID IN (SELECT CID FROM SC WHERE SID = '01') AND Student.SID <> '01'
-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
-- 查询其它同学的课程信息
SELECT * FROM student s,sc sc WHERE s.sid <> '01' AND s.sid=sc.sid
-- 统计其它同学的课程的数
SELECT s.*,COUNT(*)FROM student s,sc sc WHERE s.sid <> '01' AND s.sid=sc.sid GROUP BY sc.sid
-- 统计01学生所学课程数
SELECT COUNT(*) FROM sc WHERE sc.sid='01'
-- 查询和"01"号的同学学习的课程完全相同的其他同学的信息
SELECT s.* FROM student s,sc sc WHERE s.sid <> '01' AND s.sid=sc.sid GROUP BY sc.sid
HAVING COUNT(*)=(SELECT COUNT(*) FROM sc WHERE sc.sid='01')
-- 以上写法有问题 这样统计的只是学科数目上与01学生相同而不区分科目 所以并不是完全相同
-- 当用06同学测试时错误就很明显了
SELECT s.* FROM student s,sc sc WHERE s.sid <> '06' AND s.sid=sc.sid GROUP BY sc.sid
HAVING COUNT(*)=(SELECT COUNT(*) FROM sc WHERE sc.sid='06')
-- 改进
-- 分析完全相同 即数量相同并且课程也相同
-- 01学生所学课程
SELECT sc.cid FROM sc sc WHERE sc.sid='01'
-- 统计01学生所学课程数
SELECT COUNT(*) FROM sc WHERE sc.sid='01'
-- 1统计包含01学生课程的其他学生课程
SELECT * FROM sc WHERE sc.sid<>'01' AND sc.cid IN(SELECT sc.cid FROM sc sc WHERE sc.sid='01')
-- 2统计包含01学生课程的其他学生课程数量
SELECT *,COUNT(*) FROM sc WHERE sc.sid<>'01' AND sc.cid IN(SELECT sc.cid FROM sc sc WHERE sc.sid='01')
GROUP BY sc.sid
-- 3统计课程,课程数量与01学生相同的其他学生课程信息
SELECT *,COUNT(*) FROM sc WHERE sc.sid<>'01' AND sc.cid IN(SELECT sc.cid FROM sc sc WHERE sc.sid='01')
GROUP BY sc.sid HAVING COUNT(*)=(SELECT COUNT(*) FROM sc WHERE sc.sid='01')
-- 4统计课程,课程数量与01学生相同的其他学生id
SELECT sc.sid FROM sc WHERE sc.sid<>'01' AND sc.cid IN(SELECT sc.cid FROM sc sc WHERE sc.sid='01')
GROUP BY sc.sid HAVING COUNT(*)=(SELECT COUNT(*) FROM sc WHERE sc.sid='01')
-- 5查询和"01"号的同学学习的课程完全相同的其他同学的信息
SELECT * FROM student s WHERE s.sid IN(SELECT sc.sid FROM sc WHERE sc.sid<>'01' AND sc.cid IN(SELECT sc.cid FROM sc sc WHERE sc.sid='01')
GROUP BY sc.sid HAVING COUNT(*)=(SELECT COUNT(*) FROM sc WHERE sc.sid='01'))
SELECT * FROM student s WHERE
s.sid IN(SELECT sc.sid FROM sc WHERE sc.sid<>'01'
AND sc.cid IN(SELECT sc.cid FROM sc sc WHERE sc.sid='01')
GROUP BY sc.sid HAVING COUNT(*)=(SELECT COUNT(*) FROM sc WHERE sc.sid='01'))
SELECT * FROM student s WHERE
s.sid IN(SELECT sc.sid FROM sc WHERE sc.sid<>'06'
AND sc.cid IN(SELECT sc.cid FROM sc sc WHERE sc.sid='06')
GROUP BY sc.sid HAVING COUNT(*)=(SELECT COUNT(*) FROM sc WHERE sc.sid='06'))
-- ref
SELECT Student.* FROM Student WHERE
SID IN(SELECT DISTINCT SC.SID FROM SC WHERE SID <> '01'
AND SC.CID IN (SELECT DISTINCT CID FROM SC WHERE SID = '01')
GROUP BY SC.SID HAVING COUNT(1) = (SELECT COUNT(1) FROM SC WHERE SID='01'))
SELECT Student.* FROM Student WHERE
SID IN(SELECT DISTINCT SC.SID FROM SC WHERE SID <> '06'
AND SC.CID IN (SELECT DISTINCT CID FROM SC WHERE SID = '06')
GROUP BY SC.SID HAVING COUNT(1) = (SELECT COUNT(1) FROM SC WHERE SID='06'))
-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名
-- 查询张三老师教授课程的ID
SELECT * FROM course c WHERE c.tid = (SELECT tid FROM teacher WHERE tname = '张三')
SELECT c.cid FROM course c WHERE c.tid = (SELECT tid FROM teacher WHERE tname = '张三')
-- 查询学过此课程的学生id
SELECT * FROM sc sc WHERE sc.cid IN (SELECT c.cid FROM course c WHERE c.tid = (SELECT tid FROM teacher WHERE tname = '张三') )
SELECT sc.sid FROM sc sc WHERE sc.cid IN (SELECT c.cid FROM course c WHERE c.tid = (SELECT tid FROM teacher WHERE tname = '张三') )
-- 取反 得到没有学过此课程的学生信息
SELECT * FROM student s WHERE s.sid NOT IN (SELECT sc.sid FROM sc sc WHERE sc.cid IN (SELECT c.cid FROM course c WHERE c.tid = (SELECT tid FROM teacher WHERE tname = '张三')))
SELECT * FROM student s WHERE s.sid NOT IN
(SELECT sc.sid FROM sc sc WHERE sc.cid IN
(SELECT c.cid FROM course c WHERE c.tid =
(SELECT tid FROM teacher WHERE tname = '张三')))
-- 改进 都是单表查询 效率太低
-- 三表关联查询 去除迪卡尔集
SELECT * FROM teacher t,course c,sc sc WHERE t.tid=c.tid AND c.cid=sc.cid
-- AND 条件并列 与先后顺序无关
SELECT * FROM teacher t,course c,sc sc WHERE t.tid=c.tid AND c.cid=sc.cid AND t.tname='张三'
SELECT * FROM teacher t,course c,sc sc WHERE t.tname='张三' AND t.tid=c.tid AND c.cid=sc.cid
-- 查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT * FROM student WHERE sid NOT IN(SELECT sc.sid FROM teacher t,course c,sc sc WHERE t.tid=c.tid AND c.cid=sc.cid AND t.tname='张三')
SELECT * FROM student WHERE sid NOT IN
(SELECT sc.sid FROM teacher t,course c,sc sc
WHERE t.tid=c.tid AND c.cid=sc.cid AND t.tname='张三')
-- ref
SELECT student.* FROM student WHERE student.SID NOT IN
(SELECT DISTINCT sc.SID FROM sc , course , teacher
WHERE sc.CID = course.CID AND course.TID = teacher.TID AND teacher.tname = '张三')
ORDER BY student.SID
-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
-- 查询不及格的课程
SELECT * FROM sc sc WHERE sc.score<60
-- 查询不及格的课程的学生ID
SELECT DISTINCT sc.sid FROM sc sc WHERE sc.score<60
-- 查询不及格课程的数目
SELECT DISTINCT sc.sid,COUNT(*) FROM sc sc WHERE sc.score<60 GROUP BY sc.sid
-- 查询不及格课程的数目大于等于二
SELECT DISTINCT sc.sid FROM sc sc WHERE sc.score<60 GROUP BY sc.sid
HAVING COUNT(*)>='2'
-- 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT s.sid,s.sname,AVG(sc.score) FROM student s,sc sc WHERE s.sid=sc.sid
AND s.sid IN(SELECT DISTINCT sc.sid FROM sc sc WHERE sc.score<60 GROUP BY sc.sid
HAVING COUNT(*)>='2') GROUP BY s.sid
-- 改进
SELECT *,AVG(sc.score) FROM student s,sc sc WHERE s.sid=sc.sid
AND sc.score<60 GROUP BY s.sid
HAVING COUNT(*)>='2'
-- ref
SELECT student.SID , student.sname , CAST(AVG(score) AS DECIMAL(18,2)) avg_score
FROM student , sc WHERE student.SID = SC.SID
AND student.SID IN (SELECT SID FROM SC WHERE score < 60 GROUP BY SID
HAVING COUNT(1) >= 2)GROUP BY student.SID , student.sname
-- 16、检索"01"课程分数小于60，按分数降序排列的学生信息
-- 检索"01"课程分数小于60
SELECT * FROM sc sc WHERE sc.cid='01' AND sc.score<60
-- 检索"01"课程分数小于60的学生ID
SELECT DISTINCT sc.sid FROM sc sc WHERE sc.cid='01' AND sc.score<60
-- 检索"01"课程分数小于60，按分数降序排列的学生信息
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid='01'
AND s.sid IN(SELECT DISTINCT sc.sid FROM sc sc WHERE sc.cid='01' AND sc.score<60)
ORDER BY sc.score DESC
-- 改进
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid='01' AND sc.score<60
ORDER BY sc.score DESC
-- ref
SELECT student.* , sc.CID , sc.score FROM student , sc
WHERE student.SID = SC.SID AND sc.score < 60 AND sc.CID = '01'
ORDER BY sc.score DESC
-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
-- 学生成绩
SELECT * FROM student s,sc sc,course c WHERE s.sid=sc.sid AND sc.cid=c.cid
-- 改进
-- 所有学生包括没有成绩的学生,应用外连接
-- 查询所有
SELECT * FROM student s LEFT JOIN sc sc ON s.sid=sc.sid
LEFT JOIN course c ON sc.cid=c.cid
-- 列转行
-- 查询学生的所有成绩
SELECT * ,
(CASE c.cname WHEN '语文' THEN sc.score ELSE NULL END) 语文
FROM student s LEFT JOIN sc sc ON s.sid=sc.sid
LEFT JOIN course c ON sc.cid=c.cid
SELECT * ,
(CASE c.cname WHEN '语文' THEN sc.score ELSE NULL END) 语文,
(CASE c.cname WHEN '数学' THEN sc.score ELSE NULL END) 数学,
(CASE c.cname WHEN '英语' THEN sc.score ELSE NULL END) 英语
FROM student s LEFT JOIN sc sc ON s.sid=sc.sid
LEFT JOIN course c ON sc.cid=c.cid
-- 查询学生的所有成绩和平均成绩
SELECT * ,
(CASE c.cname WHEN '语文' THEN sc.score ELSE NULL END) 语文,
(CASE c.cname WHEN '数学' THEN sc.score ELSE NULL END) 数学,
(CASE c.cname WHEN '英语' THEN sc.score ELSE NULL END) 英语,
AVG(sc.score) avg_score
FROM student s LEFT JOIN sc sc ON s.sid=sc.sid
LEFT JOIN course c ON sc.cid=c.cid
GROUP BY s.sid
-- 分析结果 分组之后 成绩为分组字段的第一条记录
-- case when实现行列转换时会出现多条记录
-- 如果不用聚合函数直接进行group by分组
-- 那么检索的是基表里分组字段的第一条记录
-- 如果使用max()函数之后再进行group by分组
-- 那么就会检索每个字段的最大值然后再分组
-- 改进
SELECT * ,
MAX(CASE c.cname WHEN '语文' THEN sc.score ELSE NULL END) 语文,
MAX(CASE c.cname WHEN '数学' THEN sc.score ELSE NULL END) 数学,
MAX(CASE c.cname WHEN '英语' THEN sc.score ELSE NULL END) 英语,
AVG(sc.score) avg_score
FROM student s LEFT JOIN sc sc ON s.sid=sc.sid
LEFT JOIN course c ON sc.cid=c.cid
GROUP BY s.sid
-- 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT * ,
MAX(CASE c.cname WHEN '语文' THEN sc.score ELSE NULL END) 语文,
MAX(CASE c.cname WHEN '数学' THEN sc.score ELSE NULL END) 数学,
MAX(CASE c.cname WHEN '英语' THEN sc.score ELSE NULL END) 英语,
AVG(sc.score) avg_score
FROM student s LEFT JOIN sc sc ON s.sid=sc.sid
LEFT JOIN course c ON sc.cid=c.cid
GROUP BY s.sid
ORDER BY avg_score DESC
-- 美化排版
SELECT s.sid,s.sname,
MAX(CASE c.cname WHEN '语文' THEN sc.score ELSE NULL END) 语文,
MAX(CASE c.cname WHEN '数学' THEN sc.score ELSE NULL END) 数学,
MAX(CASE c.cname WHEN '英语' THEN sc.score ELSE NULL END) 英语,
CAST(AVG(sc.score) AS DECIMAL(18,2)) avg_score
FROM student s LEFT JOIN sc sc ON s.sid=sc.sid
LEFT JOIN course c ON sc.cid=c.cid
GROUP BY s.sid
ORDER BY avg_score DESC
-- 方式二
SELECT s.sid,s.sname,
MAX(CASE WHEN c.cname='语文' THEN sc.score ELSE NULL END) 语文,
MAX(CASE WHEN c.cname='数学' THEN sc.score ELSE NULL END) 数学,
MAX(CASE WHEN c.cname='英语' THEN sc.score ELSE NULL END) 英语,
CAST(AVG(sc.score) AS DECIMAL(18,2)) avg_score
FROM student s LEFT JOIN sc sc ON s.sid=sc.sid
LEFT JOIN course c ON sc.cid=c.cid
GROUP BY s.sid
ORDER BY avg_score DESC
-- ref
SELECT a.SID 学生编号 , a.Sname 学生姓名 ,
MAX(CASE c.Cname WHEN '语文' THEN b.score ELSE NULL END) 语文,
MAX(CASE c.Cname WHEN '数学' THEN b.score ELSE NULL END) 数学,
MAX(CASE c.Cname WHEN '英语' THEN b.score ELSE NULL END) 英语 ,
CAST(AVG(b.score) AS DECIMAL(18,2)) avgscore 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
ORDER BY avgscore DESC
-- ref
-- 17.1 SQL 2000 静态
SELECT a.SID 学生编号 , a.Sname 学生姓名 ,
MAX(CASE c.Cname WHEN '语文' THEN b.score ELSE NULL END) 语文 ,
MAX(CASE c.Cname WHEN '数学' THEN b.score ELSE NULL END) 数学 ,
MAX(CASE c.Cname WHEN '英语' THEN b.score ELSE NULL END) 英语 ,
CAST(AVG(b.score) AS DECIMAL(18,2)) 平均分
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
ORDER BY 平均分 DESC
SELECT a.SID 学生编号 , a.Sname 学生姓名 ,
MAX(CASE c.Cname WHEN '语文' THEN b.score ELSE NULL END) 语文,
MAX(CASE c.Cname WHEN '数学' THEN b.score ELSE NULL END) 数学,
MAX(CASE c.Cname WHEN '英语' THEN b.score ELSE NULL END) 英语 ,
CAST(AVG(b.score) AS DECIMAL(18,2)) avgscore 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
ORDER BY avgscore DESC
-- 18、查询各科成绩最高分、最低分和平均分：以如下形式显示：
-- 课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
-- 查询所有学生的课程成绩
SELECT * FROM course c LEFT JOIN sc sc ON sc.cid=c.cid
-- 语文成绩
SELECT * FROM course c LEFT JOIN sc sc ON sc.cid=c.cid AND c.cname='语文'
-- 将成绩按科目分组
SELECT c.cid 课程序列, c.cname 课程名
FROM course c LEFT JOIN sc sc ON sc.cid=c.cid
GROUP BY c.cid
-- 统计
SELECT c.cid 课程序列, c.cname 课程名 ,
MAX(sc.score) 最高分,MIN(sc.score) 最低分,AVG(sc.score) 平均分,
FROM course c LEFT JOIN sc sc ON sc.cid=c.cid
GROUP BY c.cid
-- 做不下去了 换思路
-- 方法二
-- 所有成绩
SELECT * FROM course c,sc sc WHERE sc.cid=c.cid
-- 语文成绩
SELECT * FROM course c,sc sc WHERE sc.cid=c.cid AND c.cname='语文'
SELECT sc.score FROM course c,sc sc WHERE sc.cid=c.cid AND c.cname='语文'
-- 各分段的成绩的成绩
SELECT sc.score FROM course c,sc sc WHERE sc.cid=c.cid AND c.cname='语文'
SELECT sc.score FROM course c,sc sc WHERE sc.cid=c.cid AND c.cname='语文' AND sc.score>=60
SELECT sc.score FROM course c,sc sc WHERE sc.cid=c.cid AND c.cname='语文' AND sc.score>=70 AND sc.score<=80
SELECT sc.score FROM course c,sc sc WHERE sc.cid=c.cid AND c.cname='语文' AND sc.score>=80 AND sc.score<=90
SELECT sc.score FROM course c,sc sc WHERE sc.cid=c.cid AND c.cname='语文' AND sc.score>=90
-- 各分段的成绩的人数
SELECT COUNT(*) FROM course c,sc sc WHERE sc.cid=c.cid AND c.cname='语文'
SELECT COUNT(*) FROM course c,sc sc WHERE sc.cid=c.cid AND c.cname='语文' AND sc.score>=60
SELECT COUNT(*) FROM course c,sc sc WHERE sc.cid=c.cid AND c.cname='语文' AND sc.score>=70 AND sc.score<=80
SELECT COUNT(*) FROM course c,sc sc WHERE sc.cid=c.cid AND c.cname='语文' AND sc.score>=80 AND sc.score<=90
SELECT COUNT(*) FROM course c,sc sc WHERE sc.cid=c.cid AND c.cname='语文' AND sc.score>=90
-- 及格率
SELECT c.cid 课程序列, c.cname 课程名 ,
MAX(sc.score) 最高分,MIN(sc.score) 最低分,AVG(sc.score) 平均分,
(SELECT COUNT(*) FROM course c,sc sc WHERE sc.cid=c.cid AND c.cname='语文' AND sc.score>=60) 及格人数
FROM course c ,sc sc WHERE sc.cid=c.cid
GROUP BY c.cid
-- 改进
-- 及格人数
SELECT c.cid 课程序列, c.cname 课程名 ,
MAX(sc.score) 最高分,MIN(sc.score) 最低分,AVG(sc.score) 平均分,
(SELECT COUNT(*) FROM sc sc2 WHERE sc2.cid=sc.cid AND sc2.score>=60) 及格人数
FROM course c ,sc sc WHERE sc.cid=c.cid
GROUP BY c.cid
-- 总人数
SELECT c.cid 课程序列, c.cname 课程名 ,
MAX(sc.score) 最高分,MIN(sc.score) 最低分,AVG(sc.score) 平均分,
(SELECT COUNT(*) FROM sc sc2 WHERE sc2.cid=sc.cid) 总人数
FROM course c ,sc sc WHERE sc.cid=c.cid
GROUP BY c.cid
-- 及格率
SELECT c.cid 课程序列, c.cname 课程名 ,
MAX(sc.score) 最高分,MIN(sc.score) 最低分,AVG(sc.score) 平均分,
(SELECT COUNT(*) FROM sc sc2 WHERE sc2.cid=sc.cid AND sc2.score>=60)/(SELECT COUNT(*) FROM sc sc2 WHERE sc2.cid=sc.cid) 及格率
FROM course c ,sc sc WHERE sc.cid=c.cid
GROUP BY c.cid
-- 课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
SELECT c.cid 课程序列, c.cname 课程名 ,
MAX(sc.score) 最高分,MIN(sc.score) 最低分,AVG(sc.score) 平均分,
(SELECT COUNT(*) FROM sc sc2 WHERE sc2.cid=sc.cid AND sc2.score>=60)/(SELECT COUNT(*) FROM sc sc2 WHERE sc2.cid=sc.cid) 及格率,
(SELECT COUNT(*) FROM sc sc2 WHERE sc2.cid=sc.cid AND sc2.score>=70 AND sc2.score<=80)/(SELECT COUNT(*) FROM sc sc2 WHERE sc2.cid=sc.cid) 中等率,
(SELECT COUNT(*) FROM sc sc2 WHERE sc2.cid=sc.cid AND sc2.score>=80 AND sc2.score<=90)/(SELECT COUNT(*) FROM sc sc2 WHERE sc2.cid=sc.cid) 优良率,
(SELECT COUNT(*) FROM sc sc2 WHERE sc2.cid=sc.cid AND sc2.score>=90)/(SELECT COUNT(*) FROM sc sc2 WHERE sc2.cid=sc.cid) 优秀率
FROM course c ,sc sc WHERE sc.cid=c.cid
GROUP BY c.cid
-- 优化 sc2.score<=80 sc2.score<80 sc2.score<=90 sc2.score<90
SELECT c.cid 课程序列,
c.cname 课程名 ,
MAX(sc.score) 最高分,
MIN(sc.score) 最低分,
CAST(AVG(sc.score) AS DECIMAL(18,2)) 平均分,
CAST((SELECT COUNT(*) FROM sc sc2 WHERE sc2.cid=sc.cid AND sc2.score>=60)*100/(SELECT COUNT(*) FROM sc sc2 WHERE sc2.cid=sc.cid)AS DECIMAL(18,2)) 及格率,
CAST((SELECT COUNT(*) FROM sc sc2 WHERE sc2.cid=sc.cid AND sc2.score>=70 AND sc2.score<80)*100/(SELECT COUNT(*) FROM sc sc2 WHERE sc2.cid=sc.cid)AS DECIMAL(18,2)) 中等率,
CAST((SELECT COUNT(*) FROM sc sc2 WHERE sc2.cid=sc.cid AND sc2.score>=80 AND sc2.score<90)*100/(SELECT COUNT(*) FROM sc sc2 WHERE sc2.cid=sc.cid)AS DECIMAL(18,2)) 优良率,
CAST((SELECT COUNT(*) FROM sc sc2 WHERE sc2.cid=sc.cid AND sc2.score>=90)*100/(SELECT COUNT(*) FROM sc sc2 WHERE sc2.cid=sc.cid)AS DECIMAL(18,2)) 优秀率
FROM course c ,sc sc WHERE sc.cid=c.cid
GROUP BY c.cid
-- ref
-- 方法1
-- 在两张表(课程表 成绩表)的交叉查询下 新增第三张表(成绩表) 用于过滤成绩
SELECT m.CID 课程编号 , m.Cname 课程名称 ,
MAX(n.score) 最高分 ,
MIN(n.score) 最低分 ,
CAST(AVG(n.score) AS DECIMAL(18,2)) 平均分 ,
CAST((SELECT COUNT(1) FROM SC WHERE CID = m.CID AND score >= 60)*100.0 / (SELECT COUNT(1) FROM SC WHERE CID = m.CID) AS DECIMAL(18,2)) 及格率 ,
CAST((SELECT COUNT(1) FROM SC WHERE CID = m.CID AND score >= 70 AND score < 80 )*100.0 / (SELECT COUNT(1) FROM SC WHERE CID = m.CID) AS DECIMAL(18,2)) 中等率 ,
CAST((SELECT COUNT(1) FROM SC WHERE CID = m.CID AND score >= 80 AND score < 90 )*100.0 / (SELECT COUNT(1) FROM SC WHERE CID = m.CID) AS DECIMAL(18,2)) 优良率 ,
CAST((SELECT COUNT(1) FROM SC WHERE CID = m.CID AND score >= 90)*100.0 / (SELECT COUNT(1) FROM SC WHERE CID = m.CID) AS DECIMAL(18,2)) 优秀率
FROM Course m , SC n
WHERE m.CID = n.CID
GROUP BY m.CID , m.Cname
ORDER BY m.CID
-- 方法2
-- 在单表(课程表)查询的基础上 增加一张成绩表 用于过滤成绩 更为简洁 WHERE CID = m.CID 相当于 GROUP BY m.CID
SELECT m.CID 课程编号 , m.Cname 课程名称 ,
(SELECT MAX(score) FROM SC WHERE CID = m.CID) 最高分 ,
(SELECT MIN(score) FROM SC WHERE CID = m.CID) 最低分 ,
(SELECT CAST(AVG(score) AS DECIMAL(18,2)) FROM SC WHERE CID = m.CID) 平均分 ,
CAST((SELECT COUNT(1) FROM SC WHERE CID = m.CID AND score >= 60)*100.0 / (SELECT COUNT(1) FROM SC WHERE CID = m.CID) AS DECIMAL(18,2)) 及格率,
CAST((SELECT COUNT(1) FROM SC WHERE CID = m.CID AND score >= 70 AND score < 80 )*100.0 / (SELECT COUNT(1) FROM SC WHERE CID = m.CID) AS DECIMAL(18,2)) 中等率 ,
CAST((SELECT COUNT(1) FROM SC WHERE CID = m.CID AND score >= 80 AND score < 90 )*100.0 / (SELECT COUNT(1) FROM SC WHERE CID = m.CID) AS DECIMAL(18,2)) 优良率 ,
CAST((SELECT COUNT(1) FROM SC WHERE CID = m.CID AND score >= 90)*100.0 / (SELECT COUNT(1) FROM SC WHERE CID = m.CID) AS DECIMAL(18,2)) 优秀率
FROM Course m
ORDER BY m.CID
-- 举例分析
SELECT * FROM sc WHERE sc.cid=01
SELECT *,
(SELECT MAX(sc.score) FROM sc WHERE sc.cid=1) 语文最高分
FROM course c
SELECT *,
(SELECT MAX(sc.score) FROM sc WHERE sc.cid=2) 数学最高分
FROM course c
SELECT *,
(SELECT MAX(sc.score) FROM sc WHERE sc.cid=3) 外语最高分
FROM course c
SELECT *,
(SELECT MAX(sc.score) FROM sc WHERE sc.cid=c.cid) 各科最高分
FROM course c
SELECT *,
(SELECT MAX(sc.score) FROM sc WHERE sc.cid=c.cid AND c.cname='语文') 语文最高分
FROM course c
-- 查询语文最高分
SELECT *,(SELECT MAX(sc.score) FROM sc sc WHERE sc.cid=c.cid AND c.cname='语文')
FROM course c
-- 各科最高分
SELECT *,(SELECT MAX(sc.score) FROM sc sc WHERE sc.cid=c.cid)
FROM course c
-- 19、按各科成绩进行排序，并显示排名
-- 查询所有
SELECT * FROM sc sc,course c WHERE sc.cid=c.cid
-- 查询语文成绩
SELECT *,sc.score 语文
FROM sc sc,course c WHERE sc.cid=c.cid AND c.cname='语文'
ORDER BY sc.score DESC
-- 单表查询语文成绩
SELECT * FROM sc sc WHERE sc.cid=01
SELECT *,sc.score 语文,
(SELECT COUNT(*) FROM sc sc2
WHERE sc2.score>sc.score AND sc2.cid=c.cid)+1 rank
FROM sc sc,course c WHERE sc.cid=c.cid AND c.cname='语文'
ORDER BY sc.score DESC
-- ref
--Score重复时保留名次空缺
SELECT t.* , px = (SELECT COUNT(1) FROM SC WHERE CID = t.CID AND score > t.score) + 1 FROM sc t ORDER BY t.cid , px
--Score重复时合并名次
SELECT t.* , px = (SELECT COUNT(DISTINCT score) FROM SC WHERE CID = t.CID AND score >= t.score) FROM sc t ORDER BY t.cid , px
-- 方式二
-- 为什么这样写结果跟预期的不一样呢
SET @rank:=0;
SELECT *,sc.score 语文,
@rank:=@rank+1 AS rank
FROM sc sc,course c
WHERE sc.cid=c.cid AND c.cname='语文'
ORDER BY sc.score DESC
-- 这样写得不到排名的原因分析
-- 先执行
SET @rank:=0;
SELECT *,sc.score 语文,
@rank:=@rank+1 AS rank
FROM sc sc,course c
WHERE sc.cid=c.cid AND c.cname='语文'
-- 执行结果 rank的值即为查询的顺序 rank已经取值
-- 再执行
SET @rank:=0;
SELECT *,sc.score 语文,
@rank:=@rank+1 AS rank
FROM sc sc,course c
WHERE sc.cid=c.cid AND c.cname='语文'
ORDER BY sc.score DESC
-- rank的值并没有改变 只是随着成绩排序改变了位置
-- 观察结果对比
-- SQL的执行顺序是先查询,再排序
-- 所以变量的值即为查询的顺序
-- 20、查询学生的总成绩并进行排名
-- 查询学生的成绩
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid
SELECT * FROM student s LEFT JOIN sc sc ON s.sid=sc.sid
-- 查询学生的总成绩
SELECT *,SUM(sc.score) total_score FROM student s,sc sc WHERE s.sid=sc.sid
GROUP BY s.sid
-- 学生的总成绩降序排列
SELECT *,SUM(sc.score) total_score FROM student s LEFT JOIN sc sc ON s.sid=sc.sid
GROUP BY s.sid
ORDER BY total_score DESC
-- 学生的总成绩
SELECT s.sid,s.sname,IFNULL(SUM(sc1.score),0) total_score FROM student s LEFT JOIN sc sc1 ON s.sid=sc1.sid
GROUP BY s.sid
ORDER BY total_score DESC
-- 学生的总成绩排名
-- 排名思路 这条记录的总成绩与所有记录总成绩比较 统计数量作为排名
-- 1.创建第一张记录学生ID,姓名与总成绩的表t1,作为基表
SELECT * FROM () t1
SELECT * FROM
(SELECT s.sid,s.sname,IFNULL(SUM(sc1.score),0) total_score FROM student s LEFT JOIN sc sc1 ON s.sid=sc1.sid
GROUP BY s.sid
ORDER BY total_score DESC) t1
-- 2.在基表上筛选 创建第二张记录学生ID,姓名与总成绩的表t2,作为临时表,与基表比较
-- 这里t2表的所有记录的总成绩会与t1表当前记录的总成绩比较 得到数量 拼接到当前记录(当前行)的rank字段
-- 可以理解为表记录也是一行一行查出来的,实际上rank与前面的字段本来就是并列的
SELECT *,(SELECT COUNT(1) FROM () t2 WHERE t2.total_score>t1.total_score)rank FROM () t1
SELECT *,(
SELECT COUNT(1) FROM (
SELECT s.sid,s.sname,IFNULL(SUM(sc1.score),0) total_score FROM student s LEFT JOIN sc sc1 ON s.sid=sc1.sid
GROUP BY s.sid
ORDER BY total_score DESC
) t2 WHERE t2.total_score>t1.total_score
)rank
FROM (SELECT s.sid,s.sname,IFNULL(SUM(sc1.score),0) total_score FROM student s LEFT JOIN sc sc1 ON s.sid=sc1.sid
GROUP BY s.sid
ORDER BY total_score DESC) t1
-- 3.将结果rank排序
SELECT *,(SELECT COUNT(1) FROM () t2 WHERE t2.total_score>t1.total_score)rank FROM () t1 ORDER BY rank DESC
SELECT *,(
SELECT COUNT(1) FROM (
SELECT s.sid,s.sname,IFNULL(SUM(sc1.score),0) total_score FROM student s LEFT JOIN sc sc1 ON s.sid=sc1.sid
GROUP BY s.sid
ORDER BY total_score DESC
) t2 WHERE t2.total_score>t1.total_score
) rank
FROM (SELECT s.sid,s.sname,IFNULL(SUM(sc1.score),0) total_score FROM student s LEFT JOIN sc sc1 ON s.sid=sc1.sid
GROUP BY s.sid
ORDER BY total_score DESC) t1
ORDER BY rank
-- 其实由于t1,t2表的总成绩已经排序 这里rank其实不再需要排序 但这样效率较低
-- 优化t1,t2表的总成绩不需要排序 最后排序即可
-- t2 表不需要学生ID和姓名
-- -- 分数重复时保留名次空缺
SELECT t1.*,(
SELECT COUNT(t2.total_score) FROM (
SELECT IFNULL(SUM(sc1.score),0) total_score FROM student s LEFT JOIN sc sc1 ON s.sid=sc1.sid
GROUP BY s.sid) t2 WHERE t2.total_score>t1.total_score
)+1 rank
FROM (
SELECT s.sid,s.sname,IFNULL(SUM(sc1.score),0) total_score FROM student s LEFT JOIN sc sc1 ON s.sid=sc1.sid
GROUP BY s.sid) t1
ORDER BY rank
-- 分数重复时合并名次
SELECT t1.*,(
SELECT COUNT(DISTINCT t2.total_score) FROM (
SELECT IFNULL(SUM(sc1.score),0) total_score FROM student s LEFT JOIN sc sc1 ON s.sid=sc1.sid
GROUP BY s.sid) t2 WHERE t2.total_score>t1.total_score
)+1 rank
FROM (
SELECT s.sid,s.sname,IFNULL(SUM(sc1.score),0) total_score FROM student s LEFT JOIN sc sc1 ON s.sid=sc1.sid
GROUP BY s.sid) t1
ORDER BY rank
-- 方式二
-- 错误写法
SET @rank:=0;
SELECT *,SUM(sc.score) total_score,@rank:=@rank+1 rank FROM student s
LEFT JOIN sc sc ON s.sid=sc.sid
GROUP BY s.sid
ORDER BY total_score DESC
-- 这样写得不到排名的原因分析
SET @rank:=0;
SELECT *,SUM(sc.score) total_score,@rank:=@rank+1 rank FROM student s
LEFT JOIN sc sc ON s.sid=sc.sid
GROUP BY s.sid
-- 分开执行
SET @rank:=0;
SELECT *,SUM(sc.score) total_score,@rank:=@rank+1 rank FROM student s
LEFT JOIN sc sc ON s.sid=sc.sid
GROUP BY s.sid
ORDER BY total_score DESC
-- 正确写法
SET @rank:=0;
SELECT *, @rank:=@rank+1 rank
FROM (SELECT s.sid,s.sname,IFNULL(SUM(sc1.score),0) total_score FROM student s LEFT JOIN sc sc1 ON s.sid=sc1.sid
GROUP BY s.sid
ORDER BY total_score DESC) t1
-- 普通排名 12345
-- 为了初始化变量rank 新建了一张表 t3
SELECT t1.*, @rank:=@rank+1 AS rank
FROM (SELECT s.sid,s.sname,IFNULL(SUM(sc1.score),0) total_score FROM student s LEFT JOIN sc sc1 ON s.sid=sc1.sid
GROUP BY s.sid
ORDER BY total_score DESC) t1,(SELECT @rank := 0) t3
-- 分数重复时合并名次 1 2 2 3 4
SELECT t1.*,
CASE
WHEN @prevRank = total_score THEN @curRank
WHEN @prevRank := total_score THEN @curRank := @curRank + 1
END AS rank
FROM (SELECT s.sid,s.sname,IFNULL(SUM(sc1.score),0) total_score FROM student s LEFT JOIN sc sc1 ON s.sid=sc1.sid
GROUP BY s.sid
ORDER BY total_score DESC) t1,(SELECT @curRank :=0, @prevRank := NULL) t3
-- 分数重复时保留名次空缺 1 2 2 4 5
SELECT *,rank FROM
(SELECT t1.*,
@curRank := IF(@prevRank = total_score, @curRank, @incRank) AS rank,
@incRank := @incRank + 1,
@prevRank := total_score
FROM (SELECT s.sid,s.sname,IFNULL(SUM(sc1.score),0) total_score FROM student s LEFT JOIN sc sc1 ON s.sid=sc1.sid
GROUP BY s.sid
ORDER BY total_score DESC) t1, (SELECT @curRank :=0, @prevRank := NULL, @incRank := 1) r
) s
SELECT sid,sname,total_score,rank FROM
(SELECT t1.*,
@curRank := IF(@prevRank = total_score, @curRank, @incRank) AS rank,
@incRank := @incRank + 1,
@prevRank := total_score
FROM (SELECT s.sid,s.sname,IFNULL(SUM(sc1.score),0) total_score FROM student s LEFT JOIN sc sc1 ON s.sid=sc1.sid
GROUP BY s.sid
ORDER BY total_score DESC) t1, (SELECT @curRank :=0, @prevRank := NULL, @incRank := 1) r
) s
-- ref
-- 分数重复时保留名次空缺
SELECT t1.*,
(
SELECT COUNT(1) FROM
(
SELECT m.SID 学生编号 ,
m.Sname 学生姓名 ,
IFNULL(SUM(score),0) 总成绩
FROM Student m LEFT JOIN SC n ON m.SID = n.SID
GROUP BY m.SID , m.Sname
) t2 WHERE 总成绩 > t1.总成绩
)+1 rank
FROM
(
SELECT m.SID 学生编号 ,
m.Sname 学生姓名 ,
IFNULL(SUM(score),0) 总成绩
FROM Student m LEFT JOIN SC n ON m.SID = n.SID
GROUP BY m.SID , m.Sname
) t1
ORDER BY rank
-- 分数重复时合并名次
SELECT t1.*,
(
SELECT COUNT(DISTINCT 总成绩) FROM
(
SELECT m.SID 学生编号, m.Sname 学生姓名,IFNULL(SUM(score),0) 总成绩
FROM Student m LEFT JOIN SC n ON m.SID = n.SID
GROUP BY m.SID , m.Sname
) t2 WHERE 总成绩 >= t1.总成绩
)rank
FROM
(
SELECT m.SID 学生编号, m.Sname 学生姓名,IFNULL(SUM(score),0) 总成绩
FROM Student m LEFT JOIN SC n ON m.SID = n.SID
GROUP BY m.SID , m.Sname
) t1
ORDER BY rank
-- 21、查询不同老师所教不同课程平均分从高到低显示
-- 查询不同老师所教的课程
SELECT * FROM teacher t,course c,sc sc1 WHERE t.tid=c.tid AND c.cid=sc1.cid ORDER BY t.tid
-- 查询不同老师所交不同课程的平均分
SELECT t.tid,t.tname,AVG(score) avg_score FROM teacher t,course c,sc sc1
WHERE t.tid=c.tid AND c.cid=sc1.cid GROUP BY t.tid ORDER BY avg_score DESC
-- ref
SELECT m.TID , m.Tname , CAST(AVG(o.score) AS DECIMAL(18,2)) avg_score
FROM Teacher m , Course n , SC o
WHERE m.TID = n.TID AND n.CID = o.CID
GROUP BY m.TID , m.Tname
ORDER BY avg_score DESC
-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
-- 1.查询
-- 1.查询所有需要的信息(学生表 课程表 成绩表)作为基础表 注意:基础表的列名不能相同 否则从基础表拿数据会冲突
SELECT * FROM student s,course c,sc sc WHERE s.sid=sc.sid AND c.cid=sc.cid
SELECT * FROM student s LEFT JOIN sc sc ON s.sid=sc.sid LEFT JOIN course c ON c.cid=sc.cid
-- 2.筛选
-- 2.1 筛选指定课程信息
SELECT * FROM student s,course c,sc sc WHERE s.sid=sc.sid AND c.cid=sc.cid AND c.cname = '语文'
-- 2.1 指定课程排序
SELECT * FROM student s,course c,sc sc WHERE s.sid=sc.sid AND c.cid=sc.cid AND c.cname = '语文'
ORDER BY sc.score DESC
-- 2.1 指定课程排名
-- 产生名次
-- 思路-伪SQL
SELECT COUNT() FROM () t1 WHERE t1.score>b1.score
SELECT *,() rank FROM () b1
-- 方式一
-- 1 1 3 4 5
SELECT b1.sid,b1.sname,b1.cname,b1.score,
(SELECT COUNT(*) FROM (SELECT score FROM student s,course c,sc sc WHERE s.sid=sc.sid AND c.cid=sc.cid AND c.cname = '语文') t1 WHERE t1.score>b1.score)+1 rank
FROM (SELECT s.sid,sname,cname,score FROM student s,course c,sc sc WHERE s.sid=sc.sid AND c.cid=sc.cid AND c.cname = '语文'
ORDER BY sc.score DESC ) b1
-- 1 1 2 3 4
SELECT b1.sid,b1.sname,b1.cname,b1.score,
(SELECT COUNT(DISTINCT score) FROM (SELECT score FROM student s,course c,sc sc WHERE s.sid=sc.sid AND c.cid=sc.cid AND c.cname = '语文') t1 WHERE t1.score>b1.score)+1 rank
FROM (SELECT s.sid,sname,cname,score FROM student s,course c,sc sc WHERE s.sid=sc.sid AND c.cid=sc.cid AND c.cname = '语文'
ORDER BY sc.score DESC ) b1
-- 方式二
-- 1 2 3 4 5
SET @rank:=0;
SELECT b1.sid,b1.sname,b1.cname,b1.score,
@rank:=@rank+1 rank
FROM (SELECT s.sid,sname,cname,score FROM student s,course c,sc sc WHERE s.sid=sc.sid AND c.cid=sc.cid AND c.cname = '语文'
ORDER BY sc.score DESC ) b1
-- 1 2 3 4 5
SELECT b1.sid,b1.sname,b1.cname,b1.score,
@rank:=@rank+1 rank
FROM (SELECT s.sid,sname,cname,score FROM student s,course c,sc sc WHERE s.sid=sc.sid AND c.cid=sc.cid AND c.cname = '语文'
ORDER BY sc.score DESC ) b1,(SELECT @rank:=0) p
-- 2.2 第2名到第3名的学生信息及该课程成绩
SELECT * FROM() t2 WHERE rank<4 AND rank >1
SELECT * FROM(SELECT b1.sid,b1.sname,b1.cname,b1.score,
(SELECT COUNT(DISTINCT score) FROM (SELECT score FROM student s,course c,sc sc WHERE s.sid=sc.sid AND c.cid=sc.cid AND c.cname = '语文') t1 WHERE t1.score>b1.score)+1 rank
FROM (SELECT s.sid,sname,cname,score FROM student s,course c,sc sc WHERE s.sid=sc.sid AND c.cid=sc.cid AND c.cname = '语文'
ORDER BY sc.score DESC ) b1) t2 WHERE rank<4 AND rank >1
SELECT * FROM(SELECT b1.sid,b1.sname,b1.cname,b1.score,
@rank:=@rank+1 rank
FROM (SELECT s.sid,sname,cname,score FROM student s,course c,sc sc WHERE s.sid=sc.sid AND c.cid=sc.cid AND c.cname = '语文'
ORDER BY sc.score DESC ) b1,(SELECT @rank:=0) p) t2 WHERE rank<4 AND rank >1
-- 指定科目查出再查所有科目
-- 这种写法使用UNION ALL效率很低,有很多重复代码,纯手工打造,当科目很多时,根本无法手工列举
SELECT * FROM(SELECT b1.sid,b1.sname,b1.cname,b1.score,
@rank:=@rank+1 rank
FROM (SELECT s.sid,sname,cname,score FROM student s,course c,sc sc WHERE s.sid=sc.sid AND c.cid=sc.cid AND c.cname = '语文'
ORDER BY sc.score DESC ) b1,(SELECT @rank:=0) p) t2 WHERE rank<4 AND rank >1
UNION ALL
SELECT * FROM(SELECT b1.sid,b1.sname,b1.cname,b1.score,
@rank:=@rank+1 rank
FROM (SELECT s.sid,sname,cname,score FROM student s,course c,sc sc WHERE s.sid=sc.sid AND c.cid=sc.cid AND c.cname = '数学'
ORDER BY sc.score DESC ) b1,(SELECT @rank:=0) p) t2 WHERE rank<4 AND rank >1
UNION ALL
SELECT * FROM(SELECT b1.sid,b1.sname,b1.cname,b1.score,
@rank:=@rank+1 rank
FROM (SELECT s.sid,sname,cname,score FROM student s,course c,sc sc WHERE s.sid=sc.sid AND c.cid=sc.cid AND c.cname = '英语'
ORDER BY sc.score DESC ) b1,(SELECT @rank:=0) p) t2 WHERE rank<4 AND rank >1
SELECT * FROM(SELECT b1.sid,b1.sname,b1.cname,b1.score,
(SELECT COUNT(DISTINCT score) FROM (SELECT score FROM student s,course c,sc sc WHERE s.sid=sc.sid AND c.cid=sc.cid AND c.cname = '语文') t1 WHERE t1.score>b1.score)+1 rank
FROM (SELECT s.sid,sname,cname,score FROM student s,course c,sc sc WHERE s.sid=sc.sid AND c.cid=sc.cid AND c.cname = '语文'
ORDER BY sc.score DESC ) b1) t2 WHERE rank<4 AND rank >1
union all
SELECT * FROM(SELECT b1.sid,b1.sname,b1.cname,b1.score,
(SELECT COUNT(DISTINCT score) FROM (SELECT score FROM student s,course c,sc sc WHERE s.sid=sc.sid AND c.cid=sc.cid AND c.cname = '数学') t1 WHERE t1.score>b1.score)+1 rank
FROM (SELECT s.sid,sname,cname,score FROM student s,course c,sc sc WHERE s.sid=sc.sid AND c.cid=sc.cid AND c.cname = '数学'
ORDER BY sc.score DESC ) b1) t2 WHERE rank<4 AND rank >1
union all
SELECT * FROM(SELECT b1.sid,b1.sname,b1.cname,b1.score,
(SELECT COUNT(DISTINCT score) FROM (SELECT score FROM student s,course c,sc sc WHERE s.sid=sc.sid AND c.cid=sc.cid AND c.cname = '英语') t1 WHERE t1.score>b1.score)+1 rank
FROM (SELECT s.sid,sname,cname,score FROM student s,course c,sc sc WHERE s.sid=sc.sid AND c.cid=sc.cid AND c.cname = '英语'
ORDER BY sc.score DESC ) b1) t2 WHERE rank<4 AND rank >1
-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
-- 查询所有课程成绩
SELECT t.* FROM sc t
-- 将成绩按课程分类 然后排名
SELECT t.*,() rank FROM sc t
-- 使用order by cid 显然不行 我们是为了将产生的多个结果分类 而不是分组聚合之后只产生一个结果
-- 我们可以使用一张临时表 通过 sc.cid = t.cid 达到分类的效果
-- Operand should contain 1 column(s)
-- 我们想查出排名这一列(字段),所以不能返多列(字段)
SELECT t.*,(
SELECT * FROM sc WHERE sc.cid= t.cid
) rank FROM sc t
-- Subquery returns more than 1 row
-- rank与前面的数据组成一行(记录) 所以不能返回一列
-- 可以把SQL理解为一个函数 select后的是函数的返回值 from和where组成参数参数列表
SELECT t.*,(
SELECT cid FROM sc WHERE sc.cid= t.cid
) rank FROM sc t
-- 只能返回一个数据
SELECT t.*,(
SELECT COUNT(1) FROM sc WHERE sc.cid= t.cid
) rank FROM sc t
-- 排名
-- 产生名次
SELECT t.*,(
SELECT COUNT(1) FROM sc WHERE sc.cid= t.cid AND sc.score>t.score
) rank FROM sc t
-- 按名次排序
SELECT * FROM () t2
-- 先按cid 排序 cid相同再按rank排序
SELECT * FROM () t2 ORDER BY t2.cid,t2.rank
SELECT * FROM (
SELECT t.*,(SELECT COUNT(1) FROM sc WHERE sc.cid= t.cid AND sc.score>t.score) rank FROM sc t
) t2 ORDER BY t2.cid,t2.rank
-- 筛选出2到3名
SELECT * FROM () t3 WHERE t3.rank BETWEEN 2 AND 3
SELECT * FROM (SELECT * FROM (
SELECT t.*,(SELECT COUNT(1) FROM sc WHERE sc.cid= t.cid AND sc.score>t.score) rank FROM sc t
) t2 ORDER BY t2.cid,t2.rank
) t3 WHERE t3.rank BETWEEN 2 AND 3
-- 关联学生表 课程表 显示相关信息
SELECT * FROM () t4,student s,course c WHERE t4.sid=s.sid AND t4.cid = c.cid
SELECT sname,cname,t4.* FROM (SELECT * FROM (SELECT * FROM (
SELECT t.*,(SELECT COUNT(1) FROM sc WHERE sc.cid= t.cid AND sc.score>t.score) rank FROM sc t
) t2 ORDER BY t2.cid,t2.rank
) t3 WHERE t3.rank BETWEEN 2 AND 3) t4,student s,course c WHERE t4.sid=s.sid AND t4.cid = c.cid
-- 排序整理
SELECT sname,cname,t4.* FROM (SELECT * FROM (SELECT * FROM (
SELECT t.*,(SELECT COUNT(1) FROM sc WHERE sc.cid= t.cid AND sc.score>t.score) rank FROM sc t
) t2 ORDER BY t2.cid,t2.rank
) t3 WHERE t3.rank BETWEEN 2 AND 3) t4,student s,course c WHERE t4.sid=s.sid AND t4.cid = c.cid
ORDER BY t4.cid,t4.rank
-- ref
SELECT (SELECT COUNT(1) FROM SC WHERE CID = t.CID AND score > t.score) + 1 px FROM sc t
SELECT t.* , (SELECT COUNT(1) FROM SC WHERE CID = t.CID AND score > t.score) + 1 px FROM sc t
SELECT * FROM
(SELECT t.* , (SELECT COUNT(1) FROM SC WHERE CID = t.CID AND score > t.score) + 1 px FROM sc t) m
WHERE px BETWEEN 2 AND 3 ORDER BY m.cid , m.px
SELECT * FROM
(SELECT t.* , (SELECT COUNT(DISTINCT score) FROM SC WHERE CID = t.CID AND score > t.score) + 1 px FROM sc t) m
WHERE px BETWEEN 2 AND 3 ORDER BY m.cid , m.px
SELECT * FROM student s,course c,() sc WHERE s.sid=sc.sid AND c.cid=sc.cid
SELECT sname,cname,t2.* FROM student s,course c,(SELECT * FROM
(SELECT t.* , (SELECT COUNT(DISTINCT score) FROM SC WHERE CID = t.CID AND score > t.score) + 1 px FROM sc t) m
WHERE px BETWEEN 2 AND 3 ORDER BY m.cid , m.px ) t2 WHERE s.sid=t2.sid AND c.cid=t2.cid
ORDER BY t2.cid,t2.px
SELECT sname,cname,t2.* FROM student s,course c,(SELECT * FROM
(
SELECT t.* , (SELECT COUNT(1) FROM SC WHERE CID = t.CID AND score > t.score) + 1 px FROM sc t
ORDER BY t.cid , px ) m
WHERE px BETWEEN 2 AND 3 ) t2 WHERE s.sid=t2.sid AND c.cid=t2.cid
ORDER BY t2.cid,t2.px
22.查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
思路
1产生排名 ==>t1
2查询第二名到第三名的成绩 ==>t2
3关联学生表,课程表与t2显示相关学生信息
1产生排名 ==>t1
select *,() rank from sc sc2 order by sc2.cid,sc2.score desc
select *,(select count(score) from sc sc1 where sc1.cid=sc2.cid and sc1.score>sc2.score) rank from sc sc2
2查询第二名到第三名的成绩 ==>t2
select * from () t1 where rank between 2 and 3
select * from (select *,(select count(score) from sc sc1 where sc1.cid=sc2.cid and sc1.score>sc2.score) rank from sc sc2) t1 where rank between 2 and 3
3关联学生表,课程表与t2显示相关学生信息
select s.sname,c.cname,t2.* from student s ,course c,(select * from (select *,(select count(score) from sc sc1 where sc1.cid=sc2.cid and sc1.score>sc2.score) rank from sc sc2 ) t1 where rank between 2 and 3
) t2 where s.sid = t2.sid and c.cid = t2.cid order by t2.cid ,t2.rank
-- 23、统计各科成绩各分数段人数：课程编号,课程名称, 100-85 , 85-70 , 70-60 , 0-60 及所占百分比
课程编号,课程名称, 100-85 , 85-70 , 70-60 , 0-60 及所占百分比
思路
1.观察表结构需要新增多列,先统计各分段人数
2.再算出所占百分比
3.关联其它表增加更多列信息
1.观察表结构需要新增多列,先统计各分段人数
-- 查询所有成绩
select * from sc
-- 增加列 统计各分段人数
select *,() 100-85 from sc
select *,(select count(sc.score) from sc where sc.cid = sc1.cid and sc.score >= 85 and sc.score <= 100) '100-85' from sc sc1
group by sc1.cid
select *,
(select count(sc.score) from sc where sc.cid = sc1.cid and sc.score between 85 and 100) '100-85',
(select count(sc.score) from sc where sc.cid = sc1.cid and sc.score between 70 and 85) '85-70',
(select count(sc.score) from sc where sc.cid = sc1.cid and sc.score between 60 and 70) '70-60',
(select count(sc.score) from sc where sc.cid = sc1.cid and sc.score between 0 and 60) '0-60'
from sc sc1
group by sc1.cid
改进 between 85 and 100 相当于 sc.score >= 85 and sc.score <= 100 与下个分段会出现重复
select *,
(select count(sc.score) from sc where sc.cid = sc1.cid and sc.score >= 85) '100-85',
(select count(sc.score) from sc where sc.cid = sc1.cid and sc.score >= 70 and sc.score < 85) '85-70',
(select count(sc.score) from sc where sc.cid = sc1.cid and sc.score >= 60 and sc.score < 70) '70-60',
(select count(sc.score) from sc where sc.cid = sc1.cid and sc.score < 60) '0-60'
from sc sc1
group by sc1.cid
方式二(横向)
select sc.* from sc group by sc.cid
select *,
sum(case when sc.score >= 85 then 1 else 0 end) `100-85`
from sc group by sc.cid
select sc.cid,
sum(case when sc.score >= 85 then 1 else 0 end) `85-100`,
sum(case when sc.score >= 70 and sc.score < 85 then 1 else 0 end) `70-85`,
sum(case when sc.score >= 60 and sc.score < 70 then 1 else 0 end) `60-70`,
sum(case when sc.score < 60 then 1 else 0 end) `0-60`
from sc group by sc.cid
方式三(纵向)
select sc.*,sc.cid `课程编号`,course.cname `课程名称`,
case
when sc.score >= 85 then '85-100人数'
when sc.score >= 70 and sc.score <= 85 then '70-85人数'
when sc.score >= 60 and sc.score <= 70 then '60-70人数'
else '0-60人数'
end
from sc left join course on sc.cid = course.cid
select sc.cid `课程编号`,course.cname `课程名称`,
(case
when sc.score >= 85 then '85-100人数'
when sc.score >= 70 and sc.score <= 85 then '70-85人数'
when sc.score >= 60 and sc.score <= 70 then '60-70人数'
else '0-60人数'
end) `分段`,
count(1)
from sc left join course on sc.cid = course.cid
group by
case
when sc.score >= 85 then '85-100人数'
when sc.score >= 70 and sc.score <= 85 then '70-85人数'
when sc.score >= 60 and sc.score <= 70 then '60-70人数'
else '0-60人数'
end,sc.cid
order by
sc.cid,`分段`
2.再算出所占百分比
select sc1.cid,
(select count(sc.score) from sc where sc.cid = sc1.cid and sc.score >= 85) '100-85',
(select count(sc.score) from sc where sc.cid = sc1.cid and sc.score >= 85)/(select count(sc.score) from sc where sc.cid = sc1.cid)*100 百分比,
(select count(sc.score) from sc where sc.cid = sc1.cid and sc.score >= 70 and sc.score < 85) '85-70',
(select count(sc.score) from sc where sc.cid = sc1.cid and sc.score >= 70 and sc.score < 85)/(select count(sc.score) from sc where sc.cid = sc1.cid)*100 百分比,
(select count(sc.score) from sc where sc.cid = sc1.cid and sc.score >= 60 and sc.score < 70) '70-60',
(select count(sc.score) from sc where sc.cid = sc1.cid and sc.score >= 60 and sc.score < 70)/(select count(sc.score) from sc where sc.cid = sc1.cid)*100 百分比,
(select count(sc.score) from sc where sc.cid = sc1.cid and sc.score < 60) '0-60',
(select count(sc.score) from sc where sc.cid = sc.score < 60)/(select count(sc.score) from sc where sc.cid = sc1.cid)*100 百分比
from sc sc1
group by sc1.cid
3.关联其它表增加更多列信息
select sc1.cid,c.cname,
(select count(sc.score) from sc where sc.cid = sc1.cid and sc.score >= 85) '100-85',
(select count(sc.score) from sc where sc.cid = sc1.cid and sc.score >= 85)/(select count(sc.score) from sc where sc.cid = sc1.cid)*100 百分比,
(select count(sc.score) from sc where sc.cid = sc1.cid and sc.score >= 70 and sc.score < 85) '85-70',
(select count(sc.score) from sc where sc.cid = sc1.cid and sc.score >= 70 and sc.score < 85)/(select count(sc.score) from sc where sc.cid = sc1.cid)*100 百分比,
(select count(sc.score) from sc where sc.cid = sc1.cid and sc.score >= 60 and sc.score < 70) '70-60',
(select count(sc.score) from sc where sc.cid = sc1.cid and sc.score >= 60 and sc.score < 70)/(select count(sc.score) from sc where sc.cid = sc1.cid)*100 百分比,
(select count(sc.score) from sc where sc.cid = sc1.cid and sc.score < 60) '0-60',
(select count(sc.score) from sc where sc.cid = sc1.cid and sc.score between 0 and 60)/(select count(sc.score) from sc where sc.cid = sc1.cid)*100 百分比
from sc sc1 left join course c on sc1.cid=c.cid
group by sc1.cid
cast(() as decimal(5,2))
select sc1.cid,c.cname,
(select count(sc.score) from sc where sc.cid = sc1.cid and sc.score >= 85) '100-85',
cast(((select count(sc.score) from sc where sc.cid = sc1.cid and sc.score >= 85)/(select count(sc.score) from sc where sc.cid = sc1.cid)*100) as decimal(5,2)) 百分比,
(select count(sc.score) from sc where sc.cid = sc1.cid and sc.score >= 70 and sc.score < 85) '85-70',
cast(((select count(sc.score) from sc where sc.cid = sc1.cid and sc.score >= 70 and sc.score < 85)/(select count(sc.score) from sc where sc.cid = sc1.cid)*100) as decimal(5,2)) 百分比,
(select count(sc.score) from sc where sc.cid = sc1.cid and sc.score >= 60 and sc.score < 70) '70-60',
cast(((select count(sc.score) from sc where sc.cid = sc1.cid and sc.score >= 60 and sc.score < 70)/(select count(sc.score) from sc where sc.cid = sc1.cid)*100) as decimal(5,2)) 百分比,
(select count(sc.score) from sc where sc.cid = sc1.cid and sc.score < 60) '0-60',
cast(((select count(sc.score) from sc where sc.cid = sc1.cid and sc.score between 0 and 60)/(select count(sc.score) from sc where sc.cid = sc1.cid)*100) as decimal(5,2)) 百分比
from sc sc1 left join course c on sc1.cid=c.cid
group by sc1.cid
--ref
select course.cid 课程编号 , cname 课程名称 ,
sum(case when score >= 85 then 1 else 0 end) `85-100` ,
sum(case when score >= 70 and score < 85 then 1 else 0 end) `70-85` ,
sum(case when score >= 60 and score < 70 then 1 else 0 end) `60-70` ,
sum(case when score < 60 then 1 else 0 end) `0-60`
from sc , course
where sc.cid = course.cid
group by course.cid , course.cname
order by course.cid
select m.cid 课程编号 , m.cname 课程名称 , (
case when n.score >= 85 then '85-100'
when n.score >= 70 and n.score < 85 then '70-85'
when n.score >= 60 and n.score < 70 then '60-70'
else '0-60'
end) 分数段,
count(1) 数量
from course m , sc n
where m.cid = n.cid
group by m.cid , m.cname , (
case when n.score >= 85 then '85-100'
when n.score >= 70 and n.score < 85 then '70-85'
when n.score >= 60 and n.score < 70 then '60-70'
else '0-60'
end)
order by m.cid , m.cname , 分数段
-- 24、查询学生平均成绩及其名次
思路
1.统计平均成绩=>t1
2.排名
3.关联学生表,显示相关信息
1.统计平均成绩 =>t1
select *,avg(sc.score) avg from sc sc group by sc.sid
select *,cast(avg(sc.score) as decimal(4,2)) avg from sc sc group by sc.sid
2.按平均成绩排名
方式一:
select count(score) from () t2 where t2.avg>t1.avg
select *,() rank from ()t1
select *,
(select count(avg) from (select cast(avg(sc.score) as decimal(4,2)) avg from sc sc group by sc.sid) t2 where t2.avg>t1.avg) rank
from (select *,cast(avg(sc.score) as decimal(4,2)) avg from sc sc group by sc.sid)t1
方式二:
select * from ()
3.关联学生表,显示相关信息
select
s.sname,
t1.sid,
t1.avg,
(select count(avg) from (select cast(avg(sc.score) as decimal(4,2)) avg from sc sc group by sc.sid) t2 where t2.avg>t1.avg)+1 rank
from
(select *,cast(avg(sc.score) as decimal(4,2)) avg from sc sc group by sc.sid) t1 left join student s on t1.sid = s.sid
order by rank
-- ref 这是一个反面例子 内连接不加条件(相当于交叉连接) 会产生的笛卡尔积查询 加卡尔积是多表记录的乘积 这样数据量大 显然效率极低
SELECT a.sid,
a.avg_score,
sum(case when a.avg_score<b.avg_score then 1 else 0 end)+1 scc
FROM (SELECT sid,avg(score) avg_score FROM SC GROUP BY sid) a INNER JOIN
(SELECT sid,avg(score) avg_score FROM SC GROUP BY sid) b
GROUP BY a.sid,
a.avg_score
ORDER BY scc;
-- 内连接不加条件,相当于交叉连接,INNER JOIN 可以去去掉 下面的查询结果与上面是一样的
SELECT a.sid,
a.avg_score,
sum(case when a.avg_score<b.avg_score then 1 else 0 end)+1 scc
FROM (SELECT sid,avg(score) avg_score FROM SC GROUP BY sid) a,
(SELECT sid,avg(score) avg_score FROM SC GROUP BY sid) b
GROUP BY a.sid,
a.avg_score
ORDER BY scc;
-- 过程分析
SELECT a.*,b.*
FROM (SELECT sid,AVG(score) avg_score FROM SC GROUP BY sid) a INNER JOIN
(SELECT sid,AVG(score) avg_score FROM SC GROUP BY sid) b
-- (49 row(s) returned
SELECT a.*,b.*,
(CASE WHEN a.avg_score<b.avg_score THEN 1 ELSE 0 END) scc
FROM (SELECT sid,AVG(score) avg_score FROM SC GROUP BY sid) a INNER JOIN
(SELECT sid,AVG(score) avg_score FROM SC GROUP BY sid) b
-- (49 row(s) returned
-- 25、查询各科成绩前三名的记录
思路
1排名 =>t1
2选出前三名
1排名 =>t1
select *,() rank from sc s2
select *,(select count(s1.score) from sc s1 where s1.cid=s2.cid and s1.score>s2.score) rank from sc s2
2选出前三名
select * from () t1
select * from (select *,(select count(s1.score) from sc s1 where s1.cid=s2.cid and s1.score>s2.score) rank from sc s2) t1 where t1.rank<=3 order by t1.cid,t1.rank
-- ref
SELECT sc0.*,
(SELECT count(1) FROM SC WHERE SC.cid=sc0.cid AND SC.score>sc0.score)+1 rank
FROM SC sc0
GROUP BY 2,1,3
HAVING rank<=3
ORDER BY sc0.cid,rank;
-- 26、查询每门课程被选修的学生数
select *,count(sc.sid) amount from sc group by sc.cid
-- 27、查询出只有两门课程的全部学生的学号和姓名
思路
1统计学生选课数量=>t1
2找出只有两门课的学生
3关联
1统计学生选课数量=>t1
select *,count(sc.sid) amount from sc group by sc.sid
2找出只有两门课的学生
select * from () t1 where t1.amount = 2
select * from (select *,count(sc.sid) amount from sc group by sc.sid) t1 where t1.amount = 2
3关联
select s.sid,s.sname from (select *,count(sc.sid) amount from sc group by sc.sid) t1,student s
where t1.amount = 2 and t1.sid = s.sid
-- 28、查询男生、女生人数
-- 横向
select () `男生人数`,() `女生人数` from student
select sum(case when s.ssex='男' then 1 else 0 end) `男生人数`,sum(case when s.ssex='女' then 1 else 0 end) `女生人数` from student s
-- 纵向
select () `性别`,() `人数` from student s
select (case when s.ssex = '男' then '男' else '女' end) `性别`,count(s.ssex) `人数` from student s
group by (case when s.ssex = '男' then '男' else '女' end)
-- 29、查询名字中含有"风"字的学生信息
select * from student s where s.sname like '%风%'
-- 30、查询同名同性学生名单，并统计同名人数
-- 这里的1代表第一列,2代表第二列 直接写列名效果也是一样的
-- GROUP BY 1,2 的意思是先按第一列分组 第一列相同再按第二列分组 两列都相同就在同一组
-- 同名同姓人数
SELECT sname,ssex,count(1)
FROM Student GROUP BY 1,2
HAVING count(1)>1;
SELECT sname,ssex,count(sname)
FROM Student GROUP BY sname,ssex
HAVING count(sname)>1;
-- 同名人数
SELECT sname,ssex,count(1)
FROM Student GROUP BY 1
HAVING count(1)>1;
SELECT sname,ssex,count(sname)
FROM Student GROUP BY sname
HAVING count(sname)>1;
-- 31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)
SELECT Student.* FROM Student WHERE YEAR(sage)=1990;
hive
-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
统计每门课的平均成绩 按平均成绩排序
select cid,avg(sc.score) avg from sc group by sc.cid order by avg desc,cid
-- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
1统计每个学生的平均成绩 =>t1
2选出大于85的平均成绩 =>t2
3关联学生表,显示相关信息
1统计每个学生的平均成绩 =>t1
select sid,avg(sc.score) avg from sc group by sc.sid
2选出大于85的平均成绩 =>t2
select * from () t1 where t1.avg>=85
select * from (select sid,avg(sc.score) avg from sc group by sc.sid) t1 where t1.avg>=85
3关联学生表,显示相关信息
select * from () t2,student s where t2.sid = s.sid
select s.sid,s.sname,t2.avg from (select * from (select sid,avg(sc.score) avg from sc group by sc.sid) t1 where t1.avg>=85) t2,student s where t2.sid = s.sid
-- 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
select s.sname,sc.score from course c,sc sc,student s where c.cid=sc.cid and sc.sid=s.sid and c.cname='数学' and sc.score < 60
hive
-- 35、查询所有学生的课程及分数情况；
select s.sname,c.cname,sc.score from course c,sc sc,student s where c.cid=sc.cid and sc.sid=s.sid
--36、查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数；
-- 如果将任何一门成绩在70分以上,理解为所有成绩在70分以上的话
思路
1.查出每个学生的最小的成绩 => t1
2.选出最小的成绩比70大的学生,那么此学生的所有成绩都大于70 => t2
3.关联其它表,显示相关信息
1.查出每个学生的最小的成绩
select sid,min(score) min_score from sc group by sid
2.选出最小的成绩比70大的学生,那么此学生的所有成绩都大于70
select sid from () t1 where t1.min_score>70
select sid from (select sid,min(score) min_score from sc group by sid) t1 where t1.min_score>70
3.关联其它表,显示相关信息
select * from student s,sc sc,course c,() t2 where s.sid =sc.sid and sc.cid =c.cid and sc.sid = t2.sid
select
s.sname 学生姓名,c.cname 课程名,sc.score 分数
from
student s,sc sc,course c,(select sid from (select sid,min(score) min_score from sc group by sid) t1 where t1.min_score>70) t2
where
s.sid =sc.sid and sc.cid =c.cid and sc.sid = t2.sid
-- 如果将任何一门成绩在70分以上,理解为其中一门成绩在70分以上的话
思路
1.查出每个学生的最大的成绩 => t1
2.选出最大的成绩比70大的学生,那么此学生其中一门成绩大于70 => t2
3.关联其它表,显示相关信息
1.查出每个学生的最大的成绩
select sid,max(score) max_score from sc group by sid
2.选出最大的成绩比70大的学生,那么此学生其中一门成绩大于70 => t2
select sid from () t1 where t1.max_score>70
select sid from (select sid,max(score) max_score from sc group by sid) t1 where t1.max_score>70
3.关联其它表,显示相关信息
select * from student s,sc sc,course c,() t2 where s.sid =sc.sid and sc.cid =c.cid and sc.sid = t2.sid
select
s.sname 学生姓名,c.cname 课程名,sc.score 分数
from
student s,sc sc,course c,(select sid from (select sid,max(score) max_score from sc group by sid) t1 where t1.max_score>70) t2
where
s.sid =sc.sid and sc.cid =c.cid and sc.sid = t2.sid
-- 37、查询不及格的课程
select * from sc where sc.score<60
-- 38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
select * from sc sc,student s where sc.sid=s.sid and sc.cid='01' and sc.score >= 80
-- 39、求每门课程的学生人数
select count(sc.sid) from sc group by sc.cid
-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
select *,max(sc.score) from sc sc,course c,teacher t where sc.cid=c.cid and c.tid=t.tid and t.tname='张三' group by t.tname
-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
课程成绩去重后的课程数小于总课程数则不同课程存在相同成绩
select *,count(sc.score) count ,count(distinct sc.score) distinct_count from sc group by sc.sid
select * from (select *,count(sc.score) count ,count(distinct sc.score) distinct_count from sc group by sc.sid) t1 where distinct_count<count
-- 42、查询每门功成绩最好的前两名
select *,(select count(sc1.score) from sc sc1 where sc1.cid=sc.cid and sc1.score>sc.score)+1 rank from sc
select * from () t1 where rank<3
select * from (select *,(select count(sc1.score) from sc sc1 where sc1.cid=sc.cid and sc1.score>sc.score)+1 rank from sc) t1 where rank<3 order by t1.cid,t1.rank
-- ref
select sc.* from sc sc where (select count(1) from sc sc1 where sc1.cid=sc.cid and sc1.score>sc.score)<2 order by sc.cid,sc.score;
-- 43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select sc.cid,count(sc.sid) count from sc group by sc.cid order by count desc,sc.cid
select * from ()t1 where t1.count>5
select * from (select sc.cid,count(sc.sid) count from sc group by sc.cid order by count desc,sc.cid)t1 where t1.count>5
-- ref
SELECT cid,count(1) count FROM SC GROUP BY cid HAVING count>=5 ORDER BY count desc,cid ;
-- 44、检索至少选修两门课程的学生学号
select *,count(sc.cid) count from sc group by sc.sid
select * from () t1 where t1.count>=2
select * from (select *,count(sc.cid) count from sc group by sc.sid) t1 where t1.count>=2
-- ref
SELECT a.*,b.count FROM Student a INNER JOIN (SELECT sid,count(cid) count FROM SC GROUP BY sid HAVING count>1)b ON a.sid=b.sid GROUP BY 1,2,3,4;
-- 45、查询选修了全部课程的学生信息
select *,count(sc.cid) count from sc group by sc.sid having count=3
select *,count(sc.cid) count from sc group by sc.sid having count=(select count(c.cid) from course c)
-- 46、查询各学生的年龄
SELECT Student.*,YEAR(CURDATE())-YEAR(Student.sage) FROM Student;
SELECT Student.*,YEAR(current_date)-YEAR(Student.sage) FROM Student;
-- 47、查询本周过生日的学生
SELECT * FROM Student WHERE WEEKOFYEAR(NOW())-WEEKOFYEAR(sage)=0;
SELECT * FROM Student WHERE WEEKOFYEAR(current_date)-WEEKOFYEAR(sage)=0;
-- 48、查询下周过生日的学生
SELECT * FROM Student WHERE WEEKOFYEAR(current_date)-WEEKOFYEAR(sage)=-1;
-- 49、查询本月过生日的学生
select * from student where month(current_date)-month(sage)=0;
-- 50、查询下月过生日的学生
select * from student where month(current_date)-month(sage)=-1;
第二版
1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT s.*,sc1.score ,sc2.score
FROM student s,sc sc1,sc sc2
WHERE s.sid=sc1.sid AND s.sid=sc2.sid
AND sc1.cid='01' AND sc2.cid='02'
AND sc1.score>sc2.score
SELECT * FROM student s
LEFT JOIN sc sc1 ON s.sid=sc1.sid AND sc1.cid='01'
LEFT JOIN sc sc2 ON s.sid=sc2.sid AND sc2.cid='02'
WHERE IFNULL(sc1.score,0) > IFNULL(sc2.score,0)
2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select sid,avg(sc.score) avg from sc group by sc.sid having avg>=60
cast( avg(sc.score) as decimal(4,2))
select sid,cast( avg(sc.score) as decimal(4,2)) avg from sc group by sc.sid having avg>=60
select s.sid,s.sname,a.avg from student s join () a on s.sid = a.sid
select s.sid,s.sname,a.avg from student s join (select sid,cast( avg(sc.score) as decimal(4,2)) avg from sc group by sc.sid having avg>=60) a on s.sid = a.sid
4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select s.sid,s.sname,count(sc.cid),sum(sc.score) from student s join sc sc on s.sid=sc.sid group by s.sid ,s.sname
6、查询"李"姓老师的数量
select count(*) from teacher t where t.tname like "李%"
7、查询学过"张三"老师授课的同学的信息
select * from sc sc join course c on sc.cid=c.cid join teacher t on c.tid=t.tid and t.tname="张三"
8、查询没学过"张三"老师授课的同学的信息
select sc.sid sid from sc sc join course c on sc.cid=c.cid join teacher t on c.tid=t.tid and t.tname="张三"
select * from sc where sc.sid not in ()
select * from sc where sc.sid not in (select sc.sid sid from sc sc join course c on sc.cid=c.cid join teacher t on c.tid=t.tid and t.tname="张三")
9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
方式一
并行:利用学生ID相等关联两张表 然后分别给不同的条件
select * from sc sc1,sc sc2 where sc1.sid = sc2.sid and sc1.cid="01" and sc2.cid="02"
select sc1.sid from sc sc1,sc sc2 where sc1.sid = sc2.sid and sc1.cid="01" and sc2.cid="02"
select * from student s where s.sid in ()
select * from student s where s.sid in (select sc1.sid from sc sc1,sc sc2 where sc1.sid = sc2.sid and sc1.cid="01" and sc2.cid="02")
方式二
串行:先查出学过01课程的学生 然后到这个结果集中查学过02课程的学生
select sid from sc where sc.cid ="01"
select * from sc sc,() a where sc.sid = a.sid and sc.cid="02"
select * from sc sc,(select sid from sc where sc.cid ="01") a where sc.sid = a.sid and sc.cid="02"
10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select sid from sc where sc.cid ="01"
select sid from sc where sc.cid ="02"
select sid from sc where sc.cid ="01" and sc.sid not in()
select sid from sc where sc.cid ="01" and sc.sid not in(select sid from sc s where s.cid ="02")
select sid from sc sc where sc.cid ="01" and not exists(select 1 from sc s where s.sid=sc.sid and s.cid ="02")
11、查询没有学全所有课程的同学的信息
在成绩表中查询课程总数小于总课程的同学
关联学生表 显示相关信息
select count(cid) from course
select sid from sc group by sc.sid having count(cid) <(select count(cid) from course)
select sid from sc group by sc.sid having count(cid) <(select count(cid) from course)
select * from student s where s.sid in ()
select * from student s where s.sid in (select sid from sc group by sc.sid having count(cid) <(select count(cid) from course))
select * from student s join () a on s.sid=a.sid
select * from student s join (select sid from sc group by sc.sid having count(*) <(select count(cid) from course)) a on s.sid=a.sid
select * from student s join sc sc where s.sid = sc.sid group by s.sid having count(sc.cid)<(select count(cid) c from course)
12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
思路
1查询01号学生所学课程ID
2在成绩表中查找课程ID与学号为"01"的同学所学相同的同学的信息
1查询01号学生所学课程ID
select cid from sc where sid='01'
2在成绩表中查找课程ID至少有一门课与学号为"01"的同学所学相同的同学的信息
select b.sid from sc b where cid in (select a.cid from sc a where a.sid='01') group by b.sid
select * from student s join () c where s.sid=c.sid
select * from student s join (select b.sid sid from sc b where b.cid in (select a.cid from sc a where a.sid='01') group by b.sid) c on s.sid=c.sid
13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
思路
1查询01号学生所学课程ID
2在成绩表中查找课程ID与学号为"01"的同学所学相同的同学的信息
1查询01号学生所学课程ID
select cid from sc where sid='01'
2查询01号学生所学课程总数
select count(cid) from sc where sid='01'
3课程完全相同即课程在01号学生所学课程范围内 并且在数目一样
select * from sc a where a.cid in () group by a.sid having count(a.cid)=()
select * from sc a where a.cid in (select cid from sc where sid='01') group by a.sid having count(a.cid)=(select count(cid) from sc where sid='01')
14、查询没学过"张三"老师讲授的任一门课程的学生姓名
查询学过张三老师课程的学生ID
select sc.sid from sc sc,course c ,teacher t where sc.cid = c.cid and c.tid = t.tid and t.tname ='张三'
查询没学过张三老师课程的学生
select s.* from student s where s.sid not in ()
select s.* from student s where s.sid not in (select sc.sid from sc sc,course c ,teacher t where sc.cid = c.cid and c.tid = t.tid and t.tname ='张三')
15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
查询两门及其以上不及格课程的同学
查询满足上不条件的学生学号，姓名及其平均成绩
查询两门及其以上不及格课程的同学
select *,count(sid) count from sc sc where sc.score<60 group by sc.sid having count >= 2
select sid from sc sc where sc.score<60 group by sc.sid having count(sid) >= 2
查询满足上不条件的学生学号，姓名及其平均成绩
select sc.sid,avg(sc.score) from student s,sc sc where s.sid =sc.sid and sc.sid in () group by sc.sid
select s.sname,sc.sid,avg(sc.score) avg from student s,sc sc where s.sid =sc.sid and sc.sid in (select sid from sc sc where sc.score<'60' group by sc.sid having count(sid) >= 2) group by sc.sid
16、检索"01"课程分数小于60，按分数降序排列的学生信息
检索"01"课程分数小于60的学生
显示相关学生信息
检索"01"课程分数小于60的学生
select * from sc s where s.cid ='01' and s.score < 60 order by s.score desc
显示相关学生信息
select * from student a join () b on a.sid = b.sid order by b.score desc
select a.sid sid,a.sname name,b.score score from student a join (select s.sid sid,s.score score from sc s where s.cid ='01' and s.score < 60) b on a.sid = b.sid order by score desc
17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select * from sc s join course c on s.cid = c.cid
列转行 语文 数学 英语
select s.sid sid ,
max(case when c.cname='语文' then s.score else null end) `语文`,
max(case when c.cname='数学' then s.score else null end) `数学`,
max(case when c.cname='英语' then s.score else null end) `英语`,
cast(avg(s.score) as decimal(4,2)) `平均成绩`
from sc s join course c on s.cid = c.cid group by s.sid
select s.sid sid ,
max(case when c.cname='语文' then s.score else null end) `语文`,
max(case when c.cname='数学' then s.score else null end) `数学`,
max(case when c.cname='英语' then s.score else null end) `英语`,
bround(avg(s.score),2) `平均成绩`
from sc s join course c on s.cid = c.cid group by s.sid
18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
select * from sc s join course c on s.cid=c.cid group by s.cid
select c.cid cid,c.cname name,
max(s.score) max,
min(s.score) min,
avg(s.score) avg,
sum(case when s.score>=60 then 1 else 0 end)/count(1) `及格率`,
sum(case when s.score>=70 and s.score<80 then 1 else 0 end)/count(1) `中等率`,
sum(case when s.score>=80 and s.score<90 then 1 else 0 end)/count(1) `优良率`,
sum(case when s.score>=90 then 1 else 0 end)/count(1) `优秀率`
from sc s join course c on s.cid=c.cid group by c.cid,c.cname
select c.cid cid,c.cname name,
max(s.score) max,
min(s.score) min,
avg(s.score) avg,
cast(sum(case when s.score>=60 then 1 else 0 end)/count(1) as decimal(4,2)) `及格率`,
cast(sum(case when s.score>=70 and s.score<80 then 1 else 0 end)/count(1)*100 as decimal(4,2)) `中等率`,
cast(sum(case when s.score>=80 and s.score<90 then 1 else 0 end)/count(1)*100 as decimal(4,2)) `优良率`,
cast(sum(case when s.score>=90 then 1 else 0 end)/count(1)*100 as decimal(4,2)) `优秀率`
from sc s join course c on s.cid=c.cid group by c.cid,c.cname
19、按各科成绩进行排序，并显示排名
select c.cid id,c.cname name,s.score score,() rank from sc s join course c on s.cid=c.cid
select c.cid id,c.cname name,s.score score,
(select count(a.score) from sc a where a.cid=s.cid and s.score<a.score)+1 rank
from sc s join course c on s.cid=c.cid order by c.cid,c.cname,rank
20、查询学生的总成绩并进行排名
计算总成绩
排名
计算总成绩
select sc.sid sid,sum(sc.score) tatal_score from sc sc group by sc.sid
排名
select *,(select count() from () a where b.tatal_score < a.tatal_score)+1 rank from () b
select *,
(select count(a.tatal_score)
from (select sc.sid sid,sum(sc.score) tatal_score from sc sc group by sc.sid) a
where b.tatal_score < a.tatal_score)+1 rank
from (select sc.sid sid,sum(sc.score) tatal_score from sc sc group by sc.sid) b
order by rank
21、查询不同老师所教不同课程平均分从高到低显示
select c.tname tname,b.cname cname,avg(a.score) avg
from sc a join course b on a.cid=b.cid join teacher c on b.tid = c.tid
group by c.tname,b.cname
select c.tname tname,b.cname cname,cast(avg(a.score) as decimal(4,2)) avg
from sc a join course b on a.cid=b.cid join teacher c on b.tid = c.tid
group by c.tname,b.cname
22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
select *,() rank from sc a
select *,(select count(distinct b.score) from sc b where a.cid=b.cid and b.score>a.score)+1 rank from sc a
order by a.cid,rank
select * from () t where t.tank >=2 and t.tank <=3
select * from (select *,(select count(distinct b.score) from sc b where a.cid=b.cid and b.score>a.score)+1 rank from sc a
) t where t.rank >=2 and t.rank <=3 order by cid,rank
23、统计各科成绩各分数段人数：课程编号,课程名称, 100-85 , 85-70 , 70-60 , 0-60 及所占百分比
横向
select s.cid,c.cname,
sum(case when s.score<=100 and s.score >85 then 1 else 0 end) `100-85`,
sum(case when s.score<=85 and s.score >70 then 1 else 0 end) `85-70`,
sum(case when s.score<=70 and s.score >60 then 1 else 0 end) `70-60`,
sum(case when s.score<=60 and s.score >=0 then 1 else 0 end) `60-0`
from
sc s join course c on s.cid = c.cid
group by s.cid,c.cname
select s.cid,c.cname,
sum(case when s.score<=100 and s.score >85 then 1 else 0 end) `100-85`,
sum(case when s.score<=100 and s.score >85 then 1 else 0 end)/sum(1) `百分比`,
sum(case when s.score<=85 and s.score >70 then 1 else 0 end) `85-70`,
sum(case when s.score<=85 and s.score >70 then 1 else 0 end)/sum(1) `百分比`,
sum(case when s.score<=70 and s.score >60 then 1 else 0 end) `70-60`,
sum(case when s.score<=70 and s.score >60 then 1 else 0 end)/sum(1) `百分比`,
sum(case when s.score<=60 and s.score >=0 then 1 else 0 end) `60-0`,
sum(case when s.score<=60 and s.score >=0 then 1 else 0 end)/sum(1) `百分比`
from
sc s join course c on s.cid = c.cid
group by s.cid,c.cname
纵向
select
s.cid,c.cname,
(
case
when s.score<=100 and s.score >85 then '100-85'
when s.score<=85 and s.score >70 then '85-70'
when s.score<=70 and s.score >60 then '70-60'
when s.score<=60 and s.score >=0 then '60-0'
end
) `分段`,count(1) `人数`
from
sc s join course c on s.cid = c.cid
group by s.cid,c.cname,分段
select
s.cid,c.cname,
(
case
when s.score<=100 and s.score >85 then '100-85'
when s.score<=85 and s.score >70 then '85-70'
when s.score<=70 and s.score >60 then '70-60'
when s.score<=60 and s.score >=0 then '60-0'
end
) `分段`,count(1) `人数`
from
sc s join course c on s.cid = c.cid
group by s.cid,c.cname,
(case
when s.score<=100 and s.score >85 then '100-85'
when s.score<=85 and s.score >70 then '85-70'
when s.score<=70 and s.score >60 then '70-60'
when s.score<=60 and s.score >=0 then '60-0'
end)
28、查询男生、女生人数
横向
select
sum(case when ssex ='男' then 1 else 0 end) `男`,
sum(case when ssex ='女' then 1 else 0 end) `女`
from student s
纵向
select
(case when ssex='男' then '男' else '女' end) `性别`,count(1) `人数`
from
student
group by
(case when ssex = '男' then '男' else '女' end)
36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
-- 如果将任何一门成绩在70分以上,理解为所有成绩在70分以上的话
思路
1.查出每个学生的最小的成绩 => t1
2.选出最小的成绩比70大的学生,那么此学生的所有成绩都大于70 => t2
3.关联其它表,显示相关信息
1.查出每个学生的最小的成绩
select sid,min(score) min_score from sc group by sid
2.选出最小的成绩比70大的学生,那么此学生的所有成绩都大于70
select sid from () t1 where t1.min_score>70
select sid from (select sid,min(score) min_score from sc group by sid) t1 where t1.min_score>70
3.关联其它表,显示相关信息
select * from student s,sc sc,course c,() t2 where s.sid =sc.sid and sc.cid =c.cid and sc.sid = t2.sid
select
s.sname `学生姓名`,c.cname `课程名`,sc.score `分数`
from
student s,sc sc,course c,(select sid from (select sid,min(score) min_score from sc group by sid) t1 where t1.min_score>70) t2
where
s.sid =sc.sid and sc.cid =c.cid and sc.sid = t2.sid
-- 如果将任何一门成绩在70分以上,理解为其中一门成绩在70分以上的话
思路
1.查出每个学生的最大的成绩 => t1
2.选出最大的成绩比70大的学生,那么此学生其中一门成绩大于70 => t2
3.关联其它表,显示相关信息
1.查出每个学生的最大的成绩
select sid,max(score) max_score from sc group by sid
2.选出最大的成绩比70大的学生,那么此学生其中一门成绩大于70 => t2
select sid from () t1 where t1.max_score>70
select sid from (select sid,max(score) max_score from sc group by sid) t1 where t1.max_score>70
3.关联其它表,显示相关信息
select * from student s,sc sc,course c,() t2 where s.sid =sc.sid and sc.cid =c.cid and sc.sid = t2.sid
select
s.sname `学生姓名`,c.cname `课程名`,sc.score `分数`
from
student s,sc sc,course c,(select sid from (select sid,max(score) max_score from sc group by sid) t1 where t1.max_score>70) t2
where
s.sid =sc.sid and sc.cid =c.cid and sc.sid = t2.sid
