SqlServer：表的创建和约束

目录

• 数据表
• 数据类型
  • 字符型
  • 整数型
  • 精确实数型
  • 时间型
  • 文本型
  • 二进制型
  • 特殊数据类型
• 完整性约束
• SQL 语句
  • 创建表
  • SELECT INTO 子句
  • 创建约束
  • 删除表
  • 添加、删除和修改列
• 样例
  • 创建简单表
  • 修改表
  • 创建带约束的表
  • 修改约束
  • SELECT INTO 创建
• 参考资料

数据表#

数据表简称表，是数据库的最主要组成成分。数据库建好以后里面没有任何内容，通过在数据库中添加表插入记录后，数据库中才会有内容。表由若干栏目(即列或者字段)和若干行组成，每一行称为一条记录。每个栏目均需要设置其名称（即列名、字段名)、数据类
型、长度、约束，列名必须符合标识符的要求，数据类型由系统规定，长度是一个整数，表示这个列最大可以输入多少个字符，而约束是对这个列的值设置的限制条件。

数据类型#

数据类型表达的功能是当前列的值是什么类型的，建立表时决定各个列的数据类型的唯一依据是这个列的所有可能取值。

字符型#

varchar 和 char 类型的主要区别是：数据填充后实际占用的长度不同。如果 varchar(20) 类型的值为有 5 个字符，物理上只存储 5 个字节，但如果数据类型为 char(20) 则将使用全部 20 个字节。nvarchar 和 nchar 的工作方式与对应的 varchar 数据类型和 char 数据类型相同，这两种数据类型都可以处理国际性的 Unicode 通用字符。

数据类型	格式	描述
char	char(n)	1～8000 字符
varchar	varchar(n)	1～8000 字符
nchar	nchar(n)	1～4000 个 Unicode 字符
nvarchar	nvarchar(n)	最多为 2^30-1 个 Unicode 字符

整数型#

整数型简称整型，可用于存储精确的整数，几种的区别在于表示数据的范围不同。

数据类型	描述	存储空间
bit	0、1、null	1字节
tinyint	0～255 之间的整数	1字节
smallint	-32768～32767 之间的整数	2字节
int	-231~231-1 之间的整数	4字节
bigint	-263~263-1 之间的整数	8字节

精确实数型#

表示能够精确存储的实数值，由总长度和小数位数构成，总位数不得小于小数位数。

数据类型	格式	描述
十进制型	decimal(n,m)	n 表示总长度，m 表示小数位数
数值型	numeric(n,m)	n 表示总长度，m 表示小数位数

时间型#

在 SQLServer 中日期时间型表示日期或者时间，其值要以字符串的形式表示，包括 4 种类型。

数据类型	描述	范围	格式
date	日期型	1753.1.1 ~ 9999.12.31	MM/DD/YYYY 或 MM-DD-YYYY
time	时间型	12 小时或 24 小时制时间	hh:mm:ss AM/PM
datetime	日期和时间	1753.1.1 00:00:00 ~ 9999.12.31 23:59:59	MM/DD/YYYY hh:mm:ss AM/PM
smalldatetime	小日期时间型	1900.1.1 00:00:00 ~ 2079.6.6 23:59:59	MM/DD/YYYY hh:mm:ss AM/PM

文本型#

文本型数据类型主要是用于存储超大长度的文本内容，即用 char、varchar、nchar、nvarchar四种类型还不足以表示的大数据。

数据类型	描述	范围
text	字符型，用来存储大量的非统一编码型字符数据	最多可以有 2^31-1 或 20 亿个字符
nchar	统一编码字符型，用来存储定长统一编码字符型数据	最大容量为 2^30-1 字节

二进制型#

二进制数据类型用于存储二进制数据，包括 binary、varbinary、image 三种类型。当表中各条记录这个列的内容接近相同的长度时，使用 binary 比较合理。当各条记录此列的内容长短不一，变化较大时，使用 varbinary 有利于节省存储空间。类似于照片、头像、证书等这样的字段可以采用 image 类型，支持JPG、TIFF、PNG、GIF 等格式。

数据类型	描述	范围
binary	二进制数据类型	存储最长 8000 字节长的定长的二进制数据
varbinary	可变长二进制数据类型	用来存储最长 8000 字节的二进制数据
image	图像型，用来存储变长的二进制数据	最大可达 2^31-1 字节

特殊数据类型#

数据类型	描述
timestamp	时间戳类型，表示 SQL Server 活动的先后顺序
unigueidentifier	唯一标识型类型，根据网卡地址和 CPU 时钟产生

完整性约束#

建立表的时候，还需要设置表的完整性约束，完整性约束指的是按照其值的内在逻辑或完整性而确定的限制条件。比如年龄必须是正整数、Email 中必须有 @ 符号，性别只能是“男”或者“女”等。约束分两种，一种是列级约束，即约束条件只对某一列有效，另一种是表级约束，指涉及两个以上的列。

约束类型	语法	解释
主键约束	PRIMARY KEY 或 PRIMARY KEY(列名1，列名2，…)	主键是指能够唯一代表一条记录的键，可能是一列或多列的组合
非空约束	NOT NULL	这个列的值是否可以不填写，系统默认可以不填写
唯一性约束	UNIQUE	某一列的值是否必须不同，这个列是表的一个候选键但不是主键
默认值约束	DEFAULT(默认值)	输入记录的此列没有赋值时，系统会自动使用默认值
检查约束	CHECK(表达式)	检查约束用于检查列的值是否符合要求，比如是否在指定的范围内
外键约束	FOREIGN KEY	反映两个表之间的相互联系，保证数据的唯一性

SQL 语句#

创建表#

建立表的 SQL 命令是 CREATETABLE，可以逐一将列名、数据类型、长度和列约束添加进来，还可以在命令的后面部分添加表约束。

Copy Highlighter-hljs
CREATE TABLE 表名
(
  列名 类型（长度）{列约束}
    [,…n]
  [表约束]
)

SELECT INTO 子句#

SELECT INTO 语句创建一个表，并在同一操作中往表里插入行。

创建约束#

为表创建约束有两种方法，一种是声明当前列时，在数据类型后面直接加上约束内容，另一种方法是利用 CONSTRAINT 命令添加约束。第一种方法适合于比较简单的约束，第二种单独用一行命令完成，需要给出约束名，可以对约束进行修改、删除等管理操作，但书写要复杂一些。其格式是：

Copy Highlighter-hljs
CONSTRAINT 约束名 约束内容

约束名是用户定义的一个标识符，一般是“约束类型_表名_列名”这样的格。约束类型通常用简写，PK 代表主键、UQ 表示唯一性约束、CK 代表检查约束、FK 代表外键、DF 表示默认值约束。

删除表#

Copy Highlighter-hljs
DROP TABLE 表名 [,…n]

添加、删除和修改列#

修改表结构的命令是 ALTER TABLE，其格式是：

Copy Highlighter-hljs
ALTER TABLE 表名
{
    ALTER COLUMN 列名 类型 [列约束]
    ADD 列名 类型 [列约束]
    ADD CONSTRAINT 约束名 约束内容
    DROP COLUMN 列名
    [,…n]
}

添加列时的各个选项和创建表时的列选项相同，向表中添加新列时 SQL Server 在列中为表中每个现有的数据行插入一个值。这个值为默认值或为 NULL，如果新列不允许空值，则 SQL Server 向其中插入空值时将返回错误。

Copy Highlighter-hljs
ALTER TABLE 表名 ADD 列名 数据类型  [NULL | NOT NULL]

删除列时，不能删除正在复制的列、用在索引中的列、用在 CHECK、FOREIGN KEY、UNIQUE 或 PRIMARY KEY 约束中的列和与 DEFAULT 定义关联或绑定到某一默认对象的列。

Copy Highlighter-hljs
ALTER TABLE 表名 DROP 列名 [,…n]

修改列时，需要给出列名和需要修改的数据类型。

Copy Highlighter-hljs
ALTER TABLE STU ALTER COLUMN 列名 数据类型

样例#

创建简单表#

选择合适的数据类型，创建学生表、课程表、成绩表。

Copy Highlighter-hljs
CREATE TABLE STU
(
	SNO CHAR(10) NOT NULL,    --学号
	SNAME VARCHAR(20),        --姓名
	DEPA VARCHAR(20),
	AGE INT,                  --年龄
	SEX NCHAR(1),             --性别
	TEL CHAR(11)              --电话
)

CREATE TABLE CLASS
(
	CNO CHAR(3) NOT NULL,    --课程号
	CNAME VARCHAR(20),       --课程名
	cpno  char(3),           --先修课程号
	credit int               --学分
)

CREATE TABLE SC
(
	SNO CHAR(10) NOT NULL,    --学号
	CNO CHAR(10) NOT NULL,    --课程号
	GRADE DECIMAL(4,1)        --成绩
)

修改表#

将创建简单表样例的 STU 增加联系地址列，设置合适的类型。并把 sname 改为 nchar(4)，最后删除tel 列。

Copy Highlighter-hljs
ALTER TABLE STU DROP COLUMN TEL
ALTER TABLE STU ALTER COLUMN SNAME NCHAR(4)
ALTER TABLE STU ADD ADDRESS CHAR(100)

创建带约束的表#

选择合适的数据类型，创建学生表、课程表、成绩表，并使用约束。

Copy Highlighter-hljs
create table Student
(
    Sno char(6) primary key check(Sno like'[1-9][0-9][0-9][0-9][0-9][0-9]'),    --主键，学号为 6 位数字且第一位非 0
    Sname varchar(20) UNIQUE,    --姓名唯一
    Ssex char(2) check(Ssex in('男', '女')),    --性别为男或女
    Sage int check(Sage > 0),    --年龄大于 0
    Sdept varchar(6) check(Sdept in('计算机','软件','网络','信息'))    --专业为计算机、软件、网络、信息之一
)

create table Course
(
    Cno char(4) primary key check(Cno like'[1-9][0-9][0-9][0-9]'),    --主键，课程号为 4 位数字且第一位非 0
    Cname varchar(10) not null,    --课程名非空
    Cpno char(4) foreign key references Course(Cno),    --选修课课程号和 Cno 子段有外键约束
    credit int default '2'    --学分默认为 2
)

create table SC
(
    Sno char(6) foreign key references Student(Sno) ON DELETE CASCADE ON UPDATE CASCADE,    -- 主键，外键，当 Student 表中的学号修改或删除时，自动修改或删除；
    Cno char(4) foreign key references Course(Cno) ON DELETE  NO ACTION,    -- 主键，外键，当有该课程成绩时，禁止 Course 修改或删除；
    primary key(Sno,Cno),
    Pscj decimal (4,1) check (Pscj>=0 AND Pscj<=100) default '0',    --平时分，0 ~ 100，默认为 0 的一位小数
    Qkcj decimal (4,1) check (Qkcj>=0 AND Qkcj<=100) default '0',    --考试分，0 ~ 100，默认为 0 的一位小数
    Zpcj as ((0.3)*[Pscj]+(0.7)*[Qkcj])    --最终得分
)

修改约束#

修改表 STU 把 Sname 设为主键，同时保持 SC 的外键 Sno -> STU.Sno。由于原本的主键是 Sno，因此首先需要删除 Sno 的主键。然而 Sno 和表 SC 具有外键约束关系，所以要删除 SC 的 Sno 外键。

Copy Highlighter-hljs
ALTER TABLE SC 
	DROP FK__SC__Sno__6FE99F9F

接着删除主键 Sno 约束。

Copy Highlighter-hljs
ALTER TABLE STU
	DROP PK__STU__CA1FE4646F74CBE6

接着把 Sname 设为主键，不过 Sname 之前被设置为 unique，相当于存在主键约束。因此想要把 Sname 设为主键，就先要取消掉 unique。

Copy Highlighter-hljs
ALTER TABLE STU 
	DROP UQ__STU__52723D2730EB7AE8;

此时由于 Sname 是可以为空的，然而主键不能为空，因此需要把 Sname 修改为 NOT NULL。

Copy Highlighter-hljs
ALTER TABLE STU 
	ALTER COLUMN Sname VARCHAR(20) NOT NULL

现在可以把 Sname 设为主键了。

Copy Highlighter-hljs
ALTER TABLE STU
	ADD PRIMARY KEY(Sname)

由于还要保持 Sno 的 SC 和 STU 的外键关系，因此现在就把外键加回来。

Copy Highlighter-hljs
ALTER TABLE SC
	ADD FOREIGN KEY (Sno) REFERENCES STU(Sno)

由于外键的设置，需要 2 张表中的属性要完全一致，而这时 STU 中的 Sno 字段已经不是主键和 SC 的Sno不一致。因此需要先将 STU 的 Sno 修改为 unique 才可以。

Copy Highlighter-hljs
ALTER TABLE STU 
	ADD CONSTRAINT UN_Sname unique(Sno)

现在可以设置外键了。

Copy Highlighter-hljs
ALTER TABLE SC
	ADD FOREIGN KEY (Sno) REFERENCES STU(Sno)

SELECT INTO 创建#

假设 Student 和 Score 已经有了一些数据，可以使用 SELECT 查出并通过 INTO 子句放入新表中。

Copy Highlighter-hljs
SELECT * INTO Student2 FROM Student
SELECT * INTO Score2 FROM Score WHERE Degree < 0
INSERT Score2 SELECT * FROM Score

参考资料#

《SqlServer 2014 数据库技术实用教程》，胡伏湘、肖玉朝 主编，清华大学出版社