多表數(shù)據(jù)記錄查詢:

• 關(guān)系數(shù)據(jù)的各種操作
• 內(nèi)連接查詢
• 外連接查詢
• 子查詢

1. 關(guān)系數(shù)據(jù)操作
   傳統(tǒng)的運算:

• 并(UNION)
• 笛卡爾積(CARTESIAN PRODUCT)
• 專門針對數(shù)據(jù)庫操作的運算---連接(JOIN)

(1) 并(UNION)
“并”就是把具有相同字段數(shù)目和字段類型的表合并到一起。
(2) 笛卡爾積(CARTESIAN PRODUCT)
笛卡爾積就是沒有連接條件表關(guān)系返回的結(jié)果。
(3) 內(nèi)連接(INNER JOIN)
所謂連接，就是在表關(guān)系的笛卡爾積數(shù)據(jù)記錄中，按照相應(yīng)字段值的比較條件進(jìn)行選擇生成一個新的關(guān)系。連接又分為內(nèi)連接(INNER JOIN)、外鏈接(OUTER JOIN)、交叉連接(CROSS JOIN)。
所謂內(nèi)連接(INNER JOIN), 就是在表關(guān)系的笛卡爾積數(shù)據(jù)記錄中，保留表關(guān)系中所有匹配的數(shù)據(jù)記錄，舍棄不匹配的數(shù)據(jù)記錄。按照匹配的條件可以分為自然連接、等值連接和不等連接。

• 自然連接(NATURAL JOIN)
  自然連接操作就是表關(guān)系的笛卡爾積中，首先根據(jù)表關(guān)系中相同名稱的字段自動進(jìn)行記錄匹配，然后去掉重復(fù)的字段。
• 等值連接
  等值連接操作就是表關(guān)系的笛卡爾積中，選擇所匹配字段值相等的數(shù)據(jù)記錄。
• 不等連接
  不等連接操作就是表關(guān)系的笛卡爾積中，選擇所匹配字段值不相等的數(shù)據(jù)記錄。
  (4) 外連接(OUTER JOIN)
  所謂外鏈接，就是在表關(guān)系的笛卡爾積數(shù)據(jù)記錄中，不僅保留表關(guān)系中所有匹配的數(shù)據(jù)記錄，而且還會保留部分不匹配的數(shù)據(jù)記錄。按照保留不匹配條件數(shù)據(jù)記錄來源可以分為左外連接(LEFT OUTER JOIN)、右外連接(RIGHT OUTER JOIN)、和全外連接(FULL OUTER JOIN)。
• 左外連接(LEFT OUTER JOIN)
  所謂左外連接操作就是表關(guān)系的笛卡爾積中，除了選擇相匹配的數(shù)據(jù)記錄，還包含關(guān)聯(lián)左邊表中不匹配的數(shù)據(jù)記錄。
• 右外連接(RIGHT OUTER JOIN)
  所謂右外連接操作就是表關(guān)系的笛卡爾積中，除了選擇相匹配的數(shù)據(jù)記錄，還包含關(guān)聯(lián)右邊表中不匹配的數(shù)據(jù)記錄。
• 全外連接(FULL OUTER JOIN)
  所謂全外連接操作就是表關(guān)系的笛卡爾積中，除了選擇相匹配的數(shù)據(jù)記錄，還包含關(guān)聯(lián)左右兩邊表中不匹配的數(shù)據(jù)記錄。
  (5) 交叉連接(CROSS JOIN)
  交叉連接就是表關(guān)系笛卡爾積后數(shù)據(jù)記錄。

t_dept表.png

t_employee表.png

2. 內(nèi)連接查詢(INNER JOIN...ON)
   內(nèi)連接查詢: 等值連接、不等連接。

(1) 自連接--特殊的等值連接

select e.ename employeename, e.job, l.ename loadername 
    from t_employee e 
    inner join t_employee l 
    on e.mgr=l.empno;

(2) 等值連接
內(nèi)連接查詢中的等值連接，就是在關(guān)鍵字ON后的匹配條件中通過關(guān)系運算符(=) 來實現(xiàn)等值條件。

select e.empno, e.ename, e.job, d.dname, d.loc 
    from t_employee e 
    inner join t_dept d 
    on e.deptno=d.deptno;

三表查詢

select e.empno, e.ename employeename, e.sal, e.job, l.ename loadername, d
.dname, d.loc 
    from t_employee e 
    inner join t_employee l on e.mgr=l.empno 
    inner join t_dept d on l.deptno=d.deptno;

或

select e.empno, e.ename employeename, e.sal, e.job, l.ename loadername, d.dname, d.loc 
    from t_employee e, t_employee l, t_dept d 
    where e.mgr=l.empno and l.deptno=d.deptno;

(3) 不等連接
內(nèi)連接查詢中的不等連接，就是在關(guān)鍵字ON后的匹配條件中通過除了等于關(guān)系運算符來實現(xiàn)不等條件外，可以使用的關(guān)系運算符包含“>”、“>=”、“<”、“<=”和“!=”等運算符號。

select e.ename employeename, e.job, l.ename loadername 
    from t_employee e
    inner join t_employee l 
                on e.mgr=l.empno and e.empno>l.empno;

3. 外連接查詢(OUTER JOIN...ON)
   外連接查詢: 左外連接、右外連接、全外連接。

(1) 左外連接
外連接查詢中的左外連接，就是指新關(guān)系中執(zhí)行匹配條件時，以關(guān)鍵字LEFT JOIN左邊的表為參考表。

select e.ename employeename, e.job, l.ename loadername 
    from t_employee e
    inner join t_employee l on e.mgr=l.empno;

(2) 右外連接
外連接查詢中的右外連接，就是指新關(guān)系中執(zhí)行匹配條件時，以關(guān)鍵字RIGHT JOIN 右邊的表為參考表。

select e.empno, e.ename, e.job, d.dname, d.loc from t_dept d right join t
_employee e on e.deptno=d.deptno;

4. 合并查詢數(shù)據(jù)記錄

t_cstudent表.png

t_mstudent表.png

(1) 帶有關(guān)鍵字UNION的合并操作
關(guān)鍵字UNION會把查詢結(jié)果集直接合并到一起，同時將會去掉重復(fù)數(shù)據(jù)記錄。

 select *
          from t_cstudent
 union
 select *
          from t_mstudent;

(2) 帶關(guān)鍵字UNION ALL的合并操作
關(guān)鍵字UNION ALL會把查詢結(jié)果集直接合并在一起。

 select *
          from t_cstudent
 union all
 select *
          from t_mstudent;

5. 子查詢
   雖然可以通過連接查詢實現(xiàn)多表查詢數(shù)據(jù)記錄，但不建議使用。因為連接查詢的性能很差，因此出現(xiàn)了連接查詢的替代者子查詢。
   所謂子查詢，就是指一個查詢之中嵌套了其他的若干查詢，即在一個SELECT查詢語句的WHERE或FROM子句中包含另一個SELECT查詢語句。在查詢語句中，外層SELECT查詢語句稱為主查詢，WHERE子句中的SELECT查詢語句被稱為子查詢，也被稱為嵌套查詢。
   (1) 返回結(jié)果為單行單列和單行多列子查詢

• 返回結(jié)果為單行單列子查詢

select *
         from t_employee
         where sal>(
                 select sal
                         from t_employee
                         where ename='swith');

• 返回結(jié)果為單行多列子查詢

select ename, sal, job
         from t_employee
         where (sal,job)=(
                 select sal,job
                 from t_employee
                         where ename='swith');

(2) 返回結(jié)果為多行單列子查詢
當(dāng)子查詢的返回結(jié)果為多行單列數(shù)據(jù)記錄時，該子查詢語句一般會在主查詢語句的WHERE子句里出現(xiàn)，通常會包含IN、ANY、ALL、EXISTS等關(guān)鍵字。

• 帶有關(guān)鍵字IN的子查詢

select *
         from t_employee
         where deptno in(
                 select deptno from t_dept
         );

• 帶有關(guān)鍵字ANY的子查詢
  關(guān)鍵字ANY用來表示主查詢的條件為滿足子查詢返回查詢結(jié)果中任意一條數(shù)據(jù)記錄，匹配方式: =ANY(其功能與關(guān)鍵字IN一樣)、>ANY(比子查詢中返回數(shù)據(jù)記錄中最小的還要大于數(shù)據(jù)記錄)、<ANY(比子查詢中返回數(shù)據(jù)記錄中最小的還要小于數(shù)據(jù)記錄)

select ename, sal
         from t_employee
         where sal>any(
                 select sal
                         from t_employee
                         where job='manager'
         );

• 帶有關(guān)鍵字ALL的自查詢
  關(guān)鍵字ALL用來表示主查詢的條件為滿足子查詢返回查詢結(jié)果中 所有數(shù)據(jù)記錄。匹配方式: >ALL(比子查詢中返回數(shù)據(jù)記錄中最大的還要大于數(shù)據(jù)記錄)、<ALL(比子查詢中返回數(shù)據(jù)記錄中最大的還要小于數(shù)據(jù)記錄).

select ename, sal
         from t_employee
         where sal>all(
                 select sal
                         from t_employee
                         where job='manager'
         );

• 帶有關(guān)鍵字EXISTS的子查詢

select * from t_dept c where not exists(
         select * from t_employee a
                 where a.deptno=c.deptno);

(3) 返回結(jié)果為多行多列子查詢

select d.deptno, d.dname, d.loc, number, average from t_dept d inner join(
          select deptno dno, count(empno) number, avg(sal) average
                 from t_employee
                 group by deptno desc) employee
          on d.deptno=employee.dno;