第二次SQLServer试验解

 1 --给BookInfo表的BookId建立主键约束,给BookInfo表的BookName建立非空约束
 2 create table BookInfo(
 3 BookId int primary key,
 4 BookName varchar(50)NOT NULL,
 5 PublishMan char(30),
 6 BookBuymoney numeric(5,2),
 7 bookbuytime datetime,
 8 BookYesNo bit,
 9 BookPrice int,
10 BookState int
11 )
12 create table LendInfo(
13 LendID varchar(10)NOT NULL,
14 UserId varchar(10)NOT NULL,
15 BookId int NOT NULL,
16 LendTime datetime,
17 backTime datetime,
18 UserMoney money
19 )
20 --给BookInfo表的BookPrice添加默认值,  默认为30,给BookInfo表的BookBuyTime建立默认值,默认值为’1999-12-23’
21 alter table BookInfo
22 add constraint BookPrice_n default 30 for BookPrice
23 alter table BookInfo
24 add constraint BookBuyTime_n default 1999-12-23 for BookBuyTime
25 --给BookInfo表的BookState建立非空约束,向BookInfo表的BookState追加默认值,,默认值为1
26 alter table BookInfo
27 add constraint BookState_nf check(BookState is not null);
28 alter table BookInfo
29 add constraint BookState_nl default 1 for BookState
30 --向LendInfo表的 LendID追加主键约束,向LendInfo表的BookId追加外键约束,向LendInfo表的LendTime追加默认值,默认值为当前日期
31 alter table LendInfo
32 add constraint  LendID_A primary key(LendID)
33 alter table LendInfo
34 add constraint BookId foreign key(BookId) references LendInfo(BookId)
35 alter table LendInfo
36 add constraint LendTime_C default 2014-03-27 for LendTime
37 --向BookInfo表中插入记录:图书编号为1001,图书名称为涩女郎,种类为文学,购进价格为12.00元,购进日期为2004-8-24,借阅状态为已借
38 insert  into  BookInfo
39 (BookId, BookName,  PublishMan, BookPrice,bookbuytime,BookState)
40 values
41 (1001,涩女郎,文学,12.00,2004-8-24,1)
42 --向BookInfo表中插入记录:插入图书编号为1002,图书名称为一米阳光的图书. 向借阅表中插入图书编号为1002的图书
43 insert  into  BookInfo
44 (BookId, BookName)
45 values
46 (1002,一米阳光)
47 insert  into  LendInfo
48 (LendID,UserId,BookId)
49 values
50 (1,1,1002)
51 --将图书表中的图书编号为1001的图书的种类改为科普
52 update BookInfo set PublishMan=科普
53 where BookId=1001
54 --对图书表操作,将所有的进价为10.00元的图书的进价改为12.00元,删除借阅表中的名为涩女郎的图书
55 update BookInfo set BookPrice=12.00
56 where BookPrice=10.00
57 --update BookInfo set BookPrice=10.00
58 --where BookPrice=12.00
59 --删除LendInfo表,删除BookInfo表
60 drop table LendInfo
61 drop table BookInfo

第二次SQLServer试验解,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。