[MySQL][表的增删查改][三][Update][Delete][插入查询结果][聚合函数][group by]详细讲解

于 2024-07-17 00:03:45 发布
阅读量1k 收藏 17
点赞数 21
分类专栏： [MySQL] 文章标签： mysql 数据库 表的增删查改 Update 插入查询结果 聚合函数 group by
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_37281656/article/details/140454763
版权

目录

1.Update

1.基本语法

  • 对查询到的结果进行列值更新
    UPDATE table_name SET column = expr [, column = expr ...] [WHERE ...] [ORDER BY ...] [LIMIT ...]

2.示例

  • 将孙悟空同学的数学成绩变更为 80 分
    ## 更新值为具体值

## 查看原数据
SELECT name, math FROM exam_result WHERE name = '孙悟空';
+-----------+--------+
| name      | math   |
+-----------+--------+
| 孙悟空    | 78     |
+-----------+--------+
1 row in set (0.00 sec)

## 数据更新
UPDATE exam_result SET math = 80 WHERE name = '孙悟空';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0

## 查看更新后数据
SELECT name, math FROM exam_result WHERE name = '孙悟空';
+-----------+--------+
| name      | math   |
+-----------+--------+
| 孙悟空    | 80     |
+-----------+--------+
1 row in set (0.00 sec)
  • 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分
    # 一次更新多个列
# 查看原数据
SELECT name, math, chinese FROM exam_result WHERE name = '曹孟德';
+-----------+--------+---------+
|name       | math   | chinese |
+-----------+--------+---------+
| 曹孟德     | 84    | 82      |
+-----------+--------+---------+
1 row in set (0.00 sec)

# 数据更新
UPDATE exam_result SET math = 60, chinese = 70 WHERE name = '曹孟德';
Query OK, 1 row affected (0.14 sec)
Rows matched: 1 Changed: 1 Warnings: 0

# 查看更新后数据
SELECT name, math, chinese FROM exam_result WHERE name = '曹孟德';
+-----------+--------+---------+
| name      | math   | chinese |
+-----------+--------+---------+
| 曹孟德    | 60     | 70      |
+-----------+--------+---------+
1 row in set (0.00 sec)
  • 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
    # 更新值为原值基础上变更
# 查看原数据
# 别名可以在ORDER BY中使用
SELECT name, math, chinese + math + english 总分 FROM exam_result ORDER BY 总分 LIMIT 3;
+-----------+--------+--------+
| name      | math   | 总分   |
+-----------+--------+--------+
| 宋公明    | 65     | 170    |
| 刘玄德    | 85     | 185    |
| 曹孟德    | 60     | 197    |
+-----------+--------+--------+
3 rows in set (0.00 sec)

# 数据更新，不支持 math += 30 这种语法
UPDATE exam_result SET math = math + 30 ORDER BY chinese + math + english LIMIT 3;

# 查看更新后数据
# 思考：这里还可以按总分升序排序取前 3 个么？
SELECT name, math, chinese + math + english 总分 FROM exam_result WHERE name IN ('宋公明', '刘玄德', '曹孟德');
+-----------+--------+--------+
| name      | math   | 总分   |
+-----------+--------+--------+
| 曹孟德    | 90     | 227    |
| 刘玄德    | 115    | 215    |
| 宋公明    | 95     | 200    |
+-----------+--------+--------+
3 rows in set (0.00 sec)

# 按总成绩排序后查询结果
SELECT name, math, chinese + math + english 总分 FROM exam_result ORDER BY 总分 LIMIT 3;
+-----------+--------+--------+
| name      | math   | 总分   |
+-----------+--------+--------+
| 宋公明    | 95     | 200    |
| 刘玄德    | 115    | 215    |
| 唐三藏    | 98     | 221    |
+-----------+--------+--------+
3 rows in set (0.00 sec)
  • 将所有同学的语文成绩更新为原来的 2 倍
    • 注意：更新全表的语句慎用
    # 没有 WHERE 子句，则更新全表
# 查看原数据
SELECT * FROM exam_result;
+----+-----------+---------+--------+---------+
| id | name      | chinese | math   | english |
+----+-----------+---------+--------+---------+
| 1  | 唐三藏    | 67      | 98      | 56     |
| 2  | 孙悟空    | 87      | 80      | 77     |
| 3  | 猪悟能    | 88      | 98      | 90     |
| 4  | 曹孟德    | 70      | 90      | 67     |
| 5  | 刘玄德    | 55      | 115     | 45     |
| 6  | 孙权      | 70      | 73      | 78     |
| 7  | 宋公明    | 75      | 95      | 30     |
+----+-----------+---------+--------+---------+
7 rows in set (0.00 sec)

# 数据更新
UPDATE exam_result SET chinese = chinese * 2;
Query OK, 7 rows affected (0.00 sec)
Rows matched: 7 Changed: 7 Warnings: 0

# 查看更新后数据
SELECT * FROM exam_result;
+----+-----------+---------+--------+---------+
| id | name      | chinese | math   | english |
+----+-----------+---------+--------+---------+
| 1  | 唐三藏    | 134      | 98      | 56     |
| 2  | 孙悟空    | 174      | 80      | 77     |
| 3  | 猪悟能    | 176      | 98      | 90     |
| 4  | 曹孟德    | 140      | 90      | 67     |
| 5  | 刘玄德    | 110      | 115     | 45     |
| 6  | 孙权      | 140      | 73      | 78     |
| 7  | 宋公明    | 150      | 95      | 30     |
+----+-----------+---------+--------+---------+
7 rows in set (0.00 sec)

2.Delete

1.删除 基本语法

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

2.示例

  • 删除孙悟空同学的考试成绩
    # 查看原数据
SELECT * FROM exam_result WHERE name = '孙悟空';
+----+-----------+---------+--------+---------+
| id | name      | chinese | math   | english |
+----+-----------+---------+--------+---------+
| 2  | 孙悟空    | 174     | 80     | 77      |
+----+-----------+---------+--------+---------+
1 row in set (0.00 sec)

# 删除数据
DELETE FROM exam_result WHERE name = '孙悟空';
Query OK, 1 row affected (0.17 sec)

# 查看删除结果
SELECT * FROM exam_result WHERE name = '孙悟空';
Empty set (0.00 sec)
  • 删除整张表数据
    • 注意：删除整表操作要慎用
    # 准备测试表
CREATE TABLE for_delete (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
Query OK, 0 rows affected (0.16 sec)

# 插入测试数据
INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');
Query OK, 3 rows affected (1.05 sec)
Records: 3 Duplicates: 0 Warnings: 0

# 查看测试数据
SELECT * FROM for_delete;
+----+------+
| id | name |
+----+------+
| 1  | A    |
| 2  | B    |
| 3  | C    |
+----+------+
3 rows in set (0.00 sec)

# 删除整表数据
DELETE FROM for_delete;
Query OK, 3 rows affected (0.00 sec)

# 查看删除结果
SELECT * FROM for_delete;
Empty set (0.00 sec)

# 再插入一条数据，自增 id 在原值上增长
INSERT INTO for_delete (name) VALUES ('D');
Query OK, 1 row affected (0.00 sec)

# 查看数据
SELECT * FROM for_delete;
+----+------+
| id | name |
+----+------+
| 4  | D    |
+----+------+
1 row in set (0.00 sec)

# 查看表结构，会有 AUTO_INCREMENT=n 项
SHOW CREATE TABLE for_delete\G
*************************** 1. row ***************************
Table: for_delete
Create Table: CREATE TABLE `for_delete` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

3.截断表 基本语法

  • 语法
    TRUNCATE [TABLE] table_name
  • 注意：这个操作慎用
    • 只能对整表操作，不能像 DELETE 一样针对部分数据操作
    • 实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事
    • 物，所以无法回滚
      • 即：Delete会存进日志，Truncate不会
    • 会重置 AUTO_INCREMENT 项

4.示例

# 准备测试表
CREATE TABLE for_truncate (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
Query OK, 0 rows affected (0.16 sec)

# 插入测试数据
INSERT INTO for_truncate (name) VALUES ('A'), ('B'), ('C');
Query OK, 3 rows affected (1.05 sec)
Records: 3 Duplicates: 0 Warnings: 0

# 查看测试数据
SELECT * FROM for_truncate;
+----+------+
| id | name |
+----+------+
| 1  | A    |
| 2  | B    |
| 3  | C    |
+----+------+
3 rows in set (0.00 sec)

# 截断整表数据，注意影响行数是 0，所以实际上没有对数据真正操作
TRUNCATE for_truncate;
Query OK, 0 rows affected (0.10 sec)

# 查看删除结果
SELECT * FROM for_truncate;
Empty set (0.00 sec)

# 再插入一条数据，自增 id 在重新增长
INSERT INTO for_truncate (name) VALUES ('D');
Query OK, 1 row affected (0.00 sec)

# 查看数据
SELECT * FROM for_truncate;
+----+------+
| id | name |
+----+------+
| 1  | D    |
+----+------+
1 row in set (0.00 sec)

# 查看表结构，会有 AUTO_INCREMENT=2 项
SHOW CREATE TABLE for_truncate\G
*************************** 1. row ***************************
Table: for_truncate
Create Table: CREATE TABLE `for_truncate` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

3.插入查询结果

1.基本语法

INSERT INTO table_name [(column [, column ...])] SELECT ...

2.示例

  • 删除表中的的重复记录，重复的数据只能有一份
    # 创建原数据表
CREATE TABLE duplicate_table (id int, name varchar(20));
Query OK, 0 rows affected (0.01 sec)

# 插入测试数据
INSERT INTO duplicate_table VALUES
(100, 'aaa'),
(100, 'aaa'),
(200, 'bbb'),
(200, 'bbb'),
(200, 'bbb'),
(300, 'ccc');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
  • 思路：
    • 为什么最后是通过rename方式进行的？
      • 单纯的等一切都就绪了，然后统一放入、更新、生效

4.聚合函数

1.聚合函数

请添加图片描述

2.示例

  • 统计班级共有多少同学
    # 使用 * 做统计，不受 NULL 影响
SELECT COUNT(*) FROM students;
+----------+
| COUNT(*) |
+----------+
| 4        |
+----------+
1 row in set (0.00 sec)

# 使用表达式做统计
SELECT COUNT(1) FROM students;
+----------+
| COUNT(1) |
+----------+
| 4        |
+----------+
1 row in set (0.00 sec)
  • 统计班级收集的 qq 号有多少
    # NULL 不会计入结果
SELECT COUNT(qq) FROM students;
+-----------+
| COUNT(qq) |
+-----------+
| 1         |
+-----------+
1 row in set (0.00 sec)
  • 统计本次考试的数学成绩分数个数
    # COUNT(math) 统计的是全部成绩
SELECT COUNT(math) FROM exam_result;
+---------------+
| COUNT(math)   |
+---------------+
| 6             |
+---------------+
1 row in set (0.00 sec)

# COUNT(DISTINCT math) 统计的是去重成绩数量
SELECT COUNT(DISTINCT math) FROM exam_result;
+------------------------+
| COUNT(DISTINCT math)   |
+------------------------+
| 5                      |
+------------------------+
1 row in set (0.00 sec)
  • 统计数学成绩总分
    SELECT SUM(math) FROM exam_result;
+-------------+
| SUM(math)   |
+-------------+
| 569         |
+-------------+
1 row in set (0.00 sec)

# 不及格 < 60 的总分，没有结果，返回 NULL
SELECT SUM(math) FROM exam_result WHERE math < 60;
+-------------+
| SUM(math)   |
+-------------+
| NULL        |
+-------------+
1 row in set (0.00 sec)
  • 统计平均总分
    SELECT AVG(chinese + math + english) 平均总分 FROM exam_result;
+--------------+
| 平均总分      |
+--------------+
| 297.5        |
+--------------+
  • 返回英语最高分
    SELECT MAX(english) FROM exam_result;
+--------------+
| MAX(english) |
+--------------+
| 90           |
+--------------+
1 row in set (0.00 sec)
  • 返回 > 70 分以上的数学最低分
    SELECT MIN(math) FROM exam_result WHERE math > 70;
+-------------+
| MIN(math)   |
+-------------+
| 73          |
+-------------+
1 row in set (0.00 sec)

5.group by子句的使用

1.基础语法

  • 语法：
    select column1, column2, .. from table group by column;
  • 分组的目的是为了进行分组之后，方便进行聚合统计
  • 指定列名，实际分组，是用该列的不同的行数据来进行分组的
    • 分组的条件column，组内一定是相同的 --> 可以被聚合压缩
  • 分组：把一组按照条件拆成了多个组，进行各自组内的统计
    • 所以，分组可以理解为“分表”，把一张表按照条件在逻辑上拆成了多个子表，然后分别对各自的子表进行聚合统计

2.示例

  • 准备工作，创建一个雇员信息表(来自Oracle 9i的经典测试表)
    • EMP员工表
    • DEPT部门表
    • SALGRADE工资等级表
  • 如何显示每个部门的平均工资和最高工资
    select deptno,avg(sal),max(sal) from EMP group by deptno;
  • 显示每个部门的每种岗位的平均工资和最低工资
    • 先分组，再聚合
    select avg(sal),min(sal),job, deptno from EMP group by deptno, job;
  • 显示平均工资低于2000的部门和它的平均工资
    • 统计各个部门的平均工资
      • 统计出来每一个部门的平均工资 --> 结果先聚合出来
      • 再进行判断 --> 对聚合的结果，进行判断
    select avg(sal) from EMP group by deptno
  • having和group by配合使用，对group by结果进行过滤
    • having经常和group by搭配使用，作用是对分组进行筛选，作用有些像where
    select avg(sal) as myavg from EMP group by deptno having myavg<2000;

3.having vs where

  • 区别理解？

    • 不要单纯的认为，只有磁盘上表结构导入到MYSQL，真实存在的表，才叫做表
    • 中间筛选出来的，包括最终结果，在用户看来，全部都是逻辑上的表
    • 即：MYSQL一切皆表

  • 执行顺序？

    • 条件筛选的阶段是不同的
    • where****对具体的任意列进行条件筛选
    • having****对分组聚合之后的结果进行条件筛选

6.总结关键字执行顺序

  • from > on> join > where > group by > with > having > select > distinct > order by > limit
DieSnowK
关注
  • 21
    点赞
  • 踩
  • 17
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 3
    评论
专栏目录
[MySQL] MySQL 表的增删查改
weixin_67596609的博客
11-23 2719
本篇文章对mysql表的增删查改进行了详细的举例说明解释。对表的增删查改简称CRUD : Create(创建), Retrieve(读取)，Update(更新)，Delete（删除）。其中重点是对查询select语句进行了详细解释，并且通过多个实际例子来帮助你的理解。希望本篇文章会对你有所帮助。
mysql表的增删查改
m0_51641706的博客
04-13 1164
文章目录create（插入数据）单行数据全列插入多行数据全列插入指定列插入插入数据，如果是唯一性的数据且表中有重复数据，更新它replaceretrieveselectselect *select 指定列查询表达式select distinctselect + where英语不及格的同学及英语成绩 ( < 60 )语文成绩在 [80, 90] 分的同学及语文成绩数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩姓孙的同学名字两个字且姓孙的人语文成绩好于英语成绩的同学总分在 200
【MySQL】表的增删查改操作详解
Z1tai的博客
06-18 977
MySQL 表的增删查改操作详解 在数据库管理和应用开发中，增删查改（CRUD）操作是最基本且最频繁的任务。CRUD 代表了四种基本的数据库操作：创建（Create）、读取（Retrieve）、更新（Update）和删除（Delete）。
【MySQL】增删查改基础
学了就忘是正常现象
06-20 1576
什么是excel高级版啊！【战术后仰
MySQL表的约束和表的增删查改
weixin_57023347的博客
11-20 1834
主要介绍表的约束和表的增删查改操作，用法。
MySQL——表的增删查改
qq_63943454的博客
12-20 2991
mysql 基本的增删查改操作。
MySQL表的增删查改
m0_61567378的博客
10-09 1976
LIKE:模糊匹配，%表示任意多个自读，_表示任意一个字符,后面只能跟一个字符。多个条件排序 ，优先按照前面的排序，相同按后面的排序，再相同再按后面的排序。把所有人的id和英语成绩查出来，把英语成绩+10分 ,没有影响原始数据。2 row affected :表中有冲突数据，不求额数据被更新。也可以包含一个不存在的字段，只能是一个可被计算的表达式。order by 是最后执行的，筛选数据是最先执行的。查询姓s的同学或者姓c的同学，结果按照数学成绩排序。先执行from emp，后按部门分组，再进行计算。
【MySQL】数据库中表的增删查改操作详解
moteandsunlight的博客
11-15 7096
sql、curd、表的增删改查、mysql、数据库
MySQL数据库 -- 表的增删查改
qq_58724706的博客
11-15 1428
今天来讲MySQL数据库的表增删查改操作。今天主要是通过栗子来演示语法使用的，话不多说，直奔主题~Create(创建), Retrieve(读取)，Update(更新)，Delete（删除）
【MySQL】数据库的增删查改
flmz_Kk的博客
06-07 1831
本篇博客我们主要介绍了数据表的新增、约束、查询、更新、删除一系列操作，其中最重要的就是查询
mysql之增删查改
04-24
除了基础操作，还有更复杂的查询技术，如联接（JOIN）、子查询、分组（GROUP BY）和聚合函数（如COUNT、SUM等）。例如，要获取每个年龄段的学生人数： ```sql SELECT age, COUNT(*) AS count FROM students GROUP ...
MySQL聚合函数、group by、update、delete 博客中用到的一个数据库备份文件
11-20
雇员、部门、薪资表
MySQL增删改查语句练习.docx
11-01
本练习主要涉及的是MySQL的基础操作，即增（INSERT）、删（DELETE）、改（UPDATE）和查（SELECT）这四大基本操作，以及数据库设计和表之间的关系。 1. **数据库创建**： 在MySQL中，可以使用`CREATE DATABASE`语句...
Mysql基础增删改查语句
09-22
除了以上基础操作，MySQL还支持更复杂的查询，如联接（JOIN）、子查询、分组（GROUP BY）、排序（ORDER BY）、聚合函数（SUM, COUNT, AVG等）以及事务处理等。熟悉并掌握这些功能，将使你在数据库管理中游刃有余。在...
MySQL数据库增删改查操作小结
10-23
聚合函数用于计算查询结果的统计信息，例如：count(*)计算总记录数，avg(score)计算平均成绩。 添加外键 Alter table 表名 1 add foreign key 当前表名（字段 1）references 关联的表（字段 2） 添加外键约束，...
mysql笔记1
m0_62975692的博客
07-11 396
首先查看systemctl status mysqld，如果是关闭的可以进入的配置文件打开mysql，我的mysql配置文件就是在/usr/local/mysql/support-filess/下，在此目录下执行./mysql.server start,打开mysql服务，也可以通过systmectl start mysqld，如果你嫌麻烦的话可以systemctl enable mysqld 一直打开mysql的服务;重新给字段添加约束的时候不可以添加not null约束的！
mysql链接错误，unblock with ‘mysqladmin flush-hosts‘“
java之书-资源
07-16 164
message from server: "Host '172.16.39.238' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'".
Mysql具体数据操作和表的约束（上）
SDU MATH
07-13 641
Mysql具体数据操作和表的约束
记录些MySQL题集（8）
最新发布
安静的软件工程师
07-16 907
记录些MySQL题集（8）
数据库——玩转SQL语句(以MySQL为例)
12-14
文章目录一、前言二、插入数据、删除数据、更新数据2.1 插入数据2.2 删除数据2.3 更新数据三、查询数据（全文重点）3.1 原表3.2 Select语句引入3.3 最基本的select查询语句3.4 设置查询条件3.5 对查询结果排序3.6 使用聚合函数3.7 对查询结果分组3.8 指定组或聚合的搜索条件3.9 连接查询3.9.1 内连接3.9.2 外连接3.9.3 交叉连接3.10 子查询3.11 合并查询四、小结 一、前言 关于SQL语句的学习，有三个阶段。 第一阶段：懂 SQL 语句，能够根据业务需求实现 CRUD 功能； 第二阶段：懂数据库的基本原理、索引原理，能够定位分析数据库性

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 3
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

DieSnowK

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值