MYSQL经典面试题

 

 

 

 

前言

用一条SQL 语句查询xuesheng表每门课都大于80 分的学生姓名，这个是面试考sql的一个非常经典的面试题

having和not in

查询 xuesheng表每门课都大于80 分的学生姓名
name
kecheng
score
张三
语文
81
张三
数学
73
李四
语文
86
李四
数学
90
王五
数学
89
王五
语文
88
王五
英语
96

解决办法一： having

如果不考虑学生的课程少录入情况（比如张三只有2个课程，王五有3个课程）
SELECT name
FROM xuesheng
GROUP BY name
HAVING MIN(score)> 80
如果考虑学生的课程数大于等于3的情况
SELECT name
FROM xuesheng
GROUP BY name
HAVING MIN(score)> 80
AND COUNT(kecheng)>=3

解决办法二：not in

可以用反向思维，先查询出表里面有小于80分的name,然后用not in去除掉
SELECT DISTINCT name
FROM xuesheng
WHERE name NOT IN
    (SELECT DISTINCT name
    FROM xuesheng
    WHERE score <=80);

 

删除

学生表xueshengbiao 如下:自动编号 学号 姓名 课程编号 课程名称 分数
autoid
id
name
kcid
kcname
score
1
2005001
张三
0001
数学
69
2
2005002
李四
0001
数学
89
3
2005001
张三
0001
数学
69
删除除了自动编号不同, 其他都相同的学生冗余信息
DELETE t1
FROM xueshengbiao t1, xueshengbiao t2
WHERE t1.id = t2.id
and t1.kcid = t2.kcid
and t1.kcname = t2.kcname
and t1.score = t2.score
and t1.autoid < t2.autoid
如果只是查询出自动编号不同, 其他都相同的学生冗余信息，可以用group by
SELECT * from xueshengbiao t1
WHERE t1.autoid
NOT IN
    (SELECT MIN(autoid) as autoid FROM xueshengbiao
    GROUP BY id, name, kcid, kcname, score)

 

模糊查询%

表名：student ，用sql查询出“张”姓学生中平均成绩大于75分的学生信息；
name
kecheng
score
张青
语文
72
张华
英语
81
王华
数学
72
张青
物理
67
李立
化学
98
张青
化学
76
select * from student
where name in
(select name from student
where name like '张%' group by name having avg(score) > 75);

 

SQL 通配符

在 SQL 中，通配符与 SQL LIKE 操作符一起使用。SQL 通配符用于搜索表中的数据。在 SQL 中，可使用以下通配符：
通配符
描述
%
替代 0 个或多个字符
_
替代一个字符
[charlist]
字符列中的任何单一字符
[^charlist]或[!charlist]
不在字符列中的任何单一字符
MySQL 中使用 REGEXP 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE) 来操作正则表达式
找出姓张和姓李的同学, 用rlike实现匹配多个
-- 找出姓张和姓李的
select * from xuesheng
where name in
(select name from xuesheng
where name rlike '[张李]' group by name having avg(score) > 75);
也可以用 REGEXP，结合正则匹配
select * from xuesheng
where name in
(select name from xuesheng
where name REGEXP '^[张李]' group by name having avg(score) > 75);
 
 
 
 
 
posted @ 2020-03-10 17:26  dearddu  阅读(212)  评论(0)  编辑  收藏  举报