MySql教程

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

本文主要是介绍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 就不会去重
*/

 

这篇关于MYSQL笔记与练习-dql(查询)语言6-9部分的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!