mysql高级-笔记整理

001.理解触发器 trigger

进行数据库应用软件的开发时,
我们有时会碰到表中的某些数据改变,
希望同时引起其他相关数据改变的需求,
利用触发器就能满足这样的需求.
它能在表中的某些特定数据变化时自动完成某些查询.
运用触发器不仅可以简化程序,
而且可以增加程序的灵活性.

触发器是一类特殊的事务,
可以监视某种数据操作(insert/update/delete)
并触发相关操作(insert/update/delete)

案例一:当向一张表中添加或删除记录时, 需要在相关表中进行同步操作.
比如: 当一个订单产生时, 订单所购的商品的库存量相应减少.

案例二:当表上某列数据的值与其他表中的数据有联系时.
比如: 当某客户进行欠费消费,
可以在生成订单时通过设计触发器判断该客户的累积欠款是否超过了最大限度.

案例三: 当需要对某张表进行跟踪时.
比如: 当有新订单产生时, 需要及时通知相关人员进行处理,
此时可以在订单表上设计添加触发器加以实现.

002.动手写第1个触发器

• 查看已有触发器(triggers): show triggers \G

• 删除已有触发器(triggers): drop trigger 触发器名称

• 创建触发器的语法

create trigger 触发器名称
after/befor (触发时间)
insert/update/delete (监视事件)
on 表名(监视地址)
for each row
begin
sql1;
...
sqln;
end

自己的第一触发器

商品表: goods

订单表: ord

当下1个订单时, 对应的商品要相应减少(买几个商品少几个库存)

分析:
监视谁:   ord
监视动作: insert 
触发时间: after
触发事件: update


-- 测试表
create table goods(
    gid int,
    name varchar(20),
    num smallint
);

create table ord(
    oid int,
    gid int,
    much smallint
);

insert into goods values
(1,'cat',34),
(2,'dog',65),
(3,'pig',21);


insert into ord values(123, 1, 2);

delimiter $

create trigger t1
after
insert
on ord
for each row
begin
update goods set num=num-2 where gid=1;
end$

ps: 触发器中 begin 和end 中间的sql是以 ;结尾的,所以我们要用 delimiter $修改最外层的结束符号

• 如上写的, 不管我们怎么增加订单, goods表中 gid=1 都会 num-2, 那么改如何做呢? 如果能拿到具体的 变量就好了. 如下 003节

003.触发器中引用行变量

• insert(被监视的语句), 产生的数据,能否在触发器中引用到?

加入变量

create trigger t2
after
insert
on ord
for each row
begin
update goods set num=num-new.much where gid=new.gid;
end$

如上, 用了 update goods set num=num-new.much where gid=new.gid; 这个 new 代表是ord表中新增加的那一行 new.much 调用 much 列.

再举例, 如果删除订单,则恢复商品表中的数据. 仅供举例,不考虑逻辑上的对错.

再写一个触发器 t3,监视 ord表 中的删除

分析:
监视谁:   ord
监视动作: delete 
触发时间: after
触发事件: update


create trigger t3
after
delete
on ord
for each row
begin
update goods set num=num+old.much where gid=old.gid;
end$

如上, 用了 update goods set num=num+old.much where gid=old.gid; 这个old 代表的是 ord表中刚被删除的那一行 old.much调用much列.

自己的疑惑? 如果服务重启,那么触发器是否还存在? 测试了, 重启之后,触发器还在,不会随着服务的重启而丢失.

004.再谈行变量

• 上面1个是新增, 1个是删除,用到了 new 和 old ,那么改的时候,如何操作呢?
• 如下图,改之前是 old ,修改之后为 new

分析:
监视谁:   ord
监视动作: update 
触发时间: before
触发事件: update


delimiter $

create trigger t4
before
update
on ord
for each row
begin
update goods set num=num+old.much-new.much where gid=old.gid;
end$

如上, 用到了 update goods set num=num+old.much-new.much where gid=old.gid;, 修改之前的数据是 old来代表, 修改之后的数据用 new 来代表.

思考

1. before 目前似乎没有看出与 after 的区别?
2. 如果剩余3头猪, 但客户买了10头猪,发生什么情况?能否预防?
3. 能否在 购买量 much > 库存量 num 时, 把much自动改为num ? ---- 用 before 可以解决, 见 005节

005.触发器做简单编程

如004节

1. before 目前似乎没有看出与 after 的区别?
2. 如果剩余3头猪, 但客户买了10头猪,发生什么情况?能否预防?
3. 能否在 购买量 much > 库存量 num 时, 把much自动改为num ? ---- 用 before 可以解决

create trigger t6
before
insert
on ord
for each row
begin

declare
rnum int;

select num into rnum from goods where gid=new.gid;

if new.much > rnum then
    set new.much := rnum;
end if;

update goods set num=num-new.much where gid=new.gid;
end$

如上: declare 是定义变量

declare
rnum int;

select num into rnum from goods where gid=new.gid;

if new.much > rnum then
    set new.much = rnum;
end if;

然后下面对变量进行 if 判断操作. sql里面是可以进行 if判断的. 上述,必须用 before.

006.for each row是干吗的

• 在 oracle 触发器中,触发器分语句级触发器,和行级触发器

比如

create trigger tn
after update
on xxtable
for each row 
begin
 sqlN;
end$

执行: update xxtable set xxx=xxx where id>100; # 修改了100行.
那么 sqlN, 会被触发几次? # 会被触发100次.
所以, 如下
for each row 指的是 每一行受影响, 触发器都会被执行, 叫做 行级触发器.

在 oracle 中
for each row 如果不写,
无论 update 语句一次影响了多少行, 都只执行1次,叫做 语句级触发器

**比如: **
1人下了订单, 买了5件商品,insert 5次, 可以用行级触发器, 修改5次库存
用语句级触发器. insert 一条发货提醒

遗憾的是: mysql 目前不支持 语句级触发器, 所以在 mysql 中 for each row 必须写, 因为mysql只支持 行级触发器

007.存储过程概念

在以前的语言中, 有一种概念叫 过程 procedure, 和 函数 function
在 PHP 中,没有过程, 只有 函数

过程: 封装了若干条语句,调用时,这个封装体执行.
函数: 是一个有返回值的 过程
过程: 是一个没有返回值的 函数

存储过程: 我们把若干条sql封装起来, 起个名字,就叫做 过程, 把此过程存储在数据库中,叫做存储过程.

注意: 存储过程,是可以在触发器中调用的,但是最后不用用 select 输出结果集, 可以实现对表的 增删改查

存储过程的创建语法

create procedure procedureName()
begin
	-- sql语句
end$

• 查看已有的procedure : show procedure status \G
• 调用存储过程: call procedureName()
• 删除已有的 procedure : drop procedure procedureName

示例

create procedure p1()
begin
select 2+3;
end$

-- 调用
call p1()$

008.引入变量与控制结构

• 存储过程 是可以编程的, 意味着可以使用变量,表达式,控制结构 来完成复杂的功能.
• 在存储过程中, 用 declare 来声明变量

1. 引入变量

格式: declare 变量名 变量类型 [default 默认值]

create procedure p2()
begin   
    declare age int default 90;
    declare height int default 175;
    select concat('年龄',age,'身高',height) from dual;
end$

-- 执行
call p2()$

2. 变量计算

• 存储过程中, 变量可以sql语句中合法的运算,如 ±*/
• 注意的是, 运算的结果,如果赋值给变量
• 语法 set 变量名 := 值

--p3 变量开始计算
create procedure p3()
begin 
    declare age tinyint unsigned default 90;
    declare height tinyint unsigned default 175;

    set age := age+20;
    select concat('20年后的年龄', age, '岁') as '20年后';
end$

-- 执行
call p3()$

3. if/else控制结构

• if/else 语法结构

if condition then
statement
[elseif condition then]
statement
else
statement
end if;

create procedure p4()
begin
    declare age tinyint unsigned default 90;
    declare height tinyint unsigned default 175;
    if age>70 then
        select '古稀之年' as '描述';
    else 
        select '风华正茂' as '描述';
    end if;
end$

-- 执行
call p4()$

009.存储过程的参数传递

• 存储过程的括号里,可以声明参数
• 语法是 [in/out/inout] 参数名 参数类型

create procedure p5(width int, height int)
begin
    select concat('你的面积是', width*height) as area;
    if width > height then
        select '你挺胖';
    elseif width < height then
        select '你挺瘦';
    else 
        select '你挺方';
    end if;
end$

-- 执行
call p5(3,4);

010.过程中使用循环结构

• 3大控制结构: 顺序, 选择, 循环

• while 的语法格式

while 条件 do
	-- sql
end while;

求 1-100的和

create procedure p6()
begin
    declare total int default 0;
    declare num int default 0;

    while num<=100 do
        set total := total + num;
        set num := num + 1;
    end while;

    select total as '1-100的总和';

end$


-- 执行

call p6()$

011.何为输出型参数

• in 是接受外界参数
• out 是往外输出

在p6的基础上,加上参数,可以求出1-n的和

delimiter $

create procedure p7(in n int)
begin
    declare total int default 0;
    declare num int default 0;

    while num <= n do
        set total := total + num;
        set num := num + 1;
    end while;

    select total as '1-100的总和';

end$

-- 执行
call p7(10)$

out参数

create procedure p8(in n int, out total int)
begin
    declare num int default 0;
    set total := 0;
    while num < n do
        set num := num + 1;
        set total := total + num;
    end while;
end$

-- 执行
call p8(100, @sumary)$

select @sumary$

inout 参数

create procedure p9(inout age int)
begin
    set age := age + 20;
end$

-- 执行
set @currage = 18$
call p9(@currage)$
select @currage$

012.Case结构的用法

case语法

case case_value
	when when_value then statement_list
	[when when_value then statement_list] ...
	[else statement_list]
end case

OR

case 
	when search_condition then statement_list
	[when search_condition then statement_list] ...
	[else statement_list]
end case

示例

create procedure p10()
begin
    declare pos int default 1;

    set pos := floor(5*rand());

    case pos
        when 1 then select 'stall flying';
        when 2 then select 'fall in sea';
        when 3 then select 'in the island';
        else select 'i dont know';
    end case;

end$

-- 执行
call p10()$ 会随机结果显示

013.repeat循环结构

• repeat 循环

repeat
	sql statement;
	[sql statement;]
until condition end repeat;

create procedure p11()
begin
	declare total int default 0;
    declare i int default 0;
	
	repeat
        set i := i+1;
		set total := total + i;
	until i >= 100 end repeat;

    select total;

end$
-- 执行

call p11()$

014.游标的概念 cursor

• cursor 游标, 游标的标志: 1条 sql, 对应N条结果集资源,取出资源的接口/句柄,就是游标, 沿着游标,可以一次取出1行.

游标cursor使用步骤

1. declare 声明; declare 游标名 cursor for select_statement;
2. open 打开; open 游标名
3. fetch 取值; fetch 游标名 into var1, var2, […]
4. close 关闭; close 游标名;

示例

create procedure p12()
begin
    declare row_gid int;
    declare row_num int;
    declare row_name varchar(20);
    declare getGoods cursor for select gid,num,name from goods;

    open getGoods;

    fetch getGoods into row_gid,row_num,row_name;
	select row_num, row_name;
    close getGoods;

end$

-- 执行
call p12()$

015.游标循环

create procedure p14()
begin
    declare row_gid int;
    declare row_num int;
    declare row_name varchar(20);

    declare cnt int default 0;
    declare i int default 0;

    declare getGoods cursor for select gid,num,name from goods;

    select count(*) into cnt from goods;

    open getGoods;

    repeat 
        set i := i+1;
        fetch getGoods into row_gid,row_num,row_name;
        select row_num, row_name;
    until i >= cnt end repeat;

    close getGoods;

end$

-- 执行
call p14()$

016.declare处理条件

• 游标 取值越界时, 有没有标识? 利用标识来结束.
• 在 mysql cursor中, 可以 declare continue handler 来操作1个越界标识
• declare continue handler for NOT FOUND 语句

create procedure p15()
begin
    declare row_gid int;
    declare row_num int;
    declare row_name varchar(20);

    declare you int default 1;
    declare getGoods cursor for select gid,num,name from goods;
    declare continue handler for NOT FOUND set you := 0;

    open getGoods;

    repeat 
        fetch getGoods into row_gid,row_num,row_name;
        select row_num, row_name;
    until you=0 end repeat;

    close getGoods;

end$

-- 执行 
call p15()$

如上案例,最后 pig 取了2次, 解决方法如下章.

017.对比continue与exit的区别

• 016 处理的时候,出现了 pig 被取了2次.

大概的思路

第4次执行 repeat 时, fetch–>没数据–>触发 NOT FOUND–>set you := 0–>continue–>继续执行(后面的sql语句)–>select row_num, row_name–>最后一行,被取出2次.

思考: 如果 NOT FOUND 后,后面的select不再执行,最后也就不会多取一行了. 解决方法: 声明处理的 handler 不再是 continue, 而是 exit, 即可达到目的.

declare exit handler for NOT FOUND statement;

• exit 与 continue 的区别是, exit 触发后, 后面的语句不再执行. 所以将 017的代码修改一下即可.

create procedure p16()
begin
    declare row_gid int;
    declare row_num int;
    declare row_name varchar(20);

    declare you int default 1;
    declare getGoods cursor for select gid,num,name from goods;
    declare exit handler for NOT FOUND set you := 0;

    open getGoods;

    repeat 
        fetch getGoods into row_gid,row_num,row_name;
        select row_num, row_name;
    until you=0 end repeat;

    close getGoods;

end$

-- 执行
call p16()$

• 除 continue, exit 外, 还有一种 undo handler
• continue 是触发后, 后面的语句继续执行
• exit 是触发后,后面的语句不再执行
• undo 是触发后,前面的语句撤销(但是,目前mysql还不知道undo)

018.游标循环读取的正确逻辑

如上, 016节 最后重复了2遍 pig ,017节 把 continue 改成 exit 之后, 解决了这个问题. 那么, 如果还是用 continue 该怎么办呢? 如下.

create procedure p17()
begin
    declare row_gid int;
    declare row_num int;
    declare row_name varchar(20);

    declare you int default 1;
    declare getGoods cursor for select gid,num,name from goods;
    declare continue handler for NOT FOUND set you := 0;

    open getGoods;

        fetch getGoods into row_gid,row_num,row_name;

        repeat
        select row_num,row_num;
        fetch getGoods into row_gid,row_num,row_name;
        until you=0 end repeat;

    close getGoods;

end$

-- 执行
call p17()$

改成 while

create procedure p18()
begin
    declare row_gid int;
    declare row_num int;
    declare row_name varchar(20);

    declare you int default 1;
    declare getGoods cursor for select gid,num,name from goods;
    declare continue handler for NOT FOUND set you := 0;

    open getGoods;

        fetch getGoods into row_gid,row_num,row_name;

        while you=1 do
            select row_num,row_num;
            fetch getGoods into row_gid,row_num,row_name;
        end while;

    close getGoods;

end$

-- 执行

call p18()$

019.MySQL权限检查原理

• 用户连接到mysql, 并做各种查询.

[用户] <—> [服务器]
分为2个阶段:

1. 你有没有权限连接上来
2. 你有没有权限执行此操作 (如 select, update 等等)

• 对于1: 服务器如何判断用户有没有权限连接上来?

依据 3 个参数

1. 你从哪里来? host
2. 你是谁? user
3. 你的密码是多少? password

用户的这3个信息,存储在 mysql.test 表中.

• mysql 修改完权限之后,要冲刷权限 flush privileges;

• 如何修改用户的密码

update user set password=password(‘123456’) where xxx;
flush privileges;

020.全局授权与收回

注意: 如下的 192.168.1.% 的写法都换成 localhost 自己本机测试, 用ip不成功,localhost可以

• 新增一个用户

语法: grant [权限1,权限2,权限3..] on *.* to 新用户名@'localhost' identified by 'password';

上述 . 表示 所用数据库的所有表

常用权限有: all,create, drop,insert,delete,update,select

例如

grant all on *.* to lisi@‘192.168.1.%’ identified by ‘123456’;

**注意: 自己本机测试,添加ip无法访问, 填入 localhost 可以访问. **

这样的话,我们就新建了一个 lisi 的用户, 我们再用 lisi的用户登录下.

mysql -ulisi -p111111;

如上,报错,进不去的.因为上面我们设置了 192.168.1.%, 所以登录的时候要注明. 如下:

mysql -h192.168.1.4 -ulisi -p123456;

• 收回权限 revoke

语法: revoke all on *.* from lisi@'192.168.1.%';
记得之后, flush privileges;

021.库及表级别授权与收回

1. 数据库级别权限 授权与收回

• 查看 db 级别的权限 user mysql; select * from db \G

• 将单独库赋给一个用户, 数据库级别的权限授予

grant all on 数据库.* to 用户名@‘localhost’ identified by ‘密码’

例如 grant all on tp5.* to lisi@localhost identified by '123456' 如果用户已经存在, 后面的 identified by 密码 可以不写.

• 收回权限

revoke all on 数据库.* from 用户名@‘localhost’;
flush privileges;

例如 revoke all on tp5.* from lisi@localhost;

2. 表级别权限 授权与收回

• 查看表级别的权限 use mysql; select * from tables_priv \G

• 授权

语法: grant 权限[例如all,create,update等] on 数据库.数据表 to 用户名@localhost;

例如:

grant insert,update,select on gy3.goods to lisi@localhost;
flush privileges;

如上,赋予表权限之后,用户 lisi 只能看到 数据库gy3,且只能看到goods表,且只能 insert,update,select.

• 提示: mysql的权限控制,甚至可以精确到列(自己研究)

• 收回权限

语法: revoke all on 数据库.数据表 from 用户名@‘localhost’;

例如:

revoke all on gy3.goods from lisi@localhost;
flush privileges;

022.主从复制原理 replication

• 原理
  有2台服务器,主服务器和从服务器, 主服务器中的内容,在从服务器中也有一份.

• 大概步骤

1. 主服务器要配置 binlog
2. 从服务器要配置 relaylog
3. 从服务器如何有权读取 master 的binlog? 答: 授权, master要授予slave账号
4. 从服务器用账号连接master

023.主从配置过程

数据库复制 replication 的实现原理

1. 主服务器凡运行语句, 都产生一个二进制日志 binlog
2. 从服务器不断读取主服务器的 binlog
3. 从主服务器读到的 binlog转换为自身可执行的 relaylog
4. 执行 relaylog

实现步骤

1. 首先确保主服务器打开二进制日志功能.

这样, 主服务器一旦有数据变化,立即产生二进制日志.

2. 从服务器也需要开启二进制日期和relay日志功能

这样可以从主服务器读取binlog并产生relaylog

3. 在主服务器建立一个从服务器的账号,并授予读binlog的权限.

4. 指定从服务对应的主服务器,开启从服务器.

具体流程

1. 前提有2台服务器,ip分别为 199, 200, 200做从服务器.
2. 199 mysql 做 主服务器
3. 保证主从 3306 端口互通.
4. 配置主服务器, 打开 binlog

# 给服务器起一个唯一的id
server-id=199
# 开启二进制日志
log-bin=mysql-bin
# 指定日志格式
binlog-format = mixd/row/statement
重启 mysql

已经能够充当 master 服务器.

PS

binlog-format = row/statement/mixed

• statement: 2进制记录执行语句, 如 update …
• row: 2进制记录的是磁盘变化

哪个好?

update age=age+1 where id=3; //语句长而磁盘变化少,宜用 row
update salary=salary + 100; //语句短,但影响上万行,磁盘变化大,宜用statement

如果分不清,就选择mixed 让系统自己去识别.

024.启动主从