MySQL操作进阶
系列文章目录
MySQL的基础操作-CSDN博客
目录
系列文章目录
前言
一、数据库的约束
1. 约束类型：not null
2. 约束类型：unique
3. 约束类型：default
4. 约束类型：primary key
5. 约束条件：foreign key
二、表的设计
1. 一对一
2. 一对多
3. 多对多
三、查询操作进阶
1. 插入搭配查询
2. 聚合查询
1. sql 中的聚合函数
2. group by
3. 联合查询
1. 内连接
2. 外连接
3. 自连接
4. 子查询
5. 合并查询
前言
本文介绍了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;