Oracle 语法总结 (五) 柔光的暖阳◎ 2021-09-20 18:18 281阅读 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)
还没有评论，来说两句吧...