MYSQL--连接查询

1.含义

又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

2.笛卡尔乘积现象

表1 有m行，表2 有n行 ，结果=m*n行

发生原因：没有有效的连接条件

如何避免：添加有效的连接条件

3.分类

1.按年代分类

①SQL192标准

仅仅支持内连接

②SQL199标准【推荐】

支持内连接+外连接(左外+右外)+交叉连接

1.语法

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

2.分类

内连接(★)：inner

外连接：

        左外(★)：left【outer】

        右外(★)：right【outer】

        全外：full【outer】

交叉连接：cross

2.按功能分类

2.1内连接

①SQL192标准：等值连接

①多表等值连接的结果为多表的交集部分

②n表连接，至少需要n-1个连接条件

③多表的顺序没有要求

④一般需要为表起别名

⑤可以搭配前面介绍的所有子句使用，比如排序，分组，筛选

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

SELECT last_name,department_name FROM employees,departments WHERE employees.department_id = departments.department_id;

2.为表起别名

①提高语句的简洁度

②区分多个重名的字段

注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

SELECT e.last_name,e.job_id,j.job_title FROM employees e,jobs j WHERE e.job_id=j.job_id;

3.两个表的顺序是否可以调换

SELECT e.last_name,e.job_id,j.job_title FROM jobs j,employees e WHERE e.job_id = j.job_id;

4.可以加筛选

SELECT last_name,department_name FROM employees e,departments d WHERE e.department_id = d.department_id AND e.comission_pct IS NOT NULL;

SELECT department_name,city FROM departments d,locations l WHERE d.location_id = l.location_id AND city LIKE '_o%';

5.可以加分组

SELECT COUNT(*) 个数,city FROM departments d,locations l WHERE d.location_id = l.location_id GROUP BY city;

SELECT department_name,d.manager_id,MIN(salary) FROM departments d,employees e WHERE d.department_id = e.department_id AND commission_pct IS NOT NULL GROUP BY department_name,d.manager_id;

6.可以加排序

SELECT job_title,COUNT(*) FROM employees e,jobs j WHERE e.job_id = j.job_id GROUP BY job_title ORDER BY COUNT(*) DESC;

7.可以实现三表连接

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.loaction_id;

②SQL199标准：等值连接

特点：

①添加排序、分组、筛选

②INNER可以省略

③筛选条件放在WHERE后面，连接条件放在ON后面，提高分离性，便于阅读

④INNER JOIN连接和SQL192语法中的等值连接效果是一样的，都是查询多表的交集

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

1.添加筛选

SELECT las_name,job_title FROM employees e INNER JOIN jobs j ON e.job_id = j.job_id WHERE e.last_name LIKE '%e%';

2.添加分组和筛选

SELECT city,COUNT(*) 部门个数 FROM departments d INNER JOIN locations ON d.location_id = l.location_id GROUP BY city HAVING COUNT(*)>3;

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;

4.添加三表连接

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;

③SQL192标准：非等值连接

SELECT salary,grade_level FROM employees e,job_grades g WHERE salary BETWEEN g.lowest_sal AND g.highest_sal;

④SQL199标准：非等值连接

SELECT salary,grade_level FROM employees e JOIN job_grades g ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;

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:

⑤SQL192标准：自连接

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;

⑥SQL199标准：自连接

SELECT e.last_name,m.last_name FROM employees e JOIN employees m ON e.manager_id = m.employyee_id;

SELECT e.last_name,m.last_name FROM employees e JOIN employees m ON e.manager_id = m.employee_id WHERE e.last_name LIKE '%k%';

2.2外连接

应用场景：用于查询一个表中有，另一个表中没有的记录

1.特点

①外连接的查询结果为主表中的所有记录；如果从表中有和它匹配的，则显示匹配的值；如果从表中没有和它匹配的，则从显示null；外连接查询结果=内连接结果+主表中有而从表没有的记录

②左外连接，LEFT JOIN左边的是主表；右外连接，RIGHT JOIN右边的是主表

③左外和右外交换两个表的顺序，可以实现同样的效果

④全外连接=内连接的结果+表1中有但是表2中没有的+表2中有但是表1中没有的

①左外连接

SELECT b.name,bo.* FROM beauty b LEFT OUTER JOIN boys bo ON b.boyfriend_id = bo.id WHERE bo.id IS NULL;

SELECT d.* e.employee_id FROM departments d LEFT OUTER JOIN emplyees e ON d.department_id = e.department_id WHERE e.employee_id IS NULL;

②右外连接

SELECT b.name,bo.* FROM boys bo RIGHT OUTER JOIN beauty b ON b.boyfriend_id = bo,id WHERE bo.id IS NULL;

SELECT d.*,e.employee_id FROM employees e ROGHT OUTER JOIN departments d ON d.department_id = e.department_id WHERE e.employee_id IS NULL;

③全外连接

SELECT b.*,bo.* FROM beauty b FULL OUTER JOIN boys bo ON b.boyfriend_id = bo,id;

2.3交叉连接

SELECT b.*,bo.* FROM CROSS JOIN boys bo;

结果以笛卡尔乘积出现；