SQLServer学习笔记三：表的操作

表的基本概念

在数据库中，表是由数据按一定的顺序和格式构成的数据集合，是数据库的主要对象。每一行代表一条记录，每一列代表记录的一个字段。

在关系数据库中每一个关系都体现为一张表。

设计表

对于具体的某一个表，在创建之前，需要确定表的下列特征：

• 表中包含哪些列，每一列中数据的类型和长度（如果必要的话）
• 使用约束：确定主键、外键、允许空值，限制范围等、默认设置和规则

数据类型

• 精确数字

• bigint：整数类型中存储容量最大的一种，长度为8个字节，可以存储正负数字，存储范围为$-2^{63}\sim 2^{63}-1$
• int：最常用的数据类型，长度为4个字节，可以存储正负数字，存储范围为$-2^{31}\sim 2^{31}-1$
• smallint：长度为2个字节，可以存储正负数字，存储范围为$-2^{15}\sim 2^{15}-1$
• tinyint：长度为1个字节，可以存储$0\sim 255$范围内的整数
• bit：位类型，长度为1个字节，其数据有两种取值：$0$和$1$。当输入0以外的其它值时，系统均把它看做$1$。
• decima[(p[, s])]和numeric[(p[, s])]两者类型完全相同，提供了小数所需的实际空间，p确定了精确的总位数（有效数字的位数），s确定了小数位。p的默认设置为$18$，s的默认设置为$0$。
• money：存储的货币值有8个字节组成，前4字节代表整数部分，后4字节代表小数部分。
• smallmoney：与money类似，但范围比money小，由4个字节组成。

• 近似数字

• float：长度为8个字节，最大可以有15位精确数字。
• real：长度为4个字节，最大可以有7为精确数字。
• datetime：长度为8个字节，存储日期范围$1753.1.1\sim 9999.12.31$。
  smalldatetime：长度为4个字节，存储日期范围$1900.1.1\sim 2079.12.31$。

• 字符串

• char:定义形式为char(n)。存储数据时，每个字符和符号占用一字节存储空间。n表示所有字符所占的存储空间。取值$1\sim 8000$。若不指定n值则系统默认为$1$。若输入数据的字符串长度小于n，则系统自动在其后添加空格来填满设定好的空间；若输入的数据过长，将会截掉其超出部分。如果定义了一个char类型，且允许该列为空，则该字段被当做varchar来处理。
• varchar：定义形式为varchar(n)。同char类似。但不同的varchar具有变动长度的特性。varchar数据类型的存储长度为实际数值长度。若输入数据的字符数小于n，系统不会在其后添加空格来填满设定好的空间。因此此类型可以节省空间。
• text：用于存储文本数据

• Unicode字符串

• nchar：定义形式为nchar(n)。与char类似，不同的是n取值范围为$1\sim 4000$；用两个字节为一个存储单位，也就是说每个字符或符号占用2个字节存储空间。这样存储容量大大增加，不容易出现编码冲突。
• nvarchar：定义形式为nvarchar[(n)]。与varchar类似，不同的是n取值范围为$1\sim 4000$；用两个字节为一个存储单位。

• 二进制字符串

• binary：定义形式为binary(n)。数据存储长度是固定的，为$n+4$个字节。当输入的二进制数据长度小于n时，余下部分填充$0$，n 值最大为$8000$。常用于存储图像等数据。如果定义了一个binary类型，且允许该列为空，则该字段被当做varbinary来处理。
• varbinary：定义形式为varbinary(n)。数据存储长度是变化的，为实际所输入数据长度加上4字节。
• image：用于存储照片或图画等。

创建表

创建表的方式

• 使用Management Studio
• 使用T-SQL

CREATE TABLE

CREATE TABLE 语句用于创建数据库中的表。其语法格式如下：

--切换数据库
USE 数据库名称;
GO
CREATE TABLE 表名称
(
列名称1 数据类型[(长度)] [NULL|NOT NULL],
列名称2 数据类型[(长度)] [NULL|NOT NULL],
列名称3 数据类型[(长度)] [NULL|NOT NULL],
....
)

练习

创建一个学生表（student），属性字段如下：

列名	数据类型	约束
学号（sno）	字符串	长度为15，不能为空
姓名（name）	字符串	长度为20，不能为空
年龄（age）	整数	可空
性别（sex）	位类型	无

修改表

创建表之后，可以更改最初创建表时定义的许多选项，包括

• 添加列、修改或删除列
• 添加或删除约束

添加列

ALTER TABLE 表名
ADD 列名 数据类型[(长度)] [NULL|NOT NULL]

修改数据类型

ALTER TABLE 表名
ALTER COLUMN 列名 数据类型[(长度)]

删除列

ALTER TABLE 表名
DROP COLUMN 列名

修改列名

EXEC sp_renmae '表名.列名', '新列名'

修改表名

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

删除表

DROP TABLE 语句用于删除表。

DROP TABLE table_name

约束

上面讲到设计表时需要考虑约束，那什么是约束？

SQL 约束用于规定表中的数据规则。如果存在违反约束的数据行为，行为会被约束终止。
约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

创建、添加和删除约束

创建约束

在创建表的同时对列进行约束，基本语法：

CREATE TABLE table_name
(
column_name1 data_type[(size)] [约束], 
column_name2 data_type[(size)] [约束], 
column_name3 data_type[(size)] [约束],
....
);

添加约束

创建表之后也可以添加对列的约束，基本语法：

ALTER TABLE table_name
ADD CONSTRAINT [约束名] [约束类型] [约束说明]

删除约束

删除表中存在的约束，基本语法：

ALTER TABLE table_name
DROP CONSTRAINT [约束名]

约束类型

常用的约束有下面几种：

• NOT NULL，非空约束，约束强制列不接受 NULL 值。
• UNIQUE， 唯一约束，保证某列的每行必须有唯一的值，允许有空值。
• CHECK，检查约束，保证列中的值符合指定的条件。
• DEFAULT，默认约束，指定没有给列赋值时的默认值。
• PRIMARY KEY，主键约束，NOT NULL 和 UNIQUE 的结合，确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。一个表中只能有一个主键约束。
• FOREIGN KEY，外键约束，保证一个表中的数据匹配另一个表中的值的参照完整性。

NULL 约束

NOT NULL， 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

示例1
在创建Person表时，不允许Id接受 NULL 值：

CREATE TABLE Person
(
Id int NOT NULL,
Name varchar(255),
Address varchar(255),
Age int
)

示例2
将Person的Name设置为NOT NULL：

ALTER TABLE Person 
ALTER COLUMN Name varchar(255) NOT NULL

注意：若字段中数据存在空值，该字段无法从 NULL 改为 NOT NULL

示例3
将Name设置为可空字段：

ALTER TABLE Person 
ALTER COLUMN Name varchar(255) NULL

UNIQUE 约束

UNIQUE 约束唯一标识数据库表中的每条记录。每个表可以有多个 UNIQUE 约束。

示例1

在创建Person表时，为Name添加唯一约束：

CREATE TABLE Person
(
Id int NOT NULL,
Name varchar(255) UNIQUE,
Address varchar(255),
Age int
)

示例2

修改Person表，为Name添加的唯一约束：

ALTER TABLE Person
ADD CONSTRAINT uq_name UNIQUE (Name) -- un_name 约束名称，(name) 列名

示例3

删除唯一约束uq_name：

ALTER TABLE Person
DROP CONSTRAINT uq_name

CHECK 约束

CHECK约束用于限制列中的值的范围。

• 如果对单个列定义 CHECK 约束，那么该列只允许特定的值。
• 如果对一个表定义 CHECK 约束，那么此约束会在特定的列中对值进行限制。

示例1

在创建Person表时，添加检查约束，Id和Age要大于0：

CREATE TABLE Person
(
Id int NOT NULL CHECK (Id > 0),
Name varchar(255) UNIQUE,
Address varchar(255),
Age int CHECK (Age > 0),
Email varchar(100)
-- CONSTRAINT ck_person CHECK (Id > 0 AND Age > 0)
)

示例2

修改Person表，添加检查约束，Id和Age要大于0：

ALTER TABLE Person
ADD CONSTRAINT ck_age CHECK (Id > 0 AND Age > 0) -- ck_age 约束名称，(Id > 0 AND Age > 0) 约束条件

示例3

修改Person表，添加检查约束，来检查邮箱地址的格式是否有效：

ALTER TABLE Person
ADD CONSTRAINT ck_email CHECK (Email LIKE '%@%') -- ck_email 约束名称，(Email LIKE '%@%') 约束条件

示例4

删除检查约束ck_age ：

ALTER TABLE Person
DROP CONSTRAINT ck_age

DEFAULT 约束

DEFAULT 约束用于向列中插入默认值。如果没有规定其他的值，那么会将默认值添加到所有的新记录。

示例1

在创建Person表时，为Gender添加默认约束，默认值为男：

CREATE TABLE Person
(
Id int NOT NULL,
Name varchar(255) UNIQUE,
Address varchar(255),
Age int CHECK (Age > 0),
Gender char(5) DEFAULT '男'
)

示例2

修改Person表，为Gender添加默认约束，默认值为男：

ALTER TABLE Person
ADD CONSTRAINT df_gender DEFAULT '男' for Gender

示例3

删除默认约束df_gender ：

ALTER TABLE Person
DROP CONSTRAINT df_gender

PRIMARY KEY 约束

PRIMARY KEY 约束唯一标识数据库表中的每条记录。主键必须包含唯一的值。主键列不能包含 NULL 值。每个表都应该有一个主键，并且每个表只能有一个主键。

示例1

在创建Person表时，为Id添加非空约束和主键约束：

CREATE TABLE Person
(
Id int NOT NULL PRIMARY KEY,
Name varchar(255),
Address varchar(255),
Age int
)

示例2

修改Person表，为Id添加的主键约束：

ALTER TABLE Person
ADD CONSTRAINT pk_id PRIMARY KEY (id) -- pk_id 约束名称，(id) 列名

示例3

删除主键约束pk_id ：

ALTER TABLE Person
DROP CONSTRAINT pk_id

FOREIGN KEY 约束

一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。下面通过一个例子来解释外键：

Person表

P_Id	Name	Address	Age
1	张三	山东省	19
2	李四	山西省	18
3	王五	河北省	18

Order表

O_Id	OrderNo	P_Id
1	12345	3
2	12346	3
3	12347	1

说明

• Order 表中的 P_Id 列指向 Person 表中的 P_Id 列。
• Person 表中的 P_Id 列是 Person 表中的 PRIMARY KEY。
• Order 表中的P_Id 列是 Order 表中的 FOREIGN KEY。

FOREIGN KEY 约束用于预防破坏表之间连接的动作。FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

示例1

在创建Order表时，为P_Id添加外键约束：

CREATE TABLE Order
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Person(P_Id)
)

示例2

修改Order表，为P_Id添加外键约束：

ALTER TABLE Order
ADD CONSTRAINT fk_personorder FOREIGN KEY REFERENCES Person(P_Id)
)

示例3

删除外键约束fk_personorder：

ALTER TABLE Person
DROP CONSTRAINT fk_personorder

练习

在wzsql数据库中，分别使用命令创建图书表book、读者表user、借阅表record，这三个表的结构及完整性约束如下(要求建立所有的约束)：

• book(书号，书名，出版社，类别，作者，出版时间，价格)。其中，书号为主键，价格为数值型；出版时间为日期型，其他属性都是字符型，长度自己设定；要求价格为正。
• user(编号，姓名，学校，性别，电话)。编号为主键，所有属性都是字符型，长度自己设定；限制性别的取值为男、女，学校缺省值为威海职业学院。
• record(书号，编号，借阅日期，还书日期，还书标识)。书号和编号为主键，同时分别是book和user的外键；借阅日期为日期类型；还书标识为位类型。

总结

本章主要介绍了SQL Server中关于表的操作，包含：

• 表的基本概念
• 字段的数据类型
• 创建表
• 修改表
• 删除表
• 创建和删除约束