MySQL(进阶篇)

MySQL(进阶篇)

目录

• MySQL(进阶篇)
  • 一、视图
    • 1. 理解
    • 2. 用法
  • 二、储存过程
    • 1. 理解
    • 2. 简单的存储过程
    • 3. 带参数的存储过程
  • 三、IF判断语句
  • 四、循环语句
    • 1. while循环
    • 2. repeat循环
    • 3. loop循环
  • 五、动态执行sql语句
  • 六、触发器
  • 七、事务
  • 八、函数
    • 1. 内置函数
    • 2. 自定义函数
  • 九、索引
    • 1. 索引的功能
    • 2. 索引种类
    • 3. 覆盖索引
    • 4. 合并索引
    • 5. 执行计划
    • 10. 如何命中索引
    • 11. 注意事项
  • 十、日志记录
    • 1. 修改日志文件
    • 2. 修改内存中的设置

一、视图

1. 理解

相当于将一个变量名和一个sql语句绑定，可通过变量名重复调用该sql语句
sql语句的结果一般为一个临时表，所以视图也被称为动态的临时表

2. 用法

-- create a view
create view v_name as sql_statement

-- delete a view
drop view v_name

-- alter a view
alter view v_name as sql_statement

-- use view
select * from v_name

二、储存过程

1. 理解

一系列sql语句的集合

2. 简单的存储过程

-- create a procedure
delimiter //
create procedure func_name()
BEGIN
	select * from tb;
END //
delimiter ;

-- call a procedure
call func_name()

3. 带参数的存储过程

参数类型
in: 用来向存储过程传入值
out: 用来接收存储过程的返回值
inout: 既可向存储过程传入值，也可以接收返回值

delimiter \\
create procedure p1(
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;
	
	-- 一个存储过程，只能存在一个结果集(临时表)
	select * from tb;
END \\
delimiter ;

-- 执行过程
set @t1 = 4;
set @t2 = 0;
call p1(1,2,@t1,@t2);
select @t1,@t2;

-- 删除存储过程
drop procedure proc_name;

三、IF判断语句

BeGIN
	declare i int default 0;
	if i = 1 then
		select 1;
	elseif i = 2 then
		select 2;
	else
		select 3;
	end if;
END

四、循环语句

1. while循环

BEGIN
	declare num int;
	set num = 0;
	while num < 10 do
		select num;
		set num = num + 1;
	end while;
END

2. repeat循环

BEGIN
	declare i int;
	set i = 0;
	repeat
		select i;
		set i = i+1;
		until i >= 5;
	end repeat;
END

3. loop循环

BEGIN
	declare i int default 0;
	loop_exam : loop
		select i;
		set i = i+1;
		if i > 5 then
			leave loop_exam;
		end if
	end loop
END

五、动态执行sql语句

mysql级别防止sql注入

delimiter \\
drop procedure if exists proc_sql \\
create procedure proc_sql()
BEGIN
	declare num int;
	set num = 11;
	set @num = num;
	
	prepare prod from "select * from tb2 where nid>?";
	execute prod using @num;	-- 只能用@name的参数
	deallocate prepare prod;
END \\
delimiter ;

六、触发器

创建后，当mysql触发指定事件时，会自动执行触发器
before/ after (决定触发器的执行顺序)
insert/ delete/ update (决定触发器的触发条件)
NEW: 可接受传入数据的对象，例如NEW.column_name
OLD: 接受被删/改列数据的对象，列如OLD.column_name

create trigger tri_before_insert_tb1 before insert on tb1 for each row
BEGIN
	statement
END

七、事务

将两个或多个事件合成一个整体，这个整体称为事务

delimiter \\
create prodedure p1(
	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 tb1;
		insert into tb2(num) values("seven");
	commit
	-- success
	set p_return_code = 0;
END \\
delimiter ;

八、函数

1. 内置函数

-- 有关字符串拼接的函数
char_length(str)
concat(str, str2, ...)	-- 连接多个字符串
concat_ws(seperator, str1, str2, ...)	-- 用特定字符连接多个字符串

-- 数字有关的函数
conv(N, from_base, to_base)		-- 进制转换
format(X,D)
-- X为以3位分隔的数字，例123,321.2
-- D为小数点后保留的位数

insert(str, pos, len. newstr)
-- 具有替换功能
-- 序号从1开始
-- pos :标记插入字符串的首个位置
-- len :替换长度
-- newstr :需要替换的字符串

-- 提取指定字符有关的函数
instr(str, substr)		-- 返回substr首次出现的位置
left(str, len)			-- 返回从左开始的len个字符
right(str, len)			-- 返回从右开始的len个字符
substring(str, start, len)	-- 取字符串中的相应字符

-- 变换字母大小写有关的函数
lower(str)				-- 将英文字母变大写
upper(str)				-- 将英文字母变小写

-- 清洗数据有关的函数
ltrim(str)		-- 移除左边的空格
rtrim(str)		-- 移除右边的空格
trim(str)		-- 移除两边的空格
trim(leading "x" from "xxxarnoxxx")		-- 左
trim(trailing "x" from "xxxarnoxxx")	-- 右
trim(both "x" from "xxxarnoxxx")		-- 两边

-- other
locate(substr, str, pos)	# 获取索引的位置
repeat(str, count)			# 重复打印
replace(str, from_sbustr, to_substr)
reverse(str)
space(n)		-- 生成由n个空格组成的字符串

2. 自定义函数

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

-- call
f1();
-- delete
drop function func_name;

九、索引

1. 索引的功能

• 对输入数据进行限制
• 加速查找(通过新建索引表和B-tree数据结构实现)

2. 索引种类

• 普通索引

-- create
index ix_name(列名); or create index ix_name on 表(列名);
-- drop 
drop ix_name on tb1;
-- show
show index from tb1;

• 唯一索引(约束列数据不能重复)

-- create
unique ix_name(col_name); or create unique index ix_name on tb1(col_name)
-- delete
drop unique index ix_name on tb1;
-- show
show index from tb1;

• 主键索引(约束列数据不能重复，且不能为null)

-- create
primary key(col_name); or alter table tb1 add primary key(col_name);
-- delete
alter table tb1 drop primary key;
alter table tb1 modify col_name int,drop primary key;

• 组合索引(组合索引意味着以多列生成一个索引对象，遵循最左原则)

3. 覆盖索引

只查找符合条件的索引列的值，旨在索引表中就可得出相应值

4. 合并索引

对多个索引分别进行条件扫描，然后将它们各自的结果进行合并

select * from tb1 where name="arno" or email="xxx"

5. 执行计划

explain sql_Statement （可以相对准确地给出sql地运行状态）

-- type
all			-- 全数据表扫描
index		-- 全索引表扫描
range		-- 在索引表内进行范围查找(注意不等于和大于，不走索引)
index_merge	-- 合并索引
ref			-- 根据索引查找一个或多个值
eq_ref		-- 适用primary key or unique 列作为条件来索引
const		-- 常量(覆盖索引)
system		-- 系统(表中只有一行数据)

-- notice
在搜索单个数据时，可以加limit来提高效率

10. 如何命中索引

• like "conn%" -- %在尾部在尾部走索引，在前不走
• 不要对索引列的值进行函数操作
• or时，两边都需要为索引列
• 数据类型一致才可以走索引
• =时，如果是主键，则继续走索引
• >时，如果是主键或整数类型索引，也走索引
• order by 走index索引
• 组合索引左侧原则
• 问题1：num != 123 走不走索引?
• 问题2：(name, pwd, email) pwd and email 走不走索引?

11. 注意事项

• 避免使用 select *
• 用coount(1) or count(col_name) 替代 count(*)
• 尽量用char替代varchar (当遇到可测定长度的数据时)
• 定长的字段优先写
• 用组合索引替代多个单列索引 (需要多条件查询时)
• 尽量使用短索引
• 使用连表代替子查询
• 多个重复值不适合建索引，例如：性别
• 问题：怎么建立一个短查询？

十、日志记录

公司中，不要轻易重启mysql服务，最好同时修改内存和配置文件

1. 修改日志文件

slow_query_log = ON
log_queries_not_using_indexes = ON
long_query_time = 0.5
slow_query_log_file = path

2. 修改内存中的设置

show variables like "%query%";
set long_query_time = o.5;