网上书店管理系统数据库MySQL毕业用

新建数据库

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('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,'计算机');

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