You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
179 lines
6.2 KiB
179 lines
6.2 KiB
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);
|
|
|
|
##为订单明细表添加数据 |