MySQL 表主/外键 相关语句

表主键

主键：指示表中数据唯一性的字段，这个唯一性的字段也可以是多个表字段组合而成的，一个表中只有一个主键。
主键约束：主键自带非空和唯一约束，起到保证主键非空且唯一的作用。

创建表时指定主键

语句格式：

CREATE TABLE test (数据唯一性字段名 字段类型 PRIMARY KEY,其它字段名 字段类型,其它字段名 字段类型,其它字段名 字段类型); 

使用 MySQLWorkbench 执行实例：

// 创建表并指定主键
CREATE TABLE test (id BIGINT PRIMARY KEY,name VARCHAR(255),address VARCHAR(255),phone_number VARCHAR(255)); 
// 插入一条数据，但不插入主键值
INSERT INTO test (name) VALUES("阿刚");

截屏2023-05-20 15.53.07.png

// 插入一条数据，并插入主键值
INSERT INTO test (id,name) VALUES(1,"阿刚");

截屏2023-05-20 15.59.07.png

对主键设置自增

主键自增：

• 主键数值以出现过的最大值的基础上 + 1。
• 表内自增的数值只增不减。
• 清空表的数据，数值不归0，而是继续增长。

语句格式：

ALTER TABLE 表名 MODIFY 主键名 字段类型 AUTO_INCREMENT;

使用 MySQLWorkbench 执行实例：

// 设置主键 id 自增
ALTER TABLE test MODIFY id BIGINT AUTO_INCREMENT;
// 由于已经插入一条数据，设置自增的数值偏倚到2开始
ALTER TABLE test AUTO_INCREMENT = 2;
// 插入一条数据，但不插入主键值
INSERT INTO test (name) VALUES("阿全");

截屏2023-05-20 16.21.28.png

创建表时指定主键并且主键自增

语句格式：

CREATE TABLE 表名 (数据唯一性字段名 字段类型 PRIMARY KEY AUTO_INCREMENT,其它字段名 字段类型,其它字段名 字段类型,其它字段名 字段类型); 

使用 MySQLWorkbench 执行实例：

// 创建带有主键自增的表
CREATE TABLE test (id BIGINT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(255),address VARCHAR(255),phone_number VARCHAR(255)); 
// 插入数据，但都未插入主键值
INSERT INTO test VALUES(null,"阿全","秦皇岛","16666666666"),(null,"阿峰","秦皇岛","19999999999");

截屏2023-05-20 16.34.12.png

创建表后添加主键

语句格式：

ALTER TABLE 表名 ADD PRIMARY KEY (数据唯一性字段名,数据唯一性字段名...);

使用 MySQLWorkbench 执行实例：

// 创建表，但不指定主键字段
CREATE TABLE test (id BIGINT,name VARCHAR(255),address VARCHAR(255),phone_number VARCHAR(255)); 
// 将 id  字段指定为主键
ALTER TABLE test ADD PRIMARY KEY (id);

删除主键

语句格式：

ALTER TABLE 表名 DROP PRIMARY KEY;

使用 MySQLWorkbench 执行实例：

// 删除主键
ALTER TABLE test DROP PRIMARY KEY;
// 重置 NOT NULL约束
ALTER TABLE test MODIFY id BIGINT;

截屏2023-05-21 14.51.44.png

表外键

外键是关系型数据库中的一个概念，用于建立两个表之间的关联关系。一个表中的一个或多个字段可以被指定为外键，它们的值与另一个表中的字段值相对应，用来表示两个表之间的关联关系。通常情况下外键会与另一个表的主键相关联。

外键自身的约束保持着数据的一致性

• 外键的值可以为null，可以重复，但是不能是不存在的值。
• 外键所指向的表不能先删除，外键所指向的数据不能先删除。如果需要删除，则先删除外键约束或先删除外键保存的数据以断开关联关系。

注意：使用外键必须要求两张表相同的 InnoDB 引擎，Myisam 引擎不支持外键。

创建表时设置外键

语法格式：

CONSTRAINT 外键名 FOREIGN KEY 从表名 (从表字段) REFERENCES 主表名 (主表唯一字段)

放置在需要被约束的外键字段类型后。注意，外键名是唯一的。

使用 MySQLWorkbench 执行实例：

CREATE TABLE user_role2(user_role_id INT PRIMARY KEY AUTO_INCREMENT,
user_foreign_key INT,
CONSTRAINT u_f_k FOREIGN KEY user_role2 (user_foreign_key) REFERENCES user (user_id),
role_foreign_key INT
);

截屏2023-05-28 16.02.19.png

创建表后指定外键

语法格式：

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

使用 MySQLWorkbench 执行实例：

ALTER TABLE user_role2 ADD CONSTRAINT r_f_k FOREIGN KEY(role_foreign_key) REFERENCES role(role_id);

截屏2023-05-28 16.10.23.png

删除外键约束

语法格式：

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

使用 MySQLWorkbench 执行实例：

ALTER TABLE user_role2 DROP FOREIGN KEY r_f_k;

截屏2023-05-28 16.19.10.png