数据库 — Oracle_DQL语句2（DECODE函数、排序函数、集合操作、视图、序列、索引、约束）

文章目录

• 四、高级查询
• • 4.1 DECODE函数
  • 4.2 排序函数
  • • 4.2.1 row_number()：
    • 4.2.2 rank()
    • 4.2.3 dense_rank()
  • 4.3 集合操作（查询结果的并集、交集、差集）
  • • 4.3.1 并集 — union 和 union all
    • 4.3.2 交集 — intersect
    • 4.3.3 差集 — minus
• 五、视图、序列、索引
• • 5.1 视图
  • 5.2 序列
  • 5.3 索引
• 六、约束
• • 6.1 只读约束
  • 6.2 检查约束
  • 6.3 主键约束
  • 6.4 唯一约束
  • 6.5 外键约束
• 七、数据库相关可能会出的面试题

四、高级查询

• 上文的子查询和分页查询都属于高级查询，因其重要性，单独分章介绍。

4.1 DECODE函数

• DECODE

  函数是用来实现IF-ELSE的逻辑功能

-- 年终奖计算公式，不同职位的员工年终奖不同，如下

/*CLERK     sal
SALESMAN    sal * 2
PRESIDENT   sal * 1.5
MANAGER     sal * 3
ANALYST     sal * 1.2
*/

-- 查询每个员工以及他们的年终奖金额
select 
    ename, job, sal,
    DECODE(job,             -- if要判断的字段
        'ANALYST', sal * 1.2,   -- 条件（字段内的语句都用，隔开）
        'MANAGER', sal * 3,
        'PRESIDENT', sal * 1.5,
        'SALESMAN', sal * 2,
        sal                     -- 默认
    ) yearcomm
from emp;
           

• 与

  DECODE

  函数功能相同的是

  case...when...else...end

  语句

select 
    ename, job, sal,
    case job          -- if要判断的字段
    when 'ANALYST' then sal * 1.2   -- 条件(字段均以空格隔开)
    when  'MANAGER' then sal * 3
    when   'PRESIDENT' then sal * 1.5
    when    'SALESMAN' then sal * 2
    else sal
    end  yearcomm
from emp;
           

4.2 排序函数

4.2.1 row_number()：

• 值相同，仍然排序，即顺序编号在组内连续且唯一。如：若两个数据均为1200，编号分别n与n+1。

-- 按照部门编码分组显示，每组内按职员编码排序，并赋予组内编码
SELECT deptno, ename, empno,
	ROW_NUMBER()
		OVER (PARTITION BY deptno ORDER BY empno) AS emp_id
FROM emp;
-- 以deptno进行分组，并以组内的empno进行排序，并给予emp_id作为组内序号
           

4.2.2 rank()

• 值相同，编号也相同，组内相同数据顺序标号出现重复值。如：如并列第二，则两行数据都标记为2，但下一位将是第4名。

-- 按照部门编码分组，同组内按薪水倒序排序，相同薪水则按奖金数正序排序，并给予组内等级，用Rank_
SELECT deptno, ename, sal, comm,
	RANK() 
		OVER (PARTITION BY deptno ORDER BY sal DESC, comm) "Rank_ID"
FROM emp;
-- 以deptno进行分组，并以组内的empno进行排序，并给予emp_id作为组内序号
           

4.2.3 dense_rank()

• 如果有并列第2，下一个排序将是3，这一点是和

  RANK()

  的

  不同，

  RANK()

  是跳跃排序。

-- ‐‐关联emp和dept表，按照部门编码分组，每组内按照员工薪水排序，列出员工的部门名字、姓名和薪资
SELECT d.dname, e.ename, e.sal,
	DENSE_RANK() 
		OVER (PARTITION BY e.deptno ORDER BY e.sal) AS drank
FROM emp e join dept d one.deptno = d.deptno;
           

4.3 集合操作（查询结果的并集、交集、差集）

4.3.1 并集 — union 和 union all

• 两个查询结果的并集

  1. union

     ：查询结果不去重

  2. union all

     ：查询结果去重

select * from emp where deptno = 10
union --all
select * from emp where mgr = 7839;
-- union all + distinct = union
           

4.3.2 交集 — intersect

• 显示两个查询结果的交集

-- 显示职位是’MANAGER’的员工和薪水大于2500的员工的交集：
SELECT ename, job, sal FROM emp
	WHERE job = 'MANAGER'
INTERSECT 
SELECT ename, job, sal FROM emp
	WHERE sal> 2500;
           

4.3.3 差集 — minus

• 只显示第一个结果集中存在，在第二个结果集不存在的数据。

-- 列出职位是MANAGER但薪水低于2500的员工记录：
SELECT ename, job, sal FROM emp
	WHERE job = 'MANAGER'
MINUS SELECT ename, job, sal FROM emp
	WHERE sal> 2500;
           

五、视图、序列、索引

5.1 视图

• 视图是基于原表的临时表，用于存储一条

  select

  语句的结果集。当原表数据发生改变时，视图的数据也发生改变
• 若想反复查询同一条

  select

  的结果，可以创建视图来提高查询效率。

create view emp_10
as
select * from emp where deptno = 10;



-- 修改视图数据，也会修改原表数据(建议不在视图中修改数据)
update emp_10 set comm = 10;

-- 原表和视图数据是共享的
update emp set comm = null where deptno = 10;

-- 视图不允许增删数据
delete from emp_10 where ename = 'KING';
           

• 和视图有关的数据字典：

  1. user_objects

  2. user_views

  3. user_tables

  select * from user_objects where object_type='VIEW';
  select * from user_objects where object_type='TABLE';
             

5.2 序列

• 序列：用来生成唯一数字值的数据库对象
• 序列中有两个伪列：

  • NEXTVAL

    ：获取序列的下个值

  • CURRVAL

    ：获取序列的当前值
  • 当序列创建以后，必须先执行一次

    NEXTVAL

    ，之后才能使用

    CURRVAL

-- 创建序列的语句
/* CREATE SEQUENCE [schema.]sequence_name
[ START WITH i ] [ INCREMENT BY j ]
[ MAXVALUE m | NOMAXVALUE ]
[ MINVALUE n | NOMINVALUE ]
[ CYCLE | NOCYCLE ][ CACHE p | NOCACHE ]
*/

-- 获取序列的第一个值，并且使用序列值为EMP表插入新的记录：
SELECT emp_seq.NEXTVAL FROM DUAL;
INSERT INTO emp(empno, ename)
VALUES(emp_seq.NEXTVAL, 'donna');
-- 查询刚刚生成的记录，主键值将是110：
SELECT empno, ename FROM emp
WHERE ename = 'DONNA';
-- 此时查询序列的当前值，会得到110的数字。
SELECT emp_seq.CURRVAL FROM DUAL;
-- 在序列的使用过程中，比如执行了一条语句，则浪费了一个序列值，会导致表的主键值不连续。
SELECT emp_seq.NEXTVAL FROM DUAL;

-- 删除序列
DROP SEQUENCE sequence_name;
           

5.3 索引

• 索引是一种允许直接访问数据表中某一数据行的树型结构，为了提高查询效

  率而引入，是独立于表的对象

-- 在EMP表的ENAME列上建立索引：
CREATE INDEX idx_emp_ename ON emp(ename);

-- 复合索引也叫多列索引，是基于多个列的索引。如果经常在ORDER BY子句中使用job和salary作为排序
CREATE INDEX idx_emp_job_sal ON emp(job, sal);

-- 当做下面的查询时，会自动应用索引idx_emp_job_sal
SELECT empno, ename, sal, job FROM emp
ORDER BY job, sal;
           

六、约束

6.1 只读约束

• 只读约束：保证视图对应的基表数据不会被非法修改

-- with READ ONLY; --添加只读选项
create view emp_10
as
select * from emp where deptno = 10
with read only;
           

6.2 检查约束

• 检查约束：通过视图所做的修改，必须在视图的可见范围内：
  1. 假设

     INSERT

     ，新增的记录在视图仍可查看
  2. 假设

     UPDATE

     ，修改后的结果必须能通过视图查看到
  3. 假设

     DELETE

     ，只能删除现有视图里能查到的记录

-- 创建带有CHECK OPTION约束的视图：
CREATE OR REPLACE VIEW v_emp_10
AS
SELECT empno id, ename name, sal salary, deptno
FROM emp
	WHERE deptno = 10
WITH CHECK OPTION;

-- 下述DML语句操作失败，因为部门20不在视图可见范围内：
INSERT INTO v_emp_10 VALUES(1008,‘donna’,5500, 20);
UPDATE v_emp_10 SET deptno = 20 WHERE id = 7782;
           

6.3 主键约束

• 主键约束：

  Primary key

  ——非空+唯一

-- 创建表格时直接加入
create table user_hh (
    id number(6) primary key,  -- 建表的时候直接规定约束
    name varchar2(20) not null unique,
    score number(4,2) ,
    classname varchar2(20)
);

-- 修改原表结构，添加主键约束(add constraint 约束名)
alter table user_hh
    add constraint pk_user_hh primary key (id);
           

6.4 唯一约束

• 唯一约束：

  unique

  ——字段内容不能重复

6.5 外键约束

• 外键约束：常用于多对多关系的表，需要中间表辅助查询。

-- user表和order表是多对多关系，通过关联id进行匹配
alter table order_hh --子表order_hh的userid
    add constraint user_order_fk_hh 
    	foreign key (userid)
    		references user_hh(id);-- 关联父表user_hh的id
-- 父表中被子表引用的数据，被引用的id不能改，也不能删
-- 想要删除父表数据，必须确保该数据没有被子表引用
-- 结论：外键关系由业务代码维护的
           

七、数据库相关可能会出的面试题

1. 视图作用是什么？好处有哪些？
2. 索引的必要性有哪些？怎么合理添加索引？索引的优势？
3. 索引的原理
4. 事务会出现哪些并发性问题？怎么解决? - 事务的隔离级别
   • 事务并发锁：行级、表级

（后续补充~）