关于面试总结6-SQL经典面试题
前言
用一条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.name = t2.name
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);