从多个表中显示数据.ppt

从多个表中显示数据,-Itpub技术培训 Eygle 2003-06,使用相等和不等连结写SELET语句来访问多个表 使用外连接查询数据 表的自连接,EMPNO DEPTNO LOC - - - 7839 10 NEW YORK 7698 30 CHICAGO 7782 10 NEW YORK 7566 20 DALLAS 7654 30 CHICAGO 7499 30 CHICAGO . 14 rows selected.,从多个表中获得数据,EMP,DEPT,EMPNO ENAME . DEPTNO - - . - 7839 KING . 10 7698 BLAKE . 30 . 7934 MILLER . 10,DEPTNO DNAME LOC - - - 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON,什么是连接,使用连接从多个表中查询数据 在WHERE子句中写连接条件 在多个表中具有相同的列名,SELECT table.column, table.column FROM table1, table2 WHERE table1.column1 = table2.column2;,笛卡尔结果,笛卡尔结果形成于: 连接条件被省略 连接条件无效 第一个表的所有记录连接到第二个表的所有记录 为了避免笛卡尔结果我们总是在WHERET子句中使用有效连接,生成笛卡尔结果,ENAME DNAME - - KING ACCOUNTING BLAKE ACCOUNTING . KING RESEARCH BLAKE RESEARCH . 56 rows selected.,EMP (14 rows),DEPT (4 rows),EMPNO ENAME . DEPTNO - - . - 7839 KING . 10 7698 BLAKE . 30 . 7934 MILLER . 10,DEPTNO DNAME LOC - - - 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON,连接的类型,Equijoin,Non-equijoin,Outer join,Self join,什么是等值连接,EMP,DEPT,EMPNO ENAME DEPTNO - - - 7839 KING 10 7698 BLAKE 30 7782 CLARK 10 7566 JONES 20 7654 MARTIN 30 7499 ALLEN 30 7844 TURNER 30 7900 JAMES 30 7521 WARD 30 7902 FORD 20 7369 SMITH 20 . 14 rows selected.,DEPTNO DNAME LOC - - - 10 ACCOUNTING NEW YORK 30 SALES CHICAGO 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICAGO 20 RESEARCH DALLAS 20 RESEARCH DALLAS . 14 rows selected.,使用等值连接所获得的记录,SQL SELECT emp.empno, emp.ename, emp.deptno, 2 dept.deptno, dept.loc 3 FROM emp, dept 4 WHERE emp.deptno=dept.deptno;,EMPNO ENAME DEPTNO DEPTNO LOC - - - - - 7839 KING 10 10 NEW YORK 7698 BLAKE 30 30 CHICAGO 7782 CLARK 10 10 NEW YORK 7566 JONES 20 20 DALLAS . 14 rows selected.,限定列名,使用表名作为前缀在多个表中指定列名 使用表前缀改进性能 使用列的别名以区分不同表的同名列,使用AND运算符附加查询条件,EMP,DEPT,EMPNO ENAME DEPTNO - - - 7839 KING 10 7698 BLAKE 30 7782 CLARK 10 7566 JONES 20 7654 MARTIN 30 7499 ALLEN 30 7844 TURNER 30 7900 JAMES 30 7521 WARD 30 7902 FORD 20 7369 SMITH 20 . 14 rows selected.,DEPTNO DNAME LOC - - - 10 ACCOUNTING NEW YORK 30 SALES CHICAGO 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICAGO 20 RESEARCH DALLAS 20 RESEARCH DALLAS . 14 rows selected.,使用表的别名,使用别名简化查询,SQL SELECT emp.empno, emp.ename, emp.deptno, 2 dept.deptno, dept.loc 3 FROM emp, dept 4 WHERE emp.deptno=dept.deptno;,SQL SELECT e.empno, e.ename, e.deptno, 2 d.deptno, d.loc 3 FROM emp e, dept d 4 WHERE e.deptno=d.deptno;,多表联接,NAME CUSTID - - JOCKSPORTS 100 TKB SPORT SHOP 101 VOLLYRITE 102 JUST TENNIS 103 K+T SPORTS 105 SHAPE UP 106 WOMENS SPORTS 107 . . 9 rows selected.,CUSTOMER,非等值连接,EMP,SALGRADE,EMPNO ENAME SAL - - - 7839 KING 5000 7698 BLAKE 2850 7782 CLARK 2450 7566 JONES 2975 7654 MARTIN 1250 7499 ALLEN 1600 7844 TURNER 1500 7900 JAMES 950 . 14 rows selected.,GRADE LOSAL HISAL - - - 1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999,使用非等值连接获取记录,ENAME SAL GRADE - - - JAMES 950 1 SMITH 800 1 ADAMS 1100 1 . 14 rows selected.,SQL SELECT e.ename, e.sal, s.grade 2 FROM emp e, salgrade s 3 WHERE e.sal 4 BETWEEN s.losal AND s.hisal;,外连接,EMP,DEPT,ENAME DEPTNO - - KING 10 BLAKE 30 CLARK 10 JONES 20 .,DEPTNO DNAME - - 10 ACCOUNTING 30 SALES 10 ACCOUNTING 20 RESEARCH . 40 OPERATIONS,外连接,使用外连接看到不满足连接条件的记录 外连接运算符是加号(+),SELECT table.column, table.column FROM table1, table2 WHERE table1.column(+) = table2.column;,SELECT table.column, table.column FROM table1, table2 WHERE table1.column = table2.column(+);,使用外连接,SQL SELECT e.ename, d.deptno, d.dname 2 FROM emp e, dept d 3 WHERE e.deptno(+) = d.deptno 4 ORDER BY e.deptno;,ENAME DEPTNO DNAME - - - KING 10 ACCOUNTING CLARK 10 ACCOUNTING . 40 OPERATIONS 15 rows selected.,自连接,EMP (WORKER),EMP (MANAGER),EMPNO ENAME MGR - - - 7839 KING 7698 BLAKE 7839 7782 CLARK 7839 7566 JONES 7839 7654 MARTIN 7698 7499 ALLEN 7698,EMPNO ENAME - - 7839 KING 7839 KING 7839 KING 7698 BLAKE 7698 BLAKE,表的自连接,WORKER.ENAME|WORKSFOR|MANAG - BLAKE works for KING CLARK works for KING JONES works for KING MARTIN works for BLAKE . 13 rows selected.,SQL SELECT worker.ename| works for |manager.ename 2 FROM emp worker, emp manager 3 WHERE worker.mgr = manager.empno;,小结,Equijoin,Non-equijoin,Outer join,Self join,SELECT table.column, table.column FROM table1, table2 WHERE table1.column1 = table2.column2;,支持SQL1999,包括以下新的TABLE JOIN的句法结构 CROSS IN它在两个表格中创建了一个卡迪尔列，就象是在Oracle8i中没写WHERE时那样。 NATURAL JOIN这是一个很有用的Oracle9i的句法，它通过从WHERE子句中移动连接标准来改善SQL的稳定性 USING子句它可以通过名字来具体指定连接,-,ON子句这个句法允许在两个表格中为连接具体指定栏目的名头 LEFT OUTER JOIN它返回表格中左边的行和右边的数值，如果没有搭配的行的话，则返回零 RIGHT OUTER JOIN它返回表格中右边的行和左边的数值，如果没有搭配的行的话，则返回零 FULL OUTER JOIN它返回的是两个表格中所有的行，用零填满每一个空格。这在Oracle8i中则没有相应的此种句法,SQL1999,大多数改进都是为了让非Oracle的请求可更快速的进入Oracle数据库，并且必须记住这些只是句法上的不同，ISO99标准并没有给Oracle9i SQL带来任何新的功能。 对于异构数据库的迁移，这可能提供了更多的方便之处!,CROSS JOIN,CROSS JOIN产生了一个迪卡尔积（Cartesian product），就象是在连接两个表格时忘记加入一个WHERE子句一样 select last_name,dept_id from emp,depts; 在Oracle9i中，我们使用CROSS JOIN 来达到相同的结果 SQL select ename,dname 2 from emp 3 cross join dept;,NATURAL JOIN,NATURAL JOIN能够通过在两个表的列名来自动的检查join。NATURAL JOIN要求在每一个表格中的字段名相同即使没有关键词作为参考 Oracle8i, Select book_title, sum(quantity) From book, sales Where book.book_id = sales.book_id group by book_title; Oracle9i Select book_title, sum(quantity) from book natural join sales group by book_title;,USING子句,假如几个字段有同样的名字，而你又不想用所有的这些栏目来连接的时候，你就可以用USING子句。 Oracle8i select dept_id, city from departments, locations where departments.location_id = location.location_id; Oracle9i select department_name, city from departments JOIN locations USING (location_id);,ON子句,ON子句被用于当在两个表中的字段名不一致时来连接表 Oracle8i select department_name, city from department, location where department.location_id = location.loc_id; Oracle9i select department_name, city from department d JOIN location l ON (d.location_id = l.id);,易变的连接,易变的连接就是两个以上的表被连接所用的。ISO SQL 1999标准通常假设表从左至右连接，连接的条件是能够为现在的连接或以前的与左边的连接相关联的列名提供参考。 Oracle8i select emp_id, city_name, dept_name from location l, department d, emp e where d.location_id = l.location_id and d.department_id = e.department_id; Oracle9i select emp_id, city_name, dept_name from locations l JOIN departments d ON (d.location_id = l.location_id) JOIN employee