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题等,全认真做一遍。