連接配接查詢

操作的表
1.連接配接查詢概述
2.連接配接查詢的分類
3.連接配接查詢原理以及笛卡爾積現象
4.怎麼避免笛卡爾積現象？
5.内連接配接之等值連接配接
6.内連接配接之非等值連接配接
7.内連接配接之自連接配接
8.外連接配接
9.3張以上表連接配接查詢

操作的表

mysql> select * from emp;

+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

其每個字段的字段名分别為員工編号、員工姓名、員工職位、員工上級上司編号、員工雇傭日期，員工薪水、員工津貼、員工部門編号。

mysql> select * from dept;

+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

其每個字段的字段名分别部門編号、部門名稱、位置。

mysql> select * from salgrade;

+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

其中每個字段的字段名分别為等級、該等級薪水下限、該等級薪水上限

1.連接配接查詢概述

在實際開發中，大部分的情況下都不是從單表中查詢資料，一般都是多張表聯合查詢取出最終的結果。

在實際開發中，一般一個業務都會對應多張表，比如：學生和班級，起碼兩張表。

stuno	name	classno	classname
1	zs	1	北京大興區亦莊經濟技術開發區第二中學高三1班
2	ls	1	北京大興區亦莊經濟技術開發區第二中學高三1班
…	…	…	…

學生和班級資訊存儲到一張表中，結果就像上面一樣，資料會存在大量的重複，導緻資料的備援。

2.連接配接查詢的分類

根據文法出現的年代來劃分的話，包括：

SQL92（一些老的DBA可能還在使用這種文法。DBA：DataBase Administrator，資料庫管理者）

SQL99（比較新的文法）

根據表的連接配接方式來劃分，包括：

内連接配接：

等值連接配接（靠等值關系）

非等值連接配接

自連接配接

外連接配接：

左外連接配接（左連接配接）

右外連接配接（右連接配接）

全連接配接（這個不講，很少用！）

3.連接配接查詢原理以及笛卡爾積現象

在表的連接配接查詢方面有一種現象被稱為：笛卡爾積現象。（笛卡爾乘積現象）

案例：找出每一個員工的部門名稱，要求顯示員工名和部門名。

員工表emp

+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

部門表dept

+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

select ename,dname from emp,dept; //産生56條記錄

+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | ACCOUNTING |
| SMITH  | RESEARCH   |
| SMITH  | SALES      |
| SMITH  | OPERATIONS |
| ALLEN  | ACCOUNTING |
| ALLEN  | RESEARCH   |
| ALLEN  | SALES      |
| ALLEN  | OPERATIONS |
| WARD   | ACCOUNTING |
| WARD   | RESEARCH   |
| WARD   | SALES      |
| WARD   | OPERATIONS |
........

笛卡爾積現象：當兩張表進行連接配接查詢的時候，沒有任何條件進行限制，最終的查詢結果條數是兩張表記錄條數的乘積。

A表和B表連接配接得時候，從A表中每拿出一條記錄，B表中得所有記錄都與該記錄比對。

關于表的别名：

select e.ename,d.dname from emp e,dept d;

表的别名有什麼好處？

第一：執行效率高。

第二：可讀性好。

因為兩張表的屬性名可能會一樣

4.怎麼避免笛卡爾積現象？

當然是加條件進行過濾。

思考：避免了笛卡爾積現象，會減少記錄的比對次數嗎？

不會，次數還是56次。隻不過顯示的是有效記錄。

案例：找出每一個員工的部門名稱，要求顯示員工名和部門名。

select	
		e.ename,d.dname
	from
		emp e , dept d
	where
		e.deptno = d.deptno; //SQL92，以後不用。

+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+

5.内連接配接之等值連接配接

案例：查詢每個員工的部門名稱，要求顯示員工名和部門名。

SQL92:（太老，不用了）

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

select 
	e.ename,d.dname 
from 
	emp e
join 
	dept d 
ON
	e.DEPTNO=d.DEPTNO;

// inner可以省略的，帶着inner目的是可讀性好一些。
select 
		e.ename,d.dname
	from
		emp e
	inner join
		dept d
	on
		e.deptno = d.deptno;
文法：
		...
			A
		join
			B
		on
			連接配接條件
		where
			...

SQL99文法結構更清晰一些：表的連接配接條件和後來的where條件分離了。

表連接配接和資料過濾混合了

6.内連接配接之非等值連接配接

内連接配接之非等值連接配接：最大的特點是：連接配接條件中的關系是非等量關系。

案例：找出每個員工的工資等級，要求顯示員工名、工資、工資等級。

查詢員工名和工資的資訊

select ename,sal from emp;

+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+

工資等級資訊表salgrade;

+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

按照題設條件，這裡Smith先生得工資應該與Grade1進行比對，他得工資是800位于losal和hisal之間

select 
	e.ename,e.sal,s.grade
from 
	emp e
join
	salgrade s
on
	e.sal BETWEEN s.LOSAL AND s.HISAL

+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+

7.内連接配接之自連接配接

自連接配接：最大的特點是：一張表看做兩張表。自己連接配接自己。

案例：找出每個員工的上級上司，要求顯示員工名和對應的上司名。

mysql> select * from emp;

+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

其中，MGR表示上級上司的編号

如smith的mgr是7902，他的上司就是FORD.

員工的上司編号 = 上司的員工編号

select 
	a.ename as '員工名',b.ename as '上司名'
from
	emp a
inner join
	emp b
on
	a.mgr = b.empno;

+--------+--------+
| 員工名 | 上司名 |
+--------+--------+
| SMITH  | FORD   |
| ALLEN  | BLAKE  |
| WARD   | BLAKE  |
| JONES  | KING   |
| MARTIN | BLAKE  |
| BLAKE  | KING   |
| CLARK  | KING   |
| SCOTT  | JONES  |
| TURNER | BLAKE  |
| ADAMS  | SCOTT  |
| JAMES  | BLAKE  |
| FORD   | JONES  |
| MILLER | CLARK  |
+--------+--------+

找出等量關系是關鍵

注意：此時隻有13條記錄，缺少一條King的記錄，他的上司為空

8.外連接配接

什麼是外連接配接，和内連接配接有什麼差別？

内連接配接：

假設A和B表進行連接配接，使用内連接配接的話，凡是A表和B表能夠比對上的記錄查詢出來，這就是内連接配接。

AB兩張表沒有主副之分，兩張表是平等的。

外連接配接：

假設A和B表進行連接配接，使用外連接配接的話，AB兩張表中有一張表是主表，一張表是副表，主要查詢主表中的資料，捎帶着查詢副表，當副表中的資料沒有和主表中的資料比對上，副表自動模拟出NULL與之比對。

外連接配接的分類？

左外連接配接（左連接配接）：表示左邊的這張表是主表。

右外連接配接（右連接配接）：表示右邊的這張表是主表。

左連接配接有右連接配接的寫法，右連接配接也會有對應的左連接配接的寫法。

案例：找出每個員工的上級上司？（所有員工必須全部查詢出來。）

外連接配接主表所有資料都要查出來。使用内連接配接的時候King(上司為NULL)

left join左邊是主表，主表資料無論如何都要查出來，就算比對不上，也要查出來。

select 
	a.ename as '員工名',b.ename as '上司名'
from
	emp a
left join
	emp b
on
	a.mgr = b.empno;

+--------+--------+
| 員工名 | 上司名 |
+--------+--------+
| SMITH  | FORD   |
| ALLEN  | BLAKE  |
| WARD   | BLAKE  |
| JONES  | KING   |
| MARTIN | BLAKE  |
| BLAKE  | KING   |
| CLARK  | KING   |
| SCOTT  | JONES  |
| KING   | NULL   |
| TURNER | BLAKE  |
| ADAMS  | SCOTT  |
| JAMES  | BLAKE  |
| FORD   | JONES  |
| MILLER | CLARK  |
+--------+--------+

副表比對不上。

// outer是可以省略的。
select 
	a.ename '員工', b.ename '上司'
from
	emp a
left outer join
	emp b
on
	a.mgr = b.empno;

外連接配接：（右外連接配接/右連接配接）
select 
	a.ename '員工', b.ename '上司'
from
	emp b
right join
	emp a
on
	a.mgr = b.empno;

inner和outer都可以省略，區分内外連接配接主要是看有沒有right和left

帶inner和outer可讀性好

開發中使用外連接配接更多。

比對不上，不能讓資料丢失。

外連接配接最重要的特點是主表中的資料全部查詢出來

案例：找出哪個部門沒有員工

select * from emp;

mysql> select * from emp;

+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

select * from dept;

+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

SELECT
	d.*
FROM
	dept d
left JOIN
	emp e
on
	d.DEPTNO=e.DEPTNO
WHERE
	e.empno is null;

+--------+------------+--------+
| DEPTNO | DNAME      | LOC    |
+--------+------------+--------+
|     40 | OPERATIONS | BOSTON |
+--------+------------+--------+

9.3張以上表連接配接查詢

案例：找出每一個員工的部門名稱以及工資等級。

注意，解釋一下：

....
	A
join
	B
join
	C
on
	...

表示：A表和B表先進行表連接配接，連接配接之後A表繼續和C表進行連接配接。

或者A和B之間連接配接的結果再和C連接配接

select 
		e.ename,d.dname,s.grade
	from
		emp e
	join
		dept d
	on
		e.deptno = d.deptno
	join
		salgrade s
	on
		e.sal between s.losal and s.hisal;

+--------+------------+-------+
| ename  | dname      | grade |
+--------+------------+-------+
| SMITH  | RESEARCH   |     1 |
| ADAMS  | RESEARCH   |     1 |
| JAMES  | SALES      |     1 |
| MILLER | ACCOUNTING |     2 |
| WARD   | SALES      |     2 |
| MARTIN | SALES      |     2 |
| ALLEN  | SALES      |     3 |
| TURNER | SALES      |     3 |
| CLARK  | ACCOUNTING |     4 |
| JONES  | RESEARCH   |     4 |
| SCOTT  | RESEARCH   |     4 |
| FORD   | RESEARCH   |     4 |
| BLAKE  | SALES      |     4 |
| KING   | ACCOUNTING |     5 |
+--------+------------+-------+

案例：找出每一個員工的部門名稱、工資等級、以及上級上司。

這裡意味着沒有上司的king也必須查出來。

select 
		e.ename '員工',d.dname,s.grade,e1.ename '上司'
	from
		emp e
	join
		dept d
	on
		e.deptno = d.deptno
	join
		salgrade s
	on
		e.sal between s.losal and s.hisal
	left join
		emp e1
	on
		e.mgr = e1.empno;

+--------+------------+-------+-------+
| 員工   | dname      | grade | 上司  |
+--------+------------+-------+-------+
| SMITH  | RESEARCH   |     1 | FORD  |
| ADAMS  | RESEARCH   |     1 | SCOTT |
| JAMES  | SALES      |     1 | BLAKE |
| WARD   | SALES      |     2 | BLAKE |
| MARTIN | SALES      |     2 | BLAKE |
| MILLER | ACCOUNTING |     2 | CLARK |
| ALLEN  | SALES      |     3 | BLAKE |
| TURNER | SALES      |     3 | BLAKE |
| JONES  | RESEARCH   |     4 | KING  |
| BLAKE  | SALES      |     4 | KING  |
| CLARK  | ACCOUNTING |     4 | KING  |
| SCOTT  | RESEARCH   |     4 | JONES |
| FORD   | RESEARCH   |     4 | JONES |
| KING   | ACCOUNTING |     5 | NULL  |
+--------+------------+-------+-------+

注意，最後外連接配接的位置。

連接配接查詢

操作的表

1.連接配接查詢概述

2.連接配接查詢的分類

3.連接配接查詢原理以及笛卡爾積現象

4.怎麼避免笛卡爾積現象？

5.内連接配接之等值連接配接

6.内連接配接之非等值連接配接

7.内連接配接之自連接配接

8.外連接配接

9.3張以上表連接配接查詢

繼續閱讀

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述