mysql打卡学习9各种连接

本是古典何须时尚 2023-02-09 14:25 55阅读 0赞

## 二，sq199语法 ##

语法
    	select 查询列表
    	from 表1 别名【连接类型】
    	join 表2 别名
    	on 连接条件
    	【where 筛选条件】
    	【group by 分组】
    	【having 筛选条件】
    	【order by 排序列表】
    分类
    内连接：inner
    外连接
    	左外连接:left【outer】
    	右外连接:right【outer】
    	全外连接:full【outer】
    交叉连接:cross
    */

### 一.内连接 ###

![在这里插入图片描述][20200519151516468.png]

/*
    语法：
    select 查询列表
    from 表1 别名
    inner join 表2 别名
    on 连接条件;
    
    分类：
    等值
    非等值
    自连接
    
    特点
    1.添加排序，分组，筛选
    2.inner可以省略
    3.筛选条件可以放在where后面，连接条件放在on后面，提高分离性
    4.inner join 连接和sql92等值连接结果一致
    */

#### 1.等值连接 ####

#(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 departments d
    INNER JOIN locations l
    ON d.`location_id`=l.`location_id`
    GROUP BY city
    HAVING COUNT(*)>3;
    
    #(4)查询哪个部门的员工个数>3的部门名和员工个数，个数降序
    SELECT department_name,COUNT(*)
    FROM departments AS d
    INNER JOIN employees AS e
    ON d.`department_id`=e.`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;
    
    #(3)自连接
    #姓名包含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%";

#### 2.外连接 ####

/*
    应用场景：用于查询一个表中有，另一个表没有的记录
    就是俩个表有交集和无交集
    特点
    1.外连接的查询结果为主表中的所有记录
    	如果从表中有和它匹配的，则显示匹配值
    	如果从表中没有和它匹配的，则显示null
    	外连接查询结果=内链接结果+主表中有而从表没有的记录
    2.左外连接，left join左边是主表
      右外连接，right join右边的是主表
    3.左外和右外交换两个表的顺序，可以实现同样的效果
    4.全外连接=内连接结果+表1中有但是表二中没有+表2中有但是表一中有，就是合集
    */

#查询男朋友不在男神表的女神名
    SELECT * FROM beauty;
    SELECT * FROM boys;

![在这里插入图片描述][20200519151443585.png]  
![在这里插入图片描述][20200519151647750.png]

#左外连接
    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.*,bo.*
    FROM  boys bo
    LEFT OUTER JOIN beauty b
    ON b.`boyfriend_id`=bo.`id`
    #分组之前
    WHERE bo.`id` IS NOT NULL;

![在这里插入图片描述][20200519151558283.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTY3MjQwNA_size_16_color_FFFFFF_t_70]

#右外连接
    SELECT b.name,bo.*
    FROM  boys bo
    RIGHT OUTER JOIN beauty b
    ON b.`boyfriend_id`=bo.`id`
    #分组之前
    WHERE bo.`id` IS NOT 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

![在这里插入图片描述][20200519151927309.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTY3MjQwNA_size_16_color_FFFFFF_t_70 1]

#交叉连接  笛卡尔乘积 4*11
    SELECT b.*,bo.*
    FROM beauty b
    CROSS JOIN boys bo;

#### 练习 ####

#1.查询编号>3的女神的男朋友的信息，如果有则列出，如果没有，用null填充
    SELECT b.id,b.name,bo.*
    FROM beauty b
    LEFT OUTER JOIN boys bo
    ON b.`boyfriend_id`=bo.`id`
    WHERE b.id>3;
    
    #2.查询哪个城市没有部门
    SELECT city,d.*
    FROM departments d
    RIGHT OUTER JOIN locations l
    ON d.`location_id`=l.`location_id`
    WHERE d.`department_id` IS NULL;
    
    #3.查询部门名为sal或者it的员工信息
    SELECT e.*,d.department_name
    FROM departments d
    LEFT JOIN employees e
    ON e.`department_id`=d.`department_id`
    WHERE d.`department_name` IN('SAL','IT');
    
    SELECT * FROM departments
    WHERE `department_name` IN('SAL','IT');

[20200519151516468.png]: https://img-blog.csdnimg.cn/20200519151516468.png
[20200519151443585.png]: https://img-blog.csdnimg.cn/20200519151443585.png
[20200519151647750.png]: https://img-blog.csdnimg.cn/20200519151647750.png
[20200519151558283.png]: https://img-blog.csdnimg.cn/20200519151558283.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTY3MjQwNA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200519151731867.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTY3MjQwNA==,size_16,color_FFFFFF,t_70
[20200519151927309.png]: https://img-blog.csdnimg.cn/20200519151927309.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTY3MjQwNA_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200519152013922.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTY3MjQwNA==,size_16,color_FFFFFF,t_70