MYSQL之基本查询(CURD)

表的增删改查

表的增加

语法:

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. 第一次插入, 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)

在这里插入图片描述

  1. 第二次插入, 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操作, 所以可能会有一些副作用:

  1. auto_increment的字段会出现跳号的情况.
  2. 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) 的.

  1. 首先创建一个带有重复数据的表, 并插入一些重复数据:
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;
  1. 关键是这一步: 将 duplicate_table 的去重数据插入到 no_duplicate_table
    可以利用 insert ... select ... 将 select 的查询结果插入到 no_duplicate_table:
insert into no_duplicate_table select distinct * from duplicate_table;
  1. 最后对新表和旧表进行重命名即可 (主要是新表):
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 函数:

  1. 表达式为常数, 则结果中所有的列值都为这个常数:
SELECT id, name, 10 FROM exam_result;
+----+--------+----+
| id | name   | 10 |
+----+--------+----+
| 1  | 唐三藏 | 10 |
| 2  | 孙悟空 | 10 |
| 3  | 猪悟能 | 10 |
| 4  | 曹孟德 | 10 |
| 5  | 刘玄德 | 10 |
| 6  | 孙权   | 10 |
| 7  | 宋公明 | 10 |
+----+--------+----+
  1. 表达式为 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 个）任意字符；_ 表示任意一个字符
  1. 关于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          |
+---------------+------------+------------+
  1. 范围匹配

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

//下面这两句的查询效果是一样的
select * from exam_result where math>=60 and math<=80;
select * from exam_result where math between 60 and 80;
  1. 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    |
+----+--------+---------+------+---------+
  1. 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)

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

  1. 语文成绩 > 英语成绩 并且不姓孙的同学
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    |
+----+--------+---------+------+---------+
  1. 孙某同学, 否则要求总成绩 > 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 子句的查询, 返回的顺序是未定义的, 永远不要依赖这个顺序.

  1. 查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示
-- 多字段排序，排序优先级随书写顺序
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    |
+--------+------+---------+---------+
  1. 同学及 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 的用法分为两种:

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

在这里插入图片描述

  1. 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 限制:

  1. 修改单列属性
    在这里插入图片描述
  2. 也可以一次更新多个列属性:
    在这里插入图片描述
  3. 用 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 个例子:

  1. 用 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)
  1. order by + limit

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

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

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

truncate

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

TRUNCATE [TABLE] table_name

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

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

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

  1. 效率上, 也正是由于 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)返回查询到的数据的 最小值, 不是数字没有意义
  1. 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;

在这里插入图片描述

  1. 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")

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

比如:

  1. 常规的方法 使用 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>  |       |
+-------+------+------+-----+---------+-------+
  1. 显示每个部门的平均工资和最高工资.

由于我们的需求中出现了"每个部门", 并且还要统计 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  |
+--------+-------------+----------+
  1. 显示每个部门的每种岗位的平均工资和最低工资.

可以发现我们的需求中出现了 “每个部门” 的 “每种岗位”, 所以这涉及到多次分组, 假设有 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 都是进行条件筛选, 但是它们是完全不同的条件筛选.

  1. 语义是不同的, where 是对一整个表的具体的任意列进行条件筛选, 而 having 是对分组聚合之后的结果进行条件筛选.
  2. 筛选的阶段是不同的,
    在这里插入图片描述

综合练习

  1. 查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J
//正常写法
select * from emp 
where (sal > 500 or job='MANAGER') and ename like 'J%';
//用字符串函数也可以
select * from emp
where (sal > 500 or job='MANAGER') and substring(ename, 1, 1) = 'J';--结果都正确:
+-------+-------+---------+------+---------------------+---------+--------+--------+
| empno | ename | job     | mgr  | hiredate            | sal     | comm   | deptno |
+-------+-------+---------+------+---------------------+---------+--------+--------+
| 7566  | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | <null> | 20     |
| 7900  | JAMES | CLERK   | 7698 | 1981-12-03 00:00:00 | 950.00  | <null> | 30     |
+-------+-------+---------+------+---------------------+---------+--------+--------+
  1. 按照部门号升序而雇员的工资降序排序
select * from emp order by deptno, sal desc;
  1. 使用年薪进行降序排序

这里值得说一下, 假设这里年薪的计算为: 12*月薪 + 奖金, 但是SQL里年薪的表达式不能直接这样计算, 因为奖金 comm 可能为NULL, 导致表达式结果为NULL, 这里需要使用ifnull函数:

 select empno, ename , 12*sal+ifnull(comm,0) as '年薪' from emp order by '年薪' desc;
+-------+--------+----------+
| empno | ename  | 年薪     |
+-------+--------+----------+
| 7369  | SMITH  | 9600.00  |
| 7499  | ALLEN  | 19500.00 |
| 7521  | WARD   | 15500.00 |
| 7566  | JONES  | 35700.00 |
| 7654  | MARTIN | 16400.00 |
| 7698  | BLAKE  | 34200.00 |
| 7782  | CLARK  | 29400.00 |
| 7788  | SCOTT  | 36000.00 |
| 7839  | KING   | 60000.00 |
| 7844  | TURNER | 18000.00 |
| 7876  | ADAMS  | 13200.00 |
| 7900  | JAMES  | 11400.00 |
| 7902  | FORD   | 36000.00 |
| 7934  | MILLER | 15600.00 |
+-------+--------+----------+
  1. 显示工资最高的员工的名字和工作岗位, 这里需要用到子查询去得到max(sal), 然作为where的一部分:
select ename, job from emp where sal = (select max(sal) from emp);
+-------+-----------+
| ename | job       |
+-------+-----------+
| KING  | PRESIDENT |
+-------+-----------+
  1. 显示每个部门的平均工资和最高工资, 注意这里可以用 format 函数去限制小数点
select deptno, format(avg(sal), 2), max(sal) from emp group by deptno;
+--------+---------------------+----------+
| deptno | format(avg(sal), 2) | max(sal) |
+--------+---------------------+----------+
| 20     | 2,175.00            | 3000.00  |
| 30     | 1,566.67            | 2850.00  |
| 10     | 2,916.67            | 5000.00  |
+--------+---------------------+----------+

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

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.pswp.cn/pingmian/81455.shtml

如若内容造成侵权/违法违规/事实不符，请联系多彩编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

STM32简易计算机设计

STM32简易计算机设计

运用 A0上拉按钮和 A1 A2下拉按钮设计按键功能 加上独特的算法检测设计&#xff0c;先计算&#xff08;&#xff09;内在计算乘除在计算加减的值在计算乘除优先级最后计算加减优先级 #include "stm32f10x.h" #include <stdio.h> #include <stdlib.h>…
阅读更多...
sparkSQL读入csv文件写入mysql

sparkSQL读入csv文件写入mysql

思路 示例 &#xff08;年龄>18改成>20) mysql的字符集问题 把user改成person “让字符集认识中文”
阅读更多...
计算机视觉与深度学习 ｜ Python 实现SO-CNN-BiLSTM多输入单输出回归预测（完整源码和源码详解）

计算机视觉与深度学习 ｜ Python 实现SO-CNN-BiLSTM多输入单输出回归预测（完整源码和源码详解）

SO-CNN-BiLSTM **一、代码实现****1. 环境准备****2. 数据生成（示例数据）****3. 数据预处理****4. 模型构建****5. 模型训练****6. 预测与评估****二、代码详解****1. 数据生成****2. 数据预处理****3. 模型架构****4. 训练配置****5. 结果可视化****三、关键参数说明****四、…
阅读更多...
Windows软件插件-音视频捕获

Windows软件插件-音视频捕获

下载本插件 音视频捕获就是获取电脑外接的话筒&#xff0c;摄像头&#xff0c;或线路输入的音频和视频。 本插件捕获电脑外接的音频和视频。最多可以同时获取4个视频源和4个音频源。插件可以在win32和MFC程序中使用。 使用方法 首先&#xff0c;加载本“捕获”DLL&#xff0c…
阅读更多...
ios打包ipa获取证书和打包创建经验分享

ios打包ipa获取证书和打包创建经验分享

在云打包或本地打包ios应用&#xff0c;打包成ipa格式的app文件的过程中&#xff0c;私钥证书和profile文件是必须的。 其实打包的过程并不难&#xff0c;因为像hbuilderx这些打包工具&#xff0c;只要你输入的是正确的证书&#xff0c;打包就肯定会成功。因此&#xff0c;证书…
阅读更多...
CycleISP: Real Image Restoration via Improved Data Synthesis通过改进数据合成实现真实图像恢复

CycleISP: Real Image Restoration via Improved Data Synthesis通过改进数据合成实现真实图像恢复

摘要 大规模数据集的可用性极大释放了深度卷积神经网络（CNN）的潜力。然而，针对单图像去噪问题，获取真实数据集成本高昂且流程繁琐。因此，图像去噪算法主要基于合成数据开发与评估，这些数据通常通过广泛假设的加性高斯白噪声（AWGN）生成。尽管CNN在合成数据集上表现优异…
阅读更多...
《Python星球日记》 第70天：Seq2Seq 与Transformer Decoder

《Python星球日记》 第70天：Seq2Seq 与Transformer Decoder

名人说：路漫漫其修远兮，吾将上下而求索。—— 屈原《离骚》 创作者：Code_流苏(CSDN)（一个喜欢古诗词和编程的Coder😊） 目录 一、Seq2Seq模型基础1. 什么是Seq2Seq模型？2. Encoder-Decoder架构详解1️⃣编码器(Encoder)2️⃣解码器(Decoder)3. 传统Seq2Seq模型的局限性…
阅读更多...
Android 性能优化入门（二）—— 内存优化

Android 性能优化入门（二）—— 内存优化

1、概述 1.1 Java 对象的生命周期 各状态含义&#xff1a; 创建&#xff1a;分配内存空间并调用构造方法应用&#xff1a;使用中&#xff0c;处于被强引用持有&#xff08;至少一个&#xff09;的状态不可见&#xff1a;不被强引用持有&#xff0c;应用程序已经不再使用该对象…
阅读更多...
GCC 版本与C++ 标准对应关系

GCC 版本与C++ 标准对应关系

GCC 版本 与支持的 C 标准&#xff08;C11、C14、C17、C20、C23&#xff09; 的对应关系 GCC 版本与 C 标准支持对照表 GCC 版本默认 C 标准C11C14C17C20C23GCC 4.8C98✅ (部分支持)❌❌❌❌GCC 4.9C98✅ (完整支持)❌❌❌❌GCC 5.1C98✅✅ (完整支持)❌❌❌GCC 6.1C14✅✅✅ …
阅读更多...
5、事务和limit补充

5、事务和limit补充

一、事务【都是重点】 1、了解 一个事务其实就是一个完整的业务逻辑。 要么同时发生&#xff0c;要么同时结束。 是一个最小的工作单元。 不可再分。 看这个视频&#xff0c;黑马的&#xff0c;4分钟多点就能理解到 可以理解成&#xff1a; 开始事务-----如果中间抛出异常…
阅读更多...
一套基于 Bootstrap 和 .NET Blazor 的开源企业级组件库

一套基于 Bootstrap 和 .NET Blazor 的开源企业级组件库

前言 今天大姚给大家分享一套基于 Bootstrap 和 .NET Blazor 的开源企业级组件库&#xff1a;Bootstrap Blazor。 项目介绍 BootstrapBlazor 是一套基于 Bootstrap 和 Blazor 的开源&#xff08;Apache License&#xff09;、企业级组件库&#xff0c;无缝整合了 Bootstrap …
阅读更多...
mac-M系列芯片安装软件报错：***已损坏，无法打开。推出磁盘问题

mac-M系列芯片安装软件报错：***已损坏，无法打开。推出磁盘问题

因为你安装的软件在Intel 或arm芯片的mac上没有签名导致。 首先打开任何来源操作 在系统设置中配置&#xff0c;如下图&#xff1a; 2. 然后打开终端&#xff0c;输入&#xff1a; sudo spctl --master-disable然后输入电脑锁屏密码 打开了任何来源&#xff0c;还遇到已损坏…
阅读更多...
RK3568-鸿蒙5.1与原生固件-扇区对比分析

RK3568-鸿蒙5.1与原生固件-扇区对比分析

编译生成的固件目录地址 ../openharmony/out/rk3568/packages/phone/images鸿蒙OS RK3568固件分析 通过查看提供的信息&#xff0c;分析RK3568开发板固件的各个组件及其用途&#xff1a; 主要固件组件 根据终端输出的文件列表&#xff0c;RK3568固件包含以下关键组件&#x…
阅读更多...
Java正则表达式：从基础到高级应用全解析

Java正则表达式：从基础到高级应用全解析

Java正则表达式应用与知识点详解 一、正则表达式基础概念 正则表达式(Regular Expression)是通过特定语法规则描述字符串模式的工具&#xff0c;常用于&#xff1a; 数据格式验证文本搜索与替换字符串分割模式匹配提取 Java通过java.util.regex包提供支持&#xff0c;核心类…
阅读更多...
进程间通信--信号量【Linux操作系统】

进程间通信--信号量【Linux操作系统】

文章目录 并发编程相关基础概念信号量深刻理解信号量使用共享资源的方式分块使用共享资源的方式会出现的问题举例子理解信号量的第二个特性---预定信号量要成为计数器面临的问题 信号量相关操作接口--POSIX库函数&#xff1a;sem_init库函数&#xff1a;sem_destroy库函数&…
阅读更多...
谢赛宁团队提出 BLIP3-o：融合自回归与扩散模型的统一多模态架构，开创CLIP特征驱动的图像理解与生成新范式

谢赛宁团队提出 BLIP3-o：融合自回归与扩散模型的统一多模态架构，开创CLIP特征驱动的图像理解与生成新范式

BLIP3-o 是一个统一的多模态模型&#xff0c;它将自回归模型的推理和指令遵循优势与扩散模型的生成能力相结合。与之前扩散 VAE 特征或原始像素的研究不同&#xff0c;BLIP3-o 扩散了语义丰富的CLIP 图像特征&#xff0c;从而为图像理解和生成构建了强大而高效的架构。 此外还…
阅读更多...
HarmonyOs开发之——— ArkWeb 实战指南

HarmonyOs开发之——— ArkWeb 实战指南

HarmonyOs开发之——— ArkWeb 实战指南 谢谢关注！！ 前言：上一篇文章主要介绍HarmonyOs开发之———合理使用动画与转场:CSDN 博客链接 一、ArkWeb 组件基础与生命周期管理 1.1 Web 组件核心能力概述 ArkWeb 的Web组件支持加载本地或在线网页，提供完整的生命周期回调体…
阅读更多...
黑马程序员C++2024版笔记 第0章 C++入门

黑马程序员C++2024版笔记 第0章 C++入门

1.C代码的基础结构 以hello_world代码为例&#xff1a; 预处理指令 #include<iostream> using namespace std; 代码前2行是预处理指令&#xff0c;即代码编译前的准备工作。&#xff08;编译是将源代码转化为可执行程序.exe文件的过程&#xff09; 主函数 主函数是…
阅读更多...
日语学习-日语知识点小记-构建基础-JLPT-N4阶段（22）：复习

日语学习-日语知识点小记-构建基础-JLPT-N4阶段（22）：复习

日语学习-日语知识点小记-构建基础-JLPT-N4阶段（22）：复习 1、前言（1）情况说明（2）工程师的信仰2、知识点（1）复习（２）復習3、单词（1）日语（2）日语片假名单词4、对话练习5、单词辨析记录6、总结1、前言 （1）情况说明 自己在今年，在日本留学中，目前在语言学校，…
阅读更多...
Docker配置SRS服务器 ，ffmpeg使用rtmp协议推流+vlc拉流

Docker配置SRS服务器 ，ffmpeg使用rtmp协议推流+vlc拉流

目录 演示视频 前期配置 Docker配置 ffmpeg配置 vlc配置 下载并运行 SRS 服务 推拉流流程实现 演示视频 2025-05-18 21-48-01 前期配置 Docker配置 运行 SRS 建议使用 Docker 配置 Docker 请移步&#xff1a; 一篇就够&#xff01;Windows上Docker Desktop安装 汉化完整指…
阅读更多...
最新文章

Copyright @ 2022~2023