【数据库和SQL学习笔记】2.表的要素（数据类型、约束）和操作（创建、修改、删除）

数据库系统软件：SQL Server 2019 Express
操作系统：Windows 10

表（TABLE）的要素

表的要素：

1. 表名
2. 字段名
3. 字段的数据类型
4. 字段的数据约束(主键约束、外键约束、非空、唯一)

注：
1.主键本身包含唯一约束，主键外的字段需要自行定义唯一约束
2.外键的数据类型 和引用的另一张表的字段数据类型必须相同

表的呈现例子：

数据类型

包括：

• 字符串型
  （如：CHAR、VARCHAR）
• 数值型
  （如：INTEGER、SMALLINT、REAL、FLOAT、NUMERIC）
• 时间型
  （如：DATE、TIME）
• 其他
  （如：BIT位串型）

字符串型

1. CHAR(n) 固定长度字符串

n表示可存储最大字符个数，1个字符占据1个字节
CHAR(n)类型的数据占据固定长度的n个字节
当实机储存的字符个数小于n时，不足的部分被填充空格

2. VARCHAR(n) 可变长度字符串

n表示可储存的最大字符个数，1个字符占据1个字节
VARCHAR(n)类型的数据分配的存储空间：
m个字符被分配m个字节，再附加1个字节用于标识数据的长度。先读长度、再读字符。

例：VARCHAR(10) Tom →占据4个字节

总结： CHAR型数据处理效率高，但可能消耗多余的存储空间；VARCHAR型数据节省存储空间，但消耗计算资源。
一般固定长度的数据，如身份证号码可以定义为CHAR；
长度较短，也适合定义为CHAR；
长度较长且不确定，适合定义为VARCHAR，如“课程说明”字段。

3. NCHAR(n) 固定长度Unicode字符串

用于储存汉字等非英文字符，1个字符占据2个字节
存储n个Unicode字符，会占据2n个字节
实机储存的字符个数小于n时被填充空格

4. NVARCHAR(n) 可变定长度Unicode字符串

最多存储n个Unicode字符
根据数据的实际长度分配存储空间

以上为Unicode字符串类型，1个字符占据2个字节，适用于存储汉字等。
如定义性别。

数值型

5. BIGINT
6. INT
7. SMALLINT
8. TINYINT

以上为整形，以下为范围
BIGINT：$-2^{63}$ ~ $2^{63}-1$
INT：$-2^{31}$ ~ $2^{31}-1$
SMALLINT：$-2^{15}$ ~ $2^{15}-1$
TINYINT：0 ~ 255

9. DECIMAL(m,n)
10. NUMERIC(m,n)

以上为用精确数值类型：
m 代表小数点前与小数点后的位数之和
n 代表小数点后的位数
如 DECIMAL(5,2)最大可表示 999.99

时间型

11. DATE 用字符串表示日期数据，如：‘2022-03-29’
12. TIME 用字符串表示时间数据，如：‘16:22:06’
13. DATETIME 用字符串表示的日期和时间数据，如：‘2022-03-29 16:22:06’

以上为日期和时间型，用字符串表示，本质是一种字符串
注意用单引号围起来

其他

14. TEXT 文本数据
15. NTEXT Unicode类型文本数据
16. IMAGE 用二进制表示的图片数据

以上为文本和图片型

17. MONEY
18. SMALLMONEY

以上为货币型

19. BIT 位数据类型，取值为0、1
20. BINARY(n) 固定长度二进制数据
21. VARBINARY(n) 可变长度二进制数据

数据约束

数据约束的具体设置方法可见下一小节。

1 主键（Primary Key）：

一个或多个字段的组合，唯一标识表中的每一条记录
非空且唯一

如上图，每个表都设了一个主键作为唯一标识，其中最后一张表用两个字段作为联合主键。

2 外键（Foreign Key）：

涉及两张表的关联关系
如：表B的外键字段引用（参照）了表A的主键字段的值。

3 唯一约束（Unique）：

非主键字段要求取值唯一

4 非空约束（Not Null）：

要求取值非空

5 检查约束（Check）：

对表的某个字段限定取值范围
如：Sex字段限定：‘男’、'女’两种取值

表的创建

基本语法：

基本表的创建
		CREATE TABLE <基本表名>
		 (
		  <列名><列类型>[列约束],
		  <列名><列类型>[列约束],
		  ...
		  [表约束]
		 )

例子：
创建一个学生课程管理系统，表结构如下：

我们之前已经用create database teachingDB创建了名为“teachingDB”的数据库，接下来依次创建这4张表。
我们在对象资源管理器找到刚新建的数据库“teachingDB”，右键选择“新建查询”输入代码。

1 创建学生表：

代码（注意把全角井号改成半角#，这里没改是因为CSDN显示有bug）：

CREATE TABLE S
(
S＃ CHAR(10) PRIMARY KEY,
SNAME NVARCHAR(10) NOT NULL,
AGE SMALLINT,
SEX NCHAR(2) CHECK(SEX='男' OR SEX='女'),
MAJOR NVARCHAR(10)
)

注意关注 列约束和表约束 的表达；学生名需要设非空约束，性别需要设检查约束。
下面左右两种创建主键的方式都可以，对应了列约束和表约束。

上面代码也可以把列约束写成表约束：

CREATE TABLE S
(
S＃ CHAR(10),
SNAME NVARCHAR(10) NOT NULL,
AGE SMALLINT,
SEX NCHAR(2) CHECK(SEX='男' OR SEX='女'),
MAJOR NVARCHAR(10),
PRIMARY KEY(S＃)
)

敲完代码点击“执行”。
我们进SQL SERVER的对象资源管理器，刷新一下：
可以看一下新建表的情况，包括字段、数据约束等。

2 创建教师表

代码（注意把全角井号改成半角#）：

CREATE TABLE T
(
T＃ CHAR(10) PRIMARY KEY,
TNAME NVARCHAR(20) NOT NULL,
TITLE NVARCHAR(20)
)

在查询代码框中，选中代码，再点击“执行”执行选中代码：

3 创建课程表：

代码（注意把全角井号改成半角#）：

CREATE TABLE C
(C＃ CHAR(10),
CNAME NVARCHAR(20) UNIQUE,
T＃ CHAR(10),
PRIMARY KEY(C＃),
FOREIGN KEY(T＃) REFERENCES T(T＃)
)

注意外键的设置，FOREIGN KEY(T#) REFERENCES T(T#)。使用 FOREIGN KEY 和 REFERENCES 关键字设置外键。

建好后刷新，我们可以看到C表的列信息和键信息中，外键已经显示在里面了：

4 创建选课表：

注意因为本表为联合主键，设置主键的方式用表约束法定义。

代码（注意把全角井号改成半角#）：

CREATE TABLE SC
(
S＃ CHAR(10),
C＃ CHAR(10),
SCORE SMALLINT,
PRIMARY KEY(S＃,C＃),
FOREIGN KEY(S＃) REFERENCES S(S＃),
FOREIGN KEY(C＃) REFERENCES C(C＃)
)

表的撤销与修改

表的撤销（删除）：

DROP TABLE <表名>

表的修改：

ALTER TABLE <表名> 修改操作

1. 修改表的数据类型：

ALTER TABLE <表名> ALTER COLUMN <列名><类型>

例：将教师表T中的职称字段的类型修改为NVARCHAR(30)：

ALTER TABLE T
ALTER COLUMN TITLE NVARCHAR(30)

2. 从表中增加列、删除列：

①增加列：

ALTER TABLE <表名> ADD <列名><类型>

例：学生表S中添加字段ADDRESS，NVARCHAR(30)类型：

ALTER TABLE S
ADD ADDRESS NVARCHAR(30)

②删除列：

ALTER TABLE <表名> DROP COLUMN <列名><类型>

例：从学生表S中删除字段ADDRESS：

ALTER TABLE S
DROP COLUMN ADDRESS

3. 添加、删除约束：

例：在选课表SC中，在SCORE字段添加CHECK约束，限定取值范围0~100。

ATLER TABLE SC
ADD CONSTRAINT CST CHECK(SCORE>=0 AND SCORE<=100)

（注意CST是约束名，自己起的）

例：删除刚才建立的约束CST

ALTER TABLE SC
DROP CONSTRAINT CST

例：在学生表中，在SNAME字段添加UNIQUE约束

ALTER TABLE S
ADD CONSTRAINT CST UNIQUE(SNAME)

附：查看表的所有约束：

SELECT  *
FROM    sysobjects
WHERE   OBJECT_NAME(parent_obj) = '表名'

本文以上代码操作也都可以在SQL Server Management Studio的对象资源管理器进行可视化操作修改。

（本文发在我的公众号“飞舞的矩阵”和CSDN博客）