--视图VIEW
--视图是数据库对象之一,在SQL语句中体现的角色与表一致
--但是视图并非一张真实存在的表,它只是一个查询语句对应
--的结果集。
CREATE VIEW v_emp_10_gl
AS
SELECT empno,ename,sal,deptno
FROM emp
WHERE deptno=10
DESC v_emp_10_gl --查看视图结构
SELECT *
FROM v_emp_10_gl
--视图对应的子查询中的字段可以指定别名,
--这样该试图对应的字段名就是这个别名,
--当一个字段是函数或者表达式,那么该字
--段必须指定别名
CREATE OR REPLACE VIEW v_emp_10_gl
AS
SELECT empno id,ename name,sal salary,deptno
FROM emp_gl
WHERE deptno=10
--视图根据对应的子查询不同,分为简单视图和复杂视图
--简单视图:对应的子查询不含有函数,表达式,分组,去重,关联查询
--除了简单视图就是复杂视图
--简单视图可以进行DML操作,对该视图的操作就是对该试图
--数据来源的基础表进行的操作,复杂视图不允许进行DML操作
--对简单视图进行DML操作也不能违反基础表的约束条件。
--读视图进行DML操作,视图对基础表操作时,只能读视图可见
--的字段进行。
INSERT INTO v_emp_10_gl
(id,name,salary,deptno)
VALUES
(1001,'JACK',3000,10)
SELECT * FROM v_emp_10_gl
SELECT * FROM emp_gl
UPDATE v_emp_10_gl
SET salary=4000
WHERE id=1001
DELETE FROM v_emp_10_gl WHERE name='JACK'
--对视图的不当DML操作会污染基础表数据
--即:对试图进行DML操作后,试图对基础表对应数据
--进行该DML操作,但是操作后视图却对该记录不可见
INSERT INTO v_emp_10_gl
(id,name,salary,deptno)
VALUES
(1001,'JACK',3000,20)
SELECT * FROM emp_gl
SELECT * FROM v_emp_10_gl
UPDATE v_emp_10_gl
SET deptno =20
--DELETE 不会产生污染现象
DELETE FROM v_emp_10_gl
WHERE deptno=20
--为视图添加检查选项,可以避免对试图操作而导致
--的对基表的数据污染。
--WITH CHECK OPTION
--该选项要求对视图进行DML操作后,该记录必须对试图可见。
CREATE OR REPLACE VIEW v_emp_10_gl
AS
SELECT empno id,ename name,sal salary,deptno
FROM emp_gl
WHERE deptno=10
WITH CHECK OPTION
--只读选项
--WITH READ ONLY
--只读选项要求对试图仅能进行查询操作
--不能进行任何DML操作
CREATE OR REPLACE VIEW v_emp_10_gl
AS
SELECT empno id,ename name,sal salary,deptno
FROM emp_gl
WHERE deptno=10
WITH READ ONLY
--常用的数据库的数据字典
--USER_OBJECTS:记录用户创建过的所有对象
SELECT object_name,object_type FROM user_objects
WHERE object_type='VIEW'
AND object_name LIKE '%GL'
SELECT * FROM user_objects
WHERE object_name LIKE '%GL'
--USER_VIEWS:专门记录曾经创建过的试图信息
SELECT view_name,text
FROM user_views
WHERE view_name LIKE '%GL'
--USER_TABLES:专门记录曾经创建过的表的信息
SELECT table_name
FROM user_tables
WHERE table_name LIKE '%GL'
--删除视图
DROP VIEW v_emp_10_gl
--创建复杂视图
--创建一张试图,包含员工工资及相关部门信息
--包含:每个部门的平均工资,最大,最小,工资总和
--以及对应的部门名称,部门编号。
CREATE VIEW v_emp_salinfo_gl
AS
SELECT ROUND(AVG(e.sal),2) avg_sal,MAX(e.sal) max_sal,
MIN(e.sal) min_sal,SUM(e.sal) total,
e.deptno deptno,d.dname name,d.loc loc
FROM emp_gl e JOIN dept_gl d
ON e.deptno=d.deptno
GROUP BY e.deptno,d.dname,d.loc
--查看哪些员工的工资高于其所在部门平均工资?
SELECT e.ename,e.sal,e.deptno
FROM emp_gl e,v_emp_salinfo_gl v
WHERE e.deptno=v.deptno
AND e.sal>v.avg_sal
--序列SEQUENCE
--序列是数据库对象之一,作用是根据指定的规则生成
--一系列数字。通常使用序列生成的数字是为表中的
--主键字段提供值使用。
CREATE SEQUENCE seq_emp_id_gl
START WITH 1
INCREMENT BY 1
--序列支持两个伪列:
--NEXTVAL:获取序列的下一个数字,如果是新创建的序列
--那么会从START WITH开始返回。之后则是用上次生成的
--数字加上步长来得到本次生成的数字返回。
--需要注意:序列是不能后退的。并且不受事务控制。
SELECT seq_emp_id_gl.NEXTVAL
FROM dual
--CURRVAL:获取序列最后生成的数字,新创建的序列至少
--调用NEXTVAL生成一个数字后才可以使用。CURRVAL不会
--导致序列步进。
SELECT seq_emp_id_gl.CURRVAL
FROM dual
--使用序列为EMP表主键字段提供值:
INSERT INTO emp_gl
(empno,ename,job,sal,deptno)
VALUES
(seq_emp_id_gl.NEXTVAL,'JACK','CLERK',3000,10)
SELECT * FROM emp_gl
--删除序列
DROP SEQUENCE seq_emp_id_gl
--序列的数据字典
SELECT * FROM user_sequences
--INDEX 索引
--索引是数据库对象之一,作用是提高查询效率
--索引的创建时是数据库自行完成的,并且数据库
--会在适当的时候自动使用索引。
CREATE INDEX idx_emp_ename_gl
ON emp_gl(ename)
--经常出现在WHERE 中和ORDER BY中的字段要添加索引
--经常出现在DISTINGCT后面的字段也可以添加索引
--需要注意:对于字符串类型字段,若在WHERE中使用LIKE
--进行过滤时,是不会用到索引的。
CREATE INDEX idx_emp_job_sal_gl
ON emp_gl(job,sal)
CREATE INDEX idx_ename_upper_gl
ON emp_gl(UPPER(ename))
--重建索引
ALTER INDEX idx_emp_ename_gl REBUILD
--删除索引
DROP INDEX idx_emp_ename_gl
--建立非空约束
CREATE TABLE employees_gl(
eid NUMBER(6),
name VARCHAR2(30) NOT NULL,
salary NUMBER(7,2),
hiredate DATE
CONSTRAINT employees_gl_hiredate_nn NOT NULL
)
SELECT * FROM user_constraints
WHERE constraint_name='EMPLOYEES_GL_HIREDATE_NN'
DESC employees_gl
--修改时添加非空约束
ALTER TABLE employees_gl
MODIFY (eid NUMBER(6) NOT NULL)
--删除非空约束
ALTER TABLE employees_gl
MODIFY (eid NUMBER(6)NULL)
--添加唯一性约束
CREATE TABLE employees2_gl(
eid NUMBER(6)UNIQUE,
name VARCHAR2(30),
email VARCHAR2(50),
salary NUMBER(7,2),
hiredate DATE,
CONSTRAINT employess2_gl_email_uk UNIQUE(email)
)
DESC employees2_gl
SELECT * FROM user_constraints
WHERE table_name ='EMPLOYEES2_GL'
INSERT INTO employees2_gl
(eid,name,email)
VALUES
(NULL,'JACK',NULL)
SELECT * FROM employees2_gl
DELETE FROM employees2_gl
--向已建表中添加唯一性约束
ALTER TABLE employees2_gl
ADD
CONSTRAINT employees2_gl_name_uk UNIQUE(name)
--建立主键约束
--一张表只能在一个字段定义主键约束,主键约束要求该字段非空且唯一
CREATE TABLE employees3_gl(
eid NUMBER(6) PRIMARY KEY,
name VARCHAR2(30),
email VARCHAR2(50),
salary NUMBER(7,2),
hiredate DATE
)
INSERT INTO employees3_gl
(eid,name,email)
VALUES
(1,'JACK','JACK@TEDU.CN')
--添加检查约束
ALTER TABLE employees3_gl
ADD
CONSTRAINT employees3_gl_salary_check CHECK(salary>2000)
INSERT INTO employees3_gl
(eid,name,salary)
VALUES
(2,'NOAH',2500)
SELECT * FROM employees3_gl--1:创建一个视图,包含20号部门的员工信息,
-- 字段:empno,ename,sal,job,deptno
CREATE OR REPLACE VIEW v_emp_gl_20
AS
SELECT empno,ename,sal,job,deptno
FROM emp_gl
WHERE deptno=20;
SELECT * FROM v_emp_gl_20;
--2:创建一个序列seq_emp_no,从10开始,步进为10
CREATE SEQUENCE seq_emp_gl_no
START WITH 10
INCREMENT BY 10;
--3:编写SQL语句查看seq_emp_no序列的下一个数字
SELECT seq_emp_gl_no.NEXTVAL
FROM dual;
--4:编写SQL语句查看seq_emp_no序列的当前数字
SELECT seq_emp_gl_no.CURRVAL
FROM dual;
--5:为emp表的ename字段添加索引:idx_emp_ename
CREATE INDEX idx_emp_gl_ename
ON emp_gl(ename);
--6:为emp表的LOWER(ename)字段添加索引:idx_emp_lower_ename
CREATE INDEX idx_emp_gl_lower_ename
ON emp_gl(LOWER(ename));
--7:为emp表的sal,comm添加多列索引
CREATE INDEX idx_emp_gl_sal_comm
ON emp_gl(sal,comm);
--8:创建myemployee表,字段:
-- id NUMBER(4) ,
-- nameVARCHAR2(20),
-- birthday DATE,
-- telephone VARCHAR2(11)
-- scoreNUMBER(9,2)
-- 其中id作为主键,name要求不能为空,telephone需要唯一,score值必须>=0
CREATE TABLE myemployee(
id NUMBER(4),
name VARCHAR2(20) CONSTRAINT myemployee_name_NN NOT NULL,
birthday DATE,
telephone VARCHAR2(11),
score NUMBER(9,2),
CONSTRAINT myemployee_id_pk PRIMARY KEY(id),
CONSTRAINT myemployee_telephone_uk UNIQUE(telephone),
CONSTRAINT myemployee_score_check CHECK(score>=0)
)
SELECT * FROM user_constraints
WHERE table_name='MYEMPLOYEE';
笔记:ORACLE数据库基础学习 第五天
最新推荐文章于 2024-08-15 13:07:49 发布