MySQL（4）

文章目录

MySQL（4）

前言：

本文 将学习 MySQL 的 修改 和 删除 操作，完成这两个 操作后 还有 涉及到 数据库 约束 。

在进入 文章前我们 来 复习 一下 上文内容

新增：

insert into 表名 values (列的值， 值， …)

注意： 列的 值的类型 和 个数 与创建表的 类型和个数一至。

另外 还可以 指定列插入（表示插入所有的列） 同样还可以 一个语句 ，插入多条记录，values 后面放 多个括号，每个括号对应一条记录（一行）、

查找：

1.全列查找 ：

select * from 表名;

2.指定列查找 ：

 select 列名， 列名 form 表名;

3.指定表达式 ：

select 表达式 from 表名;

（往往是列和列之间的运算，得到的结果是一个临时表，不会影响到咱们 原始的数据库的数据）

4.指定别名 ：

select 表达式 as 别名 from 表名;

此处 的别名 就相当于 查询结果的临时表的列名。

5.去重

select distinct 列名 from 表名;

就会根据 指定列的 值， 来进行 去重。

6.排序：

select 列名 from 表名 order by 列名 / 表达式 asc / desc;

7.条件查询：

select 列名 from 表名 where 条件；

数据库服务器会遍历 表中的 记录，依次 带入到条件中，如果条件成立，则这个记录就被保留下来。如果条件不成立 则直接跳过。

这里条件 就涉及到 一些 运算符。

<=is nulllike<=<=>is not nullbetween…and…>andor!=>=innot<>

8.分页查询

select 列名 from 表名 limit N offset M;

从 M 条 记录 开始 ，一共返回N个

1.修改（Update）

语法：

 update 表名 set 列名 = 值 , 列名 = 值 where 条件;

注： update 与 表名之间没有单词连接

新增 ： insert 和表名之间 有 个 into

查询 ： select 和表名之间 有个 from

另外 这里表名 与 列 名之间的 set 不能 省略， where 条件 是 针对 那些 行 进行 修改 （符合条件的行就会修改，不符合就不会修改，这里如果 where 省略就是修改所有记录）。

这里 where 和 之前 select 那里 的 where 是一样的。另外 where 之外，像

order by 

和

limit

也是 可以使用的。

重要：

这里 update 是 会 修改数据库服务器上面的 原始 数据的！！！

下面 来看 几个 具体 例子

演示 ： 将孙悟空 同学的 数学成绩 变更 为 80分。

1.这里我们 先通过 条件查询 查找 到孙悟空同学的 语文成绩（方便下面对比）。

2.通过 update 将 孙悟空同学的 成绩 变更为 80分

可以看到这里 将 原来的 87.5 改为了 80。

注意： where 后面的 条件 是非常 重要的，这个条件 直接 决定了有多少个记录受到了影响。

如果这里的条件 写错了 修改的 范围 就会 更大 或者 更小。（这里是没有直接的撤销操作的，只能反向修改回去）

这里的 修改 操作 也是比较危险操作，这里 修改 如果 出现 误 操作 可能 危害 比 删库还来的 严重 ！！！

这里 对于删库 操作 ，一般的 公司 都会有 “预案”， 可以 快速 将 数据 给 还原 回来。

但 对于 误 修改 操作， 就会 出现 有的 修改了 ，有的 没修改， 应该 修改的 没有 修改，不应该 修改 的 修改了 。 这就会导致 数据 还原的 时候就需要，仔细 校对

那些 需要修改，那些 不需要 修改，这时 就会 耗时 耗力 耗人。

上面我们 是 一次 修改 一列 ，下面我们来 一次修改两列

演示： 将 曹孟德同学的数学成绩变更为 60分， 语文成绩变更 为70分

1.查询出曹孟德同学的 数学和 语文成绩 （方便下面对比）

2.通过 update 修改 曹孟德同学的 数学和 语文成绩

相比之前 的 数学 84，语文 82 这里 我们就成功 修改完成。

修改完 两列 后 ，我们 来 搭配着 排序（order by）和 分页（ limit）来完成修改。

演示： 将总成绩倒数前三的 3 同学的数学成绩 加上 30 分

这里 就需要 根据总成绩 排序，同时 limit 筛选前 三 再去 执行 这里的 + 30 操作

1.查询总成绩倒数前三的 3为同学 （方便下面对比）

这里我们 就 看到 了 他们的 总成绩 ， 这里 顺便 看看 他们其他的成绩（每一门课的具体成绩）。

2.使用 update 将总成绩倒数前三的 3 同学的数学成绩 加上 30 分

回忆 一下 我们之前创建 表头 是如何 写的 是不是 decimal(3,1) 这里 的3 的意思 就 是有效数字 3 位 ， 1 的 意思 是保留一位小数

这里 刘玄德 的 数学 成绩 85.0 + 30 = 115.0 此时 这个数据 就不是 合法的（4位有效数字，超出指定范围）。 所以 会报错。

这 就 我们 就更改位 加 10 操作。

这里 一共匹配到了3 行 但 只 修改了 两行 是 因为 其中 有 一行 是 张三 他的数学成绩 是NULL (NULL 进行 运算 结果 还是 NULL，不会发生变化)，所以这里 只会修改两行。

这里 可以看到 他们 宋公明 和 刘玄德 数学 都成功 的 加 了 10 但是 还是 倒数前3 .

修改中 使用 排序 （order by） 和 分页 （limit）演示完，那么我们 来 修改 表里的 全部数据

演示 ： 将所有的 同学 的语文成绩 更新为 原来的 一半（这里 更新为 原来的2 倍 可能会出现 超出有效位数）

这里我们 要修改 所有同学的 语文成绩，那么 只要不加上 where 即可

1.查询 全部同学的 语文成绩（方便下面对比）

2.通过 update 将 所有 同学的语文成绩 变为 原有的 一半

这里 语文成绩 就 修改完成了 。

补充： 通过

show warnings

来 查看 当前警告的详细情况。

下面继续 让 语文成绩 变为 原来的一半（相当于最开始的成绩 除以 4）

通过

show warnings

查看 详细 情况

这里就算 语文那列 ，数据 发生了截断。（第一行 和 第 七行）

2.删除（Delete）

语法：

delete from 表名 where 条件

演示 : 删除 数据

1.显示 全部 全部记录（方便下面对比）

2.删除 id 为 7 的 记录

可以看到这里 id 为 7 的就被删除了。

注意： 一旦 这里 的条件写错了，可能影响的范围就会很大。

如果 不写条件，就会把整个表给删除掉（这个 与 drop table 不一样,drop table 是将 整张表删除掉了，而delete 则是 将 表里的数据 全部删除，表还在）

可以看到这里就将 表中的全部数据 删除，得到 了 一张空表。 [（drop table ） 直接表 都删除了。]

增删查改我们这里 差多 就 这么多 了 ，下面我们来学习一下 数据库的 约束。

数据库的约束

约束就是 数据库在使用的时候，对于里面能够的数据提出的要求和限制。

程序猿就可以借助约束来完成 更好的校验。

下面来看一下我们 的 约束类型

约束类型

类型说明not null指示某列不能存储 NULL 值。unique保证某列的每行必须有唯一的值。default规定没有给列赋值时的默认值。primary keyNOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）
有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。foreign key保证一个表中的数据匹配另一个表中的值的参照完整性。check保证列中的值符合指定的条件。
对于MySQL数据库，对CHECK子句进行分析，但是忽略CHECK子句。
注意：

1. not null 如果尝试 插入 空值（null） 就会直接报错。
2. unique 表示 数据 唯一 （如果尝试 插入 重复 的 值 也会 报错）。
3. default 约定 一个 默认值。
4. primary key 主键 约束 相当于 数据 的唯一 身份标识（类似 身份证 号码/ 手机号）
5. foreign key 外键 描述两个表之间的关联关系，表1 里的 数据 必须在 表2 中 存在。
6. check 指定 一个 条件，通过条件 来对值 进行判定。（但是 MySQL 并不支持这个，这里不过多介绍）。

补：上面 说 的这些 约束，都是针对 每个 列 单独设置只是 针对这个列来说，不同列之间没有影响。

1.not null

要求数据不为空。

演示 ： 通过 not null 创建 一张表

这里 可以看到 NULL 那 一列 中 的 id 那列 显示 NO（这里 创建 表的时候 id 使用 not null 创建，此时 NULL 那列 就会 变成 NO），这里就无法插入控制。

这里我们 就来尝试 插入 空置

这里 就 会 报错 错误 信息为 id 那一些 不能 空， 这里 就插入 失败了。

注意 ： 这里 的 not null 可以 给 任意 个 列 来进行 设置的，不仅仅 是 这一个列，

演示 ： 将 id 和 name 这 两列 都设为 not null

这里 就 成功 创建 了 两列（id，name） 都是 非空的.

2.unique

要求数据是 唯一的。

演示： 通过 unique 创建 一张表

下面 我们来 显示 这张 表结构 查看 有啥 不一样的 地方。

下面我们 来尝试 对这张表进行 插入

可以看到 我们 成功 插入，那么我们 在 插入 一个相同 的数据 （id 1， name 张三）

可以 看到这里 就报错了，错误 信息 就为 出现了 重复 的 entry(条目 也就是 记录)， duplicate （重复）

这里 的 entry 是不是很眼熟 ：

回忆一下 我们 之前 学过的 Map， Map 中的 entry 也是 条目的 意思。

Map 中里面存放 的 键值对，Map 又没实现 lterable 接口，如果 想要遍历 一个Map ，就需要先把Map 给转换 成 Set. Set里面的 元素，就叫做一个 entry，就包含 一个键 和 一个值。

另外 ： 这里 MySQL 是 如何 发现新的数据 和 之前 的数据是重复的呢， 其实 ，这里的插入过程，其实 类似 于 二叉搜索树的 插入，

这里的 内容也 与 “索引” 是 相关联的（这里的 索引 后面会 说到）。

3.default

约束 一个 默认值。

MySQL 中 默认的 默认值：

演示：只 插入 id 没插入 name 观察 MySQL 中的默认值

演示 ： 将 默认的默认 值 改成 需要的 的内容（约束 一个 默认值）

通过 插入 观察 默认 值 是否 修改了 （这里 仍然是 直插入 id 这 一列， 不插入 name ，name 采取 默认值）

4.primary key

主键 约束 相当于 数据 的唯一 身份标识（类似 身份证 号码/ 手机号）

注意： 这个是 日常开发的 时候最长使用的时候最长使用的约束 （最重要的约束）。

创建表的时候，很多时候都需要指定主键。

演示：

这里 id 这里 就不能为空 ，另外 对于一个表来说，只能 有一个列被指定为主键。

演示 ： 这里来 插入 id 为空 的 值

可以发现 这里 跟 not null 插入 空报错 一样。

演示 ： 插入 重复 值

可以看到这里 报出的 错误 ，与 unique 插入 重复 的元素 报错 一样。

这里 我们 逐渐的 典型 用法，就算 直接使用 1，2，3，4 整数递增 的方式进行表示（MySQL 里面对于这种递增的主键，是由内置支持的，称为 “自增主键”）。

语法

primary key auto_increment

这里 auto自动， increment 增加，

这里我们 在通过 desc + 表名 来查看 表结构 看看有啥不同。

这里我们 设定好了 自增 主键 之后，此时 插入的记录，就可以不指定 自增 主键的值了（直接使用null表示），交给 mysql 自行 分配即可。

演示：

那么我们在插入 一个呢？？

这里 就可 得出 每次 新增加 一个 新的记录，都会产生 一个 自增 的 id。

补： 这里 自增 主键 也是可以 手动分配

下面 就只能 从 10 开始 自增了。

结论：自增主键也是可以 手动指定 id 的 一旦 指定 过后，后续新插入 的数据就都是从 指定 id 后的来排 。

5.foreign key

外键约束， 针对两张，进行关联。

这里 是 如何 关联的呢？

这里就来 举一个 典型的例子：

假设这里 有两张表 一张为 学生表另外 为 班级表

学生表（id，name, classId）
idnameclassId （班级id）1张三12李四13王五2
这里 每个 学生 都得属于 一个 具体 的班级，这个班级的存在 ，如果 学生表 这里 出现一个 记录 班级 id 是 100 这样的 数据 就是 非法的。

班级表 （id，name）
idname1高一 一班2高一 二班3高一 三班
这里 学生表中 的 classId 就 参照了 班级表中的 id ， 这里 就认为 学生表 中的 classId 这一列 就依赖了 班级表 中的 id 这一列。

这里就可以使用外键 约束 来描述 这种 关系 。 （学生表 依赖了 班级 表，学生表 称为

子表

， 班级表称为

父表

)

演示：

1.先创建出 班级表 并插入 高一 （1 - 3 ）班

通过 select * from class 查询表

2.构造学生表，来指定一下 外键约束

附上代码：（有点长 ）

create table student (studentId int primary key auto_increment, name varchar(20), classId int, foreign key (classId) references class(classId));

这里 foreign key 是一个 固定内容 这里 classId 是 学生表中 classId这 一列,[references (引用)]这里 通过 references 引用 class(表名) 这张表 classId 就是班级表 中 这一列。 

这里 引用 就是表示 依赖 关系， 当 前 这个表（学生表）的 classId 列 就 得 引用 /依赖 与 class表的 classId 这一列

外键 约束 ， 描述的是两张表的两列 之间的"依赖关系"

子表依赖于父表 （子表引用自父表），要求子表中对应的记录得 在父表中存在。

下面我们 就来插入 几条 记录：

1.查询 创建好的两张表 （方便下面对比）

2.插入 数据

这条记录中 指定 的classId 为 1 ，在 class 表中 存在 ，就可以 成功插入（在插入记录之前，mysql 会先拿这个classId 去class表里查一下，看看是否 存在，存在才能插入成功）

反例： 插入 class表中 不存在 的 id

可以看到这 就失败了， 这条记录 指定 的 classId 为 100 他在 class 表中 不存在，不能插入 成功，这里我们 来看一下 错误提示， constraint（约束）

这里我们 来 来插入 几条 记录

这里 classId 2 和 3 在 class 表中 都存在 那么 就可以 成功插入。

这里 我们 插入 成功 后 我们 想要 将 学生表 中 张三 的 班级 id 从 1 改成 3 看看 能不能成功。

可以看到这里 我们成功 的 将 张三 的 classId 从 1 改 成 了 3

那么 我们 改成 100呢 ？

可以看到这里我们 这里 报错 了 ，仔细观察 与 上面 新增 报错的 信息 是 一样的，那么 这里 我们 可以得出 ：

不仅仅是新增 的时候要考虑 到外键 约束，新增 成功的数据 如果进行 修改 也一样是存在问题。

补： 上面的 操作 我们都是 针对子表 ，这里外键 约束同样 也在约束 父表。

演示： 删除 父表中 classId 为 2 的列

1. 先查询 子表 和 父表

父表 :

子表 :

可以看到 子表 中 李四 依赖 父表 中 的 2 ，

注意：可以看到 这里 删除不了，这里 就 印证 了

外键约束,同样 也在约束着父表，当父表中的某个记录被子表依赖着的时候，此时尝试进行删除或者修改，都会失败

我们 发现子表 中没有 依赖 父表 中 的 1 那么这里我们 来删除 一下 父表中 的 id 1

演示：

这里 我们 删除 记录 不行，那么有没有 暴躁老哥 想要直接删除 表的呢？？

可以看到 我们 直接删除 表 也是 不行的 。

看完了 操作 ，这里我们 来 补充 一下 外键 约束 的工作 原理：

在子表当中插入 新的记录的 时候，就会先根据对应 的值，在父表中先查询，查询到之后，才能够执行后续的插入。 （另外这里 的查询操作可能是一个成本比较高的操作（比较耗时））。 这里外键 约束其实 要求 ，父表中依赖的这一列，必须要有索引，有了索引 就可大大提高查询速度。 这里 class表 的 classId 这一列就得是primary key 或 unique （有了 这两 约束的 列 就会自动 的创建出索引了） （索引 后面 会将，这里就不细谈）。

下面 在补充一个场景：

淘宝 购物 ：

是不是 会有 商品表 （保存了 商品信息）

商品 id

和

商品 名字 

…

有了 商品 表， 我们 购买 的 物品是不是 还有订单表 （保存订单信息）

订单id

和

商品 id

…

这里 按照 外键 约束 是不是 可以 认为 ，商品表 为 父表，订单表 为子表，订单表中的 商品 id 应该 在 商品表中的商品id 中存在。

场景 有了 那么我们 需要 考虑 下面的 这个种 情况， 这里我们 买了 一件 物品，过了 很久 我们 又 想要 再次购买这件 物品， 这里结果发现 这个商品 下架了（被从商品表删除了） 。

难么 这里 的商品 是 如何 删除的 呢 （这个数据是 如何删除的呢）？

按照 刚才的 外键 约束 ，一旦 建立好了 约束关系，此时 父表的记录不能随便删除，如果 这个 记录被子表 依赖 此时就无法删除了。

这 商品 下架 删除，我们的 订单表 是不是 不应该被删除 （用户 随时 可能 查看这里的订单），那么这里的 依赖 关系 是不能 解除 ，那么是 咋 删除的呢？？

这里采取的办法，不是 真正 的删除，而是"逻辑上的删除"给商品 表 加一个 列， 如： isOk （其他名字也行）

如果 这一列 值为 1 表示这 是 一个有效的记录，

如果 这一列 值为 0 表示这 是 一个无效的记录（相当于被删除了）