StudyNotes-MySQL必知必会

Posted on | Post modified: | In
Words count in article: 23.4k

第1章 了解SQL

1.1 数据库基础

1.1.1 什么是数据库

  • 数据库
    • 以某种有组织的方式存储的数据集合
    • 保存有组织的数据的容器（通常是一个文件或一组文件）
  • 数据库软件
    • DBMS-数据库管理系统
    • 使用DBMS访问数据库

1.1.2 表

  • 表
    • 是一种结构化的文件，用来存储某种特定类型的数据
    • 某种特定类型数据的结构化清单
    • 存储在表中的数据是一种类型或一个清单，不应该在同一个表中有两种类型或两个清单
    • 表名用来标识自己，名字是唯一的
    • 相同数据库中不能使用相同的表名，但在不同的数据库中可以使用相同的表名
  • 模式
    • 定义了数据在表中如何存储
    • 用来描述数据库中特定的表以及整个数据库和其中表的关系
    • 关于数据库和表的布局及特性的信息

1.1.3 列和数据类型

  • 列
    • 表由列组成，列中存储着表中某部分的信息
    • 表中的一个字段，所有表都是有一个或者多个列组成的
    • 正确地将数据分解分为多个列很重要
  • 数据类型
    • 所容许的数据的类型，每个表列都有响应的数据类型，限制（或容许）该列中存储的数据

1.1.4 行

  • 行
    • 表中的一个记录
    • 行 = 数据库记录

1.1.5 主键

  • 主键
    • 一列（或一组列），其值能够唯一区分表中的每个行
    • 应该总是定义主键，便于数据操纵和管理
    • 任意两行都不具有相同的主键值
    • 表中任何列都可以作为主键，主要它满足一下条件
      • 每个行都必须具有一个主键值（主键列不允许NULL值）
      • 通常定义在表的一列上，但也可以使用多个列作为主键
    • 好习惯
      • 不更新主键列中的值
      • 不重用主键列的值
      • 不在主键列中使用可能会更改的值

1.2 什么是SQL

  • SQL
    • 结构化查询语言（Structured Query Language）
    • 专门用来与数据库通信的语言
    • 优点
      • 不是某个特定数据库供应商专有的语言，通用性
      • 简单易学
      • 强有力，灵活，可以进行非常复杂和高级的数据库操作
    • 任意两个DBMS实现的SQL都不完全相同，不是完全可移植的，本书针对MySQL

1.3 动手实践

  • 附录B

B.1 样例表 - 随身物品推销商使用的订单录入系统

  • 管理供应商
  • 管理产品目录
  • 管理顾客列表
  • 录入顾客订单

表的描述

  • 6个表，表的列出顺序是因为它们之间的依赖关系

  • vendors表

    • 存储销售产品的供应商，每个供应商为一个记录。供应商列用来匹配产品和供应商
    • 所有表都应该有主键，这个表使用vend_id作为主键，vend_id为一个自动增量字段
表B-1 vendors表的列
列 说明
vend_id 唯一的供应商ID
vend_name 供应商名
vend_address 供应商的地址
vend_city 供应商的城市
vend_state 供应商的州
vend_zip 供应商的邮政编码
vend_country 供应商的国家
  • products表
    • products表包含产品目录，每行一个产品，每个产品有唯一的ID（prod_id列），通过vend_id（供应商的唯一ID）关联到它的供应商
    • 所有表都应该有主键，这个表使用prod_id列作为主键
    • 为实施引用完整性，应该在vend_id上定义一个外键，关联到vendors的vend_id
表B-2 products表的列
列 说明
prod_id 唯一的产品ID
vend_id 产品供应商ID（关联到vendors表中的vend_id）
prod_name 产品名
prod_price 产品价格
prod_desc 产品描述
  • customers表
    • customers表存储所有顾客的信息，每个顾客有唯一的ID（cust_id列）
    • 所有表都应该有主键，这个表使用cust_id列作为主键，cust_id是一个自动增量的字段
表B-3 customers表的列
列 说明
cust_id 唯一的顾客ID
cust_name 顾客名
cust_address 顾客的地址
cust_city 顾客的城市
cust_state 顾客的州
cust_zip 顾客的邮政编码
cust_country 顾客的国家
cust_contact 顾客的联系名
cust_email 顾客的联系email地址
  • orders表
    • orders表存储顾客订单（但不是订单细节），每个订单唯一的编号（order_num列），订单用cust_id列（它关联到customer表的顾客唯一ID）与响应的顾客关联
    • 所有表都应该有主键，这个表使用order_num列作为主键，order_num是一个自动增量的字段
    • 为实施引用完整性，应该在cust_id上定义一个外键，关联到customers的cust_id
表B-4 orders表的列
列 说明
order_num 唯一订单号
order_data 订单日期
cust_id 订单顾客ID（关联到customers表的cust_id）
  • orderitems表
    • orderitems表存储每个订单中的实际物品，每个订单的每个物品占一行，对orders表中的一行，orderitems中有一行或多行，每个订单物品有订单号加订单物品（第一个物品、第二个物品等）唯一标识。订单物品通过order_num列（关联到orders中订单的唯一ID）与它们响应的订单相关联，每个订单项包含订单物品的产品ID（它关联物品到products表）
    • 所有表都应该有主键，这个表使用order_num和order_item作为其主键
    • 为实施引用完整性，应该在order_num上定义一个外键，关联到orders的order_num，在prod_id定义外键，关联到products的prod_id
表B-5 orderitems表的列
列 说明
order_num 订单号（关联到orders表的order_num）
order_item 订单物品号（在某个订单中的顺序）
prod_id 产品ID（关联到products表的prod_id）
quantity 物品数量
item_price 物品价格
  • productnotes表
    • productnotes表存储于特定产品有关的注释信息，并非所有产品都有相关的注释，而有的产品可能有许多相关的注释
    • 所有表都应该有主键，这个表使用note_id作为其主键
    • 列note_text必须为FULLTEXT搜索进行索引
    • 由于这个表使用全文本搜索，因此必须指定ENGINE=MyISAM
表B-6 productnotes表的列
列 说明
note_id 唯一注释ID
prod_id 产品ID（对应于products表中的prod_id）
note_date 增加注释的日期
note_text 注释文本

B.2 创建样例表

下载数据：https://www/forta.com/books/0672327120/

  • 创建一个新数据源
  • 保证选择新数据源
  • 执行create.sql脚本
  • 重复前面的步骤，用populate.sql文件填充各个新表

第2章 MySQL简介

2.1 什么是MySQL

  • MySQL
    • 是一种DBMS，数据库软件
    • 广泛使用，原因：
      • 成本 - MySQL是开源的，可以免费使用（甚至可以免费修改）
      • 性能 - MySQL执行很快
      • 可信赖 - 很多公司，站点都用MySQL来处理自己的重要数据
      • 简单 - MySQL很容易安装和使用

2.1.1 客户机-服务器软件

  • DBMS
    • 分两类，一类为基于共享文件系统的DBMS，另一类为基于客户机-服务器的DBMS
    • MySQL是基于客户机-服务器的数据库
    • 服务器部分是负责所有数据访问和处理的一个软件，运行在数据库服务器上，与数据文件打交道的只有服务器软件，数据添加、删除和更新都由服务器软件完成
    • 客户机是与用户打交道的软件

2.1.2 MySQL版本

  • DBMS版本
    • 当前版本为5
    • 版本4：InnoDB引擎，增加事物处理（第26章），并（第17章），改进全文本搜索（第18章）的支持
    • 版本4.1：对函数库、子查询（第14章）、集成帮助等的重要增加
    • 版本5：存储过程（第23章）、触发器（第25章）、游标（第24章）、视图（第22章）等

2.2 MySQL工具

2.2.1 mysql命令行使用程序

  • 指定登录名：mysql -u ben
  • 指定用户名、主机名、端口和口令：mysql -u ben -p -h myserver -P 9999
  • 完整的参数列表：mysql —help
  • 命令输入在mysql>之后
  • 命令用 ; 或 \g 结束，仅按enter不执行命令
  • 输入help或 -h 获得帮助
  • 获得特定命令的帮助 如help select
  • 输入quit或exit退出命令行实用程序

2.2.2 MySQL Administrator

  • MySQL Administrator（MySQL管理器）是一个图形交互客户机，简化MySQL服务器的管理
  • key points：
    • Server Information（服务器信息）显示客户机和被连接的服务器的状态和版本信息
    • Server Control（服务控制）允许停止和启动MySQL以及指定服务器特性
    • User Administration（用户管理）用来定义MySQL用户、登录和权限
    • Catalogs（目录）列出可用的数据库并允许创建数据库和表

2.2.3 MySQL Query Browser

  • MySQL Query Browser是一个图形交互客户机，编写和执行MySQL命令

第3章 使用MySQL

3.1 连接

  • MySQL有自己的用户列表，用户间的权限可能不同
  • MySQL Administrator 的Users视图可直接定义新用户，包括赋予口令和访问权限
  • 主机名+端口+用户名+口令

3.2 选择数据库

  • 使用USE关键字 （关键字：作为MySQL的保留字，不要用关键字命名一个表和列。）
  • USE crashcourse （选择crashcourse数据库，不返回任何结果）

3.3 了解数据库和表

  • SHOW DATABASES（返回可用数据库的一个列表）
  • SHOW TABLES（返回当前选择的数据库内可用表的列表）
  • SHOW COLUMNS FROM customers； （显示表列）
    • 对每个字段返回一行，行中包括字段名、数据类型、是否允许NULL、键信息、默认值以及其他信息（如字段cust_id的auto_increment）
  • 自动增量：某些表列需要唯一值，在每个行添加到表中时，MySQL自动地为每个行分配下一个可用编号，不用在添加一行时手动分配唯一值。在CREATE语句创建表时定义（第21章）
  • DESCRIBE是SHOW COLUMNS FROM的快捷方式，DESCRIBE customers 与 SHOW COLUMNS FROM customers 等价
  • SHOW STATUS （显示服务器状态信息）
  • SHOW CREATE DATABASE和SHOW CREATE TABLE （分别用来显示创建特定数据库或表的MySQL语句）
  • SHOW GRANTS （显示授予用户（所有用户或特定用户）的安全权限）
  • SHOW ERRORS和SHOW WARNINGS （显示服务器错误或警告消息）
  • HELP SHOW （显示所有允许的SHOW语句）
  • MySQL新增内容：INFORMATION_SCHEMA，获得和过滤模式信息

第4章 检索数据

4.1 SELECT语句

  • 从一个表或多个表中检索信息
  • 使用SELECT时，至少给出两条信息：想选择什么，以及从什么地方选择

4.2 检索单个列

1
2
3
# 从products表中检索一个名为prod_name的列，返回的数据是无序的
SELECT prod_name
FROM products;
  • 多条语句用分号分隔，在每一条语句后加分号没有坏处
  • SQL语句不区分大小写，SELECT=Select，一般对关键字使用大写，对列和表明使用小写，易于阅读和调试
  • 在处理SQL语句时，所有空格被忽略，SQL语句可以在一行给出，也可以分成许多行，多行更容易阅读和调试

4.3 检索多个列

  • 使用相同的SELECT语句，在SELECT后给出多个列名，列名之间必须以逗号分隔，但最后一个列名后不加逗号，否则报错
1
2
SELECT prod_id, prod_name, prod_price
FROM products;
  • SQL语句一般返回原始的、无格式的数据
  • 数据的格式化是一个表示问题，而不是检索问题，表示（对齐和数值）一般在显示该数据的应用程序中规定，一般很少使用实际检索出的原始数据

4.4 检索所有列

1
2
3
# 使用通配符 * 
SELECT *
FROM products;
  • 如果给定一个通配符 * ，则返回表中所有列，列的顺序一般是列在表定义中出现的顺序，但表的模式的变化（如添加和删除列）可能会导致顺序的变化
  • 使用通配符省事，但会降低检索和应用程序的性能
  • 使用通配符优点，可以检索出名字未知的列

4.5 检索不同的行

1
2
3
# 只返回不同的vend_id行
SELECT DISTINCT vend_id
FROM products;
  • 使用DISTINCT关键字只返回不同的值，放在列名的前面
  • 不能部分使用DISTINCT，DISTINCT应用于所有列而不仅是前置它的列

4.6 限制结果

  • 使用LIMIT语句返回前几行
1
2
3
4
# 返回不多于5行
SELECT prod_name
FROM products
LIMIT 5;
  • 指定要检索的开始行和行数
1
2
3
4
# 返回从行5开始的5行
SELECT prod_name
FROM products
LIMIT 5, 5;
  • LIMIT 5, 5返回从行5开始的5行，第一个数为开始位置，第二个数为要检索的行数
  • 带一个值的LIMIT总是从第一行开始，给出的数为返回的行数
  • 第一行是行0，LIMIT 1, 1将检索出第二行而不是第一行
  • LIMIT中指定要检索的行数为检索的最大行数
  • MySQL5支持LIMIT 4 OFFSET 3 与 LIMIT 3, 4等价，从行3开始取4行

4.7 使用完全限定的表名

  • 完全限定的名字：同时使用表名和列字
1
2
SELECT products.prod_name # 完全限定的列名
FROM products；
1
2
SELECT products.prod_name # 完全限定的列名
FROM crashcourse.products； # 完全限定的表名

第5章 排序检索数据

  • SELECT语句的ORDER BY 子句

5.1 排序数据

1
2
SELECT prod_name
FROM products;

  • 以上输出数据没有特定的顺序，如果不排序，一般会以它在底层表中出现的顺序显示，这可以是数据最初添加到表中的顺序，但是，如果数据后来进行过更新或删除，则此顺序将会受到MySQL重用回收存储空间的影响。

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

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

1
2
3
SELECT prod_name
FROM products
ORDER BY prod_name;
  • 通常ORDER BY子句中使用的列为显示所选择的列，但用非检索列的列排序数据也是完全合法的。

5.2 按多个列排序

  • 按多个列排序，只要指定列名，列名之间用逗号分开即可。
1
2
3
4
# 首先按价格，再按名称排序
SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price, prod_name;

5.3 指定排序方向

  • 默认是生序排序，无须指定关键字，也可以指定升序关键字ASC
  • 降序排序需要指定DESC关键字。
1
2
3
4
# 降序排序
SELECT prod_id, Prod_price, prod_name
FROM products
ORDER BY prod_price DESC;
1
2
3
4
# prod_price降序排序, prod_name 升序排序
SELECT prod_id, Prod_price, prod_name
FROM products
ORDER BY prod_price DESC, prod_name;
  • 在多个列上降序排序，必须对每个列指定DESC关键字。

  • 大小写问题，MySQL认为A和a相同。

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

1
2
3
4
5
# 找出价格最高物品的值
SELECT prod_price
FROM products
ORDER BY prod_price DESC
LIMIT 1;
  • ORDER BY在FROM子句之后，LIMIT位于ORDER BY之后，次序不对将报错。

第6章 过滤数据

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

6.1 使用WHERE子句

  • 只检索所需数据需要指定搜索条件，搜索条件也称为过滤条件。
  • 在SELECT中，数据根据WHERE子句中指定的搜索条件进行过滤，WHERE子句在表名（FROM子句）之后给出。
1
2
3
4
# 从products中检索两个列，只返回prod_price值为2.50的行
SELECT prod_name, prod_price
FROM products
WHERE prod_price = 2.50;

6.2 WHERE子句操作符

  • WHERE支持的条件操作符。
WHERE子句操作符
操作符 说明
= 等于
<> 不等于
!= 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于
BETWEEN 在指定的两个值之间

6.2.1 检查单个值

1
2
3
4
# 返回prod_name的值为Fuses的行，不区分大小写，Fuces和fuces匹配
SELECT prod_name, prod_price
FROM products
WHERE prod_name = 'fuses';
1
2
3
4
# 列出价格小于10美元的所有产品
SELECT prod_name, prod_price
FROM products
WHERE prod_price < 10;

6.2.2 不匹配检查

1
2
3
4
# 列出不是由供应商制造的所有产品
SELECT vend_id, prod_name
FROM products
WHERE vend_id <> 1003;
  • 何时使用引号：单引号用来限定字符串，如果将值与串类型的列进行比较，则需要限定引号，用来与数值列进行比较的值不用引号。
  • <> 和 != 等价。

6.2.3 范围值检查

  • 使用BETWEEN操作符，需要两个值，即范围的开始值和结束值，匹配范围内所有的值，包括开始值和结束值。
1
2
3
4
# 检索价格在5美元到10美元的所有产品
SELECT prod_name, prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;

6.2.4 空值检查

  • 在创建表时，可以指定其中的列可以不包含空值，空值为NULL。
  • 空值NULL与字段包含0，空字符串或仅仅包含空格不同。
  • IS NULL子句用来检索NULL的列。
1
2
3
4
# 返回没有价格（空prod_price字段，不是价格为0）的所有产品的行
SELECT prod_name
FROM products
WHERE prod_price IS NULL;
  • NULL与不匹配：匹配过滤和不匹配过滤时不返回具有NULL值的行。

第7章 数据过滤

  • 组合WHERE子句，NOT和IN操作符。

7.1 组合WHERE子句

  • 以ANF子句或OR子句的方式使用多个WHERE子句。
  • 操作符：用来联结或改变WHERE子句中的子句的关键字，也称为逻辑操作符。

7.1.1 AND操作符

1
2
3
4
# 检索由供应商1003制造且价格小于等于10美元的所有产品的名称和价格
SELECT prod_id, prod_price, prod_name
FROM products
WHERE vend_id = 1003 AND prod_price <= 10;
  • AND 用在WHERE子句中的关键字，用来指示检索满足所有给定条件的行。

7.1.2 OR操作符

  • OR操作符与AND不同，它指示检索匹配任一条件的行。
1
2
3
4
# 检索由供应商1002或1003制造的任何产品
SELECT prod_name, prod_price
FROM products
WHERE vend_id = 1002 OR vend_id = 1003;
  • OR 用在WHERE子句中的关键字，用来指示检索匹配任一给定条件的行。

7.1.3 计算次序

  • WHERE可包含任意数目的AND和OR操作符，允许两者结合以进行复杂和高级的过滤。
  • AND的计算优先级比OR高，使用圆括号明确地分组相应的操作符。
1
2
3
4
# 检索由供应商1002或1003制造的任何产品，并且价格大于等于10美元的行
SELECT prod_name, prod_price
FROM products
WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;
  • 在WHERE子句中，任何时候使用AND和OR操作时，都要使用圆括号明确地分组操作符，使用圆括号没有坏处，可以消除歧义。

7.2 IN操作符

  • 圆括号在WHERE子句中还有另外一种用法，IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配，IN取合法值的由逗号分隔的清单，全都在圆括号中。
1
2
3
4
5
6
# 检索供应商1002和1003制造的所有产品
SELECT prod_name, prod_price
FROM products
WHERE vend_id IN (1002, 1003)
ORDER BY prod_name;
​`
  • IN操作符完成与OR相同的功能。

​

1
2
3
4
5
# 检索供应商1002和1003制造的所有产品
SELECT prod_name, prod_price
FROM products
WHERE vend_id = 1002 OR vend_id = 1003
ORDER BY prod_name;

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

7.3 NOT操作符

  • NOT操作符有且只有一个功能，否定它之后所跟的任何条件。
1
2
3
4
5
# 匹配1002和1003之外的供应商
SELECT prod_name, prod_price
FROM products
WHERE vend_id NOT IN (1002, 1003)
ORDER BY prod_name;
  • 与IN操作符联合使用时，NOT使找出与条件列表不匹配的行。

第8章 用通配符进行过滤

8.1 LIKE操作符

  • 通配符：用来匹配值的一部分的特殊字符。
  • 搜索模式：由字面值、通配符或两者组合构成的搜索条件。
  • 在搜索子句中使用通配符，必须使用LIKE操作符，LIKE指示后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。

8.1.1 百分号（%）通配符

  • % 表示任何字符出现的任意次数。
1
2
3
4
# 检索任意jet起头的词，%告诉MySQL接受jet之后的任意字符，不管它有多少字符
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE 'jet%';
  • 区分大小写：搜索是可以区分大小写的。
  • 通配符可以在搜索模式中任意位置使用，并且可以使用多个通配符。
1
2
3
4
# 匹配任何位置包含文本anvil的值
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '%anvil%';
1
2
3
4
# 匹配以s起头以e结尾的所有产品
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE 's%e';
  • % 还可以匹配0个字符。%代表搜索模式中给定位置的0个、1个或多个字符。
  • 注意尾空格，尾空格可能会干扰通配符匹配，例如anvil后有一个或多个空格时，则子句WHERE prod_name LIKE '%anvil'将不会匹配它们，因为在最后有空格，一个简单的方法是在搜索模式最后附加一个%，一个更好的方法是使用函数（第11章）去掉首尾空格。
  • 注意NULL，%通配符不能匹配值为NULL的行。

8.1.2 下划线（_）通配符

  • 下划线只匹配单个字符而不是多个字符。
1
2
3
4
# （_）只匹配一个字符
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '_ ton anvil'

8.2 使用通配符的技巧

  • 通配符搜索的处理一般要比前面讨论的其他搜索所花时间更长。
  • 技巧：
    • 不要过度使用通配符。
    • 除非有必要，否则不要把它们用在搜索模式的开始处，因为置于开始处是最慢的。
    • 仔细注意通配符的位置。

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

9.1 正则表达式介绍

9.2 使用MySQL正则表达式

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

9.2.1 基本字符匹配

1
2
3
4
5
# 检索列prod_name包含1000的所有行
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000'
ORDER BY prod_name;
1
2
3
4
5
# .是表示匹配任意一个字符，因此可以匹配1000和2000
SELECT prod_name
FROM products
WHERE prod_name REGEXP '.000'
ORDER BY prod_name;
  • LIKE和REGEXP比较。
1
2
3
4
5
# 不返回数据
SELECT prod_name
FROM products
WHERE prod_name LIKE '1000'
ORDER BY prod_name;
1
2
3
4
5
# 检索列prod_name包含1000的所有行
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000'
ORDER BY prod_name;
  • LIKE匹配整个列，如果被匹配的文本在列值中出现，LIKE将不会找到它，除非使用通配符。
  • 而REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP将会找到它，响应的行将被返回。
  • 匹配不区分大小写，为区分大小写，可使用BINARY关键字，如WHERE prod_name REGEXP BINARY 'JetPack .000'。

9.2.2 进行OR匹配

  • 为检索两个串之一，使用|。
1
2
3
4
5
# | 表示OR操作，表示匹配其中之一，因此1000和2000都匹配
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000|2000'
ORDER BY prod_name;
  • 可以给出两个以上的OR条件，例如'1000|2000|3000'将匹配1000或2000或3000。

9.2.3 匹配几个字符之一

  • 使用一组用[]括起来的字符即可。
1
2
3
4
5
# [123]表示匹配1或2或3，因此，1 ton和2 ton都匹配
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[123] Ton'
ORDER BY prod_name;
  • [] 是另一种形式的OR语句，[123] Ton是[1|2|3] Ton的缩写。
  • 需要用[]来定义OR语句。
1
2
3
4
5
# 表示'1'或'2'或'3 ton'
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1|2|3 Ton'
ORDER BY prod_name;
  • 需要把字符|括在一个集合里，否则它将应用于整个串。
  • 字符集合也可以被否定，即它将匹配除指定字符外的任何东西，在集合的开始处放置一个^即可，例如123匹配除1、2或3外的任何东西。

9.2.4 匹配范围

  • 集合可用来定义要匹配的一个或多个字符。例如[0123456789]将匹配数字0到9，可使用-来定义一个范围，简化为[0-9]。[a-z]匹配任意字母字符。
1
2
3
4
5
# [1-5]表示匹配1到5, 5 ton是一个匹配
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[1-5] ton'
ORDER BY prod_name;

9.2.5 匹配特殊字符

  • 加前导\\匹配特殊字符.、[]、|、_。\\_表示查找_，\\. 表示查找.。这种处理称为转义。
1
2
3
4
5
# 使用 \\. 匹配 . 
SELECT vend_name
FROM vendors
WHERE vend_name REGEXP '\\.'
ORDER BY vend_name;
  • \\也用来引用元字符（具有特殊含义的字符）。
表9-1 空白元字符
元字符 说明
\\f 换页
\\n 换行
\\r 回车
\\t 制表
\\v 纵向制表
  • 为了匹配反斜杠（\）字符本身，需要使用 \\\
  • 多数正则表达式实现使用单个反斜杠转义特殊字符，以便能使用这些字符本身，但MySQL要求两个反斜杠实现转义（MySQL解释一个，正则表达式库解释另一个）。

9.2.6 匹配字符类

  • 存在找出你自己经常使用的数字、所有字母字符或所有数字字母字符等的匹配。使用预定义的字符集，称为字符类。
    表9-2 字符类
类 说明
[:alnum:] 任意字母和数字（同[a-aA-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]）

9.2.7 匹配多个实例

表9-3 重复元字符
元字符 说明
* 0个或多个匹配
+ 1个或多个匹配（等于{1, }）
? 0个或1个匹配（等于{0,1}）
{n} 指定数目的匹配
{n, } 不少于指定数目的匹配
{n, m} 匹配数目的范围{m不超过255} 
1
2
3
4
5
# \\(匹配)，[0-9]匹配任意数字，sticks？匹配stick和sticks(s后的？使s可选，因为？匹配它前面的任何字符的0次或1次出现)，没有？，匹配stick和sticks会非常困难
SELECT prod_name
FROM products
WHERE prod_name REGEXP '\\([0-9]) sticks?\\'
ORDER BY prod_name;
  • 以下匹配连在一起的4位数字
1
2
3
4
5
# [:digit:] 匹配任意数字，{4}要求它前面的字符（任意数字）出现4次
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[[:digit:]]{4}'
ORDER BY prod_name;
1
2
3
4
5
# 与上例等价
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[0-9][0-9][0-9][0-9]'
ORDER BY prod_name;

9.2.8 定位符

  • 匹配特定位置的文本。
表9-4 定位元字符
元字符 说明
^ 文本的开始
$ 文本的结尾
[[:<:]] 词的开始
[[:>:]] 词的结尾 
1
2
3
4
5
# 使用^定位符找出以一个数（包括小数点开始的数）开始的所有产品
SELECT prod_name
FROM products
WHERE prod_name REGEXP '^[0-9\\.]'
ORDER BY prod_name;
  • ^还可以在集合中[]用来否定该集合。
  • 使REGEXP起类似LIKE的作用，通过用^开始每个表达式，用$结束每个表达式，可以使REGEXP的作用与LIKE一样。
  • 可以在不使用数据库表的情况下用SELECT来测试正则表达式，REGEXP总是返回0（不匹配）或者1（匹配）。如 SELECT 'hello' REGEXP '[0-9]';，返回0。

第10章 创建计算字段

10.1 计算字段

  • 存储在数据库表中的数据一般不是应用程序所需要的格式，直接从数据库中检索出转换、计算或格式化过的数据，称为计算字段。

  • 计算字段是运行SELECT语句是创建的。

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

  • 客户机与服务器的格式，可在客户机和服务器上完成转换和格式化工作，但在服务器上比客户机中要快得多。

10.2 拼接字段

  • 拼接，将值联结到一起构成单个值。
  • 在SELECT语句中，用Concat()函数来拼接两个列。
  • MySQL的不同之处，多数DBMS使用+或||来实现拼接，MySQL则使用Concat()函数来实现。
1
2
3
4
# 返回vend_name(vend_country)字段
SELECT Concat(vend_name, '(', vend_country, ')')
FROM vendors
ORDER BY vend_name;
  • 删除数据右侧多于的空格整理数据，使用RTrim。
1
2
3
4
# 拼接前使RTrim()函数删除vend_name和vend_country右侧的空格
SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')')
FROM vendors
ORDER BY vend_name;

  • LTrim()，去掉串左边的空格，Trim()，去掉左右两边的空格。

  • 使用别名，SELECT语句拼接地址字段没有名字，需要使用别名（alias）表示一个字段或值，用AS关键字赋予。

1
2
3
4
# 使用AS vend_title指定计算字段的列名
SELECT Concat(vend_name, '(', vend_country, ')') AS vend_title
FROM vendors
ORDER BY vend_name;
  • 别名的其他用途，在实际的表列名包含不符合规定的字符（如空格）时重新命名它，在原来的名字含混或容易误解时扩充它。
  • 别名，有称为导出列（derived column）。

10.3 执行算术计算

  • 对检索出的数据进行算术运算。
1
2
3
4
5
6
7
# 汇总物品的价格（单价乘以订购数量），expanded_price为一个计算字段
SELECT prod_id, 
	   quantity, 
	   item_price,
	   quantity*item_price AS expanded_price
FROM orderitems
WHERE order_num = 2005;
  • MySQL支持的基本算术运算符，圆括号可用来区分优先顺序。
表10-1 MySQL算术操作符
操作符 说明
+ 加
- 减
* 乘
/ 除
  • 测试计算，可以省略FROM子句以便简单地访问和处理表达式，例如 SELECT 3*2 将返回 6，SELECT Trim(' abc ')将返回abc，SELECT Now()返回当前日期和时间。

第11章 使用数据处理函数

11.1 函数

  • 能运行在多个系统上的代码称为可移植的。
  • 函数的可移植性不强。

11.2 使用函数

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

11.2.1 文本处理函数

  • Upper()函数
1
2
3
4
# 返回vend_name和vend_name大写的一个计算字段
SELECT vend_name, Upper(vend_name) AS vend_name_upcase
FROM vendors
ORDER BY vend_name;
  • 常用文本处理函数。
表11-1 常用的文本处理函数
函数 说明
Left() 返回串左边的字符
Length() 返回串的长度
Locate() 找出串的一个子串
Lower() 将串转换为小写
LTrim() 去掉串左边的空格
Right() 返回串右边的字符
RTrim() 去掉串右边的空格
Soundex() 返回串的SOUNDEX值
SubString() 返回子串的字符
Upper() 将串转换为大写

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

  • 例如customers表中有一个顾客Coyote Inc.，其联系名为Y. LEE。但如果这是输入错误，实际应为Y. Lie，按正确的联系名搜索将搜索不到。

1
2
3
4
# 返回空，因为没有叫Y. Lie的
SELECT cust_name, cust_contact
FROM customers
WHERE cust_concact = 'Y.Lie';
1
2
3
4
# 返回Y.LEE，因为Y.LEE和Y.Lie发音相似
SELECT cust_name, cust_contact
FROM customers
WHERE Soundex(cust_concact) = Soundex('Y.Lie');

11.2.2 日期和时间处理函数

  • 常用的日期和时间处理函数
表11-2 常用日期和时间处理函数
函数 说明
AddDate() 增加一个日期（天、周等）
AddTime() 增加一个时间（时、分等）
CurDate() 返回当前日期
CurTime() 返回当前时间
Date() 返回日期时间的日期部分
DateDiff() 计算两个日期之差
Date_Add() 高度灵活的日期运算函数
Date_Format() 返回一个格式化的日期或时间串
Day() 返回一个日期的天数部分
DatOfWeek() 对于一个日期，返回对应的星期几
Hour() 返回一个时间的小时部分
Minute() 返回一个时间的分钟部分
Month() 返回一个日期的月份部分
Now() 返回当前日期和时间
Second() 返回一个时间的秒部分
Time() 返回一个日期的时间部分
Year() 返回一个日期的年份部分
  • 日期格式必须为yyyy-mm-dd。其他格式也可，但这是首选的，因为它排除了多义性。
  • 使用4位数字的年份更可靠。
1
2
3
4
# 返回order_date为'2005-09-01'的订单
SELECT cust_id, order_num
FROM orders
WHERE order_date = '2005-09-01';
  • 存储order_date值为 2005-09-01 11:30:05时，则 WHERE order_date = '2005-09-01'匹配失败。
  • 解决方法是仅将给出的日期和列中的日期部分进行比较，而不是将给出的日期和整个列值进行比较。使用Date(order_date)指示仅提取列的日期部分。
1
2
3
4
# 更可靠的SELECT语句
SELECT cust_id, order_num
FROM orders
WHERE Date(order_date) = '2005-09-01';

  • 如果要的是日期，使用Date()。想要的是时间，使用Time()。

  • 检索2005年9月下的所有订单。

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

# 更好的方法
WHERE Year(order_date) = 2005 AND Month(order_date) = 9;

11.2.3 数值处理函数

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

第12章 汇总数据

12.1 聚集函数

  • 聚集函数，运行在行组上，计算和返回单个值的函数。
表12-1 SQL聚集函数
函数 说明
AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和

12.1.1 AVG()函数

  • 可以返回所有列的平均值，也可以返回特定列或行的平均值
1
2
3
# 返回products表中所有产品的平均价格
SELECT AVG(prod_price) AS avg_price
FROM products;
1
2
3
4
# 返回特定供应商的产品平均价格
SELECT AVG(prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;
  • AVG只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出，为了获得多个列的平均值，必须使用多个AVG()函数。
  • AVG函数忽略列值为NULL的行。

12.1.2 COUNT()函数

  • 确定表中行的数目或符合特定条件的行的数目。
  • 两种使用方式：
    • COUNT(*)，对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。
    • COUNT(column)，对特定列中具有值的行进行计数，忽略NULL值。
1
2
3
# 对所有行计数
SELECT COUNT(*) AS num_cust
FROM customers;
1
2
3
# 对只具有电子邮件地址的客户计数
SELECT COUNT(cust_email) AS num_cust
FROM customers;
  • 如果指定列名，则指定列的空值（NULL）被忽略，但如果用的是星号（不指定列名），则不忽略。

12.1.3 MAX()函数

  • 返回列中的最大值
1
2
3
# 返回products中的最大的价格
SELECT MAX(prod_price) AS max_price
FROM products;
  • 对非数值数据使用MAX()函数，允许返回任意列中的最大值，包括文本列。
  • MAX()函数忽略值为NULL的行。

12.1.4 MIN()函数

  • 返回列中的最小值。
1
2
3
# 返回products中的最小的价格
SELECT MIN(prod_price) AS min_price
FROM products;
  • 对非数值数据使用MIN()函数，允许返回任意列中的最小值，包括文本列。
  • MIN()函数忽略值为NULL的行。

12.1.5 SUM()函数

  • 返回指定列值的和。
1
2
3
4
# 返回orderitems表包含订单中实际的物品
SELECT SUM(quantity) AS items_ordered
FROM orderitems
WHERE order_num = 20005;
  • SUM()函数也可以合并计算值。
1
2
3
4
# 返回orderitems表中所有物品价格之和
SELECT SUM(item_price*quantity) AS total_price
FROM orderitems
WHERE order_num = 20005;
  • 可以执行多个列上的计算。
  • SUM()函数忽略列值为NULL的行。

12.2 聚焦不同值

  • 以下内容仅在MySQL 5及后期版本可用。
  • 以上5个聚集函数都可以如下使用：
    • 对所有行执行计算，指定ALL参数或不给参数（因为ALL是默认的）。
    • 只包含不同的值，指定DISTINCT参数。
1
2
3
4
# 返回特定供应商的 不同价格 的平均值
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;
  • DISTINCT必须指定列名，COUNT(DISTINCT)会报错，还不能用于计算或表达式。
  • DISTINCT用于MIN()和MAX()，可行但没有价值。用不用结果一样。

12.3 组合函数

1
2
3
4
5
6
# 执行了4个聚集计算
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;

第13章 分组数据

13.1 数据分组

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

13.2 创建分组

  • 分组时在SELECT语句的 GROUP BY子句中建立的。
1
2
3
4
# 使用GROUP BY 对每个供应商计算产品数量
SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;
  • GROUP BY 可以包含任意数目的列，这使得能对分组进行嵌套。
  • 如果使用了嵌套，数据将在最后规定的分组上进行汇总， 即所有列都一起计算。
  • GROUP BY 子句中列出的每个列都必须是检索列或有效的表达式，如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式，不能使用别名。
  • 除聚集语句外，SELECT语句中的每个列都必须在GROUP BY 子句中给出。
  • 若分组列中药NULL值，NULL值作为一个分组。

  • GROUP BY在WHERE子句之后，在ORDER BY 子句之前。

  • 使用WITH ROOLUP关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值。

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

13.3 过滤分组

  • WHERE过滤的是行而不是分组。
  • 使用HAVING过滤分组，WHERE子句可以用HAVING子句替代。
1
2
3
4
5
# 找出两个以上的订单的分组，使用WHERE不起作用
SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;
  • WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤，WHERE排除的行不包括在分组中。
1
2
3
4
5
6
# 列出具有2个及以上、价格为10及以上的产品的供应商
SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >= 2;

13.4 分组和排序

  • ORDER BY 与GROUP BY的差别
表13-1 ORDER BY 与GROUP BY
ORDER BY GROUP BY
排序产生的输出 分组行。但输出可能不是分组的顺序
任意列都可以使用（甚至非选择的列也可以使用） 只可能使用选择列或表达式列，而且必须使用每个选择列表式
不一定需要 如果与聚集函数一起使用列（或表达式），则必须使用
  • 一般在使用GROUP BY子句时，应该也给出ORDER BY 子句，这是保证数据正确排序的唯一方法。
1
2
3
4
5
6
# 列出总计订单价格大于等于50的订单号和总订单价格
SELECT order_num, SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >= 50
ORDER BY ordertotal;

13.5 SELECT子句顺序

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

第14章 使用子查询

14.1 子查询

  • 版本要求，MySQL4.1及以上版本。
  • 查询，任何SQL语句都是查询，但此术语一般指SELECT语句。
  • 子查询，嵌套在其他查询中的查询。

14.2 利用子查询进行过滤

  • 把一条SELECT语句返回的结果用于另一条SELECT语句的WHERE子句。
1
2
3
4
5
6
7
8
9
10
11
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 order_num FROM orderitems	WHERE prod_id = 'TNT2'; 在orderitems表里查询prod_id为TNT2的订单号，然后这些订单号以IN操作符传递给外部的WHERE子句。
# 然后在orders表里查询具有这些订单号的客户ID，并将客户ID以IN操作符传递给最外层的WHERE子句。
# 最后在customers表中查询这些客户ID的信息。
  • 在SELECT语句中，子查询总是从内向外处理。
  • 对于能嵌套的子查询的数目没有限制，但太多嵌套会影响性能。
  • 列必须匹配，SELECT语句应该具有与WHERE子句中相同数目的列。
  • 子查询可以与IN，=，<>等操作符结合使用。

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

  • 使用子查询的另一方法是创建计算字段。
1
2
3
4
5
6
7
8
9
# 从customers表中检索客户列表
# 对于检索出的每个客户，统计其在orders表中的订单数目
SELECT cust_name,
	   cust_state,
	   (SELECT COUNT(*)
	   FROM orders
	   WHERE orders.cust_id = customers.cust_id) AS orders)
FROM customers
ORDER BY cust_name;
  • 子查询中的WHERE语句需要使用完全限定列名。orders.cust_id = customers.cust_id。这种类型的子查询称为相关子查询，即涉及外部查询的子查询，必须使用这种语法。否则可能出错。

第15章 联结表

15.1 联结

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

15.1.1 关系表

  • 关系表的设计就是要保证把信息分解成多个表，一类数据一个表。各表通过某些常用的值（关系）互相关联。
  • 外键，某个表的一列，它包含另一个表的主键值，定义了两个表之间的关系。
  • 好处：
    • 信息不重复，不浪费时间和空间。
    • 信息变动，只更新对应表的单个记录，相关表中的数据不用改动。
    • 数据无重复，处理更简单。
    • 有效地存储和方便地处理。
  • 关系数据库的可伸缩性远比非关系数据库要好。
  • 可伸缩性，能够适应不断增加的工作量而不失败。

15.1.2 为什么要使用联结

  • 联结，用来在一条SELECT语句中关联表。
  • 维护引用完整性，仅在关系列中插入合法的数据。

15.2 创建联结

1
2
3
4
5
6
# prod_name, prod_price在products中，而vend_name在vendors中
# 使用WHERE正确联结，需完全限定列名
SELECT vend_name, prod_name, prod_price
FROM vendor, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;
  • 在引用的列可能出现二义性时，必须使用完全限定列名。

15.2.1 WHERE子句的重要性

  • WHERE作为过滤条件，只包含那些匹配给定条件（联结条件）的行。
  • 没有WHERE子句，第一个表中的每个行将与第二个表中的每个行配对，而不管它们逻辑上是否可以配在一起。 笛卡尔积，由没有联结条件的表关系返回的结果为笛卡尔积，检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。又称叉联结。
  • 应该保证所有联结都有WHERE子句，否则将返回比想要的数据多得多的数据

15.2.2 内部联结

  • 基于两个表之间的相等测试，称为等值联结，也称为内部联结。
  • 使用不同语法来明确联结的类型。
1
2
3
4
5
# 与前面例子完全相同
SELECT vend_name, prod_name, prod_price
FROM vendor INNER JOIN products
ON vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;
  • ANSI SQL规范首选INNER JOIN 语法。

15.2.3 联结多个表

  • SQL对一条SELECT语句中可以联结的表的数目没有限制。首先列出所有表，然后定义表之间的关系。
1
2
3
4
5
6
# 显示订单编号为20005的订单中的物品
SELECT vend_name, prod_name, prod_price, quantity
FROM orderitems, products, vendor
WHERE products.vend_id = vendors.vend_id
	AND orderitems.prod_id = products.prod_id
	AND order_num = 20005;

  • 性能考虑，联结的表越多，性能下降越厉害。

  • 联结表可以替代子查询。

1
2
3
4
5
6
7
8
9
10
11
12
13
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 orders.order_num = orderitems.order_num
	AND prod_id = 'TNT2';

第16章 创建高级联结

16.1 创建高级联结

  • 给列起别名的语法如下。
1
2
3
SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') AS vend_title
FROM vendors
ORDER BY vend_name;
  • 给表名起别名，可以缩短SQL语句，并允许在单条SELECT语句中多次使用相同的表。
1
2
3
4
5
SELECT cust_name, cust_contact
FROM customers AS c, orders AS o, orderitems AS oi
where c.cust_id = o.cust_id
	AND io.order_num = o.order_nu
	AND prod_id = 'TNT2';
  • 表别名只在查询执行中使用，与列别名不一样，表别名不返回到客户机。

16.2 使用不同类型的联结

  • 前文都是内部联结，下面介绍自联结、自然联结和外部联结。

16.2.1 自联结

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 查询生产ID为DTNTR的供应商的其他物品

# 使用子查询
SELECT prod_id, prod_name
FROM products
WHERE vend_id = (SELECT vend_id
				FROM products
				WHERE prod_id = 'DTNTR');

# 使用自联结
# 使用两次products表，但用不同的别名
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';
  • 用自联结而不用子查询，虽然结果相同，但自联结速度更快。

16.2.2 自然联结

  • 自然联结排除列多次出现，使每个列只返回一次。
1
2
3
4
5
6
7
# 通配符只对第一个表使用，所有其他列明确列出，所有没有重复的列被检索出来
SELECT c.*, o.order_num, o.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
	AND oi.order_num = o.order_num
	AND prod_id = 'FB';
  • 到目前为止 每个内部联结都是自然联结，一般不会用到不是自然联结的内部联结。

16.2.3 外部联结

  • 外部联结，联结包含了在相关表中没有关联行的行。
1
2
3
4
# 内部联结 检索所有客户及其订单
SELECT customers.cust_id, orders.order_num
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id;
1
2
3
4
# 外部联结 检索所有客户，包括没有订单的客户
SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;
  • 在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表，RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表。上例使用LEFT OUTER JOIN从FROM 子句的左边表(customers表)中选择所有行。
  • 不支持*=和=*的使用。

16.3 使用带聚集函数的联结

1
2
3
4
5
6
7
# 检索所有客户及每个客户所下的订单数
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;
1
2
3
4
5
6
7
# 使用左外部联结来包含所有客户，包括没有任何下订单的客户
SELECT customers.cust_name,
		customers.cust_id,
		COUNT(orders.order_num) AS num_ord
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;

16.4 使用联结和联结条件

  • 注意所使用的联结条件，一般使用内部联结。
  • 保证使用正确的联结条件，否则将返回不正确的数据。
  • 应该总是提供联结条件，否则会得出笛卡尔积。
  • 使用多个联结时，分别测试每个联结，这将使故障排除更为简单。

第17章 组合查询

17.1 组合查询

  • 组合查询，执行多条SELECT语句，并将结果作为单个查询结果集返回，这些组合查询又称并或复合查询。
  • 两种情况需要组合查询：
    • 在单个查询中从不同的表返回类似结构的数据。
    • 对单个表执行多个查询，按单个查询返回数据。
  • 组合查询与具有多个WHERE子句条件的单条查询相同。

17.2 创建组合查询

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

17.2.1 使用UNION

  • 给出每条SELECT语句，在各条语句之前放上关键字UNION。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 使用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_in IN (1001, 1002);

# 使用多条WHERE
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5 
	OR vend_in IN (1001, 1002);

17.2.2 UNION规则

  • UNION必须由两条或两条以上的SELECT语句组成，语句之间用UNION分隔。
  • UNION中的每个查询必须包含相同的列、表达式或聚集函数（列出次序可以不同）
  • 列数据类型必须兼容，类型不必完全相同，但必须是DBMS可以隐含地转换的类型。

17.2.3 包含或取消重复的行

  • UNION从查询结果集中自动去除了重复的行。可以使用UNION ALL不去除重复的行。

17.2.4 对组合结果排序

  • 在使用UNION组合查询时，只能使用一条ORDER BY子句，必须出现在最后一条SELECT语句之后。
1
2
3
4
5
6
7
8
9
# 使用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_in IN (1001, 1002)
ORDER BY vend_id, prod_price;

第18章 全文本搜索

18.1 理解全文本搜索

  • 两个最常用的引擎MyISAM和InnoDB，前者支持全文本搜索，而后者不支持。
  • 如果需要全文本搜索功能，在创建表时选择MyISAM引擎。
  • 通配符和正则表达式存在几个重要的限制：
    • 性能，通配符和正则表达式通常要求尝试匹配表中所有行，行增加时非常耗时。
    • 明确控制，很难明确控制匹配什么和不匹配什么。
    • 智能化的结果。

18.2 使用全文本搜索

  • 为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断地重新索引。
  • 在索引之后，SELECT可与Match()和Against()一起使用以执行搜索。

18.2.1 启用全文本搜索支持

  • 在创建表时启用全文本搜索，CRTATE TABLE语句（第21章介绍）接收FULLTEXT子句。
1
2
3
4
5
6
7
8
9
CRTATE TABLE productnotes
(
	note_id   int      NOT NULL AUTO_INCREMENT,
	prod_id   char(10) NOT NULL,
	note_date datetime NOT NULL,
	note_text text     NULL,
	PROMARY KEY(note_id),
	FULLTEXT(note_text)
) ENGINE = MyISAM;
  • MySQL根据子句FULLTEXT(note_text)的指示对note_text进行索引。FULLTEXT可以索引单个或者多个列。MySQL自动维护该索引，在增加、更新或删除行时，索引随之自动更新。
  • 可以在创建表时指定FULLTEXT，或者在稍后指定（在这种情况下所有已有数据必须立刻索引）。
  • 不要在导入数据时使用FULLTEXT，更新索引比较耗时。

18.2.2 进行全文本搜索

  • 使用函数Match()和Against()执行全文本搜索，其中Match()指定被搜索的列，Against()指定要使用的搜索表达式。
1
2
3
4
# 搜索note_text中含rabbit的行
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');
  • 传递给Match()的值必须与FULLTEXT()定义中的相同，如果指定多个列，则必须列出它们（而且次序正确）。
  • 搜索默认不区分大小写，除非使用BINARY关键字。
1
2
3
4
# 也使用LIKE 搜索note_text中含rabbit的行
SELECT note_text
FROM productnotes
WHERE note_text LIKE '%rabbit%';
  • LIKE以不那么有用的顺序返回数据，使用全文本搜索以文本匹配的良好程度排序返回数据。全文本搜索的一个重要部分就是对结果排序，具有较高等级的行先返回。
1
2
3
4
# 全文本搜索排序过程，不包含rabbit的行等级为0，包含rabbit的行中词靠前的行等级高
SELECT note_text
		Match(note_text) Against('rabbit') AS rank
FROM products;

18.2.3 使用查询扩展

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

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

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

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

  • 查询扩展功能只用于版本4.1.1或更高版本。
1
2
3
4
# 搜索note_text中含rabbit的行
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);
  • 表中的行越多，使用查询扩展返回的结果越好。

18.2.4 布尔文本搜索

  • MySQL支持全文本搜索的另一种形式，称为布尔方式。
  • 以布尔方式，可以提供如下内容的细节：
    • 要匹配的词；
    • 要排斥的词；
    • 排列提示；
    • 表达式分组；
    • 另外一些内容。
  • 即使没有FULLTEXT索引也可以使用，但性能比较差。
1
2
3
4
# 匹配词heavy，但排除包含任何以rope开始的词的行
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);
表18-1 全文本布尔操作符
布尔操作符 说明
+ 包含，词必须存在
- 排除，词必须不出现
> 包含，而且增加等级值
< 包含，而且减少等级值
() 把词组成表达式（允许这些子表达式作为一个组被包含、排除、排列等）
~ 取消一个词的排序词
* 词尾的通配符
“ ” 定义一个短语（与单个词的列表不一样，它匹配整个短语以便包含或排除这个短语） 
1
2
3
4
# 匹配包含词rabbit和bait的行
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE);
1
2
3
4
# 没有指定操作符，匹配包含rabbit和bait中的至少一个词的行
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit bait' IN BOOLEAN MODE);
1
2
3
4
# 匹配rabbit和carrot，增加前者的等级，降低后者的等级
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('>rabbit <carrot' IN BOOLEAN MODE);
1
2
3
4
# 匹配safe和combination，降低后者的等级
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('+safe +(<combination)' IN BOOLEAN MODE);

18.2.5 全文本搜索的使用说明

  • 在索引全文本数据时，短语被忽略且从索引中排除，短语定义为具有3个及3个以下字符的词（如果需要，这个数目可以更改）。
  • MySQL带有一个内建的非用词（stopword）列表，这些词在索引全文本数据时总是被忽略，如果需要，可以覆盖这个列表。
  • 许多词出现的频率很高，搜索它们没有用处（返回太多结果），因此，如果一个词出现在50%的行中，则将它作为一个非用词忽略。%50规则不用于 IN BOOLEAN MODE。
  • 如果表中的行数少于3，则全文本搜索不返回结果。
  • 忽略词中的单引号。例如don’t索引为dont
  • 不具有词分隔符的语言不能恰当地返回全文本搜索结果。
  • 仅在MyISAM引擎中支持全文本搜索。
  • 没有邻近操作符。

第19章 插入数据

19.1 数据插入

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

19.2 插入完整的行

  • 指定表名和被插入到新行中的值。
  • 存储到每个表列中的数据在VALUES子句中给出，对每个列必须提供一个值。
  • 各个列必须以它们在表定义中出现的次序填充。
1
2
3
4
5
6
7
8
9
10
11
# 插入一个新客户到customers表
INSERT INTO Customers
VALUES(NULL,
	'Pep E. LaPew',
	'100 Main Street',
	'Los Angeles',
	'CA',
	'90046',
	'USA',
	NULL,
	NULL);

  • 简单但不安全，尽量避免使用，高度依赖于表中列的定义次序，并且还依赖于其次序容易获得的信息，结构变动后各个列可能不能保持相同的次序。

  • 更安全的方法（不过更繁琐），在表名后的括号里明确给出列名。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 插入一个新客户到customers表
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);
  • 提供了列名，VALUES必须以其指定的次序匹配指定的列名，不一定按各个列出现在实际表中的次序，即使表的结构改变，仍能正确工作。
  • 总是使用列的列表。
  • 仔细地给出正确数目的VALUES值。

  • 使用给出列名的这种语法，还可以省略列，可以只给某些列提供值。

    • 该列定义为允许NULL值
    • 在表定义中给出默认值，这表示如果不给出值，将使用默认值
    • 如果对表中不允许NULL值且没有默认值的列不给出值，将会报错

  • 提高整体性能，INSERT操作比较耗时，可以在INSERT和INTO之间添加关键字LOW_PRIORITY，指示降低INSERT的优先级。

19.3 插入多个行

  • 使用多条INSERT语句，一次提交它们，每条语句用一个分号结束。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
# 插入多个新客户到customers表
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);
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);
  • 或者，只要每条INSERT语句中的列名和次序相同，可以如下组合各语句。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
# 插入多个新客户到customers表
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)，
VALUES(	'Pep E. LaPew',
	'100 Main Street',
	'Los Angeles',
	'CA',
	'90046',
	'USA',
	NULL,
	NULL);
  • 其中单条INSERT语句有多个值，每组值用一对圆括号括起来，用逗号分隔。此技术可以提高数据库处理的性能，因为用单条INSERT语句处理多个插入比使用多条INSERT语句块。

19.4 插入检索出的数据

  • 利用INSERT语句将一条SELEECT语句的结果插入表中，这就是所谓的INSERT SELECT，即由一条INSERT语句和一条SELECT语句组成。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 从custnew表将所有数据导入到customers表中
# 为避免重复，可以将INSERT和SELECT中的cust_id列忽略
INSERT INTO customers(cust_id,
					cust_contact,
					cust_email,
					cust_name,
					cust_address,
					cust_city,
					cust_state,
					cust_zip,
					cust_country)
SELECT cust_id,
        cust_contact,
        cust_email,
        cust_name,
        cust_address,
        cust_city,
        cust_state,
        cust_zip,
        cust_country
FROM custnew;
  • INSERT SELECT中的列名，不一定要求列名匹配，使用的是列的位置，SELECT中的第一列（不管其列名）将会用来填充INSERT表列中指定的第一个列。这对于使用不同列名的表中导入数据非常有用。
  • INSERT SELECT语句可包含WHERE子句过滤插入的数据。

第20章 更新和删除数据

20.1 更新数据

  • UPDATE语句更新（修改）表中的数据。
    • 更新表中特定行，使用WHERE子句
    • 更新表中所有行
  • UPDATE语句的组成。
    • 要更新的表
    • 列名和它们的新值
    • 确定要更新行的过滤条件
1
2
3
4
# 更新单个列
UPDATE customers
SET cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;
  • UPDATE后更要更新的表名。SET用来指定新值被赋予的列。WHERE指定特定的行，没有WHERE子句，将会更新所有行。
  • 在更新多个列时，只需要使用单个SET命令，每个 “列=值”对之间用逗号分隔。
1
2
3
4
5
# 更新两个列
UPDATE customers
SET cust_email = 'elmer@fudd.com',
	cust_name = 'The Fudds'
WHERE cust_id = 10005;
  • UPDATE语句可以使用子查询，使得能用SELECT语句检索出的数据更新列数据。

  • IGNORE关键字，即使发生错误，也继续进行更新，UPDATE IGNORE customers..。

  • 为删除某个列的值，可设置它为NULL值（假如表定义允许NULL值）。

1
2
3
UPDATE customers
SET cust_email = NULL
WHERE cust_id = 10005;

20.2 删除数据

  • DELETE语句删除（去掉）数据。
    • 从表中删除特定的行，使用WHERE子句
    • 从表中删除所有行
1
2
3
# 删除一行
DELETE FROM customers
WHERE cust_id = 10006;
  • DELETE不需要列名和通配符。
  • DELET 删除整行而不是删除列，为了删除指定的列，使用UPDATE语句。
  • DELETE从表中删除行，不删除表本身。
  • 如果想要删除表中所有的行，不要使用DELETE，可使用TRUNCATE TABLE语句，它完成同样的工作，但速度更快。

20.3 更新和删除的指导原则

  • 除非打算更新和删除所有行，否则不要使用不带WHERE子句的UPDATE或DELETE语句。
  • 保证每个表都有主键，尽可能像WHERE子句那样使用它。
  • 在对UPDATE或DELETE语句使用WHERE子句前，应先使用SELECT进行测试，保证正确过滤。
  • 使用强制引用完整性的数据库。
  • 没有撤销功能。

第21章 创建和操纵表

21.1 创建表

  • 创建表的方法。
    • 使用具有交互式管理和创建表的工具
    • 使用MySQL语句操纵，使用CREATE TABLE语句

21.1.1 表创建基础

  • 创建表，必须给出以下信息。
    • 新表的名字，在关键字CREATE TABLE之后给出
    • 表列的名字和定义，用逗号分隔
1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 创建customers表
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_conutry char(50)  NULL,
cust_contact char(50)  NULL,
cust_email   char(255) NULL,
PRIMARY KEY (cust_id)
) ENGINE = InnoDB;
  • SQL对缩进空格等格式化没有规定，推荐采用某种缩进格式。
  • 创建表时，指定的表名必须不存在，否则出错。如果表存在，一般首先手工删除表，然后在重建它。如果想仅在表不存在时创建，表名后使用IF NOT EXISTS。

21.1.2 使用NULL值

  • 允许NULL值的列允许在插入行时不给出该列的值，不允许NULL值的列在插入或更新行时，该列必须有值。
  • 创建表时，NULL为默认设置，如果不指定NOT NULL，则认为指定的是NULL。
  • NULL值是没有值，而不是空串''，空串是一个有效的值。

21.1.3 主键再介绍

  • 主键值必须唯一，如果使用单个列，则它的值必须唯一，如果使用多个列，则这些列的组合值必须唯一。
  • 多个列组成的主键，以逗号分隔的列表给出各列名。
1
2
3
4
5
6
7
8
9
10
# 多个列组成的主键
CREATE TABLE orderitems
(
order_num   int          NOT NULL,
order_item  int          NOT NULL,
prod_id     char(10)     NOT NULL,
quantity    int          NOT NULL,
item_price  decimal(8,2) NOT NULL,
PRIMARY KEY (order_num, order_item)
) ENGINE = InnoDB
  • 主键可以在创建表时定义，也可以在创建表之后定义。
  • 主键中不允许NULL值的列，允许NULL值的列不能作为唯一标识。

21.1.4 使用AUTO_INCREMENT

  • 设置AUTO_INCREMENT 的列，每当增加一行时自动增量。
  • 每个表只允许一个AUTO_INCREMENT 列，而且它必须被索引，可以通过使它成为主键值。
  • AUTO_INCREMENT 的列可以在插入时指定一个值，只要它是唯一（至今尚未使用过）即可，该值将被用来替代自动生成的值，后续的增量将开始使用该手工插入的值。
  • 确定AUTO_INCREMENT的值，使用SELECT last_insert_id()返回最后一个AUTO_INCREMENT值，然后可以将该值用于其他MySQL语句。

21.1.5 指定默认值

  • 默认值用CREATE TABLE语句的列定义中的DEFALUT关键字指定。
1
2
3
4
5
6
7
8
9
10
# 指定quantity的默认值为1
CREATE TABLE orderitems
(
order_num  int NOT NULL,
order_item int NOT NULL,
prod_id char(10) NOT NULL,
quantity int NOT NULL DEFAULT 1,
item_price decimal(8,2) NOT NULL,
PRIMARY KEY (order_num, order_item)
) ENGINE = InnoDB;
  • 不允许使用函数作为默认值，它只支持常量。
  • 使用默认值而不是NULL值。

21.1.6 引擎类型

  • 如果省略ENGINE=语句，则使用默认引擎（很可能是MyISAM）。
  • InnoDB是一个可靠的事物处理引擎，不支持全文本搜索。
  • MEMORY在功能上等同于MyISAM，但由于数据存储在内存中，速度很快（适合于临时表）。
  • MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事物管理。
  • 引擎类型可以混用。
  • 外键不跨引擎。
  • MyISAM最受欢迎。

21.2 更新表

  • 使用ALTER TABLE更新表定义，但是，理想状态下，当表中存储数据以后，该表就不应该别更新。
  • 使用ALTER TABLE更改表结构，需给出以下信息。
    • 在ALTER TABLE之后给出要更改的表名（该表必须存在）。
    • 所做更改的列表。
1
2
3
# 给表添加一个名为vend_phone的列，必须明确数据类型
ALTER TABLE vendors
ADD vend_phone CHAR(20);
1
2
3
# 删除列
ALTER TABLE vendors
DROP COLUMN vend_phone;
  • 使用ALTER TABLE 定义外键。
1
2
3
4
5
6
7
ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_orders
FOREIGN KEY (order_num) REFERENCES orders (order_num);

ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_products
FOREIGN KEY (prod_id) REFERENCES products (prod_id);
  • 复杂的表结构更改需要手动删除过程，步骤：
    • 用新的列布局创建一个新表；
    • 使用INSERT SELECT语句从旧表复制数据到新表，如果有必要，可使用转换函数和计算字段；
    • 检验包含所需数据的新表；
    • 重命名旧表（如果确定，可以删除它）；
    • 用旧表原来的名字重命名新表；
    • 根据需要，重新创建触发器、存储过程、索引和外键。
  • 使用ALTER TABLE前应该做一个完整的备份，因为数据表的更改不能撤销。

21.3 删除表

  • 使用DROP TABLE 语句。DROP TABLE customers2，删除表没有确认，也不能撤销。

21.4 重命名表

  • 使用RENAME TABLE语句。
1
2
3
4
5
6
7
# 重命名一个表
RENAME TABLE customers2 TO customers;

# 重命名多个表，使用逗号分隔
RENAME TABLE backup_customers TO customers,
				backup_vendors TO vendors,
				backup_produts TO products;

第22章 使用视图

22.1 视图

  • 版本要求，MySQL5及之后版本添加了对视图的支持。
  • 视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询。
1
2
3
4
5
6
7
8
9
10
11
SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
	AND orders.order_num = orderitems.order_num
	AND prod_id = 'TNT2';
	
# 假如可以把以上整个查询包装成一个productcustomers的虚拟表，则可以从虚拟表中检索数据
# productcustomers是一个视图
SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';

22.1.1 为什么使用视图

  • 重用SQL语句。
  • 简化复杂的SQL操作。
  • 使用表的组成部分而不是整个表。
  • 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。

  • 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

  • 在视图创建之后，可以用于表基本相同的方式利用他们，可以执行SELECT，过滤和排序，联结，甚至是添加和更新数据的操作。

  • 性能问题。视图不包含数据，每次使用视图时，都必须处理查询执行时所需的任一个查询，如果使用多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降得很厉害。

22.1.2 视图的规则和限制

  • 与表一样，视图必须唯一命名。
  • 对于可创建的视图数目没有限制。
  • 为了创建视图，必须具有足够的访问权限。这些限制通常有数据库管理人员授予。
  • 视图可以嵌套。
  • ORDER BY 可以用在视图中，但如果从该视图检索数据SELECT中也含有ORDER BY，那么该视图中的ORDER BY 将被覆盖。
  • 视图不能索引，也不能有关联的触发器或默认值。
  • 视图可以和表一起使用。

22.2 使用视图

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

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

  • 视图最常用的应用之一是隐藏复杂的SQL。
1
2
3
4
5
6
7
8
9
10
11
12
13
# 创建名为productcustomers的视图，联结三个表，以返回以订购了任意产品的所有客户的列表
CREATE VIEW productcustomers AS
SELECT cust_name, cust_contact, prod_id
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
	AND orders.order_num = orderitems.order_num;
	
# 如果执行 SELECT * FROM productcustomers，将列出订购了任意产品的客户

# 检索订购了产品TNT2的客户
SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';
  • 创建可重用的视图是一种好方法。

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

  • 视图的另一常见用途是重新格式化检索出的数据。
1
2
3
4
5
# 返回供应商名和位置（来自第10章）
SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')')
		AS vend_title
FROM vendors
ORDER BY vend_name;
  • 假如要经常使用这个格式的结果，不必在每次需要时执行联结，创建一个视图，每次需要时使用它即可。
1
2
3
4
5
6
7
8
9
10
# 创建视图 返回供应商名和位置
CREATE VIEW vendorlocations AS
SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')')
		AS vend_title
FROM vendors
ORDER BY vend_name;

# 检索所有供应商和位置的数据
SELECT * 
FROM vendorlocations;

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

  • 视图也可以使用WHERE子句过滤数据。
1
2
3
4
5
6
7
8
9
# 创建视图，排除没有电子邮件地址的用户
CREATE VIEW customeremaillist AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email is NOT NULL;

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

22.2.4 使用视图与计算字段

  • 视图对于简化计算字段的使用特别有用。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 检索某个特定订单中的物品，计算每种物品的总价格（来自第10章）
SELECT prod_id,
		quantity,
		item_price,
		quantity*item_price AS expand_price
FROM orderitems
WHERE order_num = 20005;

# 将其转化为一个视图
CREATE VIEW orderitemsexpanded AS 
SELECT prod_id,
		quantity,
		item_price,
		quantity*item_price AS expand_price
FROM orderitems;

# 检索订单20005的详细内容
SELECT *
FROM orderitemsexpanded
WHERE order_num = 20005;

22.2.5 更新视图

  • 视图的数据是否可以更新是视情况而定。
  • 通常，视图是可以更新的，更新一个视图将更新其基表（视图本身没有数据）。
  • 但是，并非所有视图都是可更新的，如果不能正确地确定被更新的基数据，则不允许更新。
  • 如果视图定义中有以下操作，将不能更新：
    • 分组（GROUP BY 和HAVING）
    • 联结
    • 子查询
    • 并
    • 聚集函数（MIN()、COUNT()、SUM() 等）
    • DISTINCT
    • 导出（计算）列
  • 视图主要用于数据检索（SELECT），很少用于更新（INSERT、UPDATE和DELETE）。

第23章 使用存储过程

23.1 存储过程

  • MySQL添加了对存储过程的支持，支持MySQL5及以后的版本。
  • 存储过程，就是为以后的使用而保存的一条或多条MySQL语句的集合，可将其视为批文件。

23.2 为什么要使用存储过程

  • 存储过程的好处：
    • 通过把处理封装在容易使用的单元中，简化复杂的操作。
    • 由于不要求反复建立一系列处理步骤，保证了数据的完整性，所有开发人员和应用程序都使用同一的存储过程，则所使用的代码都是相同的。 防止错误，需要执行的步骤越多，出错的可能性就越大。
    • 简化对变动的管理，如果表名、列名或业务逻辑有变化，只需要更改存储过程的代码。通过对存储过程限制对基础数据的访问保证了安全性。
    • 提高性能，使用存储过程比适用单独的SQL语句要快。
    • 总结：简单，安全，高性能。
  • 存储过程的缺陷：
    • 编写比基本的SQL语句复杂。
    • MySQL将编写和执行存储过程的安全和访问区分开来，你可能没有创建存储过程的安全访问权限。
    • 不能编写存储过程，但仍然可以使用。

23.3 使用存储过程

23.3.1 执行存储过程

  • 调用，CALL，接受存储过程的名字以及需要传递给它的任意参数。
1
2
3
4
# 执行名为productpricing的存储过程，返回产品的最低、最高和平均价格
CALL productpricing(@pricelow,
					@pricehigh,
					@priceaverage);
  • 存储过程可以显示结果，也可以不显示结果。

23.3.2 创建存储过程

1
2
3
4
5
6
# 返回平均价格的存储过程，名为productpricing，没有参数
CREATE PROCEDURE productpricing()
BEGIN
	SELECT Avg(prod_price) AS priceaverage
	FROM products;
END;
  • 因为存储过程的语句结束分隔符也为;，需要临时更改语句结束分隔符。除 \ 符号外，任何字符都可以作为语句分隔符。
1
2
3
4
5
6
7
8
9
10
11
# 告诉命令行使用 // 作为新的语句结束分隔符
DELIMITER //
# 返回平均价格的存储过程，名为productpricing，没有参数
CREATE PROCEDURE productpricing()
BEGIN
	SELECT Avg(prod_price) AS priceaverage
	FROM products;
END//

# 恢复为原来的语句结束分隔符
DELIMITER ;
1
2
# 调用存储过程
CALL productpricing();

23.3.3 删除存储过程

  • 存储过程创建后，被保存在服务器上以供使用，直至被删除。
  • 删除存储过程，DROP PROCEDURE productpricing;。存储过程名后不带()，只给出存储过程名。
  • 仅当存在时删除，DROP PROCEDURE IF EXISTS productpricing。

23.3.4 使用参数

  • 一般地，存储过程并不显示结果，而是把结果返回给你指定的变量。
  • 变量，内存中一个特定的位置，用来临时存储数据。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 以下是productpricing的修改版本，如果不事先删除此存储过程，则不能再次创建它
# 此存储过程接受三个参数，pl存储最低价格，ph存储最高价格，pa存储最高价格
CREATE PROCEDURE productpricing(
OUT pl DECIMAL(8,2),
OUT ph DECIMAL(8,2),
OUT pa DECIMAL(8,2)
)
BEGIN
	SELECT Min(prod_price)
	INTO pl
	FROM products;
	SELECT Max(prod_price)
	INTO ph
	FROM products;
	SELECT Avg(prod_price)
	INTO pa
	FROM products;
END;
  • 每个参数必须具有指定的类型，关键字OUT指出相应的参数用来从存储过程传出一个值，返回给调用者。
  • IN（传递给存储过程），OUT（从存储过程传出），INTO（对存储过程传入和传出）。
  • 记录集是不允许的类型，不能通过一个参数返回多个行和列。
1
2
3
4
5
6
7
8
# 调用此修改过的存储过程，需指定三个变量名
CALL productpricing(@pricelow,
					@pricehigh,
					@priceavgerage);
# 调用时不现实任何数据，返回可以显示的变量，显示产品的平均价格
SELECT @priceaverage;
# 显示三个值
SELECT @pricehigh, @pricelow, @priceaverage;
  • MySQL的所有变量都必须以@开始。
  • 使用IN和OUT参数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 使用IN和OUT参数，ordertotal接受订单号并返回该订单的合计
# onumber定义为IN，订单号被传入存储过程
# ototal定义为OUT，从存储过程返回合计
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

23.3.5 建立智能存储过程

  • 包含业务规则和智能处理的存储过程。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
# name: ordertotal
# parameters: onumber = order number
# 			  taxable = 0 if not taxable, 1 if taxable
# 			  ototal = order total variable
CREATE PROCEDURE ordertotal(
	IN onumber INT,
	IN taxable BOOLEAN,
	OUT ototal DECIMAL(8,2)
)COMMENT 'Obtain order total, optionally adding tax' 
# COMMENT不是必须的，如果给出，将在SHOW PROCEDURE STATUS的结果中显示
BEGIN
	# Declare variable for total
	DECLARE total DECIMAL(8,2);
	# Declare tax percentage
	DECLARE taxrate INT DEFAULT 6;
	
	# Get the order total
	SELECT Sum(item_price*quantity)
	FROM orderitems
	WHERE order_num = onumber
	INTO total;
	
	# Is this taxable
	IF taxable THEN
		# Yes, so add taxrate to the total
		SELECT total+(total/100*rate) INTO total
	END IF;
	
	# And finaly, save to out variable
	SELECT total INTO ototal;
END;

# 调用，通过给中间的参数指定0或1，可以有条件地将营业税加到合计上
CALL ordertotal(2005, 0, @total);
SELECT @total;
CALL ordertotal(2005, 1, @total);
SELECT @total;

23.3.6 检查存储过程

  • 显示用来创建一个存储过程，的CREATE语句。SHOW CREATE PROCEDURE ordertotal。
  • 获得包括何时、由谁创建等详细信息的所有存储过程列表，使用SHOW PROCEDURE STATUS。
  • 使用LIKE指定一个过滤模式，列出指定的存储过程，使用SHOW PROCEDURE STATUS LIKE 'ordertotal'。

第24章 使用游标

24.1 存储过程

  • 版本MySQL5及之后的版本支持。
  • 使用游标的原因，需要在检索出来的行中前进或后退一行或多行。
  • 游标是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。
  • 游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或作出更改。
  • MySQL的游标只能用于存储过程（和函数）。

24.2 使用游标

  • 使用前需要声明（定义）游标。
  • 声明后，需要打开游标以供使用。
  • 对于填有数据的游标，根据需要取出（检索）各行。
  • 在结束游标使用时，必须关闭游标。

24.2.1 创建游标

  • 游标用DECLARE语句创建，并定义响应的SELECT语句，根据需要带WHERE和其他子句。
1
2
3
4
5
6
7
# 创建名为ordernumbers的游标，使用了可以检索所有订单的SELECT语句
CREATE PROCEDURE processorders()
BEGIN
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
END;
  • 存储过程处理完成后，游标就消息（因为它局限于存储过程），在定义游标之后，可以打开它。

24.2.2 打开和关闭游标

  • 使用OPEN CURSOR语句打开游标。如OPEN ordernumbers。在处理OPEN语句时执行查询，存储检索出的数据以供浏览和滚动。
  • 关闭游标，CLOSE ordernumbers。CLOSE释放游标使用的所有内部内存和资源，游标不需要时应该关闭。
  • 隐含关闭，如果不明确关闭游标，将会在到达END语句时自动关闭。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 创建名为ordernumbers的游标，使用了可以检索所有订单的SELECT语句
CREATE PROCEDURE processorders()
BEGIN
	# 声明游标
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	
	# 打开游标
	OPEN ordernumbers;
	
	# 关闭游标
	CLOSE ordernumbers;
END;

24.2.3 使用游标数据

  • 使用FETCH语句分别访问它的每一行，FETCH指定检索什么数据（所需的列），检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针，使下一条FETCH语句检索下一行。

  • 从游标中检索单个行。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 创建名为ordernumbers的游标，使用了可以检索所有订单的SELECT语句
CREATE PROCEDURE processorders()
BEGIN
	# 声明游标
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	
	# 打开游标
	OPEN ordernumbers;
	
	# 检索当前行的order_num列（自动从第一行开始）到一个名为o的局部声明的变量中。
	FETCH ordernumbers INTO o
	
	# 关闭游标
	CLOSE ordernumbers;
END;
  • 循环检索数据，从第一行到最后一行。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 创建名为ordernumbers的游标，使用了可以检索所有订单的SELECT语句
CREATE PROCEDURE processorders()
BEGIN
	# 声明游标
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	
	# 打开游标
	OPEN ordernumbers;
	
	# 循环检索
	REPEAT
		FETCH ordernumbers INTO o;
	UNTIL done END REPEAT;
	
	# 关闭游标
	CLOSE ordernumbers;
END;
  • 需设置变量done为真的停止条件。
1
2
3
# 定义了CONTINUE HANDLER，指出当SQLSTATE '02000'出现时，SET done 1,
# SQLSTATE '02000'是一个未找到条件
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  • DECLARE语句定义的局部变量必须在定义任意游标或句柄之前定义，而句柄必须在游标之后定义。

  • 更详细的游标存储过程样例。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
# 创建名为ordernumbers的游标，使用了可以检索所有订单的SELECT语句
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;
	
	# 定义句柄
	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);
		# 将订单号和订单合计插入ordertotals结果表中
		INSERT INTO ordertotals(order_num, total)
		VALUES(o, t);
		
	UNTIL done END REPEAT;
	
	# 关闭游标
	CLOSE ordernumbers;
END;
  • 此存储过程不返回数据，但它能够创建和填充另一个表。查看该表，SELECT * FROM ordertotals。

第25章 使用触发器

25.1 触发器

  • MySQL5或之后的版本支持触发器。
  • 希望某条语句（或某些语句）在事件发生时自动执行。
  • 触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句（或位于BEGIN和END语句之间的一组语句）：
    • DELETE
    • INSERT
    • UPDATE
  • 其他MySQL语句不支持触发器。

25.2 创建触发器

  • 在创建触发器时，需要给出四条信息：
    • 唯一的触发器名
    • 触发器关联的表
    • 触发器应该响应的活动（DELETE、INSERT或UPDATE）
    • 触发器何时执行（处理之前或之后）

  • 触发器在每个表中唯一，在数据库中的两个表可以具有相同名字的触发器。但最好在数据库范围内使用唯一的触发器名。

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

1
2
3
4
5
# 创建名为newproduct的触发器，将在INSERT语句执行后执行
# 指定FOR EACH ROW SELECT，对每个插入行执行
# 本文Product added对每个插入行显示一次
CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added';
  • 只有表才支持触发器，视图不支持（临时表也不支持）。
  • 触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器，每个表最多支持6个触发器（每天INSERT、UPDATE和DELETE的之前和之后）。
  • 单个触发器不能与多个事件或多个表关联，如果你需要对一个INSERT和UPDATE操作执行的触发器，则应该定义两个触发器。

25.3 删除触发器

  • 使用DROP TRIGGER 语句。如DROP TRIGGER newproduct。
  • 触发器不能更新或覆盖，为了修改一个触发器，必须先删除它，然后再重新创建。

25.4 使用触发器

25.4.1 INSERT触发器

  • INSERT 触发器在INSERT语句执行之前或之后执行。
    • 在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行。
    • 在BEFORE INSERT触发器中，NEW中的值也可以被更新（允许更改被插入的值）。
    • 对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值。
1
2
3
4
5
6
7
8
9
10
# 创建一个名为neworder的触发器
# 插入一个新订单到orders表时，MySQL生成一个新订单号并保存到order_num中
# 触发器从NEW.order_num取得这个值并返回它
CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num;

# 插入一个新行
# orders包含3行，order_date和 cust_id必须给出，order_num由MySQL自动生成
INSERT INTO order(order_date, cust_id)
VALURES(now(), 10001)

25.4.2 DELETE触发器

  • DELETE触发器在DELETE语句执行之前或之后执行。
    • 在DELETE触发器代码内，可引用一个名为OLD的虚拟表，访问被删除的行。
    • OLD中的值全都是只读的，不能更新。
1
2
3
4
5
6
7
# 在任意订单被删除前将执行此触发器，它使用一条INSERT语句将OLD中的值（要被删除的订单）保存到archive_orders存档表中
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;
  • 使用BEGIN 和 END语句的好处是触发器能容纳多条SQL语句。

25.4.3 UPDATE触发器

  • UPDATE 触发器在UPDATE语句执行之前或之后执行。
    • 在UPDATE触发器代码内，可引用一个名为OLD的虚拟表访问以前的值，引用一个名为NEW的虚拟表访问被新更新的值。
    • 在BEFORE UPDATE触发器中，NEW中的值也可以被更新（允许更改被插入的值）。
    • OLD中的值全都是只读的，不能更新。
1
2
3
# 保证输入的州名是大写，不管输入是大写还是小写
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state)

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

  • 创建触发器可能需要特殊的安全访问权限。但是触发器的执行时自动的。
  • 用触发器保证数据的一致性（大小写、格式等）。
  • 触发器可以创建审计跟踪，把更改记录到另一个表非常容易。
  • 触发器不支持CALL语句。

第26章 管理事务处理

26.1 事务处理

  • MyISAM和InnoDB是两种最常使用的引擎，前者不支持明确的事务处理管理，后者支持。
  • 事务处理用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。
  • 利用事务管理，可以保证一组操作不会中途停止，它们作为整体执行，或者完全不执行。

  • 如果没有发生错误，整组语句提交给数据库表，如果发生错误，则进行回退（撤销）以恢复数据库到某个已知且安全的状态。

  • 事务（transaction）：一组SQL语句。

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

26.2 控制事务处理

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

26.2.1 使用ROLLBACK

  • 使用ROLLBACK来回退（撤销）MySQL语句。
1
2
3
4
5
6
SELECT * FROM ordertotals; # 显示表ordertotals不为空
START TRANSACTION; # 开始一个事务处理
DELETE FROM ordertotals; # 删除ordertotals中的所有行，注意不是删除表
SELECT * FROM ordertotals; # 验证ordertotals确实为空
ROLLBACK; # 回退START TRANSACTION之后的所有语句
SELECT * FROM ordertotals; # 显示表ordertotals不为空，回退成功
  • ROLLBACK 只能在一个事务处理内使用（执行一条START TRANSACTION 命令之后）。
  • 事务管理用来管理INSERT、UPDATE和DELETE语句。
  • 不能回退SELECT语句、CREATE和DROP操作。

26.2.2 使用COMMIT

  • 一般的MySQL语句都是隐含提交的，即提交（写或保存）操作都是自动的。
  • 但事务处理块中，提交不会隐含地进行，使用COMMIT语句进行明确的提交。
1
2
3
4
5
# 使用COMMIT语句仅在不出错时写出更改
START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;
  • 当COMMIT或ROLLBACK语句执行后，事务会自动关闭（将来的更改会隐含提交）。

26.2.3 使用保留点

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

  • 为了支持回退部分事务处理，必须能在事务处理块中合适位置放置占位符，如果需要回退，可以回退到某个占位符。

  • 创建占位符，使用SAVEPOINT，例如SAVEPOINT delete1;。

  • 每个保留点都标识它的唯一名字。

  • 回退到保留点，ROLLBACK TO delete1;。

  • 保留点越多越好，越灵活。

  • 保留点在事务处理完成后自动释放，也可以用RELEASE SAVEPOINT明确地释放保留点。

26.2.4 更改默认的提交行为

  • MySQL默认自动提交所有更改。
  • 为只是MySQL不自动提交更改，使用SET autocommit=0;。

第27章 全球化和本地化

27.1 字符集和校对顺序

  • MySQL需要适应不同的字符集，适应不同的排序和检索数据的方法。
  • 字符集：字母和符号的结合。
  • 编码：某个字符集成员的内部表示。
  • 校对：规定字符如何比较的指令。
  • 使用何种字符集合校对的决定在服务器、数据库和表级进行。

27.2 使用字符集和校对顺序

  • 查看所支持的字符集完整列表，SHOW CHARACTER SET;。
  • 查看所支持校对的完整列表，SHOW COLLATION。

  • 可以在安装时定义一个默认的字符集和校对，也可以在创建数据库时，指定默认的字符集和校对。为了确定所用的字符集和校对，使用语句，SHOW VARIABLES LIKE 'character%'和SHOW VARIABLES LIKE 'collation%'。

  • 给表指定字符集和校对，使用带子句的CREATE TABLE。

1
2
3
4
5
6
CREATE TABLE mytable
(
column1 INT,
column2 VARCHAR(10)
) DEFAULT CHARACTER SET hebrew
COLLATE hebrew_general_ci;
  • 除了可以给表指定字符集和校对，还可以对每个列设置它们。
1
2
3
4
5
6
7
8
CREATE TABLE mytable
(
column1 INT,
column2 VARCHAR(10),
column3 VARCHAR(10) CHARACTER SET latin1 
					COLLATE latin1_general_ci;
) DEFAULT CHARACTER SET hebrew
COLLATE hebrew_general_ci;
  • 校对在用ORDER BY子句检索出来的数据排序时起重要的作用。
1
2
3
# 使用与创建表时不同的校对顺序排序特定的SELECT语句
SELECT * FROM customers
ORDER BY lastname, firstname COLLATE latin1_general_cs;

第28章 安全管理

28.1访问控制

  • 安全基础：用户应该对他们需要的数据具有恰当的访问权，既不能多也不能少。用户不能对过多的数据具有过多的访问权。
  • 访问控制：给用户提供他们所需的访问权，且仅提供他们所需的访问权。
  • 不要在日常操作中使用root。

28.2 管理用户

  • 获取所有用户账户列表。
1
2
3
# mysql数据库有一个名为user的表，它包含所有用户账号
USE mysql;
SELECT user FROM user;

28.2.1 创建用户账户

  • 创建新用户。
1
2
# 创建一个新用户，用户名ben，口令p@$$word
CREATE USER ben IDENTIFIED BY 'p@$$word';
  • IDENTIFIED BY为纯文本口令，为了作为散列指定加密口令，使用IDENTIFIED BY PASSWORD。
  • 重命名用户名。使用RENAME USER ben TO bforta;。

28.2.2 删除用户账户

  • 使用DROP USER语句，如DROP USER bforta;。

28.2.3 设置访问权限

  • 新创建的用户没有访问权限。

  • 查看赋予用户的权限，SHOW GRANTS FOR bforta;。

  • 用户定义为user@host。默认主机名为%。

  • 为设置权限，使用GRANT语句，需给出以下信息：
    • 要授予的权限。
    • 被授予访问权限的数据库或表。
    • 用户名。
1
2
3
4
5
# 允许用户在crashcourse.*（crashcourse数据库的所有表）上使用SELECT
GRANT SELECT ON crashcourse.* TO bforta;

# 查看添加的权限
SHOW GRANTS FOR bforta;

  • 撤销特定的全效，使用REVOKE语句。REVOKE SELECT ON crashcourse.* FROM bforta;，撤销了bforta的SELECT访问权限。

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

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

28.2.4 更改口令

  • 使用SET PASSWORD，新口令必须使用Password加密：SET PASSWORD FOR bforta = Password('n3w p@$$word')。
  • 设置自己的口令：SET PASSWORD = Password('n3w p@$$word')。

第29章 数据库维护

29.1 备份数据

  • 使用命令行实用程序mysqldump转储所有数据库内容到某个外部文件。
  • 可用命令行实用程序mysqlhotcopy从一个数据库复制所有数据。
  • 可以使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE转储所有数据到某个外部文件。

29.2 进行数据库维护

  • ANALYZE TABLE，用来检查表键是否正确。
  • CHECK TABLE，用来针对许多问题对表进行检查。

29.3 诊断启动问题

  • 服务器启动问题通常在对MySQL匹配或服务器本身进行更改时出现。
  • 排除系统启动问题时，首先应该尽量用手动启动服务器。
  • mysql命令行选项。
    • —help显示帮助。
    • —safe -mode装载减去某些最佳配置的服务器。
    • —verbose显示全文本信息。
    • —version显示版本信息。

29.4 查看日志文件

  • 错误日志。—log-error
  • 查询日志。—log
  • 二进制日志。—log- bin
  • 缓慢查询日志。—log-slow-queries

第30章 改善性能

30.1 改善性能

  • 运行在专用服务器上。
  • 调整内存分配、缓冲区大小。
  • 显示所有活动进程，KILL命令终结某个特定的进程。
  • 使用联结、并、子查询等方法编写SELECT语句。
  • 使用存储过程。
  • 使用正确的数据类型。
  • 不要检索比需求还要多的数据，尽量不要用SELECT *。
  • 使用DELAYED关键字。
  • 导入数据时，应该关闭自动提交。
  • 索引数据库表以改善数据检索的性能。
  • 使用多条SELECT语句和联结它们的UNION语句来替代一系列复杂的OR语句。
  • LIKE很慢，最好使用FULLTEXT。
  • 每条规则在某些条件下都会被打破。
Donate comment here
------------The End------------
0%