创建和操纵表与约束

目录

创建表（CREATE TABLE）

指定默认值（DEFAULT）

修改表结构（ALTER TABLE）

删除表（DROP TABLE）

重命名表（RENAME TABLE）

约束（Constraint）

主键（Primary Key）

外键（Foreign Key）

唯一约束（UNIQUE）

---

创建表（CREATE TABLE）

语法结构：

CREATE TABLE 表名 ( 列名1 数据类型 [列约束], 列名2 数据类型 [列约束], ...);

注意最后一个字段的末尾不能加逗号

创建一个名为 vendors 的供应商信息表，要求包含以下字段：
供应商 ID：整数类型，作为主键
供应商名称：字符串类型（最长 50 个字符），不允许为空
供应商地址：字符串类型（最长 100 个字符），允许为空

CREATE TABLE vendors ( vend_id INT PRIMARY KEY, vend_name VARCHAR(50) NOT NULL, vend_address VARCHAR(100), vend_phone VARCHAR(20) );

varchar是什么？

varchar 是 SQL 中用来存储字符串的一种数据类型。

它的特点是可变长度，比如定义 varchar(50)，表示这个字段最多能存 50 个字符，但如果实际内容只有 10 个字符，就只占 10 个字符的空间，不会浪费多余的存储空间。

为什么使用varchar？
比如存储姓名，如果用固定长度的类型（比如 char(10)），不管名字长短都会占 10 个字符的空间，会浪费很多存储；而用 varchar(10)，多长的名字就占多少空间，更节省。

NULL 表示 没有值 或 未知值，即 数据不存在 或 无法确定具体内容。

就像问别人 明天吃什么？，对方回答 还没想好，此时无法确定具体答案，用 NULL 表示。
空字符串表示 有值，但值为空，即 数据存在，但内容为空。

如果问别人 今天吃了什么？对方回答 没吃，此时明确回答了 没吃，用空字符串表示。

指定默认值（DEFAULT）

如果插入时未提供值，使用默认值
在列定义中使用 DEFAULT 关键字设置默认值

vend_name VARCHAR(50) NOT NULL DEFAULT \'苹果科技\'

修改表结构（ALTER TABLE）

添加列语法结构（ADD）

ALTER TABLE 表名ADD 列名 数据类型 [约束];

要求：向 vendors 表中添加一个新的字段
供应商的电子邮箱：字符串类型（最长 100 个字符），允许为空

ALTER TABLE vendorsADD vend_email VARCHAR(100);

删除列语法结构（DROP COLUMN）

ALTER TABLE 表名DROP COLUMN 列名;

要求：删除 vend_email 字段

ALTER TABLE vendorsDROP COLUMN vend_email;

删除表（DROP TABLE）

语法结构：

DROP TABLE 表名;

重命名表（RENAME TABLE）

语法结构：

RENAME TABLE 原表名 TO 新表名;

要求：将 vendors 表重命名为 vendors2025

RENAME TABLE vendors TO vendors2025;

约束（Constraint）

约束是对表中数据插入、修改、删除时的一种限制或规则，用于确保数据库的准确性和一致性，保障数据完整性。

主键（Primary Key）

主键是约束的一种，用于唯一标识表中的每一行。主键值必须唯一且不可为空。
每张表只能定义一个主键，但这个主键可以由多个列组合而成，称为复合主键。

列级主键
列级主键是直接在字段定义后声明该字段为主键，适用于单个字段作为主键的场景。

CREATE TABLE vendors ( vend_id INT PRIMARY KEY, -- 列级主键 vend_name VARCHAR(50) NOT NULL, vend_address VARCHAR(100), vend_phone VARCHAR(20));

PRIMARY KEY 的定义本身包含了 NOT NULL 的特性，所以可以省略 NOT NULL

表级主键
表级主键是在所有字段定义完成后，单独通过PRIMARY KEY子句声明主键，适用于多个字段组合作为主键（复合主键）的场景。

要求：用 vend_name 和 vend_phone 组合唯一标识供应商

CREATE TABLE vendors ( vend_id INT, vend_name VARCHAR(50) NOT NULL, vend_address VARCHAR(100), vend_phone VARCHAR(20) NOT NULL, -- 表级声明复合主键 PRIMARY KEY (vend_name, vend_phone));

 使用ALTER TABLE添加主键

如果表创建时未设置主键，后续可以通过 ALTER TABLE 语句添加主键。

语法结构：

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

用 ALTER TABLE 添加主键的前提是 vend_id 字段无重复值且不为NULL

ALTER TABLE vendorsADD PRIMARY KEY (vend_id);

去除表中的主键约束

ALTER TABLE 表名DROP PRIMARY KEY;

因为一个表只能有一个主键，所以删除时不需要指定具体字段
删除主键前先确保该主键没有被其他表的外键关联，否则会删除失败（需要先删除关联表的外键约束）

外键（Foreign Key）

外键是关系型数据库中的一种约束，用于建立和维护两个表之间的引用关系，外键是实现参照完整性的核心机制。

外键是某张表中的一列（或多列），它引用了另一张表的主键或唯一约束列。
外键所在的表称为从表，外键引用的表称为主表
外键的作用就是保证从表中的某列值必须来自主表中的一列已有值。

参照完整性

参照完整性是数据库中的一种数据完整性规则，它确保当一个表中的数据引用另一个表的主键时，该引用是有效的、存在的、正确的。
换句话说：
从表中某字段引用主表的主键，那么这条引用必须指向主表中真实存在的数据，否则就是“悬空引用”或“脏数据”，违反了参照完整性。

创建顾客表：四个字段分别是顾客ID，顾客名称，顾客联系方式，顾客邮箱

CREATE TABLE customers ( cust_id INT PRIMARY KEY, cust_name VARCHAR(50) NOT NULL, cust_contact VARCHAR(50),  cust_email VARCHAR(50) );

 创建订单表：三个字段分别是订单编号，订单日期，顾客ID

CREATE TABLE orders ( order_num INT PRIMARY KEY, order_date DATE NOT NULL,  cust_id INT  );

上面的表中，orders 表的 cust_id 可以作为外键，关联 customers 表的 cust_id（主键），表示订单属于哪个顾客。

orders表中不能出现 customers 表中不存在的 cust_id，即不允许存在无主订单
换句话说：
订单表引用了客户表的客户 ID，那么订单表中出现的客户 ID，必须在客户表中存在，是参照完整性的体现。

列级外键
在从表的字段定义后直接声明外键，适用于单个字段作为外键的场景。

CREATE TABLE orders ( order_num INT PRIMARY KEY, order_date DATE NOT NULL, -- 列级声明外键 cust_id INT REFERENCES customers(cust_id));

REFERENCES customers(cust_id) 表示：orders.cust_id的值必须来自customers.cust_id的已存在值。

表级外键

在所有字段定义完成后，通过 FOREIGN KEY 子句单独声明，适用于复合外键（多个字段组合作为外键）或需要给外键命名的场景。

表级声明外键的语法结构：

-- 外键声明部分[CONSTRAINT 外键名称] -- 可选：给外键命名FOREIGN KEY (从表外键字段) -- 指定从表中作为外键的字段REFERENCES 主表名(主表被参照字段) -- 指定关联的主表及主表中的字段（主键或唯一键）[ON DELETE 级联规则] -- 可选：主表记录删除时的联动规则[ON UPDATE 级联规则] -- 可选：主表被参照字段更新时的联动规则

外键可以不命名，数据库会自动生成一个默认名称，在MySQL中，默认名称格式为 fk_从表名_从表外键字段名 

CREATE TABLE orders ( order_num INT PRIMARY KEY, order_date DATE NOT NULL, cust_id INT, -- 表级声明外键 CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers(cust_id));

创建表后添加外键

如果表已创建，可以通过 ALTER TABLE 语句添加外键。

语法结构：

ALTER TABLE 从表名ADD CONSTRAINT 外键名称FOREIGN KEY (从表外键字段)REFERENCES 主表名(主表主键字段);

要求：给已存在的orders表添加外键，关联customers表 

ALTER TABLE ordersADD CONSTRAINT fk_orders_customersFOREIGN KEY (cust_id)REFERENCES customers(cust_id);

删除外键

语法结构：

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

要求：删除之前添加的外键 fk_orders_customers

ALTER TABLE ordersDROP FOREIGN KEY fk_orders_customers;

外键的级联操作

当主表中的主键值被修改或删除时，从表中依赖它的外键值该如何处理？
可以通过级联操作定义规则，避免数据不一致。

常用级联选项：

选项 说明 ON DELETE CASCADE 删除主表记录时，自动删除从表中引用它的记录 ON DELETE SET NULL 删除主表记录时，将从表中引用它的字段设为 NULL ON DELETE RESTRICT 禁止删除主表中已被引用的记录（默认） ON UPDATE CASCADE 主表主键更新时，从表中外键随之更新

唯一约束（UNIQUE）

唯一约束用于保证列（或列组合）中的数据是唯一的，与主键不同的是：

对比点 主键 唯一约束 每表是否可多个 一个 可多个 是否允许 NULL 不允许 可以允许 NULL 是否可修改 逻辑上不建议 允许修改

列级唯一约束

CREATE TABLE customers ( cust_id INT PRIMARY KEY, cust_name VARCHAR(50) NOT NULL, cust_email VARCHAR(50) UNIQUE, -- 列级唯一约束 cust_phone VARCHAR(20));

表级唯一约束

通过 CONSTRAINT 命名约束，适用于单个字段或复合字段。

CREATE TABLE customers ( cust_id INT PRIMARY KEY, cust_name VARCHAR(50) NOT NULL, cust_email VARCHAR(50), cust_phone VARCHAR(20), -- 表级声明：单个字段唯一 CONSTRAINT uk_cust_email UNIQUE (cust_email), -- 表级声明：复合字段唯一 CONSTRAINT uk_name_phone UNIQUE (cust_name, cust_phone));

创建表后唯一约束

-- 对单个字段添加ALTER TABLE customersADD CONSTRAINT uk_cust_email UNIQUE (cust_email);-- 对复合字段添加ALTER TABLE customersADD CONSTRAINT uk_name_phone UNIQUE (cust_name, cust_phone);

删除唯一约束

要求：删除名为 uk_cust_email 的唯一约束

ALTER TABLE customersDROP CONSTRAINT uk_cust_email;