MySQL高级
一、视图 view
1.1、概念
1、视图是简化查询过程，提?数据库安全性的虚拟表。
2、 视图中保存的仅仅是?条select语句，保存的是视图的定义，并没有保存真正的数据。视图中的源数据都来?于数据库表，数据库表称为基本表或者基表，视图称为虚拟表。
1.2、作用
1、防?未经许可的?户访问敏感数据，确保数据的安全性
2、 封装sql语句，简化查询过程
3、视图可对?户屏蔽真实表结构
1.3、语法
格式：
① 创建视图：CREATE VIEW 视图名字 AS SELECT 语句;
② 查询视图：SELECT * FROM 视图名;
③ 查看视图结构：desc 视图名;
④ 查看创建视图的?本信息：SHOW CREATE VIEW 视图名;
⑤ 修改视图：ALTER VIEW 视图名称 AS SQL语句 ;
⑥ 删除视图：DROP VIEW 视图名称;
-- =================================== 视图 ====================================
-- 1、创建视图
CREATE view view_1 as SELECT * from employees where salary >5000;
-- 2、查看视图
SELECT * from view_1;
-- 3、查看视图结构
desc view_1;
-- 4、查看创建视图的语句
show create view view_1;
-- 5、修改视图
alter view view_1 as select * from employees WHERE salary <= 5000;
-- 6、删除视图
drop view view_1;
注意：
查看当前?户是否有创建视图的权限
SELECT Select_priv,Create_view_priv FROM MySQL.user WHERE user=‘?户名’;
参数说明：
(1)Select_priv：属性表示?户是否具有SELECT权限，Y表示拥有SELECT权限，N表示没有。
(2)Create_view_priv：属性表示?户是否具有CREATE VIEW权限，Y表示拥有CREATE 权限，N表示没有；
(3)MySQL.user：表示MySQL数据库下?的user表。
(4)?户名：参数表示要查询是否拥有权限的?户，该参数需要?单引号引起来。
二、存储过程 procedure
2.1、概念
1、**存储过程（Stored Procedure）**是?种在数据库中存储复杂程序，以便外部程序调?的?种数据库对象。类似于java中的?法。
2、存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，?户可通过指定存储过程的名字并给定参数(需要时)来调?执?。
3、存储过程思想上很简单，就是数据库 SQL 语?层?的代码封装与重?。
2.2、优点
1、存储过程在创建的时候直接编译，?sql语句每次使?都要编译，提?执?效率
2、?个存储过程可以被重复使?。（其实sql语句也可以，没什么卵?）
3、?条sql语句，可能需要访问?张表，对数据库连接好?次，存储过程只会连接?次
4、 存储的程序是安全的。数据库管理员可以向访问数据库中存储过程的应?程序授予适当的权限，?不向基础数据库表提供任何权限。
2.3、缺点
1、可移植性太差了
2、对于简单的sql语句，毫?意义
3、对于只有?类?户的系统安全性毫?意义
4、 团队开发，标准不定好的话，后期维护很麻烦
5、对于开发和调试都很不?便
6、复杂的业务逻辑，?存储过程还是很吃?的
2.4、使用
1、创建存储过程
? DELIMITER $$
CREATE PROCEDURE 存储过程名(参数和返回值)? BEGIN
SELECT * FROM users;? END$$
DELIMITER $$ 说明：? IN 表示这个存储过程需要的输?参数
out表示这个存储过程需要的输出参数? inout是可以接受?个参数并输出?个参数
2、 调?存储过程
CALL 存储过程名(参数);
3、 删除存储过程
DROP PROCEDURE 存储过程名;
2.5、案例
-- ================================ 存储过程 ==================================
-- 1、 创建存储过程
delimiter $$ -- 声明结束标识符
create procedure pro_1()
begin
select * from employees;
end $$
delimiter $$
-- 2、调用
call pro_1();
-- 3、带入参【in】的存储过程 出参用【out】
-- 案例1：根据传递的员工编号来查询员工名
delimiter $$
CREATE procedure pro_2(in id int,out username varchar(32))
begin
select last_name into username from employees where employee_id = id;
end $$
delimiter $$
call pro_1(103,@username); -- 调用 @变量
select @username; -- 查询变量
-- 案例：创建存储过程，实现传递n值，获取n以内的整数和
delimiter $$
CREATE procedure sum1(in n int,out sum int)
BEGIN
declare i int default 1 ; -- 定义默认值
declare sums int DEFAULT 0 ;
while i <= n do
SET sums = sums + i;
SET i = i + 1;
end while;
SET sum = sums;
end $$
delimiter $$
-- 调用存储过程
call sum1(100,@sum);
SELECT @sum;
-- 删除存储过程
drop procedure pro_1;
drop procedure sum1;
三、触发器 tigger
3.1、概念
1、触发器的这种特性可以协助应?在数据库端确保数据的完整性。也可以把触发器理解成?个特殊的存储过程，不需要显示调?，是?动被调?的存储过程。
2、 类似于servlet中的监听器
3、监视某种情况，并触发某种操作，它是提供给程序员和数据分析员来保证数据完整性的?种?法，它是与表事件相关的特殊的存储过程，它的执?不是由程序调?，也不是??启动，?是由事件来触发，例如当对?个表进?操作
（insert，delete， update）时就会激活它执?。
3.2、语法
语法：
? DELIMITER $$
CREATE TRIGGER 触发器名 触发时机（BEFORE|AFTER） 触发事件（INSERT|UPDATE|DELETE）
ON 表名 FOR EACH ROW
BEGIN
执?语句列表;END $$
DELIMITER $$
说明：
1、 BEFORE和AFTER参数指定了触发执?的时间，在事件之前或是之后。
2、FOR EACH ROW表示任何?条记录上的操作满?触发事件都会触发该触发器，也就是说触发器的触发频率是针对每??数据触发?次。
触发事件参数详解：
1、 INSERT型触发器：插?某??时激活触发器，可能通过INSERT、REPLACE 语句触发；
2、 UPDATE型触发器：更改某??时激活触发器，可能通过UPDATE语句触发；
3、DELETE型触发器：删除某??时激活触发器，可能通过DELETE、REPLACE语句触发。
介绍：
? before INSERT：在添加之前激活触发器
? before DELETE：在删除之前激活触发器
? before UPDATE：在修改之前激活触发器
? after INSERT：在添加之后激活触发器
? after DELETE：在删除之后激活触发器
? after UPDATE：在修改之后激活触发器
3.3、使用
-- ============================= 触发器 ==================================
-- 1、创建一个表
use java0210
CREATE table if not EXISTS user_log(
id int PRIMARY KEY auto_increment comment '日志编号',
log_time datetime comment '时间',
msg varchar(32) comment '日志信息'
)comment '用户日志表';
-- 2、创建触发器--监听对user表插入后的
delimiter $$
create trigger trig1 after insert on user for each row
begin
insert into user_log(log_time,msg) values (now(),'user表中插入了一条数据');
end $$
delimiter $$
--说明：
-- MySQL 中定义了 NEW 和 OLD，?来表示触发器的所在表中，触发了触发器的那??数据，来引?触发器中发?变化的记录内容，具体地：
-- ① 在INSERT型触发器中，NEW?来表示将要（BEFORE）或已经（AFTER）插?的新数据；
-- ② 在UPDATE型触发器中，OLD?来表示将要或已经被修改的原数据，NEW?来表示将要或已经修改为的新数据；
-- ③ 在DELETE型触发器中，OLD?来表示将要或已经被删除的原数据；
desc user;
insert into user(name,age) VALUES('陈爽',27);
SELECT * from user_log;
-- 删除触发器
drop trigger trig1;
delimiter $$
create trigger trig2 after update on user for each row
begin
insert into user_log(log_time,msg) values (now(),concat ('修改前：',old.name,'------修改后：',new.name ));
end $$
delimiter $$
update user set name = '张三' WHERE age = 27;
update user set name = 'lisi' WHERE age = 27;
SELECT * from user_log;
四、存储引擎
4.1、概念
思考：我们在mysql中创建的数据库、表以及表中的数据是保存在哪?的？（都是以?件的形式存储在硬盘上的）
1、在关系型数据库中每?个数据表就对应?个?件，?这些?件是以什么?式存储在硬盘上的就取决于选择的存储引擎类型，不同的存储引擎存储?件的?式也是不?样的。
2、我们可以认为数据库存储引擎是规定数据表如何存储数据，如何为存储的数据建?索引以及如何?持更新、查询等技术的实现。
3、不同的存储引擎提供不同的存储机制、索引技巧、锁定?平等功能，使?不同的存储引擎，还可以获得特定的功能
4、由于在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型，也就是说存储引擎是针对表??的
5、查看MySQL?持的存储引擎类型：show engines
6、查看当前默认的存储引擎类型：show variables like ‘%storage_engine%’
4.2、详解
4.2.1、MyISAM
特点
1、在做插?、查询时速度快，性能?
2、 ?持全?索引，表级锁
3、不?持事务，外键
4、 其数据的物理组织形式是?聚簇表。数据和索引分开存储，顶级节点只存索引，数据都存储在B+树的叶?节点
文件存储格式：
1、tb01.frm：存储tb01表结构信息（表中有哪些列，数据类型等）
2、tb01.MYI：MY表示MYISAM存储引擎，I是index索引，这?存储tb01的索引信息。
3、tb01.MYD：MY表示MYISAM存储引擎，D是data数据，这?存储tb01的数据信息（即表中的记录）
4.2.2、InnoDB
特点
1、?持外键、?持事务
2、?持?级锁，因此在?并发量的情况下效率?
3、不?持全?索引
4、 其数据的物理组织形式是聚簇表。数据和索引放在?块，都位于B+树的叶?节点上。
文件存储格式：
1、xx.frm:同MYISAM存储引擎，也是?来存储表结构信息。
2、ibdata1:共享表空间，且来存储所有InnoDB数据表的数据信息，包含索引信息
4.2.3、指定存储引擎
建表时
create table 表名(
id bigint(12),
name varchar(200)
) ENGINE=MyISAM;
create table 表名(
id int(4),
cname varchar(50)
) ENGINE=InnoDB;
已建表修改
alter table 表名 engine = innodb;
五、索引
5.1、概念
1、索引是?种特殊的?件(InnoDB数据表上的索引是表空间的?个组成部分)，它们包含着对数据表?所有记录的引?指针。更通俗的说，数据库索引好?是?本书前?的?录，能加快数据库的查询速度
2、索引分为聚簇索引和?聚簇索引两种
5.2、分类
1、 普通索引 ：仅加速查询
2、 唯?索引 ：加速查询 + 列值唯?（可以有null）
3、 组合索引 ：多列值组成?个索引，
4、主键索引：加速查询 + 列值唯? + 表中只有?个（不可以有null）
5.3、创建索引
1、创建表的时候创建索引
语法：
CREATE TABLE tbl_name(
字段名称 字段类型 [完整性约束条件]，
…
? INDEX 索引名称
);
2、在已经存在的表上创建索引：
语法：
1、create index 索引名称 on 表名（字段名）
2、alter table 表名 add index 索引名称(字段名称);
3、查看索引
show index from 表名;
4、 删除索引
drop index 索引名 on 表名;
CREATE TABLE if not EXISTS tb01(
id int PRIMARY KEY,
xx varchar(24),
index xx
)
-- 添加索引
alter TABLE user add index index_i(name,age);
-- 查看表中索引
show index from user;
-- 删除索引
drop index index_i on user;
5.4、索引使用
1、建议使用
① 主键?动建?唯?索引，任何表?定要建主键
② 频繁作为查询条件的字段应该创建索引
③ 查询中与其它表关联的字段，外键关系建?索引
④ 组合索引的选择问题， 组合索引性价?更?
⑤ 查询中排序的字段，排序字段若通过索引去访问将??提?排序速度
⑥ 查询中统计或者分组字段
2、不建议使用
① 表记录太少
② 经常增删改的表或者字段，因为对表进?INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存?下索引?件。
③ Where条件??不到的字段不创建索引
3、注意事项
① 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
② 不在索引列上做任何操作（计算、函数、(?动or?动)类型转换），会导致索引失效?转向全表扫描
③ 存储引擎不能使?索引中范围条件右边的列
④ mysql 在使?不等于(!= 或者<>)的时候?法使?索引会导致全表扫描
⑤ is not null 也?法使?索引,但是is null是可以使?索引的(和?空约束有关系)
⑥ like以通配符开头(‘%abc…’)mysql索引失效会变成全表扫描的操作
⑦ 字符串不加单引号索引失效
六、锁机制
6.1、锁概念
1、锁是计算机协调多个进程或线程并发访问某?资源的机制。
2、在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争?以外，数据也是?种供许多?户共享的资源。如何保证数据并发访问的?致性、有效性是所有数据库必须解决的?个问题，锁冲突也是影响数据库并发访问性能的?个重要因素。从这个?度来说，锁对数据库??显得尤其重要，也更加复杂。
6.2、锁分类
1、从对数据操作的类型（读\写）分
① 读锁(共享锁)：针对同?份数据，多个读操作可以同时进??不会互相影响。
② 写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。
2、从对数据操作的粒度分
① 表锁
特点：偏向MyISAM存储引擎，开销?，加锁快；?死锁；锁定粒度?，发?锁冲突的概率最?,并发度最低。
② ?锁
特点：
1). 偏向InnoDB存储引擎，开销?，加锁慢；会出现死锁；锁定粒度最?，发?锁冲突的概率最低,并发度也最?。
2).InnoDB与MyISAM的最?不同有两点：?是?持事务（TRANSACTION）；?是采?了?级锁
3、从锁的用法分
【悲观锁】
我们使?悲观锁的话其实很简单(?动加?锁就?了)：select * from xxxx for update，在select 语句后边加了for update相当于加了排它锁(写锁)，加了写锁以后，其他事务就不能对它修改了！需要等待当前事务修改完之后才可以修改.也就是说，如果操作1使?select … for update，操作2就?法对该条记录修改了，即可避免更新丢失。
【乐观锁】
乐观锁不是数据库层?上的锁，需要?户?动去加的锁。?般我们在数据库表中添加?个版本字段version来实现，例如操作1和操作2在更新User表的时，执?语句如下：
update A set Name=lisi,version=version+1 where ID=#{id} and version=#{version}，此时即可避免更新丢失。
【间隙锁 GAP】
当我们?范围条件检索数据?不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合范围条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在 的记录，叫做“间隙(GAP)”。
InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁。
–例?：假如emp表中只有101条记录，其empid的值分别是1,2,…,100,101
Select * from emp where empid > 100 for update;
上?是?个范围查询，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid?于101(这些记录并不存在)的“间隙”加锁
InnoDB使?间隙锁的?的有2个：
为了防?幻读(上?也说了，Repeatable read隔离级别下再通过GAP锁即可避免了幻读)
满?恢复和复制的需要：MySQL的恢复机制要求在?个事务未提交前，其他并发事务不能插?满?其锁定条件的任何记录，也就是不允许出现幻读
【死锁】
1、产?原因
所谓死锁：是指两个或两个以上的进程在执?过程中,因争夺资源?造成的?种互相等待的现象,若?外?作?，它们都将?法推进下去.此时称系统处于死锁状态或系统产?了死锁，这些永远在互相等待的进程称为死锁进程。表级锁不会产?死锁.所以解决死锁主要还是针对于最常?的InnoDB。
死锁的关键在于：两个(或以上)的Session加锁的顺序不?致。
那么对应的解决死锁问题的关键就是：让不同的session加锁有次序
2、产生实例
需求：将投资的钱拆成?份随机分配给借款?。
起初业务程序思路是这样的：
投资?投资后，将?额随机分为?份，然后随机从借款?表??选?个，然后通过?条条select for
update 去更新借款?表??的余额等。
【例如】：两个?户同时投资，A?户?额随机分为2份，分给借款?1，2
B?户?额随机分为2份，分给借款?2，1，由于加锁的顺序不?样，死锁当然很快就出现了。
对于这个问题的改进很简单，直接把所有分配到的借款?直接?次锁住就?了。
Select * from xxx where id in (xx,xx,xx) for update
在in??的列表值mysql是会?动从?到?排序，加锁也是?条条从?到?加的锁
