【MySQL】基本查询（表的增删改查）-- 详解，MySQL基础查询操作详解，表的增删改查全攻略

温馨提示：这篇文章已超过429天没有更新，请注意相关的内容是否还可用！

摘要：本文将详细介绍MySQL数据库的基本查询操作，包括表的增删改查。我们将了解如何创建和管理表，包括定义表结构、添加字段等。我们将学习如何进行基本的查询操作，如选择数据、条件查询和排序等。还将探讨如何更新和删除表中的数据。通过本文的学习，读者将能够掌握MySQL数据库的基本查询操作，为后续的数据库管理和应用开发打下坚实的基础。

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

一、Create

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

value_list: value, [, value] ...

values 左侧括号内是列属性，右侧括号内是列属性对应的内容，必须在类型和数值上一一对应。

若忽略 values 左侧括号内的内容，则称为全列插入，否则成为按列插入。

创建一张学生表：

【MySQL】基本查询（表的增删改查）-- 详解，MySQL基础查询操作详解，表的增删改查全攻略第1张

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）全列查询
      1. 查询的列越多，意味着需要传输的数据量越大。
      2. 可能会影响到索引的使用。
      通常情况下不建议使用 * 进行全列查询。
      （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