1 了解SQL

获得MySQL Query Browser 与MySQL Administrator一样，MySQL Query Browser不作为核心MySQL安装的成分。也必须从http://dev.mysql.com/downloads/下载它（可得到用于Linux、Mac OS X和Windows的版本，其源代码也可以下载）。

3 使用MySQL

3.2 选择数据库

在你最初连接到MySQL时，没有任何数据库打开供你使用。在你能执行任意数据库操作前，需要选择一个数据库。为此，可使用USE关键字。


mysql> USE crashcourse;
Database changed

3.3 了解数据库和表

不知道可使用的数据库名？SHOW DATABASES; 可返回可用数据库的一个列表。


mysql> SHOW DATABASES;
+-------------+
| Database    |
+-------------+
| databse1    |
| databse2    |
+-------------+

获得一个数据库内的表的列表？SHOW TABLES; 返回当前选择的数据库内可用表的列表。


mysql> SHOW TABLES;
+-----------------------+
| Tables_in_testdata    |
+-----------------------+
| table001              |
| table002              |
+-----------------------+

SHOW也可以用来显示表列


mysql> SHOW COLUMNS FROM table001;
+--------+---------+---------+---------+---------+---------------+
| Field  | Type    | Null    | Key     | Default | Extra         |
+--------+---------+---------+---------+---------+---------------+
| id     | int(11) | NO      | PRI     | NULL    |auto_increment |
+--------+---------+---------+---------+---------+---------------+
mysql> DESCRIBE table001;
+--------+---------+---------+---------+---------+---------------+
| Field  | Type    | Null    | Key     | Default | Extra         |
+--------+---------+---------+---------+---------+---------------+
| id     | int(11) | NO      | PRI     | NULL    |auto_increment |
+--------+---------+---------+---------+---------+---------------+

SHOW COLUMNS要求给出一个表名，它对每个字段返回一行，行中包含字段名、数据类型、是否允许NULL、键信息、默认值以及其他信息（如auto_increment）。

SHOW STATUS;用于显示广泛的服务器状态信息。

SHOW CREATE DATABASE 和 SHOW CREATE TABLE，分别用来显示创建特定数据库或表的MySQL语句；


MariaDB [testdatabase]> show create database testdatabase;
+--------------+-----------------------------------------------------------------------------------------------------+
| Database     | Create Database                                                                                     |
+--------------+-----------------------------------------------------------------------------------------------------+
| testdatabase | CREATE DATABASE `testdatabase` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ |
+--------------+-----------------------------------------------------------------------------------------------------+
MariaDB [testdatabase]> show create table tasks;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                              
                                                                                                                                    
                                                                                                                       |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tasks | CREATE TABLE `tasks` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(50) NOT NULL,
  `Result` tinyint(1) DEFAULT 0,
  `Fault` int(11) DEFAULT 0,
  `Correct` int(11) DEFAULT 0,
  `Description` varchar(100) DEFAULT NULL,
  `Parent` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

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


MariaDB [testdatabase]> SHOW GRANTS;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED BY PASSWORD '*DE0A26A6E94640F86D1505CF278C382693A1D00A' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------+

SHOW ERRORS 和 SHOW WARNINGS，用来显示服务器错误或警告消息。

4 检索数据

4.1 SELECT语句

SQL语句是由简单的英语单词构成的。这些单词称为关键字，每个SQL语句都是由一个或多个关键字构成的。大概，最经常使用的SQL语句就是SELECT语句了。它的用途是从一个或多个表中检索信息。

为了使用SELECT检索表数据，必须至少给出两条信息——想选择什么，以及从什么地方选择。

4.2 检索单个列


MariaDB [mysql]> SELECT user FROM user;
+-------+
| user  |
+-------+
| masha |
| root  |
| masha |
| root  |
+-------+

如果未经指定，返回的数据是未排序的。

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

4.3 检索多个列


MariaDB [mysql]> SELECT user, host FROM user;
+-------+-----------+
| user  | host      |
+-------+-----------+
| masha | %         |
| root  | %         |
| masha | localhost |
| root  | localhost |
+-------+-----------+

4.4 检索所有列


MariaDB [testdatabase]> SELECT * FROM tasks;
+----+-----------------+--------+-------+---------+-------------+--------+
| Id | Name            | Result | Fault | Correct | Description | Parent |
+----+-----------------+--------+-------+---------+-------------+--------+
|  1 | db_create_test  |      1 |     0 |       0 | NULL        | NULL   |
|  2 | db_create_test1 |      1 |     0 |       0 |             |        |
+----+-----------------+--------+-------+---------+-------------+--------+

使用通配符 一般，除非你确实需要表中的每个列，否则最好别使用*通配符。虽然使用通配符可能会使你自己省事，不用明确列出所需列，但检索不需要的列通常会降低检索和应用程序的性能。

检索未知列 使用通配符有一个大优点。由于不明确指定列名（因为星号检索每个列），所以能检索出名字未知的列。

4.5 检索不同的行


MariaDB [mysql]> SELECT DISTINCT user FROM user;
+-------+
| user  |
+-------+
| masha |
| root  |
+-------+

使用DISTINCT关键字指示MySQL只返回不同的值。

不能部分使用DISTINCT DISTINCT关键字应用于所有列而不仅是前置它的列。如果给出SELECT DISTINCT vend_id, prod_price，除非指定的两个列都不同，否则所有行都将被检索出来。

4.6 限制结果


MariaDB [testdatabase]> SELECT Id FROM tasks;
+----+
| Id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.000 sec)

MariaDB [testdatabase]> SELECT Id FROM tasks LIMIT 1;
+----+
| Id |
+----+
|  1 |
+----+
1 row in set (0.000 sec)

MariaDB [testdatabase]> SELECT Id FROM tasks LIMIT 1,1;
+----+
| Id |
+----+
|  2 |
+----+
1 row in set (0.000 sec)

MariaDB [testdatabase]> SELECT Id FROM tasks LIMIT 1,5;
+----+
| Id |
+----+
|  2 |
+----+
1 row in set (0.000 sec)

MariaDB [testdatabase]> SELECT Id FROM tasks LIMIT 5 OFFSET 1;
+----+
| Id |
+----+
|  2 |
+----+
1 row in set (0.000 sec)

带一个值的LIMIT总是从第一行开始，给出的数为返回的行数。

带两个值的LIMIT可以指定从行号为第一个值的位置开始。

行0 检索出来的第一行为行0而不是行1。因此，LIMIT 1, 1将检索出第二行而不是第一行。

在行数不够时 LIMIT中指定要检索的行数为检索的最大行数。如果没有足够的行（例如，给出LIMIT 1, 5，但只有2行），MySQL将只返回它能返回的那么多行。

由于LIMIT的这个原因，MySQL 5支持LIMIT的另一种替代语法。LIMIT 4 OFFSET 3意为从行3开始取4行，就像LIMIT 3, 4一样。

4.7 使用完全限定的表名

使用完全限定的名字来引用列（同时使用表名和列字）。


MariaDB [testdatabase]> SELECT tasks.Name FROM tasks;
+-----------------+
| Name            |
+-----------------+
| db_create_test  |
| db_create_test1 |
+-----------------+
2 rows in set (0.000 sec)

表名也可以是完全限定的


MariaDB [testdatabase]> SELECT tasks.Name FROM testdatabase.tasks;
+-----------------+
| Name            |
+-----------------+
| db_create_test  |
| db_create_test1 |
+-----------------+
2 rows in set (0.000 sec)

5 排序检索数据

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

5.1 排序数据

子句（clause） SQL语句由子句构成，有些子句是必需的，而有的是可选的。一个子句通常由一个关键字和所提供的数据组成。


MariaDB [testdatabase]> SELECT Name FROM tasks ORDER BY Id;
+-----------------+
| Name            |
+-----------------+
| db_create_test  |
| db_create_test1 |
+-----------------+
2 rows in set (0.000 sec)

MariaDB [testdatabase]> SELECT Name FROM tasks ORDER BY Id DESC;
+-----------------+
| Name            |
+-----------------+
| db_create_test1 |
| db_create_test  |
+-----------------+
2 rows in set (0.000 sec)

ORDER BY子句取一个或多个列的名字，据此对输出进行排序。

通过非选择列进行排序 通常，ORDER BY子句中使用的列将是为显示所选择的列。但是，实际上并不一定要这样，用非检索的列排序数据是完全合法的。

5.2 按多个列排序

为了按多个列排序，只要指定列名，列名之间用逗号分开即可（就像选择多个列时所做的那样）。


MariaDB [testdatabase]> SELECT id,fault,correct FROM tasks ORDER BY fault, id;
+----+-------+---------+
| id | fault | correct |
+----+-------+---------+
|  2 |     0 |       1 |
|  1 |     1 |       0 |
|  3 |     1 |       0 |
+----+-------+---------+
3 rows in set (0.010 sec)

重要的是理解在按多个列排序时，排序完全按所规定的顺序进行。换句话说，对于上述例子中的输出，仅在多个行具有相同的fault值时才对产品按id进行排序。如果fault列中所有的值都是唯一的，则不会按id排序。

5.3 指定排序方向


MariaDB [testdatabase]> SELECT id,fault,correct FROM tasks ORDER BY fault, id;
+----+-------+---------+
| id | fault | correct |
+----+-------+---------+
|  2 |     0 |       1 |
|  1 |     1 |       0 |
|  3 |     1 |       0 |
+----+-------+---------+
3 rows in set (0.000 sec)

MariaDB [testdatabase]> SELECT id,fault,correct FROM tasks ORDER BY fault, id DESC;
+----+-------+---------+
| id | fault | correct |
+----+-------+---------+
|  2 |     0 |       1 |
|  3 |     1 |       0 |
|  1 |     1 |       0 |
+----+-------+---------+
3 rows in set (0.000 sec)

DESC关键字只应用到直接位于其前面的列名。在上例中，只对id列指定DESC，对fault列不指定。因此，id列以降序排序，而fault列仍然按标准的升序排序。

在多个列上降序排序 如果想在多个列上进行降序排序，必须对每个列指定DESC关键字。

区分大小写和排序顺序 在对文本性的数据进行排序时，A与a相同吗？a位于B之前还是位于Z之后？这些问题不是理论问题，其答案取决于数据库如何设置。

在字典（dictionary）排序顺序中，A被视为与a相同，这是MySQL（和大多数数据库管理系统）的默认行为。但是，许多数据库管理员能够在需要时改变这种行为（如果你的数据库包含大量外语字符，可能必须这样做）。
这里，关键的问题是，如果确实需要改变这种排序顺序，用简单的ORDER BY子句做不到。你必须请求数据库管理员的帮助。

ORDER BY子句的位置 在给出ORDER BY子句时，应该保证它位于FROM子句之后。如果使用LIMIT，它必须位于ORDER BY之后。使用子句的次序不对将产生错误消息。

6 过滤数据

6.1 使用WHERE子句


MariaDB [testdatabase]> SELECT id, name FROM tasks WHERE fault=1;
+----+-----------------+
| id | name            |
+----+-----------------+
|  1 | db_create_test  |
|  3 | db_create_test2 |
+----+-----------------+
2 rows in set (0.000 sec)

SQL过滤与应用过滤 数据也可以在应用层过滤。为此目的，SQL的SELECT语句为客户机应用检索出超过实际所需的数据，然后客户机代码对返回数据进行循环，以提取出需要的行。

在客户机上对数据库返回的数据再进行一次处理会极大地影响应用的性能和可伸缩性，此外服务器也不得不通过网络发送更多的数据从而浪费网络带宽。因此数据库对此进行了优化。

6.2 WHERE子句操作符

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

6.2.1 检查单个值


MariaDB [testdatabase]> SELECT id, name FROM tasks WHERE fault=1;
+----+-----------------+
| id | name            |
+----+-----------------+
|  1 | db_create_test  |
|  3 | db_create_test2 |
+----+-----------------+
2 rows in set (0.000 sec)

MariaDB [testdatabase]> SELECT id, name FROM tasks WHERE fault<1;
+----+-----------------+
| id | name            |
+----+-----------------+
|  2 | db_create_test1 |
+----+-----------------+
1 row in set (0.000 sec)

MariaDB [testdatabase]> SELECT id, name FROM tasks WHERE fault<=1;
+----+-----------------+
| id | name            |
+----+-----------------+
|  1 | db_create_test  |
|  2 | db_create_test1 |
|  3 | db_create_test2 |
+----+-----------------+
3 rows in set (0.000 sec)

6.2.2 不匹配检查


MariaDB [testdatabase]> SELECT id, name FROM tasks WHERE fault<>0;
+----+-----------------+
| id | name            |
+----+-----------------+
|  1 | db_create_test  |
|  3 | db_create_test2 |
+----+-----------------+
2 rows in set (0.000 sec)

MariaDB [testdatabase]> SELECT id, name FROM tasks WHERE fault!=0;
+----+-----------------+
| id | name            |
+----+-----------------+
|  1 | db_create_test  |
|  3 | db_create_test2 |
+----+-----------------+
2 rows in set (0.000 sec)

6.2.3 范围值检查


MariaDB [testdatabase]> SELECT id, name FROM tasks WHERE fault BETWEEN 1 AND 0;
Empty set (0.000 sec)

MariaDB [testdatabase]> SELECT id, name FROM tasks WHERE fault BETWEEN 0 AND 1;
+----+-----------------+
| id | name            |
+----+-----------------+
|  1 | db_create_test  |
|  2 | db_create_test1 |
|  3 | db_create_test2 |
+----+-----------------+
3 rows in set (0.000 sec)

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

6.2.4 空值检查

在创建表时，表设计人员可以指定其中的列是否可以不包含值。在一个列不包含值时，称其为包含空值NULL。

NULL 无值（no value），它与字段包含0、空字符串或仅仅包含空格不同。


MariaDB [testdatabase]> SELECT name, description FROM tasks WHERE description IS NULL;
+-----------------+-------------+
| name            | description |
+-----------------+-------------+
| db_create_test  | NULL        |
| db_create_test2 | NULL        |
+-----------------+-------------+
2 rows in set (0.000 sec)

NULL与不匹配： NULL具有特殊含义，使用WHERE筛选或筛除具有特定值(不是NULL)的行时，数据库不知道它们是否匹配，所以不返回带NULL的行。

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


MariaDB [testdatabase]> SELECT * FROM tasks;
+----+-----------------+--------+-------+---------+-------------+--------+
| Id | Name            | Result | Fault | Correct | Description | Parent |
+----+-----------------+--------+-------+---------+-------------+--------+
|  1 | db_create_test  |      1 |     1 |       0 | NULL        |        |
|  2 | db_create_test1 |      1 |     0 |       1 |             | NULL   |
|  3 | db_create_test2 |      0 |     1 |       0 | NULL        | NULL   |
+----+-----------------+--------+-------+---------+-------------+--------+
3 rows in set (0.000 sec)

MariaDB [testdatabase]> SELECT parent FROM tasks WHERE description is not NULL;
+--------+
| parent |
+--------+
| NULL   |
+--------+
1 row in set (0.000 sec)

MariaDB [testdatabase]> SELECT parent FROM tasks WHERE description is NULL;
+--------+
| parent |
+--------+
|        |
| NULL   |
+--------+
2 rows in set (0.001 sec)

7 数据过滤

7.1 组合WHERE子句

为了进行更强的过滤控制，MySQL允许给出多个WHERE子句。这些子句可以两种方式使用：以AND子句的方式或OR子句的方式使用。

操作符（operator） 用来联结或改变WHERE子句中的子句的关键字。也称为逻辑操作符（logical operator）。

7.1.1 AND操作符


MariaDB [testdatabase]> SELECT * FROM tasks WHERE description is NULL AND parent is NULL;
+----+-----------------+--------+-------+---------+-------------+--------+
| Id | Name            | Result | Fault | Correct | Description | Parent |
+----+-----------------+--------+-------+---------+-------------+--------+
|  3 | db_create_test2 |      0 |     1 |       0 | NULL        | NULL   |
+----+-----------------+--------+-------+---------+-------------+--------+
1 row in set (0.000 sec)

AND 用在WHERE子句中的关键字，用来指示检索满足所有给定条件的行。可以添加多个过滤条件，每添加一条就要使用一个AND。

7.1.3 计算次序


MariaDB [testdatabase]> SELECT * FROM tasks;
+----+-----------------+--------+-------+---------+-------------+--------+
| Id | Name            | Result | Fault | Correct | Description | Parent |
+----+-----------------+--------+-------+---------+-------------+--------+
|  1 | db_create_test  |      1 |     1 |       0 | NULL        |        |
|  2 | db_create_test1 |      1 |     0 |       1 |             | NULL   |
|  3 | db_create_test2 |      0 |     1 |       0 | NULL        | NULL   |
+----+-----------------+--------+-------+---------+-------------+--------+
3 rows in set (0.000 sec)

MariaDB [testdatabase]> SELECT * FROM tasks WHERE id=1 OR id=3 AND description IS NOT NULL;
+----+----------------+--------+-------+---------+-------------+--------+
| Id | Name           | Result | Fault | Correct | Description | Parent |
+----+----------------+--------+-------+---------+-------------+--------+
|  1 | db_create_test |      1 |     1 |       0 | NULL        |        |
+----+----------------+--------+-------+---------+-------------+--------+
1 row in set (0.000 sec)

上面的结果，AND指定了description不是NULL，但输出的description是NULL。如果先计算OR，应该得到id=1和id=3两行，然后计算AND，description非NULL，两行都不满足。这是因为SQL（像多数语言一样）在处理OR操作符前，优先处理AND操作符。

为了使运算按照预期，我们可以使用括号来分组操作符。


MariaDB [testdatabase]> SELECT * FROM tasks;
+----+-----------------+--------+-------+---------+-------------+--------+
| Id | Name            | Result | Fault | Correct | Description | Parent |
+----+-----------------+--------+-------+---------+-------------+--------+
|  1 | db_create_test  |      1 |     1 |       0 | NULL        |        |
|  2 | db_create_test1 |      1 |     0 |       1 |             | NULL   |
|  3 | db_create_test2 |      0 |     1 |       0 | NULL        | NULL   |
+----+-----------------+--------+-------+---------+-------------+--------+
3 rows in set (0.000 sec)

MariaDB [testdatabase]> SELECT * FROM tasks WHERE (id=1 OR id=3) AND description IS NOT NULL;
Empty set (0.000 sec)

在WHERE子句中使用圆括号 任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确地分组操作符。不要过分依赖默认计算次序，即使它确实是你想要的东西也是如此。使用圆括号没有什么坏处，它能消除歧义。

7.2 IN操作符


MariaDB [testdatabase]> SELECT * FROM tasks WHERE id IN (1,3);
+----+-----------------+--------+-------+---------+-------------+--------+
| Id | Name            | Result | Fault | Correct | Description | Parent |
+----+-----------------+--------+-------+---------+-------------+--------+
|  1 | db_create_test  |      1 |     1 |       0 | NULL        |        |
|  3 | db_create_test2 |      0 |     1 |       0 | NULL        | NULL   |
+----+-----------------+--------+-------+---------+-------------+--------+
2 rows in set (0.000 sec)

IN： WHERE子句中用来指定要匹配值的清单的关键字，功能与OR相当。优点:

在使用长的合法选项清单时，IN操作符的语法更清楚且更直观；
在使用IN时，计算的次序更容易管理（因为使用的操作符更少）；
IN操作符一般比OR操作符清单执行更快；
可以包含其他SELECT语句，使得WHERE子句的建立更加灵活。

7.3 NOT操作符

NOT WHERE子句中用来否定后跟条件的关键字。


MariaDB [testdatabase]> SELECT * FROM tasks;
+----+-----------------+--------+-------+---------+-------------+--------+
| Id | Name            | Result | Fault | Correct | Description | Parent |
+----+-----------------+--------+-------+---------+-------------+--------+
|  1 | db_create_test  |      1 |     1 |       0 | NULL        |        |
|  2 | db_create_test1 |      1 |     0 |       1 |             | NULL   |
|  3 | db_create_test2 |      0 |     1 |       0 | NULL        | NULL   |
+----+-----------------+--------+-------+---------+-------------+--------+
3 rows in set (0.000 sec)

MariaDB [testdatabase]> SELECT * FROM tasks WHERE description IS NOT NULL;
+----+-----------------+--------+-------+---------+-------------+--------+
| Id | Name            | Result | Fault | Correct | Description | Parent |
+----+-----------------+--------+-------+---------+-------------+--------+
|  2 | db_create_test1 |      1 |     0 |       1 |             | NULL   |
+----+-----------------+--------+-------+---------+-------------+--------+
1 row in set (0.000 sec)

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

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

8 用通配符进行过滤

8.1 LIKE操作符

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

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

8.1.1 百分号(%)通配符


MariaDB [testdatabase]> SELECT * FROM tasks;
+----+-----------------+--------+-------+---------+-------------+--------+
| Id | Name            | Result | Fault | Correct | Description | Parent |
+----+-----------------+--------+-------+---------+-------------+--------+
|  1 | db_create_test  |      1 |     1 |       0 | NULL        |        |
|  2 | db_create_test1 |      1 |     0 |       1 |             | NULL   |
|  3 | db_create_test2 |      0 |     1 |       0 | NULL        | NULL   |
+----+-----------------+--------+-------+---------+-------------+--------+
3 rows in set (0.000 sec)

MariaDB [testdatabase]> SELECT id, name FROM tasks WHERE name LIKE "%1";
+----+-----------------+
| id | name            |
+----+-----------------+
|  2 | db_create_test1 |
+----+-----------------+
1 row in set (0.000 sec)

此例子使用了搜索模式'%1'。在执行这条子句时，将检索任意以1结尾的词。%告诉MySQL接受1之前的任意字符，不管它有多少字符。

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

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

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


MariaDB [testdatabase]> SELECT * FROM tasks;
+----+-----------------+--------+-------+---------+-------------+--------+
| Id | Name            | Result | Fault | Correct | Description | Parent |
+----+-----------------+--------+-------+---------+-------------+--------+
|  1 | db_create_test  |      1 |     1 |       0 | NULL        |        |
|  2 | db_create_test1 |      1 |     0 |       1 |             | NULL   |
|  3 | db_create_test2 |      0 |     1 |       0 | NULL        | NULL   |
+----+-----------------+--------+-------+---------+-------------+--------+
3 rows in set (0.000 sec)

MariaDB [testdatabase]> SELECT id, name FROM tasks WHERE description LIKE "%";
+----+-----------------+
| id | name            |
+----+-----------------+
|  2 | db_create_test1 |
+----+-----------------+
1 row in set (0.000 sec)

8.1.2 下划线(_)通配符

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

与%能匹配0个字符不一样，_总是匹配一个字符，不能多也不能少。


MariaDB [testdatabase]> SELECT * FROM tasks;
+----+-----------------+--------+-------+---------+-------------+--------+
| Id | Name            | Result | Fault | Correct | Description | Parent |
+----+-----------------+--------+-------+---------+-------------+--------+
|  1 | db_create_test  |      1 |     1 |       0 | NULL        |        |
|  2 | db_create_test1 |      1 |     0 |       1 |             | NULL   |
|  3 | db_create_test2 |      0 |     1 |       0 | NULL        | NULL   |
+----+-----------------+--------+-------+---------+-------------+--------+
3 rows in set (0.000 sec)

MariaDB [testdatabase]> SELECT id, name FROM tasks WHERE name LIKE "%test_";
+----+-----------------+
| id | name            |
+----+-----------------+
|  2 | db_create_test1 |
|  3 | db_create_test2 |
+----+-----------------+
2 rows in set (0.001 sec)

8.2 使用通配符的技巧

正如所见，MySQL的通配符很有用。但这种功能是有代价的：通配符搜索的处理一般要比前面讨论的其他搜索所花时间更长。这里给出一些使用通配符要记住的技巧。

不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。
仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。

9 用正则表达式进行搜索

9.2 使用MySQL正则表达式

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

9.2.1 基本字符匹配


MariaDB [testdatabase]> SELECT * FROM tasks;
+----+-----------------+--------+-------+---------+-------------+--------+
| Id | Name            | Result | Fault | Correct | Description | Parent |
+----+-----------------+--------+-------+---------+-------------+--------+
|  1 | db_create_test  |      1 |     1 |       0 | NULL        |        |
|  2 | db_create_test1 |      1 |     0 |       1 |             | NULL   |
|  3 | db_create_test2 |      0 |     1 |       0 | NULL        | NULL   |
+----+-----------------+--------+-------+---------+-------------+--------+
3 rows in set (0.000 sec)

MariaDB [testdatabase]> SELECT name FROM tasks WHERE name REGEXP 'test';
+-----------------+
| name            |
+-----------------+
| db_create_test  |
| db_create_test1 |
| db_create_test2 |
+-----------------+
3 rows in set (0.027 sec)

MariaDB [testdatabase]> SELECT name FROM tasks WHERE name REGEXP 'test.';
+-----------------+
| name            |
+-----------------+
| db_create_test1 |
| db_create_test2 |
+-----------------+
2 rows in set (0.000 sec)

以上的功能可以由LIKE实现，正则表达式没有带来太多好处（可能还会降低性能）。


MariaDB [testdatabase]> SELECT * FROM tasks;
+----+-----------------+--------+-------+---------+-------------+--------+
| Id | Name            | Result | Fault | Correct | Description | Parent |
+----+-----------------+--------+-------+---------+-------------+--------+
|  1 | db_create_test  |      1 |     1 |       0 | NULL        |        |
|  2 | db_create_test1 |      1 |     0 |       1 |             | NULL   |
|  3 | db_create_test2 |      0 |     1 |       0 | NULL        | NULL   |
+----+-----------------+--------+-------+---------+-------------+--------+
3 rows in set (0.000 sec)

MariaDB [testdatabase]> SELECT name FROM tasks WHERE name REGEXP 'test';
+-----------------+
| name            |
+-----------------+
| db_create_test  |
| db_create_test1 |
| db_create_test2 |
+-----------------+
3 rows in set (0.000 sec)

MariaDB [testdatabase]> SELECT name FROM tasks WHERE name LIKE 'test';
Empty set (0.000 sec)

LIKE和REGEXP LIKE匹配整个列。如果被匹配的文本在列值中出现，LIKE将不会找到它，相应的行也不被返回（除非使用通配符）。而REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP将会找到它，相应的行将被返回。这是一个非常重要的差别。

REGEXP使用^和$定位符后也可以匹配整行。

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

9.2.2 进行OR匹配

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


MariaDB [testdatabase]> SELECT * FROM tasks;
+----+-----------------+--------+-------+---------+-------------+--------+
| Id | Name            | Result | Fault | Correct | Description | Parent |
+----+-----------------+--------+-------+---------+-------------+--------+
|  1 | db_create_test  |      1 |     1 |       0 | NULL        |        |
|  2 | db_create_test1 |      1 |     0 |       1 |             | NULL   |
|  3 | db_create_test2 |      0 |     1 |       0 | NULL        | NULL   |
+----+-----------------+--------+-------+---------+-------------+--------+
3 rows in set (0.000 sec)

MariaDB [testdatabase]> SELECT name FROM tasks WHERE name REGEXP 'test1|test2';
+-----------------+
| name            |
+-----------------+
| db_create_test1 |
| db_create_test2 |
+-----------------+
2 rows in set (0.000 sec)

使用 | 从功能上类似于在SELECT语句中使用OR语句，多个OR条件可并入单个正则表达式。

两个以上的OR条件 可以给出两个以上的OR条件。例如，'1000 | 2000 | 3000'将匹配1000或2000或3000。

9.2.3 匹配几个字符之一


MariaDB [testdatabase]> SELECT * FROM tasks;
+----+-----------------+--------+-------+---------+-------------+--------+
| Id | Name            | Result | Fault | Correct | Description | Parent |
+----+-----------------+--------+-------+---------+-------------+--------+
|  1 | db_create_test  |      1 |     1 |       0 | NULL        |        |
|  2 | db_create_test1 |      1 |     0 |       1 |             | NULL   |
|  3 | db_create_test2 |      0 |     1 |       0 | NULL        | NULL   |
+----+-----------------+--------+-------+---------+-------------+--------+
3 rows in set (0.000 sec)

MariaDB [testdatabase]> SELECT name FROM tasks WHERE name REGEXP 'test[12]';
+-----------------+
| name            |
+-----------------+
| db_create_test1 |
| db_create_test2 |
+-----------------+
2 rows in set (0.000 sec)

正如所见，[]是另一种形式的OR语句。事实上，正则表达式test[12]为test[1|2]的缩写，也可以使用后者。但是，需要用[]来定义OR语句查找什么。


MariaDB [testdatabase]> SELECT * FROM tasks;
+----+-----------------+--------+-------+---------+-------------+--------+
| Id | Name            | Result | Fault | Correct | Description | Parent |
+----+-----------------+--------+-------+---------+-------------+--------+
|  1 | db_create_test  |      1 |     1 |       0 | NULL        |        |
|  2 | db_create_test1 |      1 |     0 |       1 |             | NULL   |
|  3 | db_create_test2 |      0 |     1 |       0 | NULL        | NULL   |
|  4 | fake_case_1     |      1 |     0 |       0 |             |        |
+----+-----------------+--------+-------+---------+-------------+--------+
4 rows in set (0.001 sec)

MariaDB [testdatabase]> SELECT name FROM tasks WHERE name REGEXP 'test2|1';
+-----------------+
| name            |
+-----------------+
| db_create_test1 |
| db_create_test2 |
| fake_case_1     |
+-----------------+
3 rows in set (0.001 sec)

这并不是期望的输出。两个要求的行被检索出来，但还检索出了另外1行。之所以这样是由于MySQL假定你的意思是'1'或'test2'。

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

9.2.4 匹配范围

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


MariaDB [testdatabase]> SELECT * FROM tasks;
+----+-----------------+--------+-------+---------+-------------+--------+
| Id | Name            | Result | Fault | Correct | Description | Parent |
+----+-----------------+--------+-------+---------+-------------+--------+
|  1 | db_create_test  |      1 |     1 |       0 | NULL        |        |
|  2 | db_create_test1 |      1 |     0 |       1 |             | NULL   |
|  3 | db_create_test2 |      0 |     1 |       0 | NULL        | NULL   |
|  4 | fake_case_1     |      1 |     0 |       0 |             |        |
+----+-----------------+--------+-------+---------+-------------+--------+
4 rows in set (0.000 sec)

MariaDB [testdatabase]> SELECT name FROM tasks WHERE name REGEXP 'test[1-2]';
+-----------------+
| name            |
+-----------------+
| db_create_test1 |
| db_create_test2 |
+-----------------+
2 rows in set (0.000 sec)

9.2.5 匹配特殊字符

点(.)匹配任意字符，因此每个行都被检索出来。为了匹配特殊字符，必须用\\为前导。\\-表示查找-，\\.表示查找.。这种处理就是所谓的转义（escaping），正则表达式内具有特殊意义的所有字符都必须以这种方式转义。这包括.、|、[]以及迄今为止使用过的其他特殊字符。

空白元字符

元符	说明
\\f	换页
\\n	换行
\\r	回车
\\t	制表
\\v	纵向制表

匹配\ 为了匹配反斜杠（\）字符本身，需要使用\\。

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

9.2.6 匹配字符类

存在找出你自己经常使用的数字、所有字母字符或所有数字字母字符等的匹配。为更方便工作，可以使用预定义的字符集称为字符类（character class）。

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


MariaDB [testdatabase]> SELECT * FROM tasks;
+----+-----------------+--------+-------+---------+-------------+--------+
| Id | Name            | Result | Fault | Correct | Description | Parent |
+----+-----------------+--------+-------+---------+-------------+--------+
|  1 | db_create_test  |      1 |     1 |       0 | NULL        |        |
|  2 | db_create_test1 |      1 |     0 |       1 |             | NULL   |
|  3 | db_create_test2 |      0 |     1 |       0 | NULL        | NULL   |
|  4 | fake_case_1     |      1 |     0 |       0 |             |        |
|  5 | create_test12   |      1 |     2 |       0 | NULL        | NULL   |
+----+-----------------+--------+-------+---------+-------------+--------+
5 rows in set (0.001 sec)

MariaDB [testdatabase]> SELECT name FROM tasks WHERE name REGEXP "test[[:digit:]]*";
+-----------------+
| name            |
+-----------------+
| db_create_test  |
| db_create_test1 |
| db_create_test2 |
| create_test12   |
+-----------------+
4 rows in set (0.001 sec)

MariaDB [testdatabase]> SELECT name FROM tasks WHERE name REGEXP "test[[:digit:]]+";
+-----------------+
| name            |
+-----------------+
| db_create_test1 |
| db_create_test2 |
| create_test12   |
+-----------------+
3 rows in set (0.000 sec)

MariaDB [testdatabase]> SELECT name FROM tasks WHERE name REGEXP "test[[:digit:]]?";
+-----------------+
| name            |
+-----------------+
| db_create_test  |
| db_create_test1 |
| db_create_test2 |
| create_test12   |
+-----------------+
4 rows in set (0.001 sec)

9.2.7 匹配多个实例

重复元字符

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


MariaDB [testdatabase]> SELECT * FROM tasks;
+----+-----------------+--------+-------+---------+-------------+--------+
| Id | Name            | Result | Fault | Correct | Description | Parent |
+----+-----------------+--------+-------+---------+-------------+--------+
|  1 | db_create_test  |      1 |     1 |       0 | NULL        |        |
|  2 | db_create_test1 |      1 |     0 |       1 |             | NULL   |
|  3 | db_create_test2 |      0 |     1 |       0 | NULL        | NULL   |
|  4 | fake_case_1     |      1 |     0 |       0 |             |        |
|  5 | create_test12   |      1 |     2 |       0 | NULL        | NULL   |
+----+-----------------+--------+-------+---------+-------------+--------+
5 rows in set (0.001 sec)

MariaDB [testdatabase]> SELECT name FROM tasks WHERE name REGEXP "test[a-z0-9]*";
+-----------------+
| name            |
+-----------------+
| db_create_test  |
| db_create_test1 |
| db_create_test2 |
| create_test12   |
+-----------------+
4 rows in set (0.000 sec)

MariaDB [testdatabase]> SELECT name FROM tasks WHERE name REGEXP "test[a-z0-9]+";
+-----------------+
| name            |
+-----------------+
| db_create_test1 |
| db_create_test2 |
| create_test12   |
+-----------------+
3 rows in set (0.001 sec)

MariaDB [testdatabase]> SELECT name FROM tasks WHERE name REGEXP "test[a-z0-9]?";
+-----------------+
| name            |
+-----------------+
| db_create_test  |
| db_create_test1 |
| db_create_test2 |
| create_test12   |
+-----------------+
4 rows in set (0.001 sec)

MariaDB [testdatabase]> SELECT name FROM tasks WHERE name REGEXP "test[a-z0-9]{1}";
+-----------------+
| name            |
+-----------------+
| db_create_test1 |
| db_create_test2 |
| create_test12   |
+-----------------+
3 rows in set (0.001 sec)

MariaDB [testdatabase]> SELECT name FROM tasks WHERE name REGEXP "test[a-z0-9]{1,}";
+-----------------+
| name            |
+-----------------+
| db_create_test1 |
| db_create_test2 |
| create_test12   |
+-----------------+
3 rows in set (0.001 sec)

MariaDB [testdatabase]> SELECT name FROM tasks WHERE name REGEXP "test[a-z0-9]{2,3}";
+---------------+
| name          |
+---------------+
| create_test12 |
+---------------+
1 row in set (0.001 sec)

9.2.8 定位符

为了匹配特定位置的文本，需要使用定位符。

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


MariaDB [testdatabase]> SELECT * FROM tasks;
+----+-----------------+--------+-------+---------+-------------+--------+
| Id | Name            | Result | Fault | Correct | Description | Parent |
+----+-----------------+--------+-------+---------+-------------+--------+
|  1 | db_create_test  |      1 |     1 |       0 | NULL        |        |
|  2 | db_create_test1 |      1 |     0 |       1 |             | NULL   |
|  3 | db_create_test2 |      0 |     1 |       0 | NULL        | NULL   |
|  4 | fake_case_1     |      1 |     0 |       0 |             |        |
|  5 | create_test12   |      1 |     2 |       0 | NULL        | NULL   |
+----+-----------------+--------+-------+---------+-------------+--------+
5 rows in set (0.000 sec)

MariaDB [testdatabase]> SELECT name FROM tasks WHERE name REGEXP "^[a-d]{2}";
+-----------------+
| name            |
+-----------------+
| db_create_test  |
| db_create_test1 |
| db_create_test2 |
+-----------------+
3 rows in set (0.001 sec)

MariaDB [testdatabase]> SELECT name FROM tasks WHERE name REGEXP "[[:digit:]_]{2}$";
+---------------+
| name          |
+---------------+
| fake_case_1   |
| create_test12 |
+---------------+
2 rows in set (0.001 sec)

^的双重用途 ^有两种用法。在集合中（用[和]定义，也就是^出现在方括号里面的时候，表示否定），用它来否定该集合，否则，用来指串的开始处。

使REGEXP起类似LIKE的作用 利用定位符，通过用^开始每个表达式，用$结束每个表达式，可以使REGEXP的作用与LIKE一样。

简单的正则表达式测试 可以在不使用数据库表的情况下用SELECT来测试正则表达式。REGEXP检查总是返回0（没有匹配）或1（匹配）。可以用带文字串的REGEXP来测试表达式，并试验它们。相应的语法如SELECT 'hello' REGEXP '[0-9]';这个例子显然将返回0（因为文本hello中没有数字）。

10 创建计算字段

10.1 计算字段

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

客户机与服务器的格式 可在SQL语句内完成的许多转换和格式化工作都可以直接在客户机应用程序内完成。但一般来说，在数据库服务器上完成这些操作比在客户机中完成要快得多，因为DBMS是设计来快速有效地完成这种处理的。

10.2 拼接字段

在MySQL的SELECT语句中，可使用Concat()函数来拼接两个列。

拼接（concatenate） 将值联结到一起构成单个值。

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


MariaDB [testdatabase]> SELECT * FROM vendors;
+-----------+---------------+
| name      | location      |
+-----------+---------------+
| alphabet  | mountain view |
| amazon    | seattle       |
| amd       | california    |
| apple     | california    |
| intel     | california    |
| meta      | california    |
| microsoft | washington    |
| nvidia    | california    |
| tesla     | california    |
+-----------+---------------+
9 rows in set (0.001 sec)

MariaDB [testdatabase]> SELECT Concat(name, ' (', location, ')') FROM vendors ORDER BY name;
+-----------------------------------+
| Concat(name, ' (', location, ')') |
+-----------------------------------+
| alphabet (mountain view)          |
| amazon (seattle)                  |
| amd (california)                  |
| apple (california)                |
| intel (california)                |
| meta (california)                 |
| microsoft (washington)            |
| nvidia (california)               |
| tesla (california)                |
+-----------------------------------+
9 rows in set (0.000 sec)

Concat()拼接串，即把多个串连接起来形成一个较长的串。Concat()需要一个或多个指定的串，各个串之间用逗号分隔。上面的SELECT语句连接以下4个元素成一个列：

存储在name列中的名字
包含一个空格和一个左圆括号的串
存储在location列中的位置
包含一个右圆括号的串

Trim函数 MySQL除了支持RTrim()（正如刚才所见，它去掉串右边的空格），还支持LTrim()（去掉串左边的空格）以及Trim()（去掉串左右两边的空格）。


MariaDB [testdatabase]> SELECT * FROM vendors;
+-----------+---------------+
| name      | location      |
+-----------+---------------+
| alphabet  | mountain view |
| amazon    | seattle       |
| amd       | california    |
| apple     | california    |
| intel     | california    |
| meta      | california    |
| microsoft | washington    |
| nvidia    | california    |
| tesla     | california    |
+-----------+---------------+
9 rows in set (0.001 sec)

MariaDB [testdatabase]> SELECT Concat(name, LTrim(' ('), location, ')') FROM vendors ORDER BY name;
+------------------------------------------+
| Concat(name, LTrim(' ('), location, ')') |
+------------------------------------------+
| alphabet(mountain view)                  |
| amazon(seattle)                          |
| amd(california)                          |
| apple(california)                        |
| intel(california)                        |
| meta(california)                         |
| microsoft(washington)                    |
| nvidia(california)                       |
| tesla(california)                        |
+------------------------------------------+
9 rows in set (0.001 sec)

使用别名


MariaDB [testdatabase]> SELECT * FROM vendors;
+-----------+---------------+
| name      | location      |
+-----------+---------------+
| alphabet  | mountain view |
| amazon    | seattle       |
| amd       | california    |
| apple     | california    |
| intel     | california    |
| meta      | california    |
| microsoft | washington    |
| nvidia    | california    |
| tesla     | california    |
+-----------+---------------+
9 rows in set (0.000 sec)

MariaDB [testdatabase]> SELECT Concat(name,' (',location,')') AS vend_title FROM vendors;
+--------------------------+
| vend_title               |
+--------------------------+
| alphabet (mountain view) |
| amazon (seattle)         |
| amd (california)         |
| apple (california)       |
| intel (california)       |
| meta (california)        |
| microsoft (washington)   |
| nvidia (california)      |
| tesla (california)       |
+--------------------------+
9 rows in set (0.001 sec)

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

导出列 别名有时也称为导出列（derived column），不管称为什么，它们所代表的都是相同的东西。

10.3 执行算术计算


MariaDB [testdatabase]> SELECT * FROM orders;
+---------+----------+------------+
| prod_id | quantity | item_price |
+---------+----------+------------+
| ANV01   |       10 |       5.99 |
| ANV02   |        3 |       9.99 |
| FB      |        1 |         10 |
| YNT2    |        5 |         10 |
+---------+----------+------------+
4 rows in set (0.001 sec)

MariaDB [testdatabase]> SELECT prod_id,quantity,item_price,quantity*item_price AS expand_price FROM orders;
+---------+----------+------------+--------------------+
| prod_id | quantity | item_price | expand_price       |
+---------+----------+------------+--------------------+
| ANV01   |       10 |       5.99 |  59.89999771118164 |
| ANV02   |        3 |       9.99 | 29.969999313354492 |
| FB      |        1 |         10 |                 10 |
| YNT2    |        5 |         10 |                 50 |
+---------+----------+------------+--------------------+
4 rows in set (0.001 sec)

MySql的float存在精度问题 浮点数在计算机中的内部表示方式是使用有限的二进制位数进行近似存储的。由于浮点数的精度是有限的，当我们进行一些复杂的计算时，可能会导小数部分的精度损失。

解决方法

使用DECIMAL类型：DECIMAL(8,2)指定了存储8位数字，小数部分保留2位


MariaDB [testdatabase]> DESCRIBE orders;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| prod_id    | varchar(50)  | NO   | PRI | NULL    |       |
| quantity   | int(11)      | NO   |     | NULL    |       |
| item_price | decimal(8,2) | NO   |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
3 rows in set (0.002 sec)

MariaDB [testdatabase]> SELECT prod_id,quantity,item_price,quantity*item_price AS expand_price FROM orders;
+---------+----------+------------+--------------+
| prod_id | quantity | item_price | expand_price |
+---------+----------+------------+--------------+
| ANV01   |       10 |       5.99 |        59.90 |
| ANV02   |        3 |       9.99 |        29.97 |
| FB      |        1 |      10.00 |        10.00 |
| YNT2    |        5 |      10.00 |        50.00 |
+---------+----------+------------+--------------+
4 rows in set (0.001 sec)

使用ROUND函数：ROUND函数可以将计算结果四舍五入到指定的精度


MariaDB [testdatabase]> DESCRIBE orders;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| prod_id    | varchar(50) | NO   | PRI | NULL    |       |
| quantity   | int(11)     | NO   |     | NULL    |       |
| item_price | float       | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.001 sec)

MariaDB [testdatabase]> SELECT prod_id,quantity,item_price,ROUND(quantity*item_price,2) AS expand_price FROM orders;
+---------+----------+------------+--------------+
| prod_id | quantity | item_price | expand_price |
+---------+----------+------------+--------------+
| ANV01   |       10 |       5.99 |        59.90 |
| ANV02   |        3 |       9.99 |        29.97 |
| FB      |        1 |         10 |        10.00 |
| YNT2    |        5 |         10 |        50.00 |
+---------+----------+------------+--------------+
4 rows in set (0.000 sec)

使用CAST函数：CAST将一个值转换为指定的数据类型。


MariaDB [testdatabase]> DESCRIBE orders;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| prod_id    | varchar(50) | NO   | PRI | NULL    |       |
| quantity   | int(11)     | NO   |     | NULL    |       |
| item_price | float       | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.001 sec)

MariaDB [testdatabase]> SELECT prod_id,quantity,item_price,CAST(quantity*item_price AS DECIMAL(8,2)) AS expand_price FROM orders;
+---------+----------+------------+--------------+
| prod_id | quantity | item_price | expand_price |
+---------+----------+------------+--------------+
| ANV01   |       10 |       5.99 |        59.90 |
| ANV02   |        3 |       9.99 |        29.97 |
| FB      |        1 |         10 |        10.00 |
| YNT2    |        5 |         10 |        50.00 |
+---------+----------+------------+--------------+
4 rows in set (0.001 sec)

11 使用数据处理函数

11.2 使用函数

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

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

11.2.1 文本处理函数


MariaDB [testdatabase]> SELECT name FROM vendors LIMIT 3;
+----------+
| name     |
+----------+
| alphabet |
| amazon   |
| amd      |
+----------+
3 rows in set (0.001 sec)

MariaDB [testdatabase]> SELECT name,Upper(name) AS name_upcase FROM vendors LIMIT 3;
+----------+-------------+
| name     | name_upcase |
+----------+-------------+
| alphabet | ALPHABET    |
| amazon   | AMAZON      |
| amd      | AMD         |
+----------+-------------+
3 rows in set (0.001 sec)

正如所见，Upper将文本转换为大写。

常用的文本处理函数

函数	说明
Left(str,length)	返回str左边数起length长度的字符
Length(str)	返回str的长度
Locate(sub,str,start)	找出str中start位置开始检查sub子串的位置，默认从1开始
Lower(str)	将串转换为小写
LTrim(str)	去掉串左边的空格
Right(str,length)	返回串右边的字符
RTrim(str)	去掉串右边的空格
Soundex(str)	返回串的SOUNDEX值
SubString(str,start,length)	返回str从start开始length长度的子串
Upper(str)	将串转换为大写

SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似的发音字符和音节，使得能对串进行发音比较而不是字母比较。虽然SOUNDEX不是SQL概念，但MySQL（就像多数DBMS一样）都提供对SOUNDEX的支持。

11.2.2 日期和时间处理函数

常用日期和时间处理函数

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


MariaDB [testdatabase]> SELECT ADDDATE('2022-03-01', INTERVAL 1 DAY);
+---------------------------------------+
| ADDDATE('2022-03-01', INTERVAL 1 DAY) |
+---------------------------------------+
| 2022-03-02                            |
+---------------------------------------+
1 row in set (0.000 sec)

MariaDB [testdatabase]> SELECT ADDDATE('2022-03-01', 1);
+--------------------------+
| ADDDATE('2022-03-01', 1) |
+--------------------------+
| 2022-03-02               |
+--------------------------+
1 row in set (0.000 sec)

MariaDB [testdatabase]> SELECT ADDDATE('2022-03-01', -1);
+---------------------------+
| ADDDATE('2022-03-01', -1) |
+---------------------------+
| 2022-02-28                |
+---------------------------+
1 row in set (0.001 sec)

MariaDB [testdatabase]> SELECT ADDDATE('2022-03-01', INTERVAL -1 WEEK);
+-----------------------------------------+
| ADDDATE('2022-03-01', INTERVAL -1 WEEK) |
+-----------------------------------------+
| 2022-02-22                              |
+-----------------------------------------+
1 row in set (0.000 sec)
MariaDB [testdatabase]> SELECT ADDTIME('12:00:00', '03:30:00');
+---------------------------------+
| ADDTIME('12:00:00', '03:30:00') |
+---------------------------------+
| 15:30:00                        |
+---------------------------------+
1 row in set (0.001 sec)

MariaDB [testdatabase]> SELECT ADDTIME('12:00:00', '-03:30:00');
+----------------------------------+
| ADDTIME('12:00:00', '-03:30:00') |
+----------------------------------+
| 08:30:00                         |
+----------------------------------+
1 row in set (0.000 sec)

MariaDB [testdatabase]> SELECT CONCAT(CURDATE(), ' ', CURTIME()) AS now;
+---------------------+
| now                 |
+---------------------+
| 2024-03-23 19:02:36 |
+---------------------+
1 row in set (0.000 sec)

MariaDB [testdatabase]> SELECT DATE(CURDATE());
+-----------------+
| DATE(CURDATE()) |
+-----------------+
| 2024-03-23      |
+-----------------+
1 row in set (0.002 sec)

MariaDB [testdatabase]> SELECT DATEDIFF(CURDATE(), '2024-03-01');
+-----------------------------------+
| DATEDIFF(CURDATE(), '2024-03-01') |
+-----------------------------------+
|                                22 |
+-----------------------------------+
1 row in set (0.001 sec)

MariaDB [testdatabase]> SELECT DATE_FORMAT('2024-03-01','%Y%m%d');
+------------------------------------+
| DATE_FORMAT('2024-03-01','%Y%m%d') |
+------------------------------------+
| 20240301                           |
+------------------------------------+
1 row in set (0.001 sec)

需要注意MySQL使用的日期格式， 无论你什么时候指定一个日期，不管是插入或更新表值还是用WHERE子句进行过滤，日期必须为格式yyyy-mm-dd。

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

11.2.3 数值处理函数

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

12 汇总数据

12.1 聚集函数

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

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

12.1.1 AVG函数

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

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

12.1.2 COUNT函数

COUNT函数有两种使用方式：

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

12.1.3 MAX函数

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

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

12.1.4 MIN函数

对非数值数据使用MIN() MIN()函数与MAX()函数类似，MySQL允许将它用来返回任意列中的最小值，包括返回文本列中的最小值。在用于文本数据时，如果数据按相应的列排序，则MIN()返回最前面的行。

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

12.1.5 SUM函数

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


MariaDB [testdatabase]> SELECT * FROM tasks;
+----+-----------------+--------+-------+---------+-------------+--------+
| Id | Name            | Result | Fault | Correct | Description | Parent |
+----+-----------------+--------+-------+---------+-------------+--------+
|  1 | db_create_test  |      1 |     1 |       0 | NULL        |        |
|  2 | db_create_test1 |      1 |     0 |       1 |             | NULL   |
|  3 | db_create_test2 |      0 |     1 |       0 | NULL        | NULL   |
|  4 | fake_case_1     |      1 |     0 |       0 |             |        |
|  5 | create_test12   |      1 |     2 |       0 | NULL        | NULL   |
+----+-----------------+--------+-------+---------+-------------+--------+
5 rows in set (0.000 sec)

MariaDB [testdatabase]> SELECT SUM(result*fault) FROM tasks;
+-------------------+
| SUM(result*fault) |
+-------------------+
|                 3 |
+-------------------+
1 row in set (0.001 sec)

12.2 聚集不同值

以上5个聚集函数都可以做如下使用

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


MariaDB [testdatabase]> SELECT * FROM tasks;
+----+-----------------+--------+-------+---------+-------------+--------+
| Id | Name            | Result | Fault | Correct | Description | Parent |
+----+-----------------+--------+-------+---------+-------------+--------+
|  1 | db_create_test  |      1 |     1 |       0 | NULL        |        |
|  2 | db_create_test1 |      1 |     0 |       1 |             | NULL   |
|  3 | db_create_test2 |      0 |     1 |       0 | NULL        | NULL   |
|  4 | fake_case_1     |      1 |     0 |       0 |             |        |
|  5 | create_test12   |      1 |     2 |       0 | NULL        | NULL   |
+----+-----------------+--------+-------+---------+-------------+--------+
5 rows in set (0.001 sec)

MariaDB [testdatabase]> SELECT SUM(result) FROM tasks;
+-------------+
| SUM(result) |
+-------------+
|           4 |
+-------------+
1 row in set (0.000 sec)

MariaDB [testdatabase]> SELECT SUM(DISTINCT result) FROM tasks;
+----------------------+
| SUM(DISTINCT result) |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.001 sec)

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

13 数据分组

13.2 创建分组

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


MariaDB [testdatabase]> SELECT * FROM products;
+----+-----------+---------+
| id | vend_name | product |
+----+-----------+---------+
|  1 | apple     | macbook |
|  2 | apple     | ipad    |
|  3 | apple     | iphone  |
|  4 | microsoft | surface |
|  5 | microsoft | windows |
|  6 | google    | gemini  |
+----+-----------+---------+
6 rows in set (0.001 sec)

MariaDB [testdatabase]> SELECT vend_name,COUNT(*) AS num_products FROM products GROUP BY vend_name;
+-----------+--------------+
| vend_name | num_products |
+-----------+--------------+
| apple     |            3 |
| google    |            1 |
| microsoft |            2 |
+-----------+--------------+
3 rows in set (0.001 sec)

在具体使用GROUP BY子句前，需要知道一些重要的规定

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


MariaDB [testdatabase]> SELECT * FROM products;
+----+-----------+---------+
| id | vend_name | product |
+----+-----------+---------+
|  1 | apple     | macbook |
|  2 | apple     | ipad    |
|  3 | apple     | iphone  |
|  4 | microsoft | surface |
|  5 | microsoft | windows |
|  6 | google    | gemini  |
|  7 | google    | NULL    |
+----+-----------+---------+
7 rows in set (0.001 sec)

MariaDB [testdatabase]> SELECT vend_name,COUNT(*) AS num_products FROM products GROUP BY vend_name;
+-----------+--------------+
| vend_name | num_products |
+-----------+--------------+
| apple     |            3 |
| google    |            2 |
| microsoft |            2 |
+-----------+--------------+
3 rows in set (0.001 sec)

MariaDB [testdatabase]> SELECT vend_name,COUNT(*) AS num_products FROM products GROUP BY vend_name WITH ROLLUP;
+-----------+--------------+
| vend_name | num_products |
+-----------+--------------+
| apple     |            3 |
| google    |            2 |
| microsoft |            2 |
| NULL      |            7 |
+-----------+--------------+
4 rows in set (0.001 sec)

13.3 过滤分组

HAVING支持所有WHERE操作符 在第6章和第7章中，我们学习了WHERE子句的条件（包括通配符条件和带多个操作符的子句）。所学过的有关WHERE的所有这些技术和选项都适用于HAVING。它们的句法是相同的，只是关键字有差别。


MariaDB [testdatabase]> SELECT vend_name,COUNT(*) AS num_products FROM products GROUP BY vend_name;
+-----------+--------------+
| vend_name | num_products |
+-----------+--------------+
| apple     |            3 |
| google    |            2 |
| microsoft |            2 |
+-----------+--------------+
3 rows in set (0.000 sec)

MariaDB [testdatabase]> SELECT vend_name,COUNT(*) AS num_products FROM products GROUP BY vend_name HAVING COUNT(*)>2;
+-----------+--------------+
| vend_name | num_products |
+-----------+--------------+
| apple     |            3 |
+-----------+--------------+
1 row in set (0.001 sec)

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

13.4 分组和排序

ORDER BY和GROUP BY的区别

ORDER BY	GROUP BY
排序产生的输出	分组行。但输出可能不是分组的顺序
任意列都可以使用(甚至非选择的列也可以使用)	只可能使用选择列或表达式列，而且必须使用每个选择列表达式

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

13.5 SELECT子句顺序

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

14 使用子查询

14.2 利用子查询进行过滤

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


MariaDB [testdatabase]> SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');
+-----------+
|  cust_id  |
+-----------+
|    10001  |
|    10004  |
+-----------+
2 rows in set (0.001 sec)

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

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

14.3 作为计算字段使用子查询


MariaDB [testdatabase]> SELECT cust_name,cust_state,(SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders FROM customers ORDER BY cust_name;
+-----------------+------------+--------+
| cust_name       | cust_state | orders |
+-----------------+------------+--------+
| Coyote Inc.     | MI         |      2 |
| E Fudd          | IL         |      1 |
| Mouse House     | OH         |      0 |
| Wascals         | IN         |      1 |
| Yosemite Place  | AZ         |      1 |
+-----------------+------------+--------+
5 rows in set (0.001 sec)

子查询中的WHERE子句与前面使用的WHERE子句稍有不同，因为它使用了完全限定列名（在第4章中首次提到）。上面的语句告诉SQL比较orders表中的cust_id与当前正从customers表中检索的cust_id。

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

逐渐增加子查询来建立查询 用子查询测试和调试查询很有技巧性，特别是在这些语句的复杂性不断增加的情况下更是如此。用子查询建立（和测试）查询的最可靠的方法是逐渐进行，这与MySQL处理它们的方法非常相同。首先，建立和测试最内层的查询。然后，用硬编码数据建立和测试外层查询，并且仅在确认它正常后才嵌入子查询。这时，再次测试它。对于要增加的每个查询，重复这些步骤。这样做仅给构造查询增加了一点点时间，但节省了以后（找出查询为什么不正常）的大量时间，并且极大地提高了查询一开始就正常工作的可能性。

15 联结表

15.1 联结

15.1.1 关系表

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

这样做的好处是：

外键指向的表中的信息变更的时候只需要做一份修改
存储的数据没有重复

因此，关系型数据库的可伸缩性远比非关系型数据库好。

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

15.1.2 为什么要使用联结

分解数据为多个表能更有效地存储，更方便地处理，并且具有更大的可伸缩性。但这些好处是有代价的。—— 查询变的复杂。

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

维护引用完整性 重要的是，要理解联结不是物理实体。换句话说，它在实际的数据库表中不存在。联结由MySQL根据需要建立，它存在于查询的执行当中。(参阅21章)

15.2 创建联结


示例

15.2.1 WHERE子句的重要性

利用WHERE子句建立联结关系似乎有点奇怪，但实际上，有一个很充分的理由。请记住，在一条SELECT语句中联结几个表时，相应的关系是在运行中构造的。在数据库表的定义中不存在能指示MySQL如何对表进行联结的东西。你必须自己做这件事情。在联结两个表时，你实际上做的是将第一个表中的每一行与第二个表中的每一行配对。WHERE子句作为过滤条件，它只包含那些匹配给定条件（这里是联结条件）的行。没有WHERE子句，第一个表中的每个行将与第二个表中的每个行配对，而不管它们逻辑上是否可以配在一起。

笛卡儿积（cartesian product） 由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

不要忘了WHERE子句 应该保证所有联结都有WHERE子句，否则MySQL将返回比想要的数据多得多的数据。同理，应该保证WHERE子句的正确性。不正确的过滤条件将导致MySQL返回不正确的数据。

15.2.2 内部联结

目前为止所用的联结称为等值联结（equijoin），它基于两个表之间的相等测试。这种联结也称为内部联结。WHERE语法可以用INNER JOIN语法替换。

使用哪种语法 ANSI SQL规范首选INNER JOIN语法。此外，尽管使用WHERE子句定义联结的确比较简单，但是使用明确的联结语法能够确保不会忘记联结条件，有时候这样做也能影响性能。

15.2.3 联结多个表

在WHERE语法中，可以通过在FROM添加多个表，在WHERE后面用AND拼接多个条件来联结多个表。

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

多做实验 为执行任一给定的SQL操作，一般存在不止一种方法。很少有绝对正确或绝对错误的方法。性能可能会受操作类型、表中数据量、是否存在索引或键以及其他一些条件的影响。因此，有必要对不同的选择机制进行实验，以找出最适合具体情况的方法。

外键约束

以下内容来自MySQL官方文档。

在MySQL的SQL语句后加上\G，表示将查询结果进行按列打印，可以使每个字段打印到单独的行。即将查到的结构旋转90度变成纵向。

MySQL支持外键(foreign key)和外键约束(foreign key constraint)。外键允许跨表数据的交叉引用，外键约束维持外键所建立的引用关系。

一对外键关系包含一个含有初始列的父表和一个子表，子表中的一列引用父表中的初始列。外键约束在子表中定义。

在CREATE TABLE或ALTER TABLE中定义外键约束，定义语法如下：


[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (col_name, ...)
    REFERENCES tbl_name (col_name, ...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

外键约束的用法：

标识符

外键约束的命名按下列规则管理：

如果给出了CONSTRAINT <symbol>子句，那么使用语句中定义的名字。
如果没有定义CONSTRAINT <symbol>子句或者CONSTRAINT后没给出symbol，那么约束名会自动生成。

在MySQL 8.0.16之前，InnoDB和NDB引擎使用FOREIGN_KEY index_name(如果有定义)。MySQL 8.0.16后的更高版本会忽略FOREIGN_KEY index_name。
CONSTRAINT <symbol>定义的值必须是database中唯一的，重复的值会引发类似的错误：
```
ERROR 1005 (HY000): Can't create table 'test.fk1' (errno: 121).
```
NDB集群使用的外键名大小写和它们定义的时候一样。8.0.20版本之前，在处理SELECT和其他SQL语句的时候，如果系统变量lower_case_table_names为0，NDB对比执行语句中的外键名和存储的大小写敏感的外键名。8.0.20和之后的版本，该系统变量会被忽略，所有的比较都是忽略大小写的(Bug #30512043)。

FOREIGN KEY ... REFERENCES子句中的表和列的标识符可以被`囊括。ANSI_QUOTESSQL模式开启的时候，"也可以起到相同的作用。这些同样考虑到了lower_case_table_names变量的影响。

条件和限制

外键约束有以下的条件和限制：

父表和子表必须使用相同的存储引擎，且不能是临时表。
创建外键约束需要有父表的REFERENCES权限。
外键和其引用的列必须是相似的数据类型。大小，符号，精度必须是一样的，比如INTEGER和DECIMAL。如果是string类型，则长度不必一致，但它们的字符集和排序规则需要一致。
MySQL支持外键引用同一张表中的其他列(外键不允许引用自己)。这种情况下，"child table record"指的是同表中的依赖记录。
MySQl要求外键和其引用列创建索引从而避免在外键检查的时候检索全表，以此提高外键检查的速度。In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order(这句没明白，需要仔细研究下索引)。这样的索引如果不存在就会被自动创建。如果创建了另外一个可以用于维持外键约束的索引，那么这个自动创建的索引会自动删掉。如果指定了index_name，将会按照标识符中的规则生效。
InnoDB允许外键引用任意索引了的一个或者一组列。However, in the referenced table, there must be an index where the referenced columns are the first columns in the same order(同样，这句没明白，需要仔细研究下索引)。InnoDB添加到索引中的隐藏列也在考虑范围内(参考聚集索引和二级索引)。

NDB中外键所引用的列需要是显示唯一的键(或者主键)。而作为标准SQL的扩展的InnoDB不需要。
外键列不支持索引前缀，因此外键列不能包含BLOB和TEXT(这些列上的索引必须始终包含前缀长度)。
InnoDB不支持在含有user-defined partitioning的表中建立外键和外键约束。

此限制不适用于由KEY或LINEAR KEY(NDB存储引擎支持的唯一用户分区类型)分区的NDB表；它们可能具有外键引用，或者是此类引用的目标。
外键约束下的表不能改用其他的存储引擎。如果要修改，必须要先删除表上所有的外键约束。
外键不能引用引用虚拟创建的列。

更多细节参考MySQL的外键实现和标准SQL的区别。

参考操作

当UPDATE或DELETE操作影响父表中具有子表中匹配行的键值时，结果取决于FOREIGN KEY子句的ON UPDATE和ON DELETE子句指定的引用操作。参考操作包括:

CASCADE：删除或更新父表中的行时会自动删除或更新子表中的匹配行。支持同时使用ON DELETE CASCADE和ON UPDATE CASCADE。两个表之间不要定义多个ON UPDATE CASCADE子句，这些子句作用于父表或子表中的同一列。

如果在一对外键关系中的两个表上都定义了FOREIGN KEY子句，使两个表都成为父表和子表，则必须为两个FOREIGN KEY子句都定义ON UPDATE CASCADE或ON DELETE CASCADE子句，以便级联(CASCADE)操作成功。如果仅为一个FOREIGN KEY子句定义了ON UPDATE CASCADE或ON DELETE CASCADE子句，则级联操作将失败并报错。

注意：CASCADE引起的列变更不会触发触发器(triggers)。
SET NULL：删除或更新父表中的行，并将子表中的外键列设置为NULL。支持同时使用ON DELETE SET NULL和ON UPDATE SET NULL子句。使用前必须确认外键列没有设置NOT NULL。
RESTRICT：拒绝父表的删除或更新操作。指定RESTRICT(或NO ACTION)等同于省略ON DELETE或ON UPDATE子句。
NO ACTION：标准SQL中的关键字。在InnoDB中这相当于RESTRICT；如果子表中存在对应的外键值，则立即拒绝父表的删除或更新操作。NDB支持延迟检查，NO ACTION指定延迟检查；使用此选项时，约束检查会延迟到提交(commit)的时候才被执行。注意，这会导致使用NDB的父表和子表的外键检查都被延迟。
SET DEFAULT：这个操作由MySQL解析器识别，但是InnoDB和NDB都拒绝在定义表的时候使用ON DELETE SET DEFAULT或ON UPDATE SET DEFAULT。

对于支持外键的存储引擎，如果在父表中没有匹配的候选键值，MySQL会拒绝任何试图在子表中创建外键值的INSERT或UPDATE操作。

如果不指定ON DELETE或ON UPDATE，则默认使用NO ACTION。

作为默认值，即便是明确指定的ON DELETE NO ACTION或ON UPDATE NO ACTION子句也不会出现在SHOW CREATE TABLE的输出和使用mysqldump转储的表中。而RESTRICT是等效的非默认关键字，会出现在SHOW CREATE TABLE输出和使用mysqldump转储的表中。

对于NDB表，如果引用是父表的主键(NDB中外键所引用的列需要是显示唯一的键)，则不支持ON UPDATE CASCADE。

从NDB 8.0.16开始：对于NDB表，如果子表包含TEXT或BLOB类型的列，则不支持ON DELETE CASCADE。(Bug #89511, Bug #27484882)

InnoDB使用深度优先搜索算法对外键约束对应的索引记录执行级联(CASCADE)操作。

被存储的生成列上的外键约束不能使用CASCADE、SET NULL或SET DEFAULT作为ON UPDATE参考操作(剩余RESTRICT，也就说对于子表存在的键值，不允许更新父表中对应的键值)，也不能使用SET NULL或SET DEFAULT作为ON DELETE的参考操作(RESTRICT：也就说对于子表存在的键值，不允许删除父表中对应的键值；CASCADE：删除父表中的键值也会同时删除子表中的所有对应键值)。

TODO 自理解(待验证)：被存储的生成列的外键约束中，如果父表中存在和子表相对应的键值，那么那些键值不可UPDATE，但可以DELETE(也可以限制)。

被存储的生成列的基列上的外键约束不能使用CASCADE、SET NULL或SET DEFAULT作为ON UPDATE或ON DELETE参考操作。

外键约束示例

下面是一个在单个列上的建立外键关系的示例：


MariaDB [testdatabase]> CREATE TABLE parent(
    -> id INT NOT NULL,
    -> PRIMARY KEY (id)
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.030 sec)

MariaDB [testdatabase]> CREATE TABLE child(
    -> id INT,
    -> parent_id INT,
    -> INDEX par_ind (parent_id),
    -> FOREIGN KEY (parent_id) REFERENCES parent(id)
    -> ON DELETE CASCADE
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.029 sec)

以下是一个更复杂的示例，其中product_order表具有另外两个表的外键。一个外键引用product表中的一个两列索引，另一个引用customer表中的单列索引：


MariaDB [test]> CREATE TABLE product (
    -> category INT NOT NULL, id INT NOT NULL,
    -> price DECIMAL,
    -> PRIMARY KEY(category, id)
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.029 sec)

MariaDB [test]> CREATE TABLE customer (
    -> id INT NOT NULL,
    -> PRIMARY KEY (id)
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.023 sec)

MariaDB [test]> CREATE TABLE product_order (
    -> no INT NOT NULL AUTO_INCREMENT,
    -> product_category INT NOT NULL,
    -> product_id INT NOT NULL,
    -> customer_id INT NOT NULL,
    -> 
    -> PRIMARY KEY(no),
    -> INDEX (product_category, product_id),
    -> INDEX (customer_id),
    -> 
    -> FOREIGN KEY (product_category, product_id)
    -> REFERENCES product(category, id)
    -> ON UPDATE CASCADE ON DELETE RESTRICT,
    -> 
    -> FOREIGN KEY (customer_id)
    -> REFERENCES customer(id)
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.037 sec)

添加外键约束

可以通过ALTER TABLE给一个已存在的表添加外键约束：


ALTER TABLE tbl_name
    ADD [CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (col_name, ...)
    REFERENCES tbl_name (col_name, ...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

外键可以自引用(引用同一个表)。当使用ALTER TABLE向表添加外键约束时，请记住首先在外键引用的列上创建索引。

删除外键约束

使用ALTER TABLE删除外键约束：


ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

如果FOREIGN KEY子句在创建约束时定义了约束名称，则可以引用该名称来删除外键约束。否则要使用内部生成的约束名称。使用SHOW CREATE TABLE确定外键约束名称：


MariaDB [testdatabase]> SHOW CREATE TABLE child\G
*************************** 1. row ***************************
       Table: child
Create Table: CREATE TABLE `child` (
  `id` int(11) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  KEY `par_ind` (`parent_id`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.000 sec)

MariaDB [testdatabase]> ALTER TABLE child DROP FOREIGN KEY `child_ibfk_1`;
Query OK, 0 rows affected (0.008 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [testdatabase]> SHOW CREATE TABLE child\G
*************************** 1. row ***************************
       Table: child
Create Table: CREATE TABLE `child` (
  `id` int(11) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  KEY `par_ind` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.000 sec)

ALTER TABLE ... ALGORITHM=INPLACE支持同时有添加和删除操作，但ALTER TABLE ... ALGORITHM=COPY不支持。

外键核查

在MySQL中，InnoDB和NDB表支持检查外键约束。外键检查由foreign_key_checks变量控制，该变量在默认情况下是启用的。通常在正常操作期间启用该变量以强制引用完整性。foreign_key_checks变量对NDB表和InnoDB表的作用是一样的。

foreign_key_checks变量是动态的，支持全局作用域和会话作用域。更多信息参考使用系统变量。

以下场景中通常会关闭外键检查：

删除由外键约束引用的表。被引用的表只有在禁用foreign_key_checks后才能被删除(否则需要先删除外键约束)。删除表时，表上定义的约束也会被删除。
Reloading tables in different order than required by their foreign key relationships。例如，mysqldump在转储文件中生成正确的表定义，包括子表的外键约束。为了更方便地从转储文件中重新加载具有外键关系的表，mysqldump自动在转储输出中包含一条禁用foreign_key_checks的语句。这使得表能够以任何顺序导入，以防转储文件中包含外键顺序不正确的表。禁用foreign_key_checks还可以通过避免外键检查来加快导入操作。
执行LOAD DATA时关闭foreign_key_checks以禁用外键检查。
对具有外键关系的表执行ALTER TABLE操作时关闭foreign_key_checks。

当foreign_key_checks被禁用时，外键约束将被忽略，除了以下例外情况：

如果表定义不符合引用此表的外键约束(比如被引用列数据类型变更)，则重新创建先前被删除的表将返回错误。表必须具有正确的列名和类型。被引用得键上必须建立有索引。如果不满足这些要求， MySQL返回错误表示未形成正确的外键约束。
如果更改后的表的外键定义不正确，则针对此表的更改将返回错误(errno: 150)。
删除外键约束所需的索引。在删除索引之前，必须先删除外键约束(为什么删表不用?)。
在类型不匹配(不满足创建外键约束的要求)的两个列间创建外键约束。

禁用foreign_key_checks有这些额外效果：

允许删除包含被该数据库外部的表引用的具有外键的表的数据库。
启用foreign_key_checks不会触发对表数据的扫描，这意味着在禁用foreign_key_checks时添加到表中的行不会在重新启用foreign_key_checks后检查约束。

锁定

MySQL根据需要将元数据锁(metadata locks)扩展到与外键约束相关的表。扩展元数据锁可以防止冲突的DML和DDL操作在相关表上并发执行。此特性还允许在修改父表时更新子表外键元数据。在早期的MySQL版本中，子表拥有的外键元数据不能安全地更新。

如果用LOCK TABLES显式地锁定表，那么与外键约束相关的任何表都将被隐式地打开和锁定。外键检查在相关表上使用共享只读锁(LOCK TABLES READ)。级联(CASCADE)更新在涉及操作的相关表上使用无共享写锁(LOCK TABLES WRITE)。

外键定义和元数据

可以从信息模式(Information Schema)KEY_COLUMN_USAGE表中获取外键信息。下面显示了针对该表的查询示例：


MariaDB [testdatabase]> SHOW CREATE TABLE child\G
*************************** 1. row ***************************
       Table: child
Create Table: CREATE TABLE `child` (
  `id` int(11) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  KEY `par_ind` (`parent_id`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.001 sec)

MariaDB [testdatabase]> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
    -> FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    -> WHERE REFERENCED_TABLE_SCHEMA IS NOT NULL;
+--------------+---------------+------------------+----------------------+
| TABLE_SCHEMA | TABLE_NAME    | COLUMN_NAME      | CONSTRAINT_NAME      |
+--------------+---------------+------------------+----------------------+
| testdatabase | child         | parent_id        | child_ibfk_1         |
+--------------+---------------+------------------+----------------------+
1 rows in set (0.012 sec)

InnoDB的外键信息可以从INNODB_FOREIGN(使用MariaDB实验的时候没发现此表，而是INNODB_SYS_FOREIGN)和INNODB_FOREIGN_COLS(使用MariaDB实验的时候没发现此表，而是INNODB_SYS_FOREIGN_COLS)表中获取。例如：


MariaDB [testdatabase]> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN \G
*************************** 1. row ***************************
      ID: test/product_order_ibfk_1
FOR_NAME: test/product_order
REF_NAME: test/product
  N_COLS: 2
    TYPE: 4
*************************** 2. row ***************************
      ID: test/product_order_ibfk_2
FOR_NAME: test/product_order
REF_NAME: test/customer
  N_COLS: 1
    TYPE: 0
*************************** 3. row ***************************
      ID: testdatabase/child_ibfk_1
FOR_NAME: testdatabase/child
REF_NAME: testdatabase/parent
  N_COLS: 1
    TYPE: 1
*************************** 4. row ***************************
      ID: testdatabase/fk_orderitems_orders
FOR_NAME: testdatabase/orderitems
REF_NAME: testdatabase/orders
  N_COLS: 1
    TYPE: 0
*************************** 5. row ***************************
      ID: testdatabase/fk_orders_customers
FOR_NAME: testdatabase/orders
REF_NAME: testdatabase/customers
  N_COLS: 1
    TYPE: 0
*************************** 6. row ***************************
      ID: testdatabase/fk_products_vendors
FOR_NAME: testdatabase/products
REF_NAME: testdatabase/vendors
  N_COLS: 1
    TYPE: 0
6 rows in set (0.000 sec)

外键错误

在涉及InnoDB表的外键错误(通常是MySQL服务器的error 150)的情况下，可以通过查看SHOW ENGINE INNODB STATUS输出来获取最新的外键错误信息。


MariaDB [testdatabase]> SHOW ENGINE INNODB STATUS\G
......
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2024-04-07 21:57:11 0x7f71cc2fa700 Error in foreign key constraint creation for table `testdatabase`.`#sql-170a53_4b6e`.
A foreign key constraint of name `testdatabase`.`fk_orderitems_orders`
already exists. (Note that internally InnoDB adds 'databasename'
in front of the user-defined constraint name.)
Note that InnoDB’s FOREIGN KEY system tables store
constraint names as case-insensitive, with the
MySQL standard latin1_swedish_ci collation. If you
create tables or databases whose names differ only in
the character case, then collisions in constraint
names can occur. Workaround: name your constraints
explicitly with unique names.
......

1 row in set (0.001 sec)

Warning

如果用户拥有所有父表的表级权限，则外键操作的ER_NO_REFERENCED_ROW_2和ER_ROW_IS_REFERENCED_2错误消息会暴露父表的信息。如果用户没有所有父表的表级权限，则会显示更通用的错误消息(ER_NO_REFERENCED_ROW和ER_ROW_IS_REFERENCED)。

一个例外是定义了以DEFINER权限执行的存储过程中，权限评估的对象是DEFINER子句中的指定的用户而不是调用者。如果该用户具有表级父表权限，则仍然显示父表信息。在这种情况下，存储程序创建者应该通过包含适当的条件处理程序来隐藏信息。

16 创建高级联结

16.2 使用不同类型的联结

之前接触到的INNER JOIN是称为内部联结或等值联结（equijoin）的简单联结。

16.2.1 自联结


MariaDB [testdatabase]> 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';
+---------+----------------+
| prod_id | prod_name      |
+---------+----------------+
| DTNTR   | Detonator      |
| FB      | Bird seed      |
| FC      | Carrots        |
| SAFE    | Safe           |
| SLING   | Sling          |
| TNT1    | TNT (1 stick)  |
| TNT2    | TNT (5 sticks) |
+---------+----------------+
7 rows in set (0.001 sec)

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

为解决此问题，使用了表别名。如果不这样，MySQL将返回错误，因为分别存在两个名为prod_id、prod_name的列。MySQL不知道想要的是哪一个表中的列（即使它们事实上是同一个列，但MySQL不知道他们是同一个表）。

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

16.2.2 自然联结

无论何时对表进行联结，应该至少有一个列出现在不止一个表中（被联结的列）。标准的联结（前一章中介绍的内部联结）返回所有数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次。

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

16.2.3 外部联结

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


MariaDB [testdatabase]> SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10002 |      NULL |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+
6 rows in set (0.001 sec)

这条SELECT语句使用了关键字OUTER JOIN来指定联结的类型（而不是在WHERE子句中指定）。与内部联结关联两个表中的行不同的是，外部联结还包括没有关联行的行。在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）。上面的例子使用LEFT OUTER JOIN从FROM子句的左边表（customers表）中选择所有行。

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

16.3 使用带聚集函数的联结


MariaDB [testdatabase]> 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;
+----------------+---------+---------+
| cust_name      | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc.    |   10001 |       2 |
| Wascals        |   10003 |       1 |
| Yosemite Place |   10004 |       1 |
| E Fudd         |   10005 |       1 |
+----------------+---------+---------+
4 rows in set (0.001 sec)

此SELECT语句使用INNER JOIN将customers和orders表互相关联。GROUP BY子句按客户分组数据，因此，函数调用COUNT(orders.order_num)对每个客户的订单计数，将它作为num_ord返回。

聚集函数也可以方便地与其他联结一起使用


MariaDB [testdatabase]> SELECT customers.cust_name,customers.cust_id,COUNT(orders.order_num) AS num_ord FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id;
+----------------+---------+---------+
| cust_name      | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc.    |   10001 |       2 |
| Mouse House    |   10002 |       0 |
| Wascals        |   10003 |       1 |
| Yosemite Place |   10004 |       1 |
| E Fudd         |   10005 |       1 |
+----------------+---------+---------+
5 rows in set (0.000 sec)

MariaDB [testdatabase]> SELECT customers.cust_name,customers.cust_id,COUNT(orders.order_num) AS num_ord FROM customers RIGHT OUTER JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id;
+----------------+---------+---------+
| cust_name      | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc.    |   10001 |       2 |
| Wascals        |   10003 |       1 |
| Yosemite Place |   10004 |       1 |
| E Fudd         |   10005 |       1 |
+----------------+---------+---------+
4 rows in set (0.001 sec)

个人理解 JOIN语句(FROM语句里的表)左边的为左表，右边的为右表。LEFT JOIN保留左表中所有的内容，RIGHT JOIN保留右表中的所有内容。(JOIN中相对于INNER的OUTER关键词一半省略)

16.4 使用联结和联结条件

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

注意所使用的联结类型。一般我们使用内部联结，但使用外部联结也是有效的。
保证使用正确的联结条件，否则将返回不正确的数据。
应该总是提供联结条件，否则会得出笛卡儿积。
在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前，分别测试每个联结。这将使故障排除更为简单。

17 组合查询

17.1 组合查询

多数SQL查询都只包含从一个或多个表中返回数据的单条SELECT语句。MySQL也允许执行多个查询(多条SELECT语句)，并将结果作为单个查询结果集返回。这些组合查询通常称为并(union)或复合查询(compound query)。

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

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

组合查询和多个WHERE条件 多数情况下，组合相同表的两个查询完成的工作与具有多个WHERE子句条件的单条查询完成的工作相同。换句话说，任何具有多个WHERE子句的SELECT语句都可以作为一个组合查询给出。这两种技术在不同的查询中性能也不同。因此，应该试一下这两种技术，以确定对特定的查询哪一种性能更好。

17.2 创建组合查询

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

17.2.2 UNION的规则

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

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

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

17.2.3 包含或取消重复的行

UNION默认会取消重复的行，若要完全的结果，使用UNION ALL即可。

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

17.2.4 对组合查询结果排序

在用UNION组合查询的时候只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后。

UNION的组合查询可以应用不同的表。

18 全文本搜索

18.1 理解全文本搜索

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

LIKE通过统配操作匹配文本，通过LIKE能查找包含特殊值或部分值的行。

虽然这些搜索机制很有用，但存在几个重要的限制

性能——通配符和正则表达式匹配通常要求MySQL尝试匹配表中所有行(而且这些搜索极少使用表索引)。因此，由于被搜索行数不断增加，这些搜索可能非常耗时。
明确控制——使用通配符和正则表达式匹配，很难(而且并不总是能)明确地控制匹配什么和不匹配什么。
智能化结果——虽然基于通配符和正则表达式的搜索提供了非常灵活的搜索，但他们都不能提供一种智能化的选择结果的方法。

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

18.2 使用全文本搜索

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

在索引后，SELECT可以Match()和Against()一起使用来实际执行搜索。

18.2.1 启用全文本搜索支持

一般在创建表的时候启用全文本搜索。CREATE TABLE语句接受FULLTEXT子句，它给出被索引列的一个都好分隔的列表。


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;

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

可以在创建表时指定FULLTEXT，或者在稍后指定(在这种情况下所有已有的数据必须立刻索引)。

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

18.2.2 进行全文本搜索

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


MariaDB [testdatabase]> SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit');
+----------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                            |
+----------------------------------------------------------------------------------------------------------------------+
| Customer complaint: rabbit has been able to detect trap, food apparently less effective now.                         |
| Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait. |
+----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

使用完整的Match()说明 传递给Match()的值必须与FULLTEXT()定义中的相同。如果指定多个列，则必须列出它们(而且次序正确)。

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

全文本搜索的一个重要部分就是对结果排序。具有较高等级的行先返回(因为这些行很可能是你真正想要的行)。


MariaDB [testdatabase]> SELECT note_text,Match(note_text) Against('rabbit') AS rank FROM productnotes;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+
| note_text                                                                                                                                                 | rank               |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+
| Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping.                          |                  0 |
| Can shipped full, refills not available.
Need to order new can if refill needed.                                                                          |                  0 |
| Safe is combination locked, combination not provided with safe.
This is rarely a problem as safes are typically blown up or dropped by customers.         |                  0 |
| Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait.                                      | 1.5905543565750122 |
| Included fuses are short and have been known to detonate too quickly for some customers.
Longer fuses are available (item FU1) and should be recommended. |                  0 |
| Matches not included, recommend purchase of matches or detonator (item DTNTR).                                                                            |                  0 |
| Please note that no returns will be accepted if safe opened using explosives.                                                                             |                  0 |
| Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.  |                  0 |
| Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.                                       |                  0 |
| Customer complaint: rabbit has been able to detect trap, food apparently less effective now.                                                              | 1.6408053636550903 |
| Shipped unassembled, requires common tools (including oversized hammer).                                                                                  |                  0 |
| Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw.                                                                |                  0 |
| Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.   |                  0 |
| Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.
Comment forwarded to vendor.                            |                  0 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+
14 rows in set (0.000 sec)

在SELECT而不是WHERE子句中使用Match()和Against()。Match()和Against()用来建立一个计算列，此列包含全文本搜索计算出的等级值。等级由MySQL根据行中词的数目、唯一词的数目、整个索引中词的总数以及包含该词的行的数目计算出来。

排序多个搜索项 如果指定多个搜索项，则包含多数匹配词的那些行将具有比包含较少词(或仅有一个匹配)的那些行高的等级值。

全文本搜索提供了简单LIKE搜索不能提供的功能。而且，由于数据是索引的，全文本搜索还相当快。

18.2.3 使用查询扩展

查询扩展用来设法放宽所返回的全文本搜索结果的范围。在使用查询扩展时，MySQL对数据和索引进行两遍扫描来完成搜索

首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有行；
其次，MySQL检查这些匹配行并选择所有有用的词。
再其次，MySQL再次进行全文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词。

只用于MySQL版本4.1.1或更高级的版本 查询扩展功能是在MySQL4.1.1中引入的，因此不能用于之前的版本。

比如，没有查询扩展：


MariaDB [testdatabase]> SELECT note_text FROM productnotes WHERE Match(note_text) Against('anvils');
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils. |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

只有一行包含词anvils，因此只返回一行。下面是相同的搜索，这次使用查询扩展：


MariaDB [testdatabase]> SELECT note_text FROM productnotes WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils. |
| Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping.                         |
| Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.  |
| Please note that no returns will be accepted if safe opened using explosives.                                                                            |
| Customer complaint: rabbit has been able to detect trap, food apparently less effective now.                                                             |
| Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw.                                                               |
| Matches not included, recommend purchase of matches or detonator (item DTNTR).                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.000 sec)

这次返回了7行。第一行词包括anvils，因此等级最高。第二行与anvils无关，但它因为包含第一行中的两个词(customer和recommend)，所以也被检索出来。第三行也包含这两个相同的词，但它们在文本中的位置更靠后且分开的更远，因此也包含这一行，但等级为第三。

正如所见，查询扩展极大地增加了返回的行数，但这样做也增加了你实际上并不想要的行的数目。

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

18.2.4 布尔文本搜索

MySQL支持全文本搜索的另外一种形式，称为布尔方式(boolean mode)。该方式可以提供如下内容的细节：

要匹配的词；
要排斥的词(如果某行包含要排斥的词，即使它包含要匹配的词也不返回)；
排列提示(指定某些词比其他词更重要，更重要的词等级更高)；
表达式分组；

即使没有FULLTEXT索引也可以使用 布尔方式不同于迄今为止所使用的全文本搜索语法的地方在于，即使没有定义FULLTEXT索引也可以使用它。但这是一种非常缓慢的操作(其性能将随着数据量的增加而降低)。


MariaDB [testdatabase]> SELECT note_text FROM productnotes WHERE Match(note_text) Against('heavy' IN BOOLEAN MODE);
+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| note_text
                                  |
+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.                                     |
| Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead. |
+---------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

此全文本搜索检索包含词heavy的所有行(有两行)。其中使用了关键字IN BOOLEAN MODE，但实际上没有指定布尔操作符，因此，其结果与没有指定布尔方式的结果相同。

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


MariaDB [testdatabase]> SELECT note_text FROM productnotes WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);
+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| note_text
                   |
+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead. |
+---------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

这次只返回一行，仍然匹配词heavy，但-rope*明确地只是MySQL配出包含rope*的行。

在MySQL 4.x中所需的代码更改如果使用的是MySQL 4.x，则上面的例子可能不返回任何行。这是*操作符处理中的一个错误。要在MySQL 4.x中使用这个例子，使用-ropes而不是-rope*(排除ropes而不是排除任何以rope开始的词)。

全文本布尔操作符

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

举例说明：

匹配包含词rabbit和bait的行。


MariaDB [testdatabase]> SELECT note_text FROM productnotes WHERE Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE);
+----------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                            |
+----------------------------------------------------------------------------------------------------------------------+
| Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait. |
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

没有指定操作符，匹配包含rabbit和bait中的至少一个词的行。


MariaDB [testdatabase]> SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit bait' IN BOOLEAN MODE);
+----------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                            |
+----------------------------------------------------------------------------------------------------------------------+
| Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait. |
| Customer complaint: rabbit has been able to detect trap, food apparently less effective now.                         |
+----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

匹配短语rabbit bait而不是匹配两个词rabbit和bait。


MariaDB [testdatabase]> SELECT note_text FROM productnotes WHERE Match(note_text) Against('"bait rabbit"' IN BOOLEAN MODE);
Empty set (0.000 sec)

MariaDB [testdatabase]> SELECT note_text FROM productnotes WHERE Match(note_text) Against('"rabbit bait"' IN BOOLEAN MODE);
+----------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                            |
+----------------------------------------------------------------------------------------------------------------------+
| Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait. |
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

匹配rabbit和carrot，增加前者的等级，降低后者的等级。


MariaDB [testdatabase]> SELECT note_text FROM productnotes WHERE Match(note_text) Against('>rabbit <bait' IN BOOLEAN MODE);
+----------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                            |
+----------------------------------------------------------------------------------------------------------------------+
| Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait. |
| Customer complaint: rabbit has been able to detect trap, food apparently less effective now.                         |
+----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

匹配safe和combination，降低后者的等级。


MariaDB [testdatabase]> SELECT note_text FROM productnotes WHERE Match(note_text) Against('+safe +(<combination)' IN BOOLEAN MODE);
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                                                         |
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| Safe is combination locked, combination not provided with safe.
This is rarely a problem as safes are typically blown up or dropped by customers. |
+---------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

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

18.2.5 全文本搜索的使用说明

重要说明：

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

18.2.6 邻近操作符

邻近搜索是许多全文本搜索支持的一个特性，它能搜索相邻的词（在相同的句子中、相同的段落中或者在特定数目的词的部分中，等等）。

笔记摘录的书写成的时候MySQL还不支持邻近操作符，待补充。

19 插入数据

19.1 数据插入

INSERT用来插入(或添加)行到数据库表的。插入可以用几种方式使用：

插入完整的行；
插入行的一部分；
插入多行；
插入某些查询的结果。

插入及系统安全 可针对每个表或每个用户，利用MySQl的安全机制禁止使用INSERT语句。

19.2 插入完整的行

指定表名和被插入到新行中的值


MariaDB [testdatabase]> INSERT INTO Customers VALUES(NULL, 'Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL, NULL);

没有输出 INSERT语句一半不会产生输出。

如果某列没有值，应该使用NULL值(如果允许的话)。各个列必须以它们在表定义中出现的次序填充。

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

这是更安全的一种做法：


INSERT INTO customers(cust_id, cust_name) VALUES(10001, 'Coyote Inc.');

在表名后的括号里明确地给出了列名。在插入行时，MySQL将用VALUES列表中的相应值填入列表中的对应项。其优点是，即使表的结构改变，此INSERT语句仍然能正确工作。而且未指定的列不用填NULL。

省略列 如果表的定义允许，则可以在INSERT操作中省略某些列。省略的列必须满足以下某个条件。

该列定义为允许NULL值（无值或空值）。
在表定义中给出默认值。这表示如果不给出值，将使用默认值。

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

提高整体性能 数据库经常被多个客户访问，对处理什么请求以及用什么次序处理进行管理是MySQL的任务。INSERT操作可能很耗时（特别是有很多索引需要更新时），而且它可能降低等待处理的SELECT语句的性能。

如果数据检索是最重要的（通常是这样），则你可以通过在INSERT和INTO之间添加关键字LOW_PRIORITY，指示MySQL降低INSERT语句的优先级，如 INSERT LOW_PRIORITY INTO 。这同样适用于 UPDATE 和 DELETE 语句。

19.3 插入多个行

可以多次INSERT来插入多条数据。但更建议组合INSERT(性能更好)。


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');
Query OK, 2 rows affected (0.005 sec)
Records: 2  Duplicates: 0  Warnings: 0

其中单条INSERT语句有多组值，每组值用一对圆括号括起来，用逗号分隔。

19.4 插入检索出的数据

INSERT一般用来给表插入一个指定列值的行。但是，INSERT还存在另一种形式，可以利用它将一条SELECT语句的结果插入表中。这就是所谓的INSERT SELECT，顾名思义，它是由一条INSERT语句和一条SELECT语句组成的。


MariaDB [testdatabase]> INSERT INTO customers(cust_id,cust_contact) SELECT cust_id,cust_contact FROM custnew;

这个例子使用INSERT SELECT从custnew中将所有数据导入customers。SELECT语句从custnew检索出要插入的值，而不是列出它们。

INSERT SELECT中的列名 为简单起见，这个例子在INSERT和SELECT语句中使用了相同的列名。但是，不一定要求列名匹配。事实上，MySQL甚至不关心SELECT返回的列名。它使用的是列的位置，因此SELECT中的第一列（不管其列名）将用来填充表列中指定的第一个列，第二列将用来填充表列中指定的第二个列，如此等等。这对于从使用不同列名的表中导入数据是非常有用的。

INSERT SELECT中SELECT语句可包含WHERE子句以过滤插入的数据。

20 更新和删除数据

20.1 更新数据

使用UPDATE更新数据表中的数据有两种方式：

更新表中特定行
更新表中所有行

不要省略WHERE子句不带WHERE会更新表中所有的行。

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

要更新的表；
列名和它们的新值；
确定要更新行的过滤条件。


MariaDB [testdatabase]> UPDATE customers SET cust_name='The Fudds', cust_email='elmer@fudd.com' WHERE cust_id=10005;

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

在UPDATE语句中使用子查询 UPDATE语句中可以使用子查询，使得能用SELECT语句检索出的数据更新列数据。

IGNORE关键字 如果用UPDATE语句更新多行，并且在更新这些行中的一行或多行时出现一个错误，则整个UPDATE操作被取消(没有任何修改被执行)。如果要让错误发生后操作继续执行，可使用IGNORE关键字，如 UPDATE IGNORE customers ...。

20.2 删除数据

使用DELETE语句从表中删除数据有两种方式：

从表中删除特定的行
从表中删除所有行

不要省略WHERE子句不带WHERE会删除表中所有的行。


MariaDB [testdatabase]> DELETE FROM customers WHERE cust_id=10006;

DELETE删除的是数据行(不能用来删除表)，因此不需要列名或者通配符。

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

20.3 更新和删除的指导原则

下面是一些DELETE和UPDATE时建议养成的好习惯：

除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。
保证每个表都有主键（如果忘记这个内容，请参阅第15章），尽可能像WHERE子句那样使用它（可以指定各主键、多个值或值的范围）。
在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。
本人尚未理解：使用强制实施引用完整性的数据库（关于这个内容，请参阅第15章），这样MySQL将不允许删除具有与其他表相关联的数据的行。

21 创建和操纵表

21.1 CREATE TABLE创建表

21.1.1 表创建基础

CREATE TABLE需要下列信息：

新表的名字，在关键字CREATE TABLE之后给出；
表列的名字和定义，用逗号分隔。

CREATE TABLE语句也可能会包括其他关键字或选项，但至少要包括表的名字和列的细节。


MariaDB [testdatabase]> 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 ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL ,
  PRIMARY KEY (cust_id)
) ENGINE=InnoDB;

每列的定义以列名(在表中必须唯一)开始，后跟列的数据类型(附录D列出了MySQL支持的数据类型)。表的主键可以在创建表时用PRIMARY KEY关键字指定。

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

21.1.2 使用NULL值

创建时指定NOT NULL的列不接受没有值的行


MariaDB [testdatabase]> CREATE TABLE orders(
  order_num  int      NOT NULL AUTO_INCREMENT,
  order_date datetime NOT NULL ,
  cust_id    int      NOT NULL ,
  PRIMARY KEY (order_num)
) ENGINE=InnoDB;

理解NULL 不要把NULL值与空串相混淆。NULL值是没有值，它不是空串。如果指定''（两个单引号，其间没有字符），这在NOT NULL列中是允许的。空串是一个有效的值，它不是无值。NULL值用关键字NULL而不是空串指定。

21.1.3 关于主键

主键值必须唯一。即，表中的每个行必须具有唯一的主键值。如果主键使用单个列，则它的值必须唯一。如果使用多个列，则这些列的组合值必须唯一。

单个列作为主键的定义如 PRIMARY KEY (vend_id)。

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


MariaDB [testdatabase]> 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;

主键也可以在表创建后定义，主键只能使用不允许为NULL值的列。

21.1.4 使用AUTO_INCREMENT

AUTO_INCREMENT告诉MySQL，本列每当增加一行时自动增量。每次执行一个INSERT操作时，MySQL自动对该列增量（从而才有这个关键字AUTO_INCREMENT），给该列赋予下一个可用的值。

确定AUTO_INCREMENT值 SELECT last_insert_id() 返回最后一个AUTO_INCREMENT值。

21.1.5 指定默认值

用DEFAULT关键字指定默认值：


MariaDB [testdatabase]> 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;

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

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

21.1.6 引擎类型

与其他DBMS一样，MySQL有一个具体管理和处理数据的内部引擎。

MySQL具有多种引擎。它打包多个引擎，这些引擎都隐藏在MySQL服务器内，全都能执行CREATE TABLE和SELECT等命令。

为什么要发行多种引擎呢？因为它们具有各自不同的功能和特性，为不同的任务选择正确的引擎能获得良好的功能和灵活性。

如果省略ENGINE=语句，则使用默认引擎（很可能是MyISAM），多数SQL语句都会默认使用它。但并不是所有语句都默认使用它，这就是为什么ENGINE=语句很重要。引擎类型可以混用。

几个重要的引擎：

InnoDB是一个可靠的事务处理引擎(详见26章)，不支持全文本搜索；
MEMORY在功能上等同于MyISAM，但由于数据存储在内存(不是磁盘)中，速度很快(适用于临时表)。
MyISAM是一个性能极高的引擎，支持全文本搜索，但不支持事务处理。

21.2 更新表

可以使用ALTER TABLE语句更新表，但理想状态下，当表中存储数据以后，该表就不应该再被更新。在表的设计过程中需要花费大量时间来考虑，以便后期不对该表进行大的改动。

ALTER TABLE更改表结构需要下面的信息：

在ALTER TABLE之后给出要更改的表明(该表必须存在，否则将出错)；
更改列表；


MariaDB [testdatabase]> ALTER TABLE vendors ADD vend_phone CHAR(20);

该语句给vendors表增加一个名为vend_phone的列，必须明确其数据类型。

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


MariaDB [testdatabase]> ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);

MariaDB [testdatabase]> ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);

MariaDB [testdatabase]> ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);

MariaDB [testdatabase]> ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);

复杂的表结构更改一半需要手动删除，设计以下步骤：

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

小心使用ALTER TABLE 使用ALTER TABLE要极为小心，应该在进行改动前做一个完整的备份（模式和数据的备份）。数据库表的更改不能撤销，如果增加了不需要的列，可能不能删除它们。类似地，如果删除了不应该删除的列，可能会丢失该列中的所有数据。

21.3 删除表

删除表(删除整个表而不是删除表中的内容)非常简单，使用DROP TABLE语句即可：DROP TABLE customers2;。

21.4 重命名表

RENAME TABLE语句可以重命名一个表：RENAME TABLE customers2 TO customers;。

RENAME TABLE所作的仅是重命名一个表。可以使用下面的语句对多个表重命名：


MariaDB [testdatabase]> RENAME TABLE backup_customers TO customers, backup_vendors TO vendors, backup_products TO products;

22 使用视图

22.1 视图

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。

22.1.1 为什么使用视图

常见应用：

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

在视图创建之后可以用与表基本相同的方式利用它们。视图仅仅是用来查看存储在别处的数据的一种设施。视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。再添加或更改这些表中的数据时，视图将返回改变过的数据。

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

22.1.2 视图的规则和限制

常见的规则和限制：

与表名一样，视图必须唯一命名；
对于可以创建的视图数目没有限制；
视图可以嵌套，即可以用一个视图来创建另外一个视图；
ORDER BY可以用在视图中，但是如果创建视图的检索(源)中也使用了ORDER BY，视图(源)中的ORDER BY将被覆盖。
视图不能索引，也不能有关联的触发器或默认值
视图可以和表一起使用。

22.2 使用视图

视图的创建：

用CREATE VIEW语句来创建视图；
使用 SHOW CREATE VIEW viewname; 来查看创建视图的语句；
使用DROP删除视图，其语法为 DROP VIEW viewname;。
更新视图时，可以先用DROP再用CREATE，也可以直接使用 CREATE OR REPLACE VIEW。如果要更新的视图不存在，则 CREATE OR REPLACE VIEW 会创建一个视图，否则替换原视图。

22.2.1 利用视图简化复杂的联结

视图最常见的应用之一是隐藏复杂的SQL：


MariaDB [testdatabase]> 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;
Query OK, 0 rows affected (0.007 sec)

MariaDB [testdatabase]> SELECT cust_name,cust_contact FROM productcustomers WHERE prod_id='TNT2';
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+
2 rows in set (0.001 sec)

创建可重用的视图 创建不受特定数据限制的视图是一种好办法。扩展视图的范围不仅使得它能被重用，而且甚至更有用。这样做不需要创建和维护多个类似视图。

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

视图的另一种常见用途是重新格式化检索出来的数据。


MariaDB [testdatabase]> SELECT Concat(Rtrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title FROM vendors ORDER BY vend_name;
+-------------------------+
| vend_title              |
+-------------------------+
| ACME (USA)              |
| Anvils R Us (USA)       |
| Furball Inc. (USA)      |
| Jet Set (England)       |
| Jouets Et Ours (France) |
| LT Supplies (USA)       |
+-------------------------+
6 rows in set (0.001 sec)

MariaDB [testdatabase]> CREATE VIEW vendorlocations AS SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') AS vend_title FROM vendors ORDER BY vend_name;
Query OK, 0 rows affected (0.006 sec)

MariaDB [testdatabase]> SELECT * FROM vendorlocations;
+------------------------+
| vend_title             |
+------------------------+
| ACME(USA)              |
| Anvils R Us(USA)       |
| Furball Inc.(USA)      |
| Jet Set(England)       |
| Jouets Et Ours(France) |
| LT Supplies(USA)       |
+------------------------+
6 rows in set (0.001 sec)

22.2.3 用视图过滤不想要的数据

可以用视图过滤掉那些不需要的或者无效的数据(比如说没有值，NULL)。

WHERE子句与WHERE子句 如果从视图检索数据时使用了一条WHERE子句，则两组子句（一组在视图中，另一组是传递给视图的）将自动组合。

22.2.4 使用视图与计算字段

创建每个结果的计算视图然后通过WHERE对视图进行筛选。

例子太长了，意思就是有商品、价格、数目构成的表，由所有的价格*数目构建视图，使用的时候只需筛选商品，不用再写计算的表达式了。

22.2.5 更新视图

通常视图是可更新的(即可以使用INSERT、UPDATE和DELETE)，而这会影响到作为数据源的表(视图本身没有数据)。也就是说对视图增加或删除行，实际上是对其基表增加或删除行。

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

分组(使用GROUP BY和HAVING)；
联结；
子查询；
并；
聚集函数；
DISTINCT；
计算列

将视图用于检索 一般，应该将视图用于检索（SELECT语句）而不用于更新（INSERT、UPDATE和DELETE）。

23 使用存储过程

23.1 存储过程

存储过程简单来说就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为皮文件，虽然它们的作用不仅限于批处理。

23.2 为什么要使用存储过程

封装处理，简化操作。

保证数据完整性，防止错误。因为存储过程在多次的使用中执行的是相同的代码。

简化管理。如果数据结构有变更，那么管理人员只需要变更存储过程，使用人员不需要知道有哪些变化。

提高性能，使用存储过程比使用单独的SQL语句更快。

存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。

总结一下就是简单、安全、高性能。

23.3 使用存储过程

存储过程的执行远比其定义更经常遇到，因此先从执行存储过程开始介绍。

23.3.1 执行存储过程

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

如CALL productpricing(@pricelow, @pricehigh, @priceaverage);，存储过程可以显示结果，也可以不显示。

23.3.2 创建存储过程

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


MariaDB [testdatabase]> CREATE PROCEDURE productpricing()
BEGIN
    SELECT Avg(prod_price) AS priceaverage FROM products;
END;

此存储过程名为productpricing，用CREATE PROCEDURE productpricing()语句定义。如果存储过程接受参数，它们将在()中列举出来。此存储过程没有参数，但后跟的()仍然需要。BEGIN和END语句用来限定存储过程体，过程体本身仅是一个简单的SELECT语句。

在MySQL处理这段代码时，它创建一个新的存储过程productpricing。没有返回数据，因为这段代码并未调用存储过程，这里只是为以后使用而创建它。

mysql命令行客户机的分隔符 默认的MySQL语句分隔符为;。mysql命令行实用程序也使用;作为语句分隔符。如果命令行实用程序要解释存储过程自身内的;字符，则它们最终不会成为存储过程的成分，这会使存储过程中的SQL出现句法错误。解决办法是临时更改命令行实用程序的语句分隔符，如下所示:


DELIMITER //

CREATE PROCEDURE productpricing()
BEGIN
    SELECT Avg(prod_price) AS priceaverage FROM products;
END //

DELIMITER;

其中，DELIMITER //告诉命令行实用程序使用//作为新的语句结束分隔符，可以看到标志存储过程结束的END定义为END //而不是END;。这样，存储过程体内的;仍然保持不动，并且正确地传递给数据库引擎。最后，为恢复为原来的语句分隔符，可使用DELIMITER;。

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


MariaDB [testdatabase]> CALL productpricing();
+--------------+
| priceaverage |
+--------------+
|    16.133571 |
+--------------+
1 rows in set (0.001 sec)

CALL productpricing();执行创建的存储过程并返回结果。因为存储过程实际上是一种函数，所以存储过程后需要有()符号(即使不传递参数也需要)。

23.3.3 删除存储过程

存储过程在创建之后，被保存在服务器上以供使用，直至被删除。可使用DROP PROCEDURE productpricing;。这条语句删除刚创建的存储过程。这里并没有使用()，只给出存储过程。

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

23.3.4 使用参数

productpricing只是一个简单的存储过程，它简单地显示SELECT语句的结果。一般，存储过程并不显示结果，而是把结果返回给你指定的变量。

变量(variable) 内存中一个特定的位置，用来临时存储数据。


MariaDB [testdatabase]> DELIMITER //
MariaDB [testdatabase]>
MariaDB [testdatabase]> 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 //
Query OK, 0 rows affected (0.001 sec)

MariaDB [testdatabase]> 
MariaDB [testdatabase]> DELIMITER ;

此存储过程接受3个参数：pl存储产品最低价格，ph存储产品最高价格，pa存储产品平均价格。

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

MySQL支持IN（传递给存储过程）、OUT（从存储过程传出，如这里所用）和INOUT（对存储过程传入和传出）类型的参数。

存储过程的代码位于BEGIN和END语句内，如前所见，它们是一系列SELECT语句，用来检索值，然后保存到相应的变量（通过指定INTO关键字）。

参数的数据类型 存储过程的参数允许的数据类型与表中使用的数据类型相同。注意，记录集不是允许的类型，因此，不能通过一个参数返回多个行和列。

为调用此修改的存储过程，必须指定3个变量名，如


MariaDB [testdatabase]> CALL productpricing(@pricelow, @pricehigh, @priceaverage);
Query OK, 3 rows affected, 1 warning (0.001 sec)

由于此存储过程要求3个参数，因此必须正好传递3个参数。

变量名 所有MySQl变量都必须以@开始。

在调用时，这条语句并不显示任何数据。他返回以后可以显示的变量，为了显示检索出的产品平均价格，可如下进行：


MariaDB [testdatabase]> SELECT @priceaverage;
+---------------+
| @priceaverage |
+---------------+
|         16.13 |
+---------------+
1 row in set (0.000 sec)

MariaDB [testdatabase]> SELECT @priceaverage, @pricelow, @priceaverage;
+---------------+-----------+---------------+
| @priceaverage | @pricelow | @priceaverage |
+---------------+-----------+---------------+
|         16.13 |      2.50 |         16.13 |
+---------------+-----------+---------------+
1 row in set (0.000 sec)

下面是另外一个例子，使用IN和OUT参数。ordertotal接受订单号并返回该订单的合计：


MariaDB [testdatabase]> DELIMITER //
MariaDB [testdatabase]> 
MariaDB [testdatabase]> 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 //
Query OK, 0 rows affected (0.006 sec)

MariaDB [testdatabase]> 
MariaDB [testdatabase]> DELIMITER ;

onumber定义为IN，因此订单号被传入存储过程。ototal定义为OUT，因为要从存储过程返回合计。使用方法如下：


MariaDB [testdatabase]> CALL ordertotal(20005, @total);
Query OK, 1 row affected (0.001 sec)

MariaDB [testdatabase]> SELECT @total;
+--------+
| @total |
+--------+
| 149.87 |
+--------+
1 row in set (0.000 sec)

23.3.5 建立智能存储过程

在存储过程内包含业务规则和智能处理


-- Name: ordertotal
-- Parameters:  onumber = order number
--              taxable = 0 if not taxable, 1 if taxable
--              ototal  = order totoal variable

CREATE PROCEDURE ordertotal(
    IN onumber INT,
    IN taxable BOOLEAN,
    OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optinally 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 taxable to the total
        SELECT total+(total/100*taxrate) INTO total;
    END IF;

    -- And finally, save to out variable
    SELECT total INTO ototal;
END;

此存储过程增加了注释(前面放置--)。

DECLARE语句定义局部变量，DECLARE要求指定变量名和数据类型，也支持可选的默认值

IF语句检查taxable是否为真，如果为真，则用另一SELECT语句增加营业税到局部变量total。

COMMENT关键字 本例子中的存储过程在CREATE PROCEDURE语句中包含了一个COMMENT值。它不是必需的，但如果给出，将在SHOW PROCEDURE STATUS的结果中显示。

IF语句 这个例子给出了MySQL的IF语句的基本用法。IF语句还支持ELSEIF和ELSE子句（前者还使用THEN子句，后者不使用）。

23.3.6 检查存储过程

为显示用来创建一个存储过程的CREATE语句，使用SHOW CREATE PROCEDURE <procedure名称>;语句。

使用SHOW PROCEDURE STATUS获得包括何时、由谁创建等详细信息的存储过程列表。

限制过程状态结果 SHOW PROCEDURE STATUS列出所有存储过程。为限制其输出，可使用LIKE指定一个过滤模式，例如：SHOW PROCEDURE STATUS LIKE 'ordertotal';。

24 使用游标

24.1 游标

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

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

游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。

只能用于存储过程 不像多数DBMS，MySQL游标只能用于存储过程（和函数）。

24.2 使用游标

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

先声明(定义)它。这个过程实际上没有检索数据，它只是定义要使用的SELECT语句。
声明后，必须打开游标以供使用。这个过程用前面定义的SELECT语句吧数据实际检索出来。
对于填有数据的游标，根据需要取出(检索各行)。
在结束游标使用时，必须关闭游标。

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

24.2.1 创建游标

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


MariaDB [testdatabase]> DELIMITER //
MariaDB [testdatabase]> CREATE PROCEDURE processorders()
    -> BEGIN
    -> DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders;
    -> END //
Query OK, 0 rows affected (0.007 sec)

MariaDB [testdatabase]> DELIMITER ;

DECLARE语句定义和命名游标为ordernumbers。存储过程处理完成后，游标就消失（因为它局限于存储过程）。

24.2.2 打开和关闭游标

用OPEN <游标名>;语句打开游标。用CLOSE <游标名>;语句关闭游标。

在处理OPEN语句时执行查询，存储检索出的数据以供浏览和滚动。

CLOSE释放游标使用的所有内部内存和资源，因此在每个游标不再需要时都应该关闭。在一个游标关闭后，如果没有重新打开，则不能使用它。但是，使用声明过的游标不需要再次声明，用OPEN语句打开它就可以了。

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

24.2.3 使用游标数据

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


CREATE PROCEDURE proicessorders()
BEGIN
    -- Declare local variables
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;

    -- Declare the cuesor
    DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders;

    -- Declare continue handler
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

    -- Open the cursor
    OPEN ordernumbers;

    -- Loop through all rows
    REPEAT

        -- Get order number
        FETCH ordernumbers INTO o;

    -- End of loop
    UNTIL done END REPEAT;

    -- Close the cursor
    CLOSE ordernumbers;
END;

FETCH检索当前order_num到声明的名为o的变量中。

FETCH在REPEAT内，因此它反复执行知道done为真(由UNTIL done END REPEAT;规定)。为了使它起作用，用一个DEFAULT 0定义变量done。

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;这条语句定义了一个CONTINUE HANDLER，它是在条件出现时被执行的代码。这里表示当SQLSTATE '02000'出现时，SET done=1。

SQLSTATE '02000'是一个未找到条件，当REPEAT由于没有更多行供循环而不能继续时出现这个条件。

MySQL的错误代码 关于MySQL 5使用的MySQL错误代码列表，请参阅http://dev.mysql.com/doc/mysql/en/error-handling.html。

DECLARE语句的次序 DECLARE语句的发布存在特定的次序。用DECLARE语句定义的局部变量必须在定义任意游标或句柄(HANDLER)之前定义，而句柄必须在游标之后定义。不遵守此顺序将产生错误消息。即变量->游标->句柄。

如果一切正常，你可以在循环内放入任意需要的处理（在FETCH语句之后，循环结束之前）。

25 使用触发器

25.1 触发器

触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句（或位于BEGIN和END语句之间的一组语句）：

DELETE；
INSERT；
UPDATE。

其他的MySQL语句不支持触发器。

25.2 创建触发器

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

唯一的触发器名；
触发器关联的表；
触发器应该响应的活动(DELETE、INSERT或UPDATE)；
触发器何时执行(处理之前或之后)。

保持每个数据库的触发器名唯一 截至这本书发行的时候，MySQL触发器名必须在每个表中唯一，但不是在每个数据库中唯一。这表示同一数据库中的两个表可具有相同名字的触发器。这在其他每个数据库触发器名必须唯一的DBMS中是不允许的，而且以后的MySQL版本很可能会使命名规则更为严格。因此，现在最好是在数据库范围内使用唯一的触发器名。

使用CREATE TRIGGER语句创建触发器。


-- 该语句在8.0版本的MySQL中执行不了。待修改
MariaDB [testdatabase]> CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added';
ERROR 1415 (0A000): Not allowed to return a result set from a trigger

该语句在products表创建一个名为newproduct的在INSERT完成后触发的触发器，针对成功插入的每一行都会显示“Product added”。

仅支持表 只有表支持触发器，视图不支持(临时表也不支持)。

触发器失败 如果BEFORE触发器失败，则MySQL将不执行请求的操作。此外，如果BEFORE触发器或语句本身失败，MySQL 将不执行AFTER触发器(如果有的话)。

25.3 删除触发器

可以用DROP TRIGGER <trigger名称>;语句删除一个触发器。

25.4 使用触发器

25.4.1 INSERT触发器

INSERT触发器在INSERT语句执行之前或之后执行。

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

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


-- 8.0可能执行不了
CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num;

使用BEFORE还是AFTER： BEFORE通常用于数据验证(目的是保证插入表中的数据确实是需要的数据)。

25.4.2 DELETE触发器

DELETE触发器在DELETE语句执行之前或之后执行。

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


-- 8.0也可能执行不了
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本身将被放弃。

25.4.3 UPDATE触发器

UPDATE触发器在UPDATE语句执行之前或之后执行。

在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问以前旧值，引用一个名为NEW的虚拟表访问将要更新的值；
在BEFORE UPDATE触发器中，允许修改NEW虚拟表，即可以更改将要更新的内容；
OLD中的值全都是只读的，不能更新。


-- 8.0也可能执行不了
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors FOR EACH ROW SET NEW.vend_state=Upper(NEW.vend_state);

25.4.4 关于触发器的进一步介绍

最新的更详细的内容参考官方的文档。

书中版本的MySQL触发器中不支持CALL语句，不能从触发器内调用存储过程。

26 管理事务处理

26.1 事务处理

事务处理(transaction processing)是一种机制，用来管理必须成批执行测MySQL操作。事务处理可以用来维护数据库的完整性，它保证成批的MySQl操作完全执行或者完全不执行。

关于事务处理的术语：

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

26.2 控制事务处理

管理事务处理的关键在于将SQL语句组分解为逻辑块，并明确规定数据何时应该回退。START TRANSACTION用来标识事务的开始。

26.2.1 使用ROLLBACK

ROLLBACK命令用来回退(撤销)MySQL语句。ROLLBACK只能在一个事务处理内使用(在START TRANSACTION执行之后)。


SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;

哪些语句可以回退： 事务处理用来管理INSERT、UPDATE和DELETE语句。SELECT无法回退(也没有意义)，CREATE和DROP也不能回退。事务处理中可以使用CREATE和DROP，但如果执行回退，它们不会被撤销。

26.2.2 使用COMMIT

一般的MySQL语句都是直接针对数据库表执行和编写的。这就是所谓的隐含提交(implicit commit)，即提交(写或保存)操作是自动进行的。

事务处理块中，提交不会隐含地进行，要使用COMMIT明确提交：


START TRANSACTION;
DELETE FROM orderitems WHERE order_num=20010;
DELETE FROM orders WHERE order_num=20010;
COMMIT;

20010项有两张表相关联，使用事务处理来删除确保没有残留。最后的COMMIT语句确保仅在不出错时写入更改。如果第一条DELETE成功而第二条DELETE失败，则DELETE不会提交(实际上是被自动撤销)。(撤销后最后修改时间之类的属性会不会改变？)

隐含事务关闭： 当COMMIT或ROLLBACK语句执行后，事务会自动关闭(将来的更改会隐含提交)。

26.2.3 使用保留点

简单的ROLLBACK和COMMIT无法支撑更复杂的事务处理，这时候就需要用到部分提交或回退。

为了支持回退部分事务处理，必须在事务处理块中合适的位置放置占位符。这样，如果需要回退就可以回退到某个占位符。这些占位符称为保留点。使用SAVEPOINT <占位符名称>;创建占位符。创建后就可以用ROLLBACK TO <保留点名称>;回退到该保留点。

保留点越多越好： 可以在MySQL代码中设置任意多的保留点，越多的保留点能确保越灵活的回退。

释放保留点： 保留点在事务处理完成(执行ROLLBACK或COMMIT)后自动释放。MySQL5后也可以用RELEASE SAVEPOINT明确地释放保留点。

26.2.4 更改默认的提交行为

autocommit标志位决定是否自动提交，该标志是针对每个连接而不是服务器的。SET autocommit=0;可以取消MySQL默认的自动提交。

27 全球化和本地化

27.1 字符集和校对顺序

数据库表被用来存储和检索数据。不同的语言和字符集需要以不同的方式存储和检索。因此MySQL需要适应不同的字符集(不同的字母和字符)，适应不同的排序和检索数据的方法。

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

27.2 使用字符集和校对顺序

MySQL支持众多的字符集，可以使用SHOW CHARACTER SET;查看所支持的字符集完整列表。使用SHOW COLLATION;查看所支持校对的完整列表。

通常系统管理在安装时定义一个默认的字符集和校对。此外，也可以在创建数据库时，指定默认的字符集和校对。为了确定所用的字符集和校对，可以使用以下语句：


SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';

实际上，字符集很少是服务器范围（甚至数据库范围）的设置。不同的表，甚至不同的列都可能需要不同的字符集，而且两者都可以在创建表时指定。


-- 指定了CHARACTER SET和COLLATE两者
CREATE TABLE mytable(
    column1 INT,
    column2 VARCHAR(10)
) DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;

一般，MySQL如下确定使用什么样的字符集和校对。

如果指定CHARACTER SET和COLLATE两者，则使用这些值。
如果只指定CHARACTER SET，则使用此字符集及其默认的校对（如SHOW CHARACTER SET的结果中所示）。
如果既不指定CHARACTER SET，也不指定COLLATE，则使用数据库默认。

MySQL还允许对每个列设置它们，这里对整个表以及一个特定的列制定了CHARACTER SET和COLLATE：


CREATE TABLE mytable(
    column1 INT,
    column2 VARCHAR(10),
    column3 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci
) DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;

校对在对用ORDER BY子句检索出来的数据排序时起重要的作用。如果你需要用与创建表时不同的校对顺序排序特定的SELECT语句，可以在SELECT语句自身中进行。此SELECT使用COLLATE指定一个备用的校对顺序(在这个例子中，为区分大小写的校对)：


SELECT * FROM customers ORDER BY lastname, firstname COLLATE latin_general_cs;

SELECT的其他COLLATE子句： 除了这里看到的在ORDER BY子句中使用以外，COLLATE还可以用于GROUP BY、HAVING、聚集函数、别名等。

如果需要，串可以使用Cast()或Convert()函数在字符集之间进行转换。

28 安全管理

28.1 访问控制

防止无意的错误： 重要的是注意到，访问控制的目的不仅仅是防止用户的恶意企图。数据梦魇更为常见的是无意识错误的结果，如错打MySQL语句，在不合适的数据库中操作或其他一些用户错误。通过保证用户不能执行他们不应该执行的语句，访问控制有助于避免这些情况的发生。

不要使用root： 应该严肃对待root登录的使用。仅在绝对需要时使用它（或许在你不能登录其他管理账号时使用）。不应该在日常的MySQL操作中使用root。

28.2 管理用户

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

28.2.1 创建用户账号

使用CREATE USER语句创建一个新用户账号，如：


CREATE USER ben IDENTIFIED BY 'yourPassword';

在创建用户账号时不一定需要口令(密码)，不过这个例子用IDENTIFIED BY 'p@$$wOrd'给出了一个口令。

指定散列口令： IDENTIFIED BY指定的口令为纯文本，MySQL将在保存到user表之前对其进行加密。为了作为散列值指定口令，使用IDENTIFIED BY PASSWORD(TODO:没明白这里说的是什么意思)。

使用GRANT或INSERT： GRANT语句也可以创建用户账号，但一般来说CREATE USER是最清楚和最简单的句子。此外，也可以通过直接插入行到user表来增加用户，不过为安全起见，一般不建议这样做。MySQL用来存储用户账号信息的表（以及表模式等）极为重要，对它们的任何毁坏都可能严重地伤害到MySQL服务器。因此，相对于直接处理来说，最好是用标记和函数来处理这些表。

可以使用RENAME USER语句重新命名一个用户账号，如：


RENAME USER ben TO bforta;

MySQL 5之前： 仅MySQL 5或之后的版本支持RENAME USER。为了在以前的MySQL中重命名一个用户，可使用UPDATE直接更新user表。

28.2.2 删除用户账号

使用DROP USER语句删除一个用户账号，如：


DROP USER bforta;

MySQL 5之前： 自MySQL 5以来，DROP USER删除用户账号和所有相关的账号权限。在MySQL 5以前，DROP USER只能用来删除用户账号，不能删除相关的权限。因此，如果使用旧版本的MySQL，需要先用REVOKE删除与账号相关的权限，然后再用DROP USER删除账号。

28.2.3 设置访问权限

在创建用户账号后，必须接着分配访问权限。新创建的用户账号没有访问权限。它们能登录MySQL，但不能看到数据，不能执行任何数据库操作。

使用SHOW GRANTS FOR可以看到赋予用户账号的权限：


MariaDB [mysql]> SHOW GRANTS FOR masha;
+---------------------------------------------------------------------------------------------+
| Grants for masha@%                                                                          |
+---------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `masha`@`%` IDENTIFIED BY PASSWORD '88888' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

用户定义为user@host： MySQL的权限用用户名和主机名结合定义。如果不指定主机名，则使用默认的主机名%（授予用户访问权限而不管主机名）。

使用GRANT语句设置权限，GRANT语句至少需要下列的信息：

要授予的权限；
被授予访问权限的数据库或表；
用户名。


-- 此GRANT允许用户bforta在crashcourse.*(crashcourse数据库的所有表)上使用SELECT。
GRANT SELECT ON crashcourse.* TO bforta;

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


-- 此GRANT撤销用户bforta在crashcourse.*(crashcourse数据库的所有表)上使用SELECT的权限。
REVOKE SELECT ON crashcourse.* FROM bforta;

REVOKE所撤销的权限必须存在，否则会出错。

GRANT和REVOKE可在几个层次上控制访问权限：

整个服务器，使用GRANT ALL和REVOKE ALL；
整个数据库，使用ON database.*；
特定的表，使用ON database.table；
特定的列；
特定的存储过程。

可供授予或撤销的权限参考官方文档 table15.11和table15.12。

未来的授权： 在使用GRANT和REVOKE时，用户账号必须存在，但对所涉及的对象没有这个要求。这允许管理员在创建数据库和表之前设计和实现安全措施。这样做的副作用是，当某个数据库或表被删除时（用DROP语句），相关的访问权限仍然存在。而且，如果将来重新创建该数据库或表，这些权限仍然起作用。

简化多次授权 可通过列出各权限并用逗号分隔，将多条GRANT语句串在一起，如GRANT SELECT,INSERT ON crashcourse.* TO bforta;。

28.2.4 更改口令

可使用SET PASSWORD语句更改用户口令，新口令必须加密：


SET PASSWORD FOR bforta=Password('new passwrod');

SET PASSWORD在不指定用户的时候将更新当前登录用户的口令：


SET PASSWORD = Password('new password');

29 数据库维护

29.1 备份数据

可用的解决方案：

使用命令行实用程序mysqldump转储所有数据库内容到某个外部文件。在进行常规备份前这个实用程序应该正常运行，以便能正确地备份转储文件。
可用命令行实用程序mysqlhotcopy从一个数据库复制所有数据（并非所有数据库引擎都支持这个实用程序）
可以使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE转储所有数据到某个外部文件。这两条语句都接受将要创建的系统文件名，此系统文件必须不存在，否则会出错。数据可以用RESTORE TABLE来复原。

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

29.2 进行数据库维护

ANALYZE TABLE用来检查表键是否正确。ANALYZE TABLE返回如下所示的状态信息：


MariaDB [testdatabase]> ANALYZE TABLE tasks;
+--------------------+---------+----------+----------+
| Table              | Op      | Msg_type | Msg_text |
+--------------------+---------+----------+----------+
| testdatabase.tasks | analyze | status   | OK       |
+--------------------+---------+----------+----------+
1 row in set (0.012 sec)

CHECK TABLE用来针对许多问题对表进行检查。在MyISAM表上还对索引进行检查。CHECK TABLE支持一系列的用于MyISAM表的方式。

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


MariaDB [testdatabase]> CHECK TABLE orders, orderitems;
+-------------------------+-------+----------+----------+
| Table                   | Op    | Msg_type | Msg_text |
+-------------------------+-------+----------+----------+
| testdatabase.orders     | check | status   | OK       |
| testdatabase.orderitems | check | status   | OK       |
+-------------------------+-------+----------+----------+
2 rows in set (0.000 sec)

如果MyISAM表访问产生不正确和不一致的结果，可能需要用REPAIR TABLE来修复相应的表。这条语句不应该经常使用，如果需要经常使用，可能会有更大的问题要解决。

如果从一个表中删除大量数据，应该使用OPTIMIZE TABLE来收回所用的空间，从而优化表的性能。

29.3 诊断启动问题

服务器启动问题通常在对MySQL配置或服务器本身进行更改时出现。MySQL在这个问题发生时报告错误，但由于多数MySQL服务器是作为系统进程或服务自动启动的，这些消息可能看不到。

在排除系统启动问题时，首先应该尽量用手动启动服务器。MySQL服务器自身通过在命令行上执行mysqld启动。下面是几个重要的mysqld命令行选项：

--help显示帮助；
--safe-mode装载减去某些最佳配置的服务器；
--verbose显示全文本消息(为获得更详细的帮助消息与--help联合使用)；
--version显示版本信息然后退出。

29.4 查看日志文件

错误日志： 它包含启动和关闭问题以及任意关键错误的细节。此日志通常名为hostname.err，位于data目录中。此日志名可用--log-error命令行选项更改。

查询日志： 它记录所有MySQL活动，在诊断问题时非常有用。此日志文件可能会很快地变得非常大，因此不应该长期使用它。此日志通常名为hostname.log，位于data目录中。此名字可以用--log命令行选项更改。

二进制日志： 它记录更新过数据（或者可能更新过数据）的所有语句。此日志通常名为hostname-bin，位于data目录内。此名字可以用--log-bin命令行选项更改。注意，这个日志文件是MySQL 5中添加的，以前的MySQL版本中使用的是更新日志。

缓慢查询日志： 顾名思义，此日志记录执行缓慢的任何查询。这个日志在确定数据库何处需要优化很有用。此日志通常名为hostname-slow.log ，位于data目录中。此名字可以用--log-slow-queries命令行选项更改。

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

30 改善性能

性能不良的数据库或查询会导致应用滞缓或出现性能问题。

总结过往各章的重点梳理的进行性能优化探讨和分析的一个出发点：

硬件原因：MySQl有建议的硬件配置，学习的时候可以使用任何旧的计算机作为服务器，但生产环境应该满足这些建议配置。一般来说，关键的生产DBMS应该运行在自己的专用服务器上。
软件配置原因：MySQL是用一系列的默认设置预先配置的，从这些设置开始通常是很好的。但过一段时间后你可能需要调整内存分配、缓冲区大小等。（为查看当前设置，可使用SHOW VARIABLES;和SHOW STATUS;。）
软件性能原因：MySQL是多用户多线程的DBMS。如果任务中的某一个请求执行缓慢，则所有请求都会执行缓慢。如果遇到显著的性能不良，可使用SHOW PROCESSLIST显示所有活动进程（以及它们的线程ID和执行时间）。还可以用KILL命令终结某个特定的进程（使用这个命令需要作为管理员登录）。
语句编写原因：
- 总是有不止一种方法编写同一条SELECT语句。应该试验联结、并、子查询等，找出最佳的方法；
- 使用EXPLAIN语句让MySQL解释它将如何执行一条SELECT语句；
- 一般来说，存储过程执行得比一条一条地执行其中的各条MySQL语句快；
- 应该总是使用正确的数据类型；
- 决不要检索冗余的数据。换言之，不要用SELECT *（除非你真正需要每个列）；
有的操作（包括INSERT）支持一个可选的DELAYED关键字，如果使用它，将把控制立即返回给调用程序，并且一旦有可能就实际执行该操作。
在导入数据时，应该关闭自动提交。你可能还想删除索引（包括FULLTEXT索引），然后在导入完成后再重建它们。
必须索引数据库表以改善数据检索的性能。确定索引什么不是一件微不足道的任务，需要分析使用的SELECT语句以找出重复的WHERE和ORDER BY子句。如果一个简单的WHERE子句返回结果所花的时间太长，则可以断定其中使用的列（或几个列）就是需要索引的对象。
SELECT语句中有一系列复杂的OR条件吗？通过使用多条SELECT语句和连接它们的UNION语句，能看到极大的性能改进。
索引改善数据检索的性能，但损害数据插入、删除和更新的性能。如果你有一些表，它们收集数据且不经常被搜索，则在有必要之前不要索引它们。（索引可根据需要添加和删除。）
LIKE很慢。一般来说，最好是使用FULLTEXT而不是LIKE。
数据库是不断变化的实体。一组优化良好的表一会儿后可能就面目全非了。由于表的使用和内容的更改，理想的优化和配置也会改变。
最重要的规则就是，每条规则在某些条件下都会被打破。

官方文档提供了许多有用的提示和技巧。

创建示例数据库结构


########################################
# MySQL Crash Course
# http://www.forta.com/books/0672327120/
# Example table creation scripts
########################################


########################
# Create customers table
########################
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 ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL ,
  PRIMARY KEY (cust_id)
) ENGINE=InnoDB;

#########################
# Create orderitems table
#########################
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;


#####################
# Create orders table
#####################
CREATE TABLE orders
(
  order_num  int      NOT NULL AUTO_INCREMENT,
  order_date datetime NOT NULL ,
  cust_id    int      NOT NULL ,
  PRIMARY KEY (order_num)
) ENGINE=InnoDB;

#######################
# Create products table
#######################
CREATE TABLE products
(
  prod_id    char(10)      NOT NULL,
  vend_id    int           NOT NULL ,
  prod_name  char(255)     NOT NULL ,
  prod_price decimal(8,2)  NOT NULL ,
  prod_desc  text          NULL ,
  PRIMARY KEY(prod_id)
) ENGINE=InnoDB;

######################
# Create vendors table
######################
CREATE TABLE vendors
(
  vend_id      int      NOT NULL AUTO_INCREMENT,
  vend_name    char(50) NOT NULL ,
  vend_address char(50) NULL ,
  vend_city    char(50) NULL ,
  vend_state   char(5)  NULL ,
  vend_zip     char(10) NULL ,
  vend_country char(50) NULL ,
  PRIMARY KEY (vend_id)
) ENGINE=InnoDB;

###########################
# Create productnotes table
###########################
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;


#####################
# Define foreign keys
#####################
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);

添加示例数据


########################################
# MySQL Crash Course
# http://www.forta.com/books/0672327120/
# Example table population scripts
########################################


##########################
# Populate customers table
##########################
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', 'ylee@coyote.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10002, 'Mouse House', '333 Fromage Lane', 'Columbus', 'OH', '43333', 'USA', 'Jerry Mouse');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10003, 'Wascals', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'rabbit@wascally.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10004, 'Yosemite Place', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Y Sam', 'sam@yosemite.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10005, 'E Fudd', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'E Fudd');


########################
# Populate vendors table
########################
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1001,'Anvils R Us','123 Main Street','Southfield','MI','48075', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1002,'LT Supplies','500 Park Street','Anytown','OH','44333', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1003,'ACME','555 High Street','Los Angeles','CA','90046', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1004,'Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1005,'Jet Set','42 Galaxy Road','London', NULL,'N16 6PS', 'England');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1006,'Jouets Et Ours','1 Rue Amusement','Paris', NULL,'45678', 'France');


#########################
# Populate products table
#########################
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV01', 1001, '.5 ton anvil', 5.99, '.5 ton anvil, black, complete with handy hook');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV02', 1001, '1 ton anvil', 9.99, '1 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV03', 1001, '2 ton anvil', 14.99, '2 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('OL1', 1002, 'Oil can', 8.99, 'Oil can, red');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FU1', 1002, 'Fuses', 3.42, '1 dozen, extra long');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SLING', 1003, 'Sling', 4.49, 'Sling, one size fits all');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT1', 1003, 'TNT (1 stick)', 2.50, 'TNT, red, single stick');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT2', 1003, 'TNT (5 sticks)', 10, 'TNT, red, pack of 10 sticks');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FB', 1003, 'Bird seed', 10, 'Large bag (suitable for road runners)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FC', 1003, 'Carrots', 2.50, 'Carrots (rabbit hunting season only)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SAFE', 1003, 'Safe', 50, 'Safe with combination lock');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('DTNTR', 1003, 'Detonator', 13, 'Detonator (plunger powered), fuses not included');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP1000', 1005, 'JetPack 1000', 35, 'JetPack 1000, intended for single use');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP2000', 1005, 'JetPack 2000', 55, 'JetPack 2000, multi-use');



#######################
# Populate orders table
#######################
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20005, '2005-09-01', 10001);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20006, '2005-09-12', 10003);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20007, '2005-09-30', 10004);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20008, '2005-10-03', 10005);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20009, '2005-10-08', 10001);


###########################
# Populate orderitems table
###########################
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 1, 'ANV01', 10, 5.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 2, 'ANV02', 3, 9.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 3, 'TNT2', 5, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 4, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 1, 'JP2000', 1, 55);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 1, 'TNT2', 100, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 1, 'FC', 50, 2.50);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 1, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 2, 'OL1', 1, 8.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 3, 'SLING', 1, 4.49);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 4, 'ANV03', 1, 14.99);

#############################
# Populate productnotes table
#############################
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(101, 'TNT2', '2005-08-17',
'Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(102, 'OL1', '2005-08-18',
'Can shipped full, refills not available.
Need to order new can if refill needed.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(103, 'SAFE', '2005-08-18',
'Safe is combination locked, combination not provided with safe.
This is rarely a problem as safes are typically blown up or dropped by customers.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(104, 'FC', '2005-08-19',
'Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(105, 'TNT2', '2005-08-20',
'Included fuses are short and have been known to detonate too quickly for some customers.
Longer fuses are available (item FU1) and should be recommended.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(106, 'TNT2', '2005-08-22',
'Matches not included, recommend purchase of matches or detonator (item DTNTR).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(107, 'SAFE', '2005-08-23',
'Please note that no returns will be accepted if safe opened using explosives.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(108, 'ANV01', '2005-08-25',
'Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(109, 'ANV03', '2005-09-01',
'Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(110, 'FC', '2005-09-01',
'Customer complaint: rabbit has been able to detect trap, food apparently less effective now.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(111, 'SLING', '2005-09-02',
'Shipped unassembled, requires common tools (including oversized hammer).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(112, 'SAFE', '2005-09-02',
'Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(113, 'ANV01', '2005-09-05',
'Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(114, 'SAFE', '2005-09-07',
'Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.
Comment forwarded to vendor.'
);

MySQL常用语句的语法

约定：

|用来指出几个选择中的一个，因此NULL | NOT NULL表示或者给出NULL或者给出NOT NULL。
包含在方括号中的关键字或子句(如[like this])是可选的。

C.1 ALTER TABLE


ALTER TABLE tablename
(
    ADD     column  datatype    [NULL|NOT NULL] [CONSTRAINTS],
    CHANGE  column  columns  datatype    [NULL|NOT NULL] [CONSTRAINTS],
    DROP    column  datatype    [NULL|NOT NULL] [CONSTRAINTS],
    ...
);

C.2 CREATE INDEX


CREATE INDEX indexname ON tablename (column [ASC|DESC], ...);

C.3 CREATE PROCEDURE


CREATE PROCEDURE procedurename([parameters])
BEGIN
...
END;

C.4 CREATE TABLE


CREATE TABLE tablename
(
    column  datatype    [NULL|NOT NULL] [CONSTRAINTS],
    column  datatype    [NULL|NOT NULL] [CONSTRAINTS],
    ...
);

C.5 CREATE USER


CREATE USER username[@hostname] [IDENTIFIED BY [PASSWORD] 'password'];

C.6 CREATE VIEW


CREATE [OR REPLACE] VIEW viewname AS SELECT ...;

C.7 DROP


DROP DATABASE|INDEX|PROCEDURE|TABLE|TRIGGER|USER|VIEW itemname;

C.8 INSERT SELECT


INSERT INTO tablename [(columns,...)] SELECT columns,... FROM tablename1,... [WHERE ...];

C.9 ROLLBACK


ROLBACK [TO savepointname];

C.10 UPDATE


UPDATE tablename SET column=value,... [WHERE ...];

MySQL数据类型

在设计表时，应该特别重视所用的数据类型。使用错误的数据类型可能会严重地影响应用程序的功能和性能。更改包含数据的列不是一件小事（而且这样做可能会导致数据丢失）。

D.1 串数据类型

定长串接受长度固定的字符串，其长度是在创建表时指定的。

变长串存储可变长度的文本。

MySQL处理定长列远比处理变长列快得多。此外，MySQL不允许对变长列（或一个列的可变部分）进行索引。这也会极大地影响性能。

数据类型	说明
CHAR	1~255个字符的定长串。他的长度必须在创建时指定，否则MySQL假定为CHAR(1)
ENUM	接受最多为64K个串组成的一个预定义集合的某个串
TEXT	最大长度为64K的变长文本
LONGTEXT	与TEXT一样，但最大长度为4GB
MEDIUMTEXT	与TEXT一样，但最大长度为16K
TINYTEXT	与TEXT一样，但最大长度为255字节
SET	接受最多64个串组成的一个预定义集合的零个或多个串
VARCHAR	长度可变，最多不超过255字节。如果在创建时指定VARCHAR(n)，则可存储0到n个字符的变长串(其中n<=255)

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

当值不是数值时： 你可能会认为电话号码和邮政编码应该存储在数值字段中（数值字段只存储数值数据），但是，这样做却是不可取的。如果在数值字段中存储邮政编码01234，则保存的将是数值1234，实际上丢失了一位数字。需要遵守的基本规则是：如果数值是计算（求和、平均等）中使用的数值，则应该存储在数值数据类型列中。如果作为字符串（可能只包含数字）使用，则应该保存在串数据类型列中。

D.2 数值数据类型

有符号或无符号： 所有数值数据类型（除BIT和BOOLEAN外）都可以有符号或无符号。有符号数值列可以存储正或负的数值，无符号数值列只能存储正数。默认情况为有符号，但如果你知道自己不需要存储负值，可以使用UNSIGNED关键字，这样做将允许你存储两倍大小的值。

数据类型	说明
TINYINT	整数值，支持-128~127(UNSIGNED时为0~255)
SMALLINT	整数值，支持-32768~32767(UNSIGNED时为0~65535)
MEDIUMINT	整数值，支持-8388608~8388607(UNSIGNED时为0~16777215)
INT(或INTEGER)	整数值，支持-2147483648~2147483647(UNSIGNED时为0~4294967295)
BIGINT	整数值，支持-9223372036854775808~9223372036854775807(UNSIGNED时为0~18446744073709551615)
BOOLEAN(或BOOL)	布尔标志，或者为0或者为1，主要用于开/关(on/off)标志
BIT	位字段，1～64位。(在MySQL 5之前，BIT在功能上等价于TINYINT)
DECIMAL(或DEC)	精度可变的浮点值
DOUBLE	双精度浮点值
FLOAT	单精度浮点值
REAL	4字节的浮点值

不使用引号： 与串不一样，数值不应该括在引号内。

存储货币数据类型： MySQL中没有专门存储货币的数据类型，一般情况下使用DECIMAL(8, 2)。

D.3 日期和时间数据类型

MySQL使用专门的数据类型来存储日期和时间值。

数据类型	说明
DATE	表示1000-01-01~9999-12-31的日期，格式为YYYY-MM-DD
TIME	格式为HH:MM:SS
DATETIME	DATE和TIME的组合
TIMESTAMP	功能和DATETIME相同(但范围较小)
YEAR	用2位数字表示，范围是70(1970年)~69(2069年)，用4位数字表示，范围是1901~2155年

D.4 二进制数据类型

二进制数据类型可存储任何数据（甚至包括二进制信息），如图像、多媒体、字处理文档等。

数据类型	说明
TINYBLOB	Blob最大长度为255字节
BLOB	Blob最大长度为64KB
MEDIUMBLOB	Blob最大长度为16MB
LONGBLOB	Blob最大长度为4GB

MySQL必知必会