MYSQL笔记与练习-dql(查询)语言6-9部分

进阶 六:连接查询

含义:多表查询,当查询的字段来自与多个表 笛卡尔乘积现象: 表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 就不会去重
*/

 

上一篇:spring boot项目实战系列


下一篇:MySQL 多表查询 内连接 和 外连接