【MySQL】表的增删查改 + 分组查询

已于 2025-04-28 11:28:38 修改
阅读量1.1k 收藏 19
点赞数 24
分类专栏： MySQL 文章标签： mysql windows 数据库
于 2025-04-28 10:36:41 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/2203_76003626/article/details/144367845
版权

MySQL

  • 表的增删查改简称CRUD：Create（新增），Retrieve（查找），Update（修改），Delete（删除）
  • CRUD的操作对象是对表当中的数据，是典型的数据操作语言 (DML)

一. 表的增删查改

1. Create

INSERT [INTO] table_name [(column1 [, column2] ...)] VALUES (value_list1) [, (value_list2)] ...;
  • SQL 中大写的表示关键字，[ ]中代表的是可选项。
  • SQL 中的每个 value_list 都表示插入的一条记录，每个 value_list 都由若干待插入的列值组成。
  • SQL 中的 column 列表，用于指定每个 value_list 中的各个列值应该插入到表中的哪一列。
create table student(
    id int unsigned primary key auto_increment comment '主键',
    name varchar(20) not null comment '姓名',
    sn int unsigned unique key comment '学号',
    qq varchar(32) unique key comment 'QQ'
);
desc student;

在这里插入图片描述

1. 单行数据 + 全列插入

如果插入数据时不指定column列表，表示按照表中默认的列顺序进行全列插入，因此插入的每条记录中的列值需要按表列顺序依次列出，如下：

insert into student values (1, '橙留香', 11, '111');
insert into student values (2, '菠萝吹雪', 22, '222');
insert into student values (3, '陆小果', 33, '333');
select * from student;

在这里插入图片描述

2. 多行数据 + 指定列插入

如果一次向表中插入多条记录，插入的多条记录之间使用逗号隔开，并且插入记录时可以只指定某些列进行插入，如下：

insert into student (name, sn, qq) values ('上官子怡', 44, '444'), ('梨花诗', 55, '555'), ('花如意', 66, '666');
select * from student;

在这里插入图片描述

3. 插入相同数据 + 同步更新

由于 主键 / 唯一键 对应的值已经存在而导致插入失败。

# 主键冲突: 插入失败
insert into student (id, name, sn, qq) values (6, '小果叮', 77, '777');

# 唯一键冲突: 插入失败
insert into student (id, name, sn, qq) values (7, '小果叮', 66, '777');

在这里插入图片描述

可以选择性的进行同步更新操作：

  • 如果表中没有冲突数据，则直接插入数据。
  • 如果表中有冲突数据，则将表中的数据进行更新。
INSERT ... ON DUPLICATE KEY UPDATE column1=value1 [, column2=value2] ...;
  • SQL中大写的表示关键字，[ ]中代表的是可选项。
  • SQL中INSERT之后语法与之前的INSERT语句相同。
  • UPDATE后面的column=value，表示当插入记录出现冲突时需要更新的列值。
insert into student (id, name, sn, qq) values (7, '小果叮', 77, '777');
select * from student;

在这里插入图片描述

主键冲突且唯一键不冲突且插入数据与该数据不相同时：插入操作变为修改操作，插入成功！

insert into student values (7, '贼眉鼠眼', 77, '777')
on duplicate key update name='贼眉鼠眼', sn=77, qq='777';

select * from student;

在这里插入图片描述

主键冲突时：将插入操作变成更新操作，但发现更新过程中与唯一键发生冲突，插入失败！

insert into student values (7, '认贼作父', 66, '777')
on duplicate key update name='认贼作父', sn=66, qq='777';

在这里插入图片描述

主键发生冲突且插入的数据与该数据相同时：不受影响

insert into student values (7, '贼眉鼠眼', 77, '777')
on duplicate key update name='贼眉鼠眼', sn=77, qq='777';

在这里插入图片描述

执行插入更新的 SQL 后，可以通过受影响的数据行数来判断本次数据的插入情况：

  • 0 rows affected：表中有冲突数据，但冲突数据的值和指定更新的值相同。
  • 1 row affected：表中没有冲突数据，数据直接被插入。
  • 2 rows affected：表中有冲突数据，并且数据已经被更新。

3. 替换数据

  • 主键 或者 唯一键 没有冲突，则直接插入。
  • 主键 或者 唯一键 如果冲突，则删除后再插入。

要达到上述效果，只需要在插入数据时将 SQL 语句中的 INSERT 改为 REPLACE 即可，比如：

唯一键没有冲突：直接插入

replace into student values (8, '认贼作父', 88, '888');
select * from student;

在这里插入图片描述

唯一键冲突：删除后再插入

replace into student values (8, '乱臣贼子', 88, '888');
select * from student;

在这里插入图片描述

  • replace 较于 update 操作更为简单，推荐。

2. Retrieve

SELECT
	[DISTINCT] {* | {column [, column] ...}} 
	[FROM table_name] 
	[WHERE ...] 
	[ORDER BY column [ASC | DESC], ...] 
	[LIMIT ...]
  • SQL 中大写的表示关键字，[ ] 中代表的是可选项。
  • { } 中的 | 代表可以选择左侧的语句或右侧的语句。

1. select + from：从表中选择列

create table exam( 
	id int unsigned primary key auto_increment comment '主键', 
	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 (name, Chinese, math, English) values 
    ('喜羊羊', 90, 99, 94),
    ('美羊羊', 95, 86, 98),
    ('懒羊羊', 35, 24, 48),
    ('沸羊羊', 60, 50, 64),
    ('暖羊羊', 95, 86, 90);
    
desc exam;

在这里插入图片描述

全列查询

select * from exam;

在这里插入图片描述

通常情况下不建议使用 * 进行全列查询的原因：

  • 查询的列越多，意味着需要传输的数据量越大，且不方便查看数据。
  • 可能会影响到索引的使用。

指定列查询

select name, math from exam;

在这里插入图片描述

查询字段是表达式

select name, Chinese + math + English from exam;

在这里插入图片描述

为查询字段取别名

select name, Chinese + math + English 总成绩 from exam;
select name 姓名, math 数学成绩, Chinese + math + English 总成绩 from exam;

在这里插入图片描述

2. distinct：行数据去重

查询结果去重

select Chinese from exam;
select distinct Chinese from exam;

在这里插入图片描述

3. where：行数据选择

比较运算符说明
>、>=、<、<=大于、大于等于、小于、小于等于
=等于，NULL 不安全。NULL = NULL 的结果是 NULL
<=>等于，NULL 安全。NULL = NULL 的结果是 true(1)
!=、<>不等于，NULL 不安全
between x and y范围匹配：[x，y]。如果 value 在 [x，y] 区间内，返回 true(1)
in(option1，option2…)如果是 option 中的任意一个，返回 true(1)
is NULL是 NULL
is not NULL不是 NULL
like模糊匹配。% 表示任意多个（包括 0 个）任意字符。_ 表示任意一个字符。
逻辑运算符说明
and多个条件必须都为 TRUE(1)，结果才是 TRUE(1)
or任意一个条件为 TRUE(1)，结果为 TRUE(1)
not条件为 TRUE(1)，结果为 FALSE(0)

查询：英语不及格的同学及英语成绩。

先用 select 查询列，再用 where 查询行。

select name, English from exam where English < 60;

在这里插入图片描述

查询：语文成绩在 [80, 90] 分的同学及语文成绩。

# 方法一
select name, Chinese from exam where Chinese >= 80 and Chinese <= 90;

# 方法二
select name, Chinese from exam where Chinses between 80 and 90;

在这里插入图片描述

查询：数学成绩是 [50，59] 或者 [90，99] 区间内的同学及数学成绩。

select name, math from exam where (math between 50 and 59) or (math between 90 and 99);

在这里插入图片描述

查询：数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩。

# 方法一
select name, math from exam where math = 58 or math = 59 or math = 98 or math = 99;

# 方法二
select name, math from exam where math in(58, 29, 98, 99);

在这里插入图片描述

查询：姓喜的同学 和 喜某同学。

insert into exam (name, Chinese, math, English) values ('喜羊', 90, 99, 94);
insert into exam (name, Chinese, math, English) values ('喜', 90, 99, 94);
select * from exam;

在这里插入图片描述

select name from exam where name like '喜%';
select name from exam where name like '喜_';

在这里插入图片描述

  • %来匹配任意个字符 (包括0个)
  • _来匹配单个字符。

查询：语文成绩高于英语成绩的同学。

select name, Chinese, English from exam where Chinese > English;

在这里插入图片描述

查询：总成绩在 200 分以下的同学。

select name, (Chinese + math + English) 总成绩 from exam where (Chinese + math + English) < 200;

在这里插入图片描述

select name, (Chinese + math + English) 总成绩 from exam where 总成绩 < 200;
select name, 总成绩 from exam where (Chinese + math + English) 总成绩 < 200;

在这里插入图片描述

结论：where 子句不能出现别名。

查询：语文成绩 > 80 并且 不姓喜的同学。

select name, Chinese from exam where Chinese > 80 and name not like '喜%';

在这里插入图片描述

查询：姓懒同学，否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80。

select name, Chinese, math, English, (Chinese + math + English) 总成绩 from exam 
where name like '懒%' or (Chinese + math + English > 200 and Chinese < math and English > 80);

在这里插入图片描述

NULL 的查询

create table testnull(
	name varchar(20),
    id int
);

insert into testnull values ('曹操', 1);
insert into testnull values ('刘备', null);
insert into testnull values ('孙权', 2);
insert into testnull values (null, null);
insert into testnull values ('', 3);

select * from testnull;

在这里插入图片描述

select * from testnull where name is not null;
select * from testnull where name is null;
select * from testnull where name = '';

在这里插入图片描述

NULL 的比较

select null = null, null <=> null, null != null, null <> null;
select null = 0, null = 1;
select null is null, null is not null;

在这里插入图片描述

  • 使用 =、!=、<> 比较 null 是不安全的，结果是 null，可以使用安全的 <==>
  • 也可以使用 is null、is not null 比较 null

4. order by：行数据排序

SELECT ... FROM table_name [WHERE ...] ORDER BY column [ASC | DESC] [, ...];
  • SQL中大写的表示关键字，[ ]中代表的是可选项。
  • ASC和DESC分别代表的是排升序和排降序，默认为ASC。

注意： 如果查询 SQL 中没有 order by 子句，那么返回的顺序是未定义的。

delete from exam where name='喜';
delete from exam where name='喜羊';
select * from exam;

在这里插入图片描述

查询：同学及数学成绩，按数学成绩升序 / 降序显示。

select name, math from exam order by math desc; # 降序
select name, math from exam order by math asc;  # 升序
select name, math from exam order by math;      # 默认升序

在这里插入图片描述

在这里插入图片描述

NULL 参与排序，且比任何值都小。

select name from testnull order by name asc;

在这里插入图片描述

查询：同学各门成绩，依次按 数学降序，英语升序（数学相等时），语文升序（英语相等时）的方式显示。

select name, math, English, Chinese from exam order by math desc, English desc, Chinese asc;

在这里插入图片描述

查询：同学及总分，由高到低。

select name, Chinese + math + English 总成绩 from exam order by 总成绩 asc;

在这里插入图片描述

SQL 语句的执行顺序

在这里插入图片描述

总结：SQL 语句执行的先后顺序，决定是否能识别别名。

查询：姓喜的同学或者姓美的同学数学成绩，结果按数学成绩由高到低显示。

select name, math from exam where name like '喜%' or name like '美%' order by math desc;

在这里插入图片描述

SQL 语句的执行顺序

在这里插入图片描述

5. limit：行数据筛选

# 从第0条记录开始，向后筛选出n条记录
SELECT ... FROM table_name [WHERE ..] [ORDER BY ...] LIMIT n;

# 从第s条记录开始，向后筛选出n条记录
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;

# 从第s条记录开始，向后筛选出n条记录
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;
  • SQL 中大写的表示关键字，[ ]中代表的是可选项。
  • 查询 SQL 中各语句的执行顺序为：from、where、select、order by、limit。
  • limit 子句在筛选记录时，记录的下标从0开始。

从表中筛选出前两行数据

select * from exam limit 2;
select * from exam limit 0, 2;
select * from exam limit 2 offset 0;

在这里插入图片描述

注意：第一行数据的下标为0。

按 id 进行分页，每页 3 条记录，分别显示 第 1、2 页

select * from exam limit 0, 3;
select * from exam limit 3, 3;

# 或者
select * from exam limit 3 offset 0;
select * from exam limit 3 offset 3;

在这里插入图片描述

建议：对未知表进行查询时，最好加一条 limit 3，避免因为表中数据过大，查询全表数据导致数据库卡死。按 id 进行分页，每页 3 条记录，分别显示 第 1、2、页。

SQL 语句的执行顺序

select name, Chinese + math + English as 总成绩 from exam
where Chinese + math + English < 200 order by 总成绩 desc limit 2;

在这里插入图片描述

在这里插入图片描述

只有数据准备好了，才能显示结果，limit 的本质的功能是 “显示”。

3. Update

1. update：行数据更新

UPDATE table_name SET column1=expr1 [, column2=expr2] ... [WHERE ...] [ORDER BY ...] [LIMIT ...];
  • SQL中大写的表示关键字，[ ]中代表的是可选项。
  • SQL中的column=expr，表示将记录中列名为column的值修改为expr
  • 在修改数据之前需要先找到待修改的记录，update语句中的where、order by和limit就是用来定位数据的。

将懒羊羊同学的数学成绩变更为 36 分

select name, math from exam where name = '懒羊羊';  #查看原数据
update exam set math = 36 where name = '懒羊羊';    #更新懒羊羊的数学成绩
select name, math from exam where name = '懒羊羊';  #查看更新后的数据

在这里插入图片描述

注意：update通常需要搭配where使用，否则会将所有的数据进行修改，此行为非常危险，不亚于delete

将沸羊羊同学的数学成绩变更为 60 分，语文成绩变更为 70 分

select name, math, Chinese from exam where name = '沸羊羊';
update exam set math = 60, Chinese = 70 where name = '沸羊羊';
select name, math, Chinese from exam where name = '沸羊羊';

在这里插入图片描述

将总成绩倒数前 2 的两位同学的数学成绩加上 30 分

# 先查看总成绩倒数前 2 的两位同学的数学成绩
select name, math, Chinese + math + English 总成绩 from exam order by 总成绩 asc limit 0, 2;

# 将总成绩倒数前 2 的两位同学的数学成绩加上 30 分
update exam set math = math + 30 order by Chinese + math + English asc limit 2;

# 再查看总成绩倒数前 2 的两位同学的数学成绩
select name, math, Chinese + math + English 总成绩 from exam order by 总成绩 asc limit 0, 2;

# 数据恢复
update exam set math = math - 30 order by Chinese + math + English asc limit 2;

在这里插入图片描述

将所有同学的语文成绩更新为原来的 2 倍

select name, Chinese from exam;
update exam set Chinese = Chinese * 2;
select name, Chinese from exam;

# 数据恢复
update exam set Chinese = Chinese / 2;

在这里插入图片描述

4. Delete

1. delete：行数据删除

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...];
  • SQL中大写的表示关键字，[ ]中代表的是可选项。
  • 在删除数据之前需要先找到待删除的记录，delete语句中的where、order by和limit就是用来定位数据的。

删除喜羊羊同学的考试成绩：

select * from exam where name = '喜羊羊';
delete from exam where name = '喜羊羊';
select * from exam where name = '喜羊羊';

# 恢复数据
insert into exam (name, Chinese, math, English) values ('喜羊羊', 90, 99, 94);

在这里插入图片描述

删除总成绩倒数第一的同学

# 先查看总成绩倒数第一的同学
select name, Chinese + math + English 总成绩 from exam order by 总成绩 asc limit 0, 1;

# 删除总成绩倒数第一的同学
delete from exam order by Chinese + math + English asc limit 1;

# 再查看总成绩倒数第一的同学
select name, Chinese + math + English 总成绩 from exam order by 总成绩 asc limit 0, 1;

# 恢复数据
insert into exam (name, Chinese, math, English) values ('懒羊羊', 35, 36, 48);

在这里插入图片描述

删除整张表中的数据

# 准备测试表
create table test_delete( 
	id int primary key auto_increment, 
	name varchar(20) 
);

# 插入测试数据
insert into test_delete (name) values ('青龙'), ('白虎'), ('朱雀'), ('玄武');

# 查看测试数据：青龙、白虎、朱雀、玄武的id依次为：1、2、3、4
select * from test_delete;

# 查看表结构，其中 AUTO_INCREMENT 等于5
show create table test_delete \G

在这里插入图片描述

# 删除表中所有的数据
delete from test_delete;

# 查看表结构，其中 AUTO_INCREMENT 依旧等于5
show create table test_delete\G

# 插入测试数据
insert into test_delete (name) values ('青龙'), ('白虎'), ('朱雀'), ('玄武');

# 查看测试数据：青龙、白虎、朱雀、玄武的id依次为：5、6、7、8
select * from test_delete;

# 查看表结构，其中 AUTO_INCREMENT 等于9
show create table test_delete \G

在这里插入图片描述

注意：删除整表操作要慎用！

2. truncate：截断表

TRUNCATE [TABLE] table_name;

注意：这个操作慎用！

  • SQL 中大写的表示关键字，[ ]中代表的是可选项。
  • truncate 只能对整表操作，不能像 delete 一样针对部分数据操作。
  • truncate 实际上不对数据操作，所以比 delete 更快。
  • truncate 在删除数据时不经过真正的事务，所以无法回滚，delete 需要经过事务。
  • truncate 会重置 AUTO_INCREMENT=n 字段，重新从1开始。

MySQL 中的日志

  • bin log：用于记录数据库的所有数据变更操作，包括数据的插入、更新和删除等。
  • redo log：数据库意外停止（如停电、系统崩溃等）后，通过 redo log 可以将未完成的事务重新执行一遍，确保数据的一致性。
  • indo log：当事务执行失败或用户主动回滚时，根据 undo log 中的反向操作记录，可以撤销事务对数据库所做的修改，将数据库恢复到事务开始前的状态。

创建一张测试表，表中包含一个自增长的主键 id 和姓名

create table test_truncate( 
	id int primary key auto_increment, 
	name varchar(20) 
);
insert into test_truncate (name) values ('青龙'), ('白虎'), ('朱雀'), ('玄武');
select * from test_truncate;
show create table test_truncate \G

在这里插入图片描述

truncate test_truncate;
insert into test_truncate (name) values ('青龙'), ('白虎'), ('朱雀'), ('玄武');
select * from test_truncate;
show create table test_truncate \G

在这里插入图片描述

注意： 截断表操作要慎用！

5. 插入查询结果

INSERT [INTO] table_name [(column1 [, column2] ...)] SELECT ... [WHERE ...] [ORDER BY ...] [LIMIT ...];
  • SQL中大写的表示关键字，[ ]中代表的是可选项。
  • SQL的作用是将筛选出来的记录插入到指定的表当中。
  • SQL中的column，表示将筛选出的记录的各个列插入到表中的哪一列。

删除表中的的重复复记录，重复的数据只能有一份

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');

# 查询表中非重复的数据：正是删除后的结果
select distinct * from duplicate_table;

在这里插入图片描述

# 1.创建相同结构的表duplicate_table_tmp
create table duplicate_table_tmp like duplicate_table;

# 2.将duplicate_table中非重复的数据插入到表duplicate_table_tmp中
insert into duplicate_table_tmp select distinct * from duplicate_table;

# 3.修改表的名称
rename table duplicate_table to old_duplicate_table;
rename table duplicate_table_tmp to duplicate_table;

# 4.查看最终结果
select * from duplicate_table;

在这里插入图片描述

二. 聚合函数

函数说明
cout([distinct] expr)返回查询到的数据的 数量
sum([distinct] expr)返回查询到的数据的 总和，不是数字没有意义
avg([distinct] expr)返回查询到的数据的 平均值，不是数字没有意义
max([distinct] expr)返回查询到的数据的 最大值，不是数字没有意义
min([distinct] expr)返回查询到的数据的 最小值，不是数字没有意义

聚合函数可以在 select 语句中使用，此时select每处理一条记录时都会将对应的参数传递给这些聚合函数。

select * from exam;

在这里插入图片描述

统计班级共有多少同学

select count(*) from exam;
select count(1) from exam;
select count(*) 总人数 from exam;
select count(math) 数学成绩人数 from exam;

在这里插入图片描述

统计语文成绩分数的个数

select count(distinct Chinese) 不同语文成绩的个数 from exam;

在这里插入图片描述

统计数学成绩的总分

select sum(math) 数学成绩总分 from exam;

在这里插入图片描述

统计数学成绩的平均分

select sum(math) / count(*) 数学成绩的平均分 from exam;
select avg(math) 数学成绩的平均分 from exam;

在这里插入图片描述

统计数学不及格的人数

select count(*) 数学不及格人数 from exam where math < 60;

在这里插入图片描述

统计三门成绩的平均分

select avg(Chinese + math + English) 三门成绩的平均分 from exam;

在这里插入图片描述

返回英文成绩最高分：

select max(English) 英文最高分 from exam;

在这里插入图片描述

返回数学成绩70分以上的最低分：

select min(math) res from exam where math > 70;

在这里插入图片描述

三. 分组查询

创建一个雇员信息表：emp员工表、dept部门表、salgrade工资等级表 (来自 oracle 9i 的经典测试表)

# scott_data.sql
DROP database IF EXISTS `scott`;
CREATE database IF NOT EXISTS `scott` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `scott`;

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `deptno` int(2) unsigned zerofill NOT NULL COMMENT '部门编号',
  `dname` varchar(14) DEFAULT NULL COMMENT '部门名称',
  `loc` varchar(13) DEFAULT NULL COMMENT '部门所在地点'
);


DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',
  `ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '雇员职位',
  `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',
  `hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
  `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);


DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
  `grade` int(11) DEFAULT NULL COMMENT '等级',
  `losal` int(11) DEFAULT NULL COMMENT '此等级最低工资',
  `hisal` int(11) DEFAULT NULL COMMENT '此等级最高工资'
);


insert into dept (deptno, dname, loc)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (deptno, dname, loc)
values (20, 'RESEARCH', 'DALLAS');
insert into dept (deptno, dname, loc)
values (30, 'SALES', 'CHICAGO');
insert into dept (deptno, dname, loc)
values (40, 'OPERATIONS', 'BOSTON');

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 1500, 0, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

insert into salgrade (grade, losal, hisal) values (1, 700, 1200);
insert into salgrade (grade, losal, hisal) values (2, 1201, 1400);
insert into salgrade (grade, losal, hisal) values (3, 1401, 2000);
insert into salgrade (grade, losal, hisal) values (4, 2001, 3000);
insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);

# 将 sql 文件导入 MySQL 数据库中
source /home/xzy/scott_data.sql;

# 使用该数据库
use scott;

# 查看3张表
desc dept;
select * from dept;

desc emp;
select * from emp;

desc salgrade;
select * from salgrade;

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

1. group by：分组

SELECT column1 [, column2], ... FROM table_name [WHERE ...] GROUP BY column [, ...] [order by ...] [LIMIT ...];
  • SQL中大写的表示关键字，[ ]中代表的是可选项。
  • 查询SQL中各语句的执行顺序为：where、group by、select、order by、limit
  • group by后面的列名，表示按照指定列进行分组查询。

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

# 员工的最高工资和平均工资
select max(sal) 最高工资, avg(sal) 平均工资 from emp;

# 通过deptno部门编号的不同，将员工划分为不同的部门，获取每个部门员工的最高工资和平均工资
select deptno, max(sal) 最高工资, avg(sal) 平均工资 from emp group by deptno;

在这里插入图片描述

  • 指定列名进行分组，实际是用该列不同的行数据来进行分组的，分组的列名 deptno，同一个组内的 deptno 值一定是相同的，可以被聚合统计。
  • 分组就是把一组按照条件拆成了多个组，进行各自组内的统计。分组类似分表，将一张表按照条件在逻辑上拆成了多个子表，然后分别对各自的子表进行聚合统计。

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

select deptno, job, avg(sal) 平均工资, min(sal) 最低工资 from emp group by deptno, job;

在这里插入图片描述

在这里插入图片描述

  • 一般来说，只有在 group by 后面出现的列名称，才能在 select 后面出现，否则语法错误。
  • 但是聚合函数可以出现，原因是分组就是为了聚合统计的。
select ename, deptno, job, avg(sal) 平均工资, min(sal) 最低工资 from emp group by ename, deptno, job;

2. having：条件筛选

SELECT ... FROM table_name [WHERE ...] [GROUP BY ...] [HAVING ...] [order by ...] [LIMIT ...];
  • SQL 中大写的表示关键字，[ ]中代表的是可选项。
  • SQL 中各语句的执行顺序为：where、group by、select、having、order by、limit。
  • having 子句中可以指明一个或多个筛选条件。

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

先统计出每一个部门的平均工资，再筛选出平均工资低于2000的部门。

select deptno, avg(sal) 平均工资 from emp group by deptno;
select deptno, avg(sal) 平均工资 from emp group by deptno having 平均工资 < 2000;

在这里插入图片描述

显示部门编号为 10 的平均工资

select deptno, avg(sal) 平均工资 from emp group by deptno;
select deptno, avg(sal) 平均工资 from emp group by deptno having deptno = 10;

在这里插入图片描述

注意：

  • having 子句可以同时包含聚合函数和非聚合列。
  • 但是 having 子句中的非聚合列必须出现在 group by 子句中。

3. having 和 where 的区别

  • where 子句放在表名后面，而 having 子句必须搭配 group by 子句使用，放在 group by 子句的后面。
  • where 子句是对整表的数据进行筛选，having 子句是对分组后的数据进行筛选。
  • where 子句中不能使用聚合函数和别名，而 having 子句中可以使用聚合函数和别名。

显示平均工资低于2000的部门和它的平均工资，其中SMITH员工不参与统计

select deptno, job, avg(sal) 平均工资 from emp where ename != 'SMITH' group by deptno, job having 平均工资 < 2000;

在这里插入图片描述

在这里插入图片描述

注意：这里 having 可以引用 select 中的别名，但是 where 不能引用 select 中的别名。

  • 不要单纯的认为，只有磁盘上表结构导入到 MySQL，真实存在的表，才叫表。
  • 中间筛选出来的，包括最终结果，全部都是逻辑上的表，MySQL 一切皆表。
  • 只有处理好单表的 “增删查改”，所有的 SQL 场景，全部都能用统一的方式进行。
select * from emp where ename = 'SMITH';
select * from emp having ename = 'SMITH';

在这里插入图片描述

having 将其看作聚合统计之后的表，进行条件筛选，可以替换为 where，但是不建议。

SQL 查询的逻辑执行顺序是：

  • from：确定数据来源表。
  • where：对表中的行进行过滤。
  • group by：将数据按指定列分组。
  • having：对分组后的结果进行过滤。
  • select：选择需要返回的列或表达式。
  • order by：对结果进行排序。
  • limit：限制返回的行数或跳过指定数量的行。

四. SQL 实战

  1. 牛客：批量插入数据
  2. 牛客：找出所有员工当前薪水salary情况
  3. 牛客：查找最晚入职员工的所有信息
  4. 牛客：查找入职员工时间排名倒数第三的员工所有信息
  5. 牛客：查找薪水记录超过15条的员工号emp_no以及其对应的记录次数t
  6. leetcode：查找重复的电子邮箱
  7. leetcode：大的国家
  8. leetcode：第二高的薪水

面试题 SQL 查询中各个关键字的执行先后顺序：

from > on > join > where > group by > with > having > select > distinct > order by > limit

【MySQL】聚合函数
学习笔记。
08-06 525
函数 说明 COUNT([DISTINCT] expr) 返回查询到的数据的数量 SUM([DISTINCT] expr) 返回查询到的数据的总和，不是数字没有意义 AVG([DISTINCT] expr) 返回查询到的数据的平均值，不是数字没有意义 MAX([DISTINCT] expr) 返回查询到的数据的最大值，不是数字没有意义 MIN([DISTINCT] ...
【MySQL】数据的增删查改
weixin_63249832的博客
11-08 1573
mysql的基本增删查改语句
Mysql基础操作语句
weixin_34368949的博客
04-09 169
SQL 简单的增删改查 不区分大小写, 表名和字段名可不加引号 查询语句 SELECT * FROM `table_name`; -- 注释 CTRL+/ : 注释 CTRL+/ : 取消注释 /* */ : 多行注释 创建表和字段 CREATE TABLE `table_name`( `name` VARCHAR(20), `age` int, `sex` VAR...
MySQL表的增删查改
最新发布
ijj_zZ的博客
02-26 1105
但在生产环境中，一般不去使用delete操作，一般在表中会加一个deleteState字段，用来表示这条记录是否被删除了，0表示正常（没有被删除），1表示已经被删除了，用update操作去更新deleteState字段，就可以实现删除功能，这条被删除的数据并没有被实质上删除掉，而是始终存在于数据库中。另外，我们的表中本来是没有总分这一列，所以通过表达式查询出来的结果集是通过一个临时表返回给我们的，执行完之后临时表就删除了。注意：在其他编程语言中，判断相等用的是==，而在SQL语言中，用的是=，赋值也是=
MySQL增删改查常用语法
ba3310的博客
03-06 977
MySQL增删改查的入门常用语法及安装
MySQL的常用语法（增删改查）
qq_58000391的博客
05-25 732
增删改查
Mysql基本增删改查语句
qq_47828184的博客
08-11 1542
SELECT * from student where class_num = '20201001' and sex = '男' and age > 20。SELECT * from student WHERE class_num = '20201001' or sex = '女'例子 insert into student(name,sex,age) values('张三'，18，'男')基本语法 insert into 表名(列1，列2，列3，列4，...) values(值，值，值)...
mysql之增删查改
04-24
在本文中，我们将深入探讨“MySQL之增删查改”这一主题，这是任何数据库管理员或开发者必须掌握的基础操作。 首先，我们来看“增”（Insert）操作。在MySQL中，增加数据到表中通常通过INSERT语句实现。例如，假设有...
【MySQL 07】表的增删查改 (带思维导图)
shangguanxiu的博客
08-19 1383
1. 插入数据。2. 查询数。3. 修改数据。4. 删除数据。5. 插入查询结果。6. 聚合函数。7. 分组查询。
【MySQL】6.表的增删查改(CURD)
weixin_74113106的博客
07-11 1015
表的增删查改
【MySQL】表的增删查改
fight_p的博客
06-07 1560
如果今天想把一个文件上传到linux上，比如这个文件是1G上传时间可能是10分钟，我们想把这个文件上传号之后放到一个目录下，我要求它是为原子性方式放入的。所以一般我们不能直接把文件上传到对应的目录下，因为它上传的过程一种在写入一定不是原子，它太慢了。所以我们把这个文件上传到临时目标下，全部上传之后然后再把文件move到那个目录下。直接move这个动作实际上是原子的。其实对一个文件进行重命名也是同一个道理，
MySQl的增删查改的几种语法格式
weixin_54828517的博客
09-06 984
一、增 1.insert into 表名（字段名,字段名,……） values （值,值,……）; 2.insert into 表名 set 字段名=值,字段名=值,字段名=值,……; 二、删 1.单表删除：delete from 表名 【where 筛选条件】; 2.多表删除：delete 表1的别名,表2的别名 from 表1 别名 inner | left | right join 表2 别名 ...
【超万字详细Mysql的基本语法之增删查改】
书生的技术分享
02-22 1037
超万字详细mysql的基本语法,增删改查,详细案例
MySQL ——数据的增删改查
2303_76544787的博客
10-30 4167
注意：连接查询涉及到两个表以上，在查询的时候至少要有一个必备的连接条件，这个必备的条件就是两个表共有的那个字段相等，而且这个字段一定在一个表里是主键，在另一个表里是外健。语法：select 字段 from 表1 right join 表2 on 连接条件 where 条件。select 字段 from 表1，表2 where 表1.条件 = 表2.条件。语法：select 字段名列表 from 表名 [where 查询条件] [基本查询语句、条件查询、多条件查询、模糊查询、分组查询、连接查询、子查询。
【MySQL】数据基本的增删改查操作
yican2580的博客
08-09 2863
详解MySQL中数据的增删改查操作指令
Mysql分组取最新一条数据总结（多种方法比较）
weixin_43582896的博客
07-25 7561
在日常业务开发中涉及到报表的统计，或者取某一个用户最新的一条记录并对他进行业务分析等等，现总结了常见的几种方法去取这这一类数据这里以mysql 5.7为例子。
MySQL：基础操作(增删查改)
一名还在努力学习技术的程序员的博客
07-18 7381
本篇博客详细介绍了MySQL数据库增删改查的基础操作，也有使用示例帮助理解
mysql：增删改查语句大全
热门推荐
weixin_44431073的博客
01-17 1万+
mysql语法：插入、查询、删除、修改语句大全
mysql 增删改查语法
每天都想吃好吃的
07-03 320
基本条件查询语句 select * from student where id =1(表示查询student表中id=1的学生的所有列)SELECT * from student WHERE class_num = '20201001' or sex = '女'例子 insert into student(name,sex,age) values('张三'，18，'男')基本语法 insert into 表名(列1，列2，列3，列4，...) values(值，值，值)
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值