佳佳的博客

Appearance

MySQL 必知必会 --[英] Ben Forta ​

🏷️ MySQL

本书使用的表结构及数据可以在 https://forta.com/books/0672327120/ 上下载到。

第 1 章 了解 SQL ​

1.1 数据库基础 ​

  • 1.1.1 什么是数据库

    数据库是以某种有组织的方式存储的数据集合。

    人们通常用数据库这个术语来代表他们使用的数据库软件。这是不正确的，它是引起混淆的根源。确切地说，数据库软件应该称为DMBS（数据库管理系统）。

  • 1.1.2 表

    表 table ：某种特定类型数据的结构化清单。
    模式 schema ：关于数据库和表的布局及特性的信息。

  • 1.1.3 列和数据类型

    列 column ：表中的一个字段。所有表都是由一个或多个列组成的。
    数据类型 datatype ：所容许的数据的类型。每个表列都有相应的数据类型，它限制（或容许）该列中储存的数据。

  • 1.1.4 行

    行 row ：表中的一个记录。

    是记录（ record ）还是行（ row ）？从技术上来说，行才是正确的术语。

  • 1.1.5 主键

    主键 primary key ：一列（或一组列），其值能够唯一区分表中的每个行。
    应该总是定义主键。

    主键的最好习惯：

    • 不更新主键列中的值
    • 不重用主键列的值
    • 不在主键列中使用可能会更改的值

1.2 什么是 SQL ​

SQL 是结构化查询语言（Structured Query Language）的缩写。

SQL 优点：

  • SQL 不是某个特定数据库供应商专有的语言。几乎所有重要的 DBMS 都支持 SQL。
  • SQL 简单易学。
  • SQL 尽管看上去很简单，但它实际上是一种强有力的语言，灵活使用其语法元素，可以进行非常复杂和高级的数据库操作。

SQL 不是一种专利语言，而且存在一个标准委员会，他们试图定义可供所有 DBMS 使用的 SQL 语法，但事实上任意两个 DBMS 实现的 SQL 都不完全相同。

第 2 章 MySQL 简介 ​

2.1 什么是 MySQL ​

MySQL 是一种 DBMS，即它是一种数据库软件。

MySQL 是开发源代码的，一般可以免费使用（甚至可以免费修改）。

  • 2.1.1 客户机 - 服务器软件

    DBMS 可以分为两类：

    1. 基于共享文件系统的 DBMS
    2. 基于客户机 - 服务器的 DBMS

    MySQL、Oracle 以及 Microsoft SQL Server 等数据库是基于客户机 - 服务器的数据库。

  • 2.1.2 MySQL 版本

    JiaJia： 这本书英文版是 2009 年出版的，当时最新版是 5，现在最新版已经到 8 了。

2.2 MySQL 工具 ​

  • 2.2.1 mysql 命令行实用程序
    每个 MySQL 安装都有一个名为 mysql 的简单命令行使用程序。
  • 2.2.2 MySQL Administrator
    MySQL 管理器是一个图形交互客户机，用来简化 MySQL 服务器的管理。
    MySQL Administrator 需要额外下载安装。
  • 2.2.3 MySQL Query Browser
    MySQL Query Browser 是一个图形交互客户机，用来编写和执行 MySQL 命令。

第 3 章 使用 MySQL ​

3.1 连接 ​

为了连接到 MySQL，需要提供以下信息：

  • 主机名
  • 端口（默认为 3306）
  • 用户名
  • 用户口令（如果需要）

3.2 选择数据 ​

sql
use [数据库名];

3.3 了解数据库和表 ​

返回所有可用的数据库：

sql
show databases;

获取一个数据库内的表的列表：

sql
show tables;

显示表的字段信息：

sql
show columns from [表名];

或者

sql
describe [表名];

显示更详细的服务器状态信息：

sql
show status;

显示创建数据库的语句：

sql
show create database [数据库名];

显示创建表的语句：

sql
show create table [表名];

显示授予当前登录用户的权限：

sql
show grants;

显示服务器错误或警告信息：

sql
show errors;
show warnings;

显示允许的 show 语句：

sql
help 'show';

JiaJia：书中的写法是 help show; ，不带单引号，但是在 Navicat 中会报错，加上单引号就可以正常执行了。

执行结果如下（MySQL 版本： 5.7.30-0ubuntu0.16.04.1）：

sql
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:

SHOW {binary | MASTER} LOGS
SHOW BINLOG EVENTS [in 'log_name'] [from pos] [limit [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS from tbl_name [from db_name] [like_or_where]
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW create table tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATABASES [like_or_where]
SHOW engine engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [limit [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX from tbl_name [from db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [from db_name] [like_or_where]
SHOW PLUGINS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [limit n]
SHOW PROFILES
SHOW RELAYLOG EVENTS [in 'log_name'] [from pos] [limit [offset,] row_count]
SHOW SLAVE HOSTS
SHOW SLAVE STATUS [FOR CHANNEL channel]
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [from db_name] [like_or_where]
SHOW [FULL] TABLES [from db_name] [like_or_where]
SHOW TRIGGERS [from db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [limit [offset,] row_count]

like_or_where:
    LIKE 'pattern'
  | WHERE expr

If the syntax for a given SHOW statement includes a LIKE 'pattern'
part, 'pattern' is a string that can contain the SQL % and _ wildcard
characters. The pattern is useful for restricting statement output to
matching values.

Several SHOW statements also accept a WHERE clause that provides more
flexibility in specifying which rows to display. See
https://dev.mysql.com/doc/refman/5.7/en/extended-show.html.

URL: https://dev.mysql.com/doc/refman/5.7/en/show.html

第 4 章 检索数据 ​

4.1 SELECT 语句 ​

4.2 检索单个列 ​

sql
select prod_name
from products;

4.3 检索多个列 ​

sql
select prod_id, prod_name, prod_price
from products;

4.4 检索所有列 ​

sql
select *
from products;

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

4.5 检索不同的行 ​

sql
select distinct vend_id
from products;

4.6 限制结果 ​

sql
select prod_name
from products
limit 5;

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

sql
select prod_name
from products
limit 5, 5;

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

为了避免两个数字的混淆，MySQL 5 开始提供了另外一种写法：

sql
select prod_name
from products
limit 5 offset 5;

4.7 使用完全限定的表名 ​

sql
select products.prod_name
from products;

第 5 章 排序检索数据 ​

5.1 排序数据 ​

sql
select prod_name
from products
order by prod_name;

5.2 按多个列排序 ​

sql
select prod_id, prod_price, prod_name
from products
order by prod_price, prod_name;

5.3 指定排序方向 ​

sql
select prod_id, prod_price, prod_name
from products
order by prod_price desc;
sql
select prod_id, prod_price, prod_name
from products
order by prod_price desc, prod_name;

第 6 章 过滤数据 ​

6.1 使用 WHERE 子句 ​

sql
select prod_price, prod_name
from products
where prod_price = 2.50;

6.2 WHERE 子句操作符 ​

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

  • 6.2.1 检查单个值

    sql
    select prod_name, prod_price
from products
where prod_name = 'fuses';
    sql
    select prod_name, prod_price
from products
where prod_price < 10;
    sql
    select prod_name, prod_price
from products
where prod_price <= 10;

  • 6.2.2 不匹配检查

    sql
    select prod_name, prod_price
from products
where vend_id <> 1003;
    sql
    select prod_name, prod_price
from products
where vend_id != 1003;

  • 6.2.3 范围值检查

    sql
    select prod_name, prod_price
from products
where prod_price between 5 and 10;

  • 6.2.4 空值检查

    sql
    select cust_id
from customers
where cust_email is null;

第 7 章 数据过滤 ​

7.1 组合 WHERE 子句 ​

  • 7.1.1 AND 操作符

    sql
    select prod_id, prod_price, prod_name
from products
where vend_id = 1003 and prod_price <= 10;

  • 7.1.2 OR 操作符

    sql
    select prod_id, prod_price, prod_name
from products
where vend_id = 1002 or vend_id = 1003;

  • 7.1.3 计算次序

    sql
    select prod_id, prod_price, prod_name
from products
where vend_id = 1002 or vend_id = 1003 and prod_price >= 10;

    SQL（像多数语言一样）在处理 or 操作符前，优先处理 and

    sql
    select prod_id, prod_price, prod_name
from products
where (vend_id = 1002 or vend_id = 1003) and prod_price >= 10;

7.2 IN 操作符 ​

sql
select prod_name, prod_price
from products
where vend_id in (1002, 1003)
order by prod_name;

in 最大的优点是可以包含其它 select 语句。

7.3 NOT 操作符 ​

sql
select prod_name, prod_price
from products
where vend_id not in (1002, 1003)
order by prod_name;

第 8 章 用通配符进行过滤 ​

8.1 LIKE 操作符 ​

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

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

  • 8.1.1 百分号 (%) 通配符

    % 表示任何字符出现任意次数。

    sql
    select prod_id, prod_name
from products
where prod_name like 'jet%';

    根据 MySQL 的配置方式，搜索可以区分或者不区分大小写。

    sql
    select prod_id, prod_name
from products
where prod_name like '%anvil%';

  • 8.1.2 下划线 (_) 通配符

    _ 只匹配单个字符。

    sql
    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 基本字符匹配

    sql
    select prod_name
from products
where prod_name regexp '1000'
order by prod_name;
    sql
    select prod_name
from products
where prod_name regexp '.000'
order by prod_name;

    使用 regexp 匹配不区分大小写，若需要区分，可使用 binary 关键字：

    sql
    select prod_name
from products
where prod_name regexp binary 'JetPack .000'
order by prod_name;

  • 9.2.2 进行 OR 匹配

    sql
    select prod_name
from products
where prod_name regexp '1000|2000'
order by prod_name;

  • 9.2.3 匹配几个字符之一

    sql
    select prod_name
from products
where prod_name regexp '[123] Ton'
order by prod_name;

    字符集合也可以被否定，在集合的开始处放置一个 ^ 即可。

    sql
    select prod_name
from products
where prod_name regexp '[^123] Ton'
order by prod_name;

  • 9.2.4 匹配范围

    sql
    select prod_name
from products
where prod_name regexp '[1-5] Ton'
order by prod_name;

  • 9.2.5 匹配特殊字符

    sql
    select vend_name
from vendors
where vend_name regexp '\\.'
order by vend_name;

    \\ 就是转义符，也可以用来引用元字符。

    • \\f ：换页
    • \\n ：换行
    • \\r ：回车
    • \\t ：制表
    • \\v ：纵向制表

    转义 \ 本身需要使用 \\\ 。

    之所以要使用两个反斜杠是因为 MySQL 自己要解释一个，正则表达式解释另一个。

  • 9.2.6 匹配字符类

    字符类（character class）就是预定义的字符集。

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

  • 9.2.7 匹配多个实例

    可以通过正则表达式重复元字符来完成。

    • * ：0 个或多个匹配
    • + ：1 个或多个匹配（等于{1,}）
    • ? ：0 个或 1 个匹配（等于{0,1}）
    • {n} ：指定数目的匹配
    • {n,} ：不少于指定数目的匹配
    • {n,m} ：匹配数目的范围（m 不超过 255）
    sql
    select prod_name
from products
where prod_name regexp '\\([0-9] sticks?\\)'
order by prod_name;
    sql
    select prod_name
from products
where prod_name regexp '[[:digit:]]{4}'
order by prod_name;z

  • 9.2.8 定位符

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

    • ^ ：文本的开始
    • $ ：文本的结尾
    • [[:<:]] ：词的开始
    • [[:>:]] ：词的结尾
    sql
    select prod_name
from products
where prod_name regexp '^[0-9\\.]'
order by prod_name;

regexp 表达式总是返回 0（没有匹配）或者 1（匹配）。
可以通过这一点来验证表达式。

sql
select 'hello' regexp '[0-9]';

第 10 章 创建计算字段 ​

10.1 计算字段 ​

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

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

10.2 拼接字段 ​

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

sql
select concat(vend_name, ' (', vend_country, ')')
from vendors
order by vend_name;

使用 rtrim() 函数来去除多余的空格：

sql
select concat(rtrim(vend_name), ' (', rtrim(vend_country), ')')
from vendors
order by vend_name;

使用 as 关键字赋予别名（alias）

sql
select concat(rtrim(vend_name), ' (', rtrim(vend_country), ')') as vend_title
from vendors
order by vend_name;

10.3 执行算术计算 ​

sql
select
    prod_id,
    quantity,
    item_price,
    quantity * item_price as expanded_price
from
    orderitems
where
    order_num = 20005

MySQL 算术操作符：

  • + : 加
  • - : 减
  • * : 乘
  • / : 除

第 11 章 使用数据处理函数 ​

11.1 函数 ​

SQL 支持利用函数来处理数据。
函数没有 SQL 的可移植性强。

11.2 使用函数 ​

11.2.1 文本处理函数 ​

sql
select
    vend_name,
    upper( vend_name ) as vend_name_upcase
from
    vendors
order by
    vend_name;

常用的文本处理函数：

函数说明
left()返回串左边的字符
length()返回串的长度
locate()找出串的一个子串
lower()将串转换为小写
ltrim()去掉串左边的空格
right()返回串右边的字符
rtrim()去掉串右边的空格
soundex()返回串的 soundex 值
substring()返回子串的字符
upper()将串转换为大写

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

sql
select
    cust_name,
    cust_contact
from
    customers
where
    soundex(cust_contact) = soundex('Y Lie');
cust_namecust_contact
Coyote Inc.Y Lee

11.2.2 日期和时间处理函数 ​

函数说明
adddate()增加一个日期（天、周等）
addtime()增加一个时间（时、分等）
curdate()返回当前日期
curtime()返回当前时间
date()返回日期时间的日期部分
datediff()计算两个日期之差
date_add()高度灵活的日期运算函数
date_format()返回一个格式化的日期或时间串
day()返回一个日期的天数部分
dayofweek()对于一个日期，返回对应的星期几
hour()返回一个时间的小时部分
minute()返回一个时间的分钟部分
month()返回一个日期的月份部分
now()返回当前日期和时间
second()返回一个时间的秒部分
time()返回一个日期时间的时间部分
year()返回一个日期的年份部分

日期必须为 yyyy-MM-dd 格式

sql
select cust_id, order_num
from orders
where order_date = '2005-09-01';

如果仅比较日期部分，更可靠的写法是：

sql
select cust_id, order_num
from orders
where date(order_date) = '2005-09-01';

比较日期区间：

sql
select cust_id, order_num
from orders
where date(order_date) between '2005-09-01' and '2005-09-30';

比较年份和月份：

sql
select cust_id, order_num
from orders
where year(order_date) = 2005 and month(order_date) = 9;

11.2.3 数值处理函数 ​

数值处理函数一般没有文本和日期函数使用的那么频繁，但却是所有 DBMS 中最一致的。

函数说明
abs()返回一个数的绝对值
cos()返回一个角度的余弦
exp()返回一个数的指数值
mod()返回除操作的余数
pi()返回圆周率
rand()返回一个随机数
sin()返回一个角度的正弦
sqrt()返回一个数的平方根
tan()返回一个角度的正切

第 12 章 汇总数据 ​

12.1 聚集函数 ​

聚集函数（aggregate function）：运行在行组上，计算和返回单个数的函数。

  • 12.1.1 avg()函数

    avg() 函数忽略列值为 null 的行。

    sql
    select avg(prod_price) as avg_price
from products;
    sql
    select avg(prod_price) as avg_price
from products
where vend_id = 1003;

  • 12.1.2 count()函数

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

    1. count(*) 统计行数，不管表列中包含的是空值（null）还是非空值
    2. count(column) 对特定列具有值的行进行统计，忽略 null 值
    sql
    select count(*) num_cust
from customers;
    sql
    select count(cust_email) num_cust
from customers;

  • 12.1.3 max()函数

    返回指定列中的最大值。

    sql
    select max(prod_price) max_price
from products;

  • 12.1.4 min()函数

    返回指定列的最小值。

    sql
    select min(prod_price) min_price
from products;

  • 12.1.5 sum()函数

    返回指定列值的和。

    sql
    select sum(quantity) items_ordered
from orderitems
where order_num = 20005;

    sum() 也可以用来合计计算值。

    sql
    select sum(quantity) items_ordered
from orderitems
where order_num = 20005;

12.2 聚集不同值 ​

sql
select avg(distinct prod_price) as avg_price
from products
where vend_id = 1003;

12.3 组合聚集函数 ​

sql
select
    count(*),
    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 创建分组 ​

sql
select vend_id, count(*) as num_prods
from products
group by vend_id;
  • group by 子句可以包含任意数目的列。
  • 如果在 group by 子句中嵌套了分组，数据将在最后规定的分组上进行汇总。
  • group by 子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。
    如果在 select 中使用表达式，则必须在 group by 子句中指定相同的表达式。
    不能使用别名。（JiaJia：可以使用别名）。
  • 除聚集计算语句外，select 语句中的每个列都必须在 group by 子句中给出。
    关于这一点现在的版本中有些情况下已经不再强制了。如果这个非聚集的列可能对应多个行的值，现在貌似返回的是第一个匹配行的值。
  • 如果分组列中具有 null 值，则 null 将作为一个分组返回。
  • group by 子句必须出现在 where 子句之后，order by 子句之前。

使用 with rollup 关键字，可以额外得到每个分组汇总级别的值。

sql
select vend_id, count(*) as num_prods
from products
group by vend_id with rollup;

13.3 过滤分组 ​

where 过滤行，having 过滤分组。
having 支持所有 where 操作符。

sql
select cust_id, count(*) as orders
from orders
group by cust_id
having count(*) >= 2;

where 在数据分组前进行过滤，having 在数据分组后进行过滤。

having 子句支持别名。

sql
select cust_id, count(*) as orders
from orders
group by cust_id
having orders >= 2;

13.4 分组和排序 ​

一般在使用 group by 子句时，应该也给出 order by 子句。这是保证数据正确排序的唯一方法。

sql
select order_num, sum(quantity * item_price) as order_total
from orderitems
group by order_num
having order_total >= 50
order by order_total;

13.5 SELECT 子句顺序 ​

No.子句说明是否必须使用
1.select要返回的列或表达式是
2.from从中检索数据的表仅在从表选择数据时使用
3.where行级过滤否
4.group by分组说明仅在按组计算聚集时使用
5.having组级过滤否
6.order by输出排序顺序否
7.limit要检索的行数否

第 14 章 使用子查询 ​

14.1 子查询 ​

查询（query）：任何 SQL 语句都是查询。但此术语一般指 select 语句。

子查询（subsquery）：即嵌套在其它查询中的查询。

14.2 利用子查询进行过滤 ​

sql
select
    cust_id
from
    orders
where
    order_num in (
        select
            order_num
        from
            orderitems
        where
            prod_id = 'TNT2'
    );

在 select 语句中，子查询总是从内向外处理。
对于能嵌套的子查询的数量没有限制。
子查询并不总是执行这种类型的数据检索的最有效方法。

14.3 作为计算字段使用 ​

sql
select
    cust_name,
    cust_state,
    (
        select
            count(*)
        from
            orders
        where
            orders.cust_id = customers.cust_id
    ) as orders
from
    customers
order by
    cust_name;

该子查询对检索出的每条数据执行一次。

这种类型的子查询称为 相关子查询（correlated subquery）。

第 15 章 联结表 ​

15.1 联结 ​

  • 15.1.1 关系表

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

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

  • 15.1.2 为什么要使用联结

    联结是一种机制，用来在一条 select 语句中关联表，因此称之为联结。
    联结有 MySQL 根据需要建立，它存在于查询的执行当中。

15.2 创建联结 ​

sql
select vend_name, prod_name, prod_price
from vendors, products
where vendors.vend_id = products.vend_id
order by vend_name, prod_name;

完全限定列名：在引用的列可能出现二义性时，必须使用完全限定列名（用一个点分隔的表名和列名）。

  • 15.2.1 WHERE 子句的重要性

    在联结时没有 where 子句，将会时表 1 中的每个行和表 2 中的每个行进行配对，而不管它们逻辑上是否可以配在一起。

    笛卡尔积（cartesian product） 由没有联结条件的表关系返回的结果为笛卡尔积。检索出的行数为表 1 的行数乘以表 2 的行数。

    sql
    select vend_name, prod_name, prod_price
from vendors, products
order by vend_name, prod_name;

  • 15.2.2 内部联结

    sql
    select vend_name, prod_name, prod_price
from vendors inner join products
    on vendors.vend_id = products.vend_id
order by vend_name, prod_name;

  • 15.2.3 联结多个表

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

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

第 16 章 创建高级联结 ​

16.1 使用表别名 ​

使用表别名可以：

  • 缩短 SQL 语句；
  • 允许在单条 select 语句中多次使用相同的表。
sql
select cust_name, cust_contact
from customers as c, orders as o, orderitems as oi
where c.cust_id = o.cust_id
    and oi.order_num = o.order_num
    and prod_id = 'TNT2';

16.2 使用不同类型的联结 ​

  • 16.2.1 自联结

    sql
    select p1.prod_id, p1.prod_name
from products p1, products p2
where p1.vend_id = p2.vend_id
and p2.prod_id = 'DTNTR';

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

  • 16.2.2 自然联结

    标准联结返回所有数，甚至相同的列多次出现。
    自然联结排除多次出现，使每个列只返回一次。但是系统不完成这项工作，由你自己完成它。

    sql
    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 外部联结

    外部联结包含了那些在相关表中没有关联行的行。

    左外联结：

    sql
    select customers.cust_id, orders.order_num
from customers left outer join orders
    on customers.cust_id = orders.cust_id;

    右外联结：

    sql
    select customers.cust_id, orders.order_num
from customers right outer join orders
    on customers.cust_id = orders.cust_id;

    外联结中的 outer 关键字可以省略。

16.3 使用带聚集函数的联结 ​

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

16.4 使用联结和联结条件 ​

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

第 17 章 组合查询 ​

17.1 组合查询 ​

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

17.2 创建组合查询 ​

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

  • 17.2.1 使用 UNION

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

  • 17.2.2 UNION 规则

    • union 必须由两条或两条以上的 select 语句组成，语句之间用关键字 union 分隔。
    • union 中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序给出）。
      JiaJia：这边的说法有点容易让人误解。union 的结果并不是根据字段名来进行合并的，而是根据 select 中各字段出现的顺序决定的。
    • 列数据类型必须兼容：类型不必完全相同，但必须是 DBMS 可以隐含地转换的类型。

  • 17.2.3 包含或取消重复的行

    union 从查询结果中自动去除了重复的行。
    如果想返回所有匹配的行，可使用 union all 关键字。

  • 17.2.4 对组合查询结果排序

    在用 union 组合查询时，只能使用一条 order by 子句，它必须出现在最后一条 select 语句之后。

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

第 18 章 全文本搜索 ​

18.1 理解全文本搜索 ​

两个最常使用的引擎为 MyISAM 和 InnoDB，前者支持全文本搜索，后者不支持。

like 和 regexp 存在的问题：

  • 性能
    通配符和正则表达式匹配通常要求 MySQL 尝试匹配表中所有行（而且这些搜索极少使用索引）。
  • 明确控制
    使用通配符和正则表达式匹配，很难明确地控制匹配什么和不匹配什么。
  • 智能化的结果
    虽然基于通配符和正则表达式的搜索提供了非常灵活的搜索，但它们都不能提供一种智能化的选择结果方法。

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

18.2 使用全文本搜索 ​

为了进行全文本搜索，必须索引被搜索的列。

  • 18.2.1 启用全文本搜索支持

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

    sql
    create table productnotes
(
    note_id    int           not null auto_increment,
    prod_id    char(10)      not null,
    note_date  datetime      not null,
    note_text  text          null ,
    primary key(note_id),
    fulltext(note_text)
) engine=MyISAM;

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

    如果正在导入数据到一个新表，此时不应该启用 fulltext 索引。
    应该首先导入所有数据，然后再修改表，定义 fulltext 。
    这样有助于更快的导入数据。

  • 18.2.2 进行全文本搜索

    match() 指定被搜索的列，against() 指定要使用的搜索表达式。

    sql
    select note_text
from productnotes
where match(note_text) against('rabbit')

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

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

    全文本搜索返回以文本匹配的良好程度排序的数据。全文本搜索的一个重要部分就是对结果排序。

    sql
    select note_text,
       match(note_text) against('rabbit') as ranks
from productnotes;

    等级由 MySQL 根据行中词的数目、唯一词的数目、整个索引中词的总数以及包含该词的行的数目计算出来。

  • 18.2.3 使用查询扩展

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

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

    • 首先，进行一次基本的全文本搜索，找出与搜索条件匹配的所有行；
    • 其次，MySQL 检查这些匹配行并选择所有有用的词；
    • 再次其，MySQL 再次进行全文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词。
    sql
    select note_text
from productnotes
where match(note_text) against('rabbit' with query expansion);

  • 18.2.4 布尔文本搜索

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

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

    即使没有 fulltext 索引也可以使用布尔方式搜索，但这是一种非常缓慢的操作。

    sql
    select note_text
from productnotes
where match(note_text) against('rabbit' in boolean mode);

    排除包含 rope* （任何以 rope 开头的单次）的行：

    sql
    select note_text
from productnotes
where match(note_text) against('heavy -rope*' in boolean mode);

    全文本布尔操作符：

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

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

  • 18.2.5 全文本搜索的使用说明

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

第 19 章 插入数据 ​

19.1 数据插入 ​

insert 用来插入（或添加）行到数据库表的。

插入可以用几种方式使用：

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

19.2 插入完整的行 ​

sql
insert into `customers`
values (
    null,
    'Coyote Inc.',
    '200 Maple Lane',
    'Detroit',
    'MI',
    '44444',
    'USA',
    'Y Lee',
    'ylee@coyote.com'
);

这种语法比较简单，但由于其高度依赖于字段定义的顺序，不推荐使用。

sql
insert into `customers`
(
    `cust_id`,
    `cust_name`,
    `cust_address`,
    `cust_city`,
    `cust_state`,
    `cust_zip`,
    `cust_country`,
    `cust_contact`,
    `cust_email`
)
values
(
    null,
    'Coyote Inc.',
    '200 Maple Lane',
    'Detroit',
    'MI',
    '44444',
    'USA',
    'Y Lee',
    'ylee@coyote.com'
);

一般不要使用没有明确给出列的列名的 insert 语句。

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

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

insert 操作可能很耗时（特别是有很多索引需要更新时），而且它可能降低等待处理 select 语句的性能。

如果数据检索是最重要的（通常是这样），则你可以通过在 insert 和 into 之间添加关键字 low_priority ，指示 MySQL 降低 insert 语句的优先级，如下所示：

sql
insert low_priority into

low_priority 同样适用于 update 和 delete 语句。

19.3 插入多个行 ​

sql
insert into
    `customers`(
        `cust_id`,
        `cust_name`,
        `cust_address`,
        `cust_city`,
        `cust_state`,
        `cust_zip`,
        `cust_country`,
        `cust_contact`,
        `cust_email`
    )
values
    (
        10001,
        'Coyote Inc.',
        '200 Maple Lane',
        'Detroit',
        'MI',
        '44444',
        'USA',
        'Y Lee',
        'ylee@coyote.com'
    );

insert into
    `customers`(
        `cust_id`,
        `cust_name`,
        `cust_address`,
        `cust_city`,
        `cust_state`,
        `cust_zip`,
        `cust_country`,
        `cust_contact`,
        `cust_email`
    )
values
    (
        10002,
        'Mouse House',
        '333 Fromage Lane',
        'Columbus',
        'OH',
        '43333',
        'USA',
        'Jerry Mouse',
        null
    );

或者合并成如下形式：

sql
insert into
    `customers`(
        `cust_id`,
        `cust_name`,
        `cust_address`,
        `cust_city`,
        `cust_state`,
        `cust_zip`,
        `cust_country`,
        `cust_contact`,
        `cust_email`
    )
values
    (
        10001,
        'Coyote Inc.',
        '200 Maple Lane',
        'Detroit',
        'MI',
        '44444',
        'USA',
        'Y Lee',
        'ylee@coyote.com'
    ),
    (
        10002,
        'Mouse House',
        '333 Fromage Lane',
        'Columbus',
        'OH',
        '43333',
        'USA',
        'Jerry Mouse',
        null
    );

19.4 插入检索出的数据 ​

sql
insert into
    `customers`(
        `cust_id`,
        `cust_name`,
        `cust_address`,
        `cust_city`,
        `cust_state`,
        `cust_zip`,
        `cust_country`,
        `cust_contact`,
        `cust_email`
    )
select
    `cust_id`,
    `cust_name`,
    `cust_address`,
    `cust_city`,
    `cust_state`,
    `cust_zip`,
    `cust_country`,
    `cust_contact`,
    `cust_email`
from
    custnew;

第 20 章 更新和删除数据 ​

20.1 更新数据 ​

使用 update 语句更新数据。

可采用两种方式使用 update ：

  • 更新表中特定行；
  • 更新表中所有行。
sql
update customers
set cust_name = 'The Fudds',
    cust_email = 'elmer@fudd.com'
where cust_id = 10005;

如果用 update 更新多行，并且在更新这些行中的一行或多行时出现一个错误，则整个 update 语句被取消。
即使发生错误时，也继续进行更新时，可使用 ignore 关键字。

sql
update ignore customers ...

为了删除某个列的值，可以将它设置为 null 。

sql
update customers
set cust_email = null
where cust_id = 10005;

20.2 删除数据 ​

使用 delete 语句删除数据。

可以用两种方式使用 delete：

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

不要省略 where 子句。

sql
delete from customers
where cust_id = 10005;

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

20.3 更新和删除指导原则 ​

  • 除非确实打算更新和删除每一行，否则绝对不要使用不带 where 子句的 update 和 delete 语句。
  • 保证每个表都有主键，尽可能像 where 子句那样使用它。
  • 在对 update 或 delete 语句使用 where 子句前，应该先用 select 进行测试，保证它过滤的是正确的记录，以防编写的 where 子句不正确。
  • 使用强制实施引用完整性的数据库，这样 MySQL 将不允许删除具有与其它表相关联的数据的行。

第 21 章 创建和操纵表 ​

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

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

21.1 创建表 ​

  • 21.1.1 表创建基础

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

    如果仅想在一个表不存在的时候创建它，应该在表名后给出 if not exists 。

  • 21.1.2 使用 NULL 值

    每个表列或者是 null 列，或者是 not null 列，这种状态在创建时由表的定义规定。
    null 值是没有值，它不是空串。

  • 21.1.3 主键再介绍

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

    sql
    PRIMARY KEY (cust_id)

    主键中只能使用不允许 null 值的列。

  • 21.1.4 使用 AUTO_INCREMENT

    AUTO_INCREMENT 告诉 MySQL，本列每当增加一行时自动增量。

    每个表只允许一个 AUTO_INCREMENT 列，而且它必须被索引。

    即使一个列是自增的，也可以在 insert 时指定一个值（只要这个值还未使用过），而且如果这个指定值比之前的自增值要大，那么后续的增量将从这个值开始增加。

    使用 last_insert_id() 获取最后插入的行的自动增量值。

    sql
    select last_insert_id();

  • 21.1.5 指定默认值

    如果在插入时没有给出值，MySQL 允许指定此时使用的默认值。
    默认值用 create table 语句的列定义中的 default 关键字指定。

    sql
    quantity int NOT NULL DEFAULT 1,

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

  • 21.1.6 引擎类型

    MySQL 与其它 DBMS 不一样，它具有多种引擎。
    它们具有各自不同的功能和特性，为不同的任务选择正确的引擎能获得良好的功能和灵活性。
    如果省略 engine= 语句，则使用默认引擎（很可能是 MyISAM），多数 SQL 语句都会默认使用它，但并不是所有的语句都默认使用它。

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

    引擎类型可以混用。不同的表可以使用不同的引擎。

    外键不能跨引擎。使用一个引擎的表不能引用具有使用不同引擎的表的外键。

21.2 更新表 ​

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

添加列：

sql
alter table vendors
add vend_phone char(20);

删除刚刚添加的列：

sql
alter table vendors
drop column vend_phone;

alter table 的一种常见用途是定义外键。

sql
alter table orderitems
add constraint fk_orderitems_orders
foreign key (order_num) references orders (order_num);

复杂的表结构更改可以参考以下步骤：

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

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

21.3 删除表 ​

sql
drop table cutomers2;

21.4 重命名表 ​

sql
rename table customers2 to customers;

一次可以重命名多张表：

sql
rename table backup_customers to customers,
             backup_vendors to vendors,
             backup_products to products;

第 22 章 使用视图 ​

视图是虚拟的表。
与包含数据的表不一样，视图只包含使用时动态检索数据的查询。
作为视图，它不包含表中应该有的任何列或数据，它包含的是一个 SQL 查询。

22.1 视图 ​

  • 22.1.1 为什么使用视图

    • 重用 SQL 语句
    • 简化复杂的 SQL 操作
    • 使用表的组成部分而不是整个表
    • 保护数据
    • 更改数据格式和表示

  • 22.1.2 视图的规则和限制

    • 与表一样，视图必须唯一命名。
    • 对于可以创建的视图数目没有限制。
    • 为了创建视图，必须具有足够的访问权限。
    • 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图。
    • order by 可以用在视图中，但如果从该视图检索数据的 select 语句中也包含 order by，那么该视图中的 order by 将被覆盖。
    • 视图不能索引，也不能有关联的触发器或默认值。
    • 视图可以和表一起使用。

22.2 使用视图 ​

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

    sql
    create view product_customers as
select cust_name, cust_contact, prod_id
from customers, orders, orderitems
where customers.cust_id = orders.cust_id
  and orderitems.order_num = orders.order_num;
    sql
    select cust_name, cust_contact
from product_customers
where prod_id = 'TNT2';

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

    sql
    create view vendor_locations as
select concat(rtrim(vend_name), ' (', rtrim(vend_country), ')') as vend_title
from vendors
order by vend_name;
    sql
    select *
from vendor_locations;

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

    sql
    create view customer_email_list as
select cust_id, cust_name, cust_email
from customers
where cust_email is not null;
    sql
    select *
from customer_email_list;

  • 22.2.4 使用视图与计算字段

    sql
    create view orderitems_expaned as
select
    order_num, prod_id, quantity, item_price,
    quantity * item_price as expanded_price
from orderitems;
    sql
    select *
from orderitems_expaned
where order_num = 20005;

  • 22.2.5 更新视图

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

    • 分组（使用 group by 和 having）；
    • 联结；
    • 子查询；
    • 并；
    • 聚集函数（min()、count()、sum()等）；
    • distinct；
    • 导出（计算）列。

第 23 章 使用存储过程 ​

23.1 存储过程 ​

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

23.2 什么要使用存储程 ​

优点：

  • 通过把处理封装在容易使用的单元中，简化复杂的操作。
  • 由于不要求反复建立一系列处理步骤，这保证了数据的完整性。
  • 简化对变动的管理。
  • 提高性能。
  • 存在一些只能用在单个请求中的 MySQL 元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。

缺点：

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

23.3 使用存储程 ​

  • 23.3.1 执行存储过程

    sql
    call productpricing(@pricelow, @pricehigh, @priceaverage);

  • 23.3.2 创建存储过程

    sql
    create procedure productpricing()
begin
    select avg(prod_price) as priceaverage
    from products;
end;

    通过 MySQL 命令行客户端创建存储过程时，需要临时修改默认的分隔符。

    sql
    delimiter //

create procedure productpricing()
begin
    select avg(prod_price) as priceaverage
    from products;
end //

delimiter ;

  • 23.3.3 删除存储过程

    sql
    drop procedure productpricing;

  • 23.3.4 使用参数

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

    sql
    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 和 inout 类型的参数。

    调用存储过程：

    sql
    call productpricing(@pricelow, @pricehigh, @priceaverage);

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

    显示存储过程返回的值：

    sql
    select @pricelow, @pricehigh, @priceaverage;

    另一个包含 in 和 out 参数的例子：

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

    执行存储过程和显示返回的结果：

    sql
    call ordertotal(20005, @total);
select @total;

  • 23.3.5 建立智能存储过程

    sql
    create procedure ordertotal(
    in onumber int,
    in taxable boolean,
    out ototal decimal(8,2)
) comment 'Obtain order total, optionally adding tax'
begin
    -- Declare variable for total
    declare total decimal(8,2);
    -- Declare tax percentage
    declare taxrate int default 6;

    -- Get the order total
    select sum(item_price * quantity)
    from orderitems
    where order_num = onumber
    into total;

    -- Is this taxable?
    if taxable then
        -- yeas, so add taxrate to the total
        select total + (total / 100 * taxrate)
        into total;
    end if;

    -- And finally, save to out variable
    select total into ototal;
end;

    上文中 comment 后的内容可以通过如下语句看到：

    sql
    show procedure status like 'ordertotal';

    通过传递不同的参数执行并查看结果：

    sql
    call ordertotal(20005, 0, @total);
select @total;

call ordertotal(20005, 1, @total);
select @total;

  • 23.3.6 检查存储过程

    sql
    show create procedure ordertotal;

第 24 章 使用游标 ​

24.1 游标 ​

游标（cursor）是一个存储在 MySQL 服务器上的数据库查询，它不是一条 SELECT 语句，而是被该语句检索出来的结果集。

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

24.2 使用游标 ​

使用步骤：

  • 使用游标，必须声明（定义）它。
    这个过程实际上没有检索数据，它只是定义要使用的 SELECT 语句。

  • 一旦声明后，必须打开游标以供使用。
    这个过程用前面定义的 SELECT 语句把数据实际检索出来。

  • 对于填有数据的游标，根据需要取出（检索）各行。

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

  • 24.2.1 创建游标

    sql
    create procedure process_orders()
begin
    declare order_numbers cursor
    for
    select order_num from orders;
end;

  • 24.2.2 打开和关闭游标

    sql
    open order_numbers;
    sql
    close order_numbers;

  • 24.2.3 使用游标数据

    sql
    create procedure process_orders()
begin
    -- Declare local variables
    declare o int;

    -- Declare the cursor
    declare order_numbers cursor
    for
    select order_num from orders;

    -- Open the cursor
    open order_numbers;

    -- Get order number
    fetch order_numbers into o;
    select o;

    -- Close the cursor
    close order_numbers;
end;

    fetch 用来检索当前行的 order_num 到一个名为 o 的局部变量中。检索出的数据不做任何处理。

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

    sql
    create procedure process_orders()
begin
    -- Declare local variables
    declare done boolean default 0;
    declare o int;

    -- Declare the cursor
    declare order_numbers cursor
    for
    select order_num from orders;
    -- Declare continue handler
    declare continue handler for sqlstate '02000' set done = 1;

    -- Open the cursor
    open order_numbers;

    -- Loop through all rows
    repeat

        -- Get order number
        fetch order_numbers into o;
        select o;

    -- End of loop
    until done end repeat;

    -- Close the cursor
    close order_numbers;
end;

    continue handler 用来定义在条件出现时执行的代码。
    上例中 sqlstate '02000' 是一个未找到条件，当 repeat 由于没有更多的行供循环而不能继续时，出现这个条件。

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

    一个修改后更为完善的存储过程示例：

    sql
    drop procedure process_orders;

create procedure process_orders()
begin
    -- Declare local variables
    declare done boolean default 0;
    declare o int;
    declare t decimal(8,2);

    -- Declare the cursor
    declare order_numbers cursor
    for
    select order_num from orders;
    -- Declare continue handler
    declare continue handler for sqlstate '02000' set done = 1;

    create table if not exists ordertotals
        (order_num int, total decimal(8,2));

    -- Open the cursor
    open order_numbers;

    -- Loop through all rows
    repeat

        -- Get order number
        fetch order_numbers into o;

        -- Get the total for this order
        call ordertotal(o, 1, t);

        insert into ordertotals(order_num, total)
        values(o, t);

    -- End of loop
    until done end repeat;

    -- Close the cursor
    close order_numbers;
end;

    执行存储过程：

    sql
    call process_orders();

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

    sql
    select *
from ordertotals;

第 25 章 使用触发器 ​

25.1 触发器 ​

触发器是 MySQL 响应以下任意语句而自动执行的一条 MySQL 语句：

  • delete
  • insert
  • update

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

25.2 创建触发器 ​

在 MySQL 5 中，触发器名必须在每个表中唯一，但不是在每个数据库中唯一。
但最好是在数据库范围内使用唯一的触发器名。

sql
create trigger new_product after insert on products
for each row select 'Product added';

JiaJia：上面是书中给出的示例，不过在运行时报了如下错误（MySQL 版本：5.7.30-0ubuntu0.16.04.1）：

1415 - Not allowed to return a result set from a trigger

可以改成在触发器中给变量赋值：

sql
create trigger new_order after insert on orders
for each row select new.order_num into @newOrderNum;

插入数据后再检查变量内容：

sql
INSERT INTO `orders`
	(`order_date`, `cust_id`)
VALUES
	('2022-02-06 00:00:00', 10001);

select @newOrderNum;

另外想在新增订单后，使用自增列的值来更新别的列：

sql
create trigger new_order after insert on orders
for each row set new.order_code = concat(new.order_num, new.cust_id);

但是运行报了如下错误：

1362 - Updating of NEW row is not allowed in after trigger

查了一下，这个功能貌似暂时无法通过触发器实现。
书中后面也有讲到，仅支持在插入之前 before insert 中更新新行。

25.3 删除触发器 ​

sql
drop trigger new_order;

25.4 使用触发器 ​

  • 25.4.1 INSERT 触发器

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

    • 在 insert 触发器代码内，可引用一个名为 new 的虚拟表，访问被插入的行；
    • 在 before insert 触发器中，new 中的值也可以被更新（允许更改被插入的值）；
    • 对于 auto_increment 列，new 在 insert 之前包含 0，在 insert 执行之后包含新的自动生成值。
    sql
    create trigger new_order after insert on orders
for each row select new.order_num into @newOrderNum;

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

  • 25.4.2 DELETE 触发器

    • 在 delete 触发器代码内，可以引用一个名为 old 的虚拟表，访问被删除的行。
    • old 中的值全都是只读的，不能更新。
    sql
    create trigger delete_order 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 的优点是，如果出于某种原因，订单不能存档，delete 本身将被放弃。

  • 25.4.3 UPDATE 触发器

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

    • 在 update 触发器中，可以引用一个名为 old 的虚拟表访问以前（UPDATE 语句前）的值，引用过一个名为 new 的虚拟表访问新更新的值；
    • 在 before update 触发器中，new 中的值可能也被更新（允许更改将要用于 UPDATE 语句中的值）；
    • old 中的值全都是只读的，不能更新。
    sql
    create trigger updatevendor before update on vendors
for each row set new.vend_state = upper(new.vend_state);

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

    • 与其它 DMBS 相比，MySQL 5 中支持的触发器相当初级。
    • 创建触发器可能需要特殊的安全访问权限，但是触发器的执行是自动的。
    • 应该用触发器来保证数据的一致性（大小写，格式等）。
    • 触发器的一种非常有意义的使用是创建审计跟踪。使用触发器，把更改记录到另一个表非常容易。
    • 遗憾的是，MySQL 触发器中不支持 call 语句。所需的存储过程代码需要复制到触发器内。

第 26 章 管理事处理 ​

26.1 事务处理 ​

并非所有引擎都支持事务处理。MyISAM 和 InnoDB 是两种最常使用的引擎。前者不支持明确的事务处理管理，而后者支持。

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

术语：

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

26.2 控制事务处理 ​

开始事务：

sql
start transaction

  • 26.2.1 使用 ROLLBACK

    sql
    select * from ordertotals;
start transaction;
delete from ordertotals;
select * from ordertotals;
rollback;
select * from ordertotals;

    rollback 只能在一个事务处理内使用。

    事务处理用来管理 insert、update、delete 语句。
    不能回退 select、create、drop 操作。

  • 26.2.2 使用 COMMIT

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

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

    sql
    start transaction;
delete from orderitems where order_num = 20010;
delete from orders where order_num = 20010;
commit;

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

  • 26.2.3 使用保留点

    更复杂的事务处理可能需要部分提交或回退。为了支持部分回退，必须能在事务处理块中合适的位置放置占位符。这些占位符称为保留点。

    sql
    savepoint delete1;

    每个保留点都取标识它的唯一的名字，以便在回退时，MySQL 知道要回退到何处。

    sql
    rollback to delete1;

    保留点越多越好。 保留点越多，意味着可以更加灵活的回退。

    保留点在事务处理完成（执行一条 rollback 或 commit）后自动释放。也可以用 release savepoint 明确地释放保留点。

  • 26.2.4 更改默认的提交行为

    默认的 MySQL 行为是自动提交所有更改。为指示 MySQL 不自动提交更改，需要使用以下语句：

    sql
    set autocommit = 0;

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

第 27 章 全球化和本地化 ​

27.1 字符集和校对顺序 ​

相关术语：

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

27.2 使用字符集和校对顺序 ​

查看所支持的字符集完整列表：

sql
show character set;

查看所支持校对的完整列表：

sql
show collation;

通常 _cs 后缀的校对表示区分大小写；_ci 后缀表示不区分大小写。

通常系统管理在安装时定义一个默认的字符集和校对。此外，也可以在创建数据库时，指定默认的字符集和校对。

确定所用的字符集和校对：

sql
show variables like 'character%';
show variables like 'collation%';

create talbe 时可以指定字符集和校对。

sql
create table mytable
(
    columnn1 int,
    columnn2 varchar(10)
) default character set hebrew
  collate hebrew_general_ci;

MySQL 还允许对每个列设置字符集和校对：

sql
create table mytable
(
    columnn1 int,
    columnn2 varchar(10),
    columnn3 varchar(10) character set latin1 collate latin1_general_ci
) default character set hebrew 
  collate hebrew_general_ci;

select 时也可以指定与表定义中不同的校对顺序：

sql
select * from customers
order by lastname, firstname collate latin1_general_cs;

另外 collate 还可以用于 group by、having、聚集函数、别名等。

第 28 章 安全管理 ​

28.1 访问控制 ​

MySQL 服务器的安全基础是：用户应该对他们需要的数据具有适当的访问权，既不能多也不能少。

MySQL Administrator 提供了一个图形用户界面，可用来管理用户及账号权限。

MySQL 默认创建了一个 root 的用户账号，它对整个 MySQL 服务器具有完全的控制。
在现实世界的日常工作中，绝不能使用 root。应该创建一系列的账号，有的用于管理，有的供用户使用，有的供开发人员使用，等等。

访问控制的目的不仅仅是防止用户的恶意企图。数据梦魇更为常见的是无意识错误的结果。

28.2 管理用户 ​

MySQL 用户账号和信息存储在名为 mysql 的数据库中。
一般不需要直接访问 mysql 数据库和表。

获取所有用户账号列表：

sql
use mysql;
select user from user;

  • 28.2.1 创建用户账号

    sql
    create user ben identified by 'p@$$w0rd';

    创建用户时不一定需要口令。例子中通过 identified by 设置了口令。命令中口令为明文，存储到 user 表中的是加密后的数据。

    重命名账号：

    sql
    rename user ben to jiajia;

  • 28.2.2 删除用户账号

    sql
    drop user jiajia;

  • 28.2.3 设置访问权限

    查看账户权限：

    sql
    show grants for jiajia;

    授予账户权限（这里是授予查询权限）：

    sql
    grant select on crash.* to jiajia;

    grant 的反操作为 revoke ，用它来撤销特定的权限。

    sql
    revoke select on crash.* from jiajia;

    grant 和 revoke 可在几个层次上控制访问权限：

    • 整个服务器 grant all revoke all
    • 整个数据库 on database.*
    • 特定的表 on database.table
    • 特定的列
    • 特定的存储过程

  • 28.2.4 更改口令

    sql
    set password for jiajia = password('n3w p@$$w9rd');

    新口令必须传递到 password 函数进行加密。

    不指定用户名时，更新当前登录用户的口令：

    sql
    set password = password('n3w p@$$w9rd');

第 29 章 数据库维护 ​

29.1 备份数据 ​

  • 使用命令行使用程序 mysqldump 转储所有数据库内容到某个外部文件。
  • 可用命令行使用程序 mysqlhotcopy 从一个数据库复制所有数据。
  • 可以使用 MySQL 的 backup table 或 select into outfile 转储所有数据到某个外部文件。
    数据可以用 restore table 来复原。

为了保证所有数据被写到磁盘（包括索引数据），可能需要在进行备份前使用 flush tables 语句。

29.2 进行数据库维护 ​

  • analyze table 用来检查表键是否正确。
  • check tabble 用来针对许多问题对表进行检查。
  • 如果 MyISAM 表访问产生不正确和不一致的结果，可能需要 repair table 来修复相应的表。
  • 如果从一个表中删除大量数据，应该使用 optimize table 来收回所用的空间，从而优化表的性能。

29.3 诊断启动问题 ​

在排除系统启动问题时，首先应该尽量用手动启动服务器。

以下是几个重要的 mysqld 命令行选项：

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

29.4 查看日志文件 ​

MySQL 主要有以下日志：

  • 错误日志。
    它包含启动和关闭问题以及任意关键错误的细节。
    此日志通常名为 hostname.err，位于 data 目录中。
    此日志名可用 --log-error 命令行选项更改。
  • 查询日志。
    它记录所有 MySQL 活动，在诊断问题时非常有用。
    此日志文件可能会很快地变得非常大，因此不应该长期使用它。
    此日志通常名为 hostname.log，位于 data 目录中。
    此名字可以用 --log 命令行选项更改。
  • 二进制日志。
    它记录更新过数据的所有语句。
    此日志通常名为 hostname-bin，位于 data 目录中。
    此日志可以用 --log-bin 命令行选项更改。
    注意，这个日志文件是 MySQL 5 中添加的，此前的 MySQL 版本中使用的是更新日志。
  • 缓慢查询日志。
    此日志记录执行缓慢的任何查询。
    此日志通常名为 hostname-slow.log，位于 data 目录中。
    此名字可以用 --log-slow-queries 命令行选项更改。

第 30 章 改善性能 ​

30.1 改善性能 ​

在诊断应用的滞缓现象和性能问题时，性能不良的数据库（以及数据库查询）通常是最常见的祸因。

  • 首先，MySQL（与所有 DBMS 一样）具有特定的硬件建议。
  • 一般来说，关键的生产 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 。
  • 数据库是不断变化的实体。一组优化良好的表可能很快就面目全非了。由于表的使用和内容的更改，理想的优化和配置也会改变。
  • 最重要的规则就是，每条规则在某些条件下都会被打破。

附录 C MySQL 语句的语法 ​

  • ALTER TABLE

    sql
    alter table tablename
(
    add    column         datatype [null|not null] [constraints],
    change column columns datatype [null|not null] [constraints],
    drop   column,
    ...
);

  • COMMIT

    sql
    commit;

  • CREATE INDEX

    sql
    create index indexname
on tablename (column [asc|desc], ...);

  • CREATE PROCEDURE

    sql
    create procedure procedurename([parameters])
begin
...
end;

  • CREATE TABLE

    sql
    create table tablename
(
    column datatype [null|not null] [constraints],
    column datatype [null|not null] [constraints],
    ...
);

  • CREATE USER

    sql
    create user username[@hostname]
[identified by [password] 'password'];

  • CREATE VIEW

    sql
    create [or replace] view viewname
as
select ...;

  • DELETE

    sql
    delete from tablename
[where ...];

  • DROP

    sql
    drop database|index|procedure|table|trigger|user|view
    itemname;

  • INSERT

    sql
    insert into tablename [(columns, ...)]
values (values, ...);

  • INSERT SELECT

    sql
    insert into tablename [(columns, ...)]
select columns, .... from tablename, ...
[where ...]

  • ROLLBACK

    sql
    rollback [to savepointname];

  • SAVEPOINT

    sql
    savepoint sp1;

  • SELECT

    sql
    select columnname, ...
from tablename, ...
[where ...]
[union ...]
[group by ...]
[having ...]
[order by ...];

  • START TRANSACTION

    sql
    start transaction;

  • UPDATE

    sql
    update tablename
set columnname = value, ...
[where ...];

附录 D MySQL 数据类型 ​

书中列出的类型限制是对应当时的 MySQL 5 版本的，和最新的 MySQL 5 中的已经不太一致了。这里仅供参考，还是要以对应版本的官方文档为准。如 5.7 版本的可以参考这里：MySQL 5.7 Reference Manual / Data Types

D.1 串数据类型 ​

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

D.2 数值数据类型 ​

所有数值类型数据（除 BIT 和 BOOLEAN 之外），都可以有符号或无符号。默认为有符号。

数据类型说明
BIT位字段，1~64 位
BIGINT整数值
BOOLEAN（或 BOOL）布尔标识，或者为 0 或者为 1
DECIMAL（或 DEC）精度可变的浮点数
DOUBLE双精度浮点数
FLOAT单精度浮点数
INT（或 INTEGER）整数值
MEDIUMINT整数值
REAL4 字节的浮点数
SMALLINT整数值 -32768~32767
TINYINT整数值 -128~127

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

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

D.4 二进制数据类型 ​

数据类型说明
BLOBblob 最大长度为 64KB
MEDIUMBLODblob 最大长度为 16MB
LONGBLOBblob 最大长度为 4GB
TINYBLODblob 最大长度为 255 字节