MYSQL增删改查操作

目录

一、Create（增）：

 1.单行数据全列插入：

  基本语法：

 例子：

 2.单行数据指定列插入：

基本语法：

例子：

3.多行数据指定列插入：

基本语法：

例子：

二、Retrieve （查）：

1.全列查询：

基本语法：

例子： 

2.指定列查询：

基本语法：

 例子：

 3.查询字段为常量表达式：

基本语法：

例子：

4.为查询结果指定别名：

基本语法：

例子：

5.结果去重查询：

基本语法：

例子：

6.Where 条件查询：

比较运算符：

逻辑运算符：

基本语法：

几个使用的例子：

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

 2.查询语文成绩高于英语成绩的同学:

 3.总分在 200 分以下的同学：

4.查询语文成绩大于80分且英语成绩大于80分的同学：

 5.查询语⽂成绩⼤于80分或英语成绩⼤于80分的同学:

6.（重点）逻辑运算符 and 和 or 的优先级：

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

8.数学成绩是 78 或者 79 或者 98 或者 99 分的同学及数学成绩：

 9.（重点）模糊查询：

 10. null 值的查询：

查询特定列名为 null 的数据行：

查询特定列名不为 null 的数据行：

要注意：

7.Order by排序：

基本语法：

例子：

1.按数学成绩从低到高排序(升序)：

2.按语文成绩从高到低排序(降序)：

3.可以使⽤列的别名进⾏排序：

8.分页查询：

基本语法：

例子：

1.查询第一页数据：

2.获取数学分数排名在中间第3，4，5的同学：

3.获取数学成绩由低到高的同学排名：

三、Update（改）：

基本语法：

例子：

1.将曹孟德同学的数学成绩变更为 60 分，语⽂成绩变更为 70 分：

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

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

四、Delete（删）：

 基本语法：

例子：

1. 删除孙悟空同学的考试成绩：

2.删除整张表的数据列：

总结：

一、Create（增）：

 这有个用于演示的表（表名为users）：

create table users(
  id bigint comment '编号',
  name varchar(32) comment '用户名'
);

 1.单行数据全列插入：

  基本语法：

insert into 表名 values (数据1，数据2，数据3，......);

 要注意的是，后面的数据1，和数据2等等，要与当前表的全部列名数据顺序，个数相同。

 例子：

insert into users values (1,'张三');

 经过查询得到：

 2.单行数据指定列插入：

基本语法：

insert into 表名 (列名，列名，......) values (数据1，数据2，......);

 要注意的是，列名的个数和顺序，后面的数据1，数据2要与之对应。

例子：

insert into users (name) values ('李四');

 经过查询得到：

 至于为什么李四前面的 id 为 null，因为如果当前的列可以为空，并且插入数据时没有给到，就默认是 null 了。

3.多行数据指定列插入：

基本语法：

insert into 表名 (列名1,列名2，...) values (第一个对象数据1,第一个对象数据2),(第二个对象数据1,第二个对象数据2),......;

例子：

insert into users (id,name) values (3,'王五'),(4,'老刘'),(5,'老黄');

经过查询得到：

 可以看到，这种方法可以插入多行数据。

二、Retrieve （查）：

先创建一张表，再往表添加数据：

-- 创建表结构
 CREATE TABLE 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);

1.全列查询：

基本语法：

select * from + 表名

例子： 

对上面的表运行此段语句：

select * from exam;

得到：

 如图，全列查询结果通常是一个包含表中所有记录的结果集。

2.指定列查询：

基本语法：

select 列名1,列名2,......from 表名

 在select后面的查询列表中指定希望查询的列，可以是⼀个也可以是多个，中间用逗号隔开。

 例子：

select id,name,math from exam;

查询结果：

 3.查询字段为常量表达式：

基本语法：

select 表达式1, 表达式2,...... from exam;

 查询列表中的表达式可以是表中不存在的值或者列，执行命令后返回的结果是一个临时表中的数据。

        临时表是一种特殊的数据库表，它的生命周期通常比较短暂，主要用于临时存储数据，以满足特定的查询或处理需求。使用完后可以自动或手动删除。

例子：

比如我想知道所有人的语文成绩加上10后的结果：

select id,name,chinese,chinese + 10 from exam;

查询得到：

4.为查询结果指定别名：

基本语法：

select 表达式 as 别名 from exam;

例子：

比如我想把他们三科的总分加起来展示出来：

select id ,name, chinese + math + english as '三科总分' from exam;

查询结果：

 可以看到，chinese + math + english 表达式 结果给得到了 '总分' 这个字符串，结果变得更可观了。

5.结果去重查询：

基本语法：

select distinct 列名 from exam;

 例子下面有注意事项。

例子：

经过查询math这个表的数学成绩有两个人是98分的：

通过这条语句后：

select distinct math from exam;

得到： 

可以看到，只有一个 98 数字出现了。

但是值得注意的是：

select distinct id,math from exam;

结果：

去重时，要求给出的条件列的值都是对应相同的，才会被mysql判定为重复记录。 

6.Where 条件查询：

在此之前，先了解一下几张表格：

比较运算符：

逻辑运算符：

基本语法：

select column1, column2, ... from table_name where condition;

• column1, column2, ...：要查询的列名，可以是单个列或多个列，用逗号分隔；若要查询所有列，可使用 *。
• table_name：要查询数据的表名。
• condition：筛选记录的条件，可以是比较条件、逻辑条件(上面已介绍)等。

几个使用的例子：

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

select name,english from exam where english < 60;

 2.查询语文成绩高于英语成绩的同学:

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

 3.总分在 200 分以下的同学：

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

4.查询语文成绩大于80分且英语成绩大于80分的同学：

select name,chinese,english from exam where chinese > 80 and english > 80;

 5.查询语⽂成绩⼤于80分或英语成绩⼤于80分的同学:

select name,chinese,english from exam where chinese > 80 or english > 80;

6.（重点）逻辑运算符 and 和 or 的优先级：

比如有这么一条语句：

select * from exam where chinese > 80 or math > 70 and english > 70;

还有这条语句：

select * from exam where (chinese > 80 or math > 70) and english > 70;

可以看到，查询的结果不一样， 因为运算符 and 优先级高于 or 。

如果想要改变默认的优先级顺序，可以使用括号来明确指定计算顺序。括号内的条件会先被计算。

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

这两种语句都可以得到相同的结果：

-- 使用 and 实现
select name,chinese from exam where chinese >= 80 and chinese <= 90;

-- 使用between  and 实现
select name,chinese from exam where chinese between 80 and 90;

8.数学成绩是 78 或者 79 或者 98 或者 99 分的同学及数学成绩：

这两种语句都可以得到相同的结果：

-- 使用 or 实现
select name,math from exam where math = 78 or math = 79 or math = 98 or math = 99;

-- 使用 in 实现
select name,math from exam where math in (78,79,98,99);

 9.（重点）模糊查询：

在此之前，先了解：

使用 like 配合通配符：

% ：匹配任意数量（包括0个）的任意字符。

_  ：匹配单个字符。

% 和 _ 可以在前面，可以在后面，下面演示的是在后面的效果：

查询所有姓孙的同学：

select name from exam where name like '孙%';

 --------------------------------------------------------------------------------------------------------------------------------

 查询姓孙且姓名共有两个字同学：

select name from exam where name like '孙_';

 10. null 值的查询：

查询特定列名为 null 的数据行：

select column1, column2, ... from table_name where column_name is null;

查询特定列名不为 null 的数据行：

select column1, column2, ... from table_name where column_name is not null;

• column1, column2, ...：要查询的列名。
• table_name：要查询数据的表名。
• column_name：用于判断是否为 NULL 的列名。

要注意：

1.  要使用 is （not） null 或 <=> 这个运算符来判断。

2. null 代表未知值或缺失值，当它与其他值进行运算时，结果通常也为 null 。

7.Order by排序：

基本语法：

select column1, column2, ...
from table_name
[where condition]
order by column1 [ASC|DESC], column2 [asc|desc], ...;

• column1, column2, ...：要查询的列名。
• table_name：要查询数据的表名。
• where condition（可选）：筛选记录的条件。
• order by：指定排序的列和排序顺序。
• asc（可选）：表示升序排列，是默认的排序方式。
• desc（可选）：表示降序排列。

例子：

1.按数学成绩从低到高排序(升序)：

select * from exam order by math asc; 

2.按语文成绩从高到低排序(降序)：

select * from exam order by chinese desc;

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

select * from exam order by math desc,english asc,chinese asc; 

 值得注意的是：

对多个列进行排序的时候，后面的排序规则，是在前一个排序规则的基础上进行的。

对上面的列来说，先排序好math，在math排序好的基础上排序英语，最后在前两个排序完的基础上排序语文。

3.可以使⽤列的别名进⾏排序：

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

 可以看到，我们使用了别名 '总分' 来对他们的三科的分数总和进行了由大到小的排序。

注意：

null 进行排序时，视为比任何值都小，升序出现在最上面，降序出现在最下面。

8.分页查询：

基本语法：

select column1, column2,...
from table_name
[where condition]
limit offset, count;

• column1, column2,...：需要查询的列名。
• table_name：表名。
• WHERE condition（可选）：筛选条件。
• offset：表示偏移量，即从第几行开始获取数据（不包含当前offset这个数据），如果省略则默认为 0。
• count：表示要获取的记录数量，即每页显示的记录数。

还有一种写法：

select ... from table_name [where ...] [order by ...] limit num offset start;

 从 start 开始，筛选 num 条结果（不包含当前start这个数据），用法更明确，建议使用。

例子：

1.查询第一页数据：

-- 第一种写法
select * from exam limit 0, 4;

-- 第二种写法
select * from exam limit 4;

这样就能得到我们按照插入数据的排序的前四个数据了。

2.获取数学分数排名在中间第3，4，5的同学：

select id,name,math from exam order by math asc limit 2,3;

3.获取数学成绩由低到高的同学排名：

select id,name,math from exam order by math asc limit 7 offset 0;

三、Update（改）：

基本语法：

 update [LOW_PRIORITY] [IGNORE] table_reference
 set assignment [, assignment] ...
 [where where_condition]
 [order by ...]
 [limit row_count]

update 表名 set 是基本语法，有需要则可以加上对数据的 条件判断（where），数据排序（order by），limit（显示多少条数据）。

例子：

1.将曹孟德同学的数学成绩变更为 60 分，语⽂成绩变更为 70 分：

update exam set math = 60,chinese = 70 where name = '曹孟德';

前后结果对比：

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

update exam set math = math + 30 
where chinese + math + english is not null 
order by chinese + math + english asc 
limit 3;

在 SQL 里，当 null 参与算术运算时，结果通常也为 null。比如，若 chinese 列的值为 null，那么不管 math 和 english 列的值是多少，chinese + math + english 的结果都会是 null。如果不排除 null 值，可能会导致在排序和后续更新操作时出现不符合预期的情况。 

修改前后对比：

    前                                                                           后

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

update exam set chinese = chinese * 2;

修改前后对比：

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

四、Delete（删）：

 基本语法：

delete from table_name
[where condition];

• table_name：要删除数据的表名。
• where 子句（可选）：用于指定删除数据的条件。如果省略该子句，delete 语句将删除表中的所有行。(谨慎操作) !

例子：

1. 删除孙悟空同学的考试成绩：

delete from exam where name = '孙悟空';

这样name为孙悟空这一列数据就删除了。

2.删除整张表的数据列：

delete from exam;

总结：

增：insert into 表名 

删：delete from表名

改：update 表名

查：select 列名 from 表名

可以根据需要，在此基础上后面加上 where（条件判断），order by（结果排序），limit（显示的数据量）。