oracle（sql）基礎篇系列（二）——多表連接查詢、子查詢、視圖，oracle基礎篇

oracle（sql）基礎篇系列（二）——多表連接查詢、子查詢、視圖，oracle基礎篇

 

 

多表連接查詢

內連接（inner join）

目的：將多張表中能通過鏈接謂詞或者鏈接運算符連接起來的數據查詢出來。

等值連接（join...on(...=...)）

--選出雇員的名字和雇員所在的部門名字

--（1）必須明確的指出重復字段屬於哪個表

select ename,dname dept.deptno from emp,dept where emp.deptno = dept.deptno;

 

--（2）新語法：join...on(...=...)

select ename, dname from emp join dept on ( emp.deptno = dept.deptno);

非等值連接（join...on(...>...)）

--求每個人的薪水值，名字和他的薪水處於第幾個級別

--（1）不使用連接查詢

select ename, sal, grade from emp, salgrade where sal between losal and hisal;

--或者

select ename, sal, grade from emp, salgrade where sal >= losal and sal <= hisal;

 

--（2）新語法：join...on(...between...and...)

select ename, grade from emp e join salgrade s on (e.sal between s.losal and s.hisal);

 

--（3）三表連接：取出雇員名字，他的部門名稱，和薪水等級其中名字第二個字母包含A的不要取出

 

select ename, dname, grade from emp e join dept d on (e.deptno = d.deptno) join salgrade s on (e.sal between s.losal and s.hisal) where ename not like '_A%';

 

從上面的例子也可以看出，比較的對象不僅可以是具體的數據，字段名也可以作為比較的對象；一個join...on(...xxx...)只能連接兩張表，要連接兩張表以上，寫多個join...on(...xxx...)即可，之間用空格隔開。

自然鏈接（natural join...）

自然連接是在兩張表中尋找那些數據類型和列名都相同的字段，然後自動地將他們連接起來，並返回所有符合條件按的結果。

--自動將emp表中的deptno和dept表中的deptno做了連接

select emp.ename,dept.dname from emp natural join dept;

交叉鏈接（cross join...）

交叉連接，交叉連接即返回這兩個表的笛卡爾積。

select * from emp cross join dept;

 

外連接(outer join)

目的：將兩張表中不能產生連接的數據也查詢出來。注意和內連接的區別：內連接只查詢符合連接條件的記錄，外連接則把不能產生連接的數據也查詢出來。

右外連接（right join... on(...=...)） 

概念：right join關鍵字右邊的表有一條（多條）不能產生連接的數據沒有在連接查詢中顯示出來，要想把他顯示出來，就要用到右連接。

--求每個雇員的名字，他所在部門的名稱，全部選出來，並且把多余的部門也選出來(有一個部門沒有員工)

select ename, dname from emp e right  join dept d on (e.deptno = d.deptno);

 

 

 

左外連接（left join...on(...=...)...）

概念：right join關鍵字左邊的表有一條（多條）不能產生連接的數據沒有在連接查詢中顯示出來，要想把他顯示出來，就要用到左連接。

--求這個人的名字，他經理人的名字，並且把沒有經理的員工也選出來

select e1.ename, e2.ename from emp e1 left join emp e2 on (e1.mgr = e2.empno);

 

 

全外連接（full join...on(...=...)...）

概念：把full join關鍵字左邊右邊沒有連接的記錄都查詢出來。

select ename, dname from emp e full join dept d on (e.deptno = d.deptno);

 

自連接

概念：為同一張表起不同的別名，然後當成兩張表來用。

--求這個人的名字，他經理人的名字(經理也是員工，經理編號就是員工編號)

 

select e1.ename,e2.ename from emp e1 join emp e2 on (e1.mgr = e2.empno)

 

小結

join後跟要連接的表，on後跟連接條件，將連接條件和過濾條件分開。

--SQL1992舊寫法

select ename,dname from emp e,dept d where e.deptno = d.deptno and e.ename not like '%A%';

 

--SQL1999新寫法

select ename,dname from emp e join dept d on(e.deptno = d.deptno) where e.ename not like '%A%';

 

 

子查詢

子查詢是一個 SELECT 語句，它嵌套在一個 SELECT語句、DELETE 語句、UPDATE 語句或嵌套在另一子查詢中。

求誰掙的錢最多

select ename, sal from emp where sal = (select max(sal) from emp);

 

求按照部門進行分組之後掙錢最多的那個人的名字，部門編號

--（1）只求掙錢最多的一個人的名字和部門編號

select ename,deptno from emp where sal = ((select max(m_max) from (select deptno,max(sal) m_max

from emp group by deptno )));

 

--（2）求出每個部門掙錢最多的人的名字和部門編號

select e.deptno,e.ename from emp e join (select deptno,max(sal) max_sal from emp group by deptno) t on (t.deptno = e.deptno and t.max_sal = e.sal);

 

思路：

1.先求出每個部門的最大工資和部門號，根據部門號分組

2.把第一步的結果當成一張表t，表t有max_sal，deptno兩個字段

3.連接查詢表t和表emp,求出掙錢最多的那個人的名字，部門編號

求每個部門的平均薪水等級是多少

select deptno ,grade from salgrade s join

(select deptno, avg(sal) s_avg from emp group by deptno) t

on (t.s_avg between s.losal and s.hisal );

分析：

1.求出每個部門的平均薪水

2.把第一步的結果當成一張表t，表t有avg_sal，deptno兩個字段

3.連接查詢表t和表salgrade表,求出grade,dept

小結

子查詢的關鍵是將子查詢的結果當成一張表。

視圖

授權

默認情況下scott用戶沒有創建視圖的權限，需要授權。

 

#使用oracle用戶登錄linux

[oracle@localhost ~]$ sqlplus / as sysdba;

......

SQL> grant create table ,create view to scott;

 

 

視圖的概念

在 SQL 中，視圖是基於 SQL 語句的結果集的可視化的表。視圖包含行和列，就像一個真實的表。視圖中的字段就是來自一個或多個數據庫中的真實的表中的字段。

簡單的理解，視圖就是一個子查詢或者就是一張表,視圖中的表叫虛表，實際數據依然在實際當中的表裡面。

創建視圖

CREATE VIEW view_name AS

SELECT column_name(s)

FROM table_name

WHERE condition

 

--求平均薪水的等級最低的部門名稱

--分析：（1）先求出每個部門的平均薪水等級，結果當成表t1

---------（2）從t1中求出平均薪水等級最低的部門編號

---------（3）根據（2）中查詢出的部門編號連接查詢dept表得出部門名稱

select dname from dept where deptno = (

 

  select deptno from (

 

    select t1.deptno, s.grade from salgrade s join

   

    (select avg(sal) s_avg,deptno from emp group by deptno) t1 on (t1.s_avg between s.losal and

     s.hisal)

 

    ) where grade = (

 

      select min(grade) from (

 

        select t1.deptno, s.grade from salgrade s join

       

        (select avg(sal) s_avg,deptno from emp group by deptno) t1 on (t1.s_avg between s.losal and

  s.hisal)

 

      )

  )

)

 

在上面的例子中，有兩部分子查詢是同樣的sql語句，我們可以將這個子查詢創建為視圖，從而簡化sql語句。

create view v$_dept_sal_grade as  (

        select t1.deptno, s.grade from salgrade s join

        (select avg(sal) s_avg,deptno from emp group by deptno) t1 on (t1.s_avg between s.losal and

    s.hisal)

)

上面的sql語句就可以簡化為：

select dname from dept where deptno = (

 

  select deptno from v$_dept_sal_grade where grade = (

 

      select min(grade) from v$_dept_sal_grade

  )

)

可見，通過創建視圖，大大簡化了sql語句。