尧图网页设计 
目录本文内容概要一、认识 CRUD二、新增数据INSERT2.1 INSERT 基本语法2.2 全列插入2.3 指定列插入2.4 一次插入多条数据2.5 插入冲突则更新ON DUPLICATE KEY UPDATE2.6 替换数据REPLACE三、查询数据SELECT3.1 SELECT 基本语法3.2 查询全部字段3.3 查询指定字段3.4 查询字段起别名3.5 查询结果去重DISTINCT3.6 查询表达式四、条件筛选WHERE4.1 WHERE 基本语法4.2 比较运算符4.3 逻辑运算符4.4 范围查询BETWEEN ... AND ...4.5 集合查询IN / NOT IN4.6 模糊查询LIKE4.7 NULL 值判断IS NULL / IS NOT NULL五、查询结果排序ORDER BY5.1 ORDER BY 基本语法5.2 多字段排序六、限制查询结果LIMIT6.1 LIMIT 基本语法6.2 指定查询起始位置6.3 SELECT 语句的逻辑执行顺序七、更新数据UPDATE7.1 UPDATE 基本语法7.2 更新一个或多个字段7.3 更新表中全部数据八、删除数据DELETE8.1 DELETE 基本语法8.2 条件删除8.3 删除表中全部数据8.4 截断表TRUNCATE本文内容概要本文主要介绍 MySQL 中数据的增删改查操作。通过本文的学习需要掌握 INSERT 数据插入、SELECT 数据查询、UPDATE 数据更新以及 DELETE 数据删除等基本操作理解 WHERE 条件筛选、ORDER BY 查询结果排序、LIMIT 结果限制与分页查询的基本用法掌握 DISTINCT 去重、BETWEEN 范围查询、IN 集合查询、LIKE 模糊匹配以及 NULL 值判断等常用查询语法同时了解 REPLACE、ON DUPLICATE KEY UPDATE 和 TRUNCATE 等相关操作并对基础 SELECT 语句的逻辑执行顺序进行总结为后续学习聚合查询、多表查询以及子查询等进阶 SQL 内容打下基础。一、认识 CRUDCRUD 是数据库中最基本的四类数据操作分别对应数据的新增、查询、修改和删除。CRUD 由四个英文单词的首字母组成Create 创建向数据表中插入新的数据Retrieve读取从数据表中查询所需要的数据Update 更新修改数据表中已经存在的数据Delete 删除删除数据表中已有的数据因此对于一张已经创建完成的数据表我们日常最主要的操作基本都是 CRUD。二、新增数据INSERT2.1 INSERT 基本语法INSERT [INTO] 表名 [(列属性, 列属性, ...)] VALUES [(值, 值, ...)]示例在学生表中插入学生的相关信息create table student( - id int unsigned primary key, - name varchar(20) not null, - age int - ); desc student; ---------------------------------------------------- | Field | Type | Null | Key | Default | Extra | ---------------------------------------------------- | id | int(10) unsigned | NO | PRI | NULL | | | name | varchar(20) | NO | | NULL | | | age | int(11) | YES | | NULL | | ----------------------------------------------------2.2 全列插入insert into student values (1, 张三, 18); insert student values (2, 李四, 20); select * from student; ------------------ | id | name | age | ------------------ | 1 | 张三 | 18 | | 2 | 李四 | 20 | ------------------ 2 rows in set (0.00 sec)INTO 在 MySQL 中可以省略不写其中值列表信息必须把所有列属性全部填充。2.3 指定列插入insert into student (id, name) values (1, 张三); select * from student; ------------------ | id | name | age | ------------------ | 1 | 张三 | NULL | ------------------ 1 row in set (0.00 sec)其他未被指定的列必须存在默认值或者主键自增值。2.4 一次插入多条数据insert into student values (1, 张三, 18), (2, 李四, 19); insert into student (id, name) values (3, 王五), (4, 赵六); select * from student; ------------------ | id | name | age | ------------------ | 1 | 张三 | 18 | | 2 | 李四 | 19 | | 3 | 王五 | NULL | | 4 | 赵六 | NULL | ------------------ 4 rows in set (0.00 sec)一次插入多条数据时既可以全列插入也可以指定列插入。数据之间以逗号分割。2.5 插入冲突则更新ON DUPLICATE KEY UPDATE在插入数据时插入的新数据可能由于主键或者唯一键冲突导致插入失败。insert into student values (1, 张三, 18); Query OK, 1 row affected (0.00 sec) insert into student values (1, zhangsan, 20); ERROR 1062 (23000): Duplicate entry 1 for key PRIMARY此时我们可以选择性的进行同步更新数据INSERT ... ON DUPLICATE KEY UPDATE column value [, column value] ...select * from student; ------------------ | id | name | age | ------------------ | 1 | 张三 | 20 | ------------------ 1 row in set (0.00 sec) insert into student values (1, zhangsan, 20) on duplicate key update name zhangsan, age 20; Query OK, 2 rows affected (0.14 sec) insert into student values (2, lisi, 16) on duplicate key update nameme lisi, age 16; Query OK, 1 row affected (0.00 sec) insert into student values (2, lisi, 16) on duplicate key update name lisi, age 16; Query OK, 0 rows affected (0.00 sec) select * from student; -------------------- | id | name | age | -------------------- | 1 | zhangsan | 20 | | 2 | lisi | 16 | -------------------- 2 rows in set (0.00 sec)在使用插入否则更新的操作时我们可以通过 MySQL 响应看到操作对于表的影响Query OK, 0 rows affected (0.00 sec) 表中有冲突数据但冲突数据的值与 update 值相同Query OK, 1 row affected (0.00 sec)表中没有冲突数据直接插入Query OK, 2 rows affected (0.14 sec)表中有冲突数据并且数据已经被更新补充通过 MySQL 函数获取上次操作影响的数据行数insert into student values (2, lisi, 16) on duplicate key update name lisi, age 16; Query OK, 0 rows affected (0.00 sec) select row_count(); ------------- | row_count() | ------------- | 0 | ------------- 1 row in set (0.00 sec) insert into student values (2, lisi, 15) on duplicate key update name lisi, age 15; Query OK, 2 rows affected (0.00 sec) select row_count(); ------------- | row_count() | ------------- | 2 | ------------- 1 row in set (0.00 sec)2.6 替换数据REPLACE在插入新数据时我们可以采用 REPLACE 方式进行插入新数据。当新数据发生主键或者唯一键冲突时REPLACE 方式会先删除旧数据再插入新数据。基本语法REPLACE [INTO] 表名 [(列属性, 列属性, ...)] VALUES [(值, 值, ...)]REPLACE 也会支持全列插入、指定列插入和一次插入多条数据replace into student values (1, 张三, 18); Query OK, 1 row affected (0.00 sec) select * from student; ------------------ | id | name | age | ------------------ | 1 | 张三 | 18 | ------------------ 1 row in set (0.00 sec) replace student values (1, zhangsan, 20); Query OK, 2 rows affected (0.00 sec) replace student values (1, zhangsan, 20); Query OK, 1 row affected (0.00 sec) select * from student; -------------------- | id | name | age | -------------------- | 1 | zhangsan | 20 | -------------------- 1 row in set (0.00 sec)REPLACE 新增记录时影响 1 行发生唯一键冲突并替换旧记录时影响 2 行。需要注意在 InnoDB 中当新旧记录完全相同时可能出现影响 1 行的情况因此不建议仅通过 affected rows 判断 REPLACE是否发生了数据替换。三、查询数据SELECT3.1 SELECT 基本语法SELECT [DISTINCT] * | 列属性, 列属性, ... FROM 表名示例构建学生表准备学生表数据create table student( - id int unsigned primary key auto_increment, - name varchar(20) not null, - math tinyint unsigned, - english tinyint unsigned, - chinese tinyint unsigned - ); desc student; ------------------------------------------------------------------ | Field | Type | Null | Key | Default | Extra | ------------------------------------------------------------------ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | math | tinyint(3) unsigned | YES | | NULL | | | english | tinyint(3) unsigned | YES | | NULL | | | chinese | tinyint(3) unsigned | YES | | NULL | | ------------------------------------------------------------------ 5 rows in set (0.01 sec) insert into student (name, math, english, chinese) values (张三, 88, 92, 93); insert into student (name, math, english, chinese) values (李四, 82, 95, 77); insert into student (name, math, english, chinese) values (王五, 59, 34, 88); select * from student; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 88 | 92 | 93 | | 2 | 李四 | 82 | 95 | 77 | | 3 | 王五 | 59 | 34 | 88 | ------------------------------------ 3 rows in set (0.00 sec)3.2 查询全部字段select * from student; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 88 | 92 | 93 | | 2 | 李四 | 82 | 95 | 77 | | 3 | 王五 | 59 | 34 | 88 | ------------------------------------ 3 rows in set (0.00 sec)3.3 查询指定字段select id, name, math from student; ------------------ | id | name | math | ------------------ | 1 | 张三 | 88 | | 2 | 李四 | 82 | | 3 | 王五 | 59 | ------------------ 3 rows in set (0.00 sec) select name, math, id from student; ------------------ | name | math | id | ------------------ | 张三 | 88 | 1 | | 李四 | 82 | 2 | | 王五 | 59 | 3 | ------------------ 3 rows in set (0.00 sec)显示顺序与指定顺序相关3.4 查询字段起别名基本语法字段名 [AS] 别名select id as 学号, name 姓名, math 数学 from student; ------------------------ | 学号 | 姓名 | 数学 | ------------------------ | 1 | 张三 | 88 | | 2 | 李四 | 82 | | 3 | 王五 | 59 | ------------------------ 3 rows in set (0.00 sec) select * from student; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 88 | 92 | 93 | | 2 | 李四 | 82 | 95 | 77 | | 3 | 王五 | 59 | 34 | 88 | ------------------------------------ 3 rows in set (0.01 sec)注别名只是显示效果起别名不会修改列属性3.5 查询结果去重DISTINCT示例准备一张学生表查询学生表中有哪些专业select * from student; ---------------------------------- | id | name | age | major | ---------------------------------- | 1 | 张三 | 18 | 计算机 | | 2 | 李四 | 19 | 计算机 | | 3 | 王五 | 20 | 软件工程 | | 4 | 赵六 | 18 | 计算机 | | 5 | 小红 | 19 | 软件工程 | | 6 | 小明 | 20 | 人工智能 | ----------------------------------select major from student; ---------- | major | ---------- | 计算机 | | 计算机 | | 软件工程 | | 计算机 | | 软件工程 | | 人工智能 | ---------- select distinct major from student; ---------- | major | ---------- | 计算机 | | 软件工程 | | 人工智能 | ---------- select distinct age, major from student; -------------------- | age | major | -------------------- | 18 | 计算机 | | 19 | 计算机 | | 20 | 软件工程 | | 19 | 软件工程 | | 20 | 人工智能 | -------------------- 5 rows in set (0.00 sec)DISTINCT 去重的是查询结果中的整行数据且 DISTINCT 支持组合去重。例如select distinct age, major from student;这里去重的是 (age, major) 这个组合而不是分别对 age 和 major 单独去重。3.6 查询表达式select * from student; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 88 | 92 | 93 | | 2 | 李四 | 82 | 95 | 77 | | 3 | 王五 | 59 | 34 | 88 | ------------------------------------ 3 rows in set (0.01 sec) select name, mathenglishchinese from student; ------------------------------ | name | mathenglishchinese | ------------------------------ | 张三 | 273 | | 李四 | 254 | | 王五 | 181 | ------------------------------ 3 rows in set (0.00 sec) select name 姓名, mathenglishchinese as 总分 from student; ---------------- | 姓名 | 总分 | ---------------- | 张三 | 273 | | 李四 | 254 | | 王五 | 181 | ---------------- 3 rows in set (0.00 sec)四、条件筛选WHERE在前面介绍的 SELECT 可以查询表中的数据但如果不添加任何限制条件则会返回表中的全部记录。而在实际开发中我们往往需要获取满足特定条件的数据。例如查询年龄大于 18 岁的学生查询计算机专业的学生查询姓名为张三或者李四的学生查询年龄处于某个范围内的学生。此时就需要 WHERE 字句对数据进行筛选。4.1 WHERE 基本语法WHERE 用于指定查询条件只要满足条件的数据才能出现到显示结果中。SELECT 字段列表 FROM 表名 WHERE 条件4.2 比较运算符运算符说明、、、大于、大于等于、小于、小于等于等于对NULL不安全例如NULL NULL的结果为NULLNULL 安全等于例如NULL NULL的结果为TRUE(1)!或者不等于示例 ---------------------------- | id | name | age | major | ---------------------------- | 1 | 张三 | 18 | 计算机 | | 3 | 王五 | 20 | 人工智能 | ---------------------------- 查询年龄大于 18 岁的学生 select * from student where age 18; 查询年龄不等于 18 岁的学生 select * from student where age ! 18; select * from student where age 18; 查询姓名为 张三 的学生: select * from student where name 张三;4.3 逻辑运算符运算符说明AND多个条件必须都为TRUE(1)结果才为TRUE(1)OR任意一个条件为TRUE(1)结果就为TRUE(1)NOT对条件结果取反例如条件为TRUE(1)结果为FALSE(0)查询年龄大于等于 18 岁并且专业为计算机的学生: select * from student where age 18 and major 计算机; 查询专业为计算机或者人工智能的学生: select * from student where major 计算机 or major 人工智能; 查询专业不是计算机的学生: select * from student where not major 计算机; 查询年龄大于等于 18 岁并且专业是计算机或者人工智能: select * from student where age 18 and (major 计算机 or major 人工智能);4.4 范围查询BETWEEN ... AND ...如果需要判断某个值是否位于指定范围内可以使用BETWEEN 最小值 AND 最大值查询年龄在 18 ~ 20 岁之间的学生 select * from student where age between 18 and 20; 也可以这样写: select * from student where age 18 and age 20; 查找年龄不在 18 ~ 20 岁之间的学生: select * from student where age not between 18 and 20;注BETWEEN ... AND ... 包含左右边界4.5 集合查询IN / NOT IN当一个字段满足多个离散值中的任意一个时如果一直使用 ORSQL 语句会比较繁琐。例如查找年龄等于 18 或者等于 20 或者等于 21 的学生: select * from student where age 18 or age 20 or age 21; 此时可以使用 IN: select * from student where age in (18, 20, 21); 查询专业为软件工程或者计算机或者人工智能的学生 select * from student where major in (软件工程, 计算机, 人工智能); 查找专业不为软件工程或者计算机或者人工智能的学生 select * from student where major not in (软件工程, 计算机, 人工智能);4.6 模糊查询LIKE在学生表信息中我们可能需要找所有姓张的学生此时就可以使用 LIKE 进行模糊匹配。通配符含义%匹配任意长度的字符可以是 0 个或多个_匹配任意一个字符查询所有姓张的学生: select * from student where name like 张%; 查找名字以三为结尾的学生: select * from student where name like %三; 查找名字中包含三的学生: select * from student where name like %三%; 查找姓张且只有两个字的学生: select * from student where name like 张_; 查找不是以张开头的学生: select * from student where name not like 张%;4.7 NULL 值判断IS NULL / IS NOT NULL在 MySQL 中NULL 表示空不参与运算和比较。假设部分学生暂时没有填写专业---------------------------- | id | name | age | major | ---------------------------- | 1 | 张三 | 18 | 计算机 | | 2 | 李四 | 20 | NULL | | 3 | 王五 | 19 | 人工智能 | ----------------------------如果想查询 major 为 NULL 的记录不能写select * from student where major NULL;而是select * from student where major is null;查询专业不为 NULL 的学生select * from student where major is not null;五、查询结果排序ORDER BY5.1 ORDER BY 基本语法ORDER BY 用于按照指定字段对查询结果进行排序。基本语法SELECT 字段列表 FROM 表名 ORDER BY 字段名 [ASC | DESC];关键字说明ASC升序排列AscendingDESC降序排列Descendingselect * from student; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 88 | 92 | 93 | | 2 | 李四 | 82 | 95 | 77 | | 3 | 王五 | 59 | 34 | 88 | ------------------------------------ 3 rows in set (0.00 sec) select * from student order by chinese; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 2 | 李四 | 82 | 95 | 77 | | 3 | 王五 | 59 | 34 | 88 | | 1 | 张三 | 88 | 92 | 93 | ------------------------------------ 3 rows in set (0.00 sec) select * from student order by chinese asc; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 2 | 李四 | 82 | 95 | 77 | | 3 | 王五 | 59 | 34 | 88 | | 1 | 张三 | 88 | 92 | 93 | ------------------------------------ 3 rows in set (0.00 sec) select * from student order by chinese desc; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 88 | 92 | 93 | | 3 | 王五 | 59 | 34 | 88 | | 2 | 李四 | 82 | 95 | 77 | ------------------------------------ 3 rows in set (0.00 sec)默认情况下ORDER BY 会按照升序进行排列。5.2 多字段排序基本语法ORDER BY 字段1 排序方式, 字段2 排序方式, ...;select * from student; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 88 | 92 | 93 | | 2 | 李四 | 82 | 95 | 77 | | 3 | 王五 | 59 | 34 | 88 | | 4 | 赵六 | 73 | 90 | 93 | ------------------------------------ 4 rows in set (0.00 sec) select * from student order by chinese desc; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 88 | 92 | 93 | | 4 | 赵六 | 73 | 90 | 93 | | 3 | 王五 | 59 | 34 | 88 | | 2 | 李四 | 82 | 95 | 77 | ------------------------------------ 4 rows in set (0.00 sec) select * from student order by chinese desc, english asc; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 4 | 赵六 | 73 | 90 | 93 | | 1 | 张三 | 88 | 92 | 93 | | 3 | 王五 | 59 | 34 | 88 | | 2 | 李四 | 82 | 95 | 77 | ------------------------------------ 4 rows in set (0.00 sec)多个字段排序时MySQL 会优先按照第一个字段排序。只有当前面的字段值相同时才会继续按照后面的字段进行排序。另外 ORDER BY 也可以和 WHERE 配合使用。查询数学成绩大于等于 90 分的学生并按照语文成绩降序排列: select * from student where math 90 order by chinese desc;六、限制查询结果LIMIT6.1 LIMIT 基本语法LIMIT 用于限制查询返回的数据条数。基本语法SELECT 字段列表 FROM 表名 LIMIT 数量;select * from student; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 88 | 92 | 93 | | 2 | 李四 | 82 | 95 | 77 | | 3 | 王五 | 59 | 34 | 88 | | 4 | 赵六 | 73 | 90 | 93 | ------------------------------------ 4 rows in set (0.00 sec) select * from student limit 2; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 88 | 92 | 93 | | 2 | 李四 | 82 | 95 | 77 | ------------------------------------ 2 rows in set (0.00 sec)其中 LIMIT 通常会和 ORDER BY 配合使用。查询数学成绩最高的 3 名学生: select * from student order by math desc limit 3; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 88 | 92 | 93 | | 2 | 李四 | 82 | 95 | 77 | | 4 | 赵六 | 73 | 90 | 93 | ------------------------------------ 3 rows in set (0.00 sec)6.2 指定查询起始位置基本语法LIMIT offset, count; 或者 LIMIT count offest value;select * from student limit 0, 3; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 88 | 92 | 93 | | 2 | 李四 | 82 | 95 | 77 | | 3 | 王五 | 59 | 34 | 88 | ------------------------------------ 3 rows in set (0.00 sec) select * from student limit 3 offset 0; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 88 | 92 | 93 | | 2 | 李四 | 82 | 95 | 77 | | 3 | 王五 | 59 | 34 | 88 | ------------------------------------ 3 rows in set (0.00 sec) select * from student limit 3 offset 1; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 2 | 李四 | 82 | 95 | 77 | | 3 | 王五 | 59 | 34 | 88 | | 4 | 赵六 | 73 | 90 | 93 | ------------------------------------ 3 rows in set (0.00 sec)LIMIT 一个非常常见的应用常见就是分页查询。例如一个学生管理系统中存在 1000 名学生如果一次将所有学生信息全部展示出来不仅页面不方便查看也没有必要一次查询全部数据。因此可以规定每页显示 50 条数据。第一页查询: select * from student limit 50 offset 0; 第二页查询: select * from student limit 50 offset 50; 第三页查询: select * from student limit 50 offset 100;实现进行分页查询时通常还会搭配 ORDEY BY 使用此时SELECT 字段列表 FROM 表名 WHERE 条件 ORDER BY 排序字段 LIMIT count OFFSET value;6.3 SELECT 语句的逻辑执行顺序书写顺序逻辑处理顺序SELECTFROMFROMWHEREWHERESELECTORDER BYDISTINCTLIMITORDER BYLIMITSELECT DISTINCT name, age FROM student WHERE age 18 ORDER BY age DESC LIMIT 3; 1. FROM student 先确定从哪张表获取数据 2. WHERE age 18 筛选满足条件的数据 3. SELECT name, age 决定最终需要哪些字段 4. DISTINCT 对查询结果进行去重 5. ORDER BY age DESC 对结果进行排序 6. LIMIT 3 最后限制返回的数据数量SELECT 语句的逻辑执行顺序带来的经典问题SELECT age 1 AS new_age FROM student WHERE new_age 20; 此时 SELECT 语句发生报错WHERE 子句不认识 new_age 原因执行 WHERE 时, new_age 这个别名还没有产生 SELECT age 1 AS new_age FROM student ORDER BY new_age; 此时 SELECT 语句正常执行 原因SELECT - ORDER BY, 到 ORDER BY 时别名已经产生了七、更新数据UPDATE7.1 UPDATE 基本语法UPDATE 表名 SET [列属性新数据, 列属性新数据, ...] [WHERE ...] [ORDER BY ...] [LIMIT...]7.2 更新一个或多个字段select * from student; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 88 | 92 | 93 | | 2 | 李四 | 82 | 95 | 77 | | 3 | 王五 | 59 | 34 | 88 | | 4 | 赵六 | 73 | 90 | 93 | ------------------------------------ 4 rows in set (0.00 sec) 更新一个字段:将张三的数学成绩加上 5 分 update student set math math 5 where id 1; 更新多个字段:将王五的数据成绩加上 5 分,英语成绩设置为 97 分 update student set math math 5, english 97 where id 3;7.3 更新表中全部数据注意更新表中全部数据谨慎使用select * from student; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 88 | 92 | 93 | | 2 | 李四 | 82 | 95 | 77 | | 3 | 王五 | 59 | 34 | 88 | | 4 | 赵六 | 73 | 90 | 93 | ------------------------------------ 4 rows in set (0.00 sec) 将所有人的数学成绩加上五分 update student set math math 5; select * from student; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 93 | 92 | 93 | | 2 | 李四 | 87 | 95 | 77 | | 3 | 王五 | 69 | 97 | 88 | | 4 | 赵六 | 78 | 90 | 93 | ------------------------------------ 4 rows in set (0.00 sec)八、删除数据DELETE8.1 DELETE 基本语法基本语法DELETE FROM 表名 [WHERE ...] [ORDER BY ...] [LIMIT ...]8.2 条件删除select * from student; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 93 | 92 | 93 | | 2 | 李四 | 82 | 95 | 77 | | 3 | 王五 | 64 | 97 | 88 | | 4 | 赵六 | 73 | 90 | 93 | ------------------------------------ 4 rows in set (0.00 sec) 删除姓名为赵六的信息 delete from student where name 赵六; select * from student; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 93 | 92 | 93 | | 2 | 李四 | 82 | 95 | 77 | | 3 | 王五 | 64 | 97 | 88 | ------------------------------------ 3 rows in set (0.00 sec)8.3 删除表中全部数据注意删除表中全部数据谨慎使用select * from student; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 93 | 92 | 93 | | 2 | 李四 | 82 | 95 | 77 | | 3 | 王五 | 64 | 97 | 88 | ------------------------------------ 3 rows in set (0.00 sec) 删除表中所有数据 delete from student; select * from student; Empty set (0.00 sec)8.4 截断表TRUNCATETRUNCATE 用于快速删除表中的全部数据。基本语法TRUNCATE TABLE 表名;create table student( id int primary key auto_increment, name varchar(20) ); insert into student (name) values (张三), (李四), (王五); show create table student \G; *************************** 1. row *************************** Table: student Create Table: CREATE TABLE student ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(20) DEFAULT NULL, PRIMARY KEY (id) ) ENGINEInnoDB AUTO_INCREMENT4 DEFAULT CHARSETutf8 1 row in set (0.00 sec) delete from student; show create table student \G; *************************** 1. row *************************** Table: student Create Table: CREATE TABLE student ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(20) DEFAULT NULL, PRIMARY KEY (id) ) ENGINEInnoDB AUTO_INCREMENT4 DEFAULT CHARSETutf8 1 row in set (0.00 sec) truncate table student; show create table student \G; *************************** 1. row *************************** Table: student Create Table: CREATE TABLE student ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(20) DEFAULT NULL, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8 1 row in set (0.00 sec)如果执行 DELETEAUTO_INCREMENT 不会改变而执行 TRUNCATEAUTO_INCREMENT 会重新开始。对比项DELETE FROM 表名TRUNCATE TABLE 表名删除范围可以配合 WHERE 删除部分数据也可以删除全部数据只能删除整张表的数据表结构保留保留自增长计数通常不会重置通常会重置SQL 类型DMLDDL删除方式按删除语句处理记录更接近重新创建一张空表使用场景需要灵活删除数据快速清空整张表当需要按照条件删除数据时使用 DELETE当需要快速清空整张表时可以考虑使用 TRUNCATE MySQL 基础篇（五）：数据操作基础 —— CRUD
目录本文内容概要一、认识 CRUD二、新增数据INSERT2.1 INSERT 基本语法2.2 全列插入2.3 指定列插入2.4 一次插入多条数据2.5 插入冲突则更新ON DUPLICATE KEY UPDATE2.6 替换数据REPLACE三、查询数据SELECT3.1 SELECT 基本语法3.2 查询全部字段3.3 查询指定字段3.4 查询字段起别名3.5 查询结果去重DISTINCT3.6 查询表达式四、条件筛选WHERE4.1 WHERE 基本语法4.2 比较运算符4.3 逻辑运算符4.4 范围查询BETWEEN ... AND ...4.5 集合查询IN / NOT IN4.6 模糊查询LIKE4.7 NULL 值判断IS NULL / IS NOT NULL五、查询结果排序ORDER BY5.1 ORDER BY 基本语法5.2 多字段排序六、限制查询结果LIMIT6.1 LIMIT 基本语法6.2 指定查询起始位置6.3 SELECT 语句的逻辑执行顺序七、更新数据UPDATE7.1 UPDATE 基本语法7.2 更新一个或多个字段7.3 更新表中全部数据八、删除数据DELETE8.1 DELETE 基本语法8.2 条件删除8.3 删除表中全部数据8.4 截断表TRUNCATE本文内容概要本文主要介绍 MySQL 中数据的增删改查操作。通过本文的学习需要掌握 INSERT 数据插入、SELECT 数据查询、UPDATE 数据更新以及 DELETE 数据删除等基本操作理解 WHERE 条件筛选、ORDER BY 查询结果排序、LIMIT 结果限制与分页查询的基本用法掌握 DISTINCT 去重、BETWEEN 范围查询、IN 集合查询、LIKE 模糊匹配以及 NULL 值判断等常用查询语法同时了解 REPLACE、ON DUPLICATE KEY UPDATE 和 TRUNCATE 等相关操作并对基础 SELECT 语句的逻辑执行顺序进行总结为后续学习聚合查询、多表查询以及子查询等进阶 SQL 内容打下基础。一、认识 CRUDCRUD 是数据库中最基本的四类数据操作分别对应数据的新增、查询、修改和删除。CRUD 由四个英文单词的首字母组成Create 创建向数据表中插入新的数据Retrieve读取从数据表中查询所需要的数据Update 更新修改数据表中已经存在的数据Delete 删除删除数据表中已有的数据因此对于一张已经创建完成的数据表我们日常最主要的操作基本都是 CRUD。二、新增数据INSERT2.1 INSERT 基本语法INSERT [INTO] 表名 [(列属性, 列属性, ...)] VALUES [(值, 值, ...)]示例在学生表中插入学生的相关信息create table student( - id int unsigned primary key, - name varchar(20) not null, - age int - ); desc student; ---------------------------------------------------- | Field | Type | Null | Key | Default | Extra | ---------------------------------------------------- | id | int(10) unsigned | NO | PRI | NULL | | | name | varchar(20) | NO | | NULL | | | age | int(11) | YES | | NULL | | ----------------------------------------------------2.2 全列插入insert into student values (1, 张三, 18); insert student values (2, 李四, 20); select * from student; ------------------ | id | name | age | ------------------ | 1 | 张三 | 18 | | 2 | 李四 | 20 | ------------------ 2 rows in set (0.00 sec)INTO 在 MySQL 中可以省略不写其中值列表信息必须把所有列属性全部填充。2.3 指定列插入insert into student (id, name) values (1, 张三); select * from student; ------------------ | id | name | age | ------------------ | 1 | 张三 | NULL | ------------------ 1 row in set (0.00 sec)其他未被指定的列必须存在默认值或者主键自增值。2.4 一次插入多条数据insert into student values (1, 张三, 18), (2, 李四, 19); insert into student (id, name) values (3, 王五), (4, 赵六); select * from student; ------------------ | id | name | age | ------------------ | 1 | 张三 | 18 | | 2 | 李四 | 19 | | 3 | 王五 | NULL | | 4 | 赵六 | NULL | ------------------ 4 rows in set (0.00 sec)一次插入多条数据时既可以全列插入也可以指定列插入。数据之间以逗号分割。2.5 插入冲突则更新ON DUPLICATE KEY UPDATE在插入数据时插入的新数据可能由于主键或者唯一键冲突导致插入失败。insert into student values (1, 张三, 18); Query OK, 1 row affected (0.00 sec) insert into student values (1, zhangsan, 20); ERROR 1062 (23000): Duplicate entry 1 for key PRIMARY此时我们可以选择性的进行同步更新数据INSERT ... ON DUPLICATE KEY UPDATE column value [, column value] ...select * from student; ------------------ | id | name | age | ------------------ | 1 | 张三 | 20 | ------------------ 1 row in set (0.00 sec) insert into student values (1, zhangsan, 20) on duplicate key update name zhangsan, age 20; Query OK, 2 rows affected (0.14 sec) insert into student values (2, lisi, 16) on duplicate key update nameme lisi, age 16; Query OK, 1 row affected (0.00 sec) insert into student values (2, lisi, 16) on duplicate key update name lisi, age 16; Query OK, 0 rows affected (0.00 sec) select * from student; -------------------- | id | name | age | -------------------- | 1 | zhangsan | 20 | | 2 | lisi | 16 | -------------------- 2 rows in set (0.00 sec)在使用插入否则更新的操作时我们可以通过 MySQL 响应看到操作对于表的影响Query OK, 0 rows affected (0.00 sec) 表中有冲突数据但冲突数据的值与 update 值相同Query OK, 1 row affected (0.00 sec)表中没有冲突数据直接插入Query OK, 2 rows affected (0.14 sec)表中有冲突数据并且数据已经被更新补充通过 MySQL 函数获取上次操作影响的数据行数insert into student values (2, lisi, 16) on duplicate key update name lisi, age 16; Query OK, 0 rows affected (0.00 sec) select row_count(); ------------- | row_count() | ------------- | 0 | ------------- 1 row in set (0.00 sec) insert into student values (2, lisi, 15) on duplicate key update name lisi, age 15; Query OK, 2 rows affected (0.00 sec) select row_count(); ------------- | row_count() | ------------- | 2 | ------------- 1 row in set (0.00 sec)2.6 替换数据REPLACE在插入新数据时我们可以采用 REPLACE 方式进行插入新数据。当新数据发生主键或者唯一键冲突时REPLACE 方式会先删除旧数据再插入新数据。基本语法REPLACE [INTO] 表名 [(列属性, 列属性, ...)] VALUES [(值, 值, ...)]REPLACE 也会支持全列插入、指定列插入和一次插入多条数据replace into student values (1, 张三, 18); Query OK, 1 row affected (0.00 sec) select * from student; ------------------ | id | name | age | ------------------ | 1 | 张三 | 18 | ------------------ 1 row in set (0.00 sec) replace student values (1, zhangsan, 20); Query OK, 2 rows affected (0.00 sec) replace student values (1, zhangsan, 20); Query OK, 1 row affected (0.00 sec) select * from student; -------------------- | id | name | age | -------------------- | 1 | zhangsan | 20 | -------------------- 1 row in set (0.00 sec)REPLACE 新增记录时影响 1 行发生唯一键冲突并替换旧记录时影响 2 行。需要注意在 InnoDB 中当新旧记录完全相同时可能出现影响 1 行的情况因此不建议仅通过 affected rows 判断 REPLACE是否发生了数据替换。三、查询数据SELECT3.1 SELECT 基本语法SELECT [DISTINCT] * | 列属性, 列属性, ... FROM 表名示例构建学生表准备学生表数据create table student( - id int unsigned primary key auto_increment, - name varchar(20) not null, - math tinyint unsigned, - english tinyint unsigned, - chinese tinyint unsigned - ); desc student; ------------------------------------------------------------------ | Field | Type | Null | Key | Default | Extra | ------------------------------------------------------------------ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | math | tinyint(3) unsigned | YES | | NULL | | | english | tinyint(3) unsigned | YES | | NULL | | | chinese | tinyint(3) unsigned | YES | | NULL | | ------------------------------------------------------------------ 5 rows in set (0.01 sec) insert into student (name, math, english, chinese) values (张三, 88, 92, 93); insert into student (name, math, english, chinese) values (李四, 82, 95, 77); insert into student (name, math, english, chinese) values (王五, 59, 34, 88); select * from student; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 88 | 92 | 93 | | 2 | 李四 | 82 | 95 | 77 | | 3 | 王五 | 59 | 34 | 88 | ------------------------------------ 3 rows in set (0.00 sec)3.2 查询全部字段select * from student; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 88 | 92 | 93 | | 2 | 李四 | 82 | 95 | 77 | | 3 | 王五 | 59 | 34 | 88 | ------------------------------------ 3 rows in set (0.00 sec)3.3 查询指定字段select id, name, math from student; ------------------ | id | name | math | ------------------ | 1 | 张三 | 88 | | 2 | 李四 | 82 | | 3 | 王五 | 59 | ------------------ 3 rows in set (0.00 sec) select name, math, id from student; ------------------ | name | math | id | ------------------ | 张三 | 88 | 1 | | 李四 | 82 | 2 | | 王五 | 59 | 3 | ------------------ 3 rows in set (0.00 sec)显示顺序与指定顺序相关3.4 查询字段起别名基本语法字段名 [AS] 别名select id as 学号, name 姓名, math 数学 from student; ------------------------ | 学号 | 姓名 | 数学 | ------------------------ | 1 | 张三 | 88 | | 2 | 李四 | 82 | | 3 | 王五 | 59 | ------------------------ 3 rows in set (0.00 sec) select * from student; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 88 | 92 | 93 | | 2 | 李四 | 82 | 95 | 77 | | 3 | 王五 | 59 | 34 | 88 | ------------------------------------ 3 rows in set (0.01 sec)注别名只是显示效果起别名不会修改列属性3.5 查询结果去重DISTINCT示例准备一张学生表查询学生表中有哪些专业select * from student; ---------------------------------- | id | name | age | major | ---------------------------------- | 1 | 张三 | 18 | 计算机 | | 2 | 李四 | 19 | 计算机 | | 3 | 王五 | 20 | 软件工程 | | 4 | 赵六 | 18 | 计算机 | | 5 | 小红 | 19 | 软件工程 | | 6 | 小明 | 20 | 人工智能 | ----------------------------------select major from student; ---------- | major | ---------- | 计算机 | | 计算机 | | 软件工程 | | 计算机 | | 软件工程 | | 人工智能 | ---------- select distinct major from student; ---------- | major | ---------- | 计算机 | | 软件工程 | | 人工智能 | ---------- select distinct age, major from student; -------------------- | age | major | -------------------- | 18 | 计算机 | | 19 | 计算机 | | 20 | 软件工程 | | 19 | 软件工程 | | 20 | 人工智能 | -------------------- 5 rows in set (0.00 sec)DISTINCT 去重的是查询结果中的整行数据且 DISTINCT 支持组合去重。例如select distinct age, major from student;这里去重的是 (age, major) 这个组合而不是分别对 age 和 major 单独去重。3.6 查询表达式select * from student; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 88 | 92 | 93 | | 2 | 李四 | 82 | 95 | 77 | | 3 | 王五 | 59 | 34 | 88 | ------------------------------------ 3 rows in set (0.01 sec) select name, mathenglishchinese from student; ------------------------------ | name | mathenglishchinese | ------------------------------ | 张三 | 273 | | 李四 | 254 | | 王五 | 181 | ------------------------------ 3 rows in set (0.00 sec) select name 姓名, mathenglishchinese as 总分 from student; ---------------- | 姓名 | 总分 | ---------------- | 张三 | 273 | | 李四 | 254 | | 王五 | 181 | ---------------- 3 rows in set (0.00 sec)四、条件筛选WHERE在前面介绍的 SELECT 可以查询表中的数据但如果不添加任何限制条件则会返回表中的全部记录。而在实际开发中我们往往需要获取满足特定条件的数据。例如查询年龄大于 18 岁的学生查询计算机专业的学生查询姓名为张三或者李四的学生查询年龄处于某个范围内的学生。此时就需要 WHERE 字句对数据进行筛选。4.1 WHERE 基本语法WHERE 用于指定查询条件只要满足条件的数据才能出现到显示结果中。SELECT 字段列表 FROM 表名 WHERE 条件4.2 比较运算符运算符说明、、、大于、大于等于、小于、小于等于等于对NULL不安全例如NULL NULL的结果为NULLNULL 安全等于例如NULL NULL的结果为TRUE(1)!或者不等于示例 ---------------------------- | id | name | age | major | ---------------------------- | 1 | 张三 | 18 | 计算机 | | 3 | 王五 | 20 | 人工智能 | ---------------------------- 查询年龄大于 18 岁的学生 select * from student where age 18; 查询年龄不等于 18 岁的学生 select * from student where age ! 18; select * from student where age 18; 查询姓名为 张三 的学生: select * from student where name 张三;4.3 逻辑运算符运算符说明AND多个条件必须都为TRUE(1)结果才为TRUE(1)OR任意一个条件为TRUE(1)结果就为TRUE(1)NOT对条件结果取反例如条件为TRUE(1)结果为FALSE(0)查询年龄大于等于 18 岁并且专业为计算机的学生: select * from student where age 18 and major 计算机; 查询专业为计算机或者人工智能的学生: select * from student where major 计算机 or major 人工智能; 查询专业不是计算机的学生: select * from student where not major 计算机; 查询年龄大于等于 18 岁并且专业是计算机或者人工智能: select * from student where age 18 and (major 计算机 or major 人工智能);4.4 范围查询BETWEEN ... AND ...如果需要判断某个值是否位于指定范围内可以使用BETWEEN 最小值 AND 最大值查询年龄在 18 ~ 20 岁之间的学生 select * from student where age between 18 and 20; 也可以这样写: select * from student where age 18 and age 20; 查找年龄不在 18 ~ 20 岁之间的学生: select * from student where age not between 18 and 20;注BETWEEN ... AND ... 包含左右边界4.5 集合查询IN / NOT IN当一个字段满足多个离散值中的任意一个时如果一直使用 ORSQL 语句会比较繁琐。例如查找年龄等于 18 或者等于 20 或者等于 21 的学生: select * from student where age 18 or age 20 or age 21; 此时可以使用 IN: select * from student where age in (18, 20, 21); 查询专业为软件工程或者计算机或者人工智能的学生 select * from student where major in (软件工程, 计算机, 人工智能); 查找专业不为软件工程或者计算机或者人工智能的学生 select * from student where major not in (软件工程, 计算机, 人工智能);4.6 模糊查询LIKE在学生表信息中我们可能需要找所有姓张的学生此时就可以使用 LIKE 进行模糊匹配。通配符含义%匹配任意长度的字符可以是 0 个或多个_匹配任意一个字符查询所有姓张的学生: select * from student where name like 张%; 查找名字以三为结尾的学生: select * from student where name like %三; 查找名字中包含三的学生: select * from student where name like %三%; 查找姓张且只有两个字的学生: select * from student where name like 张_; 查找不是以张开头的学生: select * from student where name not like 张%;4.7 NULL 值判断IS NULL / IS NOT NULL在 MySQL 中NULL 表示空不参与运算和比较。假设部分学生暂时没有填写专业---------------------------- | id | name | age | major | ---------------------------- | 1 | 张三 | 18 | 计算机 | | 2 | 李四 | 20 | NULL | | 3 | 王五 | 19 | 人工智能 | ----------------------------如果想查询 major 为 NULL 的记录不能写select * from student where major NULL;而是select * from student where major is null;查询专业不为 NULL 的学生select * from student where major is not null;五、查询结果排序ORDER BY5.1 ORDER BY 基本语法ORDER BY 用于按照指定字段对查询结果进行排序。基本语法SELECT 字段列表 FROM 表名 ORDER BY 字段名 [ASC | DESC];关键字说明ASC升序排列AscendingDESC降序排列Descendingselect * from student; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 88 | 92 | 93 | | 2 | 李四 | 82 | 95 | 77 | | 3 | 王五 | 59 | 34 | 88 | ------------------------------------ 3 rows in set (0.00 sec) select * from student order by chinese; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 2 | 李四 | 82 | 95 | 77 | | 3 | 王五 | 59 | 34 | 88 | | 1 | 张三 | 88 | 92 | 93 | ------------------------------------ 3 rows in set (0.00 sec) select * from student order by chinese asc; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 2 | 李四 | 82 | 95 | 77 | | 3 | 王五 | 59 | 34 | 88 | | 1 | 张三 | 88 | 92 | 93 | ------------------------------------ 3 rows in set (0.00 sec) select * from student order by chinese desc; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 88 | 92 | 93 | | 3 | 王五 | 59 | 34 | 88 | | 2 | 李四 | 82 | 95 | 77 | ------------------------------------ 3 rows in set (0.00 sec)默认情况下ORDER BY 会按照升序进行排列。5.2 多字段排序基本语法ORDER BY 字段1 排序方式, 字段2 排序方式, ...;select * from student; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 88 | 92 | 93 | | 2 | 李四 | 82 | 95 | 77 | | 3 | 王五 | 59 | 34 | 88 | | 4 | 赵六 | 73 | 90 | 93 | ------------------------------------ 4 rows in set (0.00 sec) select * from student order by chinese desc; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 88 | 92 | 93 | | 4 | 赵六 | 73 | 90 | 93 | | 3 | 王五 | 59 | 34 | 88 | | 2 | 李四 | 82 | 95 | 77 | ------------------------------------ 4 rows in set (0.00 sec) select * from student order by chinese desc, english asc; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 4 | 赵六 | 73 | 90 | 93 | | 1 | 张三 | 88 | 92 | 93 | | 3 | 王五 | 59 | 34 | 88 | | 2 | 李四 | 82 | 95 | 77 | ------------------------------------ 4 rows in set (0.00 sec)多个字段排序时MySQL 会优先按照第一个字段排序。只有当前面的字段值相同时才会继续按照后面的字段进行排序。另外 ORDER BY 也可以和 WHERE 配合使用。查询数学成绩大于等于 90 分的学生并按照语文成绩降序排列: select * from student where math 90 order by chinese desc;六、限制查询结果LIMIT6.1 LIMIT 基本语法LIMIT 用于限制查询返回的数据条数。基本语法SELECT 字段列表 FROM 表名 LIMIT 数量;select * from student; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 88 | 92 | 93 | | 2 | 李四 | 82 | 95 | 77 | | 3 | 王五 | 59 | 34 | 88 | | 4 | 赵六 | 73 | 90 | 93 | ------------------------------------ 4 rows in set (0.00 sec) select * from student limit 2; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 88 | 92 | 93 | | 2 | 李四 | 82 | 95 | 77 | ------------------------------------ 2 rows in set (0.00 sec)其中 LIMIT 通常会和 ORDER BY 配合使用。查询数学成绩最高的 3 名学生: select * from student order by math desc limit 3; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 88 | 92 | 93 | | 2 | 李四 | 82 | 95 | 77 | | 4 | 赵六 | 73 | 90 | 93 | ------------------------------------ 3 rows in set (0.00 sec)6.2 指定查询起始位置基本语法LIMIT offset, count; 或者 LIMIT count offest value;select * from student limit 0, 3; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 88 | 92 | 93 | | 2 | 李四 | 82 | 95 | 77 | | 3 | 王五 | 59 | 34 | 88 | ------------------------------------ 3 rows in set (0.00 sec) select * from student limit 3 offset 0; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 88 | 92 | 93 | | 2 | 李四 | 82 | 95 | 77 | | 3 | 王五 | 59 | 34 | 88 | ------------------------------------ 3 rows in set (0.00 sec) select * from student limit 3 offset 1; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 2 | 李四 | 82 | 95 | 77 | | 3 | 王五 | 59 | 34 | 88 | | 4 | 赵六 | 73 | 90 | 93 | ------------------------------------ 3 rows in set (0.00 sec)LIMIT 一个非常常见的应用常见就是分页查询。例如一个学生管理系统中存在 1000 名学生如果一次将所有学生信息全部展示出来不仅页面不方便查看也没有必要一次查询全部数据。因此可以规定每页显示 50 条数据。第一页查询: select * from student limit 50 offset 0; 第二页查询: select * from student limit 50 offset 50; 第三页查询: select * from student limit 50 offset 100;实现进行分页查询时通常还会搭配 ORDEY BY 使用此时SELECT 字段列表 FROM 表名 WHERE 条件 ORDER BY 排序字段 LIMIT count OFFSET value;6.3 SELECT 语句的逻辑执行顺序书写顺序逻辑处理顺序SELECTFROMFROMWHEREWHERESELECTORDER BYDISTINCTLIMITORDER BYLIMITSELECT DISTINCT name, age FROM student WHERE age 18 ORDER BY age DESC LIMIT 3; 1. FROM student 先确定从哪张表获取数据 2. WHERE age 18 筛选满足条件的数据 3. SELECT name, age 决定最终需要哪些字段 4. DISTINCT 对查询结果进行去重 5. ORDER BY age DESC 对结果进行排序 6. LIMIT 3 最后限制返回的数据数量SELECT 语句的逻辑执行顺序带来的经典问题SELECT age 1 AS new_age FROM student WHERE new_age 20; 此时 SELECT 语句发生报错WHERE 子句不认识 new_age 原因执行 WHERE 时, new_age 这个别名还没有产生 SELECT age 1 AS new_age FROM student ORDER BY new_age; 此时 SELECT 语句正常执行 原因SELECT - ORDER BY, 到 ORDER BY 时别名已经产生了七、更新数据UPDATE7.1 UPDATE 基本语法UPDATE 表名 SET [列属性新数据, 列属性新数据, ...] [WHERE ...] [ORDER BY ...] [LIMIT...]7.2 更新一个或多个字段select * from student; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 88 | 92 | 93 | | 2 | 李四 | 82 | 95 | 77 | | 3 | 王五 | 59 | 34 | 88 | | 4 | 赵六 | 73 | 90 | 93 | ------------------------------------ 4 rows in set (0.00 sec) 更新一个字段:将张三的数学成绩加上 5 分 update student set math math 5 where id 1; 更新多个字段:将王五的数据成绩加上 5 分,英语成绩设置为 97 分 update student set math math 5, english 97 where id 3;7.3 更新表中全部数据注意更新表中全部数据谨慎使用select * from student; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 88 | 92 | 93 | | 2 | 李四 | 82 | 95 | 77 | | 3 | 王五 | 59 | 34 | 88 | | 4 | 赵六 | 73 | 90 | 93 | ------------------------------------ 4 rows in set (0.00 sec) 将所有人的数学成绩加上五分 update student set math math 5; select * from student; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 93 | 92 | 93 | | 2 | 李四 | 87 | 95 | 77 | | 3 | 王五 | 69 | 97 | 88 | | 4 | 赵六 | 78 | 90 | 93 | ------------------------------------ 4 rows in set (0.00 sec)八、删除数据DELETE8.1 DELETE 基本语法基本语法DELETE FROM 表名 [WHERE ...] [ORDER BY ...] [LIMIT ...]8.2 条件删除select * from student; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 93 | 92 | 93 | | 2 | 李四 | 82 | 95 | 77 | | 3 | 王五 | 64 | 97 | 88 | | 4 | 赵六 | 73 | 90 | 93 | ------------------------------------ 4 rows in set (0.00 sec) 删除姓名为赵六的信息 delete from student where name 赵六; select * from student; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 93 | 92 | 93 | | 2 | 李四 | 82 | 95 | 77 | | 3 | 王五 | 64 | 97 | 88 | ------------------------------------ 3 rows in set (0.00 sec)8.3 删除表中全部数据注意删除表中全部数据谨慎使用select * from student; ------------------------------------ | id | name | math | english | chinese | ------------------------------------ | 1 | 张三 | 93 | 92 | 93 | | 2 | 李四 | 82 | 95 | 77 | | 3 | 王五 | 64 | 97 | 88 | ------------------------------------ 3 rows in set (0.00 sec) 删除表中所有数据 delete from student; select * from student; Empty set (0.00 sec)8.4 截断表TRUNCATETRUNCATE 用于快速删除表中的全部数据。基本语法TRUNCATE TABLE 表名;create table student( id int primary key auto_increment, name varchar(20) ); insert into student (name) values (张三), (李四), (王五); show create table student \G; *************************** 1. row *************************** Table: student Create Table: CREATE TABLE student ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(20) DEFAULT NULL, PRIMARY KEY (id) ) ENGINEInnoDB AUTO_INCREMENT4 DEFAULT CHARSETutf8 1 row in set (0.00 sec) delete from student; show create table student \G; *************************** 1. row *************************** Table: student Create Table: CREATE TABLE student ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(20) DEFAULT NULL, PRIMARY KEY (id) ) ENGINEInnoDB AUTO_INCREMENT4 DEFAULT CHARSETutf8 1 row in set (0.00 sec) truncate table student; show create table student \G; *************************** 1. row *************************** Table: student Create Table: CREATE TABLE student ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(20) DEFAULT NULL, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8 1 row in set (0.00 sec)如果执行 DELETEAUTO_INCREMENT 不会改变而执行 TRUNCATEAUTO_INCREMENT 会重新开始。对比项DELETE FROM 表名TRUNCATE TABLE 表名删除范围可以配合 WHERE 删除部分数据也可以删除全部数据只能删除整张表的数据表结构保留保留自增长计数通常不会重置通常会重置SQL 类型DMLDDL删除方式按删除语句处理记录更接近重新创建一张空表使用场景需要灵活删除数据快速清空整张表当需要按照条件删除数据时使用 DELETE当需要快速清空整张表时可以考虑使用 TRUNCATE