行业知识
【MySQL】基本查询（表的增删改查）-- 详解
CRUD：Create（创建），Retrieve（读取），Update（更新），Delete（删除）。
一、Create
insert [into] table_name [(column [, column] ...)] values (value_list) [, (value_list)] ...
value_list: value, [, value] ...
values 左侧括号内是列属性，右侧括号内是列属性对应的内容，必须在类型和数值上一一对应。
若忽略 values 左侧括号内的内容，则称为全列插入，否则成为按列插入。
创建一张学生表：
1、单行数据 + 全列插入
- 插入两条记录，value_list 数量必须和定义表的列的数量及顺序一致。
- 注意，这里在插入时，也可以不用指定 id（当然，那时候就需要明确插入数据到哪些列了），那么 MySQL 会使用默认的值进行自增。
2、多行数据 + 指定列插入
插入两条记录，value_list 数量必须和指定列数量及顺序一致。3、插入是否更新
由于 主键 / 唯一键 对应的值已经存在而导致插入失败。 可以选择性的进行同步更新操作： insert ... on duplicate key update column=value [, column = value] ...- 0 row affected：表中有冲突数据，但冲突数据的值和 update 的值相等。
- 1 row affected：表中没有冲突数据，数据被插入。
- 2 row affected：表中有冲突数据，并且数据已经被更新。
4、替换
- 主键 / 唯一键没有冲突，则直接插入。
- 主键 / 唯一键如果冲突，则删除后再插入。
- 1 row affected：表中没有冲突数据，数据被插入。
- 2 row affected：表中有冲突数据，删除后重新插入。
二、Retrieve
select [distinct] {* | {column [, column] ...} [from table_name] [where ...] [order by column [asc | desc], ...] limit ...
1、SELECT 列
（1）全列查询
- 查询的列越多，意味着需要传输的数据量越大。
- 可能会影响到索引的使用。
通常情况下不建议使用 * 进行全列查询。
（2）指定列查询
指定列的顺序不需要按定义表的顺序来。
（3）查询字段为表达式
a. 表达式不包含字段
b. 表达式包含多个字段
（4）为查询结果指定别名
select column [as] alias_name [...] from table_name;
（5）结果去重
2、WHERE 条件
（1）比较运算符
注意：在 MySQL 中， 用 = 来判断两个 字符串或数字 是否相等，与 C/C++ 不同。 用 = 判断是否等于 NULL 这样做是不安全的，因为 NULL 和 0 本身表示的含义不同，NULL 表示空，0 表示数字 0。（2）逻辑运算符
⚪练习
a. 练习一 —— 英语不及格的同学及英语成绩
b. 练习二 —— 语文成绩在 [80, 90] 分的同学及语文成绩
写法一：
- 使用 and 进行条件连接 写法二：
- 使用 between ... and ... 条件
c. 练习三 —— 数学成绩是 58 / 59 / 98 / 99 分的同学及数学成绩
- 使用 or 进行条件连接
- 使用 in 条件
d. 练习四 —— 姓孙的同学及孙某同学
- % 匹配任意多个（包括 0 个）任意字符
- _ 匹配严格的一个任意字符
e. 练习五 —— 语文成绩好于英语成绩的同学
- where 条件中比较运算符两侧都是字段
f. 练习六 —— 总分在 200 分以下的同学
where 条件中使用表达式，别名不能用在 where 条件中。
错误写法：
g. 练习七 —— 语文成绩 > 80 并且不姓孙的同学
- and 与 not 的使用
h. 练习八 —— 孙某同学，否则要求总成绩>200 并且 语文成绩80
-
综合性查询
i. 练习九 —— NULL 的查询
-
分别查询姓名为空、为空字符串、不为空的-
NULL 和 NULL 的比较，= 和 的区别-
SELECT NULL NULL, NULL 1, NULL 0
（3）结果排序
- ASC 为升序（从小到大）
- DESC 为降序（从大到小）
默认为 ASC。
注意：没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序。
select ... from table_name [where ...] order by column [asc|desc], [...] ;⚪练习
a. 练习一 —— 同学及数学成绩，按数学成绩升序显示
b. 练习二 —— 同学名字，按名字排序显示
- NULL 视为比任何值都小，升序出现在最上面。
- NULL 视为比任何值都小，降序出现在最下面。
c. 练习三 —— 查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示
- 多字段排序，排序优先级随书写顺序
asc 可以省略不写，但不建议。
d. 练习四 —— 查询同学及总分，由高到低
为什么在这里又能够使用别名了呢？
能否使用别名完全取决于当前 sql 子句的执行顺序。
order by 中可以使用表达式。
e. 练习五 —— 查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示
-
结合 where 子句 和 order by 子句
order by 子句的执行顺序晚于 where 子句。
（4）筛选分页结果
- 从 0 开始，筛选 n 条结果 select ... from table_name [where ...] [order by ...] limit n; 3：从表开始()开始连续读取 3 行。
-
从 s 开始，筛选 n 条结果 select ... from table_name [where ...] [order by ...] limit s, n; 1：开始位置（下标从 0 开始）。 3：步长，从指定位置开始，连续读取 3 条记录。-
从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用 select ... from table_name [where ...] [order by ...] limit n offset s; 4：步长，从指定位置开始，连续读取 4 条记录。 1：开始位置（下标从 0 开始）。 注意 ：起始下标为 0。 建议：对未知表进行查询时，最好加一条 limit 1 ，避免因为表中数据过大，查询全表数据导致数据库卡***。 按 id 进行分页，每页 3 条记录，分别显示 第 1 、 2 、 3 页。只有数据准备好了，才要显示，limit 的本质功能是 “显示”。
得先有数据，才能 “显示”，“显示” 时，limit 只是告诉 MySQL，显示时只显示从哪里开始，从开始位置显示几行。
limit 不是条件筛选，本质就是把数据准备好，排好序，然后再 limit，执行阶段更靠后。
三、Update
update table_name set column=expr [, column = expr ...] [where ...] [order by ...] [limit ...]对查询到的结果进行列值更新。
⚪练习
（1）练习一 —— 将孙悟空同学的数学成绩变更为 80 分
- 数据更新
（2）练习二 —— 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分
- 一次更新多个列
（3）练习三 —— 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
- 更新值为原值基础上变更
注意：别名可以在 order by 中使用 。
（4）练习四 —— 将所有同学的语文成绩更新为原来的 2 倍
- 没有 where 子句，则更新全表。 注意 ：更新全表的语句慎用。
四、Delete
1、删除数据
delete from table_name [where ...] [order by ...] [limit ...];
⚪练习
a. 练习一 —— 删除孙悟空同学的考试成绩
b. 练习二 —— 删除整张表数据
注意 ：删除整表操作要慎用。2、截断表
truncate [table] table_name;
-
截断整表数据，注意影响行数是 0，所以实际上没有对数据真正操作-
再插入一条数据，自增 id 再重新增长
delete 和 truncate 的区别：
- 都可以清空表中的数据。
- delete from 是传统的删除，不会对计数器进行清空或重新置位，而 truncate 清空表会重置 auto_increment 项。
- truncate 是直接将表中数据清空，它不走事务，而 delete from 以及之前学的 sql 最终在运行时都要以事务的方式被包装，然后再让 MySQL 去运行。
- 只能对整表操作，不能像 delete 一样针对部分数据操作。
- 实际上 MySQL 不对数据操作，所以比 delete 更快，但是 truncate 在删除数据时，并不经过真正的事物，所以无法回滚。
- 会重置 auto_increment 项。
五、插入查询结果
insert into table_name [(column [, column ...])] select ...;
⚪练习
（1）练习一 —— 删除表中的的重复复记录，重复的数据只能有一份
错误思路：
-
创建一张空表（no_duplicate_table，结构和 duplicate_table 一样）-
将 duplicate_table 的去重数据插入到 no_duplicate_table-
通过重命名表，实现原子的去重操作为什么最后是通过 rename 方式进行的？
就是单纯的想等一切都就绪了，然后统一放入、更新、生效等。
六、聚合函数
⚪练习
（1）练习一 —— 统计班级共有多少同学
- 使用 * 做统计，不受 NULL 影响
- 使用表达式做统计
（2）练习二 —— 统计本次考试的数学成绩分数个数
-
count(math) 统计的是全部成绩
- count(distinct math) 统计的是去重成绩数量
注意：
distinct 要写在括号内，因为我们是要对 math 去重，而不是对 count() 的结果去重。
NULL 不会计入结果。
（3）练习三 —— 统计数学成绩总分
- 不及格
- 数学的平均成绩
（4）练习四 —— 统计平均总分
（5）练习五 —— 返回英语最高分
（6）练习六 —— 返回 > 70 分以上的数学最低分
七、group by 子句的使用
分组的目的：为了进行分组后，方便进行聚合统计。
在 select 中使用 group by 子句可以对指定列进行分组查询：
select column1, column2, .. from table group by column;1、准备工作，创建一个雇员信息表（来自 Oracle 9i 的经典测试表）
- emp 员工表
-
dept 部门表-
salgrade 工资等级表2、如何显示每个部门的平均工资和最高工资
指定列名，实际分组是用该列不同的行数来进行分组的。
分组的 deptno，组内一定是相同的。说明可以被聚合压缩。
分组就是把一组按照条件拆分成了多个组，进行各自组内的统计。
分组(“分表”)，就是把一张表按照条件在逻辑上拆成了多个子表，然后分别对各自的子表进行聚合统计。
3、显示每个部门的每种岗位的平均工资和最低工资
4、显示平均工资低于 2000 的部门和它的平均工资
（1）统计出每一个部门的平均工资（结果先聚合出来）
（2）having 和 group by 配合使用，对 group by 结果进行过滤（对聚合的结果进行判断）
having 和 group by 的语义是一样的，having 相当于是对分组聚合统计后的数据，进行条件筛选。
having 经常和 group by 搭配使用，作用是对分组进行筛选，作用有些像 where。
having VS where 的区别与执行顺序是什么？
都能够做条件筛选，这是它们的共性。
但它们是完全不同的条件筛选，它们的条件筛选的阶段是不同的。
补充：不要单纯的认为，只有在磁盘上将表结构导入到 MySQL，真实存在的表才叫表。
中间筛选出来的，包括最终结果，全都是逻辑上的表。（MySQL 一切皆表）
只要我们能够处理好单表的 CURD，所有的 sql 场景就都能用统一的方式进行。
5、补充
SQL 查询中各个关键字的执行先后顺序：
from > on > join > where > group by > with > having > select > distinct > order by > limit
-
-
- emp 员工表
- 数学的平均成绩
- 不及格
- count(distinct math) 统计的是去重成绩数量
-
-
-
-
-
- 没有 where 子句，则更新全表。 注意 ：更新全表的语句慎用。
- 更新值为原值基础上变更
- 一次更新多个列
- 数据更新
-
-
- 从 0 开始，筛选 n 条结果 select ... from table_name [where ...] [order by ...] limit n; 3：从表开始()开始连续读取 3 行。
-
- 多字段排序，排序优先级随书写顺序
-
-
-
-
- and 与 not 的使用
- where 条件中比较运算符两侧都是字段
- _ 匹配严格的一个任意字符
- % 匹配任意多个（包括 0 个）任意字符
- 使用 in 条件
- 使用 or 进行条件连接
- 使用 between ... and ... 条件
下一篇：域名到期怎么查?
