数据库设计-DDL
- DDL英文全称是Data Definition Language(数据定义语言)，用来定义数据库对象（数据库、表）。
- DDL中数据库的常见操作：查询、创建、使用、删除。
项目开发流程
针对数据库来说，主要包括三个阶段：
- 数据库设计阶段：参考页面原型及需求文档设计数据库表结构
- 数据库操作阶段：根据业务功能的实现，编写SQL语句对数据表中的数据进行增删改查操作
- 数据库优化阶段：通过数据库的优化来提高数据库的访问性能。优化手段：索引、SQL优化、分库分表等
数据库操作
| SQL语句 | 说明 |
|---|---|
| show databases; | 查询所有数据库 |
| select database(); | 查询当前数据库 |
| use 数据库名; | 使用指定数据库 |
| create database 数据库名; | 创建数据库 |
| create database if not extists 数据库名; | 数据库不存在，则创建该数据库；如果存在则不创建 |
| drop database 数据库名; | 删除指定数据库 |
| drop database if exists 数据库名; | 如果数据库存在，再执行删除，否则不执行删除 |
| use 数据库名; | 使用指定数据库 |
注意：
- 在同一个数据库服务器中，不能创建两个名称相同的数据库。
- 如果删除一个不存在的数据库，将会报错。
- 语法中的database也可以替换schema。
表操作
创建
语法
create table 表名(
字段1 字段1类型 [约束] [comment 字段1注释 ],
字段2 字段2类型 [约束] [comment 字段2注释 ],
......
字段n 字段n类型 [约束] [comment 字段n注释 ]
) [ comment 表注释 ];
约束
主键auto_increment：
- 每次插入新的记录时，数据库自动生成id字段（主键）下的值。
- 具有auto_increment的数据是一个整数序列开始增长（从一开始自增）
数据类型
MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型。
数值类型：
字符串类型：
日期类型：
char与varchar都可以描述字符串，char是定长字符串，指定长度多少，就占用多少个字符串，和字段值的长度无关。而varchar是变长字符串，指定的长度为最大占用长度。相对来说，char的性能可能会更高些。
查询
- 查看指定的表结构：desc 表名;
- 查询指定表的建表语句：show create table 表名;
修改
添加字段
alter table 表名 add 字段名 字段类型（长度） [commnet 注释] [约束];
修改数据类型
- alter table 表名 modify 字段名 新数据类型（长度）;
- alter table 表名 change 旧字段名 新字段名 字段类型（长度） [commnet 注释] [约束];
删除字段
alter table 表名 drop 字段名;
修改表名
rename table 表名 to 新表明;
删除
drop table [ if exists ] 表名;
数据库操作-DML
DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增、删、改操作。
增加（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, ...);
insert操作的注意事项：
- 插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
- 字符串和日期类型数据应该包含在引号中。
- 插入数据的大小，应该在字段的规定范围内。
修改（update）
update 表名 set 字段名1 = 值1 , 字段名2 = 值2 , .... [where 条件] ;
删除（delete）
delete from 表名 [where 条件] ;
数据库操作-DQL
DQL英文全称是Data Query Language(数据查询语言)，用来查询数据库表中的记录。
查询关键字：SELECT
查询操作是所有SQL语句当中最为常见，也是最为重要的操作。在一个正常的业务系统中，查询操作的使用频次是远高于增删改操作的。而在这个查询操作过程中，还会涉及到条件、排序、分页等操作。
语法
基本查询
查询多个字段
select 字段1, 字段2, 字段3 from 表名;
查询所有字段（通配符）
select * from 表名;
设置别名
select 字段1 [ as 别名1 ] , 字段2 [ as 别名2 ] from 表名;
去除重复记录
select distinct 字段列表 from 表名;
条件查询
select 字段列表 from 表名 where 条件列表;
比较运算符
等于运算符
- 等于运算符判断等号两边的值，字符串或者表达式是否相等，如果相等则返回1，不相等则返回0。
- 在使用等号运算符时，遵循如下规则：
- 如果等号两边都为字符串时，则MySQL会按照字符串进行比较，其比较的是每个字符串中字符的ANSCII编码是否相等
- 如果等号两边都是整数，则MySQL会按照整数比较两个值的大小
- 如果等号两边一个是整数，一个是字符串，则MySQL会先将字符串转换为数字进行比较
- 如果等号两边有一个为NULL,则比较结果为NULL
安全等于运算符（<=>）
使用安全等于运算符时，两边的操作数值都为NULL时，返回结果为1而不是NULL，其中一边为NULL时，返回结果是0而不是NULL，其他返回结果与等于运算符相同。
不等于运算符
不等于运算符，用于判断两边的数值，字符串或表达式的值是否不相等，如果相等返回1不相等则返回0。不等于运算符不能判断NULL值，如果两边的值有任意一个为NULL，或两个都为NULL，则结果为NULL。
逻辑运算符
逻辑与运算符
逻辑与运算符是当给定的所有值均为非0值，并且都不为NULL时，返回1；当给定的一个值或多个值为0时则返回0；否则返回NULL。
逻辑非运算符
逻辑非运算符表示当给定的值为0时返回1，当给定的值为非0值时返回0，当给定的值为NULL时返回NULL。
逻辑或运算符
逻辑或运算符是当给定的值都不为NULL时,并且有任何一个值为非0值时，则返回1，否则返回0；当一个值为NULL，并且另外一个值为非0值时，返回1否则返回NULL；两个值都为NULL时返回NULL。
逻辑异或运算符
逻辑异或运算符，当给定的值中任何一个值为NULL时返回NULL；如果两个非NULL值，两个都是0或者都不等于0时返回0如果一个值为0，另外一个值不为0，则返回1。
运算符的优先级
聚合函数
之前我们做的查询都是横向查询，就是根据条件一行一行的进行判断，而使用聚合函数查询就是纵向查询，它是对一列的值进行计算，然后返回一个结果值。（将一列数据作为一个整体，进行纵向计算）
- 语法：select 聚合函数（字段列表）from 表名;
- 注意：聚合函数会忽略空值，对NULL值不作为统计。
常用的聚合函数：
分组查询
分组：按照一列或者某几列，把相同的数据进行合并输出。
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having分组后过滤条件];
注意事项：
- 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义
- 执行顺序：where > 聚合函数 > having
where与having区别（面试题）
- 执行时机不同：where是分组之前进行过滤的，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
- 判断条件不同：where不能对聚合函数进行判断，而having可以。
排序查询
排序在日常开发中是非常常见的一个操作，有升序排序，也有降序排序。
select 字段列表 from 表名 [where 条件列表] [group by 分组字段 ] order by 字段1 排序方式1 , 字段2 排序方式2 … ;
排序方式：
- ASC：升序（默认值）
- DESC：降序
分页查询
分页操作在业务系统开发时，也是非常常见的一个功能，日常我们在网站中看到的各种各样的分页条，后台也都需要借助于数据库的分页操作。
select 字段列表 from 表名 limit 起始索引, 查询记录数;
注意事项：
- 起始索引从0开始。计算公式：起始索引=（查询页码-1）* 每页显示记录数。
- 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是limit。
- 如果查询的是第一页数据，起始索引可以省略，直接简化为limit条数
好处：约束返回结果的数量可以减少数据表的网络传输量，也可以提升查询效率。如果我们知道返回结果只有1条，就可以使用limit 1，告诉select语句只需要返回一条记录即可，这样的好处就是select不需要扫描完整的表，只需要检索到一条符合条件的记录即可返回。
多表设计
由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：
- 一对多（多对一）：在数据库表中多的一方，添加字段，来关联属于一这方的主键。
- 多对多：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键。
- 一对一：一对一关系表在实际开发中应用起来比较简单，通常用来做单表的拆分，也就是将一张大表拆分成两张小表，将大表中的一些基础字段放在一张表中，将其他的字段放在另外一张表当中，以此来提高数据的操作效率。
外键约束
- 外键约束：让两张表的数据建立连接，保证数据的一致性和完整性。
- 对应的关键字：foreign key
语法
-- 创建表时指定
create table 表名(字段名 数据类型,...[constraint] [外键名称] foreign key (外键字段名) references
主表 (主表列名));
-- 建完表后，添加外键
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名);
物理外键与逻辑外键
- 物理外键：使用foreign key定义外键关联另外一张表。
- 逻辑外键：在业务层逻辑中，解决外键关联。通过逻辑外键，就可以很方便的解决上述问题。
物理外键缺点：
- 影响增、删、改的效率（需要检查外键关系）
- 仅用于单节点数据库，不适用于分布式、集群场景。
- 容易引发数据库的死锁问题，消耗性能。
在现在的企业开发中，很少会使用物理外键，都是使用逻辑外键。甚至在一些数据库开发规范中，会明确指出禁止使用物理外键foreign key。
