【MySQL 保姆级教学】表数据的操作--下(8)
基本查询
- 1. 列值更新 UPDATE
- 1.1 定义
- 1.2 示例
- 2. 删除数据 DELETE
- 2.1 定义
- 2.2 示例
- 3. 截断表 TRUNCATE
- 3.1 定义
- 3.2 示例
- 3.3 日志
- 4.插入查询结果
- 4.1 定义
- 4.2 示例
- 5. 聚合函数
- 5.1 定义
- 5.2 示例
- 6. 分组聚合 GROUP BY
- 6.1 定义
- 6.2 示例
- 7. where VS having
- 7.1 where VS having
- 7.2 “ MySQL一切为表 ”
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) | 返回查询到非空数据的 最小值,不是数字没有意义 |
coun
t解释:
- 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
作为最后的执行结果显示出来。