MYSQL经典50道题

2021-04-01 本文已影响0人任笙_8b8c

sql 知识点

- CASE WHEN b.score < 60 THEN 1 ELSE 0 END解释:

如果b.score这个字段的值小于60,得到的结果就+1 是score字段<60 的记录和是多少 SUM(CASE WHEN b.score<60 THEN 1 ELSE 0 END)

- HAVING关键字

通常where语句是在group by之前做数据筛选的，而having语句是对group by之后的结果进行筛选的。

- EXISTS()函数是什么和in的区别

EXISTS 会对外表student 进行循环查询匹配，它不在乎后面的内表子查询的返回值是什么，只在乎有没有存在返回值，存在返回值，则条件为真，该条数据匹配成功，加入查询结果集中；如果没有返回值，条件为假，丢弃该条数据。
场景选择:外查询表大，子查询表小，选择IN；外查询表小，子查询表大，选择EXISTS；若两表差不多大，则差不多。子查询的表大的时候，使用EXISTS可以有效减少总的循环次数来提升速度；当外查询的表大的时候，使用IN可以有效减少对外查询表循环遍历来提升速度。
用法:

SELECT a.*
FROM student a
INNER JOIN sc b
ON a.s=b.s
WHERE EXISTS(
   SELECT * FROM sc WHERE s='01' AND c=b.c
)
GROUP BY 1,2,3,4 ;

- left join、right join和join的区别

https://blog.csdn.net/Li_Jian_Hui_/article/details/105801454

left join: 以左表为主表去查询
right join: 以右表为主表去查询

图片.png

inner join: 查交集

图片.png

表设计: https://blog.csdn.net/weixin_38611497/article/details/89299582

1.查询"01"课程比"02"课程成绩高的学生的信息及课程分数

SELECT
    s1.*,
    s2.score,
    s3.score 
FROM
    student s1
    JOIN sc s2 ON s1.s = s2.s 
    AND s2.c = '01'
    JOIN sc s3 ON s1.s = s3.s 
    AND s3.c = '02' 
WHERE
    s2.score > s3.score

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

SELECT
    a.*,
    AVG( b.score ) 
FROM
    student a
    INNER JOIN sc b ON a.s = b.s 
GROUP BY
    a.s,
    a.sname 
HAVING
    AVG( b.score ) >= 60

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
05、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

SELECT
    a.s 编号,
    a.Sname 姓名,
    COUNT( b.c ) 总数,
    SUM( b.score ) 总成绩
FROM
    student a
    INNER JOIN sc b ON a.s = b.s GROUP BY b.s

6、查询"李"姓老师的数量

SELECT
COUNT(*) 
FROM
    teacher where Tname LIKE '李%'

7.查询学过"张三"老师授课的同学的信息

SELECT
    a.* 
FROM
    student a
    JOIN sc b ON a.s = b.s
    JOIN course c ON b.c = c.c
    JOIN teacher d ON c.t = d.t 
WHERE
    d.Tname = "张三"

8.查询没学过"张三"老师授课的同学的信息

SELECT
    * 
FROM
    student a
    left JOIN sc b ON a.s = b.s 
WHERE
    NOT EXISTS (
 SELECT *
              FROM course aa
              INNER JOIN teacher b
              ON aa.t=b.t
              INNER JOIN sc c
              ON aa.c=c.c
              WHERE b.tname='张三'
              AND c.s=a.s
    )  
GROUP BY 1,2,3,4 ;

9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

SELECT
    * 
FROM
    student a
    JOIN sc b ON a.s = b.s 
    AND b.c = "01"
    JOIN sc c ON a.s = c.s 
    AND c.c = "02" 
GROUP BY
    1,
    2,
    3,
    4;

10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
NOT EXISTS 不存在这题应该先查出存在02的然后关联到主表再用 NOT EXISTS

SELECT
    * 
FROM
    student a
    JOIN sc b ON a.s = b.s 
    AND b.c = "01" 
WHERE
    NOT EXISTS ( SELECT * FROM sc d WHERE d.c = "02" and a.s = d.s )


第二种
select *
from student a
left join sc b
on a.s=b.s and b.c='01'
left join sc c
on a.s=c.s and c.c='02'
where b.c='01' and c.c is null ;

11.查询没有学全所有课程的同学的信息
思路:先查询所有课程的总数再查询每个人学生学得总数用having做比较

SELECT
    *,
    COUNT( b.c ) 
FROM
    student a
    LEFT JOIN sc b ON a.s = b.s
    LEFT JOIN ( SELECT count( * ) anum FROM course ) c ON 1 = 1 
GROUP BY
    1 
HAVING
c.anum  > COUNT( b.c )

12.查询至少有一门课与学号为"01"的同学所学相同的同学的信息

推荐  :SELECT
    a.* 
FROM
    student a
    INNER JOIN sc b ON a.s = b.s 
WHERE
    EXISTS ( SELECT * FROM sc WHERE s = '01' AND c = b.c )
GROUP BY 1,2,3,4 ;



select * from student a join  sc b on a.s=b.s left join course c on b.c in(SELECT
    b.c 
FROM
    student a
    JOIN sc b ON a.s = b.s
WHERE
    a.s = "01")

     GROUP BY 1

14、查询没学过"张三"老师讲授的任一门课程的学生姓名

SELECT
   * 
FROM
   student 
WHERE
   Sname NOT IN (
SELECT
   Sname 
FROM
   student a
   JOIN sc b ON a.s = b.s
   JOIN course c ON b.c = c.c 
WHERE
   c.t = "01" 
GROUP BY
   1 
   )

17.检索"01"课程分数小于60，按分数降序排列的学生信息

SELECT
    * ,b.score
FROM
    student a
    JOIN sc b ON a.s = b.s
    JOIN course c ON b.c = c.c 
     AND c.c = "01" 
WHERE
    b.score < 60
GROUP BY
    1 
ORDER BY
    b.score DESC

17.检索"01"课程分数小于60，按分数降序排列的学生信息

SELECT
* ,b.score
FROM
student a
JOIN sc b ON a.s = b.s
JOIN course c ON b.c = c.c
AND c.c = "01"
WHERE
b.score < 60
GROUP BY
1
ORDER BY
b.score DESC

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT
    *,
    GROUP_CONCAT( b.score ),
    AVG( b.score ),
    GROUP_CONCAT( c.Cname ) 
FROM
    student a
    LEFT JOIN sc b ON a.s = b.s
    LEFT JOIN course c ON b.c = c.c 
GROUP BY 1,2,3,4 
ORDER BY AVG( b.score ) DESC

select * ,
SUM(CASE WHEN b.c="01" THEN b.score ELSE 0 END ) a1,
SUM(CASE WHEN b.c="02" THEN b.score ELSE 0 END ) a2,
SUM(CASE WHEN b.c="03" THEN b.score ELSE 0 END ) a3,
AVG(CASE WHEN b.score=0 THEN 0 ELSE b.score END ) ss,
GROUP_CONCAT( c.Cname )
from  student a left join sc b on a.s=b.s left join course c on b.c=c.c GROUP BY 1,2,3,4 ORDER BY ss desc

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

及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

SELECT
    b.c,
    b.cname,
    MAX( a.score ),
    MIN( a.score ),
    AVG( a.score ) ,
    SUM(CASE WHEN a.score>60 THEN a.score  ELSE 0 END)/COUNT(1) 及格率,
    SUM(CASE WHEN a.score>70 AND a.score<80 THEN 1  ELSE 0 END)/COUNT(1) 中等率,
    SUM(CASE WHEN a.score>80 AND a.score<90 THEN   1  ELSE 0 END)/COUNT(1) 优良率,
    SUM(CASE WHEN a.score>90 THEN   1  ELSE 0 END)/COUNT(1) 优秀率
FROM
    sc a
    JOIN course b ON a.c = b.c 
GROUP BY
    1,2

19、按各科成绩进行排序，并显示排名

SELECT
    * ,sum(CASE WHEN b.c="01" then b.score else 0 END) 分数,
    sum(CASE WHEN b.c="02" then b.score else 0 END) 分数,
    sum(CASE WHEN b.c="03" then b.score else 0 END) 分数,
    SUM(b.score)
FROM
    student a
    LEFT JOIN sc b ON a.s = b.s
    LEFT JOIN course c ON b.c = c.c GROUP BY 1 ORDER BY SUM(b.score) desc

20.查询学生的总成绩并进行排名

SELECT
    a.*,
    SUM( b.score )  c
FROM
    student a
    JOIN sc b ON a.s = b.s 
GROUP BY
    a.s 
    ORDER BY c desc

21、查询不同老师所教不同课程平均分从高到低显示

SELECT
    *,
    AVG( a.score ) 
FROM
    sc a
    LEFT JOIN course b ON a.c = b.c
    JOIN teacher c ON b.t = c.t 
GROUP BY
    c.t 
ORDER BY
    AVG( a.score ) DESC

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

 先查询出所有课程的具体分数,再查询同一个表中俩个字段的比较值 这样count(大的分数)就是排名第一 
    select *from  student a1 join  (SELECT
    a.* ,b.cname,COUNT(c.c)+1 AS tp
FROM
    sc a
    LEFT JOIN course b ON a.c = b.c 
  LEFT join sc c on a.c=c.c and a.score < c .score
GROUP BY 1,2,3,4
HAVING COUNT(c.c)+1 IN(2,3)
ORDER BY a.c ,tp) as d on  a1.s = d.s

23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

SELECT
    b.c,
    b.cname ,
    SUM(CASE WHEN a.score > 85 and a.score <100 THEN 1 ELSE 0 end) AS '[100-85]' ,
    SUM(CASE WHEN a.score > 85 and a.score <100 THEN 1 ELSE 0 end)/count(1) AS '[100-85]%'
FROM
    sc a
    JOIN course b ON a.c = b.c 
GROUP BY 1,2

24、查询学生平均成绩及其名次自己对自己左交，查看比自己分数高的有几个


SELECT
    s1.*,
    count( s2.S ) + 1 FROM(
SELECT
     a.*,
    AVG(CASE WHEN b.score IS NULL THEN 0 ELSE b.score END) AS ascore
FROM
    student a
    LEFT JOIN sc b ON a.s = b.s 
GROUP BY 1,2 ) as s1 LEFT JOIN (
SELECT
     a.*,
    AVG(CASE WHEN b.score IS NULL THEN 0 ELSE b.score END) AS ascore
FROM
    student a
    LEFT JOIN sc b ON a.s = b.s 
GROUP BY
    1,
    2 
    ) as s2 ON s1.ascore < s2.ascore 
GROUP BY 1,2,3,4,5 ;

25、查询各科成绩前三名的记录

SELECT
    a.* ,count(b.c)+1 ss
FROM
    sc a left join sc b 
    on a.c=b.c and a.score < b.score 
GROUP BY
    1,2,3
    HAVING  ss<=3 ORDER BY a.c,ss ;

26、查询每门课程被选修的学生数

select * ,count(s) from sc GROUP BY 2

27、查询出只有两门课程的全部学生的学号和姓名

SELECT
    *,
    count( c ) 
FROM
    student a
    JOIN sc b ON a.s = b.s 
GROUP BY
    1 
HAVING
    count( c ) = 2

28、查询男生、女生人数

select count(1) from student GROUP BY Ssex

29、查询名字中含有"风"字的学生信息

select * from student where  Sname LIKE  '%风%'

30、查询同名同性学生名单，并统计同名人数

SELECT sname
       ,ssex
       ,COUNT(1)
FROM student
GROUP BY 1,2
HAVING COUNT(1) > 1 ;

31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)

select * from student where  YEAR(sage) = "1990"

32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号

select * ,AVG(a.score) ascore  FROM   sc a left join course b on a.c = b.c GROUP BY 2 ORDER BY ascore desc ,a.c desc

33 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

select *,AVG(b.score) avgscore fROM student a join sc b on a.s=b.s  GROUP BY 1 HAVING  avgscore >85

34、查询课程名称为"数学"，且分数低于60的学生姓名和分数

SELECT
    c.Sname,b.score 
FROM
    course a
     LEFT JOIN sc b on  a.c = b.c
    LEFT  JOIN student c ON b.s = c.s
WHERE
    a.Cname = "数学" and b.score < 60

35、查询所有学生的课程及分数情况

select * from student a join sc b on a.s=b.s join course c on b.c =c.c

36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数

select * from student a join sc b on a.s=b.s join course c on b.c =c.c  where b.score>70

37、查询不及格的课程

select * from sc b  join course c on b.c =c.c  where b.score<60 GROUP BY 2

38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名

select * from student a join sc b on a.s=b.s join course c on b.c =c.c  where b.c="01" and b.score>80

40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

SELECT a.*,b.score
FROM student a
INNER JOIN sc b
ON a.s=b.s
INNER JOIN(
       SELECT c.c
              ,MAX(c.score) AS maxscore
       FROM teacher a
       INNER JOIN course b
       ON a.t=b.t
       INNER JOIN sc c
       ON b.c=c.c
       WHERE a.tname='张三'
       GROUP BY c)c
ON b.c=c.c AND b.score=c.maxscore ;

41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

select a.*,count(b.s) from sc a left join sc b on a.s=b.s and a.score = b.score  and a.c !=b.c GROUP BY 1,2,3 HAVING  count(b.s)

42、查询每门功成绩最好的前两名同19

43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select *,count(a.c) as counts from course a join sc b on a.c = b.c GROUP BY 1,2,3 HAVING count(a.c) >5 ORDER BY counts desc , a.c asc

44、检索至少选修两门课程的学生学号

select a.*,count(b.c) from sc a left join course b on a.c = b.c GROUP BY a.s HAVING count(b.c) >=2

46、查询各学生的年龄 year(getdate())

select  year(now()) - year(Sage) from student
SELECT a.*,YEAR(CURDATE())-YEAR(a.sage)
FROM student a ;

47、查询本周过生日的学生 WEEKOFYEAR() 范围是从1到53的日历周给一个字符串的时间返回多少周

select *,WEEKOFYEAR(student.Sage),WEEKOFYEAR(now())
from student
where WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE());



- 48、查询下周过生日的学生

select *,WEEKOFYEAR(student.Sage),WEEKOFYEAR(now())+1
from student
where WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE())+1;



- 49、查询本月过生日的学生

select *,MONTH(student.Sage),MONTH(now())
from student where MONTH(student.Sage)=MONTH(now())



- 50 查询下月过生日的学生

select *,MONTH(student.Sage),MONTH(now())+1
from student where MONTH(student.Sage)=MONTH(now())+1

MYSQL经典50道题

sql 知识点

- CASE WHEN b.score < 60 THEN 1 ELSE 0 END解释:

- HAVING关键字

- EXISTS()函数是什么和in的区别

- left join、right join和join的区别

表设计: https://blog.csdn.net/weixin_38611497/article/details/89299582

猜你喜欢

热点阅读

MYSQL经典50道题

sql 知识点

- CASE WHEN b.score < 60 THEN 1 ELSE 0 END解释:

- HAVING关键字

- EXISTS()函数是什么 和in的区别

- left join、right join和join的区别

表设计: https://blog.csdn.net/weixin_38611497/article/details/89299582

猜你喜欢

热点阅读

- EXISTS()函数是什么和in的区别