Oracle学习-day05

直接写例子

--视图是数据库对象之一，视图在SQL语句中体现的角色与表一致，但并不是表，它只是对应类一个查询语句的结果集。
	CREATE VIEW v_emp_10 AS SELECT empno,ename,sal,deptno FROM emp WHERE deptno=10;
	
--查看视图与查询表一样,两句语句等同
	SELECT * FROM v_emp_10;
	SELECT * FROM (SELECT empno,ename,sal,deptno FROM emp WHERE deptno=10);
	
--查看视图的结构
	DESC v_emp_10;
	
--视图对应的子查询若有函数或者表达式，那么必须给别名，当一个字段使用了别名那么视图中的该字段的名字就是这个别名。
--修改视图，由于视图只是对应一个查询语句，所以修改视图就是替换该SQL语句。(加 OR REPLACE已有该视图则替换，没有则重新创建)
	CREATE OR REPLACE VIEW v_emp_10 AS SELECT empno ID,ename NAME,sal salary,deptno FROM emp WHERE deptno=10;
		
--对视图进行DML操作就是对视图数据来源的基础表进行操作，并且只能对视图可见字段进行
	INSERT INTO v_emp_10 (ID,NAME,salary,deptno) VALUES (1001,'FUCK',50000,10);
	SELECT * FROM v_emp_10;
	SELECT * FROM emp;
	
	UPDATE v_emp_10 SET salary=90000 WHERE ID = 1001;
	DELETE FROM emp WHERE empno =1002;
	
--通过对视图操作的数据，若视图不可见那么等同于对基表数据进行污染。
	INSERT INTO v_emp_10(ID,NAME,salary,deptno)VALUES(1001,'FUCK',5000,20);
	UPDATE v_emp_10 SET deptno=20;
	
--删除表不会对基表有污染
	delete from v_emp_10 where deptno=20;

--为视图添加检查选项可以避免对视图进行DML操作后对基表有污染的情况，检查选项要求对视图进行DML操作的数据视图必须对其可见，否则不允许操作。
	CREATE OR REPLACE VIEW v_emp_10 AS SELECT 
		empno ID,ename NAME,sal salary,deptno FROM emp WHERE deptno=10
			WITH CHECK OPTION;
			
--插入成功
	INSERT INTO v_emp_10(ID,NAME,salary,deptno) VALUES(1002,'FUCK',8000,10);
	
--插入失败，子句违规
	INSERT INTO v_emp_10(ID,NAME,salary,deptno) VALUES(1003,'JECS',9000,20);
	
--READ ONLY选项，当视图添加只读选项后，该视图仅能查看不能执行任何DML操作
	CREATE OR REPLACE VIEW v_emp_10 AS SELECT 
		empno ID,ename NAME,sal salary,deptno FROM emp WHERE deptno=10
			WITH READ ONLY;
			
--插入数据失败
	INSERT INTO v_emp_10(ID,NAME,salary,deptno) VALUES(1001,'HELLO',8800,10);
	
--数据字典。USER_OBJECTS:记录类用户创建的所有数据库对象(表，视图，序列，索引)		
--USER_VIEWS:所有视图			USER_TABLES:所有表			USER_UPDATE_COLUMNS

--查询所有视图
	SELECT view_name FROM user_views;
	SELECT object_name FROM USER_OBJECTS WHERE object_type='VIEW';
	
--查询所有表
	SELECT table_name FROM user_tables;
	SELECT object_name FROM USER_OBJECTS WHERE object_type='TABLE';
	
--查看创建视图的那句select语句
	SELECT text FROM user_views WHERE view_name = 'V_EMP_10';

--复杂视图：创建一个各部们的工资情况视图
	CREATE VIEW v_emp_dept_salinfo 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 e,dept d
	WHERE e.deptno=d.deptno
	GROUP BY d.deptno,d.dname;
	
	SELECT * FROM v_emp_dept_salinfo;
	
--查看比自己所在部门平均工资高的员工
	SELECT e.ename,e.sal,e.deptno FROM emp e,v_emp_dept_salinfo v WHERE e.deptno=v.deptno AND e.sal>v.avg_sal;
	
--删除视图:不会对基表任何数据有影响；删除视图中的数据时，会对应的将基表的数据删除.
	DROP VIEW v_emp_10;
	
--序列，数据库对象之一。序列使用来生成一系列数字的，序列生成的数字常被用作某张表主键字段的值
	CREATE SEQUENCE seq_emp_id START WITH 1 INCREMENT BY 1;
	
--序列提供两个伪列用获取当前序列的值，NEXTVAL：获取下一个数，这个伪列会导致序列发生步进，序列是不能回退的，即：生成下一个数后，就不能得到之前的数字了。
--CURRVAL：获取当前值（最后一次生成的值）无论调用多少次，都不会导致序列发生步进。
--新创建的序列要至少调用一次NEXTVAL后才能可以使用CURRVAL
	SELECT seq_emp_id.currval FROM dual;--首次调用会出错-->尚未在此会话中定义
	SELECT seq_emp_id.nextval FROM dual;
	
--序列的使用
	INSERT INTO emp(empno,ename,sal,JOB,deptno) 
	VALUES(seq_emp_id.nextval,'lisa',9800,'CLERK',10);
	
--删除序列
	DROP SEQUENCE seq_emp_id;
	
--索引，数据库对象之一，作用是提高查询效率，索引的建立是数据库执行完成的，过程对我们是透明的，我们只需要告诉数据库是否添加索引即可。
--索引的应用也是自动的，无需在查询的过程中告知数据库是否使用索引，数据库会自行可见索引并自动使用。
	CREATE INDEX idx_emp_ename ON emp(ename);	
--当只用ename作为过滤条件(除了like)，作为排序的字段，去重等操作时，数据库会自动使用索引idx_emp_ename提供查询效率。

--复合索引
	CREATE INDEX idx_emp_job_sal ON emp(JOB,sal);
--在聚合函数使用索引
	CREATE INDEX emp_ename_upper_idx ON emp (upper(ename));
--重建索引 
	ALTER INDEX idx_emp_ename REBUILD;
--删除索引
	DROP INDEX idx_emp_ename;
	
	
--创建
	CREATE TABLE employees(
		eid NUMBER(6),
		name VARCHAR2(30) NOT NULL,
		salary NUMBER(7,2),
		hiredate DATE CONSTRAINT hiredate NOT NULL
	);
	DESC employees;
	
--约束分为表级约束与列级约束，NOT NULL只是列级约束，其他约束即使表级约束也是列级约束。
--列级约束：为某个字段添加约束只能在操作该列的同时进行。
--表级约束：可以直接对表进行添加约束并指定为该表的那个字段添加，简单说：添加约束时的语法不一样。
--添加非空约束
	ALTER TABLE employees MODIFY(eid NUMBER(6) NOT NULL);
--取消非空约束
	ALTER TABLE employees MODIFY(eid NUMBER(6) NULL); 
		
	DROP TABLE employees;
--唯一约束可以要求某个字段在表中任何集
	CREATE TABLE employees(
		eid NUMBER(6) UNIQUE,
		name VARCHAR2(30),
		email VARCHAR2(50),
		salary NUMBER(7,2),
		hiredate DATE,
		CONSTRAINT employees_email_uk UNIQUE(email)
	);
--查看表结构
	DESC employees;
--插入数据
	INSERT INTO employees(eid,name,email,salary,hiredate)
	VALUES(1,'DEKOS',8900,86,to_date('2016-11-26','YYYY-MM-DD'));
--插入空值的
	INSERT INTO employees VALUES(NULL,'DEKOS',NULL,10000,to_date('2016-11-26','YYYY-MM-DD'));
--查看数据
	SELECT eid,name,email,salary,hiredate FROM employees;

--
	ALTER TABLE employees ADD CONSTRAINT employees_name_uk UNIQUE(name);

--主键约束只能建立在单列上，并且一张表只能有一个主键约束，主键约束可以保证该字段非空且唯一。
	CREATE TABLE employees2(
		eid NUMBER(6) PRIMARY KEY,
		name VARCHAR2(30),
		email VARCHAR2(50),
		salary NUMBER(7,2),
		hiredate DATE
	);
	DESC employees2;
		
	INSERT INTO employees2(eid,NAME) VALUES(1,'HOH');
--
	ALTER table employees2 ADD CONSTRAINT employees2_salary_check CHECK(salary>2000);
	
	INSERT INTO employees2(eid,NAME,salary)VALUES(1234,'WWW',2500);
	
	INSERT INTO employees2(eid,NAME,salary)VALUES(1223,'FFF',2001);
		
	
	

如有问题欢迎留言！