MySQL必知必会笔记

使用MySQL

选择数据库

输入：USE crashcourse;

​ 选择crashcourse数据库

必须先使用USE打开数据库，才能读取其中的数据

了解数据库和表

输入：SHOW DATABASES;

​ 返回可用数据库的一个列表

输入：SHOW TABLES;

​ 返回当前选择的数据库内可用表的列表

输入：SHWO COLUMNS FROM customers;

​ SHOW COLUMNS返回表customers的字段信息

自动增量：

​ 某些表列需要唯一值，MySQL可自动为每个行分配下一个可用编号，无需手动分配唯一值

DESCRIBE语句

​ MySQL支持用DESCRIBE作为SHOW COLUMNS FROM的一种快捷方式

所支持的其他SHOW语句还有：

​ + SHOW STATUS：用于显示广泛的服务器状态信息

​ + SHOW CREATE DATABASE和SHOW CREATE TABLE：显式创建特定数据库或表的MySQL语句

​ + SHOW GRANTS：用来显示授予用户(所有用户或特定用户)的安全权限

​ + SHOW ERRORS和SHOW WARNINGS：用来显示服务器错误或警告消息

HELP SHOW;：显示允许的SHOW语句

---

检索数据

SELECT语句

必须至少给出的两条信息：想选择什么 + 从什么地方选择

检索单个列

输入：

SELECT prod_name
FROM products;

​ 从products表中检索名为prod_name的列

​ FROM关键字指出从其中检索数据的表名

未排序数据：若为明确排序查询结果，则返回的数据没有特殊一样

结束SQL语句：多条SQL语句必须以分号(;)分隔

​ MySQL不需要在单条SQL语句后加分号，但特定DBMS和mysql命令行必须加上分号

SQL语句和大小写：SQL语句不区分大小写

​ 习惯对所有SQL关键字使用大写，而对所有列和表名使用小写

MySQL4.1及之前的版本中数据库名、列名、表名等标识符是区分大小写的

使用空格：处理SQL语句时，其中所有空格都被忽略

检索多个列

必须在SELECT关键字后给出多个列名，列名之间以逗号分隔

当心逗号：选择多个列时，列间需要加逗号，但最后一个列名后不加

输入：

SELECT prod_id, prod_name, prod_price
FROM products;

​ 从表总选择数据，指定了3个列名

数据表示：SQL语句一般返回原始的无格式的数据

​ 一般很少使用实际检索出的原始数据(没有应用程序提供的格式)

检索所有列

通过在实际列名的位置使用星号(*)通配符

输入：

SELECT *
FROM products;

​ 此时列的顺序一般是列在表定义中出现的顺序

​ 有时不是这样，表的模式的变化(添加或删除等)可能导致顺序的变化

使用通配符：一般，除非确实需要表中每个列，否则最好别使用*通配符

​ 检索不需要的列通常会降低检索和应用程序的性能

检索未知列：使用通配符的一大优点，能检索出名字未知的列

检索不同的行

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

输入：

SELECT DISTINCT vend_id
FROM products;

​ 如果使用DISTINCT关键字，必须直接放在列名的最前面

不能部分使用DISTINCT：DISTINCT关键字应用于所有列而不仅是前置它的列

限制结果

SELECT语句返回所有匹配的行

可使用LIMIT子句，如：

SELECT prod_name
FROM products
LIMIT 5;

​ 使用SELECT语句检索单个列

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

SELECT prod_name
FROM products
LIMIT 5,5;

​ LIMIT 5,5指示MySQL返回从行5开始的5行

​ 第一个数为开始位置，第二个数为要检索的行数

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

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

行0：检索出来的第一行为0不是行1

​ 因此LIMIT 1, 1将检索出第二行而不是第一行

在行数不够时：MySQL将只返回它能返回的那么多行

MySQL 5的LIMIT语法：

​ 支持另一种替代语法：LIMIT 4 OFFSET 3

​ 表示：从行3开始取4行，就像LIMIT 3, 4一样

使用完全限定的表名

即同时使用表名和列字

例：

SELECT  products.prod_name
FROM crashcourse.products;

​ 表名和列字都可以是完全限定的(假定products表确实位于crashcourse数据库中)

有些情况需要完全限定名，留着备用

---

排序检索数据

使用SELECT语句的ORDER BY子句，根据需要排序检索出数据

排序数据

若不排序数据，顺序会收到MySQL重用回收存储空间的影响

因此，若不规定排序顺序，则不应该假定检索出的数据顺序有意义

子句：SQL语句由子句构成，有些子句必须，有些可选

​ 一个子句通常由一个关键子和所提供的数据组成

可使用ORDER BY子句

​ 取一个或多个列的名字，据此对输出排序

输入：

SELECT prod_name
FROM products
ORDER BY prod_name;

​ ORDER BY子句对prod_name列以字母顺序排序

通过非选择列进行排序：

​ 通常ORDER BY子句中使用的列将会是被显示的列

​ 但用非检索的列排序数据是完全合法的

按多个列排序

只要指定列名，列名间用逗号分开即可(跟选择多个列时似的)

输入：

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

​ 检索两个列，排序两个列

​ 尽在多个行具有相同的prod_price值时，才按照prod_name进行排序(左侧先排)

指定排序方向

升序排序(从A到Z)是默认排序顺序

若进行降序排序：必须指定DESC关键字

输入：

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

​ 按prod_price降序排序，再对prod_name升序排序

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

在多个列上降序排序：必须对每个列指定DESC关键字

ASC关键字与DESC相反，按升序排序(但是没啥用，升序是默认的)

区分大小写和排序顺序：在字典排序顺序中，A被视为与a相同，这是MySQL的默认行为

​ ORDER BY子句无法改变这种排序顺序

使用ORDER BY和LIMIT组合，能找出一个列中最高或最低的值

输入：

SELECT prod_price
FROM products
ORDER BY prod_price DESC
LIMIT 1;

​ prod_peice DESC保证按照降序排序

​ LIMIT 1则只返回一行

ORDER BY子句的位置：ORDER BY子句应位于FROM子句后

​ 若使用LIMIT，则其必须位于ORDER BY之后

​ 使用子句的次序不对将产生错误消息

---

过滤数据

使用SELECT语句的WHERE子句指定搜索条件

使用WHERE子句

只检索所需数据需要指定搜索条件(过滤条件)

在SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤

​ WHERE子句在表名(FROM子句)之后给出

输入：

SELECT prod_name, prod_price
FROM products
WHERE prod_price = 2.50;

​ 从products表中检索两个列，只返回prod_price值为2.50的行

SQL过滤与应用过滤：数据也可以在应用层过滤

​ SQL的SELECT语句为客户机应用检索出超过实际所需的数据，然后客户机代码对返回数据进行循环，以取出需要的行

​ 让客户机应用处理数据库的工作将会极大的影响应用的性能，并且应用完全不具备可伸缩性，还会导致网络带宽的浪费

WHERE子句的位置：

​ 让ORDER BY位于WHERE之后，否则将产生错误

WHERE子句操作符

MySQL支持表中列出的所有条件操作符

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

检查单个值

输入：

SELECT prod_name, prod_price
FROM products
WHERE prod_name = 'fuses';

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

不匹配检查

输入：

SELECT vend_id, prod_name
FROM products
WHERE vend_id <> 1003;

何时使用引号：单引号用来限定字符串，与数值列比较的值不用引号

范围值检查

可使用BETWEEN操作符，语法略有不同，需要两个值

输入：

SELECT prod_name, prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;

​ 检索prod_price在5到10之间的行

使用BETWEEN时必须指定两个值(所需范围的最低值和最高值)，并用AND分隔

BETWEEN匹配的范围包括开始值和结束值

空值检查

创建表时可指定其中类是否可包含空值

在一个列不包含值时，称其为包含空值NULL

IS NULL子句：SELECT语句中用来检查具有NULL值的列的特殊WHERE子句

输入：

SELECT prod_name
FROM products
WHERE prod_price IS NULL;

​ 返回prod_price为NULL的行

NULL与不匹配：在匹配过滤或不匹配过滤时不返回具有NULL值的列

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

---

数据过滤

组合WHERE子句以建立功能更强的更高级的搜索条件

组合WHERE子句

MySQL允许给出多个WHER子句

这些子句可以用两种方式使用：以AND子句 或 以RO子句的方式使用

操作符：用来联结或改变WHERE子句中的子句的关键字，也称为逻辑操作符

AND操作符

为通过不止一个列进行过滤，可使用AND操作符给WHERE子句附加条件

输入：

SELECT prod_id, prod_price, prod_name
FROM products
WHERE vend_id = 1003 AND prod_price <= 10;

可以添加多个过滤条件，每添加一条就要使用一个AND

OR操作符

与AND不同，指示MySQL检索匹配任一条件的行

输入：

SELECT prod_name, prod_price
FROM products
WHERE vend_id = 1002 OR vend_id = 1003;

计算次序

WHERE可包含任意数目的AND和OR操作符，允许两者结合以进行复杂和高级的过滤

BUT！：SQL在处理OR操作符前，优先处理AND操作符

​ 也就是说AND操作符的优先级比OR高(跟编程习惯反而不一样了)

解决方法：使用圆括号明确的分组相应的操作符

输入：

SELECT prod_name, prod_price
FROM products
WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;

​ 此时，DBMS先过滤括号内的OR条件

在WHERE子句中使用圆括号：任何时候使用具有AND和OR操作符的WHERE子句时，都应该使用圆括号明确的分组

​ 可以消除歧义

IN操作符

用来指定条件范围，范围中的每个条件都可以进行匹配

IN取合法值的由逗号分隔的清单，全都括在圆括号中

输入：

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

​ 此SELECT检索vend_id为1002和1003的行

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

IN操作符的优点：

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

NOT操作符

只有一个功能：否定它之后所跟的任何条件

输入：

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

​ NOT否定跟在它之后的条件，不匹配1002和1003

为什么使用NOT：

​ 在复杂的语句中，在与IN操作符联合使用时，NOT找出与条件列表不匹配的行非常简单

MySQL中的NOT：MySQL支持使用NOT对IN、BETWEEN和EXISTS子句取反

​ 这与多数其他DBMS允许使用NOT对各种条件取反有很大差别

---

用通配符进行过滤

如何使用通配符进行通配搜索，以便对数据进行复杂过滤

LIKE操作符

前述的所有操作符都是针对已知进行过滤的

通配符：用来匹配值的一部分的特殊字符

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

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

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

​ LIKE指示MySQL，后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较

谓词：操作符在它作为谓词时不是操作符

​ 技术上说，LIKE是谓词而不是操作符，虽然最终结果是相同的

百分号(%)通配符

最常使用的通配符，**%**表示任何字符出现任意次数

输入：

SELECT prod_idm prod_name
FROM products
WHERE prod_name LINK 'jet%';

​ 将检索任意以jet起头的词，%告诉MySQL接受jet之后的任意字符，不管它有多少字符

区分大小写：根据MySQL的配置方式，搜索可以是区分大小写的

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

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

通配符也可以出现在搜索模式的中间，虽然这样做不太有用：

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

注意：除了一个或多个字符外，**%**还能匹配0个字符

注意尾空格：尾空格可能会干扰通配符匹配

​ 如：匹配anvil时，如果它后面有一个或多个空格，则子句WHERE prod_name LIKE '%anvil'将不会匹配它们，因为在最后的l后有多余的字符

​ 解决的简单方法：在搜索模式最后附加一个**%**

​ 更好的方法是使用函数去掉首尾空格

注意NULL：虽然**%**通配符似乎可以匹配任何东西，但不能匹配NULL

​ 即使是WHERE prod_name LIKE '%'也不能匹配NULL值

下划线(_)通配符

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

输入：

SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '_ ton anvil';

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

使用通配符的技巧

MySQL的通配符很有用，但是有代价：

​ 通配符搜索的处理一般要比前面的其他搜索所花费时间更长

一些使用通配符的技巧：

• 不要过度使用通配符：如果其他操作符能达到相同目的，应该使用其他操作符

• 在确实需要使用通配符是：除非绝对有必要，否则不要把它们用到搜索模式的开始初

  把通配符置于搜索模式的开始初，搜索起来是最慢的

• 仔细注意通配符的位置：如果放错地方，可能不会返回想要的数据

总之，通配符是一种及其重要和有用的搜索工具

---

用正则表达式进行搜索

在MySQL WHERE子句内使用正则表达式来更好的控制数据过滤

正则表达式介绍

正则表达式：用来匹配文本的特殊的串(字符集合)

正则表达式用正则表达式语言来建立

(此处只介绍了正则表达式的基础知识)

使用MySQL正则表达式

正则表达式的作用是匹配文本，将一个模式(正则表达式)与一个文本串进行比较

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

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

基本字符匹配

检索列prod_name包含文本1000的所有行：

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

​ REGEXP后所跟的东西作为正则表达式处理

如：WHERE prod_name REGEXP '.000'

.是正则表达式语言中一个特殊的字符，表示匹配任意一个字符

LIKE与REGEXP：之间有一个重要的差别

​ LIKE匹配整个列：如果被匹配的文本在列值内部出现，则LIKE将不会找到它，相应的行也不会被返回(除非使用通配符)

​ 而REGEXP在列值内进行匹配，如果被匹配文本在列值中出现，则会被找到并返回

REGEXP也可以用来匹配整个列值(起到与LIKE相同的作用)：使用^和$定位符即可

匹配不区分大小写：MySQL中的正则表达式匹配(自版本3.23.4后)不区分大小写

​ 为区分大小写，可使用BINARY关键字，如：WHERE prod_name REGEXP BINARY 'JetPack .000'

进行OR匹配

为搜索两个串之一，使用|

输入：

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

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

使用上类似OR语句，多个OR条件可并入单个正则表达式

两个以上的OR条件：可以给出两个以上的OR条件

​ 如：'1000 | 2000 | 3000'将匹配1000或2000或3000

匹配几个字符之一

匹配任何单一字符，若只想匹配特定的字符：可以通过指定一组用[和]括起来的字符来完成

输入：

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

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

[]是另一种形式的OR语句

事实上，正则表达式[123]Ton为[1|2|3]Ton的缩写，也可以用后者

但，需要用[]来定义OR语句查找什么

若为：WHERE prod_name REGEXP '1|2|3 Ton'则会被假定为’1’或’2’或’3 Ton’

除非把字符|括在一个集合中，否则它将应用于整个串

字符集合也可以被否定，它们将匹配除指定字符外的任何东西

为否定一个字符集，在集合的开始处放置一个^即可，即：[^123]，匹配除字符1、2、3外的任何东西

匹配范围

集合可用来定义要匹配的一个或多个字符

如：[0123456789]匹配数字0到9

为简化这种类型的集合，可使用-来定义一个范围：

​ [0-9]功能等同于上述数字列表

范围不限于完整的集合：[1-3]或[6-9]也合法

范围不一定只是数值的：[a-z]匹配任意字母字符

输入：

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

​ [1-5]定义了一个范围，匹配1到5

匹配特殊字符

正则表达式语言由具有特定含义的特殊字符构成，如：.、[]、|、-等

如果要匹配这些特殊字符，必须用\\为前导

\\-表示查找-

\\.表示查找.

输入：

SELECT vend_name
FROM vendors
WHERE vend_name REGEXP '\\.'
ORDER BY vend_name;

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

\\也用来引用元字符(具有特殊含义的字符)

表 空白元字符：

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

匹配\：为了匹配反斜杠(\)字符本身，需要使用\\\

**\或\\?**：多数正则表达式实现使用单个反斜杠转义特殊字符，以便能使用这些字符本身

​ 但MySQL要求两个反斜杠(MySQL自己解释一个，正则表达式库解释另一个)

匹配字符类

为更方便工作，可以使用预定义的字符集，称为字符类

表 字符类：列出了字符类以及含义

类	说明
[: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]）

匹配多个实例

目前为止使用的所有正则表达式都试图匹配单次出现

有时需要对匹配的数目进行更强的控制，如：可能需要寻找所有的数，不管数中包含多少数字

可以用下表列出的正则表达式重复元字符来完成

表 重复元字符：

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

例1

输入：

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

​ 正则表达式\\([0-9] sticks?\\)

• \\(：匹配)
• [0-9]：匹配任意数字
• sticks?：匹配stick和sticks(s后面的?使s可选)
• \\)：匹配)

没有?，匹配stick和sticks会非常困难

例2

输入：

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

• [:digit:]：匹配任意数字
• {4}确切的要求前面的字符(任意数字)出现4次

即匹配连在一起的任意四位数字

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

比如上面的例子可以写为：WHERE prod_name REGEXP '[0-9][0-9][0-9][0-9]'

定位符

目前为止的所有例子都是匹配一个串中任意位置的文本

为匹配特定位置的文本，需要使用下表列出的定位符

表 定位元字符

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

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

简单搜索[0-9\\.](或[[:digit:]\\.])不行，因为它将在文本内任意位置查找匹配

解决办法是使用^定位符：

输入：

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

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

​ 没有^，则还要多检索那些中间有数字的行

^的双重用途：^有两种用法

• 在集合中(用[和]定义)，用它来否定该集合
• 否则，用来指串的开始处

使REGEXP起类似LIKE的作用：前面说过，LIKE和REGEXP的不同在于：LIKE匹配整个串而REGEXP匹配子串

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

简单的正则表达式测试：可以在不使用数据库表的情况下用SELECT来测试正则表达式

​ REGEXP检查总是返回0(没有匹配)或1(匹配)

​ 可以用带文字串的REGEXP来测试表达式，并试验它们

​ 相应的语法如下：

​ SELECT 'hello' REGEXP '[0-9]';

​ 这个例子显然将返回0(因为文本hello中没有数字)

---

创建计算字段

介绍什么是计算字段，如何创建计算字段，怎样从应用程序中使用别名引用它们

计算字段

存储在数据库表中的数据一般不是应用程序所需要的格式

我们需要直接从数据库中检索出转换、计算、格式化过的数据，而不是检索出数据，然后在客户机应用程序中重新格式化

此时应使用计算字段

计算字段并不实际存在于数据库表中

计算字段是运行时在SELECT语句内创建的

字段：基本上与列意思相同，经常互换使用

​ 不过数据库列一般称为列，而术语字段通常用在计算字段的连接上

只有数据库知道SELECT语句中哪些列是实际的表列，哪些列是计算字段

从客户机的角度看，计算字段的数据是以与其他列的数据相同的方式返回的

客户机与服务器的格式：可在SQL语句内完成的许多转换和格式化工作都可以直接在客户机应用程序内完成

​ 但一般在数据库服务器上完成这些操作要快得多，因为DBMS是设计来快速有效的完成这种处理的

拼接字段

拼接：将值联结到一起构成单个值

例：将vend_name和vend_country以name(loaction)这样的格式返回

解决办法：将两个列拼接起来

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

MySQL的不同之处：多数DBMS使用+或||来实现拼接，MySQL则使用Concat()函数来实现

​ 当把SQL语句转换成MySQL语句时要注意

输入：

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

​ Concat()拼接串，把多个串连接起来形成一个较长的串

​ 需要一个或多个指定的串，各个串之间用逗号分隔

删除数据右侧多余的空格来整理数据，这可以使用MySQL的RTrim()函数来完成

输入：

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

​ RTrim()函数去掉值右边的所有空格

​ 通过使用RTrim()各个列都进行了整理

Trim函数：MySQL除了支持RTrim()(去除串右边的空格)

​ 还支持LTrim()(去掉串左边的空格)

​ 以及Trim()(去掉串左右两边的空格)

使用别名

前面的输出中，SELECT语句拼接地址字段后得到的新计算列没有名字

一个未命名的列不能用于客户机应用中，因为客户机没有办法引用它

为解决此问题，SQL支持列别名

别名：是一个字段或值的替换名

别名用AS关键字赋予

输入：

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

​ AS vend_title指示SQL创建一个包含指定计算的名为vend_title的计算字段

别名的其他用途：常见的用途包括，

• 在实际的表列名包含不符合规定的字符(如空格)时重新命名它
• 在原来的名字含混或容易误解时扩充它，等等

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

执行算数计算

计算字段的另一常见用途：对检索出的数据进行算数计算

输入：

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

​ expanded_price列为一个计算字段，此计算为quantity * item_price

MySQL支持下表中列出的基本算术操作符

此外，圆括号可用来区分优先顺序

表 MySQL算术操作符：

操作符	说明
+	加
-	减
*	乘
/	除

如何测试计算：SELECT提供了册数和试验函数与计算的一个很好的办法

​ SELECT通常用来从表中检索数据，但可以省略FROM子句以便简单的访问和处理表达式

例：

• SELECT 3*2;将返回6
• SELECT Trim('abc');将返回abc
• SELECT Now()利用Now()函数返回当前日期和时间

---

使用数据处理函数

什么是函数，MySQL支持何种函数，如何使用这些函数

函数

SQL支持利用函数来处理数据

函数一般是在数据上执行的，给数据的转换和处理提供了方便

函数没有SQL的可移植性强：多数SQL语句是可移植的，在SQL实现之间有差异时，这些差异通常不那么难处理

​ 但函数的可移植性却不强，几乎每中主要的DBMS的实现都支持其他实现不支持的函数，而且有时差异还很大

​ 不使用特殊实现的功能虽然很有好处，但不总是利于应用程序的性能，如果不使用这些函数，编写某些和应用程序代码会很艰难，必须用其他方法来失效DBMS有效的完成的工作

​ 如果决定使用函数，应该保证做好代码注释，以便以后能确切的知道所编写SQL代码的含义

使用函数

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

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

文本处理函数

例：使用Upper()函数

输入：

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

​ Upper()将文本转换为大写

下表列出了某些常用的文本处理函数：

表 常用的文本处理函数

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

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

SOUBDEX考虑了类似的发音字符和音节，使得能对穿进行发音比较二不是字母比较

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

使用Soundex()函数的例子：

有个顾客联系名为Y.Lee，但如果是输入错误，此联系名时间应该是Y.Lie怎么办：

使用Soundex()函数进行搜索，匹配所有发音类似于Y.Lie的联系名：

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

​ WHERE子句使用Soundex()函数来转换cust_contact列值和搜索为它们的SOUNDEX值

最终搜索出了发音相似的Y.Lee，SOUNDEX值相匹配

日期和时间处理函数

日期和时间采用相应的数据类型和特殊的格式存储，以便能快速和有效的排序或过滤，并节省物理存储空间

一般，应用程序不使用用来存储日期和时间的格式，因此日期和时间函数总是被用来读取、统计、处理这些值

下表列出了某些常用的日期和时间处理函数

表 常用日期和时间处理函数

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

用日期进行过滤需要注意一些别的问题和使用特殊的MySQL函数

首先注意MySQL使用的日期格式：

​ 无论什么时候指定一个日期，不管是插入或更新标值还是用WHERE子句进行过滤，日期必须为格式yyyy-mm-dd

​ 虽然其他的日期格式可能也行，但这是首选的日期格式，排除了多义性

应该总是使用4为数字的年份：支持2位数的年份

​ MySQL处理00-69为2000-2069，处理70-99为1970-1999

​ 虽然可能是打算要的年份，但使用完整的4位数字年份更可靠，因为MySQL不必做出任何假定

因此，基本的日期比较应该很简单：

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

​ 检索出order_date为2005-09-01的行

但，时间值在实际中很可能并不总是这样(如：2005-09-01 11:30:05)，此时不会检索出来

解决办法：指示MySQL仅将给出的日期与列中的日期部分进行比较，而不是将给出的日期与整个列值进行比较

​ 为此，必须使用Date()函数

Date(order_date)只是MySQL仅提取列的日期部分，

更可靠的SELECT语句为：

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

**如果要的是日期，请使用Date()**：如果想要的仅是日期，则使用Date()是一个良好的习惯，即使知道相应的列只包含日期也是如此

​ 这样，如果由于某种原因表中以后有日期和时间值，SQL代码也不用改变

​ 当然，也存在一个Time()函数，在只想要时间时应该使用它

Date()和Time()都是在MySQL4.1.1中第一次引入的

若想检索出2005年9月下的所有订，简单的相等测试不行，因为要匹配月份中的天数

有几种解决方法：

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

​ BETWEEN操作符定义了一个要匹配的日期范围

另一种办法(不需要记住每个月有多少天，或不需要担心闰年二月的方法)：

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

​ Year()是一个从日期(或日期时间)中返回年份的函数

​ Month()从日期中返回月份

MySQL的版本差异：MySQL4.1.1中增加了虚度日期和时间函数

​ 如果使用更早的MySQL版本，应查阅文档以确定可以使用哪些函数

数组处理函数

数组处理函数仅处理数组数据

这些函数一般用于代数、三角、集合运算，没有串或日期-时间处理函数的使用那么频繁

BUT，在主要DBMS的函数中，数组函数是最一直最统一的函数，(怪搞笑的不是吗

表 常用数值处理函数

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

---

汇总数据

SQL的聚集函数以及如何用其进行汇总表的数据

聚集函数

由于经常需要汇总数据而不用把它们实际检索出来，MySQL提供了专门的函数
使用这些函数，MySQL查询可用于检索数据，一边分析和报表生成
这种类型的检索例子有以下几种：

• 确定表中行书(或满足猴哥条件或包含某个特定值的行数)
• 获得表中行组的和
• 找出表列(或索引行或某些特定的行)的最大值、最小值和平均值
  以上例子都需要对表中数据(而不是实际数据本身)汇总
  因此，返回实际表数据是对时间和处理资源的一种浪费
  我们想要的是汇总信息
  为了方便这种类型的检索，MySQL给出了5给聚集函数(见下表)，可进行上述罗列的检索

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

表 SQL聚集函数

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

标准偏差：MySQL还支持一系列的标准偏差聚集函数

AVG()函数

通过对表中行书计数并计算特定列值之和，求得该列的平均值

• 可用来返回所有列的平均值
• 也可以用来返回特定列或行的平均值

输入：

SELECT AVG(prod_price) AS avg_price
FROM products;

值avg_Price包含表中所有产品(prod_price)的平均值
avg_price是一个别名

AVG()也可用来确定特定列或行的平均值
输入：

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

与前一条相比包含了WHERE子句，因此avg_price中返回的值是特定列的平均值

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

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

COUNT()函数

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

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

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

输入：

SELECT COUNT(*) AS num_cust
FROM customers;

利用`COUNT(*)`对所有行计数，不管行中各列有什么值
计数值在num_cust中返回

输入：

SELECT COUNT(cust_email) AS num_cust
FROM customers;

对cust_email列中有值的行进行计数

NULL值：如果指定列名，则指定列的值为空的行被COUNT()函数忽略
但如果COUNT()函数中用的是星号(*)，则不忽略

MAX()函数

返回指定列中的最大值
MAX()要求指定列名：
输入：

SELECT MAX(prod_price) AS max_price
FROM products;

`MAX()`返回表中最大值

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

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

MIN()函数

返回指定列的最小值
与MAX()用于，MIN()要求指定列名：
输入：

SELECT MIN(prod_price) AS min_price
FROM products;

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

NULL值：MIN()函数忽略值为NULL的行

SUM()函数

用来返回指定列的和(总计)
输入：

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

函数`SUM(quantity)`返回quantity总和
WHERE子句限定了order_num的值

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

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

函数`SUM(item_price*quantity)`返回其中计算结果的总和

在多个列上进行计算：如上例所示
利用标准的算数操作符，所有聚集函数都可用来执行多个列上的计算

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

聚集不同值

MySQL 5及后期版本：下面的聚集函数的DISTINCT的使用，以及被添加到MySQL 5.0.3中
下面的内容在MySQL 4.x中不能正常运行

以上5给聚集函数都可以如下使用：

• 对所有的行执行计算
  指定ALL参数或不给参数(因为ALL是默认行为)
• 值包含不同的值
  指定DISTINCT参数

ALL为默认：ALL参数不需要指定，因为它是默认行为
如果不指定DISTINCT，则假定为ALL

下例，使用AVG()函数返回特定字段的产品平均值
与上面的SELECT语句相同，但使用了DISTINCT参数，因此平均值只考虑各个不同的值
输入：

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

使用DISTINCT后，此例中的avg_price比较高
因为有多个物品具有相同的较低的价格，排除它们提升了平均价格

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

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

组合聚集函数

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

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

这里用单条SELECT语句执行了4个聚集计算，返回4个值

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

---

分组数据

涉及两个新SELECT子句：GROUP BY子句和HAVING子句

数据分组

SQL聚集函数可用来汇总数据，但都是在表的所有数据或匹配特定的WHERE子句的数据上进行的

分组允许把数据分为多个逻辑组，以便能对每个分组进行聚集计算

创建分组

分组是在SELECT语句的GROUP BY子句中建立的
输入：

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

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

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

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

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

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

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

过滤分组

除了能用GROUP BY分组数据外，MySQL还允许过滤分组
规定包括哪些分组，排除哪些分组

与WHERE子句不同，WHERE过滤指定的是行而不是分组
事实上，WHERE没有分组的概念

MySQL为此目的提供了另外的子句：HAVING子句
HACING非常类似于WHERE
唯一的差别是WHERE过滤行，而HACING过滤分组

HAVING支持所有WHERE操作符：
前面所述的WHERE子句的条件(包括通配符条件和带多个操作符的子句)
所有的有关WHERE的计数和选项都适用于HAVING
它们的句法是相同的，只是关键字有差别

怎么过滤分组：

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

HAVING子句，过滤`COUNT(*)` >= 2的那些分组

这里WHERE子句不起作用，因为过滤是基于分组聚集值而不是特定行值的

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

有时，一条语句中需要同时使用HWERE和HAVING子句
输入：

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

• WHERE子句过滤所有prod_price至少为10的行
• 然后按vend_id分组数据
• HAVING子句过滤计数为2或2以上的分组

分组和排序

虽然GROUP BY和ORDER BY经常完成相同的工作，但它们是非常不同的
下表汇总了它们之间的差别：
表 ORDER BY与GROUP BY

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

GROUP BY分组的数据确实是以分组顺序输出的
但不总是这样，这并不是SQL规范所要求的
用户也可能会要求以不同于分组的顺序排序
仅因为某种反思分组(获得特定的分组聚集值)，并不表示需要以相同的方式排序输出
应提供明确的ORDER BY子句，即使效果等同于GROUP BY子句也是如此

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

输入：

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

• GROUP BY子句用来按照order_num列分组数据
  以便SUM(*)函数能够返回总计
• HAVING子句过滤数据，使得只返回总计订单价格大于等于50的订单
• 最后，用ORDER BY子句排序输出

SELECT子句顺序

回顾SELECT语句中子句的顺序
下表以在SELECT语句中使用时必须遵守的次序，列出迄今为止提到过的子句：
表 SELECT子句以及顺序

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

---

使用子查询

什么是子查询，如何使用子查询

子查询

版本要求：MySQL 4.1引入对子查询的支持

SELECT语句是SQL的查询，迄今为止的所有SELECT都是简单查询

​ 即：从单个数据库表中检索数据的单条语句

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

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

利用子查询进行过滤

例：查询出订购物品TNT2的所有客户

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

上述每个步骤都可以单独作为一个查询来执行

可以把一条SELECT语句返回的结果用于另一条SELECT语句的WHERE子句

也可以使用子查询来把2个查询组合成一条语句

输入：

SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
					FROM orderitems
					WHERE prod_id = 'TNT2');

​ 在SELECT语句中，子查询总是从内向外处理

内部的查询结果以IN操作符要求的逗号分隔的格式传递给外部查询的WHERE子句

外部查询变成：

SELECT cust_id FROM orders WHERE order_num IN (子查询第一个结果, 子查询第二个结果, ...)

格式化SQL：包含子查询的SELECT语句难以阅读和调试

​ 将子查询分解为多行并适当的进行缩进，能极大的简化子查询的使用

输入：

SELECT cust_name, cust_contact
FROM customer
WHERE cust_id IN (SELECT cust_id
				  FROM orders
				  WHERE order_num IN (SELECT order_num
				  					  FROM orderitems
				  					  WHERE prod_id = 'TNT2'));

可见，在WHERE子句中使用子查询能够编写出功能很强并很灵活的SQL语句

对于能嵌套的子查询数目没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询

列必须匹配：在WHERE子句中使用子查询，应该保证SELECT语句具有与WHERE子句中相同数目的列

​ 通常，子查询将返回列兵与单个列匹配

​ 但如果需要也可以使用多个列

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

子查询和性能：使用子查询并不总是执行这种类型(最初的例子)的数据检索的最有效的方法

作为计算字段使用子查询

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

例：

• 从customers表中检索客户列表
• 对检索出的每个客户，统计其在order表中的订单数目

可使用SELECT COUNT(*)对表中行进行计数

并通过提供一条WHERE子句来过滤某个特定的客户ID，可仅对该客户的订单进行计数

例：

SELECT COUNT(*) AS orders
FROM orders
WHERE cust_id = 10001;

为对每个客户执行COUNT(*)计算，应该将COUNT(*)作为一个子查询

输入：

SELECT cust_name,
	   cust_state,
	   (SELECT COUNT(*)
	   	FROM orders
	   	WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;

相关子查询：涉及外部查询的子查询

任何时候只要列名可能有多义性，就必须使用这种语法(表名和列名由一个句点分隔)

虽然子查询在构造这种SELECT语句时极有用，但必须注意限制有歧义性的列名

不止一种解决方案：虽然能用，但不是解决这种数据检索的最有效的方法

逐渐增加子查询来建立查询：用子查询测试和调试查询很有技巧性，特别是在语句的复杂性不断增加的情况下

用子查询建立(和车市)查询的最可靠的方法是逐渐进行

​ 这与MySQL处理它们的方法非常相同

• 首先建立和测试最内层的查询

• 然后用硬编码数据建立和测试外层查询

• 并且仅在确认它正常后再嵌入子查询

这时再测试它

对于要增加的每个查询，重复这些步骤

这样做仅给构造查询增加了一点点时间，但节省了以后找出查询为什么不正常的大量时间，并极大的提高了查询一开始就正常工作的可能性

---

联结表

什么是联结，为什么用联结，如何使用联结

联结

SQL最强大的功能之一：在数据检索查询的执行中联结表

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

以下介绍关系数据库设计的一些基础知识

关系表

相同数据出现多次绝不是一件好事，这是关系数据库设计的基础

关系表的设计：要保证把信息分解成多个表，一类数据一个表

各表通过某些常用的值(关系)互相关联

主键：唯一标识每一行

外键：某个表中的一列，包含另一个表的主键值，定义了两个表之间的关系

好处：

• 信息不重复，不浪费时间和空间
• 如果信息变动，可以只更新少量表中的单个记录，相关表中的数据不用改动
• 由于数据无重复，显然数据是一致的，处理数据更简单

关系数据可以有效的存储和方便的处理

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

可伸缩性：能够适应不断增加的工作量而不失败

​ 设计良好的数据库或应用程序称之为可伸缩性好

为什么要使用联结

分解数据为多个表能更有效的存储，更方便的处理，且具有更大的可伸缩性

好处是有代价的

如果数据存储在多个表中，如何用单条SELECT语句检索出数据？

​ 使用联结，一种机制，用来在一条SELECT语句中关联表，因此称为联结

使用特殊的语法，可联结多个表安徽一组输出，联结在运行时关联表中正确的行

维护引用完整性：联结在实际的数据库表中不存在

联结由MySQL根据需要建立，存在于查询的执行当中

在使用关系表时，仅在关系列中插入合法的数据非常重要

可指示MySQL只允许在对应列中出现合法值

这就是维护引用完整性，是通过在表的定义中指定主键和外键来实现的

创建联结

规定要联结的所有表以及它们如何关联即可

输入：

SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;

​ 此FRO子句列出了两个表，是联结的两个表的名字

这两个表用WHERE子句正确连接

完全限定列名：在引用的列可能出现二义性时，必须使用完全限定列名

WHERE子句的重要性

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

在数据库表的定义中不存在能指示MySQL如何对表进行联结的东西

实际上是将第一个表的每一行与第二个表中的每一行匹配

WHERE子句作为过滤条件，值包含哪些联结条件的行

笛卡儿积：由没有联结条件的表关系返回的结果为笛卡儿积

检索出的行的数目将是第一个表中的行数乘以第二个表中的行数

不要忘了WHERE子句：应该保证所有连接都有WHERE子句，否则MySQL将返回比想要的数据多得多的数据

同理，应该保证WHERE子句的正确性

​ 不正确的过滤条件将导致MySQL返回不正确的数据

叉联结：有时会听到返回称为叉联结的笛卡儿积的联结类型

内部联结

目前用的都为等值联结，基于两个表之间的相等性测试

这种联结也称为内部联结

​ 对于这种联结可以使用别的语法来明确指定联结的类型：

输入：

SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
 ON vendors.vend_id = products.vend_id;

​ FROM子句不同

这里两表之间的关系是FROM子句的组成部分，以INNER JOIN指定

在使用这种语法时，联结条件用特定的ON子句，而不是WHERE子句给出

​ 传递给ON的实际条件与传递给WHERE的相同

使用哪种语法：ANSI SQL规范首选INNER JOIN语法

使用明确的联结语法能够确保不会忘记联结条件，有时也能影响性能

联结多个表

SQL对一条SELECT语句中可以联结的表的数目没有限制

创建联结的基本规则也相同

先列出所有表，然后定义表之间的关系：

输入：

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;

​ FROM子句列出了3个表

​ WHERE子句定义了这两个联结条件

​ 第三个联结条件用来过滤出订单20005中的物品

性能考虑：MySQL在运行时关联指定的每个表以处理连接

这种处理可能是非常耗费资源的，应该仔细，不要联结不必要的表

​ 联结的表越多，性能下降的越厉害

输入：

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操作的最有效的方法，下面是使用联结的相同查询：

输入：

SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
	AND prderitems.order_num = orders.order_num
	AND prod_id = 'TNT2';

​ 没有使用嵌套子查询，而是使用了两个联结

多做实验：为执行任一给定的SQL操作，一般存在不足一种方法

很少有绝对正确或错误的方法，性能可能会受操作类型、表中数据量、是否存在索引或键、以及其他一些条件影响

因此，有必要对不同的选择机制进行实验，以找出最适合具体情况的方法

---

创建高级联结

另外一些联结类型，如何对被联结的表使用表别名和聚集函数

使用表别名

给列起别名的语法如下：

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

别名除了用于列名和计算字段外，SQL还允许给表名起别名

• 缩短SQL语句
• 允许在单条SELECT语句中多次使用相同的表

表别名不仅能用于WHERE子句，还能用于SELECT的列表、ORDER BY子句，以及语句的其他部分

应注意，别名只在查询执行中使用

与列别名不一样，表别名不返回到客户机

使用不同类型的联结

至今使用的称为内部联结或等值联结的简单链接

其他三种链接：自联结、自然联结、外部联结

自联结

使用表别名的主要原因之一：能在单条SELECT语句中不止一次引用相同的表

使用子查询：

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';

为避免二义性，使用了表别名

使用自联结而不用子查询：自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句

虽然最终结果相同，但优势处理联结远比处理子查询快得多

应该试一下两种方法，以确定哪一种的性能更好

自然联结

无论何时对表进行联结，应该有至少一个列出现在不止一个表中

标准的联结(内部联结)返回所有数据，甚至相同的列多次出现

自然联结排除多次出现，使每个列只返回一次

系统不完成这项工作，需要自己完成

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

输入：

SELECT c.*, o.order_num, order_date,
	oi.prod_id, oi.quantity, OI.item_price
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
	ANS oi.order_num = o.order_num
	AND prod_id = 'FB';

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

外部联结

联结包含了那些在相关表中没有关联行的行，这种类型的外联结称为外部联结

输入：

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

​ 使用了关键字OUTER JOIN来指定联结的类型(而不是在WHERE子句中指定)

与内部联结不同的是，外部联结还包括没有关联的行

在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表

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

外部联结的类型：存在两种基本的外部联结形式：左外部联结和右外部联结

​ 唯一差别：所关联的表的顺序不同

左外部联结可通过颠倒FROM或WHERE子句中表的顺序转换为右外联结

两种类型的外部联结可互换使用

使用带聚集函数的联结

聚集函数可以与联结一起使用

输入：

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;

使用联结和联结条件

关于联结及其使用的一些要点：

• 注意所使用的联结类型

​ 一般使用内部联结，但使用外部联结也是有效的

• 保证使用正确的链接条件，否则将返回不正确的数据
• 应该总是提供联结条件，否则会得出笛卡儿积
• 在一个联结中可以包含多个表，甚至对于每个链接可以采用不同的链接类型

​ 合法，且一般很有用，但应该在一起测试它们前，分别测试每个联结，使得故障排除更为简单

---

组合查询

利用UNION操作符将多条SELECT语句组合成一个结果集

组合查询

MySQL允许执行多个查询(多条SELECT语句)，并将结果作为单个查询结果集返回

这些组合查询通常被称为并或复合查询

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

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

组合查询和多个WHERE条件：任何具有多个WHERE子句的SELECT语句都可以作为一个组合查询给出

两种技术在不同查询中性能不同，应该测试一下，以确定对特定的查询哪一种性能更好

创建组合查询

可用UNION操作符来组合数条SQL查询

可给出多条SELECT语句，将它们的结果组合成单个结果集

使用UNION

在各条语句之间放上关键字UNION

输入：

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);

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

使用多条WHERE子句的相同查询：

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

UNION规则

并是非常容易使用的

有几条规则需要注意：

• UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔
• UNION中的每个查询必须包含相同的列、表达式、聚集函数(不过各列次序不需要相同)
• 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含的转换的类型

如果遵循这些基本规则或限制，则可以将并用于任何数据检索任务

包含或取消重复的行

UNION从查询结果集中自动去除重复的行

这是UNION的默认行为，如果需要可以改变

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

UNION与WHERE：UNION几乎总是完成与多个WHERE条件相同的工作

UNION ALL为UNION的一种形式，完成了WHERE子句完成不了的工作

对组合查询结果排序

在用UNION组合查询时，只能使用一条ORDER BY子句

必须出现在最后一条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)
ORDER BY vend_id, prod_price;

组合不同的表：使用UNION的组合查询可以应用于不同的表

---

全文本搜索

理解全文本搜索

并非所有引擎都支持全文本搜索：MySQL支持几种基本的数据库引擎，最常用的引擎为MyISAM和InnoDB

​ 前者支持全文本搜索，如果应用中需要全文本搜索功能，应该记住这点

LINK关键字利用通配操作符匹配文本

​ 使用LIKE能查找包含特殊值或部分值的行(不管这些值位于列内什么位置)

​ 使用正则表达式可以编写查找所需行的非常复杂的匹配模式

虽然这些搜索机制非常有用，但存在几个重要限制：

• 性能：

  ​ 通配符和正则表达式匹配通常要求MySQL尝试匹配表中所有行(且这些搜索极少使用表索引)

  ​ 由于被搜索行书不断增加，这些搜索可能非常耗时

• 明确控制：

​ 使用通配符和正则表达式匹配，很难明确的控制匹配什么和不匹配什么

• 智能化的结果：

  ​ 虽然基于通配符和正则表达式的搜索提供了非常灵活的搜索，但都不能提供一种智能化的选择结果的方法

所有这些限制以及更多限制都可以用全文本搜索来解决

使用全文本搜索时，MySQL不需要分别查看每个行，不需要分别分析和处理每个词

MySQL可以快速有效的决定哪些词匹配、不匹配、频率等

使用全文本搜索

为了进行全文本搜索，必须索引被搜索的列，且要随着数据的改变不断的重新索引

在对表列进行适当的设计后，MySQL会自动进行所有的索引和重新索引

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

启用全文本搜索支持

一般在创建表时启用全文本搜索

CREATE TABLE语句接受FULLTEXT子句，它给出被索引列的一个逗号分隔的列表

输入：

CREATE TABLE productnotes
(
	note_id		int 		NOT NULL AUTO_INCREMENT,
	prod_id		char(10)	NOT NULL,
	note_date	datetime	NOT NULL,
	not_text	text		NULL,
	PRIMARY KEY(note_id),
	FULLTEXT(note_text)
) ENGING = MyISAM;

​ MySQL根据子句FULLTEXT(note_text)的指示对它进行索引

这里FULLTEXT索引单个列，如果需要也可以指定多个列

在定义之后，MySQL自动维护该索引

​ 在增加、更新、删除行时，索引随之自动更新

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

不要在导入数据时使用FULLTEXT：更新索引要花时间

应该先导入所有数据，然后再修改表，定义FULLTEXT

这样有助于更快的导入数据

(且使用索引数据的总时间小于在导入每行时分别进行索引所需要的总时间)

进行全文本搜索

在索引之后，使用两个函数Match()和Against()执行全文本搜索

• Match()指定被搜索的列
• Against()指定要使用的搜索表达式

输入：

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

使用完整的Match()说明：传递给Match()的值必须与FULLTEXT()定义中的相同

​ 如果指定多个列，则必须列出它们(且次序正确)

搜索不区分大小写：除非使用BINARY方式，否则全文本搜索不区分大小写

全文本搜索对结果排序，具有较高等级的行先返回

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

全文本搜索提供了简单LIKE搜索不能提供的功能

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

使用查询扩展

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

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

利用查询扩展，能找出可能相关的结果，即使它们并不精确包含所查找的词

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

输入：

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);

查询扩展极大的增加了返回的行数，但也增加了实际并不想要的行的数目

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

布尔文本搜索

MySQL支持全文本搜索的另外一种形式：布尔方式

可提供如下内容的细节：

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

即使没有FULLTEXT索引也可以使用：布尔方式不同于迄今为止使用的全文本搜索语法的地方在于

​ 即使没有定义FULLTEXT索引，也可以使用它

​ 但这是一种非常缓慢的操作(其性能将随着数据量的增加而降低)

演示IN BOLLEAN MODE的作用：

SELECT note_text
FROM productnnotes
WHERE Match(note_text) Against('heavy' IN BOOLEAN MODE);

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

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

在MySQL 4.x中所需的代码更改：如果用的MySQL4.x，则上例可能不返回任何行

​ 因为*操作符处理中的一个错误

MySQL 4.x中可使用-ropes而不是-rope*(排除ropes而不是排除任何以rope开始的词)

下表列出支持的所有布尔操作符

表 全文本布尔操作符

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

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

全文本搜索的使用说明

全文本搜索的某些重要的说明：

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

---

插入数据

如何利用INSERT语句将数据插入表中

数据插入

INSERT用来插入(添加)行到数据库表

插入可以用集中方式使用：

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

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

插入完整的行

把数据插入表中的最简单的方法：使用基本的INSERT语法

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

输入：

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

没有输出：INSERT语句一般不会产生输出

各个列必须以它们在表定义中出现的次序填充

如果不想给出一个值，又不能省略此列，可以指定一个NULL值(被MySQL忽略，MySQL在此处插入下一个可用的值(对于自动增量))

虽然简单，但并不安全，应该尽量避免使用

高度依赖于表中列的定义次序，且还依赖于次序容易获得的信息

编写INSERT语句的更安全(不过更繁琐)的方法如下：

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必须以其指定的次序匹配指定的列名，不一定按照各个列出现在实际表中的次序

优点：即使表结构改变，此INSERT语句仍然能正确工作

总是使用列的列表：一般不要使用没有明确给出列的列表的INSERT语句

​ 使用列的列表能使SQL代码继续发挥作用，即使表结构发生了变化

使用这种语法还能省略列，值给某些列提供值

省略列：如果表的定义允许，则可以在INSERT操作中省略某些列

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

• 该列定义为允许NULL值(无值或空值)
• 该表定义中给出默认值
  • 这表示如果不给出值，将使用默认值

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

提高整体性能：INSERT操作可能很耗时，且可能降低等待处理的SELECT语句的性能

如果数据检索是最重要的(通常是这样)，则可以通过在INSERT和INTO之间添加关键字LOW_PRIORITY指示MySQL降低INSERT语句的优先级：

​ INSERT LOW_PRIORITY INTO

这也适用于UPDATE和DELETE语句

插入多个行

可以使用多条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'
);

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

提高INSERT的性能：此技术可以提高数据库处理的性能

​ 因为MySQL用单条INSERT语句处理多个插入比使用多条INSERT语句快

插入检索出的数据

ISNERT还存在另一种形式，可以利用它将一条SELECT语句的结果插入表中

这就是INSERT SELECT，由一条INSERT语句和一条SELECT语句组成

注：不应该使用在表中使用过的主键值(如果主键值重复，后续的INSERT操作将会失败)或仅省略这个值让MySQL在导入数的过程中产生新值

输入：

INSERT INTO customers(cust_id.
	cust_name,
	cust_address,
	cust_city,
	cust_state,
	cust_zip,
	cust_country)
SELECT cust_id.
	cust_name,
	cust_address,
	cust_city,
	cust_state,
	cust_zip,
	cust_country
FROM custnew;

如果这个表为空，则没有行被插入(也不产生错误，因为操作仍然是合法的)

INSERT SELECT中的列名：为简单起见，使用了相同的列名

实际上，不一定要求列名匹配，MySQL甚至不关心SELECT返回的列名

​ 它使用的是列的位置

这对于从使用不同列名的表中导入数据是非常有用的

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

---

更新和删除数据

如何利用UPDATE和DELETE语句进一步操纵表数据

更新数据

为更新(修改)表中的数据，可使用UPDATE语句

可采用两种方式使用UPDATE：

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

不要省略WHERE子句：在使用UPDATE时一定要注意，否则就会更新表中所有行

UPDATE与安全：可以限制和控制UPDATE语句的使用

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

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

输入：

UPDATE customers
SET cust_emaio = '[email protected]'
WHERE cust_id = 10005;

UPDATE语句以WHERE子句结束，它告诉MySQL更新哪一行

没有WHERE子句，MySQL将会更新表中所有行

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

UPDATE customers
SET cust_name = 'The Fudds',
	cust_email = '[email protected]'
WHERE cust_id = 10005;

在更新多个列式，只需要使用单个SET命令

每个”列=值”对之间用逗号分隔(最后一列之后不用逗号)

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

IGNORE关键字：如果用UPDATE语句更新多行，并且在更新这些行中的一行或多行时出现一个错误

​ 则整个UPDATE操作被取消

为即使是发生错误，也继续更新，可使用IGNORE关键字：

​ UPDATE IGNORE customers...

为了删除某个列的值，可设置它为NULL(假如表定义允许NULL值)

输入：

UPDATE customers
SET cust_email = NULL
WHERE cust_id = 10005;

其中NULL用来去除cust_email列中的值

删除数据

使用DELETE语句，可用两种方式使用DELETE：

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

不要省略WHERE子句：在使用DELETE时一定要注意细心，因为稍不注意，就会错误的删除表中所有行

DELETE与安全：可以限制和控制DELETE语句的使用

DELETE比UPDATE还容易使用：

输入：

DELETE FROM customers
WHERE cust_id = 10006;

DELETE不需要列名或通配符

DELETE删除整行而不是删除列

为了删除列，请使用UPDATE语句

删除表的内容而不是表：DELETE语句从表中删除行，甚至删除表中所有行

​ 但是，DELETE不删除表本身

更快的删除：如果想从表中删除所有行，不要使用DELETE

​ 可使用TRUNCATE TABLE语句，完成相同的工作，但速度更快(TRUNCATE实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据 )

更新和删除的指导原则

使用UPDATE或DELETE时所遵循的习惯：

• 除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句

• 保证每个表都有主键（如果忘记这个内容，请参阅第15章），尽可能像WHERE子句那样使用它（可以指定各主键、多个值或值的范围）

• 在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确

• 使用强制实施引用完整性的数据库，这样MySQL将不允许删除具有与其他表相关联的数据的行。

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

---

创建和操纵表

表的创建、更改和删除的基本常识

创建表

MySQL可以执行数据库和表的所有操作，包括表本身的创建和处理

一般的两种创建表的方法：

• 使用具有交互式创建和管理表的工具
• 表也可以直接用MySQL语句操纵

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

在使用交互式工具时，实际上使用的是MySQL语句

表创建基础

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

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

CREATE 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;

表的主键可以在创建表时用PRIMARY KEY关键字指定

语句格式化：MySQL语句中忽略空格

​ 使用某种缩进格式可以便于阅读和编辑，SQL语句没有规定缩进格式

处理现有的表：在创建新表时，指定的表名必须不存在，否则将出错

​ 如果要防止意外覆盖已有的表，SQL要求先手工删除该表，然后再重建它，而不是简单的用创建表语句覆盖它

​ 如果仅想在一个表不存在时创建它，应在表名后给出IF NOT EXISTS

​ 这样不做检查已有表的模式是否与打算创建的表模式相匹配

​ 只查看表名是否存在，且仅在表名不存在时创建它

使用NULL值

在插入或更新行时，不允许NULL值的列必须有值

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

输入：

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为默认设置，如果不指定NOT NULL，则认为指定的是NULL

理解NULL：NULL值与空串不同，空串是有效值

主键再介绍

主键值必须唯一：表中每行必须具有唯一的主键

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

创建单个列组成的主键：PRIMARY KEY (vend_id)

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

PRIMARY KEY (order_num, order_item)

主键可以在创建表时定义，或者在创建表之后定义

主键和NULL值：主键中只能使用不允许NULL值的列

​ 允许NULL值的列不能作为唯一标识

使用AUTO_INCREMENT

cust_id   int   NOT NULL AUTO_INCREMENT

告知MySQL，本列每当增加一行时自动增量

每次执行一个INSERT操作时，MySQL自动对该列增量

​ 给改列赋予下一个可用的值

这样给每个行分配一个唯一的值，从而可以用作主键值

每个表只允许一个AUTO_INCREMENT列，且它必须被索引(如，通过使它成为主键)

覆盖AUTO_INCREMENT：可以简单的再INSERT语句中指定一个值，只要它是唯一的(至今尚未使用过)即可

​ 该值将被用来替代自动生成的值

后续的增量将开始使用该手工插入的值

确定AUTO_INCREMENT值：让MySQL生成(通过自动增量)主键的一个缺点是：不知道这些值是什么

可使用laet_insert_id()函数：

​ SELECT last_insert_id()

返回最后一个AUTO_INCREMENT值

指定默认值

如果在插入行时没有给出值，MySQL允许指定此时使用的默认值

默认值用CREATE TABLE语句的列定义中的DEFAULT关键字指定

输入：

CREATE TABLE orderitems
(
	quantity  int  NOT NULL DEFAULE 1
) ENGINE=InnoDB;

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

使用默认值而不是NULL值：许多数据库开发人员使用默认值而不是NULL列

​ 特别是对于计算或数据分组的列更是如此

引擎类型

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

在使用CREATE TABLE语句时，该引擎具体创建表

而在使用SELECT语句或进行其他数据库处理时，该引擎在内部处理你的请求

多数时候，此引擎都隐藏在DBMS内，不需要过多关注它

MySQL与其他DBMS不同，具有多种引擎

​ 它打爆多个引擎，引擎都隐藏在MySQL服务器内，全都能执行CREATE TABLE和SELECT等命令

发行多种引擎的原因：

​ 都具有不同的功能和特性，为不同的任务选择正确的引擎能获得良好的功能和灵活性

如果省略ENGINE=语句，则使用默认引擎(很可能是MyISAM)

需要知道的几个引擎：

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

引擎类型可以混用

外键不能跨引擎：混用引擎类型有一个大缺陷，外键(用于强制实施引用完整性)不能跨引擎

​ 即，使用一个引擎的表不能引用具有使用不同引擎的表的外键

更新表

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

但理想状态下，当表中存储数据以后，该表就不应该再被冰箱

在表的设计过程中需要花费大量时间来考虑，以便后期不对该表进行大的改动

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

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

例：

ALTER TABLE vendors
ADD vend_phone CHAR(20);

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

删除列：

ALTER TABLE Vendors
DROP COLUMN vend_phone;

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

ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);

复杂的表结构更改一般需要手动删除过程，涉及以下步骤：

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

**小心使用ALTER TABLE**：使用ALTER TABLE要极为小心，应该在进行改动前做一个完整的备份(模式和数据的备份)

​ 数据库表的更改不能撤销，如果增加了不需要的列，可能不能删除它们

​ 类似的，如果删除了不该被删除的列，可能会丢失该列中的所有数据

删除表

删除整个表而不是内容，使用DROP TABLE语句：

DROP TABLE customers2;

删除customers2表(假设它存在)

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

重命名表

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

RENAME TABLE customers2 TO customers;

仅重命名一个表

可用下面的语句对多个表重命名：

RENAME TABLE backup_customers TO customers,
			 backup_vendors TO vendors,
			 backup_products TO products;

---

使用视图

视图是什么，怎么工作，何时使用

视图

需要MySQL 5

视图是虚拟的表

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

视图的作用：将查询包装成一个虚拟表，可轻松用于检索

视图不包含表中应该有的任何列或数据，包含的只是一个SQL查询

为什么使用视图

视图的一些常见应用：

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

在视图创建后，可以用于表基本相同的方式利用它们

可对视图执行SELECT操作，过滤和排序数据，将试图联结到其他视图或表，甚至能添加和更新数据(存在某些限制)

视图仅仅是用来查看存储在别处的数据的一种设施，本身不包含数据

性能问题：因为视图不包含数据，所以每次使用视图时都必须处理查询执行时所需的人一个检索

如果用多个联结和过滤创建了复杂的视图或嵌套了视图，可能性能下降的很厉害

​ 因此在部署使用了大量视图的应用前，应该进行测试

视图的规则和限制

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

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

使用视图

视图的创建

• 视图用CREATE VIEW来创建
• 使用SHOW CREATE VIEW viewname;来查看创建视图的语句
• 用DROP删除视图，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW
  • 如果要更新的视图不存在，则后者会创建一个视图
  • 如过要更新的视图存在，则后者会替换原有视图

利用视图简化复杂的联结

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

例：

CREATE VIEW procuctcustomers AS
SELECT cust_name, cust_contact, prod_id
FROM customers, order, 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';

通过WHERE子句从视图中检索特定数据

可以看出，视图极大的简化了复杂的SQL语句的使用

利用视图，可一次性编写基础的SQL，然后根据需要多次使用

创建可重用的视图：创建不受特定数据限制的视图是好办法

​ 扩展视图的范围不仅使得它能被重用，而且甚至更有用

​ 这样做不需要创建和维护多个类似的视图

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

视图的另一常见用途：重新格式化检索出的数据

例：

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

假如经常需要使用这个格式的结果，不必每次需要时执行联结

​ 创建一个视图，每次需要时使用它即可

把此语句转换成视图：

CREATE VIEW vendorlocations AS
SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title
FROM vendors
ORDER BY vend_name;

用视图过滤不想要的数据

视图对于应用普通的WHERE子句也很有用

输入：

CREATE VIEW customeremaillist AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL;

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

使用视图与计算字段

视图对于简化计算字段的使用特别有用

例：

CREATE VIEW orderitemsexpanded AS
SELECT order_num,
	   prod_id,
	   quantity,
	   item_price,
	   quantity*item_price AS expanded_price
FROM orderitems;

使用：

SELECT *
FROM orderitemsexpanded
WHERE order_num = 20005;

可见，视图非常任意创建，而且很好使用。

正确使用，视图可极大的简化复杂的数据处理

更新视图

视图能否更新视情况而定

通常视图是可更新的(即对其使用INSERT、UPDATE、DELETE)

​ 更新一个视图将更新其基表，如果对视图增加或删除行，实际是对其基表增加或删除行

如果MySQL不能正确的确定被更新的基数据，则不允许更新(包括插入和删除)

实际意味着，如果视图定义中有以下操作，则不能进行视图的更新：

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

似乎是个很严重的限制，但实际上不是，因为视图主要用于数据检索

可能的变动：上述的限制自MySQL 5以来是正确的，以后就不一定了

将视图用于检索：一般，应该将视图用于检索(SELECT语句)而不用于更新(INSERT、UPDATE、DELETE)

---

使用存储过程

存储过程

需要MySQL 5

迄今为止，使用的大多数SQL语句都是针对一个或多个表的单条语句

并非所有操作都这么简单，经常会议一个完整的操作需要多条语句才能完成

可以单独编写每条语句，并根据觉过有条件的执行另外的语句

​ 在每次需要这个处理时都必须做这些工作

可以创建存储过程：为了以后的使用而保存的一条或多条MySQL语句的集合

​ 可将其视为批文件，虽然它们的作用不仅限于批处理

为什么要使用存储过程

为什么要使用存储过程，一些主要的理由：

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

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

在将SQL代码转换为存储过程前，必须知道它的一些缺陷：

• 一般来说，存储过程的编写比基本SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验
• 你可能没有创建存储过程的安全访问权限
  • 许多数据库管理员限制存储过程的创建权限，允许用户使用存储过程，但不允许他们创建存储过程

尽管有这些缺陷，存储过程还是非常有用的，并且应该尽可能的使用

不能编写存储过程？仍然可以使用：MySQL将编写存储过程的安全和访问与执行存储过程的安全和访问区分开来

​ 这是好事，即使不能(或不想)编写自己的存储过程也仍然可以在适当的时候执行别的存储过程

使用存储过程

MySQL称存储过程的执行为调用因此MySQL执行存储过程的语句为CALL

CALL接受存储过程的名字 以及 需要传递给它的任意参数

输入：

CALL productpricing(@pricelow,
					@pricehigh,
					@priceaverage);

其中，执行名为productpricing的存储过程

​ 它计算并返回产品的最低、最高和平均价格

存储过程可以显示结果，也可以不显示结果

创建存储过程

例：

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语句分隔符为;

​ mysql命令行实用程序也使用;作为语句分隔符

​ 如果命令行实用程序要接受存储过程自身内的;字符，则它们最终不会成为存储过程的成分，这回使存储过程中的SQL出现语法错误

解决办法是临时更改命令行实用程序的语句分隔符，如下所示：

DELIMITER //

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

DELIMITER ;

• 其中DELIMITER //告诉命令行实用程序使用//作为新的语句结束分隔符

• 可以看到标志存储过程结束的END定义为END //而不是END

• 这样。存储过程体内的;仍然保持不动，并且正确的传递给数据库引擎

• 最后，为恢复为原来的语句分隔符，可使用DELIMITER ;

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

如何使用存储过程：

CALL productpricing();

执行刚刚创建的存储过程并显示返回的结果

因为存储过程实际上是一种函数，所以存储过程名称后需要有()符号(即使不传递参数也需要)

删除存储过程

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

删除命令从服务器中删除存储过程

输入：

DROP PROCEDURE productpricing;

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

仅当存在时删除：如果指定的过程不存在，则DROP PROCEDURE将产生一个错误

​ 当存储过程想删除它是(如果过程不存在也不产生错误)可使用DROP PROCEDURE IF EXISTS

使用参数

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

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

以下是prodctpricing的修改版本：

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;

此存储过程接受3个参数，每个参数必须具有指定的类型，这里使用十进制

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

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

存储过程的代码位于BEGIN和END语句内

​ 如前所见，是一系列SELECT语句，用来检索值，然后保存到相应的变量(通过指定INTO关键字)

参数的数据类型：存储过程的参数允许的数据类型与表中使用的数据类型相同

​ 注意，记录集不是允许的类型，因此，不能通过一个参数返回多个行和列

​ 因此前面的例子使用了3个参数

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

CALL productpricing(@pricelow,
					@pricehigh,
					@priceaverage);

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

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

调用时，这条语句不显示任何数据，返回以后可以显示(或在其他处理中使用)的变量

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

SELECT @priceaverage;

为获得3个值，可以使用以下语句：

SELECT @pricehigh, @pricelow, @priceaverage;

另一个例子，使用IN和OUT参数：

CREATE PROCEDURE ordertotal(
	IN onumber INT,
	OUT total  DECIMAL(8,2)
)
BEGIN
	SELECT Sum(item_price*quantity)
	FROM orderitems
	WHERE order_num = number
	INTO ototal;
END;

​ INTO使用ototal存储结果

为调用这个存储过程并获得显示，需先调用存储过程，然后显示变量：

CALL ordertotal(20005, @tota;);
SELECT @total;

建立智能存储过程

迄今为止使用的所有存储过程基本上是封装MySQL简单的SELECT语句

虽然都是有效的存储过程，但能完成的工作直接用被封装的语句就能完成

只有在存储过程内包含业务规则和智能处理时，才更能提心它们的威力

如：

• 获得合计
• 把营业税有条件的添加到合计
• 返回合计

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

-- Name: ordertota;
-- Parameters: onumber = order number
--			   taxable = 0 if not taxable, 1 if taxable
--			   ototal = order total variable

CREATE PROCEDURE ordertotal(
	IN onumber INT,
	IN taxable BOOLEAN,
	OUT ototal DECIMAL(8,2)
) COMMEND 'Obtain order total, optionally adding tax'
BEGIN

	-- Declare variable for total
	-- 声明变量total
	DECLARE total DECIMAL(8,2);
	-- Declare tax percentage
	-- 定义税金百分比tax
	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
		-- 需要交税
		SELECT total+(total/100*taxrate) INTO total;
	END IF;
	
	-- 最后 存储变量
	SELECT total INTO ototal;
	
END;

• 首先，增加了注释(前面防止--)
• 添加了另一个参数taxable
  • 是一个布尔值(如果要增加稅则为真，否则为假)
• 在定义存储过程体中，用DECLARE语句定义了两个局部变量
  • DECLARE要求指定变量名和数据类型
  • 它也支持可选的默认值(此例中的taxrate的默认值被设置为6%)
• SELECT语句已改变，因此结果存储到total(局部变量)而不是ototal
• IF语句检查taxable是否为真
  • 如果为真，则用另一SELECT语句增加营业税到局部变量total
• 最后，用另一SELECT语句将total(增加或不增加营业税)保存到ototal

COMMENT关键字：本例中的存储过程在CREATE PROCEDURE语句中包含了一个COMMENT值

​ 它不是必须的，但如果给出，将在SHOW PROCEDURE STATUS的结果中显式

这显然是个更高级，功能更强的存储过程

为了试验它，使用以下两条语句：

CALL ordertotal(20005, 0, @total);
SELECT @total;

IF语句：此例给出了MySQL的IF语句的基本用法

​ IF语句还支持ELSEIF和ELSE语句(前者还使用THEN子句，后者不能使用)

检查存储过程

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

SHOW CREATE PROCEDURE ordertotal;

为获得包括合适、由谁创建的详细信息的存储过程列表，使用SHOW PROCEDURE STATUS

限制过程状态结果：SHOW PROCEDURE STATUS列出所有存储过程

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

​ 例如：

​ SHOW PROCEDURE STATUs LIKE 'ordertotal';

---

使用游标

什么是游标以及如何使用游标

游标

需要MySQL 5：MySQL 5添加了对游标的支持

MySQL检索操作返回一组称为结果集的行
这组返回的行动是与SQL语句相匹配的行(0行或多行)
使用简单的SELECT语句，没法得到某些行，也不能在每次一行的处理所有行的简单方法(相对于成批的处理它们)

使用游标的原因：有时需要在检索出来的行中前进或后退一行或多行
游标：是一个存储在MySQL服务器上的数据库查询
它不是一条SELECT语句，而是被该语句检索出来的结果集
在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据

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

只能用于存储过程：不像多数DBMS，MySQL游标只能用于存储过程(和函数)

使用游标

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

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

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

创建游标

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

例：下面语句定义了名为ordernumbers的游标，使用了可以检索所有orders的语句
输入：

CREATE PROCEDURE processorders()
BEGIN
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
END;

这个存储过程并没有做很多事
DECLARE语句用来定义和命名游标，这里为ordernumbers
存储过程处理完成后，游标就消失(因为它局限于存储过程)

在定义游标之后，可以打开它

打开和关闭游标

游标用OPEN CURSOR语句来打开
输入：

OPEN ordernumbers;

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

游标处理完成后，应当使用如下关闭游标：
输入：

CLOSE ordernumbers;

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

在一个游标关闭后，如果没有重新开始，则不能使用它
但，使用声明过的游标不需要再次声明，用OPEN语句开始它就可以了

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

下面是前面例子的修改版本：
输入：

CREATE PROCEDURE processorders()
BEGIN
	-- 定义游标
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;

	-- 打开游标
	OPEN ordernumbers;
	
	-- 关闭游标
	CLOSE ordernumbers;

END;

​ 这个存储过程声明、打开和关闭一个游标
​ 但对检索出的数据什么也没做

使用游标数据

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

例：从游标中检索单个行(第一行)：
输入：

CREATR PROCEDUER processorders()
BEGIN
	
	-- 声明局部变量
	DECLARE o INT;

	-- 定义游标
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;

	-- 打开游标
	OPEN ordernumbers

	-- 获取ordernumbers
	FETCH ordernumbers INTO o'

	-- 关闭游标
	CLOSE ordernumbers;

END;

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

下例：循环检索数据，从第一行到最后一行：
输入：

CREATE PROCEDURE processorders()
BEGIN
	-- 声明局部变量
	DECLARE done BOOLEAN DEFAULT 0;
	DECLARE o INT;

	-- 定义游标
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;

	-- 声明 continue 处理程序
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

	-- 打开游标
	OPEN ordernumbers;

	-- 循环遍历所有行
	REPEAT

		-- 获取ordernumbers
		FETCH ordernumbers INTO o;

	-- 循环结束
	UNTIL done END REPEAT;

	-- 关闭游标
	CLOSE ordernumbers;

END;

使用FETCH检索当前order_num到声明的名为o的变量中
与前一个例子不同的是，这个例子中的FETCH是在REPEAT内

• 因此它反复执行直到done为真(由UNTIL done END REPEAT;规定)
• 为使其起作用，用一个DEFAULT 0(假，不结束)定义变量done

那么，done怎样才能在结束时被设置为真？
使用以下语句：

DECLART CONTINUE HANDLER FOR SQLSTARTE ’02000‘ SET done=1;

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

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

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

如果调用这个存储过程

• 将定义几个变量和一个CONTINUE HANDLER
• 定义并打开一个游标
• 重复读取所有行
• 然后关闭游标

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

重复或循环？：除这里使用的REPEAT语句外，MySQL还支持循环语句
它可用来重复执行代码，直到使用LEAVE语句手动退出位置
通常REPEAT语句的语法使它更适合于对游标进行循环

为把这些内容组织起来，下面给出游标存储过程阳历的更进一步修改的版本
这次对去除的数据进行某种实际的处理：
输入：

CREATE PROCEDURE processorders()
BEGIN
	
	-- 定义局部变量
	DECLARE done BOOLEAN DEFAULT 0;
	DECLARE o INT;
	DECLARE t DECIMAL(8, 2);

	-- 声明游标
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;

	-- 定义CONTINUE HANDLER
	DECLARE CONTINUE HANDLER FOR SQLSRARE '02000' SET done=1;

	-- 创建表以存储结果
	CREATR TABLE IF NOT EXISTS ordertotals
		(order_num INT, total DECIMAL(8, 2));

	-- 打开游标
	OPEN ordernumbers;

	-- 循环遍历所有行
	REPEAT
	
		-- 获取ordernumber
		FETCH ordernumbers INTO o;

		-- 获取total总计
		CALL ordertotal(o, 1, t);

		-- 将 order 和 total 插入到 ordertotals 中
		INSERT INTO ordertotals(order_num, total)
		VALUES(o, t);

	-- 循环结束
	UNTIL done END REPEAT;

	-- 关闭游标
	CLOSE ordernumbers;

END;

• 本例中，增加了另一个名为t的变量(存储每个total的合计)
• 此存储过程还在运行中创建了一个新表(如果它不存在的话)，名为ordertotals
  这个表将保存存储过程生成的结果
• FETCH像以前一样取每个order_num
  然后用CALL执行另一个存储过程(之前创建的)来计算每个合计(结果存储到t)
• 最后，用INSERT保存每个订单的订单号和合计

此存储过程不返回数据，但它能够创建和填充另一个表
可以用一条简单的SELECT语句查看该表：

SELECT *
FROM ordertotals;

这样就得到了存储过程、游标、逐行处理以及存储过程条用其他存储过程的一个完整的工作样例

---

使用触发器

触发器

需要MySQL 5

MySQL在需要时被执行，存储过程也是如此

如果想要某条语句(或某些语句)在事件发生时自动执行呢

例：在某个表发生更改时自动处理，使用触发器

触发器：是MySQL相应以下任意语句而自动执行的一条MySQL语句

​ (或位于BEGIN和END语句之间的一组语句)

• DELETE
• INSERT
• UPDATE

其他MySQL语句不支持触发器

创建触发器

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

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

保持每个数据库的触发器名唯一：在MySQL5中，触发器名必须在每个表中唯一，但不是在每个数据库中唯一

​ 在其他每个数据库触发器名必须唯一的DBMS中是不允许的，而且未来MySQL版本可能会使命名规则更为严格

​ 因此，最好在数据库范围内使用唯一的触发器名

触发器用CREATE TRIGGER语句创建：

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

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

此例中，文本Product added将对每个插入的行显示一次

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

触发器按每个表每个事件每次的定义

​ 每个表每个事件每次只允许一个触发器

因此每个表最多支持6个触发器(每条INSERT、UPDATE和DELETE的之前和之后)

单一触发器不能与多个事件或多个表关联

​ 所以如果需要对一个INSERT和UPDARE操作执行的触发器，则应该定义两个触发器

触发器失败：如果BEFORE触发器失败，则MySQL将不执行请求的操作

​ 此外没如果BEFORE触发器或语句本身失败，MySQL将不执行AFTER触发器(如果有的话)

删除触发器

为删除一个触发器，可使用DROP TRIGGER语句：

DROP TRIGGER newproduct;

​ 触发器不能更新或覆盖

为了修改一个触发器，必须先删除它，然后再重新创建

使用触发器

所支持的每种触发器类型以及它们的差别

INSERT触发器

在INSERT语句执行之前或之后执行

需要知道以下几点：

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

例：AUTO_INCREMENT列具有MySQL自动赋予的值

CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW,order_num;

但这是一种确定新生成的值的更好的方法

DEFORE或AFTER？：通常，将BEFORE用于数据验证和净化(目的是保证插入表中的数据确实是需要的数据)

​ 本提示也适用于UPDATE触发器

DELETE触发器

在DELETE语句执行之前或之后执行

需要知道以下两点：

• 在DELETE触发器代码内，可以引用一个名为OLD的虚拟表，访问被删除的行
• 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;

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

多语句触发器：触发器deleteorder使用BEGIN和END语句标记触发器体

​ 这并不是必须的，但也没有害处

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

UPDATE触发器

在UPDATE语句执行之前或之后执行

需要知道以下几点：

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

例：保证州名缩写总是大写(不管UPDATE语句中给出的是大写还是小写)

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

显然，任何数据净化都需要再UPDATE语句之前进行，就像此例中一样

​ 每次更新一个值时，NEW.vend_state中的值(将用来更新表行的值)都用Upper(NEW.vend_state)替换

关于触发器的进一步介绍

一些使用触发器时需要记住的重点：

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

---

管理事务处理

什么是事务处理 以及 如何利用COMMIT和ROLLBACK语句来管理事务处理

事务处理

并非所有引擎都支持事务处理：MySQL支持几种基本的数据库引擎

​ 并非所有引擎都支持明确的事务处理管理

MyISAM和InnoDB是两种最常使用的引擎

• 前者不支持明确的事务处理管理，而后者支持

如果应用中需要事务处理功能，则一定要使用正确的引擎类型

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

关系数据库设计吧数据鵆在多个表中，使数据更容易操纵、维护和重用

​ 在某种程度上说，设计良好的数据库模式都是关联的

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

利用事务处理，可以保证一组操作不会中途停止，它们或者作为整体执行，或者完全不执行(除非明确指示)

如果没有错误发生，整组语句提交给(写到)数据库表

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

在使用事务和事务处理时，有几个关键词汇反复出现

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

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

控制事务处理

管理事务处理的关键在于：将SQL语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退

MySQL使用下面的语句来标识事务的开始：

START TRANSACTION

使用ROLLBACK

MySQL的ROOLBACK命令用来回退(撤销)MySQL语句

例：

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

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

显然，ROLLBACK只能在一个事务处理内使用(在执行一条START TRANSACTION命令之后)

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

• 不能回退SELECT语句(这样做也没有什么意义)

• 不能回退CREATE或DROP操作

  • 事务处理块中可以使用这两条语句，但如果你执行回退，它们不会被撤销

使用COMMIT

一般的MySQL语句都是直接针对数据库表执行和编写的

​ 这就是所谓的隐含提交：即提交(写或保存)操作是自动进行的

但，在事务处理块中，提交不会隐含的进行

为进行明确的提交，使用COMMIT语句，如下所示：

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

最后的COMMIT语句不仅在不出错时写出更改

如果第一条DELETE起作用，但第二条失败，则DELETE不会提交(实际上，它是被自动撤销的)

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

使用保留点

简单的ROLLBACK和COMMIT语句就可以写入或撤销整个事务处理

​ 但，只是对简单的事务处理才能这样做，更复杂的事务处理可能需要部分提交或回退

为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符

​ 这样，如果需要回退，可以回退到某个占位符

这些占位符称为保留点

为了创建占位符，可使用SAVEPOINT语句：

SAVEPOINT delete1;

每个保留点都取标志它的唯一名字，以便在回退时，MySQL知道要回退到何处

为了回退到本例给出的保留点，可如下进行：

ROLLBACK TO delete1;

保留点越多越好：可以在MySQL代码中设置任意多的保留点，越多越好

​ 为什么：因为保留点越多，就越能按自己的意愿灵活的进行回退

释放保留点：保留点在事务处理完成(执行一条ROLLBACK或COMMIT)后自动释放

​ 自MySQL5以来，也可以用RELEASE SAVEPOINT明确的释放保留点

更改默认的提交行为

默认的MySQL行为是自动提交所有更改

换句话说，任何时候执行一条MySQL语句，该语句实际上都是针对表执行的，而且所做的更改立即生效

为指示MySQL不自动提交更改，需要使用以下语句：

SET autocommit=0;

autocommit标志决定是否自动提交更改，不管有没有COMMIT语句

​ 设置autocommit为0(假)指示MySQL不自动提交更改(直到autcommit被设置为真为止)

标志为连接专用：autocommit标志是针对每个连接而不是服务器的

---

全球化和本地化

MySQL处理不同字符集和语言的基础知识

字符集和校对顺序

数据库表被用来存储和检索数据，不同的语言和字符集需要以不同的方式存储和检索

​ 因此，MySQL需要适应不同的字符集（不同的字母和字符），适应不同的排序和检索数据的方法

在讨论多种语言和字符集时，将会遇到以下重要术语：

• 字符集：为字母和符号的集合

• 编码：为某个字符集成员的内部表示

• 校对：为规定字符如何比较的指令

校对为什么重要：大小写的区分不仅影响排序(如用ORDER BY排序数据)，还影响搜索(如WHERE)

​ 在使用法文或德文等字符时更复杂，在使用不基于拉丁文的字符集时，情况更为复杂

在MySQL的正常数据库活动(SELECT、INSERT等)中，不需要操心太多东西

使用何种字符集和校对的决定在服务器、数据库和表级进行

使用字符集和校对顺序

MySQL支持众多的字符集

为查看所支持的字符集完整列表，使用以下语句：

SHOW CHARACTER SET;

这条语句显示所有可用的字符集以及每个字符集的描述和默认校对

为查看所支持校对的完整列表，使用以下语句：

SHOW COLLATION;

显式所有可用的校对，以及它们适用的字符集

​ 可以看到有的字符集具有不止一种校对，而且许多校对出现两次，一次区分大小写(_cs)，一次不区分大小写(_ci)

通常系统管理在安装时定义一个默认的字符集和校对

此外，也可以在创建数据库时，指定默认的字符集和校对

为了确定所用的字符集和校对，可以使用以下语句：

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

实际上，字符集很少是服务器范围(甚至数据库范围)的设置

​ 不同的表，甚至不同的列都可能需要不同的字符集，而且两者都是可以在创建表时指定

为了给表指定字符集和校对，可使用带子句的CREATE TABLE：

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

这个例子中指定了CHARACTER SET和COLLATE两者

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

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

除了能指定字符集和校对的表范围外，MySQL还允许对每个列设置它们，如下所示：

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

这里对整个表以及一个特定的列指定了CHARACTER SET和COLLATE

校对在对用ORDER BY子句检索出来的数据排序时起重要的作用

如果需要用于创建表时不同的校对顺序排序特定的SELECT语句，可以在SELECT语句自身中进行：

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

此SELECT使用COLLATE指定一个备用的校对顺序(此例中为区分大小写的校对)

​ 这显然将会影响到结果排序的次序

临时区分大小写：上面的SELECT语句演示了在通常不区分大小写的表上进行区分大小写搜索的一种技术

​ 当然，反过来也是可以的

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

最后，值得注意点是，如果绝对需要，穿可以在字符集之间进行转换

​ 为此，使用Cast()或Convert()函数

---

安全管理

数据库服务器通常包含关键的数据，确保这些数据的安全和完整需要利用访问控制

访问控制

MySQL服务器的安全基础时：用户应该对他们需要的数据具有适当的访问权，既不能多也不能少

​ 即，用户不能对过多的数据具有过多的访问权

访问控制：管理访问控制需要创建和管理用户账号

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

​ 应该创建一系列的账号，有的用于管理，有的供用户使用，有的供开发人员使用，等等

防止无意的错误：重要的是注意到，访问控制的目的不仅仅是防止用户的恶意企图

​ 数据梦魇更为常见的是无意识错误的结果，如打错MySQL语句，在不适合的数据库中操作或其他一些用户错误

​ 通过保证用户不能执行他们不应该执行的语句，访问控制有助于避免这些情况的发生

不要使用root：应该严肃对待root登录的使用

​ 仅在绝对需要时使用它(或许在你不能登录其他管理账号时使用)

​ 不应该在日常的MySQL操作中使用root

管理用户

MySQL用户账号和信息存储在名为mysql的MySQL数据库中

一般不需要直接访问mysql数据库和表，但有时需要直接访问

需要直接访问它的时机之一是在需要获得多于用户账号列表时

​ 为此，可使用以下代码：

USE mySql;
SELECT user FROM user;

mysql数据库有一个名为user的表，它包含所有用户账号

user表有一个名为user的列，它存储用户登录名

用多个客户机进行试验：试验对用户账号和权限进行更改的最好办法是打开多个数据库客户机(如mysql命令行使用程序的多个副本)，一个作为管理登录，其他作为被测试的用户登录

创建用户账号

为了创建一个新用户账号，使用CREATE USER语句，如下所示：

CREATE USER ben IDENTIFIED BY 'p@$$w0rd';

CREATE USER创建一个新用户账号

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

​ 如果再次列出用户账号，将会在输出中看到新账号

指定散列口令：IDENTIDIED BY指定的口令为纯文本

​ MySQL将在保存到user表之前对其进行加密

为了作为散列值指定口令，使用IDENTIDIED BY PASSWORD

使用GRANT或INSERT：

​ GRANT语句也可以创建用户账号，但一般来说CREATE USER是最清楚和最简单的句子

此外，也可以通过直接插入行到user表来增加用户，不过为安全起见，一般不建议这样做

MySQL用来存储用户账号信息的表(以及表模式等)极为重要，对它们的任何毁坏都可能严重的伤害到MySQL服务器

​ 因此，相对于直接处理来说，最好是用标记和函数来处理这些表

为重新命名一个用户账号，使用RENAME USER语句，如下所示：

RENAME USER ben TO bforta;

MySQL 5之前：仅MySQL5或之后的版本支持RENAME USER

​ 为了在以前的MySQL中重命名一个用户，可使用UPDATE直接更新user表

删除用户账号

为了删除一个用户账号(以及相关的权限)，使用DROP USER语句，如下所示：

DROP USER bforta;

MySQL 5之前：

​ 自MySQL5以来，DROP USER删除用户账号和所有相关的账号权限

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

因此如果使用旧版本的MySQL，需要先用REVOKE删除与账号相关的权限，然后再用DROP USER删除账号

设置访问权限

在创建用户账号后，必须接着分配访问权限

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

​ 它们能登录MySQL，但不能看到数据，不能执行任何数据库操作

为看到赋予用户账号的权限，使用SHOW GRANTS FOR，如下所示：

SHOW GRANTS FOR bforta;

输出结果显示bforta有一个权限USAGE ON *.*

• USAGE表示根本没有权限(很不直观罢)

所以此结果教室在任意数据库和任意表上对任何东西都没有权限

**用户定义为user@host**：MySQL的权限用用户名和主机名结合定义

​ 如果不指定主机名，则使用默认的主机名%(授予用户访问权限而不管主机名)

为设置权限，使用GRANT语句

GRANT要求至少给出以下信息：

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

下例给出GRANT的用法：

GRANT SELECT ON crashcourse.* TO bforta;

此GRANT允许用户在crashcourse.*(crashcourse数据库的所有表)上使用SELECT

​ 通过只授予SELECT访问权限，用户bforta对crashcourse数据库中的所有数据具有只读访问权限

SHOW GRANTS反映这个更改：

SHOW GRANTS FOR bforta;

每个GRANT添加(或更新)用户的一个权限

​ MySQL读取所有授权，并根据它们确定权限

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

REVOKE SELECT ON crashcourse.* FROM bforta;

​ 这条REVOKE语句取消刚刚赋予用户bforta的SELECT访问权限

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

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	使用CREATE INDEX和DROP INDEX
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，在结合表中列出的权限，就能对用户具有完全的控制

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

​ 这允许管理员在创建数据库和表之前设计和实现安全措施

这样做的副作用是，当某个数据库或表被删除时(用DROP语句)，相关的访问权限仍然存在

而且，如果将来重新创建该数据库或表，这些权限仍然起作用

简化多次授权：可通过列出各权限并用逗号分隔，将多条GRANT语句串在一起，如下所示：

GRANT SELECT, INSERT ON crashcourse.* TO bforta;

更改口令

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

新口令必须如下加密：

SET PASSWORD FOR bforta = Password('n3w p@$$w0rd');

SET PASSWORD更新用户口令

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

SET PASSWORD还可以用来设置你自己的口令：

SET PASSWORD = Password('n3w p@$$w0rd');

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

---

数据库维护

如何进行常见的数据库维护

备份数据

像所有数据一样，MySQL的数据也必须经常备份

由于MySQL数据是基于磁盘的文件，普通的备份系统和例程就能备份MySQL的数据

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

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

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

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

进行数据库维护

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

以下是应该知道的一些语句：

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

ANALYZE TABLE返回如下所示的状态信息：

输入：

ANALYZE TABLE orders;

输出：

Table	Op	Msg_type	Msg_taxt
crashcourse.orders	analyze	status	OK

• CHECK TABLE用来针对许多问题对表进行检查
  • 在MyISAM表上海对索引进行检查
  • CHECK TABLE支持一系列的用于MyISAM表的方式
  • CHANGED检查自最后一次检查以来改动过的表
  • EXTENDED执行最彻底的检查
    • FAST只检查未正常关闭的表
    • MEDIUM检查所有被删除的链接并进行键检查
    • QUICK只进行快速扫描

如下所示，CHECK TABLE发现和修复问题：

输入：

CHECK TABLE orders, orderitems;

输出：

Table	Op	Msg_type	Msg_text
crashocourse.orders	check	status	OK
crashcourse.orderitems	check	warning	Table is marked as crashed
crashcourse.orderitems	check	status	OK

• 如果MyTSAM表访问产生不正确和不一致的结果，可能需要用REPAIR TABLE来修复相应的表
  • 这条语句不应该经常使用，如果需要经常使用，可能会有更大的问题要解决
• 如果从一个表中删除大量数据，应该使用OPTIMIZE TABLE来收回所用的空间，从而优化表的性能

诊断启动问题

服务器启动问题通常在对MySQL配置或服务器本身进行更改时出现

MySQL在这个问题发生时报告错误，但由于多数MySQL服务器是作为系统进程或服务自动启动的，这些消息可能看不到

在排除系统启动问题时，首先应该尽量用手动启动服务器

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命令行选项更改
  • 注意，这个日志文件是MySQL5中添加的，以前的MySQL版本中使用的是更新日志
• 缓慢查询日志
  • 此日志记录执行缓慢的任何查询
  • 这个日志在确定数据库何处需要优化很有用
  • 此日志通常名为hostname-slow.log，位于data目录中
  • 此名字可以用--log-slow-queries命令行选项更改

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

---

改善性能

复习与MySQL性能有关的某些要点

改善性能

数据库管理员生命中的大部分时间在调整、试验以及改善DBMS性能上(

在诊断应用的滞缓现象和性能问题时，性能不良的数据库(以及数据库查询)通常是最常见的祸因

可见，下面的内容并不能完全决定MySQL的性能

只是想回顾一下前面的重点，提供进行性能优化探讨和分析的一个出发点

• 首先，MySQL(与所有DBMS一样)具有特定的硬件协议
  • 在学习和研究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
• 数据库是不断变化的实体
  • 一组优化良好的表一会儿后可能就面目全非了
  • 由于表的使用和内容的更改，理想的优化和配置也会改变
• 最重要的规则就是，每条规则在某些提哦啊见下都会被打破

浏览文档：位于http://dev.mysql.com/doc/的MySQL文档有许多提示和技巧（甚至有用户提供的评论和反馈）

​ 一定要查看这些非常有价值的资料

---

(完结撒花)