• 【六】【SQL】多表查询，笛卡尔积

    笛卡尔积

    笛卡尔积发生在当你在查询中将两个或多个表进行交叉连接（CROSS JOIN）或者没有指定任何连接条件时。假设第一个表有M行，第二个表有N行，那么结果集将包含M x N个记录。在大多数情况下，笛卡尔积并不是你想要的结果，因为它会产生大量的无关数据。但是，理解它是如何发生的对于避免无意中产生笛卡尔积是很有帮助的。

    显示雇员名、雇员工资以及所在部门的名字

    因为上面的数据来自于EMP和DEPT表，因此要联合查询

     

    1. mysql> select *from emp;
    2. +--------+--------+-----------+------+---------------------+---------+---------+--------+
    3. | empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
    4. +--------+--------+-----------+------+---------------------+---------+---------+--------+
    5. | 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
    6. | 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
    7. | 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
    8. | 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
    9. | 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
    10. | 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
    11. | 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
    12. | 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
    13. | 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
    14. | 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
    15. | 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
    16. | 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
    17. | 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
    18. | 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
    19. +--------+--------+-----------+------+---------------------+---------+---------+--------+
    20. 14 rows in set (0.00 sec)
    21.  
    22. mysql> select *from dept;
    23. +--------+------------+----------+
    24. | deptno | dname      | loc      |
    25. +--------+------------+----------+
    26. |     10 | ACCOUNTING | NEW YORK |
    27. |     20 | RESEARCH   | DALLAS   |
    28. |     30 | SALES      | CHICAGO  |
    29. |     40 | OPERATIONS | BOSTON   |
    30. +--------+------------+----------+
    31. 4 rows in set (0.00 sec)
    32.  
    33. mysql> select *from emp,dept;
    34. +--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
    35. | empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno | deptno | dname      | loc      |
    36. +--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
    37. | 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     10 | ACCOUNTING | NEW YORK |
    38. | 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
    39. | 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     30 | SALES      | CHICAGO  |
    40. | 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     40 | OPERATIONS | BOSTON   |
    41. | 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     10 | ACCOUNTING | NEW YORK |
    42. | 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     20 | RESEARCH   | DALLAS   |
    43. | 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     30 | SALES      | CHICAGO  |
    44. | 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     40 | OPERATIONS | BOSTON   |
    45. | 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     10 | ACCOUNTING | NEW YORK |
    46. | 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     20 | RESEARCH   | DALLAS   |
    47. | 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     30 | SALES      | CHICAGO  |
    48. | 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     40 | OPERATIONS | BOSTON   |
    49. | 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     10 | ACCOUNTING | NEW YORK |
    50. | 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
    51. | 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     30 | SALES      | CHICAGO  |
    52. | 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     40 | OPERATIONS | BOSTON   |
    53. | 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     10 | ACCOUNTING | NEW YORK |
    54. | 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     20 | RESEARCH   | DALLAS   |
    55. | 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     30 | SALES      | CHICAGO  |
    56. | 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     40 | OPERATIONS | BOSTON   |
    57. | 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     10 | ACCOUNTING | NEW YORK |
    58. | 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     20 | RESEARCH   | DALLAS   |
    59. | 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
    60. | 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     40 | OPERATIONS | BOSTON   |
    61. | 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
    62. | 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     20 | RESEARCH   | DALLAS   |
    63. | 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     30 | SALES      | CHICAGO  |
    64. | 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     40 | OPERATIONS | BOSTON   |
    65. | 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     10 | ACCOUNTING | NEW YORK |
    66. | 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
    67. | 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     30 | SALES      | CHICAGO  |
    68. | 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     40 | OPERATIONS | BOSTON   |
    69. | 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
    70. | 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     20 | RESEARCH   | DALLAS   |
    71. | 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     30 | SALES      | CHICAGO  |
    72. | 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     40 | OPERATIONS | BOSTON   |
    73. | 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     10 | ACCOUNTING | NEW YORK |
    74. | 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     20 | RESEARCH   | DALLAS   |
    75. | 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     30 | SALES      | CHICAGO  |
    76. | 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     40 | OPERATIONS | BOSTON   |
    77. | 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     10 | ACCOUNTING | NEW YORK |
    78. | 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
    79. | 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     30 | SALES      | CHICAGO  |
    80. | 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     40 | OPERATIONS | BOSTON   |
    81. | 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     10 | ACCOUNTING | NEW YORK |
    82. | 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     20 | RESEARCH   | DALLAS   |
    83. | 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
    84. | 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     40 | OPERATIONS | BOSTON   |
    85. | 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     10 | ACCOUNTING | NEW YORK |
    86. | 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
    87. | 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     30 | SALES      | CHICAGO  |
    88. | 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     40 | OPERATIONS | BOSTON   |
    89. | 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
    90. | 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     20 | RESEARCH   | DALLAS   |
    91. | 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     30 | SALES      | CHICAGO  |
    92. | 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     40 | OPERATIONS | BOSTON   |
    93. +--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
    94. 56 rows in set (0.02 sec)
    95.  
    96. mysql> select *from emp,dept where emp.deptno=dept.deptno;
    97. +--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
    98. | empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno | deptno | dname      | loc      |
    99. +--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
    100. | 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
    101. | 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     30 | SALES      | CHICAGO  |
    102. | 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     30 | SALES      | CHICAGO  |
    103. | 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
    104. | 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     30 | SALES      | CHICAGO  |
    105. | 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
    106. | 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
    107. | 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
    108. | 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
    109. | 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     30 | SALES      | CHICAGO  |
    110. | 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
    111. | 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
    112. | 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
    113. | 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
    114. +--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
    115. 14 rows in set (0.00 sec)
    116.  
    117. mysql> select ename,sal,dname from emp,dept where emp.deptno=dept.deptno;
    118. +--------+---------+------------+
    119. | ename  | sal     | dname      |
    120. +--------+---------+------------+
    121. | SMITH  |  800.00 | RESEARCH   |
    122. | ALLEN  | 1600.00 | SALES      |
    123. | WARD   | 1250.00 | SALES      |
    124. | JONES  | 2975.00 | RESEARCH   |
    125. | MARTIN | 1250.00 | SALES      |
    126. | BLAKE  | 2850.00 | SALES      |
    127. | CLARK  | 2450.00 | ACCOUNTING |
    128. | SCOTT  | 3000.00 | RESEARCH   |
    129. | KING   | 5000.00 | ACCOUNTING |
    130. | TURNER | 1500.00 | SALES      |
    131. | ADAMS  | 1100.00 | RESEARCH   |
    132. | JAMES  |  950.00 | SALES      |
    133. | FORD   | 3000.00 | RESEARCH   |
    134. | MILLER | 1300.00 | ACCOUNTING |
    135. +--------+---------+------------+
    136. 14 rows in set (0.00 sec)
    137.  
    138. mysql> 
     

    这些MySQL查询示例展示了如何从emp（员工）和dept（部门）两个表中进行数据检索，以及如何通过连接条件避免生成笛卡尔积，从而获取有意义的结果。
     

    简单的多表查询（产生笛卡尔积）: 当你执行select * from emp,dept;时，没有指定连接条件，因此结果是emp表和dept表之间的笛卡尔积。每个emp表中的行都会与dept表中的每行组合，如果emp表有14行，dept表有4行，那么结果集将有56行（14*4）。
     

    指定连接条件的多表查询: 通过添加where emp.deptno=dept.deptno;，你明确指定了连接条件，即只有当员工所在部门编号（deptno）与部门表中的部门编号相匹配时，相应的记录才会出现在结果集中。这种方式有效地避免了笛卡尔积，只返回了相关联的记录，即每个员工及其对应的部门信息。
     

    特定字段选择: 最后一个查询select ename,sal,dname from emp,dept where emp.deptno=dept.deptno;进一步细化了输出，只选择了员工的姓名（ename）、薪水（sal）和部门名称（dname）。通过这种方式，可以清晰地看到每个员工所属的部门及其薪水，而不是返回两个表的所有列，这使得结果更加简洁和有用。
     

    显示部门号为10的部门名，员工名和工资
     

     

    1. mysql> select *from emp,dept where emp.deptno=dept.deptno;
    2. +--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
    3. | empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno | deptno | dname      | loc      |
    4. +--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
    5. | 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
    6. | 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     30 | SALES      | CHICAGO  |
    7. | 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     30 | SALES      | CHICAGO  |
    8. | 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
    9. | 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     30 | SALES      | CHICAGO  |
    10. | 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
    11. | 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
    12. | 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
    13. | 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
    14. | 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     30 | SALES      | CHICAGO  |
    15. | 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
    16. | 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
    17. | 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
    18. | 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
    19. +--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
    20. 14 rows in set (0.00 sec)
    21.  
    22. mysql> select ename,sal,dname from emp,dept where emp.deptno=dept.deptno;
    23. +--------+---------+------------+
    24. | ename  | sal     | dname      |
    25. +--------+---------+------------+
    26. | SMITH  |  800.00 | RESEARCH   |
    27. | ALLEN  | 1600.00 | SALES      |
    28. | WARD   | 1250.00 | SALES      |
    29. | JONES  | 2975.00 | RESEARCH   |
    30. | MARTIN | 1250.00 | SALES      |
    31. | BLAKE  | 2850.00 | SALES      |
    32. | CLARK  | 2450.00 | ACCOUNTING |
    33. | SCOTT  | 3000.00 | RESEARCH   |
    34. | KING   | 5000.00 | ACCOUNTING |
    35. | TURNER | 1500.00 | SALES      |
    36. | ADAMS  | 1100.00 | RESEARCH   |
    37. | JAMES  |  950.00 | SALES      |
    38. | FORD   | 3000.00 | RESEARCH   |
    39. | MILLER | 1300.00 | ACCOUNTING |
    40. +--------+---------+------------+
    41. 14 rows in set (0.00 sec)
    42.  
    43. mysql> select ename,sal,dname from emp,dept where emp.deptno=dept.deptno and emp.deptno=10;
    44. +--------+---------+------------+
    45. | ename  | sal     | dname      |
    46. +--------+---------+------------+
    47. | CLARK  | 2450.00 | ACCOUNTING |
    48. | KING   | 5000.00 | ACCOUNTING |
    49. | MILLER | 1300.00 | ACCOUNTING |
    50. +--------+---------+------------+
    51. 3 rows in set (0.00 sec)
    52.  
    53. mysql> select ename,sal,dname,deptno from emp,dept where emp.deptno=dept.deptno and emp.deptno=10;
    54. ERROR 1052 (23000): Column 'deptno' in field list is ambiguous
    55. mysql> select ename,sal,dname,emp.deptno from emp,dept where emp.deptno=dept.deptno and emp.deptno=10;
    56. +--------+---------+------------+--------+
    57. | ename  | sal     | dname      | deptno |
    58. +--------+---------+------------+--------+
    59. | CLARK  | 2450.00 | ACCOUNTING |     10 |
    60. | KING   | 5000.00 | ACCOUNTING |     10 |
    61. | MILLER | 1300.00 | ACCOUNTING |     10 |
    62. +--------+---------+------------+--------+
    63. 3 rows in set (0.00 sec)
    64.  
    65. mysql> 
     

    在这些MySQL查询中，我们看到了如何通过连接两个表（emp和dept）来获取员工的信息以及他们所属的部门信息。最后两个查询特别展示了如何解决列名冲突和如何正确地引用列名以避免错误。
     

    解决列名冲突： 在执行多表查询时，如果两个表中存在同名的列（在这个例子中是deptno），直接引用这个列名会导致“列名是模糊的”（ambiguous）错误。这是因为SQL无法判断你希望选择哪个表中的deptno列。
     

    错误示例： 查询select ename,sal,dname,deptno from emp,dept where emp.deptno=dept.deptno and emp.deptno=10;失败并报错ERROR 1052 (23000): Column 'deptno' in field list is ambiguous。这是因为deptno同时存在于emp和dept表中，而查询没有指明应该从哪个表中选择该列。
     

    解决方法： 通过在列名前加上表名或别名来解决这个问题。如select ename,sal,dname,emp.deptno from emp,dept where emp.deptno=dept.deptno and emp.deptno=10;明确指出选择emp.deptno，从而解决了列名冲突问题，并成功执行了查询。
     

    显示每个员工的姓名、工资和工资级别
     

     

    1. mysql> select *from salgrade;
    2. +-------+-------+-------+
    3. | grade | losal | hisal |
    4. +-------+-------+-------+
    5. |     1 |   700 |  1200 |
    6. |     2 |  1201 |  1400 |
    7. |     3 |  1401 |  2000 |
    8. |     4 |  2001 |  3000 |
    9. |     5 |  3001 |  9999 |
    10. +-------+-------+-------+
    11. 5 rows in set (0.00 sec)
    12.  
    13. mysql> select *from emp,salgrade;
    14. +--------+--------+-----------+------+---------------------+---------+---------+--------+-------+-------+-------+
    15. | empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno | grade | losal | hisal |
    16. +--------+--------+-----------+------+---------------------+---------+---------+--------+-------+-------+-------+
    17. | 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     1 |   700 |  1200 |
    18. | 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     2 |  1201 |  1400 |
    19. | 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     3 |  1401 |  2000 |
    20. | 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     4 |  2001 |  3000 |
    21. | 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     5 |  3001 |  9999 |
    22. | 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     1 |   700 |  1200 |
    23. | 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     2 |  1201 |  1400 |
    24. | 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     3 |  1401 |  2000 |
    25. | 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     4 |  2001 |  3000 |
    26. | 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     5 |  3001 |  9999 |
    27. | 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     1 |   700 |  1200 |
    28. | 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     2 |  1201 |  1400 |
    29. | 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     3 |  1401 |  2000 |
    30. | 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     4 |  2001 |  3000 |
    31. | 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     5 |  3001 |  9999 |
    32. | 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     1 |   700 |  1200 |
    33. | 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     2 |  1201 |  1400 |
    34. | 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     3 |  1401 |  2000 |
    35. | 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     4 |  2001 |  3000 |
    36. | 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     5 |  3001 |  9999 |
    37. | 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     1 |   700 |  1200 |
    38. | 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     2 |  1201 |  1400 |
    39. | 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     3 |  1401 |  2000 |
    40. | 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     4 |  2001 |  3000 |
    41. | 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     5 |  3001 |  9999 |
    42. | 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     1 |   700 |  1200 |
    43. | 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     2 |  1201 |  1400 |
    44. | 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     3 |  1401 |  2000 |
    45. | 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     4 |  2001 |  3000 |
    46. | 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     5 |  3001 |  9999 |
    47. | 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     1 |   700 |  1200 |
    48. | 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     2 |  1201 |  1400 |
    49. | 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     3 |  1401 |  2000 |
    50. | 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     4 |  2001 |  3000 |
    51. | 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     5 |  3001 |  9999 |
    52. | 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     1 |   700 |  1200 |
    53. | 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     2 |  1201 |  1400 |
    54. | 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     3 |  1401 |  2000 |
    55. | 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     4 |  2001 |  3000 |
    56. | 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     5 |  3001 |  9999 |
    57. | 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     1 |   700 |  1200 |
    58. | 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     2 |  1201 |  1400 |
    59. | 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     3 |  1401 |  2000 |
    60. | 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     4 |  2001 |  3000 |
    61. | 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     5 |  3001 |  9999 |
    62. | 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     1 |   700 |  1200 |
    63. | 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     2 |  1201 |  1400 |
    64. | 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     3 |  1401 |  2000 |
    65. | 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     4 |  2001 |  3000 |
    66. | 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     5 |  3001 |  9999 |
    67. | 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     1 |   700 |  1200 |
    68. | 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     2 |  1201 |  1400 |
    69. | 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     3 |  1401 |  2000 |
    70. | 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     4 |  2001 |  3000 |
    71. | 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     5 |  3001 |  9999 |
    72. | 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     1 |   700 |  1200 |
    73. | 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     2 |  1201 |  1400 |
    74. | 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     3 |  1401 |  2000 |
    75. | 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     4 |  2001 |  3000 |
    76. | 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     5 |  3001 |  9999 |
    77. | 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     1 |   700 |  1200 |
    78. | 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     2 |  1201 |  1400 |
    79. | 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     3 |  1401 |  2000 |
    80. | 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     4 |  2001 |  3000 |
    81. | 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     5 |  3001 |  9999 |
    82. | 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     1 |   700 |  1200 |
    83. | 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     2 |  1201 |  1400 |
    84. | 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     3 |  1401 |  2000 |
    85. | 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     4 |  2001 |  3000 |
    86. | 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     5 |  3001 |  9999 |
    87. +--------+--------+-----------+------+---------------------+---------+---------+--------+-------+-------+-------+
    88. 70 rows in set (0.00 sec)
    89.  
    90. mysql> select ename,sal ,grade from emp,salgrade;
    91. +--------+---------+-------+
    92. | ename  | sal     | grade |
    93. +--------+---------+-------+
    94. | SMITH  |  800.00 |     1 |
    95. | SMITH  |  800.00 |     2 |
    96. | SMITH  |  800.00 |     3 |
    97. | SMITH  |  800.00 |     4 |
    98. | SMITH  |  800.00 |     5 |
    99. | ALLEN  | 1600.00 |     1 |
    100. | ALLEN  | 1600.00 |     2 |
    101. | ALLEN  | 1600.00 |     3 |
    102. | ALLEN  | 1600.00 |     4 |
    103. | ALLEN  | 1600.00 |     5 |
    104. | WARD   | 1250.00 |     1 |
    105. | WARD   | 1250.00 |     2 |
    106. | WARD   | 1250.00 |     3 |
    107. | WARD   | 1250.00 |     4 |
    108. | WARD   | 1250.00 |     5 |
    109. | JONES  | 2975.00 |     1 |
    110. | JONES  | 2975.00 |     2 |
    111. | JONES  | 2975.00 |     3 |
    112. | JONES  | 2975.00 |     4 |
    113. | JONES  | 2975.00 |     5 |
    114. | MARTIN | 1250.00 |     1 |
    115. | MARTIN | 1250.00 |     2 |
    116. | MARTIN | 1250.00 |     3 |
    117. | MARTIN | 1250.00 |     4 |
    118. | MARTIN | 1250.00 |     5 |
    119. | BLAKE  | 2850.00 |     1 |
    120. | BLAKE  | 2850.00 |     2 |
    121. | BLAKE  | 2850.00 |     3 |
    122. | BLAKE  | 2850.00 |     4 |
    123. | BLAKE  | 2850.00 |     5 |
    124. | CLARK  | 2450.00 |     1 |
    125. | CLARK  | 2450.00 |     2 |
    126. | CLARK  | 2450.00 |     3 |
    127. | CLARK  | 2450.00 |     4 |
    128. | CLARK  | 2450.00 |     5 |
    129. | SCOTT  | 3000.00 |     1 |
    130. | SCOTT  | 3000.00 |     2 |
    131. | SCOTT  | 3000.00 |     3 |
    132. | SCOTT  | 3000.00 |     4 |
    133. | SCOTT  | 3000.00 |     5 |
    134. | KING   | 5000.00 |     1 |
    135. | KING   | 5000.00 |     2 |
    136. | KING   | 5000.00 |     3 |
    137. | KING   | 5000.00 |     4 |
    138. | KING   | 5000.00 |     5 |
    139. | TURNER | 1500.00 |     1 |
    140. | TURNER | 1500.00 |     2 |
    141. | TURNER | 1500.00 |     3 |
    142. | TURNER | 1500.00 |     4 |
    143. | TURNER | 1500.00 |     5 |
    144. | ADAMS  | 1100.00 |     1 |
    145. | ADAMS  | 1100.00 |     2 |
    146. | ADAMS  | 1100.00 |     3 |
    147. | ADAMS  | 1100.00 |     4 |
    148. | ADAMS  | 1100.00 |     5 |
    149. | JAMES  |  950.00 |     1 |
    150. | JAMES  |  950.00 |     2 |
    151. | JAMES  |  950.00 |     3 |
    152. | JAMES  |  950.00 |     4 |
    153. | JAMES  |  950.00 |     5 |
    154. | FORD   | 3000.00 |     1 |
    155. | FORD   | 3000.00 |     2 |
    156. | FORD   | 3000.00 |     3 |
    157. | FORD   | 3000.00 |     4 |
    158. | FORD   | 3000.00 |     5 |
    159. | MILLER | 1300.00 |     1 |
    160. | MILLER | 1300.00 |     2 |
    161. | MILLER | 1300.00 |     3 |
    162. | MILLER | 1300.00 |     4 |
    163. | MILLER | 1300.00 |     5 |
    164. +--------+---------+-------+
    165. 70 rows in set (0.00 sec)
    166.  
    167. mysql> select ename,sal ,grade from emp,salgrade where sal between losal and hisal;
    168. +--------+---------+-------+
    169. | ename  | sal     | grade |
    170. +--------+---------+-------+
    171. | SMITH  |  800.00 |     1 |
    172. | ALLEN  | 1600.00 |     3 |
    173. | WARD   | 1250.00 |     2 |
    174. | JONES  | 2975.00 |     4 |
    175. | MARTIN | 1250.00 |     2 |
    176. | BLAKE  | 2850.00 |     4 |
    177. | CLARK  | 2450.00 |     4 |
    178. | SCOTT  | 3000.00 |     4 |
    179. | KING   | 5000.00 |     5 |
    180. | TURNER | 1500.00 |     3 |
    181. | ADAMS  | 1100.00 |     1 |
    182. | JAMES  |  950.00 |     1 |
    183. | FORD   | 3000.00 |     4 |
    184. | MILLER | 1300.00 |     2 |
    185. +--------+---------+-------+
    186. 14 rows in set (0.00 sec)
    187.  
    188. mysql> select ename,sal ,grade,losal ,hisal  from emp,salgrade where sal between losal and hisal;
    189. +--------+---------+-------+-------+-------+
    190. | ename  | sal     | grade | losal | hisal |
    191. +--------+---------+-------+-------+-------+
    192. | SMITH  |  800.00 |     1 |   700 |  1200 |
    193. | ALLEN  | 1600.00 |     3 |  1401 |  2000 |
    194. | WARD   | 1250.00 |     2 |  1201 |  1400 |
    195. | JONES  | 2975.00 |     4 |  2001 |  3000 |
    196. | MARTIN | 1250.00 |     2 |  1201 |  1400 |
    197. | BLAKE  | 2850.00 |     4 |  2001 |  3000 |
    198. | CLARK  | 2450.00 |     4 |  2001 |  3000 |
    199. | SCOTT  | 3000.00 |     4 |  2001 |  3000 |
    200. | KING   | 5000.00 |     5 |  3001 |  9999 |
    201. | TURNER | 1500.00 |     3 |  1401 |  2000 |
    202. | ADAMS  | 1100.00 |     1 |   700 |  1200 |
    203. | JAMES  |  950.00 |     1 |   700 |  1200 |
    204. | FORD   | 3000.00 |     4 |  2001 |  3000 |
    205. | MILLER | 1300.00 |     2 |  1201 |  1400 |
    206. +--------+---------+-------+-------+-------+
    207. 14 rows in set (0.00 sec)
    208.  
    209. mysql> 
    210.  
     

    这些MySQL命令和查询展示了如何使用emp和salgrade表来匹配员工的薪资与相应的薪资等级。通过正确应用条件语句，可以有效地筛选出符合特定薪资范围的员工记录，并将其与相应的薪资等级关联起来。
     

    直接笛卡尔积查询： 初始查询select *from emp,salgrade;产生了一个笛卡尔积，这意味着emp表中的每一行都与salgrade表中的每一行配对，不考虑任何匹配条件。这通常不是期望的结果，因为它返回了所有可能的行组合，而不是根据实际的薪资等级来筛选员工。
     

    筛选匹配薪资等级的员工： 通过执行select ename,sal ,grade from emp,salgrade where sal between losal and hisal;，我们可以获得一个更实用的结果集，其中只包括那些其薪资位于salgrade表定义的最低薪资(losal)和最高薪资(hisal)范围内的员工。这种查询方式直观地展示了如何将两个表中的数据根据实际业务逻辑关联起来。
     

    增加薪资范围信息： 最后的查询select ename,sal ,grade,losal ,hisal from emp,salgrade where sal between losal and hisal;进一步扩展了上一查询，除了返回员工的姓名、薪资和薪资等级外，还包括了确定该薪资等级的最低和最高薪资范围。这提供了更详细的信息，帮助理解为什么特定的员工会被归入特定的薪资等级。
     

    结尾
     

    最后，感谢您阅读我的文章，希望这些内容能够对您有所启发和帮助。如果您有任何问题或想要分享您的观点，请随时在评论区留言。
     

     

    同时，不要忘记订阅我的博客以获取更多有趣的内容。在未来的文章中，我将继续探讨这个话题的不同方面，为您呈现更多深度和见解。
     

     

    谢谢您的支持，期待与您在下一篇文章中再次相遇！
     

    
                
    
                    
    
                
    • 

                
  • 

                    相关阅读:
    
                    
Java.lang.Class类 getDeclaredMethod()方法有什么功能呢？                            
    
WMWS在线监测管理系统的工程常用计算工具                            
    
期末复习总结【MySQL】五种约束类型, 主键和外键的使用方式(重点)                            
    
嵌入式C语言中整形溢出问题分析                            
    
阿里P8熬了一个月肝出这份32W字Java面试手册，在Github标星31K+                            
    
js中导入引用外部js                            
    
nginx安装（离线安装，新增--with-http_ssl_module、--with-stream模块，离线升级）                            
    
拼多多不满“国内市场”，将于9月中旬在美国推出跨境电商平台                            
    
基于JAVA民宿网站管理系统计算机毕业设计源码+数据库+lw文档+系统+部署                            
    
Java 基础 进程与线程                            
    
                        
                
    • 
                
  • 
                    原文地址：https://blog.csdn.net/m0_74163972/article/details/136411498
                
    •