第06章_多表查询_两张表没有关联,如何共同查询-优快云博客

本文链接：https://blog.youkuaiyun.com/2401_84526799/article/details/139547046

1.一个案例引发的多表连接

#为什么不把多张表合成一张表？答：
#多张表合成一张表会导致很多信息的冗余！
#将不必要的冗余信息从磁盘导入内存将导致很多的磁盘IO。使效率变低

1.2 笛卡尔积（或交叉连接）的理解

笛卡尔积的错误会在下面条件下产生：

省略多个表的连接条件（或关联条件）
连接条件（或关联条件）无效
所有表中的所有行互相连接

为了避免笛卡尔积，可以在 WHERE 加入有效的连接条件。

#案例：查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;

2. 多表查询分类讲解
分类1：等值连接 vs 非等值连接
等值连接:值相等的那一列作为连接条件
拓展1：多个连接条件与 AND 操作符
拓展2：区分重复的列名
多个表中有相同列时，必须在列名之前加上表名前缀。

在不同表中具有相同列名的列可以用表名加以区分。

#4.如果字段存在多个表中，那么就要在每个字段具体指明的其所在的表
SELECT employees.employee_id,departments.department_name,employees.department_id
FROM employees,departments
WHERE  employees.`department_id`=departments.`department_id`;

拓展3：表的别名

使用别名可以简化查询。
列名前使用表名前缀可以提高查询效率

#5.可以给表取别名，方便进行阅读
#在from里给表取别名，在select和where中就不能使用表名了
SELECT emp.employee_id,dep.department_name,emp.department_id
FROM employees emp,departments dep
WHERE  emp.`department_id`=dep.`department_id`;

拓展4：连接多个表

总结：连接 n个表,至少需要n-1个连接条件。比如，连接三个表，至少需要两个连接条件
非等值连接：值不相等的那一列，用其他条件作为连接条件
分类2：自连接 vs 非自连接

自连接：自己连接自己

#查询员工的id，姓名和其管理者的id，姓名
SELECT emp.employee_id,emp.last_name,mag.employee_id,mag.last_name
FROM employees emp,employees mag
WHERE emp.`manager_id`=mag.`employee_id`;

分类3：内连接VS外连接

#内连接：合并具有同一列的两个以上的表的行，结果包含一个表与另一个表相匹配匹配的行
```
SELECT e.employee_id,d.department_id
FROM  employees e,departments d
WHERE  e.`department_id`=d.`department_id`;
```
#外连接：合并具有同一列的两个以上的表的行，结果除了包含一个表与另一个表相匹配匹配的行，还有左表和右表不匹配的行

#外连接的分类：分为左外连接，右外连接
#左外连接：两个表中连接除了返回满足连接条件的行以外，还返回左表不满足的条件的行
#右外连接：两个表中连接除了返回满足连接条件的行以外，还返回右表不满足的条件的行

SQL92：使用(+)创建连接

在 SQL92 中采用（+）代表从表所在的位置。即左或右外连接中，(+) 表示哪个是从表。

Oracle 对 SQL92 支持较好，而 MySQL 则不支持 SQL92 的外连接。

#sql92语法实现外连接，使用 +  ，。。。。。mysql不支持这个
SELECT e.employee_id,d.department_id
FROM  employees e,departments d
WHERE  e.`department_id`=d.`department_id`(+);#左连接不支持

3. SQL99语法实现多表查询

.1 基本语法
使用JOIN…ON子句创建连接的语法结构：
3.2 内连接(INNER JOIN)的实现

语法：SELECT 字段列表

FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;

#sql99实现内连接
SELECT e.employee_id,d.department_id
FROM  employees e INNER JOIN departments d
ON  e.`department_id`=d.`department_id

3.3 外连接(OUTER JOIN)的实现

3.3.1 左外连接(LEFT OUTER JOIN)

语法：#实现查询结果是A
SELECT 字段列表
        FROM A表 LEFT JOIN B表
        ON 关联条件
        WHERE 等其他子句;

#左外连接：
SELECT employee_id,d.department_name,d.department_id
FROM  employees e LEFT JOIN departments d
ON  e.`department_id`=d.`department_id`;

3.3.2 右外连接(RIGHT OUTER JOIN)

语法：        FROM A表 RIGHT JOIN B表
                ON 关联条件
                WHERE 等其他子句;

#右外连接：
SELECT employee_id,d.department_name,d.department_id
FROM  employees e RIGHT JOIN departments d
ON  e.`department_id`=d.`department_id`;

3.3.3 满外连接(FULL OUTER JOIN)
满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。

4. UNION的使用

UNION操作符

UNION 操作符返回两个查询的结果集的并集，去除重复记录。

UNION ALL操作符

UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

SELECT id,cname FROM t_chinamale WHERE csex='男'
UNION ALL
SELECT id,tname FROM t_usmale WHERE tGender='male';

5. 7种SQL JOINS的实现

5.7.1 代码实现

7种JOINS的实现：
#内连接：
SELECT 
  employee_id,
  d.department_name 
FROM
  employees e 
  JOIN departments d 
    ON e.`department_id` = d.`department_id` ;

#左外连接：
SELECT employee_id,d.department_name,d.department_id
FROM  employees e LEFT JOIN departments d
ON  e.`department_id`=d.`department_id`;

#右外连接：
SELECT employee_id,d.department_name,d.department_id
FROM  employees e RIGHT JOIN departments d
ON  e.`department_id`=d.`department_id`;

#左外连接中左表不满足的条件的行:
SELECT employee_id,d.department_name
FROM  employees e LEFT JOIN departments d
ON  e.`department_id`=d.`department_id`
WHERE  d.`department_id` IS  NULL;#左表不满足的条件的行，对应的式另一行为空

#右外连接中右表不满足的条件的行
SELECT employee_id,d.department_name,d.department_id
FROM  employees e RIGHT JOIN departments d
ON  e.`department_id`=d.`department_id`
WHERE  e.`department_id` IS NULL;#右表不满足的条件的行，对应的式另一行为空

#满外连接：
#方式1：左外+右表
SELECT employee_id,d.department_name,d.department_id
FROM  employees e LEFT JOIN departments d
ON  e.`department_id`=d.`department_id`
UNION ALL
SELECT employee_id,d.department_name,d.department_id
FROM  employees e RIGHT JOIN departments d
ON  e.`department_id`=d.`department_id`
WHERE  e.`department_id` IS NULL;

#左表+右表
SELECT employee_id,d.department_name,d.`department_id`
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,d.department_name,d.department_id
FROM  employees e RIGHT JOIN departments d
ON  e.`department_id`=d.`department_id`
WHERE  e.`department_id` IS NULL;

6. SQL99语法新特性

6.1 自然连接

SQL99 在 SQL92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。我们可以把自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中所有相同的字段，然后进行等值连接

SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

6.2 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;

表连接的约束条件可以有三种方式：WHERE, ON, USING

WHERE：适用于所有关联查询
ON ：只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起写，但分开写可读性更好。
USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字段值相等

课后练习

#第06章 多表的查询练习题1
#练习题1：
【题目】
SELECT *FROM employees;
SELECT *FROM jobs;
SELECT *FROM locations;
SELECT *FROM departments;
DESC  departments;
# 1.显示所有员工的姓名，部门号和部门名称。
   SELECT last_name,d.department_id,department_name
   FROM  employees e LEFT JOIN departments d
   ON    e.`department_id`=d.`department_id`;
# 2.查询90号部门员工的job_id和90号部门的location_id
     SELECT  e.job_id,d.location_id,e.`department_id`
     FROM   employees e JOIN departments d 
     ON   d.`department_id`=e.`department_id`&& d.`department_id`=90;
  
   
     
 # 3.选择所有有奖金的员工的 last_name , department_name , location_id , city
       SELECT e.last_name, d.department_name,d.location_id,l.city,e.`commission_pct`
       FROM   employees e LEFT JOIN departments d
       ON    e.`department_id`=d.`department_id`
       LEFT JOIN  locations l 
       ON    d.`location_id`=l.location_id
       WHERE  e.commission_pct IS NOT NULL;
 # 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name 
    SELECT e.last_name,e.job_id,d.department_id,d.department_name,l.`city`
    FROM   employees e JOIN departments d
    ON    e.`department_id`=d.`department_id`
    JOIN  locations l
    ON    l.`location_id`=d.location_id
     WHERE  l.`city`='Toronto';
        
# 5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’
        SELECT   department_name,l.`street_address`,last_name,job_id,salary
        FROM    employees e  RIGHT JOIN departments d
        ON      e.`department_id`=d.`department_id`
        RIGHT JOIN    locations l
        ON d.`location_id`=l.`location_id`
        WHERE  d.`department_name`='Executive';
# 6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
employees   Emp#    manager Mgr#

 kochhar   101       king  100  

SELECT emp.last_name,emp.employee_id,mag.last_name,mag.employee_id
FROM employees emp LEFT JOIN employees mag
ON emp.manager_id=mag.employee_id;

  
# 7.查询哪些部门没有员工
SELECT d.department_name,d.department_id,employee_id
FROM employees e  RIGHT JOIN departments d
ON  e.`department_id`=d.`department_id`
WHERE  e.`department_id` IS NULL;
 
  
# 8. 查询哪个城市没有部门 
SELECT  city,department_id
FROM   departments d RIGHT JOIN locations l
ON    d.`location_id`=l.`location_id`
WHERE d.`location_id`<=>NULL;
# 9. 查询部门名为 Sales 或 IT 的员工信息
SELECT last_name,employee_id,department_name
FROM   employees e RIGHT JOIN departments d
ON  e.`department_id`=d.`department_id`
#WHERE  d.`department_name`='Sales' ||d.`department_name`='IT';
WHERE d.`department_name` IN('Sales','IT');





#第06章 多表的查询练习题2
储备：建表操作：
CREATE TABLE `t_dept` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `deptName` VARCHAR(30) DEFAULT NULL,
 `address` VARCHAR(40) DEFAULT NULL,
 PRIMARY KEY (`id`)
 ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 
 
 CREATE TABLE `t_emp` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(20) DEFAULT NULL,
 `age` INT(3) DEFAULT NULL,
 `deptId` INT(11) DEFAULT NULL,
 empno INT  NOT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_dept_id` (`deptId`)
 #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
 ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 
INSERT INTO t_dept(deptName,address) VALUES('华山','华山');
 INSERT INTO t_dept(deptName,address) VALUES('丐帮','洛阳');
 INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山');
 INSERT INTO t_dept(deptName,address) VALUES('武当','武当山');
 INSERT INTO t_dept(deptName,address) VALUES('明教','光明顶');
 INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺');
 INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('风清扬',90,1,100001);
 INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('岳不群',50,1,100002);
 INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('令狐冲',24,1,100003);
 INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('洪七公',70,2,100004);
 INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('乔峰',35,2,100005);
 INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('灭绝师太',70,3,100006);
 INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('周芷若',20,3,100007);
 INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张三丰',100,4,100008);
 INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张无忌',25,5,100009);
 INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('韦小宝',18,NULL,100010);
 
 SELECT * FROM t_emp;
  SELECT * FROM t_dept;
【题目】
#1.所有有门派的人员信息 
（ A、B两表共有）
SELECT  deptName,emp.name,age,deptId,empno
FROM    t_emp  emp JOIN t_dept dept
ON   emp.deptId=dept.id;

#2.列出所有用户，并显示其机构信息 
（A的全集）
SELECT emp.name,deptName,address
FROM  t_emp emp LEFT JOIN t_dept dep
ON    emp.deptId =dep.id;

#3.列出所有门派 
（B的全集）
SELECT  deptName,address,emp.name
FROM   t_emp  emp RIGHT JOIN t_dept dep
ON   emp.deptId=dep.id;

#4.所有不入门派的人员 
（A的独有）
SELECT emp.name,deptName,address 
FROM t_emp emp LEFT JOIN t_dept dep
ON  emp.deptId=dep.id
WHERE   dep.id IS NULL;
#5.所有没人入的门派 
（B的独有）
SELECT  deptName,address,emp.name
FROM  t_emp emp RIGHT JOIN t_dept dep
ON    emp.deptId=dep.id
WHERE  emp.deptId IS NULL ;
#6.列出所有人员和机构的对照关系
(AB全有)
 #MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法 
#left join + union(可去除重复数据)+ right join
 #7.列出所有没入派的人员和没人入的门派
（A的独有+B的独有）
SELECT emp.name,deptName,address 
FROM t_emp emp LEFT JOIN t_dept dep
ON  emp.deptId=dep.id
WHERE   dep.id IS NULL
UNION 
SELECT  deptName,address,emp.name
FROM  t_emp emp RIGHT JOIN t_dept dep
ON    emp.deptId=dep.id
WHERE  emp.deptId IS NULL ;