一、字元串類型及函數
字元類型分 3 種,char(n) 、varchar(n)、varchar2(n) ;
char(n)固定長度字元串,假如長度不足 n,右邊空格補齊;
varchar(n)可變長度字元串,假如長度不足 n,右邊不會補齊;
varchar2(n)可變長度字元串,Oracle 官方推薦使用,向後相容性好
char(n) VS varchar2(n) char(n)查詢效率相對較高,varchar2(n)存儲空間相對較小;
select length(userName) as char length,length(password)as varchar2 length from t_user3;
lpad() 向左補全字元串:select lpad (stuno,6,'0') from t_user3;
rpad() 向右補全字元串:select rpad (stuno,6,'0') from t_user3;
lower() 傳回字元串小寫:select lower (userName)from t_user3;
upper() 傳回字元串大寫:select uppe r(userName)from t_user3;
initcap() 單詞首字元大寫:select initcap(userName)from t_user3;
length() 傳回字元串長度:select length(password)from t_user3;
substr() 截取字元串:select substr(userName,1,2)from t_user3;
instr() 擷取字元串出現的位置:select instr(password,'23',2,2)from t_user3;
ltrim() 删除左側空格:select ltrim(userName) from t_user3;
rtrim() 删除右側空格:select rtrim(userName) from t_user3;
trim() 删除兩側空格:select trim(userName) from t_user3;
concat() 串聯字元串:select concat(userName,password)from t_user3;
reverse() 反轉字元串:select reverse(userName)from t_user3;
二、數值類型及函數
number 是 oracle 中的資料類型;
number(precision,scale);
Precision,scale 均可選;
Precision 代表精度,sacle 代表小數位的位數;
Precision 範圍[1,38]scale 範圍[-84,127]
舉例:
12345.678 Precision 是 8 scale 是 3;
常用方法:
abs() 求絕對值;
select abs(n1)from t_number where id=1; round()
四舍五入:
select round(n1,2)from t_number where id=1;
ceil() 向上取整:
select ceil(n1)from t_number where id=2;
floor 向下取整:
select floor(n1)from t_numbe rwhere id=2;
Mod()取模:
select mod(5,3)fromdual;
Sign()正負性:
select sign(n1) from t_number where id=1;
Sqrt() 求平方根:
select sqrt(9)from dual;
Power()求乘方:
select power(2,3)from dual;
Trunc()截取:
select trunc(123.456,2)from dual;
To_char() 格式化數值:常見的字元比對有 0、9、,、$、FM、L、C
select to_char(123.45,'0000.000') from dual;
select to_char(123.45,'9999.999') from dual;
select to_char(123123,'99,999,999.99') from dual;
select t o_char(123123.3,'FM99,999,999.99') from dual;
select to_char(123123.3,'$99,999,999.99') from dual;
select to_char(123123.3,'L99,999,999.99') from dual;
select to_char(123123.3,'99,999,999.99C') from dual;
三、日期類型及函數
Date 和 timestamp(時間戳)
Date 包含資訊 century(世紀資訊) year 年 month 月 day 日 hour 小時 minute 分鐘 second 秒 Timestamp 一般用于日期時間要求非常精确的情況,精确到毫秒級; insertintot_datevalues(1,sysdate,systimestamp);
下面重點講 date 類型的常用函數:
select sysdate from dual;
select systimestamp from dual;
Add_months 添加月份 select add_months(d1,2)fromt_datewhereid=1;
Last_day 傳回指定日期月份的最後一天 select last_day(d1)from t_date where id=1;
updatet_date setd3=to_date('2016-12-20','YYYY-MM-DD') where id=1;
updatet_date setd3=to_date('2016-12-2018:31:34','YYYY-MM-DD HH24:MI:SS') where id=1;
Months_between 傳回兩個日期的相差月數 select months_between(d1,d3)from t_date where id=1;
next_day 傳回特定日期之後的一周内的日期:select next_day(d1,2)from t_date where id=1;
Trunc 截取日期: select trunc(d1,'YYYY') from t_date where id=1;
select trunc(d1,'MM') fromt_datewhereid=1;
select trunc(d1,'DD') from t_date where id=1;
select trunc(d1,'HH') from t_date where id=1;
select trunc(d1,'MI') from t_date where id=1;
Extract 傳回日期的某個域: select extract(year from sysdate) from dual;
select extract(month from sysdate) from dual;
select extract(day from sysdate) from dual;
select extract(Hour from systimestamp)from dual;
select extract(minute from systimestamp)from dual;
select extract(second from systimestamp)from dual;
To_char 将日期轉換成字元串:
select to_char(d1,'YYYY-MM-DD') from t_date where id=1;
select to_char(d1,'YYYY-MM-DDHH24:MI:SS') from t_date where id=1;
四、其他常用處理函數
常用的聚合函數:
Max 求最大值:select max(sal) from emp ;
Min 求最小值:select min(sal) from emp;
Avg 求平均值:select avg(sal) from emp;
Sum 求和:select sum(sal) from emp;
Count 統計記錄數:select count(ename) from emp;
Nvl 空值處理:select ename,nvl(sal,0)from emp;
rownum Oracle 分頁:select * from(select a.*,rownum rn from(select * from emp)A where rownum<=10) where rn>5;
Oracle 中的運算:
select 2+1from dual;
select 2-1 from dual;
select 2*1 from dual;
select 2/1 from dual;
條件判斷式: Betweenand 範圍查詢:
select * from emp where sal between 900 and 1500;
select * from emp where sal>=900 and sal<=1500;
In 集合範圍:
select ename,hiredate from emp where ename in(select distinct ename from bonus)
Like 模糊查詢:
select * from emp wheree name like '%M%'
select * from emp where ename like 'M%'
select * from emp where ename like'_M%'