MySQL：CRUD进阶(七千五百字)

文章目录

前置文章：

MySQL：CRUD初阶

📑1. 数据库约束

MySQL命令是不区分大小写的，为了风格统一，以下只使用小写字母。

🌤️1.1 约束类型

✨1. NOT NULL - 指示某列不能存储 NULL 值。
✨2. UNIQUE - 保证某列的每行必须有唯一的值。
✨3. DEFAULT - 规定没有给列赋值时的默认值。
✨4. PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
✨5. FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
✨6. CHECK - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略CHECK子句。

🌤️1.2 NULL约束

指示某列不能存储 NULL 值。

createtable 表名(字段名 字段类型 notnull,字段名 字段类型 notnull,...);

举例：

🌤️1.3 Unique：唯一约束

保证某列的每行必须有唯一的值。

createtable 表名(字段名 字段类型 unique,字段名 字段类型,...);

举例：

注意：unique 会自动创建索引，以便于快速查找。暂时先了解一下，在下一篇文章中再详细介绍吧

🌤️1.4 Default：默认值约束

规定没有给列赋值时的默认值。

createtable 表名(字段名 字段类型 default,字段名 字段类型,...);

示例：

🌤️1.5 Primary key：主键约束

NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。

createtable 表名(字段名 字段类型 primarykey,...);

注意：

1. 不允许一个表中出现多个主键
2. 主键会在修改/插入的时候先触发查询
3. 数据库会针对主键创建‘索引’，方便快速查询。暂时先了解一下，在下一篇文章中再详细介绍吧

示例：

💫1.5.1 自增主键

在 MySQL 中，当主键定义为自增长后，这个主键的值就不再需要用户输入数据了，而由数据库系统根据定义自动赋值。每增加一条记录，主键会自动以1,2,3,4…的形式增长。

createtable 表名 (字段名 字段类型 primarykeyauto_increment,...);

注意：

1. 默认情况下，auto_increment 的初始值是 1，每新增一条记录，字段值自动加 1。
2. 一个表中只能有一个字段使用 auto_increment 约束，且该字段必须有唯一索引，以避免序号重复（即为主键或主键的一部分）。
3. auto_increment 约束的字段必须具备 not null 属性。
4. auto_increment 约束的字段只能是整数类型（tinyint、smallint、int、bigint 等）。
5. auto_increment 约束字段的最大值受该字段的数据类型约束，如果达到上限，auto_increment 就会失效。

示例：

此时我们手动插入一个id为100的数据，也是可以做到的。之后你将id交给MySQL来处理，会发现它是在你手动插入数据的id的向后递增的。

此时你把id为101的数据删掉，然后在插入一个新的数据，可以看到：

小结：

• 其实在MySQL中，给每个表都记录了一个‘自增主键的最大值’，后续继续插入数据，无论之前的最大值是否存在，都是根据之前保存的最大值继续向后分配的。

🌤️1.6 Foreign key：外键约束

保证一个表中的数据匹配另一个表中的值的参照完整性。换句话说外键用于关联其他表的主键或唯一键。

createtable 表名(字段名 字段类型,foreignkey(当前表中的字段名)references 
    另一个表名(另一个表中的字段名));

在上述操作中，student为子表，class为父表

注意：

1. 在往子表中插入数据的时候，会触发对父表的查询。
2. 如果父表中的某个记录，被子表引用了，此时就不能删除/修改父表中对应的记录了。
3. 在删除表时，要确保先删除子表，再删除父表。

🌤️1.7 Check约束

保证列中的值符合指定的条件。对于MySQL数据库，对check子句进行分析，但是忽略check子句。
关于check约束可以看一看这篇文章：
MySQL 8.0 新特性之检查约束（CHECK）

📑2. 表的设计

🌤️2.1 一对一

场景：
一个学生，只能有一个账号。一个账号，只能被一个学生持有。

设计表的做法：

1. 创建一个表，同时包含学生和账号的信息 student_account(studentId,name,accountName,password…);
2. 创建2个表，使用id来联系 student(studentId,name,accountId); account(accountId,accountName,password); 或者 student(studentId,name); account(accountId,accountName,password,studentId);

根据实际开放的需要来设计，怎么方便，怎么简单就怎么来~~

🌤️2.2 一对多

场景：
一个学生只能属于一个班级，一个班级可以包含多个学生。
设计表的做法：

🌤️2.3 多对多

场景：
一个学生，可以选择多门课程学习。一门课程，也可以被多个学生选择。
设计表的做法：引入关联表，通过关联表把多对多关系表示出来

📑3. 新增

插入查询结果，例如把表1的查询结果，插入到表2中

insertinto 表名 select*from 表名;

示例：

查询两个表中的数据：

注意：列数/类型/顺序要和insert into 后面的表相匹配，而列的名字是不要求相同的

📑4. 查询

🌤️4.1 聚合查询

💫4.1.1 聚合函数

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

常见的统计总数、计算平局值等操作，可以使用聚合函数来实现，常见的聚合函数有:

注意：使用聚合函数时，如果在聚合函数名和()之间加上空格，就会使函数执行失败。

🌟 count

返回查询到的数据的数量

🌟 sum

返回查询到的数字的总和，不是数字没有意义

注意：null与null或其他任何数据计算都为null，但是sum比较特殊，遇到null会直接跳过。

🌟 avg

返回查询到的数字的平均值，不是数字没有意义

🌟 max

返回查询到的数字的最大值，不是数字没有意义

🌟 min

返回查询到的数字的最小值，不是数字没有意义

💫4.1.2 Group by子句

select 中使用 group by 子句可以对指定列进行分组查询。

select 字段名 from 表名 groupby 字段名;

注意：使用 group by 进行分组查询时，select 指定的字段必须是“分组依据字段”，其他字段若想出现在select 中则必须包含在聚合函数中。

示例：

• 一般在进行 group by 分组的时候，select 后面的列名，一般要么写 group by 分组的列(role) 或者写 搭配聚合函数的列。

示例：

💫4.1.3 Having

在select语句中使用having子句来指定一组行或聚合的过滤条件。
having子句通常与group by子句一起使用，以根据指定的条件过滤分组。如果省略group by子句，则having子句的行为与where子句类似。

select 字段名  from 表名 by 字段名 having 字段名;

注意：having子句将过滤条件应用于每组分行，而where子句将过滤条件应用于每个单独的行。

示例：

🌤️4.2 联合查询

实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积

可以看出：

1. 笛卡尔积的列数，就是两个表的列数之和
2. 笛卡尔积的行数，就是两个表的行数之积

所谓的“多表联合查询”就是基于笛卡尔积这样的运算来展开的~~

进行多表查询的时候，通常会通过一定的条件把笛卡尔积中的“合法(你想要的)数据筛选出来”，这样的条件就称为“连接条件”

查询时的基本步骤：

1. 明确要查询的信息来自于哪些表
2. 针对这些表进行笛卡尔积(前提是确实能进行笛卡尔积)
3. 指定连接条件，把有意义的数据筛选出来
4. 根据需要，进一步指定条件，对数据进行筛选。(新加的条件往往通过and的方式，和连接条件放在一起)
5. 进一步的针对查询的列，进行精简(减少列数，只保存必要的)。

💫4.2.1 内连接

select 字段 from 表1 别名1[inner]join 表2 别名2on 连接条件 and 其他条件;-- 或者select 字段 from 表1 别名1,表2 别名2where 连接条件 and 其他条件;

组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分。

示例：

💫4.2.2 外连接

🌟左外连接

select 字段名  from 表名1leftjoin 表名2on 连接条件;

left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。
左(外)连接，左表的记录将会全部表示出来，而右表只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。

🌟右外连接

select 字段 from 表名1rightjoin 表名2on 连接条件;

right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。
与左(外)连接相反，右(外)连接，左表只会显示符合搜索条件的记录，而右表的记录将会全部表示出来。左表记录不足的地方均为NULL。

💫4.2.3 自连接

select 字段名 from 表名 as 别名 join 同一个表名;

示例：对student进行自连接

需要注意的是，如果在自连接的时候，表非常大，此时的连接开销也会非常大，容易把数据库搞挂。使用时需要心里有数，要知道表中数据的量级。

💫4.2.4 子查询简单介绍

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。换句话说，本来一个需求，需要通过多个sql语句来完成，但是偏偏要把多个sql合并成一个(套娃)，这就是子查询。这样会使sql语句的可读性降低，不太建议使用。

💫4.2.5 联合查询

select 字段名 from 表名 union(all)select 字段名 from 表名;

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用union和union all时，前后查询的结果集中，字段需要一致

示例：

注意：union会自动去掉结果集中的重复行(去重)。而union all不会去掉结果集中的重复行(不去重)。

📑5. SQL查询中各个关键字的执行先后顺序

SQL查询中各个关键字的执行先后顺序： from > on> join > where > group by > with > having >select > distinct > order by > limit

☁️结语

请给自己些耐心，不要急于求成。
山外青山楼外楼，莫把百尺当尽头。
保持空杯心态加油努力吧！

都看到这里啦！真棒(^▽^)

可以给作者一个免费的赞赞吗，这将会鼓励我继续创作，谢谢大家

如有纰漏或错误，欢迎指正