1.去重
#2.連接查詢
範例表格內容參考這篇文章
有些MySQL 資料表中可能存在重複的記錄，有些情況我們允許重複資料的存在，但有時候我們也需要刪除這些重複的資料。
例如：去重顯示職位資訊：
mysql> select distinct job from emp; +-----------+ | job | +-----------+ | CLERK | | SALESMAN | | MANAGER | | ANALYST | | PRESIDENT | +-----------+ 5 rows in set (0.02 sec)
另一個範例：聯合去重，尋找部門和職位的獨有資訊：
mysql> select distinct job,deptno from emp; +-----------+--------+ | job | deptno | +-----------+--------+ | CLERK | 20 | | SALESMAN | 30 | | MANAGER | 20 | | MANAGER | 30 | | MANAGER | 10 | | ANALYST | 20 | | PRESIDENT | 10 | | CLERK | 30 | | CLERK | 10 | +-----------+--------+ 9 rows in set (0.00 sec)
另一個範例：現在我們想統計一下工作崗位的數量，結合使用count函數：
mysql> select count(distinct job) from emp; +---------------------+ | count(distinct job) | +---------------------+ | 5 | +---------------------+ 1 row in set (0.00 sec)
2.連接查詢
JOIN 依功能大致分為如下三類：
INNER JOIN（內連接,或等值連接）：取得兩個表格中欄位符合關係的記錄。
LEFT JOIN（左連接）：取得左表所有記錄，即使右表沒有對應匹配的記錄。
多表連接的操作是將一個表的每個資料與另一個表的資料行進行比對。這就涉及到了效率控制問題
使用where進行多表連接查詢
現在我們來演示一個例子：取出每個員工的名字和部門名字：
mysql> select ename,dname
-> from emp,dept
-> where emp.deptno = dept.deptno;
+--------+------------+
| 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 |
+--------+------------+
14 rows in set (0.00 sec)mysql> select e.ename,d.dname
-> from emp e,dept d
-> where e.deptno = d.deptno;
+--------+------------+
| 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 |
+--------+------------+
14 rows in set (0.00 sec)注意：表的連接次數越多，效率越低，請盡量減少表的連接次數！
還是上面的例子，取出每個員工的名字和部門名字：（sql99語法）
內連接，我們使用inner
mysql> select e.ename,d.dname
-> from emp e
-> inner join
-> dept d
-> on
-> e.deptno = d.deptno;
+--------+------------+
| 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 |
+--------+------------+
14 rows in set (0.00 sec)內連結- 非等值連線
案例：找出每位員工的薪資等級，要求顯示員工名，薪資，薪資等級
mysql> select
-> e.ename,e.sal,s.grade
-> from
-> emp e
-> inner 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 |
+--------+---------+-------+
14 rows in set (0.01 sec)內連結- 自連結
案例：查詢員工的上級領導，要求顯示員工名稱和對應的領導名稱
mysql> select
-> a.ename as '员工名',b.ename as '领导名'
-> from emp a
-> 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 rows in set (0.00 sec)外連接- 左右外連接
外連接與內連接的區別是，外連接沒有匹配成功的某一個表格的記錄也會被取出
案例：找出員工的部門資訊。請部門即使沒有員工也要查出
mysql> select
-> e.ename,d.dname
-> from emp e
-> right join dept d
-> on
-> e.deptno = d.deptno;
+--------+------------+
| 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 |
| NULL | OPERATIONS |
+--------+------------+
15 rows in set (0.00 sec)同樣的，如果是左外連接，將查詢出左表的全部數據，使用left join關鍵字即可
#外連接的查詢結果條數一定是>= 內連接的查詢結果條數
###三表連接######更為複雜的情況是，群表連接#######我們來看一個案例：######找出每位員工的部門名稱及薪資等級。要求顯示員工名，部門名，薪資，薪資等級###mysql> select
-> e.ename,e.sal,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 | sal | dname | grade |
+--------+---------+------------+-------+
| SMITH | 800.00 | RESEARCH | 1 |
| ALLEN | 1600.00 | SALES | 3 |
| WARD | 1250.00 | SALES | 2 |
| JONES | 2975.00 | RESEARCH | 4 |
| MARTIN | 1250.00 | SALES | 2 |
| BLAKE | 2850.00 | SALES | 4 |
| CLARK | 2450.00 | ACCOUNTING | 4 |
| SCOTT | 3000.00 | RESEARCH | 4 |
| KING | 5000.00 | ACCOUNTING | 5 |
| TURNER | 1500.00 | SALES | 3 |
| ADAMS | 1100.00 | RESEARCH | 1 |
| JAMES | 950.00 | SALES | 1 |
| FORD | 3000.00 | RESEARCH | 4 |
| MILLER | 1300.00 | ACCOUNTING | 2 |
+--------+---------+------------+-------+
14 rows in set (0.00 sec)mysql> select
-> e.ename,e.sal,d.dname,s.grade,l.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 l
-> on e.mgr = l.empno;
+--------+---------+------------+-------+-------+
| ename | sal | dname | grade | ename |
+--------+---------+------------+-------+-------+
| SMITH | 800.00 | RESEARCH | 1 | FORD |
| ALLEN | 1600.00 | SALES | 3 | BLAKE |
| WARD | 1250.00 | SALES | 2 | BLAKE |
| JONES | 2975.00 | RESEARCH | 4 | KING |
| MARTIN | 1250.00 | SALES | 2 | BLAKE |
| BLAKE | 2850.00 | SALES | 4 | KING |
| CLARK | 2450.00 | ACCOUNTING | 4 | KING |
| SCOTT | 3000.00 | RESEARCH | 4 | JONES |
| KING | 5000.00 | ACCOUNTING | 5 | NULL |
| TURNER | 1500.00 | SALES | 3 | BLAKE |
| ADAMS | 1100.00 | RESEARCH | 1 | SCOTT |
| JAMES | 950.00 | SALES | 1 | BLAKE |
| FORD | 3000.00 | RESEARCH | 4 | JONES |
| MILLER | 1300.00 | ACCOUNTING | 2 | CLARK |
+--------+---------+------------+-------+-------+
14 rows in set (0.00 sec)以上是MySQL資料庫中去重與連線查詢的方法的詳細內容。更多資訊請關注PHP中文網其他相關文章！
