Heim >Datenbank >MySQL-Tutorial >So implementieren Sie eine Mehrtabellenabfrage in einer MySQL-Datenbankabfrage
So implementieren Sie eine Mehrtabellenabfrage in einer MySQL-Datenbankabfrage
- WBOYnach vorne
- 2023-05-29 16:18:265768Durchsuche
1. Abfrage mit mehreren Tabellen
Abfrage mit mehreren Tabellen, auch als zugeordnete Abfrage bezeichnet, bezieht sich auf zwei oder mehr Tabellen, die den Abfragevorgang gemeinsam abschließen.
Voraussetzung: Es besteht eine Beziehung zwischen diesen zusammen abgefragten Tabellen (eins-zu-eins, eins-zu-viele). Für dieses verwandte Feld muss möglicherweise ein Fremdschlüssel vorhanden sein. Beispiel: Mitarbeitertabelle und Abteilungstabelle. Diese beiden Tabellen sind durch die „Abteilungsnummer“ verknüpft.
1. Einleitung
Angenommen, wir möchten nun den Namen des Mitarbeiters und den Namen der Abteilung abfragen.
Diese beiden Felder befinden sich in unterschiedlichen Tabellen. Wie sehen die Abfrageergebnisse aus? Schauen Sie mal rein.
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
Offensichtlich ist die obige Operation falsch
Die obige Operation führt dazu, dass ein Datensatz in der Mitarbeitertabelle mit jedem Datensatz in der Abteilungstabelle übereinstimmt, als ob ein Mitarbeiter in allen Abteilungen gearbeitet hätte Es ist offensichtlich, dass diese Situation nicht auftreten wird. Dieses Phänomen ist das kartesische Produkt.
In SQL92 wird das kartesische Produkt auch als Cross-Join bezeichnet, was auf Englisch3. Lösung für das kartesische Produktist. In SQL99 wird CROSS JOIN auch zur Darstellung von Querverbindungen verwendet. Seine Funktion besteht darin, jede Tabelle zu verknüpfen, auch wenn die beiden Tabellen nicht miteinander verbunden sind. In MySQL tritt das kartesische Produkt in den folgenden Situationen auf:
CROSS JOINFragen Sie den Namen des Mitarbeiters und den Namen der Abteilung abSELECT 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;
Der Fehler beim kartesischen Produkt tritt unter den folgenden Bedingungen auf :
CARTESISCHE PRODUKTE FEHLER MEN unter folgenden Bedingungen Alle Tabellen Alle Zeilen in sind miteinander verbunden
- gültige Verbindungsbedingungen in WHERE hinzufügen.
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;
Hinweis: Wenn es in verschiedenen Tabellen dieselben Felder gibt, müssen wir angeben, welche Tabellenfelder wir überprüfen. Der Feldname ähnelt dem Klassennamen in Java. Das ist einfach verstehen.
SELECT employees.last_name, departments.department_name,employees.department_id FROM employees, departments WHERE employees.department_id = departments.department_id;
2. Klassifizierung mehrerer Tabellenabfragen 1. Äquivalente Verknüpfungen und nicht äquivalente Verknüpfungen
- Für nicht äquivalente Verknüpfungen, wie zum Beispiel das Abfragen von Datensätzen eines bestimmten Felds > eines bestimmten Werts usw.
- Um kartesisches Produkt zu vermeiden, können Sie
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;Erweiterung:Die Verwendung von Aliasen kann die Abfrage vereinfachen. — Die Verwendung des Tabellennamenpräfixes vor dem Spaltennamen kann die Abfrageeffizienz verbessern.
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;Es ist zu beachten, dass wir bei Verwendung des Alias der Tabelle nur den Alias im Abfragefeld und in der Filterbedingung verwenden können und nicht den ursprünglichen Tabellennamen verwenden können, da sonst ein Fehler gemeldet wird. 2. Selbstverbindung und Nicht-Selbstverbindung Wissen Sie, dass es keine Möglichkeit gibt, sie einer Tabelle zuzuordnen. Wenn Sie sie verknüpfen möchten, müssen dann zwei Tabellen vorhanden sein Dasselbe, und dann sind die Tabellen Tabelle1 und Tabelle2 im Wesentlichen dieselben Tabellen, aber sie werden mithilfe von Aliasen in zwei Tabellen virtualisiert, um unterschiedliche Bedeutungen darzustellen. Anschließend werden die beiden Tabellen mit Inner-Joins, Outer-Joins usw. abgefragt.
Zum Beispiel: Jetzt wollen wir die Namen von Mitarbeitern und entsprechenden Vorgesetzten finden, wir können die Selbstverbindung verwenden3. Inner-Joins und Outer-JoinsSELECT CONCAT(worker.last_name ,' works for ' , manager.last_name) FROM employees worker, employees manager WHERE worker.manager_id = manager.employee_id ;
Übung: Fragen Sie die Managerinformationen des Mitarbeiters ab, dessen Nachname „Chen’ ist.
Inner-Joins: Zeilen aus mehr als zwei Tabellen mit derselben Spalte zusammenführen,
Die Ergebnismenge enthält keine Zeilen, die nicht mit einer Tabelle mit einer anderen Tabelle übereinstimmen
外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(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关键字的使用，相信看了以后大家就清楚了4.UNION
合并查询结果
使用UNION关键字，可以将多个SELECT语句的结果组合成一个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。使用UNION或UNION ALL关键字来分隔各个SELECT语句。
语法格式：
SELECT column,… FROM table1
UNION [ALL]
SELECT column,… FROM table2UNION操作符
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 NULL4.自然连接
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);5.using连接
当我们进行连接的时候，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配合使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字段值相等
三、子查询
1.不相关子查询
子查询就是查询语句的嵌套，有多个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)2.相关子查询
【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)四、聚合函数
1.聚合函数介绍
聚合函数作用于一组数据，并对一组数据返回一个值。
聚合函数类型
AVG()
SUM()
MAX()
MIN()
COUNT()
语法
注意:聚合函数不允许嵌套使用
1.1 AVG和SUM函数
可以对数值型数据使用AVG 和 SUM 函数。
他们在计算有空值的时候，会把非空计算进去，然后自动忽略空值
AVG=SUM/COUNTmysql> 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函数
可以对任意数据类型的数据使用 MIN 和 MAX 函数。
1.3 COUNT函数
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 值的行。
这样子讲的话，大家可能还比较懵，接下来，我来演示一下
2.group by
使用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)3.使用having进行分组后的筛选
使用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)五、where和having的对比
区别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 is used for conditions that involve grouping and aggregation functions, while WHERE is used for regular conditions.。这样，我们就既利用了 WHERE 条件的高效快速，又发挥了 HAVING 可以使用包含分组统计函数的查询条件的优点。当数据量特别大的时候，运行效率会有很大的差别。
六、select的执行过程
1.关键字顺序
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 顺序 73.SQL的执行原理(先了解)
SELECT 是先执行 FROM 这一步的。在这个阶段，如果是多张表联查，还会经历下面的几个步骤：
首先先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表 vt（virtual table）1-1；
通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；
Externe Zeilen hinzufügen. Wenn wir einen Left-Join, einen Right-Link oder einen Full-Join verwenden, sind externe Zeilen beteiligt, d. h. das Hinzufügen externer Zeilen zur virtuellen Tabelle vt1-2 führt zur virtuellen Tabelle vt1-3.
Wenn wir mehr als zwei Tabellen betreiben, werden die oben genannten Schritte wiederholt, bis alle Tabellen verarbeitet wurden. Durch diesen Prozess entstehen unsere Originaldaten.
Wenn wir die Originaldaten der Abfragedatentabelle erhalten, bei der es sich um die endgültige virtuelle Tabelle vt1 handelt, können wir auf dieser Basis mit der WHERE-Stufe fortfahren. In dieser Phase werden die Ergebnisse der VT1-Tabelle gefiltert und gefiltert, um eine virtuelle Tabelle VT2 zu generieren.
Der nächste Schritt ist die dritte und vierte Phase, die Gruppierungs- und Screening-Phase. In dieser Phase werden die Gruppierung und Gruppenfilterung tatsächlich auf der Grundlage der virtuellen Tabelle vt2 durchgeführt und die virtuellen Zwischentabellen vt3 und vt4 erhalten.
Nachdem wir den bedingten Filterteil abgeschlossen haben, können wir die aus der Tabelle extrahierten Felder filtern, d. h. in die Stufen SELECT und DISTINCT eintreten.
Zuerst werden die gewünschten Felder in der SELECT-Stufe extrahiert, und dann werden doppelte Zeilen in der DISTINCT-Stufe herausgefiltert, um die virtuellen Zwischentabellen vt5-1 bzw. vt5-2 zu erhalten.
Nachdem wir die gewünschten Felddaten extrahiert haben, können wir sie nach dem angegebenen Feld sortieren, das die ORDER BY-Stufe darstellt, und erhalten die virtuelle Tabelle vt6.
Abschließend werden basierend auf vt6 die Datensätze der angegebenen Zeile herausgenommen, was die LIMIT-Stufe darstellt, und das Endergebnis wird erhalten, das der virtuellen Tabelle vt7 entspricht.
Natürlich sind beim Schreiben der SELECT-Anweisung möglicherweise nicht alle Schlüsselwörter vorhanden und die entsprechenden Phasen werden weggelassen.
Da SQL eine strukturierte Abfragesprache ähnlich wie Englisch ist, müssen wir beim Schreiben der SELECT-Anweisung gleichzeitig auch auf die entsprechende Schlüsselwortreihenfolge achten, Die sogenannte Das zugrunde liegende Funktionsprinzip ist die Ausführungssequenz, über die wir gerade gesprochen haben.
Das obige ist der detaillierte Inhalt vonSo implementieren Sie eine Mehrtabellenabfrage in einer MySQL-Datenbankabfrage. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!
Stellungnahme：
Dieser Artikel ist reproduziert unter:yisu.com. Bei Verstößen wenden Sie sich bitte an admin@php.cn löschen
Vorheriger Artikel：Was sind die int-Typen in MySQL?Nächster Artikel：Was sind die int-Typen in MySQL?