网站导航
> 文章列表 > mysql多表查询

mysql多表查询

网友: 文章列表 2024-03-21 22:36:05

mysql多表查询

mysql多表查询

  • 一、表的关联关系
    • 1.举例一对一关联
    • 2.笛卡尔积sql语句存在的问题 ：
  • 二、多表查询的分类
    • 1.等值连接的例子
    • 2.自连接的例子
    • 3.内连接和外连接的例子
  • 三、sql99语法
    • 1.sql99语法实现内连接
    • 2.99语法实现左外连接
    • 3.99语法实现右外连接
    • 4.99语法实现满外连接
  • 四、mysql99语法的七种join的操作
    • 1.union操作符
    • 2.具体实现方法
      • 2.1 内连接
      • 2.2 左外连接
      • 2.3 右外连接
      • 2.4 左中图
      • 2.5 右中图
      • 2.6 满外连接
  • 五、mysql99语法的新特性
    • 1.自然连接
    • 2.using
  • 总结

一、表的关联关系

mysql多表查询

1.举例一对一关联

mysql多表查询查询员工"abel"在那个城市工作
要用的三张表
mysql多表查询mysql多表查询我们在解决上面这个多表查询的问题之前需要了解一些基本原理

2.笛卡尔积sql语句存在的问题 ：

笛卡尔积类似于矩阵乘法运算，所以如果不做处理会产生大量冗余 运算

select employee_id,department_name from employees , departments;

mysql多表查询

为了解决笛卡尔积产生大量冗余的计算，我们需要设计出一个更好的多表连接运算的方法，也就是需要更多的额运算符加入

因此我们有了下面的语句

select last_name , employee_id , department_name , city from  employees , departments , locations where employees.LAST_NAME="abel" and employees.DEPARTMENT_ID=departments.DEPARTMENT_ID and locations.LOCATION_ID = departments.LOCATION_ID;

mysql多表查询起别名增加可读性

select last_name , employee_id , department_name , city 
from  employees as e , departments  as d , locations as l 
where e.last_name ="abel" and e.department_id = d.department_id and l.location_id = d.location_id;

mysql多表查询

二、多表查询的分类

mysql多表查询我们拿等值和非等值举例

1.等值连接的例子

非等值
job_grades
mysql多表查询我们根据salary来判断员工的薪水等级

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

mysql多表查询这个就是非等值连接

2.自连接的例子

查询员工id，员工姓名及其管理员的id和姓名

select emp.employee_id , emp.last_name , mgr.employee_id , mgr.last_name 
from employees emp , employees mgr 
where emp.manager_id = mgr.employee_id;

mysql多表查询

3.内连接和外连接的例子

我们前面写的都是内连接

mysql多表查询同理外连接的定义
mysql多表查询mysql多表查询我们主要讲外连接
分类：
左外连接、右外连接和满连接

外连接实例
左外连接
查询所有的员工的last_name,department_name信息

外连接语法分92和99年不同版本的，99可读性比92要好很多

我们用92年版本的语法写

select employee_id,department_name
from employees e , departments d 
where e.department_id = d.department_id(+);

mysql多表查询这里为什么会报错呢？
是因为mysql不支持92版本的写入
我们可以用Oracle执行
mysql多表查询

三、sql99语法

上面其实都算是sql92语法的内容，所以下面就都讲99语法的内容
使用…ON的方式实现多表的查询，这种方式也能解决外连接的问题，且mysql是支持的。

1.sql99语法实现内连接

select last_name , department_name
from employees e join departments d
on e.`department_id` = d.`department_id` ;

mysql多表查询这数据少了一个人，那个部门为null，还没分配部门
mysql多表查询

我们看内连接没有这个人

SELECT last_name , department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id WHERE last_name = "Grant";

mysql多表查询
可以看到并没有

2.99语法实现左外连接

因为左表的数据更多，所以我们需要左连接去显示出那个部门为null的人

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

mysql多表查询sql92语法是这样的
mysql多表查询

3.99语法实现右外连接

同理我们查一下所有的部门，员工id

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

mysql多表查询

4.99语法实现满外连接

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

注意虽然full outer join是对的，但是mysql不支持full

Oracle是支持的
mysql多表查询

四、mysql99语法的七种join的操作

MySQL虽然不支持满外连接，但是我们可以用其他方式弥补
mysql多表查询

1.union操作符

mysql多表查询开发中能用union all就不要用union
这里是因为union中间有重复的项目，但是union all没有

2.具体实现方法

根据上面的图片

2.1 内连接

mysql多表查询

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

mysql多表查询

2.2 左外连接

mysql多表查询

SELECT employee_id , department_name
FROM employees e LEFT JOIN departments d
ON e.department_id =  d.department_id;

mysql多表查询

2.3 右外连接

mysql多表查询

SELECT employee_id , department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id =  d.department_id;

mysql多表查询

2.4 左中图

注意：这里执行顺序是先where语句里面执行，所以得出d.DEPARTMENT_ID 为 NULL ，这样e.DEPARTMENT_ID = NULL的字段其实就是我们要的A表部分数据。

mysql多表查询

SELECT employee_id , department_name
FROM employees e LEFT JOIN departments d
ON e.department_id =  d.department_id
WHERE d.DEPARTMENT_ID IS NULL;

mysql多表查询

2.5 右中图

mysql多表查询和左中图同理

SELECT employee_id , department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id =  d.department_id
WHERE e.employee_id IS NULL;

mysql多表查询

2.6 满外连接

mysql多表查询方式1：
mysql多表查询
mysql多表查询
左中图 union all 右外连接（自动去重）

SELECT employee_id , department_name
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 , department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id =  d.department_id;

mysql多表查询方法2：
右中图 union all 左外连接（自动去重）
mysql多表查询mysql多表查询

SELECT employee_id , department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id =  d.department_id
WHERE e.employee_id IS NULL
UNION ALL
SELECT employee_id , department_name
FROM employees e LEFT JOIN departments d
ON e.department_id =  d.department_id;

mysql多表查询
方法三：
mysql多表查询mysql多表查询mysql多表查询

SELECT employee_id , department_name
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 , department_name
FROM employees e JOIN departments d
ON e.department_id =  d.department_id
UNION ALL
SELECT employee_id , department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id =  d.department_id
WHERE e.employee_id IS NULL;

mysql多表查询

五、mysql99语法的新特性

1.自然连接

我们先看看普通连接

employees 和 departments 只有两个相同的字段department_id和manager_id

SELECT employee_id , last_name,department_name
FROM employees e JOIN departments d
ON e.department_id =  d.department_id
AND e.manager_id =  d.manager_id;

mysql多表查询
自然连接就是相同字段相等的属性保留

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

mysql多表查询

2.using

普通语法

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

mysql多表查询

SELECT employee_id , last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

mysql多表查询

总结

上面就是多表查询常用的方法和语句，虽然不是全部，但是也是大多数情况能碰到的了，希望大家好好学习，最后能给我点个👍吗

网络标签：

相关问题