MySQL - 数据检索

MySQL 数据检索

检索数据（SELECT）
排序数据（ORDER BY）
过滤数据（WHERE）
组合 WHERE 子句（AND/OR/IN/NOT）
通配符过滤（LIKE）
正则表达式搜索

检索数据

使用 SELECT 语句从表中检索数据。

检索单个列：

SELECT prod_name FROM products;

检索多个列：列名之间用逗号分隔，最后一个列名后不加逗号。

SELECT prod_id, prod_name, prod_price FROM products;

检索所有列：使用 * 通配符。除非确实需要每个列，否则最好别使用 *，检索不需要的列会降低性能。

SELECT * FROM products;

检索不同的行：使用 DISTINCT 关键字，只返回不同的值。DISTINCT 应用于所有列，不仅是前置它的列。

SELECT DISTINCT vend_id FROM products;

限制结果：使用 LIMIT 子句返回前几行。

-- 返回前 5 行
SELECT prod_name FROM products LIMIT 5;

-- 从行 5 开始返回 5 行（第一个数是开始位置，第二个数是要检索的行数）
SELECT prod_name FROM products LIMIT 5, 5;

-- MySQL 5 替代语法，更易读：从行 3 开始取 4 行
SELECT prod_name FROM products LIMIT 4 OFFSET 3;

检索出来的第一行为行 0 而不是行 1，因此 LIMIT 1, 1 将检索出第二行。

完全限定的表名：

SELECT products.prod_name FROM crashcourse.products;

排序数据

如果不明确规定排序顺序，不应该假定检索出的数据的顺序有意义。使用 ORDER BY 子句排序。

按单个列排序：

SELECT prod_name FROM products ORDER BY prod_name;

按多个列排序：仅在多个行具有相同的第一列值时才按第二列排序。

SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name;

指定排序方向：默认升序（A 到 Z），使用 DESC 关键字降序排序。DESC 只应用到直接位于其前面的列名，想在多个列上降序必须对每个列都指定。

-- 按价格降序
SELECT prod_price FROM products ORDER BY prod_price DESC;

-- 按价格降序，再按名称升序
SELECT prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name;

-- 找出最贵物品：ORDER BY 和 LIMIT 组合
SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 1;

ASC（升序）关键字可以指定，但升序是默认的，通常不需要写。ORDER BY 子句必须是 SELECT 语句中的最后一条子句，位于 FROM 子句之后，LIMIT 必须位于 ORDER BY 之后。

过滤数据

使用 WHERE 子句指定搜索条件，只检索所需数据。WHERE 子句在表名（FROM 子句）之后给出。

SELECT prod_name, prod_price FROM products WHERE prod_price = 2.50;

WHERE 子句操作符：

操作符	说明
`=`	等于
`<>` / `!=`	不等于
`<`	小于
`<=`	小于等于
`>`	大于
`>=`	大于等于
`BETWEEN`	在指定的两个值之间

检查单个值：

SELECT prod_name, prod_price FROM products WHERE prod_name = 'fuses';
SELECT prod_name, prod_price FROM products WHERE prod_price < 10;
SELECT prod_name, prod_price FROM products WHERE prod_price <= 10;

MySQL 在执行匹配时默认不区分大小写，所以 fuses 与 Fuses 匹配。

不匹配检查：

SELECT vend_id, prod_name FROM products WHERE vend_id <> 1003;

何时使用引号：单引号用来限定字符串，与串类型的列比较需要引号，与数值列比较不用引号。

范围值检查：BETWEEN 需要两个值（开始和结束），用 AND 分隔，匹配范围中所有值包括开始值和结束值。

SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;

空值检查：NULL 是无值，与字段包含 0、空字符串或空格不同。使用 IS NULL 检查。

SELECT prod_name FROM products WHERE prod_price IS NULL;
SELECT cust_name FROM customers WHERE cust_email IS NULL;

NULL 与不匹配：在过滤选择出不具有特定值的行时，数据库不返回具有 NULL 值的行，因为未知具有特殊的含义。

组合 WHERE 子句

使用 AND 和 OR 操作符组合多个 WHERE 条件。

AND 操作符：检索满足所有给定条件的行。

SELECT prod_id, prod_price, prod_name FROM products
WHERE vend_id = 1003 AND prod_price <= 10;

OR 操作符：检索匹配任一条件的行。

SELECT prod_name, prod_price FROM products
WHERE vend_id = 1002 OR vend_id = 1003;

计算次序：AND 的优先级高于 OR。使用括号明确分组以避免歧义。

-- 有括号：vend_id=1003 且 price>=10，或者 vend_id=1002
SELECT prod_name, prod_price FROM products
WHERE (vend_id = 1003 OR vend_id = 1002) AND prod_price >= 10;

IN 操作符：用来指定条件范围，功能与 OR 相当，但更清晰直观，且执行速度更快，可以包含其他 SELECT 语句。

SELECT prod_name, prod_price FROM products
WHERE vend_id IN (1002, 1003) ORDER BY prod_name;

NOT 操作符：否定它之后所跟的任何条件。

SELECT prod_name, prod_price FROM products
WHERE vend_id NOT IN (1002, 1003) ORDER BY prod_name;

通配符过滤

使用 LIKE 操作符和通配符进行搜索。通配符是用于匹配值的一部分的特殊字符。

百分号（%）通配符：匹配任意字符出现任意次数（包括 0 次）。

-- 以 jet 开头
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'jet%';

-- 包含 anvil
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '%anvil%';

-- 以 s 开头 e 结尾
SELECT prod_name FROM products WHERE prod_name LIKE 's%e';

下划线（_）通配符：只匹配单个字符，不多也不少。

SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ ton anvil';

通配符使用技巧：

不要过度使用通配符，如果其他操作符能达到相同目的，应该使用其他操作符
不要把通配符用在搜索模式的开始处，这样搜索是最慢的
仔细注意通配符的位置

正则表达式

MySQL 支持正则表达式进行更复杂的搜索，使用 REGEXP 操作符。

基本字符匹配：

SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;

LIKE 和 REGEXP 的区别：LIKE 匹配整个列，而 REGEXP 在列值内匹配。LIKE '1000' 只匹配列值恰好为 1000 的行，而 REGEXP '1000' 匹配列值中包含 1000 的行。

OR 匹配：使用 | 字符。

SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000' ORDER BY prod_name;

匹配几个字符之一：使用 [ ] 方括号。

SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name;
-- 等价于 '1|2|3 Ton'，但更精确

匹配范围：[1-5] 等价于 [12345]，[a-z] 匹配任意字母。

SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5] Ton' ORDER BY prod_name;

匹配特殊字符：使用 \\ 转义，如 \\. 匹配实际的点。

SELECT prod_name FROM products WHERE prod_name REGEXP '\\.' ORDER BY prod_name;

字符类：预定义的字符集，如 [:digit:] 匹配任意数字，[:alpha:] 匹配任意字母。

匹配多个实例：使用重复元字符。

元字符	说明
`*`	0 个或多个匹配
`+`	1 个或多个匹配（等于 `{1,}`）
`?`	0 个或 1 个匹配（等于 `{0,1}`）
`{n}`	指定数目的匹配
`{n,}`	不少于指定数目的匹配
`{n,m}`	匹配数目的范围（m 不超过 255）

-- 匹配连在一起的 4 位数字
SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9]{4}\\)' ORDER BY prod_name;

定位符：

元字符	说明
`^`	文本的开始
`$`	文本的结束

-- 以数字（包括小数点）开头的所有产品
SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]' ORDER BY prod_name;

^ 有双重用途：在集合中（[^...]）表示否定，在集合外表示字符串的开始。

MySQL - 数据检索