MySQL 梳理学习

 参考视频：

黑马程序员 MySQL数据库入门到精通，从mysql安装到mysql高级、mysql优化全囊括_哔哩哔哩_bilibili

uuid 去 - 

MySQL replace uuid REPLACE(UUID(), ‘-‘, ‘‘)重复问题，多种解决方案_链诸葛的博客-CSDN博客_mysql replace uuid

Mysql官方文档：MySQL :: MySQL Documentation

干了这么多年，忽然发现Mysql的东西都没有梳理过，都是用到什么找什么，零碎一地，惭愧。

Mysql 和 Java 的类型映射

常用到的mysql与java类型对照表-CSDN博客

DDL

         数据库操作

查询所有数据库	show databases;
查询当前数据库	select database();
创建数据库	create database [if not exists] 数据库名 [ default charset 字符集][collate 排序规则]
删除数据库	drop database [if exists] 数据库名;
使用数据库	use 数据库名;

表操作

查询当前数据库所有表    

show tablses;

查询表结构    

desc 表名;

查询指定表的建表语句     

show create table 表名；

创建表    

CREATE TABLE [IF NOT EXISTS] table_name(
column_definition1,
column_definition2,
……..,
table_constraints
);

table_constraints: 它定义列的名称以及每列的数据类型。表定义中的列由逗号运算符分隔

table_constraints : 它指定了表约束，如PRIMARY KEY、UNIQUE KEY、FOREIGN KEY、CHECK 等;

eg : 

CREATE TABLE IF NOT EXISTS `runoon_tbl`(
`runoon_id` INT UNSIGNED AUTO_INCREMENT,
`runoon_title` VARCHAR(100) NOT NULL,
`runoon_author` VARCHAR(40) NOT NULL,
`submission_date` DATE,
PRIMARY KEY ( `runoon_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

复制表结构

CREATE TABLE new_table LIKE original_table;

CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2

复制表结构及数据到新表

CREATE TABLE 新表 SELECT * FROM 旧表

修改表    

添加字段
    alter table 表名 add 字段名 类型(长度) [COMMENT 注释][约束];

修改字段名 和 数据类型:
    alter table 表名 change 旧字段名 新字段名 类型(长度)  [COMMENT 注释][约束];

删除字段
    alter table 表名 drop 字段名;

修改表名
    alter table 表名 rename to 新表名

删除表    

drop table [if exists] 表名;

删除表，并重新创建该表结构

truncate table 表名;

DML

新增数据

一次插入一条数据

INSERT INTO tablename(列名…) VALUES(列值);
INSERT INTO tablename SET column_name1 = value1, column_name2 = value2，…;

如果VALUES中什么都不写，那MySQL将使用表中每一列的默认值来插入新记录

INSERT INTO users () VALUES();

如果表名后什么都不写，就表示向表中所有的字段赋值。使用这种方式，不仅在VALUES中的值要和列数一致，而且顺序不能颠倒

INSERT INTO users VALUES(123, '姚明', 25);

一个expression可以引用在一个值表先前设置的任何列

INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);
--但不能这样
INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15);

复制旧表的数据到新表(假设两个表结构一样)

 表结构一样

INSERT INTO 新表SELECT * FROM 旧表

表结构不一样

INSERT INTO 新表(字段1,字段2,.......) SELECT 字段1,字段2,...... FROM 旧表

批量插入多条语句（一次最好在500-1000条）

insert into worker values(‘tom’,’tom@yahoo.com’),(‘paul’,’paul@yahoo.com’);

insert into tbl_name1(col1,col2) select col3,col4 from tbl_name2;

数据量特别大，可以使用Load

连接客户端,加上参数

mysql --local-infile -u root -p

设置全局参数,1 开启从本地加载文件导入数据的开关

set global local_infile = 1

执行load指令将准备好的数据，加载到表结构中

load data local infile='/xxx/xxx.log' into table 'db_name'.'table_name' fields terminated by ',' lines terminated by '\n'

修改数据

update 表名 set  字段名1=值1 ，字段名2 = 值2 [WHERE 条件]; 

删除数据

DELETE FROM 表名 [WHERE 条件]

DQL

select * from 表名 where 条件列表 group by 分组字段 having 分组后条件列表 order by 排序字段列表 limit 分页参数

select 

as 取别名

distinct 去重

where

条件查询 

字段之间

=     <>    !=   <   <=     >     >= 

IN (...)
BETWEEN n AND m  //等价于 [n,m] 

is null/ is not null

like ：%：表示任意字符，_：表示单个字符

逻辑运算符

条件之间：

AND 或 && 

OR 或 ||

NOT 或 !

子查询

1 可以在 SELECT、UPDATE 和 DELETE ，where子句中使用

2 子查询必须放在圆括号内

3 先执行子查询，再执行父查询

IN | NOT IN

EXISTS | NOT EXISTS

聚合函数

常用的5种

sum()、avg()、max()、min()、count()

分组查询

聚合搭配group by 使用

select 字段 from [where 条件] group by 分组字段名 [having 分组后过滤条件]

select id,sum(score) from emp group by score having count(score) > 60;

select id,sum(score) as scores from emp group by score having scores > 60;

排序

order name 默认asc

order name desc,age asc

分页查询

Limit : 初始行的偏移量为0

select * from table_name limit 10;//检索前10行记录

select * from table_name limit 5 ，10;//从第6行开始，检索10行记录，即：检索记录行 6-15

分页

select * from table limit (start-1)*pageSize,pageSize;

执行顺序

select * from 表名 where 条件列表 group by 分组字段列表 having 分组后条件列表 order by 排序字段列表 limit 分页参数

FROM -》 WHERE -》 GROUP BY -》 HAVING -》SELECT -》ORDER BY -》 LIMIT

DCL

管理用户

查询用户

USE mysql;

select * from user;

创建用户

create user '用户名'@'主机名' IDENTIFIED BY '密码'；

eg: 

CREATE USER 'zhangsan'@'localhost' IDENTIFIED by '123456';

CREATE USER 'lisi'@'%' IDENTIFIED by '123456';

修改用户密码

alter user  '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码'；

删除用户

drop user  '用户名'@'主机名';

 权限控制

常见的几种权限

ALL, ALL PRIVILEGES 所有权限

SELECT，INSERT,UPDATE,DELETE,ALTER,DROP,CREATE

查询权限

SHOW GRANTS FOR '用户名'@'主机名'

授予权限

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名'

eg:

grant all on dbName.* TO 'itcast'@'localhost' 

撤销权限

REMOVE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名'

命令行访问

mysql -u zhangsan -p

回车，输入密码

函数

1. 字符串函数
2. 数值函数
3. 日期函数
4. 流程函数

字符串函数

常用的如下

concat(s1,s2...sn)

字符串拼接

lower(str),upper(str)

将字符串全部转小写 和 大写

LPAD(str,n,pad)  RPAD(str,n,pad)

左填充和右填充， n：达到n个字符串长度，pad : 用什么填充符号

trim(str)

去掉头和尾的空格

substring(str,start,len)

截取字符串，从start开始的len长度，start从1开始

SELECT SUBSTRING('123456789',1);  // 123456789

SELECT SUBSTRING('123456789',1,3);   // 123

SELECT SUBSTR('123456789',1,3);  // 123

数值函数

ceil(x)

向上取整，eg: 1.2->2

floor(x)

向下取整,  eg: 1.2->1

mod(x,y)

取模，x/y

rand()

返回0~1的随机数

round(x,y)

x进行四舍五入，保留y位小数

日期函数

curdate()

返回当前日期： 2024-03-15

curtime()

返回当前时间:    14:11:45

now()

返回当前日期和时间：  2024-03-15 14:12:14

year(date)，month(date)，day(date)

获取指定date的年份/月份/日期

day_add(date,INTERVAL expr type), day_sub(date,INTERVAL expr type)

往后推时间，往前推时间

eg :  SELECT DATE_ADD(NOW(),INTERVAL 70 DAY/MONTH/YEAR)

datediff(date1,date2)

返回起始时间 date1 和结束时间 date2之间的天数

eg：

SELECT DATEDIFF('2023-02-01','2023-01-01');
SELECT DATEDIFF(NOW(),'2024-01-01');

date_format

SELECT DATE_FORMAT(now(),'%Y%m%d')

流程函数

if(value,t,f)  如果value为true , 返回t， 否则返回f

ifnull(value1,value2) 如果value1不为空，返回value1,否则value2

case when [val1] then [res1]...else [default] end

case [expr] when [val1] then [res1] ...else [default] end

eg:

SELECT CASE WHEN delete_boolean = '0' THEN '正常' ELSE '已删除' END as 'status'
FROM dataresource_manage ;

SELECT CASE delete_boolean WHEN '0' THEN '正常' ELSE '已删除' END as 'status'
FROM dataresource_manage ;

约束条件

NOT NULL 非空

UNIQUE  唯一

PRIMARY KEY 主键

DEFAULT 默认

CHECK 检查  ,mysql1.8版本才有

FOREIGN KEY 外键

eg：

id int primary key auto_increment

name varchar(20) not null unique comment '名称'

age int check (age >0 && age <30) 

status default '1'

多表查询

内连接，外连接(左外连接，右外连接)，自连接，子查询,联合查询，子查询

内连接

不推荐，数据增多，笛卡尔积会非常可怕

select  字段列表 from table1,table2 where...

select 字段列表 from table1 [inner] join table2 on .. where...

左外连接

select 字段列表 from table1 left [outer] join table2 on .. where...

右外连接

select 字段列表 from table1 right [outer] join table2 on .. where...

自连接

可以内连接，外连接，

select 字段列表 from table1 a left join table1 b on 条件.. 

联合查询

union 合并后去重

union all 合并后不去重

子查询

列子查询

列  IN / NOT IN / ANY /SOME /ALL （列子查询）

IN ： 列表里匹配一个即可

NOT IN  ： 字段不在列表里的数据

ANY :  列表中满足任何一个即可

SOME：能用some的地方都可以用any

all :  满足所有，前面跟操作符

找到 大于 classroomb 班里任何学生年龄的 classrooma 班里的学生 ，也可以用min

select * from classrooma where age > any (select age from classroomb)

找到 大于 classroomb 班里所有学生年龄的 classrooma 班里的学生 ，也可以用max

select * from classrooma where age > all (select age from classroomb)

行子查询

常用操作符 = , <> , in , not in 

select * from emp where (salary,managerid) = (select salary,managerid from emp  where id = 'asd')

事务

SELECT @@autocommit;    1 是自动提交；0是手动提交

set @@autocommit = 0;     只对当前的mysql命令行窗口有效

commit;

rollback;

开始事务：start transaction  或 begin

提交任务：commit

回滚：rollback 

ACID

事务的四大特性： 原子性，一致性，隔离性，持久性

并发事务引发的问题

脏读： 一个事务读到另一个事务还没提交的数据

不可重复读： 一个事务两次查询读取同一条记录，但两次读取的数据不同（会读到另一个事务修改后的数据）

幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了幻影。（会读到另一个事务新增的数据）

基于这些问题，mysql使用隔离级别来解决

隔离级别	脏读	不可重复读	幻读
Read uncommitted未提交读	√	√	√
Read commited已提交读（Oracle默认）	×	√	√
Repeatable Read（默认）可重复读	×	×	√
Serializable序列化读	×	×	×

serializable 并发时会导致其他事务增删改操作阻塞，影响效率 

-- 查看事务隔离级别
select @@transaction_isolation

-- 设置事务隔离级别,session 当前会话有效，global 全局
set [session|global] transaction isolation level [read uncommited | read commited | repeatable read | serializable]

存储引擎

Mysql体系结构

连接层，服务层，存储引擎（可插拔式）层，存储层

InnoDB是Mysql5.5之后的默认引擎

存储引擎简介

        存储引擎就是存储数据，更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。

-- 建表语句里选择存储引擎，不选择则默认 InnoDB
create table table1(..)engine=InnoDB 

-- 查看当前数据库支持的存储引擎
show engines;

ps：
存储引擎Memory，存在内存中，速度快，但存在丢失数据等问题，只能作为临时缓存，渐渐被 Redis 取代
存储引擎MyISAM ， InnoDb的老版本，不支持事务，外键等

存储引擎特点

InnoDb

        是一种兼顾高可靠性和高性能的通用存储引擎，在Mysql5.5之后，是mysql的默认存储引擎

特点：

• DML操作遵循ACID模型，支持事务
• 行级锁，提高并发访问性能
• 支持外键约束，保证数据的完整性和正确性

文件

        xxx.ibd    

        二进制文件，xxx代表表名，innoDb引擎的每张表都会对应这样一个表空间文件，存储该表的表结构(frm,sdi（8.0之后frm也存到sdi里面）),数据和索引。

        参数： innodb_file_per_table

show variables like 'inno_file_per_table'    

show @@inno_file_per_table;

如果是 ON，则每个表对应一个xxx.ibd文件

cmd环境/Linux环境查看文件指令 ：

ibd2sdi account.ibd

逻辑存储结构如下，都是1:n

表空间(tablespace) -> 段(segment) -> 区(extent) -> 页(page) -> 行 (row)

页固定大小：16k

区固定大小：1M

行包括信息：Trx id（最后一次操作事务的id），Roll pointer(指针),col1,col2..（字段） 

MyISAM（了解）

        Mysql早期的默认存储引擎

特点：

• 不支持事务，不支持外键
• 支持表锁定，不支持行锁
• 访问速度快

文件： tb_book.MYD（存放表数据）, tb_book.MY （存放索引）, tb_book.sdi(存放表结构，json格式)

Memory（了解）

        表数据是存储在内存中，由于受到硬件问题，或断电问题的影响，只能将这些表作为临时表或缓存使用。

特点：

• 内存存放
• hash索引（默认）

文件： xxx.sdi 存储表结构信息

指令

启动/重启/停止 数据库
systemctl start/restart/stop mysqld;

用户连接mysql
mysql -u 用户名 -p

第一次的时候查询日志文件中的密码
grep 'temporary passwod' /var/log/mysqld.log

索引

        索引(Index)是帮助MySQL高效获取数据的数据结构(有序)。Mysql的索引是在存储引擎层实现，不同的存储引擎有不同的结构，主要:

        B+Tree索引（最常见的索引）：重点

        Hash索引,只有精确匹配，不支持范围查询（Memory）

        R-tree索引(空间索引)，是MYISAM引擎的特殊索引，用于地图空间数据类型，使用少

        Full-text（全文索引）：Mysq5.6+MySQL也可以实现分词搜索(FULLTEXT)_mysql 分词-CSDN博客

       索引结构：B+Tree索引

        二叉树 ：可能形成单链

        -> 二叉平衡树（红黑树）：基于二叉树原理，平衡左右两侧，使层级减少

        ->B-Tree（多路平衡查找树）: 一颗最大度数(max-degree)为n（n阶）的b-tree为例，每个节点最多存储(n-1)个key,n个指针

        -> B+Tree，基于b-tree的原理使得每个数据都在最底层的叶子节点，且形成单项链表

        -> Mysql优化了B+tree，在此基础上，增加了一个指向相邻叶子节点的链表指针(循环双向链表)，就形成了带有顺序指针的B+Tree

索引分类

分类	含义	特点	关键字
主键索引	针对表主键创建的索引	默认自动创建，只能有一个	primary
唯一索引	字段里的数据不能重复	可以有多个	unique
常规索引	快速定位特定数据	可以有多个
全文索引	分词，查询文本关键字	可以有多个	fulltext

InnoDb存储引擎，根据索引形式，又可以分

分类	含义	特点
聚集索引	将数据存储和索引放在一起，索引结构的叶子节点保存了行数据	必须有，且只有一个
二级索引	将数据和索引分开存储，索引结构的叶子节点关联的是对应的主键，然后根据回表查询找到对应数据	可以存在多个

聚集索引选取规则：

1. 如果有主键，主键索引就是聚集索引
2. 如果不存在主键，将使用第一个唯一（unique）索引作为聚集索引
3. 如果既没有主键，也没有唯一主键，自动生成一个rowid作为隐藏的聚集索引

索引语法

--索引名称，一般 idx_表名_col1_col2..
-- 创建索引
create [unique|fulltext] index index_name on table_name(col1,col2..);

-- 查询索引
show index from table_name;

-- 删除索引
drop index index_name on table_name;

-- 查看表的所有索引
show index from table_name;

SQL性能分析

-- SQL 执行频率
show global status like 'Com_______'  (7个下划线)

慢日志查询

        慢查询日志记录了所有执行时间超过指定参数(long_query_time ,单位：秒，默认10秒)的所有SQL语句的日志。

        MYSQL的慢查询日志默认没有开启，需要在/etc/my.cnf中配置： 

# 开启Mysql慢日志查询开关

show_query_log=1

# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会被视为慢查询，记录慢查询日志

long_query_time=15

重启数据库 : systemctl restart mysql

查看是否开启： show variables like 'slow_query_log'

文件生成位置 ：/var/lib/mysql/localhost.slow.log（也不一定，到时候根据  .log   模糊匹配，自己找下，名字也不一定一样）

SQL性能分析

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了，通过have_profiling参数，能够看到当前MYSQL是否支持profile操作：select @@have_profiling;

set 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

获取mysql如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的顺序

explain/desc  select ...

id： id相同从上往下执行，id不同，id大的先执行

索引使用法则

最左前缀法则

        联合索引，要遵守最左前缀法则，指的是查询从索引的最左列开始，并且不跳过索引中的列。  如果跳过某一列，索引将部分失效（后面的字段索引失效）

        与where后面条件查询里 字段顺序无关，mysql会自动优化

索引失效

范围查询

        联合索引中出现范围查询(>,<)，范围查询右侧的列索引失效

索引列运算

        不要在索引列上进行运算操作，否则索引将失效,

        eg:  select * from table1 where substring(age,1,2) = 'ds' --- age的索引失效

字符串不加引号

        字符串类型字段使用时，不加单引号，索引将失效

        eg： select * from table1 where phone = 123456789 -- phone的索引失效

模糊查询

        如果仅仅是尾部模糊匹配，索引不会失效，如果是头部模糊匹配，索引将失效

or连接

        用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到，两边都有才会生效

数据分布影响

        如果Mysql苹果使用索引比全表更慢，则不使用索引，mysql自主评估

索引使用提示

        SQL提示，是优化数据库的一个重要手段。简单来说，就是SQL语句中加入一些人为的提示来达到优化操作的目的

user index:       

同时有联合索引和单列索引，用户建议索引

select * from table1 use index(idx_table1_pro) where pro =  '软件'

ignore index:

忽略索引

select * from table1 ignore index(idx_table1_pro) where pro =  '软件'

force index:

必须使用这个索引

select * from table1 force index(idx_table1_pro) where pro =  '软件'

索引使用原则

覆盖索引

        查询中尽量使用覆盖索引（查询使用索引，并且返回需要的列，在该索引中已经全部能够找到，减少select *）

前缀索引

        当字段类型为字符串(varchar,text等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率，此时可以将字符串一部分前缀，建立索引，节省索引空间，提高效率

create index idx_xxx on tablr_name(column(n));

前缀长度

        可以根据索引的选择性来决定，而选择性指的是不重复的索引值和数据表的记录总数的比值，索引选择性越高查询效率越高，唯一索引的选择性是1，是最好的索引

select count(distinct email)/count(*) from tb_user;

select count(distinct substring(email,1,5)) / count(*) from tb_user

单列索引和联合索引的选择

        如果存在多个查询条件，考虑针对查询字段建立索引时，建议创建联合索引，而非单列索引

索引设计原则

1. 针对数据量较大，且查询比较频繁的表建立索引
2. 针对常作为条件(where),排序(order by) , 分组(group by)操作的字段建立索引
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度较高，使用索引的效率越高
4. 如果是字符串类型的字段，字段的长度较长，可以针对字段的特点，建立前缀索引
5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引 的代价也就越大，会影响增删改的效率
7. 如果索引列不能存储NULL值，请在创建表的时候使用 NOT NULL 约束它，当优化器知道每列是否包含NULL值时，它可以更好地确定那个索引最有效地用于查询

SQL优化

插入优化：

        单条插入 -》 批量插入 -》 load插入数据

主键优化：

        1 满足业务需求情况下，尽量降低主键的长度

        2 插入数据时，尽量顺序插入，选择使用 auto_incremenet自增主键

        3 尽量不要使用uuid做主键或者其他自然主键，比如身份证号

        4 业务操作时，避免对主键的修改

order by优化:

        Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫做FileSort排序

        Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index, 不需要额外排序，操作效率高

创建索引,默认升序索引

create index idx_xxx on table_name(col1 , col2.. )

如果一个字段升序，一个降序

create index idx_xxx on table_name(col1 acs, col2 desc...)

• 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
• 尽量使用覆盖索引
• 多字段排序，一个升序一个降序，此时需要注意联合索引创建时的规则
• 如果不可避免的出现filesort,大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size（默认256K）

group by优化：

order by col1,col2 

-- 创建索引,order 后面的字段，还可以加上查询的字段

create index idx_xxx on table_name(col1,col2..)

Limit优化：

 select * from table1 limit 20000,10;

优化

select t1.* from table1 t1

left join (select id from table1 order by id limit 20000,10) t2 on t1.id = t2.id

count优化：

count(*)

InnoDB 引擎执行的时候，需要把每行数据一行一行从引擎里面读出来，然后累计技术

count对于返回的结果集，一行行判断，如果count的参数不是null，累计值+1，否则不加，最后返回累计值

count(*) , count(主键)，count(字段),count(1)

count(1): 遍历整张表，服务层对于返回的每一行放1 

count(*):不取值，服务层直接进行累加

效率：

count(*) > count(1) > count(主键) >count(字段)

update优化

避免行锁升级成表锁

update 的 where 后面的条件如果非主键的情况下，最好加索引

innodb的行锁是针对索引加锁，不是针对记录加锁，并且该索引不能失效，否则会从行锁变成表锁

视图、存储过程、触发器

视图

         视图(View)是一种虚拟存在的表，视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且在使用视图时动态生成的

        通俗讲，视图只保存查询的SQL逻辑，不保存查询结果，所以我们在创建视图的时候，主要的工作就在创建这条SQL查询语句上。

        虚拟表，比如连接过大，创建视图后查询再继续关联表，仍然连接过大；插入的数据，会插入到原来的表里。

创建/修改视图
create [or replace] view 视图名称[(列名列表)] AS SELECT [with [cascade|local] check option ]

with cascade|local check option，默认为cascade:
1 update，要保证数据update之后能被视图查询出来，也就是要符合where的条件
2 insert,保证insert的数据能被视图查询出来
3 delete,有无 with check option都一样
4 对于没有where字句的视图，使用with check option是多余的
不加则增删改的时候不做校验，如果v1不校验，v2查询v1且v2做校验，则增删改v2时，若是cascade（级联），则v2,v1的条件也做校验，若是local,则v1的条件不做校验； 若v3查询来自v2,v3不做校验，则v3本条sql不校验，但会校验v2及以上的条件

eg:
create or replace view v_user_temp as select id,name from student where id <10;

查询创建视图
show create view 视图名称;

查看视图数据
select * from 视图名称...

修改2
alter view 视图名称[(列名列表)] as select [with [cascade|local] check option ]

删除视图
drop view if exists 视图名称[,视图名称...]

存储过程

        存储过程是事先经过编译并保存在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据层和应用服务器之间的传输，对于提高数据处理的效率很有好处。

创建
create proceduce 存储过程名称(参考列表)
begin
    ..sql语句
end;

如果在命令行执行，需要前后加上分隔符 delimiter ;

调用
call 名称(参数);

查看指定数据库的存储过程以及状态信息
select * from infomation_schema.routines where routine_schema = 数据库名
查看某个存储过程的定义
show create procedure 存储过程名称

删除
drop procedure [if exists] 存储过程名称;

变量

@变量名
用户定义的变量不需要进行声明或初始化，只不过获得的值为NULL

赋值
set @var_name = 值[,@var_name=值]
set @var_name := 值[,@var_name:=值]  --常量赋值，建议采用这种
select @var_name := expr[,@var_name :=expr]...
select 字段名 into @var_name from 表名

使用
select @var_name[,@var_name...];

局部变量

        是根据需要定义的在局部生效的变量，访问之前，需要declare声明，可用作存储过程内的局部变量和输入参数，局部变量的范围是在声明的begin...end块

declare 变量名 变量类型[default ...]

变量类型是数据库类型：int,bigint,char,warchar,date,time等

赋值
set 变量名=值
set 变量名:=值
select 字段名 into 变量名 from 表名...

if语法

if 条件1 then
    ...
elseif 条件2 then
    ...
else
    ...
end if;

case语法

语法1
case case_value
    when val1 then statemenet_list1
    [when val2 then statemenet_list2  ...]
    [else statemenet_listn  ]
end case;

语法2
case 
    when condition1 then statemenet_list1
    [when condition2 then statemenet_list2]...
    [else statemenet_listn]
end case;

参数

in 输入(默认)，out 输出 ，inout 可以作为输入，也可以作为输出参数

create procedure 存储过程名称(IN / OUT / INOUT 参数名 参数类型)
begin
    ...
end

create procedure proCheckScore(in score int,out jige varchar(20))
begin
    ...
end

call(70,@result);
select @result;

循环

while循环

先判定条件，如果条件为true,则执行逻辑，否则不执行

while 条件 do
    ...
end while;

repeat循环

repeat 是有条件的循环控制，当满足条件的时候，退出循环;如果不满足，则下一次继续循环

repeat
    SQL 逻辑
    until 条件
end repeat;

对比while，会先执行一次，然后再判断是否满足

loop循环

loop本身没有退出条件，需要搭配leave
leave 配合循环，退出循环
iterate 跳过当前循环剩下的循环，直接进入下一次,即java的continue

[begin_label:] loop
    sql逻辑
end loop [end_label]

leave label;退出指定标记的循环体
iterate label; 直接进入下一次循环

eg:
sum: loop
    if n <=0 then leave sum end if;
    ...
end loop sum;

游标和handler

        游标(cursor)用来存储查询结果集的数据类型，在存储过程中和函数中可以使用游标对结果集进行循环的处理，游标的使用包括游标的声明，open、fetch和close

声明,要先声明游标赋值给的普通变量，再声明游标
declare 游标名称 cursor for 查询语句

打开
open 游标名称

获取游标记录
fetch 游标名称 into 变量[,变量]

关闭
close 游标名称

条件处理程序handler ，用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤
declare handler_action handler for condition_value[,condition_value] ...statement;

handler_action:
    continue :继续执行当前程序
    exit: 终止
condition_value:
    sqlstate sqlstate_value :状态码，如02000
    sqlwarning: 所有以01开头的sqlstate代码简写
    not found :所有以02开头的sqlstate代码简写,找不到数据
    sqlexception: 所有没有被sqlwarning 或not found获取的sqlstate代码的简写

eg:
declare u_cursor cursor for select name,age from tb_user where age < 10;
declare exit handler for SQLSTATE '02000' close u_cursor;
//或 declare exit handler for not found close u_cursor;

open u_cursor; 
    while true do
        -- 游标每一行给局部变量
        fetch u_cursor into uname,uage;
        insert into table1 values ( uname,uage);
    end while;
close u_cursor;

存储函数

        存储函数 是必须有返回值的存储过程，存储函数的参数只能是IN类型

create function 存储函数名称(参数列表) returns type [characteristic...]
begin
    ..
    return ..
end;


characteristic说明：
1 deterministic: 相同的输入参数总是产生相同的结果
2 no sql :不包含sql语句
3 reads sql data:包含读取数据的语句，但不包含写入数据的语句

eg:
create function f1(n int) returns int deterministic
begin
    declare total int default 0;

    while n>0 do
        set total:=total+n;
        set n:=n-1;
    end while;

    return total;
end;

调用
select f1(100);

触发器

        触发器是和表有关的数据库对象，指在增删改之前或之后，触发并执行触发器中定义的sql语句集合。

        触发器这样的特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作

        使用笔名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的，现在触发器还只支持行级触发，不支持语句级触发

解释：

        如果对表进行增删改，操作的数据有n条，就执行n次触发器——行级触发，一次操作n条数据——语句级触发

触发器类型	NEW 和 OLD
insert触发器	NEW 表示新增的数据
update触发器	OLD表示修改前的数据，NEW表示修改后的数据
delete触发器	OLD表示删除的数据

create trigger trigger_name before/after insert/update/delete
on table_name for each row -- 行级触发器
begin 
    获得数据：new.id,new.column... / old.id,old.column...
end ;

查看触发器
show triggers;

删除,schema_name没有指定则是当前数据库
drop trigger [schema_name.]trigger_name;

锁

        锁是计算机协调多个进程或线程并发访问某一资源的机制。

• 全局锁，锁数据库中的所有表
• 表级锁，锁每次操作的那张表
• 行级锁，锁每次操作的那一行数据

全局锁

        全局锁对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交够都将被阻塞。

典型的使用场景： 全库备份，锁定所有表，从而获得一次性试图，保证数据的完整性。

// 加全局锁
lush tables with read lock;

// 在linux或者windows命令行里执行，-h远程访问
mysqldump [-h192.168.20.20] -uroot -p1234 db_name > db_name.sql

// 解锁
unlock tables;

数据库中加全局锁，是一个比较重的操作，存在以下问题

1. 如果在主库上备份，那么在备份期间都不能执行更新，业务基本就将停摆
2. 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志(binlog),会导致主从延迟

在InnoDB引擎中，我们可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数据备份，底层通过快照读来实现

mysqldump --single-transaction -uroot -p1234 db_name > db_name.sql

表级锁

        表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低，应用在MyISAM,InnoDB,BDB等存储引擎中

 主要分三类：

• 表锁
  • 表共享读锁（read lock）：简称读锁，谁都可以读，但谁都不能写
  • 表独占写锁（write lock）：简称写锁，当前客户端可以读写，其他客户端不能读写
• 元数据锁(meta data lock,MDL)：MDL加锁过程时系统自动控制，无需显式使用，在访问一张表的时候自动加上。DML锁主要作用时维护表元数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作（如果某张表有活动事务，就不能修改表结构）
• 意向锁：为了避免DML在执行时，加的行锁和表锁的冲突，在InnoDb中引入意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查
  • 流程： update  -> 先加行锁  -> 再对这张表加上意向锁 ->意向锁和另一个事务加的表锁兼容，那么加锁；不兼容，则阻塞。
  • 意向锁类型：
    • 意向共享锁(IS) :由语句 select ... lock in share mode ，与表锁共享锁(read)兼容，与表锁排他锁(write)互斥
    • 意向排他锁(IX) :由insert , update ,delete ,select ... for update 添加，与表锁共享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥。

表加锁
lock tables db_name[,db_name...] read/write

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

在Mysql5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁（共享）；当对表结构进行变更操作的时候，加MDL写锁（排他）

对应SQL	锁类型	说明
lock tables xxx read/write	shared_read_only/shared_no_write
select , select ... lock in share mode	shared_read(共享锁)	与shared_read,shared_write兼容，与排他锁互斥
insert,update,delete,select ... for update()	shared_write(共享写锁)	与shared_read,shared_write兼容，与排他锁互斥
alter table...	exclusive（排他锁）	与其他的MDL都互斥

查看元数据锁：
SELECT * FROM PERFORMANCE_SCHEMA.metadata_locks;

查看意向锁
SELECT * FROM performance_schema.data_locks;

行级锁

        行级锁，每次操作所著对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高，应用在InnoDb存储引擎当中。

        InnoDb的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为三类：

•  行锁(Record Lock) : 锁定单行记录的锁，防止其他事务对此行进行update和delete。再RC，RR隔离级别下都支持
• 间隙锁(Gap Lock):锁定索引记录间隙(不含该记录)，确保索引间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下支持。
• 临键锁（Next-Key Lock）: 行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。

	请求-共享锁	请求-排他锁
当前锁-共享锁	兼容	冲突
当前锁-排他锁	冲突	冲突

SQL	行所类型	说明
insert,delete,update	排他锁	自动加锁
select	不加任何锁
select...lock in share mode	共享锁
select...from update	排他锁

information_schema数据库: 

存储的元数据，只能查看，不能增删改

详解mysql中的information_schema_mysql information_schema-CSDN博客

  变量： 全局变量(global)，会话变量(session)

查看所有系统变量,默认会话
show [session | global] variables;

可以通过like模糊匹配查询
show [session | global] variables like '';

查看指定变量的值
show [session | global] 系统变量名;
select @@[session | global .]autocommit;

设置系统变量
set [session | global] 系统变量名=值;
set @@[session | global .]系统变量名 = 值;

服务器重启后，所有设置都恢复成初始化状态，若要永久修改，改my.cnf配置

InnoDB引擎

MySQL进阶-InnoDB引擎_minnodb-CSDN博客

Mysql管理

。。。