【半夜学习MySQL】表的增删改查操作[上篇]（含单行与多行数据插入、全列与指定列插入、select中的where子句与order by子句、limit用法详谈）

本文链接：https://blog.youkuaiyun.com/m0_66926829/article/details/138803756

在这里插入图片描述

🏠关于专栏：半夜学习MySQL专栏用于记录MySQL数据相关内容。
🎯每天努力一点点，技术变化看得见

文章目录

前言
Create（创建）
Retrieve（读取）

前言

我们平时常说的数据库表的CURD指的就是：Create（创建）、Retrieve（读取）、Update（更新）、Delete（删除）。对于上述四种操作，将分为两篇文章介绍，本文将先行介绍Create（创建）和Retrieve（读取）操作。

Create（创建）

这里create指的就是数据的新增
语法：

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

案例：
下方介绍的各个操作，将基于下方表↓↓↓

create table students(
id int unsigned primary key auto_increment,
sn int not null unique comment '学号',
name varchar(32) not null,
qq varchar(20)
);
desc students;

在这里插入图片描述

单行数据插入&&全列插入

在插入时，如果没有指名列名称，则value_list数量和顺序必须与表定义时数量和顺序一致。

insert into students values(100, 10000, '小明', NULL);
insert into students values(101, 10001, '小光', '123456');
select * from students;

在这里插入图片描述
像这种没有指名列名称的，称为全列插入。而一次只插入一行数据的操作，称为单行数据插入。

多行数据插入&&指定列插入

如果我们在一条SQL语句中插入多条数据，则称为多行数据插入。

insert into students values (102, 10002, '小树', '456789'), (103, 10003, '小勇', '963852');
select * from students;

在这里插入图片描述
由于我们在创建表的时候，设定id是自增的，在插入时，我们可以不设置id值。如果不想对id值进行设置，则需要使用指定列插入，即列出要指定值的列名称。

insert into students(sn, name, qq) values('10004', '小何', '852741');
insert into students(qq, sn, name) values('111222', '10005', '小津');
select * from students;

在这里插入图片描述
★ps：指定列插入时，可以不按照创建表时的顺序进行插入，只要保证指定列名顺序和value_list各个值的顺序相同即可。

插入否则更新

由于主键或者唯一键对应的值已经存在而导致插入失败。如下所示↓↓↓

#主键冲突
insert into students values(100, 20001, '小华', '222333');
#唯一键冲突
insert into students(sn, name, qq) values(10001, '小利', '888999');

在这里插入图片描述
如果我们希望某个数据如果存在，插入值和表中数据不同，则更新，相同则不更新；不存在则插入。此时就可以选择插入否则更新，语法如下：

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

下面尝试插入1组和表中数据一模一样的数据（发生冲突时，修改的数据也和原表中数据相同）↓↓↓

insert into students values(102, 10002, '小树', '456789') on duplicate key update sn=10002, name='小树';

在这里插入图片描述
★ps：此时虽然发生了主键、唯一键冲突，而执行update操作。但由于update的数据与表原先的数据相同，故mysql显式0 rows addected（0行受影响）。

下面插入表中已经存在的数据，如果存在该数据，则更新为其他数值的数据。↓↓↓

select * from students;
insert into students values(101, 10001, '小光', '123456') on duplicate key update sn='20001', name='小光同学';

在这里插入图片描述
★ps：由于插入的数据与表中的数据发生冲突（主键及唯一键冲突），导致执行了更新操作。mysql尝试插入时，虽然没有插入成功，但也算影响了1行；后序更新发生冲突的行数据，也算影响了1行，因而，总共影响了2行（2 rows affected）。

如果插入一行原表中不存在的数据呢？

insert into students values(107, 10006, '小天', '888999') on duplicate key update sn=20006, name='小天同学', qq='444555';
select * from students;

在这里插入图片描述
★ps：由于此时没有发生主键或唯一键冲突，故不会有尝试插入失败，而导致的1 rows affected；而是直接插入数据，此时插入数据总共影响1行，故插入数据后显式1 rows affcted。

综上，可以得出如下结论：
● 0 rows affected：表中有冲突数据，但冲突数据的值和update的值相等；
● 1 rows affected：表中没有冲突数据，数据被插入；
● 2 rows affected：表中有冲突数据，并且数据已经被更新。

★ps：mysql中可以通过select row_count();来查看收到影响的数据行数

在这里插入图片描述

替换

替换时，如果没有发生主键或唯一键冲突，则直接插入；如果发生主键或唯一键冲突，则删除原数据后，再插入。

#没有发生冲突的情况
select * from students;
replace into students(sn, name, qq) values('10006', '阿布', '999999');

在这里插入图片描述

#发生冲突的情况
replace into students(id, sn, name, qq) values(108, 10006, '勇敢阿布', '666666');
select * from students;

在这里插入图片描述

Retrieve（读取）

语法：

select [distinct] from table_name [where ...] [order by column [asc | desc], ...] limit ...

案例：
下方介绍的各个操作，将基于下方表↓↓↓

create table exam_result(
id int unsigned primary key auto_increment,
name varchar(32) not null comment '姓名',
chinese float default 0.0 comment '语文成绩',
math float default 0.0 comment '数学成绩',
english float default 0.0 comment '英语成绩'
);
desc exam_result;

在这里插入图片描述
插入exam_result数据↓↓↓

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); 
select * from exam_result;

在这里插入图片描述

select 列

全列查询

如果我们想显式每个列的数据，可以使用全列查询。（即不指定显式哪些列）

select * from exam_result;

在这里插入图片描述
★ps：通常情况下不建议使用*进行全列查询：一方面，查询的列越多，意味着需要传输的数据两越大（我们只需要某些列的数据时，指定需要传输的列数据，可以减少数据的传输，从而提高效率）；另一方面，全列查询会影响到索引的使用（将于后序文章讲解）。

指定列查询

在select关键字后，指定需要实现的列的名称，即可进行列查询。指定的列名称顺序不需要按照定义表的顺序来。


select id, name, math from exam_result;
select name, id, english from exam_result;

在这里插入图片描述

查询字段为表达式

情况一：select中表达式不包含任何字段

select id, name, 'jammingpro' from exam_result;

在这里插入图片描述
情况二：select中表达式包含一个表的字段

select id, name, english + 10 from exam_result;

在这里插入图片描述
情况三：select中表达式包含多个表的字段

select id, name, chinese+math+english from exam_result;

在这里插入图片描述

为查询结果指定别名

上面虽然使用了select显式了每个学生的总分成绩，但表格的总分字段显式为chinese+math+english，可不可以将该字段名改为“总分”呢？这就需要使用到为查询结果指定别名了↓↓↓

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

上面多字段表达式查询，可以改成如下这样↓↓↓

select id, name, chinese+math+english as '总分' from exam_result;

在这里插入图片描述

结果去重

如果需要显式当前所有学生的数学成绩，且不显式数值相同的数学成绩，那应该怎么做呢？

select math from exam_result;

在这里插入图片描述
从上图可知，存在两个数学成绩同为98的查询结果。可以通过在select关键字后添加distinct，来去除重复的数据↓↓↓

select distinct math from exam_result;

在这里插入图片描述

where条件

我们在查询数据时，会带有一定的条件，如：显式姓孙的同学，数学成绩大于60的同学等。如果要实现条件查询，就需要使用where子句。在开始介绍where子句前，先来认识一下mysql中的运算符↓↓↓

比较运算符

运算符	说明
>,>=,<,<=	大于，大于等于，小于，小于等于
=	等于，NULL不安全，例如NULL=NULL的结果是NULL
<=>	等于，NULL安全，例如NULL<=>的结果是TRUE(1)
!=,<>	不等于
BETWEEN a0 AND a1	范围匹配，[a0, a1]，如果a0<=value<=a1，返回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)

下面使用多个案例介绍上述运算符及where自己↓↓↓
案例1： 英语不及格的同学及英语成绩

select name, english from exam_result where english < 60;

在这里插入图片描述
案例2： 语文成绩在[80,90]分的同学及语文成绩

#and解法
select name, chinese from exam_result where chinese >= 80 and chinese <= 90;
#between...and...解法
select name, chinese from exam_result where chinese between 80 and 90;

在这里插入图片描述
案例3： 数学成绩是58或者59或者98或者99分的同学及数学成绩

#or解法
select name,math from exam_result where math=58 or math=59 or math=98 or math=99;
#使用in条件解法
select name,math from exam_result where math in (58,59,98,99);

在这里插入图片描述
案例4： 姓孙的同学及孙某同学

# %匹配任意多个（包括0个）字符---查找姓孙的同学
select name from exam_result where name like '孙%';
# _匹配一个字符---查找孙某同学
select name from exam_result where name like '孙_';

在这里插入图片描述
案例5： 语文成绩好于英语成绩的同学

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

在这里插入图片描述
案例6： 总分在200分以下的同学

select name,chinese+math+english as '总分' from exam_result where chinese+math+english<200;

在这里插入图片描述
★ps：上面的SQL语句对chinese+math+english取了别名“总分”，但别名无法在where子句中使用。在SQL执行过程中，先使用where子句将用于需要的结果选出来，再按用户显式需求进行显式，取别名属于显式需求，故无法在where子句中使用。

案例7： 语文成绩>80并且不姓孙的同学

select name, chinese from exam_result where chinese>80 and name not like '孙%';

在这里插入图片描述
案例8： 显式孙某同学，及总成绩>200且语文成绩<数学成绩且英语成绩>80

select name, chinese, math, english, chinese+math+english as '总分' from exam_result 
where name like '孙_' 
or 
(chinese+math+english>8 and chinese<math and english > 80);

在这里插入图片描述
案例9： 该案例介绍关于NULL的查询
● 创建test表，并插入数据↓↓↓

create table test(
id int unsigned primary key auto_increment,
name varchar(32) not null,
qq char(20) unique
);
desc test;
insert into test(name, qq) values('子鼠', null);
insert into test(name, qq) values('丑牛', '111111');
insert into test(name, qq) values('寅虎', null);
insert into test(name, qq) values('卯兔', null);

在这里插入图片描述

查询qq号是空的同学，查询qq号不为空的同学

#查询qq号是空的同学
select * from test where qq is null;
#查询qq号不为空的同学
select * from test where qq is not null;

在这里插入图片描述
★ps：关于=和<=>的区别，及NULL的比较

# NULL的比较
select null=null, null=1, null=0;
# =和<=>的区别
select null<=>null, null<=>1, null<=>0;

在这里插入图片描述
由上可知：null和任何数计算，表达式结果都是null；=不能对null进行比较，<=>可以对null进行比较。

结果排序

语法：

select * from table_name [where ...] order by column[asc | desc], [...];

★ps：asc为升序（从小到大），desc为降序（从大到小），默认为asc。没有使用order by子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序。

下面通过多组案例，对order by的用法做出介绍↓↓↓
案例1： 显式学生姓名及数学成绩，按数学成绩升序显式

select name, math from exam_result order by math [asc];
# 默认是升序的，加不加asc都可以

在这里插入图片描述
案例2： 对test表（含学生id，学生姓名，学生qq号字段），显式学生姓名和qq号，按qq号升序和降序排列

# qq号升序
select name, qq from test order by qq asc;

在这里插入图片描述

# qq号降序
select name, qq from test order by qq desc;

在这里插入图片描述
★ps：NULL比任何数值都小，也比“”（空串）小。
案例3： 查询学生的各科成绩，依次按数学降序英语升序的方式显式

select name, math, english, chinese from exam_result order by math desc, english asc;

在这里插入图片描述
案例4： 查询学生及总分，按总分从高到低显式

select name,chinese+math+english '总分' from exam_result order by chinese+math+english desc;

在这里插入图片描述
对于order by子句，可以使用别名↓↓↓

select name,chinese+math+english '总分' from exam_result order by '总分' desc;

在这里插入图片描述
★ps：where子句中不能使用别名，order by子句中可以使用别名。
案例5： 查询姓孙和姓曹的同学的数学成绩，按数学成绩由高到低显式

select name,math from exam_result where name like '孙%' or name like'曹%' order by math desc;

在这里插入图片描述

筛选分页结果

语法：

# 分页显式时使用的行号，起始从第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;

案例1： 从第0行开始，显式5行

select * from exam_result limit 5;

在这里插入图片描述
案例2： 使用用法2，对exam_result分页显式，每页3条数据

select * from exam_result limit 0,3;
select * from exam_result limit 3,3;
select * from exam_result limit 6,3;

在这里插入图片描述
案例3： 使用用法3，对exam_result分页显式，每页3条数据

select * from exam_result limit 3 offset 0;
select * from exam_result limit 3 offset 3;
select * from exam_result limit 3 offset 6;

在这里插入图片描述

🎈欢迎进入半夜学习MySQL专栏，查看更多文章。
如果上述内容有任何问题，欢迎在下方留言区指正b(￣▽￣)d