网上书店管理系统数据库MySQL样本.doc

新建数据库Create database BookData;Use BookData;新建表B1create table Tbl_Customer(Customer decimal not null,Customer_Name varchar(10),Address varchar(20),City varchar(20),State varchar(20),Zip varchar(6),Referred decimal)；插入数据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 decimal(2),Costs decimal(5,2),Retail decimal(5,2),Categorys varchar(12);插入数据insert into tbl_books values(,一天中10分钟身体成分,-1-21,4,18.75,30.95,健康);insert into tbl_books values(,米奇复仇,-12-12,1,14.2,22,家庭生活);insert into tbl_books values(,用牙签造车，-08-18,2,37.8,59,小朋友);insert into tbl_books values(,数据库实现，1999-06-04 ,3,31,55,计算机);insert into tbl_books values(,用蘑菇烹饪，-02-28 ,4,12.5,19.95,烹饪);insert into tbl_books values(,数据库法宝，-11-30 ,3,47,75,计算机);新建表B3create table Tbl_Orders(Orders decimal(4)not null,Customer decimal(4),Orderdate Date，Shipdate Date ，Shipstreet varchar(18) ,Shipcity varchar(15),Shipstate varchar(2),Shipzip varchar(6);修改数据长度时用alter table 表名 modify 字段名称 字段类型插入数据insert into Tbl_Orders values(1000,1005，-03-31 ，-04-02 ，奥体大街 ，南京,江苏,210019);insert into Tbl_Orders values(1001,1010，-03-31 ，-04-01 ，和会街 ，南京,江苏,210001);insert into Tbl_Orders values(1002,1011，-03-31 ，-04-01 ，南京路 ，上海,上海,00);insert into Tbl_Orders values(1003,1001，-04-01 ，-04-01 ，南京路 ，上海,上海,00);insert into Tbl_Orders values(1004,1020，-04-01 ，-04-05 ，欧风街 ，无锡,江苏,204002);新建表B4create table Tbl_Oraderitems(orders decimal(4) not null,Item decimal(2) not null,Isbn varchar(10),quantity decimal(3);插入数据insert into Tbl_Oraderitems values(1000,1,1);insert into Tbl_Oraderitems values(1001,1,1);insert into Tbl_Oraderitems values(1001,2,1);insert into Tbl_Oraderitems values(1002,1,2);insert into Tbl_Oraderitems values(1003,1,1);MySQL语句可以放在同一行；但是DB2不可以新建表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(,s100);insert into Tbl_BookAuthor values(,p100);insert into Tbl_BookAuthor values(,j100);insert into Tbl_BookAuthor values(,k100);insert into Tbl_BookAuthor values(,p105);insert into Tbl_BookAuthor values(,a100);insert into Tbl_BookAuthor values(,a105);insert into Tbl_BookAuthor values(,b100);insert into Tbl_BookAuthor values(,a100);insert into Tbl_BookAuthor values(,w100);insert into Tbl_BookAuthor values(,w105);insert into Tbl_BookAuthor values(,j100);insert into Tbl_BookAuthor values(,r100);insert into Tbl_BookAuthor values(,f100);insert into Tbl_BookAuthor values(,b100);insert into Tbl_BookAuthor values(,s100);insert into Tbl_BookAuthor values(,r100);insert into Tbl_BookAuthor values(,s100);insert into Tbl_BookAuthor values(,w100);insert into Tbl_BookAuthor values(,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 decimal(5,2),maxretail decimal(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_Customer;select * from Tbl_Books;select * from Tbl_Orders;select * from Tbl_Oraderitems；select * from Tbl_Author;select * from Tbl_BookAuthor;select * from Tbl_Publisher;select * from Tbl_Promotion;