drop DATABASE if exists test2;
create database test2 charset=utf8;

use test2;

drop table if exists book;

drop table if exists distributionList;

drop table if exists generate;

drop table if exists orderDetails;

drop table if exists orders;

drop table if exists shoppingCart;

drop table if exists users;

/*==============================================================*/
/* Table: book                                                  */
/*==============================================================*/
create table book
(
   bookNo               varchar(8) not null,
   ISBN                 varchar(8) not null,
   bookName             varchar(12) not null,
   price                float not null,
   press                varchar(12) not null,
   author               varchar(8) not null,
   primary key (bookNo)
)DEFAULT CHARSET=utf8;

/*==============================================================*/
/* Table: distributionList                                      */
/*==============================================================*/
create table distributionList
(
   deliveryNo           int auto_increment,
   orderNo              varchar(40) not null,
   userNo               varchar(10) not null,
   deliveryStatus       varchar(50) not null,
   primary key (deliveryNo)
)DEFAULT CHARSET=utf8;

/*==============================================================*/
/* Table: generate                                              */
/*==============================================================*/
-- create table generate
-- (
--    deliveryNo           varchar(10) not null,
--    primary key (deliveryNo)
-- )DEFAULT CHARSET=utf8;

/*==============================================================*/
/* Table: orderDetails                                          */
/*==============================================================*/
create table orderDetails
(
	 id                   int auto_increment,
   orderNo              varchar(30) not null ,
   deliveryStatus       varchar(30) not null,
   bookNo               varchar(8) not null,
   userNo               varchar(10) not null,
   Onumber              int not null,
   primary key (id)
)AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

/*==============================================================*/
/* Table: orders                                                */
/*==============================================================*/
create table orders
(
	 id     							int auto_increment,
   orderNo              varchar(30) not null ,
   userNo               varchar(10) not null,
	 orderStatus          varchar(50) not null,
   orderTime            date not null,
   sumMoney             float not null,
   scartNo              varchar(10) not null,
   primary key (id)
)AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

/*==============================================================*/
/* Table: shoppingCart                                          */
/*==============================================================*/
create table shoppingCart
(
	 id							      int auto_increment,
   scartNo              varchar(8) not null,
   userNo               varchar(10) not null,
   bookNo               varchar(8) not null,
   num                  int not null,
   price                float not null,
   primary key (id)
)AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

/*==============================================================*/
/* Table: users                                                 */
/*==============================================================*/
create table users
(
   userNo               varchar(10) not null,
   name                 varchar(10) not null,
   age                  int not null,
   level                int not null,
   userName             varchar(12) not null,
   passWord             varchar(8) not null,
   primary key (userNo)
)DEFAULT CHARSET=utf8;




-- ## 创建视图
-- CREATE VIEW view_users
-- (userNo,name,age,level,userName)
-- As SELECT userNo,name,age,level,userName
-- FROM users;
-- ##创建没有密码的会员信息视图

##创建索引
CREATE INDEX userNo ON users(userNo);		##建立会员号索引
CREATE INDEX orderNo ON orders(orderNo);	##建立订单号索引
CREATE INDEX bookNo ON book(bookNo);		##建立书号索引

##加入数据
INSERT INTO book (bookNo,ISBN,bookName,price,press,author)
VALUES
('B0001','00001','三国演义',61,'三峡大学出版社','罗贯中'),
('B0002','00002','红楼梦',53.2,'教育出版社','曹雪芹'),
('B0003','00003','水浒传',65,'西工出版社','施耐庵'),
('B0004','00004','西游记',82.6,'工业出版社','吴承恩'),
('B0005','00005','西游记传',19.8,'南方出版社','六小龄童');
##为book表添加数据
INSERT INTO users (userNo,name,age,level,username,password)
VALUES
('0000001','张三',11,1,'13797133321','123456'),
('0000002','李四',22,2,'13797133322','123456'),
('0000003','王五',33,3,'13797133323','123456'),
('0000004','赵柳',44,4,'13797133324','123456'),
('0000005','陈琦',55,5,'13797133325','123456');
##为user表添加数据
INSERT INTO shoppingcart(scartNo,userNo,bookNo,num,price)
VALUES
('30001','0000001','B0001',1,61),
('30001','0000001','B0002',2,53.2),
('30001','0000001','B0003',3,65),
('30001','0000001','B0004',4,82.6),
('30002','0000002','B0001',4,61),
('30002','0000002','B0002',6,53.2),
('30002','0000002','B0003',7,65),
('30003','0000003','B0001',3,61),
('30003','0000003','B0002',2,53.2),
('30004','0000004','B0004',1,82.6),
('30005','0000005','B0005',4,19.2);
##为购物车表添加数据
INSERT INTO orders (orderNo,userNo,orderTime,orderStatus,sumMoney,ScartNo)
VALUES
('1001','0000001','2000-01-01','已处理',261.8,'S0001'),
('1002','0000002','2002-02-02','已处理',179.2,'S0002'),
('1003','0000003','2003-03-03','未处理',114.2,'S0003'),
('1004','0000004','2004-04-04','未处理',82.6,'S0004'),
('1005','0000005','2005-05-05','已处理',19.2,'S0005');
##为订单表添加数据
-- INSERT INTO distributionlist(deliveryNo,orderNo,userNo,deliveryStatus,bookNo)
-- VALUES


-- ##为配送单表添加数据
INSERT INTO orderdetails(orderNo,deliveryStatus,bookNo,userNo,Onumber)
VALUES
('1001', '已配送', 'B0001', '0000001', 1),
('1001', '已配送', 'B0002', '0000001', 2),
('1001', '已配送', 'B0003', '0000001', 3),
('1002', '已配送', 'B0004', '0000002', 4),
('1002', '已配送', 'B0005', '0000002', 5);

##为订单明细表添加数据