MySql进阶_1

1、视图

1.1 介绍

是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自视图查询中使用的表。

保存了查询的SQL逻辑，不保存查询结果。

注：往视图内插入数据，数据存至相关表中。

MySQL中允许基于视图创建视图。

1.2 语法

1、创建：create （or replace) view 视图名[(列名)] as select语句；

2、查看创建视图的语句：show create view 视图名；

查看视图数据：select * from 视图名；

3、修改：create （or replace) view 视图名[(列名)] as select语句；

alter view 视图名 as select语句；

4、删除：drop view (if exits) 视图名；

检查选项(位于select语句之后) → 不加则表示无视当前语句条件(where)

with cascaded / local check option ：检查插入数据是否符合select中的条件

cascaded：强制级联(看本图与关联图标的关系)，会强制添加检查选项

local：依赖的图表加了检查选项才级联

1.3 更新

视图中的行与基础表的行必须存在一对一的关系。

不能有：

1、聚合函数或宽口函数

2、distinct

3、group by

4、having

5、union或union all

1.4 作用

1、简化用户对数据的理解和操作

把经常使用的查询定义为视图。

2、安全，通过视图用户只能查询和修改所见数据。

3、数据独立，帮助用户屏蔽真实表结构变化带来的影响。

2、存储过程

2.1 介绍

事先经过编译并保存在数据库中的一段SQL语句的集合。

即SQL语句层面的代码封装与使用。

2.2 特点

1、封装，复用

2、可接收、返回参数

3、减少网络交互，效率提升

2.3 语法

1、创建：create procedure 存储过程名称([参数列表])

begin

SQL语句；

end；

2、调用：call 存储过程名称([参数列表])；

3、查看(指定)：

select * from information_schema.ROUTINES where ROUTINE_SCHEMA = '数据库名称'；

查看具体存储过程：show create procedure；

4、删除：drop procedure 存储过程名；

注：delimiter $$ ： 自定义语句介绍符为$$

2.4 变量

2.4.1 系统变量

由服务器提供，分为全局变量(global)和会话变量(session—默认)。

1、查看系统变量：show [session / global] variables;

like '.....'；—模糊匹配

查看指定变量的值：select @@[session / global] 系统变量名；

2、设置系统变量：set [@@] [session /global] 系统变量名 = 值；

注：MySQL的服务器重启后，所设置的全局参数会失效，可在/etc/my.cnf中永久配置。

2.4.2 用户定义变量

不用提前声明，用的时候直接用 “@变量名” 即可，若无赋值则为null。

作用域：当前会话/连接

1、赋值：select / set @变量名 = / := 值；

select 字段名 into @变量名 from 表名；

2、查看：select @变量名；

2.4.3 局部变量

需要declare声明，作为存储过程的局部变量和输入函数。

作用域：begin......end之间

1、声明：declare 变量名 变量类型 [default.......]

2、赋值：set 变量名 = / := 值；

select 字段名 into 变量名 from 表名....；

2.5 if条件判断

语法：

if 条件1 then

............

else if 条件2 then

.............

else

...........

end if;

2.6 参数

1、类型：IN、OUT、INOUT

2、用法：创建存储过程时定义

create procedure 存储过程名([in/out/inout 参数 参数类型])； 注：逗号分隔多个参数

3、out显示时要用@参数名称调用

inout还要先设置值

2.7 case

语法1：

case 输入值

when 匹配值1， then 过程1

when 匹配值2， then 过程2

else 过程3

end case；

语法2：

case

when 条件1 then 过程1

when 条件2 then 过程2

else 过程3

end case；

2.8 循环

1、while(先判定，再循环)

while 条件 do

SQL逻辑语句

end while；

2、repeat(先循环，再判定) ：有条件的循环控制语句，满足条件时退出循环。

repeat

SQL语句

until 条件

end repeat；

3、loop —简单的循环

配合leave：退出循环

iterate：跳过当前循环剩下的语句，直接进入下一次循环。

语法：

[begin_label：] loop begin_label: : 定义一个名字（带冒号）

SQL逻辑语句

end loop[end_label]; end_label : 与上面名字一致（无冒号）

→leave label：退出指定标记的循环体

iterate label：直接进入下一次循环

2.9 游标cursor

定义：用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。

语法：

1、声明：declare 游标名 cursor for 查询语句； →将查询语句的结果封装到游标里面

2、打开：open 游标名；

3、获取游标记录：fetch 游标名 into 变量[，变量]；

4、关闭：close 游标名；

注：先声明普通变量，再声明游标。

2.10 条件处理程序(定义即可)

定义在流程控制结构执行过程中遇到问题时相应的处理步骤。

语法：declare handler_action handler for condition_value [，condition_value] ....statement；

(条件) (具体的SQL逻辑)

备注：handler_action

可选：continue：继续执行当前程序

exit：终止执行当前程序

condition_value

可选：SQLSTATE 'sqlstate_value' : 状态码，具体值

SQLWARNING ： 所有以01开头的SQLSTATE代码的简写

NOT FOUND ： 02

SQLEXCEPTION : 除去SQLWARNING和NOT FOUND的其他SQLSTATE代码的简写

3、存储函数

(一般用存储过程可替代)

定义：有返回值的存储过程，且参数只能是in类型。

语法：

create function 存储函数名称 ([参数列表])

return type[characteristic........]

begin

SQL语句

return........；

end；

characteristic选项：

deterministic：相同的输入参数总是产生相同的结果。

NO SQL ：不包含SQL语句

READS SQL DATA ：包含读取数据的语句，但不包含写入数据的语句。

4、触发器

4.1 定义

指在增删改之前/之后，触发并执行触发器中定义的SQL语句集合。

4.2 作用

保证数据的完整性、日志文件、数据校验等操作。

4.3 类型

insert型： NEW：将要/已经新增的数据

update型： OLD：修改前的数据 NEW：修改后/将要修改的数据

delete型： OLD：已经/将要修改的数据

4.4 语法

1、创建：

create trigger 触发器名称

before / after insert / update / delete

on 表名 for each row →行级触发器

begin (影响多少行列，触发多少次)

SQL语句

end；

2、查看：show triggers；

3、删除：drop trigger [schema_name] 触发器名称；

(数据库名称(默认当前))

5、锁

定义：计算机协调多个进程或线程并发访问某一资源的机制，保证数据并发访问的一致性、有效性。

分类：

全局锁：锁定数据库中的所有表。

表锁：每次操作锁住整张表。

行锁：每次操作锁住对应的行数据。

5.1 全局锁

1、定义：对整个数据库实例加锁，加锁后整个实例处于只读状态，写语句都将被阻塞。

2、应用场景：全库的逻辑备份，对所有表进行锁定，从而获取一致性视图，保证数据的完整性。

3、语法：

加锁：flush table with read lock； (MySQL命令行运行)

执行备份：mysqldump -uroot -p密码 数据库名称 > (带路径的)文件； (window命令行执行)

解锁：unlock tables；

4、特点：

1、若在主库上备份，则其期间无法更新，业务基本上停摆。

2、若在从库上备份，则其期间不能执行主库同步过来的二进制文件，会导致主从延迟。

注：InnoDB引擎中，可在备份时加上 --single-transaction 参数

实现动态备份——完成不加锁的一致性数据备份。

⇒mysqldump --single-transaction -uroot -p密码 数据库名 > 文件

5.2 表级锁

定义：每次操作锁住整张表。

锁定粒度大，发生锁冲突概率最高，并发度最低。

分类：表锁

元数据锁(MDL)

意向锁

5.2.1 表锁

1、分类：

共享锁(read lock)：读锁，自己与别人均可读不可写

独占锁(write lock)：写锁，仅有自己可读可写

2、语法

加锁：lock tables 表名 ....... read / write；

解锁：unlock tables / 客户端断开连接；

5.2.2 元数据锁(meta data lock MDL)

1、定义：

MDL加锁过程是系统自动控制的，无须显式使用，在访问一张表的时候会自动加上。

(元数据 ≈ 表结构)

2、作用

维护表元数据的数据一致性

在表上有活动事务时，不可对元数据进行写入操作

避免DML与DDL冲突，保证读写的正确性

3、用法

对一张表数据进行增删改查时，自动加MDL读锁(共享)

对一张表结构进行变更操作时，自动加MDL写锁(排他)

注：元数据锁的共享锁又分共享读锁和共享写锁，读锁之间兼容（即对数据的读、写均无影响）

读锁与写锁不兼容

5.2.3 意向锁

1、定义

为了避免DML在执行时，加的行锁与表锁产生冲突

意向锁使得表锁不用检查每行数据是否加锁，减少表锁的检查。

2、分类

意向共享锁(IS)：select...........lock in share mode；

意向排他锁(IX)：增删改查............for update；

注：意向锁之间不互斥

意向共享锁(IS)与表锁共享锁兼容，与表锁排他锁互斥

意向排他锁(IX)与表锁互斥

5.3 行级锁

定义：应用于InnoDB中，MyISAM不支持

每次操作锁住对应的行数据

锁定粒度最小，发生锁冲突的概率最低，并发度最高

注：行锁通过对索引上的索引项加锁实现，不是对记录加锁。

RR隔离情况下，InnoDB用next-key锁进行搜索和索引扫描。

5.3.1 行锁

1、定义

锁定单个行记录的锁，防止其他记录对此行进行update和delete

在RC、RR隔离级别下都支持

2、分类

1、共享锁(S)：允许一个事务去读一行

阻止其他事务获得相同数据集的排他锁。

2、排他锁(X)：允许获得排他锁的事务更新数据

阻止其他事务获得相同数据集的共享锁和排他锁

注：仅有共享锁之间兼容

3、使用

增删改 ： 自动加排他锁

查 ： 不加任何锁

select.....lock in share mode ：手动加共享锁

..................for update : 手动加排他锁

注：针对索引加的锁，若不走索引，行锁自动升级为表锁，对所有记录加锁

5.3.2 间隙锁

1、定义

锁定索引记录之前的间隙(不含该记录)，防止其他事务在这个间隙进行insert，产生幻读。

在RR隔离级别支持

2、目的

防止其他事务插入间隙

⇒间隙锁可以共存，不同事务可以对同一间隙加间隙锁

3、使用

1、索引上等值查询(唯一索引)：对不存在的记录加锁，优化为间隙锁

2、 (普通索引)：直至记录开始不匹配，退化为间隙锁，对该记录前后间隙加锁，符合要求的加行锁

3、 范围查询(唯一索引)：会访问到不满足条件的第一个值为止

5.3.3 临键锁

定义：行锁与间隙锁的组合

同时锁住数据及其前面的间隙

在RR隔离级别支持