第二次SQLServer试验解

--给BookInfo表的BookId建立主键约束,给BookInfo表的BookName建立非空约束
create table BookInfo(
BookId int primary key,
BookName varchar(50)NOT NULL,
PublishMan char(30),
BookBuymoney numeric(5,2),
bookbuytime datetime,
BookYesNo bit,
BookPrice int,
BookState int
)
create table LendInfo(
LendID varchar(10)NOT NULL,
UserId varchar(10)NOT NULL,
BookId int NOT NULL,
LendTime datetime,
backTime datetime,
UserMoney money
)
--给BookInfo表的BookPrice添加默认值,  默认为30,给BookInfo表的BookBuyTime建立默认值，默认值为’1999-12-23’
alter table BookInfo
add constraint BookPrice_n default 30 for BookPrice
alter table BookInfo
add constraint BookBuyTime_n default '1999-12-23' for BookBuyTime
--给BookInfo表的BookState建立非空约束,向BookInfo表的BookState追加默认值，,默认值为1
alter table BookInfo
add constraint BookState_nf check(BookState is not null);
alter table BookInfo
add constraint BookState_nl default 1 for BookState
--向LendInfo表的 LendID追加主键约束,向LendInfo表的BookId追加外键约束,向LendInfo表的LendTime追加默认值，默认值为当前日期
alter table LendInfo
add constraint  LendID_A primary key(LendID)
alter table LendInfo
add constraint BookId foreign key(BookId) references LendInfo(BookId)
alter table LendInfo
add constraint LendTime_C default '2014-03-27' for LendTime
--向BookInfo表中插入记录：图书编号为1001，图书名称为涩女郎，种类为文学，购进价格为12.00元，购进日期为2004-8-24，借阅状态为已借
insert  into  BookInfo
(BookId, BookName,  PublishMan, BookPrice,bookbuytime,BookState)
values
('1001','涩女郎','文学',12.00,'2004-8-24',1)
--向BookInfo表中插入记录:插入图书编号为1002，图书名称为一米阳光的图书. 向借阅表中插入图书编号为1002的图书
insert  into  BookInfo
(BookId, BookName)
values
('1002','一米阳光')
insert  into  LendInfo
(LendID,UserId,BookId)
values
(1,1,'1002')
--将图书表中的图书编号为1001的图书的种类改为科普
update BookInfo set PublishMan='科普'
where BookId='1001'
--对图书表操作，将所有的进价为10.00元的图书的进价改为12.00元,删除借阅表中的名为涩女郎的图书
update BookInfo set BookPrice=12.00
where BookPrice=10.00
--update BookInfo set BookPrice=10.00
--where BookPrice=12.00
--删除LendInfo表,删除BookInfo表
drop table LendInfo
drop table BookInfo