参考视频：
黑马程序员 MySQL数据库入门到精通，从mysql安装到mysql高级、mysql优化全囊括_哔哩哔哩_bilibili
uuid 去 -
MySQL replace uuid REPLACE(UUID(), ‘-‘, ‘‘)重复问题，多种解决方案_链诸葛的博客-CSDN博客_mysql replace uuid
Mysql官方文档：MySQL :: MySQL Documentation
干了这么多年，忽然发现Mysql的东西都没有梳理过，都是用到什么找什么，零碎一地，惭愧。
Mysql 和 Java 的类型映射
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
回车，输入密码
函数
- 字符串函数
- 数值函数
- 日期函数
- 流程函数
字符串函数
常用的如下
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); // 123456789SELECT 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存储引擎，根据索引形式，又可以分
| 分类 | 含义 | 特点 |
| 聚集索引 | 将数据存储和索引放在一起，索引结构的叶子节点保存了行数据 | 必须有，且只有一个 |
| 二级索引 | 将数据和索引分开存储，索引结构的叶子节点关联的是对应的主键，然后根据回表查询找到对应数据 | 可以存在多个 |
聚集索引选取规则：
- 如果有主键，主键索引就是聚集索引
- 如果不存在主键，将使用第一个唯一（unique）索引作为聚集索引
- 如果既没有主键，也没有唯一主键，自动生成一个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
单列索引和联合索引的选择
如果存在多个查询条件，考虑针对查询字段建立索引时，建议创建联合索引，而非单列索引
索引设计原则
- 针对数据量较大，且查询比较频繁的表建立索引
- 针对常作为条件(where),排序(order by) , 分组(group by)操作的字段建立索引
- 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度较高，使用索引的效率越高
- 如果是字符串类型的字段，字段的长度较长，可以针对字段的特点，建立前缀索引
- 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
- 要控制索引的数量，索引并不是多多益善，索引越多，维护索引 的代价也就越大，会影响增删改的效率
- 如果索引列不能存储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;
数据库中加全局锁，是一个比较重的操作，存在以下问题
- 如果在主库上备份，那么在备份期间都不能执行更新，业务基本就将停摆
- 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志(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管理
。。。
1419
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
