MySQl基础入门⑨

03-19 1666阅读

上一遍文章内容

修改、删除数据表结构

前言

修改表是指修改数据库中已经存在的数据表的结构。mysql使用ALTER TABLE语句修改表,

常用的操作有修改表明、修改字段数据类型或字段名、增加和删除字段、修改字段的排列位置、

更改表的存储引擎、删除表的外键约束等。

实操开始

我们就拿上个内容的表来做练习。

这边我是重新起了个数据库，所以只有员工表

1.修改表名

两种写法都行
ALTER TABLE 旧表名 RENAME TO 新表名;
RENAME TABLE 旧表名 TO 新表名;

2.修改字段数据类型或字段名

修改字段数据类型

ALTER TABLE 表名 MODIFY COLUMN 字段名 新的字段类型;

修改新员工表2中性别的字段类型为varchar

在这里说一下，尽量同数据类型转换比如char和varchar，

修改字段名

命令：ALTER TABLE 表名 RENAME COLUMN 旧字段名 TO 新字段名;

将新员工表2中的姓名改为名字。

3.增加和删除字段

ALTER TABLE 表名 ADD 字段名 数据 类型类型;

在表中增加一个手机号码的字段

我们再把手机号码字段给删除掉

ALTER TABLE 表名 DROP 字段名;

4.修改字段的排列位置

我们把部门的排列位置提升到出生日期前面

ALTER TABLE 表名 MODIFY 字段1 数据类型 FIRST | AFTER 字段2;

表名是你要修改的表的名称。

字段1 是你想要修改位置的字段。

数据类型是字段1的数据类型，这是必填项，即使你想要保持数据类型不变也需要指定。

FIRST 是一个可选参数，表示将字段1修改为表的第一个字段。

AFTER 字段2 表示将字段1插入到字段2的后面。字段2是你选择的一个已存在的字段，字段1将会排在这个字段之后。

5.修改表的存储引擎

MySQL 从 3.23.34a 开始就包含 InnoDB 存储引擎。大于等于 5.5 之后，默认采用 InnoDB 引擎，

存储引擎是负责对表中的数据进行提取和写入工作的，我们可以为不同的表设置不同的存储引擎，

也就是说不同的表可以有不同的物理存储结构，不同的提取和写入方式。

在修改之前，我们先查看它默认是什么引擎

show variables like '%storage_engine%';
再查看存储引擎列表
show engines;

修改存储引擎命令

修改之前一定要先查看表的存储引擎：命令show create table 表名;

如果表已经建好了，我们也可以使用下边这个语句来修改表的存储引擎:
ALTER TABLE 表名 ENGINE = 存储引擎名称;
比如我们修改一下 student 表的存储引擎:
ALTER TABLE student ENGINE = InnoDB;
这时我们再查看一下 student  的表结构:
SHOW CREATE TABLE student;

6.修改表的各类约束，添加主键约束的语法格式

先随便创建一个没有设置主键的表

命令alter table 表名 add primary key (字段名);

7.删除主键约束的语法格式为：

命令为：alter table 表名 drop primary key;

8.添加外键约束的语法格式为：

先弄个班级表

也给我们的student表加上班级序号这个字段

修改数据表 student，将字段班级序号设置为外键，与数据表 class 的主键班级序号进行关联

命令:

alter table student add constraint class foreign key(班级序号) references class(班级序号);

翻译：alter table 表名 add constraint class foreign key(你的外键字段名) references 外表表名(对应的表的主键字段名);

这条语句的作用是向当前表（“表名”）中添加一个新的外键约束，该约束名为，其作用是使当前表中的某个字段（“你的外键字段名”）的值依赖于另一个表（“外表表名”）的主键字段（“对应的表的主键字段名”）的值，嫌麻烦的可以不写，会自动生成一个的。

可以根据实际需求进行自定义命名，主要用于标识和管理这个特定的外键约束，方便后续可能进行的查询、修改或者删除约束等操作。

在创建表时候添加外键

第一种 直接在属性值后面添加
create table score(
cscore int(12),
st_id int(60) references student(id),
cs_id int(60) references classes(id),
primary key(st_id,cs_id)
);

cscore：一个整数类型字段，长度为12，用于存储学生的分数或评价。
st_id：一个整数类型字段，长度为60。尽管MySQL中的int类型不需要指定长度（它始终固定为4字节），但这里表示的是该字段将用来存储对应于student表中id字段的学生ID值，并通过references student(id)设置外键约束，确保每个st_id在score表中的值都必须是student表中存在的有效id。
cs_id：也是一个整数类型字段，长度同样为60，同样作为外键引用classes表的id字段，这意味着score表中的每条记录都将关联到特定的班级。
primary key(st_id, cs_id)：定义了一个复合主键，由st_id和cs_id组成，这意味着这两个字段联合起来必须具有唯一性，即同一个学生在同一门课程下只能有一个唯一的分数记录。

总结来说，这个score表是用来存储学生在各个班级的得分信息，并通过外键与student和classes两个表建立了关联关系。

第二种
create table score(
cscore int(12),
st_id int(60),
cs_id int(60),
primary key(st_id,cs_id),
FOREIGN KEY (st_id) REFERENCES student(id),
FOREIGN KEY (cs_id) REFERENCES classes(id)
);

cscore：一个整数类型字段，长度为12，用于存储学生的成绩。
st_id：一个整数类型字段，长度为60。虽然MySQL中的int类型不需要指定长度，这里表示的是该字段将用来存储学生ID，并且通过后面的外键约束明确声明了它引用了student表的id字段。
cs_id：另一个整数类型字段，长度也为60，表示课程ID，同样通过后面的外键约束声明了它引用了classes表的id字段。
primary key(st_id, cs_id)：定义了一个复合主键，由st_id和cs_id组成，确保同一个学生在同一门课程下只能有一个分数记录。
FOREIGN KEY (st_id) REFERENCES student(id)：这是一个外键约束，明确指出score表中的st_id字段是student表中id字段的外键，即每个score记录的st_id值必须存在于student表的id列中。
FOREIGN KEY (cs_id) REFERENCES classes(id)：另一个外键约束，说明score表中的cs_id字段是classes表中id字段的外键，即每个score记录的cs_id值必须存在于classes表的id列中。

总的来说，这个score表是用来存储学生的具体分数信息，并通过外键关联到student（学生）表和classes（课程）表，确保数据的一致性和完整性。

第三种 添加约束
create table score(
cscore int(12),
st_id int(60),
cs_id int(60),
primary key(st_id,cs_id),
CONSTRAINT `FK_ID_ST` FOREIGN KEY (st_id) REFERENCES student(id),
CONSTRAINT `FK_ID_CS` FOREIGN KEY (cs_id) REFERENCES classes(id)
);

这个 SQL 语句用于创建一个名为 score 的数据库表，该表主要用于存储学生成绩信息。表的结构和约束如下：

cscore 字段：类型为整数（int），长度为12，用于存储学生的课程分数。
st_id 字段：类型为整数（int），长度为60，用于存储学生的唯一标识符（例如学号）。
cs_id 字段：类型为整数（int），长度为60，用于存储课程的唯一标识符（例如课程编号）。
主键定义：(st_id, cs_id) 是复合主键，这意味着同一学生在同一门课程下的记录是唯一的，不允许重复。
外键约束：
- CONSTRAINT FK_ID_ST：这是一个外键约束，它表示 score 表中的 st_id 字段引用了另一个名为 student 的表的 id 字段。这意味着每个在 score 表中出现的学生 ID 必须存在于 student 表中。
- CONSTRAINT FK_ID_CS：同样是一个外键约束，它表示 score 表中的 cs_id 字段引用了另一个名为 classes 的表的 id 字段。这意味着每个在 score 表中出现的课程 ID 必须存在于 classes 表中。

总结来说，这个 score 表是用来记录学生课程成绩的，并通过外键约束确保与学生信息表（student）和课程信息表（classes）之间的数据一致性。

9.删除外键约束

在SQL Server中删除外键约束的步骤通常通过执行SQL语句来完成。以下是具体的命令格式：

ALTER TABLE child_table_name
DROP CONSTRAINT fk_constraint_name;

这里的 child_table_name 是包含外键约束的表名，而 fk_constraint_name 是需要删除的外键约束的具体名称。

例如，如果要删除上述示例中的名为 FK_ID_ST 的外键约束，可以这样做：

ALTER TABLE score
DROP CONSTRAINT FK_ID_ST;

在实际操作前，你需要知道外键约束的确切名称，可以通过查询系统表或使用如下的SQL语句来查看：

SELECT name 
FROM sys.foreign_keys 
WHERE parent_object_id = OBJECT_ID('score');

这个查询将返回与 score 表关联的所有外键约束名称。

请注意，在删除外键约束时，请确保理解这种操作可能对数据完整性和参照完整性的影响，以及是否需要更新相应的业务逻辑或进行数据迁移以保持数据库的一致性。

10.添加唯一约束

在 SQL 中，要向某个字段添加唯一约束（确保该字段的值在整个表中都是唯一的），可以使用 ALTER TABLE 语句结合 ADD CONSTRAINT 子句。以下是一个示例：

ALTER TABLE score
ADD CONSTRAINT UC_Score UNIQUE (field_name);

其中，UC_Score 是你为唯一约束指定的名称，field_name 是你希望设置唯一性的字段名。

如果要在上述 score 表中，比如给 st_id 添加唯一约束，应这样操作：

ALTER TABLE score
ADD CONSTRAINT UC_StudentID UNIQUE (st_id);

这将确保 score 表中的 st_id 字段所有值都是唯一的，不会有重复的学号记录。

11.使用语句删除数据表

要删除一个数据库表，可以使用 DROP TABLE 语句。以下是如何删除名为 score 的数据表的 SQL 语句：

DROP TABLE IF EXISTS score;

或者，如果你确定表 score 存在：

DROP TABLE score;

DROP TABLE 会永久性地删除表以及其中的所有数据和约束（如主键、外键、唯一约束等）。在执行此操作前，请确保你确实希望删除该表，并且已经备份了所有需要的数据。

VPS购买请点击我