视图、索引、权限管理

视图：VIEW

1、视图简介

1）视图是一张虚拟的表，不存储具体的数据

2）视图建立在已有的表的基础上，那这个表称为“基表”

3）不存储具体的数据，数据来源都是SELECT语句，所以理解为视图存储的是SQL语句

作用：

1）简化查询语句

2）权限控制

3）避免重复访问相同的数据

视图的分类

特性 简单视图 复杂视图

表的数量 一个 一个或多个

函数 没有 有

分组 没有 有

DML 操作 可以 有时可以

2、创建视图

       语法：CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
              [(alias[, alias]...)]
             AS subquery
            [WITH CHECK OPTION [CONSTRAINT constraint]]
            [WITH READ ONLY [CONSTRAINT constraint]];
       修改视图结构没有：ALTER，通过 OR REPLACE

3、查询视图数据、添加、修改、删除视图数据

视图中使用DML的规定：

- 当视图定义中包含以下元素之一时不能使用delete:

组函数

GROUP BY 子句

DISTINCT 关键字

ROWNUM 伪列

- 以下元素之一时不能使用update:

组函数

GROUP BY子句

DISTINCT 关键字

ROWNUM 伪列

列的定义为表达式

- 以下元素之一时不能使insert:

组函数

GROUP BY 子句

DISTINCT 关键字

ROWNUM 伪列

列的定义为表达式

表中非空的列在视图定义中未包括

- 屏蔽视图DML操作，不让通过视图进行增删改

WITH READ ONLY

- 删除视图：

DROP VIEW 视图名;

4、TOP-N (ROWNUM)伪列

Top-N 分析查询一个列中最大或最小的 n 个值:

销售量最高的十种产品是什么?

销售量最差的十种产品是什么?

SELECT * FROM (
SELECT  employee_id,last_name,salary,department_id 
FROM employees
WHERE salary>10000); 

-- 简单视图

CREATE VIEW emp_vu AS
SELECT  employee_id,last_name,salary,department_id 
FROM employees
WHERE salary>10000;
SELECT * FROM emp_vu;
DELETE FROM emp_vu WHERE employee_id = 103;
UPDATE employees SET salary = salary+1000 WHERE employee_id = 205;
UPDATE emp_vu SET salary = salary+1000 WHERE employee_id = 205;
SELECT * FROM employees;

-- 复杂视图

CREATE OR REPLACE VIEW emp_dept_vu002 AS
SELECT e.employee_id,e.last_name,e.salary,e.job_id,e.department_id,d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
WHERE e.salary>=10000;
SELECT * FROM emp_dept_vu002;
UPDATE emp_dept_vu002 SET salary = salary+1000 WHERE employee_id = 205;
UPDATE emp_dept_vu002 SET salary = salary+1000 WHERE employee_id = 205;
UPDATE emp_dept_vu002 SET department_name = 'Oracle' WHERE employee_id = 205;
SELECT * FROM employees;
SELECT * FROM departments;

--单表分组

CREATE OR REPLACE VIEW emp_group AS
SELECT department_id,MAX(salary) ma,MIN(salary) mi 
FROM employees
GROUP BY department_id ;
select * from emp_group;
DELETE FROM emp_group WHERE department_id = 100;

-- 表达式

CREATE OR REPLACE VIEW emp_exp 
AS
SELECT employee_id,last_name,salary,salary*12 nn,'男' sex 
FROM employees
WITH READ ONLY;
delete from emp_exp WHERE employee_id = 169;
delete from emp_exp WHERE nn = 120000;
UPDATE emp_exp SET salary = salary+10000 WHERE employee_id = 150;
SELECT * FROM EMP_EXP;

TOP-n

ROWNUM:

1）伪列，虚拟的列

2）在查询结果的上给每一行数据添加一个标示，从1开始

3）ORDER BY 默认是在ROWNUM 后面执行，顺序可能被打乱

解决：所以得先排序，再进行添加ROWNUM

4）伪列总是从1开始计算，不存在大于1，然后没有1的数据

ROWID:

1)记录内容在磁盘中具体的位置

2)帮我们解决删除重复数据问题

*/
-- 需求：查询工资最高的前10位员工信息
SELECT t1.*,ROWNUM FROM (
  SELECT e.*
  FROM employees e
  ORDER BY salary DESC
)t1 WHERE ROWNUM<=20;

-- 需求：查询工资位于5-10位员工信息
SELECT * FROM (
  SELECT t1.*,ROWNUM rn FROM (
    SELECT e.*
    FROM employees e
    ORDER BY salary DESC
  )t1
)t2 WHERE t2.rn BETWEEN 5 AND 10;

SELECT rowid,e.* FROM employees e;

SELECT * FROM student;

INSERT INTO student VALUES(1,'张三丰','男','30-8月-1688',1);
INSERT INTO student VALUES(2,'张三丰','男','30-8月-1688',1);
INSERT INTO student VALUES(3,'张三','男','30-8月-1688',1);
INSERT INTO student VALUES(3,'张三','男','30-8月-1688',1);
-- 需求：删除表中所有重复的记录
SELECT MAX(rowid),s.* 
FROM student s
GROUP BY s.sno,
         s.sname,
         s.ssex,
         s.sbirthday,
         s.class;


SELECT rowid,s.* 
FROM student s
DELETE FROM student 
WHERE rowid NOT IN(
      SELECT MAX(rowid)
      FROM student s
      GROUP BY s.sno,
               s.sname,
               s.ssex,
               s.sbirthday,
               s.class
);

select * from employees;

序列:SEQUENCE

1、简介

1）自动的生成有规律的数值，类似:mysql中的自增 oracle:没有自增

2）主要用于提供主键值

2、序列的语法

       CREATE SEQUENCE sequence
       [INCREMENT BY n]  --每次增长的数值 步长，默认：1
       [START WITH n]    --从哪个值开始         默认：1
       [{MAXVALUE n | NOMAXVALUE}]
       [{MINVALUE n | NOMINVALUE}]
       [{CYCLE | NOCYCLE}]     --是否需要循环
       [{CACHE n | NOCACHE}];  --是否缓存登录

3、相关的属性

NEXTVAL 返回序列中下一个有效的值，任何用户都可以引用

CURRVAL 中存放序列的当前值

注意：

1）NEXTVAL 应在 CURRVAL 之前指定 ，否则会报CURRVAL 尚未在此会话中定义的错误。

2）NEXTVAL每执行一次，都会消耗一个值

*/

CREATE SEQUENCE seq_001;
SELECT seq_001.nextval FROM dual;
SELECT seq_001.currval FROM dual;

-- 主要用于提供主键值

CREATE SEQUENCE seq_002
       START WITH 300
       INCREMENT BY 10;
SELECT seq_002.nextval FROM dual;
SELECT seq_002.currval FROM dual;
SELECT * FROM departments;
insert into departments VALUES(300,'研发部',100,1700);
insert into departments VALUES(300,'研发部',100,1700);
insert into departments VALUES(seq_002.nextval,'研发部',100,1700);
SELECT * FROM student;

-- 查看序列的状态

SELECT * FROM USER_SEQUENCES;

-- 修改序列

ALTER SEQUENCE seq_002
               INCREMENT BY 20
               MAXVALUE 999999
               NOCACHE
               NOCYCLE;
SELECT seq_002.nextval FROM dual;
SELECT seq_002.currval FROM dual;

-- 删除序列

DROP SEQUENCE seq_002;

索引

一、索引简介

1）索引是数据库的一种数据结构，主要目的是为了提高查询速度

二、什么时候创建索引

列中数据值分布范围很广(重复内容少)

列经常在 WHERE 子句或连接条件中出现

表经常被访问而且数据量很大 ，访问的数据大概占数据总量的2%到4%

三、什么时候不要创建索引

表很小

列不经常作为连接条件或出现在WHERE子句中

查询的数据大于2%到4%

表经常更新

四、索引的分类

主键索引：PRIMARY KEY

唯一索引：UNIQUE

普通索引: INDEX

五、单独创建索引的语法

CREATE [UNIQUE] INDEX ind_name ON 表(字段);

六、注意

1）索引已经要出现在WHERE字句中才有效

2）索引不需要我们去调用，数据库会根据我们的SQL语句系统来决定是否使用索引

七、索引失效场景

使用模糊查询 '%10' '10%'

八、删除索引

DROP INDEX 索引名;

*/

create table check_5w as
select rownum as id,
               to_char(sysdate + rownum/24/3600, 'yyyy-mm-dd hh24:mi:ss') as inc_datetime,
               trunc(dbms_random.value(0, 100)) as random_id,
               dbms_random.string('x', 20) random_string
          from dual
        connect by level <= 2800000;
SELECT COUNT(1) FROM check_5w;
SELECT * FROM check_5w;
-- 不创建 索引的效率 
-- 0.165  0.152 0.158
SELECT * FROM check_5w WHERE inc_datetime = '2022-10-05 15:58:21';
CREATE INDEX ind_5w_datetime ON check_5w(inc_datetime);
-- 创建索引的效率 
-- 0.040   0.006 0.008  0.037  0.004
SELECT * FROM check_5w WHERE inc_datetime = '2022-10-06 15:58:21';
CREATE UNIQUE INDEX ind_5w_ID ON check_5w(id);
SELECT * FROM check_5w WHERE id = 1;
-- 选中：F5 执行计划
SELECT * FROM check_5w WHERE inc_datetime LIKE  '2022-09-06 15%';
DROP INDEX ind_5w_ID;

synonym:同义词，给表取别名

    CREATE [PUBLIC] SYNONYM synonym
    FOR    object;

-- 授权

GRANT DBA TO hr;

-- 创建同义词

CREATE PUBLIC SYNONYM e FOR employees;

SELECT * FROM e;

SELECT * FROM employees e;

行列转换

行转列

create table hzlb(
       username varchar2(10),
       subject varchar2(10),
       score number
);
insert into hzlb values('张三','语文',80);
insert into hzlb values('张三','数学',90);
insert into hzlb values('张三','英语',70);
insert into hzlb values('张三','生物',85);
insert into hzlb values('李四','语文',80);
insert into hzlb values('李四','数学',92);
insert into hzlb values('李四','英语',76);
insert into hzlb values('李四','生物',88);
insert into hzlb values('码农','语文',60);
insert into hzlb values('码农','数学',82);
insert into hzlb values('码农','英语',96);
insert into hzlb values('码农','生物',78);
COMMIT;
select * from hzlb;
select 
       username,
       MAX(CASE subject WHEN '数学' THEN score ELSE NULL END) 数学,
       MAX(CASE subject WHEN '语文' THEN score ELSE NULL END) 语文,
       MAX(CASE subject WHEN '英语' THEN score ELSE NULL END) 英语,
       MAX(CASE subject WHEN '生物' THEN score ELSE NULL END) 生物
from hzlb
GROUP BY username;

列转行

create table lzhb(
       username varchar2(10),
       数学 number,
       语文 number,
       英语 number,
       生物 number
);
insert into lzhb values('张三',90,80,70,85);
insert into lzhb values('李四',92,80,76,88);
insert into lzhb values('码农',82,60,96,78);
commit;
SELECT * FROM lzhb;
SELECT z.*,'xx' FROM lzhb z
UNION ALL
SELECT z.*,'xx' FROM lzhb z
SELECT * FROM lzhb;
-- '数学':编写固定的表达式，就是科目字符
-- 数学：建表的时候，表的具体的字段名称，存放是具体科目的成绩
SELECT username,'数学' subject,数学 score FROM lzhb
UNION  
SELECT username,'语文' subject,语文 score FROM lzhb
UNION  
SELECT username,'英语' subject,英语 score FROM lzhb
UNION  
SELECT username,'生物' subject,生物 score FROM lzhb;

权限管理

1、创建用户

CREATE USER user IDENTIFIED BY password;

2、授权：直接授权 授权角色

GRANT 权限|角色 TO 用户1,用户2

3、回收权限

系统表：

数据字典视图描述

ROLE_SYS_PRIVS角色拥有的系统权限

ROLE_TAB_PRIVS角色拥有的对象权限

USER_ROLE_PRIVS用户拥有的角色

USER_TAB_PRIVS_MADE用户分配的关于表对象权限

USER_TAB_PRIVS_RECD用户拥有的关于表对象权限

USER_COL_PRIVS_MADE用户分配的关于列的对象权限

USER_COL_PRIVS_RECD用户拥有的关于列的对象权限

USER_SYS_PRIVS用户拥有的系统权限

CREATE USER HW123 IDENTIFIED BY "123456";

授权具体的权限：CREATE SESSION

GRANT CREATE SESSION TO HW123;
CREATE TABLE xxxxx(
       id number(10),
       username varchar2(20)
);

USER_SYS_PRIVS用户拥有的权限

SELECT * FROM USER_SYS_PRIVS;

USER_ROLE_PRIVS用户拥有的角色

SELECT * FROM USER_ROLE_PRIVS;

ROLE_SYS_PRIVS角色拥有的系统权限

SELECT * FROM ROLE_SYS_PRIVS WHERE role = 'DBA';

SELECT * FROM ROLE_SYS_PRIVS WHERE role = 'RESOURCE';

GRANT RESOURCE TO HW123;

HR：中的所有表对象，没有给HW123授权

SELECT * FROM hr.departments;

授权对象

GRANT select, insert

ON departments

TO HW123;

SELECT * FROM hr.departments;

INSERT INTO hr.departments VALUES(666,'研发部',200,1700);

UPDATE hr.departments SET location_id = 1900 WHERE department_id = 666;

回收权限 REVOKE

REVOKE insert

ON departments

FROM HW123;