8:查看每个职位的工资排名--在组内排序
SELECT ENAME,SAL,JOB,ROW_NUMBER() OVER(PARTITION BY JOB ORDER BY SAL DESC) MM FROM EMP_GH
9:查看每个职位的工资排名，若工资一致，排名一致
SELECT ENAME,SAL,JOB,RANK() OVER(PARTITION BY JOB ORDER BY SAL DESC) MM FROM EMP_GH
10:查看每个职位的工资排名，若工资一致，排名一致，不跳名次。
SELECT ENAME,SAL,JOB ,DENSE_RANK() OVER(PARTITION BY JOB ORDER BY SAL DESC) MM FROM EMP_GH
11:分别查看:同部门同职位，同职位，以及所有员工的工资总和 SELECT DEPTNO,JOB,SUM(SAL) FROM EMP_GH
GROUP BY ROLLUP(DEPTNO,JOB)
12:分别查看:同部门同职位，同职位，同部门以及所有员工的工资总和 SELECT DEPTNO,JOB,SUM(SAL) FROM EMP_GH
GROUP BY CUBE(DEPTNO,JOB)
13:分别查看同部门同职位和同职位的员工的工资总和 SELECT DEPTNO,JOB,SUM(SAL) FROM EMP_GH
GROUP BY GROUPING SETS((DEPTNO,JOB),JOB)
**********************************DAY05************************************** ****视图(VIEW)
***创建视图,视图名字规则,V_EMP_GH_DEPTNO_10
*** 以v开头_表名字_视图内容信息,在数据库中,不能出现相同的名字 视图不是正在存在的一张表,而是通过SQL语句查询后的到的 虚表 CREATE VIEW V_EMP_GH_DEPTNO_10
AS (SELECT ENAME,SAL,EMPNO,DEPTNO FROM EMP_GH WHERE DEPTNO=10)
SELECT * FROM V_EMP_GH_DEPTNO_10
*** 工作原理:先看from后面是表还是视图,如果是视图,就会找到相关的select语句,并执行,在执行
**** 原来的SELECT 语句,进行查询.
查结构 b
DESC V_EMP_GH_DEPTNO_10
*******视图分为简单视图,复杂视图
简单视图:该视图对应的SELECT语句不含有加工的操作,比如,avg,分组等
**可以进行DML操作,但实际上对视图的DML操作,就是对该视图数据来源的基础表
进行操作
复杂视图:除简单视图之外的视图都是复杂视图
**不能进行DML操作
***对视图进行修改
OR REPLACE,如果没有相关视图则创建一个新视图,如果有则在该视图上修改
CREATE OR REPLACE VIEW V_EMP_GH_DEPTNO_10 AS
SELECT EMPNO ID,ENAME NAME,SAL SALARY,DEPTNO FROM EMP_GH WHERE DEPTNO=10 *******简单视图
********插入 INSERT INTO
**再插入过程中,字段使用别名,如果 使用基础表中的要出现错误 INSERT INTO V_EMP_GH_DEPTNO_10(ID,NAME,SALARY,DEPTNO) VALUES(1021,'SB',9000,10)
SELECT * FROM EMP_GH
*******修改 ***UPDATE
修改过程中,将修改基础表, UPDATE V_EMP_GH_DEPTNO_10 SET SALARY=6000 WHERE NAME='SB'
***dml操作可能会对基础表进行数据污染, **通过视图表插入,但是,在视图内不可见
INSERT INTO V_EMP_GH_DEPTNO_10 VALUES(1021,'SB',2500,20) 或
UPDATE V_EMP_GH_DEPTNO_10 SET DEPTNO=20
上视图在插入中,deptno =20,不在该视图可是范围(deptno=10),造成数据污染 SELECT * FROM V_EMP_GH_DEPTNO_10 ******删除
在进行视图删除是,就是对基础表进行删除 DELETE FROM
DELETE FROM V_EMP_GH_DEPTNO_10
DELETE FROM V_EMP_GH_DEPTNO_10 WHERE DEPTNO=20
********对视图添加检查选项后,可以避免视图对基础表污染, 在创建视图最下面加 WITH CHECK OPTION
CREATE OR REPLACE VIEW V_EMP_GH_DEPTNO_10 AS
SELECT EMPNO ID,ENAME NAME,SAL SALARY,DEPTNO FROM EMP_GH WHERE DEPTNO=10 WITH CHECK OPTION
**修改
修改失败,视图是10部门的 UPDATE V_EMP_GH_DEPTNO_10 SET DEPTNO=20 **插入
插入失败,视图是10部门的,视图不可见 INSERT INTO V_EMP_GH_DEPTNO_10 VALUES(1020,'SB',1245,20) ***只读选项语句
WITH READ ONLY,只读
加入该句后,就可以让视图只能进行查询,不能进行DML操作,
CREATE OR REPLACE VIEW V_EMP_GH_DEPTNO_10 AS
SELECT EMPNO ID,ENAME NAME,SAL SALARY,DEPTNO FROM EMP_GH WHERE DEPTNO=10 WITH READ ONLY *****数据字典 -user_objects -user_views -user_tables
**查看数据库中视图名
SELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE='VIEW' **查看视图中的试图名
SELECT VIEW_NAME FROM USER_VIEWS **查看表里面有哪些表明
SELECT TABLE_NAME FROM USER_TABLES ***查看视图中的创建视图的select语句 SELECT TEXT FROM USER_VIEWS
*********复杂视图
** 创建复杂视图,每个部门的部门编号,名字,及该部门员工的工资最大值,最小值,平均
值及工资总和
CREATE VIEW V_DEPT_GH AS
SELECT D.DEPTNO,D.DNAME, MAX(E.SAL) MAN_SAL, MIN(E.SAL) MIN_SAL, AVG(E.SAL) SUM_SAL FROM EMP_GH E,DEPT_GH D WHERE E.DEPTNO=D.DEPTNO GROUP BY D.DEPTNO,D.DNAME
SELECT ENAME,SAL
FROM EMP_GH E ,V_DEPT_GH V WHERE E.DEPTNO = V.DEPTNO AND E.SAL ***删除视图 DROP VIEW V_EMP_10 ************序列 ***序列创建后,就可以用nextval进行数据填充, ** 序列也是数据库对象之一,作用是可以按照指定的方式生成一系列数字,最常用于为表的主键提供的数据 **创建 CREATE SEQUENCE SEQ_EMP_GH_ID START WITH 1--从1开始 INCREMENT BY 1--步进为1 序列支持两个伪列,以获取该序列的数字: NEXTVAL:使序列生成下一个数字,若刚创建的序列,就将start WITH 指定的数字返回,以后就是 根据步长计算下一个数字后返回.序列不可以发生回退 CURRVAL:获取当前序列,最后生成的数字,不会发生步进效果 **在调用nextval过后才能调用currval,否则会出现错误 SELECT SEQ_EMP_GH_ID.CURRVAL FROM DUAL INSERT INTO EMP_GH(EMPNO,ENAME,SAL,DEPTNO) VALUES(SEQ_EMP_GH_ID.NEXTVAL,'NB',5200,10) SELECT * FROM EMP_GH DELETE FROM EMP_GH WHERE ENAME='NB' ****删除序列 DROP SEQUENCE SEQ_EMP_GH_ID **可以缓存,但是可能发生不连续的数字 ****UUID gUID是一个32位不重复字符串 SELECT SYS_gUID() FROM DUAL *****索引 加快查询效率,数据库自动调用 CREATE INDEX IDX_EMP_GH_ENAME ON EMP_GH(ENAME)--在emp_gh上的ename加索引 创建多列索引 CREATE INDEX IDX_EMP_GH_JOB_SAL ON EMP(JOB,SAL) SELECT EMPNO,ENAME,SAL,JOB FROM EMP ORDER BY JOB,SAL select * FROM EMP_GH **创建索引函数 CREATE INDEX EMP_GH_ENAME_UPPER_INDEX ON EMP_GH(UPPER(ENAME)) 当做下面的查询时,会自动用于刚刚建立的索引: SELECT * FROM EMP WHERE UPPER(ENAME)='KING' ****修改索引 从新整理索引 ALTER INDEX IDX_EMP_ENAME REBUILD ***删除索引 DROP INDEX EMP_GH_ENAME_UPPER_INDEX *********************约束 *****NOT NULL非空约束 NN CONSTRAINT + 名 + NOT NULL 全写 属于列级约束,就是要修改该列同时进行 CREATE TABLE EMPLYEE_GH( ID NUMBER(6), NAME VARCHAR2(30) NOT NULL,--简写,系统分配名字 SALARY NUMBER(7,2),