练习.txt

orale培训教材包括了所有的sql说明和实例

--使用instr,substr,处理字符串数据

------------------------------------shangpin表增加一个caigou_date字段用于存储采购日期-------
drop table shangpin;
--建立商品表
create table shangpin
(name varchar(20), --商品名
 price number(8,2),--价格
 shuliang number(3),--库存数量
 caigou_date date) --采购日期;

--插入数据,商品名为 厂家,产品
insert into shangpin values ('TCL,电视',100,3,'29-8月 -02');
insert into shangpin values ('三元,豆腐',1,30,'28-8月 -02');
insert into shangpin values ('tcl,电脑',1000,3,'30-8月 -02');
insert into shangpin values ('Tcl,电话',50,3,'29-8月 -02');
insert into shangpin values ('Abb,开关',1,30,'29-7月 -02');
insert into shangpin values ('Abb,电脑',100,30,'29-8月 -02');


select  name,instr(name,',',1) from shangpin;

select substr(name,1,instr(name,',',1)-1) 
from shangpin;



--将厂家与产品分别显示
select substr(name,1,instr(name,',',1)-1),
substr(name,instr(name,',',1)+1,99) ,price from shangpin
order by name;

select substr(name,1,instr(name,',',1)-1),
substr(name,instr(name,',',1)+1,length(name)) ,price from shangpin
order by name;

--将所有含字母的厂家名大写
select upper(substr(name,1,instr(name,',',1)-1)),
substr(name,instr(name,',',1)+1,length(name)) ,price from shangpin
order by name;

--使用distinct get 单一name
select distinct 
upper(substr(name,1,instr(name,',',1)-1))
from shangpin;


--建立产品信息表，包括：生产厂商,商品名,单价
用shangpin表的为产品信息表添加数据




create table product (company varchar2(20), product varchar2(20),price number(6,2));

insert into product select upper(substr(name,1,instr(name,',',1)-1)),
substr(name,instr(name,',',1)+1,length(name)) ,price from shangpin
order by name;