文章目录
四、高级查询
- 上文的子查询和分页查询都属于高级查询,因其重要性,单独分章介绍。
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
- 两个查询结果的并集
union
:查询结果不去重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';
-
和视图有关的数据字典:
user_objects
user_views
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 检查约束
- 检查约束:通过视图所做的修改,必须在视图的可见范围内:
- 假设
INSERT
,新增的记录在视图仍可查看 - 假设
UPDATE
,修改后的结果必须能通过视图查看到 - 假设
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不能改,也不能删
-- 想要删除父表数据,必须确保该数据没有被子表引用
-- 结论:外键关系由业务代码维护的
七、数据库相关可能会出的面试题
- 视图作用是什么?好处有哪些?
- 索引的必要性有哪些?怎么合理添加索引?索引的优势?
- 索引的原理
- 事务会出现哪些并发性问题?怎么解决? - 事务的隔离级别
- 事务并发锁:行级、表级
(后续补充~)