Oracle下SQL基本操作(三)

原创

baiyan425 2011-07-20 15:15:48 博主文章分类：Database

文章标签 oracle 职场 SQL 数据库休闲 文章分类 Oracle 数据库 阅读数647

©著作权归作者所有：来自51CTO博客作者baiyan425的原创作品，请联系作者获取转载授权，否则将追究法律责任

----------------------------建表，与插入数据
--创建学位表
CREATE TABLE QUAFICATION (
QUALID CHAR(1),
QUALNAME VARCHAR(10),
---不要忘记括号
CONSTRAINTS QUA_ID_PK PRIMARY KEY (QUALID)

);

--创建工资级别表
CREATE TABLE EMPLEVEL(
   LEVELNO VARCHAR2(2),
   LOWSALARY NUMBER(7,2),
   HIGHSALARY NUMBER(7,2),
   CONSTRAINTS EMP_NO_PK PRIMARY KEY (LEVELNO)
);

---创建职务表
CREATE TABLE POSITION(
POSITIONID VARCHAR(2),
POSITIONNAME VARCHAR(20),
CONSTRAINTS POS_ID_PK PRIMARY KEY (POSITIONID)
);

---创建部门表
CREATE TABLE DEPARTMENT(
DEPTID VARCHAR2(2),
DEPTNAME VARCHAR2(30),
LOCATION VARCHAR2(30),
MANAGERID VARCHAR2(4),
CONSTRAINTS DEP_ID_PK PRIMARY KEY (DEPTID)
);

---创建员工表
CREATE TABLE EMPLOYEE(
EMPLOYEEID VARCHAR2(4),
NAME VARCHAR2(40) NOT NULL,
HIREDATE DATE,
SALARY NUMBER(7,2),
COMMISSION NUMBER(7,2),
MANAGERID VARCHAR2(4),
DEPTID VARCHAR2(2),
POSITIONID VARCHAR2(2),
QUALID CHAR(1),
CONSTRAINTS EMP_ID_PK PRIMARY KEY (EMPLOYEEID),
CONSTRAINTS EMP_DEP_FK FOREIGN KEY(DEPTID) REFERENCES DEPARTMENT(DEPTID),
CONSTRAINTS EMP_POS_FK FOREIGN KEY(POSITIONID) REFERENCES POSITION(POSITIONID),
CONSTRAINTS EMP_QUA_FK FOREIGN KEY(QUALID) REFERENCES QUAFICATION(QUALID)
);

--DROP TABLE EMPLOYEE;
----添加员工表EMPLOYEE 外键,参照原表
ALTER TABLE EMPLOYEE
ADD CONSTRAINTS EMP_MAN_FK FOREIGN KEY(MANAGERID) REFERENCES EMPLOYEE(EMPLOYEEID);
----------------添加部门表DEPARTMENT 外键,参照 EMPLOYEE 表
ALTER TABLE DEPARTMENT
ADD CONSTRAINTS DEP_MAN_FK FOREIGN KEY(MANAGERID) REFERENCES EMPLOYEE(EMPLOYEEID);

---SELECT * FROM USER_CONSTRAINTS;查看约束

SELECT * FROM EMPLOYEE FOR UPDATE;
----INSERT POSITION
INSERT INTO POSITION(POSITIONID,POSITIONNAME) VALUES('01','程序员');----指定列
INSERT INTO POSITION VALUES('02','组长');---不指定列，则要给所有的列指定值
INSERT INTO POSITION VALUES('03','经理');
INSERT INTO POSITION VALUES('04','总裁');
----SELECT *FROM POSITION;

----INSERT EMPLEVEL
INSERT INTO EMPLEVEL VALUES('01',1000,2000);
INSERT INTO EMPLEVEL VALUES('02',2001,3000);
INSERT INTO EMPLEVEL VALUES('03',3001,4000);
INSERT INTO EMPLEVEL VALUES('04',401,5000);
----SELECT *FROM EMPLEVEL;

---INSERT QUAFICATION
INSERT INTO QUAFICATION VALUES('1','学士');
INSERT INTO QUAFICATION VALUES('2','博士');
INSERT INTO QUAFICATION VALUES('3','硕士');
----SELECT * FROM QUAFICATION;

----INSERT DEPARTMENT
INSERT INTO DEPARTMENT VALUES('01','教学部','教学部办公室',NULL);
INSERT INTO DEPARTMENT VALUES('02','市场部','市场部办公室',NULL);
INSERT INTO DEPARTMENT VALUES('03','教务部','教务部办公室',NULL);
INSERT INTO DEPARTMENT VALUES('04','资助部','资助部办公室',NULL);
----SELECT * FROM DEPARTMENT;

----INSET EMPLOYEE
INSERT INTO EMPLOYEE VALUES('01','张琴',TO_DATE('2009-10-15','YYYY-MM-DD'),
1500,600,NULL,NULL,NULL,NULL);
-----以下两条记录主要是用于在SELECT时，DISTINCT
----与下面的记录相同,EMPLOYEENAME,SALARY
INSERT INTO EMPLOYEE VALUES('02','张五',TO_DATE('2005-6-5','YYYY-MM-DD'),
1600,800,NULL,NULL,NULL,NULL);
-----与上面的记录相同
INSERT INTO EMPLOYEE VALUES('06','张五',TO_DATE('2004-6-5','YYYY-MM-DD'),
1600,900,NULL,NULL,NULL,NULL);

----以下四条记录主要是为了LIKE
INSERT INTO EMPLOYEE VALUES('03','杨刚',TO_DATE('2003-7-7','YYYY-MM-DD'),
2003,1000,NULL,NULL,NULL,NULL);

INSERT INTO EMPLOYEE VALUES('07','刚',TO_DATE('2000-5-12','YYYY-MM-DD'),
2450,1120,NULL,NULL,NULL,NULL);

INSERT INTO EMPLOYEE VALUES('04','杨刚荣',TO_DATE('2001-9-8','YYYY-MM-DD'),
3000,1500,NULL,NULL,NULL,NULL);

INSERT INTO EMPLOYEE VALUES('05','刚果',TO_DATE('2004-9-6','YYYY-MM-DD'),
2700,1200,NULL,NULL,NULL,NULL);

INSERT INTO EMPLOYEE VALUES('08','AA',TO_DATE('2004-9-6','YYYY-MM-DD'),
2700,1200,NULL,NULL,NULL,NULL);

INSERT INTO EMPLOYEE VALUES('09','BB',TO_DATE('2004-9-6','YYYY-MM-DD'),
2700,1200,NULL,NULL,NULL,NULL);
INSERT INTO EMPLOYEE VALUES('10','aa',TO_DATE('2004-9-6','YYYY-MM-DD'),
2700,1200,NULL,NULL,NULL,NULL);

----SELECT * FROM EMPLOYEE;
---DELETE以下为删除元组，也就是一组
DELETE FROM EMPLOYEE EMP WHERE EMP.EMPLOYEEID = '02';

---UPDATE EMPLOYEE
UPDATE EMPLOYEE SET MANAGERID = '01';--更改一列上所有的数据
UPDATE EMPLOYEE SET COMMISSION = 850 WHERE EMPLOYEEID = '02';

--DELETE POSTION
DELETE POSITION;--删除所有的记录
DELETE POSITION WHERE POSITIONNAME='程序员';

---SELECT EMPLOYEE
SELECT * FROM EMPLOYEE;
SELECT EMP.EMPLOYEEID ID,EMP.NAME 姓名 FROM EMPLOYEE EMP;--指定别名
SELECT EMP.EMPLOYEEID ,EMP.NAME FROM EMPLOYEE EMP WHERE EMP.NAME = '张五';

---DISTINCT 要在SELECT后
--可以看到张五只有一条记录了
SELECT DISTINCT EMP.NAME 姓名, EMP.SALARY 薪水 FROM EMPLOYEE EMP;

---||串联将多列作为一列
SELECT EMP.NAME || ' 的月薪是 ' || EMP.SALARY INFO FROM EMPLOYEE EMP;

---算术表达式
SELECT EMP.NAME, EMP.SALARY * 12 年薪 FROM EMPLOYEE EMP;

---WHERE 子句
---->
SELECT EMP.NAME,EMP.SALARY FROM EMPLOYEE EMP WHERE EMP.SALARY > 1600;

---BETWEEN AND
SELECT EMP.EMPLOYEEID,EMP.NAME,EMP.SALARY FROM EMPLOYEE EMP WHERE EMP.SALARY BETWEEN 1600 AND 2500;
----NOT BETWEEN AND
SELECT EMP.NAME,EMP.SALARY FROM EMPLOYEE EMP WHERE EMP.SALARY NOT BETWEEN 1600 AND 2500;
----IN
SELECT EMP.EMPLOYEEID,EMP.NAME,EMP.SALARY FROM EMPLOYEE EMP WHERE EMP.SALARY IN(1600,2001,2004);
---NOT IN
SELECT EMP.EMPLOYEEID,EMP.NAME,EMP.SALARY FROM EMPLOYEE EMP WHERE EMP.SALARY NOT IN(1600,2003);
----IS NULL
UPDATE EMPLOYEE SET MANAGERID = NULL WHERE EMPLOYEE.EMPLOYEEID = '01';
SELECT * FROM EMPLOYEE EMP WHERE EMP.MANAGERID IS NULL;
--- IS NOT NULL
SELECT * FROM EMPLOYEE EMP WHERE EMP.MANAGERID IS NOT NULL;
---LIKE
--SELECT * FROM EMPLOYEE;
SELECT * FROM EMPLOYEE EMP WHERE EMP.NAME LIKE '%刚';
SELECT * FROM EMPLOYEE EMP WHERE EMP.NAME LIKE '_刚';
SELECT * FROM EMPLOYEE EMP WHERE EMP.NAME LIKE '%刚%';
SELECT * FROM EMPLOYEE EMP WHERE EMP.NAME LIKE '_刚_';
SELECT * FROM EMPLOYEE EMP WHERE EMP.NAME LIKE '%刚_';

---NOT LIKE
SELECT * FROM EMPLOYEE EMP WHERE EMP.NAME NOT LIKE '%刚%';
SELECT * FROM EMPLOYEE EMP WHERE EMP.NAME NOT LIKE '%刚';
SELECT * FROM EMPLOYEE EMP WHERE EMP.NAME NOT LIKE '%刚_';

---AND
SELECT EMP.EMPLOYEEID,EMP.NAME,EMP.SALARY,EMP.COMMISSION FROM EMPLOYEE EMP WHERE EMP.SALARY >1600 AND EMP.COMMISSION >=1500;

---OR
SELECT FROM EMPLOYEE EMP WHERE EMP.EMPLOYEEID = '02'OR EMP.NAME = '刚果';

---ORDER BY
SELECT EMP.EMPLOYEEID,EMP.NAME,EMP.SALARY,EMP.COMMISSION FROM EMPLOYEE EMP
ORDER BY SALARY;

SELECT EMP.EMPLOYEEID,EMP.NAME,EMP.SALARY,EMP.COMMISSION FROM EMPLOYEE EMP
ORDER BY EMP.SALARY ,EMP.COMMISSION DESC;

---CASE 子句
--SELECT * FROM EMPLOYEE;
SELECT   EMP.EMPLOYEEID,EMP.NAME,EMP.SALARY, CASE WHEN EMP.EMPLOYEEID = '02' THEN EMP.SALARY + 400
                                                    WHEN EMP.EMPLOYEEID = '03' THEN EMP.SALARY + 7
                                                    ELSE EMP.SALARY
                                                    END 加薪后
FROM EMPLOYEE EMP

UPDATE EMPLOYEE EMP SET EMP.SALARY = CASE WHEN EMP.EMPLOYEEID = '02' THEN EMP.SALARY + 400
                                            WHEN EMP.EMPLOYEEID = '03' THEN EMP.SALARY + 7
                                            ELSE EMP.SALARY
                                            END
-----内置函数
----字符函数
----LOWER
SELECT LOWER(EMP.NAME)   FROM EMPLOYEE EMP ;
---UPPER
SELECT UPPER(EMP.NAME) FROM EMPLOYEE EMP;

---SUBSTR
SELECT SUBSTR('123456',2,2) FROM DUAL;
SELECT SUBSTR('123456',2) FROM DUAL;
SELECT INSTR('ABCABC','AB') FROM DUAL;
-----INITCUP
SELECT INITCAP('aaabbgb') FROM DUAL;
----CONCAT
SELECT CONCAT(EMP.NAME,EMP.HIREDATE) FROM EMPLOYEE EMP;

---LTRIM
SELECT LTRIM('AAABCAA','AA') FROM DUAL;
SELECT LTRIM('A12AAABCDAAA12','A12') FROM DUAL;
SELECT LTRIM('A12AAABCDAAA12','12') FROM DUAL;
---TRIM
SELECT TRIM(' AFASFDA AAA ') FROM DUAL;
----REPLACE
SELECT REPLACE('AAABBBBBBBAAA','A','XY') FROM DUAL;
-----TRIM
SELECT TRIM('A' FROM 'ABAXYZABAbAAAA') FROM DUAL;
---LENGTH
SELECT LENGTH('ABC大大') FROM DUAL;

----数字函数
--ROUND
SELECT ROUND(-5.678,2), ROUND(5.678,2) FROM DUAL;
SELECT ROUND(1.678,-1) FROM DUAL;
--TRUNC
SELECT TRUNC(5.687,2) FROM DUAL;
--POWER
SELECT POWER(2,3) FROM DUAL;
--ABS
SELECT ABS(-12.69) FROM DUAL;
--MOD
SELECT MOD(12,7) FROM DUAL;
--FLOOR
SELECT FLOOR(15.4 ) FROM DUAL;
SELECT FLOOR(-15.4) FROM DUAL;
---CEIL
SELECT CEIL(15.4) FROM DUAL;
SELECT CEIL(-15.4) FROM DUAL;
----SQRT
SELECT SQRT(4) FROM DUAL;

-----日期函数
SELECT SYSDATE FROM DUAL;
--加两天
SELECT SYSDATE + 2 FROM DUAL;
----加四个小时
SELECT SYSDATE + 2/12 FROM DUAL;
---加了两个小时
SELECT SYSDATE + 2/24 FROM DUAL;
---加了两分钟
SELECT SYSDATE + 2/1440 FROM DUAL;
--日期相减
SELECT TO_DATE('2009-5-9','YYYY-MM-DD') - TO_DATE('2008-5-9','YYYY-MM-DD')   FROM DUAL;
---Add_moths
SELECT ADD_MONTHS(TO_DATE('2009-5-9','YYYY-MM-DD'),2) FROM DUAL;
---MONTHS_BETWEEN
SELECT MONTHS_BETWEEN(TO_DATE('2009-7-19','YYYY-MM-DD'),TO_DATE('2009-6-11','YYYY-MM-DD')) FROM DUAL;
----ROUND 2010-9-11是星期六
SELECT ROUND(TO_DATE('2010-9-11','YYYY-MM-DD'),'DAY') FROM DUAL; --0912
SELECT ROUND(TO_DATE('2010-9-8','YYYY-MM-DD'),'DAY') FROM DUAL;---0905
SELECT ROUND(TO_DATE('2010-9-11','YYYY-MM-DD'),'MONTH')    FROM DUAL;--0901
SELECT ROUND(TO_DATE('2010-9-15','YYYY-MM-DD'),'MONTH')    FROM DUAL;--0901
SELECT ROUND(TO_DATE('2010-9-16','YYYY-MM-DD'),'MONTH')    FROM DUAL;--1001
SELECT ROUND(TO_DATE('2010-7-5','YYYY-MM-DD'),'YEAR')    FROM DUAL;--2010
SELECT ROUND(TO_DATE('2010-7-16','YYYY-MM-DD'),'YEAR')    FROM DUAL;--2011
SELECT ROUND(TO_DATE('2010-6-16','YYYY-MM-DD'),'YEAR')    FROM DUAL;--2011
SELECT ROUND(TO_DATE('2010-7-16 11:59:59','YYYY-MM-DD HH24:MI:SS'))    FROM DUAL;--0716
SELECT ROUND(TO_DATE('2010-7-16 12:00:00','YYYY-MM-DD HH24:MI:SS'))    FROM DUAL;--0716
--日期中没写时间,to_round控制不会显示
SELECT ROUND(TO_DATE('2010-7-16','YYYY-MM-DD HH24:MI:SS'))    FROM DUAL;
---日期中没写时间,to_char控制会显示
SELECT TO_CHAR(ROUND(TO_DATE('2010-7-16','YYYY-MM-DD HH24:MI:SS')),
'MM-DD-YYYY HH24:MI:SS')   FROM DUAL;

----NEXT_DATE
SELECT NEXT_DAY(TO_DATE('2010-9-11','YYYY-MM-DD'),'星期日') FROM DUAL;
SELECT NEXT_DAY(TO_DATE('2010-9-11','YYYY-MM-DD'),'SUNDAY') FROM DUAL;
-----修改语言环境
ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN';
ALTER SESSION SET NLS_LANGUAGE = 'SIMPLIFIED CHINESE';
---LAST_DATE
SELECT LAST_DAY(TO_DATE('2010-9-11','YYYY-MM-DD')) FROM DUAL;

------ 转换函数
--TO_NUMBER
SELECT TO_NUMBER('12') FROM DUAL;
SELECT TO_NUMBER('12','9999') FROM DUAL;
SELECT TO_NUMBER('12','9999.999') FROM DUAL;
SELECT TO_NUMBER('12','9999') FROM DUAL;
---会自动转换,所以to_number已经没什么用了
SELECT '12'* 2 FROM DUAL;
----TO_CHAR
SELECT TO_CHAR(12) FROM DUAL;
SELECT TO_CHAR(12,'9999') FROM DUAL;--带空格
SELECT TO_CHAR(12,'FM9999') FROM DUAL;--去掉空格
SELECT TO_CHAR(12,'09999') FROM DUAL;--带0
SELECT TO_CHAR(12,'$9999') FROM DUAL;--带$
SELECT TO_CHAR(12,'FM$9999') FROM DUAL;
SELECT TO_CHAR(12,'FML9999') FROM DUAL;
SELECT TO_CHAR(123456,'$9999.00') FROM DUAL;--####
SELECT TO_CHAR(12.123,'$9999.00') FROM DUAL;---四舍五入
SELECT TO_CHAR(TO_DATE('2010-09-11','YYYY-MM-DD'),'MM-DD-YYYY HH12:MI:SS AM')   FROM DUAL;
SELECT TO_DATE('2010-09-11 12:10:57','YYYY-MM-DD HH24:MI:SS')   FROM DUAL;
--日期中没写时间,to_date控制不会显示
SELECT TO_DATE('2010-09-11 ','YYYY-MM-DD HH24:MI:SS')   FROM DUAL;
---日期中没写时间,to_char控制会显示
SELECT TO_CHAR(TO_DATE('2010-09-11 ','YYYY-MM-DD HH24:MI:SS'),
'MM-DD-YYYY HH24:MI:SS')   FROM DUAL;
--如果不要格式控制,可以直接
SELECT SUBSTR(123456,2,3) FROM DUAL;

---其他函数
----NVL,两个参数,如果薪水为空,则返回资金
SELECT NVL(EMP.SALARY,EMP.COMMISSION) FROM EMPLOYEE EMP;
-----NVL2三个参数
SELECT EMP.SALARY,NVL2(EMP.HIREDATE,EMP.SALARY,EMP.COMMISSION) FROM EMPLOYEE EMP;
---COALESCEL
SELECT EMP.SALARY,EMP.COMMISSION,COALESCE(NULL,EMP.SALARY,EMP.COMMISSION,0) FROM EMPLOYEE EMP;

----用于可以手动修改
SELECT * FROM EMPLOYEE FOR UPDATE;

---多行函数,分组函数
----MAX,MIN,AVG,SUM 忽略空值
---COUNT,包括空行
SELECT MAX(EMP.SALARY),MIN(EMP.SALARY),AVG(EMP.SALARY),
SUM(EMP.SALARY), COUNT(*) FROM EMPLOYEE EMP;
---
SELECT COUNT(EMP.HIREDATE) 列中值的个数 FROM EMPLOYEE EMP;

---WHERE,GROUP BY,HAVING
---WHERE为GROUP BY服务,GROUP BY 为HAVING服务,HAVING 再控制最后的显示结果

SELECT EMP.DEPTID, MAX(EMP.SALARY) 最大工资 FROM EMPLOYEE EMP
WHERE EMP.DEPTID IN('01','02')---1首先选择符合DEPTID的行
GROUP BY EMP.DEPTID---2对符合条件的行进行分组
HAVING MAX(EMP.SALARY) >= 4000----3对要显示的分组进行限制
ORDER BY 最大工资 DESC;

SELECT * FROM EMPLOYEE;
SELECT * FROM EMPLOYEE FOR UPDATE;

--分部门统计学位为'1'的员工的平均工资
SELECT EMP.DEPTID, AVG(EMP.SALARY) FROM EMPLOYEE EMP
WHERE EMP.QUALID = '1'
GROUP BY EMP.DEPTID
----统计每种学位的平均工资
SELECT EMP.QUALID,AVG(EMP.SALARY) FROM EMPLOYEE EMP
GROUP BY EMP.QUALID
---统计每个部门的总年薪
SELECT EMP.DEPTID,SUM(COALESCE(EMP.SALARY + EMP.COMMISSION, EMP.SALARY,EMP.COMMISSION,0)) * 12 总年薪 FROM EMPLOYEE EMP
GROUP BY EMP.DEPTID

SELECT * FROM EMPLOYEE;
---统计每个部门员工资金大于1000 的最小工资再按
SELECT EMP.DEPTID ,MIN(EMP.SALARY)   最小工资
   FROM EMPLOYEE EMP
   WHERE EMP.COMMISSION > 1000
   GROUP BY EMP.DEPTID
--- ORDER BY 最小工资 desc;

----员工姓名部门名
SELECT     EMP.NAME,DEP.DEPTNAME
       FROM EMPLOYEE EMP,DEPARTMENT DEP
       WHERE EMP.DEPTID = DEP.DEPTID

----员工姓名学位名
SELECT   EMP.NAME,QUA.QUALNAME
      FROM EMPLOYEE EMP,QUAFICATION QUA
      WHERE EMP.QUALID = QUA.QUALID

-----SQL 1999新连接标准
SELECT EMP.EMPLOYEEID, DEPT.DEPTID
FROM EMPLOYEE EMP
CROSS JOIN DEPTMENT DEPT;

SELECT EMP.EMPLOYEEID, DEPTID
FROM EMPLOYEE EMP
NATURAL JOIN DEPTMENT DEPT;

SELECT EMP.EMPLOYEEID, DEPTID
FROM EMPLOYEE EMP
JOIN DEPTMENT DEPT USING (DEPTID);

----教学部的员工姓名 ,学位名,工资等级    SELECT * FROM EMPLOYEE;
--- SELECT * FROM EMPLOYEE FOR UPDATE
SELECT EMP.NAME,QUA.QUALNAME, LEV.LEVELNO,QUA.QUALID
       FROM EMPLOYEE EMP, QUAFICATION QUA, EMPLEVEL LEV , DEPARTMENT DEP
       WHERE DEP.DEPTNAME = '教学部'
       AND EMP.QUALID = QUA.QUALID
       AND EMP.SALARY BETWEEN LEV.LOWSALARY AND LEV.LOWSALARY;

--不相关子查询

---薪水高于0004号员工的薪水的员工，他的ID
SELECT OUTEMP.EMPLOYEEID
FROM EMPLOYEE OUTEMP
WHERE OUTEMP.SALARY >
        (SELECT INEMP.SALARY
           FROM EMPLOYEE INEMP
           WHERE INEMP.EMPLOYEEID = '0004');

----伪列
--ROWID用它来唯一标识一行数据（与磁盘地址对应）,默认按ROWID排序
SELECT EMP.EMPLOYEEID,ROWID FROM EMPLOYEE EMP;
---ROWNUM对查询结果集依次编号（默认从1开始）
SELECT EMP.EMPLOYEEID,ROWID,ROWNUM FROM EMPLOYEE EMP;
---编号打乱了，因为一开始它会默认按ROWID或SELECT后的第一关键字排序后，
---就把ROWNUM编好号,如果再按其他关键字排序，则编号就打乱了。
SELECT EMP.NAME,EMP.NAME,ROWID,ROWNUM,EMP.EMPLOYEEID
FROM EMPLOYEE EMP
ORDER BY EMP.NAME

---TOP_N查询
--1先排序后作为一个子结果，2再查询
SELECT SUB.EMPLOYEEID,SUB.SALARY,ROWNUM
    FROM (SELECT EMP.EMPLOYEEID,EMP.SALARY
             FROM EMPLOYEE EMP
             ORDER BY SALARY) SUB
   WHERE ROWNUM <= 3;

---ROWNUME要想使用>或>=操作它，只能先为它起别名才行

---翻页查询
SELECT SUBB.EMPLOYEEID,SUBB.SALARY,SUBB.RN
FROM
( SELECT SUB.EMPLOYEEID,SUB.SALARY,ROWNUM RN
    FROM (SELECT EMP.EMPLOYEEID,EMP.SALARY
             FROM EMPLOYEE EMP
             ORDER BY SALARY) SUB) SUBB
   WHERE RN <= 5 AND RN >=2;

---相关的子查询
---一个部门中，工资最高的员工ID
SELECT OUTEMP.EMPLOYEEID
     FROM EMPLOYEE OUTEMP
     WHERE OUTEMP.SALARY = (
      SELECT MAX(INEMP.SALARY)
         FROM EMPLOYEE INEMP
         WHERE OUTEMP.DEPTID = INEMP.DEPTID)

---EXISTS
---如果一个部门中有成员，则输出这个部门的ID
SELECT DEPT.DEPTID
FROM DEPARTMENT DEPT
WHERE EXISTS (
SELECT 'AA'
   FROM EMPLOYEE EMP
     WHERE DEPT.DEPTID = EMP.DEPTID)

-----集合操作
SELECT * FROM EMPLOYEE FOR UPDATE;
SELECT * FROM DEPARTMENT;
---0918 02 市场部一行为重复
--UNION 并( 去重)
SELECT EMP.EMPLOYEEID,EMP.NAME   FROM EMPLOYEE EMP
UNION
SELECT DEP.DEPTID,DEP.DEPTNAME   FROM DEPARTMENT DEP
----UNION 并( 不去重)
SELECT EMP.EMPLOYEEID,EMP.NAME   FROM EMPLOYEE EMP
UNION ALL
SELECT DEP.DEPTID,DEP.DEPTNAME   FROM DEPARTMENT DEP
--INTERSECT交
SELECT EMP.EMPLOYEEID,EMP.NAME   FROM EMPLOYEE EMP
INTERSECT
SELECT DEP.DEPTID,DEP.DEPTNAME   FROM DEPARTMENT DEP
--MINUS   差,去掉公共部分
SELECT EMP.EMPLOYEEID,EMP.NAME   FROM EMPLOYEE EMP
MINUS
SELECT DEP.DEPTID,DEP.DEPTNAME   FROM DEPARTMENT DEP

---sequence创建序列
CREATE SEQUENCE EMPLVL_SEQ
INCREMENT BY 1 --步长
START WITH 1   -- 起始值
NOMAXVALUE   --设置没有最大值
NOCYCLE    --设置没有循环
CACHE 5; --缓存
---建表
CREATE TABLE EMPLVL
(LVLNO VARCHAR2(2),
LOWSALARY NUMBER,
HIGHSALARY NUMBER);
--EMPLVL_SEQ.NEXTVAL开始为起始值
INSERT INTO EMPLVL VALUES(EMPLVL_SEQ.NEXTVAL, 1000, 2000);
--以后为上一次的值加步长
INSERT INTO EMPLVL VALUES(EMPLVL_SEQ.NEXTVAL, 2001, 3000);
INSERT INTO EMPLVL VALUES(EMPLVL_SEQ.NEXTVAL, 3001, 4000);
---查询当前序列号
SELECT EMPLVL_SEQ.CURRVAL FROM DUAL;
--SELECT *FROM EMPLVL

----视图
---创建视图
CREATE OR REPLACE VIEW SHITU
AS SELECT STU.ID,STU.NAME
FROM STU
--像表一样操作视图
SELECT * FROM SHITU;

---事务控制
--创建表
CREATE TABLE STU(
ID CHAR(10),
NAME VARCHAR2(40) NOT NULL,
SEX CHAR(1) CONSTRAINTS STU_SEX_NN NOT NULL,
BIRTHDAY DATE,
CONSTRAINTS STU_ID_PK PRIMARY KEY (ID)
);
---SELECT * FROM STU
INSERT INTO STU VALUES('0002', 'YAO','1',NULL);
--回滚
ROLLBACK;
--写入磁盘
COMMIT

INSERT INTO STU VALUES('0003', 'YAO','1',NULL);
INSERT INTO STU VALUES('0004', 'YAO','1',NULL);
--设置保存点
SAVEPOINT X;
INSERT INTO STU VALUES('0005', 'YAO','1',NULL);
INSERT INTO STU VALUES('0006', 'YAO','1',NULL);
--回滚到保存点
ROLLBACK TO SAVEPOINT X;
--写入磁盘
COMMIT;

-----带图片的表
CREATE TABLE IMAGEFILES (
ID VARCHAR2(3),
FILENAME VARCHAR2(40),
IMAGE BLOB,
CONSTRAINTS IMG_PK_ID PRIMARY KEY (ID)
);