# 综合练习
# 创建一个用户表
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
千峰JAVA学院 --- mysql数据库教程综合实验
最新推荐文章于 2024-01-20 12:55:13 发布