MySQL复习笔记（七）：SQL92/SQL99、内连接、外连接、交叉连接 |
您所在的位置：网站首页 › mysql外连接、内连接与自连接的区别 › MySQL复习笔记（七）：SQL92/SQL99、内连接、外连接、交叉连接 |
MySQL复习笔记（七）：SQL92/SQL99、内连接、外连接、交叉连接
|
连接查询
含义：又称多表查询，当查询的字段来自多个表时，就需要用到连接查询 笛卡尔乘积现象：表1有m行，表2有n行，则结果有m*n行 分类： sql92标准：只支持内连接 sql99标准【推荐】：MySQL支持内、外、交叉连接，但不支持全外连接 内连接： 等值连接非等值连接自连接外连接： 左外连接右外连接全外连接交叉连接 一、SQL92标准（内连接） 等值连接 可以为表起别名，as可以省略为表起别名之后，则查询的字段就不能用原来的表名进行限定FROM中表的顺序可以调换可以加筛选、分组、排序多表等值连接的结果为多表的交集部分n个表进行连接需要n-1个连接条件1、 查询员工名和对应的部门名，且要求部门名中包含字母a SELECT e.last_name, department_name FROM employees as e, departments d WHERE e.department_id = d.department_id AND department_name LIKE '%a%';2、查询每个城市的部门个数 SELECT count(*), city FROM departments d, locations l WHERE d.location_id = l.location_id GROUP BY l.location_id;3、查询每个工种的工种名和员工个数，并且按照工种个数进行排序 SELECT job_title, count(*) '个数' FROM employees e, jobs j WHERE e.job_id=j.job_id GROUP BY j.job_id ORDER BY count(*) 非等值连接1、查询员工的工资和工资等级 SELECT last_name, salary, grade_level FROM employees e, job_grades j WHERE e.salary BETWEEN j.lowest_sal and j.highest_sal; 自连接1、查询员工名和他上级的名字 SELECT a.last_name, b.last_name FROM employees a, employees b WHERE a.manager_id = b.employee_id; 练习 #查询所有员工的姓名、部门号和部门名 SELECT last_name, e.department_id, department_name FROM employees e, departments d WHERE e.department_id = d.department_id; #查询90号部门员工的job_id和90号部门的location_id SELECT job_id, location_id FROM employees e, departments d WHERE e.department_id = d.department_id AND e.department_id = 90; #查询所有有奖金员工的last_name, department_name,location_id, city SELECT last_name, department_name,l.location_id, city FROM employees e, departments d, locations l WHERE e.department_id = d.department_id AND d.location_id = l.location_id AND e.commission_pct is not null; #查询每个工种、每个部门的部门名、工种名和最低工资 SELECT department_name, job_title, min(salary) FROM departments d, employees e, jobs j; WHERE d.department_id = e.department_id AND e.job_id = j.job_id GROUP BY department_name,job_title; #查询每个国家下面部门数大于2的国家编号 SELECT country_id, count(*) FROM departments d, locations l WHERE d.location_id = l.location_id GROUP BY country_id HAVING count(*) > 2; 二、SQL99标准语法: SELECT 查询表达式 FROM 表1 别名 【连接类型】 JOIN 表2 别名 ON 连接条件 【where 筛选条件】 【GROUP BY 分组条件】 【HAVING 筛选条件】 【ORDER BY 列名】分类 内连接：inner外连接 左外连接：left【outer】右外连接：right【outer】全外：full【outer】 交叉连接：cross （一）内连接核心语法： SELECT 查询表达式 FROM 表1 别名 INNER JOIN 表2 别名 ON 连接条件分类：等值连接、非等值连接、自连接 等值连接特点 可以添加排序、分组、筛选inner可以省略筛选条件放在where后面，连接条件放在on后面，提高分离性，利于阅读inner join连接和sql92中的等值连接效果一样，都是查询交集1、查询员工名、部门名称 SELECT last_name, department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id;2、查询名字中包含e的员工名和工种名（添加筛选） SELECT last_name, job_title FROM employees e INNER JOIN jobs j ON e.job_id = j.job_id WHERE last_name LIKE '%e%';3、查询部门个数大于3的城市名和部门个数（分组+筛选） SELECT city, count(*) 个数 FROM departments d INNER JOIN locations l ON d.location_id = l.location_id GROUP BY city HAVING count(*) > 34、查询部门员工个数大于3的部门名和员工个数，并按个数降序排列（添加排列） SELECT department_name, count(*) 员工个数 FROM employees e Inner JOIN departments d ON e.department_id = d.department_id GROUP BY e.department_id HAVING count(*) > 3 ORDER BY count(*) DESC;5、查询员工名、部门名、工种名，并按部门名排序(三表查询) 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; 非等值连接1、查询员工工资级别 SELECT last_name, grade_level FROM employees e JOIN job_grades j ON e.salary BETWEEN j.lowest_sal AND j.highest_sal;2、查询每个工资级别的人数，并降序排序 SELECT grade_level, count(*) FROM employees e JOIN job_grades j ON e.salary BETWEEN j.lowest_sal AND j.highest_sal GROUP BY grade_level ORDER BY count(*) DESC; 自连接1、查询员工名和其上级的名字 SELECT e.last_name, m.last_name FROM employees e JOIN employees m ON e.manager_id = m.employee_id （二）外连接应用场景：用于查询一个表中有，另一表中没有。 特点： 外连接的查询结果为主表中的所有记录，如果从表中有和它匹配的则显示从表的值，如果没有则显示null。外连接结果=内连接结果+主表中有而从表中没有的左外连接：left join左边的是主表。右外连接：right join右边的是主表。左外连接和右外连接交换两个表的顺序，可以实现同样的效果全外连接（MySQL不支持）：内连接结果+表1中有但表2中没有的+表2中有但表1中没有的1、查询男朋友不在男神表中的女神名 #左外连接 SELECT g.name FROM beauty g LEFT JOIN boys b ON g.boyfriend_id = b.id WHERE b.id IS NULL; #右外连接 SELECT g.name FROM boys b RIGHT JOIN beauty g ON b.id = g.boyfriend_id WHERE b.id IS NULL;2、查询哪个部门没有员工 #左外连接 SELECT department_name FROM departments d LEFT OUTER JOIN employees e ON d.department_id = e.department_id WHERE e.employee_id IS NULL; #右外连接 SELECT department_name FROM employees e RIGHT OUTER JOIN departments d ON e.department_id = d.department_id WHERE e.employee_id IS NULL; （三）交叉连接 结果为笛卡尔乘积：表1有4行，表2有11行，则结果有4*11=44行。和sql92标准中不加连接条件结果一样 SELECT b.*,g.* FROM boys b CROSS JOIN beauty g; 总结SQL99 VS SQL92: SQL99支持的功能更多SQL99实现了查询条件和连接条件的分离，可读性较高
|
【本文地址】
今日新闻 |
推荐新闻 |