Select语句中各种子句的执行顺序为：
Where->group by->having->order by
字符型函数
· RPAD和LPAD
rpad('string',l,'add')
用字符串 add 在字符串右边进行补全，新的字符串长度为l
lpad('string',l,'add')
用字符串 add 在字符串左边进行补全，新的字符串长度为l
例
SQL> select rpad('abc',20,'d') from dual;
RPAD('ABC',20,'D')
abcddddddddddddddddd
SQL> select lpad('abc',20,'d') from dual;
LPAD('ABC',20,'D')
dddddddddddddddddabc
· concat
concat（'str1','str2'）
连接字符串（任何数据库中都可使用）
'str1'||'str2'
连接字符串（Oracle特有）
例
SQL> select concat('abc','efg') from dual;
abcefg
SQL> select 'abc'||'a' from dual;
abca
·LOWER,UPPER和INITCAP
lower('str')
转小写字母
upper('str')
转大写字母
initcap（'str'）
首字母变大写，其他字母转小写
例
SQL> select lower(ename) from emp;
smith
allen
ward
jones
martin
blake
clark
scott
king
turner
adams
james
ford
miller
·LENGTH
length('str')
求字符串长度
·SUBSTR
substr('str',n,m)
从第n个字符开始，截取长度为m的子串
例
SQL> select substr('akegdake',3,3) from dual;
egd
·LTRIM和RTRIM
ltrim('str1','str2')
'str2’是截断集
从左往右逐个字符检查，剔除str1中属于截断集的字符，直到读取到str1中不属于截断集的字符，输出截断后的字符。
rtrim('str1','str2')
'str2’是截断集
从右往右逐个字符检查，剔除str1中属于截断集的字符，直到读取到str1中不属于截断集的字符，输出截断后的字符。
截断集缺省，默认为空格
例
SQL> select ltrim('abcdefg','bagf') from dual;
cdefg
SQL> select rtrim('abcdefg','bagf') from dual;
abcde
·INSTR
instr('str1','str2')
返回str2在str1中的第一个位置
返回“0”表示查询不到
例
SQL> select instr('abcdefg','bcx')from dual;
INSTR('ABCDEFG','BCX')
----------------------
0
SQL> select instr('abcdefg','bc')from dual;
INSTR('ABCDEFG','BC')
---------------------
2
·CHR
chr(n)
打印对应ASCII码（并非所有都可以打印，例如控制字符）
例
SQL> select chr(78) from dual;
CH
--
N
·REPLACE
replace(str,s1,s2)
将字符串str中的s1（若存在）替换成s2
例
SQL> select ename,replace(ename,'S','*') from emp;
ENAME REPLACE(ENAME,'S','*
-------------------- --------------------
SMITH *MITH
ALLEN ALLEN
WARD WARD
JONES JONE*
MARTIN MARTIN
BLAKE BLAKE
CLARK CLARK
SCOTT *COTT
KING KING
TURNER TURNER
ADAMS ADAM*
JAMES JAME*
FORD FORD
MILLER MILLER
·TRANSLATE
translate(str,str1,str2)
遍历字符串str，将其存在的str1中的字符用对应位置str2中的字符替换
例
SQL> select ename,translate(ename,'S','*') from emp;
ENAME TRANSLATE(ENAME,'S','*')
-------------------- --------------------------------------------------------------------------------
SMITH *MITH
ALLEN ALLEN
WARD WARD
JONES JONE*
MARTIN MARTIN
BLAKE BLAKE
CLARK CLARK
SCOTT *COTT
KING KING
TURNER TURNER
ADAMS ADAM*
JAMES JAME*
FORD FORD
MILLER MILLER
·SOUNDEX
sound(str)
返回字符串str的语音码
###例
SQL> select ename,soundex(ename) from emp;
ENAME SOUNDEX(
-------------------- --------
SMITH S530
ALLEN A450
WARD W630
JONES J520
MARTIN M635
BLAKE B420
CLARK C462
SCOTT S300
KING K520
TURNER T656
ADAMS A352
JAMES J520
FORD F630
MILLER M460
进行模糊语音识别（不支持中文）
SQL> select ename from emp where soundex(ename)=soundex('skot');
ENAME
--------------------
SCOTT
数值处理函数
·NVL 空值处理函数
nvl(m,n)
当m是空值时，返回n
当m非空时，返回本身
例
SQL> select ename,sal+nvl(comm,0) from emp;
ENAME SAL+NVL(COMM,0)
-------------------- ---------------
SMITH 800
ALLEN 1900
WARD 1750
JONES 2975
MARTIN 2650
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
·CEIL,FLOOR,ROUND,TRUNC小数处理函数
ceil(n)
向上取整
floor(n)
向下取整
round(n,m)
按m位小数进行四舍五入
trunc(n，m)
按m位小数进行截断
例
SQL> select round(123.123823,3) from dual;
ROUND(123.123823,3)
-------------------
123.124
SQL> select trunc(123.123823,3) from dual;
TRUNC(123.123823,3)
-------------------
123.123
·ABS,POWER,MOD,SQRT,EXP,LN,LOG,SIN,COS,TAN,SIGN数学运算函数
例
SQL> select exp(ln(2)) from dual;
EXP(LN(2))
----------
2
SQL> select log(2,8) from dual;
LOG(2,8)
----------
3
·LEAST和GREATEST
least(list)
返回列表list中的最小值
greatest(list)
返回列表list中的最大值
例
SQL> select least(1.90,2,8.0,-120,ln(2)) from dual;
LEAST(1.90,2,8.0,-120,LN(2))
----------------------------
-120
SQL> select greatest(123,58) from dual;
GREATEST(123,58)
----------------
123
SQL> select least('A','B','C','c','a') from dual;
LE
--
A
伪列函数
·sysdate
无需参数，显示系统时间
SQL> select sysdate from dual;
SYSDATE
--------------
18-5月 -20
·user
无需参数，显示当前用户
SQL> select user from dual;
USER
------------------------------------------------------------
C##SCOTT
·rownum
无需参数，打印输出顺序的行号
SQL> select rownum from dual;
ROWNUM
----------
1
注意：有输出才赋予行号
SQL> select * from emp where rownum>3;
未选定行
·rowid
无需参数，输出存储地址
```sql
SQL> select rowid,ename from emp;
ROWID ENAME
------------------ --------------------
AAAWdMAAGAAAADHAAA SMITH
AAAWdMAAGAAAADHAAB ALLEN
AAAWdMAAGAAAADHAAC WARD
AAAWdMAAGAAAADHAAD JONES
AAAWdMAAGAAAADHAAE MARTIN
AAAWdMAAGAAAADHAAF BLAKE
AAAWdMAAGAAAADHAAG CLARK
AAAWdMAAGAAAADHAAH SCOTT
AAAWdMAAGAAAADHAAI KING
AAAWdMAAGAAAADHAAJ TURNER
AAAWdMAAGAAAADHAAK ADAMS
AAAWdMAAGAAAADHAAL JAMES
AAAWdMAAGAAAADHAAM FORD
AAAWdMAAGAAAADHAAN MILLER
日期与时间相关操作函数
·TO_CHAR
to_char(时间/数字，格式码)
格式码诸如‘xxx’时，进行十进制到十六进制的进制转换
例
SQL> select to_char(sysdate,'yyyy-mm-dd') from dual;
TO_CHAR(SYSDATE,'YYY
--------------------
2020-05-18
SQL> select to_char(128.129387,'999.99') from dual;
TO_CHAR(128.12
--------------
128.13
SQL> select TO_CHAR(123,'$99,999.9') from dual;
TO_CHAR(123,'$99,999
--------------------
$123.0
SQL> select to_char(123,'xxx') from dual;
TO_CHAR(
--------
7b
SQL> select to_char(sysdate,'yyyy-mm-dd--hh-mi-ss') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DD--HH-MI-SS')
----------------------------------------
2020-05-18--08-19-42
△to_char函数返回中文年月日日期格式
SQL> select to_char(sysdate, 'yyyy"年"mm"月"dd"日" hh24"时"mi"分"ss"秒"') from dual;
TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日"HH24"时"MI"分"SS"秒"')
------------------------------------------------------------------
2020年05月22日 09时49分24秒
SQL> select to_char(sysdate, 'yyyy"年"monthdd"日" hh"时"mi"分"ss"秒"') from dual;
TO_CHAR(SYSDATE,'YYYY"年"MONTHDD"日"HH"时"MI"分"SS"秒"')
------------------------------------------------------------------------
2020年5月 22日 09时49分52秒
SQL> select to_char(to_date('20150109', 'yyyy-mm-dd'), 'yyyy"年"monthdd"日"') from dual;
TO_CHAR(TO_DATE('20150109','YYYY-MM-DD')
----------------------------------------
2015年1月 09日
SQL> select to_char(sysdate, 'fmyyyy"年"mm"月"dd"日" hh24"时"mi"分"ss"秒"') from dual;
TO_CHAR(SYSDATE,'FMYYYY"年"MM"月"DD"日"HH24"时"MI"分"SS"秒"')
------------------------------------------------------------------
2020年5月22日 9时50分10秒
·TO_DATE
作用与to_char相反
例
SQL> select to_date('20200518','yyyymmdd') from dual;
TO_DATE('20200
--------------
18-5月 -20
·add_months和last_day
add_months(date,n)
输出日期date加一个月的日期，若超出这个月的最大日期则显示最后一天
last_day(date)
显示这个日期的最后一天
例
SQL> select add_months(to_date(20200131,'yyyymmdd'),1) from dual;
ADD_MONTHS(TO_
--------------
29-2月 -20
SQL> select last_day(sysdate) from dual;
LAST_DAY(SYSDA
--------------
31-5月 -20
·months_between
months_between(date1.date2)
计算日期1与日期2之间相差的月份数
SQL> select months_between(sysdate,hiredate) from emp;
MONTHS_BETWEEN(SYSDATE,HIREDATE)
--------------------------------
473.059833
470.963059
470.898542
469.543704
463.704994
468.575962
467.317897
399.834026
462.059833
464.350155
397.543704
461.511446
461.511446
459.866284
聚组 Group by子句
聚组表达式，主要用于分组，所有聚组表达式相同的数据分为一组。跟在select语句的后面。
如果select语句中有group by子句，则出现在select后面的只能是聚组表达式和聚组函数。
以下介绍几种聚组函数。
·avg()
求平均值。
例
SQL> select deptno,avg(sal) from emp group by deptno;
DEPTNO AVG(SAL)
---------- ----------
30 1566.66667
20 1968.75
10 1875
·sum()
sum()用于求和
例
SQL> select job,sum(sal) from emp group by job;
JOB SUM(SAL)
------------------ ----------
CLERK 4150
SALESMAN 5600
MANAGER 8275
ANALYST 3000
·max()和min()
用于求最大值与最小值
例
SQL> select mgr,max(sal) from emp group by mgr;
MGR MAX(SAL)
---------- ----------
7839 2975
7782 1300
7698 1600
7902 800
7788 1100
7566 3000
已选择 6 行。
·stddev()和variance()
用于求标准差和方差
例
SQL> select mgr,max(sal) from emp group by mgr;
MGR MAX(SAL)
---------- ----------
7839 2975
7782 1300
7698 1600
7902 800
7788 1100
7566 3000
已选择 6 行。
·count()
count()用于统计数量。
例如
SQL> select job,count(sal) from emp group by job;
JOB COUNT(SAL)
------------------ ----------
CLERK 4
SALESMAN 4
MANAGER 3
ANALYST 1
SQL> select job,count(*) from emp group by job;
JOB COUNT(*)
------------------ ----------
CLERK 4
SALESMAN 4
MANAGER 3
ANALYST 1
dinstinct a 过滤a重复的类型
conut (distinct a) 统计过滤后a的数量
例
SQL> select count(distinct job) from emp;
COUNT(DISTINCTJOB)
------------------
4
SQL> select distinct job from emp;
JOB
------------------
CLERK
SALESMAN
MANAGER
ANALYST
Having子句
having子句，用于对聚组统计的结果进行筛选。
例
SQL> select deptno,count(*) from emp group by deptno having count(*)>3;
DEPTNO COUNT(*)
---------- ----------
30 6
20 4
连接
·无条件连接（笛卡尔积）
返回连接对象的所有排列组合结果。
若连接对象相同，通常需要通过别名来消除歧义以防混淆。（例如emp表语自身连接：select A.ename c1,B.ename c2 from emp A,emp B）
例
结果为emp内12行ename和dept内4行dname所有的排列组合结果。
SQL> select ename,dname from emp,dept;
ENAME DNAME
-------------------- ----------------------------
SMITH ACCOUNTING
ALLEN ACCOUNTING
WARD ACCOUNTING
JONES ACCOUNTING
MARTIN ACCOUNTING
BLAKE ACCOUNTING
CLARK ACCOUNTING
TURNER ACCOUNTING
ADAMS ACCOUNTING
JAMES ACCOUNTING
FORD ACCOUNTING
MILLER ACCOUNTING
SMITH RESEARCH
ALLEN RESEARCH
WARD RESEARCH
JONES RESEARCH
MARTIN RESEARCH
BLAKE RESEARCH
CLARK RESEARCH
TURNER RESEARCH
ADAMS RESEARCH
JAMES RESEARCH
FORD RESEARCH
MILLER RESEARCH
SMITH SALES
ALLEN SALES
WARD SALES
JONES SALES
MARTIN SALES
BLAKE SALES
CLARK SALES
TURNER SALES
ADAMS SALES
JAMES SALES
FORD SALES
MILLER SALES
SMITH OPERATIONS
ALLEN OPERATIONS
WARD OPERATIONS
JONES OPERATIONS
MARTIN OPERATIONS
BLAKE OPERATIONS
CLARK OPERATIONS
TURNER OPERATIONS
ADAMS OPERATIONS
JAMES OPERATIONS
FORD OPERATIONS
MILLER OPERATIONS
已选择 48 行。
·有条件连接
有条件连接通常可视为，先做无条件连接，在进行筛选的操作。
实际上这种方式繁琐，一般并不是这么来做。例子是为了方便理解。
例
列出员工的姓名及其所在部门名称。
SQL> select ename,dname from emp,dept where emp.deptno=dept.deptno;
ENAME DNAME
-------------------- ----------------------------
CLARK ACCOUNTING
MILLER ACCOUNTING
FORD RESEARCH
SMITH RESEARCH
ADAMS RESEARCH
JONES RESEARCH
JAMES SALES
TURNER SALES
MARTIN SALES
WARD SALES
ALLEN SALES
BLAKE SALES
已选择 12 行。
·自连接
一个表与它自己进行连接。
通常需要通过别名来消除歧义以防混淆。（例如emp表语自身连接：select A.ename c1,B.ename c2 from emp A,emp B）
例
select A.ename c1,B.ename c2 from emp A,emp B;
已选择 144 行。
·外连接
外连接，在连接条件的左端或者右端后面添上一个加号（左外连接与右外连接），表示左边或者右边这个值即使是空值条件依然成立。
例
右连接
SQL> select a.ename,b.ename from emp a,emp b
2 where a.mgr=b.empno(+);
ENAME ENAME
-------------------- --------------------
FORD JONES
JAMES BLAKE
TURNER BLAKE
MARTIN BLAKE
WARD BLAKE
ALLEN BLAKE
MILLER CLARK
SMITH FORD
ADAMS
CLARK
BLAKE
JONES
已选择 12 行。
左连接
SQL> select a.ename,b.ename from emp a,emp b
2 where a.mgr(+)=b.empno;
ENAME ENAME
-------------------- --------------------
SMITH
ALLEN
WARD
FORD JONES
MARTIN
JAMES BLAKE
WARD BLAKE
MARTIN BLAKE
ALLEN BLAKE
TURNER BLAKE
MILLER CLARK
TURNER
ADAMS
JAMES
SMITH FORD
MILLER
已选择 16 行。
6万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
