文章目录
我们缺乏的不是知识,而是学而不厌的态度
1、一个案例引发的多表连接
多表查询,也称为关联查询,指两个或更多个表一起完成查询操作。
前提条件:这些一起查询的表之间是有关系的(一对一、一对多),它们之间一定是有关联字段,这个关联字段可能建立了外键,也可能没有建立外键。
比如:员工表和部门表,这两个表依靠“部门编号”进行关联。
1.1、案例说明
从多个表中获取数据:
employees 表获取 employee_id 和 department_id 字段,departments 表获取 department_name 字段。
#案例:查询员工的姓名及其部门名称
SELECT last_name,
department_name
FROM employees, departments;
查询结果:
mysql> SELECT last_name, department_name FROM employees, departments;
+-------------+----------------------+
| last_name | department_name |
+-------------+----------------------+
| King | Payroll |
| King | Recruiting |
| King | Retail Sales |
| King | Government Sales |
| King | IT Helpdesk |
| King | NOC |
| King | IT Support |
| King | Operations |
| King | Contracting |
| King | Construction |
| King | Manufacturing |
| King | Benefits |
| King | Shareholder Services |
| King | Control And Credit |
| King | Corporate Tax |
| King | Treasury |
| King | Accounting |
| King | Finance |
| King | Executive |
| King | Sales |
| King | Public Relations |
| King | IT |
| King | Shipping |
| King | Human Resources |
| King | Purchasing |
| King | Marketing |
| King | Administration |
......
| Gietz | Administration |
+-------------+----------------------+
2889 rows in set (0.00 sec)
分析错误情况:
mysql> SELECT COUNT(employee_id) FROM employees;
+--------------------+
| COUNT(employee_id) |
+--------------------+
| 107 |
+--------------------+
1 row in set (0.01 sec)
#输出107行
mysql> SELECT COUNT(department_id)FROM departments;
+----------------------+
| COUNT(department_id) |
+----------------------+
| 27 |
+----------------------+
1 row in set (0.00 sec)
#输出27行
mysql> SELECT 107*27 FROM dual;
+--------+
| 107*27 |
+--------+
| 2889 |
+--------+
1 row in set (0.00 sec)
#输出2889
我们把上述多表查询中出现的问题称为:笛卡尔积的错误。
1.2、笛卡尔积(或交叉连接)的理解
笛卡尔乘积是一个数学运算。
假设我有两个集合 X 和 Y,那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合,也就是第一个对象来自于 X,第二个对象来自于 Y 的所有可能。
组合的个数即为两个集合中元素个数的乘积数。
SQL92中,笛卡尔积也称为 交叉连接 ,英文是 CROSS JOIN 。
在 SQL99 中也是使用 CROSS JOIN表示交叉连接。
它的作用就是可以把任意表进行连接,即使这两张表不相关。
在MySQL中如下情况会出现笛卡尔积:
#查询员工姓名和所在部门名称
SELECT last_name, department_name FROM employees, departments;
SELECT last_name, department_name FROM employees CROSS JOIN departments;
SELECT last_name, department_name FROM employees INNER JOIN departments;
SELECT last_name, department_name FROM employees JOIN departments;
1.3、案例分析与问题解决
- 笛卡尔积的错误会在下面条件下产生:
- 省略多个表的连接条件(或关联条件)
- 连接条件(或关联条件)无效
- 所有表中的所有行互相连接
- 为了避免笛卡尔积, 可以在 WHERE 加入有效的连接条件。
- 加入连接条件后,查询语法:
#在 WHERE子句中写入连接条件。
SELECT table1.column,
table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2; #连接条件
#案例:查询员工的姓名及其部门名称
mysql> SELECT last_name, department_name FROM employees JOIN departments WHERE employees.department_id = departments.department_id;
+-------------+------------------+
| last_name | department_name |
+-------------+------------------+
| Whalen | Administration |
| Hartstein | Marketing |
| Fay | Marketing |
| Raphaely | Purchasing |
......
| Gietz | Accounting |
+-------------+------------------+
106 rows in set (0.00 sec)
- 在表中有相同列时,在列名之前加上表名前缀。
2、多表查询分类讲解
2.1、等值连接 vs 非等值连接
2.1.1、等值连接
SELECT employees.employee_id,
employees.last_name,
employees.department_id,
departments.department_id,
departments.location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;