mysql2-CSDN博客

本文链接：https://blog.csdn.net/qq_36590288/article/details/123501827

来自视频学习：黑马程序员 MySQL数据库入门到精通，从mysql安装到mysql高级、mysql优化全囊括_哔哩哔哩_bilibilihttps://www.bilibili.com/video/BV1Kr4y1i7ru?p=153&spm_id_from=pageDriver

二、进阶

1、视图

a、介绍：视图（view）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。通俗的讲：视图只保存了查询的sql逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条sql的查询语句上
b、创建视图：create [or repalce] view 视图名称[(列名列表)] as select语句 [with [cascaded | local] check option]
eg：create view user_info_v1 as select id, name from user_info where id < 5; #创建视图user_info_v1
create or replace view user_info_v2 as select id, name, age from user_info where id < 5; #创建视图user_info_v2

c、查询视图：show create view 视图名称; #查询创建视图的sql语句
       select * from 视图名称.....; #查询视图数据，跟查普通表一样
        eg：select * from user_info_v2 where id < 3;
d、修改视图，方式一：create [or replace] view 视图名称[(列名列表)] as select语句 [with [cascaded | local] check option]
       方式二：alter view 视图名称[(列名列表)] as select语句 [with [cascaded | local] check option]
        eg：create or replace view user_info_v1 as select id, name,gender from user_info where id < 5;
   alter view user_info_v2 as select id, name,gender from user_info where id < 5;
e、删除视图：drop view [if exists] 视图名称 [,视图名称]...
        eg：drop view [if exists] 视图名称 [,视图名称]...

f、检查选项cascaded：不加cascaded时会出现以下情况

当加上cascaded后

（注：下图的v1后面没有加with cascaded check option）

g、检查选项local

（注：下图的v2后面加了with local check option）

h、视图的更新：要使视图可以更新(insert、update)，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图中包含聚合函数或窗口函数(sun()、min()、max()、count()等)、distinct、group by、having、union、union all中的任意一项，该视图都不可更新
i、视图作用：
   简单：视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次都要指定全部的条件
   安全：数据库可以授权，但不能授权到数据库特定的行和特定的列。通过授权视图，就可以控制用户只能查看或者修改指定的行和列的数据
   数据独立：视图可帮忙用户屏蔽真实的表结构变化带来的影响（意思使原表的表字段可以被命名为别名显示在视图中，这样用户就不用关心原表的字段信息了）

2、存储过程

a、介绍：存储过程是事先编译并存储在数据库中的一段sql语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务之间的传输，提高数据处理的效率
   存储过程的思想其实就是数据库SQL语言层面的代码封装与重用
   特点：封装、复用；可以接收参数，也可以返回数据；减少网络交互，效率提升
b、创建存储过程：create procedure 存储过程名([参数列表]) begin sql语句(可以多条) end;
   eg：create procedure p1()
                begin
                    select count(*) from user_info;
                end;

注：用命令行创建存储过程时，会识别到begin和end之间的分号为语句结束符，需要通过关键字delimiter指定sql语句的结束符

c、调用存储过程：call 存储过程名([参数]);
   eg：call p1(); #出来的就是select count(*) from user_info;的结果
d、查看存储过程：
   查询指定数据库的存储过程及状态信息：select * from information_schema.routines where routine_schema = '数据库名';
   查询某个存储过程的定义：show create procedure 存储过程名;
e、删除存储过程：drop procedure [if exists] 存储过程名;
f、系统变量：系统变量时MySQL服务器提供，不是用户定义的，属于服务层面，分为全局变量(global)、会话变量(session)。

g、用户变量：用户自己定义的变量，不用提前声明，在用的时候直接用“@变量名”使用就可以(系统变量是@@，用户变量是@)，起作用于为当前会话
        赋值：set @变量名 = 值;
                set @变量名 := 值;
               select @变量名 := 变量名
                select 字段名 into @变量名 from 表名; #将表中查询的值赋给变量
               set/select @变量名1 := 值1, @变量名2 = 值2...; #多个变量赋值
        使用：select @变量名;
        注：用户定义的变量无需对其进行声明或初始化，如果没有声明或者初始化，获取到的值为null，不会报错
h、局部变量：作用域在begin和end语句块之间，访问之前需要先用declare声明。可用于存储过程内的局部变量和输入参数。
        声明：declare 变量名变量类型 [default 默认值]; #变量类型就是数据库类型int、bigint、char、varchar、date、time等
        赋值：set 变量名 = 值;
set 变量名 := 值;
          select 字段名 into 变量名 from 表名...;
        使用：select 变量名;

i、if语句

j、存储过程的参数。参数有三种类型：in(输入，不写的话默认输入)、out(输出)、inout(即输入又输出)

k、case语句

l、while循环：先判断条件，满足条件后，再执行循环体中的sql语句，否则不执行
语法while 条件 do sql语句 end while;

m、repeat循环：先执行一次，再判断条件，如果满足条件则推出循环，不满足继续循环
语法：repeat sql语句 until 条件 end repeat;

n、loop循环：如果不在sql逻辑中怎加推出循环的条件，可以用其来实现简单的死循环。loop可以配合以下两个语句使用
        leave：推出循环
        iterate：跳过当前循环剩下的语句，直接进入下一次循环
        语法：[begin_label:] loop sql语句 [leave label;] [iterate label;] end loop [end_label];

o、游标(curdor)：用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。
   声明游标：declare 游标名称 cursor for 查询语句;
   打开游标：open 游标名称;
   获取游标记录：fetch 游标名称 into 变量1 [,变量2...];
   关闭游标：close 游标名称;
p、条件处理程序

针对o和p的例子：

q、存储函数（用的较少，因为可以用存储过程替代）

3、触发器

a、介绍：触发器时与表有关的数据库对象，指在insert/update/delete之前或者之后，触发并执行触发器中定义的sql语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作
使用别名old和new来引用触发器中发生变化的记录内容，这与其他的数据库时相似的。现在触发器还只支持行级触发器，不支持语句级触发器(意思是如果一个update语句更新了3条数据，那么将会被触发三次。而不是一次)。以下是触发器类型：

b、语法
   创建：create trigger 触发器名 before/after insert/update/delete on 表名 for each row
       begin 触发后的操作; end;
   查看：show triggers; #查看当前数据库的所有触发器
   删除：drop trigger [数据库名.]触发器名; #如果不知道触发器名，默认为当前数据库

c、insert：

d、更新

e、删除

4、锁

a、概念：锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算机资源(CPU、RAM、I/O)的争用以外，数据也是一种供用户共享的资源，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。
b、mysql的锁按照锁的粒度分为以下三类：
        全局锁：锁住数据库中所有表
        表级锁：每次操作锁住整张表
        行级锁：每次操作锁住对应的行数据
c、全局锁：对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML、DDL和各种更新操作的事务提交语句将被堵塞。其典型的使用场景就是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图。保证数据的完整性
        语法：flush tales with read lock; #加锁
mysqldump [-h 数据库IP(非本机数据库时需要写上)] -u用户名 -p密码数据库名 > 备份的目标文件名 #备份整个数据库。注：这是mysql提供的一个工具，不是sql语句，所以要在mysql外面运行
unlock tables； #解锁
        特点：数据库中加全局锁是一个比较重的操作，如果从主库上备份，那备份期间会让业务停摆；如果从从库上备份，那备份期间从库就不能执行主库同步过来的二进制日志(binlog)，会导致主从延时。innoDB为了解决以上问题，可以在备份时加上--single-transaction参数来完成不加锁的一致性数据备份。语法如下:
                mysqldump --single-transaction [-h 数据库IP(非本机数据库时需要写上)] -u用户名 -p密码数据库名 > 备份的目标文件名

d、表级锁：每次锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、innoDB、BDB等存储引擎中。主要分为以下三类：
   d.1、表锁。分为两类:
       表共享读锁(read lock)：会堵塞所有客户端的写(包括加锁的这个客户端)，不会堵塞所有客户端的读
       表独占写锁(write lock)：加锁的这个客户端读和写都可以，但是会堵塞其他客户端的读和写
       语法：加锁：lock tables 表名, ... read/wirte;
           释放锁：unlock tables; #或者直接断开加锁的客户端也行
   d.2、元数据锁(meta data lock, MDL)：MDL加锁过程时系统自动控制的，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。主要为了避免DML和DDL冲突，保证读写的正确性

    d.3、意向锁：没有意向锁之前，在执行DML语句时会给表中的某一行数据加行锁，这时候如果要给这个表加一个表锁，就需要一行一行的去查看有没有哪一行数据加了行锁并且锁不兼容，这时候效率就会很慢。为了解决这个问题就加了意向锁，当某一行数据被加了行锁之后，就会给这个表加一个意向锁，如果客户端想再来加表锁时，可以直接跟意向锁对比，如果兼容，加表锁成功；如果不兼容，加表锁的操作就会进入堵塞状态
       意向锁分为：意向共享锁（IS)：由语句select ... lock in share mode添加；
                              意向排他锁(IX)：由insert、update、delete、select... for update添加

e、行级锁：锁住操作对应的行数据。锁的粒度最小，发生冲突的概率最低，并发度最高。应用在innoDB存储引擎中。innoDB时数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。主要分为以下三类
        行锁(record lock)：锁定单个行记录的锁，防止其他事务对此进行update和delete。在RC、RR隔离级别下都支持
        间隙锁(gap lock)：锁定索引记录间隙(不含该记录)，确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。
        临键锁(next-key lock)：行锁与间隙锁的组合，同时锁住数据和数据前面的间隙。在RR隔离级别下支持

f、行锁。分为以下两种：
共享锁(s)：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁（就是加了锁之后都可以读，但都不能写）
排他锁(X)：允许获取排他锁的书屋更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。（加了之后只有加锁的那个事务可以读和写，其他事务都不能读和写）

sql语句对应的行锁类型

g、间隙锁和临键锁：默认情况下，innoDB在repeatable read事务隔离级别运行，innoDB使用next-key(临键锁)进行搜索和索引扫描，以防止幻读
        g.1、索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁
                eg：目前存在id为1、4、5的数据，id为主键，执行update a set a_a = 'xxx' where id = 4;时，就会给2和3加上锁
        g.2、索引的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock退化为间隙锁。
                eg：穆穆目前存在age为1，4，6的记录，age为普通索引，执行select * from user where age = 4 lock in share mode;时，就会给2、3、4、5都加上锁
        g.3、索引的范围查询（唯一索引）--会访问到不满足条件的第一个为止
                eg：目前存在id为1、4、6的数据，id为主键执行select * from user where id >= 4 lock in share mode;时，就会给4及4以后的都加上锁
        注意：间隙锁的唯一目的就是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同意间隙上采用间隙锁

5、innoDB引擎

a、储存架构：分内存结构和磁盘结构；sql操作的数据首先时更新内存，然后内存会在一定的频率或者时机更新到磁盘中。mysql服务器中会分配不同的线程去进行内存和磁盘的同步。详细信息（133-137）黑马程序员 MySQL数据库入门到精通，从mysql安装到mysql高级、mysql优化全囊括_哔哩哔哩_bilibilihttps://www.bilibili.com/video/BV1Kr4y1i7ru?p=133b、事务原理：原子性、一致性和持久性是由redo log和undo log来保证的；隔离性是由锁和MVCC来保证的

c、MVCC基本概念

d 、数据库表中的三个隐式字段，可以在数据库的表空间文件(.ibd文件)中查找（ibd文件只存放数据库信息和表结构）
   DB_TRX_ID：最后一次更新该记录的事务ID
   DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本
   DB_ROW_ID：隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段
e、undo log：回滚日志，在insert、update、delete时产生的便于数据回滚的日志。insert操作时，产生的undo log日志只在回滚时需要，事务提交后，可被立即删除；而update、delete时，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即删除
f、undo log版本链：

注：不同事务或者相同事务对同一条记录进行修改，会导致该记录的undo log生成一条记录版本链条，链条的头部时最新的旧记录，链条尾部时最早的旧记录

g、readview(读视图)：时快照读sql执行时，mvcc提取数据的依据（就是这个快照读到底要读取undolog的那条记录），记录并维护系统当前活跃的事务（未提交的）ID。readview中包含了以下四个核心字段
   m_ids：当前活跃的事务ID集合
   min_trx_id：最小活跃事务ID
   max_trx_id：预分配事务ID，当前最大事务ID+1(因为事务ID都是自增的)
   creator_trx_id：readview创建者的事务ID

h、在rc(读已提交)隔离级别下，在事务中每次执行快照读时都会生成readview

在RR(可重复读)隔离级别下，只有在事务中第一次执行快照读时会生成readview，后续复用该readview

i、所以隐藏字段、undolog版本链、read view就合成了mvcc，mvcc加上锁就保证了事务的隔离性；而redolog和undolog就保证了一致性

6、mysql管理工具

a、MySQL安装后，自带了以下四个数据库

b、mysql工具(客户端工具)： -e选项可以在mysql客户端执行sql语句，而不是连接到MySQL数据库再执行，对于一些批处理脚本比较常用。注意当连的时远程的mysql就一定要写服务器和端口

        eg: mysql -uxiaoling -p123456 test -e "select * from user_info" (注意-e之前要指定数据库)
c、mysqladmin工具：是一个执行管理操作的客户端程序，可以用它来检查服务器的配置和当前状态、创建并输出数据库等
   eg：mysqladmin --help #查看mysqladmin所有工具选项
   mysqladmin -uxiaoling -p123456 version #查看数据库版本信息
   mysqladmin -uxiaoling -p123456 create test1 #创建test1数据库
d、mysqlbinlog：由于服务器生成的二进制日志文件以二进制格式保存，所以如果想检查这些文本格式的文件，就会用到mysqlbinlog日志管理工具