oracle系列笔记(1)---查询数据

select * from employees --代表查询employees表中所有数据
select last_name, job_id from employees--查询特定的列
select email,salary*10+100 from employees --运用+-*/运算符
/*
空值（null）与对空值的处理
空值是无效的未指定的未知的不可预知的值
空值不是指空格也不是0
包含空值的数学表达式都是空值
*/

*/
select salary*(commission_pct+12) as yearsalary ,commission_pct jiangjing from employees
/*
通过as对列明重新命名，也可以不写as中间不加逗号。
commission_pct中有空值，包含空值的数学表达式都是空值，比如：100*(null+12),结果不是0，不是1200，而是null
*/

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，中间添加固定字符串，这里只能是单引号

select distinct department_id from employees
/*
有相同的id，那么只显示一个，这并不是真正删除数据，只是你看到的是没有重复的数据
*/

--查询部门编号为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'

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%';

--得到当前公司的老板是谁？
SELECT first_name,last_name, manager_id
FROM employees e
WHERE manager_id IS NULL;

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 不包含关系

--查找部门为101，的员工的姓名,工资，同时工资按升序排序,如果工资相同员工编号按降序排序
SELECT employee_id ,last_name,salary as ids, manager_id
FROM employees e
where manager_id=101
order by ids,employee_id DESC

--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不去除

--日期 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,'','') 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; --效果和上面作用是一样的，只是格式简单点

--组函数忽略空值：
1) select avg(commission_pct) from employees;
在组函数中使用NVL函数，NVL函数使分组函数无法忽略空值。
2)select avg(nvl(commission_pct,0)) from employees;
--上面两组得出的结果是不一样的，第一个空值不计算，所以行数也不包括,下面一组，空值变成了0，那这一行也算一行了，所以平均值会小点

select department_id,job_id ,avg(salary)
from employees
group by department_id, job_id --先根据部门排序，在部门里有效的部门在排序
order by department_id

--求员工的工资 高于平均工资的所有员工
select first_name,avg(salary),salary
from employees
where salary>avg(salary) ----这个是错误的 不能在where子句中使用组函数

--部门工资比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

select employee_id,job_id
from employees
Minus --返回属于第一个集合(上面），但不属于第二个集合的记录
select employee_id,job_id
from job_history