增删改查(CRUD)操作

MySQL系列：

初识MySQL，MySQL常用数据类型和表的操作
有些前面博客提及的知识点这里都可以会省略，有兴趣的可以去观看前面内容。

1.CRUD简介

CRUD是对数据库中的记录进⾏基本的增删改查操作：
• Create (创建)
• Retrieve (读取)
• Update (更新)
• Delete (删除)

2.Create(创建)

语法: INSERT [INTO] table_name
[(column [, column] …)]
VALUES
(value_list) [, (value_list)] …
value_list: value, [, value] …

创建一个用于演示的表

2.1单行数据全列插入

value_list 中值的数量必须和定义表的列的数量及顺序⼀致

2.2 单行数据指定插入

value_list 中值的数量必须和指定列数量及顺序⼀致

2.3 多⾏数据指定列插⼊

在⼀条insert语句中也可以指定多个value_list，实现⼀次插⼊多⾏数据

在单行数据插入时推荐使用指定插入，当表数据多时可以更清楚的知道插入的内容.
指定位置去插入表属性的顺序也可以调换

注意插入时表属性不能缺少或者不赋值

Create(创建)代码:
#2.1单行数据全列插入

-- 插入第一行数据 
insert into user values (1,'刘1');
-- 插入第二行
insert into user values (2,'刘2');#2.2单行数据指定插入
insert into user(id,name) values (3,'张三');insert into user(name,id) values ('赵六',6);#2.3 多行数据指定列插入
insert into user(id,name) values (4,'李四'),(5,'王五');

3.Retrieve(读取)

语法:
SELECT
[DISTINCT]
select_expr [, select_expr] …
[FROM table_references]
[WHERE where_condition]
[GROUP BY {col_name | expr}, …]
[HAVING where_condition]
[ORDER BY {col_name | expr } [ASC | DESC], … ]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]

创建一个用于演示的表:

3.1 Select查询

3.1.1 全列查询

查询全部数据

3.1.2 指定列查询

查询所有人的编号、姓名和数学成绩

查询的顺序没有要求:

3.1.3 查询字段为表达式（都是临时表不会对原有表数据产生影响）

常量表达式:

也可以是常量的运算

表达式中包含⼀个字段（列于常量运算）

表达式中多个字段（列于列运算）:

3.1.4为查询结果指定别名

语法:
1 SELECT column [AS] alias_name [, …] FROM table_name;
AS可以省略，别名如果包含空格必须⽤单引号包裹

将每个人的总分展示出来：

3.1.5查询结果去重

查看两条math为98的数据进行去重

去重的条件是所要求值全部相同
以下math相同但id分别为1,3

注意：
•
查询时不加限制条件会返回表中所有结果，如果表中的数据量过⼤，会把服务器的资源消耗殆尽
•
在⽣产环境不要使用不加限制条件的查询

Retrieve(Select )代码:CREATE TABLE if not exists exam(id BIGINT,name VARCHAR(20) COMMENT '同学姓名',chinese float COMMENT '语文成绩',math float COMMENT '数学成绩',english float COMMENT '英语成绩'
);
-- 插入测试数据
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);
#3.1.1 全列查询
#使用*可以查询表中所有列的值
select * from exam;
#3.1.2 指定列查询
#• 查询所有人的编号、姓名和数学成绩
select id,name,math from exam;
#查询的顺序没有要求:
select name,english,id from exam;#  查询字段为表达式
#常量表达式:
select id,name,1 from exam;
#也可以是常量的运算
select id,name,1+1 from exam;
# 表达式中包含一个字段
select id,name,math+10 from exam;
#表达式中多个字段:
select id,name,chinese+math+english from exam;
# 3.1.3为查询结果指定别名
#将每个人的总分展示出来：
select id,name,chinese+math+english as 总分 from exam;
# 3.1.4查询结果去重
#查看两条math为98的数据进行去重
select math from exam;

3.2 where 条件查询

语法：
SELECT
select_expr [, select_expr] … [FROM table_references]
WHERE where_condition

比较运算符:

< , > , >= , <= 小于,大于，大于等于，小于等于

= MySQL中=同时代表赋值和判断 ，对于NULL不安全,NULL=NULL还是NULL

<=> 代表等于 对于NULL相对安全 NULL<=>NULL 结果为TRUE(1)

!= ,<> 代表不等于

IS NULL 是NULL

IS NOT NULL 不是NULL

value BETWEEN a0 AND a1
范围匹配，[a0, a1]，如果a0 <= value <= a1，返回TRUE或1，NOT BETWEEN则取反

value IN (option, …) 如果value 在optoin列表中，则返回TRUE(1)，NOT IN则取反

LIKE 模糊匹配，% 表⽰任意多个(包括0个)字符；_ 表⽰任意⼀个字符，NOT LIKE则取反

逻辑运算符:

AND 多个条件必须都为 TRUE(1)，结果才是 TRUE(1)

OR 任意⼀个条件为 TRUE(1), 结果为 TRUE(1)

NOT 条件为 TRUE(1)，结果为 FALSE(0)

3.2.1 where 基础查询

（1）查询语文<=70的

（2）查询数学高于语文的

（3）查询总分低于250的

这里我们需要了解select与from与where之间的优先级

首先执行的是from找到这个表，然后执行符合where条件的,
最后执行select返回在符合条件的要显示的列

所以是错误的当whiere执行时 total还没有被定义，select执行完后chinese+math+english as total 执行 total才定义完成

3.2.2where 范围查询

查询英语成绩在60~80之间的

查询数学成绩是 78 或者 79 或者 98 或者 99 分的同学及数学成绩

3.2.3 where 模糊查询

%表示任意多个(包括0个)字符;

%表示所有,等于没有指定条件
%xxx,表示以xxx结束,前面可以包含任意多个字符
xxx%,表示以xxx开头,后面可以包含任意多个字符
%xxx%,前面和后面可以包含任意多个字符,中间必须有xxx

表示任意一个字符,

严格匹配 写多少个_就表示多少个字符

是一个占位符
表示只有一个字符
_ xxx,表示以xxx结束,前面可以包含一个字符
xxx _,表示以xxx开头,后面可以包含一个字符
_XXX _,前面和后面可以包含一个字符,中间必须是xxx

%系列：

_系列:

3.2.4 where NULL查询

对NULLL与其他值进行运算结果为NULL

3.2.5where AND,OR查询

观察AND与OR的优先级：

AND的优先级高于OR

Retrieve(where )代码：

#3.2.1基础查询
#查询语文<=70的
#查询数学高于语文的
#查询总分低于250的
select id,name,chinese from exam where chinese<=70;
select id,name,chinese,math from exam where math>chinese;
select id,name, chinese+math+english as total from exam where (chinese+math+english)<250; 
#3.2.2范围  查询英语成绩在60~80之间的  查询数学成绩是 78 或者 79 或者 98 或者 99 分的同学及数学成绩
select id,name,english from exam where english between 60 and 80;
select id,name,math from exam where math in(78,79,98,99);
#3.2.3 where 模糊查询
select id,name from exam where name like '%孟%';
select id,name from exam where name like '孙%';
select id,name from exam where name like '%德';
#_系列
select id,name from exam where name like '孙_';
select id,name from exam where name like '孙__';
select id,name from exam where name like '_孟_';
#3.2.3 where NULL查询
#插入一条null
insert into exam values (8,'张飞',88,98,NULL);
select *from exam where english is null;
select *from exam where english is not null;
#对NULLL与其他值进行运算结果为NULL
select id,name,chinese+math+english as total from exam;

3.3 Order by查询

语法:
– ASC 为升序（从⼩到⼤）
– DESC 为降序（从⼤到⼩）
– 默认为 ASC
SELECT … FROM table_name [WHERE …] ORDER BY {col_name | expr } [ASC | DESC], … ;

3.3.1ASC查询

对语文进行ASC

3.3.2 desc查询

对数学进行desc

3.3.3 ASC与desc查询

改一下数据观察同时对语文成绩进行asc，数学进行desc

来观察是否可以使⽤列的别名进⾏排序

注意在排序时NULL比任何值都小, 改一负数进行观察

Retrieve(Order by)代码：
#Order by查询
#对语文进行ASC
select id,name,chinese from exam order by chinese asc;
#对数学进行desc
select id,name,math from exam order by math desc;
#改一下数据观察同时对语文成绩进行asc，数学进行desc
select id,name,chinese,math from exam order by chinese asc, math desc;
#来观察是否可以使⽤列的别名进⾏排序
select id,name,chinese+math+english as total from exam order by chinese+math+english desc;
select id,name,chinese+math+english as total from exam order by total desc;
#注意在排序时NULL比任何值都小, 改一负数进行观察
select id,name,chinese+math+english as total from exam order by total desc;

3.4 分页查询

语法：-- 起始下标为 0
– 从 0 开始，筛选 num 条结果
SELECT … FROM table_name [WHERE …] [ORDER BY…] LIMIT num;
– 从 start 开始，筛选 num 条结果
SELECT … FROM table_name [WHERE …] [ORDER BY…] LIMIT start, num;
– 从 start 开始，筛选 num 条结果，⽐第⼆种⽤法更明确建议使⽤
SELECT … FROM table_name [WHERE …] [ORDER BY…] LIMIT num OFFSET start;

分页查询主要掌握查询页数与每页查询多少列之间的关系
插入一列数据：
insert into exam(id,name,chinese,math,english) values (9,‘李白’,94,91,77);

接下来将数据增加到9条分5页（第一条为0下标）
num=2;
start=（页数-1）*num
进行分页查询;

Retrieve(分页查询)代码：

#3.4 分页查询
#插入一列
insert into exam(id,name,chinese,math,english) values (9,'李白',94,91,77);select * from exam order by id desc limit 0,2;select * from exam order by id desc limit 2,2;select * from exam order by id desc limit 4,2;select * from exam order by id desc limit 6,2;select * from exam order by id desc limit 8,2;

4.Update（更新）

语法:
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET assignment [, assignment] …
[WHERE where_condition]
[ORDER BY …]
[LIMIT row_count]

4.1.1 改写指定单列数据

将孙悟空的语文数学成绩都是加10

4.1.2 改写指定多列数据

将所有英语成绩*2

注意：• 不加where条件时，会导致全表数据被列新，谨慎操作
Update(更新）代码：

# 4.Update（更新）# 4.1.1  改写指定单行数据select name,chinese,math from exam where name ='孙悟空';update exam set chinese=chinese+10,math=math+10 where name '孙悟空';select name,chinese,math from exam where name ='孙悟空';# 4.1.2  改写指定多行数据,将所有英语成绩*2id,name,english from exam order by english asc ;update exam set english=english*2;id,name,english from exam order by english asc ;

5.Delete(删除)

语法: DELETE FROM tbl_name [WHERE where_condition] [ORDER BY …] [LIMIT row_count]

5.1.1指定单列删除

删除名为张飞的数据

5.1.2删除整张表，插入一张演示表

注意：Delete操作非常危险，执⾏Delete时不加条件会删除整张表的数据，谨慎操作
Delete(删除)代码：

#5.Delete# 5.1.1指定单列删除,删除名为张飞的数据
delete from exam where name='张飞';
select name from exam where name='张飞';
#5.1.2删除整张表，插入一张演示表
create table if not exists t_student(
id bigint not null comment'编号',
name varchar(20) not null comment'用户名'
);
insert into t_student values (1,'小明'),(2,'小龙'),(3,'小兰');

6.总代码:

#2Create(创建)
#创建一个用于演示的表
create table if not exists user(
id bigint not null comment'编号',
name varchar(20) not null comment'用户名'
);
#2.1单行数据全列插入
-- 插入第一行数据 
insert into user values (1,'刘1');
-- 插入第二行
insert into user values (2,'刘2');#2.2单行数据指定插入
insert into user(id,name) values (3,'张三');
insert into user(name,id) values ('赵六',6);#2.3 多行数据指定列插入
insert into user(id,name) values (4,'李四'),(5,'王五');insert into user(name) values ('宋七');
#3.Retrieve(读取)
CREATE TABLE if not exists exam(id BIGINT,name VARCHAR(20) COMMENT '同学姓名',chinese float COMMENT '语文成绩',math float COMMENT '数学成绩',english float COMMENT '英语成绩'
);
-- 插入测试数据
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);
#3.1.1 全列查询
#使用*可以查询表中所有列的值
select * from exam;
#3.1.2 指定列查询
#• 查询所有人的编号、姓名和数学成绩
select id,name,math from exam;
#查询的顺序没有要求:
select name,english,id from exam;
#  查询字段为表达式
#常量表达式:
select id,name,1 from exam;
#也可以是常量的运算
select id,name,1+1 from exam;
# 表达式中包含一个字段
select id,name,math+10 from exam;
#表达式中多个字段:
select id,name,chinese+math+english from exam;
# 3.1.3为查询结果指定别名
#将每个人的总分展示出来：
select id,name,chinese+math+english as 总分 from exam;
# 3.1.4查询结果去重
#查看两条math为98的数据进行去重
select math from exam;#3.2.1基础查询
#查询语文<=70的
#查询数学高于语文的
#查询总分低于250的
select id,name,chinese from exam where chinese<=70;
select id,name,chinese,math from exam where math>chinese;
select id,name, chinese+math+english as total from exam where (chinese+math+english)<250; 
#3.2.2范围  查询英语成绩在60~80之间的  查询数学成绩是 78 或者 79 或者 98 或者 99 分的同学及数学成绩
select id,name,english from exam where english between 60 and 80;
select id,name,math from exam where math in(78,79,98,99);
#3.2.3 where 模糊查询
select id,name from exam where name like '%孟%';
select id,name from exam where name like '孙%';
select id,name from exam where name like '%德';
#_系列
select id,name from exam where name like '孙_';
select id,name from exam where name like '孙__';
select id,name from exam where name like '_孟_';
#3.2.3 where NULL查询
#插入一条null
insert into exam values (8,'张飞',88,98,NULL);
select *from exam where english is null;
select *from exam where english is not null;
#对NULLL与其他值进行运算结果为NULL
select id,name,chinese+math+english as total from exam;#Order by查询
#对语文进行ASC
select id,name,chinese from exam order by chinese asc;
#对数学进行desc
select id,name,math from exam order by math desc;
#改一下数据观察同时对语文成绩进行asc，数学进行desc
select id,name,chinese,math from exam order by chinese asc, math desc;
#来观察是否可以使⽤列的别名进⾏排序
select id,name,chinese+math+english as total from exam order by chinese+math+english desc;
select id,name,chinese+math+english as total from exam order by total desc;
#注意在排序时NULL比任何值都小, 改一负数进行观察
select id,name,chinese+math+english as total from exam order by total desc;#3.4 分页查询
#插入一列
insert into exam(id,name,chinese,math,english) values (9,'李白',94,91,77);select * from exam order by id desc limit 0,2;select * from exam order by id desc limit 2,2;select * from exam order by id desc limit 4,2;select * from exam order by id desc limit 6,2;select * from exam order by id desc limit 8,2# 4.Update（更新）# 4.1.1  改写指定单行数据select name,chinese,math from exam where name ='孙悟空';update exam set chinese=chinese+10,math=math+10 where name '孙悟空';select name,chinese,math from exam where name ='孙悟空';# 4.1.2  改写指定多行数据,将所有英语成绩*2id,name,english from exam order by english asc ;update exam set english=english*2;id,name,english from exam order by english asc ;#5.Delete# 5.1.1指定单列删除,删除名为张飞的数据
delete from exam where name='张飞';
select name from exam where name='张飞';
#5.1.2删除整张表，插入一张演示表
create table if not exists t_student(
id bigint not null comment'编号',
name varchar(20) not null comment'用户名'
);
insert into t_student values (1,'小明'),(2,'小龙'),(3,'小兰');