Oracle 语法总结 (五)

原创

day_day_up_cium 2021-07-07 13:57:18 ©著作权

文章标签 oracle 文章分类 Oracle 数据库

©著作权归作者所有：来自51CTO博客作者day_day_up_cium的原创作品，请联系作者获取转载授权，否则将追究法律责任

一，知识点

视图
是数据库的对象之一，在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)