oracle 11g 學習筆記10_29(2)

sql函數的使用一、字元函數

字元函數是oracle中最常用的函數，
字元函數：
.lower(char):將字串轉化為小寫格式
.upper(char):將字串轉化為大寫的格式
.length(char):返回字串的長度。
.substr(char,m,n):取字串的子串
其中，m代表從第m個開始取，n代表取的子串長度

將所有員工的名字按小寫方式顯示
將所有員工的名字按大寫的方式顯示
顯示正好為5個字元的員工的姓名
顯示所有員工姓名的前三個字元
以首字母大寫的方式顯示所有員工的姓名

//1、完成首字母大寫select upper(substr(ename,1,1)) from emp;//2、完成後面字母小寫select upper(substr(ename,1,;length(ename)-1)) from emp;//3、合并select upper(substr(ename,1,1)) || lower(substr(ename,2,length(ename)-1)) from emp;

以首字母小寫方式顯示所有員工的姓名

select lower(substr(ename,1,1)) || upper(substr(ename,2,length(ename)-1)) from emp;

.replace(char1,search_string,replace_string)
其中，search_sting 是要尋找的原字串，replace_string 是替換的字串。
.instr(char1,char2,[,n[,m]])取子串在字串的位置
顯示所有員工的姓名，用"a"替換所有的"A"

select replace(ename,'A','a') from emp;

二、數學函數

數學函數的輸入參數和傳回值的資料類型都是數字類型的。數學函數包括
cos,cosh,exp,ln,log,sin,sinh,sqrt,tan,tanh,acos,asin,atan,round,
我們最常用的：
.round(n,[m])
該函數用來執行四捨五入，如果省掉m，則四捨五入到正數；如果m是正數，則四捨五入到小數點的m位後，如果m是負數，則四捨五入到小數點的m位前
.trunc(n,[m])
該函數用於截取數字，如果省掉m，就截去小數部分，如果m是正數就截去到小數點的m位後，如果m是負數，則截去到小數點的前m位
.mod(m,n) m對n模數
.floor(n) 返回小於或等於n的最大整數
.ceil(n)  返回大於或等於n的最大整數
三、日期函數

日期函數用於處理date類型的shuju預設情況下日期格式是dd-mon-yy 即12-7月-78
（1）sysdate:該函數返回系統時間
（2）add.months(d,n)
（3）last_day(d):返回指定日期所在月份的最後一天
尋找已經入職8個多月的員工

select * from emp where sysdate > add_months(hiredate,8);

其中，add_months(hiredate,8)表示在僱傭日期加上8個月。要是sysdate大於這個已經加了8個月時間的，那麼該員工就符合條件了。因為，如果是入職2個月的話，加8個月肯定是大於系統時間的。
顯示滿10年服務年限的員工的姓名和受雇日期

select * from emp where sysdate>= add_months(hiredate,12*10);

對於每個員工，顯示其加入公司的天數
如果是這樣：

select sysdate-hiredate "入職天數",ename from emp;

      入職天數 ENAME
---------- ----------
8732.63312 pangzi
8943.63312 小紅
11639.6331 SMITH
11574.6331 ALLEN
11572.6331 WARD
那麼就會出現天數有小數點，那是因為它把不夠一天的小時也算進去了。
如果不想出現小數點，那麼我們可以這樣：

select floor(sysdate-hiredate)||'天' "入職天數",ename from emp;

入職天數                                   ENAME
------------------------------------------ ----------
8732天                                     pangzi
8943天                                     小紅
11639天                                    SMITH
11574天                                    ALLEN

11572天                                    WARD

找出各月倒數第三天受雇的所有員工

select hiredate, ename from emp where last_day(hiredate)-2 = hiredate;

HIREDATE    ENAME
----------- ----------
1981/9/28   MARTIN
其中要注意的是last_day(hiredate)-2是減去 2 不是 3； 
四、轉換函式

轉換函式用於將資料類型從一種轉為另一種，在某些情況下，oracle server 允許值的資料類型和實際的不一樣，這是oracle server會隱含的轉化資料類型
比如：
create table t1(id int);
insert into t1 values('10') -->這樣oracle 會自動地將'10' -->10
create table t2 (id varchar2(10));
insert into t2 values(1); -->這樣oracle 就會自動地將 1 -->'1'
儘管oracle可以進行隱含的資料類型的轉換，但是它並不適應所有的情況。為了提高可靠性，應該使用轉換函式進行轉換。

*to_char
.顯示日期的 時/分/秒
.to_char(time,'yyyy-mm-dd hh24:mi:ss')
yy:兩位元字的年份 2012-->12
yyyy: 四位元字的年份 
mm: 兩位元字的月份 8月-->08
dd: 兩位元字的天 29號 -->29
hh24: 24小時制
hh12: 12小時
mi,ss -->顯示分鐘\秒

select ename, empno, job, to_char(hiredate,'yyyy-mm-dd hh24:mi:ss'), sal from emp;

ENAME      EMPNO JOB       TO_CHAR(HIREDATE,'YYYY-MM-DDHH       SAL
---------- ----- --------- ------------------------------ ---------
pangzi      9999 CLERK     1988-12-02 00:00:00              2456.34
小紅        9998 MANAGER   1988-05-05 00:00:00                28.90
shouzi      9997 manager   2012-10-29 16:51:38              3800.50
最後那個有時分秒是我最新插入進去的，插入時hiredate那用了sysdate。要是插入資料時沒有用時分秒來表示，那麼顯示的時候時分秒那裡都是為零。
.顯示薪水的指定的貨幣符號
9：顯示數字，並忽略前面0
0：顯示數字，如位元不足，則用0補齊
.: 在指定位置顯示小數點
,：在指定位置顯示逗號
$：在數字前面加貨幣符號
L：在數字前加本地貨幣符號
C：在數字前面加國際貨幣符號

select ename, to_char(sal,'L9999.99') from emp;

ENAME      TO_CHAR(SAL,'L9999.99')
---------- -----------------------
pangzi              ￥2456.34
小紅                  ￥28.90
shouzi              ￥3800.50
SMITH                ￥800.00

select ename, to_char(sal,'L9,999.99') from emp;

ENAME      TO_CHAR(SAL,'L9,999.99')
---------- ------------------------
pangzi              ￥2,456.34
小紅                   ￥28.90
shouzi              ￥3,800.50
SMITH                 ￥800.00

顯示1980年入職的所有員工

select * from emp where to_char(hiredate,'yyyy')='1980';EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980/12/17     800.00               20

顯示所有12月份入職的員工。
select * from emp where to_char(hiredate,'mm')=12;
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 9999 pangzi     CLERK      7782 1988/12/2     2456.34     55.66     10
 7369 SMITH      CLERK      7902 1980/12/17     800.00               20
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20

oracle的('yyyy-mm-dd hh24:mi:ss')這個可以用得很靈活，想取那個時間段就取那個時間段。
五、系統函數

terminal：當前會話客戶所對應的終端標識符
lanuage：語音
db.name：當前資料庫名稱
nls_date_format：當前會話客戶所對應的日期格式
session_user：當前會話客戶所對應的資料庫使用者名稱
current_schema: 當前會話客戶所對應的預設方案名
host：返回資料庫所在主機的名稱

select sys_context('userenv','nls_date_format') from dual;

SYS_CONTEXT('USERENV','NLS_DAT
--------------------------------------------------------------------------------
DD-MON-RR

select sys_context('userenv','db_name') from dual;

SYS_CONTEXT('USERENV','DB_NAME
--------------------------------------------------------------------------------
orcl

select sys_context('userenv','language') from dual;

SYS_CONTEXT('USERENV','LANGUAG
--------------------------------------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK

*使用者和方案的關係：

一旦使用者建立之後，oracle就會自動地建立一個方案，oracle是以方案的方式來管理資料庫對象的，方案的名字跟使用者名稱一模一樣，方案裡面有很多的資料對象，比如有表，視圖，觸發器，預存程序等等。

要是該列是中文列，那麼列名稱要用雙引號括起來。要是是改表的謀列中的中文資料，那麼只要單引號括起來。