当前位置：首页 > web >正文

MYSQL之基本查询(CURD)

web 2025/5/16 10:01:05

表的增删改查

表的增加

语法:

INSERT [INTO] table_name
[(column [, column] ...)]
VALUES (value_list) [, (value_list)] ...
value_list: value, [, value] ...

全列插入和指定列插入

//创建一张学生表
CREATE TABLE students (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,sn INT NOT NULL UNIQUE COMMENT '学号',name VARCHAR(20) NOT NULL,qq VARCHAR(20)
);

全列插入:

插入的一行数据中, value_list 数量 必须和 定义表的列 的数量及顺序一致.

INSERT INTO students VALUES (100, 10000, '唐三藏', NULL);

指定列插入

注意, 这里在插入的时候, 由于id是primary key 且 auto_increment, 所以也可以不用指定 id , 这时就需要明确插入数据到那些列了, 那么mysql会使用默认的值进行自增

INSERT INTO students (id, sn, name) VALUES (101, 20001, '曹孟德');

单行插入和多行插入

单行插入

之前就是单行插入, 略.

多行插入

多行插入就是在单行插入后用逗号分隔多条 value_list:

INSERT INTO students (id, sn, name) VALUES
(102, 20001, '刘玄德'),
(103, 20002, '孙仲谋');

插入否则更新和替换

ON DUPLICATE KEY UPDATE 和 REPLACE 都是在插入数据时处理 主键或唯一键冲突 的两种机制, 但它们有行为差别.

1. ON DUPLICATE KEY UPDATE

INSERT INTO table_name (col1, col2, ...)
VALUES (val1, val2, ...)
ON DUPLICATE KEY UPDATE colX = valX, colY = valY, ...;

ON DUPLICATE KEY UPDATE 是 INSERT 语句的一种扩展, 用于在插入时如果发生主键或唯一键冲突时, 自动转为更新已有记录, 它并不会删除旧的记录.
所以如果希望保留记录的其他字段, 只更新部分内容, 可以使用这个.

假如现在有这样一个商品表:

CREATE TABLE cart (->   user_id INT,->   product_id INT,->   quantity INT DEFAULT 1,->   PRIMARY KEY (user_id, product_id)-> );

0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等
1 row affected: 表中没有冲突数据，数据被插入
2 row affected: 表中有冲突数据，并且数据已经被更新

同一个用户添加相同商品时, 应该是更新数量, 而不是重复记录:

第一次插入, 1 row affected 说明表中没有冲突数据:

mysql> insert into cart values (1, 101, 1) on duplicate key update quantity = quantity + 1;
Query OK, 1 row affected (0.01 sec)

在这里插入图片描述

第二次插入, 2 rows affected 说明表中有冲突数据, 数据被更新:

mysql> insert into cart values (1, 101, 1) on duplicate key update quantity = quantity + 1;
Query OK, 2 rows affected (0.00 sec)

在这里插入图片描述

使用select row_count()可以查看被影响的行数:
在这里插入图片描述

2. replace

REPLACE INTO table_name (col1, col2, ...)
VALUES (val1, val2, ...);

1 row affected: 表中没有冲突数据，数据被插入
2 row affected: 表中有冲突数据，删除后重新插入. MySQL会先 delete 原有行, 后 insert 新行.

由于replace会进行delete和insert操作, 所以可能会有一些副作用:

auto_increment的字段会出现跳号的情况.
ON DELETE 约束会被触发

举个例子:

create table temp_stock_cache(-> product_id int primary key auto_increment,-> product_name varchar(100) unique, -> stock int-> );

现在插入几条数据, 都是 1 row affected, 说明表中没有冲突数据，数据被插入:

replace into temp_stock_cache (product_name, stock) values ('电脑', 10);
Query OK, 1 row affected (0.00 sec)
replace into temp_stock_cache (product_name, stock) values ('手机', 20);
Query OK, 1 row affected (0.01 sec)
replace into temp_stock_cache (product_name, stock) values ('平板', 30);
Query OK, 1 row affected (0.01 sec)

再插入一条数据, 此时 2 row affected, 此时product_name唯一键冲突, 需要被替换:

replace into temp_stock_cache (product_name, stock) values ('手机', 15);
Query OK, 2 rows affected (0.00 sec)

可以看到第二行被删除, 并且新增了一行, 但是会跳号

在这里插入图片描述

所以如果不在乎是否丢失原来的主键和记录, 也就是旧数据无效, 那就可以使用replace进行覆盖式更新.

插入查询结果(insert + select)

这里我们的最终目的是将一个带有重复数据的 duplicate_table 表去重, 我们的整体思路是通过 tmp文件 + 重命名的方式, 保证原子性地替换文件. insert+select是用在构建 tmp文件(此处名为no_duplicate_table) 的.

首先创建一个带有重复数据的表, 并插入一些重复数据:

create table duplicate_table (id int, name varchar(20));
insert into duplicate_table values (100, 'aaa'),
-> (100, 'aaa'),
-> (200, 'bbb'),
-> (200, 'bbb'),
-> (200, 'bbb'),
-> (300, 'ccc');

在这里插入图片描述
2. 然后创建一个和 duplicate_table 结构一模一样的新表 no_duplicate_table,

create table no_duplicate_table like duplicate_table;

关键是这一步: 将 duplicate_table 的去重数据插入到 no_duplicate_table
可以利用 insert ... select ... 将 select 的查询结果插入到 no_duplicate_table:

insert into no_duplicate_table select distinct * from duplicate_table;

最后对新表和旧表进行重命名即可 (主要是新表):

rename table duplicate_table to old_duplicate_table, 
no_duplicate_table to duplicate_table;

在 Linux 上传或写入大文件时, 为了保证原子性和一致性, 防止系统崩溃 or 断电 or 磁盘满等原因, 导致目标文件"一半新, 一半旧". 也能保证文件的使用者不会读到正在被修改的数据, 读者只能看到两态: 原文件和新文件.
方法是: 通常会先将数据写入一个临时文件, 再通过重命名（mv）来“原子替换”目标文件, 因为 mv 在同一个文件系统内不会复制数据, 而是直接修改 inode 的映射, 这个操作是原子的.

表的查询

查询操作的语法:

SELECT
[DISTINCT] {* | {column [, column] ...}
[FROM table_name]
[WHERE ...]
[ORDER BY column [ASC | DESC], ...]
LIMIT ...

先以这个表为例子:

-- 创建表结构
CREATE TABLE exam_result (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL COMMENT '同学姓名',
chinese float DEFAULT 0.0 COMMENT '语文成绩',
math float DEFAULT 0.0 COMMENT '数学成绩',
english float DEFAULT 0.0 COMMENT '英语成绩'
);
-- 插入测试数据
INSERT INTO exam_result (name, chinese, math, english) VALUES
('唐三藏', 67, 98, 56),
('孙悟空', 87, 78, 77),
('猪悟能', 88, 98, 90),
('曹孟德', 82, 84, 67),
('刘玄德', 55, 85, 45),
('孙权', 70, 73, 78),
('宋公明', 75, 65, 30);

1. SELECT 列

全列查询

select * from table_name;

这样所有的列属性全部显示出来了:
在这里插入图片描述

通常情况下不建议使用 * 进行全列查询
– 1. 查询的列越多, 意味着需要传输的数据量越大, 而一个数据库中的数据量往往很大;
– 2. 可能会影响到索引的使用.

指定列查询

指定列查询更为常用.

假如我只想查询某些特定的列属性, 比如我只想知道英语成绩:

select id, name, english from exam_result;

在这里插入图片描述

查询字段为表达式

a. 表达式不包含字段, 只是常数 or 函数:

表达式为常数, 则结果中所有的列值都为这个常数:

SELECT id, name, 10 FROM exam_result;
+----+--------+----+
| id | name   | 10 |
+----+--------+----+
| 1  | 唐三藏 | 10 |
| 2  | 孙悟空 | 10 |
| 3  | 猪悟能 | 10 |
| 4  | 曹孟德 | 10 |
| 5  | 刘玄德 | 10 |
| 6  | 孙权   | 10 |
| 7  | 宋公明 | 10 |
+----+--------+----+

表达式为 mysql 的函数, 比如常用的 database(), 或者当前时间 now()

select database();
+------------+
| database() |
+------------+
| test1      |
+------------+
select now();
+---------------------+
| now()               |
+---------------------+
| 2025-05-14 22:07:49 |
+---------------------+

b.表达式包含 1个 or 多个字段:

SELECT id, name, english + 10 FROM exam_result;
+----+--------+--------------+
| id | name   | english + 10 |
+----+--------+--------------+
| 1  | 唐三藏 |  66.0        |
| 2  | 孙悟空 |  87.0        |
| 3  | 猪悟能 | 100.0        |
| 4  | 曹孟德 |  77.0        |
| 5  | 刘玄德 |  55.0        |
| 6  | 孙权   |  88.0        |
| 7  | 宋公明 |  40.0        |
+----+--------+--------------+
SELECT id, name, english + chinese + math FROM exam_result;
+----+--------+--------------------------+
| id | name   | english + chinese + math |
+----+--------+--------------------------+
| 1  | 唐三藏 | 221.0                    |
| 2  | 孙悟空 | 242.0                    |
| 3  | 猪悟能 | 276.0                    |
| 4  | 曹孟德 | 233.0                    |
| 5  | 刘玄德 | 185.0                    |
| 6  | 孙权   | 221.0                    |
| 7  | 宋公明 | 170.0                    |
+----+--------+--------------------------+

查询结果指定别名 (as)

语法: 在 select 后指定的字段后添加 as 别名 即可, 其中as可以省略:

SELECT column [AS] alias_name [...] FROM table_name;

举个例子, 把表达式 chinese + math + english 取别名为 总分

 SELECT id, name, chinese + math + english 总分 FROM exam_result;
+----+--------+-------+
| id | name   | 总分  |
+----+--------+-------+
| 1  | 唐三藏 | 221.0 |
| 2  | 孙悟空 | 242.0 |
| 3  | 猪悟能 | 276.0 |
| 4  | 曹孟德 | 233.0 |
| 5  | 刘玄德 | 185.0 |
| 6  | 孙权   | 221.0 |
| 7  | 宋公明 | 170.0 |
+----+--------+-------+

结果去重 distinct

要对查询的结果去重, 只需要在 select 后加一个 distinct即可:

SELECT DISTINCT math FROM exam_result;
+------+
| math |
+------+
| 98.0 |
| 78.0 |
| 84.0 |
| 85.0 |
| 73.0 |
| 65.0 |
+------+

2. where 条件

一. 比较运算符:

运算符	说明
>, >=, <, <=	大于，大于等于，小于，小于等于
=	等于, 注意 NULL 不安全, 例如 NULL = NULL 的结果是 NULL
!=, <>	不等于, 注意NULL 不安全, NULL != NULL 的结果是 NULL
<=>	等于, NULL 安全, 例如 NULL <=> NULL 的结果是 TRUE(1)
IS NULL	是 NULL
IS NOT NULL	不是 NULL
BETWEEN a0 AND a1	范围匹配，[a0, a1]，如果 a0 <= value <= a1，返回 TRUE(1)
IN (option, …)	集合匹配, 如果是 option 中的任意一个, 返回 TRUE(1)
LIKE	模糊匹配。% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符

关于NULL的判断

where 中, 用＝和 !＝ 判断NULL 都是 NULL 不安全, 所以判断是否为 NULL 一般不用<＝>, 改用 IS NULL 和 IS NOT NULL.

// = 和 !=、<> 两侧只要涉及到NULL, 结果就是NULL.
SELECT NULL = NULL, NULL = 1, NULL != 0;
+-------------+----------+----------+
| NULL = NULL | NULL = 1 | NULL != 0 |
+-------------+----------+----------+
| <null>      | <null>   | <null>   |
+-------------+----------+----------+
// <=> 可以进行 NULL 的比较.
SELECT NULL <=> NULL, NULL <=> 1, NULL <=> 0;
+---------------+------------+------------+
| NULL <=> NULL | NULL <=> 1 | NULL <=> 0 |
+---------------+------------+------------+
| 1             | 0          | 0          |
+---------------+------------+------------+

范围匹配

范围查找如果是左闭右闭区间, 可以用 between and 去替换 >= and <=. 比如:

//下面这两句的查询效果是一样的
select * from exam_result where math>=60 and math<=80;
select * from exam_result where math between 60 and 80;

in 的使用举例

数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

 select * from exam_result where math in (58,59,98,99);
+----+--------+---------+------+---------+
| id | name   | chinese | math | english |
+----+--------+---------+------+---------+
| 1  | 唐三藏 | 67.0    | 98.0 | 56.0    |
| 3  | 猪悟能 | 88.0    | 98.0 | 90.0    |
+----+--------+---------+------+---------+

LIKE 模糊匹配

模糊匹配 like 有两种特殊的占位符:

_ : 严格匹配1个字符
%: 匹配[0, n]个字符

举例: 姓孙的同学及孙某同学

//查找姓孙的同学, 所以名字长度不限制, 用% 
select * from exam_result where name like '孙%';
+----+--------+---------+------+---------+
| id | name   | chinese | math | english |
+----+--------+---------+------+---------+
| 2  | 孙悟空 | 87.0    | 78.0 | 77.0    |
| 6  | 孙权   | 70.0    | 73.0 | 78.0    |
+----+--------+---------+------+---------+
//孙某同学, 严格要求名字总长度为 2
select * from exam_result where name like '孙_';
+----+------+---------+------+---------+
| id | name | chinese | math | english |
+----+------+---------+------+---------+
| 6  | 孙权 | 70.0    | 73.0 | 78.0    |
+----+------+---------+------+---------+

二. 逻辑运算符:

运算符	说明
AND	多个条件必须都为 TRUE(1), 结果才是 TRUE(1)
OR	任意一个条件为 TRUE(1), 结果为 TRUE(1)
NOT	条件为 TRUE(1), 结果为 FALSE(0)

没什么可说的, 直接看例子.

语文成绩 > 英语成绩并且不姓孙的同学

select * from exam_result 
where chinese > english and name not like '孙%'+----+--------+---------+------+---------+
| id | name   | chinese | math | english |
+----+--------+---------+------+---------+
| 1  | 唐三藏 | 67.0    | 98.0 | 56.0    |
| 4  | 曹孟德 | 82.0    | 84.0 | 67.0    |
| 5  | 刘玄德 | 55.0    | 85.0 | 45.0    |
| 7  | 宋公明 | 75.0    | 65.0 | 30.0    |
+----+--------+---------+------+---------+

孙某同学, 否则要求总成绩 > 200 并且语文成绩 < 数学成绩并且英语成绩 > 80

select id, name, chinese+math+english as total 
from exam_result 
where name like '孙_' or chinese+math+english > 200 and chinese < math and english > 80;
+----+--------+-------+
| id | name   | total |
+----+--------+-------+
| 3  | 猪悟能 | 276.0 |
| 6  | 孙权   | 221.0 |
+----+--------+-------+

补充: where两边可以都是字段名, 但是它不能使用别名去进行比较.
因为 select 的执行顺序是 1. from 2. where 3. select, 所以别名不能用于where中, 也不能在where里起别名.

比如这里用别名去进行比较:
在这里插入图片描述

可以理解为别名是属于"显示"的范畴, 只是最后数据拿到之后改个名而已, 注意只能在 select 中起别名.

3. order by 排序

语法:

-- ASC 为升序（从小到大）
-- DESC 为降序（从大到小）
-- 默认为 ASC
SELECT ... FROM table_name [WHERE ...]
ORDER BY column [ASC|DESC], [...];

注意: 没有 ORDER BY 子句的查询, 返回的顺序是未定义的, 永远不要依赖这个顺序.

查询同学各门成绩，依次按数学降序，英语升序，语文升序的方式显示

-- 多字段排序，排序优先级随书写顺序
SELECT name, math, english, chinese FROM exam_result
ORDER BY math DESC, english, chinese;
+--------+------+---------+---------+
| name   | math | english | chinese |
+--------+------+---------+---------+
| 唐三藏 | 98.0 | 56.0    | 67.0    |
| 猪悟能 | 98.0 | 90.0    | 88.0    |
| 刘玄德 | 85.0 | 45.0    | 55.0    |
| 曹孟德 | 84.0 | 67.0    | 82.0    |
| 孙悟空 | 78.0 | 77.0    | 87.0    |
| 孙权   | 73.0 | 78.0    | 70.0    |
| 宋公明 | 65.0 | 30.0    | 75.0    |
+--------+------+---------+---------+

同学及 qq 号, 按 qq 号排序显示

-- NULL 视为比任何值都小, 升序出现在最上面, 降序出现在最下面
//升序
select * from students order by qq;
+----+------+--------+
| id | name | qq     |
+----+------+--------+
| 1  | 张三 | <null> |
| 4  | 田七 | <null> |
| 3  | 王五 | 111111 |
| 2  | 李四 | 123456 |
+----+------+--------+
//降序
select * from students order by qq desc;
+----+------+--------+
| id | name | qq     |
+----+------+--------+
| 2  | 李四 | 123456 |
| 3  | 王五 | 111111 |
| 1  | 张三 | <null> |
| 4  | 田七 | <null> |
+----+------+--------+

4. LIMIT 筛选分页结果

limit 的用法分为两种:

LIMIT n

SELECT ... LIMIT n; --从 0 开始, 连续读出 n 条数据

在这里插入图片描述

LIMIT s, n 或 LIMIT n OFFSET s

//含义都是从下标 s 开始, 连续读出 n 条结果. 其中 s 从 0 开始SELECT ... LIMIT s, n;
--或者
SELECT ... LIMIT n OFFSET s; --含义更明确一些

在这里插入图片描述

建议: 对未知表进行查询时, 最好加一条 LIMIT 1, 避免因为表中数据过大, 查询全表数据导致数据库卡死

例子: 比如我想查询总分大于200分的学生里的最高分:

select id, name, chinese+math+english as total from exam_result 
where chinese+math+english > 200 
order by total desc 
limit 1;

limit 还可以实现分页: 比如按 id 进行分页, 每页 3 条记录, 分别显示第 1、2、3 页:
在这里插入图片描述

表的更新

update

语法:

UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] 
[ORDER BY ...] 
[LIMIT ...]

update 是对查询到的结果进行列值更新, 也就是在查询的基础上多了一步修改.

注意: 由于 update 会对表进行更新, 所以where的限制很重要, 否则可能会对一些意外的行进行修改. 所以更新全表的语句慎用.

因此 update 语句一般都要添加 where 或 limit 限制:

修改单列属性
也可以一次更新多个列属性:
用 order by + limit 也可以达到筛选的目的

比如: 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分:

update exam_result set math = math + 30 
order by chinese+math+english asc 
limit 3;Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

这样做看上去有些奇怪, 但是也可以理解, 因为update其实是先进行了一次select操作, 因此可以理解为对select的结果进行更新.

验证一下结果:

update 之前:
在这里插入图片描述

update之后, 注意由于math已经更改, 总成绩发生变化, 所以要修改查询的限制条件:
在这里插入图片描述

不过普通一般也没有权限直接对数据库进行增删改查操作, 也不会直接在命令行去操作.

表的删除

delete

语法:

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

delete的主要功能概括来说是: 删除表中指定条件的数据行
所以 delete 一般都要搭配 where 或 order by, limit 子句去使用, 如果直接 delete from table_name 是全表删除, 要谨慎使用, 且 delete 全表删除和 truncate 有一些区别, 下面再说.

使用很简单, 举 3 个例子:

用 where 去删除特定行

delete from exam_result where name='孙悟空';
Query OK, 1 row affected (0.00 sec)SELECT * FROM exam_result WHERE name = '孙悟空';
Empty set (0.00 sec)

order by + limit

删除总成绩最高的同学的数据:

delete from exam_result 
order by chinese+math+english asc 
limit 1;Query OK, 1 row affected (0.00 sec)

delete from 全表删除

全表删除的效果就是清空表的所有行:
在这里插入图片描述
注意看这里全表删除前后 auto_increment 的值没有发生变化, 而 truncate 则会重置:

truncate

truncate 只用来删除整张表的所有数据, 所以它的语法很简单:

TRUNCATE [TABLE] table_name

主要来看它和 delete from 的区别:

truncate 只能对整表操作, 不能像 DELETE 一样针对部分数据操作.
truncate 会重置 AUTO_INCREMENT 项, 所以可以逻辑上理解为 drop 旧表+ create 新表.
事务控制也有区别, 因为实际上 truncate 本质上不是DML 语句, 而是 DDL语句, 它不对数据操作, 因此 truncate 是隐式提交事务, 不能回滚;
而 DELETE 是 DML 操作, 属于事务的一部分, 可以在需要时进行回滚.

本质是因为 delete 会为每一行被删除的数据生成 Undo 日志, 所以可以被回滚

效率上, 也正是由于 TRUNCATE 是直接释放整个数据页, 没有记录每行的删除日志, 因此在性能上远优于逐行处理并记录日志的 DELETE.

总结: delete from 是逻辑删除, 记录每行的变更, 适用于事务处理, 可以回滚; truncate 是物理删除, 删除整表的数据, 不可回滚.

但是 delete from 和 truncate 都需要谨慎使用.

聚合函数

聚合函数的使用存在一定限制. 通常, 聚合函数( COUNT()、SUM()、AVG()、MAX()、MIN() 等) 在没有分组的情况下不能与 (逗号隔开的多个列）或 .(某些表达式语法) 随意混用.

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

count

统计 exam_result 表有多少行数据:

--两种方式结果都一样
//使用 * 做统计, 结果不受 NULL 的影响
select count(*) from exam_result;
//使用 表达式 做统计, 结果受 NULL 影响
select count(1) from exam_result; --1可以是任何常数

在这里插入图片描述

统计表中有多少同学英语不及格:

select count(*) from exam_result where english < 60;

在这里插入图片描述

统计 exam_result 表中有多少不重复的数学成绩:

select count(distinct math) from exam_result;

在这里插入图片描述
2. sum

统计所有同学的英语总成绩:

select sum(english) from exam_result;

在这里插入图片描述
结合count, 可以统计班里同学的英语平均分:

select sum(english)/count(*) from exam_result;

在这里插入图片描述
3. avg
与其 sum()/count() 统计平均分, 不如直接使用 avg 函数:

select avg(english) from exam_result;

在这里插入图片描述

max 和 min

统计班里同学总分的最高分:

SELECT max(chinese+english+math) from exam_result;

在这里插入图片描述

统计数学及格的同学里的最低分:

select min(math) from exam_result where math > 60;

在这里插入图片描述

如果我们想知道这个分数的同学的更多信息(比如name) 呢 ?不能想当然的单纯添加一个name:

// 错误写法
select name, min(math) from exam_result where math > 60;
(1140, "In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'test1.exam_result.name'; this is incompatible with sql_mode=only_full_group_by")

正确写法必须借助其他手段:

比如:

常规的方法使用 ORDER BY + LIMIT 1:

select name, math from exam_result where math > 60 order by math asc limit 1;

在这里插入图片描述
2. 使用子查询:

select name, math from exam_result 
where math = 
(select min(math) from exam_result where math > 60);

在这里插入图片描述

对于大多数应用场景, 聚合函数主要用于对整张表或查询结果进行简单的整体统计, 也就是像上面一样直接使用聚合函数. 在剩下的场景中, 聚合函数则通常结合 GROUP BY 子句使用, 先对数据按照某个维度进行分组, 再对每个分组分别进行统计分析.

分组 group by

在select中使用 group by 子句可以以指定列为分组依据进行分组查询

语法:

select column1, column2, .. from table group by column;

分组的目的是为了: 在分组之后, 更好的进行聚合统计.

因此我们使用 group by 的时候, 重要的是将 group by 本身的功能和我们的需求对应.

举例: 事先创建一个雇员信息表(来自oracle 9i的经典测试表)

现在有 EMP员工表, DEPT部门表, SALGRADE工资等级表

desc dept;
+--------+--------------------------+------+-----+---------+-------+
| Field  | Type                     | Null | Key | Default | Extra |
+--------+--------------------------+------+-----+---------+-------+
| deptno | int(2) unsigned zerofill | NO   |     | <null>  |       |
| dname  | varchar(14)              | YES  |     | <null>  |       |
| loc    | varchar(13)              | YES  |     | <null>  |       |
+--------+--------------------------+------+-----+---------+-------+
desc emp;
+----------+--------------------------+------+-----+---------+-------+
| Field    | Type                     | Null | Key | Default | Extra |
+----------+--------------------------+------+-----+---------+-------+
| empno    | int(6) unsigned zerofill | NO   |     | <null>  |       |
| ename    | varchar(10)              | YES  |     | <null>  |       |
| job      | varchar(9)               | YES  |     | <null>  |       |
| mgr      | int(4) unsigned zerofill | YES  |     | <null>  |       |
| hiredate | datetime                 | YES  |     | <null>  |       |
| sal      | decimal(7,2)             | YES  |     | <null>  |       |
| comm     | decimal(7,2)             | YES  |     | <null>  |       |
| deptno   | int(2) unsigned zerofill | YES  |     | <null>  |       |
+----------+--------------------------+------+-----+---------+-------+
desc salgrade;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| grade | int  | YES  |     | <null>  |       |
| losal | int  | YES  |     | <null>  |       |
| hisal | int  | YES  |     | <null>  |       |
+-------+------+------+-----+---------+-------+

显示每个部门的平均工资和最高工资.

由于我们的需求中出现了"每个部门", 并且还要统计 avg 和 max 工资, 因此发现我们的需求和 group by 的功能相符, 这里可以用 group by deptno 将表按照部门划分, 并进行聚合统计, 最终显示的行数为分组的组数.

select deptno, avg(sal), max(sal) from emp group by deptno;
+--------+-------------+----------+
| deptno | avg(sal)    | max(sal) |
+--------+-------------+----------+
| 20     | 2175.000000 | 3000.00  |
| 30     | 1566.666667 | 2850.00  |
| 10     | 2916.666667 | 5000.00  |
+--------+-------------+----------+

显示每个部门的每种岗位的平均工资和最低工资.

可以发现我们的需求中出现了 “每个部门” 的 “每种岗位”, 所以这涉及到多次分组, 假设有 n 个部门, m 种岗位, 最终显示的岗位最大值为 n×m, 最终以实际数据为准.

select deptno, job, avg(sal),min(sal) from emp 
group by deptno, job 
order by deptno;
+--------+-----------+-------------+----------+
| deptno | job       | avg(sal)    | min(sal) |
+--------+-----------+-------------+----------+
| 10     | CLERK     | 1300.000000 | 1300.00  |
| 10     | MANAGER   | 2450.000000 | 2450.00  |
| 10     | PRESIDENT | 5000.000000 | 5000.00  |
| 20     | ANALYST   | 3000.000000 | 3000.00  |
| 20     | CLERK     | 950.000000  | 800.00   |
| 20     | MANAGER   | 2975.000000 | 2975.00  |
| 30     | CLERK     | 950.000000  | 950.00   |
| 30     | MANAGER   | 2850.000000 | 2850.00  |
| 30     | SALESMAN  | 1400.000000 | 1250.00  |
+--------+-----------+-------------+----------+

分组, 实际上是把整个表当成一个组, 然后按照条件拆成了多个组, 可以从逻辑上理解为将一个大表拆分为了多个子表, 从而能够对各个子表分别进行聚合统计.
所以能出现在 select 子句后字段的一般都是group by 后出现的充当条件的字段 和聚合函数.

having

having 是对聚合后的统计数据进行条件筛选.

显示平均工资低于2000的部门和它的平均工资

select deptno, avg(sal) as avg_sal 
from emp 
group by deptno 
having avg_sal < 2000;

having vs where 区别理解?
having 和 where 都是进行条件筛选, 但是它们是完全不同的条件筛选.

语义是不同的, where 是对一整个表的具体的任意列进行条件筛选, 而 having 是对分组聚合之后的结果进行条件筛选.
筛选的阶段是不同的,

最后可以总结一下 select 子句中各个部分的执行顺序:
SQL查询中各个关键字的执行先后顺序 from > on > join > where > group by > with > having > select > distinct > order by > limit

查看全文

http://www.xdnf.cn/news/6566.html

基于javaweb的JSP+Servlet家政服务系统设计与实现(源码+文档+部署讲解）

c++,windows,多线程编程详细介绍

机器视觉对位手机中框点胶的应用

Oracle统计信息收集时的锁持有阶段

全面指南：Xinference大模型推理框架的部署与使用

平替BioLegend品牌-FITC 标记 CD11c 抗体 [N418]，精准识别小鼠树突细胞

[Harmony]大文件持久化

Spring-messaging-MessageHandler接口实现类ServiceActivatingHandler

Pangle出海指南：如何实现ROI最大化？

Spring Boot多数据源配置的陷阱与终极解决方案

在 Vue 中使用 Three.js 渲染 GLB 格式模型

antd 主题色定制

C++（12）：using声明

智能包装机控制终端技术方案：基于EFISH-SCB-RK3588/SAIL-RK3588的全场景国产化替代解析

Console Importer浏览器插件的编译及制作成.crx浏览器插件的步骤

electron进程通信

128.在 Vue 3 中使用 OpenLayers 实现绘制矩形截图并保存地图区域

关于xammp数据库打开不了，但是日志没错误的问题解决以及其数据库的备份

如何利用 Python 爬虫按关键字搜索京东商品：实战指南

Docker 安装 MySQL8

OpenCV 图像直方图：从原理剖析到实战应用

linux系统服务

Qt信号槽机制与UI设计完全指南：从基础原理到实战应用

【笔试训练】简单写词|dd爱框框|除2！

Cursor无法使用C/C++调试的解决办法

Ubuntu使用Docker搭建SonarQube企业版（含破解方法）

Hugging Face 中 LeRobot 使用的入门指南

LangChain4j +DeepSeek大模型应用开发——10 检索增强生成 RAG

Ubuntu下配置VScode出现#include错误请更新includePath的解决方法

Java类一文分解:JavaBean,工具类,测试类的深度剖析

表的增删改查

表的增加

全列插入和指定列插入

全列插入:

指定列插入

单行插入和多行插入

单行插入

多行插入

插入否则更新 和 替换

1. ON DUPLICATE KEY UPDATE

2. replace

插入查询结果(insert + select)

表的查询

1. SELECT 列

全列查询

指定列查询

查询字段为表达式

查询结果指定别名 (as)

结果去重 distinct

2. where 条件

3. order by 排序

4. LIMIT 筛选分页结果

表的更新

update

表的删除

delete

truncate

聚合函数

分组 group by

having

相关文章：

插入否则更新和替换