MySQL学习笔记（二）

一、概述

　　本篇文章主要介绍MySQL视图，触发器，存储过程，函数，事务，索引等内容，本节内容在Navicat上进行试验，具体软件安装及操作不再赘述。

二、视图　

　　视图是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，可以将该结果集当做表来使用。使用视图我们可以把查询过程中的临时表摘出来，用视图去实现，这样以后再想操作该临时表的数据时就无需重写复杂的sql了，直接去视图中查找即可，但视图有明显地效率问题，并且视图是存放在数据库中的，如果我们程序中使用的sql过分依赖数据库中的视图，即强耦合，那就意味着扩展sql极为不便，因此并不推荐使用

　　如某个SQL语句获取的动态结果集重复使用，使用视图可以很大程度上减少我们的SQL语句代码量

创建部门表
create table dep(
id int primary key auto_increment,
name char(32)
);

创建用户表
create table user(
id int primary key auto_increment,
name char(32),
dep_id int,
foreign key(dep_id) references dep(id)
);

数据插入

INSERT into dep(name) VALUES('财政部');
INSERT into dep(name) VALUES('后勤部');
INSERT into dep(name) VALUES('研发部');
INSERT into dep(name) VALUES('采购部');

INSERT into user(name,salary,dep_id) VALUES('小花','5000','1');
INSERT into user(name,salary,dep_id) VALUES('太阳','8000','3');
INSERT into user(name,salary,dep_id) VALUES('豆豆','12000','2');
INSERT into user(name,salary,dep_id) VALUES('小虎','11000','2');

试验表创建

如我们要查询工资高于所有人员平均工资的人员姓名，通常我们这样写

SELECT name from user WHERE salary >(SELECT avg(salary) from user);

此时我们可以使用视图查询

CREATE VIEW salary_avg_view AS SELECT avg(salary) from user;

SELECT name from user WHERE salary >(SELECT * from salary_avg_view);

1、创建视图（--格式：CREATE VIEW 视图名称 AS SQL语句）

CREATE VIEW salary_avg_view AS SELECT avg(salary) from user;

2、删除视图（--格式：DROP VIEW 视图名称）

DROP VIEW salary_avg_view;

3、修改视图（-- 格式：ALTER VIEW 视图名称 AS SQL语句）

ALTER VIEW salary_avg_view AS SELECT avg(salary) from user;


#此处并未做修改，实际操作可根据需要修改

4、使用视图（当作普通表使用即可）

SELECT * from salary_avg_view

三、触发器

对某个表进行【增/删/改】操作的前后如果希望触发某个特定的行为时，可以使用触发器，触发器用于定制用户对表的行进行【增/删/改】前后的行为。注：没有查

现在我们新建一个user人数统计的表user_count，设置user_total字段

CREATE TABLE user_count(
    id int NOT NULL auto_increment PRIMARY KEY,
    user_total int DEFAULT 0
);

user表中已有4人，这里就偷懒直接设置下user_total值为4

UPDATE user_count SET user_total=4;

下面定义一个触发器，规定当user表中新增时，user_count加1，当user表中有删除行时，user_count减1

CREATE TRIGGER tigger_user_count_insert AFTER INSERT
ON user FOR EACH ROW
UPDATE user_count SET user_total=user_total+1 WHERE id=1;

CREATE TRIGGER tigger_user_count_delete AFTER DELETE
ON user FOR EACH ROW
UPDATE user_count SET user_total=user_total-1 WHERE id=1;

此处补张图

此时，user_total值为4，我们试着插入一个值

测试代码：

INSERT INTO user(name,salary,dep_id) VALUES('小明',8000,4);

执行后user_total字段变为5

同理，删除的触发器的测试

测试代码：

DELETE from user WHERE name='小明';

执行后user_total字段变为4

下面具体介绍下触发器的使用

1、创建触发器

CREATE

    [DEFINER = { user | CURRENT_USER }]

    TRIGGER trigger_name

    trigger_time trigger_event

    ON tbl_name FOR EACH ROW

    trigger_body

trigger_name：触发器的名称，不能与已经存在的触发器重复；

trigger_time：{ BEFORE | AFTER }，表示在事件之前或之后触发；

trigger_event:：{ INSERT |UPDATE | DELETE }，触发该触发器的具体事件；

tbl_name：该触发器作用在tbl_name上；

 1 # 插入前
 2 CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
 3 BEGIN
 4     ...
 5 END
 6 
 7 # 插入后
 8 CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
 9 BEGIN
10     ...
11 END
12 
13 # 删除前
14 CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
15 BEGIN
16     ...
17 END
18 
19 # 删除后
20 CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
21 BEGIN
22     ...
23 END
24 
25 # 更新前
26 CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
27 BEGIN
28     ...
29 END
30 
31 # 更新后
32 CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
33 BEGIN
34     ...
35 END

创建触发器基本语法

如：

1 CREATE TRIGGER trigger_user_count_insert AFTER INSERT
2 ON user FOR EACH ROW
3 UPDATE user_count SET user_total=(user_total+1);
4 
5 CREATE TRIGGER trigger_user_count_delete AFTER DELETE
6 ON user FOR EACH ROW
7 UPDATE user_count SET user_total=user_total-1;

示例代码

2、删除触发器

DROP TRIGGER 触发器名称;

如：

DROP TRIGGER trigger_user_count_delete;

示例代码

3、使用触发器

触发器无法由用户直接调用，而知由于对表的【增/删/改】操作被动引发的。

如：

INSERT INTO user(name,salary,dep_id) VALUES('小明',8000,4);

示例代码

四、存储过程

MySQL数据库在5.0版本后开始支持存储过程，存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql，类似于函数(方法),简单的说存储过程是为了完成某个数据库中的特定功能而编写的语句集合，该语句集包括SQL语句（对数据的增删改查）、条件语句和循环语句等（流程控制见part 9）

存储过程优点：
        1、存储过程增强了SQL语言灵活性。

　　　　　　存储过程可以使用控制语句编写，可以完成复杂的判断和较复杂的运算，有很强的灵活性；
        2、减少网络流量，降低了网络负载。

　　　　　　存储过程在服务器端创建成功后，只需要调用该存储过程即可，而传统的做法是每次都将大量的SQL语句通过网络发送至数据库服务器端然后再执行
        3、存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译。

　　　　　　一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
存储过程缺点：     

　 　1、扩展功能不方便

　　 2、不便于系统后期维护

存储过程优缺点

1、查看存储过程

SHOW PROCEDURE STATUS;

2、创建存储过程

无参存储过程　　

 1 -- 创建存储过程
 2 
 3 delimiter //
 4 create procedure 存储过程名称()
 5 BEGIN
 6     SQL语句
 7 END//
 8 delimiter ;
 9 
10 
11 
12 -- 执行存储过程
13 
14 call 存储过程名称()

对于存储过程，可以接收参数，其参数有三类：

in 仅用于传入参数用
out 仅用于返回值用
inout 既可以传入又可以当作返回值

 1 -- 创建存储过程
 2 delimiter \
 3 create procedure p1(
 4     in i1 int,
 5     in i2 int,
 6     inout i3 int,
 7     out r1 int
 8 )
 9 BEGIN
10     DECLARE temp1 int;
11     DECLARE temp2 int default 0;
12     
13     set temp1 = 1;
14 
15     set r1 = i1 + i2 + temp1 + temp2;
16     
17     set i3 = i3 + 100;
18 
19 end\
20 delimiter ;
21 
22 -- 执行存储过程
23 set @t1 =4;
24 set @t2 = 0;
25 CALL p1 (1, 2 ,@t1, @t2);
26 SELECT @t1,@t2;

有参存储过程

create procedure p2(in i int,out n varchar(50))
begin
 select name into n from account where id = i;
end
 
-- 调用
set @name =null;
CALL p2(1,@name);
select @name;

--into 关键字 可以 将前面字段的查询结果 执行 给 into 后面的变量.

into使用

 create procedure p3(in x int,in c char(1))
 begin
    if c ='d' then
         select * from account where money >x;
   else
         select * from account where money <x;     
　　end if;
end

if语句

 1 create procedure p4(inout n int)
 2 begin
 3       DECLARE sum int default 0; -- 设置总和变量,并且指定初始值0
 4       declare i int; -- 声明变量
 5       set i = 0;    -- 通过set为变量设置值
 6 　　  while i<=n DO  -- 开始循环
 7             set sum = sum +i;
 8             set i = i+1;
 9       end while; -- 结束循环
10  
11 　　  select sum; -- 提供结果
12                      
13      set n = sum;--将计算结果提供给 输出变量 n;
14 end;
15                  
16  -- 调用:
17  set @n = 100;
18  call p4(@n);
19  select @n;

使用循环计算1-100的和

3、删除存储过程

drop procedure proc_name;

4、执行存储过程

-- 无参数
call proc_name()

-- 有参数，全in
call proc_name(1,2)

-- 有参数，有in，out，inout
set @t1=0;
set @t2=3;
call proc_name(1,2,@t1,@t2)

五、函数

1、MySQL提供的内建函数

  1 一、数学函数
  2     ROUND(x,y)
  3         返回参数x的四舍五入的有y位小数的值
  4         
  5     RAND()
  6         返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。
  7 
  8 二、聚合函数(常用于GROUP BY从句的SELECT查询中)
  9     AVG(col)返回指定列的平均值
 10     COUNT(col)返回指定列中非NULL值的个数
 11     MIN(col)返回指定列的最小值
 12     MAX(col)返回指定列的最大值
 13     SUM(col)返回指定列的所有值之和
 14     GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果    
 15     
 16 三、字符串函数
 17 
 18     CHAR_LENGTH(str)
 19         返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。
 20     CONCAT(str1,str2,...)
 21         字符串拼接
 22         如有任何一个参数为NULL ，则返回值为 NULL。
 23     CONCAT_WS(separator,str1,str2,...)
 24         字符串拼接（自定义连接符）
 25         CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。
 26 
 27     FORMAT(X,D)
 28         将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若  D 为 0, 则返回结果不带有小数点，或不含小数部分。
 29         例如：
 30             SELECT FORMAT(12332.1,4); 结果为： '12,332.1000'
 31     
 32     INSERT(str,pos,len,newstr)
 33         在str的指定位置插入字符串
 34             pos：要替换位置其实位置
 35             len：替换的长度
 36             newstr：新字符串
 37         例如:
 38             SELECT INSERT('abcd',1,2,'tt'); 结果为: 'ttcd'
 39             SELECT INSERT('abcd',1,4,'tt'); 结果为: 'tt'
 40         特别的：
 41             如果pos超过原字符串长度，则返回原字符串
 42             如果len超过原字符串长度，则由新字符串完全替换
 43     
 44     INSTR(str,substr)
 45         返回字符串 str 中子字符串的第一个出现位置。
 46 
 47     LEFT(str,len)
 48         返回字符串str 从开始的len位置的子序列字符。
 49         例如:
 50             SELECT INSTR('abc','c'); 结果为： 3
 51             SELECT INSTR('abc','d'); 结果为： 0
 52             
 53     LOWER(str)
 54         变小写
 55 
 56     UPPER(str)
 57         变大写
 58    
 59     REVERSE(str)
 60         返回字符串 str ，顺序和字符顺序相反。
 61         例如:
 62             SELECT REVERSE('1234567') 结果为：7654321
 63             
 64     SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
 65         不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。
 66 
 67         mysql> SELECT SUBSTRING('Quadratically',5); -- 从第5位开始截取
 68             -> 'ratically'
 69 
 70         mysql> SELECT SUBSTRING('foobarbar' FROM 4); -- 从第4位开始截取
 71             -> 'barbar'
 72 
 73         mysql> SELECT SUBSTRING('Quadratically',5,6); --从第5位开始截取,截取6个长度
 74             -> 'ratica'
 75 
 76         mysql> SELECT SUBSTRING('Sakila', -3);    -- 从倒数第3位开始截取
 77             -> 'ila'
 78 
 79         mysql> SELECT SUBSTRING('Sakila', -5, 3); -- 从倒数第5位开始截取,截取3个长度
 80             -> 'aki'
 81             
 82 四、日期和时间函数
 83     CURDATE()或CURRENT_DATE() 返回当前的日期
 84     CURTIME()或CURRENT_TIME() 返回当前的时间
 85     DAYOFWEEK(date)   返回date所代表的一星期中的第几天(1~7)
 86     DAYOFMONTH(date)  返回date是一个月的第几天(1~31)
 87     DAYOFYEAR(date)   返回date是一年的第几天(1~366)
 88     DAYNAME(date)   返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE);
 89     FROM_UNIXTIME(ts,fmt)  根据指定的fmt格式，格式化UNIX时间戳ts
 90     HOUR(time)   返回time的小时值(0~23)
 91     MINUTE(time)   返回time的分钟值(0~59)
 92     MONTH(date)   返回date的月份值(1~12)
 93     MONTHNAME(date)   返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE);
 94     NOW()    返回当前的日期和时间
 95     QUARTER(date)   返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE);
 96     WEEK(date)   返回日期date为一年中第几周(0~53)
 97     YEAR(date)   返回日期date的年份(1000~9999)
 98     
 99     重点:
100     DATE_FORMAT(date,format) 根据format字符串格式化date值
101 
102        mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
103         -> 'Sunday October 2009'
104        mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
105         -> '22:23:00'
106        mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
107         ->                 '%D %y %a %d %m %b %j');
108         -> '4th 00 Thu 04 10 Oct 277'
109        mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
110         ->                 '%H %k %I %r %T %S %w');
111         -> '22 22 10 10:23:00 PM 22:23:00 00 6'
112        mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
113         -> '1998 52'
114        mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
115         -> '00'
116         
117 五、加密函数
118     MD5()    
119         计算字符串str的MD5校验和
120         例如:
121             SELECT MD5('1234') 结果为：81dc9bdb52d04dc20036dbd8313ed055
122     PASSWORD(str)   
123         返回字符串str的加密版本，这个加密过程是不可逆转的
124         例如:
125             SELECT PASSWORD('1234') 结果为:*A4B6157319038724E3560894F7F932C8886EBFCF
126         
127 六、控制流函数            
128     CASE WHEN[test1] THEN [result1]...ELSE [default] END
129         如果testN是真，则返回resultN，否则返回default
130     CASE [test] WHEN[val1] THEN [result]...ELSE [default]END  
131         如果test和valN相等，则返回resultN，否则返回default
132 
133     IF(test,t,f)   
134         如果test是真，返回t；否则返回f
135 
136     IFNULL(arg1,arg2) 
137         如果arg1不是空，返回arg1，否则返回arg2
138         例如:
139             SELECT IFNULL('bbb','abc'); 结果为: bbb
140             SELECT IFNULL(null,'abc');  结果为: abc
141 
142     NULLIF(arg1,arg2) 
143         如果arg1=arg2返回NULL；否则返回arg1
144         例如:
145             SELECT NULLIF('bbb','bbb');结果为： null
146             SELECT NULLIF('aaa','bbb');结果为： aaa

MySQL提供的内建函数

delimiter \
create function f1(
    i1 int,
    i2 int)
returns int
BEGIN
    declare num int;
    set num = i1 + i2;
    return(num);
END \
delimiter ;

自定义函数

2、删除函数

drop function func_name;

删除函数

3、执行函数

# 获取返回值
declare @i VARCHAR(32);
select UPPER('doudou') into @i;
SELECT @i;


# 在查询中使用
select f1(11,nid) ,name from tb2;

执行函数

4、函数与存储过程区别

六、事务

事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。

1、事务的特性

　　原子性（Atomicity）

　　原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，这和前面两篇博客介绍事务的功能是一样的概念，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

　　一致性（Consistency）

　　一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。

　　拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。

　　隔离性（Isolation）

　　隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

　　即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。

　　关于事务的隔离性数据库提供了多种隔离级别，稍后会介绍到。

　　持久性（Durability）

　　持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

注意：

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务用来管理 insert,update,delete 语句

2、事务的控制

BEGIN 或 START TRANSACTION；显式地开启一个事务；
COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改称为永久性的；
ROLLBACK；有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
SAVEPOINT : 保存点,可以把一个事物分割成几部分.在执行ROLLBACK 时可以指定在什么位置上进行回滚操作.

注意: SET AUTOCOMMIT=0 ;禁止自动提交和 SET AUTOCOMMIT=1 开启自动提交.

3、应用举例（双方转账）

create table account(
    id int(50) not null auto_increment primary key,
    name VARCHAR(50) not null,
    money DOUBLE(5,2) not NULL
);

创建表

insert into account (name,money) values('鲁班',250),('后羿',5000);

插入数据

start transaction; -- 开启事物
 --  执行sql语句操作
 update account set money = money - 500 where id ='1';  
 update account set money = money+500 where id = '2';
 
commit;  -- 手动提交事物
rollback; -- 回滚事物
 
--  查看结果
select * from account;

执行转账

START TRANSACTION ;
 
insert into account (name,money) values('李元芳',1000);
 
SAVEPOINT s1; -- 设置保存点
 
insert into account (name,money) values('张桂枝',1500);
 
ROLLBACK to s1; -- 事物回滚到保存点<br>COMMIT; --提交事物

保存点使用

七、索引

索引，是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，然后直接获取即可。

常见的索引有普通索引，唯一索引，主键索引，组合索引。

1、普通索引

普通索引仅有一个功能：加速查询

1 create table in1(
2     nid int not null auto_increment primary key,
3     name varchar(32) not null,
4     email varchar(64) not null,
5     extra text,
6     index ix_name (name)
7 )

创建表+索引

1 create index index_name on table_name(column_name)

创建索引

drop index_name on table_name;

删除索引

show index from table_name;

查看索引

注意：对于创建索引时如果是BLOB 和 TEXT 类型，必须指定length。

1 create index ix_extra on in1(extra(32));

示例

2、唯一索引

1 create table in1(
2     nid int not null auto_increment primary key,
3     name varchar(32) not null,
4     email varchar(64) not null,
5     extra text,
6     unique ix_name (name)
7 )

创建表+唯一索引

1 create unique index 索引名 on 表名(列名)

创建唯一索引

drop unique index 索引名 on 表名

删除唯一索引

3、主键索引

主键有两个功能：加速查询和唯一约束（不可含null）

 1 create table in1(
 2     nid int not null auto_increment primary key,
 3     name varchar(32) not null,
 4     email varchar(64) not null,
 5     extra text,
 6     index ix_name (name)
 7 )
 8 
 9 OR
10 
11 create table in1(
12     nid int not null auto_increment,
13     name varchar(32) not null,
14     email varchar(64) not null,
15     extra text,
16     primary key(ni1),
17     index ix_name (name)
18 )

创建表+主键

1 alter table 表名 add primary key(列名);

创建主键

1 alter table 表名 drop primary key;
2 alter table 表名  modify  列名 int, drop primary key;

删除主键

4、组合索引

组合索引是将n个列组合成一个索引，频繁的同时使用n列来进行查询，如：where name = 'doudou' and age = 18。

1 create table in3(
2     nid int not null auto_increment primary key,
3     name varchar(32) not null,
4     email varchar(64) not null,
5     extra text
6 )

创建表

1 create index ix_name_email on in3(name,email);

创建组合索引

注意：对于同时搜索n个条件时，组合索引的性能好于多个单一索引合并。

八、数据锁

1、锁的基本概念

　　当并发事务同时访问一个资源时，有可能导致数据不一致，因此需要一种机制来将数据访问顺序化，以保证数据库数据的一致性。

2、共享锁与排他锁

　　多个事务同时读取一个对象的时候，是不会有冲突的。同时读和写，或者同时写才会产生冲突。因此为了提高数据库的并发性能，通常会定义两种锁：共享锁和排它锁。

　　共享锁(S)表示对数据进行读操作。因此多个事务可以同时为一个对象加共享锁。

　　排他锁(X)表示对数据进行写操作。如果一个事务对对象加了排他锁，其他事务就不能再给它加任何锁了。

3、悲观锁与乐观锁

　　悲观锁顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block(阻塞)直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制.

注意：要使用悲观锁，我们必须关闭mysql数据库的自动提交属性.因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。关闭自动提交命令为：set autocommit=0;

乐观锁, 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。　

使用乐观锁的两种方式:

　　1.使用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。

　　2.乐观锁定的第二种实现方式和第一种差不多，同样是在需要乐观锁控制的table中增加一个字段，名称无所谓，字段类型使用时间戳（datatime）, 和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。

1 悲观锁与乐观锁的优缺点:
2 
3 　　两种锁各有其有点缺点,不能单纯的讲哪个更好.
4 
5 　　　　乐观锁适用于写入比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。
6 
7 　　　　但如果经常产生冲突，上层应用会不断的进行重试操作，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适.

悲观锁与乐观锁的优缺点

4、试验示例

　　扯了一堆概念，下面介绍数据锁试验，需求: 有一个账户,两个人在同一时间要对此账户操作,A要对账户充值100块,B要从账户中取出100块.操作前都要先看一下账户的余额然后再操作

 1 -- 窗口1 用户进行充值
 2 
 3 -- 充值前 先查看余额
 4 set @m=0;
 5 
 6 SELECT money into @m from account where id = 1;
 7 
 8 select @m;
 9 
10 -- 看到余额后 充值100 块
11 update account set money = @m + 100 where id = 1;
12 
13 SELECT * from account;
14 
15 --------------------------------------------------------------
16 -- 窗口2 用户进行取款
17 
18 -- 取款前 先查看余额
19 set @m=0;
20 
21 SELECT money into @m from account where id = 1;
22 
23 select @m;
24 
25 -- 看到余额后 取款100 块
26 update account set money = @m - 100 where id = 1;
27 
28 SELECT * from account;

示例

4.1、使用悲观锁

设置完autocommit后，我们就可以执行我们的正常业务了。具体如下：

-- 0.开始事务
start transaction;
 
-- 1.查询账户余额
set @m = 0; -- 账户余额
select money into @m from account where id = 1 for update;
select @m;
 
-- 2.修改账户余额
update account set money = @m -100 where id = 1;
 
select * FROM account where id = 1;
-- 3. 提交事务
commit;

在另外的查询页面执行:

-- 0.开始事务
start transaction;
 
-- 1.查询账户余额
set @m = 0; -- 账户余额
select money into @m from account where id = 1 for update;
select @m;
 
-- 2.修改账户余额
update account set money = @m +100 where id = 1;
 
select * FROM account where id = 1;
-- 3. 提交事务
commit;

4.2、使用乐观锁

-- 1.查询账户余额
set @m = 0; -- 账户余额
select money into @m from account where id = 1 ;
select @m;
-- 2.查询版本号
set @version = 0; -- 版本号
select version into @version from account where id = 1 ;
select @version;
 
-- 3.修改账户余额
update account set money = @m -100,version=version+1 where id = 1 and version = @version;
 
select * FROM account where id = 1;

九、流程控制（补充）

1、条件语句

 1 delimiter \
 2 CREATE PROCEDURE proc_if ()
 3 BEGIN
 4     
 5     declare i int default 0;
 6     if i = 1 THEN
 7         SELECT 1;
 8     ELSEIF i = 2 THEN
 9         SELECT 2;
10     ELSE
11         SELECT 7;
12     END IF;
13 
14 END\
15 delimiter ;

条件语句if

2、循环语句

 1 delimiter \
 2 CREATE PROCEDURE proc_while ()
 3 BEGIN
 4 
 5     DECLARE num INT ;
 6     SET num = 0 ;
 7     WHILE num < 10 DO
 8         SELECT
 9             num ;
10         SET num = num + 1 ;
11     END WHILE ;
12 
13 END\
14 delimiter ;

while循环

 1 delimiter \
 2 CREATE PROCEDURE proc_repeat ()
 3 BEGIN
 4 
 5     DECLARE i INT ;
 6     SET i = 0 ;
 7     repeat
 8         select i;
 9         set i = i + 1;
10         until i >= 5
11     end repeat;
12 
13 END\
14 delimiter ;

repeat循环

 1 BEGIN
 2     
 3     declare i int default 0;
 4     loop_label: loop
 5         
 6         set i=i+1;
 7         if i<8 then
 8             iterate loop_label;
 9         end if;
10         if i>=10 then
11             leave loop_label;
12         end if;
13         select i;
14     end loop loop_label;
15 
16 END

loop循环

3、动态执行sql

 1 delimiter \
 2 DROP PROCEDURE IF EXISTS proc_sql \
 3 CREATE PROCEDURE proc_sql ()
 4 BEGIN
 5     declare p1 int;
 6     set p1 = 11;
 7     set @p1 = p1;
 8 
 9     PREPARE prod FROM 'select * from tb2 where nid > ?';
10     EXECUTE prod USING @p1;
11     DEALLOCATE prepare prod; 
12 
13 END\
14 delimiter ;

动态执行SQL

十、数据库备份

　　mysqldump 命令将数据库中的数据备份成一个文本文件。表的结构和表中的数据将存储在生成的文本文件中。

　　mysqldump命令的工作原理很简单。它先查出需要备份的表的结构，再在文本文件中生成一个CREATE语句。然后，将表中的所有记录转换成一条INSERT语句。然后通过这些语句，就能够创建表并插入数据。

1、使用mysqldump实现逻辑备份

 1 #语法：
 2 # mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql
 3  
 4 #示例：
 5 #单库备份
 6 mysqldump -uroot -p123456 db1 > c:/db1.sql
 7 mysqldump -uroot -p123456 db1 table1 table2 > c:/db1-table1-table2.sql
 8  
 9 #多库备份
10 mysqldump -uroot -p123456 --databases db1 db2 mysql db3 > c:/db1_db2_mysql_db3.sql
11  
12 #备份所有库
13 mysqldump -uroot -p123456 --all-databases > c:/all.sql

  1 --all-databases  , -A
  2 导出全部数据库。
  3 mysqldump  -uroot -p --all-databases
  4 --all-tablespaces  , -Y
  5 导出全部表空间。
  6 mysqldump  -uroot -p --all-databases --all-tablespaces
  7 --no-tablespaces  , -y
  8 不导出任何表空间信息。
  9 mysqldump  -uroot -p --all-databases --no-tablespaces
 10 --add-drop-database
 11 每个数据库创建之前添加drop数据库语句。
 12 mysqldump  -uroot -p --all-databases --add-drop-database
 13 --add-drop-table
 14 每个数据表创建之前添加drop数据表语句。(默认为打开状态，使用--skip-add-drop-table取消选项)
 15 mysqldump  -uroot -p --all-databases  (默认添加drop语句)
 16 mysqldump  -uroot -p --all-databases –skip-add-drop-table  (取消drop语句)
 17 --add-locks
 18 在每个表导出之前增加LOCK TABLES并且之后UNLOCK  TABLE。(默认为打开状态，使用--skip-add-locks取消选项)
 19 mysqldump  -uroot -p --all-databases  (默认添加LOCK语句)
 20 mysqldump  -uroot -p --all-databases –skip-add-locks   (取消LOCK语句)
 21 --allow-keywords
 22 允许创建是关键词的列名字。这由表名前缀于每个列名做到。
 23 mysqldump  -uroot -p --all-databases --allow-keywords
 24 --apply-slave-statements
 25 在'CHANGE MASTER'前添加'STOP SLAVE'，并且在导出的最后添加'START SLAVE'。
 26 mysqldump  -uroot -p --all-databases --apply-slave-statements
 27 --character-sets-dir
 28 字符集文件的目录
 29 mysqldump  -uroot -p --all-databases  --character-sets-dir=/usr/local/mysql/share/mysql/charsets
 30 --comments
 31 附加注释信息。默认为打开，可以用--skip-comments取消
 32 mysqldump  -uroot -p --all-databases  (默认记录注释)
 33 mysqldump  -uroot -p --all-databases --skip-comments   (取消注释)
 34 --compatible
 35 导出的数据将和其它数据库或旧版本的MySQL 相兼容。值可以为ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options等，
 36 要使用几个值，用逗号将它们隔开。它并不保证能完全兼容，而是尽量兼容。
 37 mysqldump  -uroot -p --all-databases --compatible=ansi
 38 --compact
 39 导出更少的输出信息(用于调试)。去掉注释和头尾等结构。可以使用选项：--skip-add-drop-table  --skip-add-locks --skip-comments --skip-disable-keys
 40 mysqldump  -uroot -p --all-databases --compact
 41 --complete-insert,  -c
 42 使用完整的insert语句(包含列名称)。这么做能提高插入效率，但是可能会受到max_allowed_packet参数的影响而导致插入失败。
 43 mysqldump  -uroot -p --all-databases --complete-insert
 44 --compress, -C
 45 在客户端和服务器之间启用压缩传递所有信息
 46 mysqldump  -uroot -p --all-databases --compress
 47 --create-options,  -a
 48 在CREATE TABLE语句中包括所有MySQL特性选项。(默认为打开状态)
 49 mysqldump  -uroot -p --all-databases
 50 --databases,  -B
 51 导出几个数据库。参数后面所有名字参量都被看作数据库名。
 52 mysqldump  -uroot -p --databases test mysql
 53 --debug
 54 输出debug信息，用于调试。默认值为：d:t,/tmp/mysqldump.trace
 55 mysqldump  -uroot -p --all-databases --debug
 56 mysqldump  -uroot -p --all-databases --debug=” d:t,/tmp/debug.trace”
 57 --debug-check
 58 检查内存和打开文件使用说明并退出。
 59 mysqldump  -uroot -p --all-databases --debug-check
 60 --debug-info
 61 输出调试信息并退出
 62 mysqldump  -uroot -p --all-databases --debug-info
 63 --default-character-set
 64 设置默认字符集，默认值为utf8
 65 mysqldump  -uroot -p --all-databases --default-character-set=utf8
 66 --delayed-insert
 67 采用延时插入方式（INSERT DELAYED）导出数据
 68 mysqldump  -uroot -p --all-databases --delayed-insert
 69 --delete-master-logs
 70 master备份后删除日志. 这个参数将自动激活--master-data。
 71 mysqldump  -uroot -p --all-databases --delete-master-logs
 72 --disable-keys
 73 对于每个表，用/*!40000 ALTER TABLE tbl_name DISABLE KEYS */;和/*!40000 ALTER TABLE tbl_name ENABLE KEYS */;语句引用INSERT语句。这样可以更快地导入dump出来的文件，因为它是在插入所有行后创建索引的。该选项只适合MyISAM表，默认为打开状态。
 74 mysqldump  -uroot -p --all-databases 
 75 --dump-slave
 76 该选项将主的binlog位置和文件名追加到导出数据的文件中(show slave status)。设置为1时，将会以CHANGE MASTER命令输出到数据文件；设置为2时，会在change前加上注释。该选项将会打开--lock-all-tables，除非--single-transaction被指定。该选项会自动关闭--lock-tables选项。默认值为0。
 77 mysqldump  -uroot -p --all-databases --dump-slave=1
 78 mysqldump  -uroot -p --all-databases --dump-slave=2
 79 --master-data
 80 该选项将当前服务器的binlog的位置和文件名追加到输出文件中(show master status)。如果为1，将会输出CHANGE MASTER 命令；如果为2，输出的CHANGE  MASTER命令前添加注释信息。该选项将打开--lock-all-tables 选项，除非--single-transaction也被指定（在这种情况下，全局读锁在开始导出时获得很短的时间；其他内容参考下面的--single-transaction选项）。该选项自动关闭--lock-tables选项。
 81 mysqldump  -uroot -p --host=localhost --all-databases --master-data=1;
 82 mysqldump  -uroot -p --host=localhost --all-databases --master-data=2;
 83 --events, -E
 84 导出事件。
 85 mysqldump  -uroot -p --all-databases --events
 86 --extended-insert,  -e
 87 使用具有多个VALUES列的INSERT语法。这样使导出文件更小，并加速导入时的速度。默认为打开状态，使用--skip-extended-insert取消选项。
 88 mysqldump  -uroot -p --all-databases
 89 mysqldump  -uroot -p --all-databases--skip-extended-insert   (取消选项)
 90 --fields-terminated-by
 91 导出文件中忽略给定字段。与--tab选项一起使用，不能用于--databases和--all-databases选项
 92 mysqldump  -uroot -p test test --tab=”/home/mysql” --fields-terminated-by=”#”
 93 --fields-enclosed-by
 94 输出文件中的各个字段用给定字符包裹。与--tab选项一起使用，不能用于--databases和--all-databases选项
 95 mysqldump  -uroot -p test test --tab=”/home/mysql” --fields-enclosed-by=”#”
 96 --fields-optionally-enclosed-by
 97 输出文件中的各个字段用给定字符选择性包裹。与--tab选项一起使用，不能用于--databases和--all-databases选项
 98 mysqldump  -uroot -p test test --tab=”/home/mysql”  --fields-enclosed-by=”#” --fields-optionally-enclosed-by  =”#”
 99 --fields-escaped-by
100 输出文件中的各个字段忽略给定字符。与--tab选项一起使用，不能用于--databases和--all-databases选项
101 mysqldump  -uroot -p mysql user --tab=”/home/mysql” --fields-escaped-by=”#”
102 --flush-logs
103 开始导出之前刷新日志。
104 请注意：假如一次导出多个数据库(使用选项--databases或者--all-databases)，将会逐个数据库刷新日志。除使用--lock-all-tables或者--master-data外。在这种情况下，日志将会被刷新一次，相应的所以表同时被锁定。因此，如果打算同时导出和刷新日志应该使用--lock-all-tables 或者--master-data 和--flush-logs。
105 mysqldump  -uroot -p --all-databases --flush-logs
106 --flush-privileges
107 在导出mysql数据库之后，发出一条FLUSH  PRIVILEGES 语句。为了正确恢复，该选项应该用于导出mysql数据库和依赖mysql数据库数据的任何时候。
108 mysqldump  -uroot -p --all-databases --flush-privileges
109 --force
110 在导出过程中忽略出现的SQL错误。
111 mysqldump  -uroot -p --all-databases --force
112 --help
113 显示帮助信息并退出。
114 mysqldump  --help
115 --hex-blob
116 使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用该选项。影响到的字段类型有BINARY、VARBINARY、BLOB。
117 mysqldump  -uroot -p --all-databases --hex-blob
118 --host, -h
119 需要导出的主机信息
120 mysqldump  -uroot -p --host=localhost --all-databases
121 --ignore-table
122 不导出指定表。指定忽略多个表时，需要重复多次，每次一个表。每个表必须同时指定数据库和表名。例如：--ignore-table=database.table1 --ignore-table=database.table2 ……
123 mysqldump  -uroot -p --host=localhost --all-databases --ignore-table=mysql.user
124 --include-master-host-port
125 在--dump-slave产生的'CHANGE  MASTER TO..'语句中增加'MASTER_HOST=<host>，MASTER_PORT=<port>'  
126 mysqldump  -uroot -p --host=localhost --all-databases --include-master-host-port
127 --insert-ignore
128 在插入行时使用INSERT IGNORE语句.
129 mysqldump  -uroot -p --host=localhost --all-databases --insert-ignore
130 --lines-terminated-by
131 输出文件的每行用给定字符串划分。与--tab选项一起使用，不能用于--databases和--all-databases选项。
132 mysqldump  -uroot -p --host=localhost test test --tab=”/tmp/mysql”  --lines-terminated-by=”##”
133 --lock-all-tables,  -x
134 提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭--single-transaction 和--lock-tables 选项。
135 mysqldump  -uroot -p --host=localhost --all-databases --lock-all-tables
136 --lock-tables,  -l
137 开始导出前，锁定所有表。用READ  LOCAL锁定表以允许MyISAM表并行插入。对于支持事务的表例如InnoDB和BDB，--single-transaction是一个更好的选择，因为它根本不需要锁定表。
138 请注意当导出多个数据库时，--lock-tables分别为每个数据库锁定表。因此，该选项不能保证导出文件中的表在数据库之间的逻辑一致性。不同数据库表的导出状态可以完全不同。
139 mysqldump  -uroot -p --host=localhost --all-databases --lock-tables
140 --log-error
141 附加警告和错误信息到给定文件
142 mysqldump  -uroot -p --host=localhost --all-databases  --log-error=/tmp/mysqldump_error_log.err
143 --max_allowed_packet
144 服务器发送和接受的最大包长度。
145 mysqldump  -uroot -p --host=localhost --all-databases --max_allowed_packet=10240
146 --net_buffer_length
147 TCP/IP和socket连接的缓存大小。
148 mysqldump  -uroot -p --host=localhost --all-databases --net_buffer_length=1024
149 --no-autocommit
150 使用autocommit/commit 语句包裹表。
151 mysqldump  -uroot -p --host=localhost --all-databases --no-autocommit
152 --no-create-db,  -n
153 只导出数据，而不添加CREATE DATABASE 语句。
154 mysqldump  -uroot -p --host=localhost --all-databases --no-create-db
155 --no-create-info,  -t
156 只导出数据，而不添加CREATE TABLE 语句。
157 mysqldump  -uroot -p --host=localhost --all-databases --no-create-info
158 --no-data, -d
159 不导出任何数据，只导出数据库表结构。
160 mysqldump  -uroot -p --host=localhost --all-databases --no-data
161 --no-set-names,  -N
162 等同于--skip-set-charset
163 mysqldump  -uroot -p --host=localhost --all-databases --no-set-names
164 --opt
165 等同于--add-drop-table,  --add-locks, --create-options, --quick, --extended-insert, --lock-tables,  --set-charset, --disable-keys 该选项默认开启,  可以用--skip-opt禁用.
166 mysqldump  -uroot -p --host=localhost --all-databases --opt
167 --order-by-primary
168 如果存在主键，或者第一个唯一键，对每个表的记录进行排序。在导出MyISAM表到InnoDB表时有效，但会使得导出工作花费很长时间。 
169 mysqldump  -uroot -p --host=localhost --all-databases --order-by-primary
170 --password, -p
171 连接数据库密码
172 --pipe(windows系统可用)
173 使用命名管道连接mysql
174 mysqldump  -uroot -p --host=localhost --all-databases --pipe
175 --port, -P
176 连接数据库端口号
177 --protocol
178 使用的连接协议，包括：tcp, socket, pipe, memory.
179 mysqldump  -uroot -p --host=localhost --all-databases --protocol=tcp
180 --quick, -q
181 不缓冲查询，直接导出到标准输出。默认为打开状态，使用--skip-quick取消该选项。
182 mysqldump  -uroot -p --host=localhost --all-databases 
183 mysqldump  -uroot -p --host=localhost --all-databases --skip-quick
184 --quote-names,-Q
185 使用（`）引起表和列名。默认为打开状态，使用--skip-quote-names取消该选项。
186 mysqldump  -uroot -p --host=localhost --all-databases
187 mysqldump  -uroot -p --host=localhost --all-databases --skip-quote-names
188 --replace
189 使用REPLACE INTO 取代INSERT INTO.
190 mysqldump  -uroot -p --host=localhost --all-databases --replace
191 --result-file,  -r
192 直接输出到指定文件中。该选项应该用在使用回车换行对（\r\n）换行的系统上（例如：DOS，Windows）。该选项确保只有一行被使用。
193 mysqldump  -uroot -p --host=localhost --all-databases --result-file=/tmp/mysqldump_result_file.txt
194 --routines, -R
195 导出存储过程以及自定义函数。
196 mysqldump  -uroot -p --host=localhost --all-databases --routines
197 --set-charset
198 添加'SET NAMES  default_character_set'到输出文件。默认为打开状态，使用--skip-set-charset关闭选项。
199 mysqldump  -uroot -p --host=localhost --all-databases 
200 mysqldump  -uroot -p --host=localhost --all-databases --skip-set-charset
201 --single-transaction
202 该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储引擎，仅InnoDB。本选项和--lock-tables 选项是互斥的，因为LOCK  TABLES 会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用--quick 选项。
203 mysqldump  -uroot -p --host=localhost --all-databases --single-transaction
204 --dump-date
205 将导出时间添加到输出文件中。默认为打开状态，使用--skip-dump-date关闭选项。
206 mysqldump  -uroot -p --host=localhost --all-databases
207 mysqldump  -uroot -p --host=localhost --all-databases --skip-dump-date
208 --skip-opt
209 禁用–opt选项.
210 mysqldump  -uroot -p --host=localhost --all-databases --skip-opt
211 --socket,-S
212 指定连接mysql的socket文件位置，默认路径/tmp/mysql.sock
213 mysqldump  -uroot -p --host=localhost --all-databases --socket=/tmp/mysqld.sock
214 --tab,-T
215 为每个表在给定路径创建tab分割的文本文件。注意：仅仅用于mysqldump和mysqld服务器运行在相同机器上。注意使用--tab不能指定--databases参数
216 mysqldump  -uroot -p --host=localhost test test --tab="/home/mysql"
217 --tables
218 覆盖--databases (-B)参数，指定需要导出的表名，在后面的版本会使用table取代tables。
219 mysqldump  -uroot -p --host=localhost --databases test --tables test
220 --triggers
221 导出触发器。该选项默认启用，用--skip-triggers禁用它。
222 mysqldump  -uroot -p --host=localhost --all-databases --triggers
223 --tz-utc
224 在导出顶部设置时区TIME_ZONE='+00:00' ，以保证在不同时区导出的TIMESTAMP 数据或者数据被移动其他时区时的正确性。
225 mysqldump  -uroot -p --host=localhost --all-databases --tz-utc
226 --user, -u
227 指定连接的用户名。
228 --verbose, --v
229 输出多种平台信息。
230 --version, -V
231 输出mysqldump版本信息并退出
232 --where, -w
233 只转储给定的WHERE条件选择的记录。请注意如果条件包含命令解释符专用空格或字符，一定要将条件引用起来。
234 mysqldump  -uroot -p --host=localhost --all-databases --where=” user=’root’”
235 --xml, -X
236 导出XML格式.
237 mysqldump  -uroot -p --host=localhost --all-databases --xml
238 --plugin_dir
239 客户端插件的目录，用于兼容不同的插件版本。
240 mysqldump  -uroot -p --host=localhost --all-databases --plugin_dir=”/usr/local/lib/plugin”
241 --default_auth
242 客户端插件默认使用权限。
243 mysqldump  -uroot -p --host=localhost --all-databases --default-auth=”/usr/local/lib/plugin/<PLUGIN>”

参数说明

2、恢复逻辑备份

#在mysql命令下，用source命令导入备份文件：
mysql>  USE 数据库名;
mysql>  source 备份文件.sql;
 
注意：只能在cmd界面下执行source命令，不能在mysql工具里面执行source命令，会报错，因为cmd是直接调用mysql.exe来执行命令的。