十一、分区技术

1、分区介绍

把一个表，从逻辑上分成多个区域，便于存储数据。

采用分区的前提，数据量非常大。

如果数据表的记录非常多，比如达到上亿条，数据表的活性就大大降低，数据表的运行速度就比较慢、效率低下，影响mysql数据库的整体性能，就可以采用分区解决，

分区是mysql本身就支持的技术。

#查看当前mysql软件是否支持分区；
show variables like '%partition%';

以上的结构，在创建（修改）表时，可以指定表，可以被分成几个区域。

利用表选项：partition 完成。

create table  表名(
    字段信息，
    索引，
)engine myisam charser utf8
partition by 分区算法 (分区字段)(
    分区选项
);

分区算法：

条件分区：list (列表) 、range(范围)、 取模轮询（hash,key）

2、分区算法

1、list分区

list :条件值为一个数据列表。

通过预定义的列表的值来对数据进行分割

例子：假如你创建一个如下的一个表，该表保存有全国20家分公司的职员记录，这20家分公司的编号从1到20.而这20家分公司分布在全国4个区域，如下表所示：

职员表：emp

id name store_id(分公司的id)

12 小宝 1

14 二宝 6

北部 1,4,5,6,17,18

南部 2,7,9,10,11,13

东部 3,12,19,20

西部 8,14,15,16

create table p_list(
    id int,
    name varchar(32),
    store_id int
)engine myisam charset utf8
partition by list (store_id)(
    partition p_north values in (1,4,5,6,17,18),
    partition p_east values in(2,7,9,10,11,13),
    partition p_south values in(3,12,19,20),
    partition p_west values in(8,14,15,16)
);

创建分区表后查看文件，

添加几条数据，测试是否用到了分区：

注意：在使用分区时，where后面的字段必须是分区字段，才能使用到分区。

explain partitions select * from p_list where store_id=18\G

如下查询，没有分区条件，则会到所有的分区里面去查找，即便如此，查询效率也要比单表查询高。

2、Range（范围）

这种模式允许将数据划分不同范围。例如可以将一个表通过月份划分成若干个分区

create table p_range(
    id int,
    name varchar(32),
    birthday date
)engine myisam charset utf8
partition by range (month(birthday))(
    partition p_1 values less than (4),
    partition p_2 values less than(7),
    partition p_3 values less than(10),
    partition p_4 values less than MAXVALUE
);
less than   小于;
MAXVALUE 可能的最大值

插入的数据如下；

分区的效果如下；

3、Hash（哈希）

这种模式允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。

例如可以建立一个对表主键进行分区的表。

create table  p_hash(
    id  int,
    name varchar(20),
    birthday date
)engine myisam charset utf8 
partition by hash(month(birthday)) partitions 5;

4、Key（键值）

上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。

create table p_key(
    id int,
    name varchar(32),
    birthday date
)engine myisam charset utf8
partition by key (id) partitions 5;

3、分区管理

具体就是对已经存在的分区进行增加、减少操作。

（1）删除分区

在key/hash领域不会造成数据丢失(删除分区后数据会重新整合到剩余的分区去)

在range/list领域会造成数据丢失

#求余方式(key/hash):
alter table 表名 coalesce partition 数量;
#范围方式(range/list):
alter table 表名 drop partition 分区名称;

1）删除hash类型分区

删除分区之前，数据如下

执行删除分区的操作：alter table p_hash coalesce partition 4

上图，把5个分表中的4个都删除，只剩下一个

剩余一个分表效果：

剩余唯一一个分区的时候，就禁止删除了，但是可以drop掉整个数据表

2）删除range类型分表(数据有对应丢失)

alter table p_range drop partition   p_1;

（2）增加分区

#求余方式： key/hash
alter table 表名  add  partition partitions  数量;
#范围方式： range/list
    alter table 表名 add partition(
           partition 名称 values  less than (常量)
           或
           partition 名称 values  in (n,n,n)
       );

1) 给p_hash 增加hash分表

alter table p_hash  add partition partitions 6;

增加后，一共有7个分表体现：

上图，分表增加好后，又把数据平均地分配给各个分表存储。

4、特别注意：

create table p_range2(
    id int primary key auto_increment,
    name varchar(32),
    birthday date
)engine myisam charset utf8
partition by range (month(birthday))(
    partition p_1 values less than (4),
    partition p_2 values less than(7),
    partition p_3 values less than(10),
    partition p_4 values less than MAXVALUE
);

注意：创建分区的字段必须是主键或唯一索引的一部分

create table p_range2(
    id  int auto_increment,
    name varchar(32),
    birthday date,
    primary key(id,birthday)
)engine myisam charset utf8
partition by range (month(birthday))(
    partition p_1 values less than (4),
    partition p_2 values less than (7),
    partition p_3 values less than(10),
    partition p_4 values less than MAXVALUE
);

create table p_range3(
    id  int auto_increment,
    name varchar(32),
    birthday date,
    unique key(id,birthday)
)engine myisam charset utf8
partition by range (month(birthday))(
    partition p_1 values less than (3),
    partition p_2 values less than (6),
    partition p_3 values less than(9),
    partition p_4 values less than MAXVALUE
);

十二、分表技术

物理方式分表设计

自己手动创建多个数据表出来

php程序需要考虑分表算法：数据往哪个表写，从哪个表读

1、水平分表

水平分表：是把一个表的全部记录信息分别存储到不同的分表之中。

QQ的登录表。假设QQ的用户有10亿，如果只有一张表，每个用户登录的时候数据库都要从这10亿中查找，会很慢很慢。如果将这一张表分成100份，每张表有1000万条，就小了很多，比如qq0,qq1,qq1…qq99表。

用户登录的时候，可以将用户的id%100，那么会得到0-99的数，查询表的时候，将表名qq跟取模的数连接起来，就构建了表名。比如123456789用户，取模的89，那么就到qq89表查询，查询的时间将会大大缩短。

注册时，如何存储到多张表里面？

$user_id = $redis->incr(‘user_id’);

表单提交过来的内容；

$username = ‘大宝’;

假如我们要分四张表来存储；

$user_id%4 = 获取余数

假如$user_id=8了 余数是0，那我们就存储到user_0表里面了，

user_0表里面的字段 id $user_id ‘大宝’

username_register_name,$user_id)

登录时，如何知道查询那张表？

登录时，我们用名称来登录；

$username = ‘大宝’, 如何知道该名称在那张表里面呢？

$username（通过redis）->user_id->通过user_id算出存储的表；

2、垂直分表（比较常用）

垂直分表：是把一个表的全部字段分别存储到不同的表里边。

有的时候，一个数据表设计好了，里边有许多字段，但是这些字段有的是经常使用的，有的是不常用的。在进行正常数据表操作的时候，不常用的字段也会占据一定的资源，对整体操作的性能造成一定的干扰、影响。

为了减少资源的开销、提升运行效率，就可以把不常用的字段给创建到一个专门的辅表中去。

同一个业务表的不同字段分别存储到不同数据表的过程就是“垂直分表”。

例如：

会员数据表有如下字段：

会员表： user_id 登录名 密码 邮箱 手机号码

            身高  体重  性别  家庭地址  身份证号码

为了使得常用字段运行速度更快、效率更高，把常用字段给调出来,因此数据表做以下垂直分表设计：

会员表(主)user字段：user_id 登录名 密码 邮箱 手机号码

会员表(辅)user_fu字段：user_id 身高 体重 性别 家庭地址 身份证号码

以上把会员表根据字段是否常用给分为两个表的过程就是垂直分表。

再如：存储文章

经常查询的数据 title（标题） author（作者）

十三、数据碎片与维护

在长期的数据更改过程中，索引文件和数据文件，都将产生空洞，形成碎片，我们可以通过一个操作（不产生对数据实质影响的操作）来修改表，

create table t1(id int)engine myisam;
insert into t1 values(1),(2),(3)
insert into t1 select * from t1;

表的原始大小：

删除了一部分数据，应该表的容量会减少一部分，但是没有减掉，

开始整理：

1、optimize table 表名;

整理后的结果，容量减少了一部分。

2、重新修改表引擎

alter table 表名 engine 引擎

注意：修复表的数据及索引碎片，就会把所有的数据文件重新整理一遍，使之对齐，这个过程，如果表的行数比较大，也是比较耗费资源的操作，所以，不能频繁的修复。

如果表的update，delete操作很频繁，可以按周月来修复。

十四、表的范式讲解

第一范式

确保每列的原子性

若每列都是不可再分的最小数据单元(也称最小原子单元)，则满足第一范式。

第二范式

若一个关系满足1NF，并且除了主键以外的其他列，必须完全依赖于该主键(消除非主属性对主属性的部分函数依赖)，则满足第二范式。

第三范式

若一个关系满足2NF，并且除了主键以外的其他列 都不传递依赖于主键列(消除传递依赖)，则满足第三范式。

逆范式

有的时候基于性能考虑，需要有意违反 三范式，适度的冗余，以达到提高查询效率的目的。

相册浏览次数设计案例：

十五、视图

1、视图的定义

视图是由查询结果形成的一张虚拟表，是表通过某种运算得到的一个投影。

创建视图的语法：

create  view  视图名 as  select 语句

说明：

（1）视图名跟表名是一个级别的名字，隶属于数据库；

（2）该语句的含义可以理解为：就是将该select命名为该名字（视图名）；

（3）视图也可以设定自己的字段名，而不是select语句本身的字段名——通常不设置。

（4）视图的使用，几乎跟表一样！

2、视图的作用

准备测试数据；goods表和category表；

（1）可以简化查询

_案例1：查询平均价格前高的栏目。

传统的sql语句写法

select cat_id,avg(shop_price)  pj  from goods  group by cat_id order by pj  desc limit 3;

创建一个视图

create   view   goods_avg_price  as select cat_id,avg(shop_price) pj from goods_avg_price group by cat_id;

创建好了视图，_再次查询平均价格前高的栏目时，我们就可以直接查询视图

select * from goods_avg_price  order by pj desc limit 3;

案例2:查询出商品表，以及所在的栏目名称；

传统的写法

select a.*, b.cat_name from goods a left join category b on a.cat_id=b.id;

创建一个视图

create view  goods_cat  as  select a.*, b.cat_name from goods a left join category b on a.cat_id=b.id;

查询视图；

select * from goods_cat;

（2）可以进行权限控制

把表的权限封闭，但是开放相应的视图权限，视图里只开放部分数据，

比如某张表，用户表为例，2个网站搞合作，可以查询对方网站的用户，需要向对方开放用户表的权限，但是呢，又不想开放用户表中的密码字段。

再比如一个goods表，两个网站搞合作，可以相互查询对方的商品表，比如进货价格字段不能让对方查看。

案例：

1）、创建一个goods表，添加几条数据

给测试的goods表添加一个in_price(进货价格)字段；

2）创建一个视图

create view  goods_v1 as select id,goods_name,shop_price from goods;

3）授权一个账号

grant  权限  on 数据库名称.视图名或表名  to  '用户名称'@'%'  identified by '密码'

表示创建了一个 dahei的用户，密码是123456，权限是在php69库下面的goods_v1视图具有查询的权限；

4）案例测试

3、查询视图

语法：

#视图和表一样，可以添加where 条件
select * from 视图名 [where 条件]

4、修改视图

alter   view   视图名  as   select   XXXX

5、删除视图

drop  view  视图名称

6、查看视图结构

#和表一样的，语法，
desc 视图名称

7、查看所有视图

注意：没有show views语句；

#和表一样，语法：
show   tables;

8、视图与表的关系

视图是表的查询结果，自然表的数据改变了，影响视图的结果。

1）视图的数据与表的数据一一对应时，可以修改

2）视图增删改也会影响表，但是视图并不是总是能增删改的

create view goods_avg_price as select cat_id,max(shop_price) as pj from goods group by cat_id; 
mysql> update goods_avg_price set pj=5678 where cat_id=2; 
ERROR 1288 (HY000): The target table goods_avg_price of the UPDATE is not updatable

3）对于视图insert还应注意，视图必须包含表中没有默认值的列。

注意：向视图里面插入数据时，视图必须包含表中没有默认值的列，才能插入成功，否则就插入失败。

注意：在实际的开发中，不要对视图进行增删改。

十六、SQL编程

1、变量声明

1、会话变量

定义形式：

set  @变量名 =  值；

说明：

1，跟php类似，第一次给其赋值，就算定义了

2，它可以在编程环境和非编程环境中使用！

3，使用的任何场合也都带该“@”符号。

2、普通变量

定义形式：

declare  变量名  类型  【default  默认值】；

说明：

1、它必须先声明（即定义），此时也可以赋值；

2、赋值跟会话变量一样： set 变量名 = 值；

3、它只能在编程环境中使用！！！

说明：什么是编程环境？

编程环境是指 （1）存储过程 （2）函数 （3）触发器。

3、变量赋值形式

语法1：

#此语法中的变量必须先使用declare声明，在编程环境中使用
set 变量名 = 表达式;

语法2：

#此方式可以无需declare语法声明，而是直接赋值，类似php定义变量并赋值。
set @变量名=表达式;

语法3：

#此语句会给该变量赋值，同时还会作为一个select语句输出‘结果集’。
select @变量名:=表达式;

语法4：

#此语句虽然看起来是select语句，但其实并不输出‘结果集’，而是给变量赋值。
select 表达式 into @变量名;

2、运算符

（1）算术运算符

+、-、*、/、%

_注意：运算符

（2）关系运算符

、>=、<、<=、=（等于）、<>（不等于） !=（不等于）

（3）逻辑运算符

and（与）、or（或）、not（非）

3、语句块包含符

所谓语句块包含符，在js或php中，以及绝大部分的其他语言中，都是大括号：{}

它用在很多场合：if， switch, for, function

而mysql编程中的语句块包含符是begin end结构。

4、if判断

MySQL支持两种判断，第一个是if判断，第二个 case判断

单分支

if 条件 then 
    #代码
end if;

双分支

if 条件 then 
    代码1 
else 
    代码2 
end if;

多分支

if 条件 then 
    代码1 
elseif 条件 then 
    代码2 
else 
    代码3
end if;

案例：接收4个数字，

如果输入1则输出春天，2=》夏天 3=》秋天 4 =》冬天 其他数字=》出错

注意：

_通常情况下，“；“表示语句结束，同时向服务器提交并执行。

_但是存储过程中有很多语句，每一句都要以分号隔开，这时候我们就需要使用其他符号来代替向服务器提交的命令。

_通过命令更改语句结束符。

create procedure  p1(num int)
begin
    if num=1 then
        select '春天' as '季节';
    elseif num=2 then
        select '夏天' as '季节';
    elseif num=3 then
        select '秋天' as '季节';
    elseif num=4 then
        select '冬天' as  '季节';
    else
        select '无法无天' as '季节';
    end if;
end$

5、case判断

语法：

case  变量
when 值 then   语句; 
when 值 then   语句; 
else  语句; 
end case ;

案例：接收4个数字，

如果输入1则输出春天，2=》夏天 3=》秋天 4 =》冬天 其他数字=》出错

create procedure  p2(num int)
begin
    case num
    when 1 then  select '春天' as '季节';
    when 2 then  select '夏天' as '季节';
    when 3 then  select '秋天' as '季节';
    when 4 then  select '冬天' as '季节';
    else  select '无法无天' as '季节';
    end case;
end$

6、循环

MySQL支持的循环有loop、while、repeat循环

1、loop循环

语法：

标签名:loop 
    leave 标签名  #退出循环
end loop;

案例：创建一个存储过程，完成计算1到n的和。

create procedure  p3(n int)
begin
    declare i int default 1;
    declare s int default 0;
    aa:loop
        set s=s+i;
        set i=i+1;
        if i>n then
            leave aa;
        end if;
    end loop;
    select s;
end$

2、while循环

 [标签:]while 条件 do 
    #代码
end while;

案例：创建一个存储过程，完成计算1到n的和。

create procedure  p4(n int)
begin
    declare i int default 1;
    declare s int default 0;
    while  i<=n  do 
        set s=s+i;
        set i=i+1;
    end while;
    select s;
end$

十七、函数

1、自定义函数

1、定义语法

create function 函数名(参数) returns 返回值类型
begin 
    #代码
end

说明：

（1）函数内部可以有各种编程语言的元素：变量，流程控制，函数调用；

（2）函数内部可以有增删改等语句！

（3）但：函数内部不可以有select（或show或desc）这种返回结果集的语句！

2、调用

跟系统函数调用一样：任何需要数据的位置，都可以调用该函数。

案例1：返回两个数的和

create function sumhe(num1 int,num2 int) returns int
begin
     return num1+num2;
end$

案例2：定义一个函数，返回1到n的和。

create function nhe(n int)  returns int
begin
    declare i int default 1;
    declare s int default 0;
    while i<=n do 
        set s=s+i;
        set i=i+1;
    end while;
    return s;
end$

注意：创建的函数，是隶属于数据库的，只能在创建函数的数据库中使用。

2、系统函数

（1）数字类

select rand();#返回0到1间的随机数
select * from it_goods  order by rand() limit 2;#随机取出2件商品

select  floor(3.9); #输出3 
select  ceil(3.1); #输出4 
select  round(3.5); #输出4四舍五入
select goods_name,round(shop_price) from goods limit 10;

（2）大小写转换

select ucase('I am a boy!');    -- 转成大写
select lcase('I am a boy!');    -- 转成小写

（3）截取字符串

select left('abcde',3);            -- 从左边截取
select right('abcde',3);        -- 从右边截取
select substring('abcde',2,3);   -- 从第二个位置开始，截取3个，位置从1开始
select left(goods_name,1),round(shop_price) from goods limit 10

 select concat('welcome',':beijing');   -- 字符串相连
 select concat(left(goods_name,1),'...'),round(shop_price) from goods limit 10;

# coalesce(str1,str2)：如果第str1为null，就显示str2 
select coalesce(null,123); 
select goods_name, coalesce(goods_thumb,'无图') from goods limit 10;

select length('锄禾日当午'); #   输出10   显示字节的个数
select char_length('锄禾日当午'); # 输出5   显示字符的个数
select length(trim('  abc  ')); #  trim用来去字符串两边空格
select replace('abc','bc','pache');   #将bc替换成pache

（4）时间类

 select unix_timestamp();      -- 时间戳

-- 将时间戳转成日期格式 from_unixtime(unix_timestamp(),'%Y-%m-%d-%h-%i-%d')
select from_unixtime(unix_timestamp());

# 返回今天的时间日期： 
select  curdate();
-- 取出当前时间
select now()

案例1：比如一个电影网站，求出今天添加的电影；在添加电影时，有一个添加的时间戳。

select id, title from dede_archives where (from_unixtime(添加时间,'%Y-%m-%d') = curdate());

案例2：比如一个电影网站，求出昨天添加的电影；在添加电影时，有一个添加的时间戳。

扩展，如何取出昨天或者指定某个时间的电影：date_sub

基本用法：

date_sub(时间日期时间, interval 数字 时间单位)

说明：

（1）时间单位：可以是year month day hour minute second

（2）数字：可以是正数和负数。

#比如：取出昨天的日期：
select date_sub(curdate(),interval 1 day);
#比如：取出上一个月日期：
select date_sub(curdate(),interval 1 month);

如下案例是：求出前第2天添加的电影数据

select id, title, from_unixtime(add_time,'%Y-%m-%d') from movie where (from_unixtime(add_time,'%Y-%m-%d')) = date_sub(curdate(),interval 2 day);

十八、存储过程

1、概念

存储过程(procedure)

概念类似于函数，就是把一段代码封装起来，当要执行这一段代码的时候，可以通过调用该存储过程来实现。

在封装的语句体里面，可以使用if/else ,case,while等控制结构。

可以进行sql编程。

#查看现有的存储过程。
show procedure status

2、存储过程的优点

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

（1）存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

（2）当对数据库进行复杂操作时(_如对多个表进行时)，可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。

（3）存储过程可以重复使用,可减少数据库开发人员的工作量

（4）安全性高,可设定只有某些用户才具有对指定存储过程的使用权

3、创建存储过程

语法：

create procedure 存储过程名(参数1,参数2,…) 
begin 
     #代码
end

参数的类型：

in（输入参数）： 表示该形参只能接受实参的数据这是默认值，不写就是**；

out（输出参数）：表示该形参其实是用于将内部的数据“传出”到外部给实参；

inout（输入输出参数）：具有上述2个功能。

案例1：查询一个表里面某些语句

create procedure p6(goods_id int)
begin
    select * from goods;
end$

案例2：使用参数

create procedure p8(price float)
begin
select * from goods where shop_price > price;
end$

说明：

（1）存储过程中，可有各种编程元素：变量，流程控制，函数调用；

（2）还可以有：增删改查等各种mysql语句；

（3）其中select（或show，或desc）会作为存储过程执行后的“结果集”返回；

（4）形参可以设定数据的“进出方向”：

（5）存储过程是属于数据库，在哪个数据库里面定义的，就在哪个数据库里面调用。

如下图，在别的数据库里面调用其他数据库里面定义的存储过程时，会报如下提示。

4、调用存储过程

语法：

call 存储过程名称(参数)

在php里面如何调用，

mysql_query('call p7(5)');

5、创建复杂的存储过程

案例1，体会“控制结构”;

定义一个存储过程，有两个参数，第一个参数是价格，第二个参数是一个字符串，

如果该字符串等于’h’ 则就取出大于该价格（第一个参数）商品数据，其他则输出小于该价格的商品；

create procedure p8(price float,str char(1))
begin
    if str='h' then
    select id,goods_name,shop_price from goods where shop_price >= price;
    else
    select id,goods_name,shop_price from goods where shop_price < price;
    end if;
end$

案例2：带有输出参数的存储过程

create procedure p9(in num int,out res int)
begin
    set  res = num*num;
end$

注意：在调用具有输出参数的存储过程时，要使用一个变量来接收。

call p9(8,@res);select @res;

案例3：带有输入输出参数的存储过程

create procedure p10(inout num int)
begin
    set   num=num*num;
end$

注意：在调用时先创建一个变量，调用存储过程时，使用该变量接收。

6、删除存储过程

语法：

drop   procedure   存储过程的名称

十九、触发器

1、简介

（1）触发器是一个特殊的存储过程，它是MySQL在insert、update、delete的时候自动执行的代码块。

（2）触发器必须定义在特定的表上。

（3）自动执行，不能直接调用，

作用：监视某种情况并触发某种操作。

触发器的思路：

监视it_order表，如果it_order表里面有增删改的操作，则自动触发it_goods里面增删改的操作。

比如新添加一个订单，则it_goods表，就自动减少对应商品的库存。

比如取消一个订单，则it_goods表，就自动增加对应商品的库存减少的库存。

2、触发器四要素

监视地点：就是设置监视的表
监视事件；设置监视的那张表的insert ,update,delete操作；
触发时间：设置触发时间，监视表的操作之前，还是之后；
触发事件：满足条件了，设置的触发的操作；

准备测试数据；

3、创建触发器

语法：

create  trigger  trigger_name 
after/before  insert/update/delete  on  表名
for each row
begin 
    #sql语句：（触发的语句一句或多句）
end

_案例个库存。

分析：

监视地点：it_order表

监视事件：it_order表的insert 操作；

触发时间：it_order表的insert 操作之后

触发事件：it_goods表猪的库存减1操作；

create  trigger  t1
after   insert  on  it_order
for each row
begin
update it_goods set goods_number=goods_number-1 where id=1;
end$

注意：以上触发器是有问题的， 无论买谁，都是减少的猪的数量，而且数量是1，

案例2：购买商品，减少对应库存

特别注意：

create  trigger  t1
after   insert  on it_order
for each row
begin
update it_goods set goods_number = goods_number - new.much where id = new.goods_id;
end$

注意：如果在触发器中引用行的值。

对于insert 而言，新增的行用new来表示，行中的每一列的值，用 new.列名 来表示。

测试结果：

案例3：取消订单时，减掉的库存要添加回来

分析：

监视地点：it_order表

监视事件：it_order表的delete操作；

触发时间：it_order表的delete操作之后

触发事件：it_goods表减掉库存再加回来；

create  trigger  t2
after   delete  on  it_order
for each row
begin
update it_goods set goods_number=goods_number+old.much where id=old.goods_id;
end$

注意：

对于delete而言，it_order表删除的行用old来表示，行中的每一列的值，用 old.列名 来表示。

案例4：修改订单时，库存也要做对应修改(修改的数据，有商品的数量，类型)

分析：

（1）取消订单

（2）重新下单

注意：

对于update而言，修改之前行用old来表示，行中的每一列的值，用 old.列名 来表示。

修改之后，用new来表示，行中的每一列的值，用 new.列名 来表示

create  trigger  t3
after   update  on  it_order
for each row
begin
update it_goods set goods_number=goods_number+old.much where id=old.goods_id;
update it_goods set goods_number=goods_number-new.much where id=new.goods_id;
end$

4、删除触发器

语法：

drop trigger 触发器的名称

5、查看触发器

语法：

show triggers

6、before和after的区别

after是先完成数据的增删改，再触发，触发器中的语句晚于监视的增删改，无法影响前面的增删改动作。

就类似于先吃饭，再付钱。

before是先完成触发，再增删改，触发的语句先于监视的增删改发生，我们有机会判断修改即将发生的操作。

就类似于先付钱，再吃饭

典型案例：对于已下的订单，进行判断，如果订单的数量>5，就认为是恶意订单，强制把所定的商品数量改成5

分析：

监视的表 :it_order

监视的事件：it_order表的insert操作

触发的时间：it_order表的insert操作之前

触发的事件：如果订单数量大于5，则改成5

create  trigger  t4
before  insert  on  it_order
for each row
begin
    if new.much>5 then
    set new.much=5;
    end if;
end$

二十、事务操作

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

事务用来管理 insert,update,delete 语句

语法：

BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认

测试数据如下；

$pdo  = new PDO('mysql:host=localhost;dbname=php69','root','root');
$pdo->exec('set names utf8');
//启动事务
$pdo->beginTransaction();
$res1 = $pdo->exec("insert into user values(null,'name3',12,'email1',1)");
$res2 = $pdo->exec("insert into user values(null,'name4',12,'email2',2))");
if(!$res1 || !$res2 ){
    $pdo->rollback();
}else {
    $pdo->commit();
}
echo 'ok';

事务特征：ACID

（1）原子性（Atomicity，或称不可分割性）：
    一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
（2）一致性（Consistency）：
    在事务开始之前和事务结束以后，数据库的完整性没有被破坏。
（3）隔离性（Isolation，又称独立性）：
    数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
-------------------------------------------------------------------------------
    一个事务的执行不能被其他事务干扰。即一个事物内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
如果一个客户端在使用事务操作一个数据(一行/整表)的时候，另外一个客户端不能对该数据进行操作。
行被隔离？整表被隔离？
    如果条件中使用了索引(主键)，那么系统是根据主键直接找到某条记录，这个时候与其他记录无关，那么只隔离一条记录。
    反之，如果说系统是通过全表检索(每一条记录都去检查：没有索引)，被检索的所有数据都会被锁定(整表)
-------------------------------------------------------------------------------
（4）持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务隔离等级

数据库事务有不同的隔离级别，不同的隔离级别对锁的使用是不同的，锁的应用最终导致不同事务的隔离级别。

多个事务之间是隔离的，相互独立的。

但是如果多个事务操作同一批数据，则会引发一些问题，

1、脏读：一个事务，读取到另一个事务中没有提交的数据

2、不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样。

3、幻读：一个事务操作(DML增删改)数据表中所有记录，而另一个事务添加了一条数据，则第一个事务查询不到自己的修改。

设置不同的隔离级别就可以解决这些问题

1、读未提交（Read uncommitted） 产生的问题：脏读、不可重复读、幻读

2、读已提交（read committed）(Oracle默认的) 产生的问题：不可重复读、幻读

3、可重复读（repeatable read）(MySQL默认的) 产生的问题：幻读

4、串行化（Serializable） ——相当于单线程 可以解决所有的问题

注意：隔离级别从小到大安全性越来越高，但是效率越来越低

设置事务的隔离级别

innodb 存储引擎提供事务的隔离级别有：
read uncommitted、read committed、repeatable read 和 serializable。

语法：

set global transaction isolation level 级别字符串;

案例：

#设置事务的隔离级别为 读未提交
set global transaction isolation level Read uncommitted;
#开启事务
start transaction;
#执行sql操作
update account set money = money -500 where id =1;
update account set money = money +500 where id =2;
#此时还没提交，
    另外一个事务可以访问到这个事务还未提交的数据

二十一、DCL管理用户

用户管理

1、添加用户
语法：

create user '用户名'@'主机名' identified by '密码';

例：

create user 'zhangsan'@'localhost' identified by '321';
create user 'lisi'@'%' identified by '321';

2、删除用户

语法：

drop user '用户名'@'主机名';

例：

drop user 'zhangsan'@'localhost';
drop user 'lisi'@'%';

3、修改用户密码
MySQL中提供了多种修改的方式：基本上都必须使用对应提供的一个系统函数：password()函数，需要靠该函数对密码进行加密处理

3.1、update user set password = password(‘新密码’) where user = ‘用户名’;

update user set password = password('def') where user = 'zhangsan' and host = 'localhost';
update user set password = password('abc') where user = 'lisi' and host = '%';

3.2、 set password for ‘用户名’@’主机名’ = password(‘新密码’)

set password for 'zhangsan'@'localhost' = password('321')
set password for 'lisi'@'%' = password('321')

4、查询用户

#切换到mysql数据库
use mysql;
#查询user表
select user,host from user;

注意：通配符% 表示可以在任意主机使用用户登录数据库

权限管理

在mysql中将权限管理分为三类：
1、数据权限：增删改查(select/update/delete/insert)
2、结构权限：结构操作(create/drop)
3、管理权限：权限管理(create user/grant/revoke)，通常只给管理员

1、查询权限

语法：

show grants for '用户名'@'主机名';

例：

show grants for 'lisi'@'%';

2、授予权限

语法：

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
# 创建账号并指定权限
grant  权限  on 数据库.数据表  to '用户名'@'主机名' identified  by ‘密码’

例：

grant select,delete,update on db3.* to 'lisi'@'%';
grant all on *.* to 'zhangsan'@'localhost';

3、撤销权限

语法：

revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

例：

revoke update on db3.* from 'lisi'@'%';
revoke all on *.* from 'zhangsan'@'localhost';

4、刷新权限

flush：刷新，将当前对用户的权限操作，进行一个刷新，将操作的具体内容同步到对应的表中

flush privileges;

忘记root密码

1、 停止mysql服务
            cmd----> net stop mysql 
2：使用无验证方式启动mysql服务
            mysqld --skip-grant-tables #启动服务器但是跳过权限
3：输入mysql命令 直接登陆
            新开窗口，mysql
4：修改root密码
            update mysql.user set password = password('root') where user = 'root' and host = 'localhost';
5：打开任务管理器，手动结束mysqld.exe 的进程