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