数据库笔记-SQLday05

数据库day05


--视图：
--视图(VIEW)也被称作虚表，即虚拟的表，是一组数据的逻辑表示,其本质是对应于一条SELECT语句，结果集被赋予一个名字，即视图名字
--视图也是数据库对象，视图在SQL语句中体现的样子是表的样子，但是其并不是一张真实存在的表，
--而是一个查询语句的结果集。使用视图通常是为了重用子查询。


CREATE VIEW v_emp_wensq
AS
SELECT empno,ename,sal,deptno
FROM emp_wensq
WHERE deptno = 10;


--视图中对应的SELECT子句中的字段可以使用别名，这样视图对应的该字段的字段名就是别名了。
--当SELECT中的字段是函数或者表达式，那么视图要求该字段必须给别名!


CREATE OR REPLACE VIEW v_emp_wensq
AS
SELECT empno id,ename name,sal salary,deptno
FROM emp_wensq
WHERE deptno = 10;


--对视图进行DML操作，就是对视图数据来源的基表进行的。
--向视图插入数据：
--DML只能对简单视图进行，复杂视图不允许进行DML操作。
--向视图插入数据：
INSERT INTO v_emp_wensq
(id, name,salary,deptno)
VALUES
(1001,'JACK',5000,10)


INSERT INTO v_emp_wensq
(id, name,salary,deptno)
VALUES
(1001,'ROSE',5000,20)


--上面的SQL是向视图插入了一条视图，看不到的数据到基表中，这种通过视图插入但视图不可控的数据的操作是对基表的"污染"
--UPDATE语句也通常存在在该问题，因为修改视图上的数据就是修改基表的该数据，所以，有可能修改后的数据视图不在对齐可见。
UPDATE v_emp_wensq
SET name='JACKSON'
WHERE id=1001


--为视图添加检查选项，避免通过视图进行DML操作污染基表
CREATE OR REPLACE VIEW v_emp_wensq
AS
SELECT empno id,ename name,sal salary,deptno
FROM emp_wensq
WHERE deptno = 10
WITH CHECK OPTION
--WITH CHECK OPTION短语表示，通过视图所做的修改，必须在视图的可见范围内：
--当添加了检查选项后，该视图对DML操作有要求：
--插入数据时，必须保证插入的数据视图可见。
--修改数据时，必须保证修改后的数据视图对其可见。


--READ ONLY对视图添加只读选项，那么不允许对视图进行DML操作
CREATE OR REPLACE VIEW v_emp_wensq
AS
SELECT empno id,ename name,sal salary,deptno
FROM emp_wensq
WHERE deptno = 10
WITH READ ONLY


--在数据字典USER_OBJECTS中查询所有视图名称：
SELECT object_name FROM user_objects
WHERE object_type = 'VIEW';


--在数据字典USER_VIEWS中查询指定视图：
SELECT text FROM user_views
WHERE view_name = 'v_emp_wensq';


--在数据字典USER_UPDATABLE_COLUMNS中查询视图：
SELECT column_name, insertable, updatable, deletable
FROM user_updatable_columns
WHERE table_name = 'v_emp_wensq';


--创建一个记录每个部门薪水情况的视图
CREATE VIEW v_emp_sal_wensq
AS
SELECT
  d.deptno,d.dname,
  AVG(e.sal) avg_sal,
  SUM(e.sal) sum_sal,
  MIN(e.sal) min_sal,
  MAX(e.sal) max_sal
FROM emp_wensq e , dept_wensq d
WHERE e.deptno=d.deptno
GROUP BY d.deptno,d.dname
--查看那些员工比自己所在部门平均工资高?
SELECT e.ename,e.sal,e.deptno
FROM emp_wensq e,v_emp_sal_wensq v
WHERE e.deptno=v.deptno
AND e.sal>v.avg_sal


DELETE FROM v_emp_sal_wensq    --SQL 错误: ORA-01732: 此视图的数据操纵操作非法（复杂视图不允许DML操作，会报错。）


--当不再需要视图的定义，可以使用DROP VIEW语句删
除视图
DROP VIEW v_emp_wensq


--序列也是数据库对象，它可以根据指定的规则生成一组数字，通常序列是为了某表的主键提供值。
--创建一个序列seq_emp_id_wensq，初始值是1000，步进是2
CREATE SEQUENCE seq_emp_id_wensq
START WITH 1000
INCREMENT BY 1


--序列支持两个伪列：
--CURRVAL:获取序列当前值（最后一次生成的数值）
--NEXTVAL：获取序列下一个数字（当前值加不进）


--序列是不可逆的，每当调用完NEXTVAL生成新的数字后，序列不会再提供之前的数字。
--CURRVAL可以调用多次，不会导致步进。


--新创建的序列必须质数调用一次 NEXTVAL  后才可以开始使用CUPRRVAL


SELECT seq_emp_id_wensq.NEXTVAL
FROM dual


SELECT seq_emp_id_wensq.CURRVAL
FROM dual


INSERT INTO emp_wensq
(empno,ename,sal,job,deptno)
VALUES
(seq_emp_id_wensq.NEXTVAL,'JACKSON',6000,'CLERK',20)


SELECT * FROM emp_wensq


DELETE FROM emp_wensq
WHERE ename='JACK'


DROP SEQUENCE seq_emp_id_wensq    --删除序列


--UUID 也是一种作为主键的生成算法
--ORACLE提供了函数生成32位不重复字符串


SELECT sys_guid() FROM dual
www.xxbbs.com/show?tid=F7E2B22139


--索引
--在emp表的ENAME列上创立索引
CREATE INDEX idx_emp_ename_wensq ON emp_wensq(ename);


--复合索引也叫多列索引，是基于多个列的索引。如果经常在ORDER BY子句中使用job和salary作为排序依据，可以建立复合索引：
CREATE INDEX idx_emp_job_sal ON emp_wensq(job,sal);
--当做下面的查询时，会自动应用索引idx_emp_job_sal
SELECT empno,ename,sal,job FROM emp_wensq
ORDER BY job,sal;


--创建基于函数的索引
--如果需要在emp表的ename列上执行大小写无关搜索，可以在此列上建立一个基于UPPER函数的索引:
CREATE INDEX emp_ename_upper_idx ON emp_wensq(UPPER(ename));
--当做下面的查询时，会自动应用刚刚建立的索引:
SELECT * FROM emp_wensq
WHERE UPPER(ename)='KING';


--修改和删除索引
--如果经常在索引列上执行DML操作，需要定期重建索引，提高索引的空间利用率，语法如下：
ALTER INDEX index_name REBUILD;
--重建索引idx_emp_ename
ALTER INDEX idx_emp_ename REBUILD;
--当一个表上有不合理的索引，会导致操作性能下降，删除索引的语法：
DROP INDEX index_name;
--删除索引idx_emp_ename：
DROP INDEX idx_emp_ename;


--合理使用索引提升查询效率
--为提升查询效率，创建和使用索引的原则：
--为经常出现在WHERE子句中的列创建索引
--为经常出现在ORDER BY、DISTINCT后面的字段建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致
--为经常作为表的连接条件的列上创建索引
--不要在经常做DML操作的表上建立索引
--不要在小表上建立索引
--限制表上的索引数目，索引并不是越多越好
--删除很少被使用的、不合理的索引


--约束的类型
--约束条件包括：
--非空约束(Not Null)，简称NN
--唯一性约束(Unique)，简称UK
--主键约束(Primary Key)，简称PK
--外键约束(Foreign Key)，简称FK
--检查约束(Check)，简称CK


CREATE TABLE employees_wensq(
eid NUMBER(6),
name VARCHAR2(30)NOT NULL,
salary NUMBER(7,2),
hiredate DATE
  CONSTRAINT employees_wensq_hiredate_nn NOT NULL
);


--可以在建表之后，通过修改表的定义，添加非空约束：
ALTER TABLE employees_wensq
MODIFY(eid NUMBER(6)NOT NULL);


--取消非空约束
--如果业务要求取消某列的非空约束，可以采用重建表或者修改表的方式：
ALTER TABLE employees_wensq
MODIFY (eid NUMBER(6)NULL);


--添加唯一性约束
--当一个字段使用了唯一性约束，那么该字段在整张表中每条记录都不允许重复，NULL值除外
  --在建表employees的同时，在eid、email列上创建唯一约束条件，并在建表后在name列上建立一个名为employees_name_uk的唯一约束条件：
CREATE TABLE employees1_wensq(
  eid NUMBER(6)UNIQUE,
  name VARCHAR2(30),
  email VARCHAR2(50),
  salary NUMBER(7,2),
  hiredate DATE,
  CONSTRAINT employees_wensq_email_uk UNIQUE(email)
);


INSERT INTO employees1_wensq
(eid,name,email)
VALUES
(1,'JACK','JACK@tedu.cn')


SELECT * FROM employees1_wensq


--在建表之后增加唯一性约束条件：
ALTER TABLE employees1_wensq
ADD CONSTRAINT employees_wensq_name_uk UNIQUE(name);


--在建表时添加主键约束条件：
--主键约束
CREATE  TABLE employees2_wensq(
  eid NUMBER(6)PRIMARY KEY,
  name VARCHAR2(30),
  email VARCHAR2(50),
  salary NUMBER(7,2),
  hiredate DATE
);


INSERT INTO employees2_wensq
(eid,name)
VALUES
(NULL,'JACK')


--添加检查约束
--员工的薪水必须大于2000元
ALTER TABLE employees2_wensq
ADD CONSTRAINT employees4_salary_check
CHECK(salary>2000);


--正常插入数据
INSERT INTO employees2_wensq(eid,name,salary)
VALUES(1236,'donna noble',2500);


--试图修改职员的薪水为1500元，更新失败
UPDATE employees4 SET salary=1500
WHERE eid=1236;


--查看视图结构：
DESC v_emp_wensq


SELECT * FROM v_emp_wensq
SELECT *FROM emp_wensq
DELETE v_emp_wensq