Oracle通用操作指令

a not in（1,2,3）表示 a不等于1、2、3，即不等于括号里所有的值

DISTINCT COLUMN：去重

ORDER BY COLUMN ASC(DESC)：排序，默认为ASC

 

单行函数

LOWER ('ABCDE')

 

UPPER ('abcd')

INITCAP (ENAME)

 

CONCAT ('A','B')

'A' || 'B'

SUBSTR ('ABCDE',LENGTH('ABCDE')-2)

SELECT SUBSTR ('ABCDEFG',2) FROM DUAL;

SELECT SUBSTR ('ABCDEFG',-2) FROM DUAL;

 

SELECT LENGTH (ENAME),ENAME FROM EMP;

REPLACE (ENAME,'a','A')

把字符串中的a替换成A

SELECT INSTR ('HELLO WORLD','OR') FROM DUAL;

对于instr函数，我们经常这样使用：从一个字符串中查找指定子串的位置,该例中值为8

SELECT LPAD ('SMITH',10,'*') FROM DUAL;--从左边开始填充*，直到整个字符串的长度为10

 

SELECT RPAD ('SMITH',10,'*') FROM DUAL;--从右边开始填充*,直到整个字符串的长度为10

SELECT TRIM(' MR SMITH ') FROM DUAL;--过滤掉首尾空格

 SELECT ROUND (412，-2) FROM DUAL;--在小数点左侧两位进行四舍五入

round(num,digits)，如果digits>0就是保留几位小数，等于0是保留整数，小于0则是依次往左进行四舍五入。整个流程可以归为都是从右边的数四舍五入左边的数

SELECT MOD(-7,-10) FROM DUAL;--取余，负数对正数取余为负数，正数对负数取余为正数，负数对于负数取余为负数

SELECT TRUNC (412.13，-2) FROM DUAL;--TRUNC[NUMBER[,DECIMALS])NUMBER待做截取处理的数值，DECIMALS指明需要保留小数点后面的位数。可选项，忽略它则截去所有的小数部分，若为负数，则截去小数点左边的指定位数

 

SELECT MONTHS_BETWEEN (SYSDATE,HIREDATE) FROM EMP;--函数返回两个日期之间的月份数。如果两个日期月份内天数相同，或者都是某个月的最后一天，返回一个整数，否则，返回数值带小数，以每天1/31月来计算月中剩余天数，即剩余的天数/31就是小数部分。如果日期1比日期2小 ，返回值为负数。

SELECT SYSDATE,ADD_MONTHS (SYSDATE,1) FROM DUAL;--在原基础上添加一个月

SELECT LAST_DAY (SYSDATE) FROM DUAL;--当前系统时间月份的最后一天

 

--转换成指定的格式

SELECT TO_CHAR (SYSDATE,'YYYY') FROM DUAL;

SELECT TO_CHAR (SYSDATE,'FMYYYY-MM-DD'),TO_CHAR (SYSDATE,'YYYY-MM-DD') FROM DUAL;

SELECT TO_NUMBER('13')+TO_NUMBER('14') FROM DUAL;--把字符串转换为数字

SELECT TO_DATE ('20090210','YYYYMMDD') FROM DUAL;--把字符串转换为日期

SELECT NVL (COMM,0) FROM EMP;--如果第一个参数为空，则显示第二个参数，如果第一个参数不为空，则显示第一个参数

SELECT NULLIF (12,12),NULLIF(113,12) FROM DUAL;--如果两个值相等则返回NULL，不相等则返回第一个值

SELECT NVL2 (NULL,1,2),NVL2 (0,1,2) FROM DUAL;--如果第一个值为空则返回第三个值，如果第一个值不为空则返回第二个值

SELECT EMPNO,ENAME,SAL,COMM,COALESCE (SAL+COMM,SAL,0)总收入 FROM EMP;--返回第一个不为空的值

SELECT EMPNO,ENAME,SAL,DEPTNO,
CASE DEPTNO
WHEN 10 THEN '财务部'
WHEN 20 THEN '研发部'
WHEN 30 THEN '销售部'
ELSE '未知部门'
END 部门
FROM EMP;

SELECT EMPNO,ENAME,SAL,
DECODE(DEPTNO,10,'财务部',
20,'研发部',
30,'销售部',
'未知部门')
部门
FROM EMP;

SELECT * FROM EMP WHERE HIREDATE=LAST_DAY(HIREDATE)-2;--找出每个月倒数第三天受雇的员工。（如：2009-5-29）

SELECT * FROM EMP WHERE HIREDATE <= ADD_MONTHS (SYSDATE,-25*12);--找出25年前雇的员工

SELECT 'DEAR' || INITCAP (ENAME) FROM EMP;--所有员工名字前加上Dear ,并且名字首字母大写

SELECT * FROM EMP WHERE LENGTH(ENAME) = 5;--找出姓名为5个字母的员工

 

--找出姓名中不带R这个字母的员工
SELECT * FROM EMP WHERE ENAME NOT LIKE '%R%';

--显示所有员工的姓名的第一个字
SELECT SUBSTR(ENAME,0,1) FROM EMP;

--找到2月份受雇的员工
SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'FMMM')='2';

--分别用case和decode函数列出员工所在的部门，deptno=10显示'部门10',
--deptno=20显示'部门20'
--deptno=30显示'部门30'
--deptno=40显示'部门40'
--否则为'其他部门'
SELECT ENAME,DEPTNO,
CASE DEPTNO
WHEN 10 THEN '部门10'
WHEN 20 THEN '部门20'
WHEN 30 THEN '部门30'
WHEN 40 THEN '部门40'
ELSE '其他部门'
END 部门
FROM EMP;

SELECT ENAME,DEPTNO,
DECODE(DEPTNO,10,'部门10'
,20,'部门20'
,30,'部门30'
,40,'部门40'
,'其他部门')
部门
FROM EMP;

 

 

分组函数

COUNT

如果数据库的没有数据，COUNT(*)返回的不是NULL，而是0.

分组函数省略列中的空值
select avg(comm) from emp;
select sum(comm) from emp;
可使用NVL()函数强制分组函数处理空值
select avg(nvl(comm, 0)) from emp;

包含在 GROUP BY子句中的字段则不必须出现在SELECT列表中。
可使用where字句限定查询条件
可使用Order by子句指定排序方式
如果没有GROUP BY子句，SELECT列表中不允许出现字
段（单行函数）与分组函数混用的情况。