mysql学习笔记之开发篇二

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实现更好的迁移 

略，需要迁移的时候可以去了解