进阶 六：连接查询


含义：多表查询，当查询的字段来自与多个表

笛卡尔乘积现象： 表1 有M行，表2有N行，结果 = m*n
    -发生原因：没有有效的连接条件
    -如何避免，添加有效的连接条件
    
连接的分类：
    按年代分类：
    sql 92标准、99标准【推荐】：支持内连接+外连接（左外和右外） +交叉连接
    
    按功能分类：
        内连接：
        1）等值连接
        2）非等值
        3）自连接
        外连接：
        1）左外连接
        2）右外连接
        3）全外连接
        交叉连接：
一、sql 92
1、92-等值连接        
select name,boyName
from boys,beayty
where boys.id = beauty.boyfriend_id; 匹配、筛选    
结论：
    1、多表等值连接的查询结果是多表的交集

*/
SELECT NAME,boyName
FROM boys AS b1,beauty AS b2 # 可以为表起别名，提高语句简洁度，区分重名的字段
#注意：如果为表取了别名，则查询的字段不能使用原来的表名限定，只能使用别名
WHERE b1.id = b2.boyfriend_id;

#查询每个城市的部门个数 ,
#添加分组、筛选
SELECT COUNT(*),`department_id`,`city`
FROM `departments` d,`locations` l
WHERE d.`location_id` = l.`location_id`
GROUP BY `city`;


#2、92-非等值连接
SELECT salary,`grade_level`
fro
m `job_grades` j,`employees` e
WHERE salary BETWEEN `lowest_sal` AND `highest_sal`; #表的等值连接是前小后大，但在用于筛选时是前大后小，一个表对应另一个表的区间

SELECT `salary`,COUNT(`salary`)
FROM `employees`
GROUP BY `salary`;

#3、92-自连接
#自己连接自己 一个表当多个表使用


#二、sql 99 语法


内连接（*）inner join 
外连接
    左外（*）：left outer join
    右外（*）：right 【outer】join
    全外:
交叉连接:cross

语法：
    select 查询列表
    from 表1 别名 【连接类型】
    join 表2 别名
    on 连接条件
    where 筛选条件
    group by 分组条件
    having 筛选条件
    order by 排序连接

1)内连接
    1）等值连接：inner join……on
    查询员工名、部门名
    
    SELECT `department_name`,`first_name`
    FROM `employees` e INNER
    JOIN `departments` d
    ON e.`department_id` = d.`department_id`;
    
    查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序
    特点：
    ①inner 可以省略
    ②筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
    ③inner join 连接和SQL 92语法中的等值连接效果一样的，都是查询多表的
    
    2)非等值连接
    SELECT salary,`grade_level`
    from `job_grades` j
    join `employees` e
    on salary BETWEEN `lowest_sal` AND `highest_sal`; #表的等值连接是前小后
    
    3)自连接
    SELECT e.`last_name`, m.`last_name`
    from `employees` e
    join `employees` m
    on e.`manager_id` = m.`employee_id`; #表的等值连接是前小后

2)外连接
应用场景：用于查询一个表中有，另外一个表中没有的
特点：    
1、外连接的查询结果为主表的所有记录
如果从表中有和它匹配的，则显示null
外连接查询结果 = 内连接查询结果+主表中有 而从表没有的记录
2、左外连接：left左边的是主表
右外连接：right join 右边的是主表
3、左外和右外交换2个表的顺序，可以实现同样的效果

SELECT d.s`department_name`,COUNT(*) 员工个数
FROM `departments` d 
INNER JOIN `employees` e
ON d.`department_id`= e.`department_id`
GROUP BY d.`department_name` DESC
HAVING COUNT(*) > 3; 

SELECT `job_id`,COUNT(*) 员工个数   #内部等值连接
FROM `departments` d 
INNER JOIN `employees` e
ON d.`department_id`= e.`department_id`
GROUP BY e.`job_id` DESC; 

SELECT be.`name`,bo.*   
FROM `beauty` be
LEFT OUTER JOIN `boys` bo #左外连接
ON be.`boyfriend_id` = bo.`id`;

#七、子查询
#一、where或having后面
/

出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询
分类：
    （1）按子查询出现的位置：
    select后面：
        （支持标量子查询）
    from后面：
        （支持表子查询）
    where或者having后面：***
        （支持标量子查询，单行）√
        （列子查询，多行） √
        （行子查询）
        
    exist后面（相关子查询）
        表子查询
        
    （2）按结果集的行列数不同
    标量子查询（结果集只有一行一列）
    列子查询（结果集只有一列多行）
    行子查询（结果集有一行多列）
    表子查询（结果集，一般为多行多列）

*1、子查询放在小括号内
2、子查询一般放在条件的右侧
3、标量子查询一般搭配单行操作符使用
4、子查询执行结果先与使用主查询
    < > = 
    
*/

#返回`job_id`与141号员工相同，`salary`比143号员工多的员工的姓名、`job_id`、`salary`
    #（1）标量子查询---141号员工的`job_id`、`salary`  ---标量子查询
    SELECT `job_id`,`salary`
    FROM`employees`
    WHERE `employee_id` = 141;
    #(2)返回`job_id`与141号员工相同，`salary`比143号员工多的员工的姓名、`job_id`、`salary`
    SELECT `job_id`,`salary`,`last_name`
    FROM `employees`
    WHERE job_id = (
        SELECT `job_id`
        FROM`employees`
        WHERE `employee_id` = 141 #如果子查询使用的不是一行一列，报非法使用
    ) AND salary > (
        SELECT`salary`
        FROM`employees`
        WHERE `employee_id` = 143
    );
    #（2）列子查询--多行子查询
    /*
    使用多行比较操作符
        in/not in 等于列表的任意一个
        any|some //和子查询返回的某一个值比较 
        all 和子查询返回的所有值比较
    */
    #返回`location_id`是1400或1700的部门中所有员工的姓名
    SELECT em.`first_name`,`last_name`,de.`location_id`
    FROM `employees` em
    INNER JOIN `departments` de
    ON em.`department_id` = de.`department_id`
    WHERE de.`location_id` = 1400 OR 1700;
    
    SELECT `first_name`,`last_name`
    FROM `employees` 
    WHERE `department_id` IN (
        SELECT `department_id`
        FROM `departments`
        WHERE `location_id` = 1400 OR 1700 #单列多行
    );

#二、select 后面---仅仅支持标量子查询    
    #获得每个部门的员工个数
    #获取所有的部门
    SELECT DISTINCT `department_id`
    FROM `employees`;
    
    #获取每个部门的员工个数
    SELECT COUNT(`employee_id`),`department_id`
    FROM `employees`
    GROUP BY `department_id`;
    
#三、from后面
    #（1）将查询结果作为一张表
    
    #查询每个部门的平均工资的工资等级
    #1、各个部门的平均工资
    
    SELECT 表1.*,jg.`grade_level`
    FROM (
        SELECT AVG(salary) avg_salary,`department_id` #子查询，获得各个部门的平均工资
        FROM `employees`
        GROUP BY `department_id`
    ) 表1
    INNER JOIN `job_grades` jg
    ON avg_salary BETWEEN `lowest_sal` AND `highest_sal`
    
#四、exists 后面（相关子查询）
    #exist（完整的查询语句），有查询结果返回1，没有返回0
    
#查询有员工的部门名
SELECT DISTINCT `department_name`
FROM `departments` de
LEFT OUTER JOIN `employees` em
ON de.`department_id` = em.`department_id`
WHERE em.`employee_id` IS NOT NULL;

SELECT `department_name`
FROM `departments` de
WHERE EXISTS(
    SELECT *
    FROM employees em
    WHERE em.`department_id` = de.`department_id`
);
#exist的解析：
（1）查询顺序，先执行from，然后where，进入exist，通过判断exist为真或者为假决定select是否执行，判断过程会传入外部查询的每一行分别执行一次子查询
 

#内联结
SELECT `openbid_state`,`profession`
FROM`open_bid_section` INNER JOIN `open_bid`
ON `open_bid`.`bid_section_id` =  `open_bid_section`.`bid_section_id`;

#外联结，需要指明方向，right/left/full outer join 
SELECT `openbid_state`,`profession`
FROM`open_bid_section` LEFT OUTER JOIN `open_bid`
ON `open_bid`.`bid_section_id` =  `open_bid_section`.`bid_section_id`#;--不能检索右边的null
UNION 
SELECT `openbid_state`,`profession``Timestamp`
FROM`open_bid_section` RIGHT OUTER JOIN `open_bid`
ON `open_bid`.`bid_section_id` =  `open_bid_section`.`bid_section_id`;

SELECT `openbid_state`,`profession`
FROM`open_bid_section` FULL OUTER JOIN `open_bid`
ON `open_bid`.`bid_section_id` =  `open_bid_section`.`bid_section_id`; --MYSQL不支持full


#查询8：分页查询 **
/*
应用场景：当要显示的数据一页显示不全，需要分页提交sql请求
语法：
    select 查询列表
    from 表
    【join type 表2 
    on 链接条件
    where 筛选条件
    group by 分组字段
    having 分组后的筛选
    order by 排序的字段】
    limit 起始索引offset，size;  -----起始索引从0开始可以省略
        查询第11-25条
        limit 10,15
    
        offset 要显示条目的起始索引（这里索引从0开始）
        size 要显示的条目个数
特点：
①limit 放在查询语句的最后
②公式
    select
    from
    limit (page -1)*size,size
*/

#进阶9 联合查询
/*
union 联合 合并：将多条查询语句的结果合并成为一个结果

语法：
    查询语句1
    union
    查询语句2
    应用场景：
    要查询的结果来源于多个表，且多个表没有直接的链接关系，但查询的信息一致
特点：
（1）查询的字段数量相同
（2）要求查询的字段的顺序和类型一致
（3）使用union关键字，结果会自动去重
    使用union all 就不会去重
*/