当天视频     其他笔记     代码 课件

ORACLE 03

第23天笔记
学生表

编号id        姓名name      性别sex       年龄age
    1           石鹏          男           18
    2           魏洋          女           16
    3           李腾          男           14
    4           高阳          女           17
    5           于洋          男           17

---------------------------------------------------------------

要求: 查询出所有的男生, 要求:   他必须比一个或 一个以上的女生年龄大 .

条件: s1  s2          s1.sex=男  and s2.sex=女 and s1.age>s2.age

---------------------------------------

外连接 (+) 熟悉

外连接的结果 = 自连接的结果 + 自连接匹配不上的结果.
(自连接,  指的是 将一个表格 看作两张表, 取出其中更深层次的数据含义.
自连接 会通过一些条件 ,筛选掉一些不匹配的数据,  
外连接 就是将这些不匹配的数据, 通过null 匹配, 并填充到结果集中

)

外连接语法:
    我们编写自连接时,  会给两个表格添加where条件, 来处理笛卡尔积. 例如:
        e.manager_id = m.id

    外连接的语法 与 自连接很像, 我们在where条件的等号的任意一边 的字段后 加入(+) ,
    被(+)符号所修饰的字段 对面的表格的数据 全部被选中.

    格式:

        select 列名列表 from 表名1,表名2 where 表名1.x(+) = 表名2.y;



    员工表

编号id    姓名name  领导编号manager_id
    1       石鹏          null
    2       于洋          1
    3       魏洋          1
    4       李腾          2

---------------------------------------------------------

自连接     条件 员工表1.manager_id = 员工表2.id , 查询的信息是:  员工表2: 编号 和 姓名

        员工表1

编号id    姓名name  领导编号manager_id
    1       石鹏          null
    2       于洋          1
    3       魏洋          1
    4       李腾          2

        员工表2

编号id    姓名name  领导编号manager_id
    1       石鹏          null
    2       于洋          1
    3       魏洋          1
    4       李腾          2

结果:
-------------------------------------
        员工表1(+)                                         员工表2
编号id    姓名name  领导编号manager_id      |   编号id    姓名name  领导编号manager_id
    2       于洋          1                   |       1       石鹏          null
    4       李腾          2                   |       2       于洋          1
    null    null        null                |       3       魏洋          1
    null    null        null                |       4       李腾          2

员工表1.id is null  and 员工表2.id is not null;


案例: 查询员工表格,  获取所有的普通员工的信息

语句: select distinct m.id,m.last_name,m.salary from s_emp m,s_emp e where m.id=e.manager_id(+) and e.id is null;

SQL99标准的 内连接

oracle语法:
    select 列名列表 from 表名1,表名2...表名n where 关联条件 and 筛选过滤条件.

SQL99标准:
    select 列名列表 from 表名1 inner join 表2 on 关联条件 where  过滤条件;

案例: 查询部门表格 s_dept 和 地区表格 s_region . 查询部门编号,部门名称, 地区名称 , 
    oracle:     select d.id,d.name,r.name from s_dept d,s_region r where d.region_id=r.id;
    SQL99 :     select d.id,d.name,r.name from s_dept d inner join s_region r on d.region_id=r.id;

练习: 查询部门表格 s_dept 和 地区表格 s_region . 查询部门编号,部门名称, 地区名称 ! 要求: 只查询部门编号大于40的部门信息;
    oracle:     select d.id,d.name,r.name from s_dept d,s_region r where d.region_id=r.id and d.id>40;
    SQL99 :     select d.id,d.name,r.name from s_dept d inner join s_region r on d.region_id=r.id where d.id>40;

SQL99标准的 外连接 了解

在oracle 编写外连接SQL语句时, 在where条件的字段后, 加入(+)  , (+)位置不同, 分为左外连接 和 右外连接
在SQL99标准中也分为两种格式

    1.  左外连接
            格式: select 列名列表 from 表名1 left outer join 表名2 on 连接条件 where 过滤条件 !
            作用: 相当于在oracle 的外连接语法中, 将(+) 加在了右边. 左边数据都匹配到
    2.  右外连接
            格式: select 列名列表 from 表名1 right outer join 表名2 on 连接条件 where 过滤条件 !
            作用: 相当于在oracle 的外连接语法中, 将(+) 加在了左边. 右边数据都匹配到

案例: 查询普通员工:

oracle: select distinct m.id,m.last_name,m.salary from s_emp m,s_emp e where m.id=e.manager_id(+) and e.id is null;
SQL99 : select distinct m.id,m.last_name,m.salary from s_emp m left outer join s_emp e on m.id=e.manager_id where e.id is null;

练习: 查询普通员工: 要求: 薪资大于1400

oracle: select distinct m.id,m.last_name,m.salary from s_emp m,s_emp e where m.id=e.manager_id(+) and e.id is null  and m.salary>1400;
SQL99 : select distinct m.id,m.last_name,m.salary from s_emp m left outer join s_emp e on m.id=e.manager_id where e.id is null and m.salary>1400;

组函数 (多行函数) *

单行函数:   对于结果中的每一行数据, 都会产生一个运算的结果
多行函数:   对于结果的所有数据, 产生一个结果.

任何的组函数在使用时, 会忽略null值;

常用的组函数:
    -   count(列名)       :   统计行数
    -   max(列名)     :   结果的所有行里, 此列最大的值
    -   min(列名)     :   结果的所有行里, 此列最小的值
    -   sum(列名)     :   结果的所有行里, 此列所有值的和
    -   avg(列名)     :   结果的所有行里, 此列所有值的平均值

案例: 查询部门的员工人数:
语句: select count(id) from s_emp;

练习1:    统计s_emp表格中, 所有员工的薪资总和
语句: select sum(salary) from s_emp;

练习2:    统计s_emp表格中, 所有员工的薪资平均值
语句: select avg(salary) from s_emp;

练习3.    统计s_emp表格中, 所有员工的薪资最小值
语句: select min(salary) from s_emp;

练习4.    统计s_emp表格中, 所有员工的薪资最大值
语句: select max(salary) from s_emp;

练习5:    统计s_emp表格中, 所有员工的姓 最大值
语句: select max(last_name) from s_emp;

问题1:

很多人 在使用组函数时, 会编写一些逻辑上存在错误的代码: 

    查询s_emp表格的信息:

    //  既想统计一些信息, 还想查看某些列的值
    select count(id),last_name,salary from s_emp;

    出现了错误:
        第 1 行出现错误:
        ORA-00937: 不是单组分组函数

    原因是:    count(id) 的结果只有一个, 而last_name 和 salary 的结果有25个. 语句的结果无法按照表格形式逐行显示.  

    如何解决:   在使用组函数时, 查询的字段与组函数的结果, 必须是一一对应 !  使用组函数查询时, 查询的所有列, 应都是组函数的结果.

    select count(id),max(last_name),max(salary) from s_emp;

问题2:
练习:     统计s_emp表格中, 所有员工的平均提成 (commission_pct)
语句:     select avg(commission_pct) from s_emp;

结果为13 . 

原因是:    组函数忽略了null值, 其实是将5个拥有提成员工的 提成信息/5 !

解决方案:
    笨:      select sum(commission_pct)/count(id) from s_emp;    
    正常: select avg(nvl(commission_pct,0)) from s_emp;

SELECT 语句 - group by 分组子句 以及 having 分组条件 熟悉

格式: select 字段列表 from 表名 group by 分组标准字段 [having 分组过滤条件]

分组标准字段:     指的是查询的结果 按照某些字段, 分成多组数据 ! 
            例如: s_emp表格可以按照员工的部门编号进行分组
分组过滤条件:     指的是对分组后的结果, 进行筛选! 

分组查询的限制:  
        在数据库操作中, 分组查询语法要求很强硬 , 必须遵循规则, 否则很容易触发错误.

        分组查询时, 查询的数据 必须一一对应.
        在使用分组查询时, 查询的字段 只能是如下两种字段:
            1.  组函数的结果 (每一组统计一个结果)
            2.  分组标准字段  (同一组中 , 分组标准是一致的 . 也就是只有一个分组标准)

分组查询时 常见的错误:
    第 1 行出现错误:
    ORA-00979: 不是 GROUP BY 表达式

    原因: 分组查询的数据 没有一一对应 .



案例: 查询员工信息 , 按照部门的编号分组 , 并统计每一个部门的人数(显示部门编号 和 部门人数):
语句: select dept_id,count(id) from s_emp group by dept_id;

案例: 查询员工信息, 按照部门编号分组, 统计每个部门的人数 , 条件: 部门人数低于3人的 不显示
语句: select dept_id,count(id) from s_emp group by dept_id having count(id)>=3;

案例: 查询员工信息, 按照部门编号分组, 统计每个部门的人数,要求薪资低于1000的不参与统计 , 条件: 统计结果中部门人数低于3人的 不显示
语句: select dept_id,count(id) from s_emp where salary>=1000 group by dept_id having count(id)>=3;

练习1:    查询员工信息, 按照部门编号分组,  统计每个部门的人数, 平均薪资!
语句: select dept_id,count(id),avg(salary) from s_emp group by dept_id;

练习2:    查询员工信息, 按照部门编号分组,  统计每个部门的人数, 平均薪资! 要求: 薪资低于1000的不参与统计!
语句: select dept_id,count(id),avg(salary) from s_emp where salary>=1000 group by dept_id;

练习3:    查询员工信息, 按照部门编号分组,  统计每个部门的人数, 平均薪资! 条件: 部门薪资平均值低于1200的 不显示.
语句: select dept_id,count(id),avg(salary) from s_emp group by dept_id having avg(salary)>=1200;

练习4.    查询员工信息, 按照部门编号分组,  统计每个部门的人数, 平均薪资! 要求: 薪资低于1000的不参与统计! 条件: 部门薪资平均值低于1200的 不显示 
语句: select dept_id,count(id),avg(salary) from s_emp where salary>=1000 group by dept_id having avg(salary)>=1200;

练习5:    查询部门表格 s_dept , 地区表格s_region! 按照地区名称分组, 显示地区名称, 和 地区部门数量.  
语句: select r.name,count(d.id) from s_dept d,s_region r where d.region_id = r.id group by r.name;

有时我们分组查询, 要显示一些其他字段数据 :

查询部门信息 , 要求: 按照部门编号进行分组 , 显示部门的人数, 部门的编号 , 部门的名称:

select count(e.id),d.id,max(d.name) from s_emp e,s_dept d where e.dept_id=d.id group by d.id;

where 条件 与 having条件的区别 *****

where条件是对 所有数据 在分组之前 进行筛选.
having条件是对分组查询的结果 进行筛选.

where条件 在 having 条件之前执行.

select 语句 完整格式 *****

select 列名列表 from 表名 [where 条件][group by 分组标准字段 [having 过滤条件]] [order by 排序字段 排序规则]

执行顺序:   -->from   --> where条件  --> group by分组 --> having条件 -->select结果 --> order by排序

子查询 ***

指的是:    将一个select查询语句的结果 , 当作另一个select查询语句的一部分.
子查询中的子语句 , 必须使用小括号括住!

子查询可以在查询语句的三个位置编写:   

    1.  将一个select语句的结果, 编写在from 之后, 当作一个表格进行查询.
    2.  将一个select语句的结果, 编写在where条件之后 , 作为条件的一部分存在.
    3.  将一个select语句的结果, 编写在having条件之后 , 作为分组条件的一部分存在.
子查询应用到from之后 ***
我们之前查询的是一个个的表格,  
现在我们将一条查询语句的结果 看作一个新的表格.
查询这个新表格:

案例:
        查询员工的id,last_name,salary 条件为: id大于10!
        select id,last_name,salary from s_emp where id>10;

        查询上述的结果, 获取其中薪资不低于1200元的.
        select * from (select id,last_name,salary from s_emp where id>10) where salary>=1200;

练习:
        查询员工的id,last_name,salary 条件为: 部门编号45;
        select id,last_name,salary,dept_id from s_emp where dept_id=45;
        查询上述的结果, 获取其中薪资低于1300元的员工信息 id,last_name,salary 
        select * from (select id,last_name,salary,dept_id from s_emp where dept_id=45) where salary<1300;

子查询 应用到 where 条件 ***

将一个select 查询的结果, 当作where后的筛选条件. 

子查询的语句 可以在逻辑运算符的 两边 , 或者在闭区间 和 in关键字后 !

案例:查询id最大的 ,员工信息:

    步骤1.    查询最大的员工id
            select max(id) from s_emp;
    步骤2.    根据上述查询得到的id , 查询最大员工id的 员工信息id,last_name,salary;
            select id,last_name,salary from s_emp where id=(select max(id) from s_emp);


练习1:    查询部门编号最大的 , 部门所有员工的信息(id,last_name,salary,dept_id)
    步骤1.    查询出最大的部门编号
            select max(dept_id) from s_emp;

    步骤2.    根据上述查询的编号, 查询部门的员工信息.
            select id,last_name,salary,dept_id from s_emp where dept_id=(select max(dept_id) from s_emp);


练习2:    查询出公司所有的领导
    步骤1.    查询所有人的 manager_id , 找出所有的领导id , 并去重复
            select distinct manager_id from s_emp where manager_id is not null;

    步骤2.    根据上面得到的所有领导的id , 使用 in (子查询) , 得到所有领导的 id,last_name,salary.
            select id,last_name,salary from s_emp where id in(select distinct manager_id from s_emp where manager_id is not null);

练习3.    查询出公司所有的普通员工.
            select id,last_name,salary from s_emp where id not in(select distinct manager_id from s_emp where manager_id is not null);

子查询应用在 having 之后 了解

将一个查询语句的结果, 作为另一个查询语句的分组过滤条件 .

案例: 查询所有部门的平均薪资, 要求:    平均薪资大于 公司平均薪资的 才显示出来.

    步骤1.    查出公司的平均薪资
            select avg(salary) from s_emp;
    步骤2.    按照部门编号分组, 求出每个部门的平均薪资!  按照第一步查询出的结果, 进行筛选 !
            select dept_id,avg(salary) from s_emp group by dept_id having avg(salary)>(select avg(salary) from s_emp);