2. 科技
    3. MySQL连接查询

    MySQL连接查询

    科技2024-10-30  15

    配套资料，以下所有的查询都是建立在此基础之上 https://pan.baidu.com/s/1-Q-f9L4SGkYNtiprJR40Jw 提取码: qju4

    连接查询按照功能分类： 1.内连接

    等值连接非等值连接自连接

    2.外连接

    左外连接右外连接全外连接

    3.交叉连接

    sql92标准

    内连接的应用场景：应用于一个表中有，另外一个表中的对应字段也有的情况，相当于是查询两个表中交集的部分。

    等值连接 案例1：查询女神名和对应的男神名

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

    案例2：查询员工名和对应的部门名

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

    添加筛选条件 案例2：查询城市名中第二个字符为o的部门名和城市名

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

    添加分组 案例3：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

    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

    添加排序 案例4：查询每个工种的工种名和员工的个数，并且按照员工个数降序

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

    三表连接 案例5：查询以s开头的员工名，部门名和所在城市，并且按部门名降序

    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 AND last_name LIKE 's%' ORDER BY department_name DESC;

    注意：如果为表起了别名，查询的字段就不能用原始的表名了，否则会报错

    非等值连接

    案例1：查询员工的工资和工资级别

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

    自连接 自己和自己链接，一个表查询了两次。

    案例：查询员工名和上级的名称

    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；

    sql99标准

    语法： select 查询列表 from 表1 别名 [连接类型] join 表2 别名 on 连接条件 [where筛选条件] [group by分组] [having 筛选条件] [order by 排序列表]

    连接类型分为：[inner]（内连接）、left[outer]（左外连接）、right[outer] （右外连接）、full[outer]（全外连接）、cross（交叉连接）。

    等值连接

    案例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 e.last_name LIKE '%e%';

    添加分组和筛选 案例3：查询部门个数>3的城市名和部门个数

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

    添加排序 案例4：查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序

    SELECT department_name,COUNT(*) 员工个数 FROM departments d INNER JOIN employees e ON d.department_id=e.department_id GROUP BY d.department_name HAVING 员工个数>3 ORDER BY 员工个数 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 DESC;

    非等值连接

    案例1：查询员工的工资级别

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

    添加分组，排序 案例2：查询每个工资级别的个数>20的个数，并且按工资级别降序

    SELECT grade_level,COUNT(*) FROM employees e INNER JOIN job_grades g ON e.salary BETWEEN g.lowest_sal AND g.highest_sal GROUP BY g.grade_level HAVING COUNT(*)>16 ORDER BY g.grade_level DESC;

    案例2：查询姓名中包含字符k的员工名字和他上级的名字

    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%';

    外连接

    应用场景：用于查询一个表中有，而另外一个表中对应的字段没有的情况。

    特点：

    外连接的查询结果为主表中的所有记录如果从表中有和他匹配的，就显示匹配的值如果从表中没有和他匹配的，就显示null外连接查询结果=内连接结果+主表中有而从表中没有的记录左外连接，left join左边的是主表右外连接，right join右边的是主表左外连接和右外连接交换两个表的顺序，可以实现同样的效果

    案例1：查询男朋友不在男神表中的女神名 左外连接

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

    右外连接

    SELECT b.name,bo.* FROM boys bo RIGHT JOIN beauty b ON b.boyfriend_id=bo.id

    案例2：哪个部门没有员工 左外连接

    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 d.*,e.employee_id FROM employees e RIGHT JOIN departments d ON d.department_id=e.department_id WHERE e.employee_id IS NULL;

    全外连接MySQL不支持，全外连接相当于是两个表的并集 案例：

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

    交叉连接 交叉连接就是sql92标准的笛卡尔积

    SELECT b.*,bo.* FROM boys bo CROSS JOIN beauty b；

    推荐使用sql99语法

    常见连接图示总结

    案例1：查询编号>3的女神的男朋友信息，如果有则详细列出，如果没有，则用null填充

    SELECT b.id,b.name,bo.* FROM beauty b LEFT JOIN boys bo ON b.boyfriend_id=bo.id WHERE b.id>3;

    案例2：查询哪个城市没有部门

    SELECT city,department_id FROM locations l LEFT JOIN departments d ON l.location_id=d.location_id WHERE d.department_id IS NULL;

    案例3：查询部门名为SAL和IT的员工信息

    SELECT d.department_id,d.department_name,e.* FROM departments d LEFT JOIN employees e ON e.department_id=d.department_id WHERE d.department_name IN ('SAL','IT');
    Processed: 0.033, SQL: 8