oracle伪列、增删改

1.SELECT * FROM EMP WHERE (  DEPTNO , JOB )
    = ( SELECT DEPTNO , JOB FROM EMP WHERE EMPNO = '7369' );
    
SELECT * FROM EMP WHERE (  DEPTNO , JOB )
    = ( SELECT DEPTNO , JOB FROM DEPT WHERE DNAME = 'ACCOUNTING' AND EMP.DEPTNO = DEPT.DEPTNO );
等效的
SELECT * FROM EMP WHERE  DEPTNO 
    IN ( SELECT DEPTNO FROM DEPT WHERE DNAME = 'ACCOUNTING' );
    
2.投影列绝大多数来自于一个结果集    
员工编号  员工名称 .... 部门名称（子查询）
SELECT 
    EMP.* , (
        SELECT DNAME FROM DEPT WHERE EMP.DEPTNO = 
            DEPT.DEPTNO
    ) 部门名称 
FROM 
    EMP
ORDER BY EMPNO DESC

3.伪列 ROWNUM
SELECT
    ROWNUM , EMPNO , ENAME , JOB 
FROM
    EMP WHERE ROWNUM <= 10
    -----错误范例----------------------------------------------------------------------------------------------------------------
第五笔 - 第十笔资料
SELECT
    ROWNUM , EMPNO , ENAME , JOB 
FROM
    EMP WHERE ROWNUM <= 10 AND ROWNUM >= 5  
    
SELECT
    ROWNUM , EMPNO , ENAME , JOB 
FROM
    EMP WHERE ROWNUM > 5    
 
SELECT
    ROWNUM , EMPNO , ENAME , JOB 
FROM
    EMP WHERE ROWNUM <= 10    
ORDER BY EMPNO DESC

SELECT 
    ROWNUM , EMPNO , ENAME ,SAL
FROM 
    EMP WHERE DEPTNO IN ( 10 , 20) 
    AND ROWNUM <=5 
ORDER BY SAL DESC

  -------------------------------------------------------------------------------------
正确答案，用内敛视图
SELECT ROWNUM ,EMPNO , ENAME , SAL  FROM  (
    SELECT 
        EMPNO , ENAME , SAL
    FROM 
        EMP WHERE DEPTNO IN ( 10 , 20) 
    ORDER BY SAL DESC )
WHERE ROWNUM <=5 

求5-10数据 EMP表
SELECT ROWNUM , A.* FROM ( 
    SELECT ROWNUM r,EMP.* FROM EMP     WHERE ROWNUM <= 10 
    ) A WHERE r > 5
    
求工资排名在5-10的员工
SELECT * FROM (
SELECT ROWNUM r , A.* FROM ( 
    SELECT EMP.* FROM EMP
        WHERE SAL IS NOT NULL ORDER BY SAL DESC
    ) A ) WHERE r BETWEEN 5 AND 10

4.SELECT * FROM EMP;
--给EMP表 没有工资的 做一个历史备份 名字叫做EMP_HIS

CREATE TABLE EMP_HIS 
AS
    SELECT * FROM EMP WHERE SAL IS NULL;
    
SELECT * FROM     EMP_HIS 

5.UNION 不会出现重复记录，加ALL关键字出现重复记录
SELECT * FROM EMP WHERE DEPTNO = 10
UNION 
SELECT * FROM EMP_HIS WHERE DEPTNO = 10

6.INTERSECT 求交集，相同的部分
SELECT * FROM EMP WHERE DEPTNO = 10
INTERSECT 
SELECT * FROM EMP_HIS WHERE DEPTNO = 10

7.MINUS 求差集 （ A-A交B）
SELECT * FROM EMP WHERE DEPTNO = 10
MINUS 
SELECT * FROM EMP_HIS WHERE DEPTNO = 10

8.求修了1和2这两门课的学生
SELECT * FROM SC WHERE CID = 1
INTERSECT 
SELECT * FROM SC WHERE CID = 2

9.提取日期部分 
求1980年入职的员工
SELECT 
    *
FROM EMP WHERE EXTRACT(YEAR FROM HIREDATE) 
= 1980

10.层次数
SELECT
    LEVEL 层次 , ROWNUM 行号 ,
    EMPNO , ENAME , SAL 
FROM EMP
START WITH MGR IS NULL 
CONNECT BY MGR = PRIOR EMPNO;

11.--找到所有的领导（他有下属）
--如何证明他有下属
SELECT * FROM EMP WHERE EMPNO IN (
SELECT MGR FROM EMP WHERE MGR IS NOT NULL )

12.Oracle优化查询

SELECT AVG(SAL) , JOB FROM
EMP 
WHERE JOB IN (
    'PRESIDENT','MANAGER'
)
GROUP BY JOB
HAVING AVG(SAL) > 2000 

IN 的查询效率 理论上比 OR要高

能用系统函数尽量用系统函数实现，
而不要用复杂的SQL语法实现

索引优化

13.批量插入
INSERT INTO EMP_HIS 
SELECT * FROM EMP WHERE COMM IS NULL

INSERT INTO EMP_HIS (EMPNO , ENAME)
SELECT EMPNO , ENAME FROM EMP 
WHERE COMM IS NULL AND SAL IS NULL

P144自己体会

15.更新 杨玉环的工种为CLERK，部门编号为空
EMP_HIS
UPDATE EMP_HIS SET JOB = 'CLERK' ,
    DEPTNO = NULL 
WHERE ENAME = '杨玉环'

16.EMP_HIS每一个员工增加10%的工资
UPDATE EMP_HIS SET SAL = SAL*1.1

SELECT * FROM EMP_HIS
SELECT * FROM EMP

更新EMP_HIS表里面的数据，与EMP表的数据一样
（SAL ， COMM）

UPDATE EMP_HIS SET
SAL = ( SELECT SAL FROM EMP WHERE     EMP_HIS.EMPNO = EMP.EMPNO
)

17.--删除表 
TRUNCATE TABLE EMP_HIS  <==> DELETE FROM EMP_HIS
删除整个表的数据要用TRUNCATE效率比较高，
但是危险，因为数据不记录到日志里面，所以不能通过日志进行恢复。

查询语句也可能需要事务

18.--为一个表添加主键
ALTER TABLE EMP_HIS 
    ADD CONSTRAINT PK_S_001 
    PRIMARY KEY (EMPNO)
    
CREATE TABLE S (
    SID number(4),
    SNAME VARCHAR2(20),
    SSEX char(2) default '女'
)

INSERT INTO S( SID , SNAME ) VALUES( 
    1001 , '杨玉环'
)

SELECT * FROM S

INSERT INTO S( SID , SNAME ,SSEX ) VALUES( 
    1001 , '杨玉环' , NULL
)

UPDATE S SET SSEX = DEFAULT;

--修改表结构
ALTER TABLE S ADD 
    SBIRTHDAY DATE;
    
ALTER  TABLE S MODIFY     SNAME VARCHAR2(30) NOT NULL;

ALTER TABLE S DROP( SBIRTHDAY )