Mysql从0到1

MySQL参考手册

1 预备知识

1.1 安装

• 获取yum源rz : http://repo.mysql.com/

• 安装yum源：rpm -ivh mysql57-community-release-e17.rpm

• 安装mysql：yum install -y mysql-community-server

  • 报错：

    

    rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022

安装完后：

• 启动mysql： start mysqld

• 查服务端口号： netstat -nltp

1.2 Ubuntu下安装MySQL

1.3 登录 & 退出

mysql -u root -p

quit

• 初始化root密码

1.4 配置文件

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

//免密码登录
--skip-grant-tables  

2 基础知识

2.1 链接服务器

mysql -h 127.0.0.1 -P 3306 -u root -p

• -h：指明登录部署了mysql服务的主机
• -P：指明要访问的端口号
• -u：指明登录用户
• -p：指明需要输入密码

2.2 什么是数据库

• mysql 是数据库服务的客户端，mysqld是数据库服务的服务器端
• mysql本质是基于CS模式的一种网络服务
• 数据库一般指的是再磁盘或内存存储的特定结构的数据

2.3 基本使用

2.3.1创建表

• 查看数据库文件:

  • cd /var/lib/mysql
    

2.3.2 插入数据

2.4 服务器、数据库、表的关系

2.5 SQL分类

• DDL（data definition language）数据定义语言，用来维护存储数据的结构
  代表指令: create, drop, alter

• DML（data manipulation language）数据操纵语言，用来对数据进行操作
  代表指令： insert，delete，update

  • DML中又单独分了一个DQL，数据查询语言，代表指令： select

• DCL（data control language）数据控制语言，主要负责权限管理和事务
  代表指令： grant，revoke，commit

2.6 存储引擎

• 什么是存储引擎：数据库管理系统如何存储数据，如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法

• MyISAM 、 BDB 、 Memory 、 innoDB（最常用）、 Archive 、 NDB

• show engines \G；   #查看存储引擎
  

3 Mysql数据库的操作

3.1 创建和删除

• 创建 —— 本质是在 /var/lib/mysql创建一个目录

  • create [if not exists] database db_name;    #[]里面的是可选项	
    

• 删除 —— 本质是在 /var/lib/mysql删除一个目录

  • drop [if exists] database db_name;
    

3.2 字符集和校验规则

查看编码集（写）：

show variables like 'character_set_database';

查看校验集（读）：

show variables like 'collation_%';

• 创建一个使用utf-8字符集、并带校对规则的数据库（如果没有写，默认为utf-8）

create database dp_name charset=utf8 collate=utf8_general_ci;
create database dp_name character set utf8 collate utf8_general_ci;   #与  charset=utf8  一样

3.3 查看数据库

• 查看当前在哪一个数据库中

select database();

• 查看所有数据库

show databases;

3.4 修改数据库

alter database db_name charset=utf8   #对编码格式修改

3.5 备份和恢复

• 备份

mysqldump -P3306 -u root -B [数据库名1] [数据库名2] > [数据库备份存储的文件路径]  

mysqldump -P3306 -u root -B [数据库名] [表名1] [表名2] > [数据库备份存储的文件路径]    #备份同一个数据库中的不同表

• 恢复

source [数据库备份存储的文件路径]

3.6 数据库的链接情况

show processlist

4 表的操作 — create\alter\drop

4.1 创建表

CREATE TABLE [if not exists] table_name (
	field1 datatype comment [文字说明],
	field2 datatype,
	field3 datatype  
)character set [字符集] collate [校验规则] engine [存储引擎];

4.1.1 复制表

create table [new_table] like [old_table]; 

4.2 查看表

desc [表名]

show create table [表名] \G

4.3 修改表

¹

4.3.1重命名

• 改表名

alter table [表名] rename to [新表名] ;   #或者下面一种
rename table [] to [];  

• 改表中某一列的名字

alter table [表名] change [old_name] [new_name] [属性];

4.3.2 插入数据

insert into user values (1, 'yy', '1234', '2003-8-26');

4.3.3 向表中新增一列

alter table [表名] add [新增列名] [列属性] comment [说明] after [列名];

4.3.4 修改某一列的属性

alter table [表名] modify [列名] [列属性] comment [说明]; 

4.3.5 删除某一列

alter table [表名] drop [要删除的列名];   

4.4 删除表

drop table [表名]   

4.5 条件更改

• 将来自美国、澳大利亚、英国供应商的税率设置为0.08，其他供应商税率设置为0.00。

update Lineitem set tax = case
    when Lineitem.suppkey in (
    select suppkey from Lineitem, Supplier, Nation
    where Lineitem.suppkey = Supplier.suppkey and Supplier.nationkey = Nation.nationkey and Nation.name in ('美国', '澳大利亚', '英国')
    ) then 0.08
    else 0.00
    end;

5 数据类型

5.1 数值类型

tinyint、 smallint、 mediumint、 int、 bigint 分别占1、2、3、4、8个字节， 默认为有符号整数（无符号：tinyint unsigned、 smallint unsigned…）

• 和C/C++不同的是，Mysql中，值超出某一类型的数据范围，会直接报错（mysql中，数据类型是一种约束）

5.2 bit类型

[type_name] bit(M)    # M表示位数，范围[1,64], 默认为1

5.3 浮点数类型

5.3.1 float /double

[type_name] float(m, d)   
# m表示数字总个数（与是有符号或无符号无关），d表示小数位数
# 默认有符号 (m - d 表示整数的位数)
# 

²

³

5.3.2 decimal

• 基本用法与float类似，精度比float高

5.4 字符类型

5.4.1 char

type_name char(L)  
# L为字符的固定长度（一个汉字/字母视为一个字符）
# 最大长度为255

5.4.2 varchar

type_name varchar(L) 
# L表示字符长度
# 最大长度为65535字节（65535/3个字符， utf-8一个字符占3个字节）

• varchar中用1-3个字节表示内容总长度（“用多少给多少”），所以实际用于数据存储的字节最大有65532

5.5 日期类型

• 当表被更新时，timestamp自动更新成当前时间

5.6 enum 和 set 类型

• enum： 多选一，插入时只能写枚举的常量或者下标（从1开始）
• set ： 多选多，用n个位表示是否选取，1表示选，全0表示空串

建表

3表示二进制的011，即插入basketball和football

5.6.1 enum类型和set类型的选取 —— find_in_set

上述语句选取的是只包含’football‘的，而不是含有’football‘的

• find_in_set

select find_in_set('a', 'a,b,c');    # 0为false 非0为true（下标）

⁴

• 若要查询包含多项，可用and链接find_in_set

6 表的约束

• 通过约束，使插入数据库表中的数据如何符合预期

6.1 空属性约束

[type_name] [type] not null

• null（默认）或 not null
• 约束 not null 表示当插入数据时，该列不能为空

⁵

⁶

6.1.1 null的存储

如果列属性允许为null，那么会默认有一个字节的空间表示一列是否为空，一列用一个bit表示（超过八列加一个字节，以此类推）

6.2 default约束

• 如果同时约束了 not null 和 default ，在插入数据时可以忽略该列，即填入默认值
• 没有写默认值，那么默认值默认为NULL（没有not null 约束时）

[field_name] [type] default [defualt_value]   # 设置默认值

6.3 comment约束

• comment没有实际含义，用来描述字段，类似于注释

[type_name] [type] comment [描述]

6.4 zerofill

• 将数字剩余的宽度填充0

⁷

6.5 主键 —— primary key

• primary key 用来约束该字段里面的数据不能重复且不能为空， 一张表中最多只能有一个主键（不代表只能有一列为主键），主键所在的列通常是整数类型

• 去除主键

alter table [table_name] drop primary key; 

• 添加主键

alter table [table_name] add primary key([某一字段名])

• 复合主键（当多个字段值均相同时才报错）

6.6 auto_increment

• 自增长，每次插入后加一
• 一张表最多只能有一个自增长，必须配合主键使用

create table [table_name] (
	  id int primary key auto_increment,
    #...
) AUTO_INCREMENT=1000;  #默认为1

• 获取上一次auto_increment的值

select last_insert_id();

6.7 唯一键 —— unique key

create table [table_name] (
    id int unique key,
    #...
);

6.7.1 与主键的区别

• 唯一键可以为NULL，且可以多个为空
• 主键更多是标识唯一性；唯一键更多是保证不重复(配合primary key使用，保证主键之外的唯一性)

6.8 外键 —— references

• 外键约束主要定义在从表上，主表必须有主键或唯一键约束，定义外键后，要求外键列数据必须在主表的主键列存在或者为NULL

• 约束主表(master_table)与从表(slave_table)的关系

  • 例如主表为班级，从表为学生，通过外键将从表的某一列与主表建立联系

 foreign key([从表的某一列]) references [主表名]([主表某一列])

7 Mysql基本查询

7.1 insert —— 增

7.1.1 一次插入多行

insert into [table_name] values(/*...*/), (/*...*/), (/*...*/);

7.1.2 主键/唯一键冲突后修正

#若存在冲突，则更新
#若没有冲突，则插入values括号内的值
insert into [table_name] values() on duplicate key update []=[], []=[];

3. replace

#如果有冲突，删除之前的，新增一条
replace into [table_name] () values ();

7.2 select —— 查

7.2.1基本查询

#全列查询
select * from [table_name];
#选列查询
select [/*可以写表达式， 通过as重命名列*/] from [table_name];   

7.2.2 distinct 查询去重

select distinct [列名] from [table_name]

7.2.3 where条件

• 比较运算符

⁸

• 逻辑运算符
  • 可以用括号将多个逻辑条件分组

7.2.4 order by子句

• NULL表示最小值
• 默认升序
• 执行顺序是先筛选数据，最后排序

select [] from [table_name] order by [column_name1] [asc/desc], [column_name2] [asc/desc], ...
#多个排序，当第一个相同时，再通过第二个排...

例题：586. 订单最多的客户

select customer_number from Orders group by customer_number order by count(*) desc limit 1;

7.2.5 筛选分页结果 —— limit

#筛选前n条
select [] from [table_name] where ... limit n;   #[0,n)  ,表的每一行数据下标从0开始
#筛选第s到n条
select [] from [table_name] where ... limit s, n;     	  #[s, n)
select [] from [table_name] where ... limit n offset s;   #[s, n)

• 考虑表中数据不足limit数量的情况

7.3 update —— 改

• 对查询到的结果进行列值更新

update [table_name] set [column_name]=[expr] [where ...] ...;

7.4 delete —— 删

delete from [table_name];  #删除表内所有数据
delete from [table_name] where [column_name]=[expr]...;   #删除指定数据

delete from [table_name] order by [column] asc limit n;   #删除表内排序后的前n行数据

7.4.1 截断表

• 清空后会重置auto_increment项
• 速度比delete快，因为它不走事务，不记录日志

truncate table_name;     #与delete from [table_name]类似，都是清空表的数据

7.5 插入查询操作

insert into [table_name] select [column] from [table_name] ...;

7.7 分组查询&聚合函数

7.7.1 group by 分组查询

• 分组之后便于聚合统计
• group by 按照后一列的值进行分组，分成不同行
• 可以将多列联合分组

7.7.2 having

• 对分组聚合统计之后的数据进行筛选（类似于where）

#显示平均工资低于2000的部门及它的平均工资 
select department_no, avg(salary) department_avg_salary from employee group by department_no having department_avg_salary<2000; 

• having于where的区别？执行顺序？
  • where对任意列进行筛选，having对聚合之后的结果进行筛选
  • where先，having在group by之后

7.7.3 group by，where，having执行的先后顺序以及用法

在 MySQL 中，GROUP BY，WHERE，和 HAVING 子句的执行顺序如下：

1. WHERE 子句：WHERE 子句用于筛选行，仅选择符合条件的行。这是在聚合之前执行的，因此它可以用来过滤数据。
2. GROUP BY 子句：GROUP BY 子句将行分组为汇总行，并为每个组计算汇总值（如 COUNT, SUM, AVG 等）。GROUP BY 子句在 WHERE 子句之后执行。
3. HAVING 子句：HAVING 子句用于过滤分组后的结果。它在 GROUP BY 子句之后执行，并且只保留满足 HAVING 子句条件的分组。

这里有一个简单的示例来说明它们的用法：

sqlCopy codeSELECT department, COUNT(*) as total
FROM employees
WHERE salary > 50000
GROUP BY department
HAVING COUNT(*) > 1;

这个查询首先筛选出薪资大于 50000 的员工，然后按部门分组。最后，它仅保留至少有两名员工的部门，并计算每个部门的员工总数。

count

sum

avg

保留小数点后2位round(avg(xxx), 2)

max / min

• 511. 游戏玩法分析 I

select player_id, min(event_date) as first_login from Activity group by player_id ;

• 619. 只出现一次的最大数字

select max(t.num) as num from (select num from MyNumbers group by num having count(*) = 1) as t;

8 复合查询

8.1 多表查询

以下两张表为例

8.1.1 合并两张表（内连接）

#通过deptno相同合并两张表
select * from emp, dept where emp.deptno=dept.deptno;

8.2 自链接

#默认自链接方式
select * from emp as t1, emp as t1;

# 查找员工FORM的领导的名字和编号
# 1. 通过子查询
select ename, empno from emp where empno=(select mgr from emp where enmae='FORM');

# 2. 通过自链接表
select e2.ename, e2.empno from emp e1, emp e2 where e1.ename='FORM' and e1.mgr=e2.empno;

8.3 子查询

select [] from [] where ()(select [] from [] where ...);

8.3.1 单行子查询

# 显示SMITH同一部门的员工
select * from emp where deptno=(select deptno from emp where ename='SMITH');

8.3.2 多行子查询

三个关键字：

1. in/not in：筛选在一个集合中的列

# 查询与10号部门的工作岗位相同的员工的名字、岗位、工资、部门号，但是不包含10好部门自己的
select enmae, job, sal, deptno from emp where job in (select distinct job from emp where deptno=10) and deptno<>10;

2. all：所有

# 显示工资比30号部门的所有员工的工资高的员工的名字、工资、部门号
select ename, sal, deptno from emp where sal > all (select distinct sal from emp where deptno=30);

3. any：任意

# 显示工资比30号部门的任意一个员工的工资高的员工的名字、工资、部门号
select ename, sal, deptno from emp where sal > any (select distinct sal from emp where deptno=30);

8.3.3 多列子查询

# 与员工SMITH的部门和工作相同的其他员工的数据
select * from emp where (deptno, job)=(select deptno, job from emp where enmae='SMITH');

8.3.4 在from子句中使用子查询

上述子查询在where语句之后，充当判断条件；以下子查询出现在from子句中，将查询结果作为一张新的表

# 显示每个高于自己部门平均工资的员工的信息
select * from emp, (select deptno, avg(sal) avg_sal  from emp group by deptno) as tmp where emp.deptno=tmp.deptno and emp.sal>tmp.avg_sal;
 显示每个部门的信息和人员数量
select * from dept t1, (select deptno, count(*) dept_num from emp group by deptno) t2 where t1.deptno=t2.deptno;

8.4 合并查询

• union：求两个表的并集
• union all：直接拼接两个表

9 函数

9.1 日期函数

 #查看当前时间
select current_time();   

#想表中插入时间数据 (birthday 类型为date)
insert into [] (birthday) value (date(current_date()));   

#统计两分钟以内插入的数据
select content msg_time from [] where msg_time > date_sub(now(), interval 2 minute);

9.2 字符串函数

#用concat链接多列信息
select concat('姓名: ', name, '总分：', chinese + english, '语文成绩： '， chinese, '英语成绩', english) msg from exam_score;

9.3 数学函数

9.4 其他函数

9.5 窗口函数

SELECT
  S.score,
  DENSE_RANK() OVER (
    ORDER BY
      S.score DESC
  ) AS 'rank'
FROM
  Scores S;

10 表的内外链接

10.1 内连接

select 字段 from table1 inner join table2 on 连接条件 and 其他条件;

# 显示SMITH的名字和部门名称
# 之前合并表的写法
select ename, dname from emp, dept where emp.deptno=dept.deptno and ename='SMITH';

#标准的内连接写法
select ename, dname from emp inner join dept on emp.deptno=dept.deptno and ename='SIMTH';

10.2 外连接

10.2.1 左外连接

• 链接两张表，左侧表完全显示称为左外连接

select 字段 from table1 left join table2 on 链接条件; 

⁹

例题：577. 员工奖金

#注意判断空不能用   = null
#需要用          is null
select name, bonus from Employee left join Bonus on Employee.empid = Bonus.empid where bonus is null or bonus < 1000;

10.2.2 右外连接

select 字段 from table1 right join table2 on 连接条件;

11 索引

• 索引的设计原则

11.1 存储

• 在linux中，表结构在磁盘中就是文件 ，找到文件就是定位到对应的扇区（磁头->柱面->扇区）
• Mysql进行IO的基本单位为16KB（page）
• B+树：叶子节点保留数据，非叶子节点不要数 据，只保存目录项，叶子节点全部用双向链表连起来

11.2 聚簇索引和非聚簇索引

• innoDB和MyISAM都采用B+树
• MyISAM最大的特点是将索引page和数据page分离，也就是叶子节点没有数据，而存储数据地址 ，称为非聚簇索引

11.2.1 聚簇索引与二级索引

• innoDB叶子节点存放数据，称为聚簇索引， 二级索引叶子节点存放对应的主键
• 如果该表没有主键索引或者唯一索引，该表会在每一列生成一个隐藏的row_id作为聚集索引
• 一张表可能对应多个B+树

11.3 索引操作

• 查看索引结构

show index from [tablename]\G

11.3.1主键索引 —— primary key

1. 创建主键索引

#其他创建主键索引的方法写在表的约束中
alter table [] add primary key([]);

2. 删除主键索引

alter table [] drop paimary key;

11.3.2 唯一索引 —— unique

1. 其他索引的删除

alter table [] drop index []
drop index [索引名] on [表名]

2. 其他操作在表的约束中

11.3.3 普通索引

1. 创建

#1.在创建表时添加
#2.alter，以某一列为普通索引
alter table [] add index([]);
#3.创建索引并给索引起名
create index index_name on 表名(列名);

2. 删除与唯一键索引相同

11.3.4 复合索引(联合索引)

• 尽量使用联合索引，而非单列索引

11.3.5 全文索引

• 当文章字段或有大量文字的字段进行检索时，使用全文索引

create table articles (
	id int unsigned auto_increment not null primary key,
    title varchar(200),
    body text,
    fulltext(title, body)
)engine=MyISAM;

11.3.6 前缀索引

create index idx_name on table_name(cloumn(n));

• 查询选择性：字符串前n个不重复的比值，越大性能越高

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

• 查询步骤
  • 通过前缀索引找到id值
  • 通过id值找到一行的内容
  • 将改行该列的内容与where后面的条件比对，如果不相同，则通过叶子节点的链表往后找，相同则返回

11.4 性能分析

1. 查看SQL执行频率

   show [global|session] status like 'Com_______';
   

2. 慢查询日志 —— 定位耗时查询

开启后，当一条SQL超过了设置的long_query_time他就会记录到慢查询日志中

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

• 在配置文件/etc/my.cnf中配置相关信息:

  #开启MySQL慢查询日志
  slow_query_log = 1
  
  #设置慢查询日志时间
  long_query_time = 2
  

• 查看慢日志文件中记录的信息

  cat /var/lib/mysql/localhost-slow.log
  

3. profile —— 查看时间耗费在哪里

#是否支持
select @@have_profiling;

#是否开启
select @@profiling;

#开启
set [session|global] 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

4. explain

explain select ...;

• id：select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下; id不同，值越大，越先执行)。

• select_type

  表示SELECT的类型，常见的取值有SIMPLE〈简单表，即不使用表连接或者子查询)、PRIMARY（主查询，即外层的查询)、UNION(UNION中的第二个或者后面的查询语句）、SUBQUERY (SELECT/WHERE之后包含了子查询)）等

• type

  表示连接类型，性能由好到差的连接类型为NULL、system、const(通过主键或者唯一索引)、eq_ref、ref(非唯一索引)、range、index、all 。

• prossible_key

  显示可能应用在这张表上的索引，一个或者多个

• key

  实际用到的索引，没有则是NULL

• key_len

  表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。

• rows

  MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。

• filtered

  表示返回结果的行数占需读取行数的百分比,filtered的值越大越好。

11.5 索引失效

1. 联合索引最左前缀法则

如果索引了多列(联合索引)，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效(后面的字段索引失效)。与位置无关

2. 联合索引的范围查询

   

3. 对索引进行函数操作

4. 隐式转换

5. 左模糊匹配

6. or链接条件

11.6 SQL提示

11.7 覆盖索引

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

• extra信息：

  

12 事务

12.1 什么是事务？

• mysql是网络服务，多个客户端请求访问数据，mysql内部采用多线程 -> 事务锁？
• 事务是一组DML语句，这些语句在逻辑上具有相关性，要么全部成功，要么全部失败
• 一个完整的事务满足以下四个属性
  • 原子性(Atomicity)：对于所有操作保证原子性，一次性全部完成
  • 一致性(Consistency)：写入数据必须完全符合所有的预设规则，保证数据的精确度，可预期 —— redo log + undo log
  • 隔离性(Isolation)：未提交、读提交、可重复读、串行化？ —— undo log + readView + 隐藏字段 + 锁
  • 持久性(Durability)：保证数据的修改是永久的 —— redo log

哪些存储引擎支持事务？

• 设置隔离级别为：读未提交

set global transaction isolation level READ UNCOMMITTED;

• 查看链接mysql服务的用户

show processlist;

12.2 事务的提交方式

事 物的提交方式有两种：自动提交、手动提交

#查看提交方式
show variables like 'autocommit';
#修改提交方式
set autocommit=0;

12.3 事务的基本操作

create table if not exists account (
	id int primary key,
    name varchar(20) not null default'',
    balance decimal(10,2) not null default 0.0
)engine=InnoDB default charset=utf8;

#1. 启动事务
start transaction;  
#或
begin;

#2. 设置保存点s1
savepoint s1;

#3. 插入
insert into account values(1, 'xx', 1234);

#4. 回滚，事务提交之后无法回滚
rollback to s1;

#5. 结束
commit;

12.4 事务异常

• 只要输入begin或者start transaction，事务便需要通过commit提交之后才会持久化，与是否开启自动提交无关
• 当操作异常时，mysql会自动回滚
• 单独一句sql会被打包成事务，当自动提交打开时，mysql异常退出后，该sql语句会自动提交，关掉之后，需要手动commit

12.5 事务的隔离级别

12.5.1 理解 隔离性与隔离级别

• 隔离性：数据库中，为了保证事务执行过程中尽量不受干扰，区分事务谁先执行，谁在执行，一个执行的事务时原子的，相互隔离

• 隔离级别：数据库中，允许事务受不同程度的干扰

  • 读未提交(read uncommitted)：两个事务，当一个事务对数据进行修改但未提交，第二个事务立马能看见修改（隔离级别最低）

    • dirty read：一个事务在执行中读到另一个执行中事务的更新（或其他操作）但是未commit的数据的现象

    

  • 读提交(read committed )：一端提交之后，另一端才能看见修改

    • 不可重复读（non repeatable read)：用一个事务内，在不同时间段独到的数据不同

    

    • 幻读：当同一个查询在不同的时间产生不同的结果集时，事务中就会出现所谓的幻象问题。例如，如果 SELECT 执行了两次，但第二次返回了第一次没有返回的行，则该行是“幻像”行。

      

  • 可重复读(repeatable read)：一端提交之后，另一端需要也提交或者结束才能看见修改（默认隔离级别）

  • 串行化(serializable)：在每个读的数据行上加上共享锁，强制事务排序，使之不可能互相冲突（隔离级别最高）

12.6 查看与设置隔离级别

1. 查看隔离级别

select @@global.tx_isolation;  #全局

select @@session.tx_isolation;  #仅该会话（局部）

2. 设置

#[]内可不写， {}内四选一
set [session|global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable};

#设置完全局之后，重新登录之后，局部和默认的隔离级别通过全局初始化

RC与RR的本质区别

• RC级别下，事务中每次快照读都会新生成一个Read View
• RR级别下，同一个事务下的第一次快照才会创建Read View

13 视图/存储过程/触发器

13.1 视图

视图是一个虚拟表，其内容由表查询定义。

视图的作用：操作简单，安全， 数据独立（屏蔽了基表的变化）

#语法
create view [view_name] as [基表];
create [or replace] view 视图名称[(列明列表)] as select语句 [with [cascaded | local] check option];


#E.G.
create view ename_dname as select ename, dname from emp inner join dept on emp.deptno=dept.deptno;

• 在视图中插入/删除数据

13.1.1 视图的检查选项

cascaded

• 如果当前视图加了with cascaded check option，那么插入时该视图及之上的视图都会检查

local

• 如果当前视图加了with local check option，那么插入时只检查当前视图有没有检查选项，对于上层有则检查，没有则不检查（MySQl8.0）

  

create or replace view stu_v_4 as select id , name from student where id <= 15 ;
insert into stu_v_4 values(5 , 'Tom');		#ok
insert into stu_v_4 values(16, 'Tom');		#error

create or replace view stu_v_5 as select id, name from stu_v_4 where id >= 10 with local check option ;
insert into stu_v_5 values (13, ' Tom ' );		#ok
insert into stu_v_5 values(17,' Tom ' );   		#ok ： 不检查id <= 15

13.1.2 视图的更新

13.2 存储过程

• 特点：封装，复用；可以接受参数，也可以返回数据；减少网络交互，提升效率

delimiter $$

#创建
create procedure 存储过程名称([参数列表])
begin
	...    #在命令行中遇到分号就结束，所以这里需要用delimiter设置结束符
end;

delimiter ;

#调用
call 名称([参数]);

#查看
#查看xxx数据库的存储过程
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'xxx';	
#查询存储过程的内容
show create procedure 名称;	

#删除
drop procedure [if exists] 名称;

13.2.1 变量

系统变量

#查看系统变量
SHOW [SESSION | GLOBAL] VARIABLES;
SHOW [SESSION | GLOBAL] VARIABLES LIKE 'xxx%';
SELECT @@[SESSION. |GLOBAL.]系统变量名;

#设置系统变量
SET [SESSION | GLOBAL] 系统变量名 = 值;
SET @@[SESSION | GLOBAL] 系统变量名 = 值;

• 如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量。
• mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在/etc/tany.cnf 中配置。

用户自定义变量

#赋值
赋值
SET @var_name1 = expr [@var_name2 = expr ] ... ;
SET @var_name1 := expr [@var_name2 := expr] ... ;

SELECT @var_name1 := expr [@var_name2 := expr] ...;
SELECT 字段名 INTO @var_name FROM 表名;

#使用
SELECT @var_name;

• 用户定义的变量无需对其进行声明或初始化，初始值为NULL

局部变量 —— declare

create procedure p2()
begin
	declare stu_count into default 0;
	select count(*) into stu_count from student;
	select stu_count;
end;

call p2();

参数

#eg.1
#判断成绩
create procedure p4(in score int, out result varchar(10))
begin
	if score >= 85 then
		set result := '优秀';
	elseif score >= 60 then
		set result := '及格';
	else 
		set result := '不及格';
	end if;
end;

call p4(100, @result);
select @result;

#eg.2 
#将两百分值换算成百分制
create procedure p5(inout score double)
begin
	set score := score * 0.5;
end;

set @score = 150;
call p5(score);
select score;

13.2.2 流程控制语句

if

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

case

• 610. 判断三角形

  SELECT 
      x,
      y,
      z,
      CASE
          WHEN x + y > z AND x + z > y AND y + z > x THEN 'Yes'
          ELSE 'No'
      END AS 'triangle'
  FROM
      triangle
  ;
  

while

#eg.1
#计算1-n的和
create procedure p7(in n int) 
begin
	declare total int default 0;
	while n>0 do
		set total := total+n;
		set n := n - 1;
	end while;
end;

repeat

• 满足条件退出循环

#语法
repeat
	...
	until 条件
end repeat;

loop

#eg.1
# 计算1-n中偶数的总和
create procedure p9(in n int) 
begin
	declare total int default 0;
	
	sum: loop
		if n <= 0 then 
			leave sum;
		end if;
		if n % 2 = 1 then 
			n := n - 1;
			iterate sum; 
		end if;
		set total := total + n;
		n := n - 1;
	end loop sum;
	
end;

13.2.3 游标(光标）—— cursor

#eg.1
#将年龄小于uage的行插入到一个新表当中
create procedure p11(in uage int)
begin
	#需要先声明变量再声明游标
	declare uname varchar(100);
	declare upro varchar(100);
	declare u_cursor cursor for select name , profession from tb_user where age <=uage;

	
	drop table if exists tb_user_pro;
	create table if not exists tb_user_pro(
        id int primary key auto_increment,
        name varchar(100),
        profession varchar(100)
   	);
   	
   	open u_cursor;
   	while frue do
   		fetch u_cursor into uname , upro;     #error:考虑判断游标里没有数据
   		insert into tb_user_pro values (null, uname,upro);
   	end while;
   	close u_cursor;
   
end;

13.2.4 条件处理程序

#eg.1
#将年龄小于uage的行插入到一个新表当中
#解决前一个例子的报错问题
create procedure p11(in uage int)
begin
	#需要先声明变量再声明游标
	declare uname varchar(100);
	declare upro varchar(100);
	declare u_cursor cursor for select name , profession from tb_user where age <=uage;
	#用于退出循环
	declare exit handler for SQLSTATE '02000' close u_cursor;  
	#declare exit handler for not found close u_cursor;  
	
	drop table if exists tb_user_pro;
	create table if not exists tb_user_pro(
        id int primary key auto_increment,
        name varchar(100),
        profession varchar(100)
   	);
   	
   	open u_cursor;
   	while true do
   		fetch u_cursor into uname ,upro;     #当游标没有数据时报错，被条件处理程序捕获。退出游标
   		insert into tb_user_pro values (null, uname,upro);
   	end while;
   	close u_cursor;
   
end;

13.2.5 存储函数

• 有返回值的存储过程，参数只能是IN类型（默认也是IN）

#求1-n的总和
create function func(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 func(10);

13.3 触发器

• 行级触发：影响多少行，触发多少次
• 语句级触发：一次sql之触发一次

#创建
CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE ON tbl_name FOR EACH ROW #行级触发器
BEGIN
	...
END;

#查看
SHOW TRIGGERS ;

# 删除
DROP TRIGGER [schema_name.]trigger_name; #如果没有指定schema_name，默认为当前数据库。

14 用户管理

• root初始化密码

14.1 用户操作

use musql;
#查看主机、用户、密码
select host, user, authentication_string from user;

#创建一个在本地登录的用户，不能远程登陆，Dusong 密码：123456
create user 'Dusong'@'localhost' identified by '123456';   

#刷新
flush privileges;

#删除用户
drop user '用户名'@'主机名';

#创建可以远端登录的用户
create user 'Dusong'@'%' identified by '123456';

#自己该自己的密码
set password=password('xxxx');

#root用户改别人的密码 
#1.
set password for '用户名'@'主机名'=password('xxxx');
#2.
update user set authentication_string=password('xxx') where user='用户名';

14.2 用户权限

#给某用户权限
grant 权限列表 on 库.表名 to '用户名'@'主机名'

# 给某用户所有权限
grant all [privileges] on ...;

#查看某个用户的权限
show grant for ''@'';

#去掉用户的某个权限
revoke [] on 库.表名 from ''@''      
#eg. revoke delete,insert on rootDB.user from 'Dusong'@'%';

15 C/C++引入MySQL客户端库

#mysql动静态库
ls /usr/lib64/mysql/
#mysql头文件
ls /usr/include/mysql/

//查看客户端版本
mysql_get_client_info()

15.1 C API

• 初始化

#include <mysql/mysql.h>
#include <iostream>

int main() {
    //初始化
	MYSQL *myfd = mysql_init(nullptr);
    
    //链接, 返回nullptr则链接失败
    mysql_real_connect(myfd, "127.0.0.1", "Dusong", "123456", "test_db", 3306, nullptr, 0); 
    
    //设置字符集
    mysql_set_character_set(myfd, "utf8"); 
    
    //sql
    mysql_query(myfd, "select * from user");  //查询user表
    
    //获取结果
    MYSQL_RES *res = mysql_store_result(myfd);
        
    //关闭连接 
    mysql_close(myfd);
}

• 链接数据库

• 设置字符集，默认时latin1，而mysqld使用的utf8

mysql_set_character_set(myfd, "utf8");

• 下发mysql命令

• 获取查询结果集 （select）

  

  • 获取行数：my_ulonglong mysql_num_rows(MYSQL_RES *res)
  • 获取列数：my_ulonglong mysql_num_fields(MYSQL_RES *res)

• 打印结果

my_ulonglong row = mysql_num_rows(res);
my_ulonglong col = mysql_num_fields(res);

//属性
MYSQL_FIELD *fields = mysql_fetch_fields(res);
for (int i = 0; i < col; i++) {
    cout << fields[i].name << '\t';
}
cout << '\n';


//内容
for (int i = 0; i < row; i++) {
    MYSQL_RES row_res = mysql_fetch_row(res);
    for (int j = 0; j < col; j++) {
        cout << row_res[j] << '\t';
    }
    cout << '\n';
}

• 释放结果集的空间

16 SQL优化

16.1 insert优化

1. 批量插入

insert into tb_test values(1,'Tom'),(2,'Cat'),(3,"Jerry');

2. 手动事务提交

start transaction;
insert into tb_test values(1, 'Tom'),(2,'Cat'),(3,'Jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
commit;

3. 主键顺序插入(主键优化)

主键乱序插入:8 1 9 21 88 24 15 89 5 7 3

主键顺序插入:1 2 3 4 5 7 8 9 15 21 88 89

4. 大批量插入数据

mysql --local-infile -u root -p
 
select @@local_infile

set global local_infile = 1;

load data local infile '/root/xx.log' into table 'xxx' fields terminated by ',' lines terminated by '\n';

16.2 主键优化

16.2.1 页分裂——主键乱序插入

此时插入id为50的行，发生页分裂，开辟一个新的数据页3#，将1#号数据页的50%移动到3#，最后链接数据页

16.2.2 页合并——删除数据

• MERGE_THRESHOLD：合并页的阈值，在创建表或者索引时可指定

16.2.3 主键的设计原则

1. 满足业务需求的情况下，尽量降低主键的长度。
2. 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
3. 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
4. 业务操作时，避免对主键的修改。

16.3 order by优化

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

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

1. 对于一个默认升序的联合索引，如果在order by时一个升序一个降序，将会走using index 和 using filesort

• show index from xxx中的Collation列为A(asc)表示升序，D(desc)表示倒序，可以在建立索引时指定：

  create index idx_user_age_pho_ad on tb_user(age asc, phone desc);
  

2. 尽量使用覆盖索引，不然都会使用using filesort

3. 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)。

   show variables like 'sort_buffer_size';
   

16.4 group by 优化

• 通过索引进行优化，分组时索引的使用也满足最左前缀法则

16.5 limit 优化

• 通过覆盖索引 + 子查询优化

explain select * from tb_sku t, 
(select id from tb_sku order by id limit 2000000, 10) a
where t.id = a.id;

16.6 count优化

count(常量)、count(字段名/主键)、count(*)的区别

• count(常量)、count(*)表示查询符合条件的行数
• count(字段名)查询符合条件的列的值不为NULL的行数

16.7 update优化

事务中，根据索引进行update使用的是行锁，反之则用的是表级锁，行锁升级为表锁会降低并发性

17 锁

17.1 全局锁

• 备份表，不加锁的情况：

• 加锁的情况

flush tables with read lock;	#加锁


unlock tables;		#解锁

#备份
mysqldump -uroot -p123123 itcase > itcast.sql

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

mysqldump --single-transaction -uroot -p123123 itcase > itcast.sql

17.2 表级锁

17.2.1 表锁

1. 表共享读锁（read lock）：共享读，不能写
2. 表独占写锁（write lock）：只有自己能读写

#加锁
lock tables xxx read;
lock tables xxx write;

#解锁
unlock tables;  #或者客户端断开连接

17.2.2 元数据锁(meta data lock, MDL)

• 共享锁与排他锁互斥的情况

#查看元数据锁
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;

17.2.3 意向锁

1. 意向共享锁（IS)∶
   1. 由语句select ... lock in share mode添加。
   2. 与表锁共享锁( read）兼容，与表锁排它锁（ write)互斥。
2. 意向排他锁（IX)︰
   1. 由insert、update、delete、select ... for update添加。
   2. 与表锁共享锁(read）及排它锁(write）都互斥。意向锁之间不会互斥。

为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

• 线程A在update时加行锁并且加上意向锁，此时线程B加表锁会被阻塞

• 查看意向锁及行锁的加锁情况

  select object_schema,object_name,index_name, lock_type,lock_mode,lock_data from performance_schema.data_locks;
  

17.3 行级锁

17.3.1 行锁

默认情况下，InnoDB在 REPEATABLEREAD事务隔离级别运行，InnoDB使用**next-key锁**进行搜索和索引扫描，以防止幻读。

1. 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
2. InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁

17.3.2 间隙锁&临键锁

1. 索引上的等值查询(唯一索引)，给不存在的记录加锁时,优化为间隙锁。

2. 索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock退化为间隙锁

• 因为普通索引不唯一，防止其他事务在间隙中插入相同的索引值，需要加上间隙锁

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

begin;

select * from stu where id >= 10 lock in share mod;

end;

• 间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

18 InnoDB存储引擎

18.1 内存结构

1. Buffer Pool

2. Change Buffer

3. 自适应哈希

4. Log Buffer

18.2 磁盘结构

18.3 后台线程

18.4 事务原理

18.4.1 redo log —— 一致性

18.4.2 undo log —— 原子性&MVCC

18.5 MVCC

基本概念

隐藏字段

• 查看idb文件信息

  idb2sdi xxx.idb
  

undo log

回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。

• insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。

• update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。

read view

• read view 生成的时机：

  • RC：每一次执行快照读都会生成

  

  • RR：只在第一次快照读的时候生成，后面快照读复用

    

19 MySQL管理

19.1 系统数据库

19.2 常用工具

1. mysql

2. mysqladmin

3. mysqlbinlog

4. mysqlshow

5. mysqldump

6. mysqllimport / source

20 其他

• 因为普通索引不唯一，防止其他事务在间隙中插入相同的索引值，需要加上间隙锁

[外链图片转存中…(img-moCzQw7z-1725528538918)]

[外链图片转存中…(img-LQ85eBjU-1725528538918)]

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

begin;

select * from stu where id >= 10 lock in share mod;

end;

• 间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

18 InnoDB存储引擎

[外链图片转存中…(img-Onr80GEd-1725528538918)]

18.1 内存结构

1. Buffer Pool

[外链图片转存中…(img-m6Mhdnmq-1725528538919)]

2. Change Buffer

[外链图片转存中…(img-ISl0bhTs-1725528538919)]

3. 自适应哈希

[外链图片转存中…(img-N0JBXlo7-1725528538919)]

4. Log Buffer

[外链图片转存中…(img-fanYVNRi-1725528538920)]

18.2 磁盘结构

[外链图片转存中…(img-lRXY2769-1725528538920)]

[外链图片转存中…(img-PEWegT9g-1725528538920)]

[外链图片转存中…(img-LJP5ucZL-1725528538920)]

18.3 后台线程

[外链图片转存中…(img-Eu9hJhJL-1725528538921)]

18.4 事务原理

[外链图片转存中…(img-8u4mwGS2-1725528538921)]

18.4.1 redo log —— 一致性

[外链图片转存中…(img-y4CGiSyr-1725528538921)]

18.4.2 undo log —— 原子性&MVCC

[外链图片转存中…(img-Fb4mWrsz-1725528538921)]

18.5 MVCC

基本概念

[外链图片转存中…(img-Ycx72cg0-1725528538921)]

隐藏字段

[外链图片转存中…(img-dGPYPvdM-1725528538922)]

• 查看idb文件信息

  idb2sdi xxx.idb
  

undo log

回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。

• insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。

• update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。

[外链图片转存中…(img-lXoLndBZ-1725528538922)]

read view

[外链图片转存中…(img-aTLqaARq-1725528538922)]

[外链图片转存中…(img-yHtG53R5-1725528538922)]

• read view 生成的时机：

  • RC：每一次执行快照读都会生成

  [外链图片转存中…(img-IVMSOlRK-1725528538923)]

  • RR：只在第一次快照读的时候生成，后面快照读复用

    [外链图片转存中…(img-6GnrkZzy-1725528538923)]

19 MySQL管理

19.1 系统数据库

[外链图片转存中…(img-981t4izF-1725528538923)]

19.2 常用工具

1. mysql

[外链图片转存中…(img-hGS7IvBC-1725528538923)]

2. mysqladmin

[外链图片转存中…(img-qo6WpRRl-1725528538924)]

3. mysqlbinlog

[外链图片转存中…(img-9b16dLqP-1725528538924)]

4. mysqlshow

[外链图片转存中…(img-EpkKGzkn-1725528538924)]

5. mysqldump

[外链图片转存中…(img-C1j0sIge-1725528538924)]

6. mysqllimport / source

[外链图片转存中…(img-73KaXiah-1725528538924)]

20 其他

[外链图片转存中…(img-jmyP8uZq-1725528538925)]