MySQL必知必会—检索数据篇

方旭

MySQL必知必会—概念篇

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必知必会

阅读 694

陈工移山
移山之志,一点一滴。

移山之志,一点一滴。

3.2k 声望
435 粉丝
0 条评论
你知道吗?

移山之志,一点一滴。

3.2k 声望
435 粉丝
宣传栏