SQL练习二条件查询

最新推荐文章于 2024-07-18 15:32:08 发布

IT南城小仙女

最新推荐文章于 2024-07-18 15:32:08 发布

阅读量446

点赞数

分类专栏： Oracal学习笔记文章标签： sql

本文链接：https://blog.csdn.net/qq_35091777/article/details/78047141

版权

Oracal学习笔记专栏收录该内容

11 篇文章 0 订阅

订阅专栏

Drop table order_detail_tab;
Drop table order_tab;
Drop table user_tab;
Drop table sp_tab;
Drop table sp_type;

create table sp_type(
id nvarchar2(20) not null primary key,
name nvarchar2(30),
p_id nvarchar2(20)
);

create table sp_tab(
id nvarchar2(20) not null primary key,
name nvarchar2(30),
model nvarchar2(50),
sp_type_id nvarchar2(20),
sp_text clob,
spcie number(8,2),
c_number number(10),
foreign key(sp_type_id) References sp_type(id)
);
select * from user_tab;
create table user_tab(
id nvarchar2(20) not null primary key,
name nvarchar2(30),
pwd nvarchar2(6),
type nvarchar2(1),
creat_time date
);

create table order_tab(
id nvarchar2(20) not null primary key,
user_id nvarchar2(20),
creat_time date,
type nvarchar2(1),
foreign key(user_id) References user_tab(id)
);

create table order_detail_tab(
id nvarchar2(20) not null primary key,
order_id nvarchar2(20),
sp_id nvarchar2(20),
count number(10),
foreign key(order_id) References order_tab(id)
);
--分类表
insert into sp_type(id,name,p_id) values('1','家电','');
insert into sp_type(id,name,p_id) values('2','生鲜','');
insert into sp_type(id,name,p_id) values('3','服装','');
insert into sp_type(id,name,p_id) values('4','化妆品','');
insert into sp_type(id,name,p_id) values('5','鞋','');
insert into sp_type(id,name,p_id) values('6','生活用品','');
insert into sp_type(id,name,p_id) values('7','手机','');
insert into sp_type(id,name,p_id) values('8','电脑','');
insert into sp_type(id,name,p_id) values('9','笔记本','8');
insert into sp_type(id,name,p_id) values('10','女鞋','5');
insert into sp_type(id,name,p_id) values('11','男鞋','5');
insert into sp_type(id,name,p_id) values('12','台式机','8');
insert into sp_type(id,name,p_id) values('13','小家电','1');
insert into sp_type(id,name,p_id) values('14','大家电','1');
insert into sp_type(id,name,p_id) values('15','冰箱','14');
insert into sp_type(id,name,p_id) values('16','电视','14');
insert into sp_type(id,name,p_id) values('17','洗衣机','14');
insert into sp_type(id,name,p_id) values('18','吹风机','13');
insert into sp_type(id,name,p_id) values('19','电磁炉','13');
commit;
--商品表
insert into sp_tab(id,name,model,Sp_Type_Id,Sp_Text,Spcie) values('1','飞利浦吹风机','NSF-01-81','18','',98.00);
insert into sp_tab(id,name,model,Sp_Type_Id,Sp_Text,Spcie) values('2','飞科吹风机','M-80','18','可折叠，冷热风',56.00);
insert into sp_tab(id,name,model,Sp_Type_Id,Sp_Text,Spcie) values('3','TCL冰箱','TS009-1','15','',1500);
insert into sp_tab(id,name,model,Sp_Type_Id,Sp_Text,Spcie) values('4','美的电磁炉','MD-009','19','电子控制',138.00);
insert into sp_tab(id,name,model,Sp_Type_Id,Sp_Text,Spcie) values('5','华为手机','MATE 8','7','',2999);
insert into sp_tab(id,name,model,Sp_Type_Id,Sp_Text,Spcie) values('6','华为手机','p10','7','',3999);
insert into sp_tab(id,name,model,Sp_Type_Id,Sp_Text,Spcie) values('7','牛肉','进口','2','每斤价格',90);
insert into sp_tab(id,name,model,Sp_Type_Id,Sp_Text,Spcie) values('8','牛肉','国产','7','每斤价格',40);
insert into sp_tab(id,name,model,Sp_Type_Id,Sp_Text,Spcie) values('9','华硕笔记本','H-09-R51','8','送无线键盘鼠标套装',4999);
insert into sp_tab(id,name,model,Sp_Type_Id,Sp_Text,Spcie) values('10','IBM笔记本','R31-97C62','8','',7999);
commit;

--用户表

insert into user_tab(id,name,pwd,type,Creat_Time) values('1','张继','123456','0',to_date('2017-08-23 09:28:01','YYYY-mm-dd HH24:mi:ss'));
insert into user_tab(id,name,pwd,type,Creat_Time) values('2','张飞','111111','0',to_date('2017-08-23 19:28:01','YYYY-mm-dd HH24:mi:ss'));
insert into user_tab(id,name,pwd,type,Creat_Time) values('3','楠西','000000','1',to_date('2017-08-21 10:28:01','YYYY-mm-dd HH24:mi:ss'));
insert into user_tab(id,name,pwd,type,Creat_Time) values('4','李楠','888888','0',to_date('2017-08-19 12:28:01','YYYY-mm-dd HH24:mi:ss'));
insert into user_tab(id,name,pwd,type,Creat_Time) values('5','陈楠','bf9014','0',to_date('2017-07-19 11:28:01','YYYY-mm-dd HH24:mi:ss'));
insert into user_tab(id,name,pwd,type,Creat_Time) values('6','刘娜','123456','0',to_date('2017-07-19 03:28:01','YYYY-mm-dd HH24:mi:ss'));
insert into user_tab(id,name,pwd,type,Creat_Time) values('7','娜娜','335678','0',to_date('2017-07-17 11:28:01','YYYY-mm-dd HH24:mi:ss'));
insert into user_tab(id,name,pwd,type,Creat_Time) values('8','Tom','667841','0',to_date('2017-06-15 21:28:01','YYYY-mm-dd HH24:mi:ss'));
commit;
--订单
insert into order_tab(id,User_Id,Type,Creat_Time) values('1','1','3',to_date('2017-09-3 10:28:01','YYYY-mm-dd HH24:mi:ss'));
insert into order_tab(id,User_Id,Type,Creat_Time) values('2','2','3',to_date('2017-09-1 10:28:01','YYYY-mm-dd HH24:mi:ss'));
insert into order_tab(id,User_Id,Type,Creat_Time) values('3','3','4',to_date('2017-05-14 10:28:01','YYYY-mm-dd HH24:mi:ss'));
insert into order_tab(id,User_Id,Type,Creat_Time) values('4','4','4',to_date('2017-03-12 10:28:01','YYYY-mm-dd HH24:mi:ss'));
insert into order_tab(id,User_Id,Type,Creat_Time) values('5','5','4',to_date('2017-01-17 10:28:01','YYYY-mm-dd HH24:mi:ss'));
insert into order_tab(id,User_Id,Type,Creat_Time) values('6','6','3',to_date('2017-09-14 10:28:01','YYYY-mm-dd HH24:mi:ss'));
insert into order_tab(id,User_Id,Type,Creat_Time) values('7','7','4',to_date('2017-09-1 10:28:01','YYYY-mm-dd HH24:mi:ss'));
insert into order_tab(id,User_Id,Type,Creat_Time) values('8','8','4',to_date('2017-08-14 10:28:01','YYYY-mm-dd HH24:mi:ss'));
insert into order_tab(id,User_Id,Type,Creat_Time) values('9','2','4',to_date('2017-04-22 10:28:01','YYYY-mm-dd HH24:mi:ss'));
insert into order_tab(id,User_Id,Type,Creat_Time) values('10','1','3',to_date('2017-09-3 10:28:01','YYYY-mm-dd HH24:mi:ss'));

commit;

insert into order_detail_tab(id,Order_Id,Sp_Id,Count) values('16','1','1',2);
insert into order_detail_tab(id,Order_Id,Sp_Id,Count) values('17','1','7',10);

insert into order_detail_tab(id,Order_Id,Sp_Id,Count) values('1','2','5',1);
insert into order_detail_tab(id,Order_Id,Sp_Id,Count) values('2','2','6',1);
insert into order_detail_tab(id,Order_Id,Sp_Id,Count) values('3','2','2',1);
insert into order_detail_tab(id,Order_Id,Sp_Id,Count) values('4','2','4',1);

insert into order_detail_tab(id,Order_Id,Sp_Id,Count) values('5','3','1',1);

insert into order_detail_tab(id,Order_Id,Sp_Id,Count) values('6','4','2',1);
insert into order_detail_tab(id,Order_Id,Sp_Id,Count) values('7','4','6',2);
insert into order_detail_tab(id,Order_Id,Sp_Id,Count) values('8','4','5',2);

insert into order_detail_tab(id,Order_Id,Sp_Id,Count) values('9','5','1',1);
insert into order_detail_tab(id,Order_Id,Sp_Id,Count) values('10','5','8',20);

insert into order_detail_tab(id,Order_Id,Sp_Id,Count) values('11','6','7',8);
insert into order_detail_tab(id,Order_Id,Sp_Id,Count) values('12','7','5',1);
insert into order_detail_tab(id,Order_Id,Sp_Id,Count) values('13','8','2',7);
insert into order_detail_tab(id,Order_Id,Sp_Id,Count) values('14','9','6',1);
insert into order_detail_tab(id,Order_Id,Sp_Id,Count) values('15','10','3',1);
commit;
select *from order_detail_tab;
select *from order_tab;
select *from user_tab;
select *from sp_tab;
select *from sp_type;

--查询所有订单及订单金额；
select o.id 订单编号,sum(od.count*sp.spcie)订单金额
from order_tab o,order_detail_tab od,sp_tab sp
where o.id=od.order_id and sp.id=od.sp_id
group by o.id;

--查询用户张飞所有的订单记录；
select *
from user_tab u,order_tab o
where u.name='张飞' and u.id=o.user_id;
--查询商品“飞科吹风机”的总销售数量；
select sp.id ,sp.name, sum(od.count) 总销售
from sp_tab sp,order_detail_tab od
where sp.id=od.sp_id and sp.name='飞科吹风机'
group by sp.id ,sp.name;

--查询2017-05月所销售的商品“TCL冰箱”的销售数量；

select sum(od.count)
from sp_tab sp,order_detail_tab od,order_tab o
where sp.id=od.sp_id and sp.name='TCL冰箱' and to_char(o.creat_time,'yyyy-mm')='2017-09'
group by sp.id;
--查询美的电磁炉的总销售额
select sum(od.count*sp.spcie)
from sp_tab sp,order_detail_tab od,order_tab o
where sp.id=od.id and sp.name='美的电磁炉'and o.id=od.order_id;
select sum(s.Spcie*od.Count)
from sp_tab s,Order_Detail_Tab od
where od.Sp_Id=s.Id and s.Name='美的电磁炉'

--查询所有商品的销售额，并排序显示；
select sp.id,sp.name,sum(od.count*sp.spcie)
from sp_tab sp,order_detail_tab od,order_tab o
where sp.id=od.id and o.id=od.order_id
group by sp.id ,sp.name
order by sp.id asc

--查询每个用户的消费总额，并排序显示
select u.id,u.name,sum(od.count*sp.spcie) 消费总额
from sp_tab sp,order_detail_tab od,order_tab o,user_tab u
where u.id=o.user_id and sp.id=od.id and o.id=od.order_id
group by u.id,u.name
order by u.id asc