数据库

数据库

【一】什么是数据库

一种用于存储、组织和管理大量数据的系统，它将数据以一定的结构形式组织起来，并提供了一套标准的操作接口（如 SQL 语言），使得用户能够有效地查询、更新、插入和删除数据。

【二】数据库的本质

• 数据库其实就是一块基于网络通信的应用程序
• 存储数据的基于TCP协议的客户端和服务端的服务器

【三】数据库的分类及对应的主流数据库

• 关系型数据库
  • 主流数据库：Oracle、MySQL、Microsoft SQL Server、PostgreSQL、SQLite 等。
  • 关系型数据库采用表格的形式表示数据，基于 SQL 查询语言，强调数据之间的表结构关系以及 ACID（原子性、一致性、隔离性、持久性）事务处理。
• 非关系型数据库（NoSQL Database）
  • 分布式文档型数据库（如 MongoDB）：如MongoDB、Couchbase、CouchDB。
  • 键值对型数据库（如 Redis、Memcached）：如Redis、Amazon DynamoDB。
  • 列族数据库（如 HBase）：Apache HBase、Google Bigtable。
  • 图数据库（如 Neo4j）：Neo4j、JanusGraph。
  • 时间序列数据库（如 InfluxDB）：InfluxDB、OpenTSDB。
  • 对象数据库（如 OrientDB）：OrientDB、ObjectDB。
  • NoSQL 数据库通常不强求严格的表结构，而是灵活的数据存储和查询方式，适用于大规模分布式系统和实时大数据场景。

【四】数据库操作

【1】创建数据库

create databases 数据库名;

【2】查看数据库

show databases;  -- 查看所有数据库
show create database 数据库名; -- 查看数据库的建库sql语句

【3】修改数据库

alter database 数据库名 [character set 编码字符集];

【4】删除数据库

• 删除数据库后，数据库中存储的所有数据表和数据也将一同被删除，而且不能恢复。

drop database [if exists] 数据库名;

【5】切换到指定库

use 数据库名;

【五】表操作

【1】创建表

create table  表名 (
    字段名1    数据类型[ ( 存储空间 )   字段约束 ],
    .....
    primary key(一个 或 多个 字段名) 
)

【2】查看表

• 列出当前数据库中所有的数据表

show tables;

• 查看指定的表

show create table 表名;

• 查看当前表的详细信息

describe 表名;

• 查看建表语句

show create table 表名 \G;

【3】修改表

• 修改表的字段类型 modify(只能改类型不能改名字)

alter table 表名 modify 字段名 字段类型(宽度);

• change(名字类型都可)

alter table 表名 change 字段名 字段类型(宽度);

• 修改表名字重命名

alter table 原表名 rename 新表名;

• 添加字段默认是尾部追加字段

alter table 表名 add 字段名 字段类型;

• 指定追加位置

alter table 表名 add 字段名 字段类型 after 原字段名;

• 指定头部添加字段

alter table 表名 add 字段名 字段类型 first;

• 删除字段

alter table 表名 drop 字段名;

【4】删除表

drop table 表名;

【六】约束条件

【1】非空约束（not null）

• not null约束的字段不能为 NULL 值，必须赋具体数据

create table b1(
  id int(10) not null,
  ...
);

【2】唯一约束（unique）

• unique约束的字段具有唯一性，不可重复，但是可以为空（null）。

alter table b2 modify cid int unique;

【3】主键约束（primary key）

• 单一主键

create table b1(
	id int(10),
....
  primary key(id)
);

• 复合主键

create table b2(
	id int(10);
  name varchar(32);
 ....
  primary key(id,name)
);

• 自增字段auto_increment

create table db(
  id int(10) primary key auto_increment,
  name varchar(32)
);

【4】外键约束（foreign key）

create table 表名2(
	字段名1 字段类型1 约束条件1 comment 注释1,
	字段名2 字段类型2 约束条件2 comment 注释2,
  foreign key(在表名2中显示的字段名) references 表名1(表明1中需要建立外键关系的字段名)
);

• 级联更新和级联删除

on update cascade  # 级联更新
on delete cascade  # 级联删除

【七】表数据操作

【1】增加数据

insert into 表名(字段名) value(值)

【2】修改数据

update  表名 set  字段名=值  where 条件

【3】删除数据

delete from 表名  where 条件

【4】查看数据

select * from 表名 where 条件

【八】筛选过滤条件

【1】where

select * 
from db 
where 条件;

【2】group by(分组)

• 用于计数、平均、最高、最低等函数

select * 
from db 
group by class;

• group_concat

• 聚合函数：group_concat- 获得分组之后的具体的值

• 关键字 where 和 group by 同时出现的时候，group by 必须在 where 后面

  • where 先对整体数据进行过滤
  • group by 再对数据进行分组

【3】having(分组之后筛选)

select * 
from db 
group by class
having  ...

【4】distinct(去重)

select distinct age from db;

【5】order by(排序)

• order by : 默认是升序
• asc 默认可以省略不写 ---> 修改降序
• desc : 降序

select * from db order by age;

【6】 limit(限制展示条数)

• limit x,y : 第一个参数是起始位置，第二个是条数

select * from emp limit 10;
select * from emp limit 0,6;

【7】正则

 属性名 REGEXP '匹配方式'

选项	说明	例子	匹配值示例
^	匹配文本的开始字符	‘^b’ 匹配以字母 b 开头的字符串	book、big、banana、bike
$	匹配文本的结束字符	‘st$’ 匹配以 st 结尾的字符串	test、resist、persist
.	匹配任何单个字符	‘b.t’ 匹配任何 b 和 t 之间有一个字符	bit、bat、but、bite
*	匹配前面的字符 0 次或多次	‘f*n’ 匹配字符 n 前面有任意个字符 f	fn、fan、faan、abcn
+	匹配前面的字符 1 次或多次	‘ba+’ 匹配以 b 开头，后面至少紧跟一个 a	ba、bay、bare、battle
?	匹配前面的字符 0 次或1次	‘sa?’ 匹配0个或1个a字符	sa、s
字符串	匹配包含指定字符的文本	‘fa’ 匹配包含‘fa’的文本	fan、afa、faad
[字符集合]	匹配字符集合中的任何一个字符	‘[xz]’ 匹配 x 或者 z	dizzy、zebra、x-ray、extra
[^]	匹配不在括号中的任何字符	‘[^abc]’ 匹配任何不包含 a、b 或 c 的字符串	desk、fox、f8ke
字符串	匹配前面的字符串至少 n 次	‘b{2}’ 匹配 2 个或更多的 b	bbb、bbbb、bbbbbbb
字符串	匹配前面的字符串至少 n 次， 至多 m 次	‘b{2,4}’ 匹配最少 2 个，最多 4 个 b	bbb、bbbb

【九】多表操作

【1】子查询

• =返回一条数据

select * from 表1 where id = (select id from 表2 where 条件)

• in 返回多条数据

select * from 表1 where id in (select id from 表2 where 条件)

【2】联表查询

• inner join(内连接)

select *
from 表1 inner join 表2 on 表1.字段名=表2.字段名

• left join(左连接)

select *
from 表1 left join 表2 on 表1.字段名=表2.字段名

• right join(右连接)

select *
from 表1 right join 表2 on 表1.字段名=表2.字段名

• union(全连接)

select * from 表1 left join 表2 on 表1.字段名=表2.字段名
union
select * from 表1 right join 表2 on 表1.字段名=表2.字段名

【3】关键字exist

• 只返回布尔值
• 返回true时，外层查询语句执行
• 返回false时，外层查询语句不执行

select * from db where exists (select age from db1 where age > 18);

【十】触发器

【1】什么是触发器

• 在满足对表数据进行增删改的情况下，自动触发的功能，称为触发器

• 触发器可以作为一种数据库的约束，用于保证数据的完整性和一致性。

【2】触发器的特点

• 触发器通常与表一起创建、修改和删除。
• 触发器可以在特定的数据操作之前或之后触发执行。
• 触发器可以根据用户定义的条件判断是否执行相应的逻辑。
• 触发器可以调用存储过程、函数、触发其他触发器等，实现更复杂的业务逻辑。

【3】使用情况

• 增前

create trigger 触发器的名字 
before insert on 表名 
for each row 
begin
	SQL语句
end

• 增后

create trigger 触发器的名字 
after insert on 表名 
for each row 
begin
	SQL语句
end

• 删前

create trigger 触发器的名字 
before delete on 表名 
for each row 
begin
	SQL语句
end

• 删后

create trigger 触发器的名字 
after delete on 表名 
for each row 
begin
	SQL语句
end

• 改前

create trigger 触发器的名字 
before update on 表名 
for each row 
begin
	SQL语句
end

• 改后

create trigger 触发器的名字 
after update on 表名 
for each row 
begin
	SQL语句
end

【4】删除触发器

drop 触发器的名字;

【十一】索引

【1】什么是索引

• 索引（在MySQL中也叫做“键（key）”）是存储引擎用于快速找到记录的一种数据结构，这也是索引最基本的功能。

• 简单的说，数据库索引类似于书前面的目录，能加快数据库的查询速度。

【2】索引的类型

• 创建主键约束（PRIMARY KEY）、唯一约束（UNIQUE）、外键约束（FOREIGN KEY）时，会自动创建 对应列的索引。

• foreign key：不是用来加速查询的

• primary key/unique key：不仅可以加速查询速度，还具有对应的约束条件

• index key：只有加速查询速度的功能

【3】索引操作

• 创建索引

create index  索引名 on 表名(字段名);

• 查看索引

show index from 表名;

• 删除索引

drop index 索引名 on 表名

【4】聚集索引(主键索引)

• 聚集索引（Clustered Index）是关系型数据库中的一种索引类型，它决定了表中数据的物理存储顺序。
• 聚集索引对表进行了重新组织，使得数据按照聚集索引的键值顺序存储在磁盘上。
• 由于聚集索引决定了数据的物理存储顺序，因此通过聚集索引可以快速地找到特定范围内的数据
• MySQL的聚簇索引是基于B+树的数据结构实现的，它会把数据存储在索引的叶子节点上，叶子节点之间按顺序链接，使得按主键进行搜索时速度最快。
• 如果没有主键，如果按主键搜索，速度是最快的。

特点

• 查询速度很快。

• 聚集索引的键值必须是唯一的，不允许重复值存在。

• 如果表中没有定义聚集索引，那么表的数据存储顺序将按照物理地址来存储。

• 表不建立主键，也会有个隐藏字段是主键，是主键索引。

• 主键索引对于按照主键进行查询的性能非常高。

语法

• 数据列不允许重复，不允许为NULL，一个表只能有一个主键。

ALTER TABLE table_name ADD PRIMARY KEY (column);

【5】唯一索引（unique）

• 唯一索引是指该索引的所有值都是唯一的，不允许出现重复值。

ALTER TABLE table_name ADD UNIQUE KEY index_name (column);

【6】组合索引(联合索引)

• 组合索引是一种由两个或更多列组成的索引。
• 当查询涉及多个列时，组合索引可以大大提高查询性能。

ALTER TABLE table_name ADD INDEX index_name (column1, column2, ...);

【十二】视图

【1】什么是视图

• 视图就是通过查询得到一张虚拟表，然后保存下来，下次可以直接使用

【2】视图的优点

• 简化复杂查询：
  • 视图可以对基本表进行复杂的操作，包括连接多个表、过滤条件、聚合函数等。
  • 通过定义视图，可以将复杂的查询逻辑封装到一个简单的视图中，使用户能够以更简洁明了的方式进行数据检索。
• 数据安全性：
  • 视图可以限制用户对数据的访问权限，通常用于隐藏敏感数据或只提供部分数据给特定的用户。
  • 通过定义视图并设置相应的权限，可以保护数据的安全性，防止未经授权的用户访问敏感信息。
• 数据完整性：
  • 视图可以用于实现数据完整性约束，即对数据的有效性进行验证。
  • 通过定义视图并添加计算列、过滤条件等约束，可以确保所返回的数据满足一定的条件，提高数据的准确性和一致性。
• 逻辑数据独立性：
  • 视图使得应用程序与数据之间解耦，即应用程序不需要了解底层表结构的细节。
  • 这样，当底层数据库发生变化时（如表结构修改），只需调整底层视图的定义而无需修改应用程序，从而提高系统的可维护性和扩展性。
• 性能优化：
  • 物化视图是一种缓存机制，可以将视图的查询结果存储在磁盘上，以提高查询性能。
  • 当基本表的数据频繁变动时，物化视图可以减少查询的计算开销和响应时间，提升系统的性能。

【3】使用视图

• 创建视图

CREATE VIEW my_view AS SELECT column1, column2 FROM my_table WHERE condition;
# condition 是过滤条件。

• 使用视图

SELECT * FROM my_view;

• 更新视图

UPDATE my_view SET column1 = value1 WHERE condition;

• 删除视图

DROP VIEW my_view;

【4】总结

• 创建视图咋硬盘上只会有表结构，没有表数据
  • 表数据还是来自之前的表
• 视图一般只用来查询
  • 不建议对视图内的数据进行更改，有可能会对原始数据产生影响
• 视图的使用频率并不高
  • 当创建了较多视图后，会造成数据的难以维护

【十三】存储过程

【1】什么是存储过程

• 存储过程是在关系型数据库中存储的一组预定义的SQL语句集合，可以接收参数并返回结果。
• 它们被封装在数据库服务器中，并由应用程序通过调用存储过程来执行特定的数据库操作。

【2】存储过程的特点

• 代码复用 : 存储过程只需要编写一次，可以在多个地方被调用，提高了代码的复用性。
• 数据库性能优化 : 存储过程在数据库中被编译、优化和缓存，可以减少网络传输开销，提高查询性能。
• 安全性增强: 存储过程可以限制直接对表的访问权限，通过存储过程来进行数据操作，可以为数据库提供更好的安全性。

【3】使用存储过程

• 定义存储器

create procedure 存储器的名字(形参1,形参2...)
begin
	sql 代码
end

• 调用

call 存储器的名字();

• 查看存储过程具体信息

show create procedure pro1;

• 查看所有存储过程

show procedure status;

• 删除存储过程

drop procedure pro1;

【十四】事务

【1】什么是事务

• 开启一个事务可以包含多条语句，这些语句要么同时成功，要么都不成功
• 事务是指一系列相关操作的集合，这些操作被视为一个不可分割的工作单元。
• 事务的目标是确保在多个操作中的每一个都要么全部成功执行，要么全部失败回滚。
  • 即事务的原子性

【2】事务的四大特性

• 原子性（Atomicity）：事务中的所有操作被视为不可分割的整体，要么全部完成，要么全部不执行，没有部分成功的可能。
• 一致性（Consistency）：事务开始前和结束后，数据库都必须处于一致状态。这意味着事务的操作不会违反任何实体完整性、参照完整性和用户定义的完整性约束。
• 隔离性（Isolation）：在同一时间点上，不同的事务看到的是数据库的不同视图，彼此之间互不影响，避免了数据竞态条件（Race Condition）的发生。
• 持久性（Durability）：一旦事务被提交，无论后续发生什么情况（如系统故障、电源中断等），事务对数据库的修改都会永久保存下来，不会丢失。

【3】事务的作用

• 事务的主要作用在于在多个并发操作下保证数据的一致性和完整性，特别是在分布式系统和多用户环境中。
• 通过控制事务边界，可以防止脏数据（Dirty Data）的产生，确保每个操作都在数据库中按照预期的方式执行。

【4】如何开启事务和事务回滚

• 开启事务（以MySQL为例）：

  START TRANSACTION;
  

  • 提交事务（MySQL）：

  COMMIT;
  

• 回滚事务（MySQL）：

  ROLLBACK;
  

  当事务处理过程 ** 现错误或者操作未按预期执行时，通常会调用 ROLLBACK 来撤销已执行的所有操作，回到事务开始前的状态。

【5】结束事务

• 不论是提交还是回滚事务，都需要通过特定的语句来结束事务。

START TRANSACTION; -- 开始事务

-- 执行事务操作
INSERT INTO 表名 (列1, 列2, 列3) VALUES (值1, 值2, 值3);
UPDATE 表名 SET 列1 = 值1 WHERE 条件;
DELETE FROM 表名 WHERE 条件;

COMMIT; -- 提交事务，将修改永久保存
OR
ROLLBACK; -- 回滚事务，撤销所有修改

END; -- 结束事务

【6】数据库事务隔离机制

• Read uncommitted（读未提交）
  • 存在脏读、不可重复读和幻读问题。
• Read committed（读已提交）
  • 解决了脏读问题，但仍可能出现不可重复读和幻读。
• Repeatable read（可重复读取）
  • 解决了脏读和不可重复读问题，但仍可能出现幻读。
• Serializable（串行化）
  • 解决了脏读、不可重复读和幻读问题，但在效率方面有所牺牲。

【十五】数据库三大读现象

• 幻读（Read Ghost）：在一个事务中，如果两个查询返回的行数不同，即使它们使用了相同的 WHERE 子句和排序条件，这称为幻读。幻读通常发生在对行数可变的表（如日志表）上。
• 不可重复读（Phantom Reads）：在同一事务内部，多次读取同一行数据，发现结果不一致，原因是第二次读取时发生了新的插入操作，这种现象称为不可重复读。
• 读已提交（Read Committed）：这是大多数事务隔离级别的默认行为，在这一级别下，事务看到的都是已经提交的数据，但无法阻止其他事务在当前事务执行期间进行插入或更新操作，可能导致不可重复读和幻读的现象。

【十六】数据库锁机制

• 锁保证并发的多个事务同一时间只有一个能运行
  • 会一定程度上降低程序的运行效率
  • 但是能大大提升数据的安全性。

【1】按粒度分类

1.表级锁

• 每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、 InnoDB、 BDB 等存储引擎中。

2.行级锁

• 每次操作锁住一行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB 存储引擎中。

3.页级锁

• 页级锁：每次锁定相邻的一组记录，锁定粒度界于表锁和行锁之间，开销和加锁时间界于表锁和行锁之间，并发度一般。应用在BDB 存储引擎中。

【2】按级别分类

1.共享锁（读锁）

语法：

select … lock in share mode

若事务T对记录A加上共享锁，则事务T可以读A但不能修改A，其他事务只能再对A加共享锁，而不能加排它锁，直到事务T释放A上的共享锁。这保证了其他事务可以读A，但在事务T释放A上的共享锁之前不能对A做任何修改

2.排它锁（写锁、独占锁）

语法：

select … for update

若事务T对记录A加上排它锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁（共享锁/排它锁），直到T释放A上的排它锁。这保证了其他事务在事务T释放A上的排它锁之前不能再读取和修改A

【3】按使用方式分类

1.悲观锁

• 顾名思义指的是对外界将要进行的数据修改操作持悲观态度
• 因此，在整个数据处理过程中，将数据处于锁定状态。
• 现在由于互联网的高并发架构，即使加上悲观锁也无法保证数据不被外界修改，因此不推荐使用。

2.乐锁

• 相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突
• 所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测
• 如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。
• 通常乐观锁的实现是在表中加一个字段（可能是时间戳或版本号）
• 在写入的时候会查询一下版本号
  • 如果版本号没有改变，就写入数据库并同时改变版本号。
• 从本质上来说，乐观锁并没有加锁
  • 所以效率会大大提升
  • 但也有一定的缺陷，就是可能导致一部分任务的写入失败。

【十七】数据库的三大范式

• 范式就是我们在设置数据库的表时，一些共同需要遵守的规范

• 掌握这些设计时的范式，可以让我们在项目之初，设计库的表结构更加合理和优雅

• 三大范式之间，是逐级递进的关系，也就是说后一个范式是在前一个范式的基础上推行。

【1】第一范式 1NF

• 定义：一个关系模式必须满足属性域内的值都是原子的，即不可再分，不存在重复的值集。
• 意义：消除了列值的重复，确保了数据的最小单位是单一且有意义的信息项，降低了数据冗余，有助于提高数据的一致性。

【2】第二范式 2NF

• 定义：在1NF的基础上，关系模式中的非主键依赖于整个主键（键集合），而不仅仅是单个属性。
• 意义：消除部分依赖，使得非关键属性与键之间存在完全函数依赖，减少数据冗余并提高数据 ** 性。

【3】第三范式 3NF

• 定义：在2NF的基础上，关系模式中没有任何非主键属性传递依赖于其他非主键属性（也称为三元组依赖）。
• 意义：消除传递依赖，保证数据的逻辑 ** 性，避免因非关键属性的变化导致冗余信息的不一致。