mysql 视图、触发器、存储过程

发表于

视图

基本操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
# 视图
## 创建视图
create view test_view as select id,name,age from tb_test where status = 1 with check option;

## 插入视图数据
insert into test_view (name,age,status) values ('uiste',18,1);
-- insert into test_view (name,age) values ('uiste',18)
-- 1369 - CHECK OPTION failed 'riskserve_test.test_view', Time: 0.009000s

-- insert into test_view (name,age,status) values ('uiste',18,1)
-- 1054 - Unknown column 'status' in 'field list', Time: 0.011000s

## 修改视图数据
update test_view set age =19 where id=1;
-- Affected rows: 1, Time: 0.022000s

delete from test_view where id =1;
-- Affected rows: 1, Time: 0.012000s
## 查询视图数据
select * from test_view;

## 删除视图
drop view test_view;
-- OK, Time: 0.006000s

## 查询视图结构
desc view test_view;

视图应用&好处

提高了重用性，就像一个函数
对数据库重构，却不影响程序的运行
提高了安全性能。可以对不同的用户
让数据更加清晰

视图不可编辑表数据的情况

  1. 包含聚合函数、distinct、group by、having、union、union all
  2. 常量视图。
  3. select包含子查询。
  4. 包含连接操作。
  5. from 一个不能更新的视图。
  6. where 子句的子查询引用了 from 子句中的表。

触发器

创建

1
2
3
4
5
6
7
create trigger [触发器名称] [触发器的执行时间] [执行的动作点] on [表名] for each row [函数 或者动作]
触发器的执行时间 :before, after 
执行的动作点:insert, update, delete 
函数:begin end;
或者动作:update ,insert

触发器类似于框架事件

基本操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
# 触发器
-- 触发器 类似于 框架 事件
-- create trigger [触发器名称] [触发器的执行时间] [执行的动作点] on [表名] for each row [函数 或者动作]
-- 触发器的执行时间 :before, after 
-- 执行的动作点:insert, update, delete 
-- 函数:begin sql end;
-- 或者动作:update ,insert
## 创建触发器
DROP TRIGGER IF EXISTS `test_trigger`;
DELIMITER $$
create trigger test_trigger 
before insert
on tb_test 
for each row 
begin
	set new.created_at=unix_timestamp(now()),new.updated_at=unix_timestamp(now());
	-- 其它表操作可以使用SQL语句
	-- update tb_other set num=num+1,updated_at=unix_timestamp(now()) where id = NEW.id;
	-- 本表操作只能修改字段
end;
$$
DELIMITER;

# 插入数据
insert into tb_test (`name`,`age`,`status`) values ('mandy',17,1);

# 查询时create_at 、updated_at 自动生成数据
select * from tb_test;

相对INSERT触发器中使用NEW关键字，UPDATE/DELETE触发器有OLD 和 NEW关键字.

存储过程

定义

存储过程和函数可以理解为一段 SQL 语句的集合，它们被事先编译好并且存储在数据库中

创建一个存储过程的语法为:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
create procedure 存储过程名(参数列表) 
begin
存储过程体
end
call 存储过程名(参数列表)
如下简单存储过程

DROP PROCEDURE IF EXISTS test_procedure;
DELIMITER $$
CREATE PROCEDURE test_procedure(in x int, out y varchar(32)) -- in 表示输入 
BEGIN
  -- declare y int default 0; -- 变量声明和赋默认值
  select name into y from tb_test where id = x; -- 执行
END
$$
DELIMITER ;

call test_procedure(1, @a);
select @a;

参数类型

  1. 从上面的过程中我们了解到存储过程有参数类型这种说法，它的类型可以取值有三个:in、out、inout。
  2. 其中它们的意义如下:
    (1) in 表示只是用来输入。
    (2) out 表示只是用来输出。
    (3) inout 可以用来输入，也可以用作输出。

存储过程理解

  1. 调用存储过程与直接执行 SQL 语句的效果是相同的，但是存储过程的一个好处是处理逻辑都封装在数据库端。
  2. 当我们调用存储过程的时候，我们不需要了解其中的处理逻辑，一旦处理逻辑发生变化，只需要修改存储过程即可，对调用它的程 序完全无影响。 3. 调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，可以提高数据处理的效率
    show create PROCEDURE user_procedure_out;

    变量

  3. 存储过程中是可以使用变量的，我们可以通过 declare 来定义一个局部变量，该变量的作用域只是 begin….end 块中。
  4. 变量的定义必须写在符合语句的开头，并且在任何其他语句的前面。我们可以一次声明多个相同类型的变量，我们还可以使用default来赋予默认值。 3. 定义一个变量的语法为: declare 变量名 1 [,变量名 2…] 变量类型 [default 默认值]
  5. 上面的变量类型就是 MySQL 支持的类型，而变量名的取值规则也是一个老生常谈的话题了，就不赘述了。
  6. 变量可以直接赋值，还可以通过查询赋值。
  7. 直接赋值就是使用 set 来进行赋值，它的语法为: set 变量名 1 = 表达式 1 [,变量名 2=表达式 2…]
  8. 也可以通过查询来将结果赋值给变量，它需要要求查询返回的结果只有一行，语法范例: select 列名列表 into 变量列表 from 表名 其他语句;

存储过程中的数据类型

  1. 数值类型:Int,float,double,decimal
  2. 日期类型:timestamp,date,year
  3. 字符串:char,varchar,text
    timestamp: 是使用最多的数据类型-》十位数的时间戳 text:一旦用到text类型的时候就可以考虑分表; 如果部分表的话，该字段的查询不会直接放在一起查 询，因为多个字段查询中其中如果有text字段的话，就容易遇到慢查询 所以通常的话，如果需要这个值的时候会根据id单独拿这个text字段

流程控制语句其他语法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
if 的语法格式为:
if 条件表达式 then 语句
[elseif 条件表达式 then 语句] ....
[else 语句] end if
case 的语法格式 首先是第一种写法: case 表达式
when 值 then 语句 when 值 then 语句 ...
[else 语句]
end case
然后是第二种写法:
case
when 表达式 then 语句 when 表达式 then 语句 ....
[else 语句]
end case
loop 循环 语法格式为: [标号:] loop
循环语句 end loop [标号]
while
while a>100 do
循环语句
End while
Repeat //游标 SQL语句1
UNTIL 条件表达式
END Repeat;
Loop SQL语句
  所有的条件判断和跳出需要自己实现
End loop
leave 语句用来从标注的流程构造中退出，它通常和 begin...end 或循环一起使用 leave 标号;

声明语句结束符，可以自定义: DELIMITER [符合]
delimiter $$
$$

游标

  1. 游标也有的资料上称为光标。
  2. 我们可以在存储过程中使用游标来对结果集进行循环的处理。
  3. 游标的使用步骤基本分为:声明、打开、取值、关闭。
    语法:
    1
    2
    3
    4
    5
    DECLARE test_cursor CURSOR FOR 结果集; //声明游标
    OPEN test_cursor;
    fetch test_cursor into 变量名,
    CLOSE test_cursor;
    DECLARE CONTINUE HANDLER FOR NOT FOUND //结果集查询不到数据自动跳出

总结:

  1. 游标的声明的语法: declare 游标名称 cursor for 查询语句;
  2. 打开光标的语法: open 游标名称;
  3. 获取游标数据: fetch 游标名称 into 变量名 1 [,变量名 2 ….]
  4. 关闭游标的语法: close 游标名称;
  5. 游标的基本使用须知:对某个表按照循环的处理，判断循环结束 的条件是捕获 not found 的条件，当 fetch 光标找不到下一条记录的 时候，就会关闭光标 然后退出过程。
  6. 可能有过 Pascal 编程经验的朋友们都会知道，声明的顺序也是很 重要的，在 SQL 中，我们使用 declare 定义的顺序是:变量、条件、 游标、应用程序
    操作 查询出来的数据会放置于临时表中，然后再通过游标去读取数据。 案例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
delimiter $$
create procedure exchange(out count int ) begin
declare supply_id1 int default 0; 
declare amount1 int default 0;
-- 游标标识
declare blag int default 1;
-- 游标
declare order_cursor cursor for select supply_id,amount from order_group; -- not found 这个异常进行处理
declare continue handler for not found set blag = 0;
set count = 0;
-- 打开游标
open order_cursor;
-- 遍历
read_loop: LOOP
fetch order_cursor into supply_id1,amount1; if blag = 0 then
leave read_loop; end if;
if supply_id1 = 1 then
set count = count + amount1;
end if;
end loop read_loop;
end;
$$
delimiter ;
call exchange(@count); select @count;

存储过程优点

  1. 第一点优势就是执行速度快。因为我们的每个 SQL 语句都需要经 过编译，然后再运行，但是存储过程都是直接编译好了之后，直接 运行即可。
  2. 第二点优势就是减少网络流量。我们传输一个存储过程比我们传 输大量的 SQL 语句的开销要小得多。
  3. 第三点优势就是提高系统安全性。因为存储过程可以使用权限控 制，而且参数化的存储过程可以有效地防止 SQL 注入攻击。保证了 其安全性。
  4. 第四点优势就是耦合性降低。当我们的表结构发生了调整或变动 之后，我们可以修改相应的存储过程，我们的应用程序在一定程度 上需要改动的地方就较
    小了。
  5. 第五点优势就是重用性强。因为我们写好一个存储过程之后，再 次调用它只需要一个名称即可，也就是”一次编写，随处调用”,而且 使用存储过程也可以让
    程序的模块化加强。
    //打开游标 //关闭游标

存储过程的缺点

  1. 第一个缺点就是移植性差。因为存储过程是和数据库绑定的，如 果我们要更换数据库之类的操作，可能很多地方需要改动。
  2. 第二个缺点就是修改不方便。因为对于存储过程而言，我们并不 能特别有效的调试，它的一些 bug 可能发现的更晚一些，增加了应 用的危险性。
  3. 第三个缺点就是优势不明显和赘余功能。对于小型 web 应用来说， 如果我们使用语句缓存，发现编译 SQL 的开销并不大，但是使用存 储过程却需要检查
    权限一类的开销，这些赘余功能也会在一定程度 上拖累性能。

案例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
//插入更新利息 
$sql = "CREATE PROCEDURE ADD_ACCOUNT_INTEREST_AND_RETURN_AMOUNT (IN accountId INT, IN dayInteresetRate decimal(10,10))  

BEGIN

DECLARE originAmount decimal(28,14) DEFAULT 0;

DECLARE afterUpdateAmount decimal(28,14) DEFAULT 0;

DECLARE changeAmountValue decimal(28,14) DEFAULT 0;
 
SELECT `amount` INTO originAmount FROM user_assets_account WHERE id=accountId;

SET changeAmountValue = originAmount*dayInteresetRate;
 
UPDATE user_assets_account SET `amount`=`amount`+changeAmountValue WHERE id=accountId;
 
SELECT `amount` INTO afterUpdateAmount FROM user_assets_account WHERE id=accountId;
 
SELECT originAmount,afterUpdateAmount,changeAmountValue;

END;";
return Account::getDb()->createCommand($sql)->execute();


$db = Account::getDb();
$transaction = $db->beginTransaction();
try {
    $amountSql = "CALL ADD_ACCOUNT_INTEREST_AND_RETURN_AMOUNT({$toAccount->id},{$dayInterestRate})";
    $data = $db->createCommand($amountSql)->queryOne();
    
    TradeService::addInterestLog($interestAcoount, $toAccount, $data['changeAmountValue'], $data['afterUpdateAmount']);
    $transaction->commit();
    return true;
} catch (\Exception $ex) {
    $transaction->rollBack();
    throw $ex;
} finally {
    
}