SQL 函數(shù)包含多行函數(shù)和單行函數(shù)
單行函數(shù)就是輸入一行輸出也是一行
多行函數(shù) 也稱為分組函數(shù)咬像，將多行數(shù)據(jù)返回一個(gè)值
1. 字符函數(shù)
1. ASCII（x）用于返回字母的ASCII碼
2. length（x） 獲取字符個(gè)數(shù)
3. concat （x） 拼接字符串
4. lower（x） 轉(zhuǎn)換小寫
5. upper（x）轉(zhuǎn)換大寫
6. substr（x，start【，length】）
x： 需要截取的字符串
start ： 開始位置
length ： 截取的長(zhǎng)度
7. instr （x县昂，find_string【肮柜，start】【，occurrense】）查詢find_string 倒彰，然后返回所在的位置审洞，occirrence 第幾次出現(xiàn)的位置
8. replace（x， search_string待讳，replace_string）
x： 用于查找的 字符串
search_string ： 要查詢的字符串
replace_String： 要替換的字符串
9. initcap（x）用于將單詞首字母轉(zhuǎn)換
10. rpad（x芒澜，width【， pad_String】）
11. lpad（）（同10）
2. 數(shù)字函數(shù)
1. abs（x）絕對(duì)值
2. MOD（X） 余數(shù)
3. CEIL（x） 向上取整
4. FLOOR（x） 向下取整
5. ROUND（x） 四舍五入
6. POWER（x,y） x的y次冪
7. SQRT （X） 平方根
8. SIGN（x） 檢測(cè)正負(fù)值
9. COS（x）
10. ACOS（x）反余弦
3. 轉(zhuǎn)換函數(shù)
1. to_char（x） ：將x轉(zhuǎn)換成一個(gè)字符串创淡，格式化字符串
2. to_number（x痴晦， 【x，format】）： 將x轉(zhuǎn)換成一個(gè)數(shù)字
3. case（x as type）： 將x轉(zhuǎn)換成指定type的兼容類型
4. asciiStr （）： 將任意字符串轉(zhuǎn)換成數(shù)據(jù)庫字符集的acsii
5. bin_to_num（x）： 將二進(jìn)制數(shù)轉(zhuǎn)換成十進(jìn)制
6. to_date（）： 將字符串轉(zhuǎn)換成時(shí)間類型
4. 正則表達(dá)式
1. regexp（想琳彩，pattern【誊酌，match_option】）用于在x中查找
5. 聚合函數(shù)
1. avg（）平均數(shù)，非null的平均值
2. count（） 獲取非空值得數(shù)量
3. max（）和min（） 函數(shù)
4. sum（）所有值得和
6. 日期函數(shù)
1. to_char（）和to_date()轉(zhuǎn)化時(shí)間
2. sysdate 獲取系統(tǒng)當(dāng)前時(shí)間
3. months_between（x露乏，y）函數(shù)判斷2個(gè)月分差
4. add_months（x碧浊，y）計(jì)算x+y個(gè)月后的結(jié)果
7. 其他函數(shù)
nvl
nvl2
case
子查詢（嵌套查詢）
單行子查詢： 不向外部的SQL語句返回結(jié)構(gòu)，或者只返回一行
多行子查詢： 向外部的SQL語句返回多行或一行
多列子查詢： 向外部SQL語句返回多列
關(guān)聯(lián)子查詢： 引用外部SQL語句中的一列或多列瘟仿，這種子查詢被稱為關(guān)聯(lián)查詢
嵌套子查詢： 位于子查詢中的查詢箱锐，子查詢最多可以嵌套255層
子查詢可放在另一個(gè)查詢的where子句中.
查詢部門平均工資,低于部門平均工資的最高值
1000
1500
800
select avg(sal) from emp group by deptno
顯示高于自身部門平均工資的員工信息
部門編號(hào),員工姓名,工種,工資
獲取每個(gè)部門的平均工資
當(dāng)員工的部門編號(hào)等于 查詢出來平均工資的部門編號(hào),使用工資和平均工資比較
where中的子查詢
SQL> select empno,ename from emp where deptno = (select deptno from dept where dname = 'SALES');
EMPNO ENAME
---------- ----------
7499 ALLEN
7521 WARD
7654 MARTIN
7698 BLAKE
7844 TURNER
7900 JAMES
已選擇6行。
having中的子查詢
SQL> select deptno,avg(sal) from emp group by deptno
2 having avg(sal) < (select max(avg(sal)) from emp group by deptno);
DEPTNO AVG(SAL)
---------- ----------
800
30 1566.66667
20 2518.75
from中的子查詢
SQL> select d.deptno,ename,job,sal from emp,(select deptno,avg(sal) avgsal from emp group by deptno) d
2 where emp.deptno=d.deptno and emp.sal<d.avgsal;
DEPTNO ENAME JOB SAL
---------- ---------- --------- ----------
10 SMITH CLERK 800
30 WARD SALESMAN 1250
30 MARTIN SALESMAN 1250
30 TURNER SALESMAN 1500
20 ADAMS CLERK 1100
30 JAMES CLERK 950
10 MILLER CLERK 1300
已選擇7行劳较。
子查詢常見的錯(cuò)誤:
1. 單行子查詢返回多個(gè)值
SQL> select deptno,ename from emp where deptno = (select deptno from dept);
select deptno,ename from emp where deptno = (select deptno from dept)
*
第 1 行出現(xiàn)錯(cuò)誤:
ORA-01427: 單行子查詢返回多個(gè)行
2. 子查詢不能包含order by語句
SQL> select * from emp where sal<(select avg(sal) from emp order by avg(sal) desc);
select * from emp where sal<(select avg(sal) from emp order by avg(sal) desc)
*
第 1 行出現(xiàn)錯(cuò)誤:
ORA-00907: 缺失右括號(hào)
多行子查詢:
in : 匹配子查詢結(jié)構(gòu)的任意一個(gè)值即可.
all : 必須符合子查詢結(jié)果的所有值
any : 只要符合子查詢結(jié)構(gòu)的人一個(gè)值即可
CLERK
PRESIDENT
MANAGER
select ename,job deptno from emp where job in (CLERK,PRESIDENT,MANAGER);
查詢部門編號(hào)30的所有員工,工資.
顯示所有員工中工資大于上面工資中任意一個(gè)工資的員工信息
SQL> select ename,sal,deptno from emp where sal < any(select sal from emp where deptno = 30);
ENAME SAL DEPTNO
---------- ---------- ----------
SMITH 800 10
JAMES 950 30
ADAMS 1100 20
WARD 1250 30
MARTIN 1250 30
MILLER 1300 10
TURNER 1500 30
ALLEN 1600 30
CLARK 2450 10
已選擇9行驹止。
SQL> select ename,sal,deptno from emp where sal < any(500,2000);
ENAME SAL DEPTNO
---------- ---------- ----------
SMITH 800 10
ALLEN 1600 30
WARD 1250 30
MARTIN 1250 30
TURNER 1500 30
ADAMS 1100 20
JAMES 950 30
MILLER 1300 10
已選擇8行。
SQL> select ename,sal,deptno from emp where sal < all(select sal from emp where deptno = 30);
ENAME SAL DEPTNO
---------- ---------- ----------
SMITH 800 10
SQL> select * from dept where deptno > all(10,20,30);
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
50 b a
多列子查詢
SQL> select deptno,ename,sal from emp where (deptno,sal)in(select deptno,min(sal) from emp group by deptno);
DEPTNO ENAME SAL
---------- ---------- ----------
30 JAMES 950
20 ADAMS 1100
10 SMITH 800
關(guān)聯(lián)子查詢:
關(guān)聯(lián)子查詢對(duì)外部查詢中每一行都會(huì)運(yùn)行一次,這與非關(guān)聯(lián)子查詢是不同的.
非關(guān)聯(lián)子查詢只在運(yùn)行外部查詢之前執(zhí)行一次,
SQL> select empno,deptno,ename,sal from emp outer
where sal >
(select avg(sal) from emp inner where inner.deptno = outer.deptno)
;
EMPNO DEPTNO ENAME SAL
---------- ---------- ---------- ----------
7499 30 ALLEN 1600
7566 20 JONES 2975
7698 30 BLAKE 2850
7782 10 CLARK 2450
7788 20 SCOTT 3000
7839 10 KING 5000
7902 20 FORD 3000
已選擇7行兴想。
select * from emp e,
(select deptno,avg(sal) a from emp group by deptno) d;
where e.deptno = d.deptno and e.sal<d.a
子查詢 exists和not exists
select ename,job,sal,deptno from emp e
where exists(
select 1 from dept d
where loc = 'NEW YORK' and d.deptno = e.deptno
)
選課表 : 學(xué)號(hào),課程號(hào)
學(xué)生表 : 學(xué)號(hào),姓名
課程表 : 課程號(hào),課程名
查詢選修了java課程的學(xué)生姓名
select 姓名 from 學(xué)生表 where 學(xué)號(hào) in(
select 學(xué)號(hào) from 選課表 where 課程號(hào) =
(select 課程號(hào) from 課程表 where 課程名= 'java')
)
exists的寫法
select 姓名 from 學(xué)生表
where exists
(
select * from 選課表 where 學(xué)生表.學(xué)號(hào) = 選課表.學(xué)號(hào) and 課程號(hào) =(.......)
)
查詢選修了所有課程的學(xué)生:
select 姓名 from 學(xué)生表
where not exists
(
select * from 課程表 where not exists(
select * from 選課表 where 學(xué)生表.學(xué)號(hào) = 選課表.學(xué)號(hào) and 課程表.課程號(hào) = 選課表.課程號(hào)
)
)
嵌套子查詢
SQL> select deptno,avg(sal) from emp
2 group by deptno
3 having avg(sal)>
4 (select max(avg(sal)) from emp
5 where deptno in
6 (select deptno from dept
7 where deptno > 10)
8 group by deptno);
select deptno from dept where deptno > 10
查詢部門編號(hào)大于10的部門編號(hào) 20,30,40,50
select max(avg(sal)) from emp where deptno in (20,30,40,50) group by deptno
計(jì)算部門平均工資的最大值 2200
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2200
顯示大于20,30,40,50部門最高平局工資的部門編號(hào)和平均工資
包含子查詢的修改和刪除
update where
delete where
delete from emp where deptno =(
select deptno from dept where dname = 'SALES'
)
update emp set (sal,comm) = (select sal,comm from emp where ename = 'SMITH')
where job = (select job from emp where ename = 'SMITH')
高級(jí)查詢
集合操作 : 將兩個(gè)或多個(gè)查詢返回的結(jié)構(gòu)結(jié)合起來
union,union all,intersect,minus
并集(獲取的結(jié)果集合并到一個(gè)結(jié)果集中)
SQL> select * from dept where deptno > 10
2 union all
3 select * from dept where deptno >30
4 ;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 b a
40 OPERATIONS BOSTON
50 b a
已選擇6行幢哨。
SQL> select * from dept where deptno > 10
2 union
3 select * from dept where deptno >30
4 ;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 b a
并集:
SQL> select * from dept where deptno > 10
2 intersect
3 select * from dept where deptno >30
4 ;
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
50 b a
差集:
SQL> select * from dept where deptno > 10
2 minus
3 select * from dept where deptno >30;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
30 SALES CHICAGO
case表達(dá)式
case search_expression
when expression1 then result1
when expression2 then result2
......
when expression then result
else default_result;
員工工資小于1000
1000~2000
>2000
low med high
3 8 10
SQL> select empno,ename,
2 case deptno
3 when 10 then 'A'
4 when 20 then 'B'
5 when 30 then 'C'
6 else 'not exists'
7 end
8 from emp;
EMPNO ENAME CASEDEPTNO
---------- ---------- ----------
7369 SMITH A
7499 ALLEN C
7521 WARD C
7566 JONES B
7654 MARTIN C
7698 BLAKE C
7782 CLARK A
SQL> select
2 count(case when sal<1000 then 1 else null end) low,
3 count(case when sal between 1000 and 2000 then 1 else null end) med,
4 count(case when sal>2000 then 1 else null end) high
5 from emp;
LOW MED HIGH
---------- ---------- ----------
2 6 6
一行數(shù)據(jù)展示出來所有部門的人數(shù)(列轉(zhuǎn)行)
select * from
(select deptno,count(1) c from emp group by deptno) t
pivot (min(c) for deptno in(10,20,30,40,50))
