MySQL學習-連線查詢

荒草何茫茫發表於2020-12-26
MySql

操作的表
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 |
+-------+-------+-------+

其中每個欄位的欄位名分別為等級、該等級薪水下限、該等級薪水上限

連線查詢概述

在實際開發中，大部分的情況下都不是從單表中查詢資料，一般都是多張表聯合查詢取出最終的結果。
在實際開發中，一般一個業務都會對應多張表，比如：學生和班級，起碼兩張表。

stunonameclassnoclassname
1zs1北京大興區亦莊經濟技術開發區第二中學高三1班
2ls1北京大興區亦莊經濟技術開發區第二中學高三1班
…………

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

連線查詢的分類

根據語法出現的年代來劃分的話，包括：
  SQL92（一些老的DBA可能還在使用這種語法。DBA：DataBase Administrator，資料庫管理員）
  SQL99（比較新的語法）
根據表的連線方式來劃分，包括：
  內連線：
    等值連線（靠等值關係）
    非等值連線
  自連線
  外連線：
    左外連線（左連線）
    右外連線（右連線）
全連線（這個不講，很少用！）

連線查詢原理以及笛卡爾積現象

在表的連線查詢方面有一種現象被稱為：笛卡爾積現象。（笛卡爾乘積現象）
案例：找出每一個員工的部門名稱，要求顯示員工名和部門名。
員工表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;
  表的別名有什麼好處？
    第一：執行效率高。
    第二：可讀性好。
因為兩張表的屬性名可能會一樣

怎麼避免笛卡爾積現象？

當然是加條件進行過濾。
思考：避免了笛卡爾積現象，會減少記錄的匹配次數嗎？
  不會，次數還是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 |
+--------+------------+

內連線之等值連線

案例：查詢每個員工的部門名稱，要求顯示員工名和部門名。
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條件分離了。
表連線和資料過濾混合了

內連線之非等值連線

內連線之非等值連線：最大的特點是：連線條件中的關係是非等量關係。
案例：找出每個員工的工資等級，要求顯示員工名、工資、工資等級。
查詢員工名和工資的資訊
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 |
+--------+---------+-------+

內連線之自連線

自連線：最大的特點是：一張表看做兩張表。自己連線自己。
案例：找出每個員工的上級領導，要求顯示員工名和對應的領導名。
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的記錄，他的領導為空

外連線

什麼是外連線，和內連線有什麼區別？
內連線：
假設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 |
+--------+------------+--------+

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  |
+--------+------------+-------+-------+

注意，最後外連線的位置。

相關文章