106 MySQL_11 _综合练习

最新推荐文章于 2022-07-28 13:37:59 发布

悬浮海

最新推荐文章于 2022-07-28 13:37:59 发布

阅读量450

点赞数 1

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/wang_luwei/article/details/107682348

版权

MySQL 专栏收录该内容

40 篇文章

订阅专栏

本文提供了一套MySQL数据库的综合练习案例，包括多表查询、子查询及分页查询等核心技能训练，通过实际操作加深对SQL语句的理解与运用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

106 MySQL_11 _综合练习

综合练习

1.数据库表

#创建用户表 
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 fk_products_category_id  FOREIGN KEY(category_id) REFERENCES category(cid)
);


#订单表
CREATE TABLE orders(
	oid VARCHAR(32) PRIMARY KEY ,
	totalprice DOUBLE(12,2), 
	userid INT ,
	CONSTRAINT fk_orders_userId FOREIGN KEY(userid) REFERENCES USER(userid) #外键
);


#订单项表
create table orderitem( 
    oid varchar(32), #订单id 
    pid varchar(32), #商品id 
    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 USER(username,PASSWORD, address,phone) 
VALUES( '张三','123','北京昌平沙河', '13812345678');

INSERT INTO USER(username, PASSWORD, address, phone) 
VALUES('王五', '567','北京海淀', '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',5000, 'c001'); 

insert into products(pid,name,price,category_id)
values('P002', '海尔3000',3000,'c001' );

insert into products(pid,name,price,category_id) 
values('p003', '雷神','5000',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,'002');

insert into products(pid,name,price,category_id)
values('p006', '花花公子',1440, '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','相宜本草',1200,'C003');       
       
insert into products(pid,name,price,category_id)
values( 'p010','梅明子',1200,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);

2.综合练习1-【多表查询】

2.1查询所有用户的订单

SELECT o.oid,o.totalprice, u.userid,u.username,u.phone FROM orders o 
INNER JOIN USER u ON o.userld=u.userid;

2.2查询用户id为1的所有订单详情

SELECT o.oid,o.totalprice, u.userid,u.username,u.phone ,oi.pid FROM orders o 
INNER JOIN USER u ON o.userId=u.userid 
INNER JOIN orderitem oi ON o.oid=oi.oid
where u .userid=1;

3.综合练习2-【子查询】

3.1查看用户为张三的订单

SELECT * FROM orders 
WHERE userId=(SELECT userid FROM USER WHERE username='张三')

3.2查询出订单的价格大于800的所有用户信息。

SELECT * FROM USER 
WHERE userid IN (SELECT DISTINCT userid FROM orders WHERE totalprice>800);

4.综合练习3-【分页查询】

4.1查询所有订单信息，每页显示5条数据

#查询第一页

SELECT * FROM orders LIMIT 0,5;