【MySQLMariadb】SELECT备忘录

0.引

在笔试面试过程中，后端开发的岗位难免会问到数据库方面的知识，理论性的东西当然需要熟知，当然实际操作也不能落下。

在春招的几次面试中，被问到几个关于SQL方面的问题，因为很久没看，复杂一点的SQL语句都不会写了，实在惭愧。

这几天又重新复习了一下，SQL的重难点还是在于查询语句，所以本水文就针对SELECT语句做一下总结（复杂的部分如缓存/分表没深入研究，暂时跳过），同时末尾还有几道常见的面试题。

全部示例已在Ubuntu16.10上Mariadb10.1.22成功运行，原则上与MySQL无差别。

1.基本查询

SELECT语句根据官网的 Reference Manual，语法格式如下：

SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr [, select_expr ...] [FROM table_references [PARTITION partition_list] [WHERE where_condition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [PROCEDURE procedure_name(argument_list)] [INTO OUTFILE 'file_name' [CHARACTER SET charset_name] export_options | INTO DUMPFILE 'file_name' | INTO var_name [, var_name]] [FOR UPDATE | LOCK IN SHARE MODE]]

先创建两个测试表：

create table s( id integer primary key, student_id integer, score integer ); create table t( id integer primary key, student_id integer, score integer ); INSERT INTO s(id,student_id,score) values(1,1,80); INSERT INTO s(id,student_id,score) values(2,1,80); INSERT INTO s(id,student_id,score) values(3,2,88); INSERT INTO s(id,student_id,score) values(4,2,90); INSERT INTO s(id,student_id,score) values(5,3,92); INSERT INTO s(id,student_id,score) values(6,4,78); INSERT INTO s(id,score) values(7,78); INSERT INTO t(id,student_id,score) values(1,2,82); INSERT INTO t(id,student_id,score) values(2,5,99); INSERT INTO t(id,student_id,score) values(3,2,88);

1.1.ALL/DISTINCT

ALL (the default)：返回查询结果的所有行，无论查询结果中是否有重复项，ALL是默认的修饰符

DISTINCT/DISTINCTROW: 自动过滤重复的行，DISTINCTROW和DISTINCT作用相同

注：ALL和DISTINCT只能修饰一个查询结果

例：查询s表中的所有记录，包括重复项

SELECT ALL * FROM s;

查询s表中的所有记录，不包括重复项

SELECT DISTINCT * FROM s;

1.2.LIMIT

截取查询结果，如截取前N条，格式为 LIMIT 起始位置，长度；

例：查询s表中的前三条记录

SELECT * FROM s LIMIT 0,3;

1.3. WHERE

WHERE可以对查询结果进行过滤，其中：

1.3.1. OR / AND

例：查询id大于2并且成绩大于80的学生记录

SELECT * FROM s WHERE id>2 AND score>80;

1.3.2. BETWEEN

语法格式：BETWEEN A AND B 取值在A到B之间

例：查成绩在90到100的学生记录

SELECT * FROM s WHERE score BETWEEN 90 AND 100;

1.3.3. IS [NOT] NULL

判断字段是否为空，在SELETE时，不能通过a=’NULL’简单判断该字段为空，而应该使用 IS [NOT] NULL

例：查询s表中student_id不为空的学生记录

SELECT * FROM s WHERE student_id IS NOT NULL;

1.3.4. IN

多值判断，语法格式：IN (A,B,C) 取值为A或B或C

例：查询s表中成绩为80或92的学生

SELECT * FROM s WHERE score IN (80,92);

1.3.5.LIKE/REGEXP

LIKE 表示模糊查询，其条件为：

% 表示匹配0到n个字符（like不会匹配null） _ 匹配1个字符

REGEXP ‘正则表达式’

SELECT * FROM s WHERE score LIKE '%8'; SELECT * FROM s WHERE score LIKE '8_'; SELECT * FROM s WHERE score REGEXP '^[789]8';

注：

'%...%' 会进行全表扫描,而不走索引 '%...' 无法直接使用索引，会进行全表扫描 '...%' 不会全表扫描

1.4.GROUP BY

对结果进行分组，条件过滤可以使用WHERE和HAVING，但HAVING是在分组后过滤，WHERE在分组前过滤，两者可以一起使用

例：查询s表中每个学生的记录数（通过student_id划分）

SELECT student_id id,count(*) count FROM s GROUP BY student_id;

1.5.ORDER BY

对查询结果进行排序，可以有多个排序条件，以‘,’分开，其中ASC表示正序(可省略)，DESC表示倒序

在集合运算中,只能在运算结果使用 ORDER BY ,中间表不能用 ORDER BY

例：查询s表中所有结果，按student_id正序，成绩倒序排列

SELECT * FROM s ORDER BY student_id ASC,score DESC;

1.6.INTO

将查询结果插入到另一个列变量，也可以导出文件

例：导出文件

SELECT * FROM s INTO OUTFILE '/tmp/s.txt';

1.7.FOR UPDATE | LOCK IN SHARE MODE

FOR UPDATE：在当前事务内，锁定查询的结果（行级锁），直到当前事务结束

LOCK IN SHARE MODE：对查询结果加共享锁，即其它事务只允许读而不允许写

2.子查询（嵌套查询）

作为子查询的语句只能查询一个列，需要注意的是，过多的子查询会导致性能下降，因为会重复执行子查询

例：查询s表中student_id存在于t表的记录（相关子查询）

SELECT * FROM s WHERE student_id in (SELECT student_id FROM t);

3.连接查询

连接查询使用 ‘ON’ 做各表连接前的条件判断，同时使用 ‘WHERE’ 做生成表的条件判断

3.1.内连接

[INNER | CROSS] JOIN

使用’,’或 INNER JOIN 或 CROSS JOIN 连接多个表，如果不加条件，则产生笛卡尔积

例：查询s表与t表student_id相同的记录

SELECT s.* FROM s INNER JOIN t ON s.student_id = t.student_id;

3.2.左外连接

LEFT [OUTER] JOIN

包含的左表的所有行，如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为NULL。

例：查询s表与t表student_id相同的记录，如t表无数据，则t所有数据列为空

SELECT * FROM s LEFT OUTER JOIN t ON s.student_id=t.student_id ORDER BY s.id;

3.3.右外连接

RIGHT [OUTER] JOIN

右向外连接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。

例：查询s表与t表student_id相同的记录，如t表无数据，则t所有数据列为空

SELECT * FROM s RIGHT OUTER JOIN t ON s.student_id=t.student_id ORDER BY s.id;

3.4.完整外连接

FULL JOIN 或 FULL OUTER JOIN

完整外连接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。

MySQL不支持FULL JOIN，可以使用UNION 拼接 LEFT [OUTER] JOIN 和 RIGHT [OUTER] JOIN

SELECT * FROM s LEFT OUTER JOIN t ON s.student_id=t.student_id UNION SELECT * FROM s RIGHT OUTER JOIN t ON s.student_id=t.student_id;

3.5.自然连接

NATURAL JOIN

对两张表中字段名和数据类型都相同的字段进行等值连接，并返回符合条件的结果 ，NATURAL JOIN 没有条件判断语句

SELECT * FROM s NATUAL JOIN t;

4.集合操作

4.1.并集 UNION [ALL]

UNION 合并多个查询(并集)，同时将重复的项去除(如果是ALL则保留重复)

UNION 要求列数必须相同，如果字段名不同，则显示为第一个查询的字段

4.2.交集 INTERSECT

MYSQL不支持,使用 WHERE 的 IN 或 EXIST 或 JOIN ON 代替

4.3.差集 EXCEPT

MYSQL不支持,使用 WHERE 的 NOT IN 或 NOT EXIST 代替

5.一些例子

5.1.查出s表中所有分数都大于80的学生id

两次查表，第一次分组查询某学生的课程数量，子查询查出该学生大于80分的课程数量，两者比较，如果相等，则该学生符合条件

SELECT a.student_id FROM s a GROUP BY student_id HAVING COUNT(*) = (SELECT COUNT(*) FROM s b WHERE b.score>80 AND b.student_id=a.student_id);

5.2.查询s表中student_id重复的数据（单条数据重复）

对student_id进行分组，再使用count()计算记录数

SELECT student_id FROM s GROUP BY student_id HAVING COUNT(student_id)>1;

5.3.查询s表中student_id和score重复的数据（多条数据重复）

两次查表，先查出重复的目标字段，in判断条件，然后再选出所需数据

SELECT student_id FROM s GROUP BY student_id,score HAVING COUNT(*) > 1;

5.4.删除重复数据，保留id最大那一项

该方法参考了stackoverflow一位大神的写法,链接忘记了，也是两次查表

delete dupes FROM s dupes,s full WHERE dupes.student_id=full.student_id AND dupes.score=full.score AND dupes.id < full.id;

5.5.查询A表中有而B表中没有的数据(如果是SQL Server可以使用 EXCEPT)

可以用NOT IN或NOT EXISTS或LEFT JOIN实现

例:查询student_id存在于s而不存在于t的数据

1.NOT IN

SELECT s.* FROM s WHERE student_id NOT IN(SELECT student_id FROM t); # NULL 不做为判断的条件

2.NOT EXISTS

SELECT s.* FROM s WHERE NOT EXISTS (SELECT * FROM t WHERE s.student_id=t.student_id);

3.LEFT JOIN

SELECT s.* FROM s LEFT JOIN t ON s.student_id=t.student_id WHERE t.id IS NULL AND t.student_id IS NULL AND t.score IS NULL;

参考

https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-data-manipulation.html