MYSQL DQL 语句

单表操作

语法：

SELECT
[ALL | DISTINCT | DISTINCTROW ]
[SQL_CACHE | SQL_NO_CACHE]
select_expr [, select_expr ...]
[FROM table_references
[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}]
[FOR UPDATE | LOCK IN SHARE MODE]

字段显示可以使用别名：

col1 AS alias1, col2 AS alias2, …

SELECT name 姓名,age 年龄 FROM students;

WHERE子句，指明过滤条件以实现”选择”的功能：

布尔型表达式

算术操作符：+, -, *, /, %

比较操作符：=, <=>（相等或都为空）, <>, !=(非标准SQL), >, >=, <, <=

范例查询：BETWEEN min_num AND max_num

不连续的查询：IN (element1, element2, …)

空查询：IS NULL, IS NOT NULL

DISTINCT 去除重复行

SELECT DISTINCT gender FROM students;

模糊查询: LIKE

RLIKE：正则表达式，索引失效，不建议使用

REGEXP：匹配字符串可用正则表达式书写模式，同上

逻辑操作符：NOT，AND，OR，XOR

GROUP BY，根据指定的条件把查询结果进行”分组”以用于做”聚合”运算：

常见聚合函数：count()，sum()，max()，min()，avg()，不对null统计

HAVING: 对分组聚合运算后的结果指定过滤条件

一旦分组 group by ，select语句后只跟分组的字段，聚合函数

ORDER BY: 根据指定的字段对查询结果进行排序

升序：ASC
降序：DESC

LIMIT [[offset,]row_count]：

对查询的结果进行输出行数数量限制,跳过offset,显示row_count行,offset默为值为0。

对查询结果中的数据请求施加”锁”：

FOR UPDATE: 写锁，独占或排它锁，只有一个读和写操作
LOCK IN SHARE MODE: 读锁，共享锁，同时多个读操作

子查询

子查询 subquery 即SQL语句调用另一个SELECT子句，可以是对同一张表，也可以是对不同表，主要有以下四种常见的用法。

1.用于比较表达式中的子查询：

SELECT name,age FROM students WHERE age>(SELECT avg(age) FROM teachers);

2.用于IN中的子查询：

SELECT name,age FROM students WHERE age IN (SELECT age FROM teachers);

3.用于EXISTS 和 Not EXISTS

EXISTS子句根据其内查询语句的结果集空或者非空，返回一个布尔值。

select * from students s where EXISTS (select * from teachers t where s.teacherid=t.tid);

4.用于FROM子句中的子查询

select stuid,name,age from students where age > (select avg(age) from students);

联合查询

联合查询 Union 实现的条件，多个表的字段数量相同，字段名和数据类型可以不同，但一般数据类型是相同的。

SELECT name,age FROM students UNION SELECT name,age FROM teachers;

交叉连接

cross join 即多表的记录之间做笛卡尔乘积组合，并且多个表的列横向合并相加。

比如: 第一个表3行4列,第二个表5行6列,cross join后的结果为3*5=15行,4+6=10列。

SELECT * from students CROSS JOIN teachers;

内连接

inner join 内连接取多个表的交集。

SELECT * from students AS s inner join teachers AS t ON s.teacherid=t.tid;

左和右外连接

左连接: 以左表为主根据条件查询右表数据﹐如果根据条件查询右表数据不存在使用null值填充。
右连接: 以右表为主根据条件查询左表数据﹐如果根据条件查询左表数据不存在使用null值填充。

左外连接

SELECT s.id,s.name,s.age,s.teacherid,t.tid,t.name,t.age FROM students AS s LEFT OUTER JOIN teachers AS t ON s.teacherid=t.tid;

右外连接

SELECT * from students s RIGHT OUTER JOIN teachers t ON s.teacherid=t.tid;

自连接

表自身连接自身。

#假设创建一张表格，里面有id，姓名，和leader的id
CREATE TABLE emp(id smallint,name varchar(20),leaderid smallint);
#随便插入4条记录
INSERT emp values(1,'frog',null),(2,'cat',1),(3,'mice',2),(4,'worm',1);
#用自连接查询每个人leader的名字
SELECT e.name,IFNULL(l.name,'无') FROM emp AS e LEFT JOIN emp AS l ON e.leaderid=l.id;

SELECT 语句处理的顺序

FROM Clause --> WHERE Clause --> GROUP BY --> HAVING Clause -->SELECT --> ORDER BY --> LIMIT