关于面试总结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);

《Python3接口自动化第7期》12月8号开学