1 存储结构
定义:存储过程就是多个sql语句的组合,并加上一定的逻辑。
优点:
1 第一次执行存储过程,mysql会编译该存储过程的语句,后续再执行该存储过程,无需编译,直接执行。普通的sql语句,每次运行都要先编译后执行,所以存储过程会比普通的sql执行速度较快一点。
2 如果存在多个复杂的数据库操作,在代码中执行一个个sql,为了保证数据库数据库的一致性,只能通过事务来解决,如果全部写入存储过程,即无需事务,一次执行完毕。
1.1 创建存储过程
1.1.1 无参存储过程
delimiter $
create PROCEDURE pro_test01()
begin
select *from ws_chat_log;
end $
delimiter
上诉语句中:delimiter $和 delimiter // 效果一样,看每个人的习惯。
在mybatis中调用存储过程
<select id="callProduct" resultType="cn.nldxm.windspace.website.model.mysql.MysqlTest">
call pro_test01()
</select>
1.1.2 有参存储过程
delimiter $
create PROCEDURE pro_test03(in a int)
begin
select *from ws_chat_log where id = a;
end $
delimiter
1.1.3 参数解析
IN: 输入参数:表示调用者向过程传入值(传入值可以是字面量或变量)
OUT: 输出参数:表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)
INOUT: 输入输出参数:既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)
1.1.4 in的例子
delimiter $
create PROCEDURE pro_test03(in a int)
begin
select *from ws_chat_log where id = a;
end $
delimiter
mybatis中的写法
<select id="callProductWithParam" resultType="cn.nldxm.windspace.website.model.mysql.MysqlTest">
call pro_test03(
#{id}
)
<select>
1.1.5 out的例子
delimiter $
CREATE PROCEDURE proc_out_damo4(out a int,out b VARCHAR(255))
begin
select id,pic_name
#绑定值
into a,b
from ws_thousand_pic_src where id = 1;
end $
delimiter
mybatis的写法
<select id="proc_out_damo1" statementType="CALLABLE" parameterType="java.util.Map">
{
call proc_out_damo4(#{id,mode=OUT,jdbcType=INTEGER},#{picName,mode=OUT,jdbcType=VARCHAR})
}
</select>
java代码:
mapper:
//注意没有返回参数!
void proc_out_damo1(Map<String,Object> map);
调用:
Map<String,Object> map = new HashMap<>(8);
map.put("id",null);
map.put("picName",null);
mapper.proc_out_damo1(map);
取值:
结果值在map的id key中
1.1.6 inout的例子
delimiter $
CREATE PROCEDURE proc_inout_damo2(inout a int)
begin
select a;
set a = 3;
end $
delimiter
mybatis写法
<select id="prodInoutDamo1" statementType="CALLABLE" parameterType="java.util.Map" resultType="java.lang.Integer">
{
call proc_inout_damo2(#{id,mode=INOUT,jdbcType=INTEGER})
}
</select>
mapper:
Integer prodInoutDamo1(Map<String,Object> map);
调用:
Map<String,Object> map = new HashMap<>(8);
map.put("id",10);
int a = mapper.prodInoutDamo1(map);
取值:
结果值再map的id key中
1.1.7 变量
局部变量
DECLARE l_int int unsigned default 4000000;
DECLARE l_numeric number(8,2) DEFAULT 9.95;
DECLARE l_date date DEFAULT '1999-12-31';
DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59';
DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';
用户变量
set @a = 1;
1.2 修改存储过程
目前,MySQL还不提供对已存在的存储过程的代码修改.如果,一定要修改存储过程的diam,必须,先将存储过程删除之后,在重新编写代码,或者创建一个新的存储过程
1.3 删除存储过程
#pro_name 是存储过程的名称
DROP PROCEDURE pro_name;
2 函数
mysql中存在很多已经定义好的函数,例如sum()。mysql也支持我们自己创建自定义函数;
函数只会返回一个值,不像存储结构,既可以返回一个值,也可以返回一个集合。
2.1 创建函数
create function 函数名([参数列表]) returns 数据类型
begin
sql语句;
return 值;
end;
例1:
CREATE FUNCTION fuc1(a int) returns int
begin
#sql语句
return 100;
end;
2.2 调用函数
select fuc1(10);
2.3 函数的查看
show create function 函数名;
例:show create function fuc1;
2.4 函数的修改
函数的修改只能修改一些如comment的选项,不能修改内部的sql语句和参数列表。
2.5 函数的删除
drop function 函数名
例:drop function fuc1
2.6 注意事项
存储过程和函数的区别
存储过程:不允许执行return,但是通过out返回多个值;存储过程的限制相对较少;作为一个单独部分来执行;
函数:只能通过return语句返回单个值或表对象;限制较多,不能使用零时表,只能用表变量,还有一些函数不可用;可以嵌入sql语句中使用。
不能修改存储过程中的代码,只能删了重写一个;
存储过程可以调用其他存储过程,但是不能删除其他存储过程;
定义存储过程参数列表时,要把参数名与数据库中的字段区分开来。否则会出现无法预料的结果。
传入中文参数要注意,在参数后面要加上character set gbk
存储过程是用户定义的一系列sql语句的集合,涉及特定表或其它对象的任务,用户可以调用存储过程。而函数通常是数据库已定义的方法,它接收参数并返回某种类型的值并且不涉及特定用户表。
3 视图
定义:是虚拟表,MYSQL5.0后开始使用视图,从数据库一个表或多个表中导出来的表。一经定义就存储在数据库汇总,若基本表发生变化,则这种变化也会自动反映到视图上。
3.1 创建视图
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [{column_list}] AS SELECT_statement [WITH[CASCADED | LOCAL] CHECK OPTION]
CREATE 是创建表 REPLACE 是替换表 。
ALGORITHM 是选择算法。
UNDEFINED :让mysql自动选择算法;
MERGE:将引用视图语句与视图的sql语句合并起来,最后一起执行;
TEMPTABLE:将视图的结果存入临时表,然后用临时表来执行语句。
CASCADED与LOCAL为可选参数。CASCADED为默认值,表示更新视图时要满足所有相关视图和表的条件:LOCAL表示更新视图的时候满足视图本身定义的条件即可。
#单表创建视图
CREATE VIEW view_t as SELECT quantity,prive,quantity*prive FROM t;
#多表创建视图
CREATE VIEW stu_glass (id, NAME, glass) AS SELECT student.s_id, student. NAME, stu_info.glass FROM student, stu_info WHERE student.s_id = stu_info.s_id;
3.2 调用视图
select * from v_damo1;
3.3 查看视图
DESCRIBE 视图名;查看视图表的结构
show table status like '视图名';
show create view 视图名;查看视图的详细信息;
在mysql中,information_schema数据库下的views表存储了所有视图的定义:select * from information_schema.views;
3.4 修改视图
CREATE OR REPLACE [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [{column_list}] AS SELECT_statement [WITH[CASCADED | LOCAL] CHECK OPTION]
ALTER 语句修改视图
ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [{column_list}] AS SELECT_statement [WITH[CASCADED | LOCAL] CHECK OPTION]
3.5 删除视图
DROP VIEW view_name
3.6 注意事项
表和视图的区别
1:视图是已经编译好的sql语句,基于sql语句结果集可视化的表,而表不是。
2:视图没有实际的物理记录,而基本表有。
3:表是内容。视图是窗口。
4:表占用物理内存,而视图不占,视图只能用创建语句来修改。
5:视图是查看数据的一种方法;防止用户接触数据表。
6:表属于全局模式,是实表,视图属于全局模式属于虚表。
7:视图的建立和删除只影响视图本身,不影响对应的基本表。
4 触发器
定义:触发器即在表的insert、update、delete的前后进行的操作。所以一个表最多只能设置六个触发器。
4.1 创建触发器
CREATE TRIGGER trigger_name 时机 事件 ON tb1_name FOR EACH ROW trigger_stmt;
事件:insert、update、delete
时机:after、before
for each row 是代表任何记录执行对应操作都会触发器
trigger_stmt:触发器语句
例:
CREATE TRIGGER trigger_damo2
before insert ON ws_thousand_pic_src
FOR EACH ROW
insert ws_trigger(message)value("触发器插入数据");
当你执行
insert into ws_thousand_pic_src (pic_source,pic_url,pic_name,oss_url,oss_status) value(0,"测试","测试","测试",1);
时,就会触发插入操作,在ws_trigger表中新增一条数据。
4.2 查看触发器
SHOW TRIGGERS;
SELECT * FROM INFORMATION_SCHEMA .TRIGGERS WHERE TRIGGER_NAME = '...';
4.3 删除触发器
DROP TRIGGER trigger_name
4.4 注意事项
1:相同的表相同的事件只能创建一个触发器
2:及时删除不再需要的触发器
3:MYSQL中触发器中不能对本表进行 insert ,update ,delete 操作,以免递归循环触发。