1、零散知识

-- 字段名、表名默认大小写不敏感，

-- 字符串连接符是 || 

-- 日期做条件需要转格式
select * from employees where  hire_date  =  to_date('1987-06-17 00:00:00','yyyy-mm-dd hh24:mi:ss')
select * from employees where to_char( hire_date ,'yyyy-mm-dd')='1987-06-17';

-- distinct关键字必须放在前面，否则会出现“行数不匹配”的错误，前一个字段查的行数多，后一个字段被distinct修饰后查的行数少，无法组成表
select  job_id , distinct first_name from employees;

-- like关键字,%和_都是特殊符号，需要escape做转义
select * from EMPLOYEES where FIRST_NAME like '%\_%' escape '\'

-- 字段别名可以排序，但不能做where条件，SELECT语句在WHERE语句后面才执行，而列的别名是在SELECT时才生成的
select job_id job_type from employees order by job_type
-- 字段别名前可以加as或者不加，表别名前不能加as

-- 取出x条记录
select rownum as rn, t.* from employees t where rownum<10;

-- 重新(分区)排序
select employee_id,first_name||' '||last_name,job_id,salary, row_number() over(partition by job_id order by salary) as num 
from employees order by job_id

2、单行函数

-- 数值函数：四舍五入、截断、求余
select round(54.935,2),trunc(54.935,2),mod(102,5) from dual;

-- 字符串函数：oracle的字符索引是从1开始的
-- 大小写转换
select lower('aBc'),upper('aBc') ,initcap('aBc') from dual;
-- 连接、子串、长度、索引
select concat('hello',' world') ,substr('hello world',1,5),length('hello world'),instr('hello world','w') from dual
-- 填充头尾、去除头尾、替换
select lpad('world',10,'h'),trim('h' from 'hello ho hh'),ltrim('hello','h'),replace('hello worl','worl','world')from dual

-- 日期函数：星期从星期日开始算
-- 当前时间、日期加减、月份差
select sysdate, sysdate+1, months_between(sysdate,to_date('2019-09-19','yyyy-mm-dd'))	 from dual;
-- 加减月份、下周几、月末、日期截断
select sysdate, add_months(sysdate,-1), next_day(sysdate,1),last_day(sysdate), from dual;
-- 日期四舍五入：年、月、周日、日、时、分
select sysdate,round(sysdate,'yy'),round(sysdate,'mm'),round(sysdate,'d'),
round(sysdate,'dd'),round(sysdate,'hh'), round(sysdate,'mi') from dual;
-- 日期截断：参数同上
select sysdate, trunc(sysdate,'yy')  from dual;

-- 类型转换函数：varchar2<==>number<==>date 之间能自动(隐式)转换，也可以用以下函数进行显示转换
-- to_date,to_number,to_char
select to_char(sysdate,'yyyy-mm-dd "insert" hh:mi:ss'),
to_char(145665.421,'$999,999,999,999.00'),
to_date('2012年10月29日 08:10:21','yyyy"年"mm"月"dd"日"hh:mi:ss'),
to_number('$1,234,567,890.00','$999,999,999,999.99')
from dual;

-- 空值函数(通用)：
-- 空值转换
select last_name, salary ,commission_pct,(salary*12) + (salary*12*NVL(commission_pct, 0)) total_salary FROM employees; 
-- 空值转换之三元表达式
select last_name,  salary, commission_pct,NVL2(commission_pct, 'SAL+COMM', 'SAL') income from   employees
-- 空值转换之交替处理，参数个数不行，找到不为空的返回
select last_name,coalesce(commission_pct, salary, 10) comm from employees order by commission_pct;
-- 相等为空
select first_name,last_name, NULLIF(LENGTH(first_name), LENGTH(last_name)) result from   employees;

-- 条件表达式：if-else逻辑
-- case表达式
select last_name, job_id, salary,
	case job_id 
		when 'IT_PROG'  then  1.10*salary
		when 'ST_CLERK' then  1.15*salary
		else  salary 
	end as revised_salary
from   employees
-- decode函数
select last_name, job_id, salary,
	decode(job_id,
		'IT_PROG', 	1.10*salary,
		'ST_CLERK',	1.15*salary,
		salary) as revised_salary
from   employees

3、多表查询

-- 多表联查时，为了避免笛卡尔积，需要在where后加上有效的连接条件，不加条件相当于cross join
select last_name, department_name from employees, departments

-- 内连接分为等值连接、非等值连接、自连接
-- 等值连接
select last_name, department_name from employees  emp, departments  dpt where emp.department_id=dpt.department_id
select last_name, department_name from employees  emp inner join departments  dpt on emp.department_id=dpt.department_id  
-- 非等值连接
select e.last_name, e.salary, j.grade_level from   employees e, job_grades j where  e.salary between j.lowest_sal and j.highest_sal;

-- 外连接，相当于left join/right join/full join，连接条件中没有匹配行的表的列后面要加外连接运算符(+)
-- 外连接的两种写法
select e.last_name, e.department_id, d.department_name from employees e, departments d where  e.department_id(+) = d.department_id ;
select e.last_name, e.department_id, d.department_name from employees e right join departments d on e.department_id = d.department_id ;

-- 自然连接，会以两个表中具有相同名字的列为条件创建等值连接，若多表的连接列列名不同则不合适
select department_id, department_name, location_id, city from departments natural join locations ;
select department_id, department_name, location_id, city from departments  join locations using(location_id);

4、分组查询

-- 组函数会忽略空值
select count(*)  cnt from employees; --107
select count(commission_pct) cnt from employees --35

-- NVL()函数使分组函数无法忽略空值
select count(NVL(commission_pct, 0)) cnt from employees --107

-- count+distinct，非空且唯一
select count(distinct commission_pct)  cnt from  employees;--7

-- 分组，select 列表中所有未包含在组函数中的列都应该包含在 group by 子句中。
 select  department_id, AVG(salary) from     employees group by department_id ;

-- 过滤分组，不能在 where 子句中使用组函数，需要在group by后用having
select   department_id, AVG(salary) from     employees group by department_id having   AVG(salary) > 8000 ;

-- 组函数嵌套
select   trunc(MAX(AVG(salary)),2) as max_avg_salary from   employees group by department_id;

-- 练习，查询公司在1995-1998年之间，每年雇用的人数
select count(*) "total",
       count(decode(to_char(hire_date,'yyyy'),'1995',1,null)) "1995",
       count(decode(to_char(hire_date,'yyyy'),'1996',1,null)) "1996",
       count(decode(to_char(hire_date,'yyyy'),'1997',1,null)) "1997",
       count(decode(to_char(hire_date,'yyyy'),'1998',1,null)) "1998"
from employees
where to_char(hire_date,'yyyy') in ('1995','1996','1997','1998')

5、子查询

-- 子查询不能反回空值

-- 多行比较操作符：in、any、all
-- in和any的区别: any可以用大于小于，a>any(1,2,3)
-- any和all的区别: any满足其一，all满足所有
select employee_id, last_name, job_id, salary
from   employees
where  
	alary < all (select salary= from   employees where  job_id = 'IT_PROG')
and  job_id <> 'IT_PROG';

-- 父查询的字段可以在子查询中用
-- 查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
select employee_id,last_name,salary
from employees e1
where salary > ( select avg(salary) from employees e2 where e1.department_id = e2.department_id);

-- 多列子查询，成对比较
select	employee_id, manager_id, department_id 
from	employees 
where  (manager_id, department_id) IN (SELECT manager_id, department_id FROM   employees WHERE  employee_id IN (141,174)) 
	and	employee_id not in (141,174);

-- From语句使用子查询
-- 例子：返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资
select  a.last_name, a.salary, a.department_id, b.salavg 
from    employees a
inner join 
	(select   department_id, AVG(salary) salavg from     employees group by department_id) b on a.department_id = b.department_id  
where   a.salary > b.salavg;

-- Order语句使用子查询 
select   employee_id, last_name 
from     employees e 
order by  (select department_name from departments d where e.department_id = d.department_id);

-- 相关子查询
select last_name, salary, department_id 
from   employees outer 
where  salary > (SELECT AVG(salary) from   employees where  department_id = outer.department_id);

-- exists子查询
select employee_id, last_name, job_id, department_id 
from   employees outer 
where  exists ( select 'X' from   employees where  manager_id = outer.employee_id);

-- 相关更新
update employees e set    department_name = (select department_name from   departments d where  e.department_id = d.department_id);

-- 相关删除
delete from employees e where employee_id = (select employee_id from   emp_history where  employee_id = e.employee_id);

-- With子句，将该子句中的语句块执行一次并存储到用户的临时表空间中，效率比临时表高
with 
dept_costs  as
( select  d.department_name, sum(e.salary) as dept_total from    employees e, departments d where   e.department_id = d.department_id group by d.department_name),
avg_cost    as 
( select sum(dept_total)/count(*) as dept_avg from   dept_costs) 
select * from   dept_costs where  dept_total > (select dept_avg from avg_cost) 

6、集合运算

-- 并集 union、union all（交集不去重）
select employee_id, job_id from   employees
union
select employee_id, job_id from   job_history;

-- 交集 intersect
select employee_id, job_id from   employees
intersect
select employee_id, job_id from   job_history;

-- 差集 minus (A存在B不存在)
select employee_id, job_id from   employees
minus
select employee_id, job_id from   job_history;	

7、数据处理

-- DML：data Manipulation language -数据操控语言
-- insert从其它表中拷贝数据
insert into  sales_reps(id, name, salary, commission_pct)
	select employee_id, last_name, salary, commission_pct from   employees where  job_id like '%REP%';

-- update关联更新另一个表的数据
-- 写法一：
update tab1 set val=(select max(val) from tab2 where tab1.id=tab2.id)
where exists (select 1 from tab2 where tab1.id=tab2.id);
-- 写法二：内联视图更新
update
(select t1.val as t1val, t2.val t2val from tab1 t1 inner join tab2 t2 on t1.id=t2.id  ) u
set t1val=t2val;

-- 数据库事务以第一个DML语句的执行作为开始，结束条件为：commit/rollback/DDL语句/DCL语句/会话正常结束/会话异常终止/

-- savepoint 创建事务保存点，rollback to 回滚到保存点，提交后自动释放保存点
delete from employees;
savepoint delete_done;
insert into employees where employees_id=1000;
rollback to delete_done;

-- 用户提交事务前，其他用户看不到当前用户做的改变，事务中被DML语句所涉及到的行被锁定，其他用户无法其进行DML操作。

Q.E.D.