网上书店管理系统数据库数据库.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(免费购