Oracle Database ：Oracle11g SQL开发指南学习笔记之（5）：从数据库表中检索信息

1、理解行标志和行号（ROWID 和 ROWNUM）；

SQL> select rowid , rownum , ename from emp ;

ROWID                  ROWNUM ENAME
------------------ ---------- ----------
AAAR3sAAEAAAACXAAA          1 SMITH
AAAR3sAAEAAAACXAAB          2 ALLEN
AAAR3sAAEAAAACXAAC          3 WARD
AAAR3sAAEAAAACXAAD          4 JONES
AAAR3sAAEAAAACXAAE          5 MARTIN

2、列运算：

SQL> select ename , sal , sal*2+1 from emp;

ENAME             SAL    SAL*2+1
---------- ---------- ----------
SMITH             880       1761
ALLEN            1600       3201
WARD             1250       2501
JONES            2975       5951
MARTIN           1250       2501
BLAKE            2850       5701

3、使用列别名：

SQL> select sal*2 double_sal , sal*2 "double sal" , sal*2  as double_sal , sal from emp;

DOUBLE_SAL double sal DOUBLE_SAL        SAL
---------- ---------- ---------- ----------
      1760       1760       1760        880
      3200       3200       3200       1600
      2500       2500       2500       1250
      5950       5950       5950       2975
      2500       2500       2500       1250

注意：默认情况下，Oracle会去掉别名中的空格，并使用大写格式将别名显示出来，如果希望使用空格并保持文本的大小写格式，就必须使用双引号将别名文本引起来；在别名之前也可以家as关键字；


4、使用连接操作合并列输出的结果：

SQL> select ename||' '||job  as person_job from emp;

PERSON_JOB
--------------------
SMITH CLERK
ALLEN SALESMAN
WARD SALESMAN
JONES MANAGER


5、理解控制NULL；

SQL> select * from emp where comm is null;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        880                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30



SQL> select ename , comm ,nvl(comm , 0) as Unkonwn_Comm from emp  where comm is null;

ENAME            COMM UNKONWN_COMM
---------- ---------- ------------
SMITH                            0
JONES                            0
BLAKE                            0
CLARK                            0
SCOTT                            0
KING                             0
ADAMS                            0

6、禁止重复显示行；
SQL> select distinct deptno from emp;

    DEPTNO
----------
        30
        20
        10

7、比较值：SOME等同于any，应该使用any代替some，因为any范围更广泛，更易读懂；

= 、<> or != 、< 、>、<=、>=、ANY、SOME、ALL；

SQL> select * from emp where sal > 2000;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       6000                    10
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

6 rows selected.

--sal>any(2000,3000,4000)：表示sal大于三个数中任何一个就行，
--换句话说，如果表中有大于2000而小于3000的，那么这条语句就等同于：
--select * from emp where sal>2000；
--如果表中没有大于2000而小于3000的，但是存在大于3000而小于4000的，
--那么这条语句就等同于：select * from emp where sal>3000；
--当然，如果表中最小的数都大于4000，那么这条语句就等同于：
--select * from emp where sal>4000；
SQL> select * from emp where sal>any(2000,3000,4000);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       6000                    10
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

6 rows selected.

SQL> select * from emp where sal>all(2000,3000,4000);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       6000                    10

8、使用SQL操作符：

 【NOT】LIKE ； 【NOT】IN; 【NOT】BETWEEN ;IS 【NOT】NULL,
IS 【NOT】NAN;IS 【NOT】INFINITE;

SELECT * FROM EMP WHERE ENAME LIKE '_%R%';

SELECT * FROME PROMOTIONS WHERE NAME 【NOT】 LIKE '%\%%' ESCAPE '\';
--ESCAPE '\':escape后面的字符告诉数据库如何区分要搜索的字符与通配符，在此范围中使用反斜杠（\)；第一个%是通配符，第二个%是实际搜索的字符--，第三个%是通配符；这个子句可以查出向：10%这样的字符串；


9、使用逻辑操作符：
x AND y； x OR y；NOT x；
AND的优先级大于OR；
比较操作符的优先级大于AND；

select * from emp where job='MANAGER' or sal>3000;

10、ORDER BY对子句进行排序：

ORDER BY必须位于FROM 或者 WHERE（如果提供了EHRER）之后 ；

SQL> select * from emp order by deptno , sal desc;
解释：在EMP中查找结果，结果是按照deptno升序排列，而同一个deptno总的结果又是按照降序排列；

在ORDER BY子句中，也可以根据列的次序指定对那一列进行排序，默认是按照升序排列，1表示第一列，2表示第二列，以此类推；

select * from emp order by 1 desc;
select ename , sal from emp order by 2 asc;

10、连接条件和连接类型；
1）、连接条件：等连接（equijoin）和不等连接（non_equijoin）；
2）、连接类型：
㈠、内连接（inner join）：只有当连接中的列包含满足条件的值时才会返回一行；这就是说，如果某一行的连接条件中的一列是空值，那么这列不会返回，如果不加任何操作符，oracle默认是内连接；
㈡、外连接（outer join）：即使连接条件的一列包含空值也返回；
㈢、自连接（self join）；返回连接到同一个表中的行；


⒈外连接：即使连接中的列包含一个空值，外连接也会返回一行；Oracle特有的外连接操作符是圆括号括起来的加号：(+);
select e.ename , e.sal ,d.dname from emp e , dept  d where e.deptno=d.deptno (+) ;
①、注意：虽然可以将外连接操作符放在连接操作符的任何一边，但是通常都应该将其放在想要搜索的行中包含空值的列的相反的一边；这样，即使某一列有空值，也会在结果里返回；

例如：下面的表中，products表中的列：product_type_id包含空值，那么就将外连接操作符放在product_type表中的product_type_id列的一边；

select p.name , pt.name
from products p , product_type pt
where  p.product_type_id = pt.product_type_id (+)
order by p.name;


上面的语句和下面的语句相同：

select p.name , pt.name
from products p , product_type pt
where  pt.product_type_id (+) = p.product_type_id
order by p.name;

当然：这是使用Oracle特殊的外连接操作符来进行外连接的，在一般情况下建议使用SQL/92标准的操作符进行外连接操作；

②、左外连接和右外连接 ；
1）、左外连接： 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。
注意：Oracle左外联接种，外连接操作符实际上是位于连接操作符的右边；

例如；
select p.name , pt.name
from products p , product_type pt
where  p.product_type_id = pt.product_type_id (+)
order by p.name;

2）、右外连接：关键字会右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有匹配的行。
注意：Oracle右外联接种，外连接操作符实际上是位于连接操作符的左边；
例如：

select p.name , pt.name
from products p , product_type pt
where  pt.product_type_id (+) = p.product_type_id
order by p.name;



③、外连接限制：
1）、只能在连接的一端使用外连接操作符，而不能两端都使用。如果这样的话，会出错；
2）、不能同时使用一个外连接条件和一个IN操作符；
3）、不能同时使用一个外连接条件和一个OR操作符的连接条件；

④、自连接：自连接是对同一张表进行的连接，要执行一个自连接，必须使用不同的表别名来标识在查询中每次对表的引用；

11、使用SQL/92标准执行两张表的内连接；

SQL/86：

select p.name , pt.name
from productd p , product_type pt
where p.product_type_id = pt.product_type_id
order by p.name;

SQL/92:引入了INNER JOIN 和ON子句来执行内连接；

SELECT P.NAME , PT.NAME
FROM PRODUCTS P INNER JOIN PRODUCT_TYPE PT
ON P.PRODUCT_TYPE_ID = PT.PRODUCT_TYPE_ID
ORDER BY P.NAME;

不等连接操作符和ON子句可以同时使用。

SQL/86:
SELECT E.FIRST_NAME , E.LAST_NANEM , E.TITTLE , E.SALARY ,SG.SALARY_GRADE_ID
FROM EMPLOYEES E , SALARY_GRADES SG
WHERE E.SALARY BETWEEN SG.LOW_SALARY AND SG.HIGE_SALARY
ORDER BY SALARY_GRADE_ID;

SQL/92:

SELECT E.FIRST_NAME , E.LAST_NANEM , E.TITTLE , E.SALARY ,SG.SALARY_GRADE_ID
FROM EMPLOYEES E INNER JOIN SALARY_GRADES SG
ON E.SALARY BETWEEN SG.LOW_SALARY AND SG.HIGE_SALARY
ORDER BY SALARY_GRADE_ID;


12、使用USING关键字简化连接：
使用USING关键字需要一定的条件：
①、查询必须是等连接的；
②、连接中的列必须同名；

SELECT P.NAME , PT.NAME
FROM PRODUCTS P INNER JOIN PRODUCT_TYPE PT
USING(PRODUCT_TYPE_ID)
ORDER BY P.NAME;

如果希望查看PRODUCT_TYPE_ID的值，那么在SELECT子句中只能指定该列名称，不能使用表明或别名；


SELECT P.NAME , PT.NAME ，PRODUCT_TYPE_ID
FROM PRODUCTS P INNER JOIN PRODUCT_TYPE PT
USING(PRODUCT_TYPE_ID)
ORDER BY P.NAME;

如果试图这样写：
SELECT P.NAME , PT.NAME ，P.PRODUCT_TYPE_ID
FROM PRODUCTS P INNER JOIN PRODUCT_TYPE PT
USING(PRODUCT_TYPE_ID)
ORDER BY P.NAME;
就会出错；

在USING子句中也只能使用单独的列名；

如果这样写：
SELECT P.NAME , PT.NAME ，PRODUCT_TYPE_ID
FROM PRODUCTS P INNER JOIN PRODUCT_TYPE PT
USING(P.PRODUCT_TYPE_ID)
ORDER BY P.NAME;
也会出错；


13、使用SQL/92执行多于两张表的内连接；

SQL/86：

SELECT C.FIRST_NAME , C.LAST_NAME ,P.NAME AS PRODUCT ,
PT.NAME AS TYPE
FROM CUSTOMERS C , PURCHASES PR , PRODUCTS P ,PRODUCT_TYPES PT
WHERE C.CUSTOMER_ID = PR.CUSTOMER_ID
AND P.PRODUCT_TYPE = PR.PRODUCT_TYPE
AND P.PRODUCT_TYPE_ID = PT.PRODUCT_TYPE_ID
ORDER BY P.NAME;


SQL/92:

SELECT C.FIRST_NAME , C.LAST_NAME ,P.NAME AS PRODUCT ,
PT.NAME AS TYPE
FROM CUSTOMERS C INNER JOIN PURCHASES PR
USING(CUSTOMER_ID)
INNER JOIN PRODUCTS P
USING(PRODUCT_ID)
INNER JOIN PRODUCT_TYPES PT
USINT(PRODUCT_TYPE_ID)
ORDER BY P.NAME;

14、使用SQL/92执行多列的内连接；：如果连接使用了两个表中的多个列，那么就可以在ON子句中使用AND操作逐一列出这些列；

SQL/86;

SELECT ....
FROM TALBEONE INNER JOIN TALBLETWO
ON TABLEONE.COLUMNONE = TABLETWO.COLUMNONE
AND TABLEONE.COLUMNTWO = TABLETWO.COLUMNTWO;

SQL/92;

SELECT ....
FROM TALBEONE INNER JOIN TALBLETWO
USING(COLUMEONE , COLUMNTWO);


15、用SQL/92执行外连接；

SELECT ...
FROM TABLE1 { LEFT | RIGHT | FULL } OUTER JOIN TABLE2
ON ...

或者

USING(...);



LEFT OUTER JOIN:左外连接；
RIGHT OUTER JOIN：右外连接；
FULL OUTER JOIN：说明希望执行全外连接，只要其中某个表存在匹配，FULL JOIN 关键字就会返回行，返回两张表所有行，包括连接列值为空的行；不能使用(+)来执行全外连接；


㈠、使用SQL/92执行左外连接；


ORACLE:

select p.name , pt.name
from products p , product_type pt
where  p.product_type_id = pt.product_type_id (+)
order by p.name;

SQL/92:

SELECT P.NAME , PT.NAME
FROM PRODUCTS P LEFT OUTER JOIN PRODUCT_TYPE PT
USING(PRODUCT_TYPE_ID)
ORDER BY P.NAME;

㈡、使用SQL/92执行右外连接：

ORACLE：

select p.name , pt.name
from products p , product_type pt
where  pt.product_type_id (+) = p.product_type_id
order by p.name;

SQL/92：

SELECT P.NAME , PT.NAME
FROM PRODUCTS P RIGHT OUTER JOIN PRODUCT_TYPE PT
USING(PRODUCT_TYPE_ID)
ORDER BY P.NAME;


㈢、使用SQL/92执行全外连接：

SQL/92：

SELECT P.NAME , PT.NAME
FROM PRODUCTS P FULL OUTER JOIN PRODUCT_TYPE PT
USING(PRODUCT_TYPE_ID)
ORDER BY P.NAME;


16、使用SQL/92执行自连接：

ORACLE：

SELECT W.LAST_NAME||' WORKS FOR '||M.LAST_NAME
FROM EMPLOYEES W , EMPLYEES M
WHERE W.MANAGER_ID = M.EMPLOYEE_ID;


SQL/92:

SELECT W.LAST_NAME||' WORKS FOR '||M.LAST_NAME
FROM EMPLOYEES W INNER JOIN EMPLYEES M
ON W.MANAGER_ID = M.EMPLOYEE_ID;

17、使用SQL/92进行交叉连接：

如果你的确像使用笛卡尔集，SQL/92表中要求必须在查询中使用CROSS JOIN关键字显示的声明；

SQL/92：

SELECT * FROM PRODUCT_TYPES CROSS JOIN PRODUCTS;