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)
);

#创建分类表
create table category(
cid varchar(32) PRIMARY KEY ,
cname varchar(100) not null #分类名称
);

商品表

CREATE TABLE products (
pid varchar(32) PRIMARY KEY,
name VARCHAR(40) ,
price DOUBLE(7,2),
category_id varchar(32),
constraint foreign key(category_id) references category(cid)
);

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

订单项表

create table orderitem(
oid varchar(32), #订单id
pid varchar(32), #商品id
num int , #购买商品数量
primary key(oid,pid), #主键
foreign key(oid) references orders(oid),
foreign key(pid) references products(pid)
);

添加数据

#用户表添加数据
INSERT INTO USER(username,PASSWORD,address,phone) VALUES(‘张三’,‘123’,‘北京昌平沙河’,‘13812345678’);
INSERT INTO USER(username,PASSWORD,address,phone) VALUES(‘王五’,‘5678’,‘北京海淀’,‘13812345141’);
INSERT INTO USER(username,PASSWORD,address,phone) VALUES(‘赵六’,‘123’,‘北京朝阳’,‘13812340987’);
INSERT INTO USER(username,PASSWORD,address,phone) VALUES(‘田七’,‘123’,‘北京大兴’,‘13812345687’);

#给商品表初始化数据
insert into products(pid,name,price,category_id) values(‘p001’,‘联想’,5000,‘c001’);
insert into products(pid,name,price,category_id) values(‘p002’,‘海尔’,3000,‘c001’);
insert into products(pid,name,price,category_id) values(‘p003’,‘雷神’,5000,‘c001’);
insert into products(pid,name,price,category_id) values(‘p004’,‘JACK JONES’,800,‘c002’);
insert into products(pid,name,price,category_id) values(‘p005’,‘真维斯’,200,‘c002’);
insert into products(pid,name,price,category_id) values(‘p006’,‘花花公子’,440,‘c002’);
insert into products(pid,name,price,category_id) values(‘p007’,‘劲霸’,2000,‘c002’);
insert into products(pid,name,price,category_id) values(‘p008’,‘香奈儿’,800,‘c003’);
insert into products(pid,name,price,category_id) values(‘p009’,‘相宜本草’,200,‘c003’);
insert into products(pid,name,price,category_id) values(‘p010’,‘梅明子’,200,null);

#给分类表初始化数据
insert into category values(‘c001’,‘电器’);
insert into category values(‘c002’,‘服饰’);
insert into category values(‘c003’,‘化妆品’);
insert into category values(‘c004’,‘书籍’);

#添加订单
insert into orders values(‘o6100’,18000.50,1);
insert into orders values(‘o6101’,7200.35,1);
insert into orders values(‘o6102’,600.00,2);
insert into orders values(‘o6103’,1300.26,4);

#订单详情表
insert into orderitem values(‘o6100’,‘p001’,1),(‘o6100’,‘p002’,1),(‘o6101’,‘p003’,1);

需求：

查询所有用户的订单：

需求二：查询用户id为 1 的所有订单详情

实现功能

注意：

1.使用inner join 时 要明确连接条件，不然很容易找不到数据

错误示范：

查询所有订单价格大于800的用户：

distinct：去重
in：一个区间

查询所有订单，每页分5行数据：