存储过程
–Mysql
show database
show tables
show columns from table_Name
show status
show create database
show create table
show grants
show errors
show warnings
REGEXP
–创建存储过程
create procedure procedurename(
OUT pl decimal(8,2),
OUT ph DECIMAL(8,2),
OUT pa DECIMAL(8,2)
)
BEGIN
SELECT Min(pro_price)
into pl
from products;
select Max(pro_price)
into ph
from products;
select Avg(pro_price)
into pa
from products;
end;
–调用存储过程
call procedurename(@pricelow,@pricehigh,@priceaverage)
–显示数据
select @priceaverage
select @pricehigh,2pricelow,@priceaverage;
–有输入与输出的存储过程
create procedure ordertotal(
IN onumber INT,
OUT ototal DECIMAL(8,2)
)
BEGIN
SELECT Sum(item_price*quantity)
from orderitems
where order_num = onumber
into ototal
end;
–调用存储过程
call ordertotal(2001,@total);
–显示数据
select @total
–触发器
/*唯一的触发器名
触发器关联的表
触发器响应的活动(DELETE 、INSERT 、UPDATE)
触发器何氏执行(处理之前或之后)
*/
create or replace trigger tri_tablename_operation
after operation on table_name
for each row
…
–删除触发器
drop trigger truggername
–INSERT 触发器
/*1.在INSERT触发器代码内,可引用一个名为NEW的虚拟表,访问被插入的行
2.在BEFORE INSERT触发器中,new中的值也可以被更新(允许更改被插入的值)
3.对于auto_increment列,NEW在insert执行之前包含0,在INSERT执行之后包含新的自动生成值
*/
–eg:
create or replace trigger tri_table_name_operation
after insert on table_name
for each row select NEW.ORDER_NUM
create table A
( id int,
name varchar(12)
)
select * from AA
create or replace trigger TRI_A_INSERT
after insert on A
for each row
--DELETE触发器
1.在delete触发器代码内,可以引用一个名为OLD的虚拟表
访问被删除的行
2.OLD的值全都是只读的,不能更新
--eg:
create or replace trigger tri_tablename_del
before delete on orders
for each row
begin
insert into tablename (cloumn1,column2…)
values(column1,column2…)
end;
–创建一张表和AA结构一样
create table BB as select * from AA where 1=2
–创建触发器
create or replace trigger TRI_AA_DEL
after delete on AA
for each row
begin
insert into BB (nub, name, age) values (:old.nub,:old.name,:old.age);
end;
–测试
select * from AA
delete from AA where age = 12;
commit;
select * from BB
–UPDATE触发器
1.在update触发器代码内,可以引用一个名为OLD虚拟表访问以前的值,
引用一个名为NEW的虚拟表访问更新的新的值
2.在before update触发器中,NEW中的值可能也被更新(允许更改将要用于UPDATE语句中的值)
3.OLD中的值全都是只读的,不能更新