## 前言 用一条SQL 语句查询student2表每门课都大于80 分的学生姓名，这个是面试考sql的一个非常经典的面试题 ## **having和not in** 查询 student2表每门课都大于80 分的学生姓名 | name | course | score | | --- | --- | --- | | 张三 | 语文 | 81 | | 张三 | 数学 | 73 | | 李四 | 语文 | 86 | | 李四 | 数学 | 90 | | 王五 | 数学 | 89 | | 王五 | 语文 | 88 | | 王五 | 英语 | 96 | ### **解决办法一： having** 如果不考虑学生的课程少录入情况（比如张三只有2个课程，王五有3个课程） ~~~ SELECT name FROM student2 GROUP BY name HAVING MIN(score)> 80 ~~~ 如果考虑学生的课程数大于等于3的情况 ~~~ SELECT name FROM student2 GROUP BY name HAVING MIN(score)> 80 AND COUNT(course)>=3 ~~~ ### **解决办法二：not in** 可以用反向思维，先查询出表里面有小于80分的name,然后用not in去除掉 ~~~ SELECT DISTINCT name FROM student2 WHERE name NOT IN (SELECT DISTINCT name FROM student2 WHERE score <=80); ~~~  <br /> ## **删除** 学生表student3 如下:自动编号 学号 姓名 课程编号 课程名称 分数 | autoid | id | name | kcid | course | score | | --- | --- | --- | --- | --- | --- | | 1 | 2005001 | 张三 | 0001 | 数学 | 69 | | 2 | 2005002 | 李四 | 0001 | 数学 | 89 | | 3 | 2005001 | 张三 | 0001 | 数学 | 69 | 删除除了自动编号不同, 其他都相同的学生冗余信息 ~~~ DELETE t1 FROM student3 t1, student3 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 student3 t1 WHERE t1.autoid NOT IN (SELECT MIN(autoid) as autoid FROM student3 GROUP BY id, name, kcid, kcname, score) ~~~  <br /> ## **模糊查询%** 表名：student ，用sql查询出“张”姓学生中平均成绩大于75分的学生信息； | name | course | 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); ~~~ <br /> ## **SQL 通配符** 在 SQL 中，通配符与 SQL LIKE 操作符一起使用。SQL 通配符用于搜索表中的数据。在 SQL 中，可使用以下通配符： | 通配符 | 描述 | | --- | --- | | % | 替代 0 个或多个字符 | | \_ | 替代一个字符 | | \[charlist\] | 字符列中的任何单一字符 | | \[^charlist\]或\[!charlist\] | 不在字符列中的任何单一字符 | **MySQL 中使用 REGEXP 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE) 来操作正则表达式** 找出姓张和姓李的同学, 用rlike实现匹配多个 ~~~ -- 找出姓张和姓李的 select * from student where name in (select name from student where name rlike '[张李]' group by name having avg(score) > 75); ~~~ 也可以用 REGEXP，结合正则匹配 ~~~ select * from student where name in (select name from student where name REGEXP '^[张李]' group by name having avg(score) > 75); ~~~