mysql笔记(五)--其他操作

文章目录

(一) mysql笔记–基本概念
(二) mysql笔记–基本操作
(三) mysql笔记–事务
(四) mysql笔记–索引
(五) mysql笔记–其他操作
(六) mysql笔记–锁

1. 视图

    某个查询语句设置别名,方便日后使用,即创建一个临时表,是虚拟的表,因为保存的是语句(不常,不方便维护用)

• 创建
  create view 视图名称 as SQL
• 修改
  alter view 视图名称 as SQL
• 删除
  drop view 视图名称;

2. 触发器

    当对某张表做：增删改操作时，可以使用触发器自定义关联行为

	delimiter //
	create trigger t1 BEFORE INSERT on student for EACH ROW
	BEGIN
	INSERT into teacher(tname) values(NEW.sname);
	END //
	delimiter ;

	-- 插入数据前则执行begin里面的内容
	-- delimiter,修改语句终结符
	-- NEW,代指新数据
	-- OLD,代指老数据

3. 函数

1. 内置函数：

    	执行日期函数 select CURDATE();
    	获取年月日期:
    	select ctime,count(1) from blog group ctime
    	select DATE_FORMAT(ctime, "%Y-%m"),count(1) from blog group DATE_FORMAT(ctime, "%Y-%m")
   

2. 自定义函数(有返回值)：

	delimiter \\
		create function f1(
			i1 int,
			i2 int)
		returns int
		BEGIN
			declare num int default 0;
			set num = i1 + i2;
			return(num);
		END \\
	delimiter ;

		使用方式:
		SELECT f1(1,100);

4. 存储过程

MySQL: 存储过程方便了操作,功能比视图强大,但不常用
程序：调用存储过程,支持事务与游标操作,可用于防止sql注入

1. 简单

	create procedure p1()
	BEGIN
		select * from student;
		INSERT into teacher(tname) values("ct");
	END

		调用方式:
		call p1()
		cursor.callproc('p1')

2. 传参数（in,out,inout）

	delimiter //
	create procedure p2(
		in n1 int,
		in n2 int
	)
	BEGIN
		
		select * from student where sid > n1;
	END //
	delimiter ;

		调用:
		call p2(12,2)
		cursor.callproc('p2',(12,2))

3. 参数 out

	delimiter //
	create procedure p3(
		in n1 int,
		inout n2 int
	)
	BEGIN
		set n2 = 123123;
		select * from student where sid > n1;
	END //
	delimiter ;

		参数获取与调用:
		set @v1 = 10;
		call p2(12,@v1)
		select @v1;
		
		set @_p3_0 = 12
		ser @_p3_1 = 2
		call p3(@_p3_0,@_p3_1)
		select @_p3_0,@_p3_1

4. 动态执行SQL（防SQL注入）

	delimiter //
	create procedure p7(
		in tpl varchar(255),
		in arg int
	)
	begin 
		1. 预检测某个东西 SQL语句合法性
		2. SQL =格式化 tpl + arg 
		3. 执行SQL语句
		
		set @xo = arg;
		PREPARE xxx FROM 'select * from student where sid > ?';
		EXECUTE xxx USING @xo;
		DEALLOCATE prepare prod; 
	end  //
	delimter ;

		调用方式:
		call p7("select * from tb where id > ?",9)

	delimiter \\
	CREATE PROCEDURE p8 (
		in nid int
	)
	BEGIN
		set @nid = nid;
		PREPARE prod FROM 'select * from student where sid > ?';
		EXECUTE prod USING @nid;
		DEALLOCATE prepare prod; 
	END\\
	delimiter ;

5. 执行计划

    可使用explain加sql语句查看该语句执行时间等级,可用于查看是否命中索引
    执行计划作用：让mysql预估执行操作(一般正确)
    等级: all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const

		慢：
			select * from userinfo3 where name='alex'
			
			explain select * from userinfo3 where name='alex'
			type: ALL(全表扫描)
				select * from userinfo3 limit 1;
		快：
			select * from userinfo3 where email='alex'
			type: const(走索引)

6. 日志

	慢日志
		- 执行时间 > 10
		- 未命中索引
		- 日志文件路径
		
	配置：
		- 内存
			show variables like '%query%'
			set global 变量名 = 值
		- 配置文件
			mysqld --defaults-file='E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\my-default.ini'
			
			my.conf内容：
				slow_query_log = ON
				slow_query_log_file = D:/....
				
			注意：修改配置文件之后，需要重启服务

7.分页

    由于id不连续，所以无法直接使用id范围进行查找,所以一般使用limit,分页间接命中索引提高翻页速度

		- 索引表中扫：
			select * from userinfo3 where id in(select id from userinfo3 limit 200000,10)
		- 方案：
			记录当前页最大或最小ID
			1. 页面只有上一页，下一页
				# max_id
				# min_id
				下一页：
					select * from userinfo3 where id > max_id limit 10;
				上一页：
					select * from userinfo3 where id < min_id order by id desc limit 10;
			2. 上一页 192 193  [196]  197  198  199 下一页
				
				select * from userinfo3 where id in (
					select id from (select id from userinfo3 where id > max_id limit 30) as N order by N.id desc limit 10
				)