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 |
+-------+-------+-------+
其中每個欄位的欄位名分別為等級、該等級薪水下限、該等級薪水上限
連線查詢概述
在實際開發中，大部分的情況下都不是從單表中查詢資料，一般都是多張表聯合查詢取出最終的結果。
在實際開發中，一般一個業務都會對應多張表，比如：學生和班級，起碼兩張表。
| stuno | name | classno | classname |
|---|---|---|---|
| 1 | zs | 1 | 北京大興區亦莊經濟技術開發區第二中學高三1班 |
| 2 | ls | 1 | 北京大興區亦莊經濟技術開發區第二中學高三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 |
+--------+------------+-------+-------+
注意，最後外連線的位置。
相關文章
- MYSQL學習筆記23： 多表查詢（自連線內連線+左右外連線）MySql筆記
- MySQL之連線查詢MySql
- MySQL之連線查詢和子查詢MySql
- mysql查詢語句5：連線查詢MySql
- 資料庫學習（四）連線查詢資料庫
- MySQL8：連線查詢MySql
- 【由淺入深學MySQL】- MySQL連線查詢詳解MySql
- mysql-分組查詢-子查詢-連線查詢-組合查詢MySql
- MySQL內連線查詢語句MySql
- 【從零開始學習 MySql 資料庫】(3) 函式與子查詢和連線查詢MySql資料庫函式
- go 連線資料庫 - GORM學習 - sql查詢Go資料庫ORMSQL
- MySQL 連線查詢超全詳解MySql
- PHP連線、查詢MySQL資料庫PHPMySql資料庫
- 【學習】SQL基礎-006-多表連線查詢SQL
- MySql中的資料查詢語言（DQL）三：連線查詢MySql
- MYSQL學習筆記26: 多表查詢|子查詢MySql筆記
- SQL連線查詢SQL
- oracle 連線查詢Oracle
- 【Oracle】--連線查詢Oracle
- sql 連線查詢例項（left join）三表連線查詢SQL
- MySQL講義第27講——select 查詢之自連線查詢MySql
- mysql常用連線查詢join，left,right,crossMySqlROS
- 《MySQL 入門教程》第 18 篇 連線查詢MySql
- nodejs 連線 mysql 查詢事務處理NodeJSMySql
- myeclipse 中java連線mysql、查詢測試EclipseJavaMySql
- mysql求交集：UNION ALL合併查詢，inner join內連線查詢，IN/EXISTS子查詢MySql
- MYSQL學習筆記25: 多表查詢(子查詢)[標量子查詢,列子查詢]MySql筆記
- 深圳軟體測試學習：【資料庫】-【oracle】-連線查詢資料庫Oracle
- select查詢之四：連線查詢
- MySQL學習（三） SQL基礎查詢MySql
- （MySQL學習筆記）分頁查詢MySql筆記
- MySQL 學習之索引篇和查詢MySql索引
- MYSQL高效能學習查詢心得MySql
- 我的MYSQL學習心得（7） ： 查詢MySql
- MySQL not in巢狀查詢改寫成外連線方式MySql巢狀
- 【從零開始學習Oracle資料庫】(3)函式與子查詢和連線查詢Oracle資料庫函式
- 資料庫 - 連線查詢、巢狀查詢、集合查詢資料庫巢狀
- MySQL學習筆記之多表連線MySql筆記