Oracle Day5
本节内容
1.数据库中常用的函数
2.分组查询
3.过滤查询
4.伪列 rownum ，rowid，分组排序函数 row_number() over()
5.set操作符
6.多表联接查询
7.子查询
一、数据库常用函数
函数：通过给定参数输出结果。
数据库中的函数分类：
单行函数：一条数据返回一个结果。
数值函数：关于数值的一些操作。
--数值函数的操作 --四舍五入 round(数值,保留的小数点位数) select round(45.965,2) from dual; --截断 保留几位小数，直接截断不进行四舍五入 select trunc(45.965,2) from dual; --求余数 select mod(16,5) from dual; --向上取整 只要小数点后有1-9就会向上取整 select ceil(3.01) from dual; --整数不管向上还是向下取整都是整数本身 --向下取整：不管小数点后是多少数值都会向下取整 select floor(3.99999) from dual --绝对值 select abs(-10) from dual; --求次方 power(数值,次方数); select power(2,4) from dual;
字符函数：关于字符的一些操作
--字符函数的操作
--大小写转换
--大写转小写
select lower('Drango Wal') from dual;
--小写转大写
select upper('Drango Wal') from dual;
--首字母转大写，其余转小写
select initcap('Drango wal') from dual;
--字符联接: 只能2个字符联接，可以嵌套使用
select concat('Drango','Wal') from dual;
select concat('Drango',concat('Wal','欢迎学习Oracle')) from dual;
--字符串截取 :从开始截到最后
select substr('Drango War',2) from dual;
--从2开始截取共截取5位
select substr('Drango War',2,5)from dual;
--字符串长度
select length('Drango War') from dual;
--查找字符串在字符串中的下标位置:存在返回下标，不存在返回0
select instr('Drango War','an')from dual;
--使用字符补齐字符串的位数
--左补齐
select lpad('Drango War',20,'*') from dual;
--右补齐
select rpad('Drango War',20,'*') from dual;
--去掉字符:去掉2端的字符
select trim('D'from 'Drango WarD') from dual;
--去掉右边的
select rtrim('Drango WarD' , 'D') from dual;
--去掉左边的
select ltrim('Drango WarD' , 'D') from dual;
运行结果：
drango wal
DRANGO WAL
Drango Wal
DrangoWal
DrangoWal欢迎学习Oracle
rango War
rango
10
3
**********Drango War
Drango War**********
rango War
Drango War
rango WarD
日期函数：关于日期的一些操作
--日期函数的操作
--2个日期相差的月份数：不是整月数，就会产生小数
select months_between(sysdate,to_date('2017/10-13','yyyy/mm/dd')) from dual
--向月份上加上月份数
select add_months(sysdate,3) from dual;
--指定日期的下一个星期几是那一天
select next_day(sysdate,7) from dual;
--返回本月的最后一天
select last_day(sysdate) from dual
运算结果：
10
2018/11/13 15:25:15
2018/8/18 15:25:26
2018/8/31 15:25:35
转换函数：字符、数值、日期之间的转换
--转换函数
--字符串与数值可以直接转换
select '10'+20 from dual
--数值与字符转可以直接转
select concat(10,'20')from dual;
--字符串转日期
select to_date('2018-8-13','yyyy-mm-dd') from dual
--字符转数值
select to_number('10000.00','99999.99')from dual
--日期转字符
select to_char(sysdate,'yyyy-mm-dd') from dual;
函数可以嵌套使用：函数的执行是由内到外执行。
通用函数： nvl 逻辑判断函数
nvl(参数1，参数2)；
第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来 的值
select nvl('',sysdate) from dual;
decode函数：
逻辑判断函数
select salary, decode(employees.employee_id,
101 ,salary*0.5,
130,salary*10,
salary) from employees
使用decode自定义排序：
select salary, decode(employees.employee_id,
101 ,salary*0.5,
130,salary*10,
salary) from employees
多行函数（组函数）：多条数据返回一个结果。
avg 平均值 count 统计 max最大值 min 最小值 sum求和
select avg(salary) from employees select max(salary),min(salary) from employees select sum(salary) from employees --count() * 不会忽略空值 字段：会忽略空值 select count(*) from employees; 108 select count(employees.commission_pct) from employees 35
在查询中 where之后不能使用组函数。
二、分组查询
使用的关键词： group by
使用 group by 出现在where之后，如果没有where 条件，则直接出现在表名之后 。
使用group by注意：
在SELECT 列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中。
包含在 GROUP BY 子句中的列不必包含在SELECT 列表中
group by后不能使用组函数，可以出现单行函数。
--分组查询 --分组查询员工表中有多少个部门 select department_id from employees group by department_id --查询每个部门的平均工资 select department_id,avg(salary) from employees group by department_id; --分组查询每个部门的员工的姓名: select last_name from employees group by department_id,last_name;
三、过滤查询
过滤查询关键词：having
注意：
使用having过滤查询，过滤指：是在分组查询的基础上进行过滤，有分组查询，才能有过滤查询
使用having查询语句必须有group by ，使用group by 不一定有having出现，
having后跟的是过滤条件：可以出现组函数。
--过滤查询 --查询各部门人数超过4人的部门,按照部门升序排序 select department_id,count(department_id) from employees group by department_id having count(department_id)>4 order by department_id --按照职位分组，工资大于10000的职位显示 select job_id,salary from employees group by job_id,salary having salary>10000 order by salary desc
四、伪列、分组统计函数
伪列：就像表中的一个列一样，每个表创建号之后就存在了，只不过不显示，我们使用时需要手动显示出来。 例如：rownum、以及rowid
rownum ： 行号，他会随着数据显示的位置而改变。从1开始的
rowid：每一条数据的唯一标识，数据添加后系统会产生一个rowid,永久性的不会改变。
例如我现在要查询前10条员工信息
select rownum,employees.* from employees where rownum<10
现在我单独查询102号员工
select rownum,rowid,employees.* from employees where employee_id=102
你会发现：rownum在随着查询数据显示的位置而发生改变，但是rowid不会改变。
伪列我们会在我们讲解Orcale分页的时侯来讲解，他的使用..........
分组排序函数：
select employees.*,row_number() over(partition by department_id order by salary
desc) from employees
这样就可以看到每一组的排序。
五、set操作符
set操作符：并集 交集 差集
并集： UNION / UNION ALL 取并集
UNION 会去掉重复元素
UNION ALL 不会去掉重复元素
交集：INTERSECT
差集： MINUS
--员工表与部门表部门编号的并集 select department_id from employees union --去重 select department_id from departments select department_id from employees union all --不去重 select department_id from departments --交集 select department_id from employees INTERSECT --不去重 select department_id from departments
六、多表联接查询
我们前面学习的都是单表查询，今天我们学习多表查询，也就是多张表关联查询：主要在与找主外键关系
笛卡儿积
select employees.*,departments.* from employees inner join departments on 1=1;
关联条件无效
select employees.*,departments.* from employees,departments ;
没有关联条件
这种情况就会出现笛卡尔积，数据会全部交叉。
避免笛卡儿积就需要关联查询的条件。
等值联接查询
使用“=”来进行左关联条件的操作符
select employees.*,departments.* from employees,departments ; 等值关联查询 select employees.*,departments.* from employees inner join departments on employees.department_id=departments.department_id;
--查询 90号部门的员工以及部门信息 select employees.*,departments.* from employees,departments where departments.department_id=employees.department_id and employees.department_id=90;
不等值联接查询（了解）
select e.last_name,e.salary,j.job_id,j.job_title,j.min_salary,j.max_salary from employees e,jobs j where e.salary between j.min_salary and j.max_salary order by e.last_name
外连接查询：
主要是为了找到不符合条件的数据
左外联查询
--查询的是员工信息，不管员工有没有部门，都展示出来 select e.employee_id,e.department_id,d.department_name from employees e,departments d where e.department_id = d.department_id(+)
右外联查询
--查询的是部门信息，不管部门有没有员工，都展示出来 select e.employee_id,e.department_id,d.department_name from employees e,departments d where e.department_id(+) = d.department_id
自联接查询
--自连接(难点) --from后面写2个相同的表，需要同别名区别开 select worker.employee_id,worker.first_name,manager.employee_id,manager.last_name from employees worker,employees manager where worker.manager_id = manager.employee_id order by worker.employee_id
注意：在多表联接查询中：关联查询的条件个数不能少于 表数-1个，只能多不能少。
七、子查询
子查询：就是在一个查询中嵌套另一个查询。也即通过SELECT语句的嵌套使用形成子查询。当我们不知道特定的查询条件时,可以用子查询来为父查询提供查询条件以获得查询结果
常见的子查询： 单列子查询 ：单列单行子查询、单列多行子查询、
多列子查询
在书写子查询时需要注意：
子查询必须放在括号内
子查询也必须放在比较操作符号的右边
子查询最多可以嵌套到255级
子查询中不能使用ORDER BY子句，即ORDER BY必须位于查询的最外层
子查询的运行原理： 先执行子查询，后执行父查询。（由内向外执行）
单列单行子查询：
行子查询并不是最后输出的结果只能返回一行，而是指子查询只能返回一行
当我们用“等于”比较操作符把子查询和父查询嵌套在一起时，父查询期望从子查询那里只得到一行返回值。
单行子查询：子查询只能返回一行数据，否则会出错。
单行操作符：= 、> 、< 、>= 、<= 、 <>
--子查询 --单行子查询 --查询比Abel工资高的员工信息 select * from employees where salary> (select salary from employees where last_name='Abel')
注意：如果子查询没有结果，那么整个查询都不会有结果。
--每个部门哪些员工工资比本部门平均工资高，同时显示员工工资，和
--部门平均工资
--子查询出现在from子句后，相当于一张虚拟的表
select e.employee_id,e.last_name,e.salary,e.department_id,avgsal.sal
from employees e,(select department_id,avg(salary) sal
from employees
group by department_id) avgsal
where e.department_id = avgsal.department_id
and e.salary > avgsal.sal
order by e.department_id
--子查询可以出现在select后面，相当于一个列
select employee_id,last_name,salary,(select avg(salary) from employees) sal
from employees
where salary > (select avg(salary)
from employees)
多行子查询：
子查询返回多行数据
多行操作符：in any all
--2.多行子查询
select employee_id,salary
from employees
where salary <any(
select salary
from employees
where job_id = 'IT_PROG')
and job_id<>'IT_PROG'
select employee_id,salary
from employees
where salary >any(
select salary
from employees
where job_id = 'IT_PROG')
and job_id<>'IT_PROG'
select employee_id,salary
from employees
where salary in(
select salary
from employees
where job_id = 'IT_PROG')
and job_id<>'IT_PROG'
select employee_id,salary
from employees
where salary >all(
select salary
from employees
where job_id = 'IT_PROG')
and job_id<>'IT_PROG'
select employee_id,salary
from employees
where salary <all(
select salary
from employees
where job_id = 'IT_PROG')
and job_id<>'IT_PROG'
--2.选择工资大于所有JOB_ID = 'SA_MAN'的员工的
--工资的员工的last_name, job_id, salary
select last_name, job_id, salary
from employees
where salary>all(
select salary
from employees
where JOB_ID = 'SA_MAN')
使用多行子查询时：不能使用单行子查询操作符。单行子查询可以使用多行子查询操作符，但是子查询只能返回一行数据。
多列子查询：子查询返回多个列
在使用多列子查询时必须注意：
主查询中，必须把WHERE子句中需要的多个列用括号括起来，否则发生错误
主查询WHERE子句中的列与子查询中返回的列必须匹配
--多列子查询 --查询与150号员工工资相同，部门相同的员工 select * from employees where (salary,department_id)= (select salary,department_id from employees where employee_id=150) and employee_id<>150;
Oracle中的分页查询：
Oracle中分页查询使用子查询来完成。借助伪列 rownum。
就是从查询中截取出我们需要的每页的数据
select * from (select rownum rn,employees.* from employees where rownum<=20) where rn>10 10每页开始的下标 20每页结束的下标
