网上书店管理系统数据库

新建数据库

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('98','一天中10分钟的身体成分','2001-1-21',4,,,'健康');

insert into tbl_books values('03','米奇的复仇','2001-12-12',1,,22,'家庭生活'); insert into tbl_books values('10','用牙签造车', '2002-08-18',2,,59,'儿童'); insert into tbl_books values('13','数据库的实现', '1999-06-04' ,3,31,55,'计算机');

insert into tbl_books values('90','用蘑菇烹饪', '2000-02-28' ,4,,,'烹饪');

insert into tbl_books values('68','数据库法宝', '2001-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, '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');

新建表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,'90',1);

insert into Tbl_Oraderitems values(1001,1,'01',1);

insert into Tbl_Oraderitems values(1001,2,'20',1);

insert into Tbl_Oraderitems values(1002,1,'32',2);

insert into Tbl_Oraderitems values(1003,1,'32',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('98','s100');

insert into Tbl_BookAuthor values('98','p100');

insert into Tbl_BookAuthor values('03','j100');

insert into Tbl_BookAuthor values('10','k100');

insert into Tbl_BookAuthor values('13','p105');

insert into Tbl_BookAuthor values('13','a100');

insert into Tbl_BookAuthor values('13','a105');

insert into Tbl_BookAuthor values('90','b100');

insert into Tbl_BookAuthor values('68','a100');

insert into Tbl_BookAuthor values('92','w100');

insert into Tbl_BookAuthor values('92','w105');

insert into Tbl_BookAuthor values('21','j100');

insert into Tbl_BookAuthor values('20','r100');