#创建一个数据库
create database bookStore;

#使用数据库bookStore
use bookStore;

#删除数据库
#drop database bookStore

#创建bs_user表
create table bs_user
(id int AUTO_INCREMENT primary key, #设置自增长
username nvarchar(20),
password varchar(20),
email varchar(20)
);
#创建bs_book表
create table bs_book
(id int AUTO_INCREMENT primary key, #设置自增长
title nvarchar(20),
author nvarchar(20),
price double,
sales int(11),
stock int(11),
imgPath varchar(50)
);

#创建bs_order表
create table bs_order
(id varchar(20) primary key not null,
createTime varchar(20),
totalCount int(11),
totalAmount double,
status int(11),
userId int(11)
);
#创建bs_orderItem表
create table bs_orderItem
(id int AUTO_INCREMENT primary key, #设置自增长
createTime varchar(20),
title nvarchar(20),
author nvarchar(20),
price double,
imgPath varchar(30),
count int(11),
amount double,
orderId varchar(20)
);


#删除表
drop table bs_user;
drop table bs_book;
drop table bs_order;
drop table bs_orderItem;


#查看表
select * from bs_user;
select * from bs_book;
select * from bs_order;
select * from bs_orderItem;

#导入数据（bs_user）
insert into bs_user (username,password) values ('root',123);
insert into bs_user values (null,'ling',123,'1234567890@qq.com');

#导入数据（bs_order）
insert into bs_order values('1638183574638','2021-11-29 18:59:34',51,50.55,2,2);
#导入数据（bs_orderItem）
insert into bs_orderItem values(null,'2021-11-29 18:59:34','红楼梦','曹雪芹',50.55,'static/img/book/hlm.jpg',1,50.55,'1638183574638');

#导入数据（bs_book）
insert into bs_book values (null,'三国演义','罗贯中',50.23,300,50,'static/img/book/sgyy.jpg');
insert into bs_book values (null,'西游记','吴承恩',49.76,300,50,'static/img/book/xyj.jpg');
insert into bs_book values (null,'水浒传','施耐庵',52.34,300,50,'static/img/book/shz.jpg');
insert into bs_book values (null,'红楼梦','曹雪芹',50.55,300,50,'static/img/book/hlm.jpg');
insert into bs_book values (null,'时间简史','霍金',61.21,300,50,'static/img/book/sjjs.jpg');
insert into bs_book values (null,'斗罗大陆','唐家三少',32.77,2500,50,'static/img/book/dldl.jpg');

#分页（limit）
select id,title,author,price,sales,stock,imgPath from bs_book limit 0,3;

#根据价格区间分页（limit）
select id,title,author,price,sales,stock,imgPath from bs_book where price between 50 and 60 limit 3,3;