# 简介

## 数据类型

主要包括以下五大类：

整数类型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT

浮点数类型：FLOAT、DOUBLE、DECIMAL

字符串类型：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB

日期类型：Date、DateTime、TimeStamp、Time、Year

其他数据类型：BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等

# 整型

| MySQL数据类型 | 含义（有符号）                       |
| ------------- | ------------------------------------ |
| tinyint(m)    | 1个字节 范围(-128~127)               |
| smallint(m)   | 2个字节 范围(-32768~32767)           |
| mediumint(m)  | 3个字节 范围(-8388608~8388607)       |
| int(m)        | 4个字节 范围(-2147483648~2147483647) |
| bigint(m)     | 8个字节 范围(+-9.22*10的18次方)      |

int(m) m 用于指定显示宽度，int(5)表示5位数的宽度。注意显示宽度属性不能控制列可以存储的值范围，显示宽度属性通常由应用程序用于格式化整数值。

### 浮点型

| MySQL数据类型 | 含义                                  |
| ------------- | ------------------------------------- |
| float(m,d)    | 单精度浮点型  4字节  m总个数，d小数位 |
| double(m,d)   | 双精度浮点型  8字节  m总个数，d小数位 |

浮点型数据类型会有精度丢失的问题，比如小数位设置6位，存入0.45，0.45转换成二进制是个无限循环小数0.01110011100...，无法准确表示，存储的时候会发生精度丢失。

不论是定点还是浮点类型，如果用户指定的精度超出精度范围，则会四舍五入进行处理。

### 定点数

浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值，不会丢失精度。定点数以字符串形式存储。

decimal(m,d) 参数m是总个数，d是小数位。

### 字符串

| MySQL数据类型 | 含义                            |
| ------------- | ------------------------------- |
| char(n)       | 固定长度，最多255个字节         |
| varchar(n)    | 可变长度，最多65535个字节       |
| tinytext      | 可变长度，最多255个字节         |
| text          | 可变长度，最多65535个字节       |
| mediumtext    | 可变长度，最多2的24次方-1个字节 |
| longtext      | 可变长度，最多2的32次方-1个字节 |

查询速度：char > varchar > text

char：定长，效率高，一般用于固定长度的表单提交数据存储 ；例如：身份证号，手机号，电话，密码等。char长度不足时，在右边使用空格填充，而varchar值保存时只保存需要的字符数。

varchar：不定长，效率偏低，内容开头用1到2个字节表示实际长度（长度超过255时需要2个字节），因此最大长度不能超过65535。

nvarchar（存储的是Unicode数据类型的字符）不管是一个字符还是一个汉字，都存为2个字节 ，一般用作中文或者其他语言输入，这样不容易乱码 ；varchar存储汉字是2个字节，其他字符存为1个字节 ，varchar适合输入英文和数字。

text：不需要指定存储长度，能用varchar就不用text。

### 二进制数据（BLOB）

二进制数据类型可存储任何数据，如图像、多媒体、文档等。

BLOB和TEXT存储方式不同，TEXT以文本方式存储，英文存储区分大小写；而Blob是以二进制方式存储，不区分大小写。

### 日期时间类型

| MySQL数据类型 | 含义                          |
| ------------- | ----------------------------- |
| date          | 日期 '2008-12-2'              |
| time          | 时间 '12:25:36'               |
| datetime      | 日期时间 '2008-12-2 22:06:44' |
| timestamp     | 自动存储记录修改时间          |

若定义一个字段为timestamp，这个字段里的时间数据会随其他字段修改的时候自动刷新，所以这个数据类型的字段可以存放这条记录最后被修改的时间。



# 基本命令

## 启动

启动服务：`service mysqld start`

关闭服务：`service mysqld stop`

启动客户端：`mysql -uroot -p`  -u 后不要有空格（Ubuntu有空格）

## 数据库操作

```mysql
SHOW DATABASES;
CREATE DATABASE db_name;
USE db_name;
DROP DATABASE db_name;
```

## 表

创建表：`create table user (id int, name varchar(10))`

清空表数据：`truncate table user;`

查看表结构：`desc table_name/select columns from table_name`

`SHOW CREATE db` | `SHOW CREATE table`：显示创建特定数据库或表的MySQL语句

## 检索

检索不同的行：

```mysql
SELECT DISTINCT vend_id
FROM products;
```

限制结果：

```mysql
SELECT prod_name
FROM products
LIMIT 0, 5; #开始位置，行数|返回从第0行开始的5行数据
```

## 排序

```mysql
SELECT prod_name
FROM products
ORDER BY prod_name, prod_price DESC; #先按名称排序，再按价格排序 | DESC降序排列，默认ASC升序
```

找出最贵的物品：

```mysql
SELECT prod_price
FROM products
ORDER BY prod_price DESC
LIMIT 1; # 仅返回一行
```

子句顺序：FORM -- ORDER BY -- LIMIT，顺序不对会报错。

## 过滤

子句操作符：

| 操作符  | 说明     |
| ------- | -------- |
| =       | 等于     |
| <>      | 不等于   |
| !=      | 不等于   |
| <       | 小于     |
| <=      | 小于等于 |
| BETWEEN | 两值之间 |

### 不匹配检查：

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

### 范围查询：

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

### 空值检查

```mysql
SELECT prod_name
FROM products
WHERE prod_price IS NULL;
```

### 计算次序

```mysql
SELECT prod_name, prod_price
FROM products
WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10; # AND优先级大于OR
```

### IN 操作符

```mysql
SELECT prod_name, product_price
FROM products
WHERE vend_id IN (1002, 1003)
ORDER BY prod_name;
```

IN操作符一般比OR操作符清单执行更快。IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建立WHERE子句。

### NOT操作符

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

```mysql
SELECT prod_name, product_price
FROM products
WHERE vend_id NOT IN (1002, 1003)
```

### LIKE操作符

% 匹配0到多个任意字符。

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

_ 匹配单个字符。

```mysql
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '_jet_';
```

通配符搜索比其他简单搜索耗时，不能过度使用通配符。

### LIMIT

limit 0,4 ：从第0条记录开始，取4条

### 正则表达式

OR 匹配：

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

匹配特定字符：

```mysql
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[123] Rely' #匹配1或2或3 [^123]取反
ORDER BY prod_name;
```

匹配范围：

```mysql
SELECT prod_name
FROM products
WHERE prod_anem REGEXP '[1-5] Ton';#匹配1-5任意一个数字，[a-z]同理
```

匹配特殊字符：

```mysql
SELECT prod_name
FROM products
WHERE prod_anem REGEXP '\\.';#转义
```

匹配多个实例:

```mysql
SELECT prod_name
FROM products
WHERE prod_anem REGEXP '\\([0-9] sticks?\\)'; #?匹配它前面的任何字符出现0次或1次
```

匹配连着的四个数：

```mysql
SELECT prod_name
FROM products
WHERE prod_anem REGEXP '[[:digit:]]{4}'; #[:digit:]匹配任意数字
```

定位符：

| 元字符  | 说明     |
| ------- | -------- |
| ^       | 文本开始 |
| $       | 文本结束 |
| [[:<:]] | 词开始   |
| [[:>:]] | 词结束   |

查找一个数（包括小数点开始的数）开始的所有产品：

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

简单的正则表达式测试：

```mysql
SELECT 'hello' REGEXP '[0-9]';#REGEXP检查返回0或1；此处返回0
```

# 计算字段

## 拼接字段

MySQL使用Concat()函数实现拼接。

```mysql
SELECT Concat(RTrim(vend_name), ' (', vend_country, ')') #RTrim()去掉右边空格 | Trim()去掉两边空格
FROM vend_name
ORDER BY vend_name;
```

返回值：`ACME (USA)`
使用别名：`SELECT dept AS department FROM t_dept;`

## 计算字段

```mysql
SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price
FROM orderitems
WHERE order_num = 2005;
```

# 函数

## 文本处理

```mysql
SELECT vend_name, Upper(vend_name) AS vend_name_upcase
FROM vendors
ORDER BY vend_name;
```

Soundex()函数，匹配所有同音字符串。

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

返回数据：`Tyson Y lee`

## 日期处理函数

![](http://img.topjavaer.cn/img/20220530235607.png)
查找2005年9月的所有订单：

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

或者

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

## 数值处理函数

![](http://img.topjavaer.cn/img/20220530233617.png)

# 汇总数据

## 聚集函数

Sum:求和
Avg:求平均数
Max:求最大值
 Min:求最小值
 Count:求记录

```mysql
SELECT SUM(item_price*quanlity) AS total_price
FROM orderitems
WHERE order_num = 2005;
```

聚集不同值：

```mysql
SELECT AVG(DISTINCT prod_price) AS avg_price #只考虑不同价格
FROM products
WHERE vend_id = 1003;
```

# 分组

单独地使用group  by没意义，它只能显示出每组记录的第一条记录。

```mysql
SELECT * FROM orders
GROUP BY cust_id;
```

![](http://img.topjavaer.cn/img/20220530233523.png)

除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。

```mysql
SELECT vend_id, COUNT(*) AS num_prods #vend_id在GROUP BY子句给出
FROM products
GROUP BY vend_id;
```

GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

## 过滤分组

having 用来分组查询后指定一些条件来输出查询结果，having作用和where类似，但是having只能用在group  by场合，并且必须位于group  by之后order  by之前。

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

## having和where区别

```mysql
SELECT cust_id FROM orders GROUP BY cust_id HAVING COUNT(cust_id) >= 2;
SELECT cust_id FROM orders GROUP BY cust_id WHERE COUNT(cust_id) >= 2; #Error Code : 1064
```

第一个sql语句可以执行，但是第二个会报错。

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

- 二者作用的对象不同，where子句作用于表和视图，having作用于组。

- WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。

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

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

## SELECT 子句顺序

```mysql
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT
```



# 子查询

由于性能的限制，不能嵌套太多的子查询。

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



# 连接

## 内连接

找出供应商生产的产品。

```mysql
SELECT vend_name, prod_name
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id; #连接条件使用on子句
```

等价于：

```mysql
SELECT vend_name, prod_name
FROM vendors, products
WHERE vendors.vend_id = products.vend_id;
```

没有给出连接条件的话，会得到两张表的笛卡尔积。

## 自连接

找出生产nike的供应商生产的所有物品。

```mysql
SELECT prod_id, prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
	AND p2.prod_id = 'nike';
```

## 自然连接

natural join是对两张表中字段名和数据类型都相同的字段进行**等值连接**，并返回符合条件的结果 。

```mysql
SELECT * FROM role NATURAL JOIN user_role;
```

返回结果：

![](http://img.topjavaer.cn/img/20220530235619.png)

## 内连接

显示符合连接条件的记录。没有设置连接条件则返回笛卡尔积的结果。join 默认是 inner join。

```mysql
SELECT * FROM role INNNER JOIN user_role
```

返回结果：

![](http://img.topjavaer.cn/img/20220530235640.png)

join…using(column)按指定的属性做等值连接。
join…on tableA.column1 = tableB.column2 指定条件。

```mysql
SELECT * FROM role INNER JOIN user_role ON role.role_id = user_role.role_id
```

返回结果：

![](http://img.topjavaer.cn/img/20220530235654.png)

## 外连接

左外联接（Left Outer Join）：除了匹配2张表中相关联的记录外，还会匹配左表中剩余的记录，右表中未匹配到的字段用NULL表示。
右外联接（Right Outer Join）：除了匹配2张表中相关联的记录外，还会匹配右表中剩余的记录，左表中未匹配到的字段用NULL表示。
在判定左表和右表时，要根据表名出现在Outer Join的左右位置关系。

查找所有客户及其订单，包括没有下过订单的客户。使用左外连接，保留左边表的所有记录。

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

## 多表连接

```mysql
SELECT goal.player, eteam.teamname, game.stadium, game.mdate
FROM game JOIN goal
ON game.id = goal.matchid 
JOIN eteam
ON eteam.id = goal.teamid
WHERE eteam.id = 'GRE'
```



# 组合查询

## UNION

UNION中的每个查询必须包含相同的列、表达式或聚集函数。列数据类型必须兼容。

```mysql
SELECT vend_id, prod_id
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id
FROM products
WHERE vend_id IN (1001, 1002);
-- 等价于
SELECT vend_id, prod_id
FROM products
WHERE prod_price < = 5
	AND vend_id IN (1001, 1002);
```

UNION 默认会去掉重复的行，使用 UNION ALL可以返回所有匹配行。



# 全文搜索

为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断地重新索引。在对表列进行适当设计后，MySQL会自动进行所有的索引和重新索引。

启动全文搜索（仅在MyISAM数据库引擎中支持全文本搜索）：

```mysql
CREATE TABLE productnotes
(
	note_id int NOT NULL AUTO_INCREMENT,
    note_text text NULL,
    PRIMARY KEY(note_id),
    FULLTEXT(note_text)
) ENGINE=MyISAM;
```

在定义之后，MySQL自动维护该索引。在增加、更新或删除行时，索引随之自动更新。

不要在导入数据时使用FULLTEXT，。应该首先导入所有数据，然后再修改表，定义FULLTEXT，这样可以更快导入数据。

使用全文搜索：

```mysql
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('shoe'); #Match指定搜索列，Against指定搜索词
```

返回结果：`nike shoes is good`，搜索不区分大小写。

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

```mysql
SELECT note_text,
	Match(note_text) Against('shoes') AS rank #等级由MySQL根据行中词的数目、唯一词的数目、整个索引中词的总数以及包含该词的行的数目计算出来。
FROM productnotes;
```

全文搜索数据是有索引的，速度快。



# 表操作

## 创建表

```mysql
CREATE TABLE productnotes
(
	note_id int NOT NULL AUTO_INCREMENT,
    note_text text NULL,
    quanlity int NOT NULL DEFAULT 1, # 默认值，只支持常量
    PRIMARY KEY(note_id),
    FULLTEXT(note_text)
) ENGINE=MyISAM;
```

主键中只能使用NOT NULL值的列。

## 更新表

数据库表的更改不能撤销，应先做好备份。

添加列：

```mysql
ALTER TABLE vendors
ADD vend_phone CHAR(20);
```

删除列：

```mysql
ALTER TABLE vendors
DROP COLUMN vend_phone;
```

更改列属性：

```mysql
ALTER TABLE vendors
MODIFY vend_phone CHAR(16);
```

复杂的表结构更改一般需要手动删除过程：

- 用新的列布局创建一个新表；

- 使用INSERT SELECT语句，从旧表复制数据到新表；

- 检验包含所需数据的新表；
- 重命名旧表（如果确定，可以删除它）；

- 用旧表原来的名字重命名新表；

- 重新创建触发器、存储过程、索引和外键。

## 约束

添加主键约束：

```mysql
ALTER TABLE vendors
ADD CONSTRAINT pk_vendors PRIMARY KEY(vend_id);
```

删除主键约束：

```mysql
ALTER TABLE vendors
DROP PRIMARY KEY;
```

添加外键约束：

```mysql
ALTER TABLE products
ADD FOREIGN KEY(vendor_id) REFERENCES vendors(vendor_id);
```

删除外键约束：

```mysql
ALTER TABLE products DROP FOREIGN KEY vendor_id;
```

## 删除表

`DROP TABLE cumstomers`

## truncate、delete与drop区别

**相同点：**

1. truncate和不带where子句的delete、以及drop都会删除表内的数据。

2. drop、truncate都是DDL语句(数据定义语言)，执行后会自动提交。

**不同点：**

1. truncate 和 delete 只删除数据不删除表的结构；drop 语句将删除表的结构被依赖的约束、触发器、索引；

2. 速度，一般来说: drop> truncate > delete。

## 重命名表

`RENAME TABLE cusmtomers TO cust`



# 列操作

## 插入数据

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

```mysql
INSERT INTO customers(cust_name, cust_city)
	VALUES('Tyson', 'GD'),
		('sophia','GZ');
```

INSERT操作可能很耗时（特别是有很多索引需要更新时），而且它可能降低等待处理的SELECT语句的性能。降低INSERT语句的优先级：`INSERT LOW_PRIORITY INTO`

## 更新数据

如果用UPDATE语句更新多行，并且在更新这些行中的一行或多行时出一个现错误，则整个UPDATE操作被取消。为了在发生错误时也继续进行更新，可使用IGNORE关键字：`UPDATE IGNORE customers...`

```mysql
UPDATE customers
SET cust_city = NULL
WHERE cust_id = 1005;
```

返回值是受影响的记录数。

## 删除数据

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

```mysql
DELETE FROM customers
WHERE cust_id = 1006;
```

如果执行DELETE语句而不带WHERE子句，表的所有数据都将被删除。MySQL没有撤销操作，应该非常小心地使用UPDATE和DELETE。

delete使用别名的时候，要在delete和from间加上删除表的别名。

```mysql
DELETE a #加上删除表的别名
FROM table1 a
WHERE a.status = 0
AND EXISTS
	(SELECT b.id FROM table2 b
    WHERE b.id = a.id);
```



# 引擎

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

MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）；

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

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



# 视图

视图为虚拟的表。视图提供了一种MySQL的SELECT语句层次的封装，可用来简化数据处理以及重新格式化基础数据或保护基础数据。
## 应用

- 重用SQL语句。
- 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
- 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

## 限制

- 与表一样，视图必须唯一命名
- 视图不能索引，也不能有关联的触发器或默认值。
- 视图可以和表一起使用。例如，编写一条联结表和视图的SELECT语句。
- ORDER BY可以用在视图中，但如果从该视图检索数据SELECT中也含有ORDER BY，那么该视图中的ORDER BY将被覆盖。

## 语法

`CREATE VIEW`：创建视图

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

`DROP VIEW viewname`：删除视图

`CREATE ORREPLACE VIEW`：更新视图，相当于先用`DROP`再用`CREATE`
## 简化复杂连接
创建一个视图，返回订购了任意产品的客户列表。
```mysql
CREATE VIEW productcustomers AS
SELECT cust_name, orders, orderitems
FROM customers, orders, orderitems
WHERE orderitems.order_num = orders.order_num
	AND customers.cust_id = orders.cust_id;
```
使用视图：
```mysql
SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'nike';
```
## 更新视图
对视图增加或删除行，实际上是对其基表增加或删除行。视图主要用于数据检索。




# 存储过程

为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件。
为什么使用存储过程：

- 把复杂处理进行封装，简化复杂的操作；
- 提高性能，存储过程比单独SQL语句更快；
## 创建
返回产品平均价格的存储过程：
```mysql
CREATE PROCEDURE productpricing() # 可以接受参数
BEGIN
	SELECT Avg(prod_price) AS priceaverage
	FROM products;
END;
```
BEGIN/END 用来限定存储过程体。此段代码仅创建了存储过程，未执行。
## 调用
`CALL productpricing()`
## 删除
存储过程在创建之后，被保存在服务器上以供使用，直至被删除。
`DROP PROCEDURE productpricing IF EXISTS`
## 参数
MySQL支持IN（传递给存储过程）、OUT（从存储过程传出）和INOUT（对存储过程传入和传出）类型的参数。
接受订单号并返回该订单的金额：
```mysql
CREATE PROCEDURE ordertotal(
	IN ordernum INT,
	OUT ordersum DECIMAL(8, 2)
)
BEGIN
	SELECT Sum(item_price * quantity)
	FROM orderitems
	WHERE order_num = ordernum
	INTO ordersum;
END;
```
调用存储过程：`CALL ordertotal(20, @total);`
显示订单金额：`SELECT @total;`
## 实例
获取订单税后金额（订单金额+税收）。
```mysql
CREATE PROCEDURE ordertotal(
	IN onum INT,
	IN taxable BOOLEAN, # 是否计税
	OUT ototal DECIMAL(8, 2)
) COMMENT 'order total, adding tax'
BEGIN
	DECLARE total DECIMAL(8, 2);
	DECLARE taxrate INT DEFAULT 6;

	SELECT Sum(item_price * quanlity)
	FROM orderitems
	WHERE order_num = onum
	INTO total;

	IF taxable THEN
		SELECT total + (total / 100 * taxrate) INTO total;
	END IF;
	-- SELECT total INTO ototal;
END;
```
调用存储过程：
```mysql
CALL ordertotal(20005, 1, @total);
SELECT @total;
```
## 查看
创建存储过程的 CREATE 语句。
```mysql
SHOW CREATE PROCEDURE ordertotal;
```
获得包括何时、由谁创建等详细信息的存储过程列表，使用`SHOW PROCEDURE STATUS LIKE 'ordertotal';`

查看存储过程状态：

```mysql
SHOW PROCEDURE status;
```

# 游标
存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。MySQL游标只能用于存储过程（和函数）。
## 创建游标
DECLARE 命名游标。存储过程处理完成后，游标便消失（游标只存在于存储过程）。定义游标之后，便可以打开它。
```mysql
CREATE PROCEDURE processorders()
BEGIN
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
END;
```
## 使用游标
`OPEN ordernumbers` 打开游标。
`CLOSE ordernumbers` CLOSE释放游标使用的所有内部内存和资源。
```mysql
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);
	
	-- done为1结束循环
	UNTIL done END REPEAT;
	
	CLOSE ordernumbers;
END;
```
存储过程还在运行中创建了一个新表，。这个表将保存存储过程生成的结果。FETCH取每个order_num，然后用CALL执行另一个存储过程，计算每个订单税后金额。最后，用INSERT保存每个订单的订单号和金额。



# 触发器

触发器提供SQL语句自动执行的功能。DELETE/INSERT/UPDATE支持触发器，其他SQL语句不支持。
## 创建
创建触发器四要素：1.唯一的触发器名（MySQL5规定触发器名在表中唯一，数据库没要求）；2.触发器关联的表；3.相应的SQL语句；4.何时执行（处理之前或者之后）。
```mysql
CREATE TRIGGER newproduct AFTER INSERT ON products #插入之后执行
FOR EACH ROW SELECT 'product added'; #对每个插入行执行
```
只有表支持触发器，视图不支持。单一触发器不能与多个事件或多个表关联，如果需要对INSERT和UPDATE操作执行触发器，则应该定义两个触发器。
## 删除
`DROP TRIGGER newproduct`

## 使用触发器

INSERT 触发器可饮用名为 NEW  的虚拟表，访问被插入的行。NEW中的值也可以被更新（允许更改被插入的值）。

```mysql
CREATE TRIGGER neworder AFTER INSERT ON order
FOR EACH ROW SELECT NEW.order_num; #返回新的订单号
```

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

```mysql
CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
	INSERT INTO archive_orders(order_num, cust_id)
	VALUES(OLD.order_num, OLD.cust_id);
END;
```

订单删除之前保存订单信息到存档表。

UPDATE 触发器可以引用名为 OLD 的虚拟表访问以前的值，引用一个名为NEW的虚拟表访问新更新的值。NEW 值可被更新，OLD 值是只读的。

下面的例子保证州名缩写总是大写。

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



# 事务处理

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

CREATE/DROP 操作不能回退，即便可以执行回退操作，回退不会有效果。

执行事务过程，一旦某个SQL失败，则之前执行成功的SQL会被自动撤销。

## 语法

```mysql
START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;
```

当COMMIT或ROLLBACK语句执行后，事务会自动关闭。

## 保留点

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

保留点在事务处理完成后自动释放。

```mysql
...
SAVEPOINT delete1;
...
ROLLBACK TO delete1;
```



# 权限

MySQL用户账号和信息存储在名为mysql的MySQL数据库中。

获取用户账号列表：

```mysql
USE mysql;
SELECT user FROM user;
```

创建用户账号：`CREATE USER tyson IDENTIFIED BY 'abc123'`

修改密码：`SET PASSWORD FOR tyson = Password('xxx');`，新密码需传递到Password()函数进行加密。

设置当前用户密码：`SET PASSWORD = Password('xxx');`

重命名账号：`RENAME USER tyson TO tom`

删除用户账号：`DROP USER tyson`

查看访问权限：`SHOW GRANTS FOR tyson`，返回`USAGE ON *.*`则表示没有权限。

授予访问权限：`GRANT SELECT ON mall.# TO tyson`，允许用户在mall数据库所有表使用SELECT。

撤销权限：`REVOKE SELECT, INSERT ON mall.* FROM tyson`，被撤销的访问权限必须存在，否则会出错。

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

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



# 性能优化

使用EXPLAIN语句让MySQL解释它将如何执行一条SELECT语句。

如果一个简单的WHERE子句返回结果所花的时间太长，则可以断定其中使用的某些列就是需要索引的对象。

避免使用OR。通过使用多条SELECT语句和连接它们的UNION语句，会有极大的性能改进。

LIKE很慢，最好是使用FULLTEXT而不是LIKE。

很多高性能的应用都会对关联查询进行分解，有如下的优势：

1 、让缓存效率更高。如果某张表很少变化，那么基于该表的查询就可以重复利用查询缓存结果。
2 、将查询分解后，执行单个查询可以减少锁的竞争。
3 、在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
4 、查询本身效率也可能会有所提升。例如 IN()代替关联查询，可能比随机的关联更高效。
5 、减少冗余记录得查询。
6 、更进一步，这样做相当于在应用中实现了哈希关联，而不是使用 MySQL 得嵌套循环关联。某些场景哈希关联得效率要高很多。



# 索引

## 创建索引

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

```mysql
ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)
```

CREATE INDEX可对表增加普通索引或UNIQUE索引。

```mysql
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
```

在创建索引时，可以规定索引能否包含重复值。如果不包含，则索引应该创建为PRIMARY KEY或UNIQUE索引。

## 删除索引

```mysql
DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY #只有一个主键，不需要指定索引名
```

## 查看索引

```mysql
show index from tblname;
show keys from tblname;
```