当前位置：首页 > article >正文

MySQL表的增删改查(CRUD3约束)

article 2024/11/6 5:24:36

这次我们开始先不复习嗷，等到把数据表的删除说完咱们统一，总结书写

1.数据表的删除：

语法：

1. 使用 DROP TABLE 语句删除单个表

基本语法：DROP TABLE [IF EXISTS] table_name;
- table_name是要删除的表的名称。
- IF EXISTS是可选的。如果指定了IF EXISTS，当要删除的表不存在时，MySQL 不会抛出错误，而是发出一个提示。
示例：
- 假设要删除名为student的表，可以使用DROP TABLE students;。
- 如果不确定students表是否存在，为了避免报错，可以使用DROP TABLE IF EXISTS students;。例如在执行数据库清理脚本时，表可能已经被删除，使用IF EXISTS就很合适。

2. 删除多个表

语法：DROP TABLE [IF EXISTS] table_name1, table_name2, …;
示例：如果要同时删除students表和teachers表，可以使用DROP TABLE students, teachers;。使用IF EXISTS的版本则是DROP TABLE IF EXISTS students, teachers;。这种方式在需要一次性清理多个相关或不相关表的场景下很有用，比如删除一个测试数据库中的所有临时表。

3. 涉及外键约束时的表删除

如果要删除的表是其他表的外键关联表，有以下两种情况：
- 先删除关联关系或其他相关表：可以先使用ALTER TABLE语句删除外键约束，或者先删除引用该表的其他表中的相关数据，然后再删除目标表。
- 使用 CASCADE 选项（如果数据库支持）：某些数据库管理系统支持在DROP TABLE语句中使用CASCADE选项来自动删除与该表相关的依赖对象（如外键约束等），但 MySQL 的标准DROP TABLE语句没有CASCADE选项用于处理外键。需要手动处理外键约束

注意：

删除 MySQL 数据表时一定要谨慎，因为这是一个不可逆的操作，数据一旦删除就无法恢复。在执行删除操作之前，建议先备份数据库或者相关表的数据。

delete的时候，如果不加where条件会出现什么问题?

如果删除时不加条件限制，那么整张表的数据都会被删除掉(清空)
非常危险的操作
但是数据是可以恢复的，每一条执行的SQL都会被记录到日志中，把日志中记录的操作，再执行一遍基本上就可以完成恢复
在生产环境中一般不去使用delete操作
一般在表中会加一个deletestate字段，用来表示这条记录是否删除，0表示正常(没有删除)1表示已删除用update操作去更新deleteState字段，就可以实现删除功能，这条被删除的数据并没有实质上删除掉而是始终存在与数据库

2.对数据表的总结：

1.新增-插入
insert into 表名[(列名[, 列名][,列名]..)] values (值[,值][, 值].);

# 插入时列名与值的个数--对应

2.查询操作
a.全列査询 select*from 表名;

#查询表中所有的列，如是不加条数限制，会把表中所有的记录全部都查出来
b.指定列査询 select 列名[, 列名],列名]... from 表名;

# 按实际需要指定要查询的列
c.列名为表达式 select 列名/表达式 from 表名;

# 表达式可以是常量，也可以是多个列的运算

d.查询中使用别名 select 列名/表达式 as 别名 from 表名;

#as可以省略，别名可以是任意的字符串，如果字符串中包含空格，字符串用单引号引起来

e. 去重查询 select disctinct 列名[,列名],列名].. from 表名;

# 如果查询多个列，去重时，所有列都相同才被判定为两行数相同

f.排序 select* from 表名 order by列/表达式/别 asc|desc;

# asc 升序，小的在前，desc降序，大的在前
g.条件査询 select* from 表名 where 列名/表达式比较|逻辑运算符 [order by子旬];

# where 中只能写列名或表达式，不能使用别名

h.区间查询 select * from 表名 where 列名 between 开始条(件 and 结東条件;

#等价与开始条件<=列名<= 结束条件，列名>=开始条件 AND 列名<=结束条件

i.模糊査询 select* from 表名 where 列名 like'%值 ;

# %可以匹配0个或任意多个字符，只能匹配一个字符
j.分页查询 select* from 表名 [where 条件][order by 列名 asc|desc] limit num;

#查询结果集中从0开始的前num条数据
select* from 表名 [where 条件][order by 列名 asc|desc] limit start num;

#从第start条开始，向后取num条数据

select* from 表名 [where 条件] [order by 列名 asc|desc] limit num offset start;

#从第start条开始，向后取num条数据
3. 更新操作

如果不加where条件，那么会导致表中所有的记录都被更新，危险操作
update 表名 set 列名=值[, 列名=値][,列名=值]... where 条件 order by 列名 asc|desc limit n;
4.删除操作
如果不加where条件，那么会导致表中所有的记录都被删除，危险操作
delete from 表名 where 条件 order by 列名 ascldesc limit n;

3.数据库约束

数据库约束是关系型数据库的一个重要功能
主要作用是保证数据的完整性，也可能理解数据的正确性(数据本身是否正确，关联关系是否正确)数据库会帮我们做校验工作人工检查数据完整性的工作量非常的大，在数据表中定义一些约束，那么数据库写入数据的时候，
约束一般是指定在列上的

1. 非空约束（NOT NULL）

定义：非空约束确保列中的值不能为空值。它强制列必须包含有效的数据。
NOT NULL-指示某列不能存储 NULL 值。

我们看第三行如果没有指定非空约束时，当前的列是可以写入一个NULL值的
如果要把某一列定义为一个必填项，那么就可以使用not null(非空)约束

我们在创建（数据表）时，可以直接添加表约束，让某一个数据不能为空，

当我在查看表结构时就会发现第三行的NULL中名字（NAME）为 NO

此时 NO 表示当前列不能为空：表示必须有值
YES 表示当前列可以为空：不一定有值，可以为空

1.非空列有值时可以正常写入

2.写入数据时会报错，提示不能写入NULL值数据库帮我们做了一次校验

2. 唯一约束（UNIQUE）

定义：唯一约束确保列或列的组合中的值在整个表中是唯一的。与主键不同，唯一约束列可以包含 NULL 值（但只能有一个 NULL，因为多个 NULL 被认为是相等的，违反唯一性）。
UNIQUE-保证某列的每行必须有唯一的值。
某列的值在整个表中不能重复，比如说身份证号，学号

不加唯一约束的时候，可能出现编号相同，但是人名不同的情况不符合逻辑
创建一个在ID字段加唯一约束的表

我们可以看到当填加了唯一约束（UNIQUE）后，id为1的数据不能被第二次插入

3. DEFAULT-规定没有给列赋值时的默认值。

插入时只指定了ld，这时name列使用默认值填充
当为某列设置了默认约束的时候，如果不给这个列指定值才会使用默认值

虽然指定的默认约束，"但是当我们手动指定这一列的值为NULL时插入的值依然是NULL，因为这个NULL 是我们自己手动指定的，也可以理解为我们想要的值用户指定的优先级要高于默认约束

4.主键约束（PRIMARY KEY ）是 NOT NULL和 UNIQUE的结合。

1. 主键定义

主键（Primary Key）是数据库表中用于唯一每条标识记录的一列父母列的组合。
主键中的值必须是唯一的，且不能为NULL。
每个表只有一个主键。

2. 主键墩

唯一性：主键列中的值必须唯一，不能重复。
非空性：主键列不能包含NULL值，保证每行数据的完整性。
自动索引：MySQL会自动在主键列上创建索引，这样可以加速数据查询。
不可更改性：通常主键列的值不会修改，这是因为修改主键会涉及到大量的关联更新操作。

3. 定义主键的方法

MySQL中可以通过以下几种方式定义主键：

3.1 创建表时定义主键

在创建表时可以直接指定主键，语法如下：

例如

3.2 使用多列定义主键（复合主键）

当一张表的唯一性需要多个列一起保证时，可以创建一个复合主键：

3.3 使用`ALTER TABLE`语句添加主键

对于已存在的表，可以使用ALTER TABLE语句来添加主键：

ALTER TABLE 表名 ADD PRIMARY KEY (列名);

4. 删除主键

如果需要删除主键，可以使用ALTER TABLE语句删除：

ALTER TABLE 表名 DROP PRIMARY KEY;

例如

注意：删除主键后，如果表中的其他列仍需要唯一性和非空约束，则需要单独添加这些约束。

5.主键的自动增长（AUTO_INCRMENT）

主键通常与AUTO_INCREMENT属性结合使用，使主键自动递增。只有当主键为整数类型时，才可以使用AUTO_INCREMENT。

这样，每次插入新记录时，id列会自动增加1。注意，一个表中只能有一个AUTO_INCREMENT列，且必须是主键或唯一键。

6. 主键和外键的关系

主键和外键通常一起使用以维护数据库的缺陷。外键引用另一个表中的主键，这样可以在不同的表之间建立关系。例如：

在employees表中，dept_id是一个外键，引用了departments表中的dept_id主键。这就保证了employees表中的dept_id值在departments表中是存在的。

7. 主键的注意事项

唯一性和非空性：确保主键列没有重复和NULL值。
性能问题：主键列通常不适合间隙更新，因为它是数据的唯一标识，更新会导致索引重建，影响性能。
合适的数据类型：优先使用较小的整数类型（如INT或BIGINT）作为速度主键，以减少存储空间并提高查询能力。

8.主键设计建议

使用列主键：如果可能，尽量使用单一列主键（如自增单一的整数类型），避免使用复合主键，简化查询。
自增主键：自增的整数主键可以简化插入操作，适用于大多数场景。
不可变性：主键一旦设置，应尽量避免修改，以保证数据的一致性。

9. 博主总结

主键约束帮我们校验了非空和唯一，这两个校验在写入数据时对效率是有一定影响但是比起不做校验来说，这个性能消耗还是可以承担的而且主键对后面讲的索引起到了非常重要的作用

强烈建议为每张表定义一个主键

4. 外键约束（FOREIGN KEY）

定义：外键用于建立两个表之间的关联。它定义了一个表中的列（或列组合）与另一个表中的主键（或唯一键）之间的引用关系。外键约束可以保证数据的参照完整性，即外键列的值必须是它所引用的表中主键列的值或者为 NULL。

1. 外键定义

外键（Foreign Key）用于在表之间建立和保持关系。
外键字段中的值必须是另一张表的主键或唯一键中存在的值。
通过外键，可以在不同的表之间形成“父子关系”，保证数据之间的依赖性和一致性。

2. 外键特性

引用缺陷：外键保证一个表中的数据在另一个表中有对应的值，防止出现“孤立”记录。
约束数据操作：通过外键可以限制删除和更新操作，保证数据的正确性。
级联操作：可以通过外键设置级联删除或更新规则，使得父表中的变化自动反映到子表中。

3. 定义外键的方法

在MySQL中，可以在创建表时直接定义外键，也可以通过ALTER TABLE语句为已存在的表添加外键。

3.1 创建表时定义外键

在创建表时直接定义外键，语法如下：

在这个例子中，employees表的dept_id列是一个外键，引用了departments表中的dept_id主键。这确保了在employees表中，每个dept_id都必须在departments表中存在。

3.2 使用`ALTER TABLE`添加外键

可以使用ALTER TABLE语句为已存在的表添加外键约束：

例如：

ALTER TABLE employees
ADD FOREIGN KEY (dept_id) REFERENCES departments(dept_id);

4.外键的级别操作

MySQL支持以下几种常见的级别联操作，用于在父表数据被修改或删除时对子表数据的处理：

ON DELETE CASCADE：当父表记录被删除时，自动删除子表中所有相关的记录。
ON DELETE SET NULL：当父表记录被删除时，将子表中的外键列设为NULL（前提是该列允许为NULL）。
ON DELETE RESTRICT：当父表记录被删除时，如果子表中存在该引用记录的数据，则阻止删除操作（默认行为）。
ON UPDATE CASCADE：当父表记录的主键被更新时，自动更新子表中所有相关的外键。
ON UPDATE SET NULL：当父表记录的主键被更新时，将子表中的外键列设为NULL。

注意：MySQL的InnoDB存储引擎支持外键和级联操作，MyISAM不支持。

在这个例子中，order_items表中的order_id外键设置了ON DELETE CASCADE和ON UPDATE CASCADE。这意味着如果orders表中某个订单被删除或更新，order_items表中引用该订单的行会自动删除或更新。

5. 删除外键

如果需要删除外键约束，可以使用以下ALTER TABLE语句：

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

外键名称可以在创建外键时指定，也可以通过SHOW CREATE TABLE 表名;查看表结构来找到。

6. 外键的注意事项

数据类型匹配：外键列和被的主键列必须具有引用相同的数据类型。
索引要求：MySQL中，被引用的父表列必须是主键或具有唯一索引的列。
存储引擎：MySQL的InnoDB存储引擎支持外键约束，但MyISAM不支持。如果表使用的是MyISAM，外键不会生效。

7. 外键的设计建议

使用合适的级别联策略：根据业务逻辑选择合适的级别联删除和更新策略。
减少外键数量：在高并发的情况下，外键会增加数据库的锁定和检查压力，因此在性能要求的表中尽量减少外键数量。
避免循环外键引用：避免两个互相引用对方的外键，这样会导致复杂性增加。

8. 总结

MySQL的外键约束主要维护表之间的关联性，保证数据之间的关联性和一致性。合理使用外键和级联操作可以简化数据管理，避免“孤立数据”问题。在性能要求的上述场景中，应详细使用外键，权衡性能和数据缺陷之间的关系。

5. 检查约束（CHECK）

定义：检查约束用于限制列中的值必须满足指定的条件。例如，可以限制某列的值在一定范围内。
创建方式：
- 创建表时定义（不过 MySQL 对检查约束的支持有限，在某些情况下可能不会完全按照预期执行），例如：
- CREATE TABLE employees (
- id INT PRIMARY KEY,
- salary DECIMAL(10,2) CHECK (salary > 0)
- );

以上代码salary DECIMAL(10,2) CHECK (salary > 0)表示检查salary的值是否大于0；

虽然 MySQL 支持语法上的检查约束，但在执行插入或更新操作时，它可能不会像其他一些数据库那样严格执行检查。不过，从 MySQL 8.0.16 开始，对于通过CREATE TABLE或ALTER TABLE语句创建的检查约束会有更完善的支持。