MySQL数据库进阶
一、视图（不重要）
视图是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，并可以将其当作表来使用
1.1 为什么需要视图
- 简化查询
- 可以进行权限控制，我们可以把表的权限进行封闭，但是开放相应的视图权限，视图里只开放部分数据
1.2 创建视图
--格式：CREATE VIEW 视图名称 AS SQL语句 CREATE VIEW v1 AS SELET nid, name from A where nid > 4
1.3 使用视图
使用视图时，将其当作表进行操作即可,视图和真实的表都会相互影响
select * from v1
1.4 删除视图
--格式：DROP VIEW 视图名称 DROP VIEW v1
1.5 修改视图
-- 格式：ALTER VIEW 视图名称 AS SQL语句 ALTER VIEW v1 AS SELET A.nid, B. NAME FROM A LEFT JOIN B ON A.id = B.nid LEFT JOIN C ON A.id = C.nid WHERE A.id > 2 AND C.nid < 5
二、函数（不重要）
2.1 常见内置函数
CHAR_LENGTH(str) 返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。 对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。 CONCAT(str1,str2,...) 字符串拼接 如有任何一个参数为NULL ，则返回值为 NULL。 FORMAT(X,D) 将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若 D 为 0, 则返回结果不带有小数点，或不含小数部分。 例如： SELECT FORMAT(12332.1,4); 结果为： '12,332.1000' INSTR(str,substr) 返回字符串 str 中子字符串的第一个出现位置。 LEFT(str,len) 返回字符串str 从开始的len位置的子序列字符。 LOWER(str) 变小写 UPPER(str) 变大写 LTRIM(str) 返回字符串 str ，其引导空格字符被删除。 RTRIM(str) 返回字符串 str ，结尾空格字符被删去。 SUBSTRING(str,pos,len) 获取字符串子序列 LOCATE(substr,str,pos) 获取子序列索引位置 REPEAT(str,count) 返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count 。 若 count <= 0,则返回一个空字符串。 若str 或 count 为 NULL，则返回 NULL 。 REPLACE(str,from_str,to_str) 返回字符串str 以及所有被字符串to_str替代的字符串from_str 。 REVERSE(str) 返回字符串 str ，顺序和字符顺序相反。 RIGHT(str,len) 从字符串str 开始，返回从后边开始len个字符组成的子序列
2.2 自定义函数
delimiter \\ create function f1( i1 int, i2 int) returns int BEGIN declare num int; set num = i1 + i2; return(num); END \\ delimiter ;
2.3 删除函数
drop function func_name;
2.4 执行函数
# 获取返回值 declare @i VARCHAR(32); select UPPER('alex') into @i; SELECT @i; # 在查询中使用 select f1(11,nid) ,name from tb2;
三、触发器（不重要）
3.1 触发器的定义
进行数据库应用软件的开发时，我们有时会碰到表中的某些数据改变，希望同时引起其他相关数据改变的需求
利用触发器就能满足这样的需求,它能在表中的某些特定数据变化时自动完成相关的操作
3.2 触发器的应用场合
当向一张表中添加或删除记录时，需要在相关表中进行同步操作
比如，当一个订单产生时,订单所购的商品的库存量相应减少
当表上某列数据的值与其他表中的数据有联系时
比如，当某客户进行欠款消费，可以在生成订单时通过设计触发器判断该 客户的累计欠款是否超出了最大限度
当需要对某张表进行跟踪时
比如，当有新订单产生时，需要及时通知相关人员进行处理，此时可以在 订单表上设计添加触发器加以实现
3.3. 触发器的创建语法
# 插入前 CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW BEGIN ... END # 插入后 CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW BEGIN ... END # 删除前 CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW BEGIN ... END # 删除后 CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW BEGIN ... END # 更新前 CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW BEGIN ... END # 更新后 CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW BEGIN ... END
3.4 触发器案例
delimiter // CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW BEGIN INSERT INTO tb2 (NAME) VALUES ('aa') END// delimiter ;注意：NEW表示即将插入的数据行，OLD表示即将删除的数据行
3.5 删除触发器
DROP TRIGGER tri_after_insert_tb1;
3.6 使用触发器
触发器无法由用户直接调用，而知由于对表的【增/删/改】操作被动引发的
insert into tb1(num) values(666)
四、事务
通俗的说，事务指一组操作，要么都执行成功，要么都执行失败
4.1 事务的ACID特性
- 原子性(Atomicity)，原子意为最小的粒子，即不能再分的事务，要么全部执行，要么全部取消（就像上面的银行例子）
- 一致性(Consistency)：指事务发生前和发生后，数据的总额依然匹配
- 隔离性(Isolation)：简单点说，某个事务的操作对其他事务不可见的
- 持久性(Durability)：当事务完成后，其影响应该保留下来，不能撤消，只能通过“补偿性事务”来抵消之前的错误
4.2 事务的用法
开启事务(start transaction) 执行sql操作(普通sql操作) 提交/回滚(commit/rollback)create table user( id int auto_increment primary key, name varchar(32) not null default '', money int not null default 1000 )engine=innodb charset=utf8; insert into user (name,money) values ('wangyong',1000),('liguo',1000); # 正常操作 start transaction; update user set money=1100 where name='wangyong'; updata user set money=900 where name='liguo'; # 出现异常 rollback; # 最终结果， 数据未发生变化 mysql> select * from user; +----+----------+-------+ | id | name | money | +----+----------+-------+ | 1 | wangyong | 1000 | | 2 | liguo | 1000 | +----+----------+-------+
注意：建表的时候, 选择 innodb 引擎
4.3 事务常见的执行流程
购物流程中的订单付款操作含有以下3步子操作：
- 从用户的余额中扣除订单价格
- 在用户的账户变动记录里记下流水
- 修改订单状态为已付款
这三步需保证原子性,请用事务来控制此过程
五、索引
索引是表的目录，在查找内容之前可以先在目录中查找索引位置，以此快速定位查询数据。对于索引，会保存在额外的文件中。
5.1 索引的时间复杂度
设有N条记录，如果不用索引，那平均查找N/2次，但是用了索引之后呢？一般情况下，是以2为底，N的对数
这块就涉及到二叉树的查找了
5.2 索引的好处与坏处
- 好处：加快了查询的速度
- 坏处：降低了增删改的速度，增大了表的文件大小(索引文件甚至比数据文件都还要大)
问题：大数据量进行导入别的数据表的时候，怎么办？？
5.3 索引的概念
类似于字典中的目录，如何快速找到某个字?
可以给字典加目录
对数据库来说,索引的作用即是给 “数据” 加目录
所以所谓的索引，是数据库中专门用于帮助用户快速查询数据的一种数据结构
5.4 常见的索引类型
- 主键索引：加速查询 + 列值唯一 + 表中只有一个（不可以有null）
- 普通索引：仅加速查询
- 唯一索引：加速查询 + 列值唯一（可以有null）
- 组合索引：多列值组成一个索引，
5.4.1 主键索引（primary key）
1. 创建表 + 索引
# 第一种: create table test( id int auto_increment primary key, name varchar(32) not null default '' )engine=innodb charset=utf8; # 第二种： create table test( id int not null default 0, name varchar(32) not null default '' )engine=innodb charset=utf8; alter table test change id id int auto_increment primary key
5.4.2 普通索引（index）
普通索引仅有一个功能：加速查询
1. 创建表 + 索引
# 第一种 create table test( id int auto_increment primary key, name varchar(32) not null default '', index ix_name (name) )engine=innodb charset=utf8; # 第二种 create table test( id int auto_increment primary key, name varchar(32) not null default '' )engine=innodb charset=utf8; create index ix_name on test (name);2. 创建索引
create index index_name on table_name(column_name)3. 删除索引
drop index_name on table_name;4. 查看索引
show index from table_name;
5.4.3 唯一索引（unique）
唯一索引有两个功能：加速查询 和 唯一约束（可含null）
1. 创建表 + 索引
# 第一种 create table test( id int auto_increment primary key, name varchar(32) not null default '', unique ix_name (name) )engine=innodb charset=utf8; # 第二种 create unique index 索引名称 on 表名(name); create table test( id int auto_increment primary key, name varchar(32) not null default '' )engine=innodb charset=utf8; create unique index ix_name on test (name);2. 创建索引
create unique index 索引名 on 表名(列名)3. 删除索引
drop unique index 索引名 on 表名
5.4.4 联合索引 （unique index）
组合索引是将n个列组合成一个索引
其应用场景为：频繁的同时使用n列来进行查询，如：where n1 = ‘alex’ and n2 = 666
1. 创建表 + 索引
# 创建联合唯一索引 create unique index 索引名称 on 表名 (name,age); create table test( id int auto_increment primary key, age int not null default 0, name varchar(32) not null default '' )engine=innodb charset=utf8; create unique index ix_name_age on test (age, name); create table in3( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text )2. 创建索引
create index ix_name_email on in3(name,email);如上创建组合索引之后，查询：
- name and email – 使用索引
- name – 使用索引
- email – 不使用索引
注意：对于同时搜索n个条件时，组合索引的性能好于多个单一索引合并
5.5 使用规则
数据库表中添加索引后确实会让查询速度起飞，但前提必须是正确的使用索引来查询，如果以错误的方式使用，则即使建立索引也会不奏效。
即使建立索引，索引也不会生效：- like '%xx' select * from tb1 where name like '%cn'; - 使用函数 select * from tb1 where reverse(name) = 'xxx'; - 类型不一致 如果列是字符串类型，传入条件是必须用引号引起来，不然... select * from tb1 where name = 999; - order by select email from tb1 order by name desc; 当根据索引排序时候，选择的映射如果不是索引，则不走索引 特别的：如果对主键排序，则还是走索引： select * from tb1 order by nid desc; - 组合索引最左前缀 如果组合索引为：(name,email) name and email -- 使用索引 name -- 使用索引 email -- 不使用索引其他注意事项
- 避免使用select * - count(1)或count(列) 代替 count(*) - 创建表时尽量时 char 代替 varchar - 表的字段顺序固定长度的字段优先 - 组合索引代替多个单列索引（经常使用多个条件查询时） - 使用连接（JOIN）来代替子查询(Sub-Queries) - 索引散列值（重复少）不适合建索引，例：性别不适合
5.6 查看是否使用索引：explain
使用方式：explain selext * from test;
explain selext * from test; +----+-------------+-------+-------+---------------+---------+---------+------+------+------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra +----+-------------+-------+-------+---------------+---------+---------+------+------+------------- | 1 | SIMPLE | test | index | NULL | ix_name | 98 | NULL | 1 | Using index +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------参数分析：
id 查询顺序标识 select_type 查询类型 SIMPLE 简单查询 PRIMARY 最外层查询 SUBQUERY 映射为子查询 DERIVED 子查询 UNION 联合 UNION RESULT 使用联合的结果 ... table 正在访问的表名 type 查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const ALL 全表扫描，对于数据表从头到尾找一遍 select * from tb1; 特别的：如果有limit限制，则找到之后就不在继续向下扫描 select * from tb1 where email = 'xxxx@live.com' select * from tb1 where email = 'xxxx@live.com' limit 1; 虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。 INDEX 全索引扫描，对索引从头到尾找一遍 select nid from tb1; RANGE 对索引列进行范围查找 select * from tb1 where name < 'xxx'; PS: between and in > >= < <= 操作 注意：!= 和 > 符号 REF 根据索引查找一个或多个值 select * from tb1 where name = 'xxx'; CONST 常量 表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。 select nid from tb1 where nid = 2 ; SYSTEM 系统 表仅有一行(=系统表)。这是const联接类型的一个特例。 possible_keys 可能使用的索引 key 真实使用的 key_len MySQL中使用索引字节长度 rows mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值 extra 该列包含MySQL解决查询的详细信息 “Using index” 此值表示mysql将使用索引覆盖，以避免访问表。不要把覆盖索引和index访问类型弄混了。 “Using where” 这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。 “Using temporary” 这意味着mysql在对查询结果排序时会使用一个临时表。 “Using filesort” 这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。删除索引后效果
# drop 索引名称 on 表名 drop index ix_name_age on test; mysql> explain select * from test; +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 1 | NULL | +----+-------------+-------+------+---------------+------+---------+------+------+-------+
5.7 慢日志
日志文件: 记录了执行速度特别慢的SQL语句
配置MySQL自动记录慢日志
slow_query_log = OFF 是否开启慢日志记录 long_query_time = 2 时间限制，超过此时间，则记录 slow_query_log_file = /usr/slow.log 日志文件
5.7.1 开启方式
1. show variables like '%query%';
2. set global long_query_time = 1; 设置慢查询的时间
3. set global slow_query_log = ON
4. set global slow_query_log_file = E:\program\mysql-5.6.44-winx64\data\oldboy-slow.log
5.7.2 普通日志记录（general log）
SQL审计 (记录sql的操作语句)
show variables like '%general%';
