网上书店管理系统数据库DB2.doc
新建表B1create table Tbl_Customer(Customer Num(4)not null,Customer_Name varchar(10),Address varchar(20),City varchar(20),State varchar(20),Zip varchar(6),Referred Num(4); 插入数据insert into tbl_customer values(1001,'张三','白下区','南京','江苏','210000',null )insert into tbl_customer values(1002,'李四','徐汇区','上海','上海','110000', null)insert into tbl_customer values(1003,'王五','朝阳区','北京','北京','110000',null)insert into tbl_customer values(1004,'赵六','北大街','无锡','江苏','110000', null)insert into tbl_customer values(1005,'李三','平江路','苏州','江苏','110000', null)insert into tbl_customer values(1006,'陈三','升州路','南京','江苏','110000', null)insert into tbl_customer values(1007, '朱三','三牌楼','合肥','安徽','110000',1003)新建表B2create table Tbl_Books(Isbn varchar(10) not null,Title varchar(300),Pubdate Date,Pubid Num(2),Costs Num(5,2),Retail Num(5,2),Categorys varchar(12)插入数据insert into tbl_books values('1059831198','一天中10分钟的身体成分','2001-1-21',4,18.75,30.95,'健康')insert into tbl_books values('0401140733','米奇的复仇','2001-12-12',1,14.2,22,'家庭生活')insert into tbl_books values('4981341710','用牙签造车', '2002-08-18',2,37.8,59,'儿童')insert into tbl_books values('8843172113','数据库的实现', '1999-06-04' ,3,31,55,'计算机')insert into tbl_books values('3437212490','用蘑菇烹饪', '2000-02-28' ,4,12.5,19.95,'烹饪')insert into tbl_books values('3957136468','数据库法宝', '2001-11-30' ,3,47,75,'计算机')新建表B3create table Tbl_Orders(Order num(4)not null,Customer num(4),Orderdate Date, Shipdate Date , Shipstreet varchar(18) ,Shipcity varchar(15),Shipstate varchar(2),Shipzip varchar(6)插入数据insert into Tbl_Orders values(1000,1005, '2003-03-31' , '2003-04-02' , '奥体大街' , '南京','江苏','210019')insert into Tbl_Orders values(1001,1010, '2003-03-31' , '2003-04-01' , '和会街' , '南京','江苏','210001')insert into Tbl_Orders values(1002,1011, '2003-03-31' , '2003-04-01' , '南京路' , '上海','上海','201100')insert into Tbl_Orders values(1003,1001, '2003-04-01' , '2003-04-01' , '南京路' , '上海','上海','201100')insert into Tbl_Orders values(1004,1020, '2003-04-01' , '2003-04-05' , '欧风街' , '无锡','江苏','204002')新建表B4create table Tbl_Oraderitems(order num(4) not null,Item num(2) not null,Isbn varchar(10),quantity num(3)插入数据insert into Tbl_Oraderitems values(1000,1,'3437212490',1)insert into Tbl_Oraderitems values(1001,1,'9247381001',1)insert into Tbl_Oraderitems values(1001,2,'2491748320',1)insert into Tbl_Oraderitems values(1002,1,'8843174832',2)insert into Tbl_Oraderitems values(1003,1,'8843174832',1)新建表B5create table Tbl_Author(AuthorId varchar(4) not null,name varchar(20)插入数据insert into Tbl_Author values('s100','薛明')insert into Tbl_Author values('j100','陈洪')insert into Tbl_Author values('a100','张二')insert into Tbl_Author values('m100','刘虎')insert into Tbl_Author values('k100','陈康')新建表B6create table Tbl_BookAuthor(Isbn varchar(10) not null,AuthorId varchar(4) not null)drop table Tbl_BookAuthor插入数据insert into Tbl_BookAuthor values('1059831198','s100')insert into Tbl_BookAuthor values('1059831198','p100')insert into Tbl_BookAuthor values('0401140733','j100')insert into Tbl_BookAuthor values('4981341710','k100')insert into Tbl_BookAuthor values('8843172113','p105')insert into Tbl_BookAuthor values('8843172113','a100')insert into Tbl_BookAuthor values('8843172113','a105')insert into Tbl_BookAuthor values('3437212490','b100')insert into Tbl_BookAuthor values('3957136468','a100')insert into Tbl_BookAuthor values('1915762492','w100')insert into Tbl_BookAuthor values('1915762492','w105')insert into Tbl_BookAuthor values('9959789321','j100')insert into Tbl_BookAuthor values('2491748320','r100')insert into Tbl_BookAuthor values('2491748320','f100')insert into Tbl_BookAuthor values('2491748320','b100')insert into Tbl_BookAuthor values('0299282519','s100')insert into Tbl_BookAuthor values('8117949391','r100')insert into Tbl_BookAuthor values('0132149871','s100')insert into Tbl_BookAuthor values('9247381001','w100')insert into Tbl_BookAuthor values('2147428890','w105')新建表B7create table Tbl_Publisher(Pubid varchar(10) not null,Name varchar(23) ,Contact Varchar(15),Phone varchar(12)插入数据insert into Tbl_Publisher values('1','新华出版社','程明','000-714-8321')insert into Tbl_Publisher values('2','扬子出版社','张译','010-414-8321')insert into Tbl_Publisher values('3','人民教育出版社','园艺','044-714-8321')insert into Tbl_Publisher values('4','北京大学出版社','郑爽','000-564-8321')insert into Tbl_Publisher values('5','机械工业出版社','陆华','000-714-8751')新建表B8create table Tbl_Promotion(geft varchar(100),minretail num(5,2),maxretail num(5,2)插入数据insert into Tbl_Promotion values('书签',0,12)insert into Tbl_Promotion values('书签题题',12.01,25)insert into Tbl_Promotion values('图书封面',25.01,56)insert into Tbl_Promotion values('免费购物券',56.01,999.99)select * from Tbl_Customerselect * from Tbl_Booksselect * from Tbl_Ordersselect * from Tbl_Oraderitemsselect * from Tbl_Authorselect * from Tbl_BookAuthorselect * from Tbl_Publisherselect * from Tbl_Promotion