MySQL连接查询

昵称70680357 2020-06-29

展开全文

SELECT 查询列表
FROM table_a AS 别名 [连接类型]
JOIN table_b 别名
ON 连接条件
[WHERE 筛选条件]
[GROUP BY 分组]
[HAVING 分组筛选条件]
[ORDER BY 排序列表]

1.1、连接类型分类

内连接:INNER JOIN
外连接:
　　左外连接:LEFT [OUTER] JOIN　
　　右外连接:RIGHT [OUTER] JOIN
全外连接:FULL [OUTER] JOIN
交叉连接:CROSS

2、数据库库表数据如下

3、各种连接查询解释及案例

　　3.1、INNER JOIN:内连接(查询的是两张表符合条件的公共部分)

　　　 3.1.1、基本语法

　　　　　　SELECT 查询列表

　　　　　　FROM 表A AS 别名 a

　　　　　　INNER JOIN 表 B AS 别名 b

　　　　　　ON 连接条件;

　　　 3.1.2、根据连接条件分类

1. 等值连接
2. 非等值连接
3. 自连接

　　　 3.1.3、具体案例

　　1、显示所有员工的姓名,部门编号和部门名称(等值连接)

SELECT e.last_name,d.department_id,d.department_name
FROM employees e INNER JOIN departments d 
ON e.department_id = d.department_id;

　　2、查询90号部门员工的job_id和90号部门的location_id(等值连接)

SELECT e.department_id,e.job_id,d.location_id
FROM employees e INNER JOIN departments d
ON e.department_id = d.department_id
AND e.department_id = 90;

语言	方法
3714	KGSPs2ThzP
w	Jv d a vK q De
u	2010.01.05 01-20-35

　　3、查询工作city在Toronto的员工的 last_name , job_id , department_id , department_name(等值连接)

SELECT e.last_name,j.job_id,e.department_id,d.department_name
FROM employees e INNER JOIN jobs j INNER JOIN departments d INNER JOIN locations l
ON e.job_id = j.job_id 
AND e.department_id = d.department_id 
AND d.location_id = l.location_id
WHERE l.city = 'Toronto';

　　4、查询每个工种、每个部门的部门名、工种名和最低工资(等值连接)

SELECT j.job_title,d.department_name,MIN(e.salary)
FROM employees e INNER JOIN departments d INNER JOIN jobs j
ON e.job_id = j.job_id
AND e.department_id = d.department_id
GROUP BY e.job_id,d.department_id;

　　5、查询员工的工资和工资级别并按照工资升序排序(非等值连接)

SELECT salary,grade_level
FROM employees e INNER JOIN job_grades j
ON salary BETWEEN lowest_sal AND highest_sal
ORDER BY salary ASC;

　　6、选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式(自连接)

　　employee_name    employee_id    manager_name    manager_id
   　　kochhar           101            king           100

SELECT 
　　e.last_name AS employee_name,
　　e.employee_id,
　　m.last_name AS manager_name,
　　m.manager_id
FROM employees e INNER JOIN employees m
ON e.employee_id = m.employee_id;

　　3.2、LEFT JOIN:左外连接(查询的是A表中有而B表中没有的部分 + 两张表的公共部分)

　　　　3.2.1、基本语法

　　　　　　SELECT 查询列表

　　　　　　FROM 表A AS 别名 a

　　　　　　LEFT JOIN 表 B AS 别名 b

　　　　　　ON 连接条件;

　　　　3.2.2、表结构与数据

　　　　3.2.3、具体案例

　　1、在执行左连接查询之前,先看看使用内连接查询出来的数据,如下图可以看到查出的是7条记录

SELECT g.id,g.name,g.age,g.boyFriend_id,b.id,b.name,b.age
FROM girls AS g
INNER JOIN boys AS b
ON g.boyFriend_id = b.id;

　　2、接着以girls表作为主表进行左连接查询,如下图可以看到查询的记录是9条,可以看出1-7条记录就是INNER JOIN查询出来的记录,而第8条和第9条记录是主表(girls)中存在的记录而从表(boys)中不存在.

得出结论:左连接查询的结果 = 两表的交集部分(INNER JOIN的查询结果) + 主表中存在的记录而从表中不存在的记录

SELECT g.id,g.name,g.age,g.boyFriend_id,b.id,b.name,b.age
FROM girls AS g
LEFT JOIN boys AS b
ON g.boyFriend_id = b.id;

　　3、最后以boys表作为主表进行左连接查询,如下图可以看到查询的记录是10条,可以看出1-7条记录就是INNER JOIN查询出来的记录,而第8条第9条和第10条记录是主表(boys)中存在的记录但是从表(girls)中不存在该记录.

SELECT g.id,g.name,g.age,g.boyFriend_id,b.id,b.name,b.age
FROM girls AS g
INNER JOIN boys AS b
ON g.boyFriend_id = b.id;

　　得出结论:左连接查询的结果 = 两表的交集部分(INNER JOIN的查询结果) + 主表中存在的记录而从表中不存在的记录

　　3.3、RIGHT JOIN:右外连接(查询的是B表中有而A表中没有的部分 + 两张表公共的部分)

　　右外连接和左外连接使用基本相似,可以通过变换主表和从表的位置来达到相同的效果,例如下面的SQL的效果是相同的

# 左外连接
SELECT g.id,g.name,g.age,g.boyFriend_id,b.id,b.name,b.age
FROM girls AS g
LEFT JOIN boys AS b
ON g.boyFriend_id = b.id;

# 右外连接
SELECT g.id,g.name,g.age,g.boyFriend_id,b.id,b.name,b.age
FROM boys AS b
RIGHT JOIN girls g
ON g.boyFriend_id = b.id;

　　3.4、全连接(查询的是A表中存在而B表中不存在的记录 + B表中存在而A表中不存在的记录 + A表和B表的交集)

　　　　注:Oracle中有 full join,mysql中没有full join,但是我们可以通过union 或 union all来实现全连接

　　　　1、使用UNION进行全连接(union),可以看出数据是去除了重复值

SELECT g.id,g.name,g.age,g.boyFriend_id,b.id,b.name,b.age
FROM girls AS g
LEFT JOIN boys AS b
ON g.boyFriend_id = b.id
UNION 
SELECT g.id,g.name,g.age,g.boyFriend_id,b.id,b.name,b.age
FROM boys AS b
LEFT JOIN girls AS g
ON g.boyFriend_id = b.id;

　　　　2、使用UNION ALL进行全连接(union all),可以看出数据没有去除了重复值,简单的将A表和B表合并在一起了

SELECT g.id,g.name,g.age,g.boyFriend_id,b.id,b.name,b.age
FROM girls AS g
LEFT JOIN boys AS b
ON g.boyFriend_id = b.id
UNION ALL
SELECT g.id,g.name,g.age,g.boyFriend_id,b.id,b.name,b.age
FROM boys AS b
LEFT JOIN girls AS g
ON g.boyFriend_id = b.id;

　　3.5、其它常用的连接查询形式

　　　　1、查询出A表中存在而B表中不存在的记录

SELECT g.id,g.name,g.age,g.boyFriend_id,b.id,b.name,b.age
FROM girls AS g
LEFT JOIN boys AS b
ON g.boyFriend_id = b.id
WHERE b.id IS NULL;

　　　　2、查询出B表中存在而A表中不存在的记录

SELECT g.id,g.name,g.age,g.boyFriend_id,b.id,b.name,b.age
FROM boys AS b
LEFT JOIN girls AS g
ON g.boyFriend_id = b.id
WHERE g.boyFriend_id IS NULL;

　　　　3、查询出A表和B表去除了交集以后的并集部分

SELECT g.id,g.name,g.age,g.boyFriend_id,b.id,b.name,b.age
FROM girls AS g
LEFT JOIN boys AS b
ON g.boyFriend_id = b.id
WHERE b.id IS NULL
UNION
SELECT g.id,g.name,g.age,g.boyFriend_id,b.id,b.name,b.age
FROM boys AS b
LEFT JOIN girls AS g
ON g.boyFriend_id = b.id
WHERE g.boyFriend_id IS NULL;

　　外连接总结: