MySQL增删改查操作 -- CRUD

个人主页：顾漂亮

目录

1.CRUD简介

2.Create新增

使用示例：

注意点：

3.Retrieve检索

使用示例：

注意点：

4.where条件查询

前置知识：-- 运算符

比较运算符

使用示例： 

注意点：

5.Order by 排序

使用示例：

注意点：

6.分页查询

使用示例：

注意点：

7.Update修改

使用示例：

注意点：

8.Delete删除

使用示例：

注意点：

9.截断表

使用示例：

注意点：

10.插入查询结果

使用示例：

注意点：

11.聚合函数

常见聚合函数：

12.group by分组查询

使用示例：

注意点：

---

 

1.CRUD简介

• Create(创建)

• Retrieve(读取)

• Update(更新)

• Delete(删除)

2.Create新增

使用示例：

use ghr;
-- create新增
drop table if exists users; -- 在创建之前添加一层校验
create table users(id bigint primary key auto_increment, -- 为id列设置自增主键name varchar(20) not null  -- 为name列设置不为空条件
);-- 显示表   -- 使用该语句前必须先使用use 语句，进入一个数据库中！！！！
show tables;-- 单行数据全插入  values 中的数据顺序必须要与列的顺序一致
insert into users values (1, '张三');  -- 注意：MySQL中的字符串需要用单引号包裹-- 单行数据指定列插入  -- values中的数据 必须要与users后面括号中的顺序一致
insert into users(id, name) values (3, '王五'); -- 多行数据指定列插入  多个数据中间用 , 分开即可 
insert into users(id, name) values (4, '赵六'), (5, '钱七');select * from users; 

注意点：

1. 一次插入一条数据的效率高还是一次插入多条数据的效率更高？

• 执行所有的SQL语句都会有网络开销

• MySQL数据库在保存数据的时候也会有磁盘开销

• 每执行一条SQL语句都需要开启一个事务，事务的开启到关闭也需要消耗资源

• 因此：一次提交多条数据在一个可控范围内的时候，比一次提交一次数据的效率可以高一点！！

3.Retrieve检索

使用示例：

-- 检索查询
use ghr; -- 使用ghr数据库-- 首先创建表
drop table if exists exam; -- 添加校验
create table exam(id bigint primary key auto_increment,name varchar(20) not null, chinese float,math float,english float 
);-- 插入测试数据insert into exam(id, name, chinese, math, english) values 
(1, '唐三藏', 67, 98, 56),(2, '孙悟空', 87, 78, 77),(3, '猪悟能', 88, 98, 90),(4, '曹孟德', 82, 84, 67),(5, '刘玄德', 55, 85, 45),(6, '孙权', 70, 73, 78),(7, '宋公明', 75, 65, 30);-- 全列查询select * from exam;-- 指定列查询select name, chinese from exam;
select chinese, name from exam; -- 要查询的列只要在表中存在，与顺序无关-- 查询字段为表达式
# 把所有语文成绩+10
select id, name, chinese+10 as sum from exam;  -- 可以为表达式起一个别名 可以加 as 不加也可以
# 计算总成绩
select id, name, chinese + math + english as 总分 from exam;-- 为结果去重查询
select distinct math from exam;

注意点：

• 用select返回的查询结果是根据查询列表中字段和表达式生成的一个临时表，并不会真正修改数据表中的值

• 在select后面的查询列表中指定希望查询的列，可以是一个也可以是多个，中间用逗号隔开。指定列的顺序与表的结构无关

• 使用disctinct去重时候，只有查询列表中所有列的值相同的时候才会判定为重复

• 查询不加限制条件会返回表中的所有结果，如果表中的数据量过大，会把服务器的资源消耗殆尽

• 在生产环境下，一定注意谨慎使用不加限制条件的查询

4.where条件查询

前置知识：-- 运算符

比较运算符

运算符	说明
>,>=,<, <=	与Java/c等用法相同
=	等于，对于NULL的比较不安全，比如NULL = NULL的结果还是NULL
<=>	等于，对于NULL的比较是安全的， 比如NULL <=> NULL 结果为1
!=, <>	不等于
Between A and B	匹配范围[A,B]
Value in(A, B, ....)	如果value在in中返回1，不再返回0
Is null	判断是NULL
Is not null	判断不是NULL
like	模糊匹配

运算符	说明
or	任意一个条件
and	多个条件必须同时成立
not	类似于！取反操作

使用示例： 

-- where条件查询use ghr;-- 基本查询
# 英语成绩小于60
select name, english from exam where english <= 60;
# 总成绩200分以下 -- 注意where语句中不可以使用别名
select name, chinese+math+english as 总分 from exam where chinese + math+english < 200;-- and和or    优先级 -- not > and > as 如果三者混合使用，建议加（）
select * from exam where chinese > 80 and english > 80;select * from exam where chinese > 80 or english > 80;-- 范围查询select name, chinese from exam where chinese between 80 and 90;select name, math from exam where math in(78, 79, 98, 99);-- 模糊查询select * from exam where name like '孙%';
# 注意二者区别
select * from exam where name like '孙_';-- NULL的查询
# 构造数据
insert into exam values (8, '张飞', 27, 0, NULL);select * from exam where english is null;select * from exam where english is not null;# NULL 值与其他值运算结果为NULL
select name, chinese + math + english as sum from exam;

注意点：

• where条件中可以使用表达式，但是不能使用别名

• and优先级高于or，在同时使用的时候，建议使用小括号包裹优先执行的部分

• NULL与任何值运算结果都为NULL

• 过滤NULL时不要使用 =,!=,<>

5.Order by 排序

使用示例：

-- 前置知识：asc 为升序   desc为降序-- 按照英语成绩降序
select name, english from exam order by english desc;-- 查询总分，从高到低
select name, chinese + math + english as 总分 from exam order by 总分 desc; # 可以使用别名进行查询-- 去除有NULL值的排序
-- 所有英语成绩不为NULL的同学，按照语文成绩从高到低
select name, chinese, math, english from exam where english is not null order by chinese desc;

注意点：

• 查询中若没有oder by 子句，返回的顺序是未定义的，永远不要依赖这个顺序

• Order by子句中可以使用列的别名进行排序，注意与where进行区分

• NULL进行排序的时候，视为比任何值都小，升序出现在最上面，降序出现在最下面

6.分页查询

使用示例：

-- 分页查询   -- 三种方案，以下三种方案查询结果都是相同的select * from exam order by id asc limit 3; -- 默认从偏移量0位置开始，查询3行数据select * from exam order by id asc limit 0,3; -- 从偏移量0位置开始查询，查询3条数据select * from exam order by id asc limit 3 offset 0;-- 从偏移量0位置开始查询，查询3条数据

注意点：

• 分页查询可以有效控制一次返回的记录条数

• 可以有效减少数据库服务器的压力，同时对于用户也比较友好

• 在工作中，一定注意多使用分页查询

7.Update修改

使用示例：

-- 将孙悟空同学数学成绩变为80update exam set math=80 where name='孙悟空';-- 将总成绩倒数前3的数学成绩加上5分# 先查看原始数据
select name, chinese+math+english as sum from exam where chinese+math+english is not null order by sum asc limit 3;# 注意 where语句中不可以随意起别名
-- 错误示范select name, chinese+math+english from exam where chinese+math+english as sum is not null order by sum asc limit 3;
update exam set math=math+5 where chinese + math + english is not null order by chinese + math + english  asc limit 3;# 查看查询结果
select name, chinese+math+english as sum from exam where name in ('宋公明', '刘玄德', '唐三藏') order by sum asc;

注意点：

• 在原值的基础上做变更时，不能使用math+=30这样的语法

• 不加where条件时，会导致全表数据被更新，谨慎操作

• 一般情况下类似于这样的更新update exam set math=80 where name='孙悟空';最为合适，先用where语句过滤，再进行更新

8.Delete删除

使用示例：

-- 删除表中一项数据
# 删除表中孙悟空的成绩
delete from exam where name = '孙悟空';-- 删除整张表的数据
# 准备一个测试表作为备份
create table if not exists t_delete(id int,name varchar(20)
);
# 插入被测数据
insert into t_delete(id,name) values (1, 'A'), (2, 'B'), (3, 'C');#删除表格中的数据
delete from t_delete;

注意点：

• 如果执行delete操作不加限制条件，会将整个表中的数据全部删除，谨慎使用

• MySQL服务如果开启了二进制日志，每一次的insert、update、delete操作都会记录在二进制日志里，如果需要恢复数据，可以读取日志中的记录，再进行反操作即可、

• 注意delete只是删除表中的数据，并不会删除表，删除表需要用到表的操作中的drop，注意不要搞混淆

9.截断表

使用示例：

-- 创建测试表
# 检查判断
drop table if exists t_truncate;
create table t_truncate(id bigint primary key auto_increment,name varchar(20)
);# 插入测试数据
insert into t_truncate(name) values ('A'), ('B'), ('C');-- 显示建表结构 -- 以下是在命令行窗口中进行 AUTO_INCREMENT=4
mysql> show create table t_truncate;
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                   |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_truncate | CREATE TABLE `t_truncate` (`id` bigint NOT NULL AUTO_INCREMENT,`name` varchar(20) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)-- 截断表 
mysql> truncate table t_truncate;
Query OK, 0 rows affected (0.03 sec)
-- 显示截断后表的结构 发现AUTO_INCREMENT 被重置为0
mysql> show create table t_truncate;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table|
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_truncate | CREATE TABLE `t_truncate` (`id` bigint NOT NULL AUTO_INCREMENT,`name` varchar(20) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-- 继续写入数据 AUTO_INCREMENT为1
insert into t_truncate(name) values('d');

注意点：

• 只能对整表操作，不能像delete一样对部分数据删除

• 执行truncate操作时会把表的状态重置为初始状态，表中的数据也会被清除

• 执行truncate不对数据操作所以比delete更快，truncate在删除数据的时候，不经过真正的事务，所以无法回滚

• 会重置auto_increment项

10.插入查询结果

使用示例：

-- 删除表中的重复数据
#首先创建测试表
drop table if exists t_record;
create table t_record(id int,name varchar(20)
);#插入测试数据
insert into t_record(id,name) values
(100, 'aaa'),
(100, 'aaa'),
(200, 'bbb'),
(200, 'bbb'),
(200, 'bbb'),
(300, 'ccc');# 创建一张新表，表结构与t_record相同
drop table if exists t_record_new;
create table t_record_new like t_record;# 原来表中的数据去重之后copy进入新表
insert into t_record_new select distinct * from t_record;   -- 核心步骤#新表与原来表重命名
rename table t_record to t_record_old, t_record_new to t_record; 

注意点：

• 对于重命名表和数据表的迁移一般是由数据库管理员（DBA）来操作

• 原始表中的数据一般都不会主动删除，但是真正查询时不需要重复的数据，如果每次查询都使用distinct进行去重操作，会严重影响效率。可以创建一张与t_record表结构相同的表，把去重的记录写入新表中，以后查询都从新表中查，这样真实数据不会丢失，同时也能保证查询效率

11.聚合函数

常见聚合函数：

-- count统计exam表中某一列数据的量
select count(*) from exam;
select count(math) from exam;-- 语文成绩小于50 的学生人数
select count(chinese) from exam where chinese < 50;-- sum统计某一列数据的总和
select sum(math) from exam;# 无法统计非数值列的和
select sum(name) from exam;-- avg统计某一列的平均数
select avg(math) from exam;# 统计总分平均分
select round(avg(chinese + math + english),2) as 总分 from exam ;-- 类比max、min用法与上述例子类似，此处不再一一赘述

12.group by分组查询

使用示例：

-- 准备测试表
drop table if exists emp;
create table emp(id bigint primary key auto_increment,name varchar(20) not null,role varchar(20) not null,salary decimal(10,2) not null
);# 插入测试用例
insert into emp values (1, '马云', '老板', 1500000.00);
insert into emp values (2, '马化腾', '老板', 1800000.00);
insert into emp values (3, '鑫哥', '讲师', 10000.00);
insert into emp values (4, '博哥', '讲师', 12000.00);
insert into emp values (5, '平姐', '学管', 9000.00);
insert into emp values (6, '莹姐', '学管', 8000.00);
insert into emp values (7, '孙悟空', '游戏角色', 956.8);
insert into emp values (8, '猪悟能', '游戏角色', 700.5);
insert into emp values (9, '沙和尚', '游戏角色', 333.3);-- 统计每个角色的人数
select role, count(*) as sum from emp group by role;-- 统计每一个角色的最高、最低工资、平均工资
select role, avg(salary), min(salary), max(salary) from emp group by role;-- 显示平均工资低于1500的角色和它的平均工资  -- having 语句支持别名
select role, avg(salary) as avg from emp group by role having avg < 1500; 
--  having 与 group by的顺序不可以颠倒

注意点：

• 使用group by进行分组处理之后，对分组的结果进行过滤的时候，不能使用where子句，而要使用having子句

• Having 用于对分组结果的条件过滤

• where用于对表中真实数据的条件过滤

• 在group by执行之前，where已经执行过了