数据库笔记04

子查询

-- 子查询
	-- 单行单列子查询：子查询返回的结果只有一行
	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，不管是否设置保存点，全部回滚
	--       一切回滚皆是在提交之前才有效