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

交流QQ群:779429633

posted @ 2018-12-14 17:44  上海-悠悠  阅读(66272)  评论(0编辑  收藏  举报