数据库探索之旅——操作基本表三步曲

目录

1. 第一步：创建（定义）基本表
   1.1 选择数据库
   1.2 语法格式
   1.3 建表实例
2. 第二步：修改基本表
   2.1 语法格式
   2.2 改表实例
3. 第三步：删除基本表
   3.1 语法格式
   3.2 删表实例

1. 第一步：创建（定义）基本表

1.1 选择数据库

在要对某个数据库进行表的操作前，我们需要先选定它。语法格式为：
USE 数据库名
这次我们对 practice 这个数据库进行操作

USE practice

1.2 语法格式

在表的定义式，需要定义表名，该表中的列（列名、数据类型、约束条件）。
其语法格式如下：

CREATE TABLE <表名> (
<列名1> <数据类型> [ <列级完整性约束>]
[, <列名2> <数据类型> [ <列级完整性约束>] ]
[, ...n]
[, 表级完整性约束条件] [, ...n]
)

一点说明：

1. 格式中的 [] （方括号）表示其中内容可以根据具体情况决定是否选用。在命令输入时，不要输入方括号。
2. 格式中 的<> （尖括号）表示其中内容为实际语义，内容以实际名称或参数输入，为必选内容。在命令输入时，不要输入尖括号。
3. 格式中的 () （小括号）是语句的组成部分，一定要输入。
4. 格式中的[,...n] 指示前面的项可以循环n次，具体值由逗号分隔。
5. 同一个数据库中不允许出现同名的表。
6. 列名又称为字段名、属性名，表示基本表的各个属性，同一个表中不允许出现同名的列。
7. 数据类型是列允许存放的数据的类型。
8. 约束条件用以分为列级和表级，用以做某些约束。

1.3 建表实例

我们建立一个基本表 Student ，其中包括属性学号（Sno），姓名（Sname），性别（Sex），出生时间（SBirthDay），专业（Sdept），同时对这些属性建立完整性约束条件。

定义 Sno 为主码；Sex 只可取值 'F'、'M' ；对 Sname和Sex建立唯一性索引，确保统一性别学生没有重名。代码如下：

CREATE TABLE Student(
    Sno CHAR(10) PRIMARY KEY,
    Sname VARCHAR(5) NOT NULL,
    Sex CHAR(1) NOT NULL CHECK(Sex='F' OR Sex='M'),
    SBirthDay DATE ,
    Sdept VARCHAR(15),
    CONSTRAINT S_UINO UNIQUE(Sname,Sno)
    )

从 Student 表的数据字典可以看出表的列名，数据类型，是否允许为空，但不可以看见完整性约束条件。

数据字典

2. 第二步：修改基本表

基本表在使用的过程中需要根据需求做出改变，这些改变包括增加列、删除列、重命名列和表、增加完整性约束条件、删除完整性约束条件等。修改基本表也可以叫做基本表的维护。

2.1 语法格式

ALTER TABLE <表名>
[ ADD <新列名> <数据类型> [完整性约束条件] ]
[ DROP <完整性约束条件> ]
[ ALTER <列名> <数据类型> ]

有以上语法格式可分为三种操作方式：ADD、DROP、ALTER

ADD方式 可用于新增列和完整性约束条件，列定义同 CREATE TABLE 语句中的列定义方式相同。其语法格式为：

ALTER TABLE <表名> ADD <列定义> | <完整性约束定义>

使用此方式增加列时自动填充NULL，因为在新增列是不能同时为列加入数据，所以不能将新增加的列定义为NOT NULL

DROP 方式 可以用于删除指定的完整性约束条件或指定的列，语法格式为：

ALTER TABLE <表名> DROP <完整性约束名> | <COLUMN <列名>>

在删除列时，如果列具有如下特征则不能删除

1. 用于 CHECK、FOREIGN KEY、UNIQUE 或 PRIMARY KEY 约束；
2. 用于索引
3. 与 DEFAULE 定义管理或绑定到某一默认对象；
4. 绑定到规则
5. 用作表的全文键
6. 已注册支持全文

ALTER 方式 用于修改某些列的数据类型，语法如下：

ALTER TABLE <> ALTER COLUMN <列名> <新数据类型>

以上是ADD、DROP、ALTER 方式的一些基本使用方式，还有更加强大的功能等着大家去发现。

至于表和列的重命名需要用到存储过程 “sp_rename”。
重命名表的格式如下：

EXEC sp_rename '原表名' ,'新表名'

重命名列的格式如下：

EXEC sp_rename '表名.原列名' ,'新列名','COLUMN'

2.2 改表实例

【实例 1 】向表 Student 中插入一列 Sadr，数据类型为 varchar(50)

ALTER TABLE Student  ADD Sadr varchar(50)

【实例 2 】向表 Student 中 SBirthDay 增加CHECK约束，使其出生年份至少在 10 年以前。

ALTER TABLE Student 
ADD CONSTRAINT SBirthDay_CHE 
CHECK (YEAR(SBirthDay)<YEAR(GETDATE())-10)

【实例 3 】删除 Student 表中的表级完整性条件 S_UINO 。

ALTER TABLE Student DROP S_UINO

【实例 4 】将列 Sname 原数据类型 VARCHAR(5) 改为 VARCHAR(8)

ALTER TABLE Student ALTER COLUMN Sname VARCHAR(8)

【实例 5 】将列 Sadr 重命名为 Address

EXEC sp_rename 'Student.Sadr','Address','COLUMN'

【实例 6 】将表 Student 重命名为 S_Student

EXEC sp_rename 'Student','S_Student'

3. 第三步：删除基本表

当不需再要某个表，或某个表出现不明错误无法改正，需要删除重建时，需要用到删除命令 DROP TABLE。删除表时，该表中的所有属性，完整性约束，索引，权限范围，触发器等都会被删除。PROCEDURE 显示删除。

通过 DROP TABLE 删除表时，存在以下限制和约束。

1. 不能删除被 FOREIGN KEY 约束引用的表。必须先删除引用 FOREIGN KEY 的约束或引用表。如果要在同一个DROP TABLE 语句中删除引用表以及包含主键的表，则必须先列出引用表。
2. 删除表时，标的规则或默认值将被解除绑定，与该表关联的任何约束或触发器会被自动删除。如果要重新创建表，则必须重新绑定相应的规则和默认值，重新创建某些触发器，并添加所有必须的约束。
3. 如果删除的表中包含带有 FILESTREAM 属性的 varbinary(max) 列，则不会删除在文件系统中存储的任何数据。
4. 不应在同一个批处理中对同一个表执行 DROP TABLE 和 CREATE TABLE ，否则可能出现意外错误。
5. 任何引用已删除表或存储过程时都必须使用 DROP VIEW 或 DROP PROCEDURE 显示删除，以便删除对该表的引用。

3.1 语法格式

DROP TABLE <表名>

3.2 删表实例

删除基本表 S_Student

DROP TABLE S_Student

以往的文章

数据库探索之旅——初识数据库
数据库探索之旅——数据库管理系统
数据库管理系统——连接数据库
数据库管理系统——对数据库的简单操作
数据库管理系统——数据类型
数据库管理系统——完整性约束条件