MySQL基础
SQL语言
DDL:
描述：data definition language 数据操纵语言,主要负责对数据库的操作、对表操作的查询、创建表、修改表和删除表
数据库语法
查询所有数据库
show databases;查询当前数据库
select databse();创建数据库
create databases [IF NOT EXISTS] 数据库名字 [DEFAULT CHARSET 字符集][COLLATE 排序规则];[DEFAULT CHARSET 字符集]:
MySQL 8.0 之前：默认字符集为
latin1，utf8字符集指向的是utf8mb3。MySQL 8.0 及以后：默认字符集改为
utf8mb4，以避免乱码问题
utf8_general_ci：不区分大小写
utf8_bin：区分大小写，按照字符的二进制值进行排序
utf8_unicode_ci：基于 Unicode 的排序规则，不区分大小写，支持更广泛的语言267[COLLATE 排序规则]:
**
utf8_general_ci**基于 UTF-8 的排序规则，不区分大小写**
utf8_bin**基于二进制的排序规则，区分大小写**
utf8_unicode_ci**基于 Unicode 的排序规则，不区分大小写，支持更广泛的语言**
utf8mb4_general_ci**默认的排序规则 for utf8mb4 字符集，不区分大小写**
utf8mb4_unicode_ci**基于 Unicode Collation Algorithm (UCA) 的排序规则，不区分大小写**
utf8mb4_bin**基于二进制的排序规则，区分大小写
删除数据库
drop database [IF EXISTS] 数据库名称;使用数据库
use 数据库名称;表查询语法
查询当前数据库中的所有表
show tables;查询表结构
desc 表名;查询指定的建表语句
show create table 表名;创建表
创建表语句
create table 表名(
字段1 字段1类型 comment'对字段1的注释' 约束1... 约束n,#comment'对表的注释';
字段2 字段2类型 comment'对字段2的注释' 约束1... 约束n,
........ ,
字段n 字段n类型 comment'对字段n的注释' 约束1... 约束n,
primary key(字段i)
foreign key(字段i) references 主表(主表字段名)
) engine=InnoDB#引擎选择
#注释：设置字段i为主键和外键时可以同时设置多个字段，也可以不设置约束注释:约束类型： 1.非空约束 not null 2.唯一约束 unique 3.主键约束(非空且唯一) primary key 4.默认约束(不设置值给默认值) default 5.检查约束(满足某一条件) check 6.外键约束(保证数据的完整一致性) foreign key
修改表
添加字段
alter table 表名 add 字段名 字段类型 comment'注释' 约束;修改数据类型
alter table 表名 modify 字段名 新字段类型;修改字段名和字段类型
alter table 表名 change 旧字段名 新字段名 字段类型 comment'注释' 约束;删除字段
alter table 表名 drop 字段名;修改表名
alter table 表名 rename to 新表名;修改现有数据库的排序规则
alter database mydatabase collate utf8_general_ci;修改表排序规则
alter table mytable convert to character set utf8 collate utf8_general_ci;删除表
删除表
drop table [IF EXISTS] 表名;删除指定表并重新创建该表
truncate table 表名;DML
描述:data manipulation language 数据操纵语言,主要负责对数据的增加、删除、修改
插入语句
insert into 表名（表字段名1,表字段名2,...） values (数据1.1,数据1.2,...),(数据2.1,数据2.2,...);更新语句
update table set 字段名1=值1,字段名2=值2....[where 条件]删除语句
delete from 表名 [where 条件]DQL
描述:data quary language 数据查询语言，主要负责对数据的查询（最被频繁使用的一个）
查询语句
select (表字段名1,表字段名2,....)(*代表查询全部)
from 表名
where 条件
group by 分组字段列表
having 条件2
order by 表字段名i [asc(升序,也是默认排序)|desc(降序)]
limit 数字DCL
描述:data control language 数据控制语音，主要负责用户权限控制
查询用户
use mysql;
select * from user;创建用户
create user '用户名'@'主机名' identified by '密码';修改用户密码
alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';删除用户
drop user '用户名'@'主机名';注意：
主机名可以使用%通配 这类SQL开发人员操作的比较少，主要是DBA(Database Administrator 数据库管理员) 使用。
函数
字符串函数
将多个字符串拼接为一个字符串
concat(str1,str2,...,strn)将全部字符串转换为小写
lower(str)将全部字符串转换为大写
upper(str)左填充,用字符串pad对str的左边进行填充，达到n个字符的长度
lpad(str,n,pad)右填充,用字符串pad对str的右边进行填充，达到n个字符的长度
rpad(str,n,pad)去掉字符串头部和尾部的空格
trim(str)返回字符串str从start位置到len个长度单位的字符
substring(str,start,len)数值函数
向上取整
ceil(x)向下取整
floor(x)返回x/y的模
mod(x/y)返回0~1内的随机数
rand()求参数x四舍五入的值，保留y位小数
round(x,y)日期函数
返回当前日期
curdate()返回当前时间
curtime()返回当前的日期和时间
now()获取指定date的年份
year(date)获取指定date的月份
month(date)获取指定date的日期
day(date)返回一个日期/时间值加上一个时间间隔expr后的时间值
date_add(date,interval expr type)返回起始时间date1和结束时间date2之间的天数
datediff(date1,date2)流程函数
如果value为true，则返回t否则返回f
if(value,t,f)如果value1不为空,返回value1,否则返回value2
ifnull(value1,value2)如果val1为true，返回res1，...否则返回default默认值
case with [val1] then [res1] ... else [default] end如果expr的值等于val1，返回res1，...否则返回default默认值
case [expr] when [val1] then [res1] ...else [default] end约束
约束介绍
约束是作用与表中字段上的规则,用于限制存储在表中的数据(保证数据库中数据的正确、有效性和完整性)
约束语法
添加外键
create table 表名(
字段名 数据类型,
...
[constraint] [外键名称] foreign key (外键字段名) references 主表(主表列名)
);alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(主表列名)删除外键
alter table 表名 drop foreign key 外键名称删除/更新行为
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(主表列名) on update [cascade...] on delete [cascade] [restrict] [set null] [set default];行为:
no action当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(与restrict一致)
restrict当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(与no action一致)
cascade当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录
set null当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键的值为null(外键允许null)
set default父表有变更时，子表将外键列设置成一个默认的值(Innodb不支持)
约束分类
1.not null 限制该字段的数据不能为null
2.unique 保证该字段的所有数据都是唯一、不重复的
3.primary key 主键一行数据的唯一标识，要求非空且唯一
4.default 保存数据时，如果未指定该字段的值，则采用默认值
5.check 保证字段值满足某个条件
6.foreign key 用来让两张表的数据之间建立连接，保证数据的一致性和完整性
多表查询
多表关系
一对多:在多的一方设置外键，关联一方的主键
多对多:建立中间表，中间表包含两个外键，关联两张表的主键
一对一：用于表结构的拆分，在其中任何一方设置外键(unique)，关联另一方的主键
多表查询
显示内连接
select ... from 表A inner join 表B on 条件显示外连接
select ... from 表A,表B where 条件左外连接
select ... from 表A left join 表B on 条件相当于表A的所有数据包含二者交集
右外连接
select ... from 表A right join 表B on 条件相当于表B的所有数据包含二者交集
自连接
select ... from 表A 别名1,表A 别名2 where 条件联合查询
select 字段列表 from 表A ...
union [all]
select 字段列表 from 表B...;注意：
联合查询就是把多次查询结果直接合并(上下拼接)，形成一个新的查询结果集
联合查询的多张表的列数必须保持一致，字段类型也必须保持一致
子查询
标量子查询 ：子查询返回的结果是单个值 常用操作符：= <> > >= < <=
列子查询：子查询返回的结果是一列(可以是多行) 常用操作符：in not in some all
行子查询：子查询返回的结果是一行(可以是多列) 常用操作符：= <> in not in
表子查询：子查询返回的结果是多行多列 常用操作符：in
事务
事务介绍
一组同时成功或失败的操作
注意:默认的MySQL的事务是自动提交的，也就是说执行一条DML语句，MySQL会立刻隐形的提交事务
事务语法
查看事务提交方式
select @@autocommit;设置事务提交方式
set @@autocommit=0;#0默认不提交事务,需要手动提交开启事务
start transaction | begin提交事务
commit;回滚事务
rollback;查看事务隔离级别
select @@TRANSACTION_ISOLATION;设置事务隔离级别
set [session|global] transaction isolation level [read uncommited | read commited | repeatable read | serializable]事务四大特性:
原子性(Automicity) : 事务是不可分割的最小操作单元，要么全部操作成功,要么全部失效
一致性(Consistency) : 事务完成时，必须使所有的数据都保持一致状态(分布式事务就是为了解决这个问题)
隔离性(Isolation) : 数据库提供的隔离机制，保证事务在不受外部并发操作的影响的环境下独立运行
持久性(Durability) : 事务一旦提交或回滚，它对数据库中的数据的改变就是永久的
注意:事务的四大特性也是事务的基本特性，任何事务都需要满足
事务并发问题:
脏读 : 一个事务读到另一个事务还没有提交的事务(读已提交-Read commited-时解决)
不可重复读 : 一个事务先后读取同一条记录，单两次读取的数据不同 (可重复读-Repeatable Read-时解决)
幻读 : 一个事务按条件查询数据的时候，没有对应的数据行，但是在插入数据的时候，又发现这行数据存在了，如同“幻影”(可序列化-Serializable-时解决)
事务隔离级别
1.读未提交 --Read Uncommited
2.读已提交 --Read Commited --解决了脏读
3.可重复读(默认级别) --Repeatable Read --解决了脏读、不可重复读
4.可序列化 --Serilizable --解决了脏读、不可重复读、幻读
存储引擎
Mysql体系结构
连接层
最上层是一些客户端的链接服务，主要完成一些类似于处理链接、授权认证以及相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
服务层
第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如 过程、函数等。
引擎层
存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API的存储和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需求，来选取合适的存储引擎。
存储层
主要是将数据存储在文件系统之上，并完成与存储引擎的交互。
各个存储引擎的特点
InnoDB
介绍
InnoDB是一种兼顾高可用和高性能的通用存储引擎，在MySQL5.5之后，InnoDB是MySQL的默认存储引擎
特点
DML操作遵循ACID模型，支持事务；
行级锁，提高并发访问性能；
支持外键foreign key约束，保证数据的完整性和准确性；
文件
xxx.ibd: xxx代表的是表名，InnoDB引擎的每张表都会对应这样的一个表空间文件，存储该表的表结构(frm、sdi)、数据和索引。
参数：innodb_file_per_table
frm：MySQL数据库中用于存储表结构的二进制文件，每个表都会生成一个frm文件，该文件包含了表的结构定义，如列名、列类型、索引等信息，frm文件是MySQL数据库文件格式的一种，与MYD（存储数据）和MYI（存储索引）文件一起，构成了MySQL数据库的存储体系
MyISAM
介绍
MyISAM是MySQL早期的默认存储引擎
特点
不支持事务；
不支持外键；
支持表锁，不支持行锁；
访问速度快；
文件
xxx.sdi：存储表结构信息
xxx.MYD：存储数据
xxx.MYI：存储索引
Memory
介绍
Memory引擎的表数据存储在内存中，由于受到硬件问题、断电问题的影响，只能即将这些表作为临时表或缓存使用。
特点
内存存放；
hash索引(默认);
文件
xxx.sdi：存储表结构信息
索引
索引介绍
索引是帮助MySQL高效获取数据的数据结构
索引优缺点
优点：提高数据的检索效率，降低数据库IO的成本；通过索引对数据进行排序，降低数据排序的成本，降低CPU的消耗
缺点：索引也占用空间；索引降低了更新表的速度，如果对表进行insert、update、delete的时候效率会降低
索引语法
创建索引
create [ unique | fulltext | spatial ] index 'index_name' on 'table_name'('index_col_name',...);注释:
fulltext的底层是倒排索引，在MySQL的FULLTEXT索引中，当对文本数据进行索引时，会进行分词处理，将文本分解成一系列的单词或短语，并为每个单词或短语建立索引，此时可以进行全文检索。
创建前缀索引
#n表示前缀长度
create index idx_xxxx on table_name(column(n));确定前缀长度（查询索引选择性）
#索引选择性越高查询效率越高,i自定
select count(distinct substring(字段,i,i+n))/count(*) from user;查看索引
show index from 'table_name';删除索引
drop index 'index_name' on 'table_name';索引结构
| 索引结构 | 描述 |
|---|---|
| B+Tree索引 | 最常见的索引类型，大部分引擎都支持B+树索引 |
| Hash索引 | 底层数据结构使用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询 |
| R-tree(空间索引) | 空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少 |
| full-text(全文索引) | 是一种通过建立倒排索引，快速匹配文档的方式。类似于Lucence，Solr，ES |
| 索引 | InnoDB | MyISAM | Memory |
|---|---|---|---|
| B+Tree索引 | 支持 | 支持 | 支持 |
| Hash索引 | 不支持 | 不支持 | 支持 |
| R-tree(空间索引) | 不支持 | 支持 | 不支持 |
| full-text(全文索引) | 5.6版本之后支持 | 支持 | 不支持 |
B+Tree索引特点：高效的数据检索能力、优化的磁盘I/O操作、支持多种索引类型、叶子节点形成有序链表等...
Hash索引特点：只能用于对等比较(=，in)、无法利用索引完成排序操作、查询效率通常高于B+Tree索引
为什么InnoDB存储引擎选择B+树？
1.相较于二叉树，层级更少，搜索效率高；
2.对于B树，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要保存同样的数据，B树的高度比B+树更高，导致IO次数增加，性能降低；
3.相对于Hash索引，B+树支持范围匹配及排序操作；
示例图：
索引分类
| 分类 | 含义 | 特点 | 关键字 |
|---|---|---|---|
| 主键索引 | 针对于表中主键创建的索引 | 默认自动创建，只能有一个 | primary |
| 唯一索引 | 避免同一个表中某个数据列中的值重复 | 可以有多个 | unique |
| 常规索引 | 快速定位特定数据 | 可以有多个 | |
| 全文索引 | 全文索引查找的是文本中的关键词，而不是比较索引中的值 | 可以有多个 | fulltext |
在InnoDB中，根据索引的存储形式，又可以分为以下两种：
| 分类 | 含义 | 特点 |
|---|---|---|
| 聚集索引（Clustered Index） | 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据 | 必须有，而且只有一个 |
| 二级索引（Secondary Index） | 将数据与索引索引分开存储，索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
聚集索引选取规则：
如果存在主键，主键就是聚集索引
如果不存在主键索引，将使用第一个唯一索引作为聚集索引
如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个
rowid作为隐藏的聚集索引
注意：一页中可以存储16行大小为1KB的行数据，而一行数据可以存储大约1171个指针，所以对于高度为n的B+树可以存储
1171* . . .*1171(共计n个1171)*16行数据
索引法则
最左前缀法则：如果索引了多列（联合索引），需遵守最左前缀法则，最左前缀法则指的是查询从索引最左列开始，并且不跳过索引中的列。如果跳过某一列，索引将部分失效（后面的字段失效）
范围查询：联合索引中，出现范围查询（>,<）,范围查询右侧的列索引失效（后面的索引失效）
索引列运算：不要在索引列上进行运算操作，索引将失效
字符串不加引号：字符串类型字段使用时，不加引号，索引将失效
模糊匹配：如果仅仅是尾部模糊匹配，索引不会失效。如果头部模糊匹配，索引失效（%后面的索引都失效）
or连接的条件：用or分割开的条件，如果or前的条件中的列有索引，而后面的列没有索引，那么涉及的索引都不会被用到（前有后没有，都失效）
数据分布影响：如果MySQL评估使用索引会更慢，就不使用索引
索引使用
添加SQL提示
use index(要求使用指定索引):
select * from user use index(idex_user_pro) where profession = '软件工程';ignore index(要求忽略指定索引):
select * from user ignore index(idex_user_pro) where profession = '软件工程';force index(要求强制使用指定索引):
select * from user force index(idex_user_pro) where profession = '软件工程';覆盖索引：尽量使用覆盖索引（要返回的列能够在索引中全部找到），减少使用select *
前缀索引：当字段类型为字符串(var,text等)，有时候需要索引很长的字符串，这会让索引的长度变的很大，查询时浪费大量的磁盘IO，影响查询效率。此时可以只存一部分前缀，节省空间，提高效率
联合索引：一个索引包含了多个列，如果存在多个查询条件，建议建立联合索引而非单列索引
索引设计原则
1.针对数据量较大，而且查询比较频繁的表建立索引
2.这对常为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引
3.尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
4.如果是字符串类型的索引，字段长度较长，可以针对于字段的特点，建立前缀索引
5.尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以索引覆盖，节省存储空间，避免回表，提高查询效率
6.控制索引的数量，避免因为索引数量过多导致的增删改的效率下降
7.如果索引列不能存储null值，请使用not null约束，当优化器知道每列是否包含null值的时候能够更好的确定索引，提高查询效率
SQL优化
SQL优化语法
查询当前数据库的insert、update、delete、select 访问频率
show global status like 'Com_______';开启慢查询日志开关（默认不开启）
slow_query_log=1设置慢查询日志的时间为n秒，SQL语句执行时间超过n秒就会被视为慢查询并记录
log_query_time=n注意：日志文件被记录在/var/lib/mysql/localhost-slow.log下
查询任意指标的状态
show variables like 'example'; #查询名为example的指标查询当前mysql是否支持profile操作
select @@have_profiling;开启对profiling的操作
set [global | session] profiling=1;查询每一条SQL的耗时基本情况
show profiles;查询指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;查询制定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;EXPLAIN或者DESC命令获取MySQL如何执行select语句的信息
explain select 字段列表 from 表名 where 条件;注意：查询的结果不包含
select后的内容，只包含这条语句本身的查询信息字段含义：
idselect查询的序列号（id相同，执行顺序从上向下；id不同，值越大，越先执行）
select_type表示select的类型，常见取值有SIMPLE（简单表，不包含任何表链接或子查询）、PRIMARY（主查询（包含一个或多个子查询的查询），即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（select/where后面 包含了子查询）等
type表示连接类型，性能由好到差的连接类型为null - system- const - eq_ref - ref - range - index - all
possible_keys显示可能应用在这张表上的索引，一个或多个
Key实际使用的索引，如果为null就是没有使用
Key_len索引使用的字节数，为索引字段的最大可能长度，并非实际使用长度，在不损失精度的前提下越短越好
rowsMySQL认为必须要查询的行数，InnoDB中是一个估计值
filtered表示返回结果的行数占需读取行数的百分比，filtered的值越大越好
insert优化
小批量插入数据：1.使用批量插入的方式（insert into (...),(...)）
2.手动提交事事务
3.按照主键顺序插入（性能高于乱序插入）
大批量插入数据：使用MySQL数据库提供的load命令进行插入
#客户端连接服务器时,加上参数--local-infile
mysql --local-infile -u root -p
#设置全局参数local-infile为1,开启从本地加载文件导入数据的开关
set global local_infile=1;
#执行load指令将准备好的数据加载到表结构中
load data local infile '/position/sql1.log' into table 'table_name' fields terminated by ',' lines terminated by '\n';#fields后面的是数据分隔符,lines后面的是行分割符tips：此时数据的结构应该是
data1,data2,data3, . . .data(n)
主键优化
数据组织方式：在InnoDB引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表
页分裂：页可以为空，也可以填充一半，也可以填充100%，每个页包含了2-N行数据（如果一行数据量多且大，可能会行溢出），根据主键排列
页合并：
1.当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用
2.当页删除的记录达到MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用
主键设计原则：
1.满足业务需求的情况如下，尽量降低主键的长度
2.插入数据时，尽量选择顺序插入，选择使用auto_increment自增主键
3.尽量不要使用UUID作为主键或者是其他自然主键，如身份证号
4.业务操作时，避免对主键的修改
order by优化
Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都加FileSort排序
Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高
使用原则：
1.根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
2.尽量使用覆盖索引
3.多字段排序，一个升序一个降序，此时需要注意联合索引在创建时期的规则（ASC/DESC）
4.如果不可避免的出现filesort，大量数据排序时，可以适当增大排序缓冲区的大小sort_buffer_size（默认256K）
group by优化
在分组操作时，可以通过索引来提高效率
分组操作时，索引的使用也是满足最左前缀法则的
limit优化
一般分页查询时，通过创建覆盖索引能够较好的提高性能，可以通过覆盖索引加子查询形式进行优化
count优化
MyISAM执行count(*)的速度很快，但是InnoDB执行的效率很低，需要自己计数，所以我们可以自己维护一个计数器计数即可
count的几种用法：
count(主键)：需要遍历整张表拿取id返回给服务层，服务层拿到主键后累加
count(字段)：没有not null约束会遍历后返回服务层判断是否为null，非null才累加；有约束同上
count(1)：直接遍历表但是不取值，直接将每层放入一个1然后累加
count(*)：InnoDB做了优化，不取值，直接在服务器进行累加
按照效率排序：count(字段) < count(主键) < count(1) = count(*)，尽量使用count(*)
update优化
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则行锁会升级为表锁，查询条件应尽量满足索引
视图
视图介绍
视图是一种虚拟存在的表，视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图的时候动态生成的（通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就是在SQL查询语句上，视图的本质就是存储一条SQL语句，不会提高查询效率）
tips：由于视图并不能提高效率而且存在一下bug，使用几乎大部分的公司都禁用视图，所以本节了解即可
视图语法
创建视图
create [or repalce] view 视图名称[(列名列表)] as select语句 [with [cascaded | local] check option];tips：当使用with check option子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如
插入、更新、删除，以使其符合视图的定义。MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项：cascaded和local，默认值为cascaded，这意味着它会检查所有依赖视图的规则；而local则只检查当前视图的规则。
查看创建视图语句
show create view 视图名称;查看视图数据
select * from 视图名称;修改视图
alter view 视图名称[(列名列表)] as select语句 [with [cascaded | local] check option];删除视图
drop view [if exists] 视图名称 [,视图名称 ...];视图的更新
视图中如果包含以下任意一项，则该视图不可更新：
1.聚合函数或窗口函数（sum( )、min( )、max( )、count( )等）
2.distinct
3.group by
4.having
5.union或者union all
视图的作用
简单：
视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件
安全：
数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的位置
数据独立：
视图可以帮助用户屏蔽真实表结构变化带来的影响
存储过程
存储过程介绍
存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的
存储过程思想上很简单，就是数据库SQL语言层面的代码的封装和重用
存储过程语法
创建
create procedure 存储过程名称([in|out|inout 参数名 参数类型])
begin
--SQL语句
end;调用
call 名称([参数]);查看
select * from information_schema.routines where routine_schema='xxx';#查询指定数据库的存储过程及状态信息
show create procedure 存储过程名称;#查询某个存储过程的定义删除
drop procedure [if exists] 存储过程名称;tips：在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter 指定SQL语句的结束符
查看所有系统变量
show [session | global] variables;通过like模糊匹配方式查找变量
show [session | global] variables like '...';查看指定变量的值
select @@[session | global] 系统变量名;设置系统变量
set [session | global] 系统变量名=值;
set @@[session | global] 系统变量名=值;tips： 如果没有指定session/global，默认是session
mysql服务重新启动后，所设置的全局参数会失效，要想不失效，可以在/etc/my.cnf中配置
赋值自定义变量
set @var_name=expr [,@var_name = expr]...;
set @var_name:=expr [,@var_name = expr]...;select @var_name:=expr[,@var_name= expr]...;
select 字段名 into @var_name from 表名;使用自定义变量
select @var_name;声明局部变量
declare 变量名 变量类型 [default ...];局部变量赋值
set 变量名=值;
set 变量名:=值;
select 字段名 into 变量名 from 表名...;if语法
if 条件1 then
...
elseif 条件2 then
...
else
...
end if;case语法
case case_value
when when_value1 then statement_list1
[when when_value2 then statement_list2]...
[else statement_list]
end case;case
when search_condition1 then statement_list1
[when search_condition2 then statement_list2]...
[else statement_list]
end case;while语法
while 条件 do
SQL逻辑...
end while;repeat语法
repeat
SQL逻辑
until 条件
end repeat;loop语法
[begin_label:] loop #相当于goto语句
SQL逻辑
end loop [end_label];
level label;#退出指定标记的循环体(break)
iterate lable;#直接进入下一次循环(continue)声明cursor
declare 游标名称 cursor for 查询语句;打开cursor
open 游标名称;获取游标记录
fetch 游标名称 into 变量[,变量];关闭游标
close 游标名称;条件处理程序
create function 存储函数名称([参数列表])
returns type [characteristic...]
begin
--SQL语句
return...;
end;characterstic说明：
deterministic：相同的输入参数总是产生相同的输出结果
no sql：不包含sql语句
reads sql data：包含读取数据的语句，但不包含写入数据的语句
创建触发器
create trigger trigger_name
before/after insert/update/delete
on 'tb_name' for each row #行级触发器
begin
触发器逻辑;
end;查看触发器
show triggers;删除触发器
drop trigger [schema_name.]trigger_name; #如果没有指定schema_name,默认为当前数据库存储过程特点
1.封装、复用
2.可以接受参数，也可以返回数据
3.减少网络交互，效率提升
存储过程变量
系统变量是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（global）、会话变量（session）
用户定义变量是用户根据需要自定义的变量，用户变量不用提前声明，在用的时候直接用"@变量名"使用就可以。其作用域为当前连接
局部变量是根据需要定义的局部生效的变量，访问之前，需要declare声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的begin...end块
存储过程参数
| 类型 | 含义 | 备注 |
|---|---|---|
| IN | 该类参数作为输入 | 默认 |
| OUT | 该类参数作为输出 | |
| INOUT | 该类参数同时作为输入输出 |
存储过程游标
游标是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理，游标的使用包括游标的声明、open、fetch和close
触发器
触发器是与表有关的数据库对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句的集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作
使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发
注意：有些版本的数据库的触发器存在一些bug，可能在指定情况下触发器不工作，这使得触发器几乎不会被使用
锁
锁介绍
锁是计算机协调多个进程或线程并发访问某一临界资源的机制，在数据库中，除了传统的计算机资源（CPU、RAM、I/O）的争用以外，数据也是一种特通许多用户共享的资源。保证数据在并发访问下的一致性和有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来看，锁对数据库尤为重要，也更加复杂。
锁分类
MySQL中锁按照粒度分为三类：全局锁、表级锁、行级锁
全局锁
介绍
全局锁就是对整个数据库实例加锁，上锁后整个数据库示例（对于任意的表）都处于只读状态，后续的DML的写语句、DDL语句，已经更新操作的事务提交语句都将被阻塞。
提示：全库的逻辑备份就是加的全局锁
特点
数据库中加全局锁，是一个非常重量级的操作，存在一下问题：
1.如果在主库上备份，那么在备份期间都不能执行更新，业务基本上全部停止
2.如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟
表级锁
介绍
表级锁，每次操作锁住整张表。锁定的粒度大，发生冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。
分类
表级锁主要分一下三类：表锁、元数据锁、意向锁
表锁
1.表共享读锁（read lock）
2.表独占写锁（write lock）
兼容性：
| 当前锁类型 | 读锁（S） | 写锁（X） |
|---|---|---|
| 读锁（S） | 兼容 | 不兼容 |
| 写锁（X） | 不兼容 | 不兼容 |
语法
加锁
lock tables 表名 ... read/write 释放锁
unlock tables/客户端断开连接元数据锁（ meta data lock, MDL）
介绍
元数据锁（MDL）加锁过程是系统自动控制，无需显示启用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性
注意：在MySQL5.5中引入了MDL，当对一张表进行增删查改的时候，加MDL读锁(共享)，当对表结构进行更改的时候，加MDL写锁（排他）
| 对应SQL | 锁类型 | 说明 |
|---|---|---|
| lock tables xxx read/write | shared_read_only/shared_no_read_write | |
| select、select ... lock in share mode | shared_read | 与shared_read、shared_write兼容,与exclusive互斥 |
| insert、update、delete、select ... for update | shared_write | 与shared_read、shared_write兼容,与exclusive互斥 |
| alter table ... | exclusive | 与其他的DML都互斥 |
查看元数据锁
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;意向锁
介绍
为了避免DML在执行是，加的行锁与表锁的冲突,在InnoDB中引入了意向锁,使得表锁不用检查每行数据是否加锁,使用意向锁来减少了表锁的检查
分类
1.意向共享锁（IS）：由语句select ... lock in share mode 添加
2.意向排他锁（IX）：由insert、update、delete、select ... for update 添加
兼容性：(意向锁之间不会互斥)
| 锁模式（表级） | IS | S | IX | X |
|---|---|---|---|---|
| IS | 兼容 | 兼容 | 兼容 | 不兼容 |
| S | 兼容 | 兼容 | 不兼容 | 不兼容 |
| IX | 兼容 | 不兼容 | 兼容 | 不兼容 |
| X | 不兼容 | 不兼容 | 不兼容 | 不兼容 |
查看意向锁
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;行级锁
介绍
行级锁每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中
InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。行级锁主要分为三类：行锁、间隙锁和临键锁
行锁（Record Lock）
介绍
锁定单个行记录的锁，防止其他事务对此进行update和delete。在RC、RR隔离级别下都支持
分类
InnoDB实现了两种类型的行锁：
1.共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁
2.排他锁（X）：允许获取排他锁的事务更新数据
兼容性：
| 锁类型（行级） | S（共享锁） | X（排他锁） |
|---|---|---|
| S（共享锁） | 兼容 | 冲突 |
| X（排他锁） | 冲突 | 冲突 |
间隙锁（Gap Lock）
介绍
锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持
临键锁（Next-key Lock）
介绍
行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持
间隙锁和临键锁的使用时机
默认情况下，InnoDB在RR事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读。
1.索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁
2.索引上的等值查询（普通索引），向右遍历最后一个值不满足查询需求时，next-key lock退化为gap lock
3.索引上的范围查询（唯一索引）-- 会访问到不满足条件的第一个值为止
注意：间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一个间隙上采用间隙锁。
InnoDB引擎
逻辑存储结构
表空间（ibd文件）：一个mysql示例可以对应多个表空间，用于存储记录、索引等数据。
段：分为数据段（Leaf node segment）、索引段（Non-leaf node segment）、回滚段（Rollback segment），InnoDB是索引组织表，数据段就是B+树的叶子节点，索引段即为B+树的非叶子节点。段用来管理多个区（Extent）
区：表空间的单元结构，每个区的大小为1M。默认状态下，InnoDB存储引擎页大小为16K，即一个区中一共有64个连续的页
页：是InnoDB存储引擎磁盘管理的最小单元，每个页的大小默认为16KB。为了保证页的连续性，InnoDB存储引擎每次从磁盘申请4-5个区
行：InnoDB存储引擎数据是按行进行存放的
Trx_id（行内）：每次对某条记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列
Roll_pointer（行内）：每次对某条索引记录进行改动时，都会把旧的版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息
架构
MySQL5.5版本开始，默认使用InnoDB存储引擎，它擅长事务处理，具有崩溃恢复特性（这一特性基于write ahead log，WAL机制实现），在日常开发中使用非常广泛。下面是InnoDB架构图，左侧为内存结构，右侧为磁盘结构
