MySQL存储过程
概念
存储过程是一组为了完成特定功能的SQL语句级集合,也就是说存储过程中可以有多条SQL语句。
存储过程经过编译后存储在数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。
说白了,就类似于php中的函数。
优点
存储过程增强了SQL语言的功能和灵活性。存储过程可以用if/while/case等控制语句编写,可以完成复杂的判断和运算
存储过程类似于变成语言的函数,可以在程序中多次调用。
存储过程能实现较快的执行速度。因为存储过程编写完成,编译一次后,之后调用,不再编译。
语法
create procedure 名字(参数1[,参数2])
begin
sql语句
end
存储过程参加完整步骤
选中某个数据库
mysql> use demo;
修改mysql默认结束符号
mysql> \d $
或 delimiter $
创建存储过程
mysql> create procedure hello()
mysql> begin
mysql> show tables;
mysql> end
mysql> $将结束符号改为;
mysql> \d ;
调用存储过程
mysql> call hello();
存储过程中的局部变量定义
局部变量只会在存储过程中生效,其他地方调用不了
局部变量定义
declare 变量名 数据类型 default 默认值
局部变量赋值
set 变量名 = 值 [, 变量名1 = 值]
存储过程的三种参数
存储过程的参数有三种,分别是输入输出类型(in),输入类型(inout),输出类型(out)。
参数形式:
[in | out | inout] 参数名 type
其中,in表示输入参数;out表示输出参数; inout表示既可以是输入,也可以是输出;type参数指定存储过程的参数类型,该类型可以是MySQL数据库的任意数据类型
in 类型的参数:
表示该参数的值必须在调用存储过程之前指定,在存储过程中修改的值不能被返回。out类型的参数:
out的值可以在存储过程内部改变,并可以返回inout类型的参数:
inout的值可以在调用时指定,并可以在存储过程中修改和返回局部变量定义例子
mysql> \d $
mysql> create procedure test_var()
mysql> begin// 定义一个局部变量
mysql> declare name varchar(20) default ‘jack’;//输出局部变量
mysql> select name;
mysql> end
mysql> $//调用存储过程
mysql> \d ;
mysql> call test_var();局部变量赋值例子
mysql> \d $
mysql> create procedure test_var_val()
mysql> begin
mysql> declare num int;//给局部变量num赋值
mysql> set num = 10;
mysql> select num;
mysql> end
mysql> $mysql> \d ;
存储过程的三种参数in类型的例子
mysql> \d $
mysql> create procedure test_three(in age int)
mysql> begin
mysql> select age;
//在存储过程中改变值
mysql> set age = age + 1;mysql> select age;
mysql> end
mysql> $mysql> \d ;
//声明一个变量
mysql> set @i=3;//调用存储过程,并且将@i这个变量传递给存储过程的age局部变量
mysql> call test_three(@i);//查询@i的值
mysql> select @i;
结论: @i的值不变,还是3,说明in类型的参数是不会影响传递@i的,也就是类似于php中的值传递。存储过程的三种参数out类型的例子
mysql> \d $
mysql> create procedure test_out(out age int)
mysql> begin
mysql> select age;
//在存储过程中改变值
mysql> set age = 22;mysql> select age;
mysql> end
mysql> $mysql> \d ;
//声明一个变量
mysql> set @out=0;//调用存储过程,并且将@i这个变量传递给存储过程的age局部变量
mysql> call test_three(@out);//查询@i的值
mysql> select @out;总结:@out的值也变成了22,说明改变了age这个局部变量的值,也会改变@out的值
所以使用out类型参数来接受到存储过程中的需要返回的结果。
总结in、out区别:
in:表示输入一个值,你需要一个值,我给你一个值
out:你往外输出一个值,你输出的那个值我就拿一个变量来接收你给我输出的那个值
存储过程的三种参数inout类型的例子
mysql> \d $
mysql> create procedure test_five(inout num int)
mysql> begin
mysql> select num;
//在存储过程中改变值
mysql> set num = 33;mysql> select num;
mysql> end
mysql> $mysql> \d ;
//声明一个变量
mysql> set @i=3;//调用存储过程,并且将@i这个变量传递给存储过程的age局部变量
mysql> call test_five(@i);//查询@i的值
mysql> select @i;inout类型参数: @i可以传递进去给num,并且num的值也返回给@i
存储过程之选择语句(if)
语法:
if 条件1 then if内语句
[elseif 条件2 then 语句2]
[else 语句3]
end if;
例子1:
//如果age> 20 就输出’成年了’,小于20就输出未成年
mysql> \d
mysql>createproceduretestif(inageint)mysql>beginmysql>ifage>20thenmysql>select‘成年了′;mysql>elsemysql>select‘未成年′;mysql>endif;mysql>endmysql>
例子2:
//如果60>age> 20 就输出'成年了',小于20就输出未成年,大于60小于200就是老年人
mysql> \d mysql> create procedure test_ifelse(in age int)
mysql> begin
mysql> if age > 20 && age < 60 then
mysql> select ‘成年人’;
mysql> elseif age <= 20 then
mysql> select ‘未成年’;
mysql> elseif age > 60 && age < 200 then
mysql> select ‘老年人’;
mysql> else
mysql> select ‘人妖’;
mysql> end if;
mysql> end
mysql>
存储过程之选择语句(case)
case语句用来进行条件判断,类似php中的switch case
语法:
case case_value
when when_value then 语句
[when when_value then 语句]
[else 语句]
end case;
例子:如果用户=2,就将工资加1000,如果用户id=3,工资加2000
create table saraly(
id int not null primary key auto_increment,
uid int unsigned not null ,
saraly decimal(18,2) not null
)engine=innodb default charset=utf8;
//插入测试数据
insert into saraly(uid, saraly) values(2,200000);
insert into saraly(uid, saraly) values(3,400000);
insert into saraly(uid, saraly) values(3,500000);
\d $
create procedure test_case(in userid int)
begin
case userid
when 1 then
update saraly set saraly=saraly+1000 where uid
=1;
when 2 then
update saraly set saraly=saraly+2000 where uid
=2;
else
select ‘工资没有涨’;
end case;
end
$
\d ;
case 另外常用例子
//将用户详情表中的性别为1,变成男,0=>女
select id,uid,city,(case sex when 1 then ‘男’ when 0 then ‘女’ else ‘人妖’ end) as sex from user_detail;
存储过程之循环语句(while)
语法:
while 条件 do
语句
end while;
例子: 利用while循环计算1+2+3+..+100的值
\d $
mysql> create procedure addsum(out sum int)
-> begin
-> declare i int default 1;
-> declare result int default 0;
-> while i<=100 do
-> set result = i + result;
-> set i = i + 1;
-> end while;
-> set sum = result;
-> end
-> $
mysql>\d ;
//定义一个变量接受addsum返回的值
mysql> set @res=0;
mysql> call addsum(@res);
mysql> select @res;
+--------+
| @resul |
+--------+
| 5050 |
+--------+
存储过程管理
查看某个数据库下的所有存储过程
show procedure status where db =’数据库名’;
查看当前数据库下面的存储过程列表
use demo;//先要进入到某个数据库
select specific_name from mysql.proc;查看存储过程的内容
use demo;//先要进入到某个数据库
show create procedure 存储过程名字;删除存储过程
drop procedure 存储过程名
触发器
- 触发器概念
触发器是一种特殊类型的存储过程,在插入,删除和修改表中数据时被触发执行。
触发时机
Before和After两种
触发条件
Insert ,delete , update
总结:所以在一个表上最多可以建立6个触发器,即:1. before insert型(插入数据之前触发) 2. before update型(更新数据之前) 3.before delete型
- after insert 型 5. after update型 6. after delete型。
并且不能在同一个表中建立两个类型完全相同的触发器。
触发器语法
create trigger 触发器名称
after/before insert/update/delete on 表名
for each row
begin
sql语句
end
在触发器中引用行值
触发事件 引用对象 表示方法
insert 新增的行 new.列名
delete 删除的行 old.列名
update 更新前的行 old.列名
update 更新后的行 new.列名
触发器例子
- 插入触发器例子
例子说明: 因为每位员工入职后都应该有对应的薪资,所以适合使用触发器来完成
//员工表建表语句
CREATE TABLEuser
(
id
int(11) NOT NULL AUTO_INCREMENT,
name
varchar(150) NOT NULL,
pass
varchar(255) NOT NULL,
PRIMARY KEY (id
),
UNIQUE KEYname
(name
)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
//薪水表
CREATE TABLE saraly
(
id
int(11) NOT NULL AUTO_INCREMENT,
uid
int(10) unsigned NOT NULL,
saraly
decimal(18,2) NOT NULL,
PRIMARY KEY (id
)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
//建立触发器
mysql> \d mysql>createtriggeruseraddsaralyafterinsert−>onuserforeachrow−>begin−>insertintosaraly(uid,saraly)values(new.id,50000);−>end−>
//当给user表插入一条的数据时,saraly表也会多一条数据
mysql> insert into user(name,pass) values(‘rest’,md5(‘234111’));
例子2: 员工离职后,需要删除员工信息与薪资信息。这个时候就可以使用到触发器
mysql> \d mysql>createtriggeruserdelsaralyafterdelete−>onuserforeachrow−>begin−>deletefromsaralywhereuid=old.id;−>end−>
//当删除user表数据,saraly表数据也会删除
mysql> delte from user where id = 6;
触发器管理
删除触发器
不再需要的触发器必须删除,否则触发器可能会在符合条件时被执行。
drop trigger 触发器名字
查看所有触发器
show trigger
视图
- 概念
视图是一张虚拟表,并不表示任何物理数据,只是用来查看数据而已。
select语句的结果集构成视图所返回的虚拟表,当表中的数据发生变化时,从视图中查询出来的数据也随之改变。
视图中的行和列都来自数据表,这些数据表称之为视图的基表,视图数据是在视图被使用时动态生成的。
视图的作用
保护一些数据,可以将数据表的一些数据放到视图中,然后只给查询者查看视图的权限
简化SQL语句.为复杂的查询建立一个视图,用户不必输入复杂的查询语句,只需要查询视图就行
注意
- 视图并不能加快查询速度,因为视图的数据是在使用视图时从基表中拿出来的
视图的创建
语法:
create view 视图名 as 查询类型的sql语句
实例
- 从用户表和用户详情表中查询出所有的数据形成视图
create view v_all_user as select u.id,u.name,d.sex,d.city from user u left join user_detail d on u.id=d.uid;
//以后需要所有用户的所有数据,就只需要查询视图 v_all_user
select * from v_all_user 的数据等同于 select u.id,u.name,d.sex,d.city from user u left join user_detail d on u.id=d.uid 的数据
视图管理
查看某个视图的定义
show create view 视图名
视图删除
drop view 视图名