【MySQL 保姆级教学】表数据的操作--下（8）

1. 列值更新 UPDATE

1.1 定义

UPDATE 语句在 SQL 中用于修改表中已有的记录。它允许你更改一个或多个列的值，可以针对表中的所有记录，也可以通过== WHERE 子句==指定特定的记录。

注意：更新指定列的时候一定要用条件子句（where）。

语法：

UPDATE table_name
SET column1 = value1, column2 = value2, …
[WHERE condition];

注意事项

• WHERE 子句的重要性：如果不使用 WHERE 子句，UPDATE 语句会更新表中的所有记录。这可能会导致意外的数据修改，因此务必谨慎使用。
• 事务处理：对于重要的更新操作，建议使用事务来确保数据的一致性和完整性。如果更新失败，可以回滚事务以恢复到更新前的状态。
• 性能考虑：大规模的更新操作可能会影响数据库性能，特别是在没有适当索引的情况下。确保表上有适当的索引，并且在生产环境中测试更新操作的性能影响。

1.2 示例

使用该表的数据

1. 将李白的英语成绩更改为60分
   命令：update score set english=60 where name ='李白';
   

2. 将李华的数学和英语都改为70分
   命令：update score set math=70, english=70 where name ='李华';
   

3. 将总成绩倒数前三的同学英语加10分。
   查询：select name, chinese+math+english total from score order by total asc limit 3;
   
   更新倒数前三的学生：
   命令：update score set english=english +10 order by chinese+math+english limit 3;
   
   

4. 将所有同学的语文成绩翻一倍。
   命令：update score set chinese=chinese*2 ;
   
   注意：更新全表的语句要慎用。

2. 删除数据 DELETE

2.1 定义

DELETE 语句在 SQL 中用于从表中删除记录。它可以删除表中的单个记录、多个记录，或者在没有 WHERE 子句的情况下删除所有记录。

语法：

DELETE FROM table_name
[WHERE condition];

注意事项

• WHERE 子句的重要性：如果不使用 WHERE 子句，DELETE 语句会删除表中的所有记录。这可能会导致意外的数据丢失，因此务必谨慎使用。
• 事务处理：对于重要的删除操作，建议使用事务来确保数据的一致性和完整性。如果删除失败，可以回滚事务以恢复到删除前的状态。
• 性能考虑：大规模的删除操作可能会影响数据库性能，特别是在没有适当索引的情况下。确保表上有适当的索引，并且在生产环境中测试删除操作的性能影响。
• 外键约束：如果表有外键约束，删除主表中的记录时，可能需要处理从表中的相关记录。可以通过设置级联删除（ON DELETE CASCADE）来自动删除从表中的相关记录。

2.2 示例

使用该表操作

1. 删除李华所在的一行
   命令：delete from score where name='李华';
   

2. 删除全部数据
   命令：delete from score;
   
   命令：select * from score;
   

3. 创建一个自增类型的表，插入数据后在删除所有数据。
   命令：create table t1(id int unique auto_increment, name varchar(10));
   命令：insert into table t1 (name) values('李华', '李明', '李刚');
   
   查询创建表命令：show create table t1;
   
   删除全部数据命令：delete from t1;

   查看创建表
   
   此时的auto_increment仍然是4
   插入数据：insert into t1 (name) values(‘李华’);
   再次查看数据：select * from t1;
   
   同学们可以发现，删除表的全部数据后，auto_increment的值仍然存在，当插入数据的是时候，auto_increment类型的字段仍然会按计数值自增。

3. 截断表 TRUNCATE

3.1 定义

TRUNCATE 语句在 SQL 中用于删除表中的所有记录，但它与 DELETE 语句有一些重要的区别。TRUNCATE 通常比 DELETE 更快，并且在某些情况下具有不同的行为。

语法：

TRUNCATE TABLE table_name;

注意：这个操作慎用

1. 只能对整表操作，不能像 DELETE 一样针对部分数据操作；
2. 实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事物，所以无法回滚
3. 会重置 AUTO_INCREMENT 项

3.2 示例

1. 准备测试表
   命令：create table t1 (id int unique auto_increment, name varchar(10));
   命令：insert into t1 (name) values('李明'), ('李华');
   命令：select * from t1;
   
2. 删除表数据
   查看创建表命令：show table t1;
   
   删除表数据，在次查看创建表
   命令：truncate table t1;
   命令：show table t1;
   
   此时已经不存在auto_increment的值了
   再次插入数据，然后查询数据
   命令：insert into t1 (name) values('李华');
   命令：select * from t1;
   
   同学们可以看到，插入数据后，id是从1开始的。所以，truncate删除数据也把auto_increment的值也清空了。

3.3 日志

TRUNCATE 通常只记录一个操作，而 DELETE 会为每条删除的记录记录一个操作。

日志：

1. bin log：用于主从复制，记录所有对数据库的更改操作（如 INSERT、UPDATE、DELETE），以便从库可以同步这些更改。
2. redo log：确保事务的持久性。当数据库崩溃后，可以通过重做日志将未完成的事务重新执行，以恢复数据到一致状态。
3. undo log：当事务需要回滚时，使用撤销日志来恢复数据到事务开始前的状态。

4.插入查询结果

4.1 定义

插入查询结果（也称为子查询插入或 INSERT … SELECT 语句）是一种在 SQL 中非常有用的技术，它允许你将一个查询的结果插入到另一个表中。这种操作通常用于批量插入数据、从一个表复制数据到另一个表、或者将复杂查询的结果存储到新表中。

语法：

INSERT INTO target_table (column1, column2, …)
SELECT column1, column2, …
FROM source_table
[WHERE condition];

4.2 示例

解决问题：删除表t1中的的重复复记录，重复的数据只能有一份

同学们是不是想到了distinct,但distinct是对查询的结果取重，问题想解决的是原表的数据去重。

解决思路：
（1）用like创建一个新表
（2）用distinct查询到的数据插入到新表中
（3）用renaem更改新表和旧表的名字

1. CREATE TABLE ... LIKE 语法，它允许你基于现有的表结构创建一个新表。这种语法主要用于复制现有表的结构，而不复制数据。
   命令：create table new_t1 like t1;
   
2. 用distinct查询的结果插入到new_t1表中
   省略插入的列表示插入全列。
   命令：insert into new_t1 select distinct id, name from t1;
   
3. 修改表名
   命令：rename table t1 to old_t1;
   命令：rename table new_t1 to t1;
   
   查看修改后的数据：select * from t1;
   

5. 聚合函数

5.1 定义

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的 数量
SUM([DISTINCT] expr)	返回查询到的非空数据的 总和，不是数字没有意义
AVG([DISTINCT] expr)	返回查询到的非空数据的 平均值，不是数字没有意义
MAX([DISTINCT] expr)	返回查询到的非空数据的 最大值，不是数字没有意义
MIN([DISTINCT] expr)	返回查询到非空数据的 最小值，不是数字没有意义

count解释：

• count(column_name)：计算指定列中的非 NULL 值的数量
• count(*)：计算所有行的数量（包括 NULL 值）

5.2 示例

对t1表进行操作

1. 使用count(*)做统计，不受 NULL影响
   命令：select count(*) from t1;
   

2. 使用count(name)，不统计统计非NULL
   命令：select count(name) from t1;
   

3. 使用count(distinct id) ，去重id 的数量。
   命令：select count(distinct id) from t1;
   

4. sun(score)统计分数的和
   命令：select sun(score) from t1;
   

5. 统计score小于70分的和
   命令：select sun(score) from t1 where score<70;
   

6. 统计score小于70分的和，没有结果返回为空。
   命令：select sum(score) from t1 where score < 30;
   

7. avg()统计平均分
   命令：select avg(score) from t1;
   

8.max 统计最高分
命令：select max(score) from t1;

9. min()找出高于或等于60的最低分
   select min(score) from t1 where score >=60;
   

6. 分组聚合 GROUP BY

6.1 定义

GROUP BY 子句在 SQL 中用于将数据按照一个或多个列进行分组。它的主要作用是将具有相同值的行组合在一起，以便可以对每个组执行聚合函数（如 SUM、COUNT、AVG 等）或其他操作。GROUP BY 通常与聚合函数一起使用，以生成汇总报告。

语法：

SELECT column1, column2, aggregate_function(column3)
FROM table_name
WHERE condition
GROUP BY column1, column2;

说明：

• column1, column2：用于分组的列。
• aggregate_function(column3)：对每个组应用的聚合函数，例如 SUM、COUNT、AVG 等。
• table_name：要查询的表名。
• condition：可选的 WHERE 子句，用于筛选数据。

注：查询的信息只能是进行分组的字段名和被聚合的字段名。

6.2 示例

对表emp进行操作

1. 查询每个部门的最高工资和平均工资
   命令：select department, max(salary), avg(salary) from emp group by deparment;
   
   错误示例：
   命令：select department,name, max(salary), avg(salary) from emp group by deparment;
   
   查询的信息不是随意可查询的，只能是进行分组的字段名和被聚合的字段名。

2. 每个部门的不同性别的平均工资和最高工资
   命令：select department,gender, max(salary),avg(salary) from emp group by department, gender;
   

3. 查询平均工资低于1万的部门
   命令：select department, avg(salary) from emp group by department having avg(salary) < 10000;
   

7. where VS having

7.1 where VS having

先看两个命令：

• having：select name, salary sal from emp having sal>10000;
  

• where：select name, salary sal from emp having sal>10000;
  

这是为什么呢？
答：子句的执行顺序不同，从上可以看出，where比having的优先级更高。

7.2 “ MySQL一切为表 ”

排除女生，只用男生做部门分组和薪资聚合，找出平均薪资大于1万的部门
命令：

select department, avg(salary)
from emp
where gender !=‘女’
group by department
having avg(salary) > 10000;

执行的步骤：

换种角度来看：

不要单纯的以为，只有磁盘上表结构导入到mysql，真实存在的表才叫做表。

其实，在sql语句执行中，中间筛选出来的、最终结果，都可以看做为逻辑上的表。“MySQL一切为表”

未来只要我们能处理好单表的CURD，所有的sql场景，我们全部都能用同一的方式进行。

比如：

1. from emp ：相当于对表的第一步操作，提取表数据，然后显示出表1
2. where gender !='女'：对表1进行筛选，筛选出不为女生的数据，筛选后的表为表2
3. group by deparment：对表2进行分组，分组后的表为表3
4. select department, avg(salary)：对表3进行查询和聚合，操作后的表为表4
5. having avg(salary)：对表4进行筛选，筛选后的表为表5，表5作为最后的执行结果显示出来。