Mysql 必知必会

术语

不同的人可能会使用相同的数据库术语表示不同的事物,会造成一些混乱,下面是一张重要的数据库术语清单。

数据库(database)

数据库是保存有组织的数据的容器(通常是一个文件或一组文件)。

易混点:人们经常用“数据库”这个词代表他们使用的数据库软件。数据库软件是 DBMS(数据库管理系统),例如 MySQL 就是一种 DBMS ,而数据库是通过 DBMS 创建和操纵的容器。我们通常不直接访问数据库,而是通过使用 DBMS 来访问数据库。

表(table)

表是某种特定类型数据的结构化清单。

数据库中的每个表都有一个名字,用来标识自己,称之为“表名”。此名字是唯一的,在相同的数据库中不能使用重复的表名,但是在不同的数据库中可以使用。

模式(schema)

模式是关于数据库和表的布局及特性的信息。

列(column)

列是表中的一个字段。所有的表都是由一个或多个列组成的。

数据类型(datatype)

数据类型是所容许的数据的类型。每个表列都有相应的数据类型,它限制(或容许)该列中存储的数据类型。

行(row)

行是表中的一个记录。

有人会把行(row)称之为数据库记录(record),这两个数据是可以互相代替的,但是从技术上说,行才是正确的术语。

主键(primary key)

主键是一列(或一组列),其值能够唯一区分表中每个行。

表中的任何列只要满足以下条件,都可以作为主键:

  • 任意两行都不具有相同的主键值;

  • 每个行都必须具有一个主键值(主键列不允许 NULL 值)

此外还有几个主键的最佳实践:

  • 不更新主键列中的值;

  • 不重用主键列的值;

  • 不在主键列中使用可能会更改的值。(例如,如果使用一个名字作为主键以标识某个供应商,当该供应商合并和更改其名字时,就得必须更改这个主键。)

外键(foreign key)

外键为某个表中的一列,它包含另一个表的主键值,定义了两个表之间的关系。

子句(clause)

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

操作符(operator)

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

通配符(wildcard)

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

搜索模式(search pattern)

由字面量、通配符或两者组合构成的搜索条件

拼接(concatenate)

将值联结到一起构成单个值

别名(alias)

是一个字段或值的替换名。别名用 AS 关键字赋予。别名有时也称为导出列(derived column),不管称为什么,它们所代表的都是相同的东西。

聚集函数(aggregate function)

运行在行组上,计算和返回单个值的函数。

查询(query)

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

相关子查询(correlated subquery)

涉及外部查询的子查询。

可伸缩性(scale)

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

变量(variable)

内存中一个特定的位置,用来临时存储数据。所有 MySQL 变量都必须以 @ 开始。

SQL(Structured Query Language)

SQL 是结构化查询语言(Structured Query Language)的缩写,是一种专门用来与数据库通信的语言。

SQL 的优点:

  • SQL 不是某个特定数据库供应商专有的语言。即 SQL 不是一种专利语言,而且存在一个标准委员会。几乎所有重要的 DBMS 都支持 SQL。

  • SQL 简单易学。它的语句全都是由描述性很强的英语单词组成,而且这些单词的书目不多。

  • SQL 尽管看上去很简单,但它实际上是一种强有力的语言,灵活使用其语言元素,可以进行非常复杂和高级的数据库操作。

MySQL 安装

推荐几个 MySQL 安装和连接的经验文章

MySQL 应用

mysql 命令行

  • 命令输入在 mysql> 之后;

  • 命令用 ; 或 \g 结束,换句话说,仅按 Enter 不执行命令;

  • 输入 help 或 \h 获得帮助,也可以输入更多的文本获得特定命令的帮助(如,输入 help select 获得试用 SELECT 语句的帮助);

  • 输入 quit 或 exit 退出命令行。

连接数据库

连接数据库需要以下信息:

  • 主机名(计算机名)——如果连接到本地 MySQL 服务器,为 localhost ;

  • 端口(如果使用默认端口 3306 之外的端口);

  • 一个合法的用户名;

  • 用户口令(如果需要)

例如下面的指令:

mysql -u root -h localhost -P 3306 -p

数据库的登录和成员管理

访问控制

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

需要给用户提供他们所需的访问权,且仅提供他们所需的访问权。这就是所谓的访问控制。访问控制的目的不仅仅是防止用户的恶意企图,访问控制也有助于避免很常见的无意识错误的结果,如错打 MySQL 语句,在不合适的数据库中操作或其他一些用户错误。

管理用户

查询已有用户

MySQL 用户账号和信息存储在名为 mysql 的 MySQL数据库中。一般只有在需要获得所有用户账号列表时才会直接访问。

# 输入
USE mysql;
SELECT user FROM user;
# 输出
+------------------+
| user |
+------------------+
| test |
| root |
+------------------+

创建用户账号

1、使用 CREATE USER 语句(推荐)

# 输入
CREATE USER chenfangxu IDENTIFIED BY '123456';
SELECT user FROM user;
#输出
+------------------+
| user |
+------------------+
| chenfangxu |
| test |
| root |
+------------------+

2、GRANT 语句也可以创建用户账号。(MySQL 8.0以上的新版本已经将创建账户和赋予权限分开了,所以不能再用这种方法创建用户了)

# mysql8.0以下
GRANT SELECT ON *.* TO [email protected]'%' IDENTIFIED BY '123456';

3、使用 INSERT 直接插入行到 user 表来增加用户(不建议)

设置访问权限

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

查看赋予用户账号的权限 SHOW GRANTS FOR

# 输入
SHOW GRANTS FOR chenfangxu;
# 输出
+----------------------------------------+
| Grants for [email protected]% |
+----------------------------------------+
| GRANT USAGE ON *.* TO `chenfangxu`@`%` |
+----------------------------------------+

权限 USAGE ON *.* ,USAGE表示根本没有权限,这句话就是说在任意数据库和任意表上对任何东西没有权限。

[email protected]% 因为用户定义为 [email protected], MySQL的权限用用户名和主机名结合定义,如果不指定主机名,则使用默认的主机名%(即授予用户访问权限而不管主机名)。

添加(更新)用户权限 GRANT privileges ON databasename.tablename TO 'username'@'host';

# 输入
GRANT SELECT ON performance_schema.* TO chenfangxu@'%';
SHOW GRANTS FOR chenfangxu;
# 输出
+------------------------------------------------------------+
| Grants for [email protected]% |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO `chenfangxu`@`%` |
| GRANT SELECT ON `performance_schema`.* TO `chenfangxu`@`%` |
+------------------------------------------------------------+

撤销用户的权限 REVOKE privileges ON databasename.tablename FROM 'username'@'host';

# 输入
REVOKE SELECT ON performance_schema.* FROM chenfangxu@'%';
SHOW GRANTS FOR chenfangxu;
#输出
+----------------------------------------+
| Grants for [email protected]% |
+----------------------------------------+
| GRANT USAGE ON *.* TO `chenfangxu`@`%` |
+----------------------------------------+

重命名

重命名:RENAME USER 'username' TO 'newusername';

# 输入
RENAME USER test TO test1;
SELECT user FROM user;
# 输出
+------------------+
| user |
+------------------+
| test1 |
| root |
+------------------+

更改用户密码(mysql 8.0.11后)

更改用户密码:SET PASSWORD FOR 'username'@'host' = 'newpassword';

SET PASSWORD FOR [email protected]'%' = '654321';
# 更改root密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yourpasswd';

删除用户

删除用户:DROP USER 'username'@'host';

# 输入
DROP USER chenfangxu@'%';
SELECT user FROM user;
#输出
+------------------+
| user |
+------------------+
| test |
| root |
+------------------+

MySQL 5 以前, DROP USER 只能用来删除用户账号,不能删除相关的权限。因此,如果使用旧版的 MySQL 需要先用 REVOKE 删除与账号相关的权限,然后再用 DROP USER 删除账号。

操作数据库

# 创建数据库,如创建 learnsql 数据库
CREATE DATABASE learnsql;
# 选择数据库,如选择 learnsql 数据库
USE learnsql;
# 显示数据库列表
SHOW DATABASES;
# 显示数据库内的表的列表
SHOW TABLES;
# 显示表中每一列的详细信息
SHOW COLUMNS FROM customers;

DESCRIBE 语句

MySQL 中 DESCRIBE 可以作为 SHOW COLUMNS FROM 的快捷方式。

# 以下两种命令结果相同
SHOW COLUMNS FROM customers;
DESCRIBE customers;

下面用到的数据库文件可在 mysql_scripts 找到。

检索数据

# 检索单个列,例如从 products 表中检索一个名为 prod_name 的列。
SELECT prod_name FROM products;
# 检索多个列。注意,列名之间要用逗号分隔,最后一个列名后不要加逗号,会报错。
SELECT prod_id, prod_name, prod_price FROM products;
# 检索所有列。
SELECT * FROM products;
# 只检索出不同的行, DESTINCT 关键字可以让指令只返回不同的值。如果指令,products 表中可能一共有14行,现在只返回不同(唯一)的 vend_id 行,可能就只返回4行了。
SELECT DISTINCT vend_id FROM products;
# 限制结果, LIMIT 5 表示只返回不多于5行。
SELECT prod_name FROM products LIMIT 5;
# LIMIT 5, 5 表示返回从行5开始的5行。
SELECT prod_name FROM products LIMIT 5, 5;
# 或者使用 LIMIT 5 OFFSET 5, 跟上面结果相同。
SELECT prod_name FROM products LIMIT 5 OFFSET 5;
# 注意,返回行数是从 0 开始的。所以,LIMIT 1, 1 将检索出第二行,而不是第一行。
SELECT prod_name FROM products LIMIT 1,1;

排序检索数据 ( ORDER BY )

不使用排序时,其实检索出的数据并不是以纯粹的随机顺序显示的,数据一般将以它在底层表中出现的顺序显示。这可以是数据最初添加到表中的顺序,但是,如果数据后来进行过更新或者删除,则此顺序将会受到 MySQL 重用回收存储空间的影响。因此,如果不明确控制的话,不能(也不应该)依赖该排序顺序。

关系数据库设计理论认为:如果不明确规定排序顺序,则不应该假定检索出的数据的顺序有意义。

ORDER BY 子句,可以给 SELECT 语句检索出来的数据进行排序。 ORDER BY 子句取一个或多个列的名字。据此对输出进行排序。

# 没有排序
SELECT prod_name FROM products;
# 对 prod_name 列以字母顺序排序数据
SELECT prod_name FROM products ORDER BY prod_name;
# 按多个列排序:如下会先按照 prod_price 排序,
# 只有出现相同的 prod_price 时,才会再按照 prod_name 排序。
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name;
# 指定排序方向,默认是升序,例如按照 prod_price 降序排序(最贵的排在最前面)
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC;
# 多个列排序,例如按照 prod_price 降序,最贵的在最前面,然后在对产品名排序
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name;
# ORDER BY 和 LIMIT 搭配,可以找出一个列中最高或最低的值。
SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 1;

注意:

  • ORDER BY 子句中使用的列不一定非得是检索的列,用非检索的列排序也是完全合法的。

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

  • ASC 是升序排序,升序是默认的,不指定 DESC ,那就是按照 ASC 升序排序。

  • ORDER BY 子句必须位于 FROM 子句之后,如果使用 LIMIT ,它必须位于 ORDER BY 之后。

过滤数据 ( WHERE )

数据库包含大量的数据,但是我们很少需要检索表中所有的行。只检索所需数据需要指定过滤条件,在 SELECT 语句中,数据根据 WHERE 子句中指定的搜索条件进行过滤。

# 检索 pro_price 为 2.50 的行
SELECT prod_name FROM products WHERE prod_price = 2.50;
# 执行筛选匹配时默认不区分大小写,所以 fuses 可以检索出 Fuses
SELECT prod_name, prod_price FROM products WHERE prod_name = 'fuses';
# 输出
+-----------+------------+
| prod_name | prod_price |
+-----------+------------+
| Fuses | 3.42 |
+-----------+------------+
# 检索出 vend_id 不等于 1003 的行
SELECT vend_id, prod_name FROM products WHERE vend_id <> 1003;
# 检索 prod_price 在 5 到 10 之间的所有行
SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;
# 检查具有 NULL 值的列,用 IS NULL 子句
SELECT cust_id FROM customers WHERE cust_email IS NULL;

WHERE 子句操作符

操作符

说明

=

等于

<>

不等于

!=

不等于

<

小于

<=

小于等于

>

大于

>=

大于等于

BETWEEN

在指定的两个值之间

注意:

  • WHERE 语句的位置:在同时使用 ORDER BY 和 WHERE 子句时,应该让 ORDER BY 位于 WHERE 之后,否则将会产生错误。

  • WHERE 子句中使用的条件,如果将值与串类型(例如字符串)比较,需要加引号,用来与数值列进行比较的值不用引号。

  • NULL 无值(no value),它与字段 0 、空字符串或仅仅包含空格不同。

数据过滤( AND、 OR、 IN )

MySQL 允许组合多个 WHERE 子句。这些子句分为两种方式使用:以 AND 子句的方式或 OR 子句的方式使用。

### AND 操作符
# 检索出 vend_id 等于 1003 并且 prod_price 小于等于 10 的行
SELECT prod_price, prod_name FROM products WHERE vend_id = 1003 AND prod_price <= 10;
#### OR 操作符
# 检索出 vend_id 等于 1002 或 vend_id 等于 1003 的所有行
SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003;
# AND 和 OR 合用,AND 优先级高。
# 下面检索出的结果是 vend_id 是 1003 并且 prod_price 大于等于 10 的和所有 vend_id 是 1002 的行。
SELECT vend_id, prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10;
# 输出结果
+---------+----------------+------------+
| vend_id | prod_name | prod_price |
+---------+----------------+------------+
| 1002 | Fuses | 3.42 |
| 1002 | Oil can | 8.99 |
| 1003 | Detonator | 13.00 |
| 1003 | Bird seed | 10.00 |
| 1003 | Safe | 50.00 |
| 1003 | TNT (5 sticks) | 10.00 |
+---------+----------------+------------+
# 如果想检索出 vend_id 是 1003 并且 prod_price 大于等于 10 的和 vend_id 是 1002 并且 prod_price 大于等于 10 的行,需要加括号。
SELECT vend_id, prod_name, prod_price FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;
### IN 操作符,指定条件范围,范围中的每个条件都可以进行匹配。IN 取值是全部括在圆括号中的由逗号分隔的列表。
SELECT vend_id, prod_name, prod_price FROM products WHERE vend_id IN (1002, 1003);
### NOT 操作符,否定它之后的任何条件
SELECT vend_id, prod_name, prod_price FROM products WHERE vend_id NOT IN (1002, 1003);

注意

  • WHERE 可包含任意数目的 AND 和 OR 操作符,并且允许两者结合以进行复杂和高效的过滤。不过 SQL 语言在处理 OR 操作符前,会优先处理 AND 操作符。

  • 任何时候使用具有 AND 和 OR 操作符的 WHERE 子句, 都推荐使用圆括号明确地分组,不要过分依赖默认计算次序。

  • IN 和 OR 具有相同的功能,但是 IN 操作符有以下优点

    • 过滤的字段太多的时候,IN 操作符的语法更清楚且更直观

    • IN 操作符一般比 OR 操作符执行的更快

    • IN 最大的优点是可以包含其他 SELECT 语句,能更动态地建立 WHERE 子句。

  • MySQL 支持使用 NOT 对 IN、BETWEEN 和 EXISTS 子句取反。

用通配符过滤

百分号(%)通配符

% 表示任何字符出现任意次数,可以使0次,1次,n次

### 找出所有以 jet 开头的产品
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'jet%';
+---------+--------------+
| prod_id | prod_name |
+---------+--------------+
| JP1000 | JetPack 1000 |
| JP2000 | JetPack 2000 |
+---------+--------------+
### 通配符可在搜索模式中任意位置使用,并且可以使用多个通配符。
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '%anvil%';
+---------+--------------+
| prod_id | prod_name |
+---------+--------------+
| ANV01 | .5 ton anvil |
| ANV02 | 1 ton anvil |
| ANV03 | 2 ton anvil |
+---------+--------------+

下划线通配符

下划线 _ 只能匹配单个字符,只能匹配一个,不能多也不能少。

### 对比一下下面两个通配符结果
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ ton anvil';
+---------+-------------+
| prod_id | prod_name |
+---------+-------------+
| ANV02 | 1 ton anvil |
| ANV03 | 2 ton anvil |
+---------+-------------+
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '% ton anvil';
+---------+--------------+
| prod_id | prod_name |
+---------+--------------+
| ANV01 | .5 ton anvil |
| ANV02 | 1 ton anvil |
| ANV03 | 2 ton anvil |
+---------+--------------+
### 下划线通配符比百分号通配符少了一个 .5 的数据

注意

  • 注意尾部空格,例如'%anvil' 是匹配不到 'anvil ',因为后面有个空格不容易发现,解决方法就是后面再附加一个 % ,或者用函数去掉首尾空格。

  • % 是不能匹配出 NULL的。

  • 通配符搜索的处理一般要比其他搜索花时间更长,所以不要过度使用通配符,如果其他操作符能达到同样的目的,优先使用其他操作符。在确实需要使用通配符时,除非绝对有必要,否则不要把他们用在搜索模式的开始处。

用正则表达式进行搜索

### 基本字符匹配,下面的语句检索列 prod_name 包含文本 1000 的所有行。
SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;
### 区分大小写需要用到 BINARY 关键字
SELECT prod_name FROM products WHERE prod_name REGEXP BINARY 'S';
### 使用 | 进行 OR 匹配,可以有两个以上的 OR 条件,例如: '1000|2000|3000'
SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000';
+--------------+
| prod_name |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
### 匹配几个字符之一
SELECT prod_name FROM products WHERE prod_name REGEXP '[1,2,3] Ton' ORDER BY prod_name;
+-------------+
| prod_name |
+-------------+
| 1 ton anvil |
| 2 ton anvil |
+-------------+
### 注意区别 1|2|3 Ton,这表示匹配出 1,2和3 Ton,其实[123]是[1|2|3]的缩写
SELECT prod_name FROM products WHERE prod_name REGEXP '1|2|3 Ton' ORDER BY prod_name;
+---------------+
| prod_name |
+---------------+
| 1 ton anvil |
| 2 ton anvil |
| JetPack 1000 |
| JetPack 2000 |
| TNT (1 stick) |
+---------------+
### 匹配特殊字符, \\ 来转义特殊字符
SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.' ORDER BY vend_name;
+--------------+
| vend_name |
+--------------+
| Furball Inc. |
+--------------+
### 匹配出连在一起的4个数字
SELECT prod_name FROM products WHERE prod_name REGEXP '[:digit:]{4}' ORDER BY prod_name;
+--------------+
| prod_name |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+

列举元字符转义和定位元字符

元字符

说明

\f

换页

\n

换行

\r

回车

\t

制表

\v

纵向制表

\\

反斜杠

^

文本的开始

$

文本的结束

[:<:]

词的开始

[:>:]

词的结束

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

列举字符类

说明

[:alnum:]

任意字符和数字(同 [a-zA-Z0-9])

[:alpha:]

任意字符(同 [a-zA-Z])

[:blank:]

空格和制表 (同 [\t])

[:cntrl:]

ASCII控制字符 (ASCII 0 到 31 和 127)

[:digit:]

任意数字 (同 [0-9])

[:xdigit:]

任意十六进制数字(同 [a-fA-F0-9])

[:lower:]

任意小写字母 (同 [a-z])

[:upper:]

任意大写字母(同 [A-Z])

[:print:]

任意可打印字符

[:graph:]

与[:print:]相同,但不包含空格

[:punct:]

既不在[:alnum:]又不在[:cntrl:]中的任意字符

[:space:]

包括空格在内的任意空白字符(同 [\f\n\r\t\v])

简单的正则表达式测试

在不使用数据库表的情况下用 SELECT 来测试正则表达式。 REGEXP 检查总是返回0(没有匹配)或 1(匹配)。

SELECT 'hello' REGEXP 'hello\\b';
+---------------------------+
| 'hello' REGEXP 'hello\\b' |
+---------------------------+
| 1 |
+---------------------------+

计算字段

### 将查出来的名字和国家拼接出来展示,使用了 Concat、Trim函数,和 AS 关键字
SELECT Concat( Trim(vend_name), '(', Trim(vend_country), ')') AS vend_name FROM vendors ORDER BY vend_name;
+------------------------+
| vend_name |
+------------------------+
| ACME(USA) |
| Anvils R Us(USA) |
| Furball Inc.(USA) |
| Jet Set(England) |
| Jouets Et Ours(France) |
| LT Supplies(USA) |
+------------------------+
### 将20005订单中的所有物品查出来,通过数量和单价算出总价
SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM orderitems WHERE order_num = 20005;
+---------+----------+------------+----------------+
| prod_id | quantity | item_price | expanded_price |
+---------+----------+------------+----------------+
| ANV01 | 10 | 5.99 | 59.90 |
| ANV02 | 3 | 9.99 | 29.97 |
| TNT2 | 5 | 10.00 | 50.00 |
| FB | 1 | 10.00 | 10.00 |
+---------+----------+------------+----------------+

测试计算

SELECT 提供了测试和实验函数与计算的一个很好的方法,就是省略 FROM 子句

SELECT 3*2;
+-----+
| 3*2 |
+-----+
| 6 |
+-----+
SELECT Now();
+---------------------+
| Now() |
+---------------------+
| 2019-11-21 22:51:13 |
+---------------------+

使用数据处理函数

函数没有 SQL 的可移植性强,几乎每种主要的 DBMS 的实现都支持其他实现不支持的函数,有时差异还很大。为了代码的可移植性,很多人不赞成使用特殊实现的功能,虽然这样做很有好处,但是对于应用程序的性能可能出现影响。如果决定使用函数,应该保证做好代码注释。

文本处理函数

函 数

说 明

Left()

返回串左边的字符

Length()

返回串的长度

Locate()

找出串的一个子串

Lower()

将串转换为小写

LTrim()

去掉串左边的空格

Right()

返回串右边的字符

RTrim()

去掉串右边的空格

Soundex()

返回串的 SOUNDEX 值

SubString()

返回子串的字符

Upper()

将串转换为大写

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

### 例如用 Y. Lie 把 Y Lee 搜出来,因为它们发音类似
SELECT cust_name, cust_contact FROM customers WHERE Soundex(cust_contact) = Soundex('Y. Lie');
+-------------+--------------+
| cust_name | cust_contact |
+-------------+--------------+
| Coyote Inc. | Y Lee |
+-------------+--------------+

日期和时间处理函数

函 数

说 明

AddDate()

增加一个日期(天、周等)

AddTime()

增加一个时间(时、分等)

CurDate()

返回当前日期

CurTime()

返回当前时间

Date()

返回日期时间的日期部分

DateDiff()

计算两个日期之差

Date_Add()

高度灵活的日期运算函数

Date_Format()

返回一个格式化的日期或时间串

Year()

返回一个日期的年份部分

Month()

返回一个日期的月份部分

Day()

返回一个日期的天数部分

DayOfWeek()

对于一个日期,返回对应的星期几

Hour()

返回一个时间的小时部分

Minute()

返回一个时间的分钟部分

Second()

返回一个时间的秒部分

Now()

返回当前日期和时间

### 检索出日期为 2005-09-01 这天的订单记录
SELECT cust_id, order_num FROM orders WHERE order_date = '2005-09-01';
### 上面的检索有个问题,如果 order_date 存储的带有时间,例如 2005-09-01 11:30:05 ,就检索不到了,解决办法是让仅将给出的日期与列中的日期部分进行比较
SELECT cust_id, order_num FROM orders WHERE Date(order_date) = '2005-09-01';
### 如果想检索出2005年9月的所有订单
### 方法一,得记住每个月有多少天,甚至要知道是不是闰年的2月
SELECT cust_id, order_num FROM orders WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';
### 方法二, 使用 Year() 和 Month() 函数
SELECT cust_id, order_num FROM orders WHERE Year(order_date) = 2005 AND Month(order_date) = 9;

注意

  • 使用日期过滤,日期必须为 yyyy-mm-dd ,这样能排除一些歧义,年份也应该使用4位数字,更加可靠。

数值处理函数

函 数

说 明

Abs()

返回一个数的绝对值

Sin()

返回一个角度的正弦

Cos()

返回一个角度的余弦

Tan()

返回一个角度的正切

Exp()

返回一个数的指数值

Mod()

返回除操作的余数

Pi()

返回圆周率

Rand()

返回一个随机数

Sqrt()

返回一个数的平方根

汇总数据

函 数

说 明

AVG()

返回某列的平均值

COUNT()

返回某列的行数

MAX()

返回某列的最大值

MIN()

返回某列的最小值

SUM()

返回某列值之和

### 计算出 products 表中所有产品的平均价格
SELECT AVG(prod_price) AS avg_price FROM products;
+-----------+
| avg_price |
+-----------+
| 16.133571 |
+-----------+
### 查看 customers 表中所有客户的总数
SELECT COUNT(*) AS num_cust FROM customers;
+----------+
| num_cust |
+----------+
| 5 |
+----------+
### 只对具有电子邮件地址的客户计数
SELECT COUNT(cust_email) AS num_cust FROM customers;
+----------+
| num_cust |
+----------+
| 3 |
+----------+
### 计算出订单号为 20005 的物品总数
SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;
+---------------+
| items_ordered |
+---------------+
| 19 |
+---------------+
### 多个聚集函数组合
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;
+-----------+-----------+-----------+-----------+
| num_items | price_min | price_max | price_avg |
+-----------+-----------+-----------+-----------+
| 14 | 2.50 | 55.00 | 16.133571 |
+-----------+-----------+-----------+-----------+

参数 ALL 和 DISTINCT

使用 DISTINCT 参数时,只会计算包含不同的值的行,如果指定参数为 ALL 或者不指定参数,默认参数为 ALL ,会计算所有的行。

### 看一下产品表里有多少家供应商,因为有可能一家供应商提供很多产品。
### 全部的行
SELECT COUNT(vend_id) AS vend_count FROM products;
+------------+
| vend_count |
+------------+
| 14 |
+------------+
### 去重后就知道有 4 家供应商
SELECT COUNT(DISTINCT vend_id) AS vend_count FROM products;
+------------+
| vend_count |
+------------+
| 4 |
+------------+

注意

  • AVG() 只能用来确定 单个 特定数值列的平均值,而且列名必须作为函数参数传入,想获取多个列的平均值,必须使用多个 AVG() 函数。

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

  • COUNT(*) 对表中行的数目进行计数, 不管列中是空值(NULL)还是非空值。

  • 使用 COUNT(column) 对特定列中具有值的行进行计数,会忽略 NULL 值。

  • MAX() 函数会忽略值为 NULL 的行(MIN()也是)。它一般是用来找出最大的数值和日期值,但是也可以对非数值的数据使用,例如返回文本列中的最大值,MAX() 会返回最后一行(MIN

    () 会返回第一行)。

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

  • 在表示某个聚集函数的结果时,不应该使用表中实际的列明,最好是指定别名,这样便于理解和使用。

分组数据

数据分组

SELECT vend_id, prod_price FROM products GROUP BY vend_id, prod_price;
  • GROUP BY 子句后面可以加多个列。

  • SELECT子句中的列名必须为分组列或列函数(聚集计算语句除外),例如 按照 vend_id, prod_price 分组,SELECT 后面检索的列必须是 vend_id, prod_price。

  • 列函数对于GROUP BY子句定义的每个组各返回一个结果,例如取最大值时,就是每个组的最大值。

  • 如果分组列中有 NULL 值,则 NULL 将作为一个分组返回,如果列中有多行 NULL 值,它们将分为一组。

  • GROUP BY 子句必须在 WHERE 子句之后, ORDER BY 子句之前。

过滤分组

### 列出至少有两个订单的所有顾客
SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2;
### 列出具有2个(含)以上、价格为10(含)以上的产品的供应商
SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price >= 10 GROUP BY vend_id HAVING COUNT(*) >= 2;
### 列出总计订单价格大于等于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;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
| 20006 | 55.00 |
| 20008 | 125.00 |
| 20005 | 149.87 |
| 20007 | 1000.00 |
+-----------+------------+
  • HAVING 跟 WHERE 类似,但是 WHERE 在数据分组前进行过滤,HAVING 在数据分组后进行过滤。

SELECT 子句顺序

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

使用子查询

假设要列出订购物品 TNT2 的所有客户。我们可以拆分出下面三步。 1. 检索包含物品 TNT2 的所有订单的编号。 2. 检索具有前一步骤列出的订单编号的所有客户的 ID。 3. 检索前一步骤返回的所有客户 ID 的客户信息。

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'));
+----------------+--------------+
| cust_name | cust_contact |
+----------------+--------------+
| Coyote Inc. | Y Lee |
| Yosemite Place | Y Sam |
+----------------+--------------+

假设需要显示 customers 表中每个客户的订单总数,我们可以查分出下面两步。 1. 从 customers 表中检索客户列表。 2. 对于检索出的每个客户,统计其在 orders 表中的订单数目。

SELECT cust_name, cust_contact,
(SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers ORDER BY cust_name;
+----------------+--------------+--------+
| cust_name | cust_contact | orders |
+----------------+--------------+--------+
| Coyote Inc. | Y Lee | 2 |
| E Fudd | E Fudd | 1 |
| Mouse House | Jerry Mouse | 0 |
| Wascals | Jim Jones | 1 |
| Yosemite Place | Y Sam | 1 |
+----------------+--------------+--------+

联结表

### 等值联结(equi join),它基于两个表之间的相等测试。这种联结也称为 内部联结。
SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name, prod_name;
### 内部联结的语法
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;
### 上面提到用子查询,返回订购产品 TNT 的客户列表,现在改成联结表的方式,可以跟子查询的方式对比一下。
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';

注意

  • 应该保证所有的联结都有 WHERE 子句,否则 MySQL 将返回比想要的数据多得多的数据。

创建高级联结

表别名和自联结

### 使用表别名,返回订购产品 TNT 的客户列表
SELECT cust_name, cust_contact FROM customers AS c, orders AS o, orderitems AS oi WHERE c.cust_id = o.cust_id AND o.order_num = oi.order_num AND prod_id = 'TNT2';
### 使用自联结,查找商品 ID 为 DTNTR 的供应商供应的所有产品
SELECT p1.prod_id, p1.prod_name FROM products AS p1, products AS p2 WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR';

外部联结

联结包含了那些在相关表中没有关联行的行,外部联结的两种基本形式:左外部联结(LEFT OUTER JOIN 即 LEFT JOIN)和右外部联结。它们之间唯一差别是所关联的表的顺序不同。更具体可以看一下 JOIN详解

### 列出每个客户下的订单,包括那些至今未下订单的客户
SELECT customers.cust_id, orders.order_num FROM customers LEFT JOIN orders ON customers.cust_id = orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
| 10001 | 20005 |
| 10001 | 20009 |
| 10002 | NULL |
| 10003 | 20006 |
| 10004 | 20007 |
| 10005 | 20008 |
+---------+-----------+
#### 对每个用户下的订单计数,包括那些至今没下订单的客户
SELECT c.cust_name, c.cust_id, COUNT(o.order_num) AS order_count FROM customers AS c LEFT JOIN orders AS o ON c.cust_id = o.cust_id GROUP BY c.cust_id;
+----------------+---------+-------------+
| cust_name | cust_id | order_count |
+----------------+---------+-------------+
| Coyote Inc. | 10001 | 2 |
| Mouse House | 10002 | 0 |
| Wascals | 10003 | 1 |
| Yosemite Place | 10004 | 1 |
| E Fudd | 10005 | 1 |
+----------------+---------+-------------+

组合查询

MySQL 允许执行多个查询(多条 SELECT 语句),并将结果作为单个查询结果集返回。这些组合查询称为并(union) 或 复合查询(compound query)。

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

  • 在单个查询中从不同的表返回类似结构的数据;

  • 对单个表执行多个查询,按单个查询返回数据。

### 查询价格小于等于5的所有物品并且查出供应商 1001 和 1002 生产的所有物品(不考虑价格)
### 先用 WHERE 多个子句来实现。
SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5 OR vend_id IN (1001,1002);
### 使用组合查询实现,会自动去除重复的行
SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5 UNION SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001, 1002);
### 使用组合查询查所有符合条件的列
SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5 UNION ALL SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001, 1002);
### 组合查询排序
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_id;

注意

  • UNION 必须由两条或两条以上的 SELECT 语句组成,语句之间用关键字 UNION 分隔。

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

  • 对组合查询结果排序时,只能使用一条 ORDER BY 子句,它必须出现在最后一条 SELECT 语句之后。

全文本搜索

并非所有引擎都支持全文本搜索,例如 MyISAM 支持全文本搜索,InnoDB 不支持。

在创建表时启用全文本搜索, CREATE TABLE 语句接受 FULLTEXT 子句,它可以对后面的一个或多个表建立索引,MySQL自动维护该索引,在增加、更新或删除行时,索引随之自动更新。FULLTEXT 也可以在表创建之后再指定。

### 看一下 productnotes 表的创建描述
CREATE TABLE `productnotes` (
`note_id` int(11) NOT NULL AUTO_INCREMENT,
`prod_id` char(10) NOT NULL,
`note_date` datetime NOT NULL,
`note_text` text,
PRIMARY KEY (`note_id`),
FULLTEXT KEY `note_text` (`note_text`)
) ENGINE=MyISAM AUTO_INCREMENT=115 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

创建索引后就可以用 Match() 和 Against() 执行全文本搜索,其中 Match() 指定被搜索的列, Against() 指定要使用的搜索表达式。

### 搜索出 node_text 中包含 rabbit 的。
### 先用 LIKE 实现
SELECT note_text FROM productnotes WHERE note_text LIKE '%rabbit%';
+-----------------------------------------------+
| Quantity varies, …… for use as rabbit bait. |
| Customer complaint: rabbit has …… |
+-----------------------------------------------+
### 使用文本搜索实现,可以看到 rabbit 排在第三个词的文本比排在第20个词的文本排序高。
SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit');
+-----------------------------------------------+
| Customer complaint: rabbit has …… |
| Quantity varies, …… for use as rabbit bait. |
+-----------------------------------------------+
### 可以使用下面的语句查看排序的等级
SELECT note_text, Match(note_text) Against('rabbit') AS rank1 FROM productnotes;
### 除了查出来的两个有数据,其他的等级都是0
| Customer complaint: rabbit has …… | 的等级是 1.6408053636550903
| Quantity varies, …… for use as rabbit bait. | 的等级是 1.5905543565750122
### 使用查询扩展 WITH QUERY EXPANSION ,找出所有提到 anvils 的注释,还要找出与当前搜索有关的所有其他行,即使它们不包含 anvils
SELECT note_text FROM productnotes WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);
### 会返回7行,但是只有第一行有 anvils ,第二行虽然与 anvils 无关,但因为它包含了第一行的两个词,所以也被检索出来。

注意

  • 传递给 Match() 的值必须与 FULLTEXT() 定义中的相同。如果指定多个列,必须列出他们,而且次序正确。

  • 经过比较可以发现,文本搜索是默认带有排序的,LIKE 搜索出来的结果是随意的,按照查询的先后输出。

  • 文本搜索的等级根据行中词的数目,唯一词的数目,整个索引中词的总数,以及包含该词的行的书目计算出来。

  • 在索引全文本数据时,短词被忽略且从索引中排除。短词的定义时那些具有3个或3个一下字符的词(如果需要,这个数目可以改)

  • 许多词出现的频率很高,搜索他们没用,MySQL 规定了一条 50% 规则,如果一个词出现50%以上的行中,则将它作为一个非用词忽略。50%规则不用于 IN BOOLEAN MODE。

  • 如果表中的行数少于3行,则全文本搜索不返回结果

  • 忽略词中的单引号。例如,don't 索引为 dont

  • 不具有词分隔符(包括日语和汉语)的语言不能恰当地返回全文本搜索结果

布尔文本查询

IN BOOLEAN MODE,即使没有 FULLTEXT 索引,也可以使用

### 匹配包含 heavy 但不包含任意以 rope 开始的词的行
SELECT note_text FROM productnotes WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);
### 匹配词 safe 和 combination, 降低后者的等级。
SELECT note_text FROM productnotes WHERE Match(note_text) Against('+safe +(<combination)' IN BOOLEAN MODE);

全文本布尔操作符

布尔操作符

说 明

+

包含,词必须存在

-

排除,词必须不出现

>

包含,而且增加等级值

<

包含,且减少等级值

()

把词组成子表达式(允许这些子表达式作为一个组被包含、排除、 排列等)

~

取消一个词的排序值

*

词尾的通配符

""

定义一个短语(与单个词的列表不一样,它匹配整个短语以便包含或排除这个短语)

插入数据

插入完整的行

### 如下的语句中,对每个列必须提供一个值,如果某个列没有值,应该使用 NULL 值(假设表允许对该列指定空值)。每个列必须按照顺序给出,自动增量的值也不能忽略,而且如果不想赋值,就需要指定为 NULL 。
INSERT INTO customers VALUES( NULL, 'Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL, NULL);

上面的语法应该避免使用,因为不安全,建议用下面的语句,可以不按照次序填充,只要保证 VALUES中的次序跟前面给出的列名次序一致就行。

INSERT INTO customers(cust_name, cust_contact, cust_email, cust_address, cust_city, cust_state, cust_zip, cust_country) VALUES('Pep E. LaPew', NULL, NULL, '100 Main Street', 'Los ANGELES', 'CA', '90046', 'USA');

插入多个行

### 可以使用多条 INSERT 语句,甚至一次提交它们,每条语句用一个分号结束。
INSERT INTO customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country) VALUES('Pep E. LaPew','100 Main Street','Los Angeles','CA','90046','USA');INSERT INTO customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country) VALUES('M. Martian','42 Galaxy Way','New York','NY','11213','USA');
### 或者每条 INSERT 语句中的列名(和次序)相同,可以如下组合语句
INSERT INTO customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country) VALUES('Pep E. LaPew','100 Main Street','Los Angeles','CA','90046','USA'), ('M. Martian','42 Galaxy Way','New York','NY','11213','USA');

注意

  • 在 INSERT 操作中可以省略某些列,省略的列必须满足:该列定义为允许 NULL 值(无值或者空值),或在表定义中给出默认值,这表示如果不给出值,将使用默认值,否则插入时省略会报错。

  • 可以使用 INSERT LOW PRIORITY INTO 来降低插入语句的优先级。

更新数据

### 更新id是10009客户名字的邮箱。
UPDATE customers SET cust_name = 'The Fudds', cust_email = '[email protected]' WHERE cust_id = 10009;
### 为了删除某个列的值,可设置它为 NULL(假定表定义为允许 NULL 值)
UPDATE customers SET cust_email = NULL WHERE cust_id = 10009;

注意

  • 使用 UPDATE 时,一定不能省略 WHERE 子句,否则就会更新表中的所有行。

  • UPDATE 操作如果报错,则整个UPDATE操作被取消,错误发生前更新的所有行被恢复到它们原来的值,如果想发生错误的时候也继续进行更新,可以使用 IGNORE 关键字 UPDATE IGNORE customers

删除数据

### 删除10011这一行
DELETE FROM customers WHERE cust_id = 10011;

注意

  • 使用 DELETE 时,一定不能省略 WHERE 子句,否则就会删除表中的所有行。即使删除所有行, DELETE 也不会删除表本身。

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

创建和操纵表

创建表

利用 CREATE TABLE 创建表,必须紧跟着给出新表的名字,然后是表列的名字和定义,用逗号分隔。

NULL 值

NULL 值就是没有值或缺值。允许 NULL 值的列也允许在插入行时不给出该列的值。 NOT NULL 即不允许 NULL 值的列不接受该列没有值的行,在插入或更新行时,该列必须有值。NULL 是默认设置,如果不指定 NOT NULL,则认为指定的是 NULL。

主键

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

使用类似 PRIMARY KEY (id) 的语句来定义。为创建由多个列组成的主键,应该以逗号分隔的列表给出各列名,例如 orderitems 表的创建用到的 PRIMARY KEY (order_num, order_item)

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

主键为唯一标识表中每个行的列,主键中只能使用不允许 NULL 值的列。

AUTO_INCREMENT

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

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

在执行 INSERT 时可以给 AUTO_INCREMENT指定一个值,只要它是至今为止唯一的就行,该值将被用来替代自动生成的值。后续的增量将开始使用该手工插入的值。

last_insert_id() 这个函数能返回最后一个 AUTO_INCREMENT 值

指定默认值

列定义中的 DEFAULT 关键字指定。 MySQL 跟大多数 DBMS 一样, 不允许使用函数作为默认值,它只支持常量。

引擎类型

大多数时候, CREATE TABLE 语句全都以 ENGINE=InnoDB 语句结束。MySQL具有多种引擎,这些打包的多个引擎都隐藏在 MySQL 的服务器内,全都能执行 CREATE TABLE 和 SELECT 等命令。这些引擎具有各自不同的功能和特性,为不同的任务选择正确的引擎能获得良好的功能和灵活性。

InnoDB 是一个可靠的事务处理引擎,它不支持全文本搜索。

MyISAM 是一个性能极高的引擎,它支持全文本搜索,但不支持事务处理

MEMORY 在功能等同于 MyISAM,但由于数据存储在内存(不是磁盘)中,速度很快(特别适合用于临时表)

########################
# 看一下 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_country char(50) NULL ,
cust_contact char(50) NULL ,
cust_email char(255) NULL ,
PRIMARY KEY (cust_id)
) ENGINE=InnoDB;

更新表

为了更新表定义,可使用 ALTER TABLE 语句。

### vendors 表中增加 vend_phone 列
ALTER TABLE vendors ADD vend_phone CHAR(20);
### 删除刚刚增加的 vend_phone 列
ALTER TABLE vendors DROP COLUMN vend_phone;
### ALTER TABLE 常见的用途就是定义外键
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id)

删除表

DROP TABLE customers2;

重命名表

### 重命名一个表
RENAME TABLE customers2 TO customers;
### 重命名多个表
RENAME TABLE backup_customers TO customers,backup_vendors TO vendors;

注意

  • 创建新表时,指定的表名必须不存在,否则将出错。如果仅想在一个表不存在时创建它,应该在表名后面给出 IF NOT EXISTS。

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

使用视图

视图仅仅是用来查看存储在别处的数据的一种设施,本身不包含数据,返回的数据都是从其他表中检索出来的,视图能更改数据格式和表示,最常见的应用就是重用 SQL 语句,简化复杂的 SQL 操作。

操作视图

CREATE VIEW 创建视图

SHOW CREATE VIEW viewname 查看创建视图的语句

DROP VIEW viewname 删除视图

更新视图时,可以先 DROP 然后再 CREATE 或者使用 CREATE OR REPLACE VIEW

### 之前有用联表查询 prod_id 是 TNT2的购买用户信息,但是如果还想看 prod_id 是其他值的时,还得重新查一遍,这样的场景就能用到视图了。
### 创建一个包含 cust_name, cust_contact, prod_id 的视图
CREATE VIEW productcustomers AS SELECT cust_name, cust_contact, prod_id FROM customers, orders, orderitems WHERE customers.cust_id = orders.cust_id AND orderitems.order_num = orders.order_num;
### 现在就能看到 productcustomers 视图能查询的信息了
select * from productcustomers;
+----------------+--------------+---------+
| cust_name | cust_contact | prod_id |
+----------------+--------------+---------+
| Coyote Inc. | Y Lee | ANV01 |
| Coyote Inc. | Y Lee | ANV02 |
| Coyote Inc. | Y Lee | TNT2 |
| Coyote Inc. | Y Lee | FB |
| Coyote Inc. | Y Lee | FB |
| Coyote Inc. | Y Lee | OL1 |
| Coyote Inc. | Y Lee | SLING |
| Coyote Inc. | Y Lee | ANV03 |
| Wascals | Jim Jones | JP2000 |
| Yosemite Place | Y Sam | TNT2 |
| E Fudd | E Fudd | FC |
+----------------+--------------+---------+
### 如果想再查询出 prod_id 为 TNT2 的客户信息就很简单了
SELECT cust_name, cust_contact FROM productcustomers WHERE prod_id = 'TNT2';
+----------------+--------------+
| cust_name | cust_contact |
+----------------+--------------+
| Coyote Inc. | Y Lee |
| Yosemite Place | Y Sam |
+----------------+--------------+

注意

  • 视图必须唯一命名(不能跟别的视图和表重名)

  • 对于可以创建的视图数量没有限制。

  • 视图可以嵌套,即可以利用从其他视图中检索数据的查询来构造一个新的视图。

  • ORDER BY 可以用在视图中,但如果从该视图检索数据的 SELECT 中也含有 ORDER BY ,那么视图中的 ORDER BY 会被覆盖。

  • 视图不能索引,也不能有关联的触发器或默认值。

  • 视图可以和表一起使用。

  • 视图一般用于检索(SELECT)而不用于更新(INSERT, UPDATE, DELETE),因为更新一个视图相当于更新其基表,如果不能正确地确定被更新的基数据,则不允许更新。

使用存储过程

使用存储过程主要有三个好处,简单,安全,高性能。

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

简单例子

### 创建名为 productpricing 的存储过程 如果存储过程接受参数,它们将在 productpricing () 这个括号中列举出来。
DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price) AS priceaverage FROM products;
END//
### 调用存储过程,仿佛在调用函数
CALL productpricing;
+--------------+
| priceaverage |
+--------------+
| 16.133571 |
+--------------+
### 删除存储过程
DROP PROCEDURE productpricing;
### 检查存储过程
SHOW CREATE PROCEDURE productpricing;

有参数的例子

### 创建存储过程
DELIMITER //
CREATE PROCEDURE productpricing (
OUT pl DECIMAL(8,2),
OUT ph DECIMAL(8,2),
OUT pa DECIMAL(8,2)
)
BEGIN
SELECT Min(prod_price) INTO pl FROM products;
SELECT Max(prod_price) INTO ph FROM products;
SELECT Avg(prod_price) INTO pa FROM products;
END //
### 调用存储过程
CALL productpricing(@pricelow,@pricehigh,@priceaverage);
### 查看3个变量
SELECT @pricelow,@pricehigh,@priceaverage;
+-----------+------------+---------------+
| @pricelow | @pricehigh | @priceaverage |
+-----------+------------+---------------+
| 2.50 | 55.00 | 16.13 |
+-----------+------------+---------------+

建立智能存储过程

需要获取订单合计,并且对某些顾客的合计增加营业税。

### 存储过程全过程
DELIMITER //
-- 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'
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*taxrate) INTO total;
END IF;
-- And finally, save to out variable
SELECT total INTO ototal;
END //
### 调用,看一下 order number 是 20005 订单关于加不加营业税的区别
### 不加营业税
CALL ordertotal(20005, 0, @total);
SELECT @total;
+--------+
| @total |
+--------+
| 149.87 |
+--------+
### 加营业税
CALL ordertotal(20005, 1, @total);
SELECT @total;
+--------+
| @total |
+--------+
| 158.86 |
+--------+

上面代码中做些必要的解释

  • 添加了两个 IN 类型参数,其中 taxable 为布尔值。

  • -- 添加注释,在存储过程复杂是,注释很有必要。

  • DECLARE 定义局部变量,需要指定变量名和数据类型,支持可选的默认值

  • COMMENT 关键字,不是必需的,如果添加了,在 SHOW PROCEDURE STATUS 的结果中显示。

注意

  • 如果在 mysql 命令行中创建存储过程的话,需要临时更改命令行实用程序的语句分隔符,因为创建存储过程会使用 ; 作为语句分隔符,这会导致语法报错。除了 符号外,任何字符都可以用作语句分隔符。 可以使用 DELIMITER // 作为新的语句结束分隔符,但是创建完存储过程后,要记得用 DELIMITER ; 恢复为原来的语句分隔符。

  • 存储过程在创建之后,被保存在服务器上以供使用,直至被删除。

  • 如果删除不存在的存储过程时,会报错,可以使用 DROP PROCEDURE IF EXISTS ,只有当过程存在时才删除。

  • MySQL 支持 IN(传递给存储过程)、OUT(从存储过程传出)、INOUT(对存储过程传入和传出)三种类型的参数。SELECT 检索出来的值通过 INTO 保存到相应的变量。特别注意,参数的数据类型不能是一个集合,所以例子中才用了三个参数输出3个数。

  • 如果存储过程要求3个参数,就必须正好传递3个参数。

  • SHOW PROCEDURE STATUS 可以列出所有存储过程,也可以使用 LIKE 指定一个过滤模式: SHOW PROCEDURE STATUS LIKE 'ordertotal';

使用游标

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

DELIMITER //
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);
INSERT INTO ordertotals(order_num,total) VALUES(o,t);
UNTIL done END REPEAT;
CLOSE ordernumbers;
END//
DELIMITER ;
SELECT * FROM ordertotals;
+-----------+---------+
| order_num | total |
+-----------+---------+
| 20005 | 158.86 |
| 20009 | 40.78 |
| 20006 | 58.30 |
| 20007 | 1060.00 |
| 20008 | 132.50 |
| 20008 | 132.50 |
+-----------+---------+

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

OPEN ordernumbers; 打开 ordernumbers 游标,在处理 OPEN 语句时执行查询,存储检索出的数据以供浏览和滚动。

CLOSE ordernumbers; 关闭 ordernumbers 游标,CLOSE 释放游标使用的所有内部内存和资源,因此在每个游标不再需要时都应该关闭,如果没有明确关闭游标,MySQL 将会在到达 END 语句时自动关闭它。在一个游标关闭后,如果没有重新打开,就不能使用它。但是,使用声明过的游标不需要再次声明,用 OPEN 语句打开就行。

FETCH 指定检索什么数据,检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针,使下一条 FETCH 语句检索下一行。

CONTINUE