如何快速学会 SQL 数据库基础？

作者：数据开发小胡同学
链接：https://www.zhihu.com/question/486712700/answer/2424239325
来源：知乎
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

/*
 * 复杂sql练习
 * 2022-04-01
 *author：Rabin
 *address:shanghai
 *题目来源：https://zhuanlan.zhihu.com/p/38354000,作者：猴子数据分析
  使用环境:sqlserver
 *
 *学生表：student(学号,学生姓名,出生年月,性别)
  成绩表：score(学号,课程号,成绩)
  课程表：course(课程号,课程名称,教师号)
  教师表：teacher(教师号,教师姓名)
 */
 
-- 一、创建数据库和表
 
-- 1、学生表
CREATE table student (
 sid int,--学号
 sname varchar(20),--姓名
 sbirth varchar(20), --出生日期
 sgender varchar(20)--性别
);
INSERT into student values (0001,'猴子','1989-01-01','男'),(0002,'猴子','1990-12-21','女'),(0003,'马云','1991-12-21','男'),(0004,'王思聪','1990-05-20','男');
SELECT * from student ;
 
-- 2.创建学生成绩表
create table score (
  sid int ,--学号
  scoure int,--课程号
  sscore int, --成绩
  primary key (sid,scoure) 
);
 
INSERT  into score values (0001,0001,80),(0001,0002,90),(0001,0003,99),(0002,0002,60),(0002,0003,80),(0003,0001,80),(0003,0002,80),(0003,0003,80);
SELECT * from score ;
-- 创建课程表
create table course (
  scoure int ,--课程号
  cname varchar(20),--课程名称
  cteacher int 
);
INSERT  into course values (001,'语文',0002),(0002,'数学',0001),(0003,'英语',0003);
SELECT * from course ;
 
--创建教师表
create table teacher(
 cteacher int,--教师号
 tname varchar (20)
);
 
INSERT into teacher values (0001,'孟扎扎'),(0002,'马化腾'),(0003,null),(0004,'');
SELECT * from teacher ;
 
 
 
-- 1、查询姓“猴”的学生名单
SELECT * from student s where s.sname like '猴%';
-- 1.1查询姓名中最后一个字是猴的学生名单
SELECT * from student s where s.sname like '%猴';
-- 1.2查询姓名中带猴的学生名单
SELECT * from student s where s.sname like '%猴%';
-- 1.3、查询姓“孟”老师的个数
SELECT count(*) from student s where s.sname like '孟%';
 
--2、查询课程编号为“0002”的总成绩
SELECT sum(s.sscore) from course c join score s on c.scoure =s.scoure where c.scoure =002 ;
-- 2.1、查询选了课程的学生人数
 
SELECT count( DISCONNECT s.sid) from score s;
-- 3、查询各科创建最高和最低分数
SELECT scoure , max(sscore),min(sscore) from score group by scoure ;
-- 3.1、查询每门课程被选修学生数
SELECT * from course ; 
SELECT s.scoure,count(*) from score s join course c on s.scoure =c.scoure  group by s.scoure ;
-- 3.2、查询男生，女生人数
SELECT sgender , count(*) from student group by sgender ;
-- 4、查询平均成绩大于70分学生的学号和平均成绩
SELECT s.sid ,AVG(s.sscore)  from score s group by s.sid HAVING AVG(s.sscore)>70 ;
-- 4.1、查询至少选修两门课程的学生学好
SELECT s.sid,count(s.scoure)from score s group by s.sid HAVING count(s.scoure)>=2;
-- 4.2、查询同名同性学生名单并统计同名人数
SELECT count(*),s2.sname  FROM  student s2 group by s2.sname HAVING count(*)>1;
-- 4.3、查询不及格的课程并按课程号从大到小排列
SELECT s.scoure,s.sscore  from score s where s.sscore <60 ORDER BY s.scoure  
-- 4.4、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
SELECT s2.scoure,AVG(s2.sscore) from score s2 group by s2.scoure ORDER by avg(s2.sscore),s2.scoure DESC ;
SELECT * from score s ;
-- 4.5、检索课程编号为“0004”且分数小于60的学生学号，结果按按分数降序排列
SELECT s.sid ,s.sscore  from score s where s.sid =004 and s.sscore <60 ORDER BY s.sscore DESC ;
SELECT  * from score s ;
SELECT * from course c ;
--4.6、统计每门课程的学生选修人数(超过2人的课程才统计)
--要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序
SELECT s.scoure ,count(s.sid) FROM  score s group by s.scoure HAVING count(s.sid)>2 ORDER BY count(s.sid) desc,s.scoure ;
-- 4.7、查询两门以上不及格课程的同学的学号及其平均成绩
SELECT s.sid , AVG(s.sscore)
       FROM score s 
       WHERE s.sscore<60
      group by s.sid 
      HAVING COUNT(s.scoure)>2;
-- 5、查询学生的总成绩并进行排序
     SELECT s.sid , sum(s.sscore)
          FROM score s 
          group by s.sid
          ORDER by sum(s.sscore);
-- 5.1、查询平均成绩大于60分的学生学号和平均成绩
         SELECT s.sid,AVG(s.sscore)
              from score s 
              group by s.sid 
              HAVING AVG(s.sscore)>60
              ORDER BY AVG(s.sscore) ;
 --复杂查询
 
-- 6、查询所有课程成绩小于80分学生的学号、姓名
             --法一
 SELECT s.sid,s2.sname
      from score s 
      join student s2
      on s.sid =s2.sid 
      WHERE s.sscore <80;
     --法二
     SELECT s2.sid ,s2.sname 
           FROM student s2
            where s2.sid in
           (SELECT s.sid
	            from score s
			where s.sscore<80);
     
-- 6.1、查询没有学全所有课的学生的学号、姓名
		SELECT s3.sid ,s3.sname from student s3 where s3.sid in (
		-- 2.把学生id作为筛选条件葱student表中找到
		SELECT s.sid -- 1.先把没有学全的学生id找到
		    from score s  
		    group by s.sid 
		    HAVING count(*)<(SELECT count(*) from course c));
--6.2、查询出只选修了两门课程的全部学生的学号和姓名
		   --法一
		   SELECT s2.sid ,s2.sname 
		      FROM student s2 
		      where s2.sid in
		   (SELECT s.sid 
		     from score s 
		     group by s.sid 
		     HAVING count(s.sid)=2)
/*
查找1990年出生的学生名单
学生表中出生日期列的类型是datetime
*/
		     
SELECT * from student s where s.sbirth LIKE  '%1990%' ;	
--按照学生id对score进行排序并输出前三条记录
SELECT  top 3 * FROM  score order by sid;
-- 查询各学生的年龄（精确到月份）
SELECT SUBSTRING(s.sbirth,6,7)from student s
-- 找出本月过生日的学生
select * 
from student 
where month(SUBSTRING(s.sbirth,6,10) )= month(current_date);
-- 7.查询所有学生的学号、姓名、选课数、总成绩
 
SELECT s.sid,count(s2.scoure),SUM(s2.sscore) 
from student s left join score s2
on s.sid =s2.sid 
group by s.sid 
		    
 
select a.sid,count(b.scoure),sum(b.sscore)
from student as a left join score as b
on a.sid = b.sid
group by a.sid;
 
-- 7.1、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
SELECT s2.sid , s2.sname
from student s2  where s2.sid in-- 1
(SELECT  s.sid
from score s 
group by s.sid 
HAVING AVG(s.sscore)>85)
 
select a.sid ,max(a.sname) ,avg(b.sscore)
from student as a left join score as b
on a.sid  = b.sid 
group by a.sid 
having avg(b.sscore)>85;
 
 
-- 7.2、查询学生的选课情况：学号，姓名，课程号，课程名称
SELECT  s.sid ,s.sname ,c.scoure ,c.cname 
from student s  
left  join score s2 on s.sid =s2.sid 
LEFT  join course c on s2.scoure =c.scoure;
-- 7.3、查询出每门课程的及格人数和不及格人数（***）
SELECT 
s.scoure 
,sum(case when s.sscore<60 then 1 else 0 end ) as '不及格人数'
,sum(case when s.sscore>=60 then 1 else 0 end ) as '及格人数'
from score s 
GROUP by s.scoure 
-- 7.4、使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称
SELECT s2.scoure ,c.cname 
,sum(case when s2.sscore<60 then 1 else 0 end ) as '不及格人数'
,sum(case when s2.sscore between 60 and 70 then 1 ELSE 0 END) as '60-70分人数'
,sum(case when s2.sscore BETWEEN 70 and 80 then 1 ELSE 0 END) as '70-80分人数'
,sum(case when s2.sscore BETWEEN 85 and 100 then 1 ELSE 0 END) as '85-100分人数'
from score s2
left join course c on s2.scoure =c.scoure 
group by s2.scoure ,c.cname ;
 
-- 7.5、查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名
SELECT a.sid ,s2.sname,a.sscore  
from student s2 join 
(SELECT *
from score s 
where s.scoure =3 and s.sscore >80) a
on s2.sid =a.sid 
 
 
SELECT s.sid ,s.sname,s2.sscore  
from student s inner join score s2 
on s.sid =s2.sid 
where s2.scoure =3 and s2.sscore >80
 
 
-- 8.sql面试题：行列如何互换？（****）
select s.sid  ,'课程号0001','课程号0002','课程号0003'
from score s ;
 
select s.sid
,MAX((case s.scoure  when 1 then s.sscore  else 0 end )) as '课程号0001'-- 当s.scoure =1时输出s.sscore 否则就为0
,max((case s.scoure when 2 then s.sscore else 0 end)) as '课程号0002'
,max((case s.scoure when 3 then s.sscore else 0 end))as '课程号0003'
from score s 
group by s.sid ;
--9.多表连接
--9.1、检索0001课程分数小于90，按照分数降序排列的学生信息
SELECT s3.*,a.scoure ,a.sscore 
from student s3 right join 
(SELECT *
from score s2 
where s2.sscore <90 and s2.scoure =1 
) a
 on s3.sid =a.sid 
 ORDER BY a.sscore DESC 
 
SELECT * from score s ;
 
 
SELECT s.*,s2.sscore ,s2.scoure 
from student s inner join score s2 
on s.sid =s2.sid 
where s2.sscore <90 and s2.scoure =1
ORDER BY s2.sscore DESC ;
-- 9.2、查询不同老师所教授不同课程平均分从高到低显示
SELECT t.cteacher,avg(s.sscore)
from score s
join course c on s.scoure =c.scoure 
join teacher t on t.cteacher =c.cteacher 
GROUP by t.cteacher 
ORDER BY  AVG(s.sscore) DESC  
 
 
SELECT * from teacher t ;
-- 9.3 查询课程名称为"数学"，且分数低于60的学生姓名和分数
SELECT s2.sname ,s.sscore,s.sid  
from course c 
inner join score s on c.scoure  =s.scoure 
INNER join student s2 on s.sid =s2.sid 
where c.cname ='数学' and s.sscore <90
-- 9.4、查询任何一门课程成绩在70分以上的姓名、课程名称和分数（与上题类似）
SELECT s3.sname ,c2.cname ,s4.sscore 
from student s3
join score s4 on s3.sid =s4.sid 
join course c2 on c2.scoure =s4.scoure 
WHERE s4.sscore >70
-- 9.5、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
--法1⃣
SELECT s.sid ,avg(s2.sscore)
from student s 
join score s2 on s.sid =s2.sid
WHERE s2.sscore <90 --先整体过滤出不及格(先初步筛选后分组，再对分组进一步筛选)
group by s.sid --再根据学生id进行分组
HAVING count(s.sid)>=2;--每个学生里面的记录大于等于2就说明
 
--法二
SELECT s.sid ,avg(s2.sscore)
from student s 
join score s2 on s.sid =s2.sid
group by s.sid --（先分组，后对每一组进行筛选）
HAVING sum(case when s2.sscore<90 then 1 ELSE 0 end)>=2;
 
-- 9.6、查询学生的总成绩并进行排名
SELECT s.sid ,AVG(s.sscore) 
from score s 
group by s.sid 
ORDER by sum(s.sscore) 
 
--9.7、查询平均成绩大于60分学生的学号和平均成绩
SELECT s.sid ,avg(s.sscore) 
FROM score s
group by s.sid 
HAVING AVG(s.sscore)>60 
 
-- 10.1、查询所有课程成绩小于60分学生的学号、姓名
--法一
SELECT s.sid ,s2.sname ,s.sscore 
FROM score s 
join student s2 on s.sid =s2.sid 
WHERE s.sscore <90;
--法二
SELECT s2.sid ,s2.sname  
FROM  student s2
where s2.sid  IN 
(SELECT s.sid 
from score s 
where s.sscore <90);
-- 10.2、查询没有学全所有课的学生的学号、姓名
SELECT s.sid ,s2.sname 
from score s
join student s2 on s.sid =s2.sid 
GROUP BY s.sid,s2.sname  
HAVING count(*)<(SELECT count(*) from course c );
 
-- 10.3、查询出只选修了两门课程的全部学生的学号和姓名
SELECT s.sid ,s2.sname 
from score s
join student s2 on s.sid =s2.sid 
GROUP BY s.sid,s2.sname  
HAVING count(*)=2;
/*
 * CURRENT_DATE :2022-04-03
 * current_time:11:05:23
 * current_timestamp:2022-04-03 05:23
 * 
 * */
 SELECT YEAR ('2022-04-03')   --2022
 SELECT MONTH  ('2022-04-03')   --4
 SELECT day ('2022-04-03')   --3
 
select * from score s 
 
--11.1、查询所有学生的学号、姓名、选课数、总成绩
select s.sid ,s.sname ,count(*),sum(s2.sscore)
FROM student s 
join score s2 on s.sid =s2.sid 
GROUP BY s.sid ,s.sname; 
--11.2、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
SELECT s.sid ,s.sname ,AVG(s2.sscore) 
from student s 
join score s2 on s.sid =s2.sid 
GROUP BY s.sid,s.sname  
HAVING AVG(s2.sscore)>85 ;
--11.3、查询学生的选课情况：学号，姓名，课程号，课程名称
SELECT s.sid ,s.sname ,s2.scoure ,c.cname
FROM student s 
join score s2 on s.sid =s2.sid 
JOIN course c on s2.scoure =c.scoure ;
--GROUP  by s.sid ,s.sname ,s2.scoure ,c.cname 
--11.4、查询出每门课程的及格人数和不及格人数
SELECT s.scoure as '课程号'
,SUM(CASE when s.sscore<60 then 1 else 0 end) as '不及格人数'
,SUM(case when s.sscore>60 then 1 else 0 end)  as '及格人数'
from score s 
group by s.scoure ;
--11.5、使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称
--考察case when,between  小 and 大
SELECT s.scoure ,c.cname 
,sum(case when s.sscore between 85 and 100 then 1 else 0 END ) as '100-85'
,sum(case when s.sscore BETWEEN 70 and 85 then 1 else 0 END ) as '85-70'
,sum(CASE WHEN s.sscore  BETWEEN 60 and 70 then 1 else 0 END ) as '70-60'
,sum(case when s.sscore<60 then 1 else 0 END ) as '小于60'
from score s 
join course c on s.scoure =c.scoure 
GROUP by s.scoure ,c.cname ;
--11.6、查询课程编号为3且课程成绩在80分以上的学生的学号和姓名
SELECT * from score s2 
select s3.sid ,s3.sname 
FROM score s 
join student s3 on s.sid =s3.sid  
where s.scoure =3 and s.sscore  >80;
 
--11.7、对score进行行转列
SELECT * FROM  score s2 
--第一步：
SELECT s.sid ,'课程表001','课程表002','课程表003'
FROM score s ;
--第二步：
SELECT s.sid --每一条记录都会走下面的3个case when
--例如sid=1,source=1,sscore=80,
--第一个case when 判断scoure是否为1，是的话就输出80，否则就输出0
--第二个case when 判断scoure是否为2，不是话就输出0
--第三个case when 判断scoure 是否为3，不是的话就输出0
,case s.scoure when 1 then s.sscore  else 0 end as '课程表001'
,case s.scoure when 2 then s.sscore  else 0 end as '课程表002'
,case s.scoure when 3 then s.sscore  else 0 end as '课程表003'
FROM score s ;
 
--第三步：
SELECT s.sid 
,sum(case s.scoure when 1 then s.sscore  else 0 end) as '课程表001'
,sum(case s.scoure when 2 then s.sscore  else 0 end) as '课程表002'
,sum(case s.scoure when 3 then s.sscore  else 0 end) as '课程表003'
FROM score s 
group by  s.sid ;
 
-- 12.1、-检索"0001"课程分数小于90，按分数降序排列的学生信息
SELECT s2.*,s.*
from score s 
join student s2 on s.sid =s2.sid 
where s.scoure =1 and s.sscore <90
ORDER by s.sscore DESC ;
 
--12.2、-查询不同老师所教不同课程平均分从高到低显示
 
SELECT t.tname ,t.cteacher,AVG(s.sscore) 
FROM score s 
join course c on s.scoure =c.scoure 
join teacher t on t.cteacher =c.cteacher 
GROUP BY t.tname,t.cteacher  
ORDER BY AVG(s.sscore) DESC;
 
--12.3、查询课程名称为"数学"，且分数低于90的学生姓名和分数
SELECT s.sid,s.sname,s2.sscore 
FROM student s 
join score s2 on s.sid =s2.sid 
join course c on s2.scoure =c.scoure 
where c.cname ='数学' and s2.sscore <90;
 
--12.4、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
SELECT s.sname ,c.cname ,s2.sscore 
FROM student s
join score s2 on s.sid =s2.sid
join course c on s2.scoure =c.scoure 
where s2.sscore >70;
 
--12.5、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT s.sid ,s.sname ,avg(s2.sscore)
FROM  student s 
join score s2 on s.sid =s2.sid 
where s2.sscore <90  --先过滤
group by s.sid ,s.sname 
HAVING count(*)>=2; --后对分组数据就行筛选
 
-- 12.6、查询课程编号为“0001”的课程比“0002”的课程成绩等于或者低的所有学生的学号，成绩，和课程号
 
--思路：把0001和0002课程的学生分别找出来然后对两个临时表进行join然后进行筛选
--先把0001和0002课程的学生分别找出来
 
SELECT a.* 
FROM 
  (select * from score s where s.scoure=1) as a
  JOIN 
  (select * from score s where s.scoure=2) as b
on a.sid =b.sid 
where a.sscore <=b.sscore ;
		     
--12.7、查询学过“孟扎扎”老师所教的所有课的同学的学号、姓名
SELECT s.sid,s.sname 
from student s 
join score s2 on s.sid =s2.sid 
join course c on c.scoure =s2.scoure 
join teacher t on t.cteacher =c.cteacher 
where t.tname ='孟扎扎'
 
-- 12.8、查询没学过"孟扎扎"老师讲授的任一门课程的学生姓名
SELECT s3.sname 
FROM student s3 
where s3.sid not in 
(
SELECT s.sid  
from student s 
join score s2 on s.sid =s2.sid 
join course c on c.scoure =s2.scoure 
join teacher t on t.cteacher =c.cteacher 
where t.tname ='孟扎扎'
)