MySQL学习笔记——By Bug

文档说明

笔记开始日期：2023年12月1日 笔记最新更新日期：2024年1月25日 笔记更新状态：MySQL基础和MySQL进阶已完结，MySQL运维待日后择期更新。

你好：

首先感谢你使用这份笔记手册，一起学习，一起进步，本学习笔记是我在自学过程（网课视频在下方链接）中的随手笔记，可能出现遗漏，顺序错误或语法，单词等错误，你可以在自己的学习过程中对这份笔记更正即可。

学习参考网课资料资料：https://www.bilibili.com/video/BV1Kr4y1i7ru/ ​ 相关资料可在黑马程序员官网或视频下方评论区下载

本笔记文档所用到的软件 ​ MySQL版本：8.0.35 MySQL Community server-GPL ​ 可视化开发工具：DataGrip-2023.3.2 ​ MySQL官方说明文档：MySQL :: MySQL Documentation

由于图片路径问题，笔记中的图片可能出现无法正常显示得到问题，请谅解。

说明到此结束下面是正文内容 ：

MySQL准备

windows安装MySQL

待补充

Linux系统安装MySQL(生产环境)

1. 准备一台Linux服务器

云服务器或者虚拟机都可以;

Linux的版本为 CentOS7;

虚拟机安装linux系统方法可自行搜索。

2. 下载Linux版MySQL安装包

https://downloads.mysql.com/archives/community/

3. 上传MySQL安装包

4. 创建目录,并解压

xxxxxxxxxx
3
1
mkdir mysql
2
192.168.237.129
3
tar -xvf mysql-8.0.26-1.el7.x86_64.rpm-bundle.tar -C mysql

5. 安装mysql的安装包

xxxxxxxxxx
20
1
cd mysql
2

3
rpm -ivh mysql-community-common-8.0.26-1.el7.x86_64.rpm 
4

5
rpm -ivh mysql-community-client-plugins-8.0.26-1.el7.x86_64.rpm 
6

7
rpm -ivh mysql-community-libs-8.0.26-1.el7.x86_64.rpm 
8
若提示mariadb-libs 被 mysql-community-libs-8.0.26-1.el7.x86_64 取代
9
执行yum remove mysql-libs
10

11
rpm -ivh mysql-community-libs-compat-8.0.26-1.el7.x86_64.rpm
12

13
yum install openssl-devel
14

15
rpm -ivh  mysql-community-devel-8.0.26-1.el7.x86_64.rpm
16

17
rpm -ivh mysql-community-client-8.0.26-1.el7.x86_64.rpm
18

19
rpm -ivh  mysql-community-server-8.0.26-1.el7.x86_64.rpm
20

6. 启动MySQL服务

xxxxxxxxxx
1
1
systemctl start mysqld

xxxxxxxxxx
1
1
systemctl restart mysqld

xxxxxxxxxx
1
1
systemctl stop mysqld

7. 查询自动生成的root用户密码

xxxxxxxxxx
1
1
grep 'temporary password' /var/log/mysqld.log

命令行执行指令 :

xxxxxxxxxx
1
1
mysql -u root -p

然后输入上述查询到的自动生成的密码, 完成登录 .

8. 修改root用户密码

登录到MySQL之后，需要将自动生成的不便记忆的密码修改了，修改成自己熟悉的便于记忆的密码。

xxxxxxxxxx
1
1
ALTER  USER  'root'@'localhost'  IDENTIFIED BY '123456';

执行上述的SQL会报错，原因是因为设置的密码太简单，密码复杂度不够。我们可以设置密码的复杂度为简单类型，密码长度为4。

xxxxxxxxxx
2
1
set global validate_password.policy = 0;
2
set global validate_password.length = 6;

降低密码的校验规则之后，再次执行上述修改密码的指令。

9. 创建用户

默认的root用户只能当前节点localhost访问，是无法远程访问的，我们还需要创建一个root账户，用户远程访问

xxxxxxxxxx
1
1
create user 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

10. 并给root用户分配权限

xxxxxxxxxx
1
1
grant all on *.* to 'root'@'%';

11. 重新连接MySQL

xxxxxxxxxx
1
1
mysql -u root -p

然后输入密码

12. 通过DataGrip远程连接MySQL

MYSQL下载与安装

MySQL官网

SQL图形化工具下载

下载

专业版收费，社区版免费

DataGrip

DBeaver Community

安装

如需安装激活，请自行百度激活码和激活工具

MySQL基础

SQL注释语法

单行注释：--注释内容或#注释内容

多行注释：/注释内容/

SQL分类

1. DDL

2. DMI

3. DQL

4. DCL

MySQL数据库启动与停止

方法1： WIN+R打开运行，输入services.msc，找到MySQL80，选择启动或停止

方法2： 打开命令行（cmd），输入下面的指令 启动：net start mysql80 停止：net stop mysql80

MySQL客户端连接

方法1：使用MySQL提供的客户端命令行工具

方法2：使用windows的命令行工具连接 打开CMD控制台：输入mysql [-h 127.0.0.1] [-p 3306] -u root -p

[-h 127.0.0.1] [-p 3306]可以省略

数据模型

关系型数据库（RDBMS）： 概念：建立在关系模型基础上的，由多张相互连接的二维表组成的数据库；

特点：

1. 使用表存储数据，格式统一，便于维护。

2. 使用SQL语言操作，标准统一，使用方便。

DDL数据库操作

查询

查询所有数据库：

xxxxxxxxxx
1
1
SHOW DATABASES;

查询当前数据库：

xxxxxxxxxx
1
1
SELECT DATABASE();

创建

xxxxxxxxxx
1
1
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则]

删除

xxxxxxxxxx
1
1
DROP DATABASE[IF EXISTS]数据库名;

使用

xxxxxxxxxx
1
1
USE 数据库名；

DLL表操作

创建

xxxxxxxxxx
8
1
CREATE TABLE 表名{
2
字段1 字段类型[COMMENT 字段1注释],
3
字段2 字段类型[COMMENT 字段2注释],
4
字段3 字段类型[COMMENT 字段3注释],
5
......
6
字段n 字段类型[COMMENT 字段n注释]
7
}[COMMENT 表注释];
8
#最后一个不要加，

例如：创建一个员工信息表

xxxxxxxxxx
8
1
create table emp{
2
id int comment '编号',
3
name varchar(10) comment '姓名',
4
genner char(1) comment '年龄',
5
age tinyint unsined comment '年龄',
6
idcard char(18) comment '身份证号',
7
entrydate date comment '入职时间'
8
}comment '员工表’;

添加修改删除

添加字段

xxxxxxxxxx
1
1
alter table 表名 add 字段名 类型(长度) [comment 注释][约束];

修改数据类型

xxxxxxxxxx
1
1
alter table 表名 modify 字段名 新数据类型(长度);

修改字段名和字段类型

xxxxxxxxxx
1
1
alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释][约束];

案例：

xxxxxxxxxx
1
1
alter table emp change nickname username varchhar(30) comment'昵称'

删除字段：

xxxxxxxxxx
1
1
alter table 表名 drop 字段名;

案例：删除emp中的username

xxxxxxxxxx
1
1
alter table emp drop username;

修改表名：

xxxxxxxxxx
1
1
alter table 表名 rename 新表名;

案例：

xxxxxxxxxx
1
1
alter table emp rename to employee;

删除表：

xxxxxxxxxx
4
1
#方法1：删除表(常用这个）
2
drop table [if exists]表名;
3
#方法2：删除指定表，并重新创建该表；
4
truncate table 表名;

数据类型

数值类型：

字符串类型：

日期类型：

DML操作（操作数据）

查询

xxxxxxxxxx
10
1
#查询表中前三条数据
2

3
select * from 表名 limit 3;
4

5
#查询表中从第二条数据开始，向后数3条
6

7
select * from student limit 1,3;
8

9
#查询所有数据
10
select * from 表名;

添加数据

1. 给指定字段添加数据

   xxxxxxxxxx
   1
   1
   insert into(字段1，字段2，....) values (值1,值2,...)
   

2. 给全部字段添加数据

   xxxxxxxxxx
   1
   1
   insert into 表名 values(值1,值2,...)
   

3. 批量添加数据

   xxxxxxxxxx
   1
   1
   insert into 表名(字段1,字段2,...) values(值1,值2,...),(值1,值2,...),(值1,值2,...)
   

   xxxxxxxxxx
   1
   1
   insert into 表名 values()(值1,值2,...),(值1,值2,...),(值1,值2,...)
   

修改数据

xxxxxxxxxx
1
1
update 表名 set 字段名1 = 值1,字段名2=值2，...[where 条件];

删除数据

xxxxxxxxxx
1
1
delete from 表名 where 条件

DQL-操作（数据查询语言）

DQL语法

xxxxxxxxxx
7
1
select #字段列表
2
from #表名
3
where #条件列表
4
group by #分组字典列表
5
having #分组后条件列表
6
order by #排序字段列表
7
limit #分页参数

基本查询

xxxxxxxxxx
8
1
#查询多个字段
2
select 字段1,字段2,字段n from 表名;
3
select * from 表名; #查询全部数据
4
#查询某个字段并为其起一个别名
5
select 字段1 as 'name' from 表名;
6
select 字段1 'name' from 表名;
7
#去除重复数据查询
8
select distinct 字段1 from 表名;

条件查询

基本语法：

xxxxxxxxxx
1
1
select 字段列表 from 表名 where 条件;

条件：

xxxxxxxxxx
17
1
#案例
2
-- 查询年龄为20岁的人
3
select * from user where age = 20;
4
-- 查询年龄小于20岁的人
5
select * from user where age < 20;
6
-- 查询没有身份证号的人
7
select * from user where idcard is null;
8
-- 查询年龄在15-20岁的人
9
select * from user where age >=15 && age <=20;
10
select * from user where age >=15 and age <=20;
11
select * from user where between 15 and 20;
12
-- 身份证号最后一位为X的人
13
select * from user where idcard like '%X';
14
-- 查询姓名为2个字符的人
15
select * from user where name like '__';
16
-- 查询姓名为3个字符的人
17
select * from user where name like '___';

聚合函数

常见的聚合函数

xxxxxxxxxx
12
1
# 聚合函数案例
2
# 注意 所有null不参与聚合函数的运算
3
-- 1.统计员工数量
4
select count(*) from user;
5
-- 2.统计平均年龄
6
select avg(age) from user;
7
-- 3.统计最大年龄
8
select max(age) from user;
9
-- 4.统计最小年龄
10
select min(age) from user;
11
-- 5.统计员工年龄之和
12
select count(age) from user;

分组查询

语法:

xxxxxxxxxx
2
1
#分组查询的基本语法
2
select 字段列表 from 表名[where 条件] group by 分组字段名 [having 分组后过滤条件];

where 和 having 的区别

• 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对分组结果进行过滤。

• 判断条件不同：where不能对聚合函数进行判断，而having可以；

xxxxxxxxxx
6
1
# 根据性别分组 统计男员工和女员工的数量
2
select genner,count(*) from emp group by genner;
3
# 根据性别分组 统计男性员工和女性员工平均年龄
4
select genner,avg(age) from emp group by genner;
5
# 查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址；
6
select * from emp where age<45 group by workaddress having count(*)>=3;

排序查询

基本语法

xxxxxxxxxx
1
1
select 字段列表 from 表名 order by 字段1 排序方式1,字段2 排序方式2; # (支持多字段排序)

排序方式

• ASC：升序；（默认）

• DESC：降序

• 注意：！若多字段排序，第一个字段值相同时，才会根据第二个字段排序

xxxxxxxxxx
2
1
# 根据年龄排序，若年龄相同 则按照入职时间进行降序排序
2
select * from users order by age asc, joindate desc;

分页查询

xxxxxxxxxx
2
1
# 基本语法
2
select 字段列表 from 表名 limit 起始索引,查询记录数;

注意：

1. 起始索引是从0开始的,起始索引=(页码-1)*每页显示记录数

2. 分页查询时数据库的方言，不同数据库有不同的语法，MySQL是limit

3. 如果查询的是第一页的数据，起始索引可以省略，直接写limit 10;

xxxxxxxxxx
6
1
# 查询前三条数据
2
select * from 表名 limit 3;
3
# 查询第一页的数据,每页展示10条
4
select * from 表名 limit 0,10;
5
# 查询第2页的数据,每页展示10条
6
select * from 表名 limit 10,10;

DQL执行顺序

from→where→group by→select→order by→limit

DQL语法练习案例

xxxxxxxxxx
10
1
# 查询员工年龄为20 21 22 23岁的女员工信息
2
select * from users where genner ='女' and in(20,21,22,23);
3
# 查询性别为男，并且年龄再20-40岁的姓名为3个字的员工
4
select * from users where age between 20 and 40 && genner = '男' and name like '___'
5
# 统计表中年龄小于60的男员工和女员工的人数
6
select genner, count(*) from users where age <= 60 group by genner ;
7
# 查询所有年龄小于等于35岁的员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序。
8
select name,age from users order by age asc, joindate desc;
9
#查询性别为男，且年龄在20-40岁以内的前五个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序。
10
select * from users where genner = '男' and age between 20 and 40 order by age,joindate desc limit 5;

DCL-管理用户

1. 查询用户

xxxxxxxxxx
2
1
use mysql;
2
select * from users;

2. 创建用户

xxxxxxxxxx
1
1
create users '用户名'@'主机名' identified by '密码';

3. 修改用户密码

xxxxxxxxxx
1
1
alter users '用户名'@'主机名' identified with musql_password by '新密码';

4. 删除用户

xxxxxxxxxx
1
1
drop users '用户名'@'主机名';

函数

字符串函数 (常用)

使用语法：

xxxxxxxxxx
1
1
select 函数(参数);

xxxxxxxxxx
2
1
-- 业务变更，修改企业员工工号长度为5位，不足五位的在前面补0
2
update 表名 set workno = lpad(workno,5,'0')

数值函数

xxxxxxxxxx
1
1
select 函数(参数);

xxxxxxxxxx
2
1
-- 生成6位随机数
2
select lpad(ceil(rand()*1000000),6,'0')

日期函数

xxxxxxxxxx
3
1
# date_add(date,interval expr type)语法应用
2
select date_add(now(),interval 70 day)
3
# 从今天往后推70天的日期

案例

xxxxxxxxxx
2
1
# 查询所有员工的入职天数,并进行倒序排序
2
select name,datediff(curdate(),joindate) from users other by datediff(curdate(),joindate) desc;

流程函数

xxxxxxxxxx
4
1
select
2
    name,
3
    (case genner when '男' then '男孩子' else '女孩子' end )as sex
4
from user order by sex desc ;

案例

学生成绩表

xxxxxxxxxx
6
1
# 查询学生成绩，80分以上为优秀，60以上及格，60以下为不及格
2
select id, name,
3
    (case  when math>=80 then '优秀' when math>=60 then '及格' else '不及格' end) as '数学',
4
    (case  when chinese>=80 then '优秀' when chinese>=60 then '及格' else '不及格' end) as '语文',
5
    (case  when english>=80 then '优秀' when english>=60 then '及格' else '不及格' end) as '英语'
6
    from user;

约束

概念

一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性。MySQL数据库通过约束(constraints)防止无效的数据进入到表中，以保护数据的实体完整性。

约束分类

案例

根据下面的条件创建一张表

xxxxxxxxxx
8
1
# 根据约束创建一个新表
2
create table users(
3
    id int primary key auto_increment comment '主键',
4
    name varchar(10) not null unique comment '姓名',
5
    age int check (age >0&&age<=120 ) comment '年龄',
6
    status char(1) default (1) comment '状态',
7
    genner char(1) comment '性别'
8
) comment '用户表';

外键约束

外键相关语法

添加外键

xxxxxxxxxx
8
1
# 方法1 创建表时添加
2
create table 表名(
3
字段名 数据类型,
4
......
5
[constraint] [外键名称] foreign key(外键字段名) references 主表(主表列名);
6
);
7
# 方法2 表创建完成后追加
8
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名);

删除外键

xxxxxxxxxx
1
1
alter table 表名 drop foreign key 外键名称;

外键删除/更新行为

语法

xxxxxxxxxx
1
1
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名) on update cascade on delete cascade;

表1：员工表（a1）(子表)

表2：部门表（a2）(父表)

xxxxxxxxxx
1
1
alter table a1 add constraint fk_a1_a2_id foreign key(bumen_id) references a2(bumen);

多表查询

多表关系

一对多（多对一），多对多，一对一。

多表查询概述

1.概述：从多张表中查询数据。

2.笛卡尔积：AB两个集合的所有组合情况。（在多表查询中，需要消除无效的笛卡尔积） 如何消除笛卡尔积：

学生表（users）

课程编号表（class）

xxxxxxxxxx
3
1
select * from 表1,表2 where 字段1=字段2;
2
-- 例如在两个表中查询学生选课情况
3
select id, name, genner, class_name from users,class where users.class_choose= class.class_id;

3.分类：

连接查询 内连接：相当于查询A、B交集部分数据。

外连接：

左外连接，查询左表所有数据，以及两张表交集部分数据。

右外连接，查询右表所有数据，以及两张表交集部分数据。

自连接：当前表与自身的连接查询，自连接必须使用表别名。

内连接

隐式内连接

xxxxxxxxxx
4
1
select 字段列表 from 表1,表2 where 条件......;
2
-- 案例
3
select id, name, genner, class_name from users,class where users.class_choose=class.class_id;
4

显式内连接

xxxxxxxxxx
4
1
select 字段列表 from 表1 inner[可省略] join 表2 on 连接条件;
2
-- 案例
3
select id, name, genner, class_name from users inner join class on users.class_choose=class.class_id;
4

外连接

左外连接

xxxxxxxxxx
1
1
select 字段列表 from 表1 left outer join 表2 on 条件...;

右外连接

xxxxxxxxxx
1
1
select 字段列表 from 表1 right outer join 表2 on 条件...;

案例

xxxxxxxxxx
4
1
# 外连接（左）
2
select id, name, genner, class_name from users left join  class on users.class_choose=class.class_id;
3
# 外连接（右）
4
select id, name, genner, class_name from users right outer join  class on users.class_choose=class.class_id;

自连接

自连接查询语法

xxxxxxxxxx
1
1
select 字段列表 from 表A 别名a join 表B 别名b on 条件...;

自连接查询可以是内连接查询，也可以是外连接查询。

查询员工及其所属领导的姓名

xxxxxxxxxx
1
1
select a.name,b.name from 表1 a join 表1 b on a.mangerid= b.id

子查询

概念：在SQL语句中嵌套select语句。

语法

xxxxxxxxxx
1
1
select * from 表A where 字段1 = (select 字段2 from 表B);

子查询中，外部语句可以是增（insert）删（delete）改（update）查（select）的任何一个；

子查询类型 标量子查询（子查询结果为单个值） 列子查询（子查询结果为一列） 行子查询（子查询结果为一行） 表子查询（子查询结果为多行多列）

标量子查询

常用操作符：= ,<> ,> ,< ,>= ,<=

列子查询

常用操作符：in , not in , any , some ,all

行子查询

常用操作符：=，<>，in，not in

表子查询

常用操作符：in

xxxxxxxxxx
1
1
select * from 表A where (字段1,字段2) in (子查询);

xxxxxxxxxx
4
1
-- 子查询
2
select users.money, age from users where name='李丽丽' or name='张丁丁';
3
-- 查询表
4
select * from users where (money,age) in (select users.money, age from users where name='李丽丽' or name='张丁丁');

联合查询

xxxxxxxxxx
7
1
-- 联合查询语法
2
select 字段列表 from 表A...
3
union[all]
4
select 字段列表 from 表B...;
5
-- union all和union的区别
6
union all -- 直接将两次结果合并
7
union -- 将两次结果去重后再进行合并

联合查询注意事项：！ 多张表列数必须相同，字段数据类型也必须相同

union all -- 直接将两次结果合并，union -- 将两次结果去重后再进行合并

案例

根据需求，编写SQL语句

1. 查询员工的姓名、年龄、职位、部门信息。

2. 查询年龄小于30岁的员工姓名、年龄、职位、部门信息。

3. 查询拥有员工的部门ID、部门名称。

4. 查询所有年龄大于40岁的员工，及其归属的部门名称；如果员工没有分配部门，也需要展示出来。

5. 查询所有员工的工资等级。

6. 查询“研发部”所有员工的信息及工资等级。

7. 查询“研发部”员工的平均工资。

8. 查询工资比“灭绝”高的员工信息。

9. 查询比平均薪资高的员工信息。

10. 查询低于本部门平均工资的员工信息。

11. 查询所有的部门信息，并统计部门的员工人数。

12. 查询所有学生的选课情况，展示出学生名称，学号，课程名称

员工表（emp）

部门表（work_ids）

工资等级表（salgarde)

xxxxxxxxxx
24
1
-- 1. 查询员工的姓名、年龄、职位、部门信息。
2
select name,age,work,bumen from emp e, work_ids w where e.wkbm_id = w.ids;
3
-- 2. 查询年龄小于30岁的员工姓名、年龄、职位、部门信息。
4
select name,age,work,bumen where from emp,work_ids age<30 and e.wkbm_id = w.ids;
5
-- 3. 查询拥有员工的部门ID、部门名称。
6

7
-- 4. 查询所有年龄大于40岁的员工，及其归属的部门名称；如果员工没有分配部门，也需要展示出来。
8

9
-- 5. 查询所有员工的工资等级。
10

11
-- 6. 查询“研发部”所有员工的信息及工资等级。
12

13
-- 7. 查询“研发部”员工的平均工资。
14

15
-- 8. 查询工资比“灭绝”高的员工信息。
16

17
-- 9. 查询比平均薪资高的员工信息。
18

19
-- 10. 查询低于本部门平均工资的员工信息。
20

21
-- 11. 查询所有的部门信息，并统计部门的员工人数。
22

23
-- 12. 查询所有学生的选课情况，展示出学生名称，学号，课程名称
24

事务

事务简介

事务：是一组操作的集合，它是一个不可分割的工作单位，事务会把所有操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

事务操作

• 查询、设置事务提交方式

xxxxxxxxxx
6
1
-- 查看设置事务提交方式
2
-- 查询
3
select @@autocommit;
4
-- 设置
5
set @@autocommit=0;
6
-- 0为手动提交，1为自动提交

• 提交事务

  xxxxxxxxxx
  2
  1
  -- 若为手动提交，需执行以下指令提交事务
  2
  commit;
  

   

• 回滚事务

  xxxxxxxxxx
  2
  1
  -- 数据出现异常时回滚事务
  2
  rollback;
  

• 开启事务

xxxxxxxxxx
3
1
start transaction 
2
--或
3
begin

表名：account

完成张三为李四转账的操作：

xxxxxxxxxx
22
1
-- 1.查询张三余额
2
select * from account where name='张三';
3
-- 2.把张三余额-1000
4
update account set money = money-1000 where name='张三';
5
-- 3.把李四余额+1000
6
update account set money = money+1000 where name='李四';
7

8

9

10
-- 使用事务控制实现
11
start transaction ;
12
-- 1.查询张三余额
13
select * from account where name='张三';
14
-- 2.把张三余额-1000
15
update account set money = money-1000 where name='张三';
16
-- 3.把李四余额+1000
17
update account set money = money+1000 where name='李四';
18
-- 手动提交事务
19
commit;
20
-- 数据出现异常时回滚事务
21
rollback;
22

事务的四大特性|ACID（⭐）

• 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。

• 一致性（Consistency）：事务完成时，必须使所有数据都保持一致状态。

• 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。

• 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

并发事务问题

不可重复读

事务A第一步查询后，事务B将数据进行了修改，此时事务A第三步再次查询时，与第一步查询得到的数据不同！称之为不可重复读。

事务隔离级别

不同隔离级别下，会出现的并发事务问题

注意：！表中，事务隔离级别从上到下依次升高，事务隔离级别越高，数据安全性越高，但性能越低。

查看和设置事务隔离级别

xxxxxxxxxx
5
1
-- 查看事务隔离级别
2
select @@transaction_isolation;
3
-- 设置事务隔离级别
4
set [session|global] transaction isolation level {（事务隔离等级）read uncommitted|read committed|repeatable read|serializable};
5
-- session ：只对当前会话生效 | global ：对所有会话生效；

serializable（串行化）：在并发事务操作时，只允许一个事务执行。

总结

学习过程

MySQL进阶

存储引擎

MySQL体系结构

存储引擎简介

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

在创建表时，指定存储引擎的方式：

xxxxxxxxxx
5
1
create table 表名(
2
    字段1.....
3
    字段2.....
4
    
5
)engine = innodb comment'注释';

查询当前数据库支持的存储引擎

xxxxxxxxxx
1
1
show engines;

存储引擎特点

Innodb引擎

介绍：

Innodb引擎是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL5.5以后，Innodb是MySQL的默认存储引擎

特点：

• DML操作遵循ACID模型，支持事务；

• 行级锁，提高并发访问性能；

• 支持外键约束，保证数据的完整性和正确性；

文件：

XXX.ibd：xxx为表名，Innodb引擎的每张表都会对应这样一个表空间文件，存储该表的结构（frm,sdi）、数据、索引

参数：Innodb_file_per_table

逻辑存储结构

• tablespece：表空间

• segment：段

• extent：区

• page：页

• row：行

MyISAM引擎

介绍：

MyISAM引擎是MySQL早期的默认存储引擎。

特点：

• 不支持事务，不支持外键；

• 支持行锁，不支持表锁；

• 访问速度快；

文件：

• xxx.sdi：存储表结构信息；

• xxx.MYD：存储数据；

• xxx.MYI：存储索引；

Memory引擎

介绍：

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

特点：

• 内存存放（访问速度快）

• hash索引（默认）

文件：

xxx.sdi：存储表结构信息

三种引擎不同维度区别（⭐）

存储引擎选择

存储引擎没有好坏之分，在选择存储引擎时，应根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

• InnoDB：是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。

• MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。

• MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。

索引

索引概述

索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就叫索引

在无索引时，通过全表扫描进行查询，效率极低

在有索引时，通过对应的数据结构进行查询，更加高效。

索引的优缺点：

• 优点:

1. 提高数据检索的效率，降低数据库的IO成本

2. 通过索引列对数据进行排序，降低数据排序成本，降低CPU消耗。

• 缺点

1. 索引列也要占用空间

2. 索引大大提高了查询效率，同时也降低了更新表的速度，如对表进行insert,update,delete时，效率降低。

索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种：