函数:
SQL内部提供函数:
如reverse,concat,sleep之类的
MySQL :: MySQL 5.7 Reference Manual :: 12 Functions and Operators
自定义函数:
delimiter $$ --这一行是改变结束条件为输入$$
create function f1(i1 int, i2 int)
returns int -- 返回值
BEGIN
declare num int;
declare maxId int;
select max(id) from big into maxId;
set num = i1 + i2 + maxId;
return(num);
END $$
delimiter ; --再改回来执行函数:
select f1(11,22);删除函数:
drop function f1;
存储过程:
存储过程,是一个存储在MySQL中的SQL语句集合,当主动去调用存储过程时,其中内部的SQL语句会按照逻辑执行。
1.创建存储过程
delimiter $$
create procedure p1()
BEGIN
select * from d1;
END $$
delimiter ;2.执行,py操作差不多相同
call p1();
3.删除
drop procedure proc_name;
参数类型:
int(传入当参数),out(传出当返回值),inout(both)
delimiter $$
create procedure p2(
in i1 int,
in i2 int,
inout i3 int,
out r1 int
)
BEGIN
DECLARE temp1 int;
DECLARE temp2 int default 0;
set temp1 = 1;set r1 = i1 + i2 + temp1 + temp2;
set i3 = i3 + 100;end $$
delimiter ;set @t1 =4;
set @t2 = 0;
CALL p2 (1, 2 ,@t1, @t2);
SELECT @t1,@t2;
返回值&结果集
delimiter $$
create procedure p3(
in n1 int,
inout n2 int,
out n3 int
)
begin
set n2 = n1 + 100;
set n3 = n2 + n1 + 100;
select * from d1; --返回结果集
end $$
delimiter ;
事物&异常(不常用)
delimiter $$
create PROCEDURE p4(
OUT p_return_code tinyint
)
BEGIN
DECLARE exit handler for sqlexception
BEGIN
-- ERROR 执行异常就执行这个
set p_return_code = 1;
rollback; --回滚,将成功的恢复原样
END;
DECLARE exit handler for sqlwarning
BEGIN
-- WARNING
set p_return_code = 2;
rollback;
END;
START TRANSACTION; -- 开启事务,要成功都成功,要失败都失败
delete from d1;
insert into tb(name)values('seven');
COMMIT; -- 提交事务
-- SUCCESS
set p_return_code = 0;
END $$
delimiter ;
游标(不常用)
delimiter $$
create procedure p5()
begin
declare sid int;
declare sname varchar(50);
declare done int default false;
declare my_cursor CURSOR FOR select id,name from d1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; --此时如果已经执行完
open my_cursor;
xxoo: LOOP
fetch my_cursor into sid,sname;
IF done then
leave xxoo;
END IF;
insert into t1(name) values(sname);
end loop xxoo;
close my_cursor;
end $$
delimiter ;
视图:
视图其实是一个虚拟表(非真实存在),其本质是【根据SQL语句获取动态的数据集,并为其命名】,用户使用时只需使用【名称】即可获取结果集,并可以将其当作表来使用。
SELECT
*
FROM
(SELECT nid,name FROM tb1 WHERE nid > 2) AS A
WHERE
A.name > 'alex';创建视图
create view v1 as select id,name from d1 where id > 1;
使用视图,等价于注释的那个
select * from v1;
-- select * from (select id,name from d1 where id > 1) as v1;
删除视图
drop view v1;
修改
alter view v1 as SQL语句
视图不能被修改,但是修改原表,也会改变视图
触发器(不常用):
触发操作前先执行trigger
# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
...
END# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
...
END# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
...
END# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
...
END# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END
NEW.id NEW.name 的NEW是新添加的
OLD是原来的删除前的