【DataBase】MySQL 15 连接查询其二 SQL99标准 内连接

视频参考自：P78 - P81

https://www.bilibili.com/video/BV1xW411u7ax

 

连接查询 SQL99标准

*
SQL 99 标准
-----------------------------------------------
SELECT 
    查询列表
FROM 
    表N 别名 [连接类型] join 表N 别名
ON 
    连接条件
WHERE
    筛选条件
GROUP BY
    分组条件
HAVING
    筛选条件
ORDER BY
    排序条件
-------------------------------------
内连接： 
    INNER
外连接： 
    LEFT [OUTER] & RIGHT [OUTER] & FULL [OUTER]
交叉连接：
    CROSS
*/
    

 

内连接的种类又分为

-- 内连接
/*
SELECT 查询列表
FROM 表1 INNER JOIN 表2
ON 连接条件 

1、等值连接
2、非等值连接
3、自连接
*/

 

等值连接

-- 等值连接

-- 案例 查询员工名、部门名
SELECT
    E.`last_name`,D.`department_name`
FROM
    `employees` AS E INNER JOIN `departments` AS D
ON
    E.`department_id` = D.`department_id`;



-- 查询名字中包含a的员工名和工种名
SELECT
    E.`last_name`,J.`job_title`
FROM
    `employees` AS E
    INNER JOIN 
    `jobs` AS J
ON 
    E.`job_id` = J.`job_id`
WHERE
    E.`last_name` LIKE '%a%';
    
    
    
-- 查询部门个数大于3的城市名和部门个数
SELECT 
    L.`city`,COUNT(*) '部门个数'
FROM
    `departments` AS D
    INNER JOIN
    `locations` AS L
ON
    D.`location_id` = L.`location_id`
GROUP BY 
    L.`city`
HAVING 
    部门个数 > 3;
    
    
    
-- 查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序(添加排序)
SELECT
    D.`department_name`,COUNT(*) AS '员工个数'
FROM
    `departments` AS D
    INNER JOIN
    `employees` AS E
ON 
    D.`department_id` = E.`department_id`
GROUP BY
    D.`department_name`
HAVING
    员工个数 > 3
ORDER BY
    员工个数 DESC;
    
    

-- 查询员工名、部门名、工种名、按部门名排序
SELECT
    E.`last_name`,D.`department_name`,J.`job_id`
FROM
    `departments` AS D
    INNER JOIN `employees` AS E
    INNER JOIN `jobs` AS J
ON
    D.`department_id` = E.`department_id`
    AND E.`job_id` = J.`job_id`
ORDER BY
    D.`department_name` DESC;

 

等值连接的总结

/*
等值连接的总结
1、支持排序、分组、筛选
2、Inner 可以省略
3、筛选条件放在Where后面,连接条件交给了ON，分离了连接和筛选
4、INNER JOIN & SQL92 的效果是一样的
*/

 

非等值连接查询

-- 非等值连接



-- 查询员工的工资级别
SELECT 
    E.`salary`,JG.`grade_level`
FROM
    `employees` AS E
    INNER JOIN `job_grades` AS JG
ON
    E.`salary` BETWEEN JG.`lowest_sal` AND JG.`highest_sal`;
    
    

-- 查询每个工资级别的个数，再进行一个排序
SELECT 
    JG.`grade_level`,COUNT(*) "级别个数"
FROM
    `employees` AS E
    INNER JOIN `job_grades` AS JG
ON
    E.`salary` BETWEEN JG.`lowest_sal` AND JG.`highest_sal`
GROUP BY
    JG.`grade_level`
ORDER BY
    JG.`grade_level` DESC;
    
    

-- 再个数大于20的筛选
SELECT 
    JG.`grade_level`,COUNT(*) "级别个数"
FROM
    `employees` AS E
    INNER JOIN `job_grades` AS JG
ON
    E.`salary` BETWEEN JG.`lowest_sal` AND JG.`highest_sal`
GROUP BY
    JG.`grade_level`
HAVING
    级别个数 > 20
ORDER BY
    JG.`grade_level` DESC;

 

自连接查询

-- 自连接查询


-- 查询员工的名字、上级的名字
SELECT
    E1.`last_name` "打工仔" ,E2.`last_name` "高级打工仔"
FROM
    `employees` AS E1
    INNER JOIN `employees` AS E2
ON
    E1.`employee_id` = E2.`manager_id`;

    
-- 追加查询：员工 姓名 包含k的
SELECT
    E1.`last_name` "打工仔" ,E2.`last_name` "高级打工仔"
FROM
    `employees` AS E1
    INNER JOIN `employees` AS E2
ON
    E1.`employee_id` = E2.`manager_id`
WHERE 
    E1.`last_name` LIKE '%k%';