【T-SQL】二、表的创建与管理

二、表的创建与管理

（一）创建表

语法格式

CREATE TABLE 表名
（ 列名1 数据类型 列级完整性约束，
   列名2 数据类型 列级完整性约束，
   ……
   列名n 数据类型 列级完整性约束，
   表级完整性约束1，
   ……
   表级完整性约束n
 ）

功能：CREATE TABLE命令为表定义各列的名字、数据类型和完整性约束。

列级完整性约束

DEFAULT 常量表达式：默认值约束。
NULL/NOT NULL：空值/非空值约束。
UNIQUE：单值约束。
PRIMARY KEY：主键约束，等价非空和单值约束。
REFERENCES 父表名（主键）：外键约束。
CHECK（逻辑表达式）：检查约束。

表级完整性约束

UNIQUE（列名1，列名2，……列名n）：多个列名单值约束。
PRIMARY KEY（列名1，列名2，……列名n）：多个列名组合主键约束。
FOREIGN KEY（外键）REFERENCES 主键表（主键）：多个列名组合外键约束。
CHECK（逻辑表达式）：含有多个列名逻辑表达式的检查约束。

例题：图书管理数据库表

在图书管理系统的数据库“Library”中，创建读者表“Reader”、读者类型表“ReaderType”、图书表“Book”和借阅表“Borrow”。

ReaderType(TypeID，Typename，LimitNum，LimitDays )
PK:TypeID
Reader (RID，Rname,TypeID，Lendnum)
PK:RIDEK:Type ID
Book(BID，Bname，Author，PubComp，PubDate, Price,Class)
PK:BID
Borrow(RID，BID，LendDate,ReturnDate)
PK:RID，BID,LendDateFK:RID和BID

1.创建读者类型表“ReadType”

USE Library
GO
CREATE TABLE ReaderType               --读者类型表
(
  TypeID int not NUll PRIMARY KEY ,     --读者类型编号，主键
  Typename char(8) NULL,                --读者类型名称
  LimitNum int  NUll,                   --限借数量
  LimitDays int NUll	                 --限借天数
)

2.创建读者表“Reader”

USE Library
GO
CREATE TABLE Reader                           --读者表
(
   RID char(10) not NUll PRIMARY KEY ,  --读者编号，主键
   Rname char(8) NULL,                             --读者姓名
   TypeID int  NUll,                                      --读者类型
   Lendnum int NUll,                                   --已借数量
   Foreign key(TypeID) references ReaderType(TypeID)
   on delete no action                              --外键、不级联删除	
)

3.创建图书表“Book”

USE Library
GO
CREATE TABLE Book                                       --图书表
(
   BID char(9) not NUll PRIMARY KEY ,            ---图书编号，主键
   Bname varchar(42) NULL,                              --书名
   Author  varchar(20)  NUll,                               --作者
   PubComp varchar(28) NUll,                           --出版社
   Pubdate datetime Null,                                   --出版日期
   Price decimal(7,2) Null check (Price>0),       --价格，检查约束
   Class char(10)  NULL                                      --所属类别
)

4.创建图书借阅表“Borrow”

USE Library
GO
CREATE TABLE Borrow                           --借阅表
(
   RID char(10) not NUll                             --设置外键，级联删除
   Foreign key references reader(RID) on delete cascade,
   BID char(9) not null                                -设置外键，不级联删除
   Foreign key references Book(BID) on delete no action,
   LendDate datetime2（0）not null default(getdate()),--借书日期，默认当前日期
   ReturnDate datetime2（0）null,                     --还书日期
   Primary Key (RID,BID,LendDate)               --设置组合键为主键
)

例题：教务管理数据库及表

-- 创建教务管理数据库
CREATE DATABASE EDUC
ON PRIMARY(
NAME='EDUC',
FILENAME='F:\SQL Server 数据文件\教务管理数据文件\EDUC.mdf',
SIZE=5MB,
MAXSIZE=10MB,
FILEGROWTH=10%
)

LOG ON(
NAME='EDUC_log',
FILENAME='F:\SQL Server 数据文件\教务管理日志文件\EDUC_log.ldf',
SIZE=2MB,
MAXSIZE=4MB,
FILEGROWTH=10%
)

-- 创建教务管理馆表
use EDUC
go

-- 创建学生表
create table Student
(
SID char(10) not null primary key,
Sname char(8) not null,
Sex char(2) null check([sex]='男' or [sex]='女'),
Biethday date null,
Speciality varchar(26) null
)

go

-- 创建课程表
create table Course
(
CID char(8) not null primary key,
Canem nchar(30) not null,
Period int null,
Crefit decimal(3,1) null
)

go

-- 创建选课表
create table SC
(
SID char(10) not null
foreign key references Student(SID) on delete no action,
CID char(8) not null
foreign key references Course(CID) on delete no action,
Grade numeric(5,1) null
primary key(SID,CID)
)

（二）修改表

语法格式

ALTER TABLE 表名（
ALTER COLUMN 列名  列定义，
ADD  列名 数据类型 约束，
…
DROP 列名，
…
ADD CONSTRAINT 约束名 约束，
…
DROP CONSTRAINT 约束名，
…
）

说明：

• ALTER COLUMN 列名 列定义：修改列定义。
• ADD 列名 数据类型 约束：增加新的列。
• DROP 列名：删除已有的列。
• ADD CONSTRAINT 约束名 约束：添加约束。
• DROP CONSTRAINT 约束名：删除已有的约束。
• 其中，列定义包括列的数据类型和完整性约束。

例题：图书管理数据库表

0.使用数据库

use Library

注：语句之间可使用go进行合并执行

1.修改属性

把表“Book”中“Bname”的类型“varchar(42)”改为“varchar(40)”

alter table book
alter column Bname varchar(40) not null

2.添加或删除列

为表“Reader”新增字段邮件地址

alter table Reader
add E_mail varchar(50) null check(E_mail like '%@%')

为表“Book”新增字段“ISBN”

alter table Book
add ISBN varchar(17) null

删除表“Book”中列“ISBN”(删除后可再创建)

alter table Book
drop column ISBN

3.添加或删除约束

为表“Borrow”添加主键约束（先删除原创建时的主键约束）

alter table Borrow
add constraint PK_BH primary key (RID,BID,LendDate)

为表“Borrow”删除主键约束

alter table borrow
drop constraint PK_BH

为表“Borrow”添加外键“RID”约束（先删除原创建时的外键约束）

alter table borrow
add constraint FK_borrow foreign key(RID)  references Reader(RID)

为表“Book”添加价格大于零（Price>0）的检查约束

alter table book
add constraint ck_book_pr  check (price>0)

提示：定义check约束后，可以使用NOCHECK CONSTRAINT和CHECK CONSTRAINT子句是的CHECK约束失效和重新生效
使上例CHECK约束失效：

alter table book
nocheck constraint ck_book_pr

使上例CHECK约束重新生效：

alter table book
check constraint ck_book_pr

（三）删除表

语法格式

DROP TABLE 表名

例题：图书管理数据库表

在数据库中“Library”中创建一个表“test”

create table test(
test1 char(10) not null pirmary key,
test2 int null
)

然后删除

drop table test