mysql 专业笔记 -- 第 3 章：SELECT

SELECT 用于从一个或多个表中检索行。

第 3.1 节：SELECT 与 DISTINCT

SELECT 之后的 DISTINCT 子句用于从结果集中去除重复行。

CREATE TABLE car (
    car_id INT UNSIGNED NOT NULL PRIMARY KEY,
    name VARCHAR(20),
    price DECIMAL(8,2)
);

INSERT INTO car (car_id, name, price) VALUES
(1, 'Audi A1', '20000'),
(2, 'Audi A1', '15000'),
(3, 'Audi A2', '40000'),
(4, 'Audi A2', '40000');

SELECT DISTINCT name, price FROM car;

结果：

+---------+----------+
| name    | price    |
+---------+----------+
| Audi A1 | 20000.00 |
| Audi A1 | 15000.00 |
| Audi A2 | 40000.00 |
+---------+----------+

DISTINCT 作用于所有列以产生结果，而不是单独作用于某一列。后者是新 SQL 开发人员常见的误解。简而言之，结果是行级别的去重，而不是列级别的去重。要理解这一点，请看上面结果中的 "Audi A1"。

在较新的 MySQL 版本中，DISTINCT 与 ORDER BY 一起使用时会有一些影响。ONLY_FULL_GROUP_BY 设置会起作用，详见 MySQL 手册《MySQL Handling of GROUP BY》。

第 3.2 节：SELECT 所有列（*）

SELECT * FROM stack;

结果：

+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | admin    | admin    |
|  2 | stack    | stack    |
+----+----------+----------+
2 rows in set (0.00 sec)

在连接查询中，你可以通过以下方式从一个表中选择所有列：

SELECT stack.* FROM stack JOIN ...;

优点：

当添加/删除列时，使用 SELECT * 的地方不需要修改。
写起来更短。
可以立即看到所有列。

缺点：

返回了不需要的数据。例如，添加一个每行 200KB 的 VARBINARY 列，而你只在某个地方需要这一条记录的数据，使用 SELECT * 可能会为每 10 行返回 2MB 的无用数据。
明确指定使用的数据更清晰。
指定列名后，如果某列被删除，会收到错误。
查询处理器需要做更多工作——确定表中存在哪些列。
更容易找到某个列被使用的位置。
在连接查询中使用 SELECT * 会返回所有连接表的所有列。
不能安全地使用列序号引用（尽管在 SQL 中使用列序号引用本身就是坏习惯）。
在复杂的查询中，如果包含 TEXT 字段，可能会因为临时表处理不够优化而降低查询速度。

第 3.3 节：按列名 SELECT

CREATE TABLE stack (
    id INT,
    username VARCHAR(30) NOT NULL,
    password VARCHAR(30) NOT NULL
);

INSERT INTO stack (id, username, password) VALUES
(1, 'Foo', 'hiddenGem'),
(2, 'Baa', 'verySecret');

SELECT id FROM stack;

结果：

+------+
| id   |
+------+
|    1 |
|    2 |
+------+

[图片占位符]

第 3.4 节：SELECT 与 LIKE（%）

CREATE TABLE stack (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL
);

INSERT stack(username) VALUES
('admin'), ('k admin'), ('adm'), ('a adm b'), ('b XadmY c'), ('adm now'), ('not here');

-- 任意位置包含 "adm"：
SELECT * FROM stack WHERE username LIKE "%adm%";

结果：

+----+-----------+
| id | username  |
+----+-----------+
|  1 | admin     |
|  2 | k admin   |
|  3 | adm       |
|  4 | a adm b   |
|  5 | b XadmY c |
|  6 | adm now   |
+----+-----------+

以 "adm" 开头：

SELECT * FROM stack WHERE username LIKE "adm%";

结果：

+----+----------+
| id | username |
+----+----------+
|  1 | admin    |
|  3 | adm      |
|  6 | adm now  |
+----+----------+

以 "adm" 结尾：

SELECT * FROM stack WHERE username LIKE "%adm";

结果：

+----+----------+
| id | username |
+----+----------+
|  3 | adm      |
+----+----------+

与 % 在 LIKE 子句中匹配任意数量字符类似，_ 字符只匹配一个字符。例如：

SELECT * FROM stack WHERE username LIKE "adm_n";

结果：

+----+----------+
| id | username |
+----+----------+
|  1 | admin    |
+----+----------+

注意事项：

没有通配符的 LIKE（如 LIKE 'adm'）等同于 =，但 LIKE 'adm' 是一个“范围”，类似于 BETWEEN...AND...，它可以很好地利用列上的索引。
LIKE '%adm'（或任何带有前导通配符的变体）不能使用任何索引，因此会很慢。在包含大量行的表上，它几乎毫无用处。
RLIKE（REGEXP）通常比 LIKE 慢，但功能更强。
尽管 MySQL 在许多类型的表和列上提供了 FULLTEXT 索引，但这些 FULLTEXT 索引不用于执行使用 LIKE 的查询。

第 3.5 节：SELECT 与 CASE 或 IF

SELECT
    st.name,
    st.percentage,
    CASE
        WHEN st.percentage >= 35 THEN 'Pass'
        ELSE 'Fail'
    END AS 'Remark'
FROM student AS st;

结果：

+------+------------+--------+
| name | percentage | Remark |
+------+------------+--------+
| Isha | 67         | Pass   |
| Rucha| 28         | Fail   |
| Het  | 35         | Pass   |
| Ansh | 92         | Pass   |
+------+------------+--------+

或者使用 IF：

SELECT
    st.name,
    st.percentage,
    IF(st.percentage >= 35, 'Pass', 'Fail') AS 'Remark'
FROM student AS st;

注意： IF(st.percentage >= 35, 'Pass', 'Fail') 的意思是：如果 st.percentage >= 35 为真，则返回 'Pass'，否则返回 'Fail'。

第 3.6 节：SELECT 与别名（AS）

SQL 别名用于临时重命名表或列。它们通常用于提高可读性。

SELECT username AS val FROM stack;
-- 或者
SELECT username val FROM stack;   -- AS 在语法上是可选的

结果：

+-------+
| val   |
+-------+
| admin |
| stack |
+-------+
2 rows in set (0.00 sec)

第 3.7 节：SELECT 与 LIMIT 子句

SELECT * FROM Customers ORDER BY CustomerID LIMIT 3;

结果：

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico

最佳实践： 使用 LIMIT 时始终加上 ORDER BY，否则你得到的行将是不可预测的。

SELECT * FROM Customers ORDER BY CustomerID LIMIT 2,1;

解释： 当 LIMIT 子句包含两个数字时，它被解释为 LIMIT offset, count。因此，在此示例中，查询跳过两条记录并返回一条。

结果：

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico

注意： LIMIT 子句中的值必须是常量，不能是列值。

第 3.8 节：SELECT 与 BETWEEN

你可以使用 BETWEEN 子句来替代“大于等于且小于等于”条件的组合。

数据：

+----+----------+
| id | username |
+----+----------+
| 1  | admin    |
| 2  | root     |
| 3  | toor     |
| 4  | mysql    |
| 5  | thanks   |
| 6  | java     |
+----+----------+

使用运算符的查询：

SELECT * FROM stack WHERE id >= 2 AND id <= 5;

使用 BETWEEN 的类似查询：

SELECT * FROM stack WHERE id BETWEEN 2 AND 5;

结果：

+----+----------+
| id | username |
+----+----------+
| 2  | root     |
| 3  | toor     |
| 4  | mysql    |
| 5  | thanks   |
+----+----------+
4 rows in set (0.00 sec)

注意： BETWEEN 使用 >= 和 <=，而不是 > 和 <。

使用 NOT BETWEEN：

SELECT * FROM stack WHERE id NOT BETWEEN 2 AND 5;

结果：

+----+----------+
| id | username |
+----+----------+
| 1  | admin    |
| 6  | java     |
+----+----------+

注意： NOT BETWEEN 使用 < 和 >。即 WHERE id NOT BETWEEN 2 AND 5 等同于 WHERE (id < 2 OR id > 5)。

如果在用于 BETWEEN 搜索的列上有索引，MySQL 可以使用该索引进行范围扫描。

第 3.9 节：SELECT 与 WHERE

SELECT * FROM stack WHERE username = "admin" AND password = "admin";

结果：

+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | admin    | admin    |
+----+----------+----------+
0 row in set (0.00 sec)  -- 原文示例可能没有返回数据，这里保持原样但修正了行数显示

WHERE 子句中使用嵌套 SELECT

WHERE 子句可以包含任何有效的 SELECT 语句来编写更复杂的查询。这就是“嵌套”查询。

嵌套查询通常用于返回单个原子值进行比较。

SELECT title FROM books
WHERE author_id = (SELECT id FROM authors WHERE last_name = 'Bar' AND first_name = 'Foo');

选择所有没有电子邮件地址的用户名：

SELECT * FROM stack
WHERE username IN (SELECT username FROM signups WHERE email IS NULL);

免责声明： 在比较整个结果集时，考虑使用连接（JOIN）来提高性能。

第 3.10 节：SELECT 与 LIKE（_）

LIKE 子句模式中的 _ 字符匹配单个字符。

SELECT username FROM users WHERE username LIKE 'admin_';

结果：

+----------+
| username |
+----------+
| admin1   |
| admin2   |
| admin-   |
| adminA   |
+----------+

第 3.11 节：SELECT 与日期范围

SELECT ... WHERE dt >= '2017-02-01' AND dt < '2017-02-01' + INTERVAL 1 MONTH

当然，这可以通过 BETWEEN 并包含 23:59:59 来实现。但此模式具有以下优点：

不需要预先计算结束日期（结束日期通常是从开始日期开始的固定长度）。
不会像 BETWEEN 那样包含两个端点，也不需要输入 '23:59:59' 来避免包含端点。
适用于 DATE、TIMESTAMP、DATETIME，甚至包含微秒的 DATETIME(6)。
处理闰日、年末等情况。
对索引友好（BETWEEN 也对索引友好）。