MySQL數(shù)據(jù)庫查詢進階之多表查詢詳解
更新時間：2022年04月08日 11:53:04 作者：Java的學(xué)習(xí)之路
Mysql數(shù)據(jù)庫是web開發(fā)中最常用的數(shù)據(jù)庫之一,mysql多表查詢是開發(fā)人員必備的技能,下面這篇文章主要給大家介紹了關(guān)于MySQL數(shù)據(jù)庫查詢進階之多表查詢的相關(guān)資料,需要的朋友可以參考下
一、多表查詢
多表查詢，也稱為關(guān)聯(lián)查詢，指兩個或更多個表一起完成查詢操作。
前提條件：這些一起查詢的表之間是有關(guān)系的（一對一、一對多），它們之間一定是有關(guān)聯(lián)字段，這個關(guān)聯(lián)字段可能建立了外鍵，也可能沒有建立外鍵。比如：員工表和部門表，這兩個表依靠“部門編號”進行關(guān)聯(lián)。
1.引出
假如我們現(xiàn)在要查詢員工的姓名還有部門名稱
這兩個字段在不同表中，如果沒有關(guān)聯(lián)條件的話，查詢出來的結(jié)果會怎么樣呢，讓我們來看看。
SELECT last_name, department_name FROM employees, departments; +-----------+----------------------+ | last_name | department_name | +-----------+----------------------+ | King | Administration | | King | Marketing | | King | Purchasing | | King | Human Resources | | King | Shipping | | King | IT | | King | Public Relations | | King | Sales | | King | Executive | | King | Finance | | King | Accounting | | King | Treasury | ... | Gietz | IT Support | | Gietz | NOC | | Gietz | IT Helpdesk | | Gietz | Government Sales | | Gietz | Retail Sales | | Gietz | Recruiting | | Gietz | Payroll | +-----------+----------------------+ 2889 rows in set (0.01 sec)
SELECT COUNT(employee_id) FROM employees; #輸出107行 SELECT COUNT(department_id)FROM departments; #輸出27行 SELECT 107*27 FROM dual; 107*27=2889
很明顯上面的操作是錯誤的
上面的操作，會導(dǎo)致員工表的一條記錄會和部門表的每一條記錄相匹配，就好像一個員工在所有部門都工作過一樣，從現(xiàn)實角度來說，很明顯，是不會出現(xiàn)這種情況的，
這種現(xiàn)象就是笛卡爾積。
2.笛卡爾積
笛卡兒積就是關(guān)系代數(shù)里的一個概念，表示兩個表中的每一行數(shù)據(jù)任意組合的結(jié)果。比如：有兩個表，左表有m條數(shù)據(jù)記錄，x個字段，右表有n條數(shù)據(jù)記錄，y個字段，則執(zhí)行交叉連接后將返回m*n條數(shù)據(jù)記錄，x+y個字段。笛卡兒積示意圖如圖所示。
SQL92中，笛卡爾積也稱為交叉連接，英文是
CROSS JOIN。在 SQL99 中也是使用 CROSS JOIN表示交叉連接。它的作用就是可以把任意表進行連接，即使這兩張表不相關(guān)。在MySQL中如下情況會出現(xiàn)笛卡爾積：
查詢員工姓名和所在部門名稱SELECT last_name,department_name FROM employees,departments; SELECT last_name,department_name FROM employees CROSS JOIN departments; SELECT last_name,department_name FROM employees INNER JOIN departments; SELECT last_name,department_name FROM employees JOIN departments;
3. 笛卡爾積的解決方法
笛卡爾積的錯誤會在下面條件下產(chǎn)生：
笛卡爾積的錯誤會在下面條件下產(chǎn)生：
- 省略多個表的連接條件（或關(guān)聯(lián)條件）
- 連接條件（或關(guān)聯(lián)條件）無效
- 所有表中的所有行互相連接
為了避免笛卡爾積， 可以在 WHERE 加入有效的連接條件。
SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2; #連接條件
#案例：查詢員工的姓名及其部門名稱 SELECT last_name, department_name FROM employees, departments WHERE employees.department_id = departments.department_id;
注意:如果不同的表中有相同的字段，我們要聲明我們查的是哪一張表的字段,表名.字段名這個和Java中，類名.屬性是類似的，挺好理解的。
SELECT employees.last_name, departments.department_name,employees.department_id FROM employees, departments WHERE employees.department_id = departments.department_id;
二、多表查詢分類
1.等值連接和非等值連接
等值連接其實很好理解，就是誰等于誰的意思，使用=。
非等值連接的話，比如查詢某個字段>某個值的記錄等等
SELECT employees.employee_id, employees.last_name, employees.department_id, departments.department_id, departments.location_id FROM employees, departments WHERE employees.department_id = departments.department_id;拓展:
使用別名可以簡化查詢。— 有的字段名太長了列名前使用表名前綴可以提高查詢效率。SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e , departments d WHERE e.department_id = d.department_id;需要注意的是，如果我們使用了表的別名，在查詢字段中、過濾條件中就只能使用別名進行代替，不能使用原有的表名，否則就會報錯。
2.自連接和非自連接
自連接，它的字面意思就是自己和自己連接
比如說現(xiàn)在有一張表，我們想要查找員工信息和對應(yīng)的上級信息
我們知道，只有一張表是沒辦法把它們關(guān)聯(lián)起來的，要想把它們他們關(guān)聯(lián)起來，肯定是要有關(guān)聯(lián)條件的，那么就應(yīng)該要有兩張表，這個時候，我們就可以抽取出一張表，和本來的表本質(zhì)上是一樣的，然后我們對表起別名，table1和table2本質(zhì)上是同一張表，只是用取別名的方式虛擬成兩張表以代表不同的意義。然后兩個表再進行內(nèi)連接，外連接等查詢。
比如說：現(xiàn)在我們想要查找員工和對應(yīng)老板的名字，我們就可以使用自連接
SELECT CONCAT(worker.last_name ,' works for ' , manager.last_name) FROM employees worker, employees manager WHERE worker.manager_id = manager.employee_id ;練習(xí)：查詢出last_name為 ‘Chen’ 的員工的 manager 的信息。
3.內(nèi)連接和外連接
內(nèi)連接: 合并具有同一列的兩個以上的表的行, 結(jié)果集中不包含一個表與另一個表不匹配的行
外連接: 兩個表在連接過程中除了返回滿足連接條件的行以外還返回左（或右）表中不滿足條件的行 ，這種連接稱為左（或右） 外連接。沒有匹配的行時, 結(jié)果表中相應(yīng)的列為空(NULL)。
如果是左外連接，則連接條件中左邊的表也稱為主表，右邊的表稱為從表。
如果是右外連接，則連接條件中右邊的表也稱為主表，左邊的表稱為從表。
外連接查詢的數(shù)據(jù)比較多
SQL92：使用(+)創(chuàng)建連接在 SQL92 中采用（+）代表從表所在的位置。即左或右外連接中，(+) 表示哪個是從表。
Oracle 對 SQL92 支持較好，而 MySQL 則不支持 SQL92 的外連接。
#左外連接 SELECT last_name,department_name FROM employees ,departments WHERE employees.department_id = departments.department_id(+); #右外連接 SELECT last_name,department_name FROM employees ,departments WHERE employees.department_id(+) = departments.department_id; ```
SQL99語法實現(xiàn)多表查詢
1.基本語法
使用JOIN…ON子句創(chuàng)建連接的語法結(jié)構(gòu)：SELECT table1.column, table2.column,table3.column FROM table1 JOIN table2 ON table1 和 table2 的連接條件 JOIN table3 ON table2 和 table3 的連接條件語法說明：
可以使用 ON 子句指定額外的連接條件 。
這個連接條件是與其它條件分開的。ON 子句使語句具有更高的易讀性。關(guān)鍵字 JOIN、INNER JOIN、CROSS JOIN 的含義是一樣的，都表示內(nèi)連接2.內(nèi)連接(INNER JOIN)
語法
select 字段
from 表1
join 表2 on 兩個表的連接條件
where 其他子句
比如我們現(xiàn)在想要查詢各個部門的員工的信息，他們的連接條件就是員工表中部門id和部門表中的部門id一樣
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id);
這里截取部分結(jié)果
+-------------+-------------+---------------+---------------+-------------+
| employee_id | last_name | department_id | department_id | location_id |
+-------------+-------------+---------------+---------------+-------------+
| 103 | Hunold | 60 | 60 | 1400 |
| 104 | Ernst | 60 | 60 | 1400 |
| 105 | Austin | 60 | 60 | 1400 |
| 106 | Pataballa | 60 | 60 | 1400 |
| 107 | Lorentz | 60 | 60 | 1400 |
| 120 | Weiss | 50 | 50 | 1500 |
| 121 | Fripp | 50 | 50 | 1500 |
| 122 | Kaufling | 50 | 50 | 1500 |
| 123 | Vollman | 50 | 50 | 1500 |
| 124 | Mourgos | 50 | 50 | 1500 |
| 125 | Nayer | 50 | 50 | 1500 |
| 126 | Mikkilineni | 50 | 50 | 1500 |
| 127 | Landry | 50 | 50 | 1500 |
| 128 | Markle | 50 | 50 | 1500 |
| 129 | Bissot | 50 | 50 | 1500 |
使用內(nèi)連接的一個問題就是他們把所有的信息都顯示出來，它只能夠顯示匹配的數(shù)據(jù)，而外連接可以把不匹配的數(shù)據(jù)也顯示出來
先來看看表的數(shù)據(jù)，方便后續(xù)操作
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 | +-------+--------+-----------+------+------------+---------+---------+--------+ 14 rows in set (0.00 sec)
mysql> select * from dept; +--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+ 4 rows in set (0.00 sec)
mysql> select * from emp e
-> join dept d
-> on e.deptno=e.deptno;
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | DNAME | LOC |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 10 | ACCOUNTING | NEW YORK |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 10 | ACCOUNTING | NEW YORK |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | 10 | ACCOUNTING | NEW YORK |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 10 | ACCOUNTING | NEW YORK |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 10 | ACCOUNTING | NEW YORK |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 10 | ACCOUNTING | NEW YORK |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 10 | ACCOUNTING | NEW YORK |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 10 | ACCOUNTING | NEW YORK |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 10 | ACCOUNTING | NEW YORK |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 10 | ACCOUNTING | NEW YORK |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 10 | ACCOUNTING | NEW YORK |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 20 | RESEARCH | DALLAS |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 20 | RESEARCH | DALLAS |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 20 | RESEARCH | DALLAS |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 20 | RESEARCH | DALLAS |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | 20 | RESEARCH | DALLAS |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 20 | RESEARCH | DALLAS |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 20 | RESEARCH | DALLAS |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 20 | RESEARCH | DALLAS |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 20 | RESEARCH | DALLAS |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 30 | SALES | CHICAGO |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 30 | SALES | CHICAGO |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | 30 | SALES | CHICAGO |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 30 | SALES | CHICAGO |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 30 | SALES | CHICAGO |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | 30 | SALES | CHICAGO |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 30 | SALES | CHICAGO |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 30 | SALES | CHICAGO |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 40 | OPERATIONS | BOSTON |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 40 | OPERATIONS | BOSTON |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 40 | OPERATIONS | BOSTON |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | 40 | OPERATIONS | BOSTON |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 40 | OPERATIONS | BOSTON |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 40 | OPERATIONS | BOSTON |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 40 | OPERATIONS | BOSTON |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | 40 | OPERATIONS | BOSTON |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 40 | OPERATIONS | BOSTON |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 40 | OPERATIONS | BOSTON |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 40 | OPERATIONS | BOSTON |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 40 | OPERATIONS | BOSTON |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 40 | OPERATIONS | BOSTON |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 40 | OPERATIONS | BOSTON |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
56 rows in set (0.01 sec)
– 問題：
– 1.40號部分沒有員工，沒有顯示在查詢結(jié)果中
– 2.員工scott沒有部門，沒有顯示在查詢結(jié)果中
所以想顯示所有數(shù)據(jù)，要使用外連接
外連接(OUTER JOIN)
1.左外連接左外連接： left outer join – 左面的那個表的信息，即使不匹配也可以查看出效果
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 關(guān)聯(lián)條件
WHERE 等其他子句;2.右外連接
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 關(guān)聯(lián)條件
WHERE 等其他子句;
mysql> select *
-> from emp e
-> right outer join dept d
-> on e.deptno = d.deptno;
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | DNAME | LOC |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 40 | OPERATIONS | BOSTON |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
15 rows in set (0.00 sec)
3.滿外連接(FULL OUTER JOIN)
滿外連接的結(jié)果 = 左右表匹配的數(shù)據(jù) + 左表沒有匹配到的數(shù)據(jù) + 右表沒有匹配到的數(shù)據(jù)。
SQL99是支持滿外連接的。使用FULL JOIN 或 FULL OUTER JOIN來實現(xiàn)。
需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。
在講滿外連接之前，我們先來介紹一下union關(guān)鍵字的使用，相信看了以后大家就清楚了
4.UNION
合并查詢結(jié)果
利用UNION關(guān)鍵字，可以給出多條SELECT語句，并將它們的結(jié)果組合成單個結(jié)果集。合并時，兩個表對應(yīng)的列數(shù)和數(shù)據(jù)類型必須相同，并且相互對應(yīng)。各個SELECT語句之間使用UNION或UNION ALL關(guān)鍵字分隔。
語法格式：
SELECT column,… FROM table1
UNION [ALL]
SELECT column,… FROM table2
UNION操作符
UNION 操作符返回兩個查詢的結(jié)果集的并集，去除重復(fù)記錄。
`UNION ALL操作符
UNION ALL操作符返回兩個查詢的結(jié)果集的并集。對于兩個結(jié)果集的重復(fù)部分，不去重。
注意：執(zhí)行UNION ALL語句時所需要的資源比UNION語句少。如果明確知道合并數(shù)據(jù)后的結(jié)果數(shù)據(jù)不存在重復(fù)數(shù)據(jù)，或者不需要去除重復(fù)的數(shù)據(jù)，則盡量使用UNION ALL語句，以提高數(shù)據(jù)查詢的效率。
為什么union all的效率比較高呢？首先我們?nèi)绻褂胾nion的話，它會先把數(shù)據(jù)查詢出來，緊接著還要進去去重操作，它多了一步去重操作，當(dāng)然花費的時間就比較多了，影響效率。
mysql> select *
-> from emp e
-> left outer join dept d
-> on e.deptno = d.deptno
-> union -- 并集 去重 效率低
-> select *
-> from emp e
-> right outer join dept d
-> on e.deptno = d.deptno;
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | DNAME | LOC |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 40 | OPERATIONS | BOSTON |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
15 rows in set (0.01 sec)
mysql> ^C
mysql> https://blog.csdn.net/weixin_42250835/article/details/123535439^Z^Z^C
mysql> select *
-> from emp e
-> left outer join dept d
-> on e.deptno = d.deptno
-> union -- 并集 去重 效率低
-> select *
-> from emp e
-> right outer join dept d
-> on e.deptno = d.deptno;
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | DNAME | LOC |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 40 | OPERATIONS | BOSTON |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
15 rows in set (0.00 sec)
mysql> select *
-> from emp e
-> left outer join dept d
-> on e.deptno = d.deptno
-> union all-- 并集 不去重 效率高
-> select *
-> from emp e
-> right outer join dept d
-> on e.deptno = d.deptno;
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | DNAME | LOC |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 40 | OPERATIONS | BOSTON |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
29 rows in set (0.00 sec)
為了讓大家更清楚知道他們的區(qū)別，我們分別看一下有多少記錄
-> on e.deptno = d.deptno' at line 2
mysql> select *
-> from emp e
-> left outer join dept d
-> on e.deptno = d.deptno;
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | DNAME | LOC |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
14 rows in set (0.00 sec)
mysql> select *
-> from emp e
-> right outer join dept d
-> on e.deptno = d.deptno;
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | DNAME | LOC |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 40 | OPERATIONS | BOSTON |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
15 rows in set (0.00 sec)
14+15=29所=所以可以看出union all確實是不去重
總結(jié)
中圖：內(nèi)連接 A∩B SELECT employee_id,last_name,department_name FROM employees e JOIN departments d ON e.`department_id` = d.`department_id`;
左上圖：左外連接 SELECT employee_id,last_name,department_name FROM employees e LEFT JOIN departments d ON e.`department_id` = d.`department_id`;
右上圖：右外連接 SELECT employee_id,last_name,department_name FROM employees e RIGHT JOIN departments d ON e.`department_id` = d.`department_id`;
左中圖：A - A∩B SELECT employee_id,last_name,department_name FROM employees e LEFT JOIN departments d ON e.`department_id` = d.`department_id` WHERE d.`department_id` IS NULL
右中圖：B-A∩B SELECT employee_id,last_name,department_name FROM employees e RIGHT JOIN departments d ON e.`department_id` = d.`department_id` WHERE e.`department_id` IS NULL
左下圖：滿外連接 左中圖 + 右上圖 A∪B SELECT employee_id,last_name,department_name FROM employees e LEFT JOIN departments d ON e.`department_id` = d.`department_id` WHERE d.`department_id` IS NULL UNION ALL #沒有去重操作，效率高 SELECT employee_id,last_name,department_name FROM employees e RIGHT JOIN departments d ON e.`department_id` = d.`department_id`;
右下圖 左中圖 + 右中圖 A ∪B- A∩B 或者 (A - A∩B) ∪ （B - A∩B） SELECT employee_id,last_name,department_name FROM employees e LEFT JOIN departments d ON e.`department_id` = d.`department_id` WHERE d.`department_id` IS NULL UNION ALL SELECT employee_id,last_name,department_name FROM employees e RIGHT JOIN departments d ON e.`department_id` = d.`department_id` WHERE e.`department_id` IS NULL
4.自然連接
SQL99 在 SQL92 的基礎(chǔ)上提供了一些特殊語法，比如 NATURAL JOIN 用來表示自然連接。我們可以把自然連接理解為 SQL92 中的等值連接。它會幫你自動查詢兩張連接表中所有相同的字段，然后進行等值連接。
SELECT employee_id,last_name,department_name FROM employees e NATURAL JOIN departments d;
上面的寫法的效果和下面是一樣的
SELECT employee_id,last_name,department_name FROM employees e JOIN departments d USING (department_id);
5.using連接
當(dāng)我們進行連接的時候，SQL99還支持使用 USING 指定數(shù)據(jù)表里的同名字段進行等值連接。但是只能配合JOIN一起使用。比如：
SELECT employee_id,last_name,department_name FROM employees e JOIN departments d USING (department_id);
你能看出與自然連接 NATURAL JOIN 不同的是，USING 指定了具體的相同的字段名稱，你需要在 USING 的括號 () 中填入要指定的同名字段。同時使用 JOIN...USING 可以簡化 JOIN ON 的等值連接。它與下面的 SQL 查詢結(jié)果是相同的：
SELECT employee_id,last_name,department_name FROM employees e ,departments d WHERE e.department_id = d.department_id;
注意:using只能和join配合使用，而且要求兩個關(guān)聯(lián)字段在關(guān)聯(lián)表中名稱一致，而且只能表示關(guān)聯(lián)字段值相等
三、子查詢
1.不相關(guān)子查詢
子查詢就是查詢語句的嵌套，有多個select語句
子查詢的引入:
– 查詢所有比“CLARK”工資高的員工的信息
– 步驟1：“CLARK”工資
mysql> select * from emp where ename='clark'; 工資2450 +-------+-------+---------+------+------------+---------+------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+-------+---------+------+------------+---------+------+--------+ | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | +-------+-------+---------+------+------------+---------+------+--------+ 1 row in set (0.00 sec)
– 步驟2：查詢所有工資比2450高的員工的信息
mysql> select * from emp where sal > 2450; +-------+-------+-----------+------+------------+---------+------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+-------+-----------+------+------------+---------+------+--------+ | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | +-------+-------+-----------+------+------------+---------+------+--------+ 5 rows in set (0.01 sec)
兩次命令解決問題的話，效率低 ，第二個命令依托于第一個命令，第一個命令的結(jié)果給第二個命令使用，但是
因為第一個命令的結(jié)果可能不確定要改，所以第二個命令也會導(dǎo)致修改
將步驟1和步驟2合并 --》子查詢：-- 一個命令解決問題 --》效率高
mysql> select *from emp where sal>(select sal from emp where ename='clark'); +-------+-------+-----------+------+------------+---------+------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+-------+-----------+------+------------+---------+------+--------+ | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | +-------+-------+-----------+------+------------+---------+------+--------+ 5 rows in set (0.00 sec)
【2】執(zhí)行順序：
先執(zhí)行子查詢，再執(zhí)行外查詢；
【3】不相關(guān)子查詢：
子查詢可以獨立運行，稱為不相關(guān)子查詢。
【4】不相關(guān)子查詢分類：
根據(jù)子查詢的結(jié)果行數(shù)，可以分為單行子查詢和多行子查詢。
練習(xí)
單行子查詢
mysql> -- 單行子查詢
mysql> -- 查詢工資高與拼接工資的員工名字和工資
mysql> select ename,sal from emp
-> where sal>(select avg(sal) from emp);
+-------+---------+
| ename | sal |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
+-------+---------+
6 rows in set (0.00 sec)
-- 查詢和CLARK同一部門且比他工資低的雇員名字和工資。
select ename,sal
from emp
where deptno = (select deptno from emp where ename = 'CLARK')
and
sal < (select sal from emp where ename = 'CLARK')
+--------+---------+
| ename | sal |
+--------+---------+
| MILLER | 1300.00 |
+--------+---------+
1 row in set (0.00 sec)
多行子查詢： 【1】查詢【部門20中職務(wù)同部門10的雇員一樣的】雇員信息。 -- 查詢雇員信息 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 | +-------+--------+-----------+------+------------+---------+---------+--------+ 14 rows in set (0.00 sec) -- 查詢部門20中的雇員信息 select * from emp where deptno = 20; +-------+-------+---------+------+------------+---------+------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+-------+---------+------+------------+---------+------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | +-------+-------+---------+------+------------+---------+------+--------+ 5 rows in set (0.00 sec) -- 部門10的雇員的職務(wù)： select job from emp where deptno = 10; -- MANAGER,PRESIDENT,CLERK +-----------+ | job | +-----------+ | MANAGER | | PRESIDENT | | CLERK | +-----------+ 3 rows in set (0.00 sec) -- 查詢部門20中職務(wù)同部門10的雇員一樣的雇員信息。 select * from emp where deptno = 20 and job in (select job from emp where deptno = 10) -- > Subquery returns more than 1 row select * from emp where deptno = 20 and job = any(select job from emp where deptno = 10)
【2】查詢工資比所有的“SALESMAN”都高的雇員的編號、名字和工資。 -- 查詢雇員的編號、名字和工資 select empno,ename,sal from emp +-------+--------+---------+ | empno | ename | sal | +-------+--------+---------+ | 7369 | SMITH | 800.00 | | 7499 | ALLEN | 1600.00 | | 7521 | WARD | 1250.00 | | 7566 | JONES | 2975.00 | | 7654 | MARTIN | 1250.00 | | 7698 | BLAKE | 2850.00 | | 7782 | CLARK | 2450.00 | | 7788 | SCOTT | 3000.00 | | 7839 | KING | 5000.00 | | 7844 | TURNER | 1500.00 | | 7876 | ADAMS | 1100.00 | | 7900 | JAMES | 950.00 | | 7902 | FORD | 3000.00 | | 7934 | MILLER | 1300.00 | +-------+--------+---------+ 14 rows in set (0.00 sec) -- “SALESMAN”的工資： select sal from emp where job = 'SALESMAN'; +---------+ | sal | +---------+ | 1600.00 | | 1250.00 | | 1250.00 | | 1500.00 | +---------+ 4 rows in set (0.00 sec) -- 查詢工資比所有的“SALESMAN”都高的雇員的編號、名字和工資。 -- 多行子查詢： select empno,ename,sal from emp where sal > all(select sal from emp where job = 'SALESMAN'); +-------+-------+---------+ | empno | ename | sal | +-------+-------+---------+ | 7566 | JONES | 2975.00 | | 7698 | BLAKE | 2850.00 | | 7782 | CLARK | 2450.00 | | 7788 | SCOTT | 3000.00 | | 7839 | KING | 5000.00 | | 7902 | FORD | 3000.00 | +-------+-------+---------+ 6 rows in set (0.00 sec)
2.相關(guān)子查詢
【1】不相關(guān)的子查詢引入：
不相關(guān)的子查詢：子查詢可以獨立運行，先運行子查詢，再運行外查詢。
相關(guān)子查詢：子查詢不可以獨立運行，并且先運行外查詢，再運行子查詢
【2】不相關(guān)的子查詢優(yōu)缺點：
好處：簡單 功能強大（一些使用不相關(guān)子查詢不能實現(xiàn)或者實現(xiàn)繁瑣的子查詢，可以使用相關(guān)子查詢實現(xiàn)）
缺點：稍難理解
【3】sql展示：
-- 【1】查詢最高工資的員工 （不相關(guān)子查詢） select * from emp where sal = (select max(sal) from emp) -- 【2】查詢本部門最高工資的員工 （相關(guān)子查詢） -- 方法1：通過不相關(guān)子查詢實現(xiàn)： select * from emp where deptno = 10 and sal = (select max(sal) from emp where deptno = 10) union select * from emp where deptno = 20 and sal = (select max(sal) from emp where deptno = 20) union select * from emp where deptno = 30 and sal = (select max(sal) from emp where deptno = 30) -- 缺點：語句比較多，具體到底有多少個部分未知 -- 方法2： 相關(guān)子查詢 select * from emp e where sal = (select max(sal) from emp where deptno = e.deptno) order by deptno -- 【3】查詢工資高于其所在崗位的平均工資的那些員工 （相關(guān)子查詢） -- 不相關(guān)子查詢： select * from emp where job = 'CLERK' and sal >= (select avg(sal) from emp where job = 'CLERK') union ...... -- 相關(guān)子查詢： select * from emp e where sal >= (select avg(sal) from emp e2 where e2.job = e.job)
四、聚合函數(shù)
1.聚合函數(shù)介紹
聚合函數(shù)作用于一組數(shù)據(jù)，并對一組數(shù)據(jù)返回一個值。
聚合函數(shù)類型
- AVG()
- SUM()
- MAX()
- MIN()
- COUNT()
語法
注意:聚合函數(shù)不允許嵌套使用
1.1 AVG和SUM函數(shù)
可以對數(shù)值型數(shù)據(jù)使用AVG 和 SUM 函數(shù)。
他們在計算有空值的時候，會把非空計算進去，然后自動忽略空值
AVG=SUM/COUNT
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 | +-------+--------+-----------+------+------------+---------+---------+--------+ 14 rows in set (0.00 sec)
1.2 MIN和MAX函數(shù)
可以對任意數(shù)據(jù)類型的數(shù)據(jù)使用 MIN 和 MAX 函數(shù)。
1.3 COUNT函數(shù)
COUNT(*)返回表中記錄總數(shù)，適用于任意數(shù)據(jù)類型。
mysql> select count(*) from emp; +----------+ | count(*) | +----------+ | 14 | +----------+ 1 row in set (0.01 sec)
計算指定字段再查詢結(jié)果中出現(xiàn)的個數(shù)
mysql> select count(comm) from emp; +-------------+ | count(comm) | +-------------+ | 4 | +-------------+ 1 row in set (0.00 sec)
COUNT(expr) 返回expr不為空的記錄總數(shù)。
-問題：用count(*)，count(1)，count(列名)誰好呢?
其實，對于MyISAM引擎的表是沒有區(qū)別的。這種引擎內(nèi)部有一計數(shù)器在維護著行數(shù)。
Innodb引擎的表用count(*),count(1)直接讀行數(shù)，復(fù)雜度是O(n)，因為innodb真的要去數(shù)一遍。但好于具體的count(列名)。
問題：能不能使用count(列名)替換count(*)?
不要使用 count(列名)來替代 count(*)，count(*)是 SQL92 定義的標(biāo)準(zhǔn)統(tǒng)計行數(shù)的語法，跟數(shù)據(jù)庫無關(guān)，跟 NULL 和非 NULL 無關(guān)。
說明： count(*)會統(tǒng)計值為 NULL 的行，而 count(列名)不會統(tǒng)計此列為 NULL 值的行。
這樣子講的話，大家可能還比較懵，接下來，我來演示一下
2.group by
使用group by可以進行分組，我們以前使用avg可以求出所有員工的平均工資，但是如果我們想要求各個部門的員工的平均工資的話，就得對部門進行分組，以部門為單位來劃分，然后求出他們各自的平均工資
注意:字段不可以和多行函數(shù)一起使用，因為記錄個數(shù)不匹配，這樣就會導(dǎo)致查詢的數(shù)據(jù)沒有全部展示，但是，如果這個字段屬于分組是可以的
mysql> select deptno,avg(sal) from emp group by deptno; +--------+-------------+ | deptno | avg(sal) | +--------+-------------+ | 20 | 2175.000000 | | 30 | 1566.666667 | | 10 | 2916.666667 | +--------+-------------+ 3 rows in set (0.00 sec)
統(tǒng)計各個崗位的平均工資 mysql> select job,avg(sal) from emp group by job; +-----------+-------------+ | job | avg(sal) | +-----------+-------------+ | CLERK | 1037.500000 | | SALESMAN | 1400.000000 | | MANAGER | 2758.333333 | | ANALYST | 3000.000000 | | PRESIDENT | 5000.000000 | +-----------+-------------+ 5 rows in set (0.00 sec)
3.使用having進行分組后的篩選
使用having的條件:
1 行已經(jīng)被分組。
2. 使用了聚合函數(shù)。
3. 滿足HAVING 子句中條件的分組將被顯示。
4. HAVING 不能單獨使用，必須要跟 GROUP BY 一起使用。
統(tǒng)計各個部門的平均工資 ,只顯示平均工資2000以上的 - 分組以后進行二次篩選 having
mysql> select deptno,avg(sal) from emp
-> group by deptno
-> having avg(sal) >2000;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 20 | 2175.000000 |
| 10 | 2916.666667 |
+--------+-------------+
2 rows in set (0.01 sec)
五、where和having的對比
區(qū)別1：WHERE 可以直接使用表中的字段作為篩選條件，但不能使用分組中的計算函數(shù)作為篩選條件；HAVING 必須要與 GROUP BY 配合使用，可以把分組計算的函數(shù)和分組字段作為篩選條件。
這決定了，在需要對數(shù)據(jù)進行分組統(tǒng)計的時候，HAVING 可以完成 WHERE 不能完成的任務(wù)。這是因為，在查詢語法結(jié)構(gòu)中，WHERE 在 GROUP BY 之前，所以無法對分組結(jié)果進行篩選。HAVING 在 GROUP BY 之后，可以使用分組字段和分組中的計算函數(shù)，對分組的結(jié)果集進行篩選，這個功能是 WHERE 無法完成的。另外，WHERE排除的記錄不再包括在分組中。
區(qū)別2：如果需要通過連接從關(guān)聯(lián)表中獲取需要的數(shù)據(jù)，WHERE 是先篩選后連接，而 HAVING 是先連接后篩選。 這一點，就決定了在關(guān)聯(lián)查詢中，WHERE 比 HAVING 更高效。因為 WHERE 可以先篩選，用一個篩選后的較小數(shù)據(jù)集和關(guān)聯(lián)表進行連接，這樣占用的資源比較少，執(zhí)行效率也比較高。HAVING 則需要先把結(jié)果集準(zhǔn)備好，也就是用未被篩選的數(shù)據(jù)集進行關(guān)聯(lián)，然后對這個大的數(shù)據(jù)集進行篩選，這樣占用的資源就比較多，執(zhí)行效率也較低。
小結(jié)如下：
開發(fā)中的選擇：
WHERE 和 HAVING 也不是互相排斥的，我們可以在一個查詢里面同時使用 WHERE 和 HAVING。包含分組統(tǒng)計函數(shù)的條件用 HAVING，普通條件用 WHERE。這樣，我們就既利用了 WHERE 條件的高效快速，又發(fā)揮了 HAVING 可以使用包含分組統(tǒng)計函數(shù)的查詢條件的優(yōu)點。當(dāng)數(shù)據(jù)量特別大的時候，運行效率會有很大的差別。
六、select的執(zhí)行過程
1.關(guān)鍵字順序
SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT…
2.SELECT 語句的執(zhí)行順序
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT
比如你寫了一個 SQL 語句，那么它的關(guān)鍵字順序和執(zhí)行順序是下面這樣的：
SELECT DISTINCT player_id, player_name, count(*) as num 順序 5 FROM player JOIN team ON player.team_id = team.team_id 順序 1 WHERE height > 1.80 順序 2 GROUP BY player.team_id 順序 3 HAVING num > 2 順序 4 ORDER BY num DESC 順序 6 LIMIT 2 順序 7
3.SQL的執(zhí)行原理(先了解)
SELECT 是先執(zhí)行 FROM 這一步的。在這個階段，如果是多張表聯(lián)查，還會經(jīng)歷下面的幾個步驟：
- 首先先通過 CROSS JOIN 求笛卡爾積，相當(dāng)于得到虛擬表 vt（virtual table）1-1；
- 通過 ON 進行篩選，在虛擬表 vt1-1 的基礎(chǔ)上進行篩選，得到虛擬表 vt1-2；
- 添加外部行。如果我們使用的是左連接、右鏈接或者全連接，就會涉及到外部行，也就是在虛擬表 vt1-2 的基礎(chǔ)上增加外部行，得到虛擬表 vt1-3。
當(dāng)然如果我們操作的是兩張以上的表，還會重復(fù)上面的步驟，直到所有表都被處理完為止。這個過程得到是我們的原始數(shù)據(jù)。
當(dāng)我們拿到了查詢數(shù)據(jù)表的原始數(shù)據(jù)，也就是最終的虛擬表 vt1，就可以在此基礎(chǔ)上再進行 WHERE 階段。在這個階段中，會根據(jù) vt1 表的結(jié)果進行篩選過濾，得到虛擬表 vt2。
然后進入第三步和第四步，也就是 GROUP 和 HAVING 階段。在這個階段中，實際上是在虛擬表 vt2 的基礎(chǔ)上進行分組和分組過濾，得到中間的虛擬表 vt3 和 vt4。
當(dāng)我們完成了條件篩選部分之后，就可以篩選表中提取的字段，也就是進入到 SELECT 和 DISTINCT 階段。
首先在 SELECT 階段會提取想要的字段，然后在 DISTINCT 階段過濾掉重復(fù)的行，分別得到中間的虛擬表 vt5-1 和 vt5-2。
當(dāng)我們提取了想要的字段數(shù)據(jù)之后，就可以按照指定的字段進行排序，也就是 ORDER BY 階段，得到虛擬表 vt6。
最后在 vt6 的基礎(chǔ)上，取出指定行的記錄，也就是 LIMIT 階段，得到最終的結(jié)果，對應(yīng)的是虛擬表 vt7。
當(dāng)然我們在寫 SELECT 語句的時候，不一定存在所有的關(guān)鍵字，相應(yīng)的階段就會省略。
同時因為 SQL 是一門類似英語的結(jié)構(gòu)化查詢語言，所以我們在寫 SELECT 語句的時候，還要注意相應(yīng)的關(guān)鍵字順序，所謂底層運行的原理，就是我們剛才講到的執(zhí)行順序。
總結(jié)
到此這篇關(guān)于MySQL數(shù)據(jù)庫查詢進階之多表查詢的文章就介紹到這了,更多相關(guān)MySQL多表查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了MySQL定時備份數(shù)據(jù)庫操作,結(jié)合實例形式分析了MySQL定時備份數(shù)據(jù)庫相關(guān)命令、原理、實現(xiàn)方法及操作注意事項,需要的朋友可以參考下2020-03-03
Mysql允許他人訪問本機數(shù)據(jù)庫的實現(xiàn)步驟
mysql允許其他電腦訪問,授權(quán)mysql允許其他電腦訪問,本文就來介紹一下Mysql允許他人訪問本機數(shù)據(jù)庫的實現(xiàn)步驟,文中通過圖文介紹的非常詳細,對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2024-11-11
mysql 本地數(shù)據(jù)庫如何從遠程數(shù)據(jù)庫導(dǎo)數(shù)據(jù)
mysql 本地數(shù)據(jù)庫如何從遠程數(shù)據(jù)庫導(dǎo)數(shù)據(jù),本文以此問題進行詳細介紹，需要了解的朋友可以參考下2012-11-11
Mysql賬號管理與引擎相關(guān)功能實現(xiàn)流程
Mysql中的每一種技術(shù)都使用不同的存儲機制、索引技巧、鎖定水平、并且最終提供廣泛的不同功能和能力。通過選擇不同的技術(shù)，你能夠獲得額外的速度或者功能，從而改善應(yīng)用的整體功能。這些不同的技術(shù)以及配套的相關(guān)功能在MySQL中被稱作存儲引擎2022-10-10
MySQL中你可以使用SELECT…INTO OUTFILE語句來簡單的導(dǎo)出數(shù)據(jù)到文本文件上,這篇文章給大家介紹了Mysql 中如何導(dǎo)出數(shù)據(jù),感興趣的朋友跟隨小編一起看看吧2023-11-11
Mysql 數(shù)據(jù)庫常用備份方法和注意事項 ，需要的朋友可以參考下。2010-04-04
MySQL高速緩存啟動方法及參數(shù)詳解(query_cache_size)
這篇文章主要介紹了MySQL高速緩存啟動方法及參數(shù)詳解(query_cache_size),需要的朋友可以參考下2014-12-12
MySQL 8.0 驅(qū)動與阿里druid版本兼容問題解決
MySQL 8.0 驅(qū)動與阿里druid版本不兼容會導(dǎo)致有報錯問題，本文就詳細的介紹一下解決方法，具有一定的參考價值，感興趣的可以了解一下2021-07-07
這篇文章主要給大家介紹了關(guān)于MySQL中exists、in及any的基本用法，文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-03-03
MySQL使用binlog日志進行數(shù)據(jù)庫遷移和數(shù)據(jù)恢復(fù)
MySQL的二進制日志是MySQL數(shù)據(jù)庫中非常關(guān)鍵的一個組件,主要用于記錄所有數(shù)據(jù)庫表結(jié)構(gòu)或表數(shù)據(jù)改變的操作語句,binlog是MySQL數(shù)據(jù)復(fù)制的基礎(chǔ),并且常常被用于數(shù)據(jù)恢復(fù),本文給大家介紹了MySQL使用binlog日志進行數(shù)據(jù)庫遷移和數(shù)據(jù)恢復(fù),需要的朋友可以參考下2024-04-04