• MySQL_Sql_打怪升级_进阶篇_进阶6:连接查询


    进阶6:连接查询

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

    笛卡尔乘积现象:

    表1 有m行,表2有n行,结果=m*n行
    
    发生原因:没有有效的连接条件
    如何避免:添加有效的连接条件
    

    分类:

    按年代分类:

    sql92标准:仅仅支持内连接
    sql99标准【推荐】:支持内连接+外连接(左外和右外)+交叉连接
    

    按功能分类:

    	内连接:
    		等值连接
    		非等值连接
    		自连接
    	外连接:
    		左外连接
    		右外连接
    		全外连接
    	
    	交叉连接
    

    引入:笛卡尔乘积错误情况:

    SELECT COUNT(*) FROM beauty;    	    #12行数据
    SELECT COUNT(*) FROM boys;		        #4行数据
    SELECT NAME,boyName FROM boys,beauty	#此时生成48行数据
    
    
    SELECT * FROM beauty;
    SELECT * FROM boys;
    
    SELECT NAME,boyName FROM boys,beauty
    WHERE beauty.boyfriend_id= boys.id;
    

    一、SQL92标准

    一)内连接

    1、等值连接

    ① 多表等值连接的结果为多表的交集部分
    ② n表连接,至少需要n-1个连接条件
    ③ 多表的顺序没有要求
    ④ 一般需要为表起别名
    ⑤ 可以搭配前面介绍的所有子句使用,比如排序、分组、筛选
    

    案例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、为表起别名

    ①提高语句的简洁度
    ②区分多个重名的字段
    
    注意:如果为表起了别名,则查询的字段就不能使用原来的表名去限定
    

    案例:查询员工名、工种号、工种名

    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、可以加筛选

    案例1:查询有奖金的员工名、部门名

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

    案例2:查询城市名中第二个字符为o的部门名和城市名

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

    5、可以加分组

    案例1:查询每个城市的部门个数

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

    案例2:查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

    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.`location_id`
    AND city LIKE 's%'
    ORDER BY department_name DESC;
    

    二)非等值连接

    案例1:查询员工的工资和工资级别,显示等级为A的级别

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

    所需工资级别表

    CREATE TABLE job_grades
    (grade_level VARCHAR(3),
     lowest_sal  int,
     highest_sal int);
    
    INSERT INTO job_grades
    VALUES ('A', 1000, 2999);
    
    INSERT INTO job_grades
    VALUES ('B', 3000, 5999);
    
    INSERT INTO job_grades
    VALUES('C', 6000, 9999);
    
    INSERT INTO job_grades
    VALUES('D', 10000, 14999);
    
    INSERT INTO job_grades
    VALUES('E', 15000, 24999);
    
    INSERT INTO job_grades
    VALUES('F', 25000, 40000);
    

    查看表内容

    select salary,employee_id from employees;
    select * from job_grades;
    

    三)自连接

    作用:

    ​ 可以理解为是等值连接(涉及到多张表),而自连接是在一张表内根据字段名进行多次的查询。

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

    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
    

    一)内连接

    语法:

    select 查询列表
    from 表1 别名
    inner join 表2 别名
    on 连接条件;
    

    分类:

    等值
    非等值
    自连接
    

    特点:

    ① 可以添加排序、分组、筛选
    ② inner可以省略
    ③ 筛选条件放在where后面,连接条件放在on后面,提高分离性,便于阅读
    ④ inner join连接和sql92语法中的等值连接效果是一样的,都是查询多表的交集
    

    1、等值连接

    案例1.查询员工名、部门名

    SELECT last_name,department_name
    FROM departments d
    JOIN employees  e
    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 departments d
    INNER JOIN locations l
    ON d.`location_id`=l.`location_id`
    GROUP BY city
    
    HAVING COUNT(*)>3;
    

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

    ①查询每个部门的员工个数

    SELECT COUNT(*),department_name
    FROM employees e
    INNER JOIN departments d
    ON e.`department_id`=d.`department_id`
    GROUP BY department_name
    

    ② 在①结果上筛选员工个数>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;
    

    案例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;
    

    2、非等值连接

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

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

    案例2.查询工资级别的个数>20的个数,并且按工资级别降序

    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;
    

    3、自连接

    案例1.查询员工的名字、上级的名字

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

    案例2.查询姓名中包含字符k的员工的名字、上级的名字

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

    二)外连接

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

    特点:

     1、外连接的查询结果为主表中的所有记录
    	如果从表中有和它匹配的,则显示匹配的值
    	如果从表中没有和它匹配的,则显示null
    	外连接查询结果=内连接结果+主表中有而从表没有的记录
     2、左外连接,left join左边的是主表,查询左表有的和右表交集的部分
        右外连接,right join右边的是主表,查询右表有的和左表交集的部分
     3、左外和右外交换两个表的顺序,可以实现同样的效果 
     4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
    

    引入:查询男朋友 不在男神表的的女神名

     SELECT * FROM beauty;
     SELECT * FROM boys;
    

    左外连接

    SELECT b.name,bo.*
    FROM beauty b
    LEFT OUTER JOIN boys bo
    ON b.`boyfriend_id` = bo.`id`
    WHERE bo.`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.`employee_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;
    

    全外

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

    三)交叉连接

    使用的SQL99标准实现的笛卡尔乘积

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

    三、SQL92和SQL99

    SQL92:
    	1. 仅支持内连接(等值连接,非等值连接,自连接)
    	2. 语法简洁,可读性略显不足
    	3. 语法上使用,FROM 接入表 , WHERE连接表之间的关系,AND追加条件
    
    SQL99:
    	1. 支持内连接+外连接(左外和右外)+交叉连接
    	2. 语法复杂,可读性较强
    	3.语法上使用, JOIN接入表,ON连接表之间的关系,
    
    两种语法均都是经典,执行的原理也是一样的,但是还是推荐使用 SQL99标准。
    
  • 相关阅读:
    2019.6.30 Spring注解 bean后置处理器和属性赋值
    2019.6.29 Spring注解-Bean的注册
    2019.6.28 Spring注解
    boost基础环境搭建
    动态规划入门一:钢条切割问题
    《剑指offer》读书笔记
    字符串的全排列
    西山居递归面试题
    常见的数据结构
    832. Flipping an Image
  • 原文地址:https://www.cnblogs.com/liych/p/13615588.html
Copyright © 2020-2023  润新知