MySQL必知必会—检索数据篇

下面用到的数据库文件可在 mysql_scripts 找到。

检索数据

# 检索单个列，例如从 products 表中检索一个名为 prod_name 的列。
SELECT prod_name FROM products;

# 检索多个列。注意，列名之间要用逗号分隔，最后一个列名后不要加逗号，会报错。
SELECT prod_id, prod_name, prod_price FROM products;

# 检索所有列。
SELECT * FROM products;

# 只检索出不同的行， DESTINCT 关键字可以让指令只返回不同的值。如果指令，products 表中可能一共有14行，现在只返回不同（唯一）的 vend_id 行，可能就只返回4行了。
SELECT DISTINCT vend_id FROM products;

# 限制结果， LIMIT 5 表示只返回不多于5行。
SELECT prod_name FROM products LIMIT 5;

# LIMIT 5, 5 表示返回从行5开始的5行。
SELECT prod_name FROM products LIMIT 5, 5;
# 或者使用 LIMIT 5 OFFSET 5， 跟上面结果相同。
SELECT prod_name FROM products LIMIT 5 OFFSET 5;

# 注意，返回行数是从 0 开始的。所以，LIMIT 1, 1 将检索出第二行，而不是第一行。
SELECT prod_name FROM products LIMIT 1,1;

排序检索数据 ( ORDER BY )

不使用排序时，其实检索出的数据并不是以纯粹的随机顺序显示的，数据一般将以它在底层表中出现的顺序显示。这可以是数据最初添加到表中的顺序，但是，如果数据后来进行过更新或者删除，则此顺序将会受到 MySQL 重用回收存储空间的影响。因此，如果不明确控制的话，不能（也不应该）依赖该排序顺序。

关系数据库设计理论认为：如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有意义。

ORDER BY 子句，可以给 SELECT 语句检索出来的数据进行排序。 ORDER BY 子句取一个或多个列的名字。据此对输出进行排序。

# 没有排序
SELECT prod_name FROM products;

# 对 prod_name 列以字母顺序排序数据
SELECT prod_name FROM products ORDER BY prod_name;

# 按多个列排序：如下会先按照 prod_price 排序，
# 只有出现相同的 prod_price 时，才会再按照 prod_name 排序。
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name;

# 指定排序方向，默认是升序，例如按照 prod_price 降序排序（最贵的排在最前面）
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC;
# 多个列排序，例如按照 prod_price 降序，最贵的在最前面，然后在对产品名排序
SELECT prod_id, 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;

注意：

ORDER BY 子句中使用的列不一定非得是检索的列，用非检索的列排序也是完全合法的。
如果想在多个列上进行降序排序，必须对每个列指定 DESC 关键字。
ASC 是升序排序，升序是默认的，不指定 DESC ，那就是按照 ASC 升序排序。
ORDER BY 子句必须位于 FROM 子句之后，如果使用 LIMIT ，它必须位于 ORDER BY 之后。

过滤数据（ WHERE ）

数据库包含大量的数据，但是我们很少需要检索表中所有的行。只检索所需数据需要指定过滤条件，在 SELECT 语句中，数据根据 WHERE 子句中指定的搜索条件进行过滤。

# 检索 pro_price 为 2.50 的行
SELECT prod_name FROM products WHERE prod_price = 2.50;

# 执行筛选匹配时默认不区分大小写，所以 fuses 可以检索出 Fuses
SELECT prod_name, prod_price FROM products WHERE prod_name = 'fuses';
# 输出
+-----------+------------+
| prod_name | prod_price |
+-----------+------------+
| Fuses     |       3.42 |
+-----------+------------+

# 检索出 vend_id 不等于 1003 的行
SELECT vend_id, prod_name FROM products WHERE vend_id <> 1003;

# 检索 prod_price 在 5 到 10 之间的所有行
SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;

# 检查具有 NULL 值的列，用 IS NULL 子句
SELECT cust_id FROM customers WHERE cust_email IS NULL;

WHERE 子句操作符

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

注意：

WHERE 语句的位置：在同时使用 ORDER BY 和 WHERE 子句时，应该让 ORDER BY 位于 WHERE 之后，否则将会产生错误。
WHERE 子句中使用的条件，如果将值与串类型（例如字符串）比较，需要加引号，用来与数值列进行比较的值不用引号。
NULL 无值(no value)，它与字段 0 、空字符串或仅仅包含空格不同。

数据过滤（ AND、 OR、 IN ）

MySQL 允许组合多个 WHERE 子句。这些子句分为两种方式使用：以 AND 子句的方式或 OR 子句的方式使用。

### AND 操作符
# 检索出 vend_id 等于 1003 并且 prod_price 小于等于 10 的行
SELECT prod_price, prod_name FROM products WHERE vend_id = 1003 AND prod_price <= 10;


#### OR 操作符
# 检索出 vend_id 等于 1002 或 vend_id 等于 1003 的所有行
SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003;


# AND 和 OR 合用，AND 优先级高。
# 下面检索出的结果是 vend_id 是 1003 并且 prod_price 大于等于 10 的和所有 vend_id 是 1002 的行。
SELECT vend_id, prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10;
# 输出结果
+---------+----------------+------------+
| vend_id | prod_name      | prod_price |
+---------+----------------+------------+
|    1002 | Fuses          |       3.42 |
|    1002 | Oil can        |       8.99 |
|    1003 | Detonator      |      13.00 |
|    1003 | Bird seed      |      10.00 |
|    1003 | Safe           |      50.00 |
|    1003 | TNT (5 sticks) |      10.00 |
+---------+----------------+------------+

# 如果想检索出 vend_id 是 1003 并且 prod_price 大于等于 10 的和 vend_id 是 1002  并且 prod_price 大于等于 10 的行，需要加括号。
SELECT vend_id, prod_name, prod_price FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;


### IN 操作符，指定条件范围，范围中的每个条件都可以进行匹配。IN 取值是全部括在圆括号中的由逗号分隔的列表。
SELECT vend_id, prod_name, prod_price FROM products WHERE vend_id IN (1002, 1003);


### NOT 操作符，否定它之后的任何条件
SELECT vend_id, prod_name, prod_price FROM products WHERE vend_id NOT IN (1002, 1003);

注意

WHERE 可包含任意数目的 AND 和 OR 操作符，并且允许两者结合以进行复杂和高效的过滤。不过 SQL 语言在处理 OR 操作符前，会优先处理 AND 操作符。
任何时候使用具有 AND 和 OR 操作符的 WHERE 子句，都推荐使用圆括号明确地分组，不要过分依赖默认计算次序。
IN 和 OR 具有相同的功能，但是 IN 操作符有以下优点
- 过滤的字段太多的时候，IN 操作符的语法更清楚且更直观
- IN 操作符一般比 OR 操作符执行的更快
- IN 最大的优点是可以包含其他 SELECT 语句，能更动态地建立 WHERE 子句。
MySQL 支持使用 NOT 对 IN、BETWEEN 和 EXISTS 子句取反。

用通配符过滤

百分号（%）通配符

% 表示任何字符出现任意次数，可以使0次，1次，n次

### 找出所有以 jet 开头的产品
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'jet%';
+---------+--------------+
| prod_id | prod_name    |
+---------+--------------+
| JP1000  | JetPack 1000 |
| JP2000  | JetPack 2000 |
+---------+--------------+

### 通配符可在搜索模式中任意位置使用，并且可以使用多个通配符。
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '%anvil%';
+---------+--------------+
| prod_id | prod_name    |
+---------+--------------+
| ANV01   | .5 ton anvil |
| ANV02   | 1 ton anvil  |
| ANV03   | 2 ton anvil  |
+---------+--------------+

下划线通配符

下划线 _ 只能匹配单个字符，只能匹配一个，不能多也不能少。

### 对比一下下面两个通配符结果
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ ton anvil';
+---------+-------------+
| prod_id | prod_name   |
+---------+-------------+
| ANV02   | 1 ton anvil |
| ANV03   | 2 ton anvil |
+---------+-------------+


SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '% ton anvil';
+---------+--------------+
| prod_id | prod_name    |
+---------+--------------+
| ANV01   | .5 ton anvil |
| ANV02   | 1 ton anvil  |
| ANV03   | 2 ton anvil  |
+---------+--------------+

### 下划线通配符比百分号通配符少了一个 .5 的数据

注意

注意尾部空格，例如'%anvil' 是匹配不到 'anvil ',因为后面有个空格不容易发现，解决方法就是后面再附加一个 % ，或者用函数去掉首尾空格。
% 是不能匹配出 NULL的。
通配符搜索的处理一般要比其他搜索花时间更长，所以不要过度使用通配符，如果其他操作符能达到同样的目的，优先使用其他操作符。在确实需要使用通配符时，除非绝对有必要，否则不要把他们用在搜索模式的开始处。

用正则表达式进行搜索

### 基本字符匹配，下面的语句检索列 prod_name 包含文本 1000 的所有行。
SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;

### 区分大小写需要用到 BINARY 关键字
SELECT prod_name FROM products WHERE prod_name REGEXP BINARY 'S';

### 使用 | 进行 OR 匹配，可以有两个以上的 OR 条件，例如： '1000|2000|3000'
SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000';
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+

### 匹配几个字符之一
SELECT prod_name FROM products WHERE prod_name REGEXP '[1,2,3] Ton' ORDER BY prod_name;
+-------------+
| prod_name   |
+-------------+
| 1 ton anvil |
| 2 ton anvil |
+-------------+
### 注意区别 1|2|3 Ton，这表示匹配出 1，2和3 Ton，其实[123]是[1|2|3]的缩写
SELECT prod_name FROM products WHERE prod_name REGEXP '1|2|3 Ton' ORDER BY prod_name;
+---------------+
| prod_name     |
+---------------+
| 1 ton anvil   |
| 2 ton anvil   |
| JetPack 1000  |
| JetPack 2000  |
| TNT (1 stick) |
+---------------+

### 匹配特殊字符， \\ 来转义特殊字符
SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.' ORDER BY vend_name;
+--------------+
| vend_name    |
+--------------+
| Furball Inc. |
+--------------+


### 匹配出连在一起的4个数字
SELECT prod_name FROM products WHERE prod_name REGEXP '[:digit:]{4}' ORDER BY prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+

列举元字符转义和定位元字符

元字符	说明
\f	换页
\n	换行
\r	回车
\t	制表
\v	纵向制表
\\	反斜杠
^	文本的开始
$	文本的结束
[[:<:]](8版本之后改为 b)	词的开始
[[:>:]](8版本之后改为 b)	词的结束

多数正则表达式实现使用单个反斜杠转义特殊字符，以便能使用这些字符本身。但 MySQL 要求两个反斜杠（MySQL自己解释一个，正则表达式库解释另一个）。

列举字符类

类	说明
[:alnum:]	任意字符和数字（同 [a-zA-Z0-9]）
[:alpha:]	任意字符（同 [a-zA-Z]）
[:blank:]	空格和制表（同 [\t]）
[:cntrl:]	ASCII控制字符（ASCII 0 到 31 和 127）
[:digit:]	任意数字（同 [0-9]）
[:xdigit:]	任意十六进制数字（同 [a-fA-F0-9]）
[:lower:]	任意小写字母（同 [a-z]）
[:upper:]	任意大写字母（同 [A-Z]）
[:print:]	任意可打印字符
[:graph:]	与[:print:]相同，但不包含空格
[:punct:]	既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:]	包括空格在内的任意空白字符(同 [\f\n\r\t\v])

简单的正则表达式测试

在不使用数据库表的情况下用 SELECT 来测试正则表达式。 REGEXP 检查总是返回0（没有匹配）或 1（匹配）。

SELECT 'hello' REGEXP 'hello\\b';
+---------------------------+
| 'hello' REGEXP 'hello\\b' |
+---------------------------+
|                         1 |
+---------------------------+

计算字段


### 将查出来的名字和国家拼接出来展示，使用了 Concat、Trim函数，和 AS 关键字
SELECT Concat( Trim(vend_name), '(', Trim(vend_country), ')') AS vend_name FROM vendors ORDER BY vend_name;
+------------------------+
| vend_name              |
+------------------------+
| ACME(USA)              |
| Anvils R Us(USA)       |
| Furball Inc.(USA)      |
| Jet Set(England)       |
| Jouets Et Ours(France) |
| LT Supplies(USA)       |
+------------------------+


### 将20005订单中的所有物品查出来，通过数量和单价算出总价
SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM orderitems WHERE order_num = 20005;
+---------+----------+------------+----------------+
| prod_id | quantity | item_price | expanded_price |
+---------+----------+------------+----------------+
| ANV01   |       10 |       5.99 |          59.90 |
| ANV02   |        3 |       9.99 |          29.97 |
| TNT2    |        5 |      10.00 |          50.00 |
| FB      |        1 |      10.00 |          10.00 |
+---------+----------+------------+----------------+

测试计算

SELECT 提供了测试和实验函数与计算的一个很好的方法，就是省略 FROM 子句

SELECT 3*2;
+-----+
| 3*2 |
+-----+
|   6 |
+-----+

SELECT Now();
+---------------------+
| Now()               |
+---------------------+
| 2019-11-21 22:51:13 |
+---------------------+

使用数据处理函数

函数没有 SQL 的可移植性强，几乎每种主要的 DBMS 的实现都支持其他实现不支持的函数，有时差异还很大。为了代码的可移植性，很多人不赞成使用特殊实现的功能，虽然这样做很有好处，但是对于应用程序的性能可能出现影响。如果决定使用函数，应该保证做好代码注释。

文本处理函数

函数	说明
Left()	返回串左边的字符
Length()	返回串的长度
Locate()	找出串的一个子串
Lower()	将串转换为小写
LTrim()	去掉串左边的空格
Right()	返回串右边的字符
RTrim()	去掉串右边的空格
Soundex()	返回串的 SOUNDEX 值
SubString()	返回子串的字符
Upper()	将串转换为大写

SOUNDEX 是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX 考虑了类似的发音字节和音节，使得能对串进行发音比较而不是字母比较。

### 例如用 Y. Lie 把 Y Lee 搜出来，因为它们发音类似
SELECT cust_name, cust_contact FROM customers WHERE Soundex(cust_contact) = Soundex('Y. Lie');
+-------------+--------------+
| cust_name   | cust_contact |
+-------------+--------------+
| Coyote Inc. | Y Lee        |
+-------------+--------------+

日期和时间处理函数

函数	说明
AddDate()	增加一个日期（天、周等）
AddTime()	增加一个时间（时、分等）
CurDate()	返回当前日期
CurTime()	返回当前时间
Date()	返回日期时间的日期部分
DateDiff()	计算两个日期之差
Date_Add()	高度灵活的日期运算函数
Date_Format()	返回一个格式化的日期或时间串
Year()	返回一个日期的年份部分
Month()	返回一个日期的月份部分
Day()	返回一个日期的天数部分
DayOfWeek()	对于一个日期，返回对应的星期几
Hour()	返回一个时间的小时部分
Minute()	返回一个时间的分钟部分
Second()	返回一个时间的秒部分
Now()	返回当前日期和时间

### 检索出日期为 2005-09-01 这天的订单记录
SELECT cust_id, order_num FROM orders WHERE order_date = '2005-09-01';


### 上面的检索有个问题，如果 order_date 存储的带有时间，例如 2005-09-01 11:30:05 ，就检索不到了，解决办法是让仅将给出的日期与列中的日期部分进行比较
SELECT cust_id, order_num FROM orders WHERE Date(order_date) = '2005-09-01';

### 如果想检索出2005年9月的所有订单

### 方法一，得记住每个月有多少天，甚至要知道是不是闰年的2月
SELECT cust_id, order_num FROM orders WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';

### 方法二, 使用 Year() 和 Month() 函数
SELECT cust_id, order_num FROM orders WHERE Year(order_date) = 2005 AND Month(order_date) = 9;

注意

使用日期过滤，日期必须为 yyyy-mm-dd ,这样能排除一些歧义，年份也应该使用4位数字，更加可靠。

数值处理函数

函数	说明
Abs()	返回一个数的绝对值
Sin()	返回一个角度的正弦
Cos()	返回一个角度的余弦
Tan()	返回一个角度的正切
Exp()	返回一个数的指数值
Mod()	返回除操作的余数
Pi()	返回圆周率
Rand()	返回一个随机数
Sqrt()	返回一个数的平方根

汇总数据

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

### 计算出 products 表中所有产品的平均价格
SELECT AVG(prod_price) AS avg_price FROM products;
+-----------+
| avg_price |
+-----------+
| 16.133571 |
+-----------+

### 查看 customers 表中所有客户的总数
SELECT COUNT(*) AS num_cust FROM customers;
+----------+
| num_cust |
+----------+
|        5 |
+----------+
### 只对具有电子邮件地址的客户计数
SELECT COUNT(cust_email) AS num_cust FROM customers;
+----------+
| num_cust |
+----------+
|        3 |
+----------+

### 计算出订单号为 20005 的物品总数
SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;
+---------------+
| items_ordered |
+---------------+
|            19 |
+---------------+

### 多个聚集函数组合
SELECT COUNT(*) AS num_items, MIN(prod_price) AS price_min, MAX(prod_price) AS price_max, AVG(prod_price) AS price_avg FROM products;
+-----------+-----------+-----------+-----------+
| num_items | price_min | price_max | price_avg |
+-----------+-----------+-----------+-----------+
|        14 |      2.50 |     55.00 | 16.133571 |
+-----------+-----------+-----------+-----------+

参数 ALL 和 DISTINCT

使用 DISTINCT 参数时，只会计算包含不同的值的行，如果指定参数为 ALL 或者不指定参数，默认参数为 ALL ，会计算所有的行。

### 看一下产品表里有多少家供应商，因为有可能一家供应商提供很多产品。
### 全部的行
SELECT COUNT(vend_id) AS vend_count FROM products;
+------------+
| vend_count |
+------------+
|         14 |
+------------+
### 去重后就知道有 4 家供应商
SELECT COUNT(DISTINCT vend_id) AS vend_count FROM products;
+------------+
| vend_count |
+------------+
|          4 |
+------------+

注意

AVG() 只能用来确定单个特定数值列的平均值，而且列名必须作为函数参数传入，想获取多个列的平均值，必须使用多个 AVG() 函数。
AVG() 函数忽略列值为 NULL 的行。
COUNT(*) 对表中行的数目进行计数，不管列中是空值（NULL）还是非空值。
使用 COUNT(column) 对特定列中具有值的行进行计数，会忽略 NULL 值。
MAX() 函数会忽略值为 NULL 的行（MIN()也是）。它一般是用来找出最大的数值和日期值，但是也可以对非数值的数据使用，例如返回文本列中的最大值，MAX() 会返回最后一行（MIN

() 会返回第一行）。

SUM() 函数会忽略值为 NULL 的行
在表示某个聚集函数的结果时，不应该使用表中实际的列明，最好是指定别名，这样便于理解和使用。

分组数据

数据分组

SELECT vend_id, prod_price FROM products GROUP BY vend_id, prod_price;

GROUP BY 子句后面可以加多个列。
SELECT子句中的列名必须为分组列或列函数（聚集计算语句除外），例如按照 vend_id, prod_price 分组，SELECT 后面检索的列必须是 vend_id, prod_price。
列函数对于GROUP BY子句定义的每个组各返回一个结果，例如取最大值时，就是每个组的最大值。
如果分组列中有 NULL 值，则 NULL 将作为一个分组返回，如果列中有多行 NULL 值，它们将分为一组。
GROUP BY 子句必须在 WHERE 子句之后， ORDER BY 子句之前。

过滤分组

### 列出至少有两个订单的所有顾客
SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2;

### 列出具有2个（含）以上、价格为10（含）以上的产品的供应商
SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price >= 10 GROUP BY vend_id HAVING COUNT(*) >= 2;

### 列出总计订单价格大于等于50的订单，并按照总价排序
SELECT order_num, SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) >= 50 ORDER BY ordertotal;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
|     20006 |      55.00 |
|     20008 |     125.00 |
|     20005 |     149.87 |
|     20007 |    1000.00 |
+-----------+------------+

HAVING 跟 WHERE 类似，但是 WHERE 在数据分组前进行过滤，HAVING 在数据分组后进行过滤。

SELECT 子句顺序

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

一个持续更新的github笔记，链接地址：Front-End-Basics，可以watch,也可以star。

此篇文章的地址：MySql必知必会