oracle数据操纵语言(DML)data manipulation language

浏览数：39 / 时间：2015年06月12日

数据库操纵语言（DML）用于查询和操纵模式对象中的数据，它不隐式地提交当前事务。

SELECT
INSERT
UPDATE
DELETE
CALL
EXPLAIN PLAN
LOCK TABLE
MERGE
使用算术操作符（在数值型数据上使用

SELECT ename, sal, sal+300 FROM emp;

NULL值：NULL是一个值，它和“0”、“空白”不一样。

插入图片

定义列的别名：给列表达式提供一个不同的名字，该别名在列标题中使用。如果别名中包含空格或特定的字符或需要区分大小写时，需要使用双引号。
SELECT ename AS name, sal salary FROM emp;

SELECT ename "Name", sal*12 "Annual Salary" FROM emp;[添加了引号，看清楚了]

连接符（||）：将两个串结合在一起，两个串可以是列名或直接量

使用DISTINCT关键字消除所选择的重复行，只返回一行。
SELECT DISTINCT deptno FROM emp;

条件（WHERE）:比较符

SELECT   ename, sal
2 FROM    emp
3 WHERE   sal BETWEEN 1000 AND 1500;

SELECT   empno, ename, sal, mgr
2 FROM    emp
3 WHERE   mgr IN (7902, 7566, 7788);

条件（WHERE）:比较符，LIKE用例。Oracle支持两种匹配符号：「 % 」和「 _ 」，符号「 % 」可匹配0个或多个字符，符号「 _ 」可匹配任何单一字符。

条件（WHERE）:逻辑操作符 [and or not]

排序（ORDER BY子句）：使得SQL在显示查询结果时将各返回行按顺序排列，返回行的排列顺序由ORDER BY 子句指定的表达式的值确定.
ASC:从小到大排序（default）
DESC:从大到小排序
ORDER BY子句在SELECT语句的最后
SELECT ename,job,deptno,hiredate
2 FROM emp
3 ORDER BY hiredate DESC;

数值函数

日期类型：
Oracle是用数字来存储日期信息，数字的整数部分代表与Julian Calendar（罗马儒略历，公历）相距的天数（由公元前4712年1月1日开始），而小数部分代表时、分和秒。
Oracle默认日期格式：DD-MON-YY
SYSDATE函数返回系统当前的日期和时间。
DUAL是Oracle内置的虚拟表，只有一行一列。
日期运算：不允许日期加日期
日期函数：

数据转换

转换函数：隐式转换
在赋值时，Oracle服务器能够进行自动转换的数据类型列表

显示转换

函数TO_CHAR
TO_CHAR(date, ‘format_model‘)

SELECT   ename,
2       TO_CHAR(hiredate, ‘fmDD Month YYYY‘) HIREDATE
3 FROM     emp;

ENAME      HIREDATE
---------- -----------------
KING       17 November 1981
BLAKE      1 May 1981
CLARK      9 June 1981
JONES      2 April 1981
MARTIN     28 September 1981
ALLEN      20 February 1981

SELECT Ename,TO_CHAR(hiredate,‘DD-Mon-YYYY‘)
FROM emp
WHERE hiredate < TO_DATE(‘01-01-90‘,‘DD-MM-RR‘);

空值置换函数：如果表达式不为NULL，函数就返回该表达式的值。如果位空，就返回用来替换的值

NVL (expr1, expr2)
NVL2 (expr1, expr2, expr3)

函数可以使用date, character, 和number数据类型。
表达式的值和替换值类型必须匹配:
      NVL(comm,0)
      NVL(hiredate,‘01-JAN-97‘)
      NVL(job,‘No Job Yet‘)

条件表达式： CASE表达式
SELECT ename, job, sal,
       CASE job WHEN ‘ANALYST‘ THEN 1.10*sal
                   WHEN ‘CLERK‘ THEN 1.15*sal
                   WHEN ‘SALESMAN‘   THEN 1.20*sal
       ELSE sal END REVISED_SALARY
FROM   emp;

条件表达式： DECODE函数

SELECT ename, job, sal,
       DECODE(job, ‘ANALYST‘, 1.10*sal,
                      ‘CLERK‘, 1.15*sal,
                      ‘SALESMAN‘,   1.20*sal,
              sal)
       REVISED_SALARY
FROM   emp;

函数的嵌套：单行函数能够在任何一个层次嵌套。

连接：
连接是将二个或多个表、视图或快照的行合并，每一返回行包含来自多个表的数据。每当在FROM子句中出现多表时ORACLE需要执行连接，WHERE子句决定如何连接。
在连接表时，应尽量避免迪卡尔乘积（Cartesian）。

如：SELECT   table1.column, table2.column
FROM   table1, table2
WHERE   table1.column1 = table2.column2;

连接：使用表的别名
SELECT e.empno, e.ename, e.deptno,
2 d.deptno, d.loc
3 FROM emp e, dept d
4 WHERE e.deptno=d.deptno;

连接：外连接(Outer Joins) —是对简单联接的扩充，它返回简单联接所返回的全部行，而且还返回一表中不与另一表的行相匹配的行。

外连接操作符时(+)加号（表示在该表中加入一个空行来与没有直接匹配行的数据进行匹配）
SQL> SELECT   e.ename, d.deptno, d.dname
2 FROM   emp e, dept d
3 WHERE   e.deptno(+) = d.deptno
4 ORDER BY   e.deptno;

自连接

SELECT worker.ename||‘ works for ‘||manager.ename
2 FROM emp worker, emp manager
3 WHERE worker.mgr = manager.empno;

WORKER.ENAME||‘WORKSFOR‘||MANAG
-------------------------------
BLAKE works for KING
CLARK works for KING
JONES works for KING
MARTIN works for BLAKE
...
13 rows selected.

连接：递归查询（树查询）。应用举例:（只提一下下）

SELECT lpad(ename,length(ename)+(level-1)*3,‘-‘) employee,level
FROM emp
START WITH ename = ‘KING‘
CONNECT BY PRIOR empno = mgr;