oracle 常用查询语言

SQL- Data Query Language

1. select

2. where

3. distinct

4. Operators

• 算数

+,-,*,/ 

• 关系

=,<,>,<=,>=,<>,!=,^=

• 逻辑&&bool

AND,OR,NOT 

• 集合操作

UNION,UNION ALL,INTERSECT, MINUS 

- The UNION operator returns the records retrieved by either of the queries 
- By default, the UNION operator eliminates duplicate records 
- To retain duplicates, we use UNION ALL instead of UNION 
- The INTERSECT operator returns those rows which are retrieved by both the queries 
- The MINUS operator returns all rows retrieved by the first query but not by the second query 

• 其他

IN, BETWEEN, LIKE , IS NULL 

• 排序

order by

5. Single Row Functions

• 数字

NVL :将null 转成需要的值,TD 使用的是coalesce

SELECT sal + NVL( comm, 0 ) FROM emp;

ABS: 绝对值

SELECT ABS( -10 ) FROM dual;
o/p: 10

CEIL:往上取整数

SELECT CEIL( 23.2 ) FROM dual;

o/p: 24

floor:往下取整数

SELECT FLOOR( 56.99 ) FROM dual; 

o/p: 56

MOD: 取模

SELECT MOD( 5, 2 ) FROM dual; 

o/p: 1

POWER:幂

SELECT POWER( 3, 2 ) FROM dual: 

o/p: 9

ROUND:四舍五入

SELECT ROUND( 52.5 ) FROM dual; 

o/p: 53

SQRT:开方

SELECT SQRT( 4 ) FROM dual; 

o/p: 2

TRUNC:保留小数位数

SELECT TRUNC( 56.223, 1 ) FROM dual; 

o/p : 56.2

SIGN:判断正负

SELECT SIGN( -10 ), SIGN( 0 ), SIGN( 10 ) FROM dual;

o/p -1 0 1

• 字符串
  CONCAT:拼接

SELECT CONCAT( ename, job ) FROM emp;
-- teradata 用的是||

UPPER(大写), LOWER(小写), INITCAP(首字母大写)

SELECT UPPER( ename ), LOWER( ename ), INITCAP( ename )  FROM emp;

RPAD, LPAD:左右拼接

SELECT LPAD( ename, 30, '*' ), RPAD( ename, 30, '-' ) FROM emp;

LTRIM,RTRIM:左右裁剪

SELECT LTRIM( ename ), RTRIM( ename ) FROM emp;

-- teradata 默认是trim 左右所有的空格,有多个也会清除掉

LENGTH:长度

SELECT LENGTH ( ename ) FROM emp;

INSTR:位置

SELECT INSTR( ename, 'S', 1 ) FROM emp;

SUBSTR:截取字符串

SELECT SUBSTR( ename, 1, 3 ) FROM emp;

SOUNDEX:发音一样的

SELECT ename FROM emp WHERE SOUNDEX( ename ) = SOUNDEX( 'SMEETH' );

• 时间

TO_CHAR:转成字符串

SELECT TO_CHAR( hiredate, 'day-month-year' ) FROM EMP;

ADD_MONTHS:增加月份

SELECT ADD_MONTHS( hiredate, 11 ) FROM emp;
SELECT ADD_MONTHS( hiredate, -1 ) FROM emp;-- 前一个月

MONTHS_BETWEEN:月份之间

SELECT ADD_MONTHS( hiredate, 11 ) FROM emp;

上一天:LAST_DAY

SELECT LAST_DAY( hiredate ) FROM emp;

下一天:NEXT_DAY

SELECT NEXT_DAY( hiredate, 'friday' ) from emp;

TO_CHAR(X): 制定转换字符串

SELECT TO_CHAR( 1981 ) FROM dual;

SELECT TO_CHAR( hiredate, 'YYYYMMDD' ) FROM dual;

TO_NUMBER(X): 转成数字

SELECT TO_NUMBER( '1221' ) FROM dual;

TO_DATE(X,[Y]):转换日期

SELECT TO_DATE( '12-FEB-2007' ) FROM dual;

• 汇总函数
  SUM/AVG/COUNT/MIN/MAX

6. Group by and Having

• group by
• having

7. 其他函数

• decode
• case

8.在线分析处理函数(OLAP(Online Analytical processing) )

ROLLUP，是GROUP BY子句的一种扩展，可以为每个分组返回小计记录以及为所有分组返回总计记录。
CUBE，也是GROUP BY子句的一种扩展，可以返回每一个列组合的小计记录，同时在末尾加上总计记录。

9. 连接

• equi join
• non equi
• outer
• self

参考文档

SQL- Data Query Language