索取营销诊断书
021-80394720QQ
您的位置：上海毫米网络优化公司 > 小程序 > 百度小程序 >
相关推荐recommended
【MySQL】多表查询(JOINUNION)
作者：mmseoamin日期：2023-12-20

文章目录

    • 多表查询
      • 1. 笛卡尔积错误
      • 2. 等值连接 WHERE
      • 3. 非等值连接
      • 4. 自连接
      • 5. 内连接 INNER JOIN
      • 6. 外连接 LEFT JOIN
      • 7. 合并查询 UNION
      • 8. 注意
      • 9. 七种JOIN的实现
      • 10.练习

        多表查询

        • 多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。
        • 前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。

          1. 笛卡尔积错误

          • X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，组合的个数即为两个集合中元素个数的乘积数。
          • 笛卡尔积也称为交叉连接 CROSS JOIN 。作用就是可以把任意表进行连接，即使这两张表不相关。
          • 直接进行多表查询，不添加连接条件会出现笛卡尔积错误： 
            #查询员工姓名和所在部门名称
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;
# 结果会将last_name所有值和department_name所有值做排列组合
            • 在 WHERE子句中写入连接条件，可以避免出现笛卡尔积情况。 
              SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2; #连接条件

              2. 等值连接 WHERE

              • 多个表中有相同列时，必须在列名之前加上表名前缀。
              • 使用别名可以简化查询，AS可省略。
              • 列名前使用表名前缀可以提高查询效率。
              • 如果使用了表的别名，在查询字段中、过滤条件中就只能使用别名，不能使用原有的表名，否则就会报错。

                - 阿里开发规范：【强制】对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名（或表名）进行限定。否则后续多表新增同名字段时，导致ambiguous模糊异常。

              • 等值连接 n个表,至少需要n-1个连接条件。 
                # 表名前缀
SELECT employees.employee_id, employees.last_name, employees.department_id, departments.location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;
# 别名
SELECT e.last_name, d.department_id, e.department_id
FROM employees e, departments AS d 
WHERE e.department_id = d.department_id;
# 连接多个表,连接 n个表,至少需要n-1个连接条件。
SELECT e.employee_id, e.last_name, e.department_id, d.department_name, d.location_id, l.location_id, l.city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id AND d.location_id = l.location_id
ORDER BY e.employee_id, l.location_id;

                3. 非等值连接

                # 非等值连接
SELECT e.last_name, e.salary, j.grade_level
FROM employees AS e, job_grades AS j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal
ORDER BY grade_level;

                4. 自连接

                • 当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行连接查询。 
                  # CONCAT() 可实现字段和字符串拼接
SELECT CONCAT (worker.last_name,' works for ', manager.last_name)
FROM employees worker, employees manager 
WHERE worker.manager_id = manager.manager_id;

                  【MySQL】多表查询(JOINUNION),在这里插入图片描述,第1张

                  5. 内连接 INNER JOIN

                  • 内连接: 两表连接查询时，连接结果仅包含符合连接条件的行，参与连接的两个表都应该符合连接条件。不包含不匹配的行。
                  • 内连接通过 INNER JOIN 表名 ON 条件 实现，INNER 可省略。
                  • 语法： 
                    # 内连接(INNER JOIN)的实现
SELECT 字段列表
FROM A表 
JOIN B表
ON 关联条件
WHERE 等其他子句;

                    # 例一
SELECT e.employee_id, e.last_name, e.department_id,d.department_id, d.location_id
FROM employees e 
JOIN departments d
ON e.department_id = d.department_id;
# 效果相同：
SELECT e.employee_id, e.last_name, e.department_id,d.department_id, d.location_id
FROM employees e, departments d 
WHERE e.department_id = d.department_id;
# 例二
SELECT employee_id, city, department_name
FROM employees e 
JOIN departments d 
ON d.department_id = e.department_id
JOIN locations l 
ON d.location_id = l.location_id;
# 效果相同
SELECT employee_id, city, department_name
FROM employees e, departments d, locations l 
WHERE e.department_id = d.department_id 
AND d.location_id = l.location_id;

                    6. 外连接 LEFT JOIN

                    • 外连接：两表连接查询时，连接结果不仅包含符合连接条件的行，同时也包含自身不符合条件的行。没有匹配的行时, 结果表中相应的列为空(NULL)。
                    • 外连接分为左外连接、右外连接，通过 LEFT / RIGHT OUTER JOIN 表名 ON 条件 实现，OUTER 可省略。
                    • 如果是左外连接，则连接条件中左边的表也称为主表，右边的表称为从表。
                    • 如果是右外连接，则连接条件中右边的表也称为主表，左边的表称为从表。
                    • 结果包括主表的所有行，从表不满足条件的行内容为NULL。
                    • MySQL不支持满外连接，可以通过UNION实现。 
                      #左外连接
SELECT 字段列表
FROM A表 
LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;
#右外连接
SELECT 字段列表
FROM A表 
RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;

                      #左外连接
SELECT e.last_name, e.department_id, d.department_id, d.department_name
FROM employees e 
LEFT JOIN departments d 
ON e.department_id = d.department_id;

                      结果包括employees表的全部行，employees表中的Grant没有department_id，departments表中无对应数值，仍显示在结果中（NULL）。

                      【MySQL】多表查询(JOINUNION),在这里插入图片描述,第2张

                      # 右外连接
SELECT e.last_name, e.department_id, d.department_id, d.department_name
FROM employees e 
RIGHT JOIN departments d 
ON e.department_id = d.department_id;

                      结果包括departments表的全部行，employees表中无对应department_id，仍显示在结果中（NULL）。

                      【MySQL】多表查询(JOINUNION),在这里插入图片描述,第3张

                      7. 合并查询 UNION

                      • 利用UNION关键字，可以同时查询多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。
                      • 各个SELECT语句之间使用 UNION 或 UNION ALL 关键字分隔。
                      • UNION ：返回查询的结果集不包括重复记录。
                      • UNION ALL：返回查询的结果集包括重复记录。
                      • 执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。 
                        SELECT column,... FROM table1
UNION
SELECT column,... FROM table2
SELECT column,... FROM table1
UNION ALL
SELECT column,... FROM table2
                        • 查询部门编号>90或邮箱包含a的员工信息 
                          SELECT * FROM employees
WHERE email LIKE '%a%' OR department_id > 90;
SELECT * FROM employees
WHERE email LIKE '%a%'
UNION 
SELECT * FROM employees
WHERE department_id > 90;
                          • 查询中国用户中男性的信息以及美国用户中年男性的用户信息 
                            SELECT id,cname FROM t_chinamale WHERE csex='男'
UNION ALL
SELECT id,tname FROM t_usmale WHERE tGender='male';

                            8. 注意

                            • 多表连接就相当于嵌套 for 循环一样，非常消耗资源，会让 SQL 查询性能下降得很严重，因此要控制连接表的数量，不连接不必要的表。
                            • 在许多 DBMS 中，也都会有最大连接表的限制。
                            • 阿里【强制】：超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时， 保证被关联的字段需要有索引。即使双表 join 也要注意表索引、SQL 性能。

                              9. 七种JOIN的实现

                              1、

                              【MySQL】多表查询(JOINUNION),在这里插入图片描述,第4张

                              # A ∩ B
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
JOIN departments d 
ON e.department_id = d.department_id;

                              2、

                              【MySQL】多表查询(JOINUNION),在这里插入图片描述,第5张

                              SELECT e.last_name, e.departments_id, d.department_name
FROM employees e 
LEFT JOIN departments d 
ON e.department_id = d.department_id;

                              3、

                              【MySQL】多表查询(JOINUNION),在这里插入图片描述,第6张

                              SELECT e.last_name, e.departments_id, d.department_name
FROM employees e 
RIGHT JOIN departments d 
ON e.department_id = d.department_id;

                              4、

                              【MySQL】多表查询(JOINUNION),在这里插入图片描述,第7张

                              # A - A∩B
SELECT e.last_name, e.department_id, d.department_name
FROM employees e 
LEFT JOIN departments d 
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

                              5、

                              【MySQL】多表查询(JOINUNION),在这里插入图片描述,第8张

                              # B-A∩B
SELECT e.last_name, e.department_id, d.department_name
FROM employees e 
RIGHT JOIN departments d 
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

                              6、

                              【MySQL】多表查询(JOINUNION),在这里插入图片描述,第9张

                              # A∪B 
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT JOIN departments d 
ON e.department_id = d.department_id
UNION 
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT JOIN departments d 
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

                              7、

                              【MySQL】多表查询(JOINUNION),在这里插入图片描述,第10张

                              SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT JOIN departments d 
ON e.department_id = d.department_id
WHERE d.department_id IS NULL
UNION
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT JOIN departments d 
ON e.department_id = d.department_id
WHERE e.department_id IS NULL

                              10.练习

                              # 1.显示所有员工的姓名，部门号和部门名称。
-- 1.1等值连接WHERE，只显示满足连接条件的结果
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;
-- 1.2内连接join on，只显示满足连接条件的结果，1/2方法等效
SELECT last_name, e.department_id, d.department_name
FROM employees e 
JOIN departments d 
ON e.department_id = d.department_id;
-- 1.3外连接left join，显示主表左表的全部结果
-- 因为存在无主管的员工（deptid和deptname填null），结果比1/2更充分
SELECT last_name, e.department_id, d.department_name
FROM employees e 
LEFT JOIN departments d 
ON e.department_id = d.department_id;
# 2.查询90号部门员工的job_id和90号部门的location_id
SELECT e.department_id, e.job_id, d.location_id
FROM employees e, departments d
WHERE e.department_id = 90 
AND e.department_id = d.department_id;
-- 先通过join on 连接两表，再添加筛选条件
SELECT e.department_id, e.job_id, d.location_id
FROM employees e 
JOIN departments d 
ON e.department_id = d.department_id
WHERE e.department_id = 90;
# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city
SELECT last_name, d.department_name, d.location_id, city 
FROM employees e, departments d, locations l 
WHERE e.commission_pct IS NOT NULL 
AND e.department_id = d.department_id
AND d.location_id = l.location_id; 
SELECT last_name, d.department_name, d.location_id, city 
FROM employees e 
JOIN departments d 
ON e.department_id = d.department_id
JOIN locations l 
ON d.location_id = l.location_id
WHERE commission_pct IS NOT NULL;
# 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name
SELECT e.last_name, e.job_id, e.department_id, d.department_name
FROM employees e, departments d, locations l 
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND l.city = 'Toronto';
SELECT e.last_name, e.job_id, e.department_id, d.department_name
FROM employees e 
JOIN departments d 
ON e.department_id = d.department_id
JOIN locations l 
ON d.location_id = l.location_id
WHERE l.city = 'Toronto';
#5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’
SELECT d.department_name, l.street_address, e.last_name, e.job_id, e.salary
FROM employees e, departments d, locations l 
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND d.department_name = 'Executive';
SELECT d.department_name, l.street_address, e.last_name, e.job_id, e.salary
FROM employees e 
JOIN departments d 
ON e.department_id = d.department_id
JOIN locations l 
ON d.location_id = l.location_id
WHERE d.department_name = 'Executive';
#6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式employees Emp# manager Mgr#kochhar 101 king 100
SELECT e1.last_name, e1.employee_id, e1.manager_id, e2.last_name manager_name 
FROM employees e1, employees e2 
WHERE e1.manager_id = e2.employee_id;
SELECT e1.last_name, e1.employee_id, e1.manager_id, e2.last_name manager_name 
FROM employees e1 
JOIN employees e2 
ON e1.manager_id = e2.employee_id;
-- left join on 会包含employee没有manager的结果
SELECT emp.last_name employees, emp.employee_id "Emp#", mgr.last_name manager,
mgr.employee_id "Mgr#"
FROM employees emp
LEFT OUTER JOIN employees mgr
ON emp.manager_id = mgr.employee_id;
# 7.查询哪些部门没有员工
SELECT d.department_id, d.department_name
FROM departments d 
LEFT JOIN employees e 
ON d.department_id = e.department_id
WHERE e.department_id IS NULL;
SELECT d.department_id, d.department_name
FROM departments d 
WHERE NOT EXISTS (
					SELECT * FROM employees e
					WHERE e.department_id = d.department_id);
# 8. 查询哪个城市没有部门
SELECT l.location_id, l.city 
FROM locations l
LEFT JOIN departments d 
ON l.location_id = d.location_id
WHERE d.location_id IS NULL;
# 9. 查询部门名为 Sales 或 IT 的员工信息
SELECT e.last_name, e.employee_id, e.department_id, d.department_name
FROM employees e, departments d 
WHERE e.department_id = d.department_id
AND d.department_name IN ('Sales', 'IT');

上一篇：如何应用Nginx Rewrit实现网页跳转

下一篇：MAMBA介绍：一种新的可能超过Transformer的AI架构

网站优化运营
整站seo优化
网络营销运营
成功案例
品牌创意网站
营销型网站
小程序
微信小程序
微信公众号
百度熊掌号
网站优化分享
关于我们
公司概况
公司新闻
公司文化
结构体系
企业资质
加入我们
关注我们
扫描二维码
获取更多信息
精彩一手掌握
Copyright © 2014-2019 seo,上海网站优化_网络制作_网站seo排名_网站建设推广_上海网络公司 版权所有 沪ICP备19022315号