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

新建数据库
Create database BookData;
Use BookData;
新建表B1
create 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);
新建表B2
create 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,'计算机');
新建表B3
create 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');

新建表B4
create 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不可以
新建表B5
create 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','陈康');
新建表B6
create 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');
新建表B7
create 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');
新建表B8
create 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;