子查询
-- 子查询
-- 单行单列子查询:子查询返回的结果只有一行
select ename, sal from scott.emp
where sal = (select max(sal) from scott.emp);
-- 多行单列子查询:子查询返回的结果有多行
select empno, job, sal, deptno
from scott.emp
where deptno in(
select deptno from scott.emp where job = 'MANAGER'
) and job <> 'MANAGER';
-- 多行多行子查询:子查询之后需要继续查询,将子查询作为“临时表”
select e.ename, e.deptno, e.sal, t.avg_sal
from scott.emp e, (select deptno, round(avg(sal), 2) as avg_sal from scott.emp group by deptno) t
where e.deptno = t.deptno and e.sal < t.avg_sal
order by e.deptno
-- 分页查询
-- 伪列rownum
select rownum, ename, job, sal
from scott.emp
-- 子查询分页
select t.num, t.ename, t.sal
from (
select rownum as num, ename, job, sal
from scott.emp
-- order by sal
) t
where t.num between 10 and 20;
-- 分页
select t.num, t.ename, t.sal
from (
select rownum as num, t1.*
from (
select empno, ename, sal from scott.emp order by sal desc
) t1
) t
where t.num between 1 and 10;
decode函数
-- decode函数:decode(判断条件 , 匹配 1 , 值 1 , 匹配 2 , 值 2 , … , 默认值)
-- case 判断条件 when 匹配1 then 值1 when 匹配2 then 值2...else 默认值 end。类似if-else if-else
select ename, job, sal, deptno, (
case deptno
when 10 then sal * 1.5
when 20 then sal * 2
when 30 then sal * 4
else sal
end
) as sum_sal
from scott.emp
order by sum_sal desc;
-- decode类似switch...case...default。mysql 没有decode函数
select decode(e.job, 'ANALYST', 'HIGH_MANA','SALESMAN','SALCOUNT', 'MANAGER', 'HIGH_MANA', 'OPERATION') job,
count(*) as job_cnt
from scott.emp e
group by decode(e.job, 'ANALYST', 'HIGH_MANA','SALESMAN','SALCOUNT', 'MANAGER', 'HIGH_MANA', 'OPERATION')
order by job_cnt desc, job desc;
排序函数
-- ROW_NUMBER() OVER( PARTITION BY col1 ORDER BY col2)。
-- 表示根据col1分组,在分组内部根据col2排序。该函数计算的值就是每组内部排序后的顺序编号,组内连续且唯 一。
select deptno, ename, empno, row_number() over(partition by deptno order by empno) as emp_id
from scott.emp;
-- RANK() OVER(PARTITION BY col1 ORDER BY col2)
-- 表示根据col1分组,在分组内部根据col2排名,相同的数据返回相同排名。特点是跳跃排序。
select deptno, ename, sal, rank() over(partition by deptno order by sal desc) as emp_id
from scott.emp;
-- DENSE_RANK() OVER(PARTITION BY col1 ORDER BY col2)
-- 与RANK()相似,不同点是DENSE_RANK()是连续排序
select deptno, ename, sal, dense_rank() over(partition by deptno order by sal desc) as emp_id
from scott.emp;
集合
当列的个数、列的顺序、列的数据类型一致时,称这两个结果集结构相同。只有结构相同的结果集才能做 集合操作
-- 并 union、union all(两个结果合并)
select empno, ename, hiredate, sal
from scott.emp
where hiredate > to_date('1985-1-1', 'yyyy-mm-dd')
union all
select empno, ename, hiredate, sal
from scott.emp
where sal >= 3000;
两者区别:
UNION 操作符会自动去掉合并后的重复记录,并对查询结果第一列升序排序
UNION ALL 返回两个结果集中的所有行,包括重复的行,并且对查询结果不排序
-- 交 intersect(取 相同的结果集)
select ename, deptno, sal
from scott.emp
where deptno = 20
intersect
select ename, deptno, sal
from scott.emp
where sal > 2500;
-- 差 minus(取 第一个结果集除去两个结果集共有的)
select ename, deptno, sal
from scott.emp
where deptno = 20
minus
select ename, deptno, sal
from scott.emp
where sal > 2500;
select ename, deptno, sal
from scott.emp
where sal > 2500
minus
select ename, deptno, sal
from scott.emp
where deptno = 20;
第一个语句结果
第二个语句结果
事务
-- 事务
-- 回滚(rollback)是在没有提交事务(commit)之前
-- 在命令行测试以下用例,同时开两个窗口,一个创建表和插入数据,
-- 另一个验证未提交事务和提交后的情况
-- =================================== 创建 ================================== --
SQL> create table A(id number(8));
表已创建。
SQL> insert into A values(666);
已创建 1 行。
SQL> savepoint one;
保存点已创建。
SQL> insert into A values(888);
已创建 1 行。
SQL> savepoint two;
保存点已创建。
SQL> insert into A values(1314);
已创建 1 行。
SQL> savepoint three;
保存点已创建。
SQL> insert into A values(438);
已创建 1 行。
SQL> rollback to three;
回退已完成。
SQL> commit;
提交完成。
SQL>
-- =================================== 测试 ================================== --
SQL> desc A;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
ID NUMBER(8)
SQL> select id from A;
未选定行
SQL> select id from A;
未选定行
SQL> select id from A;
ID
----------
666
888
1314
SQL>
-- 备注:savepoint three是设置保存点名称,用于指定回滚位置(如rollback to three),
-- 若是直接rollback,不管是否设置保存点,全部回滚
-- 一切回滚皆是在提交之前才有效