与MySQL相识的第三天，我们的关系终于又近了一步（多表连接查询）

最新推荐文章于 2024-10-06 20:42:07 发布

码小春

最新推荐文章于 2024-10-06 20:42:07 发布

阅读量190

点赞数 9

文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/zsdzb/article/details/121329821

版权

前言

这是我和MySQL认识的第三天，我们无话不谈，终于可以推心置腹的聊天，以至于我们能够更加的了解彼此，接下来就是我和MySQL更多的故事。

一、笛卡尔集&内连接

当我们学习了一些单表上的查询之后，我们会想到万一我们要在好几张表上来查询怎么办，这个问题确实很重要。我们在实际应用中不可能一直只在一张表上进行查询我们需要的东西，有的时候甚至能涉及到两张，三张或者更多表的联合查询，所以今天我携手MySQL给大家带来了我们的解决方案。

笛卡尔集

* 产生条件：
1. 省略连接条件
2. 连接条件无效
3. 所有表中所有行互相连接
* 解决方案：
添加有效筛选条件

虽然我们使用笛卡尔集能够直接链接我们需要的两张表，但是也会将一些错误的连接给展示出来，所以这个时候我们就需要进行对条件进行筛选。接下来我们就进行条件的筛选吧。

内连接

* SQL92语法：
       SELECT 查询列表
       FROM 表名1 别名1 ,表名2 别名2
       WHERE 连接条件
       AND 筛选条件
       GROUP BY 分组列表
       HAVING 分组后筛选条件
       ORDER BY 排序列表
   * SQL99语法：
       select 字段列表
       from 表名1
       [inner] join 表名2 on 条件
       where 筛选条件
       group by 分组条件
       having 分组后的筛选条件
       order by 排序字段
* 注意：
   1. 使用表名前缀在多个表中区分相同的列
   2. 在不同表中具有相同列名的列可以用表的别名加以区分
   3. 如果使用了表别名，则在select语句中需要使用表别名代替表名
   4. 表别名最多支持32个字符长度，但建议越少越好

在使用内连接的时候我们需要注意的就是要分清两个语法的不同之处，因为很多地方都一样所以容易忽略掉不一样的地方。

实践

光学不练也没有，只掌握了理论不进行实践知识也只是停留在理论阶段，并且通过实践我们才能更好的熟练应用，并且能加深我们对知识的理解。

1.查询90号部门员工的job_id和90号部门的location_id

SELECT e.job_id,d.location_id
FROM employees e,departments d
WHERE e.`department_id`=90 AND e.`department_id`=d.`department_id`;

2.选择所有有奖金的员工的last_name,department_name,location_id,city

SELECT e.last_name,d.department_name,d.location_id,l.city
FROM employees e,departments d,locations l
WHERE e.`commission_pct` IS NOT NULL
AND e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`;

3.选择city在Toronto工作的员工的last_name , job_id , department_id , department_name

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

二、外连接

当我们说了内连接，那肯定也有外连接呀，接下来我们就来学学这外连接和内连接有什么不一样的地方。

左外连接

* 语法：
       select 字段列表
       from 表1
       left [outer] join 表2 on 条件
       ...
   * 注意：
       左外连接查询的是左表所有数据以及其交集部分

右外连接

* 语法：
       select 字段列表
       from 表1
       right [outer] join 表2 on 条件
       ...
   * 注意：
       右外连接查询的是右表所有数据以及其交集部分

外连接又分为左外和右外，左外连接顾明思议就是把左边的表当作主表，而右外连接就是右边的表当做主表，而我们怎么去选择使用哪个呐？这就需要看我们的题目了，如果题目要展示的是左边的表的全部信息我们就使用左外连接反之亦然。

实践

1.查询城市名包含a字符的哪个城市没有部门，并按城市名降序

SELECT l.city,d.*
FROM departments d
RIGHT OUTER JOIN 
locations l ON d.`location_id`=l.`location_id`
WHERE d.`department_id`  IS NULL
ORDER BY city DESC;

2.查询部门名为SAL或IT的员工信息

SELECT e.last_name,d.department_name
FROM departments d
LEFT JOIN employees e
ON e.`department_id`=d.`department_id`
WHERE d.`department_name` IN('SAL','IT');

三、子查询

有的时候我们进行的查询会建立在另一个查询的结果之上，这个时候我们就需要用到子查询了，而子查询和我们之前学习java中的循环有点类似，接下来我们一起看看吧。

概念

查询中嵌套查询，称嵌套查询为子查询

特点

1、子查询都放在小括号内
2、子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧
3、子查询优先于主查询执行，主查询使用了子查询的执行结果
4、子查询根据查询结果的行数不同分为以下两类：
① 单行子查询
结果集只有一行
一般搭配单行操作符使用：> < = <> >= <=
非法使用子查询的情况：
a、子查询的结果为一组值
b、子查询的结果为空
② 多行子查询
结果集有多行
一般搭配多行操作符使用：any、all、in、not in
in：属于子查询结果中的任意一个就行
any和all往往可以用其他查询代替

实践

1.返回公司工资最少的员工的last_name,job_id和salary

SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
      SELECT MIN(salary)
      FROM employees
);

2.查询最低工资大于50号部门最低工资的部门id和其最低工资

1. SELECT MIN(salary),department_id
   FROM employees
   GROUP BY department_id
   HAVING MIN(salary)>(
   	SELECT  MIN(salary)
   	FROM employees
   	WHERE department_id = 50


   );

3.查询管理者是King的员工姓名和工资

SELECT last_name,salary,manager_id
FROM employees
WHERE manager_id=(
      SELECT employee_id
      FROM employees
      WHERE last_name='K_ing'
      AND manager_id IS NULL
);

4.查询在部门的location_id为1700的部门工作的员工的员工号

SELECT employee_id
FROM employees
WHERE department_id <>ALL(
       SELECT DISTINCT department_id
       FROM departments
       WHERE location_id=1700            
)；

四、分页查询&联合查询（union ）

由于分页查询和联合查询（union ）都是比较简单的，一般知道原理就能用了，所以在这里就不作过多的讲解。

分页查询

语法：
   select 字段|表达式,...
   from 表
   where 条件
   group by 分组字段
   having 条件
   order by 排序的字段
   limit 起始的条目索引，条目数;

示例：每页显示3条记录
       * SELECT * FROM student LIMIT 0,3; -- 第1页

       * SELECT * FROM student LIMIT 3,3; -- 第2页

       * SELECT * FROM student LIMIT 6,3; -- 第3页
特点：
   1.起始条目索引从0开始

   2.limit子句放在查询语句的最后

   3.公式：select * from 表 limit （page-1）*sizePerPage,sizePerPage
   假如:
       每页显示条目数sizePerPage
       要显示的页数 page

联合查询（union ）

语法：
   select 字段|常量|表达式|函数 from 表 where 条件 union 【all】
   select 字段|常量|表达式|函数 from 表 where 条件 union 【all】
   select 字段|常量|表达式|函数 from 表 where 条件 union 【all】
   .....
   select 字段|常量|表达式|函数 from 表 where 条件
特点：
   1、多条查询语句的查询的列数必须是一致的
   2、多条查询语句的查询的列的类型几乎相同
   3、union代表去重，union all代表不去重