系列文章目录
目录
前言
本文介绍了MySQL数据库的基础操作，主要包括三个方面内容：数据库约束、表设计和查询操作进阶。数据库约束部分详细讲解了NOT NULL、UNIQUE、DEFAULT、PRIMARY KEY和FOREIGN KEY五种约束类型的作用和使用方法。表设计部分阐述了如何根据需求场景设计表结构，重点分析了一对一、一对多和多对多三种关系的实现方式。查询操作进阶部分介绍了聚合查询、分组查询、联合查询（内连接、外连接、自连接）、子查询和合并查询等高级查询技巧，特别强调了在数据量大时联合查询可能带来的性能问题。全文通过大量SQL示例代码，系统性地讲解了MySQL数据库的基础操作知识。
一、数据库的约束
数据库的约束：数据库自动对数据的合法性进行校验检查的一系列机制；
目的：保证数据库中避免被插入或修改一些非法数据；
1. 约束类型：not null
not null：指示某列不能存储 null 值；
create table 表名 (列名 not null, 列名...);2. 约束类型：unique
unique：保证某列的每行必须有唯一的值；
create table 表名 (列名 unique, 列名...);unique 约束会让后续插入数据，修改数据的时候都先触发一次查询操作，通过这个查询操作来确认当前这个记录是否已经存在；
3. 约束类型：default
default：规定没有给列赋值时的默认值；
create table 表名 (列名 default "默认值", 列名...);default 主要应用于指定列插入，未被指定的列会使用默认值；
4. 约束类型：primary key
primary key：not null 和 unique 的结合，确保某列有唯一标识，有助于快速查询到表中的特定记录；
create table 表名 (列名 primary key, 列名...);primary key 最重要的约束，是一行数据记录的身份标识；
一张表里面只能有一个 primary key；一张表里的记录，只能有一个作为身份标识的数据；
对于带有主键的表，每次插入修改数据也会涉及到进行先查询的操作；
mysql 会把带有 primary key 和 unique 的列自动生成索引，从而加快查询速度；
保证主键唯一的方式：自增主键
不手动指定主键值，由数据库和服务器自动分配，服务器会从 1 开始，依次递增分配主键的值；
- 插入数据时，可以将设置为自增主键的列设置为 null，表示由服务器自动分配；
- 插入数据时，也可以手动指定主键的值；
- 手动指定主键值插入后，再插入下一个数据，如果不手动指定，主键就从当前主键的最大值加 1 开始，向后分配；
如果是分布式系统，如何保证生成的主键唯一？
- 1. 如果插入数据的速度比较慢，通常是通过时间戳，就能保证生成的主键唯一；
- 2. 如果插入数据速度很块，就需要时间戳拼接机房编号/主机编号，落在不同主机上的数据也能保证主键唯一；
- 3. 如果数据是插入到同一台机器上，还需要在上述基础上拼接一个随机因子，保证插入数据的主键唯一；
5. 约束条件：foreign key
foreign key：保证表中的数据匹配另一个表中的值的参照完整性；
create table 表名2(列名1，列名2..., foreign key (列名1) references 表名2(列名1));描述了两个表之间的关联关系，用于约束的表叫做父表（表名2），被约束的表叫做子表（表名1）；
插入数据时，服务器会先触发一次查询操作，查看被外键约束的列的值是否在父表对应的列中存在；
父表对子表的约束要注意：
- 1. 子表中插入或者修改数据时，被约束的列的值要在父表对应的列中存在；
- 2. 父表中删除数据时，要保证该数据没有在子表中用到；
- 3. 即使子表为空，也不能删除父表，因为新插入数据时还需要参考父表；
- 4. 指定外键约束的时候，要求父表被关联的这一列得是主键或者 unique；
注意事项：
数据库引入约束之后，执行效率就会受到影响，就可能会降低很多；
二、表的设计
根据实际的需求场景，明确当前要创建几个表，每个表都有哪些列，这些表之间是否存在一定的联系。
1. 梳理好需求中的实体；
2. 再确定好实体间的关系（一对一，一对多，多对多）；
1. 一对一
例如：一用户只能拥有一个账号；一个账号也只能被一个用户拥有；
-- 一个用户只能拥有一个账号
user(userId, name, acountId);
-- 一个账户也只能被一个用户拥有
acount(acountId, username, password, userId);2. 一对多
例如：一个用户只能在一个地区；一个地区，可以包含多个用户；
-- 一个用户只能有一个地区
user(userId, username, address);
-- 一个地区可以有多个用户
address(addressId, addressName);3. 多对多
一个用户可以选择多个游戏，一个游戏可以有多个用户；
-- 一个用户可以参与多个游戏
user(userId, username);
-- 一个游戏可以有多个用户
game(gameId, gameName);
-- 借助关联表表示
user_game(userId, gameId);
三、查询操作进阶
1. 插入搭配查询
把查询语句的结果，作为插入的数值；
-- 插入搭配查询
insert into 表1 select * from 表2;要求查询出来的结果集合，列数和类型要和要插入的表匹配；
2. 聚合查询
表达式查询是针对列和列之间运算；
聚合查询是针对行和行之间运算；
1. sql 中的聚合函数
1. count：查询出来的结果集的行数；
-- 查询总的行数
select count(*) from 表名;
-- 查询有多少列不为空的行数
select count(列名) from 表名;
-- 查询列不重复的行数
select count(distinct 列名) from 表名;count 里面填写的是 *，表示查询的是总的行数；
如果 count 里面填写的是列名，遇到空行就不统计了；
同时 count 里面可以填写 distinct 和列名，统计不重复的行数；
count 在代码中调用是非常有必要的；
2. sum：把某一列的若干行进行求和运算；
-- 针对某一列求和
select sum(列名) from 表名;
-- 针对表达式求和
select sum(表达式) from 表名;如果列的值为 null，就会被自动排除掉；
求和时 mysql 会尝试把列转换为 double，如果转换成功，就可以进行运算，如果没转成就会报错；
针对表达式求和时，会先求表达式的值，得到临时表，再针对临时表求和；
avg，max，min 用法和 sum 相同；
2. group by
使用 group by 分组，再针对每个组分别进行聚合查询；
针对列进行分组，把这一列中值相同的行，分成到一组中，得到若干个组；
再针对这些组，分别使用聚合函数；
-- 分组聚合查询
select 列名1, avg(列名2) from 表名 group by 列名1;如果针对分组之后，不适用聚合函数，此时的结果就是查询出每一组中的某个代表数据；
因此，分组通常时搭配聚合函数使用的；
使用 group by 的时候，还可以搭配条件，但是需要区分清楚是分组之前的条件还是分组之后的条件；
分组之前：条件在 group by 前面，用 where
-- 聚合查询搭配条件：分组之前
select 列名1, avg(列名2) from 表名 where 条件 group by 列名1;分组之后：条件在 group by 后面，用 having
-- 聚合查询搭配条件：分组之后
select 列名1, avg(列名2) from 表名 group by 列名1 having 条件; 分组前后都有条件： 分组前使用 where，分组后使用 having
-- 分组前后都有条件，分组前条件用 where，分组后条件用 having
select 列名1, avg(列名2) from 表名 where 条件1 group by 列名1 having 条件2;3. 联合查询
1. 内连接
笛卡尔积是将两张表的行通过排列组合的方式，得到一个更大的表；
笛卡尔积的列数，是这两个表的列数相加；
笛卡尔积的行数，是这两个表的行数相乘；
-- 笛卡尔积
select * from 表1, 表2;笛卡尔积的基础上，加上连接条件：
-- 加上连接条件
select * from 表1, 表2 where 表1.列名 = 表2.列名;
select * from 表1 join 表2 on 表1.列名 = 表2.列名;在上述基础上，添加条件（聚合查询等），对数据进行筛选：
-- 添加条件筛选数据
select * from 表1, 表2 where 表1.列名 = 表2.列名 and 条件;
select * from 表1 join 表2 on 表1.列名 = 表2.列名 and 条件;
-- 多张表联合查询
select 列名1, 列名2, 列名3... from 表1, 表2, 表3... where 连接条件1 and 连接条件2 and ... ;
select 列名1, 列名2, 列名3... from 表1 join 表2 on 连接条件1 and 表2 join 表3 on 连接条件2...;
注意：如果实际情况中，数据量很大，多表联合查询会生成大量的临时结果，这个过程非常消耗时间，给服务器的响应速度造成很大的影响；因此，联合查询之前要评估好数据量。
2. 外连接
如果两张表，里面的记录存在对应关系，内连接和外连接的结果是一致的；
如果存在不对应的记录，内连接和外连接就会出现差别；
左外连接：以左侧表为基准，保证左侧表的每个数据都会出现在最终结果中；如果在右侧表中不存在，对应列就会填成空；
右外连接：以右侧表为基准，保证右侧表的每个数据都会出现在最终结果中；如果在左侧表中不存在，对应列就会填成空；
-- 左外连接
select * from 表1 left join 表2 on 表1.列名 = 表2.列名;
-- 右外连接
select * from 表1 right join 表2 on 表1.列名 = 表2.列名;3. 自连接
进行行和行之间的比较；
SQL 不能进行行和行之间的比较，这时候需要用到自连接；
-- 进行行与行之间的比较，自连接
select 列名, 列名... from 表名 as 表1, 表名 as 表2 where 连接条件 and 条件...; 4. 子查询
把多个简单 sql 拼接成一个复杂 sql；
-- 单行子查询
select 列名, 列名... from 表名 where 列名 = (select 列名 from 表名 where 列名 = ?) and 条件;
-- 多行子查询
select 列名, 列名... from 表名 where 列名 in (select 列名 from 表名 where 条件1 or 条件2);
5. 合并查询
允许把两个不同的表 sql 查询的结果集合，合并到一起；
合并的两个 sql 结果集的列需要匹配，列的个数和类型需要一致；
合并的时候会去重，如果不想去重，需要用 union all；
-- 合并查询
select 列名 from 表1 union select 列名 from 表2;