DML数据操作

1. MDL数据的增删改（重点）

1.1 数据的增加操作

基本语法：

insert into 数据表名称([字段1,字段2,字段3...]) values (字段1的值,字段2的值,字段3的值...);

说明：

• INSERT INTO：表示插入数据的操作。

• 数据表名称：替换为您要插入数据的实际数据表的名称。

• 字段1, 字段2, 字段3...：替换为数据表中要插入数据的列名。确保列名的顺序和数量与VALUES子句中的值一致。

• 字段1的值, 字段2的值, 字段3的值...：替换为要插入到相应列中的值。确保值的顺序和数量与列名一致。

其他写法：

• 可以省略字段列表，此时要确保插入的值的顺序和数量与表中的列一致，如：

INSERT INTO 表名称 VALUES (值1, 值2, 值3);

注意事项：

• 插入的值需要与相应列的数据类型匹配，否则可能会引发错误。

• 如有需要，可以省略某些列，但是在列名和VALUES子句中的值的数量仍然需要一致，且省略的列需要有默认值或可为空。

• 插入的数据应符合数据表的约束条件，例如

  INSERT INTO 表名称 (字段1, 字段2) VALUES (100, 200);

  主键或唯一约束。

• 在SQL语句中，对于字符串类型的值，需要使用引号（单引号或双引号）将其引起来。但其他数据类型（例如数字、日期等）的值并不需要使用引号。

例如：

插入字符串值时，需要使用引号引起来：

INSERT INTO 表名称 (字段1, 字段2) VALUES ('字符串值', '另一个字符串值');

插入数字值时，不需要使用引号：

INSERT INTO 表名称 (字段1, 字段2) VALUES (100, 200);

插入日期值时，通常也不需要使用引号，但取决于数据库的日期格式设置：

INSERT INTO 表名称 (字段1) VALUES ('2022-01-01'); -- 日期格式可以根据数据库设置而有所不同

2.2 数据的修改操作

基本语法：

 update 数据表名称 set 字段1=更新后的值,字段2=更新后的值,... where 更新条件;

特别说明：如果在更新数据时，不指定更新条件，则其会把这个数据表的所有记录全部更新一遍。

例：

2.3 数据的删除操作

基本语法：

delete from 数据表名称 [where 删除条件];

delete from与truncate用于删除数据表中的数据：

delete from 数据表;

​truncate 数据表;

delete from与truncate的区别？

• delete：删除所有数据记录

  • 数据操作语言（DML）。

  • 只删除数据，主键自增序列不清零。

  • 可以配合WHERE子句使用，以指定删除的数据条件。

  • 使用这条语句时，MySQL会逐行删除数据，并在事务日志中生成相应的日志记录。因此，如果表中有较多的数据行，执行速度可能较慢。

• truncate：删除所有数据记录，并重置自增主键（如果有）

  • 数据定义语言（DDL）。

  • 主键自增序列清零，重置自增主键（如果有）。

  • 不支持WHERE子句，无法指定删除的数据条件。

  • 这条语句会一次性清空整个数据表，而且不会生成事务日志。相因，执行速度通常比DELETE FROM快得多

2. SQL约束

2.1 主键约束

（1）主键约束介绍

主键约束（PRIMARY KEY constraint）用于唯一标识数据库表中的每条记录。

它有以下特点和要求：

• 唯一性：主键必须包含唯一的值，确保每条记录都可以被唯一标识。

• 非空性：主键列不能包含NULL值，确保每条记录都具有一个有效的标识。

• 单一性：每个表应该有一个主键，并且每个表只能有一个主键。这是确保表中每条记录都有唯一标识的基本要求。

主键约束的作用是提供唯一性和标识性，它有以下几个方面的好处：

• 通过主键，我们可以精确地定位和访问表中的每条记录，从而提高检索和查询的效率。

• 主键约束可以防止数据中出现重复或重复的标识，确保数据表的完整性和一致性。

• 主键约束还可以用于建立与其他表之间的关系和数据关联。

（2）遵循原则

• 主键应当是对用户没有意义的：主键是用于唯一标识表中的每条记录的，因此理想情况下，主键不应该包含任何具有业务含义的信息。主键的作用是提供唯一性，而不是用于描述记录的某个特征或属性。

• 永远也不要更新主键：主键的值用于标识和索引记录，如果更新主键值，则可能导致记录无法被正确定位和引用。因此，应该避免更新主键的值。如果需要修改某个记录的主键标识，通常的做法是插入新记录，再删除旧记录。

• 主键不应包含动态变化的数据：主键应该是稳定的、不变动的值，不应该包含随时间变化的数据，如时间戳、创建时间列、修改时间列等。动态变化的数据会增加主键维护的复杂性，并可能导致索引效率下降。

• 主键应当由计算机自动生成：为了简化主键的管理和确保唯一性，通常使用自动增长（Auto-increment）方式生成主键值。这样，数据库会自动为新记录分配一个唯一的主键值，而无需手动指定。

遵循这些原则，可以提高数据库的性能和可维护性，减少数据冗余和错误，同时简化开发和管理工作。

（3）创建主键约束

在MySQL中，可以使用以下几种方式来创建主键约束：

方式一：在MySQL中，有一种简化语法，在字段定义之后直接使用PRIMARY KEY来指定主键约束。这种方式适用于只有单列作为主键的情况。

语法如下：

CREATE TABLE 表名 (
    列名1 数据类型 PRIMARY KEY,
    列名2 数据类型,
    ...
);

方式二：使用自动增长列作为主键

这种方式适用于只有单列作为主键的情况。

常见的一种方式是使用自动增长列作为主键。

在创建表时，可以将某一列的数据类型设置为整型（INT或BIGINT）并添加AUTO_INCREMENT属性，然后将其设置为主键。这样每次插入新记录时，该列的值会自动递增，确保主键值的唯一性。

CREATE TABLE 表名 (
    列名 数据类型 PRIMARY KEY AUTO_INCREMENT,
    ...
);

方式三：在创建表时直接指定主键约束

在创建表时，可以使用PRIMARY KEY关键字来直接指定主键约束。在列定义之后，使用PRIMARY KEY关键字指定希望作为主键的列名。一个表可以有多个列作为主键，只需将它们逗号分隔在PRIMARY KEY关键字的括号中。

CREATE TABLE 表名 (
    列名1 数据类型,
    列名2 数据类型,
    ...
    PRIMARY KEY (列名1, 列名2, ...)
);

列出的列名构成了主键的复合部分。主键约束的作用是确保这些列中的值的组合在整个表中是唯一的。

方式四：在创建表后添加主键约束

如果已经创建了表，但没有定义主键约束，可以使用ALTER TABLE语句添加主键约束。使用ADD PRIMARY KEY关键字将主键约束添加到已存在的表中。可以一次指定一个或多个列名作为主键，多个列名之间用逗号分隔。

ALTER TABLE 表名
ADD PRIMARY KEY (列名1, 列名2, ...);

通过这种方式添加的主键约束与直接在表创建时指定主键约束的效果是一样的。

无论使用哪种方式，主键约束的目标都是确保在表中每个记录都具有唯一标识，并且主键值不允许为NULL。在添加主键约束之前，要确保列中不存在重复的值，否则会导致主键冲突的错误。通过添加主键约束，可以提高数据的完整性和性能。

（4）补充：自动增长

我们通常希望在每次插入新记录时，数据库自动生成字段的值。

我们可以在表中使用 auto_increment（自动增长列）关键字，自动增长列类型必须是整型，自动增长列必须为键(一般是主键)。

下列 SQL 语句把 "Persons" 表中的 "Id" 列定义为 auto_increment 主键：

create table persons3(
  id int auto_increment primary key,
  name varchar(255),
  address varchar(255),
  city varchar(255)
) default charset=utf8;

2.2 非空约束

非空约束是一种数据库约束，用于确保表中的某个列不接受空值。当为表的某个列定义了非空约束后，插入或更新该列时，必须提供非空的值。

非空约束的作用包括：

• 数据完整性：非空约束确保了数据的完整性和一致性。它防止了空值的插入，使数据更加可靠和准确。

• 查询结果过滤：定义非空约束后，可以确保查询结果中不会包含空值，从而过滤掉无效或缺失的数据。

• 查询性能优化：非空约束可以帮助数据库优化查询执行计划，提高查询性能。数据库可以利用非空约束来避免不必要的空值处理操作，从而提高查询效率。

理解：

NOT NULL 约束强制列不接受 NULL 值。

NOT NULL 约束强制字段始终包含值。

这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

在MySQL数据库中，可以使用以下几种方式来定义非空约束：

CREATE TABLE 表名 (
    列名 数据类型 NOT NULL,
    ...
);

ALTER TABLE 表名 MODIFY 列名 数据类型 NOT NULL;

2.3 唯一约束

唯一约束是一种数据库约束，用于确保表中的某个列或一组列的值是唯一的，即不允许重复值。唯一约束是一种数据完整性措施，它可以保证数据的唯一性和一致性。

唯一约束的主要特点如下：

• 唯一性：唯一约束要求表中指定列或一组列的每个值都是唯一的，即不允许重复值出现。

• 空值：唯一约束允许列中包含多个NULL值，因为NULL在数据库中被认为是不可比较的。

• 多列唯一约束：除了单列唯一约束，还可以定义多列唯一约束，它要求指定的多个列组合的值在表中是唯一的。

• 插入和更新：唯一约束会对插入和更新操作进行验证来确保没有重复的值被插入或更新。

• 查询性能：数据库会为唯一约束自动创建索引，提高查询性能和索引的利用率。

在MySQL数据库中，可以使用以下方式定义唯一约束：

（1）在创建表的同时定义主键约束和唯一约束

CREATE TABLE 表名 (
    列名1 数据类型 PRIMARY KEY,
    列名2 数据类型 UNIQUE,
    ...
);

（2）在创建表时定义唯一约束

CREATE TABLE 表名 (
    列名1 数据类型,
    列名2 数据类型,
    ...
    UNIQUE (列名1, 列名2, ...)
);

（3）在已存在的表上添加唯一约束

ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (列名1, 列名2, ...);

需要注意的是，唯一约束可以是列级别的，也可以是表级别的，取决于唯一约束定义的位置。在列级别定义的唯一约束只适用于该列，而在表级别定义的唯一约束适用于指定的列组合。

唯一约束可以确保数据库中的数据只有唯一值，避免重复数据的出现，以保证数据的准确性和一致性。

请注意： 每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

2.4 默认值约束

默认值约束是一种数据库约束，用于在插入数据时，如果未显式为列指定值，则自动使用指定的默认值。

默认值约束是一种数据完整性措施，它确保了在缺少显式值的情况下，表中的列始终具有默认值。

默认值约束的主要特点如下：

• 默认值：默认值约束定义了每个列的默认值，如果未提供显式值，则会自动使用默认值。

• 可选使用：默认值约束是可选的，如果未定义默认值约束，则插入数据时可以不提供该列的值。

• 数据类型：默认值必须与列的数据类型兼容，例如，如果列的数据类型是整数，则默认值应为整数。

• 插入操作：当插入一条新记录时，如果没有为列提供值，则将使用默认值。

• 更新操作：当更新记录时，如果未提供新值，则列不会自动重置为默认值，除非显式指定。

在MySQL数据库中，可以使用以下方式定义默认值约束：

（1）在创建表时定义默认值约束：

CREATE TABLE 表名 (
    列名1 数据类型 DEFAULT 默认值,
    列名2 数据类型 DEFAULT 默认值,
    ...
);

（2）在已存在的表上添加默认值约束：

ALTER TABLE 表名 ALTER COLUMN 列名 SET DEFAULT 默认值;

需要注意的是，默认值约束只在插入记录时使用。更新记录时，如果未提供新值，则列的值将保持不变，不会自动重置为默认值。

默认值约束可用于确保数据的一致性和完整性，尤其在某些情况下，如果未提供值，使用默认值可以避免数据错误或缺失。

2.5 外键约束

外键约束是一种数据库约束，用于在相关表之间建立关联关系，保持数据的完整性和一致性。

外键约束可以确保在一个表中的列值必须在另一个表中存在。

外键约束的主要特点如下：

• 关联关系：外键约束通过关联两个表中的列来建立关系，通常是将一个表的主键作为另一个表的外键。

• 引用完整性：外键约束确保外键列中的值必须在关联表的主键列中存在，保证了数据的引用完整性。

• 级联操作：外键约束支持级联操作，包括级联更新和级联删除。当关联表中的外键列发生更新或删除操作时，可以自动更新或删除相关的行。

• 多列外键：除了单列外键，还可以定义多列外键，它们一起形成一个复合外键，确保多列值的完整性。

• NULL值：外键列允许包含NULL值，表示没有任何关联。

在MySQL数据库中，可以使用以下方式定义外键约束：

（1）在创建表时定义外键约束：

CREATE TABLE 表名 (
    列名1 数据类型,
    列名2 数据类型,
    ...
    FOREIGN KEY (列名) REFERENCES 关联表名(关联列名)
);

（2）在已存在的表上添加外键约束：

ALTER TABLE 表名
    ADD CONSTRAINT 约束名 FOREIGN KEY (列名) REFERENCES 关联表名(关联列名);

外键约束是保持数据库数据完整性和一致性的关键机制之一。它们可以确保表与表之间的关联关系正确，并在更新和删除操作中自动处理相关数据的变动。使用外键约束可以有效地处理数据关系和避免数据不一致。

2.6 删除约束

在MySQL数据库中，使用SHOW CREATE TABLE语句查看约束名，然后使用ALTER TABLE语句根据约束名删除约束。

删除约束有以下几种方式：

（1）使用ALTER TABLE语句：

ALTER TABLE 表名 DROP CONSTRAINT 约束名;

这种方式适用于删除表的主键、外键和唯一约束。

（2）使用ALTER TABLE语句删除主键约束：

ALTER TABLE 表名 DROP PRIMARY KEY;

这种方式只适用于删除表的主键约束。

（3）使用ALTER TABLE语句删除外键约束：

ALTER TABLE 表名 DROP FOREIGN KEY 外键名;

这种方式只适用于删除表的外键约束。

（4）使用ALTER TABLE语句删除唯一约束：

ALTER TABLE 表名 DROP INDEX 索引名;

这种方式只适用于删除表的唯一约束。

（5）要删除MySQL数据库表中的非空约束，可以使用以下方法：

使用ALTER TABLE语句：

方式一：这种方法将列的数据类型修改为允许为NULL，从而删除非空约束。

ALTER TABLE 表名 MODIFY 列名 数据类型 NULL;

方式二：这种方法将列的NOT NULL属性移除，从而删除非空约束。

ALTER TABLE 表名 ALTER COLUMN 列名 DROP NOT NULL;

（6）在MySQL数据库中，可以使用以下几种方式来删除默认值约束

方式一：使用ALTER TABLE语句删除默认值约束

ALTER TABLE 表名 ALTER COLUMN 列名 DROP DEFAULT;

这将删除指定列的默认值约束。

方式二：使用ALTER TABLE语句修改列的默认值为NULL，从而删除默认值约束：

ALTER TABLE 表名 ALTER COLUMN 列名 SET DEFAULT NULL;

这将将列的默认值修改为NULL，相当于删除了默认值约束。

方式三：使用ALTER TABLE语句修改列的默认值为其他值，从而替换默认值约束：

ALTER TABLE 表名 ALTER COLUMN 列名 SET DEFAULT 新默认值;

这将将列的默认值修改为新的默认值，实际上是替换了原有的默认值约束。

需要注意的是，删除默认值约束时，可能需要注意以下几点：

• 默认值约束名，默认值约束可能有一个名字，如果使用了命名的默认值约束，需要使用其名字来删除。

• 某些情况下，删除默认值约束可能需要重新定义表的结构，这可能会涉及到其他依赖于该列的约束或索引，谨慎操作。

删除默认值约束是对表结构的修改操作，需要谨慎操作，确保没有对其他相关表或代码产生不良影响。

请注意，在执行以上任何一种方式之前，务必备份数据，以防止意外删除或数据丢失。

此外，某些约束会依赖其他约束或索引，删除某些约束可能会影响其他约束或索引的有效性，所以在删除约束之前，应该先确认相关的依赖关系。