IT评测·应用市场-qidao123.com技术社区

标题: 【MySQL 保姆级讲授】表数据的操作--下（8） [打印本页]

---

作者: 羊蹓狼    时间: 2024-11-1 17:19
标题: 【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 示例

使用该表的数据

• 将李白的英语结果更改为60分
  命令：update score set english=60 where name ='李白';
  
  
  
  
• 将李华的数学和英语都改为70分
  命令：update score set math=70, english=70 where name ='李华';
  
  
  
  
• 将总结果倒数前三的同砚英语加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;
  
  
  
  
  
  
  
• 将所有同砚的语文结果翻一倍。
  命令：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 示例

使用该表操作

• 删除李华地点的一行
  命令：delete from score where name='李华';
  
  
  
  
• 删除全部数据
  命令：delete from score;
  
  
  
  命令：select * from score;
  
  
  
  
• 创建一个自增类型的表，插入数据后在删除所有数据。
  命令：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;
  注意：这个操作慎用

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

3.2 示例

• 准备测试表
  命令：create table t1 (id int unique auto_increment, name varchar(10));
  命令：insert into t1 (name) values('李明'), ('李华');
  命令：select * from t1;
  
  
  
  
• 删除表数据
  查看创建表命令：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 会为每条删除的记载记载一个操作。
日记：

• bin log：用于主从复制，记载所有对数据库的更改操作（如 INSERT、UPDATE、DELETE），以便从库可以同步这些更改。
  
• redo log：确保变乱的持久性。当数据库崩溃后，可以通过重做日记将未完成的变乱重新实行，以规复数据到同等状态。
  
• 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更改新表和旧表的名字

• CREATE TABLE ... LIKE 语法，它允许你基于现有的表结构创建一个新表。这种语法重要用于复制现有表的结构，而不复制数据。
  命令：create table new_t1 like t1;
  
  
  
  
• 用distinct查询的结果插入到new_t1表中
  省略插入的列表示插入全列。
  命令：insert into new_t1 select distinct id, name from t1;
  
  
  
  
• 修改表名
  命令：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表进行操作

• 使用count(*)做统计，不受 NULL影响
  命令：select count(*) from t1;
  
  
  
  
• 使用count(name)，不统计统计非NULL
  命令：select count(name) from t1;
  
  
  
  
• 使用count(distinct id) ，去重id 的数量。
  命令：select count(distinct id) from t1;
  
  
  
  
• sun(score)统计分数的和
  命令：select sun(score) from t1;
  
  
  
  
• 统计score小于70分的和
  命令：select sun(score) from t1 where score<70;
  
  
  
  
• 统计score小于70分的和，没有结果返回为空。
  命令：select sum(score) from t1 where score < 30;
  
  
  
  
• avg()统计匀称分
  命令：select avg(score) from t1;
  
  
  
  

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

• 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进行操作

• 查询每个部分的最高工资和匀称工资
  命令：select department, max(salary), avg(salary) from emp group by deparment;
  
  
  
  错误示例：
  命令：select department,name, max(salary), avg(salary) from emp group by deparment;
  
  
  
  查询的信息不是随意可查询的，只能是进行分组的字段名和被聚合的字段名。
  
• 每个部分的差异性别的匀称工资和最高工资
  命令：select department,gender, max(salary),avg(salary) from emp group by department, gender;
  
  
  
  
• 查询匀称工资低于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场景，我们全部都能用同一的方式进行。
好比：

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

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

---

欢迎光临 IT评测·应用市场-qidao123.com技术社区 (https://dis.qidao123.com/)

Powered by Discuz! X3.4