Skip to content

4. 查询数据

向上取整函数：CEILING

向下取整函数：FLOOR

1. 基本查询⚓

SELECT * FROM <table>

使用DISTINCT去除重复的数据，它是修饰字段的：

# 查询每种日期
SELECT DISTINCT createDate FROM user;
# 查询有多少种日期
SELECT COUNT(DISTINCT createDate) FROM user;

SELECT语句还拥有计算能力：

SELECT 101+59;

不带FROM子句的SELECT语句用来判断当前数据库的连接是否有效。许多检测工具会执行一条SELECT 1;来测试数据库连接。

2. 条件查询⚓

SELECT * FROM <table> WHERE <condition-expression>

WHERE后跟条件表达式。

2.1 条件表达式⚓

2.1.1 与、或、非条件⚓

用于条件的拼接。

  • AND两边都为真，则结果为真
  • OR两边只要一个为真，则结果为真；
  • NOT即为非运算，它使条件取反。

NOT优先级最高，其次是AND，最后是OR。加上括号可以改变优先级。

2.1.2 相等、不等条件⚓

相等：=

不等：<>或!=

2.1.3 范围条件⚓

<, >, <=, >=, BETWEEN...AND...。

BETWEEN...AND...在数据库执行时被转化为<=和>=两个操作条件。

2.1.4 成员条件⚓

IN

SELECT * FROM user WHERE name IN ('shihr', 'aaaa');

2.1.5 匹配条件（模糊查询）⚓

LIKE

NOT LIKE

  • _：匹配一个字符
  • %：匹配任意个字符（包括0个字符）
SELECT * FROM blog WHERE title LIKE '%super%';

默认的转义字符是\，可以通过ESCAPE 改变：

mysql> SELECT 'David_' LIKE 'David|_' ESCAPE '|';
        -> 1

使用\\\\表示一个普通的\。

注意：比较字符也是不区分大小写的，可以通过比较二进制的方式来区分大小写：

mysql> SELECT 'abc' LIKE 'ABC';
        -> 1
mysql> SELECT 'abc' LIKE BINARY 'ABC';
        -> 0

2.1.6 正则表达式匹配⚓

REGEXP、RELIKE

NOT REGEXP

mysql> SELECT 'a' REGEXP '^[a-d]';
        -> 1

3. 投影查询⚓

SELECT <column1, column2 column-alias> FROM <table> <table-alias>
  • column-alias：列的别名
  • table-alias：表的别名

4. 排序⚓

查询结果一般根据主键排序。使用ORDER BY可以改变这一行为根据指定的字段按照升序排序；加上DESC表示倒序。可添加多个字段排序。

SELECT * FROM user ORDER BY name;
SELECT * FROM user ORDER BY name DESC, createDate;

如果有WHERE要放在WHERE后面。

5. 分页查询⚓

LIMIT是MySQL的扩展语法。

LIMIT <page-size> OFFSET <result-index>

  • page-size：每页的结果集条数
  • result-index：从结果集的第几条开始（第一条是0）

result-index = page-size * （page-index - 1）

SELECT * FROM user LIMIT 5 OFFSET 0;

result-index超过了查询的最大数量并不会报错，而是得到一个空的结果集。

使用LIMIT <M> OFFSET <N>分页时，随着偏移量N越来越大，查询效率也会越来越低。

6. 聚合查询⚓

通常，使用聚合查询时，我们应该给列名设置一个别名，便于处理结果。

聚合函数 说明
COUNT 计算结果集的条目数量
SUM 计算某一列的合计值，必须是数值/时间类型
AVG 计算某一列的平均值，必须是数值/时间类型
MAX 计算某一列的最大值
MIN 计算某一列的最小值
BIT_AND 按位与，计算以64位（BIGINT）精度执行。
BIT_OR 按位或
BIT_XOR 按位异或

注意：如果聚合查询的WHERE条件没有匹配到任何行，COUNT()会返回0，而SUM()、AVG()、MAX()和MIN()会返回NULL。

SELECT column1, func_name, ... FROM table_name [GROUP BY column1, ... [WITH ROLLUP]] [HAVING contition_expression];
  • GROUP BY：分组查询
  • WITH ROLLUP：对结果进行汇总
  • HAVING：对聚合后的结果进行条件过滤；WHERE是在聚合前进行条件过滤。尽可能地使用WHERE先过滤。
SELECT createDate, COUNT(createDate) nums FROM user GROUP BY createDate WITH ROLLUP;
+------------+------+
| createDate | nums |
+------------+------+
| 2019-04-01 |    1 |
| 2019-04-02 |    2 |
| 2019-04-08 |   11 |
| 2019-04-09 |    1 |
| 2019-04-11 |    1 |
| NULL       |   16 |
+------------+------+

SELECT createDate, COUNT(createDate) nums FROM user GROUP BY createDate WITH ROLLUP HAVING nums > 5;
+------------+------+
| createDate | nums |
+------------+------+
| 2019-04-08 |   11 |
| NULL       |   16 |
+------------+------+

注意：

  1. 当使用 ROLLUP 时, 不能同时使用 ORDER BY 子句进行结果排序。 换言之， ROLLUP 和 ORDER BY 是互相排斥的
  2. LIMIT 用在 ROLLUP 后面。

6.1 分组查询⚓

对于聚合查询，SQL还提供了分组聚合的功能，对结果集进行分组。

SELECT userID, COUNT(*) num FROM blog GROUP BY userId;
+--------+-----+
| userID | num |
+--------+-----+
|     46 |   3 |
|     47 |   1 |
+--------+-----+
2 rows in set (0.01 sec)

聚合查询的字段中，只能放入分组的字段。后可跟多个字段。

SELECT userID, title, COUNT(*) num FROM blog GROUP BY userId, title;

7. 连接查询⚓

7.1 内连接（INNER JOIN）⚓

只返回同时存在于两张表的行数据。

SELECT u.id, u.name, b.title blog_title FROM user u INNER JOIN blog b ON b.userId = u.id;

写法：

  • 先确定主表，FROM <table>
  • 再确定连接的表，INNER JOIN <other-table>
  • 然后是连接条件，ON ...
  • 可选子句，WHERE等

7.2 左外连接（LEFT OUTER JOIN）⚓

返回左表都存在的行。如果某一行仅在左表存在，那么结果集就会以NULL填充剩下的字段。

7.3 右外连接（RIGHT OUTER JOIN）⚓

返回右表都存在的行。如果某一行仅在右表存在，那么结果集就会以NULL填充剩下的字段。

7.4 全外连接（FULL OUTER JOIN）⚓

会把两张表的所有记录全部选择出来，并且，自动把对方不存在的列填充为NULL。

MySQL不支持，可使用UNION联合左外、右外连接代替：

SELECT u.id, u.name, b.title blog_title FROM user u LEFT OUTER JOIN blog b ON b.userId = u.id 
UNION 
SELECT u.id, u.name, b.title blog_title FROM user u RIGHT OUTER JOIN blog b ON b.userId = u.id;

8. 子查询⚓

关键字主要包括IN, NOT IN, =, !=, EXISTS, NOT EXISTS。

SELECT * FROM user WHERE id IN (2, 3);

子查询在某些情况下可以转化为表连接，表连接在很多情况下用于优化子查询。

9. 联合记录⚓

使用UNION或UNION ALL将查询结果合并起来。

SELECT ...
UNION [ALL | DISTINCT] SELECT ...
[UNION [ALL | DISTINCT] SELECT ...]

区别：UNION会对结果去重，即DISTINCT；UNION ALL则不会。