常用数据操纵语言

浏览数：20 / 时间：2015年06月08日

DISTINCT：
   指定结果集中返回指定列存在不重复数据的记录
SELECT DISTINCT EMAIL FROM HR.EMPLOYEES;

ROWNUM:
   使用ROWNUM列来查询结果集中前N个记录
SELECT * FROM HR.EMPLOYEES WHERE ROWNUM<=3;

显示列别名：

SELECT EMP_NAME AS 姓名 FROM HR.EMPLOYEES;

设置查询条件：
SELECT SALARY FROM HR.EMPLOYEES WHERE SALARY>2000 AND SALARY <4000;

在查询条件中使用BETWEEN关键字
SELECT SALARY FROM HR.EMPLOYEES WHERE SALARY BETWEEN 2000 AND 4000;

在查询条件中使用IN关键字
   指定查询的取值列表
SYS AS SYSDBA@ORCL>SELECT FIRST_NAME,LAST_NAME, SALARY FROM HR.EMPLOYEES WHERE SALARY IN (‘2000‘,‘3000‘);

FIRST_NAME         LAST_NAME               SALARY
-------------------- ------------------------- ----------
Anthony          Cabrio                 3000
Kevin             Feeney                 3000

实现模糊查询：
   使用LIKE关键字和通配符实现模糊查询
%   包含零个或多个任意字符的字符串
-   任意单个字符
？   任意单个字符
#   表示0～9的数字
[]   指定范围或集合中的任意单个字符，[a~f]表示a~f中的一个字符

SYS AS SYSDBA@ORCL>SELECT FIRST_NAME,LAST_NAME,PHONE_NUMBER FROM HR.EMPLOYEES WHERE PHONE_NUMBER LIKE ‘%80%‘;

FIRST_NAME         LAST_NAME               PHONE_NUMBER
-------------------- ------------------------- --------------------
Shelley          Higgins               515.123.8080
Trenna             Rajs               650.121.8009

排序结果集：
ORDER BY {<排序表达式>[ASC|DESC]} [,...N]
SELECT FIRST_NAME,LAST_NAME,SALARY FROM HR.EMPLOYEES WHERE ROWNUM<=10 ORDER BY SALARY DESC

对多列进行排序：
SELECT FIRST_NAME,LAST_NAME,SALARY,PHONE_NUMBER FROM HR.EMPLOYEES WHERE ROWNUM<=10 ORDER BY SALARY,PHONE_NUMBER;

使用分组统计：
   使用GROUP BY子句指定查询结果的分组条件
   选择列表中每一个非聚合表达式内的所有列都应包含在GROUP BY列表中,GROUP BY 表达式必须与选择列表表达式完全匹配
GROUP BY [ALL] <分组表达式>[,....n]
SYS AS SYSDBA@ORCL>SELECT DEPARTMENT_ID,AVG(SALARY) FROM HR.EMPLOYEES GROUP BY DEPARTMENT_ID;
DEPARTMENT_ID AVG(SALARY)
------------- -----------
      100 8601.33333
       30         4150

HAVING
   HAVING通常与GROUP BY 子句一起使用，作用与WHERE子句一样
   HAVING:子句搜索条件在进行分组操作之后应用
   WHERE:子句搜索条件在进行分组之前应用
SYS AS SYSDBA@ORCL>SELECT DEPARTMENT_ID,AVG(SALARY) FROM HR.EMPLOYEES GROUP BY DEPARTMENT_ID HAVING AVG(SALARY)>4000;

DEPARTMENT_ID AVG(SALARY)
------------- -----------
      100 8601.33333
       30         4150
             7000
       20         9500

连接查询：
   内连接使用比较运算符(=号),包含空值的列不与任何值匹配，不能包含在结果集中，空值不与其他的空值匹配
SYS AS SYSDBA@ORCL>SELECT T1.FIRST_NAME,T1.LAST_NAME,T2.DEPARTMENT_NAME FROM HR.DEPARTMENTS T2,HR.EMPLOYEES T1 WHERE T1.DEPARTMENT_ID=T2.DEPARTMENT_ID;

SYS AS SYSDBA@ORCL>SELECT T1.FIRST_NAME,T1.LAST_NAME,T2.DEPARTMENT_NAME FROM HR.DEPARTMENTS T2 INNER JOIN HR.EMPLOYEES T1 ON T1.DEPARTMENT_ID=T2.DEPARTMENT_ID;

   外连接：主表的每一行数据去匹配从表中的数据列，符合的返回到结果集中，不符合的，将被填上空值后返回到结果集中

   LEFT [OUTER] JOIN左向外连接：连接子句左侧的表为主表，主表中的所有记录都将出现在结果集中，如果主表中的记录在右表中没有匹配的数据，则结果集中右表的列值为空
   SYS AS SYSDBA@ORCL>SELECT T1.DEPARTMENT_NAME,T2.FIRST_NAME FROM HR.DEPARTMENTS T1 LEFT OUTER JOIN HR.EMPLOYEES T2 ON T1.DEPARTMENT_ID=T2.DEPARTMENT_ID;

   RIGHT [OUTER] JOIN右向外连接:子句右侧的表为主表，主表中所有记录都将出现在结果集中，如果主表中的记录在左表中没有匹配的数据，则结果集中右表的列
   SYS AS SYSDBA@ORCL>SELECT T1.FIRST_NAME,T2.DEPARTMENT_NAME FROM HR.EMPLOYEES T1 RIGHT OUTER JOIN HR.DEPARTMENTS T2 ON T1.DEPARTMENT_ID=T2.DEPARTMENT_ID;

   FULL [OUTER] JOIN完整外部连接：   连接包括连接表中的所有行，无论它们是否匹配(相关于左向外连接与右向外连接的并集)
SYS AS SYSDBA@ORCL>SELECT T1.FIRST_NAME,T2.DEPARTMENT_NAME FROM HR.EMPLOYEES T1 FULL OUTER JOIN HR.DEPARTMENTS T2 ON T1.DEPARTMENT_ID=T2.DEPARTMENT_ID;

   交叉连接：不常用，两个表中的每两行都可能互相组合成为结果集中的一行，一般用于穷举两个表的所有可能的记录组合
   SELECT T1.FIRST_NAME,T2.DEPARTMENT_NAME FROM HR.EMPLOYEES T1 CROSS JOIN HR.DEPARTMENTS T2
   查询不存在员工的部门:
   SYS AS SYSDBA@ORCL>SELECT T1.FIRST_NAME,T2.DEPARTMENT_NAME FROM HR.EMPLOYEES T1 RIGHT OUTER JOIN HR.DEPARTMENTS T2 ON T1.DEPARTMENT_ID=T2.DEPARTMENT_ID WHERE T1.FIRST_NAME IS NULL;

   使用简单的子查询：
   子查询就是在一个SELECT语句中又嵌套一个SELECT语句
   SYS AS SYSDBA@ORCL>SELECT FIRST_NAME,LAST_NAME,SALARY FROM HR.EMPLOYEES WHERE DEPARTMENT_ID=(SELECT DEPARTMENT_ID FROM HR.DEPARTMENTS WHERE DEPARTMENT_NAME=‘Marketing‘);

   查询所有低于平均工资的员工：
SYS AS SYSDBA@ORCL>SELECT FIRST_NAME,SALARY FROM HR.EMPLOYEES WHERE SALARY<(SELECT AVG(SALARY) FROM HR.EMPLOYEES);

   IN关键字与返回多值的子查询
   SYS AS SYSDBA@ORCL>SELECT FIRST_NAME,LAST_NAME,SALARY FROM HR.EMPLOYEES WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM HR.DEPARTMENTS WHERE DEPARTMENT_NAME=‘Marketing‘ OR DEPARTMENT_NAME=‘Purchasing‘);


   使用UNION关键字的合并查询:
   将两个或更多查询的结果给合为单个结果集,结果集包含联合查询中的所有查询的全部行,会自动过滤重复的记录
   SYS AS SYSDBA@ORCL>SELECT DEPARTMENT_ID,DEPARTMENT_NAME FROM HR.DEPARTMENTS
        UNION   SELECT DEPARTMENT_ID,FIRST_NAME FROM HR.EMPLOYEES WHERE SALARY>1000;
   UNION ALL: 将不会过滤重复的记录

在SELECT语句中使用DECODE函数：
   将输入值和函数职的参数列表相比较，根据输入值返回一个对应值，如果参数列表中没有对应值，则返回默认值
   DECODE(<输入值>,<值1>，<结果1>[,<值2>，<结果2>][,<默认值>])
   SYS AS SYSDBA@ORCL>SELECT FIRST_NAME,DECODE(SALARY,‘2600‘,‘LOW‘,‘10000‘,‘HIGH‘,‘VERY_LOW‘)AS SALARY FROM HR.EMPLOYEES;

在SELECT 语句中使用CASE函数(与DECODE函数功能相同)
   CASE<输入值>WHEN<值1>THEN<结果1>
   [WHEN<值2>THEN<结果2>]
   [ELSE<默认结果>]
   END
SYS AS SYSDBA@ORCL>SELECT FIRST_NAME,CASE SALARY WHEN 2600 THEN ‘LOW‘ WHEN 10000 THEN ‘HIGH‘ ELSE ‘VERY_LOW‘ END AS SALARY FROM HR.EMPLOYEES;
   CASE
   WHEN<逻辑表达式1>THEN<结果1>
   [WHEN<逻辑表达式2>THEN<结果2>]
   [ELSE<默认结果>]
   END

SELECT FIRST_NAME,CASE WHEN SALARY<=2000 THEN ‘LOW‘ WHEN SALARY>=3000 AND SALARY<=10000 THEN ‘HIGH‘ ELSE ‘VERY_LOW‘ END AS SALARY FROM HR.EMPLOYEES;

保存查询结果：
   将查询结果保存到一个新表中
   CREATE TABLE <新表名> AS
   <SELECT 子句>
   <FROM 子句>
   <WHERE 子句>
   SYS AS SYSDBA@ORCL>CREATE TABLE HR.EMP_USER AS SELECT FIRST_NAME,LAST_NAME,SALARY FROM HR.EMPLOYEES;
   SYS AS SYSDBA@ORCL>DESC HR.EMP_USER;
Name                                   Null?    Type
----------------------------------------------------------------- -------- --------------------------------------------
FIRST_NAME                                    VARCHAR2(20)
LAST_NAME                               NOT NULL VARCHAR2(25)
SALARY                                     NUMBER(8,2)