mysql学习笔记之开发篇二
视图
什么是视图:
一种虚拟的表
特点:
简单
安全
数据独立
视图的基本操作
创建视图
create or replace view myview1 as select * from country;
create or replace view myview2 as select 3.1415926 as pi;
create view myview3 as select(select name from country where id=1);
含有以下字段的视图是没法自动更新的
运算函数
聚合字段
常量视图
jion
from一个不能更新的视图
where字句的子查询引用了from子句的变量
with [local基本的|cascaded全部条件] check option
是否允许字段更新后不再满足条件
查看视图
show create view v2
删除视图
drop view v1
存储过程和函数
存储过程和函数的定义:
事先通过编译并存储在数据库中的一段sql语句的集合。
存储过程和函数的区别:
函数必须有返回值,存储过程必须有
函数从参数只能是in,存储过程的参数可以是 in out inout
创建存储过程或者函数
创建简单的存储过程
无参数的存储过程
```
delimiter //
drop procedure if exists my_proce11//
create procedure mysp11() select now()//
```
调用无参数的存储过程
call mysp11();
创建含有输入输出的存储过程
delimiter //
drop procedure if exists myprocedure//
create procedure myprocedure(in pid int,in age int,out pcount int)
sql security definer
begin
select name from country where id=pid and user_age=age;
select FOUND_ROWS() INTO pcount;
end
//
call myprocedure(2,3,@z)//
select @z;
删除存储过程或者函数
drop procedure if exists myprocedure
查看存储过程或者函数
show procedure status like '%xx%';
show create procedure myprocedure\G;
变量的使用
变量的定义
delclare var 123;
变量的赋值
select xxx into var2 from xxx
set @var1 = 123;
定义条件和处理
捕获
declare continue handler for 1062 set @x = 1;
事先定义
declare duplicatekey condition fro sqlstate '2300';
捕获sqlexception
declare continue handler for sqlexception set @x2 = 1;
光标的使用–对循环的结果集进行循环的处理
声明光标
declare cursor_name cursor for select_statment
打开光标
open cursor_name
fetch 光标
fetch cursor_name into var_name,...
关闭光标
colse cursor_name
流程控制
支持if,case,loop,leave,iterate,repeate,while 的语法格式
用的比较少,当需要的时候再去查阅
触发器
定义
是与表相关的数据库对象,在满足定义条件时出发,并执行触发器中定义的语句集合
只能创建在永久性的表上面
创建触发器
create trigger trigger_name trigger_time trigger_event on tabname for
each row trigger_stmt
trigger_time 可以是after或者before
trigger_event可以是insert after before
例子
drop trigger my_trigger;
delimiter $$
create trigger my_trigger
after insert on country for each row begin
insert into test(id)values(22);
end;
$$
delimiter ;
/*drop trigger my_trigger;*/
insert into country(id,name,user_age)values(3,3,'xxxxx');
删除触发器
drop trigger my_trigger;
查看触发器
show triggers;
select * from triggers where trigger_name='xxx';
触发器的使用注意
1 触发器可以使用out或者inout参数的函数或者存储过程,但是不能使用直接返回参数的函数或者存储过程
2 不能再触发器中使用事务开始或者结束语句
触发器本身支持事务,执行失败会回顾,所以2会出现问题
事务控制和锁定语句
lock table 和unlock table
lock table 锁定当前线程的表
unlock table 释放表
事务控制
本地事务
set autocommit 设置自动提交set autocommit=0所有的事务都需要显示提交
start transaction 开始事务
commit,rollback 提交或者回滚
chain 在事务回滚或者提交后的动作,创建一个相同级别的事务
release 在事务回滚或者提交后的动作 断开与客户端的连接
简单实用事务
set autocommit=0;
update country set id = 222;
rollback;
commit;
分布式事务的使用
分布式事务只在innodb支持,行动涉及多个活动,所有的活动一起完成或者一起回滚
原理:
资源管理器RM
事务管理器TM
使用分布式必须知道事务的资源管理器
分布式事务的流程
1 所有的分支准备好
2 TM告诉RM是否需要回滚或者提交
xa [start|begin] xid [join|resume]
启动一个带xid值得事务
xid:gtrid,[,bqual[,formatid]]
我也没有细究
sql中的安全问题
sql注入,使用预处理解决sql注入的问题
校验输入的合法性,过滤掉特殊字符
sql mode及相关问题
sql模式:
1 完成不同的数据校验
2 sqlmode ansi可以符合sql标准,方便迁移
3 可以通过设置sql mode实现更好的迁移
略,需要迁移的时候可以去了解