【MySQL】MySQL表的增删改查（进阶）

1.❤️❤️前言~🥳🎉🎉🎉

Hello, Hello~ 亲爱的朋友们👋👋，这里是E绵绵呀✍️✍️。

如果你喜欢这篇文章，请别吝啬你的点赞❤️❤️和收藏📖📖。如果你对我的内容感兴趣，记得关注我👀👀以便不错过每一篇精彩。

当然，如果在阅读中发现任何问题或疑问，我非常欢迎你在评论区留言指正🗨️🗨️。让我们共同努力，一起进步！

加油，一起CHIN UP！💪💪

🔗个人主页：E绵绵的博客
📚所属专栏：

1. JAVA知识点专栏

深入探索JAVA的核心概念与技术细节

2.JAVA题目练习

实战演练，巩固JAVA编程技能

3.c语言知识点专栏

揭示c语言的底层逻辑与高级特性

4.c语言题目练习

挑战自我，提升c语言编程能力

5.Mysql数据库专栏

了解Mysql知识点，提升数据库管理能力

6.html5知识点专栏

学习前端知识，更好的运用它

7. css3知识点专栏

在学习html5的基础上更加熟练运用前端

8.JavaScript专栏

在学习html5和css3的基础上使我们的前端使用更高级

📘 持续更新中，敬请期待❤️❤️

2. 数据库约束

在MySQL中，约束用于定义表中数据的规则，保证数据的一致性、完整性和准确性。以下是常见的数据库约束类型：

NULL约束

NOT NULL：指示某列不能存储NULL值。例如，创建一个表时，可以规定某列必须输入数据。
```
CREATE TABLE student (id INT NOT NULL,name VARCHAR(20) NOT NULL
);
```

UNIQUE：唯一约束

UNIQUE：确保某列的每行数据唯一，避免重复。适用于需要保证唯一性的字段，如学号、身份证号等。
```
CREATE TABLE student (id INT NOT NULL,sn INT UNIQUE,name VARCHAR(20),qq_mail VARCHAR(20)
);
```

DEFAULT：默认值约束

DEFAULT：用于为列定义一个默认值，当插入数据时没有指定该列值时，使用默认值。
```
CREATE TABLE student (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(20) DEFAULT 'Unknown'
);
```

PRIMARY KEY：主键约束

PRIMARY KEY：确保某列或多列数据唯一，并且不能为空。通常用于唯一标识每行记录。

表里最多只能有一个主键。

对于整数类型的主键，常配搭自增长auto_increment来使用。插入数据对应字段不给值时（赋值null时），从1开始，使用最大值+1（如果中间我们手动赋值后，再插入数据时，系统会使用最大值+1）

CREATE TABLE student (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(20)
);

FOREIGN KEY：外键约束

FOREIGN KEY：保证一个表中的数据与另一个表的数据相匹配，确保表与表之间的参照完整性。

CREATE TABLE classes (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(20),description VARCHAR(100)
);CREATE TABLE student (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(20),classes_id INT,FOREIGN KEY (classes_id) REFERENCES classes(id)
);

当赋值外键后，table student表中的classes_id为子键，classes表中的id为父键

从而导致classes_ID 内部的值要出自 id内部，如果出现不属于id内的值就会报错。（因为有约束，导致不能随意修改父键与子键）

对于父键必须要被unique或primary key 修饰

CHECK约束

CHECK：用于限制列的值，确保数据符合给定的条件。对于MySQL，它的使用并不会真正执行检查，只是语法上的支持。
```
CREATE TABLE test_user (id INT,sex VARCHAR(1),CHECK (sex = 'M' OR sex = 'F')
);
```

这个用的少，知道就行了，很少去使用。

3. 表的设计

在数据库设计中，表之间的关系是至关重要的。MySQL支持一对一、一对多和多对多的关系。

一对一

每个记录只对应另一个表中的一条记录。

一对多

一种常见的表关系，在这种关系中，父表的每一条记录可以与子表中的多条记录相关联。

多对多

多对多关系通常需要一个中间表来映射两张表的关系。

4. 插入和查询搭配（进阶）

在MySQL中，通过INSERT语句插入数据是常见的操作，插入数据时可以使用SELECT来插入其他表的内容。

INSERT INTO 目标表名 (列名1, 列名2, ...)
SELECT 列名1, 列名2, ...
FROM 源表

案例：创建一张用户表，设计有name姓名、email邮箱、sex性别、mobile手机号字段。需要把已有的学生数据复制进来，可以复制的字段为name、qq_mail

-- 创建用户表
DROP TABLE IF EXISTS test_user;
CREATE TABLE test_user (id INT primary key auto_increment,name VARCHAR(20) comment '姓名',age INT comment '年龄',email VARCHAR(20) comment '邮箱',sex varchar(1) comment '性别',mobile varchar(20) comment '手机号'
);
-- 将学生表中的所有数据复制到用户表
insert into test_user(name, email) select name, qq_mail from student;

5. 查询

5.1 聚合查询

MySQL支持多种聚合函数，如COUNT、SUM、AVG、MAX、MIN等，用于执行数据汇总操作。

示例：

SELECT COUNT(*) FROM student;
SELECT AVG(age) FROM student;

5.2 GROUP BY 子句

GROUP BY用于将结果集按指定列进行分组，并且可以与聚合函数一起使用。

SELECT 列名1, 聚合函数(列名)
FROM 表名
WHERE 条件
GROUP BY 列名1;
列名1 是用来分组的列。GROUP BY将结果按照这些列的值进行分组。
聚合函数(列名)：用于执行分组后的聚合计算（如COUNT()、SUM()、AVG()等）。
WHERE：过滤数据，通常在GROUP BY之前使用，来限制参与分组的数据。
GROUP BY：将数据按指定列进行分组。

注意select 指定的列必须是“分组依据列” (指定列中相同的行为一组)，其他列若想出现在select 中则必须包含在聚合函数中，否则会出现错误

（假如一组中有3行，该组内部都为不同的值，那该组的列展示出来的就是其中的一个值，下次可能会出现其他两个值，所以会有错误）

5.3 HAVING

HAVING用于在分组查询后对结果进行过滤。它与WHERE不同，WHERE用于对原始数据进行筛选，而HAVING用于对分组后的结果进行筛选。

where一般用在group by 前面，having用在group by 后面

SELECT department, AVG(salary) AS average_salary
FROM employees
WHERE salary > 3000  -- 使用WHERE先筛选薪水大于3000的员工
GROUP BY department  -- 按部门分组
HAVING AVG(salary) > 5000;  -- 使用HAVING过滤平均薪水大于5000的部门

6. 联合查询

联合查询：把多个表的记录一起合并，一起进行查询。联合查询又称多表查询

由于两个表的所有组合可能都有，所以我们要用where或者其他关键词进行限制

6.1 内连接（INNER JOIN）

INNER JOIN用于返回两个表中匹配的记录。

SELECT 列1, 列2, ...
FROM 表1
INNER JOIN 表2 ON 表1.列名 = 表2.列名;

还有另一种形式


SELECT 列1, 列2, ...
FROM 表1，表2 where 表1.列名 = 表2.列名;

6.2 左连接（LEFT JOIN）

左连接（也叫左外连接）返回左表中的所有记录，以及右表中匹配的记录。如果右表中没有匹配的记录，结果中的右表字段将为NULL。

SELECT 列1, 列2, ...
FROM 表1
LEFT JOIN 表2 ON 表1.列名 = 表2.列名;

6.3 右连接（RIGHT JOIN）

右连接（也叫右外连接）与左连接类似，不同之处在于它返回右表中的所有记录，以及左表中匹配的记录。如果左表中没有匹配的记录，结果中的左表字段将为NULL。

SELECT 列1, 列2, ...
FROM 表1
RIGHT JOIN 表2 ON 表1.列名 = 表2.列名;

6.4 自连接（Self JOIN）

自连接是将同一张表与自己进行连接，常用于查找表中相同数据之间的关系。


SELECT ........
FROM student as A, student as B
WHERE ........ ;

6.5 子查询

子查询是指将一个查询语句嵌套在另一个查询语句中，常用于处理复杂的查询需求。

如果内部的查询语句只查询到了一行，那么用 =

SELECT * FROM student WHERE classes_id = (SELECT id FROM classes WHERE name = '计算机系');

如果内部的查询语句查询到了多行，那么用 in 关键字


SELECT * FROM student WHERE classes_id  in (SELECT id FROM classes WHERE name = '计算机系');

6.6 合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符union, union all。

-- UNION：去除重复数据 
select ... from ... where 条件 union select ... from ... where 条件-- UNION ALL：不去重select ... from ... where 条件 union all select ... from ... where 条件  
使用UNION和UNION ALL时，前后查询的列要求 数量和类型一致 这样才能上下合并一块。