• MySql的回顾五:多表查询下(内联/左外/右外/自连接/交叉)-1999语法


      好好吃饭,好好休息,听着很简单,实际落实缺失不那么容易。

           继续回顾MySql的多表查询之1999语法

    #二,SQL1999语法
    语法:
        SELECT 查询列表
        FROM 表1 别名 【连接类型】
        JOIN 表2 别名
        ON 链接条件
        【WHERE 筛选条件】
        【GROUP BY 分组】
        【HAVING 筛选条件】
        【ORDER BY 排序列表ASC|DESC】
    
    分类(连接类型):
        内连接(★): INNER
        外联结
            左外(★):LEFT 【OUTER】
            右外(★):RIGHT 【OUTER】
        全外:FULL 【OUTER】
        交叉连接:CROSS                

    ===============================================================================

    一、内连接
    语法:
        SELECT 查询列表
        FROM 表1 别名
        INNER JOIN 表2 别名
        ON 连接条件
        【WHERE 筛选条件】
        【GROUP BY 分组】
        【HAVING 筛选分组】
        【ORDER BY 排序列表 ASC|DESC】
    
    分类:
        等值
        非等值
        自连接
         特点:
        ①添加排序,分组,筛选
        ②INNER可以省略
        ③筛选条件放在WHERE后面,连接条件放在ON后面,提高分离性,便于阅    
           读。
        ④INNER JOIN连接和SQL1992语法中的等值连接效果是一样的,都是查 
           询交集部分。    
    #1.等值连接
    #案例1.查询员工名,部门名。交换连接条件不影响结果。
    SELECT last_name AS 员工名,department_name AS 部门名
    FROM employees e
    INNER JOIN departments d
    ON e.department_id=d.department_id;
    SELECT last_name AS 员工名,department_name AS 部门名
    FROM employees d
    INNER JOIN departments e
    ON e.department_id=d.department_id;

    #案例2.查询名字中包含e的员工和工种名(筛选)
    SELECT last_name AS 员工名,job_title AS 工种名
    FROM employees e
    INNER JOIN jobs j
    ON e.job_id=j.job_id
    WHERE e.last_name LIKE '%e%';

    #案例3.查询部门个数>3的城市名和部门个数。(分组+筛选)
    分步:先把每个城市的部门个数查出来,在筛选满足条件的。
    SELECT city AS 城市,COUNT(*) AS 个数
    FROM locations l
    INNER JOIN departments d
    ON l.location_id=d.location_id
    GROUP BY city
    HAVING COUNT(*) > 3;

    #案例4.查询哪个部门员工个数>3的部门名和员工个数,并按个数降序排序。
    分步:
    1.查询每个部门的员工个数,
    2.在上面的结果上筛选出员工数大于3的记录,
    3.把员工数排序
    SELECT COUNT(*) AS 员工个数,d.department_name AS 部门名
    FROM employees e
    INNER JOIN departments d
    ON e.department_id=d.department_id
    GROUP BY d.department_name
    HAVING COUNT(*) > 3
    ORDER BY 员工个数 DESC;

    #案例5.查询员工名,部门名,工种名,并按部门名降序。三表连接注意条件。
    SELECT last_name AS 员工名,department_name AS 部门名,
    job_title AS 工种名
    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 d.department_name DESC;

    #2.非等值连接。范围(间接)
    #案例1.查询员工的工资级别。
    SELECT salary AS 月薪,grade_level AS 工资等级
    FROM employees e
    INNER JOIN job_grades g
    ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;

    #案例2.查询每个工资的级别的个数>20,并且按降序排列。
    SELECT j.grade_level AS 等级,COUNT(*) 个数
    FROM employees e
    INNER JOIN job_grades j
    ON e.salary BETWEEN j.lowest_sal AND j.highest_sal
    GROUP BY j.grade_level
    HAVING COUNT(*) > 20
    ORDER BY 个数 DESC;

    #3.自连接
    #案例1.查询员工的名字,上级的名字。
    SELECT e.last_name AS 员工名,m.last_name AS 上级名
    FROM employees e
    INNER JOIN employees m
    ON e.manager_id=m.employee_id;

    #案例2.查询员工的名字,上级的名字,名字中包含字符k。
    SELECT e.last_name AS 员工名,m.last_name AS 上级名
    FROM employees e
    INNER JOIN employees m
    ON e.manager_id=m.employee_id
    WHERE e.last_name LIKE '%k%';

    #二,外连接
    
    应用场景:用于查询一个表中有,另一个表没有的记录。
    特点:
    1.外连接的查询结果为主表中的所有记录
    如果从表中有和它匹配的,则显示匹配的值
    如果从表中没有和它匹配的,则显示null
    外连接查询结果=内连接结果+主表中有而从表没有的记录
    2.左外连接,LEFT JOIN左边的是主表
    右外连接,RIGHT JOIN右边的是主表
    3.左外和右外交换两个表的顺序,可以实现同样的效果。
    4.全外连接=内连接的结果+表1中有但表2中没有的+表2中有但表一没有的(Mysql不支持)
    引入:
    SELECT * FROM beauty;
    SELECT * FROM boys;

    #引入:查询男朋友不在男神表的女神名
    SELECT b.NAME,bo.*
    FROM beauty b
    LEFT OUTER JOIN boys bo
    ON b.boyfriend_id=bo.id;

    #左外连接
    SELECT b.NAME,bo.*
    FROM beauty b
    LEFT OUTER JOIN boys bo
    ON b.boyfriend_id=bo.id
    WHERE bo.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;

    #把beauty表中的id为10行的boyfriend_id从4改到6.才能查询出如下结果。
    SELECT b.*,bo.*
    FROM boys bo
    LEFT OUTER JOIN beauty b
    ON b.boyfriend_id=bo.id
    WHERE b.id IS NULL;

    #案例1.哪个部门没有员工。
    #左外写法
    SELECT d.*,e.employee_id
    FROM departments d
    LEFT OUTER JOIN employees e
    ON d.department_id=e.department_id
    WHERE e.department_id IS NULL;
    ==============================
    #右外写法
    SELECT d.*,e.employee_id
    FROM employees e
    RIGHT OUTER JOIN departments d
    ON d.department_id=e.department_id
    WHERE e.employee_id IS NULL;

    #全外连接查出的结果有三部分组成(举例,Mysql不支持)语法结构演示
      先把两张表的交集查出来,在把缺失的填充查出来,在把其他无关联的查出来
      语法结构示例
    SELECT b.*,bo.*
    FROM beauty b
    FULL OUTER JOIN boys bo
    ON b.boyfriend_id=bo.id
    #交叉连接,使用1999的语法标准实现的
    笛卡尔乘积
    SELECT b.*,bo.*
    FROM beauty b
    CROSS JOIN boys bo;

    #SQL1992语法 与 SQL 1999语法 相比
    功能:SQL1999支持的较多
    可读性:SQL1999实现连接条件和筛选条件的分离,可读性提高

    总结:

    SELECT <select_list>
    FROM A
    INNER JOIN B
    ON A.KEY=B.KEY;

    SELECT <select_list>
    FROM A
    LEFT JOIN B
    ON A.KEY=B.KEY;

    SELECT <select_list>
    FROM A
    RIGHT JOIN B
    ON A.KEY=B.KEY;

    SELECT <select_list>
    FROM A
    LEFT JOIN B
    ON A.KEY=B.KEY
    WHERE B.KEY IS NULL;

    SELECT <select_list>
    FROM A
    RIGHT JOIN B
    ON A.KEY=B.KEY
    WHERE A.KEY IS NULL;

    SELECT <select_list>
    FROM A
    FULL JOIN B
    ON A.KEY=B.KEY;

    SELECT <select_list>
    FROM A
    FULL JOIN B
    ON A.KEY=B.KEY
    WHERE A.KEY IS NULL
    OR B.KEY IS NULL;

       简单的多表查询已结束,相信看到这里的基本对简单多表连接应该手到擒来,至于看不懂的emmm...

    用我们杨老师的话就是,自己去写个二三十遍就有感觉了。o(^▽^)o。

             高考结束,大学生活的开始,世界是属于你们的倒计时开启了...不管你们选择什么专业,只要是你自己选择的,一定要相信自己会在这个专业留下一笔....。

        

  • 相关阅读:
    正则表达式入门
    HtmlUnit 开发网络爬虫
    IntelliJ IDEA 下的svn配置及使用的非常详细的图文总结
    myeclipse通过数据表生成jpa或hibernate实体---https://blog.csdn.net/partner4java/article/details/8560289
    javaWeb学习之 Filter过滤器----https://www.cnblogs.com/xdp-gacl/p/3948353.html
    @Resource与@Autowired注解的区别
    IntelliJ IDEA 下的svn配置及使用的非常详细的图文总结
    IntelliJ IDEA配置本地Tomcat方法---亲测有效
    使用IDEA部署Myeclipse项目----亲测有效
    爱钱帮对付相关信息
  • 原文地址:https://www.cnblogs.com/jxearlier/p/13376452.html
Copyright © 2020-2023  润新知