Oracle資料庫系列之基礎篇2

1、單行函式
– 字符函式，接收字符輸入回傳字符或者數值，dual是偽表

desc dual;
select * from dual;

2、字串的連接可以使用concat，也可以用 || ，建議使用 ||

select concat('hello','world') 連接 from dual;
select 'hello' || ',' || 'world' 連接 from dual;

3、字串截取，使用substr
– 第一個引數是源字串，第二個引數是開始索引，第三個引數長度，開始的索引使用1和0效果相同

select substr('hello',0,3) from dual;
select substr('hello',1,3) from dual;
select substr('hello',2,3) from dual;

4、獲取字串的長度，使用length

select length('hello') from dual;

5、字串替換，使用replace
– 第一個引數是源字串，第二個引數是被替換的字串，第三個引數是替換的字串

select replace('hello','l','p') from dual;

數值函式
6、ROUND 四舍五入

select round(3.1415,2) from dual;
select round(3.1440,2) from dual;
select round(3.1446,2) from dual;
select round(3.1449,2) from dual;
select round(3.1450,2) from dual;
select round(3.1466,2) from dual;
select round(45.923,2) from dual;
select round(45.926,2) from dual;

7、TRUNC 截斷

select trunc(3.1415826,4) from dual;
select trunc(34.923,2) from dual;

8、MOD 求余

select mod(167,3) from dual;
select mod(167,30) from dual;

/*
日期函式
日期型資料實際含有兩個值：日期和時間，默認日期格式：DD-MM–YY
日期的數學運算，在日期上加減一個數字結果仍為日期，兩日期相級訓傳日期之間相差天數
可以用數字除24
*/

9、查詢雇員的進入公司的周數

select ename,round((sysdate - hiredate)/7) from emp;

10、獲得兩個時間段中的月數，MONTHS_BETWEEN()

select ename,round(months_between(sysdate,hiredate)) from emp;

11、獲得幾個月后的日期，ADD_MONTHS()
– 求出兩個月后的日期

select add_months(sysdate,2) from dual;

/*
轉換函式
TO_CHAR函式對日期的轉換
TO_CHAR(date, ‘format_model’)
*/

/*
日期的格式
YYYY 2020
Year twenty twenty
MM 10
MONTH 10月
DY 星期二
DAY 星期二
DD 06
*/

/*
TO_CHAR函式對數字的轉換
TO_CHAR(number, ‘format_model’)
數字轉換的格式
9 數字
0 零
$ 美元符
L 本地貨幣符號
. 小數點
, 千位符
*/

/*
TO_NUMBER函式將字符轉換成數字
TO_NUMBER(char[,‘format_model’])
TO_DATE函式將字符轉換成日期
TO_DATE(char[,‘format_model’])
*/

/*
查詢所有的雇員將年月日分開，此時可以使用TO_CHAR函式來拆分，拆分時需要使用通配符
年：y，年是四位，使用yyyy
月：m，月是兩位，使用mm
日：d，日是兩位，使用dd
*/

select empno,ename,to_char(hiredate,'yyyy-mm-dd') from emp;

– 10以下的月前面被補零，可以使用fm去掉前導零

select empno,ename,to_char(hiredate,'fmyyyy-mm-dd') from emp;

12、TO_NUMBER數值轉換函式

select to_number('10') + to_number('20') from dual;
select to_char(2345.67,'$9999.9') from dual;
select to_char(2345.67,'L9999.9') from dual;
select to_char(2345.67,'$9999.0') from dual;

select to_char(2342345.67,'$9,999,999.999') from dual; 
--  $2,342,345.670

select to_number('$2,342,345.670','$9999999.999') from dual; 
-- 2342345.67

select to_number('a','xx') from dual; 
-- 10

select to_number('d','xx') from dual; 
-- 13

select to_number('1a','xxx') from dual; 
-- 26

select to_number('00012345') from dual;
select to_number('34567.89') from dual;

13、TO_DATE日期轉換函式

select to_date('2020-10-06','yyyy-mm-dd') from dual;
select to_date('2020-10-06','yyyy/mm/dd') from dual;
select to_date('2020/10/06','yyyy-mm-dd') from dual;
select to_date('2020-10-06','yy-mm-dd') from dual;
select to_date('06-10-2020','dd-mm-yyyy') from dual;

/*
通用函式：適用于任何資料型別，同時也適用于空值
常用通用函式 NVL(expr1,expr2) NVL2(expr1,expr2) NULLIF(expr1,expr2) COALESCE(expr1,expr2)
空值處理 NVL
*/

14、查詢所有雇員的年薪

select ename 雇員名,sal*12 + comm 年薪 from emp;

15、發現很多員工年薪是null，這是因為null和任何數值計算都是null，下面用nvl處理

select ename 雇員名, nvl(comm,0) 獎金, sal*12 + nvl(comm,0) 年薪 from emp;

/*
條件運算式
CASE運算式，SQL99語法，類似Basic，比較繁瑣

CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END

/*
DECODE函式，Oracle自身語法，類似Java，比較簡潔

DECODE(col|expression, search1, result1
[, search2, result2,…,]
[,default])

16、條件運算式示例：根據10部門員工工資，顯示稅率

select ename,sal,
       decode(trunc(sal/2000,0),
                        0,0.00,
                        1,0.09,
                        2,0.20,
                        3,0.30,
                        4,0.40,
                        5,0.42,
                        6,0.44,
                          0.45) tax_rate
from emp
where deptno = 10;

/*
ENAME SAL TAX_RATE

---

CLARK 2450.00 0.09
KING 5000.00 0.2
MILLER 1300.00 0
*/

/*
多行函式，分組函式，組函式
組函式會忽略空值，NVL函式使分組函式無法忽略空值
常用多行函式
AVG
COUNT
MAX
MIN
SUM
*/

17、統計記錄數 count()
– 查詢所有員工的記錄數

select count(*) from emp;

– 不建議使用count(*)，可以使用一個具體的列以免影響性能，

select count(ename) 員工數 from emp;

18、最小值查詢 min()
– 查詢員工最低工資

select min(sal) 最低工資 from emp;

19、最大值查詢 max()
– 查詢員工最高工資

select max(sal) 最高工資 from emp;

20、查詢平均值 avg()
– 查詢員工平均工資

select avg(sal) from emp;

21、求和函式 sum()
– 查詢20 部門員工工資總和

select '20部門員工工資總和' || sum(sal) from emp e where e.deptno=20;

/*
分組資料
GROUP BY 子句將表中的資料分成若干組
*/

22、查詢各部門人數

select deptno,count(ename) from emp group by deptno;

23、查詢每個部門的平均工資

select deptno,avg(sal) from emp group by deptno; 

24、查詢部門編號和部門下的人數

select deptno,count(ename) from emp;

– 報錯

/*
select deptno,count(ename) from emp
ORA-00937: 不是單組分組函式
*/

/*
注意：
1、如果使用分組函式，SQL只可以把 GROUP BY 分組條件欄位和分組函式查詢出來，不能有其他欄位；
2、如果使用分組函式，不使用 GROUP BY 只可以查詢出來分組函式的值，
*/

select deptno,ename,count(ename) from emp group by deptno;

/*
select deptno,ename,count(ename) from emp group by deptno
ORA-00979: 不是 GROUP BY 運算式
*/

25、過濾分組資料 group by … having …
– 查詢部門平均工資大于2000的部門

select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

/*
WHERE 和 HAVING 的區別
最大的區別在于：where后面不能有組函式
*/

select deptno,avg(sal) from emp where avg(sal)>8000 group by deptno;

/*
select deptno,avg(sal) from emp where avg(sal)>8000 group by deptno
ORA-00934: 此處不允許使用分組函式
*/

select deptno,avg(sal) from emp where sal>800 group by deptno;

都看到最后了啦，如果覺得寫得好的話吶
記得 一鍵三連 哦！點贊 也行吶！