s4 mysql其他 笔记
MySQL二
____oldboy fullstack4
参考博客：
Python开发【第十八篇】：MySQL（二）
http://www.cnblogs.com/wupeiqi/articles/5713323.html
索引补充
http://www.cnblogs.com/wupeiqi/articles/5716963.html
- 权限管理：
- 基于用户权限管理
用户信息
id username pwd
1 alex 123123
权限
1 订单管理
2 用户管理
用户类型&权限
1 1
1 2
2 1
3 1
- 基于角色的权限管理
用户信息
id username pwd role_id
1 alex 123123 1
2 eric 123123 1
权限
1 订单管理
2 用户管理
角色表：
1 IT部门员工
2 咨询员工
3 IT主管
角色、权限管理
1 1
1 2
3 1
3 2
1. 视图
100个SQL,88: v1
select .. from v1
select asd from v1
某个查询语句设置别名，日后方便使用
- 创建
create view 视图名称 as SQL语句
PS: 虚拟存在，不能插入数据
- 修改
alter view 视图名称 as SQL语句
- 删除
drop view 视图名称;
2. 触发器
当对某张表做：增删改操作时，可以使用触发器自定义关联行为
# 查询时不会引发触发器
insert into tb (....)
-- delimiter // # 更改终止符
-- create trigger t1 BEFORE INSERT on student for EACH ROW
-- BEGIN
-- INSERT into teacher(tname) values(NEW.sname);
-- INSERT into teacher(tname) values(NEW.sname);
-- END //
-- delimiter ; # 更改回终止符
-- insert into student(gender,class_id,sname) values('女',1,'陈涛'),('女',1,'张根');
-- NEW,代指新数据，插入、更新时
-- OLD,代指老数据，删除、更新时
-- drop trigger t1;
3. 函数
内置函数：（具体见博客）
执行函数： select CURDATE();
blog
id title ctime
1 asdf 2019-11
2 asdf 2019-11
3 asdf 2019-10
4 asdf 2019-10
select ctime,count(1) from blog group ctime
时间格式化函数：
select DATE_FORMAT(ctime, "%Y-%m"),count(1) from blog group DATE_FORMAT(ctime, "%Y-%m")
2019-11 2
2019-10 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. 存储过程 v5.5之后
保存在MySQL上的一个别名 => 一坨SQL语句
使用：别名()
用于替代程序员写SQL语句
方式一：
MySQL: 存储过程
程序：调用存储过程
方式二：
MySQL：nothing
程序：SQL语句
方式三：
MySQL：nothing
程序：类和对象（自动转换成SQL语句）
1. 简单
delimiter //
create procedure p1()
BEGIN
select * from student;
INSERT into teacher(tname) values("ct");
END
delimiter ;
终端：
call p1()
pymysql：
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; # session级别变量
call p2(12,@v1)
select @v1; # @v1被重新赋值，selet @v1表示查看
set @_p3_0 = 12
ser @_p3_1 = 2
call p3(@_p3_0,@_p3_1)
select @_p3_0,@_p3_1
cursor.callproc('p3',(12,2))
r1 = cursor.fetchall()
print(r1)
cursor.execute('select @_p3_0,@_p3_1')
r2 = cursor.fetchall()
print(r2)
- 示例：
import pymysql
# conn = pymysql.connect(host='localhost',user='root',password='',database='db66',charset='utf8')
# cursor =conn.cursor()
# # cursor.callproc('p1')
# cursor.callproc('p2',(12,2))
# conn.commit()
# result= cursor.fetchall()
# print(result) # 中文需要设置charset
# cursor.close()
# conn.close()
conn = pymysql.connect(host='localhost',user='root',password='',database='db66',charset='utf8')
cursor =conn.cursor()
cursor.callproc('p3',(12,2))
result= cursor.fetchall()
print(result)
cursor.execute('select @_p3_0,@_p3_1')
result2= cursor.fetchall()
print(result2) # ((12,123123),)
cursor.close()
conn.close()
- 存储过程特性：
a. 可传参： in out inout
b. pymysql操作：
cursor.callproc('p3',(12,2))
r1 = cursor.fetchall()
print(r1)
cursor.execute('select @_p3_0,@_p3_1')
r2 = cursor.fetchall()
print(r2)
- 为什么有结果集又有out伪造的返回值？
delimiter //
create procedure p3(
in n1 int,
out n2 int 用于标识存储过程的执行结果 1,2
)
BEGIN
insert into vv(..)
insert into vv(..)
insert into vv(..)
END //
delimiter ;
4. 事务（原子性操作）
delimiter //
create procedure p4(
out status int
)
BEGIN
1. 声明如果出现异常则执行{
set status = 1;
rollback;
}
开始事务
-- 由秦兵账户减去100
-- 方少伟账户加90
-- 张根账户加10
commit;
结束
set status = 2;
END //
delimiter ;
=====
delimiter \\
create PROCEDURE p5(
OUT p_return_code tinyint
)
BEGIN
DECLARE exit handler for sqlexception
BEGIN
-- ERROR
set p_return_code = 1;
rollback;
END;
START TRANSACTION;
DELETE from tb1;
insert into tb2(name)values('seven');
COMMIT;
-- SUCCESS
set p_return_code = 2;
END\\
delimiter ;
5. 游标（实现循环）
delimiter //
create procedure p6()
begin
declare row_id int; -- 自定义变量1
declare row_num int; -- 自定义变量2
declare done INT DEFAULT FALSE;
declare temp int;
declare my_cursor CURSOR FOR select id,num from A;
declare CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
open my_cursor;
xxoo: LOOP
fetch my_cursor into row_id,row_num;
if done then
leave xxoo;
END IF;
set temp = row_id + row_num;
insert into B(number) values(temp);
end loop xxoo;
close my_cursor;
end //
delimter ;
6. 动态执行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 ;
- 索引
作用：
- 约束
- 加速查找
索引：
- 主键索引：加速查找 + 不能为空 + 不能重复
- 普通索引：加速查找
- 唯一索引：加速查找 + 不能重复
- 联合索引（多列）：
- 联合主键索引
- 联合唯一索引
- 联合普通索引
加速查找举例：
select * from tb where name='asdf'
select * from tb where id=999
# mysql -u root -h 192.168.11.98 -p
索引种类（某种格式存储）：
hash索引：
单值快,范围取值不占优势
btree索引：
InnoDB,二叉树
建立索引：
- a. 额外的文件保存特殊的数据结构、
- b. 查询快；插入更新删除慢
- c. 命中索引（命令中使用索引）
select * from userinfo3 where email='asdf';
select * from userinfo3 where email like 'asdf'; 慢
普通索引：
- create index 索引名称 on 表名(列名,)
- drop index 索引名称 on 表名
唯一索引：
- create unique index 索引名称 on 表名(列名)
- drop unique index 索引名称 on 表名
组合索引(最左前缀匹配)：
- create unique index 索引名称 on 表名(列名,列名)
- drop unique index 索引名称 on 表名
- create index ix_name_email on userinfo3(name,email,)
- 最左前缀匹配
select * from userinfo3 where name='alex';
select * from userinfo3 where name='alex' and email='asdf';
select * from userinfo3 where email='alex@qq.com';
# 不会索引
- 组合索引效率 > 索引合并
组合索引：
- (name,email,)
select * from userinfo3 where name='alex' and email='asdf';
select * from userinfo3 where name='alex';
索引合并：
- name
- email
select * from userinfo3 where name='alex' and email='asdf';
select * from userinfo3 where name='alex';
select * from userinfo3 where email='alex';
两个名词：
覆盖索引：
- 在索引文件中直接获取数据
# select id from userinfo where id=999;
索引合并：
- 把多个单列索引合并使用
全文索引：
借助第三方工具，通常不在mysql操作
- 频繁查找的列创建索引
- 创建索引
- 命中索引 *****
- 正确使用索引（id，email）：
- like '%xx'
select * from tb1 where email like '%cn';
# 数据量大时避免使用，借助第三方工具
- 使用函数
select * from tb1 where reverse(email) = 'wupeiqi';
- or
select * from tb1 where nid = 1 or name = 'seven@live.com';
# nid索引，name不索引
特别的：当or条件中有未建立索引的列才失效，以下会走索引
select * from tb1 where nid = 1 or name = 'seven';
select * from tb1 where nid = 1 or name = 'seven@live.com' and email = 'alex'
# 忽略name，使用nid、email索引
- 类型不一致
如果列是字符串类型，传入条件是必须用引号引起来，不然...
select * from tb1 where email = 999;
- !=
select * from tb1 where email != 'alex'
特别的：如果是主键，则还是会走索引
select * from tb1 where nid != 123
- >
select * from tb1 where email > 'alex'
特别的：如果是主键或索引是整数类型，则还是会走索引
select * from tb1 where nid > 123
select * from tb1 where num > 123
- order by
select name from tb1 order by email desc;
当根据索引排序时候，选择的映射如果不是索引，则不走索引
特别的：如果对主键排序，则还是走索引：
select * from tb1 order by nid desc;
- 组合索引最左前缀
如果组合索引为：(name,email)
name and email -- 使用索引
name -- 使用索引
email -- 不使用索引
- 其他注意事项：
- 避免使用select *
- count(1)或者count(列)代替count(*)
- 创建表时尽量使用 char代替varchar
- 表字段顺序固定长度的字段优先
- 组合索引代替多个单列索引（经常使用多个条件查询时）
- 尽量使用短索引
creae index ixx on tb(title(16))
# title(16) 前16个字符索引
# Text
- 使用Join代替子查询(sub-queries)(mysql无差别)
- 连表时注意条件类型一致
- 索引散列值（重复少）不适合建索引，例如：性别。
- 执行计划
让mysql预估执行操作(一般正确)
all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
慢：（id,email索引，name不索引）
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(走索引)
- DBA工作
慢日志
- 执行时间 > 10
- 未命中索引
- 日志文件路径
配置：
- 基于内存
show variables like '%query%'
set global 变量名 = 值
- 基于配置文件
mysqld --defaults-file='E:\...\my-default.ini'
my.conf内容：
slow_query_log = ON
slow_query_log_file = D:/....
注意：修改配置文件之后，需要重启服务
- 分页 *******
基础：
select * from userinfo3 limit 20,10;
方案一：
不让看
方案二：
索引表中扫（效率一般）
select * from userinfo3 where id in(select id from userinfo3 limit 200000,10)
方案三：
记录当前页最大或最小ID：# 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;
- 上一页 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
)
# id不连续，所以无法直接使用id范围进行查找
