oracle常用数据操纵语言

re2212

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)