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

【MySQL】数据的增删查改

article 2024/11/13 16:33:23

文章目录

  • 1. 插入数据(Create)
    • 1.1 全列插入
    • 1.2 指定列插入
    • 1.3 多行数据插入
    • 1.4 插入否则更新
    • 1.5 替换
  • 2. 读取数据(Retrieve)
    • 2.1 select列
    • 2.2 where条件
    • 2.3 结果排序
    • 2.4 筛选分页结果
  • 3. 修改数据(Update)
  • 4. 删除数据(delete)
    • 4.1 删除数据
    • 4.2 截断表
  • 5. 插入查询的结果
  • 6. 分组与聚合统计
    • 6.1 聚合统计
    • 6.2 group by子句分组

对数据的操作本质上就是增删查改，在mysql中，他们有特别的名字，我们将其称为 CRUD( Creat Retrieve Update Delete)，接下来我们依次介绍这些内容

1. 插入数据(Create)

语法：

insert [into] table_name
	[column [, column ...]] 
	values (value_list)[, (value_list)] ...
value_list: value [, value] ...

接下来的测试表结构：

image-20241028212817300

1.1 全列插入

insert into student values (1, '张三', '101');

image-20241028213034758

1.2 指定列插入

insert into student (name, class_id) values ('李四', 102);

image-20241028213140711

1.3 多行数据插入

insert into student (name, class_id) values ('王五', 101), ('赵六', 101), ('钱七', 102);

image-20241028213412579

1.4 插入否则更新

数据插入时，可能会因为主键或者唯一键冲突而导致插入失败，此时可以选择性的进行同步更新操作。

语法：

insert ... on duplicate key update
    column = value [, column = value] ...

image-20241029003536843

image-20241029004035310

1.5 替换

语法：

replace [into] table_name
	[column [, column ...]] 
	values (value_list)[, (value_list)] ...
value_list: value [, value] ...

image-20241029005127529

2. 读取数据(Retrieve)

案例构建：

create table if not exists result_exam(
	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 result_exam (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);

image-20241029010341465

2.1 select列

基本语法：

SELECT
    [DISTINCT] {* | {column [, column] ...}  -- 这里可以是*或者任何列，列之间使用“,”隔开
    [FROM table_name]                        -- 这里是从那个表中查询
    [WHERE ...]                              -- 筛选表中符合条件的数据
    [ORDER BY column [ASC | DESC], ...]      -- 对筛选出来的数据进行排序
    LIMIT ...                                -- 对排序出的数据进行分页显示

注意：通常情况下不建议使用 * 进行全列查询

    1. 查询的列越多，意味着需要传输的数据量越大;
    2. 可能会影响到索引的使用。(索引待后面课程讲解)

1. 全列查询

image-20241029010705293

2. 指定列查询

image-20241029011129530

3. 表达式查询与计算

image-20241029011342987

4. 为查询结果指定别名

语法：

select column [as] alias_name [...] from table_name;

image-20241029011407871

5. 去重结果

语法：

select distinct ...

image-20241029011642529

2.2 where条件

和C/C++的条件表达式类似，这里的条件也是选取表达式为真的结果，那么自然会有一些比较运算符和逻辑运算符

  • 比较运算符
运算符说明
>,>=,<,<=大于，大于等于，小于，小于等于
=等于，NULL不安全，例如NULL = NULL的结果是NULL
<=>等于，NULL安全，例如NULL = NULL的结果是true(1)
!=,<>不等于
between a0 and a1范围匹配，[a0,a1]，如果a0 <= value <= a1，返回true
in(option, …)如果是option中的任意一个，返回true
is null是null
is not null不是null
Like模糊匹配。%表示任意多个(包括0个)任意字符；_表示任意一个字符
  • 逻辑运算符
运算符说明
and多个条件都必须为true(1)， 结果才是true(1)
or任意一个条件为true(1)，结果为true(1)
not条件为true(1)，结果为false(0)

案例：

1. 英语不及格的同学及英语成绩 ( < 60 )

select name, english from result_exam where english < 60;

image-20241029130009371

2. 语文成绩在 [80, 90] 分的同学及语文成绩

select name,chinese from result_exam where chinese >= 80 and chinese <= 90; -- 使用逻辑运算符
select name,chinese from result_exam where chinese between 80 and 90; -- 使用between and

image-20241029130328310

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

select name, math from result_exam where math=58 or math=59 or math=98 or math=99; -- 使用逻辑运算符
select name, math from result_exam where math in(58,59,98,99);  -- 使用in表达式

image-20241029130629982

4. 姓张的同学 及 张某同学

select * from result_exam where name like '张%';  -- % 匹配任意多个(包括 0 个)任意字符
select * from result_exam where name like '张_';  -- _ 匹配1个任意字符

image-20241029132801785

5. 语文成绩好于英语成绩的同学

select name, chinese, english from result_exam where chinese > english;

image-20241029133053300

6. 总分在 200 分以下的同学

select name, chinese + math + english as 总分 from result_exam 
	where chinese + math + english < 200; 
-- 注意，这里不能在where子句中使用select的重命名，因为查询语句中是先执行where子句，此时还没有重命名

image-20241029135309402

7. 语文成绩 > 80 并且不姓张的同学

select name, chinese from result_exam where chinese > 80 and name not like '张%';

image-20241029222304222

8. 张某同学，否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80

select name, chinese + math + english as 总分 
from result_exam 
where name like '张_' or (
	chinese + math + english > 200 and chinese < math and english > 80
);

image-20241029222717765

9. NULL查询

select name, qq from student where qq is not null;

image-20241030095459386

在最开始我们说到where的比较运算符中有NULL安全和NULL不安全之分，这里我们演示一下

image-20241030101217237

2.3 结果排序

select ... from table_name [where ...]
    order by column [asc|desc], [...];
-- asc 为升序(从小到大) -- desc 为降序(从大到小) -- 默认为 ASC

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

案例：

1. 同学及数学成绩，按数学成绩升序显示

select name, math from result_exam order by math;

image-20241030101734412

2. 同学及 qq 号，按 qq 号排序显示

select name, qq from student order by qq;

image-20241030101845576

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

select name, chinese, math, english from result_exam order by math desc, english, chinese; 
-- order by的排序顺序可以有多个排序规则，按照先后顺序排序，执行排序

image-20241030103416567

4. 查询同学及总分，由高到低

select name, chinese + math + english as 总分 from result_exam order by 总分 desc;
-- 在order by的时候可以使用 select 语句的地方重命名的表达式，因为order by的执行本质上是将select查询到的内容进行排序，所以是先执行select再执行order by

image-20241030103532688

5. 查询姓张的同学或者姓赵的同学数学成绩，结果按数学成绩由高到低显示

select name, math from result_exam where name like '张%' or name like '赵%' order by math;

image-20241030104119252

2.4 筛选分页结果

语法：

-- 起始下标为 0
-- 从 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;

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

案例：

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

select * from result_exam order by id limit 3 offset 0;
select * from result_exam order by id limit 3 offset 3;
select * from result_exam order by id limit 3 offset 6;

image-20241030110046124

3. 修改数据(Update)

语法：

update table_name set column = expr [, column = expr ...]
    [where ...] [order by ...] [limit ...]

案例：

1. 将李四的数学成绩变更为80分

update result_exam set math = 80 where name = '李四';

image-20241030114152892

2. 将赵六同学的数学成绩变更为 60 分，语文成绩变更为 70 分

update result_exam set math = 60, chinese = 70 where name = '赵六';

image-20241030114823484

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

select name, math, chinese + math + english as 总分 from result_exam order by chinese + math + english asc limit 3 offset 0; -- 查看数据
update result_exam set math = math + 30 order by chinese + math + english asc limit 3; -- 修改指定数据
select name, math, chinese + math + english as 总分 from result_exam where name in ('钱七', '吴九', '李四'); -- 查看之前修改的数据

image-20241107154729189

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

select name, chinese from result_exam; -- 查看全表
update result_exam set chinese = chinese * 2; -- 没有限制则更新全表
select name, chinese from result_exam; -- 查看更新后的数据

image-20241107155136689

4. 删除数据(delete)

4.1 删除数据

语法：

delete from table_name [where ...] [order by ...] [limit ...]

案例：

1. 删除张三二的成绩

select * from result_exam where name='张三二';
delete from result_exam where name='张三二';
select * from result_exam where name='张三二';

image-20241107161000107

2. 删除整张表

-- 准备测试表
create table for_delete (
	id int primary key auto_increment,
	name varchar(10)
);
-- 插入表数据
insert into for_delete (name) values ('A'), ('B'), ('C');
-- 查看插入的数据
select * from for_delete;
-- 删除表
delete from for_delete;
-- 查看删除之后的结果
select * from for_delete;

image-20241107214935206

image-20241107214958788

image-20241107215306317

4.2 截断表

语法：

truncate [table] table_name

注意：这个操作慎用，原因如下

  1. 只能对整表操作，不能像 delete 一样针对部分数据操作；
  2. 实际上 MySQL 不对数据操作，所以比 delete 更快，但是truncate在删除数据的时候，并不经过真正的事物，所以无法回滚
  3. 会重置 auto_increment 项

案例：

create table for_truncate (id int primary key auto_increment, name varchar(20)); -- 准备测试表
insert into for_truncate (name) values ('A'), ('B'), ('C'); -- 插入测试数据
select * from for_truncate; -- 查看测试数据
truncate for_truncate; -- 截断整表数据，注意影响行数是 0，所以实际上没有对数据真正操作
select * from for_truncate; -- 查看删除结果
insert into for_truncate (name) values ('D'); -- 再插入一条数据，自增 id 在重新增长
select * from for_truncate; -- 查看数据
show create table for_truncate\G; -- 查看表结构，会有 AUTO_INCREMENT=2 项

image-20241108003427445

5. 插入查询的结果

案例：

insert into table_name [(column [, column ...])] 
	select ...

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

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

处理思路：

  1. 创建一张空表 no_duplicate_table，结构和 duplicate_table 一样
  2. 将 duplicate_table 的去重数据插入到 no_duplicate_table
  3. 通过重命名表，实现原子的去重操作
  4. 查看最终结果

代码：

create table no_duplicate_table like 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;
select * from duplicate_table;

image-20241108010812726

6. 分组与聚合统计

6.1 聚合统计

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

案例：

1. 统计班级共有多少同学

select count(*) from student;

image-20241108135115177

2. 统计班级收集的 qq 号有多少

select count(qq) from student; -- NULL不计入结果

image-20241108135232014

3. 统计本次考试的数学成绩分数个数

select count(math) from result_exam; -- count统计的是所有的结果
select count(distinct math) from result_exam; -- 先去重再计算

image-20241108140201043

4. 统计数学成绩总分

select sum(math) from result_exam;

image-20241108140246869

5. 统计平均总分

select avg(chinese + math + english) as 平均总分 from result_exam;

image-20241108140449572

6. 返回英语最高分

select max(english) from result_exam;

image-20241108140711588

7. 返回 > 70 分以上的数学最低分

select min(math) from result_exam where math > 70;

image-20241108140816085

6.2 group by子句分组

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

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

案例：这是来自oracle 9i的经典测试表

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

表结构如下：

image-20241108151744483

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

select deptno, avg(sal), max(sal) from emp group by deptno;

image-20241108151919221

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

select deptno, job, avg(sal), min(sal) from emp group by deptno, job;

image-20241108152154623

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

  • 统计各个部门的平均工资

    select deptno, avg(sal) as myavg from emp group by deptno;

  • 3.2 having和group by配合使用，对group by结果进行过滤

    select deptno, avg(sal) as myavg from emp group by deptno having myavg > 2000;

    image-20241108152511681

    注：having经常和group by搭配使用，作用是对分组进行筛选，作用有些像where

SQL查询中各个关键字的执行先后顺序 from > on> join > where > group by > with > having > select > distinct > order by > limit

本节完……


http://www.kler.cn/a/387934.html

相关文章：

  • 实现一个BLE HID鼠标
  • DeBiFormer实战：使用DeBiFormer实现图像分类任务（二）
  • 字节、快手、Vidu“打野”升级，AI视频小步快跑
  • PHP API如何使用access_token开放接口有效期
  • window下安装rust 及 vscode配置
  • 即插即用篇 | YOLOv8 引入 代理注意力 AgentAttention
  • //字符串数组
  • STM32单片机WIFI语音识别智能衣柜除湿消毒照明
  • 在 C# 中，ICollection 和 IList 接口有什么区别？
  • SQL注入攻击及其在SpringBoot中使用MyBatisPlus的防范策略
  • [OpenGL]使用OpenGL实现硬阴影效果
  • Audio-Language Models
  • MySQL8完全卸载方法-Win10系统
  • 4.python字面量
  • nvm 切换 Node.js 版本
  • LabVIEW扫描探针显微镜系统
  • 订单分库分表
  • 常用的c++特性--＞day02
  • IMX93适配4G网络
  • 【LLaMa-Factory】监督微调训练方法
  • Android 启动时应用的安装解析过程《二》
  • MATLAB实现智能水滴算法(Intelligent Water Drops Algorithm, IWDA)
  • oracle 带有小数点的0.几数据在转化为字符串的时候丢失前面的0
  • C# 用于将一个DataTable转换为Users对象的列表
  • fastapi_socketio连接vue的socktio.client
  • prompt资料收集