文章目录
一、数据库
(一)常见术语
-
DB (Database):数据库
-
DBMS(Database Management System):数据库管理系统
-
SQL(Structured Query Language ):结构化的查询语言
(二)MySql中常用的数据类型
- 字符串类型 (char,varchar,text,…)
- 日期/时间类型(date,time,datetime,timestamp,…)
- 数值类型(tinyint,int,bigint,decimal,…)
- 二进制类型(blob,mediumblob,longblob,…)
- 其它(enum,set,json,…)
其中,查看具体类型的使用可以应用 help ‘bigint’;
(三)MySQL中数据类型的应用原则
- 尽量选择简单数据类型,例如存储整数用int不用varchar;
- 尽量使用最小数据类型,例如能用tinyint不用int;
- 假如要存储小数可以考虑使用decimal类型;
- 尽量避免使用text、blob等大字段类型,假如需要使用,则尽量放到一张表中;
(四) MySql表中常用的字段约束
- 非空约束(not null):字段的值不允许为空
- 主键约束(primary key):字段值不允许为空并且唯一
- 唯一约束(unique key):字段值必须唯一
- 检查约束(check):字段值需要在指定范围(但是数据库之间的兼容不好)
- 外键约束(foreign key):字段值需要参考引用表中的字段值。
(五)表设计时的三大范式
范式是一种设计规范,一种关系模式,可以对表的设计起到一个指导性作用。
-
第一范式(1NF):描述的是字段名不可再分(原子性)。例如姓名可再分为姓和名,这属于可再分。
-
第二范式(2NF): 在满足1NF的基础上,不存在非主键字段对主键字段的部分依赖。
-
第三范式(3NF): 在满足1NF的基础上,不存在非主键字段对主键字段的传递依赖。
补充:反范式:范式设计为我们进行表设计提供一些指导性思想,但实际项目中有时为了提高查询,选择进行调整。
范式:Noram Form 简称 NF
(六)视图(View)
视图是数据库中的一个对象,可以看作为一张虚拟表。
视图是基于表创建,只有结构,不存储数据,可以通过视图查询到表中的数据
简单视图(基于单张表并且没有数据统计的视图)可以基于视图更新表中数据,但是不推荐.
(七)事务处理(Transaction)
1.事务简介
事务是一个逻辑工作单元。
这个工作单元中的所有操作,要么都成功,要么都失败。
2.使用事务的原因
用于保证数据的正确性(完整和一致)
3.事务是如何保证数据的正确性的?
通过事务的四大特性。
ACID是指在可靠数据库管理系统(DBMS)中,事务(transaction)所应该具有的四个特性:原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)
- 原子性: 事务是一个不可再分割的工作单位,事务中的操作要么都发生,要么都不发生。
- 一致性: 在事务开始之前和事务结束以后,数据库的完整性约束没有被破坏。这是说数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性。
- 隔离性: 多个事务并发访问时,事务之间是隔离的,一个事务不应该影响其它事务运行效果。
- 持久性: 意味着在事务完成以后,该事务所对数据库所作的更改便持久的保存在数据库之中,并不会被回滚。即使出现了任何事故比如断电等,事务一旦提交,则持久化保存在数据库中。
4.事务的隔离级别
- Read Uncommitted(读未提交)
- 事务未提交前,就可被其他事务读取(最低隔离级别)
- 会出现幻读、脏读、不可重复读
- Read Committed(读已提交)
- 一个事务提交后才能被其他事务读取到
- 会造成幻读、不可重复读
- Repeatable Read(可重复读)
- 保证多次读取同一个数据时,其值都和事务开始时候的内容是一致,禁止读取到别的事务未提交的数据
- 会造成幻读
- Serializable(串行化)
- 代价最高最可靠的隔离级别
- 能防止脏读、不可重复读、幻读
5.多个事务并发执行时可能会带来的问题?
- 脏读 :一个事务读取了其它事务为提交的数据。
- 不可重复读:一个事务对同样查询条件的数据进行多次查询时,得到结果不一致。
- 幻读 :一个事务读取到的数据可能是表中不存在数据。
6.如何解决多个事务并发执行时可能会带来什么问题?
修改事务的隔离级别
隔离级别越高效率越差.
7.事务的隔离级别在底层是如何实现的吗?
通过锁和MVCC(多版本并发控制)
(八)锁
锁的类型
- 从性能维度进行分类
- 悲观锁
- 乐观锁-性能教好)
- 从操作类型上进行分类
- 共享锁
- 排它锁
- 从数据锁定粒度上进行分析
- 全局锁
- 表锁
- 行锁
全局锁应用
- 如何理解全局锁?
全局锁可以对库中所有表上锁,默认是关闭的,使用前可以手动打开.
- 如何打开全局读锁?
flush tables with read lock.
- 如何关闭全局锁(解锁)?
unlock tables;
- 全局锁案例演示
事务A | 事务B |
---|---|
flush tables with read lock | |
select * from regions | select * from regions |
select * from jobs | select * from jobs |
insert into jobs (region_name) values (‘A8’); | |
Can’t execute the query because you have a conflicting read lock | insert into jobs(region_name) values (‘A9’); |
… | 阻塞; |
unlock tables | |
… | 阻塞结束开始执行. |
表锁的应用
- 什么是表锁?
表锁是对整张表进行锁定的一种锁的设计,可以分为表读锁,表写锁.
- 如何对mysql中的表添加读写锁?
- 添加表读锁(lock table regions read);
- 添加表写锁(lock table regions write);
- 如何对mysql中的表进行解锁?
unlock tables;
- 当前线程对表添加了表读锁,当前线程可以执行的操作?
- 读
- 不可以写,会出错
- 当前线程对表添加了表读锁,其它线程可以执行的操作?
- 读
- 写会阻塞,直到解锁或超时
- 当前线程对表添加了写锁,当前线程可以执行的操作?
1.可以读
2.可以写
- 当前线程对表添加了写锁,其他线程可以执行的操作?
1.不可以读写,会被阻塞.(直到解锁或超时)
行锁应用
-
什么是行锁?
行锁是mysql中InnoDB存储引擎的一种针对行记录进行加锁的一种实现方式,默认所有的
select 操作不加锁. -
如何理解共享锁与排它锁.
- 共享锁(S锁):允许当前事务读取一行,阻止其它事务对相同记录添加排它锁.
- 排它锁(X锁):允许当前事务更新数据,阻止其它事务获取相同数据集的共享锁,排它锁.
- 如何添加共享锁和排它锁?
- 共享锁
select * from regions where id=12 lock in share mode
- 排它锁
select * from regions where id=12 for update
- 如何保证多个并发事务对同一记录进行操作时数据的一致性?
可以对这条记录添加排它锁,但是这样可能会降低系统并发性能.
(九)MVCC
MVCC 是什么?
MVCC(Multi Version Concurrent Control)多版本并发控制,它可以通过历史版本
保证读数据的一致性,但是这样方式相对于添加排它锁,并发性能要好.
- 你是否还记得事务的四个特性,底层是如何保证这些特性成功的?
- 原子性(通过undolog实现-执行回滚)
- 隔离性(通过锁,MVCC-版本控制)
- 一致性(通过undolog,redolog,隔离性)
- 持久性(通过redolog日志实现)
MVCC的底层逻辑实现
MVCC的实现原理主要依赖于记录中的三个隐藏字段,undolog,ReadView来实现的.
- MVCC中的隐藏字段指的是哪些?(了解)
- DB_TRX_ID:记录创建这条记录或者最后一次修改该记录的事务id
- DB_ROLL_PTR:回滚指针,指向这条记录的上一个版本,用于配合undolog实现数据的回滚.
- DB_ROW_ID:隐藏的主键,如果数据表没有主键,那么innodb会自动生成一个row_id,
- 什么是ReadView?
对于Read Committed和Repeatable Read的隔离级别,都要读取已经提交的事务数据,也就
是说如果版本链中的事务没有提交,该版本的记录是不能被读取的,那哪个版本的事务是可以读取
的,此时就引入了ReadView.
事务执行操作时,会生成当前事务的ReadView,保存当前事务之前活跃的所有事务id。
- ReadView中包含什么?
- m_ids: 截止到当前事务id之前,所有活跃的事务id。
- min_trx_id: 记录以上活跃事务id中的最小值。
- max_trx_id: 保存当前事务结束后应分配的下一个id值。
- creator_trx_id: 保存创建ReadView的当前事务id。
- 事务隔离(RC,RR)特性的实现?
- 如果db_trx_id与Readview中的creator_trx_id相等,则说明当前事务在访问自己的操作数据,此时可以访问。
- 如果db_trx_id小于ReadView中的min_trx_id值,表明生成的该版本的事务在当前事务生成readview之前已经提交,所以可以直接读取.
- 如果被访问版本的db_trx_id大于ReadView中的max_trx_id值,表明该版本的事务在当前事务生成ReadView后才开启的,所以该版本不可以被当前事务访问.
- 如果访问的版本的db_trx_id属性值在min_trx_id和max_trx_id之间 ,就需要判断一下db_trx_id的值是不是在m_ids列表中,如果在,说明创建 ReadView时,生成的该版本的事务还是活跃的,该版本不可以访问,如果不存在,则说明创建ReadView时,生成该版本的事务已经提交则可以读取.
(十)索引
索引的定义
索引是一种数据结构,从生活维度讲,假如将一本书看成是一张表,这本书的目录就是表中的索引.
使用索引的原因
数据量比较大时,为了快速找到们需要的数据可以使用索引,这样可以提高查询的效率.
- 索引(Index)有什么弊端?
- 会额外的占用空间
- 对更新操作会带来一定的复杂度.
应用场景
- on 子句
- where 子句
- group by 子句
- having 子句
- order by 子句
索引的分类
- 逻辑应用维度 (主键,普通,联合,唯一,空间索引,…)
- 物理存储维度 (聚簇索引,非聚簇索引)
- 数据存储结构维度(hash,B+树,…)
索引创建及应用
- 如何查看表中的索引?
show index from student;
- 如何创建索引?
- 创建表的同时创建索引.(例如 create table tablename(…,index 索引名 (字段名)))
- 创建表后通过create语句创建索引(例如 create index 索引名 on 表名(字段名))
- 创建表后通过alter语句创建索引(例如 alter table add index 索引名(字段名))
- 创建普通索引案例分享.
create index index_first_name on student(first_name);
alter table student add index index_last_name (last_name);
- 创建唯一索引案例分享
create unique index index_first_name on student(first_name);
alter student add unique index index_first_name (last_name)
- 创建组合索引案例分享
create index index_first_last on student(first_name,last_name);
alter table student add index index_first_last (first_name,last_name);
- 如何删除索引?
drop index 索引名 on 表名;
例如
drop index index_first_last on student;
索引存储结构分析
-
MySQL中索引支持哪些存储结构?
hash,B+树,…
-
MySQL中InnoDB默认的索引存储结构是什么?
B+树
-
MySQL中的B+树有什么特点?
- 树中的非叶子节点只存储索引和指针
- 树中的叶子节点存储索引和数据
- 树中的叶子节点处于相同层,并且之间会使用双向链表连接,可以更好的支持范围查询.
- MySQL中B+树相对于B树有什么优点?
- 一个磁盘可以存储索引数量会更多.
- 相同数据量的B+树相对于B树的高度相对会比较低(因为分叉多了)
- 叶子节点之间B+树有双向链表的连接,可以支持快速的范围查询.
-
你觉得Hash索引有什么优势和劣势?
hash索引查询效率比较高,但是不支持范围查询.
聚簇索引和非聚簇索引
- 什么是聚簇索引?
聚簇索引也叫聚集索引,索引和数据存储在一起,也就是索引与数据是不分离。
InnoDB存储引擎就是聚簇索引。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XyGnGi4j-1668764219371)(img_4.png)]
- 聚簇索引都有哪些类型?
InnoDB中,一张表只有一个聚集索引(即主键索引),其他索引都是二级索引。
在实际应用中我们可以直接通过主键聚集索引查询到具体记录。假如是二级索引,这个
索引中存储是索引以及对应主键,我们使用二级索引执行查询时,是先基于二级索引找
到主键,再基于主键去查询具体记录(这个过程是回表查询)。
select first_name,salary
from employees
where employee_id=206
create index index_hire_date on employees(hire_date);
select first_name,salary,email
from employees
where hire_date='1987-07-09'
create index index_hire_date_salary on employees(hire_date,salary);
select first_name,salary,email
from employees
where hire_date='1987-07-09' and salary<8000
- 什么是非聚簇索引?
非聚簇索引是索引与数据是分离的,索引和数据是单独存储的。MyISAM存储引擎是非聚簇索引。
例如:
[10,0x1001] 0x1001表示记录地址
[12,0x1002]
0x1001 [10,Jack,J@t.com]
0x1002 [12,Mike,M@t.com]
- 非聚簇索引存储的是什么?
非聚簇索引在创建时,存储的是索引值以及索引对应的记录的地址。
基于非聚簇索引查询数据时,可以先基于索引找到数据的一个地址,然后基于地址再去查找数据。
单从索引角度来说,非聚集索引查找速度不如聚集索引,非聚集索引找到索引位置后还需要根据索引找到数据对应的位置
二、SQL语句
(一)数据库相关SQL
在console查询删除数据库获取语法
help 'create database';
在Terminal中查询删除数据库获取语法
help create database;
创建数据库
CREATE DATABASE
IF NOT EXISTS db_name
CHARACTER SET utf8mb4;
CREATE DATABASE
IF NOT EXISTS db_name
COLLATE utf8mb4_general_ci;
查看Mysql中自带的字符集
show variables like '%collation_%'
variables 意为变量。
查询所有数据库
show databases;
查询数据库信息
show create database db_name;
使用数据库
use db_name;
在console查询删除数据库获取语法
help drop database;
在Terminal中查询删除数据库获取语法
help 'drop database';
删除数据库
drop database db_name;
(二)表相关SQL
查询指定数据库中表的数量(mysql5.7)
SELECT table_schema,count(*) tables
FROM information_schema.tables
WHERE table_schema = 'db_name'
GROUP BY table_schema;
db_name为数据库名
创建表
CREATE table 表名(
name varchar(50),age int
)charset = utf8;
查询所有表
show tables;
查询表信息(字符集)
show create table 表名;
查询表字段
desc 表名;
修改表名
rename table 原表名 to 新表名
添加表字段
alter table 表名
add 字段名 类型
first/after 字段名;
first是加在字段名前,after是加在字段名
修改表字段
alter table 表名
change 原名 新名 新类型;
删除表字段
alter table 表名 drop 字段名;
删除表
drop table 表名;
(三)数据相关SQL
插入数据:
INSERT INTO 表名(字段名1,字段名2)
VALUES (值1,值2),(值1,值2),(值1,值2);
INSERT INTO 表名(字段1)
VALUES (值1),(值2);
查询数据:
SELECT 字段信息
FROM 表名
WHERE 条件;
修改数据:
update 表名
set 字段名=值,字段名=值
where 条件;
删除数据:
delete from 表名 where 条件;
比较运算符 > < >= <= = != <>
SELECT 字段信息 FROM db_name WHERE 字段名 !=数据;
SELECT 字段信息 FROM db_name WHERE 字段名 <>数据;
and or not:
SELECT 字段信息 FROM db_name WHERE 条件1 and 条件2;
SELECT 字段信息 FROM db_name WHERE 条件1 or 条件2;
SELECT 字段信息 FROM db_name WHERE 字段名 is null;
SELECT 字段信息 FROM db_name WHERE 字段名 not is null;
between … and …
SELECT 字段信息
FROM 表名
WHERE 字段名
BETWEEN 数据1 AND 数据2;
SELECT 字段信息
FROM 表名
WHERE 字段名
NOT BETWEEN 数据1 AND 数据2;
in:
SELECT 字段信息
FROM db_name
WHERE 字段名
IN(数据1, 数据2);
SELECT 字段信息
FROM db_name
WHERE 字段名
NOT IN(数据1, 数据2);
去重:
SELECT DISTINCT 字段信息
FROM 表名
WHERE 条件;
模糊查询:
SELECT 字段信息
FROM 表名
WHERE 字段名
like "x%";
" " 中还可以有其他形式:“%x” “%x%” “_x%” ”x%y“ ” _x%y“
其中_代表1个未知字符;%代表0或多个未知字符。
排序+分页查询
SELECT 字段信息 FROM 表名
WHERE 条件
ORDER BY 字段名 ASC
LIMIT 跳过的条数,请求的条数(每页的条数);
SELECT 字段信息 FROM 表名
WHERE 条件
ORDER BY 字段名 DESC
LIMIT 跳过的条数,请求的条数(每页的条数);
ASC为升序,且默认为升序。DESC为降序。
跳过的条数=(请求的页数-1)*每页的条数
别名:
SELECT 字段名1 别名1, 字段名2 别名2
FROM db_name;
聚合函数:
- 平均值(avg):
SELECT avg(字段名)
FROM 表名
WHERE 条件;
- 最大值(max):
SELECT max(字段名)
FROM 表名
WHERE 条件;
- 最小值(min):
SELECT min(字段名)
FROM 表名
WHERE 条件;
- 求和(sun):
SELECT sum(字段名)
FROM 表名
WHERE 条件;
- 计数(count):
SELECT count(*)
FROM 表名
WHERE 条件;
分组查询:
SELECT 字段名
FROM 表名
GROUP BY 字段名;
having
where 后面只能写普通字段条件,不能写聚合函数条件。
having关键字专门用来写聚合函数条件的, 并且需要和分组group by结合使用,写在分组的后面。
子查询(嵌套查询)
把一条SQL语句嵌套到另外一条SQL语句中,称为子查询。
关联查询:
同时查询多张表数据的查询方式称为关联查询
三种关联查询的方式:等值连接;内连接;外连接
等值连接
SELECT 字段信息
FROM A,B
WHERE A.x=B.x(关联关系)
AND 其它条件
内连接
内连接和等值连接作用一样, 查询到的是两个表之间的交集数据(有关系的数据)
SELECT 字段信息
FROM A
JOINB ON A.x=B.x(关联关系)
WHERE 其它条件;
外连接
内连接和等值连接作用一样, 查询到的是两个表之间的交集数据
外连接查询的是一张表的全部和另外一张表的交集
select 字段信息 from A left/right
join B on A.x=B.x where 其它条件;
描述查询语句中各部分的执行顺序
select distinct from A join B on a.xx=b.xx where xx=? and xx=? group by xx having xx=? order by ... limit m,n;
- from 产生虚拟表v1,产生的是笛卡尔积,产生m*n条数据;
- on 根据连接条件进行筛选,基于V1,产生虚拟表v2
- left/right join 若是外连接,将主表中的所有数据进行补充到v2表中
- where 根据查询条件,从v2中进行数据筛选,产生虚拟表v3
- group by 对v3分组,产生虚拟表v4
- having 对分组后的结果再次筛选 ,产生虚拟表v5
- select 选择最终要显示的字段,产生虚拟表v6
- distinct 对结果集进行去重,产生虚拟表v7
- order by 对结果根据某字段进行升序/降序排列
- limit 选择需要的数据
(四)视图应用
创建视图
create view 视图名 as
select 字段信息
from 表名1 join 表名2 on 连接条件
如何基于视图查询数据
select 字段信息 from 视图名;
删除视图
drop view if exists 视图名;
(五)事务隔离应用
查询当前会话的事务隔离级别
select @@tx_isolation;
修改当前会话的事务隔离级别
- 将当前会话设置为读未提交。
set session transaction isolation level read uncommitted;
- 将当前会话设置为读已提交。
set session transaction isolation level read committed;
- 将当前会话设置为可重复读。
set session transaction isolation level repeatable read;
- 将当前会话设置为序列化。
set session transaction isolation level serializable;
session 意为会话。