《MYSQL必知必会》阅读随记 （ 四 - 十三章）

工具准备

MySQL Workbench 8.0

create schema crashcourse;

use crashcourse;

分别运行 create.sql 和 populate.sql

这两个脚本下载地址：

脚本地址

第四章 - 检索数据

检索单个列

SELECT prod_name
FROM products;

利用 SQL SELECT 语句从 products 表种检索处一个名为 prod_name 的列。

检索多个列

SELECT prod_id, prod_name, prod_price
FROM products;

检索所有列

SELECT *
FROM products;

检索不同的行

SELECT vend_id
FROM products;

该表其实只有 4 个组，可以通过 DISTINCT 关键字检索出含有不同值的列表, DISTINCT 放在列名之前

SELECT DISTINCT vend_id
FROM products;

限制结果

SELECT prod_name
FROM products
LIMIT 5; # 指示MySQL返回不多于5行
SELECT prod_name
FROM products
LIMIT 5, 5; # 知识返回从行 5 开始的 5 行。即 (start_index, row_counts)
# 由于版本原因，会有行 0 和 行 1 的混乱，所以建议使用如下语法：
LIMIT 4 OFFSET 3 # 意为从行 3 开始取 4 行

使用完全限定的表明

SELECT products.prod_name
FROM products;
SELECT products.prod_name
FROM crashcourse.products;
# 等同于
SELECT prod_name
FROM products

第五章 - 排序检索数据

排序数据

字句( clause ) SQL 由子句构成。SELECT 语句的 FROM 是字句。

排序可以用 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;
# 对两个列进行排序 -- 首先按照价格，然后再按照名称 （ORDER BY 后列顺序）

指定排序方向

降序指定 DESC 关键字

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

# 价格降序，产品名正排序
SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price DESC;

DESC 关键字只应用到直接位于其前面的列名字

与 DESC 相反的关键字是 ASC( ASCENDING ). 就是正序，实际上默认正序，没多大用处

例子，用 ORDER BY 和 LIMIT 组合找到一个列中最高或者最低的值

SELECT prod_price
FROM products
ORDER BY prod_price DESC
LIMIT 1;

第六章 - 过滤数据

WHERE

只检索所需要数据需要指定 搜索条件( search criteria ), 搜索条件也成为 过滤条件 ( filter condition)。

在 SELECT 语句中，数据根据 WHERE 字句中指定的搜索条件进行过滤。WHERE 字句在表名( FROM字句 ) 之后给出

SELECT prod_name, prod_price
FROM products
WHERE prod_price = 2.50;

在使用 ORDER BY 和 WHERE 字句时，应该让 ORDER BY 位于 WHERE 之后，否则产生错误

WHERE 可以根据字句操作符来查找符合条件的值，操作符如:

• = 等于

• <> 不等于

• != 不等于

• < 、<=、 >、 >=

• BETWEEN 在指定的两个值之间

  SELECT prod_name, prod_price
  FROM products
  WHERE prod_price
  BETWEEN 2.00 AND 3.00
  ORDER BY prod_name DESC;
  

空值检查

SELECT prod_name
FROM products
WHERE prod_price IS NULL;

在过滤选择不具备某特定值的行时，是不返回 具有 NULL 的行的。因为“未知”具有特殊含义，数据库不知道是否匹配，所以在匹配过滤或不匹配过滤时不返回它们

因此，在过滤数据时，一定要验证返回数据中确实给出了被过滤列具有 NULL 的行

第七章 - 数据过滤

**操作符( operator )**用来联结或改变 WHERE 字句中的字句的关键字。也成为逻辑操作符(logical operator)

IN操作符

IN 操作符用来指定条件返回，范围中每个条件都可以进行匹配。IN 取合法值得由逗号分隔得清单，全都括在圆括号中。

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

# 等效于
SELECT prod_name, prod_price
FROM products
WHERE vend_id = 1002 OR vend_id = 1003
ORDER BY prod_name;

• 在使用长得合法选项清单时，IN 操作符得语法更清楚且更直观
• 在使用 IN 时，计算得次序更容易管理
• IN 操作符一般比 OR 操作符清单执行更快
• IN 得最大优点是可以包含其他 SELECT 语句，使得能够更动态地建立 WHERE 字句。

NOT 操作符

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

第八章 - 用通配符进行过滤

通配符（ wildcard ） 用来匹配值的一部分特殊字符

搜索模式（ search pattern ) 由字面值、通配符或两者组合构成的搜索条件

为在搜索子句中使用通配符，必须使用 LIKE 操作符。LIKE 指示 MySOL，后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较

% – 任何字符出现任意次数

SELECT prod_name, prod_price
FROM products
WHERE prod_name LIKE 'jet%'; # jet 区分大小写

• % 告诉 MySQL 接收 jet 之后的任意字符，不管有多少字符

SELECT prod_name, prod_price
FROM products
WHERE prod_name LIKE '%anvil%';

• 表示匹配任何位置包含文本 anvil 的值。

(_) – 下划线通配符

用途与 % 一样，但下划线只匹配单个字符而不是多个字符

第九章 - 用正则表达式进行搜索

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

除关键字 LIKE 被 REGEXP 替代外，这条语句看上去非常像使用 LIKE 的语句。实际上它告诉 MySQL:REGEXP 后跟的东西作为正则表达式处理

正则表达式匹配不区分大小写，要区分大小写可使用 BINARY 关键字

–> WHERE prod_name REGEXP BINARY ‘JetPack .000’;

OR 匹配

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

匹配几个字符之一

SELECT prod_name, prod_price
FROM products
WHERE prod_name REGEXP '[123] Ton'
ORDER BY prod_name;

匹配特殊字符

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

\\ 转义

匹配字符类

预定义的字符集–字符类

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

定位符

元字符	说明
^	文本的开始
$	文本的结尾
[[:<:]]	词的开始
[[:>:]]	词的结尾

SELECT prod_name
FROM products
WHERE prod_name REGEXP '^[0-9\\.]'
ORDER BY prod_name;

第十章 - 创建计算字段

计算字段不实际存在于数据库表中。计算字段是运行时在 SELECT 语句内创建的。

字段（ filed ）

基本上与列 （ column ）的意思相同，经常互换使用，不过数据库列一般称为列，而术语字段通常用在计算字段的连接上。

拼接（ concatenate ）

将值联结到一起构成单个值

SELECT Concat(vend_name, ' (', vend_country, ')')
FROM vendors
ORDER BY vend_name;

Concat() 拼接串，即把多个串链接起来形成一个较长的串。

Concat() 需要一个或多个指定的串，各个串之间用逗号分隔。上面的 SELECT 语句连接以下 4 个元素：

• 存储在 vend_name 列中的名字
• 包含一个空格和一个左圆括号的串
• 存储在 vend_country 列中的国家
• 包含一个右圆括号的串

可以通过 MySQL 的 RTrim() 函数来完成删除数据右侧多余的空格来整理数据
RTrim() 函数去掉值右边的所有空格。通过使用 RTrim() ，各个列都进行了整理（ LTrim() 同理 ）

别名( alias )

之前 SELECT 语句拼接地址字段，但这个新计算列的“名字”，实际上是没有的，只是一个值。如果只是查看，没有什么问题，但一个未命名的列不能用于客户机应用中，因为客户机没有办法引用它。
为了解决这个问题 SQL 支持列别名。别名 ( alias ) 是一个字段或值的替换名。别名用 AS 关键字赋予。

SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS
vend_tittle
FROM vendors
ORDER BY vend_name;

有图，结果与以前相同，但是列名由 "Concat(vend_name, ' (', vend_country, ')')"
改为了 vend_title 的列名，任何客户机应用都可以按名引用这个列，就像它是一个实际的表列一样

别名的其他用途 别名还有其他用途。常见的用途包括在实际的表列名包含不符合规定的字符（如空格）时重新命名它，在原来的名字含混或容易误解时扩充它等。
别名有时也称为导出列( derived column )

执行算数计算

SELECT prod_id, quantity, item_price
FROM orderitems
WHERE order_num = 20005;

计算

SELECT prod_id,
	   quantity,
       item_price,
       quantity * item_price AS expanded_price
FROM orderitems
WHERE order_num = 20005;

第十一章 - 使用数据处理函数

使用函数

大多数 SQL 实现支持以下类型的函数

• 用于处理文本串（ 如删除或填充值，转换值为大写或小写 ）的文本函数
• 用于在数值数据上进行算数操作（ 如返回绝对值，进行代数运算 ）的数值函数
• 用于处理日期和时间值并从这些值上提取特定成分（ 例如，返回两个日期只差，检查日期有效性等 ）的日期和时间函数
• 返回 DBMS 正使用的特殊信息（ 如返回用户登录信息，检查版本细节 ）的系统函数

文本处理函数

SELECT vend_name, Upper(vend_name) AS vend_name_upcase
FROM vendors
ORDER BY vend_name;

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

SOUNDEX 是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。

SELECT cust_name, cust_contact
FROM customers
WHERE cust_contact = 'Y. Lie';

上面的语句正常来说，因为表中的顾客联系名为 Y. Lee，如果用 Y. Lie 是不会返回数据，但是如果使用 Soundex() 函数进行搜索，会匹配所有发音类似于 Y. Lie 的联系名

SELECT cust_name, cust_contact
FROM customers
WHERE Soundex(cust_contact) = Soundex('Y. Lie');

日期和时间处理函数

SELECT cust_id, order_num
FROM orders
WHERE Date(order_date) = '2005-09-01';

SELECT cust_id, order_num
FROM orders
WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';

SELECT cust_id, order_num
FROM orders
WHERE Year(order_date) = 2005 AND Month(order_date) = 9;

数值处理函数

如 Abs() Cos() Exp() 等

第十二章 - 汇总数据

聚集函数

我们常需要汇总数据而不用把他们实际检索出来，为此 MySQL 提供了专门的函数。使用这些函数，MySQL 查询可用于检索数据，以便分析和报表生成。这种类型的检索例子有如下几种

• 确定表中行数( 或者满足某个条件或包含某个特定值的行数 )
• 获得表中行组的和
• 找出表列 ( 或所有行或所有特定的行 ) 的最大值、最小值和平均值

聚集函数( agregate function ) 运行在行组上，计算和返回单个值的函数

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

SELECT AVG(prod_price) AS avg_price
FROM products;

**COUNT()**函数进行计数。可利用这个函数确定表中行的数目或符合特定条件的行的数目
该函数有两种使用方式

• 使用 COUNT( * ) 对表中行的数目进行计数，不管表列中包含的时空值 ( NULL ) 还是非空值
• 使用 COUNT( column ) 对特定列中具有值得行进行计数，忽略 NULL 值

SELECT COUNT(cust_email) AS num_cust
FROM customers;

SUM()
SUM() 也可以用来合计计算值

SELECT SUM(item_price * quantity) AS total_price
FROM orderitems
WHERE order_num = 20005;

聚集不同值

• 对于所有得行执行计算，指定 ALL 参数或不给参数( 因为 ALL 时默认行为 )
• 只包含不同得值，指定 DISTINCT 参数

下面这个例子使用平均函数返回特定供应商提供得产品的平均价格。但是使用了 DISTINCT 参数，因此平均值只考虑各个不同的价格

SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;

如果指定列名，则 DISTINCT 只能用于 COUNT()。DISTINCT 不能用于 COUNT( * ) ，因此不允许使用 COUNT( DISTINCT )，否则会产生错误。类似地，DISTINCT 必须使用列明，不能用于计算或表达式

将 DISTINCT 用于 MIN() 和 MAX() 虽然 DISTINCT 从技术上可用于 MIN() 和 MAX() ，但这样做实际没有价值，一个列中地最小值和最大值不管是否包含不同值都是相同的

组合聚集函数

SELECT 语句可根据需要包含多个聚集函数

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;

第十三章 - 分组数据

数据分组

如果要返回每个供应上提供的产品数目，或者返回只提供单项产品的供应商所提供的产品，或返回提供 10 个以上产品的供应商。就需要分组来做。分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算

创建分组

分组是 SELECT 语句的 GROUP BY 字句中建立的。

SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;

上面的 SELECT 语句指定了两个列，vend_id 包含产品供应商的 ID，num_prods 为计算字段( 用 COUNT(*) 函数建立 )。GROUP BY 字句指示 MySQL 按 vend_id 排序并分组数据。这导致对每个 vend_id 而不是整个表计算 num_prods 一次。从输出看出，供应商 1 有 3个产品，2 有 2 个产品，3 有 7 个产品，而 5 有 2 个产品。

因为使用了 GROUP BY，就不必指定要计算和估值的每个组了。系统会自动完成GROUP BY 字句提示 MySQL 分组数据，然后对每个组而不是整个结果集进行聚集

在使用 GROUP BY 字句之前，需要直到的一些重要规定

• GROUP BY 字句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制
• 如果在 GROUP BY 字句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（ 所以不能从个别的列取回数据 ）
• GROUP BY 字句中列出的每个列都必须是检索列或有效的表达式（ 但不能是聚集函数 ）。如果在 SELECT 中使用表达式，则必须在 GROUP BY 中使用相同的表达式。不能使用别名
• 除聚集计算语句外，SELECT 语句中的每个列都必须在 GROUP BY 字句中给出
• 如果分组中具有 NULL 值，则 NULL 将作为一个分组返回。如果列中有多行 NULL 值，它们将分为一组
• GROUP BY 字句必须出现在 WHERE 字句之后，ORDER BY 字句之前

过滤分组

HAVING 支持所有 WHERE 操作符

SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;

最后一行增加了 HAVING 字句，它过滤 COUNT(*) >= 2 ( 两个以上的定单 ) 的那些分组

WHERE 过滤行，HAVING 过滤列
HAVING 和 WHERE 和差别 WHERE 在数据分组前进行过滤，HAVING 在数据分组后进行过滤。这是一个重要的区别，WHERE 排除的行不包括在分组中。这可能会改变计算值，从而影响HAVING 字句中基于这些值过滤掉的分组

SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >= 2;

WHERE 字句过滤所有 prod_price 至少为 10 的行。然后按 vend_id 分组数据， HAVING 字句过滤计数为 2 或 2 以上的分组。如果没有 WHERE 字句，将会多检索出两行（供应商1002，萧索的所有产品价格都在 10 以下；供应商 1001，销售 3 个产品，但只有一个产品的价格大于等于 10）:

分组和排序

虽然 GROUP BY 和 ORDER BY 经常完成相同的工作，但实际上是不同的

ORDER BY	GROUP BY
排序产生的输出	分组行。单输出可能不是分组的顺序
任意列都可以使用（甚至非选择的咧也可以使用）	只可能使用选择列或表达式列，而且不许使用每个选择列表达式
不一定需要	如果与聚集函数一起使用列（或表达式），则必须使用

不要忘记 ORDER BY 一般在使用 GROUP BY 字句时，应该也给出 ORDER BY 字句。这是保证数据正确排序的唯一方法。

SELECT order_num, SUM(quantity * item_price ) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity * item_price ) >= 50
ORDER BY ordertotal;

SELECT 字句顺序

字句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算聚集时采用
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否
LIMIT	要检索的行数	否