前言

这两天把《MySQL必知必会》看完了，书本讲解的内容比较实用，注重命令的教学，对原理的涉及比较少。

这篇博客再回顾、梳理一遍书本，记录一些重点的内容。

2022-07-30

概念

数据库（database）：保存有组织的数据的容器。
数据库软件（dbms）：称为DBMS（数据库管理系统）。数据库是通过DBMS创建和操纵的容器。
表（table）：某种特定类型数据的结构化清单（文件）。每个表都有一个名字，用来标识自己。
模式（schema）：关于数据库和表的布局及特性的信息。这些特性定义了数据如何存储。
列（column）：表中的一个字段。所有表都是由一个或多个列组成的。可以理解为一类信息放在一列，每个列都有相应的数据类型。
行（row）：表中的一个记录（record）。
主键（primary key）：一列（或一组列），其值能够唯一区分表中每个行。任何列都可以作为主键，只要它满足以下条件：
- 任意两行都不具有相同的主键值；
- 每个行都必须具有一个主键值（主键列不允许NULL值）。

主键的最好习惯 除MySQL强制实施的规则外，应该坚持的几个普遍认可的最好习惯为： 
不更新主键列中的值；
不重用主键列的值；
不在主键列中使用可能会更改的值。

SQL:(发音为字母S-Q-L或sequel）是结构化查询语言（Structured Query Language）的缩写。SQL是一种专门用来与数据库通信的语言。
MySQL服务器部分:负责所有数据访问和处理的一个软件。这个软件运行在称为数据库服务器的计算机上。
MySQL客户机部分：是与用户打交道的软件，向服务器作出请求。

基本操作

假设拥有的一个数据库名字为sample。

语句用;结束，SQL语句不区分大小写，USE和use是一样的，一般关键字都用大写，这样易于阅读和调试。

SQL语句可以在一行上给出，也可以分成许多行。多数SQL开发人员认为将SQL语句分成多行更容易阅读和调试。

使用和显示

USE：最初连接到MySQL时，没有任何数据库打开供你使用。在执行任意数据库操作前，需要选择一个数据库。为此，可使用USE关键字（USE sample;）。必须先使用USE打开数据库，才能读取其中的数据。
SHOW DATABASES：SHOW DATABASES;返回可用数据库的一个列表。
SHOW TABLES：为了获得一个数据库内的表的列表，使用SHOW TABLES;。这个数据库是前面USE的数据库。
SHOW COLUMNS：SHOW COLUMNS FROM customers;要求给出一个表名（FROM someTable），它对每个字段返回一行，行中包含字段名、数据类型、是否允许NULL、键信息、默认值以及其他信息。
- DESCRIBE customers;是SHOW COLUMNS FROM customers;的一种快捷方式。
其他SHOW语句（少用）：
- SHOW STATUS，用于显示广泛的服务器状态信息；
- SHOW CREATE DATABASE和SHOW CREATE TABLE，分别用来显示创建特定数据库或表的MySQL语句;
- SHOW GRANTS，用来显示授予用户（所有用户或特定用户）的安全权限；
- SHOW ERRORS和SHOW WARNINGS，用来显示服务器错误或警告消息。

检索

1
2
3

-- 检索单列-- 
SELECT prod_name 
FROM products;

上述语句利用SELECT语句从products表中检索一个名为prod_name的列。所需的列名在SELECT关键字之后给出，FROM关键字指出从其中检索数据的表名。

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

-- 检索多列-- 
SELECT prod_id,prod_price,prod_name 
FROM products;

-- 检索所有列-- 
SELECT *
FROM products;

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

1
2
3

-- 检索不同值（若相同则只返回一次）-- 
SELECT DISTINCT vend_id
FROM products;

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

不能部分使用DISTINCT DISTINCT关键字应用于所有列而不仅是前置它的列。如果给出SELECT DISTINCT vend_id, prod_price，除非指定的两个列都不同，否则所有行都将被检索出来。相当于同时比较两个列，只有一行对应的两个元素都相同才视为真的相同。

-- 限制结果为前面n行-- 
SELECT prod_name
FROM products
LIMIT 5;

-- 限制结果为某个区间-- 
SELECT prod_name
FROM products
LIMIT 5,5;

LIMIT 5指示MySQL返回不多于5行。

LIMIT 5, 5指示MySQL返回从行5开始（位置从0开始计）的5行。两个数字容易搞混，因此有一种代替语法：LIMIT 4 OFFSET 3意为从行3开始取4行，就像LIMIT 3, 4一样。

1
2
3

-- 完全限定名写法，限定某个列是哪个表的（表名也可以限定为哪个数据库的）-- 
SELECT products.prod_name
FROM products;

排序

一般返回的顺序是数据最初添加到表中的顺序。

-- 排序单列-- 
SELECT prod_name
FROM products
ORDER BY prod_name;

ORDER BY指示MySQL对prod_name列以字母顺序排序，通常，ORDER BY子句中使用的列将是为显示所选择的列。但是，实际上并不一定要这样，用非检索的列排序数据是完全合法的。

-- 排序多列-- 
SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY prod_price,prod_name;

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

-- 指定排序方向-- 
SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY prod_price DESC,prod_name;

数据排序不限于升序排序（从A到Z）。这只是默认的排序顺序，还可以使用ORDER BY子句以降序（从Z到A）顺序排序。为了进行降序排序，必须指定DESC关键字。

DESC关键字只应用到直接位于其前面的列名。在上例中，只对prod_price列指定DESC，对prod_name列不指定。因此，prod_price列以降序排序，而prod_name列（在每个价格内）仍然按标准的升序排序。

与DESC相反的关键字是ASC（ASCENDING），在升序排序时可以指定它。但实际上，ASC没有多大用处，因为升序是默认的。

在字典（dictionary）排序顺序中，A被视为与a相同，这是MySQL（和大多数数据库管理系统）的默认行为。如果确实需要改变这种排序顺序，用简单的ORDER BY子句做不到。必须请求数据库管理员的帮助。

-- 应用：找最大值-- 
SELECT prod_price
FROM products
ORDER BY prod_price DESC
LIMIT 1;

prod_price DESC保证行是按照由最昂贵到最便宜检索的，而LIMIT 1告诉MySQL仅返回一行。

在给出ORDER BY子句时，应该保证它位于FROM子句之后。如果使用LIMIT，它必须位于ORDER BY之后。使用子句的次序不对将产生错误消息。因为SELECT-FROM这一对给出了结果，然后再调用ORDER BY 来排序，最后用LIMIT取第一行。

过滤

-- 使用WHERE子句过滤-- 
SELECT prod_name,prod_price
FROM products
WHERE prod_price = 2.50;

这条语句从products表中检索两个列，但不返回所有行，只返回prod_price值为2.50的行。

在同时使用ORDER BY和WHERE子句时，应该让ORDER BY位于WHERE之后，否则将会产生错误（因为先过滤完再排序）

WHERE子句操作符：
- =：等于
- <>：不等于
- ！=：不等于
- <：小于
- <=：小于等于
- >：大于
- >=：大于等于
- BETWEEN：在指定的两个值之间

-- 检查单个值-- 
SELECT prod_name,prod_price
FROM products
WHERE prod_price = 'fuses';

-- 范围检查-- 
SELECT prod_name,prod_price
FROM products
WHERE prod_price <= 10;

SELECT prod_name,prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10; -- 两个值必须用AND分隔开-- 

-- 不匹配检查-- 
SELECT prod_name,vend_id
FROM products
WHERE vend_id <> 1003;

-- 空值检查-- 
SELECT prod_name,prod_price
FROM products
WHERE prod_price IS NULL; -- 注意这里用了IS关键字--

NULL：无值（no value），它与字段包含0、空字符串或仅仅包含空格不同。
NULL与不匹配：在通过过滤选择出不具有特定值的行时，你可能希望返回具有NULL值的行。但是不行。因为未知具有特殊的含义，数据库不知道它们是否匹配，所以在匹配过滤或不匹配过滤时不返回它们。

-- AND操作符-- 
SELECT prod_name,prod_price,prod_id
FROM products
WHERE vend_id = 1003 AND prod_price <= 10;

-- OR操作符-- 
SELECT prod_name,prod_price,prod_id
FROM products
WHERE vend_id = 1003 OR vend_id = 1005;

-- 使用优先级组合-- 
SELECT prod_name,prod_price,prod_id
FROM products
WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price <= 10;

-- 使用圆括号组合-- 
SELECT prod_name,prod_price,prod_id
FROM products
WHERE (vend_id = 1002 OR vend_id) = 1003 AND prod_price <= 10;

AND：用在WHERE子句中的关键字，用来指示检索满足所有给定条件的行。
OR：WHERE子句中使用的关键字，用来表示检索匹配任一给定条件的行。
优先级：SQL（像多数语言一样）在处理OR操作符前，优先处理AND操作符。此问题的解决方法是使用圆括号明确地分组相应的操作符。任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确地分组操作符，它能消除歧义。

-- IN操作符-- 
SELECT prod_name,prod_price,prod_id
FROM products
WHERE vend_id IN (1002,1003);

IN操作符完成与OR相同的功能，IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。IN取合法值的由逗号分隔的清单，全都括在圆括号中。优点：

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

-- NOT操作符-- 
SELECT prod_name,prod_price,prod_id
FROM products
WHERE vend_id NOT IN (1002,1003);

这里的NOT否定跟在它之后的条件，在更复杂的子句中，NOT是非常有用的。在与IN操作符联合使用时，NOT使找出与条件列表不匹配的行非常简单。

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

通配符过滤

通配符（wildcard）：用来匹配值的一部分的特殊字符。
搜索模式（search pattern）：由字面值、通配符或两者组合构成的搜索条件。
百分号**%**代表搜索模式中给定位置的0个、1个或多个字符。
下划线**_**总是匹配一个字符，不能多也不能少。

为在搜索子句中使用通配符，必须使用LIKE操作符。

1
2
3

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

此例子使用了搜索模式**’jet%’。在执行这条子句时，将检索任意以jet起头的词。%告诉MySQL接受jet之后的任意字符**，不管它有多少字符。

1
2
3

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

搜索模式**’%anvil%’表示匹配任何位置包含文本anvil**的值，而不论它之前或之后出现什么字符。

1
2
3

SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE 's%e';

这个例子找出以s起头以e结尾的所有产品。

-- 使用_通配符-- 
SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE '_ ton anvil';

尾空格可能会干扰通配符匹配。例如，在保存词anvil 时，如果它后面有一个或多个空格，则子句WHERE prod_name LIKE '%anvil'将不会匹配它们，因为在最后的l后有多余的字符。解决这个问题的一个简单的办法是在搜索模式最后附加一个%。一个更好的办法是使用函数去掉首尾空格。
注意NULL：虽然似乎%通配符可以匹配任何东西，但有一个例外，即NULL。即使是WHERE prod_name LIKE ‘%’也不能匹配用值NULL作为产品名的行。

使用通配符是有代价的：通配符搜索的处理一般要比前面讨论的其他搜索所花时间更长。技巧：

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

正则表达式

-- REGEXP关键字-- 
SELECT prod_id,prod_name
FROM products
WHERE prod_name REGEXP '1000';

这个语句检索列prod_name包含文本1000的所有行（注意，用LIKE要使用通配符才能检查’’包含’’），关键字LIKE被REGEXP代替，它告诉MySQL：REGEXP后所跟的东西作为正则表达式（与文字正文1000匹配的一个正则表达式）处理。

-- REGEXP关键字-- 
SELECT prod_id,prod_name
FROM products
WHERE prod_name REGEXP '.000';

这里使用了正则表达式.000。**.是正则表达式语言中一个特殊的字符。它表示匹配任意一个字符**，因此，1000和2000都匹配且返回。

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

-- OR匹配-- 
SELECT prod_id,prod_name
FROM products
WHERE prod_name REGEXP '1000|2000';

语句中使用了正则表达式1000|2000。|为正则表达式的OR操作符。它表示匹配其中之一，因此1000和2000都匹配并返回。可以有两个以上的OR条件。

-- 匹配几个字符之一-- 
SELECT prod_id,prod_name
FROM products
WHERE prod_name REGEXP '[123] Ton';

这里，使用了正则表达式[123] Ton。[123]定义一组字符，它的意思是匹配1或2或3，因此，1 ton和2 ton都匹配且返回（没有3 ton）。

[]是另一种形式的OR语句。事实上，正则表达式[123]Ton为[1|2|3]Ton的缩写，也可以使用后者。但是，需要用[]来定义OR语句查找什么。如果直接用'1|2|3 Ton'，MySQL则假定你的意思是’1’或’2’或’3 ton’。

字符集合也可以被否定，即，它们将匹配除指定字符外的任何东西。为否定一个字符集，在集合的开始处放置一个^即可。因此，尽管[123]匹配字符1、2或3，但**[^123]**却匹配除这些字符外的任何东西。
如果要匹配数字，则为集合[0123456789]，一种简化的写法为[0-9]（用’-‘定义一个范围）。范围不限于完整的集合，[1-3]和[6-9]也是合法的范围。此外，范围不一定只是数值的，[a-z]匹配任意字母字符。
如果要匹配’.’，不能直接REGEXP ‘.’，因为这会匹配任意一个字符。需要用两个反斜杠转义，即在特殊字符前加\\。如\\.。
- 为了匹配反斜杠（\）字符本身，需要使用\\\。
- MySQL要求两个反斜杠（MySQL自己解释一个，正则表达式库解释另一个）。
- \\也用来引用元字符，如\\f表示换页。

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

[:alnum:] ：任意字母和数字（同[a-zA-Z0-9]）
[:alpha:] ：任意字符（同[a-zA-Z]）
[:blank:] ：空格和制表（同[\t]）
[:cntrl:] ：ASCII控制字符（ASCII 0到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]）

字符汇总

.：匹配任意一个字符
|：表示OR（条件或）
[]：另一种形式的OR语句，来定义OR语句查找什么
^：否定
-：定义范围
\\：转义
[: :]：前面所示的一些字符类
*：0个或多个匹配，置于某字符后
+：1个或多个匹配（等于{1,}），置于某字符后
？：0个或1个匹配（等于{0,1}），置于某字符后
{n}：指定数目的匹配，置于某字符后
{n,}：不少于指定数目的匹配，置于某字符后
{n,m}：匹配数目的范围（m不超过255），置于某字符后
^：文本的开始（双重用途），放在串的开头
$：文本的结尾，放在串的结尾
[[:<:]]：词的开始
[[:>:]]：词的结尾

^的双重用途：^有两种用法。在集合中（用[和]定义），用它来否定该集合，否则，用来指串的开始处，如^[0-9\\.]（后面有例子）。

-- 使用？-- 
SELECT prod_id,prod_name
FROM products
WHERE prod_name REGEXP '\\([0-9] sticks?\\)';

正则表达式\$[0-9] sticks?\$需要解说一下。\$匹配(，[0-9]匹配任意数字，sticks?匹配stick和sticks（s后的?使s可选，因为?匹配它前面的任何字符的0次或1次出现），\$匹配)。没有?，匹配stick和sticks会非常困难。

-- 匹配连在一起的4位数字-- 
SELECT prod_id,prod_name
FROM products
WHERE prod_name REGEXP '[:digit:]{4}';

[:digit:]匹配任意数字，因而它为数字的一个集合。{4}确切地要求它前面的字符（任意数字）出现4次。

如果想找出以一个数（包括以小数点开始的数）开始的所有产品，怎么办？简单搜索[0-9\\.]（或[[:digit:]\\.]）不行，因为它将在文本内任意位置查找匹配（正则是查找”包含“）。解决办法是使用^定位符。

1
2
3

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

^匹配串的开始。因此，^[0-9\\.]只在.或任意数字为串中第一个字符时才匹配它们。

使REGEXP起类似LIKE的作用：前面说过，LIKE和REGEXP的不同在于，LIKE匹配整个串而REGEXP匹配子串。利用定位符，通过用^开始每个表达式，用$结束每个表达式，可以使REGEXP的作用与LIKE一样。

进阶操作

创建计算字段

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

-- 拼接字段-- 
SELECT Concat(vend_name,'(',vend_country,')')
FROM vendors
ORDER BY vend_name;

Concat()拼接串，即把多个串连接起来形成一个较长的串。Concat()需要一个或多个指定的串，各个串之间用逗号分隔。

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

-- 删除右侧多余空格-- 
SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')')
FROM vendors
ORDER BY vend_name;

RTrim()函数去掉值右边的所有空格。通过使用RTrim()，各个列都进行了整理。MySQL除了支持RTrim()（正如刚才所见，它去掉串右边的空格），还支持LTrim()（去掉串左边的空格）以及Trim()（去掉串左右两边的空格）。

-- 使用别名-- 
SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')') AS vend_title
FROM vendors
ORDER BY vend_name;

计算字段之后跟了文本AS vend_title。它指示SQL创建一个包含指定计算的名为vend_title的计算字段。别名有时也称为导出列（derived column）。

-- 算术计算-- 
SELECT prod_id,quantity,item_price,quantity*item_price AS expanded_price
FROM orderitems
WHERE order_num = 20005;

输出中显示的expanded_price列为一个计算字段，此计算为quantity*item_price。客户机应用现在可以使用这个新计算列，就像使用其他列一样。

测试计算：SELECT提供了测试和试验函数与计算的一个很好的办法。虽然SELECT通常用来从表中检索数据，但可以省略FROM子句以便简单地访问和处理表达式。例如，SELECT 3*2;将返回6，SELECT Trim(‘abc’);将返回abc，而SELECT Now()利用Now()函数返回当前日期和时间。通过这些例子，可以明白如何根据需要使用SELECT进行试验。

使用函数

文本处理函数

RTrim(str)：去掉串尾的空格来整理数据。
Right(str)：返回串右边的字符。
Upper(str)：将文本转换为大写并返回。
Lower(str)：将文本转换为小写并返回。
Length(str)：返回串的长度。
Locate(substr,str)：substr待查找的子串，str待查找的串。如果 substr 不在 str 中返回 0。
Locate(substr,str,pos)：返回子串 substr 在字符串 str 中的第 pos 位置后第一次出现的位置。如果 substr 不在 str 中返回 0。
Position(substr IN str)：返回substr在str中第一次出现的位置。
SubString(str,pos)：返回从第pos位置出现的子串的字符。
substring(str, pos, len)：substring(str, pos, len)。作用：返回从pos位置开始长度为len的子串的字符。

Soundex(str)：返回串的SOUNDEX值。

SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似的发音字符和音节，使得能对串进行发音比较而不是字母比较。

```mysql
– 匹配Y.Lee与Y.Lie–
SELECT cust_name,cust_contact
FROM customers
WHERE Soundex(cust_contact) = Soundex(‘Y.Lie’);


    

### 日期和时间处理函数

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

需要注意的是MySQL使用的日期格式。无论你什么时候指定一个日期，不管是插入或更新表值还是用WHERE子句进行过滤，日期必须为格式yyyy-mm-dd。因此，2005年9月1日，给出为2005-09-01。虽然其他的日期格式可能也行，但这是首选的日期格式，因为它排除了多义性。

因为一个记录里面的日期格式可能不一样，比如有可能这个日期还包括**当天时间**，这样就需要指示MySQL**仅将给出的日期与列中的日期部分进行比较**，而不是将给出的日期与整个列值进行比较。为此，必须使用Date()函数。Date(order_date)指示MySQL仅提取列的日期部分。

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

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

-- 检索某月1:需要知道一个月有多少天-- 
SELECT cust_id,order_num
FROM orders
WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';

-- 检索某月2-- 
SELECT cust_id,order_num
FROM orders
WHERE Year(order_date)=2005 ANd Month(order_date)=9;

数值处理函数

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

聚焦函数（汇总数据）

利用标准的算术操作符，所有聚集函数都可用来执行多个列上的计算（如两个列乘法，取平均、最大、求和）。这些函数是高效设计的，它们返回结果一般比在自己的客户机应用程序中计算要快得多。

AVG() ：返回某列的平均值，只能用于特定列，忽略NULL的行。
COUNT() ：返回某列的行数，指定列忽略NULL，count(*)包含NULL。
MAX() ：返回某列的最大值，忽略NULL，可用于数值、日期、文本。
MIN() ：返回某列的最小值，忽略NULL，可用于数值、日期、文本。
SUM() ：返回某列值之和，忽略NULL。

-- 整体AVG()-- 
SELECT AVG(prod_price) AS avg_price
FROM products;
-- 部分AVG()-- 
SELECT AVG(prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;

-- 所有COUNT()-- 
SELECT COUNT(*) AS num_cust
FROM customers;
-- 指定COUNT()-- 
SELECT COUNT(cust_email) AS num_cust
FROM customers;

-- MAX()-- 
SELECT MAX(prod_price) AS max_price
FROM products;

-- MIN()-- 
SELECT MIN(prod_price) AS min_price
FROM products;

-- 多列算术SUM()-- 
SELECT SUM(item_price*quantity) AS total_price
FROM orderitems
WHERE order_num = 20005;

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

-- 所有聚焦函数都可用DISTINCT-- 
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;

如果指定列名，则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*)，因此不允许使用COUNT（DISTINCT），否则会产生错误。类似地，DISTINCT必须使用列名，不能用于计算或表达式。
虽然DISTINCT从技术上可用于MIN()和MAX()，但这样做实际上没有价值。一个列中的最小值和最大值不管是否包含不同值都是相同的。

-- 组合-- 
SELECT COUNT(*) AS num_items,
	   MIN(prod_price) AS price_min,
	   MAX(prod_price) AS price_max,
	   AVG(prod_price) AS price_avg,
FROM products;

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

分组

1
2
3

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

上面的SELECT语句指定了两个列，vend_id包含产品供应商的ID，num_prods为计算字段（用COUNT(*)函数建立）。GROUP BY子句指示MySQL按vend_id排序并分组数据，然后对每个组进行聚焦。这导致对每个vend_id而不是整个表计算num_prods一次。

使用ROLLUP，可以把NULL也分组，否则不会。

1
2
3

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

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

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

-- 过滤分组-- 
SELECT cust_id,COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*)>=2;

WHERE过滤指定的是行而不是分组。事实上，WHERE没有分组的概念。HAVING非常类似于WHERE。事实上，目前为止所学过的所有类型的WHERE子句都可以用HAVING来替代。唯一的差别是WHERE过滤行，而HAVING过滤分组。

这条SELECT语句的前3行类似于上面的语句。最后一行增加了HAVING子句，它过滤COUNT(*) >=2（两个以上的订单）的那些分组。

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

-- 都使用-- 
SELECT cust_id,COUNT(*) AS orders
FROM orders
WHERE prod_price>=10
GROUP BY cust_id
HAVING COUNT(*)>=2;

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

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

SELECT子句及其顺序：

SELECT -- > FROM -- > WHERE -- > GROUP BY -- > HAVING -- > ORDER BY -- > LIMIT

子查询

查询（query）：任何SQL语句都是查询。但此术语一般指SELECT语句。
递归的子查询将下层的查询结果返回给上层利用，一般在WHERE子句里通过IN利用。
迭代的子查询将上层第一次迭代的结果拿来给自身的循环利用。

-- 嵌套子查询，相当于递归-- 
SELECT cust_name,cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
                  FROM orders
                  WHERE order_num IN (SELECT order_num
                                      FROM orderitems
                                      WHERE prod_id = 'TNT2'));

列必须匹配：在WHERE子句中使用子查询（如这里所示），应该保证SELECT语句具有与WHERE子句中相同数目的列。通常，子查询将返回单个列并且与单个列匹配，但如果需要也可以使用多个列。
虽然子查询一般与IN操作符结合使用，但也可以用于测试等于（=）、不等于（<>）等。

-- 在SELECT上子查询，迭代的，相当于多层循环-- 
SELECT cust_name,cust_state,(SELECT COUNT(*)
                             FROM orders
                             WHERE orders.cust_id=customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;

每次外部执行一次，得到一个id，用此id去orders里面遍历一次查询来次数。

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

联结

基础联结

外键（foreign key）：外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。
关于外键的作用、优缺点，参考(27条消息) 数据库外键的使用和原则_守枫竹清的博客-CSDN博客_数据库外键

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

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

-- 创建联结-- 
SELECT vend_name,prod_name,prod_price
FROM vendors,products  -- 多个表-- 
WHERE vendors.vend_id = products.vend_id  -- 联结-- 
ORDER BY vend_name,prod_name;

上面例子的步骤是：先检索第一项vend_name的一项（对应一个id），然后发现接下来是另一个表的内容（实际上在表检索的过程有遍历的先后次序，是嵌套的，具体是看FROM哪个表先，两个表是n*n的复杂度），则在这个表中检索一遍，匹配id。WHERE子句指示MySQL匹配vendors表中的vend_id和products表中的vend_id。如果没有WHERE，则对vend_name的一项都对应另一个表的所有项。

笛卡儿积（cartesian product）：由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。也即第一个表的一项都匹配了第二个表的所有项（这里可以看出实质上是嵌套遍历的）。

-- 另一种写法，内部联结-- 
SELECT vend_name,prod_name,prod_price
FROM vendors INNER JOIN products  -- 多个表，指明了嵌套关系-- 
ON vendors.vend_id = products.vend_id  -- 联结条件-- 
ORDER BY vend_name,prod_name;

两个表之间的关系是FROM子句的组成部分，以INNER JOIN指定。在使用这种语法时，联结条件用特定的ON子句而不是WHERE子句给出。

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

INNER JOIN嵌套语法

INNER JOIN 连接两个数据表的用法：
SELECT * FROM 表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号

INNER JOIN 连接三个数据表的用法：
SELECT * FROM (表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号

INNER JOIN 连接四个数据表的用法：
SELECT * FROM ((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON 表1.字段号=表4.字段号

......

-- 联结多个表-- 
SELECT prod_name,vend_name,prod_price,quantity
FROM orderitems,products,vendors
WHERE products.vend_id = vendors.vend_id AND orderitems.prod_id = products.prod_id AND order_num = 20005;

-- INNER JOIN写法-- 
SELECT prod_name,vend_name,prod_price,quantity
FROM （orderitems INNER JOIN products ON orderitems.prod_id = products.prod_id) INNER JOIN vendors 
  		         ON orderitems.prod_id = vendors.vend_id
WHERE orderitems.order_num = 20005;

-前面的子查询-- 
SELECT cust_name,cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
                  FROM orders
                  WHERE order_num IN (SELECT order_num
                                      FROM orderitems
                                      WHERE prod_id = 'TNT2'));
                                      
-- 改用联结-- 
SELECT cust_name,cust_contact
FROM customers,orders,orderitems
WHERE customers.cust_id = orders_cust_id AND orderitems.order_num = orders.order_num AND prod_id = 'TNT2';

子查询的意思是从orderitems表查到order_num，然后去orders表根据这个order_num查找cust_id，然后再去customers找name和contact。

而使用联结，则先在customers表查第一项，得到id，然后去orders表查到这个id的项获得num，然后去orderitems表根据num获得prod_id，检查是不是’TNT2’，是的话算一个结果，然后继续下一项，就相当于循环迭代。

实际上这两个方法的核心都是：给定的索引与目标记录不在一个表中。那么可以递归不断换个表，也可以更清晰地用外键联结这几个表。

高级联结

使用别名，能够在单条SELECT语句中多次使用相同的表，并缩短语句长度。

-- 使用别名-- 
SELECT cust_name,cust_contact
FROM customers AS c,orders AS o,orderitems AS oi
WHERE c.cust_id = o.cust_id AND oi.order_num = o.order_num AND prod_id = 'TNT2';

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

-- 子查询-- 
SELECT prod_id,prod_name
FROM products
WHERE vend_id = (SELECT vend_id
                 FROM products
                 WHERE prod_id = 'DTNTR');
                 
-- 自联结-- 
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';
-- 从p2中找到一条记录，从p1中找到多条记录。虽然是相同的表，但每个实例执行的条件不一样。从循环迭代的角度看，条件有传递的感觉--

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

自然联结是这样一种联结，只能选择那些唯一的列。这一般是通过对表使用通配符（SELECT *），对所有其他表的列使用明确的子集来完成的。事实上，这里迄今为止建立的每个内部联结都是自然联结，很可能我们永远都不会用到不是自然联结的内部联结。
外部联结联结包含了那些在相关表中没有关联行的行，使用OUTER JOIN关键字。在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）。

-- 外部联结-- 
SELECT customers.cust_id,orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;

上面这条语句会把所有的cust_id都打印，因为使用的LEFT关键字指定了customers的表，而右边order_num是不是NULL都会打印出来。

-- 聚焦函数与联结-- 
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;

-- 对客户表，联结订单表。-- 
-- 对客户分组，然后对于每个客户通过联结条件匹配订单表的对应客户，接着计算匹配的订单数量--

组合查询

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

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

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

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

-- 组合查询-- 
SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price<=5  -- 注意，这条语句没有分号-- 
UNION
SELECT vend_id,prod_id,prod_price
FROM products
WHERE vend_id IN (1001,1002);

这条语句由前面的两条SELECT语句组成，语句中用UNION关键字分隔。UNION指示MySQL执行两条SELECT语句，并把输出组合成单个查询结果集。

并是非常容易使用的。但在进行并时有几条规则需要注意:

UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合4条SELECT语句，将要使用3个UNION关键字）。
UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。
列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。

UNION从查询结果集中自动去除了重复的行，这是UNION的默认行为，但是如果需要，可以改变它。事实上，如果想返回所有匹配行，可使用UNION ALL而不是UNION。

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

全文本搜索

1
2

并非所有引擎都支持全文本搜索 
MySQL支持几种基本的数据库引擎。并非所有的引擎都支持本书所描述的全文本搜索。两个最常使用的引擎为MyISAM和InnoDB，前者支持全文本搜索，而后者不支持。这就是为什么虽然本书中创建的多数样例表使用 InnoDB ，而有一个样例表（productnotes表）却使用MyISAM的原因。如果你的应用中需要全文本搜索功能，应该记住这一点。

FULLTEXT

一般在创建表时启用全文本搜索。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;

这些列中有一个名为note_text的列，为了进行全文本搜索，MySQL根据子句FULLTEXT(note_text)的指示对它进行索引。这里的FULLTEXT索引单个列，如果需要也可以指定多个列。不要在导入数据时使用FULLTEXT ，要花很多时间。

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

1
2
3

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

此SELECT语句检索单个列note_text。由于WHERE子句，一个全文本搜索被执行。Match(note_text)指示MySQL针对指定的列进行搜索，Against(‘rabbit’)指定词rabbit作为搜索文本。由于有两行包含词rabbit，这两个行被返回。传递给 Match() 的值必须与FULLTEXT()定义中的相同。如果指定多个列，则必须列出它们（而且次序正确）。

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

文本中词靠前的行的等级值比词靠后的行的等级值高。
如果指定多个搜索项，则包含多数匹配词的那些行将具有比包含较少词（或仅有一个匹配）的那些行高的等级值。

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

-- 查询扩展-- 
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);

利用查询扩展，能找出可能相关的结果，即使它们并不精确包含所查找的词。比如某一行x确实包含词anvils，另一行不包含，但这行包含了行x中的几个词，那么也有可能被检索出来。

布尔模式

以布尔方式，可以提供关于如下内容的细节：

要匹配的词；
要排斥的词（如果某行包含这个词，则不返回该行，即使它包含其他指定的词也是如此）；
排列提示（指定某些词比其他词更重要，更重要的词等级更高）；
表达式分组；
另外一些内容。

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

-- 布尔模式-- 
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy' IN BOOLEAN MODE);

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

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

-- 布尔模式-- 
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);

这一次仍然匹配词heavy，但-rope*明确地指示MySQL排除包含rope*（任何以rope开始的词，包括ropes）的行。

布尔操作符：

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

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

-- 匹配包含rabbit、bait至少一个词的行，这种叫单词列表-- 
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit bait' IN BOOLEAN MODE);

-- 匹配包含词rabbit、bait的行-- 
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE);

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

说明

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

数据

插入行

INSERT语句一般不会产生输出。有两种方式

不管使用哪种INSERT语法，都必须给出VALUES的正确数目。如果不提供列名，则必须给每个表列提供一个值。如果提供列名，则必须对每个列出的列给出一个值。如果不这样，将产生一条错误消息，相应的行插入不成功。

-- 简单的方式-- 
INSERT INTO customers
VALUES('Pep E. LaPew',
      '100 Main Street',
      'Los Angeles',
      'CA',
      '90046',
      'USA',
      NULL,
      NULL);

-- 更安全、建议的插入方式-- 
INSERT INTO customers(cust_name,
                     cust_address,
                     cust_city,
                     cust_state,
                     cust_zip,
                     cust_country,
                     cust_contact,
                     cust_email)
VALUES('Pep E. LaPew',
      '100 Main Street',
      'Los Angeles',
      'CA',
      '90046',
      'USA',
      NULL,
      NULL);

在表名后的括号里明确地给出了列名。在插入行时，MySQL将用VALUES列表中的相应值填入列表中的对应项。VALUES中的第一个值对应于第一个指定的列名。第二个值对应于第二个列名，如此等等。

存储到每个表列中的数据在VALUES子句中给出，对每个列必须提供一个值。如果某个列没有值（如上面的cust_contact和cust_email列），应该使用NULL值（假定表允许对该列指定空值）。

cust_id可以不填也可以为NULL。这是因为每次插入一个新行时，该列由MySQL自动增量。如果某一列要省略，必须满足：该列定义为允许NULL，或者在表定义时给出了默认值。

INSERT可能会降低等待处理的SELECT语句的性能，如果数据检索是重要的，那么可以在INSERT和INTO之间添加关键字LOW_PRIORITY，指示MySQL降低INSERT语句的优先级，如下所示：INSERT LOW_PRIORITY INTO。

-- 插入多个行-- 
INSERT INTO customers(cust_name,
                     cust_address,
                     cust_city,
                     cust_state,
                     cust_zip,
                     cust_country,
                     cust_contact,
                     cust_email)
VALUES('Pep E. LaPew',
      '100 Main Street',
      'Los Angeles',
      'CA',
      '90046',
      'USA',
      NULL,
      NULL),
     ('Lep E. PaPew',
      '100 Main Street',
      'Los Angeles',
      'CA',
      '90046',
      'USA',
      NULL,
      NULL);

只要每条INSERT语句中的列名（和次序）相同，可以一次插入多个行。其中单条INSERT语句有多组值，每组值用一对圆括号括起来，用逗号分隔。

-- 插入检索出来的行-- 
INSERT INTO customers(cust_id,
                     cust_name,
                     cust_address,
                     cust_city,
                     cust_state,
                     cust_zip,
                     cust_country,
                     cust_contact,
                     cust_email)
SELECT cust_id,
       cust_name,
       cust_address,
       cust_city,
       cust_state,
       cust_zip,
       cust_country,
       cust_contact,
       cust_email
FROM custnew;

这个例子使用INSERT SELECT从custnew中将所有数据导入customers。SELECT语句从custnew检索出要插入的值，而不是列出它们。SELECT中列出的每个列对应于customers表名后所跟的列表中的每个列。这条语句将插入多少行有赖于custnew表中有多少行。如果这个表为空，则没有行被插入（也不产生错误，因为操作仍然是合法的）。如果这个表确实含有数据，则所有数据将被插入到customers。

为简单起见，这个例子在INSERT和SELECT语句中使用了相同的列名。但是，不一定要求列名匹配。事实上，MySQL甚至不关心SELECT返回的列名，它使用的是列的位置。

更新行

UPDATE语句由3部分组成，分别是：

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

实际上，UPDATE是选定某列更新，不过用WHERE来选中某些行。

1
2
3

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

UPDATE语句总是以要更新的表的名字开始。在此例子中，要更新的表的名字为customers。SET命令用来将新值赋给被更新的列。如这里所示，SET子句设置cust_email列为指定的值：SET cust_email = 'elmer@fudd.com'。

UPDATE语句以WHERE子句结束，它告诉MySQL更新哪一行。没有WHERE子句，MySQL将会用这个电子邮件地址更新customers表中所有行。

-- 更新多个列的行-- 
UPDATE customers
SET cust_email = 'elmer@fudd.com',
	cust_name = 'The Fudds'
WHERE cust_id = 10005;

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

IGNORE关键字：如果用UPDATE语句更新多行，并且在更新这些行中的一行或多行时出一个现错误，则整个UPDATE操作被取消（错误发生前更新的所有行被恢复到它们原来的值）。为即使是发生错误，也继续进行更新，可使用IGNORE关键字，如下所示： UPDATE IGNORE customers… 。

1
2
3

-- 删除某列，设置为NULL-- 
UPDATE customers
SET cust_email = NULL;

没有WHERE子句则更新所有行。

删除行

DELETE直接删除一整行，不能选择列。

1 2	DELETE FROM customers WHERE cust_id = 10006;

这条语句很容易理解。DELETE FROM要求指定从中删除数据的表名。WHERE子句过滤要删除的行。在这个例子中，只删除客户10006。如果省略WHERE子句，它将删除表中每个客户。

DELETE语句从表中删除行，甚至是删除表中所有行。但是，DELETE不删除表本身。

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

MySQL没有撤销（undo）按钮。应该非常小心地使用UPDATE和DELETE，否则你会发现自己更新或删除了错误的数据。

创建表

在创建新表时，指定的表名必须不存在。

CREATE TABLE customers
(
 cust_id      int          NOT NULL AUTO_INCREMENT, -- 自动增量-- 
 cust_name    char(10)     NOT NULL,
 cust_address char(50)     NOT NULL, -- 不允许NULL-- 
 cust_city    char(50)     NULL, -- 允许NULL-- 
 ...
 PRIMARY KEY(cust_id)
)ENGINE=MyISAM;

表名紧跟在CREATE TABLE关键字后面。实际的表定义（所有列）括在圆括号之中。各列之间用逗号分隔。每列的定义以列名（它在表中必须是唯一的）开始，后跟列的数据类型。表的主键可以在创建表时用PRIMARY KEY关键字指定。这里，列cust_id指定作为主键列。忽略ENGINE时，整条语句由右圆括号后的分号结束。

NULL为默认设置，如果不指定NOT NULL，则认为指定的是NULL。不允许NULL值的列不接受该列没有值的行。

-- 多个主键列-- 
CREATE TABLE customers
(
 cust_id      int          NOT NULL AUTO_INCREMENT, -- 自动增量-- 
 cust_name    char(10)     NOT NULL,
 cust_address char(50)     NOT NULL, -- 不允许NULL-- 
 cust_city    char(50)     NULL, -- 允许NULL-- 
 ...
 PRIMARY KEY(cust_id,cust_name)
)ENGINE=MyISAM;

为创建由多个列组成的主键，应该以逗号分隔的列表给出各列名。如果主键使用单个列，则它的值必须唯一。如果使用多个列，则这些列的组合值必须唯一。主键中只能使用不允许NULL值的列。允许NULL值的列不能作为唯一标识。

每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（如，通过使它成为主键）。要指定某个自动增量的值，可以简单地在INSERT语句中指定一个值，只要它是唯一的（至今尚未使用过）即可，该值将被用来替代自动生成的值。后续的增量将开始使用该手工插入的值。
让MySQL生成（通过自动增量）主键的一个缺点是你不知道这些值都是谁。如何在使用AUTO_INCREMENT列时获得这个值呢？可使用last_insert_id()函数获得这个值：SELECT last_insert_id()。此语句返回最后一个AUTO_INCREMENT值，然后可以将它用于后续的MySQL语句。

-- 指定默认值-- 
CREATE TABLE customers
(
 cust_id      int          NOT NULL AUTO_INCREMENT, -- 自动增量-- 
 cust_name    char(10)     NOT NULL DEFAULT 'xiaoming', -- 有默认值-- 
 cust_address char(50)     NOT NULL, -- 不允许NULL-- 
 cust_city    char(50)     NULL, -- 允许NULL-- 
 ...
 PRIMARY KEY(cust_id,cust_name)
)ENGINE=MyISAM;

MySQL不允许使用函数作为默认值，它只支持常量。
许多数据库开发人员使用默认值而不是NULL列，特别是对用于计算或数据分组的列更是如此。

如果省略ENGINE=语句，则使用漠人引擎（很可能是MyISAM），不同表可以使用不同的引擎类型，但外键不能跨引擎。引擎类型：

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

注：创建表为避免名称重复，可以用CREATE TABLE IF NOT EXISTS。

更新表

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

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

-- 更新表，添加一个列-- 
ALTER TABLE vendors
ADD vend_phone CHAR(20); -- 默认NULL-- 

-- 更新表，删除一个列-- 
ALTER TABLE vendors
DROP COLUMN vend_phone;

-- 常见用途：定义外键-- 
ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_orders
FOREIGN KEY (order_num) REFERENCES orders(order_num);

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

删除和重命名表

-- 删除表-- 
DROP TABLE customers;

-- 重命名表-- 
RENAME TABLE customers TO customers2;

技巧

使用视图

视图就是把类似SELECT语句的结果保存（但不是真实的保存了数据），在以后需要这些数据时直接用这个视图不用重新SELECT。因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任一个检索。如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降得很厉害。

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

-- 创建视图-- 
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;

-- 使用视图-- 
SELECT cust_name,cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';

视图还可以用来格式化检索的结果，加入经常需要某个格式的结果。不必在每次需要时执行一些操作。

-- 具有某种格式的视图-- 
CREATE VIEW vendorlocations AS
SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')') AS vend_title
FROM vendors
ORDER BY vend_name;

也可以用来过滤某些数据

-- 具有过滤功能的视图-- 
CREATE VIEW customeremaillist AS
SELECT cust_id,cust_name,cust_email
FROM customers
WHERE cust_email IS NOT NULL;

视图与计算字段

-- 包含计算字段的视图-- 
CREATE VIEM orderitemsexpanded AS 
SELECT prod_id,quantity,item_price,quantity*item_price AS expanded_price
FROM orderitems;

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

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

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

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

使用存储过程

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

使用存储过程的一些理由，换句话说，使用存储过程有3个主要的好处，即简单、安全、高性能：

通过把处理封装在容易使用的单元中，简化复杂的操作。
由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果所有开发人员和应用程序都使用同一（试验和测试）存储过程，则所使用的代码都是相同的。由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果所有开发人员和应用程序都使用同一（试验和测试）存储过程，则所使用的代码都是相同的。
简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。这一点的延伸就是安全性。通过存储过程限制对基础数据的访问减少了数据讹误（无意识的或别的原因所导致的数据讹误）的机会。
提高性能。因为使用存储过程比使用单独的SQL语句要快。
存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。

-- 创建存储过程，无参数-- 
CREATE PROCEDURE productpricing()
BEGIN
	SELECT Avg(prod_price) AS priceaverage
	FROM products; -- 注意这里有个分号，直接这样在命令行写会导致语句到这里就停止，有错误-- 
END;

-- 使用-- 
CALL productpricing();

-- 删除，如果不存在将产生错误-- 
DROP PROCEDURE productpricing; -- 注意没有()-- 

-- 删除，如果不存在也不会产生错误-- 
DROP PROCEDURE productpricing IF EXISTS；

对于mysql命令行程序：

默认的MySQL语句分隔符为;（正如你已经在迄今为止所使用的MySQL语句中所看到的那样）。mysql命令行实用程序也使用;作为语句分隔符。如果命令行实用程序要解释存储过程自身内的;字符，则它们最终不会成为存储过程的成分，这会使存储过程中的SQL出现句法错误。解决办法是临时更改命令行实用程序的语句分隔符，如下所示（delimiter是分隔符的意思）：

DELIMITER //

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

DELIMITER ;

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

-- 有参数的存储过程-- 
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;

-- 使用-- 
CALL productpricing(@pricelow,@pricehigh,@priceaverage); -- 调用，但不返回结果，而是保存在变量里--

这个存储过程执行三条语句，接受3个参数，pl存储产品最低价格，ph存储产品最高价格，pa存储产品平均价格。每个参数必须具有指定的类型，这里使用十进制值。关键字OUT指出相应的参数用来从存储过程传出一个值（返回给调用者），关键字IN用来给存储过程传入一个值。通过指定INTO关键字，把检索的值保存到相应的变量。

记录集不是允许的类型，因此不能通过一个参数返回多个行和列。这就是前面的例子为什么要使用3个参数（和3 条SELECT语句）的原因。

变量名：所有MySQL变量都必须以@开始。当传入这样一个@开始的变量时，会自动创建并保存。
然后可以检索这个变量：
1
SELECT @priceaverage;

-- 有传入参数 --  
CREATE PROCEDURE ordertotal(
	IN onumber INT,
	OUT ototal DECIMAL(8,2)
)
BEGIN
	SELECT SUM(item_price*quantity)
	FROM orderitems
	WHERE order_num = onumber
	INTO ototal; -- 检索结果放入变量-- 
END;

-- 使用 -- 
CALL ordertotal(20005,@total);
SELECT @total;

onumber定义为IN，因为订单号被传入存储过程。ototal定义为OUT，因为要从存储过程返回合计。

-- 一个复杂的例子--
CREATE PROCEDURE ordertotal(
	IN onumber INT,
	IN taxable BOOLEAN,
	OUT ototal DECIMAL(8,2)
)COMMENT 'obtain order total, optionally adding tax'
BEGIN
	DECLARE total DECIMAL(8,2); -- 声明局部变量
	DECLARE taxrate INT DEFAULT 6; -- 声明税率，默认为6%
	
	SELECT SUM(item_price*quantity)
	FROM orderitems
	WHERE order_num = onumber
	INTO total;
	
	IF taxtable THEN
		SELECT total+(total/100*taxrate) INTO total; -- 使用SELECT把计算式的结果检索，然后放入变量
	END IF;
	
	SELECT total INTO ototal; -- 实际上，这个total局部变量有点没必要
END;

--使用--
CALL ordertotal(20005,0,@total);
SELECT @total;

添加了另外一个参数taxable，它是一个布尔值（如果要增加税则为真，否则为假）。在存储过程体中，用DECLARE语句定义了两个局部变量。DECLARE要求指定变量名和数据类型，它也支持可选的默认值（这个例子中的taxrate的默认被设置为6%）。IF语句检查taxable是否为真，如果为真，则用另一SELECT语句增加营业税到局部变量total。

COMMENT关键字：本例子中的存储过程在CREATE PROCEDURE语句中包含了一个COMMENT值。它不是必需的，但如果给出，将在SHOW PROCEDURE STATUS的结果中显示。
IF语句：这个例子给出了MySQL的IF语句的基本用法。IF语句还支持ELSEIF和ELSE子句（前者还使用THEN子句，后者不使用）。在以后章节中我们将会看到IF的其他用法（以及其他流控制语句）。

-- 检查CREATE创建存储过程时用到的语句--
SHOW CREATE PROCEDURE ordertotal;

-- 获取何时、由谁创建等详细信息的存储过程列表--
SHOW PROCEDURE STATUS;

-- 限制状态结果，使用LIKE过滤--
SHOW PROCEDURE STATUS LIKE 'ordertotal';

使用游标

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

游标用DECLARE语句创建

-- 创建游标--
CREATE PROCEDURE processorders() -- 存储过程
BEGIN
	DECLARE ordernumbers CURSOR -- 创建游标
	FOR
	SELECT order_num FROM orders;
END;

游标用OPEN CURSOR语句来打开，由CLOSE CURSOR语句关闭，使用声明过的游标不需要再次声明，用OPEN语句打开它就可以了。如果你不明确关闭游标，MySQL将会在到达END语句时自动关闭它。

-- 打开游标--
OPEN ordernumbers;

-- 关闭游标--
CLOSE ordernumbers;

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

CREATE PROCEDURE processorders()
BEGIN
	-- 定义局部变量
	DECLARE o INT;
	
	-- 定义游标
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	
	-- 打开游标
	OPEN ordernumbers;
	
	-- 获取一行数据
	FETCH ordernumbers INTO o;
	
	-- 关闭游标
	CLOSE ordernumbers;
END;

-- 使用循环--
CREATE PROCEDURE processorders()
BEGIN
	-- 定义局部变量
	DECLARE o INT;
	DECLARE done BOOLEAN DEFAULT 0;
	
	-- 定义游标
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	
	-- 定义处理器
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
	
	-- 打开游标
	OPEN ordernumbers;
	
	-- 开始循环
	REPEAT
		FETCH ordernumbers INTO o; -- 获取一行数据
	-- 结束循环
	UNTIL done END REPEAT;
	
	-- 关闭游标
	CLOSE ordernumbers;
END;

与前一个例子不一样的是，这个例子中的FETCH是在REPEAT内，因此它反复执行直到done为真（由UNTIL done END REPEAT;规定）为使它起作用，用一个DEFAULT 0（假，不结束）定义变量done。

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

-- 复杂的例子--
CREATE PROCEDURE processorders()
BEGIN
	-- 定义局部变量
	DECLARE o INT;
	DECLARE t DECIMAL(8,2);
	DECLARE done BOOLEAN DEFAULT 0;
	
	-- 定义游标
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	
	-- 定义处理器
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
	
	-- 创建一个表来存储结果
	CREATE TABLE IF NOT EXISTS ordertotals
		(order_num INT,total DECIMAL(8,2));
		
	-- 打开游标
	OPEN ordernumbers;
	
	-- 开始循环
	REPEAT
	
		FETCH ordernumbers INTO o; -- 获取一行数据
		CALL ordertotal(o,1,t); -- 这是存储过程章节中写的一个存储过程，因为变量定义过了，不用加@
		INSERT INTO ordertotals(order_num,total)
		VALUES(o,t); -- 插入表中
		
	-- 结束循环
	UNTIL done END REPEAT;
	
	-- 关闭游标
	CLOSE ordernumbers;
END;

触发器

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

DELETE；
INSERT；
UPDATE。

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

只有表才支持触发器，视图不支持（临时表也不支持）。每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器（每条INSERT、UPDATE和DELETE的之前和之后）。单一触发器不能与多个事件或多个表关联。

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

唯一的触发器名(尽可能保持每个数据库的触发器名唯一）；
触发器关联的表；
触发器应该响应的活动（DELETE、INSERT或UPDATE）；
触发器何时执行（处理之前或之后）。

MYSQL5以后，不允许触发器返回任何结果，因此使用into @变量名，将结果赋值到变量中，用select调用即可。修改为

CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added' INTO @asn;

CREATE TRIGGER用来创建名为newproduct的新触发器。触发器可在一个操作发生之前或之后执行，这里给出了AFTER INSERT，所以此触发器将在INSERT语句成功执行后执行。这个触发器还指定FOR EACH ROW，因此代码对每个插入行执行。

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

1 2	-- 删除触发器-- DROP TRIGGER newproduct;

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

在MySQL中用old和new表示执行前和执行后的数据。

INSERT触发器

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

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

1 2	CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num INTO @asn;

此代码创建一个名为neworder的触发器，它按照AFTER INSERT ON orders执行。在插入一个新订单到orders表时，MySQL生成一个新订单号并保存到order_num中。触发器从NEW. order_num取得这个值并返回它。此触发器必须按照AFTER INSERT执行，因为在BEFORE INSERT语句执行之前，新order_num还没有生成。对于orders的每次插入使用这个触发器将总是返回新的订单号。

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

DELETE 触发器

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

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

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;

在任意订单被删除前将执行此触发器。它使用一条INSERT语句将OLD中的值（要被删除的订单）保存到一个名为archive_ orders的存档表中。

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

UPDATE触发器

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

在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问以前（UPDATE语句前）的值，引用一个名为NEW的虚拟表访问新更新的值；（这是因为INSERT不存在OLD（插入了就是新的），DELETE不存在NEW（删除的肯定的旧的））
在BEFORE UPDATE触发器中，NEW中的值可能也被更新（允许更改将要用于UPDATE语句中的值）；
OLD中的值全都是只读的，不能更新。

1
2
3

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

这个例子可以看出一些NEW的本质，实际上NEW中的值就是将要插入或更新的内容，当有触发器时，先写到NEW，再写到表中。因此这里在写到表前（BEFORE)，先对NEW中的内容进行改变（SET）。

一些重点

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

事务处理

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

利用事务处理，可以保证一组操作不会中途停止，它们或者作为整体执行，或者完全不执行（除非明确指示）。如果没有错误发生，整组语句提交给（写到）数据库表。如果发生错误，则进行回退（撤销）以恢复数据库到某个已知且安全的状态。

下面是关于事务处理需要知道的几个术语：

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

事务处理用来管理INSERT、UPDATE和DELETE语句。你不能回退SELECT语句。（这样做也没有什么意义。）你不能回退CREATE或DROP操作。事务处理块中可以使用这两条语句，但如果你执行回退，它们不会被撤销。

-- 开始事务--
START TRANSACTION;

-- 撤销--
ROLLBACK;

-- 例子--
SELECT * FROM ordertotals;
START TRANSACTION; -- 开启事务
DELETE FROM ordertotals; -- 删除
SELECT * FROM ordertotals; -- 确实删除了
ROLLBACK; -- 回退（撤销）
SELECT * FROM ordertotals; -- 内容又存在了

这个例子从显示ordertotals表的内容开始。首先执行一条SELECT以显示该表不为空。然后开始一个事务处理，用一条DELETE语句删除ordertotals中的所有行。另一条SELECT语句验证ordertotals确实为空。这时用一条ROLLBACK语句回退START TRANSACTION之后的所有语句，最后一条SELECT语句显示该表不为空。

显然，ROLLBACK只能在一个事务处理内使用（在执行一条STARTTRANSACTION命令之后）。

一般的MySQL语句都是直接针对数据库表执行和编写的。这就是所谓的隐含提交（implicit commit），即提交（写或保存）操作是自动进行的。在事务处理块中，提交不会隐含地进行。为进行明确的提交，使用COMMIT语句。

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

最后的COMMIT语句仅在不出错时写出更改。如果第一条DELETE起作用，但第二条失败，则DELETE不会提交（实际上，它是被自动撤销的）。

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

为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符。这样，如果需要回退，可以回退到某个占位符。这些占位符称为保留点。为了创建占位符，可使用SAVEPOINT语句。每个保留点都取标识它的唯一名字，以便在回退时，MySQL知道要回退到何处。

-- 创建保留点--
SAVEPOINT delete1;

-- 回退保留点--
ROLLBACK TO delete1;

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

如果要取消自动提交，可以用：SET autocommit = 0;

其他

全球化和本地化

数据库表被用来存储和检索数据。不同的语言和字符集需要以不同的方式存储和检索。因此，MySQL需要适应不同的字符集（不同的字母和字符），适应不同的排序和检索数据的方法。在讨论多种语言和字符集时，将会遇到以下重要术语：

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

校对为什么重要：排序英文正文很容易，对吗？或许不。考虑词APE、apex和Apple。它们处于正确的排序顺序吗？这有赖于你是否想区分大小写。使用区分大小写的校对顺序，这些词有一种排序方式，使用不区分大小写的校对顺序有另外一种排序方式。这不仅影响排序（如用ORDER BY排序数据），还影响搜索（例如，寻找apple的WHERE子句是否能找到APPLE）。在使用诸如法文à或德文ö这样的字符时，情况更复杂，在使用不基于拉丁文的字符集（日文、希伯来文、俄文等）时，情况更为复杂。

-- 查看所支持的字符集完整列表--
SHOW CHARACTER SET; 
-- 这条语句显示所有可用的字符集以及每个字符集的描述和默认校对。

-- 查看所支持的校对的完整列表--
SHOW COLLATION; 
-- 此语句显示所有可用的校对，以及它们适用的字符集。许多校对出现两次，一次区分大小写（由_cs表示），一次不区分大小写（由_ci表示）。

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

1
2
3

-- 百分号作为通配符，匹配0，1，多个任意字符--
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';

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

CREATE TABLE mytable
(
	columnn1 INT,
	columnn2 VARCHAR(10)
) DEFAULT CHARACTER SET hebrew
  COLLATE hebrew_general_ci;

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

MySQL还允许对每个列设置它们：

CREATE TABLE mytable
(
	columnn1 INT,
	columnn2 VARCHAR(10),
	columnn3 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci
) DEFAULT CHARACTER SET hebrew
  COLLATE hebrew_general_ci;

校对在对用ORDER BY子句检索出来的数据排序时起重要的作用。也可以在SELECT语句中排序时指定校对：

1 2	SELECT * FROM customers ORDER BY lastname,firstname COLLATE latin1_general_cs;

上面的SELECT语句演示了在通常不区分大小写的表上进行区分大小写搜索的一种技术。当然，反过来也是可以的。

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

值得注意的是，如果绝对需要，串可以在字符集之间进行转换。为此，使用Cast()或Convert()函数：

类型转换
- cast(expr AS type)
- convert(expr, type)
编码转换
- cast(string AS CHAR[(N)] CHARACTER SET charset_name)
- convert(expr USING transcoding_name)或convert(string, CHAR[(N)] CHARACTER SET charset_name

安全管理

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

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

1
2
3

-- 获取所有用户账号列表--
USE mysql;
SELECT user FROM user;

用户定义为user@host：MySQL的权限用用户名和主机名结合定义。如果不指定主机名，则使用默认的主机名%（授予用户访问权限而不管主机名）。**%代表所有ip段都可以使用这个用户，也可以指定host为某个ip或ip段，这样会仅允许在指定的ip主机使用该数据库用户**。

为了创建一个新用户账号，使用CREATE USER语句。

1 2	-- 创建一个新用户-- CREATE USER ben IDENTIFIED BY 'password';

CREATE USER创建一个新用户账号。在创建用户账号时不一定需要口令，不过这个例子用IDENTIFIED BY 给出了口令。

IDENTIFIED BY指定的口令为纯文本，MySQL将在保存到user表之前对其进行加密。为了作为散列值指定口令，使用IDENTIFIED BY PASSWORD。
使用GRANT或INSERT：GRANT语句（稍后介绍）也可以创建用户账号，但一般来说CREATE USER是最清楚和最简单的句子。此外，也可以通过直接插入行到user表来增加用户，不过为安全起见，一般不建议这样做（不建议插入）。MySQL用来存储用户账号信息的表（以及表模式等）极为重要，对它们的任何毁坏都可能严重地伤害到MySQL服务器。

-- 重命名一个账号--
RENAME USER ben TO bforta;

-- 删除用户--
DROP USER bforta;

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

1 2	-- 查看用户权限-- SHOW GRANTS FOR bforta;

为设置权限，使用GRANT语句。GRANT要求你至少给出以下信息：

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

-- 设置单个权限--
GRANT SELECT ON some_database.* TO bforta;

-- 设置多个权限--
GRANT SELECT,INSERT ON some_database.* TO bforta;

此GRANT允许用户在some_database.*（some_database数据库的所有表）上使用SELECT（INSERT）。通过只授予SELECT访问权限，用户bforta对some_database数据库中的所有数据具有只读访问权限。

GRANT的反操作为REVOKE，用它来撤销特定的权限(被撤销的访问权限必须存在，否则会出错。)：

1	REVOKE SELECT ON some_database.* FROM bforta;

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

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

下面列出可以授予或撤销的每个权限（前面是语句，后面是能拥有的功能）：

ALL ：除GRANT OPTION外的所有权限
ALTER ：使用ALTER TABLE
ALTER ROUTINE ：使用ALTER PROCEDURE和DROP PROCEDURE
CREATE ：使用CREATE TABLE
CREATE ROUTINE ：使用CREATE PROCEDURE
CREATE TEMPORARY TABLES：使用CREATE TEMPORARY TABLE
CREATE USER ：使用CREATE USER、DROP USER、RENAME USER和REVOKE ALL PRIVILEGES
CREATE VIEW ：使用CREATE VIEW
DELETE ：使用DELETE
DROP ：使用DROP TABLE
EXECUTE ：使用CALL和存储过程
FILE ：使用SELECT INTO OUTFILE和LOAD DATA INFILE
GRANT OPTION ：使用GRANT和REVOKE
INDEX ：使用CREATE INDEX和DROP INDEX
INSERT ：使用INSERT
LOCK TABLES ：使用LOCK TABLES
PROCESS ：使用SHOW FULL PROCESSLIST
RELOAD ：使用FLUSH
REPLICATION CLIENT ：服务器位置的访问
REPLICATION SLAVE ：由复制从属使用
SELECT ：使用SELECT
SHOW DATABASES ：使用SHOW DATABASES
SHOW VIEW ：使用SHOW CREATE VIEW
SHUTDOWN ：使用mysqladmin shutdown（用来关闭MySQL）
SUPER ：使用CHANGE MASTER、KILL、LOGS、PURGE、MASTER和SET GLOBAL。还允许mysqladmin调试登录
UPDATE ：使用UPDATE
USAGE ：无访问权限

在使用GRANT和REVOKE时，用户账号必须存在，但对所涉及的对象没有这个要求。这允许管理员在创建数据库

和表之前设计和实现安全措施。这样做的副作用是，当某个数据库或表被删除时（用DROP语句），相关的访问权限仍然存在。而且，如果将来重新创建该数据库或表，这些权限仍然起作用。

1 2	-- 更改口令（密码）-- SET PASSWORD FOR bforta = Passord('new password');

SET PASSWORD更新用户口令。新口令必须传递到Password()函数进行加密。

在不指定用户名时（没有FOR bforta），SET PASSWORD更新当前登录用户的口令。

数据库维护

像所有数据一样，MySQL的数据也必须经常备份。由于MySQL数据库是基于磁盘的文件，普通的备份系统和例程就能备份MySQL的数据。但是，由于这些文件总是处于打开和使用状态，普通的文件副本备份不一定总是有效。

下面列出这个问题的可能解决方案：

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

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

MySQL提供了一系列的语句，可以（应该）用来保证数据库正确和正常运行。

ANALYZE TABLE，用来检查表键是否正确，返回如下表的一些状态信息
1
ANALYZE TABLE orders;
CHECK TABLE用来针对许多问题对表进行检查。在MyISAM表上还对索引进行检查。CHECK TABLE支持一系列的用于MyISAM表的方式。CHANGED检查自最后一次检查以来改动过的表。EXTENDED执行最彻底的检查，FAST只检查未正常关闭的表，MEDIUM检查所有被删除的链接并进行键检验，QUICK只进行快速扫描。
1
CHECK TABLE orders,orderitems;

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

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

MySQL维护管理员依赖的一系列日志文件。主要的日志文件有以下几种。

错误日志。它包含启动和关闭问题以及任意关键错误的细节。此日志通常名为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语句来刷新和重新开始所有日志文件。

改善性能

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

关于索引：

原文链接：https://blog.csdn.net/Weixiaohuai/article/details/109696261

数据库索引是为了提高查询速度而对表字段附加的一种标识。简单来说，索引其实是一种数据结构。数据库的索引类似于书籍的索引。在书籍中，索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。

首先我们需要明白为什么索引会提高查询速度，数据库在执行一条SQL语句的时候，默认扫描方式是根据搜索条件进行全表扫描，遇到匹配条件的就加入搜索结果集合。如果我们对某一字段增加索引，查询时就会先去索引列表中一次定位到特定值的行数，大大减少遍历匹配的行数，所以数据库索引能明显提高查询的速度。

下面列举几种适合建立索引的情况：

1.经常在where条件中作为查询条件的字段可以建立索引；
2.外键关联列可以建立索引；
3.order by排序后面的字段可以建立索引；
4.group by分组后的字段可以建立索引；

当然，并不是所有情况下都适合建立索引，如下几种情况就不太适合建立索引：

1.经常增、删、改的字段不适合建立索引，每次执行，索引需重新建立；
2.数据过滤性很差的字段不适合建立索引，如性别字段；
3.当表数据量过少的时候不太适合建立索引，因为索引占用存储空间；

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。
索引的一个主要目的就是加快检索表中数据的方法，亦即能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构。
索引是对数据库表中一个或多个列（例如，employee 表的姓名 (name) 列）的值进行排序的结构。
例如这样一个查询：select * from table1 where id=10000。如果没有索引，必须遍历整个表，直到ID等于10000的这一行被找到为止；有了索引之后(必须是在ID这一列上建立的索引)，即可在索引中查找。由于索引是经过某种算法优化过的，因而查找次数要少的多。可见，索引是用来定位的。
从数据搜索实现的角度来看，索引也是另外一类文件/记录，它包含着可以指示出相关数据记录的各种记录。其中，每一索引都有一个相对应的搜索码，字符段的任意一个子集都能够形成一个搜索码。这样，索引就相当于所有数据目录项的一个集合，它能为既定的搜索码值的所有数据目录项提供定位所需的各种有效支持

数据类型

串数据类型

CHAR ：1～255个字符的定长串。它的长度必须在创建时指定，否则MySQL假定为CHAR(1)
ENUM ：接受最多64 K个串组成的一个预定义集合的某个串
LONGTEXT ：与TEXT相同，但最大长度为4 GB
MEDIUMTEXT ：与TEXT相同，但最大长度为16 K
SET：接受最多64个串组成的一个预定义集合的零个或多个串
TEXT ：最大长度为64 K的变长文本
TINYTEXT ：与TEXT相同，但最大长度为255字节
VARCHAR ：长度可变，最多不超过255字节。如果在创建时指定为VARCHAR(n)，则可存储0到n个字符的变长串（其中n≤255）

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

当数值不是数值时：你可能会认为电话号码和邮政编码应该存储在数值字段中（数值字段只存储数值数据），但是，这样做却是不可取的。如果在数值字段中存储邮政编码01234，则保存的将是数值1234，实际上丢失了一位数字。

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

数值数据类型

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

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

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

存储货币数据类型：MySQL中没有专门存储货币的数据类型，一般情况下使用DECIMAL(8, 2)：总长度为8,小数位数为2位的数值，整数有效位为6。

日期和时间数据类型

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

二进制数据类型

BLOB ：Blob最大长度为64 KB
MEDIUMBLOB ：Blob最大长度为16 MB
LONGBLOB ：Blob最大长度为4 GB
TINYBLOB ：Blob最大长度为255字节

后记

写这篇博客比看书花的时间还要久 (；′⌒`)

书上的内容并不太难，实际上，在写这篇博客的过程中，对书上的内容也有了自己的思路，因此在内容规划上有些许不同，不过章节上为了图方便就是按顺序来的。代码基本都自己又敲了一遍，有新的体会，其中也写了许多自己的见解和注释，补充了一些内容，但主要还是当日后复习用。

2022-08-01