1. 内连接(INNER JOIN)
语法:
SELECT 列名
FROM 表1
INNER JOIN 表2 ON 表1.列名 = 表2.列名;
示例:
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;
2. 左外连接(LEFT JOIN)
语法:
SELECT 列名
FROM 表1
LEFT JOIN 表2 ON 表1.列名 = 表2.列名;
示例:
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;
3. 右外连接(RIGHT JOIN)
语法:
SELECT 列名
FROM 表1
RIGHT JOIN 表2 ON 表1.列名 = 表2.列名;
示例:
SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.id;
4. 全外连接(FULL JOIN)
语法:
SELECT 列名
FROM 表1
LEFT JOIN 表2 ON 表1.列名 = 表2.列名
UNION
SELECT 列名
FROM 表1
RIGHT JOIN 表2 ON 表1.列名 = 表2.列名;
示例:
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id
UNION
SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.id;
5. 交叉连接(CROSS JOIN)
语法:
SELECT 列名
FROM 表1
CROSS JOIN 表2;
示例:
SELECT employees.name, departments.department_name
FROM employees
CROSS JOIN departments;
6. 自连接(SELF JOIN)
自联接(Self Join)是指一个表与自身进行联接。这种操作通常用于表中记录之间的比较或关联。自联接可以帮助解决例如员工与其经理的关系、产品与产品之间的关系等问题。
自连接查询,可以是内连接查询,也可以是外连接查询。
语法:
SELECT 列名1, 列名2, ...
FROM 表名 AS 别名1
JOIN 表名 AS 别名2
ON 别名1.列名 = 别名2.列名
WHERE 条件;
示例:员工与经理自联接
假设我们有一个
employees
表,如下:
employees
表结构:
idnamemanager_id1AliceNULL2Bob13Carol14Dave2
查询员工及其经理的姓名:
SELECT employees.name AS employee_name, manager.name AS manager_name
FROM employees
LEFT JOIN employees AS manager
ON employees.manager_id = manager.id;
7. 左外连接排除内连接
定义:返回左表中所有记录和右表中匹配的记录,但排除那些在右表中也有匹配的记录。
语法:
SELECT 列名
FROM 表1
LEFT JOIN 表2 ON 表1.列名 = 表2.列名
WHERE 表2.列名 IS NULL;
示例:
SELECT employees.name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id
WHERE departments.id IS NULL;
8. 右外连接排除内连接
定义:返回右表中所有记录和左表中匹配的记录,但排除那些在左表中也有匹配的记录。
语法:
SELECT 列名
FROM 表1
RIGHT JOIN 表2 ON 表1.列名 = 表2.列名
WHERE 表1.列名 IS NULL;
示例:
SELECT departments.department_name
FROM departments
RIGHT JOIN employees ON departments.id = employees.department_id
WHERE employees.id IS NULL;
9. 全外连接排除内连接
定义:返回两个表的所有记录,但排除那些在两个表中都匹配的记录。
语法:
SELECT 列名
FROM 表1
LEFT JOIN 表2 ON 表1.列名 = 表2.列名
WHERE 表2.列名 IS NULL
UNION
SELECT 列名
FROM 表2
RIGHT JOIN 表1 ON 表2.列名 = 表1.列名
WHERE 表1.列名 IS NULL;
示例:
SELECT employees.name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id
WHERE departments.id IS NULL
UNION
SELECT departments.department_name
FROM departments
RIGHT JOIN employees ON departments.id = employees.department_id
WHERE employees.id IS NULL;
总结
- 内连接:仅返回两个表中匹配的记录。
- 左外连接:返回左表的所有记录和右表中匹配的记录,右表中没有匹配的记录显示为
NULL
。 - 右外连接:返回右表的所有记录和左表中匹配的记录,左表中没有匹配的记录显示为
NULL
。 - 全外连接:返回两个表的所有记录,通过
UNION
模拟。 - 交叉连接:返回两个表的笛卡尔积。
- 自连接:表与自身的联接,常用于记录间的比较。
- 左外连接排除内连接:返回左表中的记录,这些记录在右表中没有匹配项。
- 右外连接排除内连接:返回右表中的记录,这些记录在左表中没有匹配项。
- 全外连接排除内连接:返回两个表的所有记录,排除那些在两个表中都有匹配的记录。
联合查询
联合查询(也称为集合操作)用于将多个
SELECT
查询的结果集合并在一起。
**1.
UNION
**
- 功能:将两个或多个
SELECT
查询的结果集合并为一个结果集,并去除重复行。 - 语法:
SELECT column1, column2 FROM table1UNIONSELECT column1, column2 FROM table2;
注意:所有SELECT
查询必须具有相同数量的列,并且对应列的类型应兼容。
**2.
UNION ALL
**
- 功能:将两个或多个
SELECT
查询的结果集合并为一个结果集,包括所有重复行。 - 语法:
SELECT column1, column2 FROM table1UNION ALLSELECT column1, column2 FROM table2;
注意:比UNION
更高效,因为它不去重。
**
3. INTERSECT
**
- 功能:返回两个
SELECT
查询结果中的交集,即两个查询中都存在的行。 - 语法:
SELECT column1, column2 FROM table1INTERSECTSELECT column1, column2 FROM table2;
- 注意:MySQL 8.0 及之前的版本不直接支持
INTERSECT
,可以使用INNER JOIN
来实现类似功能。
**4.
EXCEPT
(或
MINUS
)**
- 功能:返回在第一个
SELECT
查询中存在但在第二个SELECT
查询中不存在的行。 - 语法:
SELECT column1, column2 FROM table1EXCEPTSELECT column1, column2 FROM table2;
注意:MySQL 8.0 及之前的版本不直接支持EXCEPT
。可以使用LEFT JOIN
和IS NULL
实现类似功能。
三表联查
定义:将三个表通过指定的连接条件联接在一起,通常用于从多个表中获取相关数据。
语法格式:
SELECT 列名1, 列名2, ...
FROM 表1
JOIN 表2 ON 表1.列名 = 表2.列名
JOIN 表3 ON 表2.列名 = 表3.列名
WHERE 条件
GROUP BY 列名
HAVING 条件
ORDER BY 列名 [ASC|DESC]
LIMIT n OFFSET m;
假设我们有以下三个表:
employees
(员工表):包含员工的基本信息。departments
(部门表):包含部门的信息。salaries
(薪资表):包含员工的薪资信息。
表结构示例:
employees
表:
idnamedepartment_id1Alice12Bob23Carol1
departments
表:
iddepartment_name1HR2IT
salaries
表:
employee_idsalary170000280000375000
查询语句:
SELECT employees.name, departments.department_name, salaries.salary
FROM employees
INNER JOIN departments ON employees.department_id = departments.id
INNER JOIN salaries ON employees.id = salaries.employee_id;
/*
解释:
INNER JOIN departments ON employees.department_id = departments.id:
将 employees 表与 departments 表通过 department_id 和 id 列进行连接,提取部门名称。
INNER JOIN salaries ON employees.id = salaries.employee_id:
将 employees 表与 salaries 表通过 id 和 employee_id 列进行连接,提取薪资信息。
SELECT employees.name, departments.department_name, salaries.salary:
从连接后的结果中选择员工姓名、部门名称和薪资信息。
*/
四表联查
定义:将四个表通过指定的连接条件联接在一起,用于从多个表中获取更复杂的数据。
语法格式:
SELECT 列名1, 列名2, ...
FROM 表1
JOIN 表2 ON 表1.列名 = 表2.列名
JOIN 表3 ON 表2.列名 = 表3.列名
JOIN 表4 ON 表3.列名 = 表4.列名
WHERE 条件
GROUP BY 列名
HAVING 条件
ORDER BY 列名 [ASC|DESC]
LIMIT n OFFSET m;
四表联查示例
假设我们有以下四个表:
employees
(员工表):包含员工的基本信息。departments
(部门表):包含部门的信息。salaries
(薪资表):包含员工的薪资信息。projects
(项目表):包含项目的信息。
表结构示例:
employees
表:
idnamedepartment_id1Alice12Bob23Carol1
departments
表:
iddepartment_name1HR2IT
salaries
表:
employee_idsalary170000280000375000
projects
表:
project_idproject_namedepartment_id1Project X12Project Y2
查询语句:
SELECT employees.name, departments.department_name, salaries.salary, projects.project_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id
INNER JOIN salaries ON employees.id = salaries.employee_id
INNER JOIN projects ON departments.id = projects.department_id;
/*
解释:
INNER JOIN departments ON employees.department_id = departments.id:
将 employees 表与 departments 表通过 department_id 和 id 列进行连接,提取部门名称。
INNER JOIN salaries ON employees.id = salaries.employee_id:
将 employees 表与 salaries 表通过 id 和 employee_id 列进行连接,提取薪资信息。
INNER JOIN projects ON departments.id = projects.department_id:
将 departments 表与 projects 表通过 department_id 和 department_id 列进行连接,提取项目名称。
SELECT employees.name, departments.department_name, salaries.salary, projects.project_name:
从连接后的结果中选择员工姓名、部门名称、薪资信息和项目名称。
*/
版权归原作者 ZZDICT 所有, 如有侵权,请联系我们删除。