SQL 基础（三）数据表的创建与管理实战演练

结合基础知识，实际操作，巩固提升，加深记忆

实验报告

实验

1. 阅读数据表 1-表 4，考虑各字段添加约束是否合理
2. 编写 T-SQL 语句，创建表以及约束
3. 查看创建表的信息
4. 通过向表中插入数据验证数据完整性，并回答相应问题

实验任务 1

利用 T-SQL 完成书籍信息表（tb_BookInfo）的设计与创建

实验任务 2

按要求完成数据表完整性的设计，要求利用 T-SQL 在书籍信息表（tb_BookInfo）中添加以下约束：

1. 在书籍编号字段上添加主键约束
2. 在登记日期字段上添加当前日期
3. 在是否借出字段上默认值为 0

实验任务 3

利用 T-SQL 完成数据表 2、数据表 3 、数据表 4 的设计与创建

实验任务 4

利用 T-SQL 语句，在借书信息表（tb_LentInfo）表中加入一列 colBZ（备注）,数据类型 varchar，长度 100

实验任务 5

利用 T-SQL 语句删除 colBZ（备注）列

实验任务 6

利用 T-SQL 语句删除借书信息表（tb_LentInfo）中应还书日期字段上的检查约束

Query.sql

-- 数据库建立
create database db_book
on 
(
name=db_book,
filename='D:\Data\db_book.mdf'
)

-- 任务1
create table db_book.dbo.tb_BookInfo
(
SJBH char(10) not null,
SJMC varchar(20) not null,
LBBH char(2),
CBS varchar(30),
ZZ varchar(20),
DJRQ datetime, --日期时间型
SFJC bit
)

-- 任务2
alter table db_book.dbo.tb_BookInfo
ADD
primary key(SJBH),
default getdate() for DJRQ, -- 返回当前日期
constraint SFJC_default default 0 for SFJC

-- 任务3

-- 数据表2
create table db_book.dbo.tb_ReaderInfo(
DZBH char(10) primary key,
DZMC varchar(20) not null,
BZSJ Datetime default getdate(),
LXDH varchar(30) not null
)

-- 数据表3
create table db_book.dbo.tb_BookType(
LBBH char(2) not null primary key,
LBMC char(30) not null 
)

-- 数据表4
create table db_book.dbo.tb_LentInfo(
DZBH char(10) not null foreign key (DZBH) references db_book.dbo.tb_ReaderInfo(DZBH),
SJBH char(10) not null foreign key (SJBH) references db_book.dbo.tb_BookInfo(SJBH),
JSRQ Datetime not null,
YHSRQ Datetime  not null,
HSRQ Datetime null,
check (YHSRQ>JSRQ) 
)

-- 任务4
use db_book -- 指明使用哪个数据库
alter table tb_LentInfo
ADD 
colBZ varchar(100)

-- 任务5
alter table tb_LentInfo
DROP
column colBZ

-- 任务6
alter table tb_LentInfo
DROP
constraint CK__tb_LentInfo__2E1BDC42 -- 查看系统默认约束名称

其中，由于 tb_LentInfo 借书信息表中日期字段的检查（CHECK）约束，我们并未指定约束名称，所以在删除约束时可以通过下述方法查看系统默认约束名称

1.打开对象资源管理器

2.依次找到对应列约束

3.查看约束名称

拓展提升

根据以下提供的数据库、表结构、表中记录等信息完成相应的 Transact-SQL 语句。产品销售数据库，数据库名为 db_CPXS。数据库 db_CPXS 包含下列 3 个表：

• 产品表：表名为 tb_CP，描述产品信息；
• 销售商表：表名为 tb_XSS，描述销售商信息；
• 产品销售表：表名为 tb_XSCP，描述产品的销售信息；

各表的结构分别如表 1-表 3 所示，表中约束自行设计：

请设计数据表 1-表 3 的约束，并写出创建数据表的 T-SQL 语句

Query.sql

-- 拓展提升

-- 数据库创建
create database db_CPXS
on(
name=db_CPXS,
filename='D:\Data\db_CPXS.mdf'
)

-- 产品表
use db_CPXS
create table tb_CP(
CPBH char(6) primary key, -- 主键不允许为空,无需重复定义
CPMC char(30) not null,
JG float(8) null check (JG>0),
KCL int null check(KCL>=0)
)

-- 销售商表
use db_CPXS
create table tb_XSS(
XSBH char(6) primary key,
XSMC char(30) not null,
DQ char(10),
FZR char(8),
DH char(12),
BZ text
)

-- 产品销售表
use db_CPXS
create table tb_XSCP(
CPBH char(6) not null foreign key(CPBH) references tb_CP(CPBH),
XSBH char(6) not null foreign key(XSBH) references tb_XSS(XSBH),
XSSJ datetime default getdate(),
SL int check(SL>=0),
JE float check(JE>0),
primary key(CPBH,XSBH)
)