mysql distingct_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

纵向制表

\\

反斜杠

^

文本的开始

$

文本的结束

[[:<: 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。