Oracle 语法总结 (五)

柔光的暖阳◎ 2021-09-20 18:18 275阅读 0赞

### 一，知识点 ###

视图  
是数据库的对象之一，在SQL一句中体现和表一样，但是他不是一张真实存在的表，只是对应表的结果集  
授予权限  
\--1  
GRANT CREATE VIEW TO liwc  
创建简单视图  
\--2  
CREATE VIEW v\_emp\_10 AS SELECT empno,ename,sal,deptno FROM emp\_liwc WHERE deptno = 10  
查看视图结构  
\--3  
DESC v\_emp\_10  
查看视图数据  
\--4  
SELECT \* FROM v\_emp\_10  
删除视图  
\--5  
DROP VIEW v\_emp\_10  
\--6  
当视图对应的子查询中有函数或者表达式时，必须指定别名  
CREATE VIEW v\_emp\_10 AS SELECT empno id,ename,sal\*12 sal,deptno FROM emp\_liwc WHERE deptno = 10  
视图对应的子查询分为：简单视图，复杂视图，连接视图  
1对应的子查询不含有函数，表达式，去重，分组  
2不是简单视图的都是复杂视图  
3对应的子查询中有多个表关联  
简单视图可以进行DML操作，但是复杂视图不可以  
当视图对应的子查询中有函数或者表达式时，必须指定别名  
\--  
修改视图：视图的结果取决于对应的子查询，随意修改视图就是就是替换对应的子查询  
\--7  
CREATE OR REPLACE VIEW v\_emp\_10 AS SELECT empno id,ename name,sal,deptno FROM emp\_liwc WHERE deptno = 10  
\--8  
对视图进行DML操作时，就是对基础表操作  
虽然可以操作但是也不能违反基表约束(no null:看不见的默认为null)  
INSERT INTO v\_emp\_10  
(id,name,sal,deptno)  
VALUES  
(1001,'JACK',2000,10)

SELECT \* FROM v\_emp\_10  
SELECT \* FROM emp\_liwc

UPDATE v\_emp\_10 SET sal = 3000 WHERE id = 1001

DELETE FROM v\_emp\_10 WHERE id = 1001

对简单视图进行DML操作后会污染及基础表的数据  
，操作简单视图会对基础表进行响应的操作，这些过程都是不可见的  
就是对数据的污染  
删除不存在数据污染的情况  
\--9  
为视图添加检查选项可以避免产生数据污染  
要求DML操作后该记录视图对其可见，否则不允许操作  
WITH CHECK OPTION   
\--  
CREATE OR REPLACE VIEW v\_emp\_10 AS SELECT empno id ,ename name,sal,deptno FROM emp\_liwc WHERE deptno = 10  
WITH CHECK OPTION   
SQL 错误: ORA-01402: 视图 WITH CHECK OPTION where 子句违规  
INSERT INTO v\_emp\_10  
(id,name,sal,deptno)  
VALUES  
(1001,'JACK',2000,20)  
\--  
对视图进行添加只读选项，不允许进行DML操作  
WITH READ ONLY   
\--  
CREATE OR REPLACE VIEW v\_emp\_10 AS SELECT empno id ,ename name,sal,deptno FROM emp\_liwc WHERE deptno = 10  
WITH READ ONLY   
SQL 错误: ORA-42399: 无法对只读视图执行 DML 操作  
INSERT INTO v\_emp\_10  
(id,name,sal,deptno)  
VALUES  
(1001,'JACK',2000,10)  
\--10  
数据字典  
是一系列的表，这些表的数据数据库自行维护  
,记录的是一些清单信息方便随时查看  
USER\_OBJECTS:记录用户创建的虽有数据库库对象  
USER\_VIEWS：记录用户创建的所有视图  
USER\_TABLES：记录用户创建的所有的表  
SELECT \* FROM USER\_OBJECTS WHERE object\_type = 'VIEW' AND object\_name LIKE 'V\_EMP\_10'  
SELECT \* FROM USER\_OBJECTS WHERE object\_type = 'VIEW' AND object\_name LIKE 'V\_EMP\_10'

SELECT view\_name,text FROM USER\_VIEWS WHERE view\_name like ''  
SELECT \* FROM USER\_TABLES  
\--11  
创建一个含有各个部门工资的视图  
CREATE OR REPLACE VIEW v\_emp\_dept\_sal AS SELECT   
MAX(e.sal)max\_sal,  
MIN(e.sal)min\_sal,  
AVG(e.sal)avg\_sal,  
SUM(e.sal)sum\_sal,  
d.deptno,d.dname  
FROM emp\_liwc e JOIN dept\_liwc d  
ON e.deptno = d.deptno  
GROUP BY d.deptno,dname

SELECT \* FROM v\_emp\_dept\_sal  
SELECT \* FROM emp\_liwc

查看高于所在部门的平均工资员工  
SELECT e.ename,e.sal FROM emp\_liwc e,v\_emp\_dept\_sal t WHERE e.deptno = t.deptno AND e.sal>t.avg\_sal  
\--12  
序列：是数据库的对象之一，作用是根据指定的规则生成一些列数字，通常为某张表关键字段提供使用  
主键：通常每张表都有主键字段，该字段要求非空且唯一，使用该字段确定每张表中每条记录使用  
创建序列  
CREATE SEQUENCE seq\_emp\_id   
START WITH 1  
INCREMENT BY 1  
删除序列  
DROP SEQUENCE seq\_emp\_id   
SQL 错误: ORA-02201: 此处不允许序列  
DELETE FROM seq\_emp\_id  
序列提供了两个伪列：  
NEXTVAL:获取序列的下一个数字，第一次获取返回的是start  
WITH:指定的数字，以后则是最后获取的数字加上步涨的数字得到的  
NEXTVAL会导致序列发生部进，且不可回退

CURRVAL:获取序列的当前数字，即最后一次生成的数字，新创建的序列至少调用一次NEXTVAL才  
可以使用，CURRVAL不会发生步进  
\--  
ORA-08002: 序列 SEQ\_EMP\_ID.CURRVAL 尚未在此会话中定义  
SELECT seq\_emp\_id.CURRVAL FROM dual  
\--  
SELECT seq\_emp\_id.NEXTVAL FROM dual

INSERT INTO emp\_liwc  
(empno,ename,sal,job,deptno)  
VALUES  
(seq\_emp\_id.NEXTVAL,'JACK',3000,'CLERK',10)  
\--13  
索引  
怎么去添加索引（like不适用索引）  
索引是数据库的对象之一， 作用是加快查询效率，创建和使用是数据库自行完成的  
经常出现在WHERE字段中，或者去重，排序，关联条件的字段中  
创建索引  
CREATE INDEX inx\_emp\_ename ON emp\_liwc(ename)  
复合索引也叫多列索引，基于多个列的索引  
CREATE INDEX inx\_emp\_job\_sal ON emp\_liwc(job,sal)  
CREATE INDEX emp\_upper\_idx ON emp\_liwc(UPPER(ename))  
在使用增加效率  
SELECT \* FROM emp\_liwc WHERE UPPER(ename) = 'KING'  
\--  
修改和删除索引  
重建索引emp\_upper\_idx  
ALTER INDEX emp\_upper\_idx REBUILD  
删除索引  
DROP INDEX emp\_upper\_idx  
原则  
小表不建立索引  
关键词后字段顺序一致  
增删多的不建索引  
\--12  
约束概述  
约束条件  
\--  
非空约束：  
CREATE TABLE employees(  
eid NUMBER(6),  
name VARCHAR2(30) NOT NULL,  
salary NUMBER(7,2),  
hiredate DATE CONSTRAINT employees\_hiredate\_nn NOT NULL  
)  
修改表时添加  
ALTER TABLE employees MODIFY (eid NUMBER(6) NOT NULL)  
删除  
ALTER TABLE employees MODIFY (name VARCHAR2(30) NULL)  
\--  
唯一性约束  
要求改字段在整张表中每条记录的值都不重复null除外  
CREATE TABLE employees1(  
eid NUMBER(6) UNIQUE,  
name VARCHAR2(30),  
email VARCHAR2(50),  
salary NUMBER(7,2),  
hiredate DATE,--逗号勿忘，前者没有（not null）  
CONSTRAINT employees\_email\_nk UNIQUE(email)  
)  
1 行已插入。  
INSERT INTO employees1  
(eid,name,email)  
VALUES  
(10,'LIWC','lwc.gmail.com')  
SQL 错误: ORA-00001: 违反唯一约束条件 (LIWC.SYS\_C007021)  
INSERT INTO employees1  
(eid,name,email)  
VALUES  
(10,'LIWC','lwc.gmail.com')  
1 行已插入。  
INSERT INTO employees1  
(eid,name,email)  
VALUES  
(11,'LIWC','lwc.163.com')  
1 行已插入。  
INSERT INTO employees1  
(eid,name,email)  
VALUES  
(12,'LIWC',NULL)  
\--  
修改表时添加  
ALTER TABLE employees1 ADD CONSTRAINT employees\_name\_nk UNIQUE(name)

删除？？？？？？？？？？？？？？  
\--  
添加主键约束(非空且唯一)  
CREATE TABLE employees2(  
eid NUMBER(6) PRIMARY KEY,  
name VARCHAR2(30),  
email VARCHAR2(50),  
salary NUMBER(7,2),  
hiredate DATE  
)  
SQL 错误: ORA-01400: 无法将 NULL 插入 ("LIWC"."EMPLOYEES2"."EID")  
INSERT INTO employees2  
(eid)  
VALUES  
(NULL)  
\--  
外键约束  
\--不用  
检查约束  
员工的薪水必须大于两千  
ALTER TABLE employees2 ADD CONSTRAINT employees2\_salary\_check CHECK(salary>2000)  
SQL 错误: ORA-02290: 违反检查约束条件 (LIWC.EMPLOYEES2\_SALARY\_CHECK)  
INSERT INTO employees2   
(eid,salary)  
VALUES  
(16,202)

DELETE FROM employees1  
DESC employees2

### 二，例子 ###

1:创建一个视图，包含20号部门的员工信息，字段:empno,ename,sal,job,deptno  
CREATE VIEW v\_emp\_20 AS  
SELECT empno,ename,sal,job,deptno FROM emp\_liwc WHERE deptno = 20  
2:创建一个序列seq\_emp\_no,从10开始，步进为10  
CREATE SEQUENCE seq\_emp\_no   
START WITH 10  
INCREMENT BY 10  
3:编写SQL语句查看seq\_emp\_no序列的下一个数字  
SELECT seq\_emp\_no.NEXTVAL FROM dual  
4:编写SQL语句查看seq\_emp\_no序列的当前数字  
SELECT seq\_emp\_no.CURRVAL FROM dual  
5:为emp表的ename字段添加索引:idx\_emp\_ename   
CREATE INDEX idx\_emp\_no ON emp\_liwc(ename)  
6:为emp表的LOWER(ename)字段添加索引:idx\_emp\_lower\_ename  
CREATE INDEX emp\_lower\_idx ON emp\_liwc(LOWER(ename))  
7:为emp表的sal,comm添加多列索引  
CREATE INDEX inx\_emp\_job\_sal ON emp\_liwc(sal,comm)  
8:创建myemployee表，字段:  
  id NUMBER(4) ,  
  nameVARCHAR2(20),  
  birthday DATE,  
  telephone VARCHAR2(11),  
  scoreNUMBER(9,2)  
  其中id作为主键，name要求不能为空,telephone需要唯一,score值必须>=0--1  
CREATE TABLE myemployee(  
  id NUMBER(4)PRIMARY KEY,  
  name VARCHAR2(20)NOT NULL,  
  birthday DATE,  
  telephone VARCHAR2(11)UNIQUE,  
  score NUMBER(9,2)  
);  
ALTER TABLE myemployee ADD CONSTRAINT myemployee\_salary\_check CHECK(score>=0)