查詢結果排序
-- 排序：升序 asc、降序 desc，含有null值的排序
-- 默認按升序排序，在order by 字段后面指定desc，按降序。但是含有null值的會排在最前面?？梢约由?nulls last，把null值排在最后面
SELECT /*+parallel(t,4)*/ t.* from tablePost t
where t.content like'%\%%' escape? '\' and? t.post_type = 2
order by t.title desc;
-- 默認按升序排序，在order by 字段后面指定desc，按降序。但是含有null值的會排在最前面?？梢约由?nulls last，把null值排在最后面
SELECT? /*+parallel(t,4)*/? t.*? from? ?tablePost t
where? ?t.content? ?like'%\%%'? ?escape? '\'? and t.post_type = 2
order by t.title desc nulls last;
臨時表的概念
-- 創(chuàng)建臨時表
create? ?global? temporary? ?tabletemp01
(tid number,tname? varchar(20));
insert? into? temp01
values(1,'jack');
-- 沒有commit，可以查到表里的數(shù)據(jù)
select? *? ?from? ?temp01;
commit;
-- commit后，表里的數(shù)據(jù)，不存在了
select? *? ?from????temp01;
commit后，表里的記錄沒有了
-- sql函數(shù)：單行函數(shù)、多行函數(shù)
-- 字符函數(shù)
-- lower 、upper、initcap首字母大寫
select????lower('Hello World')小寫,????upper('Hello world')大寫,????initcap('Hello world')首字母大寫? from dual;
-- 字符控制函數(shù)
-- concat, substr 截取字符, length/lengthb字符長度 ：中文字符算2個字節(jié), instr, lpad, rpad, ltrim
-- substr 截取字符
select? substr('Hello World',2) 子串? from? dual
unionall
select? substr('Hello World',2,4) 子串? from? dual;
-- length/lengthb字符長度 ：中文字符算2個字節(jié)
select? length('Hello World'),? lengthb('Hello World')? from? dual
union? all
select? length('上海'),? lengthb('上海')? from? dual;
-- instr：查找字符串，找到返回所在位置，否則返回0
select? instr('Hello World','ll')? from? dual
union? all
select? instr('Hello World','kk')? from? dual;
-- lpad：左填充，rpad：右填充
select? lpad('abc',10,'*')? from? dual
union? all
select? rpad('abc',10,'*')? from? dual;
-- trim:去掉字符串前后空格，或指定字符
select? trim(' h ello world? ')? from? dual
union? all
select? trim('h'from'hello world he ')? from? dual;
-- replace:替換字符
selectre? place(' hello world','l','*')? from? dual;
-- 數(shù)字函數(shù)
-- round:四舍五入， trunc：截斷， mod: 求余
select? round(3.1415,3),? trunc(3.1415,3),? mod(100,30)? from? dual;
-- 取整數(shù)（向下取整 floor, trunc；向上取整 ceil）
select? floor(3.6),? trunc(3.6),? ceil(3.3)? from? dual;
-- 數(shù)字格式化
-- 小數(shù)后第3、4位不足補0
select? to_char(12345.123,'9999999.9900')? from? dual
union? all
select? to_char(0.123,'9999.9900')? from? dual
union? all
select? to_char(0.123,'9990.9900')? from? dual;
