DBA常用的MySQL语句

SQL分类

----	DDL	DML	DCL	TCL
全称	Data Definition Language	Data Manipulation Language	Data Control Language	Transaction Control Language
中文	数据库定义语言	数据操纵语言	数据控制语言	事务控制语言
功能	定义数据库的三级模式及其相互之间的映像	用于让用户或程序员使用，实现对数据库中数据的操作。	授权，角色控制等	事务控制
举例	​​CREATE​​​​ALTER​​ ​​DROP​​ ​​TRUNCATE​​ ​​COMMENT​​ ​​RENAME​​	​​SELECT​​​​INSERT​​ ​​UPDATE​​ ​​DELETE​​ ​​MERGE​​ ​​CALL​​ ​​EXPLAIN PLAN​​ ​​LOCK TABLE​​ 锁，用于控制并发	​​GRANT​​ 授权 ​​REVOKE​​ 取消授权	​​SAVEPOINT​​ 设置保存点 ​​ROLLBACK​​ 回滚 ​​SET TRANSACTION​​
​​commit​​	不需要，隐式提交	需要	不需要，隐式提交	需要

逻辑运算

与运算 a & b  , 
或运算 a | b ,  
异或运算 a ^ b ,
 
或者 
你也可以将 与运算理解为 + 法  
例如 
1|2 = 3   （1+2 = 3）
1|2|4 = 7 （1+2+4 = 7）
 
将 异或运算理解为 - 法
例如 
3^2 = 1 （3-2 = 1）
3^1 = 2  （3-1 = 2）
 
最后将 与运算 作为判断
例如 
3&2 = 1    (3 = 1 + 2, 由 1和2组成 ，所以判断3&2 = 1 )  
3&4 = 0   ( 3 没有由 4组成,所以判断3&4 = 0)

##连接

mysql -h127.0.0.1 -uroot -p123;

增加用户

GRANT SELECT,INSERT,UPDATE,DELETE ON MYDB.* 
    TO test2@localhosttest2@localhost IDENTIFIED BY "abc";
SET PASSWORD FOR '数据库名'@'localhost' = OLD_PASSWORD('密码');

数据库操作

-- 删除数据库
CREATE DATABASE db1;

-- 查看所有数据库
SHOW DATABASES;

-- 删除数据库
DROP DATABASE db1;

-- 使用（选中）某个数据库
USE db1;

表操作

-- 查看当前选择数据库
SELECT DATABASE();

-- 新建表
CREATE TABLE tb1(
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(32) NOT NULL COMMENT '标题',
  `source` VARCHAR(256) DEFAULT NULL COMMENT '来源',
  `content` TEXT COMMENT '内容',
  `createtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 查看表机构
SHOW COLUMNS FROM tb1;
DESC tb1;

-- 删除表
DROP TABLE tb1;

-- 查看所有表
SHOW TABLES;

-- 插入记录
INSERT INTO tb1 (`id`, `title`, `source`, `content`)
 VALUES (1, '标题1', '互联网', '内容1');

INSERT INTO tb1 VALUES
(2, '标题2', '互联网', '内容2', NULL),
(3, '标题3', '互联网', '内容3', NULL);

-- 查询记录
SELECT `id`, `title`, `content`, `createtime` FROM `tb1` ORDER BY id DESC LIMIT 0,2;
SELECT * FROM tb1 WHERE ID > 1;
SELECT * FROM tb1 WHERE SOURCE IS NULL;
SELECT title FROM tb1 WHERE title LIKE '%1';
SELECT * FROM tb1 WHERE id BETWEEN 1 AND 3;
SELECT count(id) FROM tb1 WHERE id > 1 GROUP BY 

/*
+----+-------+---------+---------------------+
| id | title | content | createtime          |
+----+-------+---------+---------------------+
|  1 | 标题1 | 内容1   | 2017-04-26 19:07:54 |
|  2 | 标题2 | 内容2   | 2017-04-26 19:09:03 |
|  3 | 标题3 | 内容3   | 2017-04-26 19:09:03 |
+----+-------+---------+---------------------+
*/

-- 拼接字符串
SELECT CONCAT(title, '(', id, ')') AS ti FROM tb1;
/*
+----------+
| ti       |
+----------+
| 标题1(1) |
| 标题2(2) |
| 标题3(3) |
+----------+
*/

-- 替换字符串
-- 把'标题'替换为'title'
UPDATE tb1 SET title=REPLACE(title, '标题', 'title');

-- N天内记录
-- date_col是DATETIME格式
SELECT something FROM tbl_name WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30;
-- TO_DAYS(date) 给定一个日期date, 返回一个天数 (从年份0开始的天数 )

-- TIMESTAMP转换成DATETIME
SELECT FROM_UNIXTIME(1492673017);
-- 2017-04-20 15:23:37

-- DATETIME转换成TIMESTAMP
SELECT UNIX_TIMESTAMP('2017-04-20 15:23:37');
-- 1492673017

-- DATETIME格式化
SELECT DATE_FORMAT('2017-06-07 12:11:10', '%Y-%m-%d');
-- 2017-06-07

-- 内关联，两张表同时满足ON条件
SELECT tb1.title, tb2.title FROM tb1 INNER JOIN tb2 ON tb1.id=tb2.id WHERE title IS NOT NULL;

-- 外关联(左外连接和右外连接)
-- 左外连接的结果集包括 LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行，如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值
SELECT tb1.title, tb2.title FROM tb1 LEFT JOIN tb2 ON tb1.id=tb2.id WHERE title IS NOT NULL;

-- 子查询（嵌套在其他查询中的查询）
select * from items where price > (select AVG(price) from items);
select username ,  address from user where id not in (select user_id from orders);
select * from user where exists (select * from orders where user.id=orders.user_id);

-- 组合查询（把多个select查询语句的结果相结合）
select id , name ,price  from items where price < 3000
 union
 select id , name ,price from items where id in (1,2,3);


-- 更新记录
UPDATE tb1 SET title='title1', content='content1' WHERE id=1;

-- 删除记录
DELETE FROM tb1 WHERE id=1;

-- 清空表
TRUNCATE tb1;

-- 重命名
-- 从tb1改名为tb2
ALTER TABLE `tb1` RENAME `tb2`;

-- 增加字段
ALTER TABLE tb1 ADD f1 INT(4) DEFAULT '0';

-- 增加索引
ALTER TABLE tb1 ADD INDEX emp_name (name);

-- 增加主键
ALTER TABLE tb1 ADD PRIMARY KEY(id);

-- 增加唯一索引
ALTER TABLE tb1 ADD UNIQUE emp_name2(cardnumber);

-- 删除索引
ALTER TABLE tb1 DROP index emp_name;

-- 修改字段名称及类型
ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;

-- 修改字段类型
ALTER TABLE table_name MODIFY colum_name field_type new_type

-- 删除字段
ALTER TABLE table_name DROP field_name;

-- 表复制
create table new_user select * from user;

-- 仅复制表结构
create table new_user_no_data like user;

-- 复制数据
insert into  new_user_no_data select * from user;

导出

# 导出整个数据库
mysqldump -h localhost -u root -p mydb >e:\mysql\mydb.sql

# 导出一个数据表
mysqldump -h localhost -u root -p mydb mytable>e:\mysql\mytable.sql 

# 导出数据结构
mysqldump -h localhost -u root -p mydb –add-drop-table >e:\mysql\mydb_stru.sql

导入

-- 创建数据库mydb2
CREATE DATABASE mydb2;

exit;

# 开始导入
mysql -h localhost -u root -p mydb2 <

视图

一张虚拟的表，减少重复写一堆sql查询的麻烦。

创建视图

语法：

CREATE VIEW 视图名(列名,…) AS SELECT 语句

CREATE VIEW tb_view AS SELECT * FROM tb1;

删除视图

DROP VIEW 视图名称

索引

减少全表扫描，提高查询效率。

单列索引

-- 新建索引
CREATE INDEX index_name ON tbl_name(index_col_name);

-- 修改索引
ALTER TABLE tbl_name ADD INDEX index_name ON (index_col_name);

-- 删除索引
DROP INDEX index_name ON user;

-- 查看索引
show index from user;

复合索引

复合索引：复合索引是在多个字段上创建的索引。复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用。因此，在复合索引中索引列的顺序至关重要。

-- 创建复合索引
create index index_pinyin on user(username,pinyin);

唯一索引

创建唯一索引必须指定关键字UNIQUE，唯一索引和单列索引类似，主要的区别在于，唯一索引限制列的值必须唯一，但允许有空值。对于多个字段，唯一索引规定列值的组合必须唯一。

-- 创建唯一索引
CREATE UNIQUE INDEX index_name ON tbl_name(index_col_name[,...]);

-- 添加（通过修改表结构）
ALTER TABLE tbl_name ADD UNIQUE INDEX index_name ON (index_col_name[,...]);


-- 创建表时直接指定
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`name` varchar(32)  NOT NULL ,
......  -- 其他字段
PRIMARY KEY (`id`),
UNIQUE indexName (name(32)) 
);

主键索引

主键索引也称丛生索引，是一种特殊的唯一索引，不允许有空值。创建主键索引语法如下：

ALTER TABLE tbl_name ADD PRIMARY KEY(index_col_name);

索引的设计

• where子句中的列可能最适合做为索引
• 不要尝试为性别或者有无这类字段等建立索引(因为类似性别的列，一般只含有“0”和“1”，无论搜索结果如何都会大约得出一半的数据)
• 如果创建复合索引，要遵守最左前缀法则。即查询从索引的最左前列开始，并且不跳过索引中的列
• 不要过度使用索引。每一次的更新，删除，插入都会维护该表的索引，更多的索引意味着占用更多的空间
• 使用InnoDB存储引擎时，记录(行)默认会按照一定的顺序存储，如果已定义主键，则按照主键顺序存储，由于普通索引都会保存主键的键值，因此主键应尽可能的选择较短的数据类型，以便节省存储空间
• 不要尝试在索引列上使用函数。

存储过程

存储过程就是数据库中保存的一系列SQL命令的集合，也就是说通过存储过程就可以编写流程语句，如循环操作语句等。

存储过程的创建与使用

CREATE PROCEDURE 存储过程名称(  参数的种类1 参数1 数据类型1
                            [,参数的种类2 参数2 数据类型2])
                            BEGIN
                                处理内容
                            END

存储过程的名称可以自由定义，但不可与存在的函数或存储过程名称重复，命名时建议以【sp_】开头，需要处理的内容则编辑在BEGIN和END之间。参数的种类分3种，分别是IN、OUT、INOUT，其中IN为输入参数类型，OUT为输出参数类型，而INOUT既是输入类型又是输出类型，下面我们创建一个存储过程，以达到对user表的用户名称进行模糊查询的目的，存储过程名称为sp_search_user：

-- 改变分隔符
DELIMITER //
-- 创建存储过程
create procedure sp_search_user (in name varchar(20));
    begin
    if name is null or name='' then
       select * from user;
    else 
       select * from user where username like name;
    end if;
    end
    // -- 执行sql
DELIMITER ; -- 恢复分隔符

其中DELIMITER可以用于改变分隔符，由于存储过程中流程语句需要使用分号结尾与mysql命令行的sql语句结尾的分号冲突，于是改变分隔符为//，执行完存储过程后再恢复为分号即可。从存储过程创建语句中，我们设置一个name的输出参数语句并在begin与end之间编写了流程语句，当名称为空时查询所有用户否则按传入的条件查询。现在可以使用该存储过程了，调用语法如下：

CALL 存储过程名称(参数，....)

执行已创建存储函数

-- name传入null值，查询所有用户。
call sp_search_user(null);

-- 查询以name以任开头的用户
call sp_search_user('任%');

输入输出参数类型

创建一个存储过程，用于返回商品的最大值、最小值和平均值，命名为sp_item_price

DELIMITER //
-- 创建存储过程
create procedure sp_item_price(out plow decimal(8,2),
                                out phigh decimal(8,2),
                                out pavg decimal(8,2)
                                )
 begin
   select min(price) into plow from items;
   select max(price) into phigh from items;
   select avg(price) into pavg from items;
 end;
 //
-- 恢复分隔符
DELIMITER ;
-- 调用存储过程
call sp_item_price(@pricelow,@pricehigh,@priceavg);

-- 查询执行结果
select @pricelow ;
select @pricehigh ;
select @priceavg ;

正如我们所看到的，创建sp_item_price时，使用了3个out参数，在存储过程内部将会把执行结果分别存入这个三个变量中，存入关键字使用的是into，完成存储过程创建后，使用call sp_item_price(@pricelow,@pricehigh,@priceavg);调用sp_item_price，传入用于存储返回值的3个变量，注意mysql的用户变量必须以@开头，名称可自定义，但不能重复，调用完成后语句并没有显示任何数据，因为这些数据都存入了@pricelow,@pricehigh,@priceavg 三个变量中，我们可以使用select操作符查询这些变量。这里有点要明白的，上述存储过程中使用对输出变量的赋值语句：

SELECT 列名1，... INTO 变量名1，... FROM 表名 WHERE 语句等...

请注意如果检索出多个列名，与之对应的赋值变量也必须有多个。ok，对输入输出参数类型有了清晰的了解后，为了加深理解，我们创建一个同时存在输入输出参数的存储过程，输入订单号，计算该订单的订单总价，名称为sp_order_sum_price

DELIMITER //
-- 创建存储过程
create procedure sp_order_sum_price(in number int ,out ptotal decimal(8,2)) 
 begin 
  select sum(price * items_num) from items inner join orderdetail as od  on items.id = od.items_id 
  where od.orders_id=number 
  into ptotal; --放到语句后面也可行
 end;
 //
-- 恢复分隔符
DELIMITER ;

-- 执行存储过程
call sp_order_sum_price(3,@priceTotal)；

-- 查询结果
select @priceTotal；

删除存储过程

DROP PROCEDURE [IF EXISTS] 存储过程名称;

查看存储过程的状态

SHOW PROCEDURE STATUS [LIKE 'pattern']

查看存储过程的创建语句

SHOW CREATE PROCEDURE 存储过程名;

存储过程的流程控制语句

IF 条件语句

IF 条件表达式1 THEN
    条件表达式1为true执行
[ELSEIF 条件表达式2 THEN
    条件表达式2为true执行
 ]
 [ELSE
      全部条件为false时执行]
 END IF；

多分支条件语句

CASE 表达式1
    WHEN 值1 THEN  表达式=值1时执行命令
    [WHEN 值N THEN  表达式=值N时执行该语句]
    [ELSE 上述值以外执行该语句]
END CASE

repeat 循环控制语句

REPEAT 
   直至条件表达式为True时执行的语句
UNTIL 条件表达式 END REPEAT;

while循环控制语句
while 循环语句与repeat循环控制语句的区别是前者条件不符合一次循环体都不会执行，而后者无论条件是否符合，至少执行一次循环体

WHILE 条件表达式 DO 
   系列语句
END WHILE

定义变量

使用DECLARE定义局部变量

DECLARE 变量名[,变量名2...] 数据类型(type) [DEFAULT value];

-- 定义变量num，数据类型为INT型，默认值为10
DECLARE  num  INT  DEFAULT 10 ;

其中， DECLARE关键字是用来声明变量的；变量名即变量的名称，这里可以同时定义多个变量；type参数用来指定变量的类型；DEFAULT value子句将变量默认值设置为value，没有使用DEFAULT子句时，默认值为NULL。声明后，我们就可以在存储过程使用该变量，设置变量值可以使用以下语法：

SET 变量名1 = expr [, 变量名2 = expr] ...

其中，SET关键字是用来为变量赋值的；expr参数是赋值表达式或某个值。一个SET语句可以同时为多个变量赋值，各个变量的赋值语句之间用逗号隔开。除了这种赋值方式，前面我们还提到过使用SELECT…INTO语句为变量赋值，那也是可行的。

了解其他类型的变量

• 用户变量：以”@”开始，形式为”@变量名”，用户变量跟mysql客户端是绑定的，设置的变量，只对当前用户使用的客户端生效，声明或者定义用户变量使用set语句，如 set @var 若没有指定GLOBAL 或SESSION ，那么默认将会定义用户变量。
• 全局变量：定义时，以如下两种形式出现，set GLOBAL 变量名 或者 set @@global.name，对所有客户端生效。只有具有super权限才可以设置全局变量。如下：

SET GLOBAL sort_buffer_size=value;
SET @@global.sort_buffer_size=value;

• 会话变量：只对连接的客户端有效。

SET SESSION sort_buffer_size=value;

使用DECLARE 定义条件和处理程序

定义条件和处理程序是事先定义程序执行过程中可能遇到的问题，并且可以在处理程序中定义解决这些问题的办法，可以简单理解为异常处理，这种方式可以提前预测可能出现的问题，并提出解决办法，从而增强程序健壮性，避免程序异常停止。MySQL通过DECLARE关键字来定义条件和处理程序。

定义条件
MySQL中可以使用DECLARE关键字来定义条件。其基本语法如下：

-- 条件定义语法
DECLARE  condition_name  CONDITION  FOR  condition_value  

-- condition_value的定义格式
SQLSTATE [VALUE] sqlstate_value |

其中，condition_name表示条件的名称，condition_value参数表示条件的类型；sqlstate_value参数和mysql_error_code参数都可以表示MySQL的错误。如常见的ERROR 1146 (42S02)中，sqlstate_value值是42S02，mysql_error_code值是1146，简单案例如下：

-- 定义主键重复错误
-- ERROR 1062 (23000): Duplicate entry '60' for key 'PRIMARY'

-- 方法一：使用sqlstate_value  
DECLARE  primary_key_duplicate  CONDITION  FOR  SQLSTATE  '23000' ;  

-- 方法二：使用mysql_error_code  
DECLARE primary_key_duplicate CONDITION  FOR  1062 ;

定义处理程序
前面定义的处理条件，可以在定义处理程序中使用,先了解一下定义语法：

DECLARE handler_type HANDLER FOR 
condition_value[,...]

handler_type 参数的取值有三种：CONTINUE | EXIT | UNDO。

• CONTINUE 表示遇到错误不进行处理，继续向下执行；
• EXIT 表示遇到错误后马上退出；
• UNDO 表示遇到错误后撤回之前的操作，但MySQL中暂时还不支持这种处理方式。

我们需要注意的是，大多数情况下，执行过程中遇到错误应该立刻停止执行下面的语句，并且撤回前面的操作。由于MySQL目前并不支持UNDO操作。所以，遇到错误时最好执行EXIT操作。如果事先能够预测错误类型，并且进行相应的处理，那么就选择CONTINUE操作。

condition_value 参数指明错误类型，该参数有6个取值。语法如下：

-- condition_value的取值： 
SQLSTATE [VALUE] sqlstate_value |
mysql_error_code                |
condition_name                  | 
SQLWARNING                      | 
SQLEXCEPTION                    |

• sqlstate_value参数和mysql_error_code参数都可以表示MySQL的错误。如常见的ERROR 1146 (42S02)中，sqlstate_value值是42S02，mysql_error_code值是1146。与条件中参数是一样的。
• condition_name是DECLARE定义的条件名称，就前面定义条件语句
• NOT FOUND表示所有以02开头的sqlstate_value值。
• SQLEXCEPTION表示所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值。
  sp_statement 参数表示要执行存储过程或函数语句。

以下定义了如何捕获和处理异常的简单例子

-- 捕获sqlstate_value值。如果遇到sqlstate_value值为42S02，执行CONTINUE操作，并且设置用户变量info。  
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='CAN NOT FIND';  

-- 捕获mysql_error_code，如果遇到mysql_error_code值为1146，执行CONTINUE操作，并且设置用户变量info。  
DECLARE CONTINUE HANDLER FOR 1146 SET @info='CAN NOT FIND';  

-- 先定义条件，然后定义处理程序调用  
DECLARE  can_not_find  CONDITION  FOR  1146 ;  
-- 定义处理程序，并使用定义的can_not_find条件
DECLARE CONTINUE HANDLER FOR can_not_find SET @info='CAN NOT FIND';  

-- SQLWARNING捕获所有以01开头的sqlstate_value值，然后执行EXIT 
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';  

-- NOT FOUND捕获所有以02开头的sqlstate_value值，然后执行EXIT操作，并且输出"CAN NOT FIND"信息
DECLARE EXIT HANDLER FOR NOT FOUND SET @info='CAN NOT FIND';  

-- SQLEXCEPTION捕获所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值，然后执行EXIT操作。  
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

为了加深理解，下面我们编写一个存储过程用于添加用户，借此来了解定义处理程序的作用，如下：

mysql > DELIMITER //
create procedure sp_insert_user()
 begin 
   set @n=1; -- 设置用户变量，用于标识程序运行到哪里一步停止
   insert into user value(60,'小米','xiaomi',null,1,null);
   set @n=2;
   insert into user value(61,'小米2','xiaomi2',null,1,null);
   set @n=3;
 end 
 //
mysql > DELIMITER ; 
-- 执行存储过程
call sp_insert_user();
-- 报错，因为主键60的用户已存在
ERROR 1062 (23000): Duplicate entry '60' for key 'PRIMARY'
-- 查询标识，显然在第一步时遇到错误就停止了执行
select @n ;

上述程序在执行完set @n=1;后就出错了，因为出现了重复的主键值，也就直接导致后面的程序也无法执行，现在我们编写一个处理程序，使用存储过程中即使出现2300错误也继续执行，如下：

create procedure insert_user_2()
 begin
         -- 定义条件
   DECLARE  primary_key_exist  CONDITION  SQLSTATE '23000'  ;  
         -- 定义处理程序,出现2300错误继续执行，@m用于标识
   DECLARE CONTINUE HANDLER FOR primary_key_exist SET @m = 1000;
   set @n=1;
   insert into user value(60,'小米','xiaomi',null,1,null);
   set @n=2;
   insert into user value(61,'小米2','xiaomi2',null,1,null);
   set @n=3;
 end 
 //
DELIMITER ; 
-- 执行，并没有报错
call insert_user_2();

-- 查询标识
select @n;
select @m;

从程序可以看出即使出现主键重复错误，但由于我们进行捕获并处理使得整个存储过程的程序可以执行完成。

构建复杂的存储过程(案例)
获取一个订单的总价，并判断是否需要营业税收，案例如下：

DELIMITER //
create procedure sp_ordertotal( in onnumber int,in taxable boolean ,out ototal decimal(8,2) )
 begin
 -- 定义变量：总价
 declare total decimal(8,2);
 -- 定义默认税收率
 declare taxrate int default 6;
 -- 关联查询并计算总价
 select sum( price * items_num) from orderdetail  as od 
  inner join items as it  on it.id=od.items_id
  where od.orders_id = onnumber 
  into total; --赋值
 -- 判断是否需要营业税收
 if taxable then 
  select total + (total/100 * taxrate) into total;
 end if;
 -- 赋值给输出参数
 select total into ototal;
 end
 //
Query OK, 0 rows affected (0.01 sec)
DELIMITER ; 

-- 执行存储过程
call sp_ordertotal(3,false,@total)//

-- 查询总价
select @total //

存储函数

创建存储函数

存储函数与存储过程有些类似，简单来说就是封装一段sql代码，完成一种特定的功能，并返回结果。
与存储过程不同的是，存储函数中不能指定输出参数(OUT)和输入输出参数(INOUT)类型。存储函数只能指定输入类型而且不能带IN。同时存储函数可以通过RETURN命令将处理的结果返回给调用方。注意必须在参数列表后的RETURNS（ 该值的RETURNS多个S，务必留意）命令中预先指定返回值的类型。如下创建一个计算斐波那契数列的函数

-- 创建存储函数
create function fn_factorial(num int) returns int
 begin
  declare result int default 1;
  while num > 1 do 
    set result = result * num ;
    set num = num -1 ;
  end while;
  return result;
 end
 //

-- 使用select 执行存储函数 
select fn_factorial(5);

select fn_factorial(0),fn_factorial(5),fn_factorial(10);

这里命名存储函数时使用了【fn_】作为开头，这样可以更容易区分与【sp_】开头的存储过程，从上述语句可以看出前面在存储过程分析的流程语句也是可以用于存储函数的，同样的，DECLARE声明变量和SET设置变量也可用于存储函数，当然包括定义异常处理语句也是适应的,请注意执行存储函数使用的是select关键字，可同时执行多个存储函数，嗯，存储函数就这样定义，是不是跟存储过程很相似呢？但还是有区别的，这点留到后面分析。ok~，为了进一步熟悉存储函数，下面编写一个用于向user插入用户的存储函数：

-- 创建存储函数fn_insert_user
create function fn_insert_user(name varchar(32),sex char(1)) returns int 
begin   
insert into user (username,pinyin,birthday,sex,address) values(name,null,null,sex,null);  
return LAST_INSERT_ID(); --返回最后插入的ID值
end//
Query OK, 0 rows affected (0.00 sec)
DELIMITER ; 

-- 执行存储函数
select fn_insert_user('xiaolong',1);


-- 查询已插入的数据
select * from user where id = 101;

显然数据插入成功了，其中 LAST_INSERT_ID()会返回最后插入的ID值，这里我们仅作为演示，因为实际开发中，我们一般更倾向于使用存储函数执行查询操作或者是数据的处理操作，对于更新插入删除这样的操作，使用较少。

删除存储函数

DROP FUNCTION [IF EXISTS] fn_name;

存储过程与存储函数的区别

• 存储过程可以有多个in,out,inout参数，而存储函数只有输入参数类型，而且不能带in
• 存储过程实现的功能要复杂一些；而存储函数的单一功能性(针对性)更强。
• 存储过程可以返回多个值；存储函数只能有一个返回值。
• 存储过程一般独立的来执行；而存储函数可以作为其他SQL语句的组成部分来出现。
• 存储过程可以调用存储函数。但函数不能调用存储过程。

触发器

触发器可以简单理解一种特殊的存储过程，之前存储过程的变量定义及流程语句同样适合触发器，唯一不同的是我们只需要定义触发器，而不用手动调用触发器。从事件触发的角度来说，触发器编写的过程就是触发事件定义的过程，因为触发器定义好后会随着数据库操作命令的执行而触发，这些具体的操作是INSERT/UPDATE/DELETE。比如可以在user表中删除记录执行后，通过定义一个触发器把删除的数据自动添加到历史表中保存以便以后可以进行其他操作。创建触发器的语法如下：

CREATE TRIGGER trigger_name trigger_time
trigger_event ON tbl_name
FOR EACH ROW
BEGIN
trigger_stmt
END

其中：

• trigger_name：触发器名称，用户自行指定；
• trigger_time：触发时机，取值为 BEFORE 或 AFTER；
• trigger_event：触发事件，取值为 INSERT、UPDATE 或 DELETE；需要注意的是这些操作命令并不一定严格意义上的命令，因为像 LOAD DATA 和 REPLACE 语句也能触发上述事件。LOAD DATA 语句用于将一个文件装入到一个数据表中，是一系列的 INSERT 操作。REPLACE 语句类似INSERT 语句，当表中有 primary key 或 unique 索引时，如果插入的数据和原来 primary key 或 unique 索引一致时，会先删除原来的数据，然后增加一条新数据，也就是说，一条 REPLACE 语句会等价于一条INSERT 语句或者一条 DELETE 语句和上一条 INSERT 语句。
• tbl_name：表示在哪张表上建立触发器；
• trigger_stmt：触发器程序体，可以是一句SQL语句或者流程语句
• FOR EACH ROW ： 在mysql中属于固定写法，指明触发器以行作为执行单位，也就是当用户执行删除命令删除3条数据，与删除动作相关的触发器也会被执行3次。

创建触发器

在日常的数据库开发中，因业务需求，可能需要在插入更新删除时留下数据的日志，这时采用触发器来实现是个非常不错的选择，下面我们定义一个用户删除事件的触发器，当用户被删除后自动把被删除的数据添加到用户历史表user_history，历史用户表结构如下：

DELIMITER //
-- 创建触发器
create trigger trg_user_history after delete
   on user for each row
 begin
  insert into user_history(uid,name,pinyin,birth,sex,address,updated)
  values(OLD.id,OLD.name,OLD.pinyin,OLD.birth,OLD.sex,OLD.address,NOW());
 end
 //
Query OK, 0 rows affected (0.02 sec)
DELIMITER ;

上述sql中创建语句的形式与前面的存储过程或者存储函数都很类似，这里有点要注意的是，使用OLD/NEW关键字可以获取数据变更前后的记录，其中OLD用于AFTER时刻，而NEW用于BEFORE时刻的变更。如OLD.name表示从user表删除的记录的名称。INSERT操作一般使用NEW关键字，UPDATE操作一般使用NEW和OLD，而DELETE操作一般使用OLD。现在我们从user表删除一条数据，然后查看user_history表的数据。

delete from user where id =60;
select * from user_history;

查看触发器

SHOW TRIGGERS [FROM schema_name];

其中，schema_name 即 Schema 的名称，在 MySQL 中 Schema 和 Database 是一样的，Schema指定为数据库名即可。

SHOW TRIGGERS \G;

删除触发器

DROP TRIGGER

游标

在前面的分析中可知sql的检索操作返回的数据几乎都是以整个集合的形式，也就是说sql善于将多条查询记录集中到一起并返回，倘若现在需要一行行地处理查询的结果，这对于sql语句来说确实是个难题，好在存在一种称为游标的技术可以解决这个问题，所谓的游标就就是可以将检索出来的数据集合保存在内存中然后依次取出每条数据进行处理，这样就解决了sql语句无法进行行记录处理的难题。

其中有个指针的概念，指针指明了当前行记录的信息，在游标的处理过程中通过移动指针进行逐行读取数据。要明白的是，游标一般结合存储过程或存储函数或触发器进行使用，ok~，理解了游标的概念后，看看其定义语法

-- 声明游标
DECLARE cursor_name CURSOR FOR SELECT 语句;

-- 打开游标
OPEN cursor_name;

-- 从游标指针中获取数据
FETCH cursor_name INTO 变量名 [,变量名2,...];

--关闭游标
CLOSE

在使用游标前需要对其进行声明，其中cursor_name表示游标名，CURSOR FOR是固定写法，SELECT 是检索语句，把检索出来的数据存放到游标中等待处理。下面我们通过一个案例演示并理解游标的使用

DELIMITER //
-- 创建存储过程
create procedure sp_cursor(out result text)
 begin 
  declare flag bit default 0;--定义标识变量用于判断是否退出循环
  declare tmp varchar(20);-- 定义临时存储变量
  declare cur cursor for select distinct name from user where id < 20;-- 声明游标
  declare continue handler for not found set flag = 1; --异常处理并设置flag=1
  open cur; -- 打开游标
  while flag!=1 do 
    fetch cur into tmp ; --从游标中取值并存放到tmp中
    if flag !=1 then
       set result = concat_ws(',',result,tmp); --拼接每次获取的结果
    end if;
  end while;
  close cur; --关闭游标
 end
 //

DELIMITER ;
--执行
call sp_user_cursor(@result);
-- 查询结果
select @result;

上述的存储过程是用于查询出id小于20的用户名称，并拼接成一个以逗号隔开的字符串输出。我们声明了一个flag的变量用于标识是否结束while循环，同时也声明了tmp变量用于存储每次从游标中获取的行数据，因为我们定义游标是从user表中查询name字段的数据，因此只需要一个tmp变量就行了，如果需要查询user中多个字段，则声明多个tmp字段并在获取数据时以fetch cur into tmp [,tmp2,tmp3,…];形式即可，请注意在使用游标前必须先打开，使用open cur;语句，而且只有在打开游标后前面定义的select语句开正式开始执行。循环获取cur中的数据使用了while流程语句，这里我们还定义了前面分析过的异常处理语句即

declare continue handler for not found set flag = 1; --异常处理并设置flag=1

在发生not found 的异常时将flag设置为1，并通过声明为continue而让程序继续执行。这样处理的理由是fetch cur into tmp语句执行时，如果游标的指针无法读取下一行数据时就会抛出NOT FOUND异常，抛出后由已声明的异常程序处理，并设置flag为1，以此来结束循环，注意抛出异常后程序还会继续执行，毕竟声明了continue。所以最后一次判断if flag !=1 then是必要的。最后执行完成，通过close cur 关闭游标，这样整个游标的使用就完成了。

事务处理

事务处理是数据库中的一个大块头，涉及到数据的完整性与一致性问题，由于mysql存在多种数据存储引擎提供给用户选择，但不是所有的引擎都支持事务处理，常见的引擎有：MyISAM和InnoDB，MyISAM是默认高速的引擎并不支持事务功能，InnoDB支持行锁定和事务处理，速度比MyISAM稍慢。事实上前面我们在创建表时都指明存储引擎为InnoDB，本篇中我们也将采用InnoDB引擎进行分析，毕竟InnoDB是支持事务功能的。

事务的概念

先看一个经典银行转账案例，A向B的银行卡转账1000元，这里分两个主要事件，一个是A向B转账1000，那么A的银行卡转账成功后必须在原来的数额上扣掉1000元，另一个是B收到了A的转款，B的银行卡上数额必须增加1000元，这两个步骤是必须都成功才算转账成功，总不能A转账B后，A的数额没有变化而B增加了1000元吧？这样银行不得亏死了？因此两个步骤只要有一个失败，此次转账的结果就是失败。但我们在执行sql语句时，两个动作是分两个语句执行的，万一执行完一个突然没电了另外一个没有执行，那岂不出问题了？此时就需要事务来解决这个问题了，所谓的事物就是保证以上的两个步骤在同一个环境中执行，只要其中一个失败，事务就会撤销之前的操作，回滚的没转账前的状态，如果两个都执行成功，那么事务就认为转成成功了。这就是事务的作用。

对事务有了初步理解后，进一步了解事务的官方概念，事务是DBMS的执行单位。它由有限个数据库操作语句组成。但不是任意的数据库操作序列都能成为事务。一般来说，事务是必须满足4个条件（ACID）

• 原子性（Autmic）：一个原子事务要么完整执行，要么干脆不执行。也就是说，工作单元中的每项任务都必须正确执行，如果有任一任务执行失败，则整个事务就会被终止并且此前对数据所作的任何修改都将被撤销。如果所有任务都被成功执行，事务就会被提交，那么对数据所作的修改将会是永久性的
• 一致性（Consistency）：一致性代表了底层数据存储的完整性。 它是由事务系统和应用开发人员共同来保证。事务系统通过保证事务的原子性，隔离性和持久性来满足这一要求; 应用开发人员则需要保证数据库有适当的约束(主键，引用完整性等)，并且工作单元中所实现的业务逻辑不会导致数据的不一致(数据预期所表达的现实业务情况不相一致)。例如，在刚才的AB转账过程中，从A账户中扣除的金额必须与B账户中存入的金额相等。
• 隔离性（Isolation）：隔离性是指事务必须在不干扰其他事务的前提下独立执行，也就是说，在事务执行完毕之前，其所访问的数据不能受系统其他部分的影响。
• 持久性（Durability）：持久性指明当系统或介质发生故障时，确保已提交事务的更新数据不能丢失，也就意味着一旦事务提交，DBMS保证它对数据库中数据的改变应该是永久性的，耐得住任何系统故障，持久性可以通过数据库备份和恢复来保证。

事务控制流程实战

在使用事务处理可能涉及到以下命令：

-- 声明事务的开始
BEGIN(或START TRANSACTION);

-- 提交整个事务
COMMIT;

-- 回滚到事务初始状态
ROLLBACK;

下面通过删除user表中的用户数据，然后再回滚来演示上述命令的作用：

-- 先查看user表中的数据
select * from user;
-- 开始事务
begin;
delete from user where id =24;
select * from user;
-- 执行回滚操作rollback
rollback;
-- 再次查看数据，可见ID为24的用户数据已恢复
select * from user;

从上述一系列操作中，从启动事务到删除用户数据，再到回滚数据，体现了事务控制的过程，这里我们还没使用COMMIT，如果刚才把rollback改成commit，那么事务就提交了，数据也就真的删除了。下面我们再次来演示删除数据的过程，并且这次使用commit提交事务。

-- 先添加一条要删除数据
insert into user values(30,'要被删除的数据',null,null,1,null);
-- 查看数据
select * from user;
-- 开启新事务
begin;
-- 删除数据
delete from user where id =30;
-- 提交事务
commit;
-- 回滚数据
rollback;
-- 查看数据
select * from user;

可以发现当删除完数据后，使用commit提交了事务，此时数据就会被真正更新到数据库了，即使使用rollback回滚也是没有办法恢复数据的。ok~，这就是事务控制最简化的流程，事实上除了上述的回滚到事务的初始状态外，还可以进行部分回滚，也就是我们可以自己控制事务发生错误时回滚到某个点，这需要利用以下命令来执行：

-- 定义保存点（回滚点）
SAVEPOINT savepoint_name（名称）;

--回滚到指定保存点
ROLLBACK TO SAVEPOINT savepoint_name（名称）;

演示案例如下：

-- 开启事务
begin;

insert into user values(31,'保存点1',null,null,1,null);

-- 创建保存点
savepoint sp;

insert into user values(32,'保存点2',null,null,1,null);

insert into user values(33,'保存点3',null,null,1,null);
insert into user values(34,'保存点4',null,null,1,null);

select * from user;
-- 回滚到保存点
rollback to savepoint sp;

-- 查看数据
select * from user;

-- 提交事务
commit;

关于commit有点需要知道的，在mysql中每条sql命令都会被自动commit，这种功能称为自动提交功能，是默认开启的。前面我们在执行事务使用了begin命令开启了事务，这时自动提交在事务中就关闭了直到事务被手动commit。当然我们也可以手动控制开启或者关闭此功能，语法如下：

-- 关闭自动提交功能
SET AUTOCOMMIT=0;
-- 开启自动提交功能
SET AUTOCOMMIT=1;