Mysql连接查询

最新推荐文章于 2024-08-29 10:15:00 发布

只要名字够长就有猪跟着念

最新推荐文章于 2024-08-29 10:15:00 发布

阅读量376

点赞数 6

分类专栏： Mysql基础文章标签： mysql android 数据库

本文链接：https://blog.csdn.net/JennyXi2001/article/details/136469161

版权

Mysql基础专栏收录该内容

16 篇文章 0 订阅

订阅专栏

本文详细介绍了SQL中的连接查询，包括内连接、等值连接、非等值连接、自连接、外连接和全连接的概念，以及如何使用别名提高查询效率。特别提到了笛卡尔积现象及其避免方法。

摘要由CSDN通过智能技术生成

连接查询

连接查询的定义

连接查询的定义

从一张表中单独查询称为单表查询
多张表的数据，从A表中取出一部分，从B表中去除一部分，将这两部分连接起来，查询，称为连接查询。

连接查询的类型

内连接inner join

等值连接

查询每个员工所在的部门名称，并显示员工名称和部门名称

#SQL92语法
mysql> select
    -> e.ename as '员工名',d.dname as '部门名'
    -> from
    -> emp e,dept d
    -> where
    -> e.deptno=d.deptno;
+-----------+------------+
| 员工名    | 部门名     |
+-----------+------------+
| CLARK     | ACCOUNTING |
| KING      | ACCOUNTING |
| MILLER    | ACCOUNTING |
| SMITH     | RESEARCH   |
| JONES     | RESEARCH   |
| SCOTT     | RESEARCH   |
| ADAMS     | RESEARCH   |
| FORD      | RESEARCH   |
| ALLEN     | SALES      |
| WARD      | SALES      |
| MARTIN    | SALES      |
| BLAKE     | SALES      |
| TURNER    | SALES      |
| JAMES     | SALES      |
+-----------+------------+
#92语法:结构不清晰，where后面容易混杂

#SQL99语法
mysql> select
    -> e.ename as '员工名',d.dname as '部门名'
    -> from
    -> emp e
    -> inner join
    -> dept d
    -> on
    -> e.deptno=d.deptno;
+-----------+------------+
| 员工名    | 部门名     |
+-----------+------------+
| CLARK     | ACCOUNTING |
| KING      | ACCOUNTING |
| MILLER    | ACCOUNTING |
| SMITH     | RESEARCH   |
| JONES     | RESEARCH   |
| SCOTT     | RESEARCH   |
| ADAMS     | RESEARCH   |
| FORD      | RESEARCH   |
| ALLEN     | SALES      |
| WARD      | SALES      |
| MARTIN    | SALES      |
| BLAKE     | SALES      |
| TURNER    | SALES      |
| JAMES     | SALES      |
+-----------+------------+

非等值连接

找出每个员工的薪资等级，要求显示员工名，薪资和薪资等级

mysql> select
    -> e.ename as '员工名称',e.sal as '工资',s.grade as '工资等级'
    -> from
    -> emp e
    -> inner join
    -> salgrade s
    -> on
    -> e.sal between s.losal and hisal
    -> order by
    -> e.sal asc;
+--------------+---------+--------------+
| 员工名称     | 工资    | 工资等级     |
+--------------+---------+--------------+
| SMITH        |  800.00 |            1 |
| JAMES        |  950.00 |            1 |
| ADAMS        | 1100.00 |            1 |
| WARD         | 1250.00 |            2 |
| MARTIN       | 1250.00 |            2 |
| MILLER       | 1300.00 |            2 |
| TURNER       | 1500.00 |            3 |
| ALLEN        | 1600.00 |            3 |
| CLARK        | 2450.00 |            4 |
| BLAKE        | 2850.00 |            4 |
| JONES        | 2975.00 |            4 |
| SCOTT        | 3000.00 |            4 |
| FORD         | 3000.00 |            4 |
| KING         | 5000.00 |            5 |
+--------------+---------+--------------+

自连接

查询员工的上级领导，要求显示员工名和对应的领导名称

mysql> select
    -> e.empno as '员工编号',e.ename as '员工名',e.mgr as '领导编号',b.ename as '领导名称'
    -> from
    -> emp e
    -> inner join
    -> emp b
    -> on
    -> ifnull(e.mgr,7839)=b.empno;
+--------------+-----------+--------------+--------------+
| 员工编号     | 员工名    | 领导编号     | 领导名称     |
+--------------+-----------+--------------+--------------+
|         7369 | SMITH     |         7902 | FORD         |
|         7499 | ALLEN     |         7698 | BLAKE        |
|         7521 | WARD      |         7698 | BLAKE        |
|         7566 | JONES     |         7839 | KING         |
|         7654 | MARTIN    |         7698 | BLAKE        |
|         7698 | BLAKE     |         7839 | KING         |
|         7782 | CLARK     |         7839 | KING         |
|         7788 | SCOTT     |         7566 | JONES        |
|         7839 | KING      |         NULL | KING         |
|         7844 | TURNER    |         7698 | BLAKE        |
|         7876 | ADAMS     |         7788 | SCOTT        |
|         7900 | JAMES     |         7698 | BLAKE        |
|         7902 | FORD      |         7566 | JONES        |
|         7934 | MILLER    |         7782 | CLARK        |
+--------------+-----------+--------------+--------------+

外连接

左外连接（左连接
右外连接（右连接

全连接

两张表进行连接

案例：查询每个员工所在部门的名称？

mysql> select ename,deptno from emp;
+--------+--------+
| ename  | deptno |
+--------+--------+
| SMITH  |     20 |
| ALLEN  |     30 |
| WARD   |     30 |
| JONES  |     20 |
| MARTIN |     30 |
| BLAKE  |     30 |
| CLARK  |     10 |
| SCOTT  |     20 |
| KING   |     10 |
| TURNER |     30 |
| ADAMS  |     20 |
| JAMES  |     30 |
| FORD   |     20 |
| MILLER |     10 |
+--------+--------+

mysql> select deptno,dname from dept;
+--------+------------+
| deptno | dname      |
+--------+------------+
|     10 | ACCOUNTING |
|     20 | RESEARCH   |
|     30 | SALES      |
|     40 | OPERATIONS |
+--------+------------+

笛卡尔积现象

当两张表进行连接查询，没有任何限制的时候，最终的结果是两张表记录的乘积，A表有5条，B表有6条，则结果就又5*6=30条
得出结论：在进行表连接时，需要在连接时将条件增加到后面：

mysql> select ename,dname
    -> from emp,dept
    -> where
    -> emp.deptno=dept.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| JONES  | RESEARCH   |
| SCOTT  | RESEARCH   |
| ADAMS  | RESEARCH   |
| FORD   | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| TURNER | SALES      |
| JAMES  | SALES      |
+--------+------------+

最终的查询结果条数是14条，但是在匹配的过程中，匹配的次数还是没有减少，依然是56次，但是4选1。

给表起别名

mysql> select e.ename as '员工姓名',e.deptno as '部门编号',d.dname as '部门名称'
    -> from emp as e,dept as d
    -> where e.deptno=d.deptno;
+--------------+--------------+--------------+
| 员工姓名     | 部门编号     | 部门名称     |
+--------------+--------------+--------------+
| CLARK        |           10 | ACCOUNTING   |
| KING         |           10 | ACCOUNTING   |
| MILLER       |           10 | ACCOUNTING   |
| SMITH        |           20 | RESEARCH     |
| JONES        |           20 | RESEARCH     |
| SCOTT        |           20 | RESEARCH     |
| ADAMS        |           20 | RESEARCH     |
| FORD         |           20 | RESEARCH     |
| ALLEN        |           30 | SALES        |
| WARD         |           30 | SALES        |
| MARTIN       |           30 | SALES        |
| BLAKE        |           30 | SALES        |
| TURNER       |           30 | SALES        |
| JAMES        |           30 | SALES        |
+--------------+--------------+--------------+