MySQL连接查询

brevity分享 2021-08-27

1198

前面几次介绍的查询都是基于单个表的，而实际中使用的查询语句往往会涉及到多个表，也就是说，要查询的字段不在同一个表中，这就要使用到连接查询。

笛卡尔积现象：假如表1有m行，表2有n行，笛卡尔积的结果就是m*n行，因为查询的SQL语句没有有效的连接条件，如下SQL所示：

  SELECT `name`,boyName FROM beauty,boys;

非笛卡尔积，即添加了连接条件：

SELECT `name`,boyName FROM beauty,boys WHERE beauty.`boyfriend_id` = boys.`id`;

SQL连接根据功能分类分为七类：①等值连接；②非等值连接；③自连接；④左外连接；⑤右外连接；⑥全连接；⑦交叉连接。根据年代分类分为SQL92和SQL99标准。

等值连接中可以为表起别名，如果同一个字段出现在多个表中，查询列表中的列要用表名.字段进行区分，但是起了别名后不能使用原来的表名进行字段的区分：

SELECT last_name,department_name FROM employees,departments WHERE employees.department_id = departments.department_id;
SELECT last_name,e.job_id,job_title FROM employees AS e,jobs j WHERE e.job_id = j.job_id;
SELECT last_name,commission_pct,department_name FROM employees e,departments d WHERE e.`department_id` =d.`department_id` AND e.`commission_pct` IS NOT NULL;
SELECT city,department_name FROM departments d,locations l WHERE d.`location_id` = l.`location_id` AND city LIKE 'T%';
SELECT city,COUNT(*) 个数 FROM locations l,departments d WHERE d.`location_id` =l.`location_id` GROUP BY city;
SELECT d.manager_id,department_name,MIN(salary) FROM employees e,departments d WHERE d.`department_id` = e.`department_id` AND commission_pct IS NOT NULL GROUP BY department_name,d.manager_id;
SELECT job_title,COUNT(*) FROM employees e,jobs j WHERE e.`job_id` = j.`job_id` GROUP BY job_title ORDER BY COUNT(*) DESC;
SELECT last_name,department_name,city FROM employees e,departments d,locations l WHERE e.`department_id` = d.`department_id` AND d.`location_id` = l.`location_id` GROUP BY department_name ORDER BY LENGTH(city) DESC;

非等值连接：

SELECT salary,grade_level FROM employees e,job_grades jg WHERE salary BETWEEN lowest_sal AND highest_sal AND grade_level = 'D' ORDER BY grade_level;

自连接：

SELECT e.employee_id,e.last_name,m.employee_id,m.last_name FROM employees e,employees m WHERE e.manager_id = m.employee_id;

上面这些的SQL语句都是FROM后面跟多个表，这种语法是SQL92的语法，下面要介绍的是另一种写法，使用JOIN ON关键字进行表连接，这种是新的SQL99语法，语法格式如下：

SELECT 查询列表 FROM 表1 别名 [连接类型] JOIN 表2 别名 ON 连接条件 [WHERE 筛选条件] [GROUP BY 分组] [HAVING 筛选条件] [ORDER BY 排序列表];

其中连接类型分为内连接INNER、左外连接 LEFT [OUTER]、右外连接 RIGHT [OUTER]、全外连接 FULL [OUTER] (MySQL不支持)、交叉连接CROSS；LEFT JOIN左边的是主表，RIGHT JOIN右边的是主表，左外连接和右外连接交换两个表的顺序，实现的效果是相同的；交叉连接的结果仍然是笛卡尔积。

示例如下：

SELECT last_name,department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id;
SELECT last_name,job_title FROM employees e INNER JOIN jobs j ON e.job_id = j.job_id WHERE e.last_name LIKE '%e%';
SELECT city,COUNT(*) 个数 FROM departments d INNER JOIN locations l ON d.`location_id` = l.`location_id` GROUP BY city HAVING 个数 > 3;
SELECT COUNT(*),department_name FROM employees e INNER JOIN departments d ON e.`department_id` = d.`department_id` GROUP BY department_name HAVING COUNT(*) > 3 ORDER BY COUNT(*) DESC;
SELECT last_name,department_name,job_title FROM employees e INNER JOIN departments d ON e.`department_id` = d.`department_id` INNER JOIN jobs j ON e.`job_id` = j.`job_id` ORDER BY department_name DESC;
SELECT COUNT(*),grade_level FROM employees e JOIN job_grades g ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal` GROUP BY grade_level HAVING COUNT(*) > 20 ORDER BY grade_level DESC;
SELECT e.`last_name`,m.`last_name` FROM employees e INNER JOIN employees m ON e.`manager_id` = m.`employee_id` WHERE e.`last_name` LIKE '%K%';
SELECT b.`name` FROM beauty b LEFT OUTER JOIN boys bo ON b.`boyfriend_id` = bo.`id` WHERE bo.`id` IS NOT NULL;
SELECT b.`name`,bo.* FROM boys bo RIGHT JOIN beauty b ON b.`boyfriend_id` = bo.`id` WHERE bo.`id` IS NULL;
SELECT d.*,e.`employee_id` FROM departments d LEFT JOIN employees e ON d.`department_id` = e.`department_id` WHERE e.`employee_id` IS NULL;
SELECT b.*,bo.* FROM beauty b CROSS JOIN boys bo;

mysql

文章转载自brevity分享，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL连接查询

评论