数据库基础

程序员文章站 2022-05-07 14:33:00

...

数据库基础

事物

事物就是必须原子地执行一组或多组数据库操作的集合。要么都执行，要不都不执行。可以通过commit提交一个事务，也可以使用rollback进行回滚。

事务的四个特性（ACID）

原子性（Atomicity）
事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚。回滚可以用回滚日志（Undo Log）来实现，回滚日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。
一致性（Consistency）
一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。
拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。
隔离性（Isolation）
隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。
即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。
持久性（Durability）
持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。
事务四个特性相互之间的关系：
只有满足一致性，事务的执行结果才是正确的。
在无并发的情况下，事务串行执行，隔离性一定能够满足。此时只要能满足原子性，就一定能满足一致性。
在并发的情况下，多个事务并行执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性。
事务满足持久化是为了能应对系统崩溃的情况。
MySQL 默认采用自动提交模式。也就是说，如果不显式使用START TRANSACTION语句来开始一个事务，那么每个查询操作都会被当做一个事务并自动提交。

并发一致性和事务的隔离级别

在并发环境下，事务的隔离性很难保证，因此会出现很多并发一致性问题。
主要会产生以下几个问题：丢失修改、脏读、不可重复度、幻读。

丢失修改
丢失修改指一个事务的更新操作被另外一个事务的更新操作替换。
脏读
读脏数据指在不同的事务下，当前事务可以读到另外事务未提交的数据。例如：T1 修改一个数据但未提交，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据。
不可重复读
不可重复读指在一个事务内多次读取同一数据集合。在这一事务还未结束前，另一事务也访问了该同一数据集合并做了修改，由于第二个事务的修改，第一次事务的两次读取的数据可能不一致。例如：T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。
幻读
幻读本质上也属于不可重复读的情况，T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。

事务的隔离级别
为了解决并发一致性问题，数据库管理系统引入事务的隔离级别。

读未提交（READ UNCOMMITTED）
事务中的修改，即使没有提交，对其它事务也是可见的。
读提交（READ COMMITTED）
一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。
可重复读（REPEATABLE READ）
保证在同一个事务中多次读取同一数据的结果是一样的。
串行化（SERIALIZABLE）
强制事务串行执行，这样多个事务互不干扰，不会出现并发一致性问题。该隔离级别需要加锁实现，因为要使用加锁机制保证同一时间只有一个事务执行，也就是保证事务串行执行。

参考资料：CS-Notes

数据库锁

MySQL中提供了两种*粒度，行级锁以及表级锁。
锁的粒度如何选取？
应该尽量只锁定需要修改的那部分数据，而不是所有的资源。锁定的数据量越少，发生锁争用的可能就越小，系统的并发程度就越高。但是加锁需要消耗资源，锁的各种操作（包括获取锁、释放锁、以及检查锁状态）都会增加系统开销。因此*粒度越小，系统开销就越大。在选择*粒度时，需要在锁开销和并发程度之间做一个权衡。

锁的类型

独写锁

互斥锁(Exclusive) ，简写为X锁，又称写锁。
共享锁(Shared)，简写为S锁，又被读锁。

有以下两个规定：

一个事务对数据对象 A 加了 X 锁，就可以对 A 进行读取和更新。加锁期间其它事务不能对 A 加任何锁。一个事务对数据对象 A 加了
S 锁，可以对 A 进行读取操作，但是不能进行更新操作。加锁期间其它事务能对 A 加 S 锁，但是不能加 X 锁。

意向锁

使用意向锁（Intention Locks）可以更容易地支持多粒度*。
在存在行级锁和表级锁的情况下，事务 T 想要对表 A 加 X 锁，就需要先检测是否有其它事务对表 A 或者表 A 中的任意一行加了锁，那么就需要对表 A 的每一行都检测一次，这是非常耗时的。
意向锁在原来的 X/S 锁之上引入了 IX/IS，IX/IS 都是表锁，用来表示一个事务想要在表中的某个数据行上加 X 锁或 S 锁。有以下两个规定：

一个事务在获得某个数据行对象的 S 锁之前，必须先获得表的 IS 锁或者更强的锁；
一个事务在获得某个数据行对象的 X 锁之前，必须先获得表的 IX 锁。

通过引入意向锁，事务 T 想要对表 A 加 X 锁，只需要先检测是否有其它事务对表 A 加了 X/IX/S/IS 锁，如果加了就表示有其它事务正在使用这个表或者表中某一行的锁，因此事务 T 加 X 锁失败。

视图

视图（子查询）：是从一个或多个表导出的虚拟表，其内容由查询内容定义.具有普通表的结构，但是不实现数据存储。
对视图的修改：单表视图一般用于查询和修改，会改变表的数据。
多表视图一般用于查询，不会改变表的数据。

作用：
（1）简化了操作，把经常使用的数据定义为视图。
我们在使用查询时，在很多时候我们要使用聚合函数，同时还要显示其它字段的信息，可能还会需要关联到其它表，这时写的语句可能会很长，如果这个动作频繁发生的话，我们可以创建视图，这以后，我们只需要select * from view就可以啦，这样很方便。
（2）安全性，用户只能查询和修改能看到的数据。
因为视图是虚拟的，物理上是不存在的，只是存储了数据的集合，我们可以将基表中重要的字段信息，可以不通过视图给用户，视图是动态的数据的集合，数据是随着基表的更新而更新。同时，用户对视图不可以随意的更改和删除，可以保证数据的安全性。
（3）逻辑上的独立性，屏蔽了真实表的结构带来的影响。
视图可以使应用程序和数据库表在一定程度上独立。如果没有视图，应用一定是建立在表上的。有了视图之后，程序可以建立在视图之上，从而程序与数据库表被视图分割开来。
缺点：
（1）性能差
数据库必须把视图查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，数据库也要把它变成一个复杂的结合体，需要花费一定的时间。
（2）修改限制
当用户试图修改视图的某些信息时，数据库必须把它转化为对基本表的某些信息的修改，对于简单的视图来说，这是很方便的，但是，对于比较复杂的试图，可能是不可修改的。

总结：
在定义数据库对象时，不能不加选择地来定义视图，应该权衡视图的优点和缺点，合理地定义视图。
视图（子查询）：是从一个或多个表导出的虚拟的表，其内容由查询定义。
具有普通表的结构，但是不实现数据存储。
对视图的修改：单表视图一般用于查询和修改，会改变基本表的数据，
多表视图一般用于查询，不会改变基本表的数据。

使用SQL语句创建、查询和删除视图

--创建视图--
create or replace view v_student as select * from student;
--从视图中检索数据--
select * from v_student;
--删除视图--
drop view v_student;

文章来自于数据库–视图的基本概念以及作用

MySQL数据库应用总结（九）—MySQL数据库视图的操作

索引

索引简介

索引使数据库程序无须对整个表进行扫描，就可以在其中找到需要的数据。当进行数据检索时，系统先搜索索引，从中找到数据的指针，从直接通过指针从表中取数据！
优点：查询快
1）大大加快数据的检索速度；
2）创建唯一性索引，保证数据库表中每一行数据的唯一性；
3）加速表和表之间的连接；
4）在使用分组和排序字句进行数据检索时，可以显著减少查询中分组和排序的时间。
缺点：
1）占用存储空间
2）在表中执行insert、delete、update操作时，将有额外的操作来维护索引
3）过多的索引起反作用

索引分类和选择

索引分为四类：
index:普通索引，数据可以重复
fulltext:全文索引，用来对大表的文本域(char,varchar,text)进行索引
unique：唯一索引，要求所有记录都唯一
primary key：主键索引，也就是唯一索引的基础上相应的列必须是主键

索引的选择原则
1）对于查询频率高的字段创建索引；
2）对排序、分组、联合查询频率高的字段创建索引；
3）索引的数目不宜太多
4）选择唯一性索引
唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。
5）尽量使用数据量少的索引
如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。

三范式

第一范式(1NF)：确保每列保持原子性，数据库的基本要求，即属性不可分
通俗的理解是：字段还可以再分吗？如果不能，则是符合1NF范式
比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式
第二范式(2NF): 确保表中的每列都和主键相关
通俗来讲：在一个数据库中只能保存一种数据，不可以把多种数据保存在同一张数据表中。比如一个订单信息表中即包含订单信息，也包含商品信息，主键是订单编号，商品信息是与订单编号没有关系的，而与商品编号有关系，可以把它拆分为两个表：订单表和商品信息表。
第三范式(3NF): 非主键列之间没有传递函数依赖关系。这是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。
比如评论表，如果将用户ID，用户头像都放在留言表中，就是不合适了。用户头像是依赖于用户ID的，而不依赖该评论。

对于数据库设计的三大范式，可以参考：数据库设计三大范式

DML，DDL，DCL和TCL

DML（data manipulation language）数据操纵语言：就是我们最经常用到的SELECT、UPDATE、INSERT、DELETE。主要用来对数据库的数据进行一些操作。

DDL（data definition language）数据库定义语言：其实就是我们在创建表的时候用到的一些sql，比如说：CREATE、ALTER、RENAME、DROP、TRUNCATE等。DDL主要是用在定义或改变表的结构，数据类型，表之间的链接和约束等初始化工作上。

数据库控制语言DCL：主要包括GRANT（授权），REVOKE（回收权限）等

创建用户:create user’xiaoming’@‘localhost’ identified by ‘666666’;
授权用户:grant all on test.*to’xiaoming’@‘localhost’;
刷新权限:flush privileges;
取消授权:revoke all on test.* from ‘xiaoming’@‘localhost’;
删除用户: drop user’xiaoming’@‘localhost’;

事物控制语句TCL:SAVEPOINT（保存点），ROLLBACK（回退到某点），COMMIT（提交事务）。

开启事务：set autocommit=0;
操作回滚：rollback;
提交事务：commit;

删除数据的方式

在速度上，一般来说，drop> truncate > delete
1）.DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录。在日志中保存以便进行进行回滚操作。

DELETE FROM 表名 WHERE 条件表达式

2）TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会**与表有关的删除触发器。执行速度快。
TRUNCATE 只能对TABLE；DELETE可以是table和view
3）DROP则删除整个表（结构和数据）。 TRUNCATE 和DELETE只删除数据， DROP则删除整个表（结构和数据）。

约束

数据库约束是什么？
他们定义数据库中的数据必须符合的某些属性。他们可以应用于列，整个表格，多个表格或整个模式。
约束类型主要有五种：

唯一性约束：唯一性，可以为空，但只能有一个。
主键约束：唯一性，非空性
外键约束：需要建立两表间的关系并引用主表的列
检查约束：对该列数据的范围，格式的限制（如：年龄、性别等）
默认值约束：该数据的默认值
创建和修改约束

# 创建表时添加约束
CREATE TABLE users(
id int PRIMARY KEY AUTO_INCREMENT,   # id列插入数据时自动递增，而不是使用默认值
age int DEAFULT 20,                  # default约束
name varchar(25),
user_id int FOREIGN KEY REFERENCES users(id)  # 外键约束，不指定约束名
UNIQUE (name)                                 # unique约束，不指定约束名 
)

CREATE TABLE users(
id int,
age int,
user_id int,
PRIMARY KEY (id),                              # 主键约束，不指定约束名
FOREIGN KEY (user_id) REFERENCES users(id)     # 外键约束，不指定约束名
CONSTRAINT index_age UNIQUE (age)              # 唯一约束名称，并指定约束名
)

# 修改表：添加或移除PRIMARY KEY约束
ALTER TABLE ADD PRIMARY KEY (id)      # 添加主键，不指定约束名
ALTER TABLE ADD CONSTRAINT index_primary PRIMARY KEY (id)
ALTER TABLE DROP FOREIGN KEY          # 移除所有主键



# 修改表：添加或移除FOREIGN KEY约束
ALTER TABLE schools ADD FOREIGN KEY (user_id) REFERENCES users(id);            
# 为schools表的user_id列添加外键，外键为users表的id列，不指定约束名称，默认为user_id
ALTER TABLE schools ADD CONSTRAINT inde_foreign_key FOREIGN KEY (user_id) REFERENCES users(id)                  # 添加外键约束并指定约束名


# 修改表： 添加或移除UNIQUE约束
ALTER TABLE ADD UNIQUE (name)               # 为name列添加唯一性约束，不指定索引名
ALTER TABLE ADD CONSTRAINT index_name UNIQUE (name)   # 添加约束并指定约束名
ALTER TABLE DROP INDEX index_name                     # 移除唯一约束


# 修改表： 添加或移除DEFAULT约束
ALTER TABLE schools ALTER age SET DEFAULT 24;  # 修改表：修改age列指定默认值为24
ALTER TABLE schools ALTER COLUMN age SET DEFAULT 24  # 同上
ALTER TABLE schools ALTER COLUMN age DROP DEFAULT    # 移除default约束

参考资料：数据库约束constraint
数据库的约束简介

存储过程

什么是数据库的存储过程？
存储过程（Stored Procedure）是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中,用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
存储过程（procedure）类似于C语言中的函数。

为什么要使用存储过程？
（1）实现了模块化编程。
（2）调用一次以后，相关信息就保存在内存中，下次调用时可以直接执行。
（3）存储过程可以接受输入参数并可以返回输出值。
（4）存储过程具有对数据库立即访问的功能。
（5）使用存储过程可以加快程序的运行速度。
（6）使用存储过程可以减少网络流量。
（7）使用存储过程可以提高数据库的安全性。

存储过程的分类
（1）数据库系统存储过程：数据库自带
（2）用户定义数据库存储过程：由用户根据需要自定义

MySQL数据库存储过程
（1）创建存储过程：

create procedure 存储过程名字()
begin
SQL语句/逻辑
end；

（2）调用存储过程：

call 存储过程名字();
call 存储过程名字(@参数1，@参数2，@参数3，...);

（3）删除存储过程

drop procedure if exists 存储过程的名字；

参考资料：MySql数据库——存储过程

触发器

1、概念：触发器是一个在修改指定表中的数据时执行的存储过程。经常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性或一致性。由于用户不能绕过触发器，所以可以用来强制实施复杂的业务规则，以此确保数据的完整性。触发器定义在某个创建的表上也可以在一个数据库上。
2、触发器和存储过程的区别：
（1）触发器主要是通过事件进行触发而被执行的。
（2）存储过程可以通过存储过程的名字而被直接调用。
3、为什么要使用触发器
（1）优点：

触发器自动执行，它们在表的数据作了任何修改之后立即**
触发器可以通过数据库中的相关表进行层叠更改。这比直接把代码卸载前台的做法更安全合理
触发器可以强制限制，这些限制比用CHECK约束所定义的更复杂。与CHECK的约束不同的是，触发器可以引用其他表中的列。
（2）功能
维护数据的安全性

4、触发器的种类
（1）DDL触发器
针对数据库，在创建一个表或者修改，删除时会触发DDL这样一个触发器，这个触发器可以防止对数据库架构进行某些更改，希望数据库中发生某种情况以响应数据库架构中的更改，要记录数据架构中的更改或者事件。
（2）DML触发器，数据操作语言，它包含三种触发器分别是：

INSERT触发器
UPDATE触发器
DELETE触发器

参考自数据库——触发器

表的连接

分布式数据库和集中式数据库

分布式数据库的优点：
（1）更适合分布式的管理与控制。
（2）具有灵活的体系结构。
（3）系统经济，可靠性高，可用性好。
（4）在一定条件下响应速度加快。如果存取的数据在本地数据库中，那末就可以由用户所在的计算机来执行，速度就快。
（5）可扩展性好，易于集成现有系统，也易于扩充。
缺点：
（1）通信开销较大，故障率高。
（2）数据的存取结构复杂。
（3）数据的安全性和保密性较难控制。

数据库分片、分区、分表、分库

分区
就是把一张表的数据分成N个区块，在逻辑上看最终只是一张表，但底层是由N个物理区块组成的
分表
就是把一张表按一定的规则分解成N个具有独立存储空间的实体表。系统读写时需要根据定义好的规则得到对应的字表明，然后操作它。
分库
一旦分表，一个库中的表会越来越多，因此需要分库

分区并不是生成新的数据表，而是将表的数据均衡分摊到不同的硬盘，系统或是不同服务器存储介子中，实际上还是一张表。另外，分区可以做到将表的数据均衡到不同的地方，提高数据检索的效率，降低数据库的频繁IO压力值，分区的优点如下：
1）相对于单个文件系统或是硬盘，分区可以存储更多的数据；
2）数据管理比较方便，比如要清理或废弃某年的数据，就可以直接删除该日期的分区数据即可；
3）精准定位分区查询数据，不需要全表扫描查询，大大提高数据检索率；
4）可跨多个分区磁盘查询，来提高查询的吞吐量；
5）在涉及聚合函数查询时，可以很容易进行数据的合并；

分片：在分布式存储系统中，数据需要分散存储在多台设备上，数据分片就是用来确定数据在多台存储设备上分布的技术。数据分片要达到三个目的：

分布均匀，即每台设备上的数据量要尽可能相近；
负载均衡，即每台设备上的请求量要尽可能相近；
扩缩容时产生的数据迁移尽可能少。

存储引擎

Myisam
（1）不支持事务
（2）支持表级锁
（3）不支持外键
（4）查询速度快
优点：ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。
缺点：它不支持事务处理、不支持外键、不能够容错、也不支持索引。

Innodb
（1）支持事务
（2）支持行锁
（3）支持外键
优点：可靠性要求比较高，支持事务；适合更新频繁的表。
缺点：（1）查询速度慢（2）需要更多的内存和存储。

常用命令

常用操作数据库的命令

show databases; 查看所有的数据库
create database test; 创建一个叫test的数据库
drop database test;删除一个叫test的数据库
show tables; 在选中的数据库之中查看所有的表
use test;选中库,在建表之前必须要选择数据库
drop table 表名; 删除表
create table 表名(字段1 类型, 字段2 类型);
desc表名;查看所在的表的字段
show create table 表名; 查看创建表的详细信息
show create databases 库名;查看创建库的详细信息

修改表的命令

修改字段类型：alter table 表名 modify 字段字段类型;
添加新的字段：alter table 表名 add 字段字段类型
添加字段并指定位置： alter table 表名 add 字段字段类型 after 字段;
删除表字段： alter table 表名 drop 字段名;
修改指定的字段： alter table 表名change 原字段名字新的字段名字字段类型

对数据的操作

增加数据(insert)3种方式
insert into 表名values(值1，值2，…)(很少用)
insert into 表名(字段1，字段2…) values(值1，值2，…);（较常用）
insert into 表名(字段1，字段2…) values(值1，值2，…)，(值1，值2，…)，(值1，值2，…);
删除数据(delete)
delete from 表名 where 条件;
注意：where条件必须加，否则数据会被全部删除
更新数据(update)
update 表名 set字段1 = 值1, 字段2 = 值2 where 条件;
查询数据(select)
（1）指定数据查询 select 字段from 表名
（2）查询前10行 select 字段 from 表名 limit 0,10;
（3）仅仅列出不同（distinct）的值：select distinct 字段 from 表名
（4）select * from 数据表 where 字段名=字段值 order by 字段名 [desc|asc(默认)]
（5） select * from 数据表 where 字段名 like ‘%字段值%’ order by 字段名 [desc]
（6）select * from 数据表 where 字段名 in (‘值1’,‘值2’,‘值3’)
（7）select * from 数据表 where 字段名 between 值1 and 值2
（8）多字段排序：select字段from 表order by 字段1 desc |asc,…字段n desc| asc;
统计数据记录
（1）AVG(字段名) 得出一个表格栏平均值
（2）COUNT(*|字段名) 对数据行数的统计或对某一栏有值的数据行数统计
（3）MAX(字段名) 取得一个表格栏最大的值
（4）MIN(字段名) 取得一个表格栏最小的值
（5）SUM(字段名) 把数据栏的值相加

高级查询

连接查询

（1）内连接
隐式内连接select username,name from user,goods where user,gid=gods,gid;
显示内连接
select username,from user inner join goods on user.gid=goods.gid;
select * from user left join goods on user.gid=goods.gid;
（2）外连接
外连接: 以某张表为主,取出里面的所有记录, 然后每条与另外一张表进行连接: 不管能不能匹配上条件,最终都会保留: 能匹配,正确保留; 不能匹配,其他表的字段都置空NULL.
left join: 左外连接(左连接), 以左表为主表
right join: 右外连接(右连接), 以右表为主表

（3）交叉连接
交叉连接: cross join, 从一张表中循环取出每一条记录, 每条记录都去另外一张表进行匹配: 匹配一定保留(没有条件匹配), 而连接本身字段就会增加(保留),最终形成的结果叫做: 笛卡尔积。
数据库基础

联合查询

联合查询:将多次查询(多条select语句), 在记录上进行拼接(字段不会增加)
基本语法：

Select 语句1
Union [union选项]
Select语句2…

Union选项有两个：
All:保留所有（不管重复）
Distinct:去重（默认）

子查询

子查询：在某个查询结果上进行查询即为子查询（一条select语句内部包含了另外一条select语句）。
子查询分类
（1）按位置分类: 子查询(select语句)在外部查询(select语句)中出现的位置

From子查询: 子查询跟在from之后
Where子查询: 子查询出现where条件中
Exists子查询:子查询出现在exists里面

（2）按结果分类：根据子查询得到的数据进行分类(理论上讲任何一个查询得到的结果都可以理解为二维表)

标量子查询: 子查询得到的结果是一行一列
列子查询: 子查询得到的结果是一列多行
行子查询: 子查询得到的结果是多列一行(多行多列)（1,2,3出现的位置都是在where之后)
表子查询: 子查询得到的结果是多行多列(出现的位置是在from之后)

参考链接：MySQL数据高级查询之连接查询、联合查询、子查询

数据库基础