多表查询

 

1、多表连接和笛卡尔积

从多个表中查询数据，也可以称为多表连接
where子句应该设置有效连接条件，否则产生笛卡尔积，第一个表中的所有行连接到第二个表中的所有行，产生显示大量行，一是没有用，二是可能把temp表空间撑满。

 

 2、连接类型

等值连接

非等值连接

自连接

外连接

　　左外连接

　　右外连接

　　全外连接

 

 3、表连接原则

1>在写一个连接表的select语句时，在列前面加表别名可以使语义清楚，并且加快数据库访问

2>为了连接n个表在一起，你最少需要n-1个连接条件。

3>一个表的主键和外键的理解

4>建议用 JOIN  ON 的方式写语句，而不用where

4、等值连接

SQL>select * 
from scott.emp a,scott.dept b  
where a.deptno=b.deptno;

SQL>select * 
from scott.emp a join scott.dept b  
on a.deptno=b.deptno;   

 5、非等值连接

between and、>，<，<>，!=，^=，等。

SQL>select * 
from scott.emp a,scott.dept b  
where a.empno BETWEEN  b.deptno and a.empno;

SQL>select * 
from scott.emp a,scott.dept b  
where a.empno< b.deptno;

 6、左外连接（两种写法）

常用的是左外连接，以左表为基准，返回左表中的所有行，右表返回按条件匹配到的行，没匹配到的显示空值。

SQL>select A.ID AID,A.VALUE AV,B.ID BID,B.VALUE BV
FROM T1_LHR A
LEFT OUTER JOIN T2_LHR B    ----OUTER 可以省略
ON A.ID=B.ID
WHERE A.ID<=9;         ---过滤的写法

SQL>select A.ID AID,A.VALUE AV,B.ID BID,B.VALUE BV
FROM T1_LHR A,T2_LHR B
WHERE A.ID=B.ID(+);

 

7、右外连接

不常用，以右表为基准，返回右表中的所有行，左表返回按条件匹配到的行，没匹配到的显示空值。

SQL>select A.ID AID,A.VALUE AV,B.ID BID,B.VALUE BV
FROM T1_LHR A
RIGHT JOIN T2_LHR B   ----RIGHT OUTER JOIN，OUTER可以省略
ON A.ID=B.ID
WHERE A.ID<=9

SQL>select A.ID AID,A.VALUE AV,B.ID BID,B.VALUE BV
FROM T1_LHR A,T2_LHR B
WHERE A.ID(+)=B.ID

 

 8、全外连接

8.1 注意和笛卡尔积的不同之处

8.2 全外连接没有where的写法

SQL>select A.ID AID,A.VALUE AV,B.ID BID,B.VALUE BV
FROM T1_LHR A
FULL JOIN T2_LHR B   ----FULL OUTER JOIN，OUTER可以省略
ON A.ID=B.ID
WHERE A.ID<=9

 

 

 

 9、自连接

自连接(self join)：
    自连接就是连接的两个列均来自同一个表
    自连接是一种很少用的连接形式
    自连接可以是等值或非等值的连接

SQL>SELECT A.EMPNO,A.ENAME,A.MGR,B.ENAME
FROM SCOTT.EMP A,SCOTT.EMP B
WHERE A.MGR=B.EMPNO(+);

 

10、WITH语句

WITH语句可以理解为定义一些SQL的结果集为变量，然后直接引用。   

 

 

 11、扩展

如何快速构建一张大表
        select level,level from dual connect by level<=1000;
dual显示3行数据
        select * from dual connect by level<=3; 

12、多表连接再举个例子

想要查看dba_tables下的表的创建时间

SQL>SELECT DT.table_name,
       DT.tablespace_name,
       DT.owner,
       DO.created
FROM DBA_TABLES DT,DBA_OBJECTS DO
WHERE DT.TABLE_NAME=DO.OBJECT_NAME
AND DT.owner=DO.owner
ORDER BY DO.created desc ;

 

 

13、关于SQL学习

麦老师博客里的SQL50题等，全认真做一遍。