接上次博客：如何使用MySQL数据库（2）操作数据库（SQL语句、数据库的基本操作：创建，查看，选中，删除、MySQL中支持的数据类型、数据表的操作、MySQL表的增删改查）_di-Dora的博客-CSDN博客
目录
数据库的备份
数据库的备份有很多种方式：
-
直接拷贝数据库文件：
- 类型：全量备份
- 描述：将数据库的物理文件直接复制到另一个位置或机器上。这种方法最简单，但也有一些缺点，例如无法在线备份（需要停止数据库服务），可能导致一致性问题，不适用于跨不同数据库管理系统的备份。
-
MySQL dump：
- 类型：全量备份和增量备份
- 描述：使用MySQL提供的 MySQL dump工具将数据库中的数据导出为SQL语句，包括CREATE TABLE和INSERT INTO语句。这种备份方法可以用于全量备份，也可以用于增量备份，通过记录上次备份的时间戳，只备份自上次备份以来的数据变化。
-
二进制日志（binlog）：
- 类型：增量备份和实时备份
- 描述：MySQL的二进制日志记录了数据库中的所有操作，包括插入、更新和删除。通过解析二进制日志，可以还原数据库的变化，实现增量备份。此外，可以将二进制日志实时传输到另一个服务器，以实现实时备份和数据复制。
MySQL的注释
SQL中可以通过“-- ”表示注释，--后面至少要有一个空格；
-- 插入测试数据
另外也支持“#”开头作为注释。
#插入测试数据数据库约束
有时候数据库对数据是有一定要求的，有些数据被认为是合法的，而有些则是非法数据。靠人工检查显然不靠谱，所以数据库会自动的对数据的合法性进行校验检查——约束，这一系列机制的目的就是为了保证数据库中能够避免被插入/修改一些非法数据。
所以数据库约束是一种用于限制数据库表中数据的完整性和有效性的规则或条件。它们定义了哪些值可以插入、更新或删除表中的数据，以确保数据的质量和一致性。这些约束可用于创建数据库表时或在表已存在时添加。它们有助于维护数据的一致性，防止无效数据的插入，并确保数据满足业务规则和要求。
一个列是可以被设置多个约束的。
约束类型
-
NOT NULL（非空约束）：指示某列不能存储 NULL 值。这意味着在插入或更新数据时，该列必须包含一个非空值。
-
UNIQUE（唯一约束）：保证某列的每行必须有唯一的值。这可以用于确保某一列中的值不会重复出现，用于标识唯一性的列。
-
DEFAULT（默认值约束）：规定没有给列赋值时的默认值。如果在插入数据时没有为该列指定值，将使用默认值。
-
PRIMARY KEY（主键约束）：是NOT NULL和UNIQUE的结合。它确保某列（或多个列的组合）具有唯一标识性，通常用于表的主要标识符。主键还可以用于更快速地查找表中的特定记录。
-
FOREIGN KEY（外键约束）：用于维护表之间的引用完整性。它确保一个表中的数据匹配另一个表中的值，通常用于建立表之间的关联关系。
-
CHECK（检查约束）：用于确保列中的值符合指定的条件。CHECK约束允许定义规则，以确保数据的一致性和有效性。然而，对于MySQL数据库，虽然可以定义CHECK子句，但MySQL在处理时会忽略它，因为MySQL并不执行CHECK约束的验证。在其他数据库管理系统中，如PostgreSQL，CHECK约束会得到有效执行。
这些约束类型在数据库中起着关键的作用，帮助确保数据的完整性、一致性和唯一性。
NULL约束
创建表时，可以指定某列不为空：
-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
id INT NOT NULL,
sn INT,
name VARCHAR(20),
qq_mail VARCHAR(20)
);
修改也同理：
UNIQUE：唯一约束
指定某一列为唯一的、不重复的。
没有加约束之前：
现在我们删表，重新定义一下：
-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
id INT NOT NULL,
sn INT UNIQUE,
name VARCHAR(20),
qq_mail VARCHAR(20)
);
这个报错信息里面，Duplicate代表重复，entry有入口的意思，也有条目、记录的意思。
还记得我们之前在什么地方遇见过entry吗？
Map遍历的时候。Java的遍历集合类都是通过迭代器实现的，对应的集合类得实现 Iterable interface 才能够进行迭代器遍历。而Map没有实现 Iterable interface，所以我们只能通过entry方法把Map转换成为一个Set，里面的元素就是一个一个的Entry（条目，包含了 key 和 value）。
import java.util.*;
public class MapIterationExample {
public static void main(String[] args) {
Map<String, Integer> map = new HashMap<>();
// 添加一些键值对
map.put("apple", 10);
map.put("banana", 5);
map.put("cherry", 20);
// 将Map转换为Set<Map.Entry<String, Integer>>
Set<Map.Entry<String, Integer>> entrySet = map.entrySet();
// 使用迭代器遍历Map
Iterator<Map.Entry<String, Integer>> iterator = entrySet.iterator();
while (iterator.hasNext()) {
Map.Entry<String, Integer> entry = iterator.next();
String key = entry.getKey();
Integer value = entry.getValue();
System.out.println("Key: " + key + ", Value: " + value);
}
}
}
当然，从Java 8开始，也可以使用Lambda表达式和Stream API来遍历Map，使代码更简洁：
import java.util.*;
public class MapIterationExample {
public static void main(String[] args) {
Map<String, Integer> map = new HashMap<>();
// 添加一些键值对
map.put("apple", 10);
map.put("banana", 5);
map.put("cherry", 20);
// 使用Lambda表达式和Stream遍历Map
map.forEach((key, value) -> {
System.out.println("Key: " + key + ", Value: " + value);
});
}
}
还有一个关键点：
UNIQUE约束会导致我们后续在修改数据或者插入数据的时候先触发一次查询操作，通过这个查询，才能确认当前这个记录是否已经存在。
所以引入约束之后，数据库的执行效率就会受到影响，可能会降低很多。
这就意味着数据库其实是比较慢的系统，也是比较吃资源的系统。部署数据库的服务很容易成为一整个系统的“性能瓶颈”。
DEFAULT：默认值约束
我们先来看看表的结构：
（排序order by里面也有 desc，那里代表descend——降序；这里查看表结构的desc是describe——描述，注意区分）
我们可以看到，里面Default这一列就代表默认值，默认的默认值就是NULL，我们可以通过Default约束来修改默认值。
好了，我们再来重新设置一下：
指定插入数据时，name列为空，默认值unkown：
-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
id INT NOT NULL,
sn INT UNIQUE,
name VARCHAR(20) DEFAULT 'unkown',
qq_mail VARCHAR(20)
);
后续插入数据的时候，default就会在没有显示插入数据的值的时候生效了：
PRIMARY KEY：主键约束
主键约束（PRIMARY KEY Constraint）用于定义一列或一组列，以确保表中的每一行都有唯一的身份标识符。主键是用来唯一标识表中的每一行数据的关键字段。
-
唯一性：主键列中的值必须是唯一的，不能有重复值。这意味着我们不能在主键列中具有相同的值。
-
非空性：主键列中的值不能为NULL。每行都必须有一个非NULL的主键值。
-
标识性：主键用于标识表中的每一行数据，使我们能够快速和容易地检索、更新或删除特定行。
-
索引：通常，数据库系统会自动为主键列创建索引，以加速对主键的检索操作。这使得主键列的查找非常高效。
-
一般为整数或唯一标识符：主键通常是整数类型，例如自增长的整数，但它们也可以是其他类型，如字符串或GUID（全局唯一标识符），只要满足唯一性和非空性要求。
-
表中只能有一个主键：每个表只能有一个主键，但主键可以由多列组成（称为复合主键），在这种情况下，组合的值必须唯一。
主键约束对于确保数据的完整性和一致性非常重要。它们确保了表中的每一行都有一个唯一的标识符，从而使数据库管理更容易，同时还支持高效的数据检索。在表设计中，选择合适的主键是一个关键决策，通常需要考虑数据的特点和访问模式。
指定id列为主键。
-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
id INT NOT NULL PRIMARY KEY,
sn INT UNIQUE,
name VARCHAR(20) DEFAULT 'unkown',
qq_mail VARCHAR(20)
); 一张表里只能有一个唯一的身份标识：
虽然只能有一个，但是主键不一定只能是一个列，我们也可以用多个列共同构成一个主键——联合主键。
DROP TABLE IF EXISTS student;
CREATE TABLE student (
id INT NOT NULL,
sn INT NOT NULL,
name VARCHAR(20) DEFAULT 'unknown',
qq_mail VARCHAR(20),
PRIMARY KEY (id, name)
);
我们还提到主键里的值是不能重复的，也不可以为NULL：
对于带有主键的表来说，每次插入或修改数据，也会涉及到先查询的操作，所以也会导致效率较低。但是MySQL也不是毫无对策，它会把带有 unique和primary的列自动生成索引，从而加快查询。（索引是一种数据结构，它可以快速查找表中的特定行，而不必遍历整个表。在具有索引的列上执行查询时，数据库可以更快地定位所需的数据，从而提高了查询性能。）
那么数据库底层是如何保证主键的唯一性呢？
MySQL提供了一种“自增主键”的机制。
MySQL使用一种称为自动增长（Auto-Increment）的机制来保证其唯一性。这种机制在底层的数据存储中工作，确保每次插入新记录时都会分配一个唯一的主键值，不需要手动指定主键值。一般情况下，自增主键是整数类型（如INT或BIGINT），并且具有以下特点：
-
自动递增：每次插入新记录时，自增主键的值会自动递增（通常递增1），以确保唯一性。
-
初始值：我们可以指定自增主键的初始值，通常为1。
-
步长（Increment）：步长指定了每次递增的值，通常为1，但也可以根据需要设置为其他值。
MySQL使用一个名为“auto_increment”的系统变量来跟踪和管理自增主键的值。每次插入新记录时，MySQL会自动增加“auto_increment”的值，然后将其分配给新记录的自增主键列。
对于整数类型的主键，常配搭自增长auto_increment来使用。插入数据对应字段不给值时，使用最大值+1。
-- 主键是 NOT NULL 和 UNIQUE 的结合，可以不用 NOT NULL
id INT PRIMARY KEY auto_increment,
接下来我们还是插入数据：
等等，刚才我们不是说这个主键不能是null吗？怎么现在……？
注意，这里写的null不是说真的插入null，而是说这一列我们不去手动指定了，交给MySQL去自动分配。
当然，你也可以选择手动指定：
那么再自动分配一次，你觉得这个主键会分配什么？ 
这个默认值就是从刚才手动设置的最大的数值开始的，继续往后分配，相当于我们底层使用了一个变量来保存当前这个表达id的最大值，后续分配自增主健都是根据这个最大值来分配的，如果手动指定id也会更新最大值。
那么我们刚刚中间跳过的那些id，是不是就用不上了？浪费了？
确实如此。但是浪费了就浪费了，不要太抠~
此处这里的ID的自动分配也是有一定局限性的，如果是单个MySQL服务器是没有问题的，但是如果是一个分布式系统（当我们面临的数据量巨大（大数据），客户端的请求量比较大（高并发），一台服务器搞不定的时候，我们就需要多台机器，也就是分布式系统，来帮我们解决问题。分布式系统是由多个独立计算机或服务器组成的系统，这些计算机或服务器通过网络连接在一起，共同协作以完成共享的任务或提供服务），有多个MySQL服务器构成的集群，这个时候依靠自增主健就不行了。
为什么会用到分布式系统呢？
一台服务器主机硬盘空间肯定是有限的，如果我们现在为一个超市做商品表，商品的ID应该如何分配呢？我们肯定是要把这个记录保存在某个数据库的表中。这个时候我们就可以引入多个数据库服务器的主机，分库分表。但是新的问题又出现了，我们应该如何保证这里的ID和另外别的数据库中的ID不重复呢？
这就引入了我们分布式系统中生成唯一ID的算法。
这里的实现算法有很多种具体的方式。我们主要讲一下核心思路：
核心思路主要是围绕一个公式：
分布式系统唯一ID = 时间戳 + 机房编号/主机编号 + 随机因子。
注意这里的 “ + ”是指字符串拼接，不是算术相加，所以拼出来的结果是一个比较长的字符串。
如果添加商品的速度比较慢，其实直接使用时间戳就足够了，但是如果一个时间戳之内，我们需要添加多个商品，那么我们就再加一个机房编号或者主机编号，这样添加的多个商品是要落到不同的主机上的，就可以保同一时间之内添加到不同主机上的商品编号是不同的了。
但是还是有很小的概率，这里的主机编号加上之后仍然会得到相同的ID。那么我们再加一个随机因子。你或许会说，最后的最后随机因子也有一定概率生成相同因子啊？但是概率真的是太小了，我们就忽略不计了。
FOREIGN KEY：外键约束
外键是用于关联其他表的主键或唯一键。
外键约束（FOREIGN KEY Constraint）是数据库中一种重要的约束，它用于确保表中的数据完整性和一致性。外键约束定义了一个列或一组列，这些列的值必须与另一个表中的列的值相匹配。外键在关系数据库中用于建立表与表之间的关联关系。
以下是外键约束的定义和一些主要特点：
-
引用关系：外键约束定义了一个表与另一个表之间的引用关系。通常，一个表中的外键列引用另一个表中的主键列，这意味着外键的值必须与另一个表中的主键值匹配。
-
确保数据一致性：外键约束用于确保数据的一致性，它防止了在外键列中插入不在关联表中存在的值。这有助于维护表之间的数据关系。
-
约束行为：外键约束通常具有一些约束行为，例如：
- CASCADE：如果引用表中的行被删除或更新，外键表中的相应行也会被删除或更新。
- SET NULL：如果引用表中的行被删除或更新，外键表中的相应列将被设置为NULL。
- SET DEFAULT：如果引用表中的行被删除或更新，外键表中的相应列将被设置为默认值。
- NO ACTION：不执行任何动作，拒绝对外键表的删除或更新操作。
-
多表关联：一个表可以有多个外键，每个外键可以引用不同的关联表，这允许建立复杂的数据关系。
-
性能影响：外键的使用可能对性能产生影响，因为在进行插入、更新或删除操作时，需要检查外键约束以确保数据完整性。
外键约束在建立表之间的关系、保持数据一致性和完整性方面起着关键作用。它们有助于防止不一致的数据输入，确保数据的准确性，并支持复杂的数据库关系。在设计数据库时，合理使用外键约束可以提高数据质量和可维护性。
语法：
foreign key (字段名) references 主表(列) 
执行这个插入操作的时候就会触发针对class表的查询，它会查询111或者201是否在class的classid中存在。前者存在，插入成功，后者不存在，爆出相关错误信息。
修改也不可以：
虽然说父表约束子表，但当我们针对父表进行修改/删除，如果当前被修改/删除的值已经被子表引用了。这样的操作也会失败！
外键约束要始终保持子表中的数据在对应的父表的列中存在，此时万一把父表中的相关联数据删除，也就打破约束了。
如下，当你删除子表中没有引用到的班级id的数据时是可以的，但是如果删除已经被引用了的数据就会报错：
外键准确来说是两个表中的列产生关联关系，其他列的修改是不受影响的：
现在我要问一个问题：
如果直接尝试：drop table class，你认为能否删除整个表？
要想删除表，就得先删除数据记录。此处的删表是必然不可以的！父表没了，子表后续添加新元素就没了参考： 
即使是子表为空，并没有引用父表的内容，先删除父表也是不被允许的： 
所以先删除子表，再删除父表是正确的顺序。
还有一个重要的地方，父表被外键约束的那一列是需要由主键约束或unique约束的，如果不是，就不可以和子表创建外键约束！
现在考虑一个现实问题：
你是某宝的商家，你的网店前段时间推出了一款商品，可惜销量一直不好。现在你希望把这个商品下架处理，但是你的电商网站上，商品表和订单表是通过商品ID关联在一起的，订单表是无法改变的，你现在要如何在外键约束存在的前提下完成删除操作？
把要下架的商品隐藏起来，让顾客查询商品的时候无法看到它，但是商品表里它又确实是存在的。这样也就不会影响到之前下的订单，间接实现了商品的下架操作。
我们给商品表新增一个单独的列 isOnline，表现是否在线。给它赋值为1的时候表示在线，如果需要下架商品，使用update把它的值改为0即可，表示下架。之后的查询商品操作都加上一个 where isOnline = 1 这样的条件就好了。
以上删除操作，我们就把它叫做逻辑删除。我们的电脑上也有很多地方用到了逻辑删除，比如有一个你删除的文件，它在硬盘上、数据上还存在，只是被标记成无效了。后续其他文件就可以重复利用这块空间了。所以我们想把某个文件彻底删除掉，通过扔进回收站、清空回收站的操作是没有用的，硬盘上的数据什么时候真正消亡是需要时间的，需要后续文件把这块标记无效的空间重复利用了它才会真正消失。只有物理删除才是靠谱的彻底删除~~
-
逻辑删除（Logical Deletion）：逻辑删除是一种通过标记数据为“无效”或“删除”状态来表示数据已被删除的方法，而实际上数据仍然存在于存储介质中。逻辑删除的优点是可以恢复数据，因为数据仍然存在，只是被标记为不可见。这种方式通常用于回收站或者需要一段时间后才能进行物理删除的情况，以便在用户意外删除数据后进行恢复。
-
物理删除（Physical Deletion）：物理删除是彻底从存储介质中删除数据的过程，使数据不可恢复。在物理删除后，数据在存储设备上的存储空间会被释放，可以被其他数据所使用。物理删除通常需要更谨慎的操作，因为一旦数据被物理删除，通常就无法恢复。
所以，在实际应用中，选择逻辑删除还是物理删除取决于数据的保留和恢复需求。如果你需要保留数据的备份或有可能需要恢复数据，那么逻辑删除可能更合适。如果你需要释放存储空间或确保数据不可恢复，那么物理删除是更适合的选择。
那么按照逻辑删除的思路，表中的数据难道会无限的扩张？是否会导致硬盘空间被占满？
可能有这种情况，但是硬盘很便宜，你也可以通过增加主机（分布式系统）的方式来进一步的扩充存储空间。
CHECK约束
CHECK约束我们就不过多了解了。MySQL使用时不报错，但忽略该约束。
drop table if exists test_user;
create table test_user (
id int,
name varchar(20),
sex varchar(1),
check (sex ='男' or sex='女')
);
表的设计
在实际开发，我们都需要根据实际的需求场景，明确当前要创建几个表，每个表的结构是啥，这些表之间是否存在一定的联系……
为了解决这些问题，我们首先需要梳理清楚需求中的“实体”，再确定好实体之间的“关系”。
这里的实体就是我们说过的“对象”，也就是需求中的关键性名词。一般来说每个实体都需要安排一个表，表的列就对应到实体的各个属性。
实体的问题解决后，我们就需要去关注实体之间的“关系”。
MySQL数据库中一般存在四种关系：
-
一对一关系：
- 一对一关系表示一个实体与另一个实体之间存在严格的一对一关联。这种关系通常在表设计中以将属性合并到同一个表中的方式来表示，而不是创建单独的表。
-
一对多关系：
- 一对多关系表示一个实体可以与多个其他实体相关联，而其他实体只能与一个实体相关联。这种关系通常通过在一个表中包含一个外键字段来表示，该字段与另一个表的主键关联起来。
-
多对多关系：
- 多对多关系表示多个实体可以相互关联，每个实体可以与多个其他实体相关联。这种关系通常需要创建一个中间表（关联表或连接表），以跟踪两个实体之间的关系。中间表包含两个外键，分别与两个相关的实体表关联。
-
没有关系：
- 如果两个实体之间没有直接关系，那么它们可以分别存储在各自的表中，而不需要特殊的关联
不同的关系，我们设计表的方式也是不同的。
一对一
- 学生和身份证：每个学生只能拥有一个唯一的身份证，而每个身份证也只能与一个学生相关联。
- 员工和健康记录：每个员工只能有一份健康记录，每份健康记录也只能与一个员工相关联。
- 人和驾驶执照：每个人只能拥有一个驾驶执照，每个驾驶执照也只能与一个人相关联。
- 学生账号：一个学生只能拥有一个账号，一个账号也只能被一个学生拥有。
以学生账号为例：
-- 创建student表
CREATE TABLE student (
student_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
age INT,
-- 其他学生相关信息列
);
-- 创建account表
CREATE TABLE account (
account_id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(255) NOT NULL,
password VARCHAR(255) NOT NULL,
student_id INT UNIQUE,
FOREIGN KEY (student_id) REFERENCES student(student_id)
);
你甚至可以把两张表之间合并为同一张表：student—account 表。
一对多
- 课程和学生：一个课程可以有多个学生，但一个学生只能参加一个课程。
- 部门和员工：一个部门可以有多个员工，但一个员工只能属于一个部门。
- 作者和书籍：一个作者可以写多本书，但每本书只有一个主要作者。
以部门和员工为例：
-- 创建department表
CREATE TABLE department (
department_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL
);
-- 创建employee表
CREATE TABLE employee (
employee_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
department_id INT,
FOREIGN KEY (department_id) REFERENCES department(department_id)
);
当然，其实这里创建表我们有两种逻辑结构可以选择：
多对多
- 学生和课程：每个学生可以选择多门课程，而每门课程也可以有多个学生。
- 演员和电影：多个演员可以出演同一部电影，同时演员也可以在多部电影中出演。
- 标签和文章：一篇文章可以有多个标签，同时一个标签也可以应用于多篇文章。
我们以演员和电影为例：
-- 创建actors表
CREATE TABLE actors (
actor_id INT PRIMARY KEY AUTO_INCREMENT,
actor_name VARCHAR(255) NOT NULL
);
-- 创建movies表
CREATE TABLE movies (
movie_id INT PRIMARY KEY AUTO_INCREMENT,
movie_title VARCHAR(255) NOT NULL
);为了建立多对多关系，我们需要创建一个中间关联表，用于表示演员和电影之间的关系：
-- 创建actor_movie表（中间表）
CREATE TABLE actor_movie (
actor_id INT,
movie_id INT,
PRIMARY KEY (actor_id, movie_id),
FOREIGN KEY (actor_id) REFERENCES actors(actor_id),
FOREIGN KEY (movie_id) REFERENCES movies(movie_id)
);插入一些数据：
演员表 (actors) 示例：
-- 插入演员表数据
INSERT INTO actors (actor_id, actor_name) VALUES
(1, 'Tom Hanks'),
(2, 'Scarlett Johansson'),
(3, 'Brad Pitt'),
(4, 'Meryl Streep'),
(5, 'Leonardo DiCaprio'),
(6, 'Julia Roberts'),
(7, 'Johnny Depp'),
(8, 'Angelina Jolie'),
(9, 'Denzel Washington'),
(10, 'Emma Watson');
电影表 (movies) 示例：
-- 插入电影表数据
INSERT INTO movies (movie_id, movie_title) VALUES
(1, 'Forrest Gump'),
(2, 'The Avengers'),
(3, 'Fight Club'),
(4, 'The Devil Wears Prada'),
(5, 'Titanic'),
(6, 'Pretty Woman'),
(7, 'Pirates of the Caribbean'),
(8, 'Maleficent'),
(9, 'Training Day'),
(10, 'Harry Potter and the Sorcerer''s Stone');
演员和电影的关系表 (actor_movie) 示例：
-- 插入演员和电影的关系表数据
INSERT INTO actor_movie (actor_id, movie_id) VALUES
(1, 1), -- Tom Hanks出演Forrest Gump
(2, 2), -- Scarlett Johansson出演The Avengers
(3, 3), -- Brad Pitt出演Fight Club
(4, 4), -- Meryl Streep出演The Devil Wears Prada
(5, 5), -- Leonardo DiCaprio出演Titanic
(6, 6), -- Julia Roberts出演Pretty Woman
(7, 7), -- Johnny Depp出演Pirates of the Caribbean
(8, 8), -- Angelina Jolie出演Maleficent
(9, 9), -- Denzel Washington出演Training Day
(10, 10); -- Emma Watson出演Harry Potter and the Sorcerer''s Stone
对于上述实体之间的关系，你可能曾经听到过ER图，及实体关系图。
ER图（Entity-Relationship Diagram）是一种图形化的工具，用于描述数据库中的实体（Entities）、属性（Attributes）和实体之间的关系（Relationships）。它是数据库设计和建模的一种常用方法，通过图形化的方式表示数据模型，有助于理解和可视化数据库的结构和关系。
但是我个人认为，画个图其实对写代码用处不大，反而可能增加了负担。
我们这里就不过多介绍，你如果感兴趣的话可以去查查看。
查询
插入搭配查询使用
我们可以把查询结果作为插入的数值，但是前提是查询出来的结果集合的列数和类型要和插入的这个表的列数和类型匹配。
通过这样一行代码，我们就实现了表的复制：
insert into students2 select * from students;聚合查询
我们之前学习的是表达式之间是针对列和列之间进行运算，要想实现行和行之间的运算还得另寻他法。SQL中提供了一些“聚合函数”来完成行之间的运算。
聚合函数
聚合函数是用于对数据库中的数据执行统计和计算操作的函数，它们对数据进行汇总和分析，常用于SQL查询中以生成汇总信息。
常见的统计总数、计算平局值等操作，可以使用聚合函数来实现，常见的聚合函数有：
1、COUNT([DISTINCT] expr)：COUNT函数用于计算查询结果集中的行数，可以用来统计特定条件下的记录数量。可以选择使用DISTINCT关键字来消除重复值的计数。
SELECT COUNT(*) FROM orders; -- 统计订单总数
SELECT COUNT(DISTINCT customer_id) FROM customers; -- 统计不重复的客户数量2、SUM([DISTINCT] expr)：SUM函数用于计算查询结果集中某个数值列的总和。它适用于数值型数据，例如订单总额、销售数量等。
SELECT SUM(order_amount) FROM orders; -- 计算订单总金额3、AVG([DISTINCT] expr)：AVG函数用于计算查询结果集中某个数值列的平均值。它适用于数值型数据。
SELECT AVG(product_price) FROM products; -- 计算产品价格的平均值4、MAX([DISTINCT] expr)：MAX函数用于找到查询结果集中某个数值列的最大值。可以用于查找最高分、最高工资等情况。
SELECT MAX(score) FROM students; -- 查找最高分5、MIN([DISTINCT] expr)：MIN函数用于找到查询结果集中某个数值列的最小值。可以用于查找最低温度、最低价格等情况。
SELECT MIN(temperature) FROM weather_data; -- 查找最低温度这些聚合函数允许我们对数据执行各种统计和计算操作，以提取有关数据集的重要信息。它们在SQL查询中是非常有用的，可用于分析数据和做出决策。
我们现在举个实例来看看：
先创建学生表和考试表：
-- 创建exam_result表
CREATE TABLE exam_result (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
chinese DECIMAL(5, 2),
math DECIMAL(5, 2),
english DECIMAL(5, 2)
);
-- 插入考试成绩数据示例
INSERT INTO exam_result (name, chinese, math, english) VALUES
('唐三藏', 16.8, 98.0, 56.0),
('猪悟能', 22.0, 98.5, 90.0),
('曹孟德', 17.5, 30.0, 67.0),
('刘玄德', 13.9, 55.0, 45.0),
('孙权', 17.5, 73.0, 78.5),
('宋公明', 18.8, 35.0, 30.0),
('孙悟空', 21.9, 80.0, 77.0);
-- 创建student表
CREATE TABLE student (
student_id INT PRIMARY KEY AUTO_INCREMENT,
student_name VARCHAR(20),
exam_result_id INT,
FOREIGN KEY (exam_result_id) REFERENCES exam_result(id)
);
-- 插入学生数据示例
INSERT INTO student (student_name, exam_result_id) VALUES
('唐三藏', 1),
('猪悟能', 2),
('曹孟德', 3),
('刘玄德', 4),
('孙权', 5),
('宋公明', 6),
('孙悟空', 7);
1、COUNT([DISTINCT] expr)：
先执行select * ，再针对结果集合进行统计，看看具体有几行。
你可能会质疑这个函数存在的意义。因为这里我们已经可以看到行数了。
这个东西其实是MySQL客户端内置的功能，如果你是通过代码来操作MySQL服务器可就没有这个功能了。
另外，count（*）计算得到的结果还可以参与各种算数运算，还可以搭配其他SQL使用。
先执行select name，再针对结果集合进行统计，看看具体有几行。
这样也可以，但是如果当前的列里面有null，两种方式计算的count就不一样了，前者会把null也算进去，但是后者就不算了：
因为我们之前设置表的时候设置了name不可以为null，所以我们去查询math吧：
还有，指定具体的列是会去重的，使用distinct：
而这种操作是不被允许的：
最后还有一个需要注意的点：（*）前面不可以有任何多余的东西，包括空格：
2、SUM([DISTINCT] expr)：
把这一列的若干行进行求和，算术运算。
既然是算术运算，那么就代表只能针对数字类型使用，虽然字符串可以相加，但不是“算数运算”。
计算语文总成绩（它会把null排除）：
针对字符串求和：
在求和的时候，MySQL会尝试把字符串转成 double，如果转成功就可以进行计算，但是如果失败就会报错。
‘007’被转成数字7。
sum的( )里面还可以写表达式：
先把对应的列相加，得到一个临时表。再把这个临时表的结果进行行和行的相加。
接下来的几个操作我们简单看看就好，它们需要注意的点都和sum是一样的，比如只可以对数字进行计算，如果需要计算别的类型它会先进行一个强转，但是这个类型强转能不能成功是不确定的。
3、AVG([DISTINCT] expr)：
4、MAX([DISTINCT] expr)：
5、MIN([DISTINCT] expr)：
综上，我们可以看出，SQL也可以进行一些简单的统计操作。
GROUP BY子句
SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足：
使用 GROUP BY 进行分组查询时，SELECT 指定的字段必须是“分组依据字段”，其他字段若想出现在SELECT 中则必须包含在聚合函数中。
select column1, sum(column2), .. from table group by column1,column3;create table emp(
id int primary key auto_increment,
name varchar(20) not null,
role varchar(20) not null,
salary numeric(11,2)
);
insert into emp(name, role, salary) values
('马云','服务员', 1000.20),
('马化腾','游戏陪玩', 2000.99),
('孙悟空','游戏角色', 999.11),
('猪无能','游戏角色', 333.5),
('沙和尚','游戏角色', 700.33),
('隔壁老王','董事长', 12000.66);
使用 group by 进行分组，针对每个分组再分别进行聚合查询。
先针对指定的列进行分组，把这一列中值相同的行分到一组，此时就会得到若干个组。再针对这些组分别进行聚合查询。
查询每个角色的最高工资、最低工资和平均工资：
select role,max(salary),min(salary),avg(salary) from emp group by role;根据 role 分组，再根据 max(salary),min(salary),avg(salary) 聚合查询：
如果分组的时候不聚合会怎么样？
如果分组的时候不进行聚合，那此时的结果就是查询出每一组中的某个代表数据（图中是第一条，但是顺序是未定义的）。
所以往往我们还是要搭配聚合函数使用，否则这里的查询将会是没有意义的。
使用 group by 进行分组还可以搭配条件，但是你需要分清楚该条件是分组之前的条件还是分组之后的条件：
1、查询每个岗位的平均工资，但是排除“猪无能”——分组前：
直接使用 where 即可，where子句一般写在group by 前面。
2、查询每个岗位的平均工资，但是排除平均薪资超过12000的结果——分组后：
使用having描述条件，having子句一般写在group by的后面。
其实在group by中可以一个SQL同时完成两类条件的筛选：
两个条件同时存在。先根据名字排除猪无能，然后根据分组条件进行分组，最后再算平均值，根据平均值再筛选。
联合查询/多表查询
实际开发中，数据通常存储在不同的数据库表中，需要从多个表中检索数据。这时就需要进行多表查询，以获得所需的信息。
我们前面学的查询都是针对一个表，没有什么太复杂的操作。相比之下，有些查询则是一次性需要从多个表中进行查询。
多表查询是数据库查询的重要组成部分，但应当谨慎使用，以避免性能问题和不必要的复杂性。
单表查询 vs. 多表查询
单表查询
- 单表查询是最简单的查询类型，用于从单个表中检索数据。
- 这类查询通常不涉及复杂的操作，仅需指定要检索的表以及条件即可。
多表查询
- 多表查询涉及从多个表中获取数据，并将它们关联在一起，以获得更全面的信息。
- 多表查询的关键思路在于理解 "笛卡尔积" 的工作过程。
-
多表查询是对多张表的数据取笛卡尔积。
笛卡尔积与多表查询
- 笛卡尔积是一种数学概念，指的是两个集合的所有可能的组合。
- 在多表查询中，如果不明确指定如何关联表，结果将是这些表的笛卡尔积，这通常是不可取的。
- 因此，多表查询的关键是定义正确的连接条件，以限制结果集的大小。
比如说这两个表：
笛卡尔积是通过排列组合的方式得到的一个更大的表，
笛卡尔积的积数是这两个表的列数相加，
笛卡尔积的行数是这两个表的行数相乘：
如上图的表，如果你仔细观察可以看到其中有一些数据是“非法”的，即不符合实际情况的、无意义的。
所以笛卡尔积是简单无脑的排列组合，把所有可能的情况都穷举了一遍，包含了一些合法的数据，也包含了一些非法的无意义的数据。
所以我们进行多表查询的时候就需要把有意义的数据筛选出来，无意义的数据过滤掉。
但是凭手动筛选显然不现实，我们需要借助SQL的条件来筛选。
所以我们可以指定条件：where 部门表的 department_id = 学生表的 department_id：
像这样的条件，我们把它称为“连接条件”。
常见的多表查询类型
我们先来构建两张表，一张学生信息表，一张班级表，一张课程表，一张成绩表：
成绩表可以看作是学生和课程之间的关联表。
-- desc 存储班级描述
CREATE TABLE classes (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
`desc` TEXT
);
CREATE TABLE student (
id INT AUTO_INCREMENT PRIMARY KEY,
sn VARCHAR(20) NOT NULL,
name VARCHAR(255) NOT NULL,
qq_mail VARCHAR(255),
classes_id INT,
FOREIGN KEY (classes_id) REFERENCES classes(id)
);
CREATE TABLE course (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL
);
CREATE TABLE score (
id INT AUTO_INCREMENT PRIMARY KEY,
score DECIMAL(5, 2) NOT NULL,
student_id INT,
course_id INT,
FOREIGN KEY (student_id) REFERENCES student(id),
FOREIGN KEY (course_id) REFERENCES course(id)
);
insert into classes(name, `desc`) values
('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
('中文系2019级3班','学习了中国传统文学'),
('自动化2019级5班','学习了机械自动化');
insert into student(sn, name, qq_mail, classes_id) values
('09982','黑旋风李逵','xuanfeng@qq.com',1),
('00835','菩提老祖',null,1),
('00391','白素贞',null,1),
('00031','许仙','xuxian@qq.com',1),
('00054','不想毕业',null,1),
('51234','好好说话','say@qq.com',2),
('83223','tellme',null,2),
('09527','老外学中文','foreigner@qq.com',2);
insert into course(name) values
('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');
insert into score(score, student_id, course_id) values
-- 黑旋风李逵
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
-- 菩提老祖
(60, 2, 1),(59.5, 2, 5),
-- 白素贞
(33, 3, 1),(68, 3, 3),(99, 3, 5),
-- 许仙
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
-- 不想毕业
(81, 5, 1),(37, 5, 5),
-- 好好说话
(56, 6, 2),(43, 6, 4),(79, 6, 6),
-- tellme
(80, 7, 2),(92, 7, 6); 
内连接（INNER JOIN）
- 内连接返回两个表中满足连接条件的行。
- 用于检索相关联的数据，只返回有匹配项的行。
select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;（1）查询“许仙”同学的 成绩：student——score
我们先把这两个表进行笛卡尔积：
select * from student,score;
你仔细观察一下就会发现两个表的排列组合：
先取那个表的记录和另外的表的所有记录排列组合，都可以。它没有一个固定的顺序。想要有顺序，你需要使用 order by。
加上连接条件，筛选出有效条件：student表的 id——score表的student_id：
这里遇到了 "Column 'id' in where clause is ambiguous" 错误，这是因为我们的查询中使用了 id 列，并且这个列在两个表中都存在（student 表和 score 表），即有重复的列。此时数据库不知道应该使用哪个表的 id 列来比较。
建议写成 “表名 . 列名”的方式：
现在我们需要查许仙同学的成绩，那么就要结合需求，进一步添加条件，针对结果进行筛选：
针对查询到的列进行精简，只保留需求中关心的列：
（2）查询所有同学的总成绩，及同学的个人信息：
之前我们是通过表达式查询来完成总成绩的计算，可是现在同学的成绩是按照行来表示的，因为每个同学选的课可能是不一样的。此处就应该进行多行数据的加和，聚合函数 sum，还需要按照同学进行分组。所以这里是一个基于多表查询和聚合查询的综合运用：
笛卡尔积：
指定连接条件：
精简一下列：
针对上述结果再进行 group by 进行聚合查询：
（3）查询所有同学的成绩，及同学的个人信息： course——student——score
列出每个同学每门课程的名字和分数。
三个表的笛卡尔积：信息已经非常多了……
……
三个行数的乘机有960行，这也是我们之前说要谨慎使用笛卡尔积的原因。
一旦表的数据量非常大，或者表的数目非常多，此时我们的笛卡尔积就会非常庞大。因此，如果我们针对大表进行笛卡尔积（多表查询），就会生成大量的临时结果，这个结果是非常消耗时间的。而且表的数目非常多，可读性也会大大降低。
指定连接条件筛选数据：三个表涉及到两个连接条件：
精简列：
不好区分，取个别名：
我们刚刚讲到的上面那些操作都是基于“内连接”，对于MySQL来说，进行多表查询还可以使用“外连接”，外连接又分为“左外连接”和“右外连接”。
内连接：内连接只返回两个表中在连接条件下有匹配的记录。如果两张表中的记录都存在对应关系（满足连接条件），那么内、外连接的结果就是一致的，因为它只返回匹配的记录。但如果存在不对应的记录，那么这些不对应的记录将被丢弃，不会出现在内连接的结果中。
例如，假设有两张表，一张是订单表，另一张是产品表。内连接会返回那些订单和产品之间有匹配关系的记录，而那些没有匹配关系的订单或产品记录将被忽略。
外连接：外连接会返回两个表中的所有记录，不管是否存在匹配关系。如果两张表中的记录都存在对应关系，外连接的结果也是一致的，因为它返回所有记录。但如果存在不对应的记录，这些不对应的记录将会出现在外连接的结果中，并且由于没有匹配的记录，对应的字段将会用空值或者默认值填充。
例如，仍然以订单表和产品表为例，外连接会返回所有订单，同时也返回那些没有匹配产品的订单记录，这些没有匹配的记录会在产品相关字段上填充空值或默认值。
如果这两张表里面的记录都是存在对应关系，内连接和外连接的结果都是一致的。
如果存在不对应的纪录，内连接和外连接就会出现差别。
为了进一步说明，我简单创建了两张表：
内连接：
我们也可以换个写法，这种写法和上面等价：
你还可以在join前面加上一个inner：
JOIN ON
JOIN ON 是 SQL 中用于合并两个或多个表的操作。它用于在一个查询中将两个或多个表的数据组合在一起，以便根据某些条件来检索相关的数据。这个条件通常是两个表之间的共同列或键。
SELECT column1, column2, ...
FROM table1
JOIN table2 ON table1.column_name = table2.column_name;
在这个语法中，JOIN 关键字用于指定要合并的表，ON 关键字用于指定连接条件，即在哪些列上进行连接。
不同类型的连接：
- 内连接（【INNER】 JOIN）：这是最常见的连接类型，它只返回两个表之间匹配的行。即只返回满足连接条件的记录。
- 左连接（LEFT JOIN）：左连接返回左表中的所有行，以及右表中与左表匹配的行。如果右表中没有匹配的行，那么结果中右表的列将包含空值。
- 右连接（RIGHT JOIN）：右连接与左连接相反，它返回右表中的所有行，以及左表中与右表匹配的行。如果左表中没有匹配的行，那么结果中左表的列将包含空值。
- 全连接（FULL OUTER JOIN）：全连接返回两个表中的所有行，如果没有匹配的行，则用空值填充。
左外连接（LEFT JOIN）
- 左连接返回左表的所有行，以及右表中与左表中的行匹配的行。
- 用于检索主表的所有数据以及与之关联的附表的数据。
右外连接（RIGHT JOIN）
- 右连接与左连接相反，返回右表的所有行以及左表中与右表中的行匹配的行。
- 用于检索附表的所有数据以及与之关联的主表的数据。
全连接（FULL JOIN）
- 全连接返回两个表中的所有行，无论是否有匹配项。
- 用于检索两个表中的所有数据，不论是否有关联。
左侧表和右侧表的所有数据都可以得到。
MySQL不支持！但是Oracle支持~~
自连接（Self Join）
- 自连接是将同一表用于多次连接的情况，使用别名区分不同的表实例。
- 用于处理层次结构或关系紧密的数据。
简单来说，自连接就是一张表自己和自己进行笛卡尔积。
有的时候我们需要进行行和行之间的比较，而SQL只能进行列和列之间的比较。
SQL里面写个条件都是列和列之间进行比较，当我们涉及到行和行之间的比较的时候就可以使用“自连接”，把行的关系转换成列的关系
好了，现在回到之前四张表关联的那个例子：
显示所有“计算机原理”成绩比“Java”成绩高的成绩信息：
相当于比较课程1和课程3的成绩高低。
此时我们想要比较的是两行数据。
自己和自己笛卡尔积：
报错了。这里 alias提示我们可以给表起一个别名：
……
此处我们关心的是学生信息，所以按照学生id去筛选：
现在你就会发现一件很神奇的事：
我们把行和行的关系转成了两列之间的关系。
但是现在结果还是太多啦，我们不想要那么多数据，我们还有进一步精简：
只有这三名同学同时选修了这两门课。
要是你还想要学生姓名，就可以拿这个表和学生表做笛卡尔积；
如果想要课程名字，就可以拿这个表和课程表做笛卡尔积。
最后！再强调！笛卡尔积虽然很好用，但是真的真的不要随便用！
子查询
子查询（Subquery）是指嵌套在其他 SQL 语句中的 SELECT 语句，也常称为嵌套查询。子查询通常用于从一个查询中获取数据，以供另一个查询使用。它可以嵌套在其他 SQL 语句中的不同部分，如 SELECT、FROM、WHERE、或 HAVING 子句中，以实现复杂的数据检索和操作。
子查询本质上就是在套娃。它把多个简单的SQL拼成一个复杂的SQL。
这违背了我们一贯的编程原则：大的拆分成小的，复杂的拆分成多个简单的。
虽然在开发中更建议大家使用多个简单的SQL替代子查询，但是基本知识还是要掌握的。
单行子查询：返回一行记录的子查询
查询与“不想毕业” 同学的同班同学：
上述操作我们先找了classes_id，又找了学生姓名。
接下来我们可以把上述操作合并为一个：
如上，单行子查询顾名思义，我们内部的SQL返回结果必须是一行记录，否则不好操作。
多行子查询：返回多行记录的子查询
查询“语文”或“英文”课程的成绩信息：course——score
这里使用联合查询也是可以的：
上述的操作用子查询：
先通过课程名字找到课程id；再通过课程id在分数表中进行查询：
如果想要完成一步操作：
1、[NOT] IN关键字：如上：
-- 查询重复的分数
SELECT
*
FROM
score
WHERE
( score, student_id, course_id ) IN ( SELECT score, student_id,
course_id FROM score GROUP BY score, student_id, course_id HAVING
count( 0 ) > 1 );
2、[NOT] EXISTS关键字：
子查询中的 [NOT] EXISTS 关键字用于检查一个查询是否返回了结果行。
EXISTS 关键字：
- EXISTS 关键字用于检查子查询是否返回了结果行。如果子查询返回了至少一行结果，那么 EXISTS返回 TEUE，否则返回 FALSE。
- EXISTS 常用于主查询的 WHERE 子句中，以检查子查询的结果是否与主查询中的条件匹配。
NOT EXISTS 关键字：
- NOT EXISTS 关键字与 EXISTS 相反，它用于检查子查询是否没有返回结果行。如果子查询没有返回结果行，NOT EXISTS 返回 TEUE，否则返回 FALSE。
- NOT EXISTS 常用于主查询的 WHERE 子句中，以查找不满足特定条件的行。
-- 使用 EXISTS 查找至少拥有一份订单的客户。
SELECT customer_name
FROM customers
WHERE EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.customer_id);
-- 使用 NOT EXISTS 查找没有下过订的客户。
SELECT customer_name
FROM customers
WHERE NOT EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.customer_id);
这种写法写起来又复杂，代码效率又低，相比于in唯一的优势就是节省内存空间。
合并查询
把多个SQL查询的结果集合合并在一起。
在实际应用中，为了合并多个 select 的执行结果，可以使用集合操作符 union，union all。
使用UNION 和 UNION ALL时，前后查询的结果集中，字段需要一致。
UNION 操作符：
-
UNION 用于合并多个查询的结果集，并返回去重后的结果。它会自动去除重复的行，只保留一次出现的行。
-
要使用 UNION 操作符，查询必须满足以下条件：
- 合并的查询必须包含相同数量的列。
- 对应位置的列的数据类型必须兼容。
该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。
SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;
查询id小于3，或者名字为“英文”的课程：
使用 OR：
你可能会问，既然可以用OR实现，为啥还要用 union？
union有一个OR 做不到的地方，它允许把两个不同的表的查询结果合并在一起。
但是，合并的两个SQL的结果集的列是需要匹配的，即列的个数和类型是要一致的，列名不需要一致。
合并的时候会去重。如果不想去重，就可以使用 UNION ALL 关键字。
注意，合并后的表列名是根据第一张表的列名定的。
UNION ALL 操作符：
-
UNION ALL 也用于合并多个查询的结果集，但不去重。它会保留所有行，包括重复的行。
-
与 UNION 不同，UNION ALL 不执行去重操作，因此速度可能更快。
该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。
SELECT column1, column2, ...
FROM table1
UNION ALL
SELECT column1, column2, ...
FROM table2;
补充知识：笛卡尔坐标系
与多表查询中的笛卡尔积有关的是笛卡尔坐标系，也称为直角坐标系。
笛卡尔坐标系用于表示点的位置，由水平轴（X轴）和垂直轴（Y轴）组成，用于描述平面上的位置。
2096
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
