SQL语言、函数以及在Oracle中的的应用
https://blog.csdn.net/qq_38170766/article/details/105379703
单行函数
为了方便数据库操作，oracle提供了多重函数操作，单行函数分为字符型单行函数、数字型单行函数和日期型单行函数。
字符型单行函数
字符型单行函数接收一个字符输入，并且返回计算结果，返回的结果可以使字符型也可以是数字型。
-
LOWER函数
LOWER函数是将字符串转换成小写。
函数格式为：LOWER（列名 | 表示）
SQL> select LOWER(ename) from emp;
LOWER(ENAME)
--------------------
smith
allen
ward
jones
martin
blake
clark
scott
king
turner
adams
LOWER(ENAME)
--------------------
james
ford
miller
已选择14行。-
UPPER函数
UPPER函数是将字符串转换成大写。
函数格式为：UPPER（列名 | 表示）
SQL> select * from dual;
DU
--
X
SQL> select UPPER('select * from tablename') from dual;
UPPER('SELECT*FROMTABLENAME')
----------------------------------------------
SELECT * FROM TABLENAME
-
INITCAP函数
INITCAP函数是将字符串的首字母转换成大写，其余部分转成小写。
函数格式为：INITCAP（列名 | 表示）
SQL> select INITCAP(ename) from emp;
INITCAP(ENAME)
--------------------
Smith
Allen
Ward
Jones
Martin
Blake
Clark
Scott
King
Turner
Adams
INITCAP(ENAME)
--------------------
James
Ford
Miller
已选择14行。-
CONCAT函数
CONCAT函数是用于连接两个字符串，或者连接两个列的数据。
函数格式为：CONCATE（列名1 | 表示1,列名2 | 表示2,.....）
SQL> select CONCAT(ename,sal) from emp;
CONCAT(ENAME,SAL)
--------------------------------------------------------------------------------
SMITH800
ALLEN1600
WARD1250
JONES2975
MARTIN1250
BLAKE2850
CLARK2450
SCOTT3000
KING5000
TURNER1500
ADAMS1100
CONCAT(ENAME,SAL)
--------------------------------------------------------------------------------
JAMES950
FORD3000
MILLER1300
已选择14行。-
SUBSTR函数
SUBSTR函数是用于获取字符串中指定开头和指定结尾的字符，如果未指定结尾，则从从指定的开头一直到字符串结尾。
函数格式为：SUBSTR（列名 | 表示,M,N）
注意：0和1的区别
SQL> select SUBSTR(ename,1,3) from emp;
SUBSTR
------
SMI
ALL
WAR
JON
MAR
BLA
CLA
SCO
KIN
TUR
ADA
SUBSTR
------
JAM
FOR
MIL
已选择14行。
SQL> select SUBSTR(ename,0,3) from emp;
SUBSTR
------
SMI
ALL
WAR
JON
MAR
BLA
CLA
SCO
KIN
TUR
ADA
SUBSTR
------
JAM
FOR
MIL
已选择14行。
SQL> select SUBSTR(ename,3) from emp;
SUBSTR(ENAME,3)
----------------
ITH
LEN
RD
NES
RTIN
AKE
ARK
OTT
NG
RNER
AMS
SUBSTR(ENAME,3)
----------------
MES
RD
LLER
已选择14行。-
LENGTH函数
LENGTH函数是用于获取字符串的长度。
函数格式为：LENGTH（列名 | 表示）
SQL> select LENGTH(ename) from emp;
LENGTH(ENAME)
-------------
5
5
4
5
6
5
5
5
4
6
5
LENGTH(ENAME)
-------------
5
4
6
已选择14行。-
INSTR函数
INSTR函数是用于在字符串中搜索一段字符串，如果在M,N之间的位置没有搜索到字符则在expression中搜索。
函数格式为：INSTR（列名 | 表示,'string',M,N）
SQL> select INSTR(ename,'S',1) from emp;
INSTR(ENAME,'S',1)
------------------
1
0
0
5
0
0
0
1
0
0
5
INSTR(ENAME,'S',1)
------------------
5
0
0
已选择14行。-
LPAD函数
LPAD函数是用于用特定字符补全字符串长度。
函数格式为：LPAD（列名 | 表示,n,'string'）
SQL> select LPAD(ename,10,'*') from emp;
LPAD(ENAME,10,'*')
--------------------
*****SMITH
*****ALLEN
******WARD
*****JONES
****MARTIN
*****BLAKE
*****CLARK
*****SCOTT
******KING
****TURNER
*****ADAMS
LPAD(ENAME,10,'*')
--------------------
*****JAMES
******FORD
****MILLER
已选择14行。
-
TRIM函数
TRIM函数是用于在字符串中剪切一个字符，输出。
LEFT：表示从左边剪切
Right：表示从右边剪切
Both：表示两边都执行（默认两边）
函数格式为：TRIM（LEFT | RIGHT | BOTH,'string' FROM 列名）
SQL> select TRIM('S' FROM ename) from emp;
TRIM('S'FROMENAME)
--------------------
MITH
ALLEN
WARD
JONE
MARTIN
BLAKE
CLARK
COTT
KING
TURNER
ADAM
TRIM('S'FROMENAME)
--------------------
JAME
FORD
MILLER
已选择14行。-
REPLACE函数
REPLACE函数是用于将字符串内指定字符替换为其他字符。
函数格式为：REPLACE（列名 , '需要替换的字符' , '替换后的字符'）
SQL> select REPLACE(ename , 'S' , '*') from emp;
REPLACE(ENAME,'S','*
--------------------
*MITH
ALLEN
WARD
JONE*
MARTIN
BLAKE
CLARK
*COTT
KING
TURNER
ADAM*
REPLACE(ENAME,'S','*
--------------------
JAME*
FORD
MILLER
已选择14行。数字型单行函数
数字型单行函数实现对数字的处理，其输出也为数字类型。
-
ROUND函数
ROUND函数是用于用户对一个数字输出用户指定的小数位结果四舍五入。
该参数n值为负则表示要取的整数位
函数格式为：ROUND（列名 , n）
SQL> select ROUND(10.1234,3) from dual;
ROUND(10.1234,3)
----------------
10.123
SQL> select ROUND(11.1234,-1) from dual;
ROUND(11.1234,-1)
-----------------
10-
TRUNC函数
TRUNC函数是用于截断一个数字，只保留小数点后一定位数，处理时候不采用四舍五入。
该参数n值为负则表示要取的整数位
函数格式为：TRUNC（列名 , n）
SQL> select TRUNC(16.1236,3) from dual;
TRUNC(16.1236,3)
----------------
16.123
SQL> select TRUNC(16.1236,-1) from dual;
TRUNC(16.1236,-1)
-----------------
10-
MOD函数
MOD函数是用于对数字的进行除后取余。
不够除的时候会直接返回数字
函数格式为：MOD（列名 , n）
SQL> select MOD(1100,200) from dual;
MOD(1100,200)
-------------
100
SQL> select MOD(200,1100) from dual;
MOD(200,1100)
-------------
200日期型单行函数（略）
日期型单行函数是用于操作或者显示日期的函数。
-
SYSDATE函数
SYSDATE函数是用于返回系统当前时间的函数。
SQL> select SYSDATE from dual;
SYSDATE
---------
08-4月-20
1 row selected.
SQL> select to_date('06-4日-10') - sysdate from dual;
TO_DATE('06-4日-10')-SYSDATE
---------------------------
-3655.7064
1 row selected.
NULL和NULL值处理函数
-
什么是空值
NULL表示一类没有定义的值，具有不确定性的值，这类值无法表示，更无法显示。
-
查询emp表中的空值
下面的操作可以看出，NULL并非一个值，我们可以用NULL代表它但是不能直接用于计算。
如果要查询空值我们可以使用 IS NULL 来表示。
SQL> select ename,comm from emp where comm = NULL;
未选定行
SQL> select ename,comm from emp where comm IS NULL;
ENAME COMM
-------------------- ----------
SMITH
JONES
BLAKE
CLARK
SCOTT
KING
ADAMS
JAMES
FORD
MILLER
已选择10行。下面的操作可以看出，如果要查询非空值我们可以使用 IS NOT NULL 来表示。
SQL> select ename,comm from emp where comm IS NOT NULL;
ENAME COMM
-------------------- ----------
ALLEN 300
WARD 500
MARTIN 1400
TURNER 0-
NVL函数
NVL函数是用于将NULL值参与计算的函数，如果列对应的行的值为空值，则替换成NVL中的值。
注意：值不能是字符或者字符串
函数格式为：NVL（列名 , 值）
SQL> select ename,sal+NVL(comm,'***')from emp;
select ename,sal+NVL(comm,'***')from emp
*
第 1 行出现错误:
ORA-01722: ？？？？
SQL> select ename,sal+NVL(comm,11)from emp;
ENAME SAL+NVL(COMM,11)
-------------------- ----------------
SMITH 811
ALLEN 1900
WARD 1750
JONES 2986
MARTIN 2650
BLAKE 2861
CLARK 2461
SCOTT 3011
KING 5011
TURNER 1500
ADAMS 1111
ENAME SAL+NVL(COMM,11)
-------------------- ----------------
JAMES 961
FORD 3011
MILLER 1311
已选择14行。-
NVL2函数
NVL2函数是用于将NULL值参与计算的函数，如果列对应的行的值为空值，则替换成NVL2中的值2，如果不为空则替换为NAL2中的值1。
注意：值不能是字符或者字符串
函数格式为：NVL2（列名 , 值1 , 值2）
SQL> select ename,NVL2(comm,sal+comm,sal)from emp;
ENAME NVL2(COMM,SAL+COMM,SAL)
-------------------- -----------------------
SMITH 800
ALLEN 1900
WARD 1750
JONES 2975
MARTIN 2650
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
ENAME NVL2(COMM,SAL+COMM,SAL)
-------------------- -----------------------
JAMES 950
FORD 3000
MILLER 1300
已选择14行。-
NULLIF函数
NULLIF函数是用于比较两个表达式，如果值相等，则返回空值，如果值不等则返回第一个表达式的值。
注意：值1不能为空值
函数格式为：NULLIF（值1 , 值2）
SQL> select ename,length(ename) "value1",job,length(job) "value2",NULLIF(length(ename),length(job)) "compare" from emp;
ENAME value1 JOB value2 compare
-------------------- ---------- ------------------ ---------- ----------
SMITH 5 CLERK 5
ALLEN 5 SALESMAN 8 5
WARD 4 SALESMAN 8 4
JONES 5 MANAGER 7 5
MARTIN 6 SALESMAN 8 6
BLAKE 5 MANAGER 7 5
CLARK 5 MANAGER 7 5
SCOTT 5 ANALYST 7 5
KING 4 PRESIDENT 9 4
TURNER 6 SALESMAN 8 6
ADAMS 5 CLERK 5
ENAME value1 JOB value2 compare
-------------------- ---------- ------------------ ---------- ----------
JAMES 5 CLERK 5
FORD 4 ANALYST 7 4
MILLER 6 CLERK 5 6
已选择14行。-
COALESCE函数
COALESCE函数是用于返回所有表达式中第一个不为NULL的值。
函数格式为：COALESCE（值1 , 值2,....,值n）
ENAME COALESCE(COMM,1)
-------------------- ----------------
SMITH 1
ALLEN 300
WARD 500
JONES 1
MARTIN 1400
BLAKE 1
CLARK 1
SCOTT 1
KING 1
TURNER 0
ADAMS 1
ENAME COALESCE(COMM,1)
-------------------- ----------------
JAMES 1
FORD 1
MILLER 1条件表达式
SQL语言中oracle也提供了两个函数来实现逻辑判断功能
-
CASE函数
CASE函数是用于进行逻辑判断。
注意：WHEN后面得值不能使用双引号，且结果不能是字符串？
函数格式为：CASE 表达式 WHEN 满足值1 THEN 结果1 WHEN 满足值2 THEN 结果2.......ELSE 都不满足的结果 END
SQL> select ename,sal,CASE ename WHEN "SMITH" THEN sal*1.2 WHEN "CLARK" THEN sal*1.3 WHEN "TURNER" THEN sal*1.4 ELSE "sal" END "last salary" from emp;
select ename,sal,CASE ename WHEN "SMITH" THEN sal*1.2 WHEN "CLARK" THEN sal*1.3 WHEN "TURNER" THEN sal*1.4 ELSE "sal" END "last salary" from emp
*
第 1 行出现错误:
ORA-00904: "sal": ？？？？？
SQL> select ename,sal,CASE ename WHEN "SMITH" THEN sal*1.2 WHEN "CLARK" THEN sal*1.3 WHEN "TURNER" THEN sal*1.4 ELSE sal END "last salary" from emp;
select ename,sal,CASE ename WHEN "SMITH" THEN sal*1.2 WHEN "CLARK" THEN sal*1.3 WHEN "TURNER" THEN sal*1.4 ELSE sal END "last salary" from emp
*
第 1 行出现错误:
ORA-00904: "TURNER": ？？？？？
SQL> select ename,sal,CASE ename WHEN 'SMITH' THEN sal*1.2 WHEN 'CLARK' THEN sal*1.3 WHEN 'TURNER' THEN sal*1.4 ELSE sal END "last salary" from emp;
ENAME SAL last salary
-------------------- ---------- -----------
SMITH 800 960
ALLEN 1600 1600
WARD 1250 1250
JONES 2975 2975
MARTIN 1250 1250
BLAKE 2850 2850
CLARK 2450 3185
SCOTT 3000 3000
KING 5000 5000
TURNER 1500 2100
ADAMS 1100 1100
ENAME SAL last salary
-------------------- ---------- -----------
JAMES 950 950
FORD 3000 3000
MILLER 1300 1300
已选择14行。-
DECODE函数
DECODE函数是用于进行逻辑判断,大致上与CASE函数类似，但是相对简单。
函数格式为：DECODE(表达式或者行，满足值1，结果1，满足值2，结果2.......，都不满足的结果)
SQL> select ename,sal,DECODE(ename,'SMITH',sal*1.2,'CLARK' ,sal*1.3,'TURNER',sal*1.4,sal) "last salary" from emp;
ENAME SAL last salary
-------------------- ---------- -----------
SMITH 800 960
ALLEN 1600 1600
WARD 1250 1250
JONES 2975 2975
MARTIN 1250 1250
BLAKE 2850 2850
CLARK 2450 3185
SCOTT 3000 3000
KING 5000 5000
TURNER 1500 2100
ADAMS 1100 1100
ENAME SAL last salary
-------------------- ---------- -----------
JAMES 950 950
FORD 3000 3000
MILLER 1300 1300
已选择14行。分组函数
分组函数对表中的多行进行操作，而每组返回一个计算结果。
-
AVG函数
AVG函数是用于对列中所有行的值进行求平均值。
函数格式为：AVG( DISTINCT或者ALL 行)，默认全部的值，也可以选择不重复的值
-
SUM函数
SUM函数是用于对列中所有行的值进行求和。
函数格式为：SUM( DISTINCT或者ALL 行)，默认全部的值，也可以选择不重复的值
SQL> SELECT sal from emp;
SAL
----------
800
1600
1250
2975
1250
2850
2450
3000
5000
1500
1100
SAL
----------
950
3000
1300
已选择14行。
SQL> select AVG(sal) "月平均工资",SUM(sal) "月总工资" from emp;
月平均工资 月总工资
---------- ----------
2073.21429 29025
SQL> select AVG(DISTINCT sal) "月平均工资",SUM(DISTINCT sal) "月总工资" from emp;
月平均工资 月总工资
---------- ----------
2064.58333 24775-
MAX函数
MAX函数是用于对列中所有行的值中最大的值。
重复值并不会影响MAX函数的计算
函数格式为：MAX( DISTINCT或者ALL 行)，默认全部的值，也可以选择不重复的值
-
MIN函数
MIN函数是用于对列中所有行的值中最小的值。
重复值并不会影响MIN函数的计算
函数格式为：MIN( DISTINCT或者ALL 行)，默认全部的值，也可以选择不重复的值
SQL> SELECT sal from emp;
SAL
----------
800
1600
1250
2975
1250
2850
2450
3000
5000
1500
1100
SAL
----------
950
3000
1300
已选择14行。
SQL> select MIN(DISTINCT sal) "工资最少",MAX(DISTINCT sal) "工资最多" from emp;
工资最少 工资最多
---------- ----------
800 5000
SQL> select MIN(sal) "工资最少",MAX(sal) "工资最多" from emp;
工资最少 工资最多
---------- ----------
800 5000-
COUNT函数
COUNT函数是用于对列中所有的行的总数，包括空行和重复行。
在单列查询时候 ：不包括空行，但是包括重复的行
函数格式为：COUNT( *或者行)，*代表全部列，也可以单列
SQL> select COUNT(*) from emp;
COUNT(*)
----------
14
SQL> select COUNT(comm) from emp;
COUNT(COMM)
-----------
4
SQL> select COUNT(sal) from emp;
COUNT(SAL)
----------
14-
GROUP BY 子句
GROUP BY 子句是用于对列中“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。
函数格式为：GROUP BY 列名
SQL> select job,AVG(sal) "average salary",SUM(sal) "sum salary" from emp GROUP BY job;
JOB average salary sum salary
------------------ -------------- ----------
CLERK 1037.5 4150
SALESMAN 1400 5600
PRESIDENT 5000 5000
MANAGER 2758.33333 8275
ANALYST 3000 6000-
ORDER BY 子句
ORDER BY 子句是用于对列中所有数据进行排序。
函数格式为：ORDER BY 列名
ASC：升序（默认），DESC：降序。
SQL> select job,AVG(sal) "average salary",SUM(sal) "sum salary" from emp GROUP BY job ORDER BY "sum salary" ;
JOB average salary sum salary
------------------ -------------- ----------
CLERK 1037.5 4150
PRESIDENT 5000 5000
SALESMAN 1400 5600
ANALYST 3000 6000
MANAGER 2758.33333 8275
SQL> select job,AVG(sal) "average salary",SUM(sal) "sum salary" from emp GROUP BY job ORDER BY "sum salary" DESC ;
JOB average salary sum salary
------------------ -------------- ----------
MANAGER 2758.33333 8275
ANALYST 3000 6000
SALESMAN 1400 5600
PRESIDENT 5000 5000
CLERK 1037.5 4150
661
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
