Oracle高级查询

²  消除重复行

 

在Oracle查询中结果中，可能出现若干行相同的情况，那么可以使用DISTINCT消除重复行。具体的用法如示例：

 

代码演示：DISTINCT消除重复行

SQL> SELECT DISTINCT DEPTNO FROM EMP; DEPTNO ------     30     20     10

 

²  NULL操作

 

如果某条记录中有缺少的数据值，就是空值（NULL值）。空值不等于0或者空格，空值是指未赋值、未知或不可用的值。任何数据类型的列都可以包括NULL值，除非该列被定义为非空或者主键。

        

代码演示：EMP中的NULL值

SQL> SELECT ENAME,JOB,SAL,COMM FROM EMP WHERE SAL<2000; ENAME JOB SAL COMM SMITH CLERK 800   ALLEN SALESMAN 1600 300 WARD SALESMAN 1250 500 MARTIN SALESMAN 1250 1400 TURNER SALESMAN 1500 0 ADAMS CLERK 1100   JAMES CLERK 950   7 rows selected

 

         在查询条件中NULL值用IS NULL作条件，非NULL值用NOT IS NULL做条件。   

 

案例4：查询EMP表中没有发奖金的员工。

 

代码演示：NULL值查询

SQL> SELECT ENAME,JOB,SAL,COMM FROM EMP 2   WHERE SAL<2000 AND COMM IS NULL;  ENAME JOB SAL COMM SMITH CLERK 800   ADAMS CLERK 1100   JAMES CLERK 950   MILLER CLERK 1300

 

²  IN 操作

 

在Where子句中可以使用IN操作符来查询其列值在指定的列表中的行。比如：查询出工作职责是SALESMAN、PRESIDENT或者ANALYST的员工。条件有两种表示方法：

1.       WHERE job = 'SALESMAN ' OR job = 'PRESIDENT ' OR job = 'ANALYST '

2.       WHERE job IN ('SALESMAN', 'PRESIDENT', 'ANALYST')

 

代码演示：IN操作

SQL> SELECT ENAME,JOB,SAL FROM EMP 2  WHERE job IN ('SALESMAN', 'PRESIDENT', 'ANALYST'); ENAME JOB SAL ALLEN SALESMAN 1600 WARD SALESMAN 1250 MARTIN SALESMAN 1250 SCOTT ANALYST 3000 KING PRESIDENT 5000 TURNER SALESMAN 1500 FORD ANALYST 3000 7 rows selected

 

         对应IN操作的还有NOT IN，用法一样，结果相反。

 

²  BETWEEN…AND…

 

在WHERE子句中，可以使用BETWEEN操作符来查询列值包含在指定区间内的行。比如，查询工资从1000到2000之间的员工。可以使用传统方法：

        

         WHERE SAL>=1000 AND SAL<=2000

        

         也可以使用：

             WHERE SAL BETWEEN 1000 AND 2000

         BWTWEEN操作所指定的范围也包括边界。

 

代码演示：BETWEEN操作

SQL> SELECT ename,job,sal FROM EMP WHERE sal BETWEEN 1000 AND 2000; ENAME JOB SAL ALLEN SALESMAN 1600 WARD SALESMAN 1250 MARTIN SALESMAN 1250 TURNER SALESMAN 1500 ADAMS CLERK 1100 MILLER CLERK 1300 6 rows selected

 

²  LIKE模糊查询

 

在一些查询时，可能把握不准需要查询的确切值，比如百度搜索时输入关键字即可查询出相关的结果，这种查询称为模糊查询。模糊查询使用LIKE关键字通过字符匹配检索出所需要的数据行。字符匹配操作可以使用通配符“%”和“_”:

Ø  %：表示零个或者多个任意字符。

Ø  _：代表一个任意字符。

 

语法是：LIKE '字符串'[ESCAPE '字符']。匹配的字符串中，ESCAPE[D12] 后面的“字符”作为转义字符。与一期SQLServer中ESCAPE用法相同。

 

通配符表达式	说明
'S%'	以S开头的字符串。
'_S%'	第二个字符时S的字符串。
'%30\%%' escape '\'	包含“30%”的字符串，“\”指转义字符，“\%”在字符串中表示一个字符“%”。

表4  通配符示例

 

案例5：显示员工名称以J开头以S结尾的员工的姓名、工资和工资。

 

代码演示：LIKE操作

SQL> SELECT ENAME,JOB,SAL FROM EMP WHERE ENAME LIKE 'J%S';   ENAME      JOB             SAL ----------   ---------        --------- JONES      MANAGER     2975.00 JAMES      CLERK         950.00

 

²  集合运算

 

集合运算就是将两个或者多个结果集组合成为一个结果集。集合运算包括：

Ø  INTERSECT[D13] (交集)，返回两个查询共有的记录。

Ø  UNION ALL(并集)，返回各个查询的所有记录，包括重复记录。

Ø  UNION(并集)，返回各个查询的所有记录，不包括重复记录。

Ø  MINUS[D14] (补集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。

 

当使用集合操作的时候，要注意：查询所返回的列数以及列的类型必须匹配，列名可以不同。

        

         案例6：查询出dept表中哪个部门下没有员工。只需求出dept表中的部门号和emp表中的部门号的补集即可。

 

代码演示：求补运算

SQL> SELECT DEPTNO FROM DEPT   2  MINUS   3  SELECT DEPTNO FROM EMP; DEPTNO ------     40

 

         前面学习过可以通过insert into …select把一个结果集插入到另一张结构相同的表中，因此可以使用union把若干条记录一次性插入到一张表中。

 

代码演示：用union插入多条数据

SQL> INSERT INTO DEPT   2  SELECT 50,'公关部','台湾' FROM DUAL   3  UNION   4  SELECT 60,'研发部','西安' FROM DUAL   5  UNION   6  SELECT 70,'培训部','西安' FROM DUAL   7  / 3 rows inserted

 

²  连接查询

 

在SQL Server中已经学习过内联接(inner join)、外联接(outer join)，外联接又分为左外联接(left outer join)和右外联接(right outer join)。Oracle中对两个表或者若干表之间的外联接用（+）表示。

 

案例7：请查询出工资大于2000元的，员工姓名，部门，工作，工资。

 

由于部门名称在dept中，其他的信息在emp表中，需要内联接才能完成。

 

代码演示：内联接

SQL> SELECT e.ENAME,e.JOB,e.SAL,d.DNAME   2  FROM emp e,dept d   3  WHERE e.deptno=d.deptno   4  AND e.SAL>2000; ENAME JOB SAL DNAME JONES MANAGER 2975 RESEARCH BLAKE MANAGER 2850 SALES CLARK MANAGER 2450 ACCOUNTING SCOTT ANALYST 3000 RESEARCH KING PRESIDENT 5000 ACCOUNTING FORD ANALYST 3000 RESEARCH 6 rows selected

 

         也可以使用SQL/92标准中的内联接：

 

代码演示：内联接

SELECT e.ENAME,e.JOB,e.SAL,d.DNAME  FROM EMP e INNER JOIN DEPT d ON e.DEPTNO=d.DEPTNO WHERE e.SAL>2000

 

这里INNER JOIN中，关键字INNER可以省略。

        

案例8：请查询出每个部门下的员工姓名，工资。

案例分析：

Emp表用外键deptno引用Dept表中的deptno，在Dept表中如果有某些部门没有员工，那么用内联接，没有员工的部门将无法显示，因此必须以Dept表为基准的外联接。

 

代码演示：外联接

SQL> SELECT e.ENAME,e.JOB,e.SAL,d.DNAME   2  FROM EMP e ,DEPT d   3  WHERE  e.DEPTNO(+)=d.DEPTNO  ①   4  / ENAME JOB SAL DNAME SMITH CLERK 800 RESEARCH ALLEN SALESMAN 1600 SALES WARD SALESMAN 1250 SALES JONES MANAGER 2975 RESEARCH MARTIN SALESMAN 1250 SALES BLAKE MANAGER 2850 SALES CLARK MANAGER 2450 ACCOUNTING SCOTT ANALYST 3000 RESEARCH KING PRESIDENT 5000 ACCOUNTING TURNER SALESMAN 1500 SALES ADAMS CLERK 1100 RESEARCH JAMES CLERK 950 SALES FORD ANALYST 3000 RESEARCH MILLER CLERK 1300 ACCOUNTING       公关部       研发部       培训部       OPERATIONS    18 rows selected

代码解析：

①   (+)：Oracle专用的联接符，在条件中出现在左边指右外联接，出现在右边指左外联接。

 

         也可以使用SQL/92标准的写法：

 

代码演示：外联接

SELECT e.ENAME,e.JOB,e.SAL,d.DNAME FROM EMP e RIGHT OUTER JOIN DEPT d ON e.DEPTNO=d.DEPTNO

 

         这里RIGHT OUTER JOIN中，关键字OUTER可以省略。

 

虽然Oracle自身的联接查询语法比较好写，同时容易理解，但是为了程序便于移植，推荐使用SQL/92表中的联接查询。同时也可以与SQL Server获得一致。