MySQL进阶

临时表

视图

给某个查询语句设置别名，方便以后使用
create view 视图名 as SQL;
alter view 视图名 as SQL;
drop view 视图名;

create view v1 as select * from student;
select * from v1;

#建表
create table t1(
id int primary key auto_increment,
name char(10)
);
#插入数据
insert into t1 values (1,'egon'),(2,'daa'),(3,'eef');
#创建视图
create view t1_view as select * from t1;
#测试创建视图以后还能不能增删改查
select * from t1_view;
update t1_view set name = '冥想' where id = 2; #可以修改(而且原来表的记录也修改了)
insert into t1_view values(4,'aaa'); #可以插入(同上)
delete from t1_view where id=3;#可以删除

触发器

当对某张表做：增删改操作时，可以使用触发器自定义关联行为before、after
触发器的两个关键字：new ,old
-- new :表示新的记录
-- old:表示旧的那条记录

#修改终止符为//
delimiter //
#创建触发器
create trigger t1 [before | after] [insert | delete | update] on student for each row
BEGIN
	#触发的操作
	insert into teacher(tname) values(new.sname);
END //
#改回来
delimiter ;

函数

自定义函数，处理数据，返回值
delimiter //
create function f1(
	i1 int,
	i2 int)
returns int
BEGIN
	declare num int;  #声明
	set num = i1 + i2;
	return(num);
END //
delimiter ;

#使用
select f1(1,20);
select f1(11,nid), name from tb2;
#删除函数
drop function func_name;

事务

事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。
事务也就是要么都成功，要么都不成功，事务就是由一堆sql语句组成的

-- 如果都成功就执行commit，如果不成功就执行rollback。	
start transaction #开启事务
update user set balance = 100 where name = '韩凡';
update user set balance = 210 where name = '小雪';
update user set balance = 290 where name = '小莹';  #sql语句错误就会报错了
commit; #如果所有的sql语句都没有出现异常，应该执行commit

start transaction
update user set balance = 100 where name = '韩凡';
update user set balance = 210 where name = '小雪';
updatezzzz user set balance = 290 where name = '小莹';  #sql语句错误就会报错了
rollback; #如果任意一条sql出现异常，都应该回归到初始状态


捕捉异常+事务
delimiter //
create procedure p6(
	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;
		insert into test(username,dep_id) values('egon',1);
		delete from tb11; #如果执行失败，就不会执行commit了
	COMMIT;
	-- SUCCESS
	set p_return_code = 0; #0代表执行成功
END //
delimiter ;

#调用
set @res = 111  #相当于定义一个全局变量
call p6(@res)

select * from test;
select @res；

存储过程

创建无参的存储过程
delimiter //
create procedure p1()
begin
	select * from test;
	insert into test(username,dep_id) values('egon',1);
end //
delimiter ;

#mysql调用存储过程
call p1();
#在python程序中调用
cursor.callproc('p1')


创建有参的存储过程(in)
delimiter //
create procedure p2(
	in m int, #从外部传进来的值
	in n int
)
begin
	insert into test(username,dep_id) values('haha',2),('xixi',3),('sasa',1),('yanyan',2);
	select * from test where id between m and n;
end //
delimiter ;

#在mysql中调用存储过程
call p2(3,7);
#在python程序中调用
cursor.callproc('p2',arg(3,7))


创建有参的存储过程(out)
delimiter //
create procedure p3(
	in m int, #从外部传进来的值
	in n int,
	out res int
)
begin
	select * from test where id between m and n;
	set res = 1;#如果不设置，则res返回null
end //
delimiter ;

#在mysql中执行存储过程
set @res = 1111;
call p3(3,7,@res);
select @res; 

#在python中
res=cursor.callproc('p3',args=(3,7,123)) #@_p3_0=3,@_p3_1=7,@_p3_2=123
print(cursor.fetchall()) #只是拿到存储过程中select的查询结果
cursor.execute('select @_p3_0,@_p3_1,@_p3_2')
print(cursor.fetchall()) #可以拿到的是返回值

流程控制

1 条件语句
#函数中不要写sql语句，它仅仅只是一个功能，是一个在sql中被应用的功能
#若要想在begin...end...中写sql，请用存储过程
delimiter //
create function f5(
	i int
)
returns int
begin
	declare res int default 0;
	if i = 10 then
		set res=100;
	elseif i = 20 then
		set res=200;
	elseif i = 30 then
		set res=300;
	else
		set res=400;
	end if;
	return res;
end //
delimiter ;


delimiter //
CREATE PROCEDURE proc_if ()
BEGIN
	
	declare i int default 0;
	if i = 1 THEN
		SELECT 1;
	ELSEIF i = 2 THEN
		SELECT 2;
	ELSE
		SELECT 7;
	END IF;

END //
delimiter ;


2 循环语句
# while循环
delimiter //
CREATE PROCEDURE proc_while ()
BEGIN

	DECLARE num INT ;
	SET num = 0 ;
	WHILE num < 10 DO
		SELECT num ;
		SET num = num + 1 ;
	END WHILE ;

END //
delimiter ;

#repeat循环
delimiter //
CREATE PROCEDURE proc_repeat ()
BEGIN

	DECLARE i INT ;
	SET i = 0 ;
	repeat
		select i;
		set i = i + 1;
		until i >= 5
	end repeat;

END //
delimiter ;

# loop循环
BEGIN	
	declare i int default 0;
	loop_label: loop
		
		set i=i+1;
		if i<8 then
			iterate loop_label;
		end if;
		if i>=10 then
			leave loop_label;
		end if;
		select i;
	end loop loop_label;
END