1 数据库系统

数据库系统包含多个部分，不只是包含 Mysql、Redis 这些数据库管理软件：

数据库系统 = 数据库（DB）+ 数据库管理系统（DBMS）+ 硬件 + 操作系统 + 用户 + 应用

我们常用的 Mysql、Postgres、Redis 这些都是数据库管理系统，这里数据库，也不特指落盘的非带电存储介质，而是存储数据的容器，即：只要是存储数据的容器，或者说数据结构，就是数据库

2.SQL 三大语言

• DDL，Data Define Language，用来定义表结构、数据库、索引、视图等，即直接操作的对象是数据库的结构而非数据本身

• DML，Data Manipulation Language，用来对数据进行操作，如 insert、select、delete、update，即直接操作的对象是数据库的结构而非数据本身

• DCL，Data Control Language，用来控制数据库的用户、权限等，控制书能够访问数据库的哪些表，如 grant、revoke、create user、drop user 等

3. 关系型数据库

• 关系型数据库中，各条数据记录的顺序是可以任意颠倒的，不影响每条记录内部的数据关系

• 通配符：SELECT * FROM user_table WHERE user_name LIKE '_W%'，这里 _ 表示匹配任意一个字符，% 表示匹配零个、一个或者任意多个字符；同时，一般数据库默认的校验格式是 utf8_general_ci，所以可能不区分大小写

4. 数据库三大范式

“范式”，描述的是数据关系的模型，

4.1 第一范式

每个字段不可以再拆分。比如学校，可以拆分为学校名称、学校地址、学校排名等，含有这样的字段的表不满足第一范式。关系型数据库的表结构必须满足第一范式，支持不满足第一范式的数据库就是非关系型数据库，所有字段都能够直接用 SQL 规定的数据类型表示时，这个表就原生支持第一范式

4.2 第二范式

在满足第一范式的基础上，不存在非关键字段与任意候选键之间的部分函数依赖（存在复合主键时）。非关键字段，即非主键约束字段；候选键，即非主键、非外键、非无主键情况下的唯一键。

比如创建一个学生选修课的成绩表，学号、学生姓名、年龄、课程id、学分、成绩 含有这些字段的一个表。这个表中的信息可以分为两部分：学生信息、课程信息。将学号、课程id作为联合主键。这时，候选键就是学号和课程id，其余字段就是非关键字段。获取学生相关的信息时，通过学号获取；获取课程相关信息时，通过课程id获取，这时这样一个表中就存在了部分函数依赖。不满足第二范式的表，会存在以下问题：

1. 数据冗余，相同的数据会存储多份，浪费空间
2. 更新异常，比如需要更新上面的课程信息，如果更新有漏的话，会出现一门课程两份信息的情况
3. 插入异常，比如在上面的表中，学校新开了一门课，要插入这个表，但是还没有考试，没有对应的学生信息、成绩信息，就无法插入
4. 删除异常，现在有一批学生毕业，要把他们的考试记录删掉，可能造成误把在校生的这门课的成绩删掉的情况

当一个表中只存在一列时，就天然满足第二范式

4.3 第三范式

在第二范式的基础上，不存在非关键字段，对任意候选键的传递依赖关系。第三范式能够解决数据冗余和更新、插入、删除异常。

比如现在有一个学生+学院表，字段为 学号、学生姓名、年龄、学院id、学院名称、学院电话，很明显这里的学号我们会作为主键，但是信息的获取存在逻辑上的传递关系：学号 -> 学生信息 以及 学号 -> 学院id -> 学院信息，这时，虽然非关键字段与候选键不存在部分函数依赖，但是存在逻辑上的传递依赖关系，这样的表就不满足第三范式。

让其满足第三范式的方法也很简单，把学院信息单独拉出来建一个表就可以，原表只保留学院id即可

5. 聚合查询

聚合查询本质上是针对数据表中的行和行进行运算，比如 COUNT、SUM、AVG、MIN、MAX，使用的是 MySQL 提供的函数

• COUNT(列名)，统计表中的行数，如果这一列检索出来为 null，则这一行不算
• SUM(列名)，统计相应列的所有值的和。因为在MySQL中，null和任何类型运算都是null，null 没有等价的值，所以 SUM 不会将 null 加入运算

6 group by

含有 gruop by 的语句中，select 后查询的字段必须是分组依据字段，或者是包含在聚合函数中的字段；反过来，使用聚合函数，如果是对整个表中的某一列做聚合查询，那么不需要加 group by，但是如果是分组做的，那么需要搭配 group by 使用。

7 having

having 和 where 一样都是条件过滤，但是作用对象不同，where 是直接对每一列的真实数据的过滤，但是 having 作用的是 group by 获得的分组信息

8. 联合查询

为了避免部分函数依赖、传递依赖，对设计出的单个表执行 SQL 可能会检索不全，所以我们经常使用联合查询。

8.1 内连接

联合查询首先进行的是笛卡尔积，即把 from 指定的每一个表的每一项一一组合，后通过 where 子句（有的话）对所有组合结果过滤

联合查询步骤可以分为五步

1. 明确哪些表需要参与查询
2. 做笛卡尔积
3. 明确连接条件
4. 明确结果筛选条件
5. 精简查询字段

select content from table1, table2... where connect_condition [where condition];
select content from table1 join table2 on connect_condition [where conditoin];

8.2 外连接

外连接和内连接的步骤、语法格式大致相同，分为左外连接和右外连接。语法格式为：

select content from table1 left join table2 on connect_condition [where condition];
select content from table1 right join table2 on connect_condition [where condition];

外连接可以让我们快速查看到两个表中哪些数据在另一个表中没有对应的数据。right join 以右侧表为基准，如果左侧表没有对应内容，就在返回结果时用 null 补全；left join 相反，右侧表没有对应的就用 null 补全显示

8.3 自连接

自连接目的是支持表自己和自己比较，语法格式和内连接一样，不过表是一张表，同时通过别名来区分表

9. 视图

视图是用来隐藏字段、区分权限而基于 select 操作的其他表创建的“拼接”出来的表，语法结构为

create view view_name [(columns)] as (select* from table1, table2);

视图具有简单性（将复杂的查询封装为一个简单的查询）、安全性（过滤掉敏感字段或增加权限）、逻辑数据独立性、重命名列。理论上视图的修改也会影响原表，反过来亦然，但是视图建立在聚合查询、自查询等非直接对应数据的情况除外

mysql 中不支持全外连接

10. 关于索引

10.1 主键索引 and 聚集索引

主键索引 和 聚集索引 是同义词（或者更严格一点，主键索引一定是聚集索引，但是聚集索引不一定是主键索引），在定义了 PRIMARY KEY 的情况下，InnoDB 使用其作为聚集索引；若未定义 PRIMARY KEY，则尝试使用第一个UNIQUE 和 Not Null 的列作为聚集索引；如果这也没有的话，就为新插入的行生成一个行号，即一个6字节的 row_id 记录，单调递增，并使用它作为聚集索引，row_id 也是行的隐藏字段之一

10.2 普通索引

没有唯一性的限制，一个表中可以有多个普通索引，每建立一个普通索引，都会依据相应列建立一棵索引树。一方面，这意味着被建立普通索引的字段可以更快的查询；另一方面，也意味着更高的管理维护成本，所以必要时再加，同时建立普通索引的字段应该尽量不重复，否则优化效果有限。

添加普通索引 index(column) 或者是直接创建时加上 index 标识，通过 create index index_name on table(column)

10.3 唯一索引

在表上定义唯一键 UNIQUE 时，自动创建唯一索引，相比普通索引区别是不允许重复

10.4 非聚集索引

聚集索引之外的索引叫做非聚集索引，作为非聚集索引的字段，在相应索引树的叶子节点中只保存其对应行的主键值和自身字段值，InnoDB在查询这些非聚集索引字段时，只去拿到主键值，然后到聚集索引来查询，这个过程叫做回表查询

10.5 索引覆盖

但是上面的回表查询并不一定会发生，比如查询的字段就是非聚集索引字段本身，那么在查找到叶子节点后，直接拿到目标值，不会再拿主键值去聚集索引里查询；除此之外，查询主键也不会回表查询，而是直接在聚集索引或非聚集索引里查询（前面提到过叶子节点直接就保存了对应行的主键值）

主键索引在删除时，如果有设置自增的话，要先取消自增属性在 drop primary key

---

关于各大关键字的执行顺序

1. from/join，首先确定数据集，完成临时数据集的创建，同时联合查询的表名的别名也在这里定义，后续都可以使用
2. where，根据筛选条件完成数据的筛选，这里不能使用聚合函数
3. group by，对初筛过的临时数据集按照指定分组依据分组
4. 聚合函数根据 group by 的结果进行聚合查询
5. having，分组完成后过滤分组结果，使用聚合函数
6. select [distinct]，筛选要展示的列，需要时去重（select 起的别名，mysql做了特殊处理，可以被having识别，order by 也支持）
7. order by，根据排序标准进行排序
8. limit，根据条数和偏移量截取结果