oracle学习过程2
来源:互联网 发布:mac virtualbox 剪切板 编辑:程序博客网 时间:2024/06/09 13:53
insert into productinfo
select '1','1',1,1,'1','1','js' from dual union all
select '2','2',2,2,'2','2','bj' from dual union all
select '3','3',3,3,'3','3','sh' from dual union all
select '4','4',4,4,'4','4','gz' ;
select PRODUCTID as 产品编号,PRODUCTNAME,PRODUCTPRICE,QUANTITY,CATEGORY,DESCRIPTION,ORIGIN from productinfo1
select PRODUCTPRICE,productprice || '*' || 1.5 || '=' || productprice * 1.5 as new_productprice from productinfo;
select distinct substr(PRODUCTID,1,6) (as) 产品编号 from productinfo1 order by productid desc nulls last;
update productinfo2
set productname = null
where productid = 1;
select productname , productid from productinfo2 order by productname(产品名称)(1) desc nulls last,2 desc;
select productname from productinfo2 where productid like '12'/productid is null //productid between 1 and 3//productid
in ('1','2','3')//productid > 1 and productid < 3//productid < 1 or productid > 3// not in//productid <> 3//like 's%'//
like 's_'
select productid,avg(productprice) from productinfo2 group by productid
merge into productinfo1
using productinfo
on (productinfo1.productid=productinfo.productid)
when matchded then update productinfo1
set productinfo1.productname=productinfo.productname,productinfo1.PRODUCTPRICE=productinfo.productprice
when not matched then insert into productinfo1
select * from productinfo
===
===
merge into productinfo1
using productinfo
on(productinfo1.productname=productinfo.productname)--不能是改更新的column
when matched then update
set productinfo1.productid=productinfo.productid
when not matched then insert
values(productinfo.PRODUCTID,productinfo.PRODUCTNAME,productinfo.PRODUCTPRICE,productinfo.QUANTITY,productinfo.CATEGORY,productinfo.DESCRIPTION,productinfo.ORIGIN)
select * from productinfo where productid = (select categoryid from categoryinfo where categoryname='4')//any (some)//
ALL
insert into categoryinfo(categoryid.categoryname)
select category,category from productinfo
select * from productinfo2 where productid < any (select categoryid from categoryinfo where categoryname='2')
连接查询
select p.productname, p.productprice,c.categoryname
from productinfo p inner join categoryinfo c
on p.category=c.categoryid;
select productname, productprice,categoryname
from productinfo inner join categoryinfo
on category=categoryid;
===where 和 on left(right full) join/inner join
select productname, productprice, categoryname
from productinfo p
left join categoryinfo c on p.category = c.categoryid and c.categoryid = 1
select productname, productprice, categoryname
from productinfo p
left join categoryinfo c on p.category = c.categoryid where c.categoryid = 1
==
insert into usercalc select min(year(birthday)),min(month(birthday)),min(day(birthday)),min(salary),min(age) from user
union all select max(year(birthday)),max(month(birthday)),max(day(birthday)),max(salary),max(age) from user
union all select avg(year(birthday)),avg(month(birthday)),avg(day(birthday)),avg(salary),avg(age) from user;
delete from usercalc;
update usercalc set birthyear = (select min(year(birthday)) from user), birthmonth= (select min(year(birthmonth)) from user) where method = "min";
//这个在Java程序中很有用 使用jdbc连接时 对一个表的查询要展现多行多列时 就可以如下使用 多行 uniom 多列增加select的字段
select "min" method,min(year(birthday)) birthyear,min(month(birthday)) birthmonth,min(day(birthday)) birthday,min(salary) salary,min(age) age from user
union all select "max" , max(year(birthday)),max(month(birthday)),max(day(birthday)),max(salary),max(age) from user
union all select "avg" ,avg(year(birthday)),avg(month(birthday)),avg(day(birthday)),avg(salary),avg(age) from user;
select '1','1',1,1,'1','1','js' from dual union all
select '2','2',2,2,'2','2','bj' from dual union all
select '3','3',3,3,'3','3','sh' from dual union all
select '4','4',4,4,'4','4','gz' ;
select PRODUCTID as 产品编号,PRODUCTNAME,PRODUCTPRICE,QUANTITY,CATEGORY,DESCRIPTION,ORIGIN from productinfo1
select PRODUCTPRICE,productprice || '*' || 1.5 || '=' || productprice * 1.5 as new_productprice from productinfo;
select distinct substr(PRODUCTID,1,6) (as) 产品编号 from productinfo1 order by productid desc nulls last;
update productinfo2
set productname = null
where productid = 1;
select productname , productid from productinfo2 order by productname(产品名称)(1) desc nulls last,2 desc;
select productname from productinfo2 where productid like '12'/productid is null //productid between 1 and 3//productid
in ('1','2','3')//productid > 1 and productid < 3//productid < 1 or productid > 3// not in//productid <> 3//like 's%'//
like 's_'
select productid,avg(productprice) from productinfo2 group by productid
merge into productinfo1
using productinfo
on (productinfo1.productid=productinfo.productid)
when matchded then update productinfo1
set productinfo1.productname=productinfo.productname,productinfo1.PRODUCTPRICE=productinfo.productprice
when not matched then insert into productinfo1
select * from productinfo
===
===
merge into productinfo1
using productinfo
on(productinfo1.productname=productinfo.productname)--不能是改更新的column
when matched then update
set productinfo1.productid=productinfo.productid
when not matched then insert
values(productinfo.PRODUCTID,productinfo.PRODUCTNAME,productinfo.PRODUCTPRICE,productinfo.QUANTITY,productinfo.CATEGORY,productinfo.DESCRIPTION,productinfo.ORIGIN)
select * from productinfo where productid = (select categoryid from categoryinfo where categoryname='4')//any (some)//
ALL
insert into categoryinfo(categoryid.categoryname)
select category,category from productinfo
select * from productinfo2 where productid < any (select categoryid from categoryinfo where categoryname='2')
连接查询
select p.productname, p.productprice,c.categoryname
from productinfo p inner join categoryinfo c
on p.category=c.categoryid;
select productname, productprice,categoryname
from productinfo inner join categoryinfo
on category=categoryid;
===where 和 on left(right full) join/inner join
select productname, productprice, categoryname
from productinfo p
left join categoryinfo c on p.category = c.categoryid and c.categoryid = 1
select productname, productprice, categoryname
from productinfo p
left join categoryinfo c on p.category = c.categoryid where c.categoryid = 1
==
insert into usercalc select min(year(birthday)),min(month(birthday)),min(day(birthday)),min(salary),min(age) from user
union all select max(year(birthday)),max(month(birthday)),max(day(birthday)),max(salary),max(age) from user
union all select avg(year(birthday)),avg(month(birthday)),avg(day(birthday)),avg(salary),avg(age) from user;
delete from usercalc;
update usercalc set birthyear = (select min(year(birthday)) from user), birthmonth= (select min(year(birthmonth)) from user) where method = "min";
//这个在Java程序中很有用 使用jdbc连接时 对一个表的查询要展现多行多列时 就可以如下使用 多行 uniom 多列增加select的字段
select "min" method,min(year(birthday)) birthyear,min(month(birthday)) birthmonth,min(day(birthday)) birthday,min(salary) salary,min(age) age from user
union all select "max" , max(year(birthday)),max(month(birthday)),max(day(birthday)),max(salary),max(age) from user
union all select "avg" ,avg(year(birthday)),avg(month(birthday)),avg(day(birthday)),avg(salary),avg(age) from user;
0 0
- oracle学习过程2
- oracle存储过程学习过程
- oracle学习之存储过程(2)注意
- Oracle存储过程学习
- Oracle学习过程
- Oracle存储过程学习
- Oracle存储过程学习
- oracle 存储过程学习
- Oracle存储过程学习
- Oracle存储过程学习
- Oracle存储过程学习
- Oracle存储过程学习
- Oracle存储过程学习
- Oracle存储过程学习
- Oracle存储过程学习
- Oracle 存储过程学习
- Oracle存储过程学习
- Oracle存储过程学习
- ffmpeg 在ubuntu下的编译安装
- poj-2393
- android开发之简单图片浏览器 读取sdcard图片+形成缩略图+Gallery
- R:RStudio和RStudio Server
- HTML 10-30
- oracle学习过程2
- Mybatis源代码分析之别名
- vc6.0添加ws2_32.lib文件的问题
- 常用的十七大学术搜索引擎
- operator new与new operator
- double x = 10 ,y = 0;y = x % 2; 这个表达式正确吗?
- 的发生过的非官方的的非官方的说法
- ajax重构代码
- 图文解说:Nginx+tomcat配置集群负载均衡