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

二、表的创建与管理

文章目录

二、表的创建与管理（一）创建表语法格式列级完整性约束表级完整性约束例题：图书管理数据库表1.创建读者类型表“ReadType”2.创建读者表“Reader”3.创建图书表“Book”4.创建图书借阅表“Borrow” 例题：教务管理数据库及表 （二）修改表语法格式例题：图书管理数据库表0.使用数据库1.修改属性2.添加或删除列3.添加或删除约束 （三）删除表语法格式例题：图书管理数据库表

（一）创建表

语法格式

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