数据库知识整理（自用，持续更新中）

无敌forever

已于 2024-04-13 17:39:59 修改

阅读量2.1k

点赞数 72

分类专栏： MySQL掌握之旅文章标签：数据库 mysql

于 2024-04-13 17:39:36 首次发布

本文链接：https://blog.csdn.net/weixin_45150434/article/details/137720574

版权

MySQL掌握之旅专栏收录该内容

10 篇文章 0 订阅

订阅专栏

简介

这些内容是自己学习的时候做的整理，并不如书籍专业知识全面，知识自己初期学习实践的时候，经常用到自己查资料，有需可取。
因为还在学习中，所以有可能会更新。

基础知识

关系型数据库
建立在关系模型基础上，由多张相互链接的二位表组成的数据库

SQL语法
DDL–数据定义语言，定义数据库对象
DML–数据操作语言，用来对数据库表中的数据进行增删改
DQL–数据查询语言，用来查询数据库中表的记录
DCL–数据控制语言，用来创建数据库用户、控制数据库的访问权限

数据库-图形化界面工具-DataGrip

MySQL启动与停止
net start mysql80
net stop mysql80

客户端连接

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

DDL

SHOW DATABASES–查询所有数据库
SELECT DATABASES–查询当前数据库
CREATE DATABASE [IF NOT EXSITS]数据库名[DEFAULT CHARSET 字符集]，[COLLATE 排序规则]—创建
DROP DATABASE[IF EXISTS]数据库名–删除
USE 数据库名–使用

表操作-查询
show tables;–查询当前数据库所有表
desc 表名;–查询表结构
show create table 表名;–查询指定表的建表语句

表操作-创建
create table(
字段1 字段1类型 comment ‘注释’，
字段2 字段2类型 comment ‘注释’，
字段3 字段3类型 comment ‘注释’
) comment ‘注释’；

表操作-修改
新增表字段
alter table 表名 ADD 字段名类型（长度） comment ‘注释’
修改数据类型
alter table 表名 modify 字段名新数据类型（长度）;
修改字段名和字段类型
alter table 表名 change 旧字段名新字段名类型（长度）comment ‘注释’
删除字段
alter table 表名 drop 字段名;
修改表名
alter table 表名 rename to 新表名;
删除表
drop table 表名；
删除指定表，并重新创建该表
truncate table 表名；
注意：在删除表的同时，表中的数据会全部删除

数据类型
数值类型
tinyint
smallint
mediumint
int
bigint
float
double
decimal 依赖于M（精度）和D（标度）
字符串类型
char
varchar
tinyblob
tinytext
blob
text
mediumblob
mediumtext
longblob
longtext
日期时间类型
date
time
year
datetime

DML

添加数据（insert）

给指定的字段添加
insert into 表名（字段1,字段2）values（值1,值2）;
给所有字段添加数据
insert into 表名 values （值1,值2）;
批量添加数据
insert into 表名（字段1,字段2）values（值1,值2）,（值1,值2）;
insert into 表名 values （值1,值2）,（值1,值2）;
字符串和日期时间类型数据应该包含在引号中

insert优化

除了以上批量添加数据方式外
手动提交事务

start transaction;
insert into tb_test values (1,'Tom'),(2,'Cat),(2,'Jarry);
insert into tb_test values (1,'Tom'),(2,'Cat),(2,'Jarry);
insert into tb_test values (1,'Tom'),(2,'Cat),(2,'Jarry);
commmit;

主键顺序插入
主键顺序插入的性能高于乱序插入

如果一次性需要插入大批量数据，使用insert语句插入性能较低，可以使用MySQL数据库提供的load指令进行插入

//客户端连接服务端时，加上参数--local-infile
mysql --local -infile -u root -p
//设置全局参数local infilc为1，开启从本地加载文件导入数据的开关
set global ocal_intile = 1;
//执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql1.iog' into table 'tb_user' fields terminated by ',' lines terminated by '\n';

修改数据（update）
update 表名 set 字段名1=值1 ，字段名2=值2，[where 条件];
修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据；

删除数据（delete）
delete from 表名 [where 条件];

DQL

查询关键字：select
编写顺序
select 字段列表
from 表名列表
where 条件列表
group by 分组字段列表
having 分组后条件列表
order by 排序字段列表
limit 分页参数
执行顺序
from-where-group by-having-select-order by-limit

基本查询

查询多个字段
select 字段1，字段2，字段3 from 表名；
select * from 表名；
设置别名
select 字段1 【as 别名1】，字段2 【as 别名2】… from 表名；
去处重复记录
select distinct 字段列表 from 表名；

条件查询（where）
select from where 条件列表;
条件

条件运算符
逻辑运算符
&& || !

聚合函数（count、max、min、avg、sum
select 聚合函数（字段列表） from 表;

SUM()：返回某列的和。
AVG()：返回某列的平均值。
COUNT() ：返回某列的行数。
MAX() ：返回某列的最大值。
MIN() ：返回某列的最小值。

分组查询（group by）
select 字段列表 from 表名 (where 条件) group by 分组字段名 (having 分组后过滤条件);

select actor_id,director_id
from ActorDirector
group by actor_id, director_id
having count(*)>=3;

排序查询（order by）
select 字段列表 from 表名 order by 字段1 排序方式1，字段2 排序方式2 ;
asc：升序（默认值）
desc：降序

分页查询（limit）
select 字段列表 from 表名 limit 起始索引,查询记录数 ;

DCL

管理用户

查询用户
use MySQL;
select * from user;

创建用户
create user '用户名‘@'主机名’ identified by ‘密码’;

修改用户密码
alter user '用户名‘@'主机名’ identified eith mysql_native_password by ‘新密码’;

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

常用权限

all,all privileges–所有权限;select–查询数据;insert–插入数据;update–修改数据;delete–删除数据;alter–修改表;drop–删除数据库/表/视图;create–创建数据库/表

查询权限
show grants for ‘用户名’@‘主机名’;
授予权限
grant 权限列表 on 数据库名.表名 to ‘用户名’@‘主机名’;
撤销权限
revoke 权限列表 on 数据库名.表名 from’用户名’@‘主机名’;

函数

字符串函数

concat(s1,s2,…sn)–字符串拼接，将多个字符串拼接成一个字符串
lower(str)–将字符串str全部转为小写
upper(str)–将字符串str全部转为大写
lpan(str,n,pad)–左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
rpad(str,n,pad)–右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
trim(str)–去掉字符串头部和尾部的空格
substing(str,start,len)–返回从字符串str从start位置起的len个长度的字符串

数值函数

ceil(x) 向上取整
floor(x) 向下取整
mod(x,y) 返回x/y的模（余数）
rand() 返回0,~1内的随机数
round(x,y) 求参数x的四舍五入的值，保留y位小数

### 正则表达式

用于定义一些字符串的规则
字段名 regexp ‘匹配方式’

^ 匹配文本的开始字符
$ 匹配文本的结束字符
. 匹配任何单个字符
- 匹配零个或多个在他前面的字符
- 匹配前面的字符1次或多次
<字符串> 匹配包含指定字符的文本
[ 字符集合 ] 匹配字符集合中的任何一个字符
[ ^ ] 匹配不在括号中的任何字符
字符串{n,} 匹配前面的字符串至少n次
字符串{n,m} 匹配前面的字符串至少n次，至多m次

select * from users where mail regexp '^[a-zA-Z]+[a-zA-Z0-9_\\.\\/\\-]*@leetcode\\.com$';

日期函数

curdata() 返回当前日期
curtime() 返回当前时间
now() 返回当前日期和时间
year() 获取指定date的年份
month(date) 获取指定date的月份
day(date) 获取指定date的日期
date_add(date,interval expr type) 返回一个日期/时间值加上一个时间间隔expr后的时间值
datediff(date1,dat2) 返回起始时间date1和结束时间dae2之间的天数

select a.id
from weather a, weather b
where a.temperature > b.temperature
and datediff(a.recordDate, b.recordDate) = 1;

//最优解
select a.id from Weather as a left join Weather as b ON a.recordDate = date_add(b.recordDate, interval 1 day) where a.Temperature > b.Temperature

流程函数

if(calue,t,f)
ifnull(value1,calue2)
case when (cal1) then(res1)…else (default) end
case(expr) when (val1) then(res1)…else(default) end

约束

（约束是作用于表中字段上的）
非空约束–not null
唯一约束–unique
主键约束–primary key(自增:auto_increment)

主键优化
数据组织方式
在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，索引组织表（index orgnized table,IOT）
页分裂
页可以为空，也可以填充一般，也可以填充100%。每个页包含了2-N行数据（如果一行数据多大，会行溢出），根据主键排列
页合并

默认约束–default
检查约束–chick

外键约束–foreign key
外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性
添加外键

create table 表名(
	字段名 数据类型,
	...
	constraint 外键名称 foreign key 外键字段名 references 主表(主表列名)
);
alter table 表名 add constraint 外键名称 foreign key 外键字段名 references 主表(主表列名);

删除外键
alter table 表名 drop foreign key外键名称;

删除/更新行为
no action–如果有则不允许删除/更新
restrict–与no action 一致
cascade–如果有则删除/更新外键在子表中的记录
set null–如果有则设置子表中改外键值为null（这就要求该外键允许取null）
set default–父表中有变更时，子表将外键列设置成一个默认的值(lnnodb不支持)

alter table 表名 add constraint 外键名称 foreign key 外键字段名 references 主表(主表列名) on uodate no action on delete no action;

多表查询

笛卡尔积现象（A集合和B集合的所有组合情况）
cross join 交叉连接
在多表查询时，需要消除无效的笛卡尔积

多表关系

一对多（多对一）
在多的一方建立外键，指向一的一个主键

多对多
建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

一对一
多用于单表拆分
在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（unique）

多表查询分类

连接查询
内连接-查询A、B交集部分数据
隐式内连接
select 字段列表 from 表1,表2 where 连接条件;
显式内连接
select 字段列表 from 表1 (inner) join 表2 on 连接条件;

distinct 查询去重

外连接
左外连接
select 字段列表 from 表1 left (outer) join 表2 on 条件;
右外连接
select 字段列表 from 表1 right (outer) join 表2 on 条件;

自连接
select 字段列表 from 表A 别名A join 表A 别名B on 条件;
自连接可以是内连接，也可以是外连接

联合查询union，union all
多次查询的结果合并起来，形成新的查询结果集
select 字段列表 from 表A
union (all)
select 字段列表 from 表B;
对于联合查询的多张表的列数必须一致，同时字段一样
union去重，union all不去重

子查询

SQL语句中嵌套select语句
子查询外部的语句可以是insert/update/delete/select中的任意一个
子查询结果：

标量子查询（单个值）
操作符 = <> > >= < <=
列子查询（一列）
常用操作符：
in：在指定的集合范围之内，多选一

select * from emp where dept_id in (select id from dept where name='销售部' or name = '市场部');

not in ：不在指定的集合范围内
any ：子查询返回列表中，有任意一个满足即可
some：与any等同，使用some的地方都可以使用any
all：子查询返回列表中所有制都必须满足

select * from emp where salary > all ( select salary from emp where dept_id = (select id from dept where name = '财务部'))；

行子查询（一行）
常用操作符= <> in not in
表子查询（多行多列）
常用操作符 in

子查询的位置：where/from/select

事务

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

四大特性

原子性：不可分割的最小操作单元
一致性：事务完成时，必须使所有的数据都保持一致状态
隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
持久性：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

事务操作

查看/设置事务提交方式
select @@autocommit;
set @@autocommit = 0;-- 设置为手动提交
提交事务
commit;
回滚事务
rollback;

开启事务
start transaction 或 begin;

并发事务问题

脏读：一个事务读到另外一个事务还没有提交的数据
不可重复读，一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读
幻读：一个事务按照条件查询数据时，没有对应的数据行。但是在插入数据时，又发现这行数据已经存在，好像出现了“幻影”

查看事务隔离级别
select @@transaction_isolation
设置事务隔离级别
set (session|global) transaction isolation level {read uncommitted | read committed | repeatable read | serializable }

存储引擎

存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型
默认存储引擎：InnoDB
create table 表名 {
字段1 字段1类型（ commit 字段1 注释）,
······
} engine = 引擎名 ( commit 表注释);

show engines;

InnoDB

兼顾高可靠性和高性能的通用存储引擎
MySQL5.5之前的默认存储引擎

特点
DML操作遵循ACID模型，支持事务
行级锁，提高并发访问性能
支持外键foreign key约束，保证数据的完整性和正确性

文件
xxx.idb–表空间文件，存储该表的表结构（frm/sdi）、数据和索引。
参数：innodb_file_per_table
show variables like ‘innodb_file_per_table’

cmd:ibd2sdi

逻辑存储结构

MyISAM

早起默认存储引擎
不支持事务，不支持外键；支持表锁，不支持行锁；访问速度快
.MYD（数据） .MYI（索引） .sdi（表结构存储文件）

Memory

存储在内存中的，作为临时表或缓存使用
内存存放；hash索引（默认）
.sdi（表结构信息）

索引

帮助MySQL高效获取数据的数据结构（有序）

优势：
提高数据检索的效率，降低数据库的0成本；
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗
劣势：
索引列也占用空间
索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行insert/update/delete时，效率降低

分类
主键索引–primary
唯一索引–unique
常规索引
全文索引–fulltext

innoDB中索引的存储形式
聚集索引–必须有，而且只有一个
如果存在主键，主键索引就是聚集索引
如果不存在主键，将使用第一个唯一索引作为聚集索引
如果表没有主键或没有唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引
二级索引–可以存在多个

索引结构

存储引擎层实现，不同的存储引擎又不同的机构
B-Tree索引：最常见的索引类型，大部分引擎支持
Hush索引：底层数据结构使用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询，Memory
R-tree（空间索引）：MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
FULL text（全文索引）：通过建立倒排索引，快速匹配文档方式，类似于Lucere/Solr/ES

Btree

二叉树缺点：顺序插入时，会形成一个链表，查询性能大大降低。大数据量的情况下，层级较深，检索速度慢
红黑树：大数据量的情况下，层级较深，检索速度慢

多路平衡查找树
以一颗最大度数为5的b-tree为例（每个节点最多存储4个key，5个指针）
中间数向上分裂

B+tree
以一颗最大读书为4（4阶）的B+tree为例
所有数据都会出现在叶子节点，叶子节点会形成一个单向链表

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，形成了带有顺序指针的B+tree，提高区间访问的性能

Hash索引

采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中
只能用于对等比较（=，in），不支持范围查询
无法利用索引完成排序操作
查询效率高，通常只需要一次检索就可以了，效率通常高于B+tree

索引语法

创建索引
create [ unique | fulltext] index index_name on table_name ( index_col_name,...);

查看索引
show index from table_name;

删除索引
drop index index_name on table_name;

性能分析

SQL执行频率

服务器状态
show [session|global]status like 'Com_______'
查看当前数据库的insert、update、delete、select的访问频率

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启，需要在噢诶之文件（/etc/my.cnf）中配置如下信息：

//开启MySQL慢日志查询开关
slow_query_log = 1
//设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time = 2

配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log

SQL性能分析

profile详情

//查询是否支持
select @@have_profiling;
//查询是否开启
set @@profiling
//开启开关
set profiling = 1;
//查看每一条SQL的耗时情况
show profiles;
//查询指定query_id的SQL语句各个阶段的耗时情况
show profiles for query query_id;
//查询指定query_id的SQL语句CPU的耗时情况
show profiles cpu for query query_id;

explain执行计划

查询SQL语句的执行情况

//直接在select语句之前加上关键字explain/desc
explain select from where;

各字段含义
id：select查询的序列号，表示查询中执行select子句或操作表的顺序（id相同，执行顺序从上到下；id值不同，值越大，越先执行）
select_type：select的类型，常见的有simple,primary,union,subquery等
type：表示连接类型，性能由好到差的连接类型为null/system/const/eq_ref/ref/range/index/all
possible_key：显示可能应用到这张表上的索引
key：实际使用的索引，如果是null，则没有使用索引
key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。
rows：执行查询的行数，预估值
filtered：表示返回结果的行数占需读取行数的百分比，值越大越好

索引使用规则

最左前缀法则
如果索引了多列（联合索引），要遵守
指查询从索引的最左列开始，并且不跳过索引中的列（如果跳跃，索引将部分失效-后面的字段索引失效）

索引失效情况

范围查询
联合查询中，出现范围查询（>,<），范围查询右侧的列索引失效
索引列运算
不要在索引列上进行运算操作，索引将失效
字符串不加引号
字符串不加引号，索引将失效
模糊查询
如果仅仅是尾部进行模糊查询，索引不会失效，如果头部模糊匹配，索引失效
or连接的条件
用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到
数据分布影响
如果MySQL评估使用索引比全表更慢，则不使用索引

SQL提示

在SQL语句中加入一些人为的提示来达到优化操作的目的
use index:（建议使用索引）

explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';

ignore index:（忽略使用）

explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';

force index:（强制使用）

explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';

索引使用

覆盖索引
尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少select(* )
using where ; using index 查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
using index condition 查找使用了索引，但是需要回表查询数据

前缀索引
当字段类型为字符串时，可以只将字符串的一部分前缀，建立索引，这样可以打打节约索引空间，从而提高索引效率

语法
create index idx_xxxx on table_name(column(n));
前缀长度
可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择是1，这是最好的索引选择性，性能也是最好的
select count(distinct email) / count( * ) from tb_user;
select count(distinct substring(email,1,5)) / count( * ) from tb_user;

单列索引和联合索引
如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引

索引设计原则

1、针对于数据量较大，且查询比较频繁的表建立索引。
2、针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。
3、尽量选择区分度高的列作为索引，尽量建立唯一素引，区分度越高，使用素引的效率越高。
4、如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5、尽量使用联合索引，减少单列素引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6、要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
7、如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。