溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
今天小編給大家分享一下MySQL數據庫查詢中怎么實現多表查詢的相關知識點，內容詳細，邏輯清晰，相信大部分人都還太了解這方面的知識，所以分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后有所收獲，下面我們一起來了解一下吧。
多表查詢，也稱為關聯查詢，指兩個或更多個表一起完成查詢操作。
前提條件：這些一起查詢的表之間是有關系的（一對一、一對多），它們之間一定是有關聯字段，這個關聯字段可能建立了外鍵，也可能沒有建立外鍵。比如：員工表和部門表，這兩個表依靠“部門編號”進行關聯。
假如我們現在要查詢員工的姓名還有部門名稱
這兩個字段在不同表中，如果沒有關聯條件的話，查詢出來的結果會怎么樣呢，讓我們來看看。
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
很明顯上面的操作是錯誤的
上面的操作，會導致員工表的一條記錄會和部門表的每一條記錄相匹配，就好像一個員工在所有部門都工作過一樣，從現實角度來說，很明顯，是不會出現這種情況的，
這種現象就是笛卡爾積。
笛卡兒積就是關系代數里的一個概念，表示兩個表中的每一行數據任意組合的結果。比如：有兩個表，左表有m條數據記錄，x個字段，右表有n條數據記錄，y個字段，則執行交叉連接后將返回m*n條數據記錄，x+y個字段。笛卡兒積示意圖如圖所示。
SQL92中，笛卡爾積也稱為交叉連接，英文是
CROSS JOIN。在 SQL99 中也是使用 CROSS JOIN表示交叉連接。它的作用就是可以把任意表進行連接，即使這兩張表不相關。在MySQL中如下情況會出現笛卡爾積：
查詢員工姓名和所在部門名稱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;
笛卡爾積的錯誤會在下面條件下產生：
笛卡爾積的錯誤會在下面條件下產生：
省略多個表的連接條件（或關聯條件）
連接條件（或關聯條件）無效
所有表中的所有行互相連接
為了避免笛卡爾積， 可以在 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;
等值連接其實很好理解，就是誰等于誰的意思，使用=。
非等值連接的話，比如查詢某個字段>某個值的記錄等等
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;需要注意的是，如果我們使用了表的別名，在查詢字段中、過濾條件中就只能使用別名進行代替，不能使用原有的表名，否則就會報錯。
自連接，它的字面意思就是自己和自己連接
比如說現在有一張表，我們想要查找員工信息和對應的上級信息
我們知道，只有一張表是沒辦法把它們關聯起來的，要想把它們他們關聯起來，肯定是要有關聯條件的，那么就應該要有兩張表，這個時候，我們就可以抽取出一張表，和本來的表本質上是一樣的，然后我們對表起別名，table1和table2本質上是同一張表，只是用取別名的方式虛擬成兩張表以代表不同的意義。然后兩個表再進行內連接，外連接等查詢。
比如說：現在我們想要查找員工和對應老板的名字，我們就可以使用自連接
SELECT CONCAT(worker.last_name ,' works for ' , manager.last_name) FROM employees worker, employees manager WHERE worker.manager_id = manager.employee_id ;練習：查詢出last_name為 ‘Chen’ 的員工的 manager 的信息。
內連接: 合并具有同一列的兩個以上的表的行, 結果集中不包含一個表與另一個表不匹配的行
外連接: 兩個表在連接過程中除了返回滿足連接條件的行以外還返回左（或右）表中不滿足條件的行 ，這種連接稱為左（或右） 外連接。沒有匹配的行時, 結果表中相應的列為空(NULL)。
如果是左外連接，則連接條件中左邊的表也稱為主表，右邊的表稱為從表。
如果是右外連接，則連接條件中右邊的表也稱為主表，左邊的表稱為從表。
外連接查詢的數據比較多
SQL92：使用(+)創建連接在 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語法實現多表查詢
1.基本語法
使用JOIN…ON子句創建連接的語法結構：SELECT table1.column, table2.column,table3.column FROM table1 JOIN table2 ON table1 和 table2 的連接條件 JOIN table3 ON table2 和 table3 的連接條件語法說明：
可以使用 ON 子句指定額外的連接條件 。
這個連接條件是與其它條件分開的。ON 子句使語句具有更高的易讀性。關鍵字 JOIN、INNER JOIN、CROSS JOIN 的含義是一樣的，都表示內連接2.內連接(INNER JOIN)
語法
select 字段
from 表1
join 表2 on 兩個表的連接條件
where 其他子句
比如我們現在想要查詢各個部門的員工的信息，他們的連接條件就是員工表中部門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); 這里截取部分結果 +-------------+-------------+---------------+---------------+-------------+ | 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 |
使用內連接的一個問題就是他們把所有的信息都顯示出來，它只能夠顯示匹配的數據，而外連接可以把不匹配的數據也顯示出來
先來看看表的數據，方便后續操作
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號部分沒有員工，沒有顯示在查詢結果中
– 2.員工scott沒有部門，沒有顯示在查詢結果中
所以想顯示所有數據，要使用外連接
外連接(OUTER JOIN)
1.左外連接左外連接： left outer join – 左面的那個表的信息，即使不匹配也可以查看出效果
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 關聯條件
WHERE 等其他子句;2.右外連接
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 關聯條件
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)
滿外連接的結果 = 左右表匹配的數據 + 左表沒有匹配到的數據 + 右表沒有匹配到的數據。
SQL99是支持滿外連接的。使用FULL JOIN 或 FULL OUTER JOIN來實現。
需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。
在講滿外連接之前，我們先來介紹一下union關鍵字的使用，相信看了以后大家就清楚了
合并查詢結果
利用UNION關鍵字，可以給出多條SELECT語句，并將它們的結果組合成單個結果集。合并時，兩個表對應的列數和數據類型必須相同，并且相互對應。各個SELECT語句之間使用UNION或UNION ALL關鍵字分隔。
語法格式：
SELECT column,… FROM table1
UNION [ALL]
SELECT column,… FROM table2
UNION操作符
UNION 操作符返回兩個查詢的結果集的并集，去除重復記錄。
`UNION ALL操作符
UNION ALL操作符返回兩個查詢的結果集的并集。對于兩個結果集的重復部分，不去重。
注意：執行UNION ALL語句時所需要的資源比UNION語句少。如果明確知道合并數據后的結果數據不存在重復數據，或者不需要去除重復的數據，則盡量使用UNION ALL語句，以提高數據查詢的效率。
為什么union all的效率比較高呢？首先我們如果使用union的話，它會先把數據查詢出來，緊接著還要進去去重操作，它多了一步去重操作，當然花費的時間就比較多了，影響效率。
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)
為了讓大家更清楚知道他們的區別，我們分別看一下有多少記錄
-> 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確實是不去重
中圖：內連接 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
SQL99 在 SQL92 的基礎上提供了一些特殊語法，比如 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);
當我們進行連接的時候，SQL99還支持使用 USING 指定數據表里的同名字段進行等值連接。但是只能配合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 查詢結果是相同的：
SELECT employee_id,last_name,department_name FROM employees e ,departments d WHERE e.department_id = d.department_id;
注意:using只能和join配合使用，而且要求兩個關聯字段在關聯表中名稱一致，而且只能表示關聯字段值相等
子查詢就是查詢語句的嵌套，有多個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)
兩次命令解決問題的話，效率低 ，第二個命令依托于第一個命令，第一個命令的結果給第二個命令使用，但是
因為第一個命令的結果可能不確定要改，所以第二個命令也會導致修改
將步驟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】執行順序：
先執行子查詢，再執行外查詢；
【3】不相關子查詢：
子查詢可以獨立運行，稱為不相關子查詢。
【4】不相關子查詢分類：
根據子查詢的結果行數，可以分為單行子查詢和多行子查詢。
練習
單行子查詢
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中職務同部門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的雇員的職務： select job from emp where deptno = 10; -- MANAGER,PRESIDENT,CLERK +-----------+ | job | +-----------+ | MANAGER | | PRESIDENT | | CLERK | +-----------+ 3 rows in set (0.00 sec) -- 查詢部門20中職務同部門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)
【1】不相關的子查詢引入：
不相關的子查詢：子查詢可以獨立運行，先運行子查詢，再運行外查詢。
相關子查詢：子查詢不可以獨立運行，并且先運行外查詢，再運行子查詢
【2】不相關的子查詢優缺點：
好處：簡單 功能強大（一些使用不相關子查詢不能實現或者實現繁瑣的子查詢，可以使用相關子查詢實現）
缺點：稍難理解
【3】sql展示：
-- 【1】查詢最高工資的員工 （不相關子查詢） select * from emp where sal = (select max(sal) from emp) -- 【2】查詢本部門最高工資的員工 （相關子查詢） -- 方法1：通過不相關子查詢實現： 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： 相關子查詢 select * from emp e where sal = (select max(sal) from emp where deptno = e.deptno) order by deptno -- 【3】查詢工資高于其所在崗位的平均工資的那些員工 （相關子查詢） -- 不相關子查詢： select * from emp where job = 'CLERK' and sal >= (select avg(sal) from emp where job = 'CLERK') union ...... -- 相關子查詢： select * from emp e where sal >= (select avg(sal) from emp e2 where e2.job = e.job)
聚合函數作用于一組數據，并對一組數據返回一個值。
聚合函數類型
AVG()
SUM()
MAX()
MIN()
COUNT()
語法
注意:聚合函數不允許嵌套使用
可以對數值型數據使用AVG 和 SUM 函數。
他們在計算有空值的時候，會把非空計算進去，然后自動忽略空值
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)
可以對任意數據類型的數據使用 MIN 和 MAX 函數。
COUNT(*)返回表中記錄總數，適用于任意數據類型。
mysql> select count(*) from emp; +----------+ | count(*) | +----------+ | 14 | +----------+ 1 row in set (0.01 sec)
計算指定字段再查詢結果中出現的個數
mysql> select count(comm) from emp; +-------------+ | count(comm) | +-------------+ | 4 | +-------------+ 1 row in set (0.00 sec)
COUNT(expr) 返回expr不為空的記錄總數。
-問題：用count(*)，count(1)，count(列名)誰好呢?
其實，對于MyISAM引擎的表是沒有區別的。這種引擎內部有一計數器在維護著行數。
Innodb引擎的表用count(*),count(1)直接讀行數，復雜度是O(n)，因為innodb真的要去數一遍。但好于具體的count(列名)。
問題：能不能使用count(列名)替換count(*)?
不要使用 count(列名)來替代 count(*)，count(*)是 SQL92 定義的標準統計行數的語法，跟數據庫無關，跟 NULL 和非 NULL 無關。
說明： count(*)會統計值為 NULL 的行，而 count(列名)不會統計此列為 NULL 值的行。
這樣子講的話，大家可能還比較懵，接下來，我來演示一下
使用group by可以進行分組，我們以前使用avg可以求出所有員工的平均工資，但是如果我們想要求各個部門的員工的平均工資的話，就得對部門進行分組，以部門為單位來劃分，然后求出他們各自的平均工資
注意:字段不可以和多行函數一起使用，因為記錄個數不匹配，這樣就會導致查詢的數據沒有全部展示，但是，如果這個字段屬于分組是可以的
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)
統計各個崗位的平均工資 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)
使用having的條件:
1 行已經被分組。
2. 使用了聚合函數。
3. 滿足HAVING 子句中條件的分組將被顯示。
4. HAVING 不能單獨使用，必須要跟 GROUP BY 一起使用。
統計各個部門的平均工資 ,只顯示平均工資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)
區別1：WHERE 可以直接使用表中的字段作為篩選條件，但不能使用分組中的計算函數作為篩選條件；HAVING 必須要與 GROUP BY 配合使用，可以把分組計算的函數和分組字段作為篩選條件。
這決定了，在需要對數據進行分組統計的時候，HAVING 可以完成 WHERE 不能完成的任務。這是因為，在查詢語法結構中，WHERE 在 GROUP BY 之前，所以無法對分組結果進行篩選。HAVING 在 GROUP BY 之后，可以使用分組字段和分組中的計算函數，對分組的結果集進行篩選，這個功能是 WHERE 無法完成的。另外，WHERE排除的記錄不再包括在分組中。
區別2：如果需要通過連接從關聯表中獲取需要的數據，WHERE 是先篩選后連接，而 HAVING 是先連接后篩選。 這一點，就決定了在關聯查詢中，WHERE 比 HAVING 更高效。因為 WHERE 可以先篩選，用一個篩選后的較小數據集和關聯表進行連接，這樣占用的資源比較少，執行效率也比較高。HAVING 則需要先把結果集準備好，也就是用未被篩選的數據集進行關聯，然后對這個大的數據集進行篩選，這樣占用的資源就比較多，執行效率也較低。
小結如下：
開發中的選擇：
WHERE 和 HAVING 也不是互相排斥的，我們可以在一個查詢里面同時使用 WHERE 和 HAVING。包含分組統計函數的條件用 HAVING，普通條件用 WHERE。這樣，我們就既利用了 WHERE 條件的高效快速，又發揮了 HAVING 可以使用包含分組統計函數的查詢條件的優點。當數據量特別大的時候，運行效率會有很大的差別。
SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT…
2.SELECT 語句的執行順序
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT
比如你寫了一個 SQL 語句，那么它的關鍵字順序和執行順序是下面這樣的：
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
SELECT 是先執行 FROM 這一步的。在這個階段，如果是多張表聯查，還會經歷下面的幾個步驟：
首先先通過 CROSS JOIN 求笛卡爾積，相當于得到虛擬表 vt（virtual table）1-1；
通過 ON 進行篩選，在虛擬表 vt1-1 的基礎上進行篩選，得到虛擬表 vt1-2；
添加外部行。如果我們使用的是左連接、右鏈接或者全連接，就會涉及到外部行，也就是在虛擬表 vt1-2 的基礎上增加外部行，得到虛擬表 vt1-3。
當然如果我們操作的是兩張以上的表，還會重復上面的步驟，直到所有表都被處理完為止。這個過程得到是我們的原始數據。
當我們拿到了查詢數據表的原始數據，也就是最終的虛擬表 vt1，就可以在此基礎上再進行 WHERE 階段。在這個階段中，會根據 vt1 表的結果進行篩選過濾，得到虛擬表 vt2。
然后進入第三步和第四步，也就是 GROUP 和 HAVING 階段。在這個階段中，實際上是在虛擬表 vt2 的基礎上進行分組和分組過濾，得到中間的虛擬表 vt3 和 vt4。
當我們完成了條件篩選部分之后，就可以篩選表中提取的字段，也就是進入到 SELECT 和 DISTINCT 階段。
首先在 SELECT 階段會提取想要的字段，然后在 DISTINCT 階段過濾掉重復的行，分別得到中間的虛擬表 vt5-1 和 vt5-2。
當我們提取了想要的字段數據之后，就可以按照指定的字段進行排序，也就是 ORDER BY 階段，得到虛擬表 vt6。
最后在 vt6 的基礎上，取出指定行的記錄，也就是 LIMIT 階段，得到最終的結果，對應的是虛擬表 vt7。
當然我們在寫 SELECT 語句的時候，不一定存在所有的關鍵字，相應的階段就會省略。
同時因為 SQL 是一門類似英語的結構化查詢語言，所以我們在寫 SELECT 語句的時候，還要注意相應的關鍵字順序，所謂底層運行的原理，就是我們剛才講到的執行順序。
以上就是“MySQL數據庫查詢中怎么實現多表查詢”這篇文章的所有內容，感謝各位的閱讀！相信大家閱讀完這篇文章都有很大的收獲，小編每天都會為大家更新不同的知識，如果還想學習更多的知識，請關注億速云行業資訊頻道。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
