create table product_tab(
pid varchar(20) not null primary key,
name varchar(20),
reice number(5,1),
type varchar(20)
);
insert into product_tab(pid,name,reice,type) values('1','冰箱','1500.0','家电');
insert into product_tab(pid,name,reice,type) values('2','苹果','3.0','水果');
insert into product_tab(pid,name,reice,type) values('3','李子','1500.0','水果');
insert into product_tab(pid,name,reice,type) values('4','电视','7.0','家电');
insert into product_tab(pid,name,reice,type) values('5','台灯','6000.0','家电');
create table customer_tab(
acid varchar(20) not null primary key,
acname varchar(20),
acadress varchar2(20)
);
insert into customer_tab( acid,acname,acadress) values('1','张三','北京');
insert into customer_tab( acid,acname,acadress) values('2','李四','上海');
insert into customer_tab( acid,acname,acadress) values('3','王五','天津');
insert into customer_tab( acid,acname,acadress) values('4','张平','上海');
create table order_tab(
id varchar(20) not null primary key,
acid varchar(20),
foreign key(acid) references customer_tab(acid),
pid varchar2(20),
foreign key( pid) references product_tab(pid),
amount number(3)
);
insert into order_tab(id,acid,pid,amount) values('1','1','1','12');
insert into order_tab(id,acid,pid,amount) values('2','3','1','1');
insert into order_tab(id,acid,pid,amount) values('3','2','2','3');
insert into order_tab(id,acid,pid,amount) values('4','1','3','1');
insert into order_tab(id,acid,pid,amount) values('5','1','2','4');
insert into order_tab(id,acid,pid,amount) values('6','3','5','4');
commit
------1.查询所有商品的分类及每个分类下商品数量
select p.type,count(p.type)
from product_tab p
group by p.type;
-----2.查询所有商品分类,及每个分类中商品名称,所有商品名称显示有同一列中
select type,WMSYS.WM_CONCAT(name) name
from product_tab
group by type;
-----3.查询所有的商品名称及价格;
select name,reice
from product_tab ;
----4.查询商品分类中商品数量2 个以上的,展示商品的分类名称及商品数量
select type,count(0)
from product_tab
group by type
having count(0)>2;
----5.查询‘张三’的所有消费记录;
select o.*,p.name
from customer_tab c,order_tab o,product_tab p
where c.acname='张三'and o.acid=c.acid and o.pid=p.pid
----6.查询来自‘上海’的用户的消费记录数;
select count(o.id)
from customer_tab c,order_tab o
where c.acadress='上海'and o.acid(+)=c.acid
----7查询来自‘上海’的用户的消费总金额;
select sum(o.amount*p.reice)
from customer_tab c,order_tab o,product_tab p
where c.acadress='上海'and o.acid=c.acid and o.pid=p.pid
----8查询所有用户的消费金额;
select c.acid,c.acname,sum(o.amount*p.reice)
from customer_tab c,order_tab o,product_tab p
where o.acid=c.acid and o.pid=p.pid
group by c.acid,c.acname
order by c.acid asc
-------用exists的嵌套查询
select o.*
from order_tab o
where o.pid in(select pid from product_tab)
select o.*
from order_tab o
where exists(select pid from product_tab)
-----内连接(inner join)
select * from order_tab o
inner join product_tab p on o.pid=p.pid
----左连接(左外连接)
select * from order_tab o
left join product_tab p on o.pid=p.pid
----7.1.2修改数据库表
---添加字段
alter table product_tab
add(
testTimes number(3)
);
select * from product_tab
----删除字段
alter table product_tab
drop ( testimes);
----修改字段=====被修改的字段必须清空
alter table product_tab
modify(
testx varchar2(15) default '010-110'
);
---禁用----
alter table product_tab
drop unused(
testTimes
);
alter table product_tab
drop unused columns checkpoint 1000;
-------创建视图
create view myView
as
select pid,name from product_tab
where type='家电'
------利用视图来操作基表的数据????==提交后再检索视图出现
insert into myView(pid,name) values('8','橘子')
select * from myView
select * from product_tab
---7.2.3修改视图
create or replace view myView
as
select*from customer_tab;
----创建复杂视图
create or replace view myView(ID,名称,类型)as
select
from
where o
-----7.2.4删除视图r
create sequence seqi
increment by 2
start with 1
maxvalue 1000
minvalue 1
cycle
cache 10;
select * from seqi
drop sequence seqi
-----9.3创建触发器
create or replace trigger mytri
before insert on mytri
for each row
exception
when others then
raise;
end;
----9.3.1insert触发器
create or replace trigger