数据库知识点总结

发表于 2021-01-05 更新于 2021-03-31 分类于 1- 编程相关，数据库阅读次数： Valine：

本篇博客对数据库的基础知识、相关操作，作简要的介绍。

0. 允许的数据类型

数据库的核心是数据，通常数据库拥有下列的数据类型：

名称	类型	说明
INT	整型	4字节整数类型，范围约+/-21亿
BIGINT	长整型	8字节整数类型，范围约+/-922亿亿
REAL	浮点型	4字节浮点数，范围约+/-10³⁸
DOUBLE	浮点型	8字节浮点数，范围约+/-10³⁰⁸
DECIMAL(M,N)	高精度小数	由用户指定精度的小数，例如，DECIMAL(20,10)表示一共20位，其中小数10位，通常用于财务计算
CHAR(N)	定长字符串	存储指定长度的字符串，例如，CHAR(100)总是存储100个字符的字符串
VARCHAR(N)	变长字符串	存储可变长度的字符串，例如，VARCHAR(100)可以存储0~100个字符的字符串
BOOLEAN	布尔类型	存储True或者False
DATE	日期类型	存储日期，例如，2018-06-22
TIME	时间类型	存储时间，例如，12:20:59
DATETIME	日期和时间类型	存储日期+时间，例如，2018-06-22 12:20:59

选择数据类型的时候，要根据业务规则选择合适的类型。通常来说，BIGINT 能满足整数存储的需求，VARCHAR(N) 能满足字符串存储的需求，这两种类型是使用最广泛的。

SQL - 结构化查询语言，用于访问和操作数据库。

SQL 语言关键字不区分大小写。而不同数据库中的表名和列名，有的区分大小写有的不区分。因此，常常这样进行规范：SQL 关键字使用大写（以表示突出），表名和列名使用小写。

1. 关系模型

关系型数据库可以看作是若干个存储数据的二维表，表的每一行称为记录（record）。

关系数据库中，表和表之间通常存在各种关系，包括：一对一；一对多；多对一等。这些关系通常都使用主键和外键来维护。

1.1 主键

在数据库表中，表中的每一行称为一条记录，每一条记录的主键必须唯一。

对主键的要求，最关键的一点是：记录一旦插入到表中，主键最好不要再修改，因为主键是用来唯一定位记录的，修改了主键，会造成一系列的影响。

通常，选取主键的原则是不要使用任何与业务相关的字段作为主键。一些建议的方式有:

使用自增整数类型作为主键 - 数据库会在插入数据的时候自动为每个记录分配一个自增的整数；
全局唯一 GUID 类型：使用一种全局唯一的字符串作为主键，类似8f55d96b-8acc-4636-8cb8-76bf8abc2f57。GUID算法通过网卡MAC地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的，大部分编程语言都内置了GUID算法，可以自己预算出主键。

1.2 外键

在某个表中，通过某一字段，把数据与另一张表关联起来，这种列称为外键。

需要注意的是，外键并不是通过列名实现的，而是通过定义外键约束来实现的(使用特定的SQL语句建立外键约束)。

由于外键约束会降低数据库的性能，大部分互联网应用程序为了追求速度，并不设置外键约束，而是仅靠应用程序自身来保证逻辑的正确性。这种情况下，”外键” 对应的列仅仅是一个普通的列，只是它起到了外键的作用而已。

1.3 索引

当数据量特别大的时候，需要使用索引来提升查询速度。

索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。

使用 ADD INDEX '索引名'('字段名') 的方式添加索引。

索引的效率 取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高。

可以对一张表创建多个索引。索引的优点是提高了 查询效率，缺点是在插入、更新和删除记录时，需要同时修改索引，因此，索引越多，插入、更新和删除记录的速度就越慢。

对于主键，关系型数据库会自动对其创建 主键索引。

唯一索引 如果某个非主键字段对于每条记录也是唯一的，那么，可以为该列添加一个唯一索引。

2. 查询数据

SELECT：表示将要执行一个查询。

FROM: 表示将从哪个表查询。

SELECT * FROM <表名>

-- 条件表达式可以使用逻辑运算符 AND、OR、NOT
-- NOT class_id = 2其实等价于class_id <> 2
SELECT * FROM <表名> WHERE <条件表达式>

如果不加括号，条件运算按照 NOT、AND、OR的优先级进行。查询字符串时，需要使用单引号将待查询对象引起来。

2.1 投影查询

如果希望查询的结果仅包含部分字段（列），使用投影查询。

1
2
3

SELECT <列名-使用逗号隔开> FROM <表名>
-- 还可以为选中的列起一个别名
SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM ...

2.2 排序

默认的，我们的查询的结果都是按照主键的顺序进行排列的。

我们可以通过 ORDER BY 来指定按照某列的值进行排序输出（默认是从低到高，使用 DESC 实现倒序）。

ORDER BY 需要放在 WHERE 后面(如果使用了 WHERE 的话)，因为这样可以减少不必要的排序，提升排序的速度。

1	SELECT * FROM <表名> WHERE xxx ORDER BY xxx DESC

2.3 分页

如果数据量太大的话，可以使用 LIMIT <M> OFFSET <N> 选取第 N 到 M 条数据，来实现分页。

如果省略 OFFSET, 则认为是从 0 开始选取 M 条记录。

2.4 聚合查询

如果需要获取查询到记录的数据，可以使用 SQL 的内置函数 COUNT() 进行查询。

除了 COUNT() 外，SQL 还提供 SUM, AVG, MAX, MIN 分别用于求取一列的和、平均值、最大值、最小值。

SQL 还提供了分组聚合功能，具体的是查询列中每个值对应的记录数目。使用 GROUP BY 列名 来实现。

-- 获取表中记录的数量，命名为 nums
SELECT COUNT(*) nums FROM <表名>

SELECT <某一列名>, COUNT(*) num FROM <表名> GROUP BY <同前面列名>

2.5 多表查询

可以直接 SELECT * FROM <表1>, <表2> 获得两个表的乘积- 即表1的每一列和表2 的每一列进行组合后的结果，共有（表1记录数 * 表2记录数）条记录（这种多表查询也成为笛卡尔查询）。

2.6 连接查询

连接查询是另一种多表查询，它对多个表进行 JOIN 运算，参考

INNER JOIN查询的写法是：

先确定主表，仍然使用 FROM <表1>的语法；
再确定需要连接的表，使用INNER JOIN <表2>的语法；
然后确定连接条件，使用ON <条件...>，
可选：加上 WHERE子句、ORDER BY等子句。

除了 inner join，SQL 还有 outer join，它们的区别如下：

INNER JOIN只返回同时存在于两张表的行数据，-求交集。
RIGHT OUTER JOIN返回右表都存在的行。
LEFT OUTER JOIN 返回左表都存在的行。
FULL OUTER JOIN 返回两个表中所有的行，相当于求并集。

3. 修改数据

关系型数据库的基本操作包括增删改查（CRUD），Create，Retrieve，Update，Delete。查询（SELECT）相关的语法在上段进行了介绍。这里对增删改进行介绍。

3.1 INSERT

插入新数据使用 INSERT 语句。其基本语法是：

-- 向表中插入一个新的记录
INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);
-- 可以一次性添加多条记录
INSERT INTO <表名> (字段1, 字段2, ...) VALUES 
(值1, 值2, ...),
(值1, 值2, ...);

3.2 UPDATE

修改数据使用 UPDATE 语句。其基本语法是：

1 2	-- 这里 set 是设置的意思（不是集合的意思） UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...

对于 UPDATE 语句，也可以不带有 WHERE 条件，这时候，会将整个表中所有的数据进行更新。

通常，在 UPDATE 之前，需要通过 SELECT 语句测试一下 WHERE 条件是否正确在做 UPDATE。

3.3 DELETE

删除数据使用 DELETE 语句。其基本语法为：

1	DELETE FROM <表名> WHERE ...

类似的，DELETE 语句也可以不带 WHERE 参数，如果不带的话，就是删除整张表。

4. MySQL

MySQL 是一个常用的开源的、免费的数据库。安装完成MySQL 之后，可以通过其自带的 WorkBench 图形化界面操作数据库，（或者使用命令行工具：MySQL x.x Command Line Client）。通过命令行程序或者图形界面作为客户端，我们可以访问后台运行的服务器程序：mysqld。

下面部分，介绍一些常用的数据库初始化操作与SQL语句技巧。

4.1 库相关操作

在 MySQL 中，使用 SHOW DATABASES, 展示库中所有的数据库。其中 information_schema、 mysql、 performance_schema、 sys 是系统库。不要去改动。

创建新库，使用命令 CREATE DATABASE <库名>。

删除一个库：使用命令 DROP DATABASE <库名>。

切换到对应库, 使用一个库之前，需要首先切换到这个库 USE <库名>。

4.2 表相关操作

列出库中所有的表：SHOW TABLES。

查看一个表的结构：DESC <表名>。

查看创建表的 SQL 语句：SHOW CREATE TABLE <表名>。

创建和删除表：CREATE TABLE, DROP TABLE

修改表：

-- 为表添加一个字段
ALTER TABLE <表名> ADD COLUMN <列名> VARCHAR(10) NOT NULL;
-- 上面创建中， VARCHAR(10) 为类型名

-- 修改列的参数
ALTER TABLE <表名> CHANGE COLUMN <旧列名> <新列名> VARCHAR(20) NOT NULL;

-- 删除列
ALTER TABLE <表名> DROP COLUMN <列名>;

4.3 使用语句技巧

在插入新的记录时，可以使用不同的语句来定义不同的行为，参考下面的例子：

-- 插入新记录：如果记录存在，删掉旧记录 再添加新纪录
REPLACE INTO <表名> (列1, 列2, ...) VALUES (X, X, ...);

-- 插入新记录：如果记录存在，更新该记录。
INSERT INTO <表名> (列1, 列2, ...) VALUES (X, X, ...) ON DUPLICATE KEY UPDATE 列1 = '新内容1', 列2 = '新内容2', 放入需要更新的内容; 

-- 插入新记录，如果存在，则忽略
INSERT IGNORE INTO <表名> (列1, 列2, ...) VALUES (X, X, ...);

创建一个表的快照：即复制一份当前表的数据到一个新的表，可以结合 CREATE TABLE 和 SELECT 语句：

1 2	-- 复制部分内容来创建一个新表 CREATE TABLE <新表名> SELECT * FROM <旧表名> WHERE '筛选语句';

写入查询的结果：可以配合使用 INSERT 和 SELECT 语句来实现直接将查询结果插入表中：

1 2	-- 后面选出的内容一定要和前面指定插入的列一一对应 INSERT INTO <表名> (列名1, 列名2, ..) SELECT 列名a, 列名b, ... FROM <表名>

5. 事务

在执行 SQL 语句过程中，某些业务要求一系列操作必须全部执行而不能仅执行其中的一部分操作。例如转账操作。

这种将多条 SQL 语句作为一个整体进行操作的功能，被称为数据库的事务。数据库可以确保事务范围内的所有操作都可以全部成功或者全部失败。

5.1 事务特点

数据库事务具有如下4个特点（ACID）:

Atomic: 原子性，将所有的 SQL 作为原子工作单元执行，要么全执行，要么都不执行；
Consistent: 一致性，事务完成后，所有数据的状态都是一致的，例如 A 账户减去了 100元，B账户必定增加了 100元。
Isolation: 隔离性。如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离；
Duration: 持久性，即事务完成后，对数据库的修改被持久化存储。

在SQL 中，我们使用 BEGIN 开启一个事务，COMMIT 提交事务。或者使用 ROLLBACK 指定事务执行失败回滚事务。

-- 以一个转账的动作为例
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

5.2 事务的隔离级别

SQL 一共有 4种隔离级别，来用于处理多个并发事务处理同一条记录的时候可能出现的一致性问题。四种级别分别为: Read Uncommitted, Read Committed, Repeatable Read, Serializable。

在数据库中，如果不特别指定隔离级别，会使用默认的隔离级别：Repeatable Read (对于 InnoDB 引擎)。

具体例子参考

a. Read Uncommitted

Read Uncommitted 是隔离级别最低的一种事务。在这种隔离级别下，一个事务可能会读到另一个事务更新但未提交的数据，如果另一个事务回滚，那么当前事务读到的数据就是 “脏数据”，称为 “脏读”。

b. Read committed

在 Read Committed 隔离级别下，一个事务可能遇到不可重复读（Non Repeatable Read）的问题。也就是说，在一个事务内，多次读同一个数据，在这个事务还没结束时，该数据可能被其他事务修改。从而导致前一个事务多次读取的数据内容不一致。

c. Repeatable Read

在 Repeatable Read 隔离级别下，一个事务可能遇到“幻读”（Phantom Read）的问题。幻读是指，在一个事务中，第一次查询某条记录，发现没有。但是试图更新这条不存在的记录，却可以 成功更新，并且再次读取时，该记录出现。

d. Serializable

Serializable 是最严格的隔离级别。在Serializable 隔离级别下，所有事务按照次序依次执行，因此，脏读、不可重复读、幻读都不会出现。但由于事务是串行执行，所以效率会大大下降，故如非特别必要，一般不会使用 Serializable 隔离级别。

全文主要 Reference: 廖雪峰SQL教程