存储的程序
========================================
* 数据库中存储的程序，对数据进行运算处理
* 存储过程
* 函数
* 触发器
会话变量
========================================
* 一次会话过程中，可以设置一些变量保存数据
* set @name='张三'
* @表示回话变量
* @@表示全局变量，指定是系统变量
set @@sql_mode='STRICT_TRANS_TABLES';
set @@autocommit=OFF
set sql_mode='STRICT_TRANS_TABLES';
set autocommit=OFF
* 显示变量的值
select @name, @a, @b, @c;
存储过程
==================================================
* 创建存储过程
delimiter // -- 设置语句的结束符号
create procedure p1()
begin
select * from t;
end;
//
delimiter ; -- 语句的结束符重新设置会成分号
* 调用存储过程
call p1();
* 删除存储过程
drop procedure [if exists] p1;
* 参数
存储过程的参数三种：
in 输入参数（默认）
out 输出参数
inout 即可输入也可输出
定义存储过程： p2(in a int, out b int)
...
...
set b = a*2;
调用 p2()：
call p2(255, @v1)
select @v1;
* 流程控制
*)选择判断
if 条件 then
...
end if;
-----------------
if 条件 then
...
else
...
end if;
==========================
case
when 条件 then ...;
when 条件 then ...;
when 条件 then ...;
else ...;
end case;
*) 循环
while 条件 do
...
end while;
---------------
loop
...
end loop;
---------------
repeat
...
until 条件 end repeat;
---------------
leave 跳出循环
iterate 直接进入下次迭代
---------------
循环命名
lp: loop
leave lp;
iterate lp;
end loop;
* declare
定义局部变量
*) declare a int; -- null
*) declare a int default 1;
*) 局部变量在 end 结束时销毁
begin
declare a int default 1;
begin
declare b int default 2;
end;
select a; -- ok
select b; -- 错误
end;
函数
==================================================
* 与存储过程类似，
* 函数有返回值
* 用 select f() 调用函数
* 创建
create function f(参数) returns int
begin
....
return 计算结果;
end;
游标 cursor
==================================================
* 游标： 查询结果的数据指针
* 只能一步一步向下一行移动
* 不能任意的被定位
* 游标操作：
*)定义游标：
declare c cursor for select ...
*)打开游标：
open c;
*)从游标取一行数据（自动下移）：
-- 从游标取出几个字段的数据，存入几个变量
fetch c into v1,v2,v3,v4;
*)关闭游标：
close c;
* 从头到尾用游标访问数据
mysql 的错误处理机制：
declare continue handler for ...
当出现指定错误(not found)时，执行一段代码(done=)，
使程序可以继续正常执行(continue)
declare done int default 0;
declare c cursor for select ...
declare continue handler for not found set done=1;
open c;
while done=0 do
-- 在末尾取不到数据，会出现 not found 错误
-- 触发 done=1 执行
fetch c into ...
end while;
触发器
=============================================
* 对一行数据进行增删改操作，
可以触发一端代码执行
* 六种： 一张表最多就能创建6个触发器
before insert
before update
before delete
after insert
after update
after delete
* 创建触发器
create trigger 名称 before insert
on tb1 for each row
begin
....
end;
* 两个隐含对象
new - 新的数据行
old - 旧的数据行
new.id, new.name, new.price
old.id, old.name, old.price
*)插入操作
new - 新插入的一行数据
old - 没有
*)修改操作
new - 修改后的新数据
old - 修改前的旧数据
*)删除操作
new - 没有
old - 被删掉的数据
练习
===================================================
1. 新建库新建表
create database db1 charset=utf8;
use db1;
create table t(
c int
);
2. 创建存储过程
delimiter // -- 修改语句结束符
drop procedure if exists p1; -- 删除存储过程
//
create procedure p1() -- 定义存储过程 p1
begin
insert into t values(floor(rand()*10));
select * from t;
end;
//
call p1();// -- 调用存储过程
//
3. 测试输入参数和输出参数
drop procedure if exists p2;
//
create procedure p2(in a int, out b int)
begin
set b = a*2; -- 设置输出变量 b 的值
end;
//
call p2(1, @a); -- a变量在p2()存储过程中被赋值
call p2(2, @b);
call p2(3, @c);
select @a, @b, @c;
//
4. 测试选择判断
drop procedure p3 if exists;
//
create procedure p3(in a int)
begin
if a=1 then -- 如果a是1
select 10;
end if;
--
case
when a=2 then select 20; -- 如果a是2
when a=3 then select 30; -- 如果a是3
when a=4 then select 40; -- 如果a是4
else select 100; -- 如果a是234之外的其他值
end case;
end;
//
call p3(1);
call p3(2);
call p3(3);
call p3(4);
call p3(5);
//
5. 循环测试，循环插入 1,2,3,4,5
truncate table t; -- 删除再重建表
//
drop procedure if exists p4;
//
create procedure p4(in a int)
begin
-- 定义变量1.局部变量 2.会话变量
declare i int default 1; -- 局部变量 i
--
while i<=a do -- 当<=a时重复执行
insert into t values(i);
set i = i+1;
end while;
--
set i=1;
lp: loop
insert into t values(i);
set i = i+1;
if i>a then -- 如果超过a离开循环
leave lp;
end if;
end loop;
--
set i=1;
repeat
insert into t values(i);
set i = i+1;
until i>a end repeat; -- 直到超过a结束循环
end;
//
call p4(5);
//
select * from t;
//
6. 测试函数，求 a 的 b 次方
drop function if exists func_pow;
//
create function func_pow(a bigint, b bigint) returns bigint
begin
declare r bigint; -- 用来保存结果
declare i bigint default 1; -- 用来控制循环次数
set r = a;
while i<b do -- 重复b-1次
set r = r*a; -- 重复地乘a
set i=i+1; -- 次数递增
end while;
return r; -- 返回计算结果
end;
//
select func_pow(2,3);
select func_pow(3,2);
select func_pow(2,8);
//
select c, func_pow(c, 2) from t;
//
7. 产生随机字符串
可以产生大量随机字符串填入表中，
对查询效率进行测试
drop function if exists rand_str;
//
-- 产生的随机字符串长度范围 [a, b)
create function rand_str(a int, b int) returns varchar(255)
begin
-- 从这些字符当中随机挑选几个
declare s0 varchar(600) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ的一是在不了有和人这中大为上个国我以要他时来用们生到作地于出就分对成会可主发年动同工也能下过子说产种面而方后多定行学法所民得经十三之进着等部度家电力里如水化高自二理起小物现实加量都两体制机当使点从业本去把性好应开它合还因由其些然前外天政四日那社义事平形相全表间样与关各重新线内数正心反你明看原又么利比或但质气第向道命此变条只没结解问意建月公无系军很情者最立代想已通并提直题党程展五果料象员革位入常文总次品式活设及管特件长求老头基资边流路级少图山统接知较将组见计别她手角期根论运农指几九区强放决西被干做必战先回则任取据处队南给色光门即保治北造百规热领七海口东导器压志世金增争济阶油思术极交受联什认六共权收证改清己美再采转更单风切打白教速花带安场身车例真务具万每目至达走积示议声报斗完类八离华名确才科张信马节话米整空元况今集温传土许步群广石记需段研界拉林律叫且究观越织装影算低持音众书布复容儿须际商非验连断深难近矿千周委素技备半办青省列习响约支般史感劳便团往酸历市克何除消构府称太准精值号率族维划选标写存候毛亲快效斯院查江型眼王按格养易置派层片始却专状育厂京识适属圆包火住调满县局照参红细引听该铁价严龙飞';
declare len int; -- 保存随机长度值
declare s varchar(255) default ''; -- 保存随机字符串结果
declare i int default 0; -- 用来控制次数
declare j int; -- 随机位置
-- 随机长度，范围 [a, b)
set len = a+floor(rand()*(b-a));
-- 产生 len 个随机字符连接到 s
while i<len do
-- 随机位置 [1, char_length(s0))
set j = 1+floor(rand()*(char_length(s0)-1));
-- 从s0取i位置字符，连接到 s
set s = concat(s, substring(s0, j, 1));
set i = i+1; -- i递增
end while;
return s; -- 返回随机字符串结果
end;
//
select rand_str(3,8);
select rand_str(3,8);
select rand_str(3,8);
select rand_str(3,8);
select rand_str(3,8);
//
1.具体长度 len
[3,8)
3+[0,5)
3+floor(rand()*5)
len = a+floor(rand()*(b-a))
0 0
0.1 *5 0.5
0.2 1
0.3 1.5
0.8 4
0.9999 4.xxxx
rand()
2. 需要一个空串 s=''
3. 循环 len 次
4. 产生随机位置 j
1+ [0, char_length(s0)-1)
j = 1+floor(rand()*(char_length(s0)-1))
5. 截取 s0 的 i 位置字符
substring(s0, j, 1)
6. 这个字符连到 s 上
s = concat(s, substring(s0, j, 1))
8. 在表中插入大量随机字符串
1.创建一个内存表
engin=innodb
myisam
memory 内存表
2.循环向内存表插入 10 万数据
3.将内存的10万数据直接插入磁盘表
-- 内存表
drop table if exists tm;
create table tm(
c varchar(255)
)engine=memory;
//
-- 磁盘表
drop table if exists td;
create table td(
id int primary key auto_increment,
c varchar(255)
)engine=innodb;
//
-- 存储过程
drop procedure if exists rand_data;
//
create procedure rand_data(n int)
begin
declare i int default 0; -- 用来控制循环多少次
while i<n do -- 循环 n 次
-- 向内存插入随机字符串
insert into tm values(rand_str(3,6));
set i=i+1;
end while;
-- 内存数据全部插入磁盘表
insert into td(c) select * from tm;
delete from tm; -- 清空内存表
end;
//
call rand_data(9);
//
call rand_data(20000);//
* 查询 td 表的 c 字段
select * from td where id=2918757
//
select * from td where c='值w农计调';
//
select * from td where c like '值w%';
//
select * from td where c like '%计调';
//
select * from td where c like '王__';
//
* 对 c 字段创建索引，再查询
或删除索引，在查询
create index td_c_index on td(c);
//
explain select * from td where c like '值w%';
//
explain select * from td where c like '%计调';
//
-- 删除索引
alter table td drop index td_c_index;
//
* 游标测试
drop procedure if exists p5;
//
create procedure p5()
begin
declare sum int default 0; -- 累加变量
declare a int; -- 保存每行的值
declare done int default 0; -- 结束标志
declare c cursor for select * from t; -- 游标
-- 错误处理，找不到数据值将标志值设置成1
declare continue handler for not found set done=1;
open c; -- 打开游标
while done=0 do -- 当结束标志是0，没结束
fetch c into a; -- 抓取下一行数据存到变量a
set sum = sum + a;
end while;
close c;
select sum;
end;
//
call p5();
//
* 商品分类表(tb_item_cat)
修改数据时，不必手动修改 updated 字段，
用触发器自动填入当前时间
update tb_item_cat set name='新类别'
where id=16;
update tb_item_cat set name='新类别',updated=now()
where id=16;
use jtds//
create trigger cat_updated_trigger
before update on tb_item_cat
for each row
begin
set new.updated=now();-- 新数据的更新时间字段填入系统时间
end;
//
select id,name,updated from tb_item_cat
where id in(3,4,5,6,7);
//
update tb_item_cat
set name=concat('>>>', name)
where id in(3,4,5,6,7);
//
* 阻止删除商品表数据
*)当 before delete 时，手动产生一个错误，
使删除操作失败
create trigger item_del_trigger
before delete on tb_item
for each row
begin
-- 手动暴力产生错误
delete from 不允许商品表的删除操作;
end;
//
delete from tb_item where id=10000028;
//
作业
=======================================================
用触发器1实现：
tb_item 商品表的商品数量，不允许减为负数
如果是负数，暴力产生错误
用触发2器实现：
向订单详情表(tb_order_item)插入数据时，
商品表商品数量减少
MySQL数据库技术实训 实习总结
转载本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
提问和评论都可以，用心的回复会被更多人看到
评论
发布评论
相关文章
-
GaussDB(for MySQL) RegionlessDB发布：全球数据库技术
GaussDB(for MySQL) RegionlessDB，是由分布在全球多个地域间若干GaussDB(for MySQL)集群组成的数据库网络。
数据库 MySQL 数据 GaussDB(for MySQL)
