oracle系列笔记---查询数据
查询数据
1. 查询（select .. form ..）
(1)普通查询
select * from employees --代表查询employees表中所有数据
select last_name, job_id from employees--查询特定的列
select email,salary*10+100 from employees --运用+-*/运算符
/*
空值（null）与对空值的处理
空值是无效的未指定的未知的不可预知的值
空值不是指空格也不是0
包含空值的数学表达式都是空值
*/
(2)列的别名
规则：重命名一个列，紧跟在列名之后 ，关键字as 也可以省略
*/ select salary*(commission_pct+12) as yearsalary ,commission_pct jiangjing from employees /* 通过as对列明重新命名，也可以不写as中间不加逗号。 commission_pct中有空值，包含空值的数学表达式都是空值，比如：100*(null+12),结果不是0，不是1200，而是null */
(3) 使用连接符（连接字符串或列)
select first_name||'is a like'||last_name as names from employees /* 类如：first_name的一行为zhang， last_name一行为san 那么通过||,那就代表他们列为一列，两列中的内容合并为zhangsan，列名改为names */ select first_name||'is a like'||last_name as names from employees --相当于变成了zhangis a likesan，中间添加固定字符串，这里只能是单引号
(4)删除相同的行
select distinct department_id from employees /* 有相同的id，那么只显示一个，这并不是真正删除数据，只是你看到的是没有重复的数据 */
2.过滤(select...from... where...)
注意：Where：使用WHERE 子句是不能使用别名，因为where的执行顺序优于select
--查询部门编号为90号所有员工id，员工工资，员工姓名 select employee_id,salary,last_name from employees where department_id=90
/*处理字符串需要注意的：
字符和日期必须出现在单引号中
字符大小写敏感日期格式敏感
默认的日期格式 DD-Mon-RR
*/
select employee_id,salary,last_name
from employees
where last_name='Weiss'
3.运算符
BETWEENAND（包含边界）; IN(包含); like（像）
WHERE salary BETWEEN 2500 AND 3100; --代表工资在2500和3100之间，包括左右 WHERE manager_id in (100,101,201); --只包含100,101,201的部门 WHERE first_name like 'S%'; --只要是s开头的first_name都能查询到 /* LIKE 1 使用LIKE运算符选择类似的值 2 选择条件 可以包括字符或者数字 3 % 代表的0个或者多个字符(任意个字符) 4 _代表一个字符 */
--筛选出 last_name 首字母任意字母 第二个字母必须是o 后方任意 SELECT first_name,last_name,salary FROM employees WHERE first_name like '_o%';
对空值（null）的处理：is null来判断当前是否为空
--得到当前公司的老板是谁？ SELECT first_name,last_name, manager_id FROM employees e WHERE manager_id IS NULL;
逻辑运算符: AND OR NOT
WHERE salary>=10000 AND job_id LIKE '%MAN%' --AND是并关系
WHERE salary>=10000 OR job_id LIKE '%MAN%' --or或关系
WHERE job_id NOT IN('MK_MAN','AD_VP','ST_MAN','SH_CLERK')--NOT IN 不包含关系
3.排序(ORDER BY)
ASC：升序排序(可以省略 默认), DESC:降序排序. ORDER BY子句 必须在SELECT语句结尾处
排序规则：
1)可以按照select出现的列名排序
2)按照列名的别名排序
3)可以按照select语句中的列名的顺序值排序
--查找部门为101，的员工的姓名,工资，同时工资按升序排序,如果工资相同员工编号按降序排序 SELECT employee_id ,last_name,salary as ids, manager_id FROM employees e where manager_id=101 order by ids,employee_id DESC
4.oracle单行函数
--trim 没有任何参数默认去除首尾空格
select trim(' hello world ') from dual;
--指定c2参数 去除头尾
select trim ('o' from 'oHello oWorldoo') from dual;
--指定leading参数 去除头部
select trim(leading 'W' from 'WWhat is this W w W')from dual; //两个ww都去除了
--指定trailing 去除尾部
select trim( trailing 'W'from 'WWhat is this W w W')from dual;//之去除最后一个W，小w不去除
数字函数
(1)ROUND: 四舍五入 例：ROUND(45.926, 2) ，结果45.93
(2) TRUNC: 截断 例：TRUNC(45.926, 2) ，结果45.92TRUNC(364, 25，-2) ，结果300
(3) MOD: 求余 例：MOD(1600, 300) ，结果100
--日期 sysdate是指当前系统时间，trunc没有说明的话默认保留0位 select first_name, trunc((sysdate-hire_date)/7) as week from employees e where department_id=90;
通用函数 这些函数适用于任何数据类型，同时也适用于空值：
NVL (expr1, expr2)，--函数将空值转换成一个已知的值 select first_name,salary, nvl(commission_pct,0) from employees --把没有奖金的空值变成0,没有新建列 NVL2 (expr1, expr2, expr3) :-- expr1不为NULL，返回expr2；为NULL，返回expr3 select first_name ,commission_pct,nvl2(commission_pct,'1','0') from employees --这个会新建列，如果为空，为0，如果不为空则为1，也可以把“1”改成 commission_pct，结果和nvl一样，但它新建列了。 NULLIF (expr1, expr2) : --相等返回NULL，不等返回expr1 --COALESCE：COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值 select first_name,commission_pct,salary,coalesce(commission_pct,salary,10) comm from employees --()中可以一直写
条件表达式：
--CASE：
select e.first_name,e.last_name,e.salary ,e.job_id,
case e.job_id when 'IT_PROG' then 2.2*salary
when 'ST_MAN' then 1.3*salary
when 'HR_REP' then 1.2*salary
else e.salary
end "REVISED_SALARY"
from employees e;
--DECODE 函数
select last_name ,job_id,salary,
decode(job_id, 'IT_PROG',2.2*salary,
'ST_MAN',1.3*salary,
'HR_REP',1.2*salary,
salary) REVISED_SALARY
from employees e; --效果和上面作用是一样的，只是格式简单点
5.分组函数（多行函数）
什么是分组函数:分组函数作用于一组数据，并对一组数据返回一个值。
类型：AVG 平均值， COUNT 数量，MAX 最大值，MIN 最小值，SUM总和
--组函数忽略空值： 1) select avg(commission_pct) from employees; 在组函数中使用NVL函数，NVL函数使分组函数无法忽略空值。 2)select avg(nvl(commission_pct,0)) from employees; --上面两组得出的结果是不一样的，第一个空值不计算，所以行数也不包括,下面一组，空值变成了0，那这一行也算一行了，所以平均值会小点
分组数据(GROUP BY)
select department_id,job_id ,avg(salary) from employees group by department_id, job_id --先根据部门排序，在部门里有效的部门在排序 order by department_id
l 非法使用组函数
(1)所有包含在select列表中 而未包含在组函数中的 必须包含在Group by 子句中
(2) 不要在where子句中 使用组函数
(3) 使用HAVING子句中可以使用组函数
--求员工的工资 高于平均工资的所有员工 select first_name,avg(salary),salary from employees where salary>avg(salary) ----这个是错误的 不能在where子句中使用组函数
过滤分组（Having）
使用HAVING 子句过滤分组
- 行数据已经被分组
- 使用了组函数
--部门工资比10000高的 select department_id,max(salary),avg(salary) from employees group by department_id having max(salary)>10000 --having语句是可以对分组在刷选的，只要符合逻辑咯
组函数嵌套
--最多嵌套两次 select max(avg(salary))--多嵌套也没有意义了，第二次嵌套就剩下一个值了 from employees group by department_id
6.集合运算 UNION/UNION ALL 并集 INTERSECT 交集 MINUS 差集
UNION运算符返回两个集合去掉重复元素后的所有记录。
UNION ALL 返回两个集合的所有记录，包括重复的。
select employee_id,job_id from employees Minus --返回属于第一个集合(上面），但不属于第二个集合的记录 select employee_id,job_id from job_history
这篇文件就讲到这里，有不足之处欢迎大家留言指点！
多表查询
这篇文章主要讲四点：
（1）oracle多表查询 （2）SQL99标准的连接查询 （3）子查询 （4）分级查询
oracle多表查询有两种方式，一种是oracle所特有的查询方式，一种是SQL99标准的连接查询，是通用的一种多表查询。
1. Oracle 连接
等值连接 在where中加入连接条件。在表中有相同的列在列名之前可以加上前缀。
--查询 员工的id 员工的姓名 员工的部门名称 员工所在的部门的城市 select e.employee_id,e.first_name,d.department_name,l.city --这三个属性都不在一个表中，所以要建立关系 from employees e, departments d,locations l where e.department_id=d.department_id and d.location_id =l.location_id
外连接： 使用外连接可以查询不满足条件的数据。外连接的符号(+)
--查询员工的id 姓名 部门名称 要求显示 所有的员工信息 没有部门的员工也要显示出来 select e.employee_id,e.first_name, d.department_name from employees e,departments d where e.department_id=d.department_id(+) --因为有可能有员工是没有部门的，这个时候默认是不显示的，要显示所有员工，就在和员工对面加（+）
--查询员工的id 姓名 部门名称 要求显示 所有的部门信息 没有员工的部门也要显示出来 select e.employee_id,e.first_name, d.department_name from employees e,departments d where e.department_id(+)=d.department_id --同样会有部门没有员工的，同上
自连接：就是都在统一表中
--显示所有的员工 姓名 编号 和上一级领导的名字 select e.employee_id,e.first_name, ee.first_name from employees e, employees ee where e.manager_id=ee.employee_id
2.SQL99标准的连接查询
通用的一种多表查询，上面的是oreal特有的
Join on ：
---查询员工的id 姓名 部门的名字 select e.employee_id,e.first_name, d.department_name from employees e join departments d on e.department_id= d.department_id;
Natural join ：自然连接 子句会以两个表中具有相同名字的列作为等值连接条件，在表中查询满足条件的数据
-- 部门的id 部门的名称 部门所在城市的名字 select d.department_id,d.department_name,l.city from departments d natural join locations l --会按顺序查找是否有相同
Join using：使用using子句可以在有多个列满足条件的情况下进行筛选，不要给选中的列加上表名或者前缀或者别名
select e.employee_id,e.last_name,d.location_id from employees e join departments d using(department_id)--我觉得比Natural join更实用吧
外联接:
Lift outer join on ：左外连接
right outer join on 右外连接
Full outer join on 满外连接： 就是把两边都不满足的选出来
--查询员工的id 员工的姓名 员工的部门 要求 显示所有的员工信息没有部门的员工信息也要显示出来 select e.employee_id, e.first_name,d.department_name from employees e left outer join departments d on e.department_id=d.department_id; --左满表示左边全部显示
3.子查询
--查找工资比OConnell工资高的所有人 select first_name,salary from employees e where e.salary>(select salary from employees where last_name='OConnelll') --括号中最后返回的仅仅是OConnell一个人的工资
oracle 中有两个隐藏列
rowid 是一个唯一的 不会重复 rownum 是隐藏的用来标识当数据行的
Rownum：
-----找出工资最高的前三个 select a.first_name,a.salary from (select rowid,rownum, e.first_name,e.salary from employees e order by e.salary desc) a where rownum<=3 --括号中已经对工资进行降序，但rownum在没有排序之前就已经从1,2...开始开好了，你最后排序了排序好后rownum变成杂乱的，外面又是一个新表，同样也有隐藏的rownum，这个时候同样是1,2....排好序，而且也对于的是降序，所以rownum<=3就可以出现工资最高的前三。
分页查询：使用oracle语法 写出一个分页查询的sql
--总共有107条件数据 每页显示10条 查询第三页数据 31-40 要求按照工资排序 --第一步完成排序 第二步 固化rownum 第三步根据条件进行筛选 select rn r,t2.first_name,t2.salary from (select rownum rn,t1.* from (select * from employees e order by e.salary desc) t1) t2 where rn>=31 and rn<=40 --第一个括号仅仅是降序了，第二个括号使rownum 和salary都有序排列，第三才是实例化rownum这个列是他变成真正存在的r列。 --同时where是不能别名的，所以要用rn，而不是r，因为顺序where优先于select
在子查询中使用组函数
--查询工资最低的员工有哪些 select first_name,salary from employees where salary= (select min(salary) from employees )--括号中就是一个工资最小值 ---筛选出比 按照部门分组 得到部门的最低工资 然后找出比 50号部门最低工资高的部门有哪些 --子查询中的 HAVING 子句 select department_id,min(salary) --有min就表示不能有where from employees e group by department_id having min(salary)>( select min(salary) from employees where department_id=50) --括号中是50号部门的最低工资
多行子查询：
In （等于列表中任意一个）
select first_name,salary from employees e where e.salary in( select salary from employees ee where ee.salary<10000) --就相当于只要满足里面一个就可以，感觉加个in一点意义都没有
ANY (和子查询返回的任一个值比较）
select first_name,salary from employees e where e.salary > any( select salary from employees ee where ee.salary<10000) --就相当于和括号中最小的一个值比较
ALL(和子查询返回的所有值比较）
--就相当于和括号中最大的一个值比较
4.分级查询：
可以明确的看到上下级关系
分级查询可以从上往下查询也可以从下往上查询
-- 从底部查询 select employee_id,last_name,job_id,manager_id from employees start with employee_id=104 connect by prior manager_id=employee_id
运行结果：
--从顶部到底部查询 select last_name,employee_id, manager_id from employees e start with last_name='King' connect by prior employee_id =manager_id
--使用level 和lpad 格式化分层查询 select lpad(last_name,length(last_name)+(level*3)-2,'_') from employees e start with last_name='King' connect by prior employee_id =manager_id
运行结果：
这篇文件就讲到这里，有不足之处欢迎大家留言指点！