知识不成体系？这篇Mysql数据库将成为你的解忧杂货店！（多表查询）

最新推荐文章于 2024-07-13 18:10:19 发布

@一夜看尽长安花

最新推荐文章于 2024-07-13 18:10:19 发布

阅读量673

点赞数 29

分类专栏： java全栈文章标签：数据库 mysql sql oracle

本文链接：https://blog.csdn.net/ta683280/article/details/140033490

版权

java全栈专栏收录该内容

14 篇文章 0 订阅

订阅专栏

欢迎来到@一夜看尽长安花博客，您的点赞和收藏是我持续发文的动力

对于文章中出现的任何错误请大家批评指出，一定及时修改。有任何想要讨论的问题可联系我：3329759426@qq.com 。发布文章的风格因专栏而异，均自成体系，不足之处请大家指正。

专栏：

java全栈
C&C++
PythonAI
PCB设计

文章概述：对mysql的多表查询的代码介绍和相应练习&常见问题的解答以及注意点

关键词：Mysql 多表查询 sql92 sql99

本文目录：

多表查询

1.笛卡尔乘积：

2.多表查询分类

sql92 标准中的的等值连接

1.注意点：

2.等值连接：

3.等值连接特点：

4.等值连接的使用

5.增加搜索条件

限制不明确的列名：

使用表别名

6、实例

sql92 标准中非等值连接

1.非等值连接：

2.练习：

sql92 标准中自连接

自连接

sql99标准中的查询

SQL99中的自然连接(NATURAL JOIN)

SQL99中的内连接(INNER JOIN)

外连接

1.孤儿数据(Orphan Data)

2.左外连接

3.右外连接

4.全外连接(FULL OUTER JOIN)

多表查询练习

多表查询

1.笛卡尔乘积：

当一个连接条件无效或被遗漏时，其结果是一个笛卡尔乘积(Cartesian product)，其中所有行的组合都被显示。第一个表中的所有行连接到第二个表中的所有行。一个笛卡尔乘积会产生大量的行，其结果没有什么用。你应该在WHERE子句中始终包含一个有效的连接条件，除非你有特殊的需求，需要从所有表中组合所有的行。

2.多表查询分类

sql92标准：内连接(等值连接、非等值连接、自连接)。
sql99标准：内连接、外连接(左外、右外、全外(MySQL不支持全外连接))、交叉连接

sql92 标准中的的等值连接

1.注意点：

一般指定两个列，一个逐渐一个外键，（外键约束，防止数据出错）

2.等值连接：

为了确定一个雇员的部门名，需要比较 EMPLOYEES 表中的 DEPARTMENT_ID 列与DEPARTMENTS 表

中的 DEPARTMENT_ID 列的值。在 EMPLOYEES 和DEPARTMENTS 表之间的关系是一个相等 (equijoin)

关系，即两个表中DEPARTMENT_ID 列的值必须相等。

3.等值连接特点：

多表等值连接的结果为多表的交集部分；
n表连接，至少需要n-1个连接条件；
多表不分主次，没有顺序要求；
一般为表起别名，提高阅读性和性能；
可以搭配排序、分组、筛选….等子句使用；

注意：等值连接也被称为简单连接 (simple joins) 或内连接

4.等值连接的使用

SELECT 子句指定要返回的列名：

− employee last name、employee number 和 department number，这些是EMPLOYEES 表中的列

− department number、department name 和 location ID，这些是 DEPARTMENTS 表中的列

FROM 子句指定数据库必须访问的两个表：

− EMPLOYEES 表

− DEPARTMENTS 表

WHERE 子句指定表怎样被连接：

EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID，因为 DEPARTMENT_ID 列是两个表的同名列，它必须用表名做前缀以避免混淆

5.增加搜索条件

and操作符

除连接之外，可能还要求用WHERE子句在连接中限制一个或多个表中的行。

限制不明确的列名：

1.需要在WHERE子句中用表的名字限制列的名字以避免含糊不清。没有表前缀，DEPARTMENT_ID列可能来自DEPARTMENTS表，也可能来自EMPLOYEES表，这种情况下需要添加表前缀来执行查询。

2.如果列名在两个表之间不相同，就不需要限定列。但是，使用表前缀可以改善性能，因为MySQL服务器可以根据表前缀找到对应的列。

3. 必须限定不明确的列名也适用于在其它子句中可能引起混淆的那些列，例如SELECT子句或ORDER BY子句。

使用表别名

表别名定义原则

表别名不易过长，短一些更好。
表别名应该是有意义的。
表别名只对当前的SELECT语句有效。

6、实例

#查询雇员King所在的部门名称。

select d.department_name from employees e,departments d where e.dept_id=
d.department_id and e.last_name='King';

#显示每个雇员的last name、departmentname和city。

SELECT e.last_name, d.department_name, l.city
FROM employees e, departments d, locations l
WHERE e.department_id=d.department_id
AND d.location_id=l.location_id;

sql92 标准中非等值连接

1.非等值连接：

一个非等值连接是一种不同于等值操作的连接条件。 EMPLOYEES 表和JOB_GRADES A 表之间的关系有一个非等值连接例子。在两个表之间的关系是EMPLOYEES 表中的 SALARY 列必须是 JOB_GRADES 表的LOWEST_SALARY 和HIGHEST_SALARY 列之间的值。使用不同于等于 (=) 的操作符获得关系。

2.练习：

1、

#创建job_grades表，包含lowest_sal，highest_sal，grade_level。

create tablejob_grades(lowest_sal int,highest_sal int ,grade_level
varchar(30));

#插入数据

1000 2999 A

2000 4999 B

5000 7999 C

8000 12000 D

insert intojob_gradesvalues(1000,2999,'A');
insert intojob_gradesvalues(2000,4999,'B');
insert intojob_gradesvalues(5000,7999,'C');
insert intojob_gradesvalues(8000,12000,'D');

2、

#查询所有雇员的薪水级别

select e.last_name,j.grade_level from employees e ,job_grades j where e.salary
between j.lowest_sal and j.highest_sal;

sql92 标准中自连接

自连接

连接一个表到它自己。有时需要连接一个表到它自己。为了找到每个雇员的经理的名字，则需要连接EMPLOYEES 表到它自己，或执行一个自连接。

图片中的例子连接EMPLOYEES表到它自己。为了在FROM子句中模拟两个表，对于相同的表

EMPLOYEES，用两个别名，分别为worker和manager。在该例中，WHERE子句包含的连接意味着“一个工人的经理号匹配该经理的雇员号”。

#查询每个雇员的经理的名字以及雇员的名字，雇员名字列别名为W，经理列别名为M。

SELECT
worker.LAST_NAMEW,manager.LAST_NAMEM
from employees worker,employees manager
where worker.MANAGER_ID=manager.EMPLOYEE_ID;

#查询Fox的经理是谁？显示他的名字。

SELECT
worker.LAST_NAME,manager.LAST_NAME
from employees worker,employees manager
where worker.MANAGER_ID=manager.EMPLOYEE_ID
AND
worker.LAST_NAME='Fox';

sql99标准中的查询

MySQL5.7支持部分的SQL99标准。

SQL99中的交叉连接(CROSS JOIN) 即迪科尔乘积（不会去用的）

#使用交叉连接查询employees表与departments表。

select*from employeescross join departments;

SQL99中的自然连接(NATURAL JOIN)

自动识别两个表中列名和类型相同的列作为等值连接的一部分，然后再有其他相同对，则用and连接作为等值的条件

LOCATIONS表被用LOCATION_ID列连接到DEPARTMENT表，这是在两个表中唯一名字相同的列。如果存在其它的同名同类型的列，自然连接会使用等值连接的方式连接他们，连接条件的关系为and。

自然连接也可以被写为等值连接：

#LOCATIONS表被用LOCATION_ID列连接到 DEPARTMENT表

SELECT department_id , department_name , location_id, city 
FROM departments 
NATURAL JOIN locations;
or
SELECT d.department_id, d.department_name , d.location_id , l.city
FROM departments d , locations l 
WHERE d.location_id = l.location_id;

#使用自然连接查询所有有部门的雇员的名字以及部门名称。

select e.last_name,d.department_name from employees enatural join departments
d;

SQL99中的内连接(INNER JOIN)

（sql92 中内连接分为等值连接、非等值连以及自连接）sql99 也可以实现这三种

SELECT查询列表;

FROM表1别名;

INNER JOIN连接表(INNER关键字可省略);

ON连接条件;

#查询雇员名字为Fox的雇员ID，薪水与部门名称。（除了内连接以外的条件用where连接）

select e.employee_id,e.salary,d.department_name from employees einner JOIN
departments done.department_id=d.department_id where e.last_name='Fox';

外连接

1.孤儿数据(Orphan Data)

孤儿数据是指被连接的列的值为空的数据。（不匹配的数据）

2.左外连接

左边的表 (EMPLOYEES) 中即使没有与 DEPARTMENTS 表中匹配的行，该查询

也会取回 EMPLOYEES 表中所有的行

#查询所有雇员的名字以及他们的部门名称，包含那些没有部门的雇员 , 并且以G开头

select e.last_name,d.department_name 
from employees eLEFT OUTER JOIN departments d 
one.department_id=d.department_id
where e.last_name like 'G%' ;

3.右外连接

右边的表 (DEPARTMENTS ) 中即使没有与 EMPLOYEES 表中匹配的行，该查询

也会取回 DEPARTMENTS 表中所有的行。

#查询所有雇员的名字以及他们的部门名称，包含那些没有雇员的部门

select e.last_name,d.department_name 
from employees eRIGHTOUTER JOIN departments d
one.DEPARTMENT_ID=d.department_id;

4.全外连接(FULL OUTER JOIN)

注意： MySQL 中不支持 FULL OUTER JOIN 连接

可以使用 union 实现全完连接。

UNION: 可以将两个查询结果集合并，返回的行都是唯一（剔除重复）的，如同对整个结果集合使用DISTINCT。

UNION ALL: 只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。

(SELECT投影列FROM表名LEFT OUTER JOIN表名ON连接条件)

UNION

(SELECT投影列FROM表名RIGHT OUTER JOIN表名ON连接条件)

( select e.last_name,d.department_name 
from employees e LEFT OUTER JOIN departments d 
one.department_id=d.department_id
)
UNION
(select e1.last_name,d1.department_name
from employees e1 RIGHT OUTER JOIN departments d1 
on e1.department_id = d1.department_id 
)

多表查询练习

1、

#.写一个查询显示所有雇员的last name、department id、anddepartment name （多表的等值连接，由 employ表中 last name、department id 连接到department表中对应department name 信息）

SELECT
e.LAST_NAME,e.DEPARTMENT_ID,d.DEPARTMENT_NAME
FROM employees e,departments d
WHERE e.DEPARTMENT_ID=d.DEPARTMENT_ID;

2、

#创建一个在部门80中的所有工作岗位的唯一列表，在输出中包括部门的地点。

SELECT DISTINCT
e.JOB_ID,d.LOCATION_ID
FROM employees e,departments d
WHERE e.DEPARTMENT_ID=d.DEPARTMENT_ID
AND e.DEPARTMENT_ID=80;

3、

#写一个查询显示所有有佣金的雇员的last name、department name、location ID和城市（三个表）

SELECT
e.LAST_NAME,d.DEPARTMENT_NAME,d.LOCATION_ID,l.CITY
FROM employees e,departments d,locations l
WHERE e.DEPARTMENT_ID=d.DEPARTMENT_ID
AND d.LOCATION_ID=l.LOCATION_ID
AND e.COMMISSION_PCT is notnull;

4、

#显示所有在其last names中有一个小写a的雇员的last name和department name。

select
e.LAST_NAME,d.DEPARTMENT_NAME
FROM employees e,departments d
where e.DEPARTMENT_ID=d.DEPARTMENT_ID
AND e.LAST_NAME like'%a%';

5、

#.用sql99的内连接写一个查询显示那些工作在Toronto的所有雇员的last name、job 、department number和department name。

SELECT
e.LAST_NAME,e.JOB_ID,e.DEPARTMENT_ID,d.DEPARTMENT_NAME
FROM employees e INNER JOIN departments d
ON e.DEPARTMENT_ID=d.DEPARTMENT_IDINNER JOIN locations l
ON d.LOCATION_ID=l.LOCATION_ID
WHERE l.CITY='Toronto';

6、

#显示雇员的last name和employee number连同他们的经理的last name和manager number。列标签分别为empname empid mgrname mgrid （同一个表自连接）（也可以用sql92 的自连接）

SELECT
w.LAST_NAME empname,w.DEPARTMENT_ID empid,m.LAST_NAME mgrname,m.EMPLOYEE_ID mgrid
FROM employees w INNER JOIN employees m
ON .MANAGER_ID=m.EMPLOYEE_ID;