MySQL必知必会

发表于 更新于 分类于 ， 阅读次数：

第1章 了解SQL

  • 数据库软件应称为DBMS（数据库管理系统）。
  • 数据库是通过DBMS创建和操纵的容器。
  • 在很大程度上说，数据库究竟是文件还是别的什么东西并不重要，因为你并不直接访问数据库；你使用的是DBMS，它替你访问数据库。
  • 模式（schema）： 关于数据库和表的布局及特性的信息。
  • 有时，模式用作数据库的同义词。遗憾的是，模式的含义通常在上下文中并不是很清晰。
  • 主键（primary key）
  • SQL（发音为字母S-Q-L或sequel）是结构化查询语言（Structured Query Language）的缩写。

第2章 MySQL简介

  • DBMS可分为两类：一类为基于共享文件系统的DBMS，另一类为基于客户机——服务器的DBMS。前者（包括诸如Microsoft Access和FileMaker）用于桌面用途，通常不用于高端或更关键的应用。
  • MySQL、Oracle以及Microsoft SQL Server等数据库是基于客户机——服务器的数据库。客户机—服务器应用分为两个不同的部分。服务器部分是负责所有数据访问和处理的一个软件。
  • 为进行所有数据库交互，客户机软件都要与服务器软件进行通信。
  • 每个MySQL安装都有一个名为mysql的简单命令行实用程序。

配置MySQL环境变量

  • Win + Pause Break 打开系统窗口。
  • 高级系统设置 - 高级 - 环境变量 - 系统变量
  • 找到Path，点击编辑 - 浏览
  • 选择MySQL安装目录下的bin文件夹所在路径，默认路径为C:\Program Files\MySQL\MySQL Server 5.7\bin
  • 一路确定，即完成环境变量的配置。此时打开命令行窗口，即通过mysql -u root -p命令并输入密码后访问数据库。

统一字符集为UTF-8

为了中文字符的顺利存储，需要将数据库的字符集统一为UTF-8。

修改C:\ProgramData\MySQL\MySQL Server 5.7\my.ini：

  • 找到[mysql]

    在下方添加default-character-set=utf8

  • 找到[mysqld]

    在下方添加character-set-server=utf8

  • 找到[client]

    在下方添加default-character-set=utf8

  • 在操作系统命令提示符下输入mysql将出现一个如下的简单提示：
    • 为了指定用户登录名ben，应该使用mysql -u ben。为了给出用户名、主机名、端口和口令，应该使用mysql -u ben -p -h myserver -P 9999。完整的命令行选项和参数列表可用mysql --help获得。
  • 命令用；或\g结束，换句话说，仅按Enter不执行命令；
  • 输入quit或exit退出命令行实用程序。

第3章 使用MySQL

连接

  • 在最初安装MySQL时，很可能会要求你输入一个管理登录（通常为root）和一个口令。如果你使用的是自己的本地服务器，并且是简单地试验一下MySQL，使用上述登录就可以了。但现实中，管理登录受到密切保护。
  • 为了连接到MySQL，需要以下信息：
    1. 主机名（计算机名）——如果连接到本地MySQL服务器，为localhost
    2. 端口（如果使用默认端口3306之外的端口）
    3. 一个合法的用户名
    4. 用户口令（如果需要）

选择数据库

  • 在你最初连接到MySQL时，没有任何数据库打开供你使用。在你能执行任意数据库操作前，需要选择一个数据库。为此，可使用USE关键字。
  • 关键字是一些用于执行MySQL操作的特殊词汇，决不要用关键字命名一个表或列。
  • USE语句并不返回任何结果。依赖于使用的客户机，显示某种形式的通知。
  • 在MySQL Query Browser中，双击Schemata列表中列出的任一数据库以使用它。你看不到USE命令的实际执行，但会看到被选择的数据库（黑体加亮），而且应用标题栏将显示所选择的数据库名。必须先使用USE打开数据库，才能读取其中的数据。

了解数据库和表

  • 数据库、表、列、用户、权限等信息被存储在数据库和表中（MySQL使用MySQL来存储这些信息）。不过，内部的表一般不直接访问。

  • 可用MySQL的SHOW命令来显示这些信息（MySQL从内部表中提取这些信息）

    • SHOW DATABASES;

      返回可用数据库的一个列表。包含在这个列表中的可能是MySQL内部使用的数据库

    • SHOW TABLES;

      获得当前选择的数据库内可用表

    • SHOW COLUMNS FROM table_name;/DESCRIBE table_name;

      要求给出一个表名，显示对应的所有表列及其具体属性

      DESCRIBE customers;是SHOW COLUMNS FROM customers;的一种快捷方式。

    • SHOW STATUS;

      用于显示广泛的服务器状态信息

    • SHOW CREATE DATABASE database_name;和SHOW CREATE TABLE table_name;

      分别用来显示创建特定数据库或表的MySQL语句。

    • SHOW GRANTS;

      用来显示授予用户（所有用户或特定用户）的安全权限；

    • SHOW ERRORS;和SHOW WARNINGS;

      用来显示服务器错误或警告消息。

    • SHOW PROCESSLIST;

      显示所有活动进程(以及它们的线程ID和执行时间)

  • **自动增量(aotu_increment)**：

    在每个行添加到表中时，MySQL可以自动地为每个行分配下一个可用编号，不用在添加一行时手动分配唯一值。如果需要它，则必须在用CREATE语句创建表时把它作为表定义的组成部分。

MySQL 5支持一个新的INFORMA-TION_SCHEMA命令，可用它来获得和过滤模式信息。

第4章 检索数据

检索单个列

1
2
SELECT column_name
FROM table_name;

所需的列名在SELECT关键字之后给出，FROM关键字指出从其中检索数据的表名。

  • 如果没有明确排序查询结果，则返回的数据的顺序没有特殊意义。返回数据的顺序可能是数据被添加到表中的顺序，也可能不是。只要返回相同数目的行，就是正常的。

  • 多条SQL语句必须以分号（;）分隔。MySQL如同多数DBMS一样，不需要在单条SQL语句后加分号，但即使不一定需要，但加上分号肯定没有坏处。如果你使用的是mysql命令行，必须加上分号来结束SQL语句。

  • SQL语句不区分大小写

  • 许多SQL开发人员喜欢对所有SQL关键字使用大写，而对所有列和表名使用小写，这样做使代码更易于阅读和调试。

检索多个列

SQL语句一般返回原始的、无格式的数据。数据的格式化是一个表示问题，而不是一个检索问题。

检索所有列

这可以通过在实际列名的位置使用星号（*）通配符来达到。

  • 虽然使用通配符可能会使你自己省事，不用明确列出所需列，但检索不需要的列通常会降低检索和应用程序的性能。

检索不同的行

使用DISTINCT关键字，顾名思义，此关键字指示MySQL只返回不同的值。

  • 不能部分使用DISTINCT：

    DISTINCT关键字应用于所有列而不仅是前置它的列

限制结果

为了返回第一行或前几行，可使用LIMIT子句：

1
2
3
SELECT prod_name
FROM products
LIMIT 5 OFFSET 4; -- 从第4行开始，取5行；等价于 LIMIT 4, 5

MySQL检索出来的第一行是行0而不是行1。

Sql Server不支持LIMIT语句，通过TOP实现结果的限制：

1
2
3
4
SELECT TOP 5 prod_name
FROM products
WHERE prod_name NOT IN (SELECT TOP 3 prod_name
                        FROM peoducts);

第5章 排序检索数据

排序数据

  • 关系数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有意义
  • 为了明确地排序用SELECT语句检索出的数据，可使用ORDER BY子句
  • 取一个或多个列的名字，据此对输出进行排序，也可以通过非选择列进行排序

按多个列排序

  • 在按多个列排序时，排序完全按所规定的顺序进行

指定排序方向

  • 默认的排序顺序：升序排序（从A到Z）。为了进行降序排序，必须指定DESC关键字。
  • DESC关键字只应用到直接位于其前面的列名（和DISTINCT关键字不同）
  • 如果想在多个列上进行降序排序，必须对每个列指定DESC关键字。
  • 在字典（dictionary）排序顺序中，A被视为与a相同，这是MySQL（和大多数DBMS）的默认行为。但是，许多数据库管理员能够在需要时改变这种行为（如果你的数据库包含大量外语字符，可能必须这样做）。这里，关键的问题是，如果确实需要改变这种排序顺序，用简单的ORDER BY子句做不到。你必须请求数据库管理员的帮助。
  • 如果使用LIMIT，它必须位于ORDER BY之后。使用子句的次序不对将产生错误消息。

第6章 过滤数据

使用WHERE子句

  • 只检索所需数据需要指定搜索条件（search criteria），搜索条件也称为过滤条件（filter condition）。在SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤。WHERE子句在表名（FROM子句）之后给出。

  • 应该让ORDER BY位于WHERE之后，否则将会产生错误

WHERE子句操作符

  • MySQL支持下列所有条件操作符：

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

检查单个值

  • MySQL在执行匹配时默认不区分大小写

不匹配检查

以下例子列出不是由供应商1003制造的所有产品：

1
2
3
SELECT vend_id, prod_name
FROM products
WHERE vend_id != 1003;

什么时候使用引号：

单引号用于限定字符串(包括日期类型)，用来与数值列进行比较的值不用引号。

** 用来限定字符串**。(Python既可以用单引号，也可以用双引号)

范围值检查

  • 在使用BETWEEN时，必须指定两个值——所需范围的低端值和高端值。这两个值必须用AND关键字分隔。BETWEEN匹配范围中所有的值，包括指定的开始值和结束值。

空值检查

  • SELECT语句有一个特殊的WHERE子句，可用来检查具有NULL值的列。这个WHERE子句就是IS NULL子句

    1
    2
    3
    SELECT prod_name
    FROM products
    WHERE prod_price IS NULL;

  • 在通过过滤选择出不具有特定值的行时，你可能希望返回具有NULL值的行，但是，不行。因为未知具有特殊的含义，数据库不知道它们是否匹配，所以在匹配过滤或不匹配过滤时不返回它们。

第7章 数据过滤

组合WHERE子句

  • MySQL允许给出多个WHERE子句。这些子句可以两种方式使用：以AND子句的方式或OR子句的方式使用。

计算次序

  • SQL（像多数语言一样）在处理OR操作符前，优先处理AND操作符，为了使机器能够理解，应使用圆括号明确地分组相应的操作符。如：

    1
    2
    3
    SLECT prod_name, prod_price
    FROM products
    WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;

  • 任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确地分组操作符，消除歧义。

IN操作符

  • IN操作符后跟由逗号分隔的合法值清单，整个清单必须括在圆括号中。IN操作符完成与OR相同的功能。

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

  • 为什么要使用IN操作符？其优点具体如下。

    1. 在使用长的合法选项清单时，IN操作符的语法更清楚且更直观。
    2. 在使用IN时，计算的次序更容易管理（因为使用的操作符更少）。
    3. IN操作符一般比OR操作符清单执行更快。
    4. IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建立WHERE子句。第14章将对此进行详细介绍。

NOT操作符

  • NOT操作符有且只有一个功能，那就是否定它之后所跟的任何条件。如：

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

  • 对于简单的WHERE子句，使用NOT确实没有什么优势。但在更复杂的子句中，NOT是非常有用的。例如，在与IN操作符联合使用时，NOT使找出与条件列表不匹配的行非常简单。

  • MySQL支持使用NOT对IN、BETWEEN和EXISTS子句取反，这与多数其他DBMS允许使用NOT对各种条件取反有很大的差别。

第8章 用通配符进行过滤

LIKE操作符

  • 怎样搜索产品名中包含文本anvil的所有产品？用简单的比较操作符肯定不行，必须使用通配符。利用通配符可创建比较特定数据的搜索模式

  • 搜索模式（search pattern）：

    由字面值、通配符或两者组合构成的搜索条件

  • 通配符本身实际是SQL的WHERE子句中有特殊含义的字符，SQL支持几种通配符：

    • %
    • _

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

  • 操作符何时不是操作符？答案是在它作为谓词（predi-cate）时。从技术上说，LIKE是谓词而不是操作符。虽然最终的结果是相同的，但应该对此术语有所了解，以免在SQL文档中遇到此术语时不知道。

百分号(%)通配符

  • 最常使用的通配符是百分号（%）。在搜索串中，**%表示任何字符出现任意次数**。

    1
    2
    3
    SELECT prod_id, prod_name
    FROM products
    WHERE prod_name LIKE 'jet%';

    运行结果：

    prod_id prod_name
    JP1000 JetPack 1000
    JP2000 JetPack 2000

  • 根据MySQL的配置方式，搜索可以是区分大小写的。如果区分大小写，'jet%'与JetPack 1000将不匹配。

  • 通配符可以在搜索模式中任意位置使用，并且可以使用多个通配符。

    1
    2
    3
    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

  • 重要的是要注意到，除了一个或多个字符外，%还能匹配0个字符。**%代表搜索模式中给定位置的0个、1个或多个字符**。

  • 注意尾空格：

    尾空格可能会干扰通配符匹配。例如，在保存词anvil时，如果它后面有一个或多个空格，则子句WHERE prod_name LIKE ‘%anvil’将不会匹配它们，因为在最后的l后有多余的字符。解决这个问题的一个简单的办法是在搜索模式最后附加一个%。一个更好的办法是使用函数（第11章将会介绍）去掉首尾空格。

  • 虽然似乎%通配符可以匹配任何东西，但有一个例外，即NULL。即使是WHERE prod_name LIKE ‘%’也不能匹配用值NULL作为产品名的行。

下划线(_)通配符

  • 下划线的用途与%一样，但下划线只匹配单个字符。

使用通配符的技巧

  1. 不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
  2. 在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。**把通配符置于搜索模式的开始处，搜索起来是最慢的(会使索引失效)**。

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

正则表达式介绍

正则表达式(Regular Expression)是用来匹配文本的特殊的串（字符集合）。

所有种类的程序设计语言、文本编辑器、操作系统等都支持正则表达式。

使用MySQL正则表达式

MySQL用WHERE子句对正则表达式提供了初步的支持，允许你指定正则表达式，过滤SELECT检索出的数据。

  • MySQL仅支持多数正则表达式实现的一个很小的子集

基本字符匹配

例1：

1
2
3
4
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000'
ORDER BY prod_name
  • 除关键字LIKE被REGEXP替代外，这条语句看上去非常像使用LIKE的语句（第8章）。它告诉MySQL，REGEXP后所跟的东西作为正则表达式（与文字正文1000匹配的一个正则表达式）处理。

例2：

1
2
3
4
SELECT prod_name
FROM products
WHERE prod_name REGEXP '.000'
ORDER BY prod_name;
  • .是正则表达式语言中一个特殊的字符，它表示匹配任意一个字符。

LIKE 与 REGEXP 的重要区别

例：

1
2
3
4
5
6
7
8
9
SELECT prod_name
FROM products
WHERE prod_name LIKE '1000'
ORDER BY prod_name;

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

  • LIKE匹配整个列

    如果被匹配的文本在列值中出现，LIKE将不会找到它，相应的行也不被返回（除非使用通配符）

  • REGEXP在列值内进行匹配

    如果被匹配的文本在列值中出现，REGEXP将会找到它，返回相应行。

那么，REGEXP能不能用来匹配整个列值（从而起与LIKE相同的作用）？答案是肯定的，使用^和$定位符（anchor）即可，本章后面介绍。

  • MySQL中的正则表达式匹配（自版本3.23.4后）不区分大小写（即，大写和小写都匹配）。为区分大小写，可使用BINARY关键字，如WHERE prod_name REGEXP BINARY 'JetPack .000'。

进行OR匹配

为搜索两个串之一（或者为这个串，或者为另一个串），使用|。

  • REGEXP '1000|2000'。

    |为正则表达式的OR操作符，它表示匹配其中之一。

  • '1000|2000|3000’将匹配1000或2000或3000。

匹配几个字符之一

.用于匹配任一字符，如果你只想匹配特定的字符，怎么办？可通过指定一组用[]括起来的字符来完成:

1
2
3
4
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[123] Ton'
ORDER BY prod_name;

  • [123]定义一组字符，它的意思是匹配1或2或3

  • 正如所见，[]是另一种形式的OR语句。事实上，正则表达式[123] Ton为[1|2|3] Ton的缩写，也可以使用后者

  • 字符集合也可以被否定，即，它们将匹配除指定字符外的任何东西。为否定一个字符集，在集合的开始处放置一个^即可。因此，尽管[123]匹配字符1、2或3，但[^123]却匹配除1、2、3字符外的任何东西

匹配范围

  • 集合可用来定义要匹配的一个或多个字符。集合[0123456789]将匹配数字0到9。为简化这种类型的集合，可使用-来定义一个范围：[0-9]功能上等同于上述数字列表。

  • 范围不限于完整的集合，[1-3]和[6-9]也是合法的范围。此外，范围不一定只是数值的，[a-z]匹配任意字母字符。

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

匹配特殊字符

为了匹配特殊字符，必须用\\为前导。\\-表示查找-, \\.表示查找.。

这种处理就是所谓的转义（escaping），正则表达式内具有特殊意义的所有字符都必须以这种方式转义。

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

匹配字符类

为更方便工作，可以使用预定义的字符集，称为字符类（character class）。下表列出字符类以及它们的含义：

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

匹配多个实例

你可能需要寻找所有的数，不管数中包含多少数字，或者你可能想寻找一个单词并且还能够适应一个尾随的s（如果存在），等等。这可以用下表列出的正则表达式重复元字符来完成：

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

  • 例1：

    1
    2
    3
    4
    SELECT prod_name
    FROM products
    WHERE prod_name REGEXP '\\([0-9] sticks?\\)'
    ORDER BY prod_name;

    其中?作用于之前的一个字符，即s，获取到两个prod_name：

    1. TNT (1 stick)
    2. TNT (5 sticks)

  • 例2：

    1
    2
    3
    4
    SELECT prod_name
    FROM products
    WHERE prod_name REGEXP '[[:digit:]{4}]'
    ORDER BY prod_name;

    匹配连在一起的4个数字，得到如下prod_name：

    1. JetPack 1000
    2. JetPack 2000

    需要注意的是，在使用正则表达式时，编写某个特殊的表达式几乎总是有不止一种方法，本例还可以这样编写：

    1
    2
    3
    4
    SELECT prod_name
    FROM products
    WHERE prod_name REGEXP '[0-9][0-9][0-9][0-9]'
    ORDER BY prod_name;

定位符

上述所有例子都是匹配一个串中任意位置的文本。为了匹配特定位置的文本，需要使用下表出的定位符：

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

  • 例如，想找出以一个数(包括以小数点开始的数)开始的所有产品：

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

    ^匹配串的开始。

  • ^的双重用途

    1. 在集合中（用[]定义），用它来否定该集合（如[^123]）
    2. 用来指串的开始处。

  • 使REGEXP起类似LIKE的作用

    LIKE匹配整个串，而REGEXP匹配子串。利用定位符，通过用^开始每个表达式，用$结束每个表达式，可以使REGEXP的作用与LIKE一样。

  • 简单的正则表达式测试

    REGEXP检查总是返回0（没有匹配）或1（匹配）。可以用带文字串的REGEXP来测试表达式，并试验它们。相应的语法如下：

    SELECT 'Hello' REGEXP '[0-9]'，这个例子显然返回0。

第10章 创建计算字段

计算字段

计算字段的作用是将数据库中几列所需的数据进行连接，直接从数据库中检索出转换、计算或格式化过的数据；而不是检索出数据后，在客户端应用程序中重新格式化。一般来说，在数据库服务器上完成这些操作比在客户端中完成要快得多，因为DBMS是设计来快速有效地完成这种处理的。

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

拼接字段

拼接(concatenate)：将值联结到一起构成单个值，具体就是把两个列拼接起来。

多数DBMS使用+或||来实现拼接，MySQL则使用Concat()函数来实现。当把SQL语句转换成MySQL语句时一定要把这个区别铭记在心。

1
2
3
SELECT Concat(vend_name, '(', vend_country, ')')
FROM vendors
ORDER BY vend_name;
  • Concat()需要一个或多个指定的串，各个串之间用逗号分隔。

在第8章中曾提到通过删除数据右侧多余的空格来整理数据，这可以使用MySQL的RTrim()函数(第11章 文本处理函数)来完成：

1
2
3
SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')')
FROM vendors
ORDER BY vend_name;

第11章 使用数据处理函数

函数

  • 相对来说，多数SQL语句是可移植的，在SQL实现之间有差异时，这些差异通常不那么难处理。而函数的可移植性却不强。几乎每种主要的DBMS的实现都支持其他实现不支持的函数，而且有时差异还很大。

  • 为了代码的可移植，许多SQL程序员不赞成使用特殊实现的功能。虽然这样做很有好处，但不总是利于应用程序的性能。

  • 如果你决定使用函数，应该保证做好代码注释，以便以后你（或其他人）能确切地知道所编写SQL代码的含义。

使用函数

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

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

转换数据类型函数

CAST(expression AS data_type)

可以选择的类型(data_type)：

  • 二进制，同带binary前缀的效果 : BINARY
  • 字符型，可带参数 : CHAR()
  • 日期 : DATE
  • 时间: TIME
  • 日期时间型 : DATETIME
  • 浮点数 : DECIMAL
  • 整数 : SIGNED
  • 无符号整数 : UNSIGNED

文本处理函数

函数 说明
TRIM(s) 删除字符串首尾的空格
LTRIM(s) 删除字符串开头的空格
RTRIM(s) 删除字符串末尾的空格
UPPER(s) 将字符串转为大写
LOWER(s) 将字符串转为小写
LENGTH(s) 获取字符串长度
SUBSTRING(s, start, length) 获取字符串子集
LEFT(s, n) 返回字符串的前n个字符
RIGHT(s, n) 返回字符串的后n个字符
REPLACE(s, s1, s2) 将字符串s中的子集s1替换为s2
GROUP_CONCAT([DISTINCT] expression
[ORDER BY expression] SEPARATOR sep)		 将分组中的字符串通过自定义的分隔符进行连接

  • 例子：

    • 错误写法：

      1
      2
      3
      SELECT ID, NAME, LENGTH(NAME) AS LEN_NAME
      FROM world.city
      WHERE LEN_NAME >= 10;

      city表中没有LEN_NAME列，执行会报错。

    • 正确写法：

      1
      2
      3
      SELECT ID, NAME, LENGTH(NAME) AS LEN_NAME
      FROM world.city 
      WHERE LENGTH(NAME) >= 10;

  • GROUP_CONCAT()的用法

    1
    2
    3
    SELECT dept_no, GROUP_CONCAT(emp_no SEPARATOR ',') 
    FROM dept_emp 
    GROUP BY dept_no;

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

使得能对串进行发音比较而不是字母比较。

虽然SOUNDEX不是SQL概念，但MySQL（就像多数DBMS一样）都提供对SOUNDEX的支持。

在这个例子中，WHERE子句使用Soundex()函数来转换cust_contact列值和搜索串为它们的SOUNDEX值。因为Y.Lee和Y.Lie发音相似，所以它们的SOUNDEX值匹配，因此WHERE子句正确地过滤出了所需的数据。

日期和时间处理函数

  • 一般，应用程序不使用用来存储日期和时间的格式，因此日期和时间函数总是被用来读取、统计和处理这些值。由于这个原因，日期和时间函数在MySQL语言中具有重要的作用。
函数 说明
ADDDATE(d, n) 计算起始日期d加上n天的日期
如SELECT ADDDATE(2017-06-15, INTERVAL 10 DAY)
ADDTIME(t, n) 时间t加上n秒的时间
CURDATE() 返回当前日期
CURTIME() 返回当前时间(不含日期)
NOW() 返回当前日期和时间
DATE() 从日期或日期时间表达式中提取日期值
如SELECT DATE("2017-06-15")
DATEDIFF(d1, d2) 计算日期d1->d2之间相隔的天数
TIMESTAMPDIFF(INTERVAL, datetime_expr1, datetime_expr2) 比较两个日期的时间差。
interval 确定时间差的单位：SECOND、MINUTE、HOUR、DAY、MONTH、YEAR
差值为 后一个时间参数 - 前一个时间参数
DATE_ADD(d, INTERVAL expr type) 计算起始日期d加上一个时间段后的日期
如DATE_ADD(paidTime, INTERVAL -2 MONTH)
DATE_SUB(d,INTERVAL expr unit) 计算起始日期d减去一个时间段后的日期
如DATE_SUB(paidTime, INTERVAL 2 MONTH)
DATE_FORMAT(d, f) 按表达式f的要求显示日期d
如SELECT DATE_FORMAT('2020-07-28', "%Y-%m")
DAYOFWEEK(d) 当前日期是星期几
YEAR(d) 返回年份
MONTH(d) 返回月份
DAY(d) 返回日期值d的日部分
HOUR(t) 返回小时值
MINuTE(t) 返回分钟值
SECOND(t) 返回秒钟值
TIME(expression) 提取表达式中的时间部分

  • 数据经常需要用日期进行过滤

    • 首先需要注意的是MySQL使用的日期格式。无论你什么时候指定一个日期，不管是插入或更新表值还是用WHERE子句进行过滤，日期必须为格式yyyy-mm-dd。虽然其他的日期格式可能也行，但这是首选的日期格式，因为它排除了多义性（如，04/05/06是2006年5月4日或2006年4月5日或2004年5月6日或……）。

    • 应该总是使用4位数字的年份。支持2位数字的年份，MySQL处理00-69为2000-2069，处理70-99为1970-1999。虽然它们可能是打算要的年份，但使用完整的4位数字年份更可靠，因为MySQL不必做出任何假定。

  • 如果你想要的仅是日期，则使用Date()是一个良好的习惯，即使你知道相应的列只包含日期也是如此。这样，如果由于某种原因表中以后有日期和时间值，你的SQL代码也不用改变。当然，也存在一个Time()函数，在你只想要时间时应该使用它。

  • Date()和Time()都是在MySQL 4.1.1中第一次引入的。

  • 还有一种日期比较需要说明。如果你想检索出2005年9月下的所有订单，怎么办？简单的相等测试不行，因为它也要匹配月份中的天数。有几种解决办法。

    • 其中之一如下所示：

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

    • 另外一种办法（不需要记住每个月中有多少天或不需要操心闰年2月）：

      1
      2
      3
      SELECT cust_id, order_num
      FROM orders
      WHERE YEAR(order_date) = 2005 AND MONTH(order_date) = 9;

数值处理函数

函数 说明
ABS(x) 返回x的绝对值
SIN(x) 返回正弦值(参数是弧度)
COS(x) 返回余弦值(参数是弧度)
TAN(x) 返回正切值(参数是弧度)
EXP(x) 返回e的x次方
MOD(x, y) 返回x除以y以后的余数
PI() 返回圆周率(3.141593)
RAND() 返回0到1的随机数
SQRT() 返回x的平方根
ROUND(column_name, decimals) 把数值字段舍入为指定的小数位数(decimals)

IF表达式

1
IF(expr1, expr2, expr3)

expr1的值为TRUE，则返回值为expr2
expr1的值为FALSE，则返回值为expr3

IFNULL函数

IFNULL(check_expression, replacement_value )

  • replacement_value：在check_expression为NULL时返回的表达式
  • check_expression和replacement_value类型必须相同

SQL Server中相同功能的函数为ISNULL()

示例：

1
2
3
4
5
6
SELECT IFNULL(
        (SELECT DISTINCT Salary 
        FROM Employee 
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),
        NULL) AS SecondHighestSalary;

开窗函数/窗口函数

窗口函数的作用

在日常工作中，经常会遇到需要在每组内排名，比如下面的业务需求：

排名问题：每个部门按业绩来排名

topN问题：找出每个部门排名前N的员工进行奖励

面对这类需求，就需要使用SQL的高级功能窗口函数了。

什么是窗口函数

窗口函数，也叫OLAP(Online Analytical Processing，实时分析处理)函数。

基本语法：

1
2
<窗口函数> OVER (PARTITION BY <用于分组的列名> 
             	ORDER BY <用于排序的列名>)

为什么叫窗口函数？

​ 因为PARTITION BY分组后的结果称为”窗口”，表示范围的意思。

<窗口函数>位置可以放以下两种函数：

  1. 专用窗口函数
  2. 聚合函数

因为窗口函数是对WHERE或者GROUP BY子句处理后的结果进行操作，所以窗口函数原则上只能写在SELECT子句中。

专用窗口函数RANK

如果想在每个班级内按成绩排名，需要如下操作：

1
2
3
SELECT *, RANK() OVER (PARTITION BY 班级
            ORDER BY 成绩 DESC) AS ranking
FROM 班级表;

  • 在每个班级内按成绩排名：

    • 按班级分组

      PARTITION BY用来对表分组

    • 按成绩排名

      ORDER BY用来对各分组内的结果排序(默认升序)

  • 为什么使用窗口函数，而不是GROUP BY+ORDER BY呢？

    因为GROUP BY分组汇总后，改变了表的行数，一行只有一个GROUP。而**PARTITION BY和RANK函数不会减少原表中的行数**。

其他专用窗口函数
  • DENSE_RANK()
  • ROW_NUMBER()
1
2
3
4
5
SELECT *,
	RANK() OVER (ORDER BY 成绩 DESC) AS ranking,
    DENSE_RANK() OVER (ORDER BY 成绩 DESC) AS dense_rank,
    ROW_NUMBER() OVER (ORDER BY 成绩 DESC) AS row_num
FROM 班级表;

  • NTILE(n)：将指定行等分成n个组，赋予组号（1-n）

    • n：指定分组数

      如果分组数n不能被整除，将会生成两种大小的组，组内行数差距为1，行数多的组以ORDER BY指定的顺序在前。

四个专用窗口函数的区别：

窗口函数 特点
RANK() 如果有并列名次的行，会占用下一名次的位置
(如：1, 1, 1, 4)
DENSE_RANK() 如果有并列名次的行，不占用下一名次的位置
(如：1, 1, 1, 2)
ROW_NUMBER() 不考虑并列名次的情况
(如：1, 2, 3, 4)
NTILE(n) 将指定行等分成n个组，赋予组号（1-n）
聚合函数作为窗口函数

聚和窗口函数和上面提到的专用窗口函数用法完全相同，只需要把聚合函数写在窗口函数的位置即可，但是函数后面括号里面不能为空，需要指定聚合的列名。

1
2
3
4
5
6
7
SELECT *,
	SUM(成绩) OVER (ORDER BY 学号) AS current_sum,
	AVG(成绩) OVER (ORDER BY 学号) AS current_avg,
	COUNT(成绩) OVER (ORDER BY 学号) AS current_count,
	MAX(成绩) OVER (ORDER BY 学号) AS current_max,
	MIN(成绩) OVER (ORDER BY 学号) AS current_min
FROM 班级表;

上述聚合函数，都是针对自身记录以及自身记录**之前(已查询出来的行)**的所有数据进行计算(从第一行到当前为止)。

聚合函数作为窗口函数，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等），同时可以看出每一行数据，对整体统计数据的影响。

  • 前后函数

    • LAG()
    • LEAD(return_value, offset, [,default])：从当前行访问下一行的数据或下一行之后的行。
    • return_value： 基于指定偏移量的后续行的返回值，必须为单个值。
      - offset：从当前行转发的行数，值为正整数，默认是1

  • 头尾函数

    • FIRST_VAL()
    • LAST_VAL()
注意事项

PARTITION子句可以省略，即不指定分组。但这样就失去了窗口函数的功能，所以一般不要这么使用。

MySQL 8.0前没有窗口函数，怎么办

通过变量达到相同效果。

例：

创建一个人员年龄表。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE TABLE person 
(
    id INT, 
    first_name VARCHAR(20), 
    age INT, 
    gender CHAR(1)
);

INSERT INTO person 
VALUES (1, 'Bob', 25, 'M');
INSERT INTO person 
VALUES (2, 'Jane', 20, 'F');
INSERT INTO person 
VALUES (3, 'Jack', 30, 'M');
INSERT INTO person 
VALUES (4, 'Bill', 32, 'M');
INSERT INTO person 
VALUES (5, 'Nick', 22, 'M');
INSERT INTO person 
VALUES (6, 'Kathy', 18, 'F');
INSERT INTO person 
VALUES (7, 'Steve', 36, 'M');
INSERT INTO person 
VALUES (8, 'Anne', 25, 'F');

现在要根据不同性别进行分组排序他们的年龄，并得到序列号：

1
2
3
4
5
6
7
8
9
10
11
12
# MySQL8.0之后，用窗口函数
SELECT gender, first_name, age, RANK() OVER (PARTITION BY gender 
                   ORDER BY age DESC) AS 排名
FROM person;

# MySQL8.0之前，用变量
SELECT gender, first_name, age, rank
FROM (SELECT first_name, gender, age, 
      			@rank:=IF(@gen=gender, @rank+1, 1) rank, -- 一开始@gen为空，获得排名为第一，其后相同性别时排名排名递增(按ROW_NUM()窗口函数规则排序) 
      			@gen:=gender -- 给gen赋值
      FROM person, (SELECT @rank:=0, @gen:=NULL) temp -- 自然连接，为person表添加两个列
      ORDER BY gender, age DESC) b -- 按性别、年龄排序

行转列Pivot函数(MySQL没有，SQL Server有)

1
2
3
4
5
6
7
SELECT *
FROM table_name	
PIVOT(
聚合函数 (value_column)
FOR pivot_column
IN (<column_list>)
);

例：一张各学生各课程的分数表如下：

姓名 科目 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94

  • 现要根据姓名分别统计三门成绩，即：姓名、语文、数学、物理

    • MySQL采用MAX(CASE WHEN ... ELSE ... END)

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      SELECT 姓名, MAX(CASE 
      					WHEN 科目='语文' THEN 分数 
                    		ELSE 0 -- MAX 是为了确保筛选出科目为语文的成绩
      				END) 语文, 
      			MAX(CASE 
                          WHEN 科目='数学' THEN 分数
                          ELSE 0
                      END) 数学,
      			MAX(CASE 
                     		WHEN 科目='物理' THEN 分数
                      END) 物理
      FROM table1
      GROUP BY 姓名;

    • 其他可以使用Pivot函数的数据库

      1
      2
      3
      4
      5
      SELECT *
      FROM table1
      Pivot(MAX(分数) -- 对 指定列 使用聚合函数
            FOR 科目 -- 需要 转换为列 的行
            IN (语文, 数学, 物理)) -- 转换为列后的各列名

列转行UNPIVOT函数

第12章 汇总数据

聚集函数

  • 我们经常需要汇总数据而不用把它们实际检索出来，为此MySQL提供了专门的函数。使用这些函数，MySQL查询可用于检索数据，以便分析和报表生成。这种类型的检索例子有以下几种：
    • 确定表中行数（或者满足某个条件或包含某个特定值的行数）
    • 获得表中行组的和。
    • 找出表列（或所有行或某些特定的行）的最大值、最小值和平均值。
  • 上述例子都需要对表中数据（而不是实际数据本身）汇总。因此，返回实际表数据是对时间和处理资源的一种浪费（更不用说带宽了）。
函数 说明 对 列值为NULL的行 的处理
AVG() 返回某列的平均值 忽略
COUNT() 返回某列的行数 COUNT(*****) /COUNT(1)不忽略
COUNT(指定列名)忽略
MAX() 返回某列的最大值 忽略
MIN() 返回某列的最小值 忽略
SUM() 返回某列之和 忽略
  • MySQL还支持一系列的标准偏差聚集函数，但本书并未涉及这些内容。

AVG()函数

  • AVG()通过对表中行数计数并计算特定列值之和，求得该列的平均值。可返回所有列的平均值，也可用来返回特定列或行的平均值。

    • 如下，使用AVG()返回products表中所有产品的平均价格
    1
    2
    SELECT AVG(prod_price) AS avg_price
    FROM products;
    • 如下，返回特定供应商所提供产品的平均价格
    1
    2
    3
    SELECT AVG(prod_price) AS avg_price
    FROM products
    WHERE vend_id = 1003;

  • AVG()只能用来确定特定数值列得平均值，且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个AVG()函数。

  • AVG()函数忽略列值为NULL的行。

COUNT()函数

  • 可利用COUNT()确定表中行的数目或符合特定条件的行的数目。

  • COUNT()函数有两种使用方式：

    1. 使用COUNT(*)/COUNT(1)对表中行的数目进行计数，包括空值（NULL）。
    2. 使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。

  • 去重计数

    COUNT(DISTINCT xxx)：xxx为列名

MAX()函数

  • MAX()返回指定列中的最大值。MAX()要求指定列名。如：

    1
    2
    SELECT MAX(prod_price) AS max_price
    FROM products;

  • 对非数值数据使用MAX()：

    虽然MAX()一般用来找出最大的数值或日期值，但MySQL允许将它用来返回任意列中的最大值，包括返回文本列中的最大值。在用于文本数据时，如果数据按相应的列排序，则MAX()返回最后一行。

  • MAX()函数忽略列值为NULL的行。

MIN()函数

  • MIN()的功能正好与MAX()功能相反，它返回指定列的最小值。与MAX()一样，MIN()要求指定列名

  • 在用于文本数据时，如果数据按相应的列排序，则MIN()返回最前面的行。

  • MIN()函数忽略列值为NULL的行。

SUM()函数

  • SUM()用来返回指定列值的和（总计）

    1
    2
    3
    SELECT SUM(quantity) AS items_ordered
    FROM orderitems
    WHERE order_num = 20005;

  • SUM()也可以用来合计计算值。在下面的例子中，合计每项物品的item_price*quantity，得出总的订单金额：

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

  • SUM()函数忽略列值为NULL的行。

聚集不同值

  • 聚集函数的DISTINCT的使用，已经被添加到MySQL 5.0.3中。下面所述内容在MySQL 4.x中不能正常运行。

  • 上述5个聚集函数都可以如下使用：

    1. 对所有的行执行计算，指定ALL参数或不给参数（因为ALL是默认行为）；
    2. 只包含不同的值，指定DISTINCT参数。

  • 下面的例子使用AVG()函数返回特定供应商提供的产品的平均价格。它与上面的SELECT语句相同，但使用了DISTINCT参数，因此平均值只考虑各个不同的价格：

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

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

组合聚集函数

1
2
3
4
5
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;

  • 这里用单条SELECT语句执行了4个聚集计算，返回4个值（products表中物品的数目，产品价格的最低、最高以及平均值）

  • 在指定别名以包含某个聚集函数的结果时，不应该使用表中实际的列名。虽然这样做并非不合法，但使用唯一的名字会使你的SQL更易于理解和使用（以及将来容易排除故障）。

第13章 分组数据

数据分组

  • 如果要返回每个供应商提供的产品数目怎么办？或者返回只提供单项产品的供应商所提供的产品，或返回提供10个以上产品的供应商怎么办？这就是分组显身手的时候了。分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算。

创建分组

  • 分组是在SELECT语句的GROUP BY子句中建立的。理解分组的最好办法是看一个例子：

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

    结果：

    vend_id num_prods
    1001 3
    1002 2
    1003 7
    1005 2

  • GROUP BY子句指示MySQL按vend_id排序并分组数据。这导致对每个vend_id而不是整个表计算num_prods一次

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

  • 一些重要的规定：

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

  • 使用WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值，如下所示:

    1
    2
    3
    SELECT vend_id, COUNT(*) AS num_prods
    FROM products
    GROUP BY vend_id WITH ROLLUP;

过滤分组

  • WHERE子句能过滤指定的行；HAVING子句用于过滤分组。

  • HAVING非常类似于WHERE。事实上，目前为止所学过的所有类型的WHERE子句都可以用HAVING来替代。

  • 过滤分组的例子：

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

    这里WHERE子句不起作用，因为过滤是基于分组聚集值。

  • HAVING和WHERE的差别

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

  • 有没有在一条语句中同时使用WHERE和HAVING子句的需要呢？事实上，确实有。假如想进一步过滤上面的语句，使它返回过去12个月内具有两个以上订单的顾客。为达到这一点，可增加一条WHERE子句，过滤出过去12个月内下过的订单。然后再增加HAVING子句过滤出具有两个以上订单的分组。

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

分组和排序

  • 虽然GROUP BY和ORDER BY经常完成相同的工作，但它们是非常不同的。

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

  • 我们经常发现用GROUP BY分组的数据确实是以分组顺序输出，但情况并不总是这样，它并不是SQL规范所要求的。

  • 应该提供明确的ORDER BY子句，即使其效果等同于GROUP BY子句也是如此。千万不要仅依赖GROUP BY排序数据。

SELECT子句顺序

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

第14章 使用子查询

子查询

  • MySQL 4.1引入了对子查询的支持，所以要想使用本章描述的SQL，必须使用MySQL 4.1或更高级的版本。

  • 查询（query）

    任何SQL语句都是查询。但此术语一般指SELECT语句。

  • SQL还允许创建子查询（subquery），即嵌套在其他查询中的查询。

利用子查询进行过滤

  • 本书所有章中使用的数据库表都是关系表。对于包含订单号、客户ID、订单日期的每个订单，orders表存储一行。各订单的物品存储在相关的orderitems表中。orders表不存储客户信息。它只存储客户的ID。实际的客户信息存储在customers表中。

  • 现在，假如需要列出订购物品TNT2的所有客户，应该怎样检索？下面列出具体的步骤：

    1. 检索包含物品TNT2的所有订单的编号。
    2. 检索具有前一步骤列出的订单编号的所有客户的ID。
    3. 检索前一步骤返回的所有客户ID的客户信息。

    上述每个步骤都可以单独作为一个查询来执行。可以把一条SELECT语句返回的结果用于另一条SELECT语句的WHERE子句。也可以使用子查询来把3个查询组合成一条语句。

  • 在SELECT语句中，子查询总是从内向外处理。包含子查询的SELECT语句难以阅读和调试，特别是它们较为复杂时更是如此。把子查询分解为多行并且适当地进行缩进，能极大地简化子查询的使用。

    1
    2
    3
    4
    5
    6
    7
    SELECT cust_name, cust_contact
    FROM customers
    WHERE cust_id IN (SELECT cust_id
                     FROM orders
                     WHERE order_num IN (SELECT order_num
                                         FROM orderitems
                                         WHERE prod_id = 'TNT2'));

  • 在WHERE子句中使用子查询能够编写出功能很强并且很灵活的SQL语句。对于能嵌套的子查询的数目没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询。

  • 列必须匹配

    在WHERE子句中使用子查询，应该保证SELECT语句具有与WHERE子句中相同数目的列。通常，子查询将返回单个列并且与单个列匹配，但如果需要也可以使用多个列。

  • 虽然子查询一般与IN操作符结合使用，但也可以用于测试等于（=）、不等于（<>）等。

作为计算字段使用子查询

  • 使用子查询的另一方法是创建计算字段

  • 假如需要显示customers表中每个客户的订单总数，订单与相应的客户ID存储在orders表中。操作应遵循以下步骤：

    1. 从customers表中检索客户列表
    2. 对于检索出的每个客户，统计其在orders表中的订单数目。对每个客户执行COUNT(*)计算，应该将COUNT(*)作为一个子查询。
    1
    2
    3
    4
    5
    6
    7
    SELECT cust_name,
    		cust_state,
    		(SELECT COUNT(*)
             FROM orders
             WHERE orders.cust_id = customers.cust_id) AS orders
    FROM customers
    ORDER BY cust_name;

  • 涉及外部查询的子查询称为相关子查询。任何时候只要列名可能有多义性，就必须使用这种语法（表名和列名由一个句点分隔）。

第15章 联结表

联结

  • 联结是利用SQL的SELECT能执行的最重要的操作

关系表

  • 假如有由同一供应商生产的多种物品，那么在何处存储供应商信息（如，供应商名、地址、联系方法等）呢？将这些数据与产品信息分开存储的理由如下。
    • 因为同一供应商生产的每个产品的供应商信息都是相同的，对每个产品重复此信息既浪费时间又浪费存储空间。
    • 如果供应商信息改变（例如，供应商搬家或电话号码变动），只需改动一次即可。
    • 如果有重复数据（即每种产品都存储供应商信息），很难保证每次输入该数据的方式都相同。不一致的数据在报表中很难利用。

相同数据出现多次决不是一件好事，此因素是关系数据库设计的基础。关系表的设计就是要保证把信息分解成多个表，一类数据一个表。各表通过某些常用的值（即关系设计中的关系（relational））互相关联。

在这个例子中，可建立两个表，一个存储供应商信息，另一个存储产品信息。

  • vendors表包含所有供应商信息，每个供应商占一行，每个供应商具有唯一的标识。此标识称为主键（primary key），可以是供应商ID或任何其他唯一值。

  • products表只存储产品信息，它除了存储供应商ID（vendors表的主键）外不存储其他供应商信息。

  • vendors表的主键又叫作products的外键，它将vendors表与products表关联，利用供应商ID能从vendors表中找出相应供应商的详细信息。

    外键（foreign key）为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。

总之，关系数据可以有效地存储和方便地处理。因此，关系数据库的可伸缩性远比非关系数据库要好。

可伸缩性（scale） 能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称之为可伸缩性好（scale well）

为什么要使用联结

如果数据存储在多个表中，要使用单条SELECT语句检索出数据，则需要使用联结。

联结是一种机制，用来在一条SELECT语句中关联表，因此称之为联结。使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。

创建联结

联结的创建非常简单，规定要联结的所有表以及它们如何关联即可。

例：

1
2
3
4
SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;
  • 这条语句的FROM子句列出了两个表，分别是vendors和products

WHERE子句的重要性

  • 在一条SELECT语句中联结几个表时，相应的关系是在运行中构造的。

  • 在联结两个表时，你实际上做的是将第一个表中的每一行与第二个表中的每一行配对。WHERE子句作为过滤条件，它只包含那些匹配给定条件（这里是联结条件）的行

  • 没有WHERE子句，第一个表中的每个行将与第二个表中的每个行配对，而不管它们逻辑上是否可以配在一起。由没有联结条件的表关系返回的结果为笛卡儿积。

    笛卡尔积：检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

    有时我们会听到返回称为叉联结（cross join）的笛卡儿积的联结类型。

内部联结

目前上述的联结称为等值联结（equijoin），它基于两个表之间的相等测试，也称为内部联结。

下面的SELECT语句返回与前面例子完全相同的数据：

1
2
3
4
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;
  • 这里，两个表之间的关系是FROM子句的组成部分，以INNER JOIN指定。在使用这种语法时，联结条件用特定的ON子句而不是WHERE子句给出。传递给ON的实际条件与传递给WHERE的相同。
  • ANSI SQL规范首选INNER JOIN语法。此外，尽管使用WHERE子句定义联结的确比较简单，但是使用明确的联结语法能够确保不会忘记联结条件，有时候这样做也能影响性能。

联结多个表

  • MySQL在运行时关联指定的每个表以处理联结。这种处理可能是非常耗费资源的，因此应该仔细，不要联结不必要的表。联结的表越多，性能下降越厉害。

第16章 创建高级联结

  • 给列起别名的语法如下：

    1
    2
    3
    4
    SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ') ') AS
    vend_title
    FROM vendors
    ORDER BY vend_name;

  • 别名除了用于列名和计算字段外，SQL还允许给表名起别名。这样做有两个主要理由：

    1. 缩短SQL语句

    2. 允许在单条SELECT语句中多次使用相同的表

      1
      2
      3
      4
      5
      SELECT cust_name, cust_contact
      FROM customers AS c, orders AS o, orderitems AS oi
      WHERE c.cust_id = o.cust_id
      	AND oi.order_num = o.order_num
      	AND prod_id = 'TNT2';

  • 表别名只在查询执行中使用。与列别名不一样，表别名不返回到客户机。

自联结

  • 假如你发现某物品（其ID为DTNTR）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。此查询要求首先找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品。

    • 方法一（子查询）:
    1
    2
    3
    4
    5
    SELECT prod_id, prod_name
    FROM products
    WHERE vend_id = (SELECT vend_id
                    FROM products
                    WHERE prod_id = 'DTNTR');
    • 方法二(联结)：
    1
    2
    3
    4
    SELECT p1.prod_id, p1.prod_name
    FROM products AS p1, products AS p2
    WHERE p1.vend_id = p2.vend_id
    	AND p2.prod_id = 'DTNTR';

    此查询中需要的两个表实际上是相同的表，因此products表在FROM子句中出现了两次。虽然这是完全合法的，但对products的引用具有二义性，因为MySQL不知道你引用的是products表中的哪个实例。为解决此问题，使用了表别名。

  • 自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是相同的，但有时候处理联结远比处理子查询快得多。应该试一下两种方法，以确定哪一种的性能更好。

自然联结

  • 标准的内部联结返回所有数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次。

  • 自然联结是这样一种联结，其中你只能选择那些唯一的列。这一般是通过对表使用通配符（**SELECT ***），对所有其他表的列使用明确的子集来完成的。如：

    1
    2
    3
    4
    5
    6
    SELECT c.*, o.order_num, o.order_date,
    		oi.prod_id, oi.quality, oi.item_price
    FROM customers AS c, orders AS o, orderitems AS oi
    WHERE c.cust_id = o.cust_id
    	AND oi.order_num = o.order_num
    	AND prod_id = 'FB';

  • 事实上，迄今为止我们建立的每个内部联结都是自然联结，很可能我们永远都不会用到不是自然联结的内部联结。

外部联结

  • 许多联结将一个表中的行与另一个表中的行相关联。但有时候会需要包含没有关联行的那些行。

    • 下面的SELECT语句给出一个简单的内部联结。它检索所有客户及其订单(不包括没有订单的客户)：

      1
      2
      3
      SELECT customers.cust_id, orders.order_num
      FROM customers INNER JOIN orders
      ON customers.cust_id = orders.cust_id;

    • 外部联结语法类似。为了检索素有客户(包括没有订单的客户)，可如下进行：

      1
      2
      3
      SELECT customer.cust_id, orders.order_num
      FROM customers LEFT OUTER JOIN orders
      ON customers.cust_id = orders.cust_id;

      这条SELECT语句使用了关键字OUTER JOIN来指定联结的类型（而不是在WHERE子句中指定）

  • 在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）

  • 外部联结的OUTER可省略

  • MySQL不支持简化字符*=和=*的使用，这两种操作符在其他DBMS中是很流行的。

  • 如果要检索所有客户及每个客户所下的订单数，下面使用了COUNT()函数的代码可完成此工作：

    1
    2
    3
    4
    5
    6
    SELECT customers.cust_name,
    	customers.cust_id,
    	COUNT(orders.order_num) AS num_ord
    FROM customers INNER JOIN orders
    ON customers.cust_id = orders.cust_id
    GROUP BY customers.cust_id;

使用联结和联结条件

  • 有必要汇总一下关于联结及其使用的某些要点：

    • 注意所使用的联结类型。一般我们使用内部联结，但使用外部联结也是有效的。

    • 应该总是提供联结条件，否则会得出笛卡儿积。

    • 在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前，分别测试每个联结。这将使故障排除更为简单。

第17章 组合查询

组合查询

  • MySQL也允许执行多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。这些组合查询通常称为并（union）或复合查询（compound query）。

  • 有两种基本情况，其中需要使用组合查询：

    1. 在单个查询中从不同的表返回类似结构的数据；
    2. 对单个表执行多个查询，按单个查询返回数据。

  • 任何具有多个WHERE子句的SELECT语句都可以作为一个组合查询给出，在以下段落中可以看到这一点。这两种技术在不同的查询中性能也不同。因此，应该试一下这两种技术，以确定对特定的查询哪一种性能更好。

创建组合查询

可用UNION操作符来组合数条SQL查询。利用UNION，可给出多条SELECT语句，将它们的结果组合成单个结果集。

使用UNION

  • UNION的使用很简单，只需给出每条SELECT语句，在各条语句之间放上关键字UNION。如：

    1
    2
    3
    4
    5
    6
    7
    SELECT vend_id, prod_id, prod_price
    FROM products
    WHERE prod_price <= 5
    UNION
    SELECT vend_id, prod_id, prod_price
    FROM products
    WHERE vend_id IN (1001, 1002);

    结果：

    vend_id prod_id prod_price
    1003 FC 2.50
    1002 FU1 3.42
    1003 SLING 4.49
    1003 TNT1 2.50
    1001 ANV01 5.99
    1001 ANV02 9.99
    1001 ANV03 14.99
    1002 OL1 8.99

    UNION指示MySQL执行两条SELECT语句，并把输出组合成单个查询结果集。

    作为参考，这里给出使用多条WHERE子句而不是使用UNION的相同查询：

    1
    2
    3
    4
    SELECT vend_id, prod_id, prod_price
    FROM products
    WHERE prod_price <= 5
    	OR vend_id IN (1001, 1002);

    这个简单的例子中，使用UNION可能比使用WHERE子句更为复杂。但对于更复杂的过滤条件，或者从多个表中检索数据的情形，使用UNION可能会使处理更简单。

UNION规则

  • UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合4条SELECT语句，将要使用3个UNION关键字）。

  • UNION中每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。

  • 列数据类型必须兼容。类型不必完全相同，但必须是DBMS可以隐含地转换的类型(如不同的数值类型或不同的日期类型)。

包含或取消重复的行

  • UNION从查询结果集中自动去除了重复的行（换句话说，它的行为与单条SELECT语句中使用多个WHERE子句条件一样）。

  • 如果想返回所有匹配行，可使用UNION ALL而不是UNION。

    1
    2
    3
    4
    5
    6
    7
    SELECT vend_id, prod_id, prod_price
    FROM products
    WHERE prod_price <= 5
    UNION ALL
    SELECT vend_id, prod_id, prod_price
    FROM products
    WHERE vend id IN (1001, 1002);

  • UNION ALL为UNION的一种形式，它完成WHERE子句完成不了的工作。如果确实需要每个条件的匹配行全部出现（包括重复行），则必须使用UNION ALL而不是WHERE。

对组合查询结果排序

  • 在用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后。对于结果集，不存在用一种方式排序一部分，而又用另一种方式排序另一部分的情况，因此不允许使用多条ORDER BY子句。

    1
    2
    3
    4
    5
    6
    7
    8
    SELECT vend_id, prod_id, prod_price
    FROM products
    WHERE prod_price <= 5
    UNION
    SELECT vend_id, prod_id, prod_price
    FROM products
    WHERE vend_id IN (1001, 1002)
    ORDER BY vend_id, prod_price;

索引

MySQL创建索引

索引的介绍

索引是一种数据结构，例如B-Tree，这种数据结构是需要额外的写入和存储为代价来提高表上数据检索的速度。

其中，当使用主键或唯一键创建表时，MySQL会自动创建名为PRIMARY的特殊索引, 该索引称为聚簇索引，这个索引本身与数据一起存储在同一个表中。PRIMARY索引之外的索引称为二级索引或非聚簇索引。

CREATE INDEX语句

  • 通常，创建表的时候就能为表创建索引：

    1
    2
    3
    4
    5
    6
    7
    CREATE TABLE t(
       c1	INT		PRIMARY KEY,
       c2	INT		NOT NULL,
       c3	INT		NOT NULL,
       c4	VARCHAR(10),
       INDEX (c2,c3)  # 创建了由c2、c3两列组成的索引
    );

  • 要为列或一组列添加索引，可以使用CREATE INDEX语句：

    1
    CREATE INDEX index_name ON table_name (column_list)

  • 默认情况下，如果未指定索引类型，MySQL将创建B-Tree索引。 以下显示了基于表的存储引擎的允许索引类型：

    存储引擎 允许的索引类型
    InnoDB BTREE
    MyISAM BTREE
    MEMORY/HEAP HASH, BTREE

举例

查找职位为Sales Rep的员工：

1
2
3
4
5
CREATE INDEX jobTitle ON employees(jobTitle);

SELECT employeeNumber, lastName, firstName
FROM employees
WHERE jobTitle = 'Sales Rep';

MySQL删除索引

1
2
3
DROP INDEX index_name ON tabel_name;

DROP INDEX PRIMARY ON tabel_name; # 删除索引名为PRIMARY的主键

MySQL查询索引信息

1
SHOW INDEXES FROM employees;

会得到一张二维表，各表列分别为：

列名 含义
Table 表名
Non_unique 0：索引不能包括重复值
1：索引可以包括重复值
Key_name 索引名称。如果名称相同，表明属于同一个索引，而不是重复。
Seq_in_index 索引中的列序列号，从1开始IN
Column_name 索引的列名
Collation 以什么方式存储于索引中
Cardinality 基数，表示索引中唯一值的数目的估计值。某个字段的重复值越少越适合建索引，所以一般根据Cardinality来判断索引是否具有高选择性
Sub_part 如果列只是被部分地编入索引，则为被编入索引的字符的数目。
如果整列被编入索引，则为NULL。
Packed 指示关键字如何被压缩。如果没有被压缩，则为NULL。压缩一般包括压缩传输协议、压缩列解决方案和压缩表解决方案。
Null 如果列含有NULL，则为YES
Index_type 索引类型，Mysql目前主要有以下几种索引类型：FULLTEXT，HASH，BTREE，RTREE。
Comment Index_comment 注释

使用唯一索引，避免重复

为了使一列或多列具有唯一性，通常使用PRIMARY KEY约束。，但每个表只能有一个主键。 因此，如果使多个列或多个组合列具有唯一性，则不能使用主键约束。

MySQL提供了另一种索引，叫做唯一索引，允许我们一个或者多个列的值具有唯一性。且每张表中可以有很多个唯一索引。

  1. 创建唯一索引

    1
    2
    CREATE UNIQUE INDEX index_name
    ON tabel_name(index_column_1, index_column_1, ...);

  2. 添加唯一索引

    1
    ALTER TABLE table_name ADD INDEX index_name( column_1,column_2 ) ;

    有一点值得注意:
    与其他数据库系统不同，MySQL将NULL值视为不同的值，所以可以在唯一索引中包含很多的空值。另一个重点是UNIQUE约束不适用于InnoDB存储引擎之外的NULL值。

例子

假设我们要管理应用程序中的联系人，并且还希望联系人表的每个联系人的电子邮件必须唯一，可以使用CREATE TABLE语句创建唯一约束来满足我们的需求：

1
2
3
4
5
6
7
8
CREATE TABLE IF NOT EXISTS contacts (
    id			INT				AUTO_INCREMENT		PRIMARY KEY,
    first_name	VARCHAR(50) 	NOT NULL,
    last_name 	VARCHAR(50) 	NOT NULL,
    phone 		VARCHAR(15) 	NOT NULL,
    email 		VARCHAR(100) 	NOT NULL,
    UNIQUE Index unique_email (email)
);

假设我们要一个first_name、last_name、phone的联合索引，我们可以使用下面的语法来得到：

1
2
CREATE UNIQUE INDEX idx_name_phone
ON contacts(first_name,last_name, phone);

索引的使用场景

  1. 主键(PRIMARY KEY)自动建立唯一索引
  2. 存在频繁作为查询条件的字段，应该创建索引
  3. 查询中与其他表关联的字段，外键关系建立索引
  4. 频繁更新的字段不适合建立索引，因为每次更新不单单时更新了记录还会更新索引
  5. WHERE条件里用不到的字段不创建索引
  6. 查询中排序的字段，排序的字段若通过索引去访问将会大大提高排序速度
  7. 查询中统计或者分组的字段

强制索引

FORCE INDEX：

1
2
3
4
SELECT * 
FROM salaries 
FORCE INDEX idx_emp_no -- idx_emp_no 是预设的索引
WHERE emp_no = 10005;

第18章 全文本搜索

理解全文本搜索

MySQL支持几种基本的数据库引擎。并非所有的引擎都支持本书所描述的全文本搜索。两个最常使用的引擎为MyISAM和InnoDB，前者支持全文本搜索，而后者不支持。

第8章和第9章介绍LIKE和正则表达式的搜索机制，存在几个重要的限制：

  • 性能

    通配符和正则表达式匹配通常要求MySQL尝试匹配表中所有行（而且这些搜索极少使用表索引）。因此，由于被搜索行数不断增加，这些搜索可能非常耗时。

  • 明确控制

    使用通配符和正则表达式匹配，很难（而且并不总是能）明确地控制匹配什么和不匹配什么。例如，指定一个词必须匹配，一个词必须不匹配，而一个词仅在第一个词确实匹配的情况下才可以匹配或者才可以不匹配。

  • 智能化的结果

    虽然基于通配符和正则表达式的搜索提供了非常灵活的搜索，但它们都不能提供一种智能化的选择结果的方法。例如，一个特殊词的搜索将会返回包含该词的所有行，而不区分包含单个匹配的行和包含多个匹配的行。

以上的限制都可以通过全文本搜索来解决。在使用全文本搜索时，MySQL不需要分别查看每个行，不需要分别分析和处理每个词。MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行。这样，MySQL可以快速有效地决定哪些词匹配（哪些行包含它们），哪些词不匹配，它们匹配的频率，等等。

使用全文本搜索

为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断地重新索引。在对表列进行适当设计后，MySQL会自动进行所有的索引和重新索引。

启用全文本搜索支持

一般在创建表时启用全文本搜索。CREATE TABLE语句（第21章中介绍）接受FULLTEXT子句，它给出被索引列的一个逗号分隔的列表。

1
2
3
4
5
6
7
8
CREATE TABLE productnotes(
	note_id		int			NOT NULL AUTO_INCREMENT,
    prod_id		char(10)	NOT NULL,
    note_date	datetime	NOT NULL,
    note_text	text		NULL,
    PRIMARY KEY(note_id),
    FULLTEXT(note_text)
) ENGINE=MyISAM;

  • FULLTEXT可以索引多个列

  • 在定义之后，MySQL自动维护该索引。在增加、更新或删除行时，索引随之自动更新。

  • 可以在创建表时指定FULLTEXT，或者在稍后指定（在这种情况下所有已有数据必须立即索引）。

不要在导入数据时使用FULLTEXT

​ 如果正在导入数据到一个新表，此时不应该启用FULLTEXT索引。应该先导入所有数据，再修改表，定义FULLTEXT。这样有助于更快地导入数据(而且使索引数据的总时间小于在导入每行时分别进行索引所需的总时间)。

进行全文本搜索

在索引之后，使用两个函数Match()和Against()执行全文本搜索，其中Match()指定被搜索的列，Against()指定要使用的搜索表达式。

1
2
3
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');

  • 此SELECT语句检索单个列note_text。由于WHERE子句，一个全文本搜索被执行。

  • 传递给Match()的值必须与FULLTEXT()定义中的相同。如果指定多个列，则必须列出它们（而且次序正确）。

  • 除非使用BINARY方式（本章中没有介绍），否则全文本搜索不区分大小写。

上述搜索也可以简单地用LIKE子句完成：

1
2
3
SELECT note_text
FROM productnotes
WHERE note_text LIKE '%rabbit%';

两种方式都没有使用ORDER BY子句。LIKE子句以不特别有用的顺序返回数据；而使用全文本搜索会返回以文本匹配的良好程度排序的数(搜索的内容出现的位置越靠前，等级越高)，全文本搜索的一个重要部分就是对结果排序。

  • 由于数据是索引的，全文本搜索还相当快。

使用查询扩展(QUERY EXPANSION)

查询扩展用来设法放宽所返回的全文本搜索结果的范围。

如下情况：你想找出所有提到anvils的注释，但还想找出可能与你的搜索有关的所有其他行，即使它们不包含词anvils。

在使用查询扩展时，MySQL对数据和索引进行两遍扫描来完成搜索：

  • 首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有行
  • 其次，MySQL检查这些匹配行并选择所有有用的词（我们将会简要地解释MySQL如何断定什么有用，什么无用）。
  • 再其次，MySQL再次进行全文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词。
1
2
3
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);

表中的行越多（这些行中的文本就越多），使用查询扩展返回的结果越好。

布尔文本搜索

MySQL支持全文本搜索的另外一种形式，称为布尔方式（BOOLEAN MODE）。以布尔方式，可以提供关于如下内容的细节：

  1. 要匹配的词；
  2. 要排斥的词（如果某行包含这个词，则不返回该行，即使它包含其他指定的词也是如此）；
  3. 排列提示（指定某些词比其他词更重要，更重要的词等级更高）;
  4. 表达式分组；
  5. 另外一些内容。
  • 即使没有定义FULLTEXT索引，也可以使用它。但这是一种非常缓慢的操作（其性能将随着数据量的增加而降低）。
1
2
3
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy' IN BOOLEAN MODE);

此全文本搜索检索包含词heavy的所有行。使用了IN BOOLEAN MODE，但没有指定布尔操作符，因此结果与没有指定布尔方式的操作相同。

  • 为了匹配包含heavy但不包含 任意以rope开始的词的行，可使用以下查询：

    1
    2
    3
    SELECT note_text
    FROM productnotes
    WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);

全文本布尔操作符：

布尔操作符 说明
+ 包含，词必须存在
- 排除，词必须不出现
> 包含，且增加等级值
< 包含，且减少等级值
() 把词组成子表达式（允许这些子表达式作为一个组被包含、排除、排列等）
~ 取消一个词的排序值
* 词尾的通配符
"" 定义一个短语(匹配一个短语，以便包含或排除这个短语)

例1：

1
2
3
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE); # 匹配包含词rabbit和bait的行

例2：

1
2
3
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit bait' IN BOOLEAN MODE); # 匹配包含词rabbit和bait中至少一个词的行

例3：

1
2
3
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('"rabbit bait"' IN BOOLEAN MODE); # 匹配短语rabbit bait

例4：

1
2
3
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('>rabbit <bait"' IN BOOLEAN MODE); # 匹配包含词rabbit和bait的行，增加前者等级，降低后者等级

例5：

1
2
3
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('+safe +(<combination)' IN BOOLEAN MODE); # 匹配词safe和combination的行，且降低后者的等级

在布尔方式中，不按等级值降序排序返回的行。

全文本搜索的使用说明

  1. 索引全文本数据时，短词会被忽略且从索引中排除。(短词：只有3个及以下字符的词，如果需要，这个数目可以更改)
  2. MySQL带有一个内建的非用词（stopword）列表，这些词在索引全文本数据时总是被忽略。如果需要，可以覆盖这个列表（请参阅MySQL文档以了解如何完成此工作）
  3. 许多词出现的频率很高，搜索它们没有用处（返回太多的结果）。因此，MySQL规定了一条50%规则，如果一个词出现在50%以上的行中，则将它作为一个非用词忽略。50%规则不用于IN BOOLEAN MODE。
  4. 如果表中的行数少于3行，则全文本搜索不返回结果（因为每个词或者不出现，或者至少出现在50%的行中）。
  5. 忽略词中的单引号。例如，don’t索引为dont。
  6. 不具有词分隔符（包括日语和汉语）的语言不能恰当地返回全文本搜索结果。
  7. 如前所述，仅在MyISAM数据库引擎中支持全文本搜索

SQL语句的查询顺序

1
2
3
4
5
6
7
8
SELECT DISTINCT column, AGG_FUNC(column_or_expression), …
FROM mytable JOIN another_table
ON mytable.column = another_table.column
WHERE constraint_expression
GROUP BY column
HAVING constraint_expression
ORDER BY column ASC/DESC
LIMIT count OFFSET COUNT;
执行顺序 语句 功能
1 FROM、JOIN 获取数据集
2 WHERE 过滤数据
3 GROUP BY 根据某一字段进行分组，并对各个组进行求聚合
4 HAVING 过滤聚合后的数据
5 SELECT 返回最终数据
6 DISTINCT 为最终数据去重
7 ORDER BY 为最终数据排序
8 LIMIT/OFFSET 限定最终数据的返回行，也可理解为分页查询
（如：LIMIT 4 OFFSET 3意为从行3开始取4行）		 
1
2
3
4
5
6
7
8
9
10
11
# 观察这种写法的问题
SELECT country, SUM(area) 
FROM country_data
WHERE SUM(area) > 1000 # GROUP BY还没执行，无法获取聚合值
GROUP BY country;

# 正确的写法
SELECT country, SUM(area) 
FROM country_data
GROUP BY country
HAVING SUM(area) > 1000;

CASE语句

1
2
3
4
5
6
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

例子：

1
2
3
4
5
UPDATE salary
SET sex =  CASE 
				WHEN sex = 'm' THEN 'f'
                WHEN sex = 'f' THEN 'm'
           END;

作用和IF表达式相当。

第19章 插入数据

数据插入

  • INSERT语句用来插入行到数据库表。插入可以用几种方式使用：
    • 插入完整的行
    • 插入行的一部分
    • 插入多行
    • 插入某些查询的结果

插入完整的行

  • INSERT语句要求指定表名和被插入到新行中的值。如：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    INSERT INTO customers
    VALUES(NULL,
          'Pep E. LaPew',
           '100 Main Street',
           'Los Angeles',
           'CA',
           '90046',
           'USA',
           NULL,
           NULL);

    存储到每个表列中的数据在VALUES子句中给出，对每个列必须提供一个值，各个列必须以在表定义中出现的次序填充。如果某个列没有值，应该使用NULL值（假定表允许对该列指定空值）。**第一列cust_id也为NULL(每次插入一个新行时，该列由MySQL自动增量，NULL值会被MySQL忽略)**。

    虽然这种语法很简单，但并不安全，应该尽量避免使用。上面的SQL语句高度依赖于表中列的定义次序。即使可得到这种次序信息，也不能保证下一次表结构变动后各个列保持完全相同的次序。

  • INSERT语句一般不会产生输出。

  • 一般不要使用没有明确给出列的列表的INSERT语句。使用列的列表能使SQL代码继续发挥作用，即使表结构发生了变化。

  • 不管使用哪种INSERT语法，都必须给出VALUES的正确数目

    • 如果不提供列名，则必须给每个表列提供一个值
    • 如果提供列名，则必须对每个列出的列提供一个值

  • 省略的列必须满足以下某个条件。

    1. 该列定义为允许NULL值。
    2. 在表定义中给出默认值。这表示如果不给出值，将使用默认值。

    如果对表中不允许NULL值且没有默认值的列不给出值，则MySQL将产生一条错误消息，并且相应的行插入不成功。

  • INSERT操作可能很耗时，而且可能降低等待处理的SELECT语句的性能。如果数据检索是最重要的（通常是这样），则你可以通过在INSERT和INTO之间添加关键字LOW_PRIORITY，指示MySQL降低INSERT语句的优先级，如下所示：

    1
    INSERT LOW PRIORITY INTO ...

    这也适用于下一章介绍的UPDATE和DELETE语句。

插入多个行

  • 如果想插入多个行：

    1. 可以使用多条INSERT语句，甚至一次提交它们，每条语句用一个分号结束。

    2. 只要每条INSERT语句中的列名(和次序)相同，可以用单条INSERT语句插入多组值，每组值用一对圆括号括起来，用逗号分隔。如下：

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      INSERT INTO customers(cust_name,
                           cust_address,
                           cust_city,
                           cust_state,
                           cust_zip,
                           cust_country)
      VALUES(
      		'Pep E. LaPew',
          	'100 Main Street',
          	'Los Angeles',
          	'CA',
          	'90046',
          	'USA'
      	),
      	(
          	'M. Martian',
              '42 Galaxy Way',
              'New York',
              'NY',
              '11213',
              'USA'
          );

  • 单条INSERT语句处理多个插入比使用多条INSERT语句快。

插入检索出的数据

  • INSERT还存在另一种形式，可以利用它将一条SELECT语句的结果插入表中。这就是所谓的INSERT SELECT。

    假如你想从另一表中合并客户列表到你的customers表，可以如下进行：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    INSERT INTO customers(cust_id,
                          cust_contact,
                          cust_email,
                          cust_name)
    SELECT cust_id,
    		cust_contact,
    		cust_email,
    		cust_name
    FROM custnew;

  • MySQL甚至不关心SELECT返回的列名。它使用的是列的位置，因此SELECT中的第一列（不管其列名）将用来填充表列中指定的第一个列，第二列将用来填充表列中指定的第二个列，如此等等

  • 如果目标表不存在，插入数据的方式：

    1
    2
    3
    SELECT value1, value2
    INTO table2 # 目标表table2不存在，因此插入的时候自动创建目标表
    FROM table1;

第20章 更新和删除数据

更新数据

  • 为了更新(修改)表中的数据，可使用UPDATE语句。可采用两种方式使用UPDATE：

    1. 更新表中特定行
    2. 更新表中所有行

  • 基本的UPDATE语句由3部分组成

    1. 要更新的表
    2. 列名和它们的新值
    3. 确定要更新行的过滤条件

    例如，客户10005现在有了电子邮件地址，需要更新记录：

    1
    2
    3
    UPDATE customers
    SET cust_email = 'elmer@fudd.com'
    WHERE cust_id = 10005;

    SET命令用来将新值赋给被更新的列。

  • UPDATE语句以WHERE子句结束，它告诉MySQL更新哪一行。没有WHERE子句，MySQL将会更新customers表中所有行。

  • 更新多个列的语法稍有不同：

    1
    2
    3
    4
    UPDATE customers
    SET cust_name = 'The Fudds',
    	cust_email = 'elmer@fudd.com'
    WHERE cust id = 10005;

    更新多个列只需使用单个SET命令，每个“列=值”对之间用逗号分隔。

  • UPDATE语句中可以使用子查询。

  • IGNORE关键字

    如果用UPDATE语句更新多行，并且这些行中的一行或者多行出现一个错误，整个UPDATE操作会被取消。如果希望即使发生错误也继续更新，可使用IGNORE关键字：

    1
    UPDATE IGNORE customers ...

  • 为了删除某个列的值，可设置其为NULL(前提表定义允许NULL值)。如：

    1
    2
    3
    UPDATE customers
    SET cust_email = NULL
    WHERE cust_id = 10005;
  • 用一张表的记录去更新另一张表的记录
    1
    2
    3
    4
    5
    UPDATE A
    SET A.Name = B.Name, 
    	A.Sex = B.Sex
    FROM A,B
    WHERE A.ID = B.ID;

删除数据

  • 为了从一个表中删除数据，使用DELETE语句。可以通过两种方式使用DELETE：

    1. 从表中删除特定的行
    2. 从表中删除所有行

  • DELETE语句以WHERE子句结束，它告诉MySQL删除哪一行。没有WHERE子句，MySQL将会删除表中所有行。

  • DELETE不需要列名或通配符。DELETE删除整行而不是删除列。为了删除指定的列，请使用UPDATE语句。

    1
    2
    DELETE FROM customers
    WHERE cust_id = 10006;

  • DELETE不删除表本身。

  • 更快的删除

    ruu过想删除表中所有行，不要使用DELETE。可使用TRUNCATE TABLE语句，它完成相同的工作，但速度更快（TRUNCATE实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据）。

更新和删除的指导原则

  • 除非确实打算更新和删除每一行，否则一定要带WHERE子句
  • 保证每个表都有主键，从而能通过WHERE子句指定唯一的列。
  • 在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。、
  • 使用强制实施引用完整性的数据库（15章），这样MySQL将不允许删除具有与其他表相关联的数据的行。
  • MySQL没有撤销（undo）按钮。应该非常小心地使用UPDATE和DELETE，否则你会发现自己更新或删除了错误的数据。

第21章 创建和操纵表

创建表

  • 为了用程序创建表，可使用SQL的CREATE TABLE语句。

表创建基础

  • 为利用CREATE TABLE创建表，必须给出下列信息：

    • 新表的名字，在关键字CREATE TABLE之后给出；
    • 表列的名字和定义在圆括号之中，各列之间用逗号分隔；
    • 每列的定义以列名开始，后跟列的数据类型。
    1
    2
    3
    4
    5
    6
    7
    8
    9
    CREATE TABLE customers
    (
    	cust_id			int			NOT NULL AUTO_INCREMENT,
        cust_name		char(50)	NOT NULL,
        cust_address	char(50)	NULL,
        cust_city		char(50)	NULL,
        cust_state		char(5)		NULL,
        PRIMARY KEY (cust_id)
    ) ENGINE=InnoDB;

  • 表的主键可以在创建表时用PRIMARY KEY关键字指定。这里，列cust_id指定作为主键列。整条语句由右圆括号后的分号结束。

  • 语句格式化

    MySQL语句中忽略空格。语句可以在一个长行上输入，也可以分成许多行。它们的作用都相同。对列定义进行恰当的缩进，以便阅读和编辑。

  • 创建新表时，指定的表名必须不存在，否则将出错。如果要防止意外覆盖已有的表，SQL要求首先手工删除该表（请参阅后面的小节），然后再重建它，而不是简单地用创建表语句覆盖它。如果仅想在一个表不存在时创建它，应该在表名后给出IF NOT EXISTS(并不检查已有表的模式是否与打算创建的表模式相匹配，只是查看表名是否存在，并且仅在表名不存在时创建它)。

使用NULL值

  • 每个表列或者是NULL列，或者是NOT NULL列，这种状态在创建时由表的定义规定。

  • NULL为默认设置，如果不指定NOT NULL，则认为指定的是NULL。

  • 进行查询时可通过WHERE XXX IS NULL或WHERE XXX IS NOT NULL进行筛选。

主键再介绍

  • 主键值必须唯一。如果主键使用单个列，则它的值必须唯一；如果使用多个列，则这些列的组合值必须唯一。

  • 为创建由多个列组成的主键，应该以逗号分隔的列表给出各列名。如：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    CREATE TABLE orderitems
    (
        order_num	int				NOT NULL,
        order_item	int				NOT NULL,
        prod_id		char(10)		NOT NULL,
        quantity	int				NOT NULL,
        item_price	decimal(8,2)	NOT NULL,
        PRIMARY KEY (order_num, order_item)
    ) ENGINE = InnoDB;

    订单号（order_num列）和订单物品（order_item列）的组合是唯一的，从而适合作为主键。

  • 主键可以在创建表时定义，或者在创建表之后定义。主键只能使用NOT NULL值的列，因为允许NULL值的列不能作为唯一标识。

使用AUTO_INCREMENT

  • AUTO_INCREMENT告诉MySQL，本列每当增加一行时自动增量。每次执行一个INSERT操作时，MySQL自动对该列增量，赋予一下一个可用的值。这样能使得每个行被分配一个唯一的值，可以作为主键值。

  • 每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（如，通过使它成为主键）。

  • 覆盖AUTO_INCREMENT

    如果一个列被指定为AUTO_INCRE-MENT，则它需要使用特殊的值吗？你可以在INSERT语句中指定一个值，只要它是唯一的（至今尚未使用过）即可，该值将被用来替代自动生成的值，后续的增量将开始使用该手工插入的值。

  • 确定AUTO_INCREMENT值

    让MySQL通过自动增量生成主键的一个缺点是你不知道这些值都是谁。

    考虑这个场景：你正在增加一个新订单，这要求在orders表中创建一行，然后在orderitems表中对订购的每项物品创建一行。order_num在orderitems表中与订单细节一起存储。这显然要求你在插入orders行之后，插入orderitems行之前知道生成的order_num。

    那么，如何在使用AUTO_INCREMENT列时获得这个值呢？可使用last_insert_id()函数获得这个值，如SELECT_last_insert_id()，此语句返回最后一个AUTO_INCREMENT值，然后可以将它用于后续的MySQL语句。

  • 更改自动增量的开始数和增幅(IDENTITY(a,b)`)

    IDENTITY(a, b)：

    • a、b均为正整数
    • a表示开始数，b表示增幅

    就像IDENTITY(2,3)，意思就是该列自动增长，由2开始，每次增加是3

指定默认值

  • 如果在插入行时没有给出值，MySQL允许指定此时使用的默认值。默认值用CREATE TABLE语句的列定义中的DEFAULT关键字指定。如：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    CREATE TABLE orderitems
    (
    	order_num	INT				NOT NULL,
        order_item	INT				NOT NULL,
        prod_id		CHAR(10)		NOT NULL,
        quantity	INT				NOT NULL DEFAULT 1,
        item_price 	DECIMAL(8,2)	NOT NULL,
        PRIMARY KEY (order_num, order_item)
    ) ENGINE=InnoDB;

    此例子中，给该列的描述添加文本DEFAULT 1指示MySQL，在未给出数量的情况下使用数量1。

  • 与大多数DBMS不一样，MySQL不允许使用函数作为默认值，它只支持常量。

  • 许多数据库开发人员使用默认值而不是NULL列，特别是对用于计算或数据分组的列更是如此。

引擎类型

  • 迄今为止使用的CREATE TABLE语句全都以ENGINE=InnoDB语句结束。如果省略ENGINE=语句，则使用默认引擎(MySQL 5.5之前，MyISAM是默认的引擎；5.5之后，InnoDB是默认的引擎)。
  • 几个需要知道的引擎：
    1. CREATE DATABASEInnoDB是一个可靠的事务处理引擎（参见第26章），它不支持全文本搜索(18章)；
    2. MyISAM是一个性能极高的引擎，它支持全文本搜索，不支持事务处理。
    3. MEMORY功能等同于MyISAM，但由于数据存储在内存中，速度很快，故适合于临时表。
  • 引擎可以混用
  • 外键（用于强制实施引用完整性，如第1章所述）不能跨引擎。

示例

1
2
3
4
5
6
7
CREATE TABLE student(
    stuId 		INT 			IDENTITY(1,1) PRIMARY KEY,
    stuName 	VARCHAR(10) 	UNIQUE NOT NULL,
    stuAge 		INT 			CHECK(stuAge BETWEEN 18 AND 100), # 加入检查约束
    stuAddress 	VARCHAR(20) 	DEFAULT 'china', # 指定默认值 
    stuDel varchar(20)
)

更新表

  • 更新表定义，可使用ALTER TABLE语句

  • 为了使用ALTER TABLE更改表结构，必须给出下面的信息：

    • 在ALTER TABLE之后给出要更改的表名（该表必须存在，否则将出错）；
    • 所做更改的列表。

  • 给表添加一个列：

    1
    2
    ALTER TABLE vendors
    ADD vend_phone CHAR(20);

    增加一个列，必须明确其数据类型。

  • 删除表中的列：

    1
    2
    ALTER TABLE vendors
    DROP COLUMN vend_phone;

  • ALTER TABLE的一种常见用途是定义外键

    1
    2
    3
    ALTER TABLE orderitems
    ADD CONSTRAINT fk_orderitems_orders -- CONSTRAINT fk_name 定义外键名，可以不定义
    FOREIGN KEY (order_num) REFERENCES orders (order_num);

    如果对单个表进行多个更改，可以使用单条ALTER TABLE语句，每个更改用逗号分隔。

  • 复杂的表结构更改一般需要手动删除过程：

    1. 用新的列布局创建一个新表
    2. 使用INSERT SELECT语句(19章)从旧表复制数据到新表。如果有必要，可使用转换函数和计算字段。
    3. 检验包含所需数据的新表
    4. 重命名旧表（如果确定，可以删除它）
    5. 用旧表原来的名字重命名新表
    6. 根据需要，重新创建触发器、存储过程、索引和外键

  • 使用ALTER TABLE要极为小心，应该在进行改动前做一个完整的备份（模式和数据的备份）。

删除表

  • 删除表（删除整个表而不是其内容）非常简单，使用DROP TABLE语句即可。如：

    1
    2
    DROP TABLE customers2;

DROP TABLE IF EXISTS table_name;

删除表没有确认，也不能撤销，执行这条语句将永久删除该表。

重命名表

方式一

  • 使用RENAME TABLE语句可以重命名一个表。如：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
      RENAME TABLE customers2 TO customers;

    - 可以**使用单条RENAME TABLE语句**对**多个表**进行重命名，**每个重命名用逗号分隔**。

    ---

    #### 方式二

    ```sql
    ALTER TABLE customers2 RENAME TO customers;

第22章 使用视图

视图

MySQL 5添加了对视图的支持。视图是虚拟的表，只包含使用时动态检索数据的查询，不包含数据。

例：

1
2
3
4
5
SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
		AND orderitems.order_num = orders.order_num
		AND prod_id = 'TNT2';

​ 此查询用来检索订购了某个特定产品的客户。任何需要这个数据的人都必须理解相关表的结构，并且知道如何创建查询和对表进行联结。为了检索其他产品的相同数据，必须修改最后的WHERE子句。假如可以把整个查询包装成一个名为productcustomers的虚拟表，则可以如下轻松地检索出相同的数据：

1
2
3
SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';

这就是视图的作用。productcustomers是一个视图，作为视图，它不包含表中应该有的任何列或数据，它包含的是一个SQL查询。

为什么使用视图

视图的一些常见应用：

  1. 重用SQL语句。
  2. 简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节。
  3. 使用表的组成部分而不是整个表。
  4. 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
  5. 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。在添加或更改这些表中的数据时，视图将返回改变过的数据。

性能问题：因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任一个检索。因此，如果用多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降得很厉害。因此，在部署使用了大量视图的应用前，应该进行测试。

视图的规则和限制

视图创建和使用的一些最常见的规则和限制：

  1. 与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）。
  2. 对于可以创建的视图数目没有限制。
  3. 为了创建视图，必须具有足够的访问权限。这些限制通常由数据库管理人员授予。
  4. 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图。
  5. ORDER BY可以用在视图中，但如果从该视图检索数据的SELECT语句中也含有ORDER BY，那么该视图中的ORDER BY将被覆盖。
  6. 视图不能索引，也不能有关联的触发器或默认值。
  7. 视图可以和表一起使用。例如，编写一条联结表和视图的SELECT语句。

使用视图

视图的创建：

  1. 视图用CREATE VIEW语句来创建。
  2. 使用SHOW CREATE VIEW viewname;来查看创建视图的语句。
  3. 用DROP删除视图，其语法为DROP VIEW viewname;。
  4. 更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACEVIEW。

利用视图简化复杂的联结

视图最常见的应用之一是隐藏复杂的SQL，这通常都会涉及联结：

1
2
3
4
5
CREATE VIEW productcustomers AS
SELECT cust_name, cust_contact, prod_id
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id 
		AND orderitems.order_num = orders.order_num;

  • 这条语句创建了一个名为productcustomers的视图，它联结三个表，以返回已订购了任意产品的所有客户的列表。如果执行SELECT * FROM productcustomers;，将列出订购了任意产品的客户。

  • 为检索订购了产品TNT2的客户，可如下进行：

    1
    2
    3
    SELECT cust_name, cust_contact
    FROM productcustomers
    WHERE prod_id = 'TNT2';
    • 这条语句通过WHERE子句从视图中检索特定数据。在MySQL处理此查询时，它将指定的WHERE子句添加到视图查询中的已有WHERE子句中，以便正确过滤数据。
    • 可以看出，视图极大地简化了复杂SQL的使用。利用视图，可一次性编写基础的SQL，然后根据需要多次使用。

用视图重新格式化检索出的数据

如下SELECT语句在单个组合计算列中返回供应商名和位置：

1
2
3
SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') AS vend_title
FROM vendors
ORDER BY vend_name;

假如经常需要这个格式的结果，不必在每次需要时执行联结，创建一个视图，每次需要时使用它即可：

1
2
3
4
5
6
7
CREATE VIEW vendorlocations AS
SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') AS vend_title
FROM vendors
ORDER BY vend_name;

SELECT *
FROM vendorlocations;

用视图过滤不想要的数据

例如，可以定义customeremaillist视图，它过滤没有电子邮件地址的客户。为此目的，可使用下面的语句：

1
2
3
4
5
6
7
CREATE VIEW customeremaillist AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL;

SELECT *
FROM customeremaillist;

使用视图与计算字段

视图对于简化计算字段的使用特别有用。下面是第10章中介绍的一条SELECT语句。它检索某个特定订单中的物品，计算每种物品的总价格：

1
2
3
4
5
6
SELECT prod_id,
		quantity,
		item_price,
		quantity * item_price AS expanded_price
FROM orderitems
WHERE order_num = 20005;

为其转换为一个视图：

1
2
3
4
5
6
7
8
9
10
CREATE VIEW orderitemsexpanded AS
SELECT prod_id,
		quantity,
		item_price,
		quantity * item_price AS expanded_price
FROM orderitems;

SELECT *
FROM orderitemsexpanded
WHERE order_num = 20005;

更新视图

视图的数据能否更新？答案视情况而定。

通常，视图是可更新的（即，可以对它们使用INSERT、UPDATE和DELETE）。更新一个视图将更新其基表（可以回忆一下，视图本身没有数据）。如果你对视图增加或删除行，实际上是对其基表增加或删除行。

但是，并非所有视图都是可更新的。基本上可以说，如果MySQL不能正确地确定被更新的基数据，则不允许更新（包括插入和删除）。这实际上意味着，如果视图定义中有以下操作，则不能进行视图的更新：

  1. 分组（使用GROUP BY和HAVING）；
  2. 联结；
  3. 子查询；
  4. 并；
  5. 聚集函数（Min()、Count()、Sum()等）；
  6. DISTINCT；
  7. 导出（计算）列。

**看上去好像是一个严重的限制，但实际上不是，因为视图主要用于数据检索(SELECT)**。

视图提供了一种MySQL的SELECT语句层次的封装，可用来简化数据处理以及重新格式化基础数据或保护基础数据。

用户变量和SET语句

用户变量即自定义的变量，我们可以给用户变量分配值，并且可用在任何可以正常使用标量表达式的地方。
必须使用SET或SELECT语句来定义它，然后为它赋一个值，否则变量就只有一个空值。一个客户端定义的变量不能被其它客户端看到或使用。当客户端退出时，该客户端连接的所有变量将自动释放。

用户变量定义

  •   SET @var_name = expr [, @var_name = expr] ...
  
    1
    2
    3

    - ```sql
    	SELECT @var_name := expr [, @var_name = expr] ...

对于SET，可以使用=或:=来赋值，对于SELECT只能使用:=来赋值

用户变量的使用

SET 语句

  1. 把一个值赋给一个变量的标量表达式可以是复合表达式。计算、函数、系统标量以及其他用户变量都是允许的，子查询也是允许的。然后通过SELECT语句可以获取用户变量的值，结果是带有一行的一个表。

    例：

    1
    2
    SET @var1=1, @var2='vartest', @var3=ABS(-2), @var4=(SELECT COUNT(*) FROM mysql.user);
    SELECT @var1, @var2, @var3, @var4;

    结果为：

    @var1 @var2 @var3 @var4
    1 vartest 2 4

  2. 在为一个用户变量赋值的表达式中，也可以指定其它的用户变量。但是MySQL会先确定所有表达式的值，再给变量赋值。

    例：

    1
    2
    3
    4
    5
    6
    7
    8
    SET @varA = 2;
    SET @varA = 3, @varB = @varA;
    SELECT @varB; -- @varB的值为2

    ----------------------------
    SET @varA = 3;
    SET @varB = @varA;
    SELECT @varB; -- @varB的值为3

SELECT 语句

1
SELECT @var1:=1, @var2:='vartest', @var3:=ABS(-2), @var4:=(SELECT COUNT(*) FROM mysql.user);

返回一个表格形式的结果：

@var1:=1 @var2:=’vartest’ @var3:=abs(-2) @var4:=(select count(*) from mysql.user)
1 vartest 2 4 
1
SELECT @var1, @var2, @var3, @var4; -- 效果与SET语句后的SELECT相同

用户变量注意事项

  • 用户变量用在WHERE或HAVING子句中时，必须首先用另一条语句来定义。如下面例子，初次查询不会返回结果，先定义以后再查询才有输出：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    SELECT @H:='localhost', user
    FROM mysql.user
    WHERE Host = @H; -- 因为这时@H的值还是NULL(MySQL先确定所有表达式的值，再给变量赋值)
    ----------------------

    SELECT @H:'localhost';
    SELECT @H:='localhost', user
    FROM mysql.user
    WHERE Host = @H;

  • 用户变量为session级别，当我们关闭客户端或退出登录时用户变量全部消失

  • 用户变量名对大小写不敏感

  • 未定义的变量初始化是NULL

第23章 使用存储过程

存储过程

迄今为止，使用的大多数SQL语句都是针对一个或多个表的单条语句。并非所有操作都那么简单，经常会有一个完整的操作需要多条语句才能完成。例如如下场景：

  1. 为了处理订单，需要核对以保证库存中有相应的物品。
  2. 如果库存有物品，这些物品需要预定以便不将它们再卖给别的人，并且要减少可用的物品数量以反映正确的库存量。
  3. 库存中没有的物品需要订购，这需要与供应商进行某种交互。
  4. 关于哪些商品入库(并可以立即发货)和哪些物品退订，需要通知相应的客户。

执行这些处理需要针对许多表的多条MySQL语句。此外，需要执行的具体语句及其次序也不是固定的，它们可能会（和将）根据哪些物品在库存中哪些不在而变化。那么，应该怎样编写代码？

可以单独编写每条语句，并根据结果，有条件地执行另外的语句。在每次需要这个处理时，都必须做这些工作。可以创建存储过程。存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件，虽然它们的作用不仅限于批处理。

为什么要使用存储过程

  1. 简化复杂操作
  2. 需要执行的步骤越多，出错的可能性就越大。使用存储过程能防止错误，保证数据的一致性。
  3. 简化对变动的管理(解耦)。
  4. 提高性能，使用存储过程比使用单独的SQL语句要快。
  5. 存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。

简单来说，使用存储过程有3个主要好处：简单、安全、高性能。

存储过程同样也存在一些缺点：

  1. 存储过程的编写比基本SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验。
  2. 可能没有创建存储过程的安全访问权限。

使用存储过程

执行存储过程

MySQL称存储过程的执行为调用，因此MySQL执行存储过程的语句为CALL。CALL接受存储过程的名字以及需要传递给它的任意参数

例：

1
2
3
4
# 执行名为productpricing的存储过程，它计算并返回产品的最低、最高和平均价格。
CALL productpricing(@pricelow,
                   @pricehigh,
                   @priceaverage);
  • 存储过程可以显示结果，也可以不显示结果。

创建存储过程

例：一个返回产品平均价格的存储过程

1
2
3
4
5
CREATE PROCEDURE productpricing()
BEGIN 
	SELECT AVG(prod_price) AS priceaverage
	FROM products;
END;
  • 定义存储过程：CREATE PROCEDURE 存储过程名()
  • 如果存储过程接受参数，它们将在()中列出
  • BEGIN和END语句用来限定存储体，存储体本身是一个简单的SELECT语句

MySQL命令行的分隔符：

默认的MySQL语句分隔符为;，如果命令行实用程序要解释存储过程自身内的;字符，会使存储过程中的SQL语句出现语法错误。通过临时更改命令行实用程序的语句分隔符来解决：

1
2
3
4
5
6
7
8
DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN 
	SELECT AVG(prod_price) AS priceaverage
	FROM products;
END //

DELIMITER ;

  • DELIMITER //指明使用//作为新的语句结束分隔符；DELIMITER ;指明使用;作为新的语句结束分隔符

    除\符号外，任何字符都可以用作语句分隔符。

  • 使用这个存储过程：CALL productpricing();

    因为存储过程实际上是一种函数，所以存储过程名后需要有()符号

删除存储过程

存储过程在创建之后，被保存在服务器上以供使用，直至被删除。

1
DROP PROCEDURE productpricing;

  • 请注意没有使用后面的()，只给出存储过程名

  • 如果指定的过程不存在，则DROP PROCEDURE将产生一个错误。当过程存在想删除它时（如果过程不存在也不产生错误）可使用DROP PROCEDURE IF EXISTS。

使用参数

一般，存储过程并不显示结果，而是把结果返回给你指定的变量。

  • 变量：内存中一个特定的位置，用来临时存储数据。
例1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE PROCEDURE productpricing( 
    OUT pl DECIMAL(8, 2),
    OUT ph DECIMAL(8, 2),
    OUT pa DECIMAL(8, 2)
)
BEGIN 
	SELECT MIN(prod_price)
	INTO pl
	FROM products;
	SELECT MAX(prod_price)
	INTO ph
	FROM products;
	SELECT AVG(prod_price)
	INTO pa
	FROM products;
END;

  • 如果不先删除已存在的同名存储过程，则不能再次创建它。

  • 关键字OUT指出相应的参数用来从存储过程传出一个值（返回给调用者）

    MySQL支持**IN(传递给存储过程)、OUT(从存储过程传出)和INOUT(对存储过程传入和传出)**类型的参数。

  • 存储过程用来检索值，然后通过指定INTO关键字保存到相应的变量

  • 不能通过一个参数返回多个行和列，因为记录集是不允许的类型。

为调用此存储过程，必须指定3个变量名：

1
2
3
CALL productpricing(@pricelow, 
                   @pricehigh,
                   @priceaverage);

  • 所有MySQL变量都必须以@开始

  • 在调用时，这条语句并不显示任何数据。它返回变量。

  • 为了显示检索出的产品平均价格，可如下进行：SELECT @priceaverage;

  • 为了获得3个变量的值，可：SELECT @pricehigh, @pricelow, @priceaverage;

例2

这次使用IN和OUT参数。ordertotal接受订单号并返回该订单的合计：

1
2
3
4
5
6
7
8
9
10
CREATE PROCEDURE ordertotal( 
    IN onumber INT,
    OUT ototal DECIMAL(8,2)
)
BEGIN 
	SELECT Sum(item_price*quantity)
	FROM orderitems
	WHERE order_num = onumber
	INTO ototal;
END;

  • 可使用调用语句：CALL ordertotal(20005, @total);

  • 为了显示合计：SELECT @total;

建立智能存储过程

迄今为止使用的所有存储过程基本上都是封装MySQL简单的SELECT语句。虽然它们全都是有效的存储过程例子，但它们所能完成的工作你直接用这些被封装的语句就能完成。只有在存储过程内包含业务规则和智能处理时，它们的威力才真正显现出来。

考虑这个场景。你需要获得与以前一样的订单合计，但需要对合计增加营业税，不过只针对某些顾客（或许是你所在州中那些顾客）。那么，你需要做下面几件事情：

  1. 获得合计
  2. 把营业税有条件地添加到合计
  3. 返回合计（带或不带税）

存储过程的完整工作如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- Name: ordertotal
-- Parameters:	onumber = order number
--				taxable = 0 if not taxable, 1 if taxable
--				ototal = order total variable

CREATE PROCEDURE ordertotal( 
    IN onumber INT,
    IN taxable BOOLEAN,
    OUT ototal DECIMAL(8, 2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN
	-- Declare variable for total
	DECLARE total DECIMAL(8,2);
	-- Declare tax percentage
	DECLARE taxrate INT DEFAULT 6;
	
	-- Get the order total
	SELECT Sum(item_price*quantity)
	FROM orderitems
	WHERE order_num = onumber
	INTO total;
	
	-- Is this taxable?
	IF taxable THEN 
		-- Yes, so add taxrate to the total
		SELECT total+(total*taxrate/100) INTO total
	END IF;
	
	-- And finally, save to out variable
	SELECT total INTO ototal;
END;

  • 注释（前面放置--，#也有同样效果）

  • 用DECLARE语句定义了两个局部变量

    DECLARE要求指定变量名和数据类型，支持可选的默认值(DEFAULT xxx)

  • COMMENT关键字

    它不是必需的，但如果给出，将在SHOW PROCEDURE STATUS的结果中显示。

  • IF语句

    1
    2
    3
    4
    5
    6
    7
    IF search_condition THEN
        statement_list
    [ELSEIF search_condition THEN]
        statement_list ...
    [ELSE
        statement_list]
    END IF

检查存储过程

  • 显示 创建一个存储过程的CREATE语句，使用SHOW CREATE PROCEDURE语句：SHOW CREATE PROCEDURE ordertotal;

  • 为了获得包括何时、由谁创建等详细信息的存储过程列表，使用SHOW PROCEDURESTATUS。

    为限制其输出，可使用LIKE指定一个过滤模式，例如：

    SHOW PROCEDURE STATUS LIKE ’ordertotal';

information_schema 数据库

information_schema这个数据库中保存了MySQL服务器所有数据库的信息，如数据库名，数据库的表，表栏的数据类型与访问权限等。
再简单点，这台MySQL服务器上，到底有哪些数据库、各个数据库有哪些表，每张表的字段类型是什么，各个数据库要什么权限才能访问等等信息都保存在information_schema里面。

  • SCHEMATA表

    • 列SCHEMA_NAME：所有数据库的名字

  • TABLES表

    • 列TABLE_SCHEMA：所有数据库的名字
    • 列TABLE_NAME：所有数据库表的名字
    • 列TABLE_ROWS：表的行数

  • COLUMNS表

    • 列COLUMN_NAME：所有数据库表的列的名字

第24章 使用游标

游标

有时，需要在检索出来的行中前进或后退一行或多行，这就是使用游标的原因。

游标（cursor）是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。不像多数DBMS, MySQL游标只能用于存储过程（和函数）。

使用游标

使用游标涉及几个明确的步骤：

  1. 在能够使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的SELECT语句。
  2. 一旦声明后，必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来。
  3. 对于填有数据的游标，根据需要取出（检索）各行。
  4. 在结束游标使用时，必须关闭游标。

在声明游标后，可根据需要频繁地打开和关闭游标。在游标打开后，可根据需要频繁地执行取操作。

创建游标

DECLARE命名游标，并定义相应的SELECT语句，根据需要带WHERE和其他子句。

1
2
3
4
5
6
CREATE PROCEDURE processorders()
BEGIN
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
END;

存储过程处理完成后，游标就消失（因为它局限于存储过程）。

打开和关闭游标

打开：

1
OPEN cursor_name;

在处理OPEN语句时执行查询，存储检索出的数据以供浏览和滚动。游标处理完成后，应当使用如下语句关闭游标：

1
CLOSE cursor_name;

CLOSE释放游标使用的所有内部内存和资源，因此在每个游标不再需要时都应该关闭。

隐含关闭：如果你不明确关闭游标，MySQL将会在到达END语句时自动关闭它。

使用游标数据

在一个游标被打开后，可以使用FETCH语句分别访问它的每一行。FETCH指定检索什么数据（所需的列），检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针，使下一条FETCH语句检索下一行（不重复读取同一行）

例1：从游标中检索单个行。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE PROCEDURE processorders()
BEGIN
	DECLARE o INT;
	
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	
	OPEN ordernumbers;
	
	FETCH ordernumbers INTO o;
	
	CLOSE ordernumbers;
END;

其中FETCH用来检索当前行的order_num列（将自动从第一行开始）到一个名为o的局部声明的变量中。对检索出的数据不做任何处理。

例2：循环检索数据，从第一行到最后一行。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE PROCEDURE processorders()
BEGIN
	DELCARE done BOOLEAN DEFAULT 0;
	DECLARE o INT;
	
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
	
	OPEN ordernumbers;
	
	REPEAT
		FETCH ordernumbers INTO o;
	UNTIL done END REPEAT;
	
	CLOSE ordernumbers;
END;

这个例子中的FETCH是在REPEAT内，因此它反复执行直到done为真（由UNTIL done END REPEAT;规定）。

为使它起作用，用一个DEFAULT 0（假，不结束）定义变量done。那么，done怎样才能在结束时被设置为真呢？

答案是用以下语句：

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

这条语句定义了一个CONTINUE HANDLER，它是在条件出现时被执行的代码。这里，它指出当SQLSTATE ‘02000’出现时，SET done=1。SQLSTATE’02000’是一个未找到条件，当REPEAT由于没有更多的行供循环而不能继续时，出现这个条件。

  • 如果一切正常，你可以在循环内放入任意需要的处理（在FETCH语句之后，循环结束之前）。
  • 除这里使用的REPEAT语句外，MySQL还支持循环语句，它可用来重复执行代码，直到使用LEAVE语句手动退出为止。通常REPEAT语句的语法使它更适合于对游标进行循环。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
CREATE PROCEDURE processorders()
BEGIN
	DELCARE done BOOLEAN DEFAULT 0;
	DECLARE o INT;
	DECLARE t DECIMAL(8,2);
	
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
	
	CREATE TABLE IF NOT EXISTS ordertotals
		(order_num INT, total DECIMAL(8,2));
	
	OPEN ordernumbers;
	
	REPEAT
		FETCH ordernumbers INTO o;
		
		CALL ordertotal(o, 1, t); -- 执行另一个存储过程
		
		INSERT INTO ordertotals(order_num, total)
		VALUES(o, t);
	UNTIL done END REPEAT;
	
	CLOSE ordernumbers;
END;

此存储过程不返回数据，但它能够创建和填充另一个表。

结果集

主要用于交互式应用

MySQL游标只能用于存储过程（和函数）

定义要使用的SELECT语句

把数据实际检索出来

在结束游标使用时，必须关闭游标

游标用DECLARE语句创建

OPEN CURSOR语句

存储检索出的数据以供浏览和滚动

CLOSE释放游标使用的所有内部内存和资源，因此在每个游标不再需要时都应该关闭。

果你不明确关闭游标，MySQL将会在到达END语句时自动关闭它。

FETCH指定检索什么数据（所需的列）

向前移动游标中的内部行指针，使下一条FETCH语句检索下一行（不重复读取同一行）

自动从第一行开始

REPEAT

UNTIL done END REPEAT

[插图]

CONTINUE HANDLER

MySQL还支持循环语句，它可用来重复执行代码，直到使用LEAVE语句手动退出为止

REPEAT语句的语法使它更适合于对游标进行循环。

第25章 使用触发器

触发器

MySQL语句在需要时被执行，存储过程也是如此。但是，如果你想要某条语句（或某些语句）在事件发生时自动执行，怎么办呢？例如：

  1. 每当增加一个顾客到某个数据库表时，都检查其电话号码格式是否正确，州的缩写是否为大写；
  2. 每当订购一个产品时，都从库存数量中减去订购的数量；
  3. 无论何时删除一行，都在某个存档表中保留一个副本。

这些例子的共同之处在于它们都需要在某个表发生更改时自动处理，触发器就是提供这样功能的工具。触发器是MySQL响应DELETE/INSERT/UPDATE语句而自动执行的一条（或位于BEGIN和END语句之间的一组语句）MySQL语句。其他MySQL语句不支持触发器。

创建触发器

在创建触发器时，需要给出4条信息：

  1. 唯一的触发器名；
  2. 触发器关联的表；
  3. 触发器应该响应的活动（DELETE、INSERT或UPDATE）；
  4. 触发器何时执行（处理之前或之后）

保持每个数据库的触发器名唯一

​ 在MySQL 5中，触发器名必须在每个表中唯一，但不是在每个数据库中唯一。这在其他每个数据库触发器名必须唯一的DBMS中是不允许的，而且以后的MySQL版本很可能会使命名规则更为严格。因此，现在最好是在数据库范围内使用唯一的触发器名。

触发器用CREATE TRIGGER语句创建：

1
2
3
4
CREATE TRIGGER newproduct 
AFTER INSERT ON products
FOR EACH ROW 
SELECT 'Product added';
  • 只有表才支持触发器，视图和临时表都不支持触发器。
  • 触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器（每条INSERT、UPDATE和DELETE的之前和之后）。
  • 如果BEFORE触发器失败，则MySQL将不执行请求的操作。此外，如果BEFORE触发器或语句本身失败，MySQL将不执行AFTER触发器（如果有的话）。

删除触发器

1
DROP TRIGGER newproduct;

触发器不能更新或覆盖，为了修改一个触发器，必须先删除它，然后再重新创建。

使用触发器

INSERT触发器

INSERT触发器在INSERT语句执行之前或之后执行。需要知道以下几点：

  1. 在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行；
  2. 在BEFORE INSERT触发器中，NEW中的值也可以被更新（允许更改被插入的值）；
  3. 对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值。

AUTO_INCREMENT列具有MySQL自动赋予的值。第21章建议了几种确定新生成值的方法，但下面是一种更好的方法：

1
2
3
4
CREATE TRIGGER neworder
AFTER INSERT ON orders
FOR EACH ROW 
SELECT NEW.order_num; -- 引用一个名为NEW的虚拟表，访问被插入的行
  • 在插入一个新订单到orders表时，MySQL生成一个新订单号并保存到order_num中。触发器从NEW. order_num取得这个值并返回它。此触发器必须按照AFTER INSERT执行，因为在BEFORE INSERT语句执行之前，新order_num还没有生成。对于orders的每次插入使用这个触发器将总是返回新的订单号。

通常，将BEFORE用于数据验证和净化（目的是保证插入表中的数据确实是需要的数据，对于UPDATE触发器也适用）。

DELETE触发器

  • 在DELETE触发器代码内，可以引用一个名为OLD的虚拟表，访问被删除的行
  • OLD中的值全都是只读的，不能更新

下面的例子演示使用OLD保存将要被删除的行到一个存档表中：

1
2
3
4
5
6
7
CREATE TRIGGER deleteorder 
BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
	INSERT INTO archive_orders(order_num, order_date, cust_id)
	VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
END;

  • 在任意订单被删除前将执行此触发器。

  • 使用BEFORE DELETE触发器的优点（相对于AFTER DELETE触发器来说）为，如果由于某种原因，订单不能存档，DELETE本身将被放弃。

  • 使用BEGIN END语句标记触发器体的好处是触发器能容纳多条SQL语句（在BEGIN END块中一条挨着一条）。

UPDATE触发器

  • 在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问以前（UPDATE语句前）的值，引用一个名为NEW的虚拟表访问新更新的值；
  • 在BEFORE UPDATE触发器中，NEW中的值可能也被更新（允许更改将要用于UPDATE语句中的值）；
  • OLD中的值全都是只读的，不能更新。

下面的例子保证州名缩写总是大写:

1
2
3
4
CREATE TRIGGER updatevendor 
BEFORE UPDATE ON vendors
FOR EACH ROW
SET NEW.vend_state = Upper(NEW.vend_state);

显然，任何数据净化都需要在UPDATE语句之前进行。

关于触发器的进一步介绍

  1. 与其他DBMS相比，MySQL 5中支持的触发器相当初级。未来的MySQL版本中有一些改进和增强触发器支持的计划。
  2. 创建触发器可能需要特殊的安全访问权限，但是，触发器的执行是自动的。如果INSERT、UPDATE或DELETE语句能够执行，则相关的触发器也能执行。
  3. 应该用触发器来保证数据的一致性（大小写、格式等）。在触发器中执行这种类型的处理的优点是它总是进行这种处理，而且是透明地进行，与客户机应用无关。
  4. 触发器的一种非常有意义的使用是创建审计跟踪。使用触发器，把更改（如果需要，甚至还有之前和之后的状态）记录到另一个表非常容易。
  5. 遗憾的是，MySQL触发器中不支持CALL语句。这表示不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内。

第26章 管理事务处理

事务处理（transaction processing）可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。

事务处理是一种机制，用来管理必须成批执行的MySQL操作，以保证数据库不包含不完整的操作结果

发生错误，则进行回退（撤销）以恢复数据库到某个已知且安全的状态。

❑ 事务（transaction）指一组SQL语句；❑ 回退（rollback）指撤销指定SQL语句的过程；❑ 提交（commit）指将未存储的SQL语句结果写入数据库表；❑ 保留点（savepoint）指事务处理中设置的临时占位符（place-holder），你可以对它发布回退（与回退整个事务处理不同）。

关键在于将SQL语句组分解为逻辑块

标识事务的开始

ROLLBACK命令用来回退（撤销）MySQL语句

用一条ROLLBACK语句回退STARTTRANSACTION之后的所有语句

ROLLBACK只能在一个事务处理内使用（在执行一条START TRANSACTION命令之后）。

哪些语句可以回退？ 事务处理用来管理INSERT、UPDATE和DELETE语句

不能回退CREATE或DROP操作

在事务处理块中，提交不会隐含地进行。为进行明确的提交，使用COMMIT语句

因为涉及更新两个数据库表orders和orderitems，所以使用事务处理块来保证订单不被部分删除。最后的COMMIT语句仅在不出错时写出更改。

更复杂的事务处理可能需要部分提交或回退。

必须能在事务处理块中合适的位置放置占位符

占位符称为保留点

SAVEPOINT

保留点在事务处理完成（执行一条ROLLBACK或COMMIT）后自动释放。自MySQL 5以来，也可以用RELEASE SAVEPOINT明确地释放保留点。

任何时候你执行一条MySQL语句，该语句实际上都是针对表执行的，而且所做的更改立即生效。为指示MySQL不自动提交更改，需要使用以下语句：

设置autocommit为0（假）指示MySQL不自动提交更改（直到autocommit被设置为真为止）

针对每个连接而不是服务器

第27章 全球化和本地化

❑ 字符集为字母和符号的集合；❑ 编码为某个字符集成员的内部表示；❑ 校对为规定字符如何比较的指令。

字符集

校对

第28章 安全管理

MySQL创建一个名为root的用户账号，它对整个MySQL服务器具有完全的控制

不过在现实世界的日常工作中，决不能使用root

mysql数据库有一个名为user的表，它包含所有用户账号。user表有一个名为user的列，它存储用户登录名

IDENTIFIED BY指定的口令为纯文本，MySQL将在保存到user表之前对其进行加密。为了作为散列值指定口令，使用IDENTIFIED BY PASSWORD

CREATE USER是最清楚和最简单的句子。此外，也可以通过直接插入行到user表来增加用户，不过为安全起见，一般不建议这样做

删除用户账号和所有相关的账号权限

MySQL 5以前，DROP USER只能用来删除用户账号，不能删除相关的权限

新创建的用户账号没有访问权限

MySQL的权限用用户名和主机名结合定义

此GRANT允许用户

使用SELECT

GRANT的反操作为REVOKE，用它来撤销特定的权限

被撤销的访问权限必须存在，否则会出错。

在使用GRANT和REVOKE时，用户账号必须存在，但对所涉及的对象没有这个要求

副作用是，当某个数据库或表被删除时（用DROP语句），相关的访问权限仍然存在。而且，如果将来重新创建该数据库或表，这些权限仍然起作用

列出各权限并用逗号分隔，将多条GRANT语句串在一起

更改用户口令，可使用SET PASSWORD语句

新口令必须传递到Password()函数进行加密

在不指定用户名时，SET PASSWORD更新当前登录用户的口令。

第29章 数据库维护

MySQL数据库是基于磁盘的文件

由于这些文件总是处于打开和使用状态，普通的文件副本备份不一定总是有效。

转储所有数据库内容到某个外部文件

从一个数据库复制所有数据

BACKUP TABLE

SELECT INTO OUTFILE

数据可以用RESTORE TABLE来复原。

首先刷新未写数据 为了保证所有数据被写到磁盘（包括索引数据），可能需要在进行备份前使用FLUSH TABLES语句。

ANALYZE TABLE，用来检查表键是否正确

CHECK TABLE用来针对许多问题对表进行检查

CHANGED检查自最后一次检查以来改动过的表。EXTENDED执行最彻底的检查，FAST只检查未正常关闭的表，MEDIUM检查所有被删除的链接并进行键检验，QUICK只进行快速扫描

下面是几个重要的mysqld命令行选项：❑ –help显示帮助——一个选项列表；❑ –safe-mode装载减去某些最佳配置的服务器；❑ –verbose显示全文本消息（为获得更详细的帮助消息与–help联合使用）；❑ –version显示版本信息然后退出。

错误日志。它包含启动和关闭问题以及任意关键错误的细节

位于data目录中

查询日志。它记录所有MySQL活动，在诊断问题时非常有用

位于data目录中

二进制日志。它记录更新过数据（或者可能更新过数据）的所有语句

位于data目录内

缓慢查询日志。顾名思义，此日志记录执行缓慢的任何查询

在确定数据库何处需要优化很有用

位于data目录中

使用日志时，可用FLUSH LOGS语句来刷新和重新开始所有日志文件。

第30章 改善性能

  • MySQL和所有的DBMS一样，具有特定的硬件建议。在学习和研究MySQL时，使用任何旧的计算机作为服务器都可以。但对用于生产的服务器来说，应该坚持遵循这些硬件建议。

  • MySQL是用一系列的默认设置预先配置的，这些设置开始通常是很好的。但过一段时间后你可能需要调整内存分配、缓冲区大小等(为查看当前设置，可使用SHOW VARIABLES;和SHOW STATUS;)。

  • MySQL是一个多用户多线程的DBMS。如果遇到显著的性能不良，可使用SHOW PROCESSLIST;显示所有活动进程（以及它们的线程ID和执行时间）。你还可以用KILL命令终结某个特定的进程（使用这个命令需要作为管理员登录）

  • 一般来说，存储过程执行得比一条一条地执行其中的各条语句要快。

  • 除非需要每一个列，否则不要用SELECT *

  • 有的操作（包括INSERT）支持一个可选的DELAYED关键字，如果使用它，将把控制立即返回给调用程序，并且一旦有可能就实际执行该操作

  • 导入数据时，应该关闭自动提交。你可能还想删除索引(包括FULLTEXT索引)，然后在导入完成后重建它们。

  • 必须索引数据库表以改善数据检索的性能。

  • 如果有一系列复杂的OR条件，通过使用多条SELECT语句和连接它们的UNION语句，能看到极大的性能改进。

  • 索引改善数据检索的性能，但损害数据插入、删除和更新的性能。如果有一些表，它们收集数据且不经常被搜索，则在有必要之前不要索引它们。

  • LIKE很慢，一般来说，最好是使用FULLTEXT而不是LIKE。

  • 数据库是不断变化的实体，理想的优化和配置也会改变。

SQL如何提高查询效率

1. 参数是子查询(14章)时，使用 EXISTS 还是 IN

在大多数时候，[NOT] IN和[NOT] EXISTS返回的结果是相同的。

当主表比从表大时，IN查询的效率较高；当从表比主表大时，EXISTS查询的效率较高。

  • IN是先执行子查询，得到一个结果集，将结果集代入外层谓词条件执行主查询，子查询只需要执行一次
  • EXISTS是先从主查询中取得一条数据，再代入到子查询中，执行一次子查询，判断子查询是否能返回结果，主查询有多少条数据，子查询就要执行多少次。

例：从Class_A表中查出同时存在于Class_B表中的员工。

1
2
3
4
5
6
7
8
9
10
11
12
# 慢
SELECT *
FROM Class_A
WHERE id IN (SELECT id 
             FROM Class_B);

# 快
SELECT *
FROM Class_A
WHERE EXISTS (SELECT * 
              FROM Class_B 
              WHERE A.id = B.id);

使用EXISTS时更快的原因：

  1. 如果连接列(上例中为id)建立了索引，那么查询Class_B时不用查实际的表，只需要查索引就可以了。

  2. 使用EXISTS，只要查到一行数据满足条件就会终止查询，而使用IN时会将表全扫描一遍。

    IN的参数是子查询时，数据库首先执行子查询，然后将结果存储在一张临时的工作表里（内联视图），然后扫描整个视图。使用 EXISTS 的话，数据库不会生成临时的工作表。

要想改善 IN 的性能，除了使用 EXISTS ，**还可以使用连接(JOIN)**：

1
2
3
4
# 使用连接代替IN
SELECT A.id, A.name
FROM Class_A AS A INNER JOIN Class_B AS B
ON A.id = B.id;

因为没有了子查询，数据库也不会产生中间表。很难JOIN和EXISTS相比谁更好，但如果没有索引，可能EXISTS会更胜一筹。

2. 避免排序

我们在查询的时候，即使没想进行排序，但是在数据库内部仍然频繁地进行着暗中的排序。会进行排序的代表性运算：

  • GROUP BY子句
  • ORDER BY子句
  • 聚合函数(SUM()、COUNT()、AVG()、MAX()、MIN())
  • DISTINCT
  • 集合运算符(UNION、INTERSECT、EXCEPT)
  • 窗口函数(RANK、ROW_NUMBER等)
使用UNION ALL代替UNION
1
2
3
4
5
SELECT *
FROM Class_A
UNION 
SELECT * 
FROM Class_B

这个操作会进行排序，如果不在乎结果中是否有重复数据，可以使用UNION ALL代替UNION。

使用EXISTS代替DISTINCT

为了排除重复数据，DISTINCT也会进行排序。如果需要对两张表的连接结果进行去重，可以考虑用EXISTS代替DISTINCT，以避免排序。

例：从商品表Items中找出同时存在于销售记录表SalesHistory中的商品。

1
2
3
SELECT I.item
FROM Item AS I INNER JOIN SalesHistory AS SH
ON I.item_no = SH.item_no;

因为是一对多连接，所以item_no列中会出现重复数据，为了排除重复数据，需要使用DISTINCT。但是**使用DISTINCT会排序，更好的做法是使用EXISTS**：

1
2
3
4
5
6
7
8
9
SELECT DISTINCT I.item_no
FROM Item AS I INNER JOIN SalesHistory AS SH
ON I. item_no = SH. item_no;

SELECT item_no
FROM Items AS I
WHERE EXISTS (SELECT * 
              FROM SalesHistory SH 
              WHERE I.item_no = SH.item_no);
在极值函数(MAX/MIN)中使用索引

使用MAX()和MIN()这两个函数都会进行排序。但如果参数字段上建有索引，则只需要扫描索引，不需要扫描整张表。

1
2
3
# 以Item表为例
SELECT MAX(item_no)
FROM Items;

这种方法并不是去掉了排序的过程，而是**优化了排序前的查找速度(只需要扫描索引，不需要扫描整张表)**，从而减弱了排序对整体性能的影响。

能写在WHERE子句里的条件不要写在HAVING子句里

  • 聚合后使用HAVING子句过滤

    1
    2
    3
    4
    SELECT sale_date, SUM(quantity)
    FROM SalesHistory
    GROUP BY sale_date
    HAVING sale_date = '2007-10-01';

  • 聚合前使用WHERE子句过滤

    1
    2
    3
    4
    SELECT sale_date, SUM(quantity)
    FROM SalesHistory
    WHERE sale_date = '2007-10-01'
    GROUP BY sale_date;

虽然结果是一样的，但是从性能上来看，使用WHERE语句效率更高。原因通常有两个：

  1. 在使用GROUP BY子句聚合时会进行排序，如果事先通过WHERE 子句筛选出一部分行，就能够减轻排序的负担。
  2. 在WHERE 子句的条件里可以使用索引。而HAVING子句是针对聚合后生成的视图进行筛选的，但是很多时候聚合后的视图都没有继承原表的索引结构。

3. 真正把索引利用起来

列举一些导致索引失效的作法：

  1. 索引字段上进行计算，会进行全表扫描。

    1
    2
    3
    4
    5
    6
    7
    8
    SELECT *
    FROM someTable
    WHERE col_1 * 1.1 > 100;

    # 优化方法：把运算的表达式放到查询条件的右侧
    SELECT *
    FROM someTable
    WHERE col_1 > 100/1.1;

    实际上，只要索引列上使用函数的时候，索引就会失效。

  2. 使用IS NULL谓词

    通常索引字段不存在NULL，所以指定IS NULL和IS NOT NULL的话会使得索引无法使用，进而导致查询性能低下。

    1
    2
    3
    SELECT *
    FROM someTable
    WHERE col_1 IS NULL;

  3. 使用否定形式

    • <>/!=

    • NOT IN

  4. 使用OR

    在col_1和col_2上分别建立了不同的索引，或者建立了(col_1,col_2)这样的联合索引时，如果使用OR连接条件，要么用不到索引，要么用到了但是效率比AND要差很多。

    1
    2
    3
    4
    SELECT *
    FROM someTable
    WHERE col_1 > 100
    OR col_2 = 'abc';

  5. 使用联合索引时，列的顺序错误

    假设存在这样顺序的一个联合索引(col_1, col_2, col_3)。联合索引中的第一列col_1必须写在查询条件的开头，而且索引中列的顺序不能颠倒。如果无法保证查询条件里列的顺序与索引一致，可以考虑将联合索引拆分为多个索引。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    # 能够利用索引的操作
    SELECT * 
    FROM SomeTable 
    WHERE col_1 = 10 
    	AND col_2 = 100 
    	AND col_3 = 500;

    SELECT * 
    FROM SomeTable 
    WHERE col_1 = 10 AND col_2 = 100;

    -------
    # 不能利用索引的操作
    SELECT * 
    FROM SomeTable 
    WHERE col_1 = 10 AND col_3 = 500;

    SELECT * 
    FROM SomeTable 
    WHERE col_2 = 100 AND col_3 = 500;

    SELECT * 
    FROM SomeTable 
    WHERE col_2 = 100 AND col_1 = 10;

  6. 使用LIKE进行后方一致或中间一致的匹配

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    # 索引失效 %为通配符，表示任何字符出现任意次数
    SELECT * 
    FROM SomeTable 
    WHERE col_1 LIKE '%a'; # 后方一致

    SELECT * 
    FROM SomeTable 
    WHERE col_1 LIKE '%a%'; # 中间一致

    -----------
    # 索引有效
    SELECT * 
    FROM SomeTable 
    WHERE col_1 LIKE 'a%'; # 开头一致

  7. 进行隐式的类型转换

    1
    2
    3
    4
    5
    6
    7
    8
    # 例如 col_1列的类型为 字符
    SELECT * 
    FROM SomeTable 
    WHERE col_1 = 10; # 隐式的类型转换，不仅会增加额外的性能开销，还会导致索引不可用

    SELECT * 
    FROM SomeTable 
    WHERE col_1 = CAST(10 AS CHAR(2)); # 把数字10转换为长度为2的字符

减少中间表

在 SQL 中，子查询的结果会被看成一张新表，这张新表与原始表一样，可以通过代码进行操作。这种高度的相似性使得 SQL 编程具有非常强的灵活性，但是如果不加限制地大量使用中间表，会导致查询性能下降。

频繁使用中间表会带来两个问题：

  1. 展开数据需要耗费内存资源
  2. 原始表中的索引不容易使用到（特别是聚合时）

因此，尽量减少中间表的使用也是提升性能的一个重要方法。

灵活使用HAVING子句 可以减少中间表的使用：

​ 对聚合结果指定筛选条件时，使用 HAVING 子句是基本原则。不习惯使用 HAVING 子句的数据库工程师可能会倾向于像下面这样先生成一张中间表，然后在 WHERE 子句中指定筛选条件：

1
2
3
4
5
6
7
8
9
10
11
SELECT *
FROM (SELECT sale_date, MAX(quantity) AS max_qty 
      FROM SalesHistory
      GROUP BY sale_date) TMP # 没用的中间表
WHERE max_qty >= 10;

# 使用HAVING子句的写法
SELECT sale_date, MAX(quantity) AS max_qty 
FROM SalesHistory
GROUP BY sale_date 
HAVING max_qty >= 10;

HAVING子句和聚合操作(GROUP BY)是同时执行的，所以比起生成中间表后再执行的WHERE子句，效率更高，代码更简洁。

附录C MySQL语句的语法

方括号中

是可选的

更新已存在表的模式

将事务处理写到数据库

在一个或多个列上创建索引

附录D MySQL数据类型

字符串数据类型

  • 定长串：接受长度固定的字符串，其长度是在创建表时指定的。
    • 定长列不允许多于指定的字符数目，它们分配的存储空间和指定的一样多。
    • CHAR属于定长串类型
  • 变长串：存储可变长度的文本。
    • 有些变长数据类型具有最大的定长；有些则是完全变长的。
    • TEXT属于变长串类型

为什么要使用定长数据类型？

​ 因为性能，MySQL处理定长列要快得多。此外，不允许对变长列（或一个列的可变部分）进行索引。

数据类型 说明
CHAR(size) 最多设置255个字符的定长串。
长度必须在创建时指定，否则MySQL默认为CHAR(1)
VARCHAR(size) 最多设置255个字符的变长串。
如果值的长度 > 255，会转为TEXT类型。
TEXT 最大长度为65535($2^{16}$)的变长串。
TINYTEXT 最大长度为255个字符的变长串，类型与TEXT相同
MEDIUMTEXT 最大长度为$2^{14}$的变长串
LONGTEXT 最大长度为$2^{22}$的变长串
ENUM(x, y, z, etc.) 枚举，最多$2^{16}$个变长串

  • 不管使用何种形式的串数据类型，串值都必须括在引号内（通常单引号更好）。

  • 如果数值是计算（求和、平均等）中使用的数值，则应该存储在数值数据类型列中。如果作为字符串（可能只包含数字）使用，则应该保存在串数据类型列中。

数值数据类型

所有数值类型(除BIT和BOOLEAN外)都可以有符号或无符号。有符号数值列可以存储正/负数值，无符号数值列只能存储正数。默认情况为有符号，但如果你知道自己不需要存储负值，可以使用 UNSIGNED关键字。

数据类型 说明
BIT 位，1~64位
BOOLEAN/BOOL 取值为0/1
INT(size) 整数值
（$-2^{31}\sim2^{31}-1$，UNSIGNED时，为$0\sim2^{32}-1$)
TINYINT(size) 整数值
($-2^7\sim2^7-1$，UNSIGNED时，为$0\sim2^8-1$)
SMALLINT(size) 整数值
($-2^{15}\sim2^{15}-1$，UNSIGNED时，为$0\sim 2^{16}-1$)
MEDIUMINT(size) 整数值
($-2^{23}\sim 2^{23}-1$，UNSIGNED时，为$0\sim 2^{24}-1$)
BIGINT(size) 整数值
($-2^{63}\sim 2^{63}-1$，UNSIGNED时，为$0\sim 2^{64}-1$)
FLOAT(size, d) 单精度浮点数，存在精度损失
size规定最大位数(小数点左侧和右侧)，d规定小数点右侧的最大位数。
DOUBLE(size, d) 双精度浮点数，存在精度损失
size规定最大位数(小数点左侧和右侧)，d规定小数点右侧的最大位数。
DECIMAL(size, d) 以字符串的形式保存数值，不存在精度损失。
适用于存储货币等金融数据。
  • MySQL中没有专门存储货币的数据类型，一般情况下使用**DECIMAL(8, 2)**。

日期和时间数据类型

数据类型 说明
DATE 日期，格式为YYYY-MM-DD
支持的范围：1000-01-01~9999-12-31
TIME 时间，格式为HH:MM:SS
DATETIME DATE和TIME的组合
支持的范围：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
TIMESTAMP 功能同DATETIME，格式更包容，但范围较小
支持的范围：1970-01-01 00:00:01 UTC ~ 2038-01-09 03:14:07 UTC
YEAR 用2位数字表示，范围是70(1970)69(2069)；
用4位数字表示，范围是19012155

即便DATETIME和TIMESTAMP返回相同的格式，它们的工作方式很不同。在INSERT或UPDATE查询中，TIMESTAMP自动把自身设置为当前的日期和时间。TIMESTAMP也接受不同的格式，比如YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD

二进制数据类型

二进制数据类型可存储任何数据（甚至包括二进制信息），如图像、多媒体、字处理文档等。

数据类型 说明
BLOB 用于 BLOBs (Binary Large OBjects，二进制大型对象)。存放最多$2^{16}$B的数据
TINYBLOB 最大长度为$2^8-1$B
MEDIUMBLOB 最大长度为$2^{24}$B
LONGBLOB 最大长度为$2^{32}$B