当天视频
其他笔记
代码
课件
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);