2021-07-26

2021/7/26
辞职复习的第一天–内容为数据库
MYSQL数据库

知识点

--1.left join （从左边返回所有，即使右表没有）
--left join==left outer join
--2.inner join(表中至少存在一个匹配的时候)
--inner join==join
--3.is null (为空不存在)
-- avg(平均值)  group by(分组) count (返回条数) sum(求和)
-- like'%'(模糊查询)
--4.在where条件中 In可以使用多个查询
--5.DIstinct (用于返回唯一不同的值)
--6.Having（子句，在where 中无法用函数的时候使用）
--7.in 与 exist (区别在于前者条件都返回，后者条件只返回第一条)
--8.sql在变量中可以使用 定义变量 和IF
-- SET @name='';
-- if 语句在使用的时候 要加入 begin end 

练习题

https://zhuanlan.zhihu.com/p/109728624

-- -- 1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
-- 
SELECT a.Sid,a.score ,student.* FROM(SELECT * FROM sc WHERE sc.Cid='01') a 
LEFT JOIN (SELECT * FROM sc WHERE sc.Cid='02') b
ON a.Sid=b.Sid LEFT JOIN student ON student.Sid=a.Sid WHERE a.score>b.score;
-- 
-- 
-- -- 1.1 查询同时存在" 01 "课程和" 02 "课程的情况
-- 
SELECT student.* FROM (SELECT * FROM sc WHERE sc.Cid='01') a
JOIN (SELECT * FROM sc WHERE sc.Cid='02')b ON a.Sid=b.Sid
LEFT JOIN student ON student.Sid=a.Sid;

-- -- 1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
-- 
SELECT * FROM (SELECT * FROM sc WHERE sc.Cid='01') a
LEFT JOIN (SELECT * FROM sc WHERE sc.Cid='02') b ON a.Sid=b.Sid


-- -- 1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
-- 
SELECT * FROM (SELECT * FROM sc WHERE sc.Cid='02') a
LEFT JOIN (SELECT * FROM sc WHERE sc.Cid='01')b ON
a.Sid=b.Sid WHERE b.Sid is NULL ;
-- 


-- -- 2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
-- 
-- 
SELECT student.*,a.b FROM(SELECT AVG(Score) b ,Sid FROM sc GROUP BY Sid )a 
INNER JOIN student ON a.Sid=student.Sid WHERE b>60;
-- 

-- -- 3. 查询在 sc 表存在成绩的学生信息
-- 
SELECT * FROM student INNER JOIN (SELECT * FROM sc)a 
ON student.Sid=a.Sid WHERE a.score is NOT NULL ;
-- 


-- -- 4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
-- 
SELECT * FROM student LEFT JOIN
(SELECT SUM(Score),count(Cid),Sid FROM sc GROUP BY Sid)a
ON a.Sid=student.Sid;


-- -- 4.1 查有成绩的学生信息
SELECT * FROM student LEFT JOIN 
(SELECT * from sc )a
ON a.Sid=student.Sid
WHERE a.score IS NOT NULL


-- -- 5. 查询「李」姓老师的数量

 SELECT COUNT(*) FROM teacher WHERE Tname LIKE '李%';
 
 -- 6. 查询学过「张三」老师授课的同学的信息
 

SELECT * FROM student LEFT JOIN 
(SELECT * FROM teacher WHERE Tname = '张三')a
LEFT JOIN (SELECT * FROM course )b ON a.Tid=b.Tid
LEFT JOIN(SELECT * FROM sc)c ON c.Cid=b.Cid
WHERE student.Sid=sc.Sid;
-- 
-- 
SELECT * FROM student stu
WHERE stu.Sid IN (SELECT Sid FROM course LEFT JOIN sc ON course.Cid=sc.Cid WHERE course.Tid IN (SELECT Tid FROM teacher
WHERE teacher.Tname='张三'));

-- 
SELECT * FROM student LEFT JOIN
(SELECT * FROM sc )a ON student.Sid= a.Sid  WHERE a.Cid=(SELECT * FROM course WHERE Tid=(SELECT Tid FROM teacher WHERE Tname='张三'));

-- 
SELECT * FROM student WHERE student.Sid IN (SELECT Sid FROM course LEFT JOIN sc ON course.Cid=sc.Cid WHERE course.Tid IN (SELECT Tid  from teacher WHERE teacher.Tname='张三'));

-- 
-- 
SELECT * from student WHERE student.Sid IN(SELECT Sid from course LEFT JOIN sc ON course.Cid=sc.Cid WHERE course.Tid IN(SELECT Tid FROM teacher WHERE Tname='张三'))



-- -- 7. 查询没有学全所有课程的同学的信息
-- 
-- 
SELECT * FROM student LEFT JOIN 
(SELECT  Sid ,COUNT(Cid) AS class  FROM sc  GROUP BY Sid)a 
ON student.Sid=a.Sid 
WHERE a.class<3 OR a.class IS NULL
-- 
-- 
-- 
-- 
-- -- 8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
-- 
SELECT * FROM student WHERE Sid IN (SELECT Sid FROM sc WHERE Cid IN (SELECT Cid FROM sc WHERE Sid='01'));


SELECT * FROM student WHERE Sid IN(SELECT Sid FROM sc WHERE Cid IN (SELECT Cid FROM sc WHERE Sid='01'));
-- 
-- -- 9. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
-- 
 SELECT * FROM student LEFT JOIN
( SELECT COUNT(Sid)AS object ,Sid, SUM(Cid)AS ab FROM sc GROUP BY Sid )a
ON student.Sid=a.Sid  WHERE  a.object=2  AND a.ab=5



-- -- 10. 查询没学过"张三"老师讲授的任一门课程的学生姓名
 SELECT * FROM student WHERE Sid IN (SELECT  Sid FROM sc WHERE Cid NOT IN (SELECT Cid FROM course WHERE Tid IN (SELECT Tid FROM teacher WHERE Tname="张三")) GROUP BY  Sid);
-- 
-- 
-- 
-- -- 11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
-- 
 SELECT * FROM student LEFT JOIN
 (SELECT Sid,AVG(score)AS c FROM sc WHERE  score<60 GROUP BY Sid  HAVING COUNT(*)>=2  )a
 ON student.Sid=a.Sid WHERE a.c IS NOT NULL
-- 
-- 
-- 
-- -- 12. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
 
 
 SELECT * FROM student WHERE Sid IN(SELECT Sid  FROM sc WHERE Cid='01' AND score<60 ORDER BY score DESC )
 
 
 -- 13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
 
 SELECT * FROM student LEFT JOIN
 (SELECT Sid,AVG(score) AS Ascore,score from sc GROUP BY Sid  ) a
  ON student.Sid=a.Sid 
  ORDER BY a.Ascore DESC;
  
 -- 14. 查询各科成绩最高分、最低分和平均分：
 SELECT * FROM  course LEFT JOIN
 (SELECT AVG(score),MIN(score),MAX(score) ,Cid FROM sc GROUP BY Cid)a
 ON course.Cid=a.Cid ;
 
 
 -- 15. 以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
 
 -- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
  SELECT * FROM course a
 
 LEFT JOIN(SELECT Cid,MAX(score) '最高分',MIN(score) '最低分',AVG(score) '平均分',
 
 SUM(CASE WHEN score >= 60 THEN 1 ELSE 0 END ) / COUNT(DISTINCT Sid) AS '及格率',
 
 SUM(CASE WHEN score >= 70 AND score < 80 THEN 1 ELSE 0 END ) / COUNT(DISTINCT Sid) '中等率',
 
 SUM(CASE WHEN score >= 80 AND score < 90 THEN 1 ELSE 0 END ) / COUNT(DISTINCT Sid) '优良率',
 
 SUM(CASE WHEN score >= 90 THEN 1 ELSE 0 END ) / COUNT(DISTINCT Sid) '优秀率'
 
 FROM sc
 
 GROUP BY Cid) b
 
 ON a.Cid=b.Cid

 -- 16. 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

 SELECT  Cid, COUNT(Sid)AS number FROM sc GROUP BY Cid ORDER BY number ,Cid; 
 
 SELECT sc.`Cid`,COUNT(*) FROM sc
 
 GROUP BY sc.`Cid`
 
 ORDER BY COUNT(*) DESC,sc.`Cid` ASC;


-- 15. 按各科成绩进行排序，并显示排名， score 重复时保留名次空缺

SET @number=0 ;
SET @score=0;
SET @lastCid=0;

SELECT sc.Cid,sc.Sid,sc.score, IF(@score=sc.score ,NULL,@number:=@number+1)AS number  FROM sc
ORDER BY Cid, score DESC

SET @rk=0;

SET @last_Cid=0;

SET @score=0;

SELECT sc.`Cid`,sc.`score`,sc.`Sid`,IF(@last_Cid=sc.`Cid`,

IF(@score=sc.`score`,NULL,@rk:=@rk+1),

@rk:=1) AS rk,

@last_Cid:=sc.`Cid` AS last_Cid,

@score:=sc.`score` AS last_score

FROM sc

ORDER BY sc.`Cid`,sc.`score` DESC