MySQL数据库（二）

补充：
临时表（子查询）
指定映射
条件：
– case when … then …
三元运算：
if (isnull(xx),0,1)
上下连表：
左右连表：join
上下连表：union(自动去重) union all(不去重)
select id,name from tb11
union all
select id,name from tb11

视图:
在子查询中，给某个查询语句（查询的临时表）设置别名，日后方便使用。
虚拟出来的表，并非真正的物理表

创建视图：create view 视图名称 as SQL语句；
修改视图：alter view 视图名称 as SQL语句；
删除视图：drop view 视图名称；	

触发器：
对某个表进行【增/删/改】操作时，可以使用触发器自定义关联行为。

函数：
性能低，有索引的列用函数的话索引不管用。

内置函数：
	时间格式化
	select data_format(ctime,"%Y-%m"),count(1) from blog group by data_format(ctime,"%Y-%m");
自定义函数(有返回值)：
	delimiter\\
	create function f1(
		i1 int,
		i2 int
	)returns int
	begin
		declare num int default 0;
		set num i1 + i2;
		returns (num);
	end \\
	delimiter;
	调用自定义函数： select f1(1,100);

存储过程：
用于替代程序员写SQL语句
保存在mysql上的一个别名，一坨SQL语句。
调用：
终端：
call 别名();
pymysql:
cursor.callproc(p1)

为什么有存储过程：将SQL语句与程序代码分开，解耦合。

1、简单
	delimiter\\
	create procedure p1()
	begin
		select * from student;
		insert into teacher(tname) values("ct");
	end
	\\delimiter;
2、传参数(in,  out,inout)
	delimiter \\
	create procedure p2(
		in n1 int,
		in n2 int
	)
	begin
		select * from student where id>n1;
	end \\
	delimiter;
	调用：
		call p2(12,2)
		cursor.callproc("p2",(12,2))
3、传参数(out)
	delimiter \\
	create procedure p3(
		in n1 int,
		out n2 int               #重新给变量赋值并返回
	)
	begin
		set n2=123;
		select * from student where id>n1;
	end \\
	delimiter;
	调用：
		终端：
			set @v1=0;
			call p2(12,@v1);
			select @v1;
		mysql：
			cursor.callproc("p2",(12,2))
			cursor.execute('select @_p3_0,@_p3_1')   #再进行一次查询
			r = cursor.fetchall()
			print(r)

存储过程的特性：
	1、可传参：in out inout
	2、pymysql 
		cursor.callproc("p2",(12,2))
		r1 = cursor.fetchall()
		print(r1)
		cursor.execute('select @_p3_0,@_p3_1')   #再进行一次查询
		r2 = cursor.fetchall()
		print(r2)
	3、为什么有结果集又有out伪造的返回值？
		delimiter \\
		create procedure p3(
			in n1 int,
			out n2 int          用于表示存储过程的执行结果。返回1,2,3
		)
		begin
			insert。。。。
			insert。。。。
			insert。。。。
		end \\
		delimiter;
	4、事物
		delimiter \\
		create procedure p4(
			out status int          
		)
		begin
			1.声明如果出现异常则执行{
				set status=1;
				rollback;
			}
			开始事物
				。。。
				commit;
			结束事物
		end \\
		delimiter;
	5、游标(类似于for循环，效率低)
		delimiter //
		create procedure p5()
		begin 
			declare ssid int; -- 自定义变量1  
			declare ssname varchar(50); -- 自定义变量2  
			DECLARE done INT DEFAULT FALSE;

			DECLARE my_cursor CURSOR FOR select sid,sname from student;
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
			
			open my_cursor;
				xxoo: LOOP
					fetch my_cursor into ssid,ssname;
					if done then 
						leave xxoo;
					END IF;
					insert into teacher(tname) values(ssname);
				end loop xxoo;
			close my_cursor;
		end  //
		delimter ;
	6、动态执行SQL(防止SQL注入)
		delimiter \\
		CREATE PROCEDURE p6 (
			in nid int
		)
		BEGIN
			set @nid=nid;
			PREPARE prod FROM 'select * from student where sid > ?';
			EXECUTE prod USING @nid;
			DEALLOCATE prepare prod; 
		END\\
		delimiter ;

索引：
作用：
1、约束
2、加速查找
类型：
1、普通索引
create table emp(
id int,
name vachar(30),
index putong (name) – 添加普通索引
);
2、唯一索引
create table emp(
id int,
name vachar(30),
unique index weiyi (name) – 添加唯一索引，创建表是写unique自动添加
);
3、全局索引
create table emp(
id int,
name vachar(30),
fulltext index quanju (name) – 添加唯一索引，创建表是写unique自动添加
);
4、组合索引
create table emp(
id int,
name vachar(30),
resume text,
index zuhe (name,resume) – 添加唯一索引，创建表是写unique自动添加
);

手动创建索引：
	create index 索引名 on 表名(字段名);
删除索引：
	alter table 表名 drop index 索引名;