-- 1、查询\"01\"课程比\"02\"课程成绩高的学生信息及课程分数
-- 考点：自连接，多表连接
-- 01课程成绩
select * from sc where c_id=01;
-- 02课程成绩
select * from sc where c_id=02;

select stu.*,sc.c_id,sc.score -- 表达方式要注意
from (select * from sc where c_id=01)t1
join (select * from sc where c_id=02)t2 on t1.s_id=t2.s_id
join stu on t1.s_id=stu.s_id
join sc on stu.s_id=sc.s_id
where t1.score>t2.score
;

-- ----------------------------------------------------------------------------------
-- 2、练习：查询\"01\"课程比\"02\"课程成绩低的学生的信息及课程分数
select * from sc where c_id=01;

select * from sc where c_id=02;

select stu.*,sc.c_id,sc.score
from (select * from sc where c_id=01)t01
join (select * from sc where c_id=02)t02 on t01.s_id=t02.s_id
join stu on t01.s_id=stu.s_id
join sc on stu.s_id=sc.s_id
where t01.score<t02.score;


-- ----------------------------------------------------------------------------------
-- 3、查询平均成绩大于等于60分的同学的学生编号、学生姓名和平均成绩
# 查询平均成绩
-- 子查询（效率低）
select s_id,avg(score) 平均成绩 from sc group by s_id having avg(score)>=60;

select stu.s_id,stu.s_name,平均成绩
from (select s_id,avg(score) 平均成绩 from sc group by s_id having avg(score)>=60)t
join stu on stu.s_id=t.s_id;

-- ----------------------------------------------------------------------------------
-- 4、练习：查询平均成绩小于60分的同学的学生编号、学生姓名和平均成绩
-- 表连接(更建议用这种，跑得比较快）
select stu.s_id,s_name,avg(score)
from stu join sc on sc.s_id= stu.s_id
group by s_id
having avg(score)<60;


-- ----------------------------------------------------------------------------------
-- 33、练习：查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select stu.s_id,s_name,avg(score)
from sc join stu on sc.s_id=stu.s_id
group by s_id
having avg(score)>=85;


-- ----------------------------------------------------------------------------------
-- 5、练习：查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

select stu.s_id,s_name,count(c_id),sum(score)
from stu left join sc on stu.s_id=sc.s_id
group by stu.s_id;


-- ----------------------------------------------------------------------------------
-- 6、查询\"李\"姓老师的数量
-- 考点：模糊匹配
select count(t_id)
from te
where t_name like \'李%\';



-- ----------------------------------------------------------------------------------
-- 29、练习：查询名字中含有\"风\"字的学生信息
select *
from stu
where s_name like \'%风%\';


-- ----------------------------------------------------------------------------------
-- 7、查询学过\"张三\"老师授课的同学的信息
select stu.*
from te
left join co on te.t_id=co.t_id
left join sc on co.c_id=sc.c_id
left join stu on sc.s_id=stu.s_id
where t_name=\'张三\';


-- ----------------------------------------------------------------------------------
-- 40、查询选修\"张三\"老师所授课程的学生中，成绩最高的学生信息及其成绩
select stu.*
from te
left join co on te.t_id=co.t_id
left join sc on co.c_id=sc.c_id
left join stu on sc.s_id=stu.s_id
where t_name=\'张三\'
order by score desc
limit 1;

select *
from stu
left join sc on stu.s_id=sc.s_id
left join co on sc.c_id=co.c_id
left join te on co.t_id=te.t_id;


-- ----------------------------------------------------------------------------------
-- 8、练习：查询没学过\"张三\"老师授课的同学的信息
SELECT
    *
FROM
    te;
select * from co;
select * from sc;
select * from stu;

select *
from stu
where s_id not in (select stu.s_id
from stu
left join sc on sc.s_id=stu.s_id
left join co on co.c_id=sc.c_id
left join te on te.t_id=co.t_id
where t_name=\'张三\');



-- ----------------------------------------------------------------------------------
-- 14、练习：查询没学过\"张三\"老师讲授的任一门课程的学生姓名
SELECT
    *
FROM
    te;
select * from co;
select * from sc;
select * from stu;

select s_name
from stu
where s_name not in (select s_name
from stu
left join sc on sc.s_id=stu.s_id
left join co on co.c_id=sc.c_id
left join te on te.t_id=co.t_id
where t_name=\'张三\');


-- ----------------------------------------------------------------------------------
-- 9、查询学过编号为\"01\"并且也学过编号为\"02\"的课程的同学的信息
select stu.*
from sc join stu on stu.s_id=sc.s_id
where c_id in (\'01\',\'02\')
group by sc.s_id
having count(c_id)=2;


-- ----------------------------------------------------------------------------------
-- 10、练习：查询学过编号为\"01\"但是没有学过编号为\"02\"的课程的同学的信息---------
-- 分组合并 group_concat
-- 思路：先把01、02挑出来，再把只选了01的找出来
-- 重点题目
select sc.s_id,group_concat(c_id order by c_id)
from sc join stu on stu.s_id=sc.s_id
where c_id in (\'01\',\'02\') -- 01或者02
group by sc.s_id
having group_concat(c_id order by c_id)=\'01\';

-- ----------------------------------------------------------------------------------
-- 45、查询选修了全部课程的学生信息
-- 当数据库很大的时候，*非常占内存（不推荐使用）
-- 使用标量子查询(这里需要再学习）
use school;

select count(c_id) from co;

select stu.*
from stu join sc on sc.s_id=stu.s_id
group by stu.s_id
having count(c_id)=(select count(c_id) from co);


-- ----------------------------------------------------------------------------------
-- 11、练习：查询没有学全所有课程的同学的信息
-- 没有学全（老师的理解：代表学了但是没有学了全部）
-- 我的理解：只要没学完就是没全
-- 这句话有歧义。。。。
-- 出题不严谨
select count(c_id) from co;

select stu.*
from stu left join sc on stu.s_id=sc.s_id
group by stu.s_id
having count(c_id)<>(select count(c_id) from co);

-- ----------------------------------------------------------------------------------
-- 12、查询至少有一门课与学号为\"01\"的同学所学相同的同学的信息
select c_id from sc where s_id=\'01\';

select sc.*
from sc join stu on sc.s_id=stu.s_id
where c_id in (select c_id from sc where s_id=\'01\')
group by sc.s_id
having sc.s_id<>\'01\';

-- 去重 (后面一定要有字段）
select distinct stu.* from stu;

-- ----------------------------------------------------------------------------------
-- 13、练习：查询和\"01\"号的同学学习的课程完全相同的其他同学的信息
select group_concat(c_id) from sc where s_id=\'01\';

select stu.*
from sc join stu on sc.s_id=stu.s_id
group by sc.s_id
having group_concat(c_id)=(select group_concat(c_id) from sc where s_id=\'01\') and sc.s_id<>\'01\';


------------------------------------------------------------------------------------
-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select s_id
from sc
where score<60
group by s_id
having count(c_id)>=2;

select stu.s_id,s_name,avg(score)
from stu join sc on stu.s_id=sc.s_id
group by stu.s_id
having stu.s_id in (select s_id
from sc
where score<60
group by s_id
having count(c_id)>=2);


-- ----------------------------------------------------------------------------------
-- 16、检索\"01\"课程分数小于60，按分数降序排列的学生信息
select stu.*
from sc join stu on sc.s_id=stu.s_id
where c_id=\'01\' and score<60
order by score desc;


-- ----------------------------------------------------------------------------------
-- 34、练习：查询课程名称为\"数学\"，且分数低于60的学生姓名和分数
select c_id
from co
where c_name=\'数学\';

select s_name,score
from sc join stu on sc.s_id=stu.s_id
join co on sc.c_id=co.c_id
where score<60 and c_name=\'数学\'
;

-- ----------------------------------------------------------------------------------
-- 38、练习：查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
select stu.s_id,stu.s_name
from sc join stu on sc.s_id=stu.s_id
where c_id=\'01\' and score>80;

select * from sc;
-- ----------------------------------------------------------------------------------
-- 35、查询所有学生的课程及分数情况（一维转二维）
-- !!!!!!考察重难点
select stu.s_id,
        sum(if(c_id=\'01\',score,0))\'01\',
    sum(if(c_id=\'02\',score,0))\'02\',
    sum(if(c_id=\'03\',score,0))\'03\'
from stu left join sc on stu.s_id=sc.s_id
group by stu.s_id;


select stu.s_id,
        sum(case when c_id=\'01\' then score else 0 end)\'01\',
    sum(case when c_id=\'02\' then score else 0 end)\'02\',
    sum(case when c_id=\'03\' then score else 0 end)\'03\'
from stu left join sc on stu.s_id=sc.s_id
group by stu.s_id;


select stu.s_id,
                sum((c_id=\'01\')*score)\'01\',
                sum((c_id=\'02\')*score)\'02\',
                sum((c_id=\'03\')*score)\'03\'
from stu left join sc on stu.s_id=sc.s_id
group by stu.s_id;


-- ----------------------------------------------------------------------------------
-- 17、练习：按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select stu.s_id,
                sum((c_id=\'01\')*score)\'01\',
                sum((c_id=\'02\')*score)\'02\',
                sum((c_id=\'03\')*score)\'03\',
        avg(score)
from stu left join sc on stu.s_id=sc.s_id
group by stu.s_id
order by avg(score) desc;


-- ----------------------------------------------------------------------------------
-- 18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
#及格为：>=60，中等为：70-80，优良为：80-90，优秀为：>=90


select sc.c_id,c_name,max(score)最高分,min(score)最低分,avg(score)平均分,
       sum(score>=60 and score<70)/(select count(s_id) from stu)及格,
       sum(score>=70 and score<80)/(select count(s_id) from stu)中等,
       sum(score>=80 and score<90)/(select count(s_id) from stu)优良,
       sum(score>=90)/(select count(s_id) from stu)优秀
from sc left join co on sc.c_id=co.c_id
group by sc.c_id;


-- ----------------------------------------------------------------------------------
-- 23、练习：统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
select sc.c_id,c_name,
           sum(score>=85)/(select count(s_id) from stu)\'[85-100]\',
       sum(score>=70 and score<85)/(select count(s_id) from stu)\'[70-85]\',
       sum(score>=60 and score<70)/(select count(s_id) from stu)\'[60-70]\',
       sum(score<60)/(select count(s_id) from stu)\'[0-60]\'
from sc left join co on sc.c_id=co.c_id
group by sc.c_id;


-- ----------------------------------------------------------------------------------
-- 19、查询学生的总成绩并进行排名

select stu.s_id,sum(score)
from stu left join sc on stu.s_id=sc.s_id
group by stu.s_id
order by sum(score) desc;

-- 定义用户变量
set @r=0;
select @r;
-- 循环计算
-- @r:=+1 == @r=@r+1

select *,@r:=@r+1 排名
from
(select stu.s_id,sum(score)
from stu left join sc on stu.s_id=sc.s_id
group by stu.s_id
order by sum(score) desc)t;
-- ----------------------------------------------------------------------------------
-- 24、练习：查询每个学生平均成绩及其名次
select s_id,avg(score)
from sc
group by s_id
order by avg(score) desc;

set @r=0;
select @r;

select *,@r:=@r+1 排名
from
(select stu.s_id,avg(score)
from stu left join sc on stu.s_id=sc.s_id
group by s_id
order by avg(score) desc)a;


-- 在一条语句中完成
select a.*,@r:=@r+1 排名
from
(select stu.s_id,avg(score)
from stu left join sc on stu.s_id=sc.s_id
group by s_id
order by avg(score) desc)a,(select @r:=0)b
;
-- 冒号是赋值的意思

-- 笛卡尔积连接
-- 不加条件的时候，所有记录进行连接
-- 消除笛卡尔积连接 排除不相等的


-- ----------------------------------------------------------------------------------
-- 20、练习：按各科成绩进行排序，并显示排名
-- 重点练习对象
-- 赋值
set @c=null;
set @r=0;
select sc.s_id,score,if(c_id=@c,@r:=@r+1,@r:=1)排名,@c:=c_id 课程ID
from sc
order by c_id,score desc;

-- 赋值
select s_id,score,if(c_id=@c,@r:=@r+1,@r:=1)排名,@c:=c_id 课程ID
from sc,(select @c:=null,@r:=0)t
order by c_id,score desc;


-- ----------------------------------------------------------------------------------
-- 21、练习：查询不同老师所教不同课程平均分从高到低显示
select sc.c_id,co.t_id,avg(score)
from sc join co on sc.c_id=co.c_id
join te on te.t_id=co.t_id
group by t_name
order by avg(score) desc;

-- ----------------------------------------------------------------------------------
-- 25、查询各科成绩前三名的记录
-- 出现概率比较高的难题
-- 关联子查询
-- 需要反复练习，认真复习
select *
from sc t1
where (select sum(t2.score>t1.score) from sc t2 where t1.c_id=t2.c_id)<3;