SQL基礎-->多表查詢--========================== --SQL基礎-->多表查詢 --========================== /* 一、多表查詢 簡言之，根據特定的連接條件從不同的表中獲取所需的數據
笛卡爾集的產生條件： 省略連接條件 連接條件無效 第一個表中的所有行與第二個表中的所有行相連接
二、多表查詢語法：*/ SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2; /* 但要注意where 不要省了，省略where 即為笛卡爾集，而且where 條件要有效， 兩張表間有一個相同的字段，才好進行有效的多表查詢
查詢時列名前，加表名或表別名前輟（如果字段在兩個表中是唯一的可以不加）
為了簡化SQL書寫，可為表名定義別名，格式：from 表名別名 如：from emp e,dept d
建議使用表的別名及表前綴，使用表別名可以簡化查詢，而使用表前綴則可以提高查詢性能
例：查詢每個員工的工號，姓名，工資，部門名和工作地點*/ select empno,ename,sal,dname,loc from emp,dept where emp.deptno=dept.deptno;
/* 三、多表連接類型： 從數據顯示方式來講有：內連接和外連接。 內連接：只返回滿足連接條件的數據。 外連接：除了返回滿足連接條的行以外，還返回左（右）表中，不滿足條件的行， 稱為左（右）連接
內連接*/ select empno,ename,sal,dname,loc from emp,dept where emp.deptno=dept.deptno; --(Oracle 8i 及以前的寫法)
--內連接的另一種寫法： select empno,ename,job,sal,dept.deptno,dname,loc from emp join dept on (emp.deptno=dept.deptno); -- (SQL 99的寫法)
/* 外連接： 兩個表的查詢中，使用外連接可以查詢另一個表或者兩個中不滿足連接條件的數據。 外連接的符號是（＋），（＋）要放在字段名后。（＋）對面的那個表，會全部顯示。
外連接語法*/ SELECT table1.column, table2.column --右外連接 FROM table1, table2 WHERE table1.column(+) = table2.column;
SELECT table1.column, table2.column --左外連接 FROM table1, table2 WHERE table1.column = table2.column(+);
--例： select empno,ename,job,sal,dept.deptno,dname,loc from emp,dept where emp.deptno(+)=dept.deptno; -- (Oracle 8i 及以前的寫法)
--另一種寫法（右連接）： -- (SQL 99的寫法) select empno,ename,job,sal,dept.deptno,dname,loc from emp right join dept on (emp.deptno=dept.deptno);
--左連接： (SQL 99的寫法) select empno,ename,job,sal,dept.deptno,dname,loc from emp left join dept on (emp.deptno=dept.deptno);
--全連接（滿連接） (SQL 99的寫法) select empno,ename,job,sal,d.deptno,dname,loc from emp e full join dept d on (e.deptno=d.deptno);
/* 自然連接 (SQL 99的寫法) 以兩個表具有相同的字段的所有列為基礎，可采用自然連接（natural join) 它將選擇兩個表中那些在所有匹配的列中值相等的行。 如果列具有相同的名稱，但數據類型能夠不同，則會報錯*/ select empno,ename,job,sal,deptno,dname,loc from emp natural join dept;
/* 自連接： 將自身表的一個鏡像當另一個表來對待。*/ select ... from emp e,emp d ...
--下面列出SQL 99的語法供參考 SELECT table1.column, table2.column FROM table1 [CROSS JOIN table2] | [NATURAL JOIN table2] | [JOIN table2 USING (column_name)] | [JOIN table2 ON(table1.column_name = table2.column_name)] | [LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name)];
--在下面的語法中 table1.column --指明從中檢索數據的表和列 CROSS JOIN --返回兩個表的笛卡爾集 NATURAL JOIN --根據相同的列名連接兩個表 JOIN table USING column_name --根據列名執(zhí)行等值連接 JOIN table ON table1.column_name --根據ON 子句中的條件執(zhí)行等值連接 = table2.column_name LEFT/RIGHT/FULL OUTER
/* 使用using子句創(chuàng)建連接 如果幾個列具有相同的名稱，但是數據類型不匹配，那么可以使用using子句來修改natural join 子句以指定要用于等值連接的列 在多個列匹配時，使用using 子句只匹配一個列 在引用列中不要使用表名或別名 natural join 和using 子句是互不相容的 */
--例： SELECT l.city, d.department_name FROM locations l JOIN departments d USING (location_id) WHERE location_id = 1400; --下面的語句無效，因為where 子句中限定了location_id SELECT l.city, d.department_name FROM locations l JOIN departments d USING (location_id) WHERE d.location_id = 1400; ORA-25154: column part of USING clause cannot have qualifier
--注意： -- 兩個表中名稱相同的列在使用時不能有任何限定符，這一限制同樣適用于natural join /* 四、演示： */ --笛卡爾集 SQL> select empno,ename,dname from emp,dept;
EMPNO ENAME DNAME ---------- ---------- -------------- 7369 SMITH ACCOUNTING 7499 ALLEN ACCOUNTING 7521 WARD ACCOUNTING 7566 JONES ACCOUNTING 7654 MARTIN ACCOUNTING 7698 BLAKE ACCOUNTING 7782 CLARK ACCOUNTING 7788 SCOTT ACCOUNTING 7839 KING ACCOUNTING 7844 TURNER ACCOUNTING 7876 ADAMS ACCOUNTING --中間結果省略 56 rows selected. --使用cross join 實現交叉連接，即笛卡爾集 SQL> select empno,ename,dname from emp 2 cross join dept;
EMPNO ENAME DNAME ---------- ---------- -------------- 7369 SMITH ACCOUNTING 7499 ALLEN ACCOUNTING 7521 WARD ACCOUNTING 7566 JONES ACCOUNTING 7654 MARTIN ACCOUNTING 7698 BLAKE ACCOUNTING 7782 CLARK ACCOUNTING 7788 SCOTT ACCOUNTING 7839 KING ACCOUNTING 7844 TURNER ACCOUNTING 7876 ADAMS ACCOUNTING
--等值連接(Oracle 寫法) SQL> select empno,ename,dname from emp,dept where emp.deptno = dept.deptno;
EMPNO ENAME DNAME ---------- ---------- -------------- 7369 SMITH RESEARCH 7499 ALLEN SALES 7876 ADAMS RESEARCH --部分結果省略 7902 FORD RESEARCH 7934 MILLER ACCOUNTING
14 rows selected.
--等值連接(SQL 99 寫法) SQL> select e.empno,e.ename,d.dname from emp e 2 inner join dept d 3 on e.deptno = d.deptno;
EMPNO ENAME DNAME ---------- ---------- -------------- 7369 SMITH RESEARCH 7499 ALLEN SALES 7876 ADAMS RESEARCH --部分結果省略 7902 FORD RESEARCH 7934 MILLER ACCOUNTING
14 rows selected.
--注意：表別名不支持as 用法 SQL> select e.empno,e.ename,d.dname from emp as e 2 inner join dept d 3 on e.deptno = d.deptno; select e.empno,e.ename,d.dname from emp as e * ERROR at line 1: ORA-00933: SQL command not properly ended
--等值連接并增加條件 SQL> select e.empno,e.ename,d.dname from emp e, 2 dept d 3 where d.deptno = e.deptno 4 and e.ename = 'SCOTT';
EMPNO ENAME DNAME ---------- ---------- -------------- 7788 SCOTT RESEARCH
--非等值連接 --查詢雇員的姓名、薪水、級別且部門為的記錄 SQL> select ename,sal,grade 2 from emp,salgrade 3 where sal between losal and hisal 4 and emp.deptno = 20;
ENAME SAL GRADE ---------- ---------- ---------- SCOTT 3000 4 FORD 3000 4 JONES 2975 4 ADAMS 1100 1 SMITH 800 1
--使用SQL 99寫法實現上述功能 SQL> select e.ename,e.sal,s.grade 2 from emp e 3 join salgrade s 4 on e.sal between losal and hisal 5 and e.deptno = 20;
ENAME SAL GRADE ---------- ---------- ---------- SCOTT 3000 4 FORD 3000 4 JONES 2975 4 ADAMS 1100 1 SMITH 800 1
--右外連接 --注意：右外連接時，加號在等號的左邊 --可以看到，左表emp中的列有為空值的 SQL> select e.ename,e.deptno,d.dname 2 from emp e,dept d 3 where e.deptno(+) = d.deptno;
ENAME DEPTNO DNAME ---------- ---------- -------------- CLARK 10 ACCOUNTING KING 10 ACCOUNTING MILLER 10 ACCOUNTING JONES 20 RESEARCH FORD 20 RESEARCH ADAMS 20 RESEARCH SMITH 20 RESEARCH SCOTT 20 RESEARCH WARD 30 SALES TURNER 30 SALES ALLEN 30 SALES
ENAME DEPTNO DNAME ---------- ---------- -------------- JAMES 30 SALES BLAKE 30 SALES MARTIN 30 SALES OPERATIONS
15 rows selected.
--使用SQL 99寫法實現右外連接 SQL> select e.ename,e.deptno,d.dname 2 from emp e 3 right join dept d 4 on e.deptno = d.deptno ;
ENAME DEPTNO DNAME ---------- ---------- -------------- CLARK 10 ACCOUNTING KING 10 ACCOUNTING MILLER 10 ACCOUNTING JONES 20 RESEARCH FORD 20 RESEARCH ADAMS 20 RESEARCH SMITH 20 RESEARCH SCOTT 20 RESEARCH WARD 30 SALES TURNER 30 SALES ALLEN 30 SALES
ENAME DEPTNO DNAME ---------- ---------- -------------- JAMES 30 SALES BLAKE 30 SALES MARTIN 30 SALES OPERATIONS
--左外連接 --注意：左外連接時，加號在等號的右邊 SQL> select d.dname,e.ename,e.deptno 2 from dept d,emp e 3 where d.deptno = e.deptno(+) 4 order by d.deptno;
DNAME ENAME DEPTNO -------------- ---------- ---------- ACCOUNTING CLARK 10 ACCOUNTING KING 10 ACCOUNTING MILLER 10 RESEARCH JONES 20 RESEARCH FORD 20 RESEARCH ADAMS 20 RESEARCH SMITH 20 RESEARCH SCOTT 20 SALES WARD 30 SALES TURNER 30 SALES ALLEN 30
DNAME ENAME DEPTNO -------------- ---------- ---------- SALES JAMES 30 SALES BLAKE 30 SALES MARTIN 30 OPERATIONS
15 rows selected.
--使用SQL 99寫法實現左外連接 SQL> select d.dname,e.ename,e.deptno 2 from dept d 3 left join emp e 4 on d.deptno = e.deptno 5 order by d.deptno;
DNAME ENAME DEPTNO -------------- ---------- ---------- ACCOUNTING CLARK 10 ACCOUNTING KING 10 ACCOUNTING MILLER 10 RESEARCH JONES 20 RESEARCH FORD 20 RESEARCH ADAMS 20 RESEARCH SMITH 20 RESEARCH SCOTT 20 SALES WARD 30 SALES TURNER 30 SALES ALLEN 30
DNAME ENAME DEPTNO -------------- ---------- ---------- SALES JAMES 30 SALES BLAKE 30 SALES MARTIN 30 OPERATIONS
15 rows selected.
--自連接 SQL> select e.ename || ' works for ' || m.ename 2 from emp e,emp m 3 where e.empno = m.mgr;
E.ENAME||'WORKSFOR'||M.ENAME ------------------------------- FORD works for SMITH BLAKE works for ALLEN BLAKE works for WARD KING works for JONES BLAKE works for MARTIN KING works for BLAKE KING works for CLARK JONES works for SCOTT BLAKE works for TURNER SCOTT works for ADAMS BLAKE works for JAMES
E.ENAME||'WORKSFOR'||M.ENAME ------------------------------- JONES works for FORD CLARK works for MILLER
13 rows selected.
--自然連接 SQL> select empno,ename,job,deptno,dname,loc 2 from emp 3 natural join dept;
EMPNO ENAME JOB DEPTNO DNAME LOC ---------- ---------- --------- ---------- -------------- ------------- 7369 SMITH CLERK 20 RESEARCH DALLAS 7499 ALLEN SALESMAN 30 SALES CHICAGO 7521 WARD SALESMAN 30 SALES CHICAGO 7566 JONES MANAGER 20 RESEARCH DALLAS 7654 MARTIN SALESMAN 30 SALES CHICAGO 7698 BLAKE MANAGER 30 SALES CHICAGO 7782 CLARK MANAGER 10 ACCOUNTING NEW YORK 7788 SCOTT ANALYST 20 RESEARCH DALLAS 7839 KING PRESIDENT 10 ACCOUNTING NEW YORK 7844 TURNER SALESMAN 30 SALES CHICAGO 7876 ADAMS CLERK 20 RESEARCH DALLAS
EMPNO ENAME JOB DEPTNO DNAME LOC ---------- ---------- --------- ---------- -------------- ------------- 7900 JAMES CLERK 30 SALES CHICAGO 7902 FORD ANALYST 20 RESEARCH DALLAS 7934 MILLER CLERK 10 ACCOUNTING NEW YORK
14 rows selected.
--使用using 子句創(chuàng)建連接 SQL> select e.empno,e.ename,d.dname,d.loc 2 from emp e 3 join dept d 4 using (deptno) 5 where deptno in (20,40);
EMPNO ENAME DNAME LOC ---------- ---------- -------------- ------------- 7369 SMITH RESEARCH DALLAS 7566 JONES RESEARCH DALLAS 7788 SCOTT RESEARCH DALLAS 7876 ADAMS RESEARCH DALLAS 7902 FORD RESEARCH DALLAS /* 五、更多*/
使用OEM,SQL*Plus,iSQL*Plus 管理Oracle實例
Oracle實例和Oracle數據庫(Oracle體系結構)
|
|
|
