数据库

数据库：DataBase（DB），是存储和管理数据的仓库

数据库管理系统：DataBase Management System（DBMS），操纵和管理数据库的大型软件

SQL：Structured Query Language，操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准

关系型数据库：（RDBMS），建立在关系模型基础上，由多张相互连接的二维表组成的数据库

特点：

使用表存储数据，格式统一，便于维护
使用SQL语言操作，标准统一，使用方便，可用于复杂查询

数据模型：在一个数据库服务器中可以创建多个数据库，而且多个数据库相互独立；在一个数据库中可以创建多张表，一张表中可以存储多条的数据。

SQL简介

SQL：一门操作关系型数据库的编程语言，定义操作所有关系型数据库的统一标准。

通用语法：

SQL语句可以单行或多行书写，以分号结尾
SQL语句可以使用空格/缩进来增强语句的可读性，不影响效果
MySQL数据库的SQL语句不区分大小写
注释：
- 单行：– 注释内容 / # 注释内容（MySQL特有）
- 多行：/* 注释内容 */

分类：

DDL（Data Definition Language）：数据定义语言，用来定义数据库对象（数据库、表、字段）
DML（Data Manipulation Language）：数据操作语言，用来对数据库表中的数据进行增删改
DQL（Data Query Language）：数据查询语言，用来查询数据库中表的记录
DCL（Data Control Language）：数据控制语言，用来创建数据库用户、控制数据库的访问权限

数据库设计—DDL

数据库操作

查询

查询所有数据库：show databases;
查询当前数据库：select database();

使用

使用数据库：use 数据库名;

创建

创建数据库：create database [if not exists] 数据库名;

删除

删除数据库：drop database [if exists] 数据库名;

注意：上述语法中的database也可替换成schema

表操作

创建

语法

create table 表名{
	字段1 字段类型 [约束] [comment 字段1注释],
	字段2 字段类型 [约束] [comment 字段2注释]
}[comment 表注释];

约束

概念：作用于表中字段上的规则，用于限制存储在表中的数据
目的：保证数据库中数据的正确性、有效性和完整性
包括：
- 非空约束：not null
- 唯一约束：unique
- 主键约束：primary key（非空且唯一）（auto_increment：自动增长）
- 默认约束：default（后接默认值，表示若未指定字段值，则采用默认值）
- 外键约束：foreign key（让两张表的数据建立连接，保证数据的一致性和完整性）

数据类型

数值类型
- tinyint：1byte，小整数值
- smallint：2byte，中整数值
- mediumint：3byte，大整数值
- int：4byte，大整数值
- bigint：8byte，极大整数值
- float：4byte，单精度浮点数
- double：8byte，双精度浮点数，eg.double(5,2)，5为整个数字长度，2为小数位个数
- decimal：小数值（精度更高）（用字符串存储，精准）
字符串类型
- char：定长字符串，性能高，但是浪费空间
- varchar：变长字符串，节省空间，但是性能低
日期时间类型
- date：日期值，YYYY-MM-DD
- time：时间值或持续时间，HH:MM:SS
- year：年份值
- datetime：混合日期和时间值

创建流程

页面原型+需求 => 原型字段（类型、约束）+ 基础字段（id、create_time、update_time）

create_time记录当前数据的插入时间，update_time记录当前数据最后的更新时间

查询

查询当前数据库所有表：show tables;
查询表结构：desc 表名;
查询建表语句：show create table 表名;

修改

添加字段：alter table 表名 add 字段名类型(长度) [comment 注释] [约束];
修改字段类型：alter table 表名 modify 字段名新数据类型(长度);
修改字段名和字段类型：alter table 表名 change 旧字段名新字段名类型(长度) [comment 注释] [约束];
删除字段：alter table 表名 drop column 字段名;
修改表名：rename table 表名 to 新表名;

删除

删除表：drop table [if exists] 表名;

在删除表时，表中的数据也都会被删除

数据库操作—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, …);

注意：

插入数据时，指定的字段顺序需要与值的顺序一一对应
字符串和日期型数据应该包含在引号中
插入的数据大小应该在字段的规定范围内
插入数据时别忘了create_time和update_time值为now()

修改数据(UPDATE)

update 表名 set 字段名1 = 值1, 字段名2 = 值2, … [where 条件];

注意：

修改语句的条件没有时，则会修改整张表的所有数据
修改时别忘了修改update_time为now()

删除数据(DELETE)

delete from 表名 [where 条件];

注意：

删除语句的条件没有时，则会删除整张表的所有数据
不能删除某一个字段的值（需要使用UPDATE，将该字段的值置为NULL）

数据库操作—DQL

格式：select 字段列表 from 表名列表 where 条件列表 group by 分组字段列表 having 分组后条件列表 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 条件列表；

比较运算符：>、>=、<、<=、=、<>或!=、between…and…（含最大最小值）、in(…)（在括号内列表中的值，多选一）、like 占位符（模糊匹配：_匹配一个字符、%匹配任意个字符（可以为0个））、is null、is not null

逻辑运算符：and或&&、or或||、not或！

分组查询（group by）

select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件]；

where和having的区别：

执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
判断条件不同：where不能对聚合函数进行判断，而having可以。

注意：

分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义
执行顺序：where > 分组操作+聚合函数 > having

聚合函数：将一列数据作为一个整体，进行纵向运算。

select 聚合函数(字段列表) from 表名；

聚合函数包括：

count：统计数量
max
min
avg：平均值
sum

注意：

null值不参与所有聚合函数的运算
统计数量可以用count(*)、count(字段)、count(常量)，推荐使用count(*)

排序查询（order by）

select 字段列表 from 表名 [where 条件列表] [group by 分组字段] order by 字段1 排序方式1，字段2 排序方式2，…；

排序方式：

ASC：升序（默认值）
DESC：降序

注意：如果是多个字段排序，只有当第一个字段值相同时，才会根据第二个字段进行排序。

分页查询（limit）

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

注意：

起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数
分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT
如果查询的是第一页数据，起始索引可以省略，直接简写为limit 查询记录数

流程控制函数

if(表达式, true_value, false_value)
case expr when value1 then result1 [when value2 then result2…] [else result] end

多表设计

概述

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，分为：

一对多（多对一）
多对多
一对一

一对多

一的一方叫父表，多的叫子表。

实现：在数据库表中多的一方添加字段，来关联一的一方的主键。

外键约束

语法：

创建表时指定：

create table 表名{
	字段名 数据类型，
	…
	[constraint][外键名称] foreign key (外键字段名) references 主表(字段名)
};

建完表后添加外键

1	alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(字段名)

物理外键

概念：使用foreign key 定义外键关联另外一张表

缺点：

影响增、删、改的效率（需要检查外键关系）
仅用于单节点数据库，不适用与分布式、集群场景
容易引发数据库的死锁问题，消耗性能

逻辑外键（推荐）

概念：在业务层逻辑中，解决外键关联

通过逻辑外键可以解决上述问题

一对一

多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升操作效率

实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）

多对多

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

设计流程

阅读页面原型及需求文档，分析各个模块涉及到的表结构，及表结构之间的关系
阅读页面原型及需求文档，分析各个表结构中具体的字段及约束

多表查询

概述

多表查询：指从多张表中查询数据

笛卡尔积：在数学中，两个集合的所有组合情况。（在多表查询中，需要消除无效的笛卡尔积）

分类：

连接查询
- 内连接：相当于查询A、B交集部分数据
- 外连接：
  - 左外连接：查询左表所有数据（包括两张表交集部分数据）
  - 右外连接：查询右表所有数据（包括两张表交集部分数据）
子查询

内连接

语法：

隐式内连接：select 字段列表 from 表1，表2 where 条件…；
显示内连接：select 字段列表 from 表1 [inner] join 表2 on 连接条件…；

外连接

语法：

左外连接：select 字段列表 from 表1 left [outer] join 表2 on 连接条件…；
右外连接：select 字段列表 from 表1 right [outer] join 表2 on 连接条件…；

子查询

概述：SQL语句中嵌套select语句，称为嵌套查询，也叫子查询

形式：select * from t1 where column1 = (select column1 from t2 …)；

子查询外部的语句可以是insert/update/delete/select的任何一个，最常见的是select。

分类：

标量子查询：返回单个值
列子查询：返回一列
行子查询：返回一行
表子查询：返回多行多列

标量子查询

返回单个值（数字、字符串、日期等），最简单的形式
常用的操作符：=、<>、>、>=、<、<=

列子查询

返回一列（可以是多行）
常用的操作符：in、not in等

行子查询

返回一行（可以是多列）
常见的操作符：=、<>、in、not in（左右都用括号括起来，然后左右相等即可）

表子查询

返回多行多列，常作为临时表（放在from后面）
常见的操作符：in

查询流程

先分析涉及到的表有哪些
然后分析表之间的关系，如何进行联查
最后考虑sql语句怎么写

子查询性能不高，能用连接查询时尽量用连接查询

事务

介绍

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

注意：默认MySQL的事务是自动提交的，即当执行一条DML语句，MySQL会立即隐式的提交事务。

事务控制

开启事务：start transaction; / begin;
提交事务：commit;
回滚事务：rollback;

四大特性

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

索引

介绍

索引（index）是帮助数据库高效获取数据的数据结构。

优点：

提高数据查询的效率，降低数据库的IO成本
通过索引对数据进行排序，降低数据排序的成本，降低CPU消耗

缺点：

索引会占用存储空间
索引大大提高了查询效率，同时却也降低了insert、update、delete的效率

结构

MySQL数据库支持的索引结构有很多，如：Hash索引、B+Tree索引、Full-Text索引等。一般默认指B+Tree结构组织的索引。

若用二叉搜索树或者红黑树，会在大数据量的情况下，层级深，检索速度慢

B+Tree（多路平衡搜索树）

每一个节点，可以存储多个key（有n个key，就有n个指针）
所有的数据都存储在叶子节点，非叶子节点仅用于索引数据（最终都要找到叶子节点才能拿到相应的数据）
叶子节点形成了一颗双向链表，便于数据的排序及区间范围查询

语法

创建索引：create [unique] index 索引名 on 表名(字段名…);
查看索引：show index from 表名;
删除索引：drop index 索引名 on 表名;

注意：

主键字段，在建表时会自动创建主键索引（主键索引的性能是最高的）
添加唯一约束时，数据库实际上会添加唯一索引