千峰JAVA学院 --- mysql数据库教程综合实验

# 综合练习
# 创建一个用户表
create table user(
				userid int primary key AUTO_increment,
				username varchar(20) not null,
				`password` varchar(18) not null,
		address varchar(100),
		phone varchar(11)
);

# 向用户表里面添加数据
insert into user(username,password,address,phone) values('张三','123','湖南长沙','110100'),('赵六','124','湖南常德','1104518510'),('田七','125','湖南怀化','1151510100');

insert into user(username,password,address,phone) values('赵四','126','湖南益阳','122210100')

# 创建商品的分类表
create table category(
		cid varchar(32) PRIMARY key,
		cname Varchar(100) not null
);

# 向分类表里面插入数据
insert into category values('c001','电器'),('c002','服饰'),('c003','化妆品'),('c004','书籍');

select * from category;

# 创建商品表
create table `products` (
		`pid` varchar(32) primary key ,
		`name` varchar(40) ,
		`price` double(7,2),
		`category_id` varchar(32),
		constraint fk_products_category_id foreign key(category_id) references category(cid)
);


# 为商品表添加数据
insert into products(pid,name,price,category_id) 
values
('p001','联想',5000,'c001'),
('p002','海尔',3000,'c001'),
('p003','雷神',5000,'c001'),
('p004','JACK JONES',800,'c002'),
('p005','真维斯',200,'c002'),
('p006','花花公子',440,'c002'),
('p007','京巴',2000,'c002'),
('p008','香奈儿',800,'c003'),
('p009','本草纲目',200,'c003'),
('p010','梅明子',200,'null');

select * from products

# 创建订单表
create table `orders`(
		`oid` varchar(32) primary key,
		`totalprice` double(12,2), # 总计
		`userId` int ,
		constraint fk_orders_userId foreign key(userId) references user(userId) #外键
);

alter table orders change old oid Varchar(32);

# 向订单表里面添加数据
insert into orders(old,totalprice,userId)
values
('o6100',18000.50,1),
('o6101',7200.35,1),
('o6102',600.00,2),
('o6104',200.50,3),
('o6103',1300.26,4);
select * from orders


# 创建一个订单项表
create table orderitem (
		oid varchar(32),
		pid VARCHAR(32),
		num int,
		primary key(oid,pid),
		constraint fk_orderitem_oid foreign key(oid) references orders(oid),
		constraint fk_orderitem_pid foreign key(pid) references products(pid)
);

# 向订单项表里面插入数据
insert into orderitem
value
('o6100','p001',1),
('o6100','p002',1),
('o6101','p003',1);

select * from orderitem
# 数据设置完毕，开始练习题目


# 查询所有用户的订单
# 链接用户表  和 订单表

# 查询所有用户的订单
select * from `user`
inner join orders on  `user`.userid = orders.userid # 链接orders表

# 查询用户id为1的所有订单详情
# 链接 用户表、订单 、 订单详情
select * from `user`
inner join orders 
on `user`.userid = orders.userid
inner join orderitem
on orders.oid = orderitem.oid
where `user`.userid = 1;

# 查看用户为张三的订单
select userid from `user` where username = '张三';
select * from orders where userid = (select userid from `user` where username = '张三');


# 查询订单价格大于800的所有用户信息
select distinct userid from orders where totalprice > 800;
select * from `user` where userid in (select userid from orders where totalprice > 800)

# 分页查询所有的订单信息，每页五调数据
select * from orders limit 0,5
select * from orders limit 5,5