【MySQL】表的增删查改-阿里云开发者社区

思维导图

学习目标

CRUD：Create（创建），Retrieve（读取），Update（更新），Delete（删除）

一、Create（创建）

1.1 语法

insert into 表名 [选项1，选项2，选项3……] values (value_list);

举个例子：创建一张学生表：

CREATE TABLE students (
              id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
              sn INT NOT NULL UNIQUE COMMENT '学号',
              name VARCHAR(20) NOT NULL,
              qq VARCHAR(20)
        );

1.2 单行数据 + 全列插入

插入两条记录，value_list的数量必须和定义表中的数量及顺序一致。注意，在插入的时候，也可以不用指定id（因为id是自增长，但是我们也是可以对id进行重新赋值），那么mysql会使用默认的值进行自增。

insert into student values(100, 1000, '唐三藏', NULL);
insert into student values(101, 1001, '孙悟空', '1111');

1.3 多行数据 + 指定列插入

插入两条记录，value_list数量必须和指定列数量即顺序是一致的。

insert into student(sn, name) values(20001, '曹孟德');

1.4 插入否则更新

1.4.1 概念

由于主键或者唯一键对应的值已经存在而导致插入失败：

主键冲突
唯一键冲突

我们在插入数据时，可以选择性的进行同步更新操作。其语法：

insert .... on  duplicate key update 字段1 = XXX, 字段2 = XXX

1.4.2 我们可以根据插入后的提示来了解是否有数据冲突，数据是否已经更新？

0 row affected:   表中有冲突数据，但冲突数据的值和update的值相同
 
1 row affected:   表中没有冲突数据，数据被插入
 
2 row affected:   表中有冲突数据，并且数据已经被更新

同样，我们可以根据MySQL函数来获取受到影响的数据行数：

select ROW_COUNT();

1.5 替换

语法：

replace into students (sn, name) values (20001, '张三');

这个语句的用法和1.4 中的语句的用法差不多，我们可以使用1.5中的语句进行冲突的更新，因为代码短。

主键或者唯一键没有冲突，则直接插入
主键或者唯一将如果有冲突，则删除后再插入
1 row affected ：表中没有数据冲突，数据被插入
2 row affected ：表中有数据冲突，删除后重新插入

二、Retrieve（读取）

2.1 语法：

SELECT
     [DISTINCT] {* | {column [, column] ...} //字段名字
     [FROM table_name]     // 数据表
     [WHERE ...]                    //条件
     [ORDER BY column [ASC | DESC], ...]  // 排序操作
     LIMIT ...        // 进行分页操作

创建一个例子：创建表结构

构建一个列子：-- 创建表结构
CREATE TABLE exam_result (
          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 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);

2.2 SELECT 列

2.2.1 全列查询

select * from 表名

通常条件下，不建议使用 * 进行全列查询：

查询的列越多，意味着需要传输的数据量越大
可能会影响到索引的使用

2.2.2 指定列查询

语法：

select 字段1(字段2，字段3……)  from 表名;  // 指定列的顺序不需要按照定义表的顺序来

2.2.3 查询字段为表达式

select 字段1，字段2…… 10 from 表名;  // 表达式中不含任何字段

表达式包含一个字段

表达式也可以包含多个字段

2.2.4 为查询结果指定别名

语法：

select id, name, chinese + math + english 总分 from 表名;

2.2.5 结果去重

语法：

select distinct 字段 from 表名;

2.3 WHERE 条件

2.3.1 比较运算符

运算符	说明
>, >=, <, <=	大于，大于等于，小于，小于等于
=	等于，NULL不安全，例如 NULL = NULL 的结果是 NULL
<=>	等于，NULL安全，例如 NULL <=> NULL 的结果是 TRUE(1)
!=, <>	不等于
between a0 and a1	范围匹配，[a0, a1] ，如果 a0 <= value <= a1，返回 TRUE(1)
in (option,……)	如果是 option 中的任意一个，返回 TRUE(1)
is null	是 NULL
is not null	不是 NULL
like	模糊匹配， % 表示任意多个（包括0个）任意字符；_ 表示任意一个字符

2.3.2 逻辑运算符

运算符	说明
AND	多个条件必须都为TRUE(1)，结果才是TRUE(1)
OR	任何一个条件为TRUE(1)，结果为TRUE(1)
NOT	条件为TRUE(1)，结果为FALSE(0)

2.3.3 一些基本操作

2.3.3.1 英语不及格的同学及英语成绩

select name, english from exam_result where english < 60;

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

select name, chinese from exam_result where chinese >= 80 and chinese <= 90;
select name, chinese from exam_result where chinese between 80 and 90;

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

select name, math from exam_result where math = 58 or math = 59 or math = 98 or math = 99;
select name, math from exam_result where matn in (58, 59, 98, 99);

2.3.3.4 姓孙的同学及孙某同学

select name from exam_result where name like '孙%';
select name from exam_result where name like '孙_';

2.3.3.5 语文成绩好于英语成绩的同学

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

2.3.3.6 总分在200分以下的同学

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

2.3.3.7 语文成绩 > 80 并且不姓孙的同学

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

2.3.3.8 孙某同学，或者要求总成绩 > 200 并且语文成绩 < 数学成绩并且英语成绩 > 80

select name, chinese, math, english, chinese + math + english 总分 from exam_result where name like '孙%‘ or (chinese + math + english > 200 and chinese < math and english > 80);

2.3.4 NULL的查询

NULL 和 NULL 的比较， = 和 <=> 的区别：

2.4 结果排序

语法：

--ASC为升序（从小到大） --DESC为降序（从大到小）默认为ASC

SELECT ... FROM table_name [WHERE ...] ORDER BY column [ASC|DESC], [...];

注意：没有 order by 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序。

2.4.1 一些基本操作：

2.4.1.1 同学及数学成绩，按数学成绩升序表示

 select name, math from exam_result order by math;
select name, math from exam_result order by math asc;

2.4.1.2 同学及qq号，按qq号排序显示

select name, qq_num from exam_result order by qq_num;
select name, qq_num from exam_result order by qq_num asc;

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

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

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

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

2.4.1.5 查询姓孙的同学或者姓曹的同学的数学成绩，结果按数学成绩由高到低排序

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

2.5 筛选分页结果

-- 起始下标为 0

-- 从 s 开始，筛选 n 条结果

SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;

-- 从 0 开始，筛选 n 条结果

SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;

-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用

SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

建议：对未知表金你修改查询时，最好加上一条 limit 1，避免因为表中数据过大，查询全表数据导致数据库卡死

三、Update（更新）

语法：

UPDATE table_name SET column = expr [, column = expr ...] [WHERE ...][ORDER BY ...] [LIMIT ...]

对查询到的结果进行列值更新

一些基本操作

3.1.1 将孙悟空同学的数学成绩变更为80分

select name, math from exam_rseult where name = '孙悟空';  // 查看原数据
update exam_result set math = 80 where name = '孙悟空';  // 数据更新

3.1.2 将曹孟德同学的数学成绩变更为60分，语文成绩变更为80分

1. select name, math, chinese from exam_result where name = '曹孟德';
2. update exam_result set math = 60, chinese = 80 where name = '曹孟德';

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

select name, chinese + math + english 总分 from exam_result order by 总分 asc limit 3;
update exam_result set math = math + 30 order by chinese + math + english asc limit 3;

3.1.4 将所有的同学的语文成绩更新为原来的2倍（更新全表的语句慎用）

update exam_result set chinese = chinese * 2;

四、Delete（删除）

4.1 删除数据

语法：

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...];

4.2 一些基本操作

4.2.1 删除孙悟空同学的考试成绩

select * from exam_result where name = '孙悟空';  // 查看原始数据
delete from exam_result where name = '孙悟空';    // 删除数据
select * from exam_result where name = '孙悟空';  // 查看删除结果

4.2.2 删除整张表的数据（慎用）

delete from exam_result;

不会将auto_increment项进行清空！！！！

4.2 截断表（这个操作慎用）

语法：

TRUNCATE [TABLE] table_name;

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

【MySQL】表的增删查改