Oracle学习笔记
学习网站:http://school.itzcn.com/special-spid-35.html
1、更新语句:
update test
set(max_price,proxy_price)=(select max_price,proxy_price from test where id=35668)
where id=35674;
commit;
2、MERGE语句:可以方便地把多个操作结合成一个,可避免使用多个insert,update以及delete语句。
eg.
create table person1(
pid number(4),
page number(3)
);
insert into person1 values(1,20);
insert into person1 values(2,21);
insert into person1 values(3,22);
commit;
create table person2(
pid number(4),
page number(3)
);
insert into person2 values(1,100);
insert into person2 values(4,100);
insert into person2 values(5,100);
select * from person1;
select * from person2;
merge into person1 p1
using person2 p2
on(p1.pid=p2.pid)
when matched then
update set p1.page=p2.page
when not matched then
insert(pid,page) values(p2.pid,p2.page);
--结果 3 行已合并。
3、
4、大对象
》LONG,LONG RAW数据类型:
LONG数据类型可以存储2GB以下的字符数据
LONG RAW数据类型可以存储2GB以下的二进制数据
RAW数据类型可以存储4KB的二进制数据。
》大对象四种类型:
CLOB 字符LOB类型,用于存储字符数据
NCLOB 国家语言字符集LOB类型,用于存储多字节字符数据,一般用于非英文字符
BLOB 二进制LOB类型,用于存储二进制数据
BFILE 二进制FILE类型,用于存储文件指针。
》LOB类型与较早类型相比优点:
1》LOB列最大可以存储128TB的数据,远远超过LONG和LONG RAW列存储的数据量2GB。
2》一个表可以有多个LOB列,但是一个表只能有一个LONG或LONG RAW 列。
3》LOG数据可以随机访问,LONG和LONG RAW数据只可以顺序访问。
CLOB:
create table clob_table(
id number primary key,
clob_column clob not null
);
--使用to_clob()
insert into clob_table(id,clob_column)
values(1,to_clob('这里是clob类型数据'));
select * from clob_table;
BLOB:
Bcreate table blob_table(
id number primary key,
blob_column blob not null
);
--to_blob()
insert into blob_table(id,blob_column)
values(1,to_blob('101010111110'));
commit;
select * from blob_table;
BFILE:
操作大对象APPEND()方法添加字符串,如,src_clob添加到dest_clob后面:
大对象COMPARE()比较,如果两个LOB相同则返回0;如果不相同则返回1或-1;如果参数值设置有误或不合理则返回空值。
大对象REASE(lob,amount,offset)方法用于删除一个LOB中的数据,删除方式是从指定的偏移量开始,删除指定数量的字符或字节。
大对象FILEGETNAME(bfile,directory,filename)方法用于获取BFILE所指向的外部文件的目录和文件名。
大对象LOADCLOBFROMFILE()方法,用于将BFILE所指向的外部文件中的数据加载到CLOB/NCLOB中。加载方式是从指定的偏移量开始,加载指定数量的字节。
5、HAVING子句通常与GROUP BY子句一起使用,在完成对分组结果的统计后,可以使用HAVING子句对分组的结果进行进一步的筛选。一个HAVING子句最多可以包含40个表达式。
6、USING使用,如果在连接查询时使用了两个表中相同的多个列,那么就可以在USING子句中指定多个列名,形式如下:
select ... from table1 inner join table2
using(column1,column2);
上述语句相当于下面的语句:
select ... from table1 inner join table2
on table1.column1=table2.column2
and table1.column2=table2.column2;
7、Oracle分页sql示例:
SELECT *
FROM
(SELECT t2.*,
rownum rowno
FROM
(SELECT t1.*
FROM
(SELECT D.ID,
D.USER_ID,
D.USER_NAME,
D.CREATED_AT,
D.REMARKS
FROM USER_TEST D
WHERE 1 =1
AND D.STATUS = '2'
ORDER BY D.CREATED_AT DESC
) t1
) t2
WHERE rownum <=25
) table_alias
WHERE table_alias.rowno >0;
MySQL分页SQL示例:
select * from table LIMIT 5,10;//返回6-15行
SqlServer分页SQL示例:
select TOP 10 * from test order by id asc;
8、Oracle分组后,取每组第一条数据。
row_number() over(partition by X)
--导出所有psam冻结的数据,只取第一条。(分组后取每组第一条数据)
select *
from (select t.*,pb.*,row_number() over(partition by t.psamId order by t.psamId desc, t.blockingDate desc,t.blockingTime desc ) cn from PayTerminalBlockingTest t join PayTerminalTest p on(t.psamId=p.psamId) join PayBranchTest pb on(p.branchId=pb.branchId) where t.blockingFlag='1') where cn = 1