【MySQL/Mariadb】SELECT备忘录，mysqlmariadb
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