Oracle SQL大全

一、 基本操作表和数据

-- 建表CREATE TABLE ab_student (id number(4))create table ab_class(id number(4),name varchar2(20))-- 修改表ALTER TABLE ab_student ADD(classid number(4))ALTER TABLE ab_student ADD(birthday date)ALTER TABLE ab_student MODIFY(classid number(4))ALTER TABLE ab_student DROP  COLUMN classidRENAME ab_student TO ab_student2-- 删除表DROP TABLE ab_studentDROP TABLE ab_class-- 增加一条数据INSERT INTO ab_student(id, birthday) VALUES(2, '02-8月-13')ALTER SESSION SET nls_date_format='yyyy-mm-dd'INSERT INTO ab_student(id, birthday) VALUES(3, '2013-08-05')INSERT INTO ab_student(id, birthday) VALUES(4, '2014-05-05')INSERT INTO ab_student(id, birthday) VALUES(5, '1992-05-25')INSERT INTO ab_student(id) VALUES(10 )-- 删除一条数据DELETE FROM ab_student WHERE id = 1-- 删除所有记录，表结构还在，写日志，可以恢复的，速度慢DELETE FROM ab_student-- delete的数据可以恢复，一个有经验的dba，在确保完成无误的情况下要定期创建还原点。SAVEPOINT sp_201308020941 -- 创建保存点DELETE FROM ab_studentROLLBACK TO sp_201308020941 -- 恢复到保存点SELECT * FROM ab_studentSELECT * FROM ab_student WHERE birthday IS NULLSELECT * FROM ab_student WHERE birthday IS NOT NULL-- 连接查询INSERT INTO ab_class(id, name) VALUES(1, '软件班')INSERT INTO ab_class(id, name) VALUES(2, '软测班')INSERT INTO ab_class(id, name) VALUES(3, '多媒体班')INSERT INTO ab_class(id, name) VALUES(5, '网络班')SELECT * FROM ab_studentSELECT * FROM ab_class-- 内连接SELECT *FROM ab_student aINNER JOIN ab_class b ON b.id = a.classid-- 左外连接SELECT *FROM ab_student aLEFT OUTER JOIN ab_class b ON b.id = a.classid-- 右外连接SELECT *FROM ab_student aRIGHT OUTER JOIN ab_class b ON b.id = a.classid-- 交叉连接SELECT *FROM ab_student aCROSS JOIN ab_class bUPDATE ab_student SET birthday = '1992-05-25' WHERE id = 3UPDATE ab_student SET classid =1 WHERE id = 3UPDATE ab_student SET classid =3 WHERE id = 5UPDATE ab_student SET classid =5 WHERE id = 10DROP TABLE ab_student --删除表的结构和数据；DELETE FROM ab_student where id = 1 --删除一条记录；TRUNCATE TABLE ab_student --删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快。

二、 表的使用技巧

-- 1. 查看表结构DESC SCOTT.EMPSELECT * FROM user_tab_columns WHERE table_name= 'EMP'GOSELECT 'SCOTT.EMP' FROM all_tables -- 所有用户的表 GO

-- 2 表复制语句CREATE TABLE SCOTT.AA_DEPT (    DEPTNO     NUMBER(2,0) NOT NULL,    DNAME      VARCHAR2(14) NULL,    LOC        VARCHAR2(13) NULL,    CONSTRAINT PK_AA_DEPT PRIMARY KEY(DEPTNO)     NOT DEFERRABLE     VALIDATE)GOINSERT INTO TABLE aa_dept(deptno, dname, loc)SELECT a.deptno, a.dname, a.loc FROM DEPT aGOSELECT deptno, dname, loc FROM AA_DEPT

-- 3. 用查询结果创建新表，这个命令是一种快捷的建表方式CREATE TABLE AB_DEPT (id, name, sal, job, deptno) AS SELECT empno, ename, sal, job, deptno FROM empSELECT * FROM AB_DEPT

三、 SQL基本查询技巧

-- 1. 查询所有SELECT * FROM EMP

-- 2.使用WHERE子句-- 查询smith所在部门，工作，薪水SELECT deptno, job, sal FROM emp WHERE ename = 'SMITH'-- 显示工资高于三千的员工SELECT ename, sal FROM emp WHERE sal > 3000-- 如何查找1982.1.1后入职的员工ALTER SESSION SET nls_date_format='yyyy-mm-dd'SELECT ename, hiredate FROM emp WHERE hiredate > '1982-01-01'-- 如何显示工资在2000到3000的员工SELECT ename, sal FROM emp WHERE sal >= 2000 AND sal <= 3000

-- 3. 使用IN -- 如何显示empno为7844,7839,123,456的雇员情况？SELECT * FROM emp WHERE empno IN (7844, 7839, 123, 456)

-- 4. 排除重复行DISTINCTSELECT DISTINCT  deptno, job FROM emp

-- 5. 使用列的别名ASSELECT ename AS "姓名" , sal * 12 AS "年收入" FROM emp

-- 6. 连接字符串||SELECT ename || ' is a ' || job FROM emp

-- 7. 使用LIKE关键字，%：表示0到多个字符 _：表示任意单个字符-- 如何显示首字符为s的员工姓名和工资？SELECT ename,sal FROM emp WHERE ename like 'S%'-- 如何显示第三个字符为大写o的所有员工的姓名和工资？SELECT ename,sal FROM emp WHERE ename LIKE '__O%'

-- 8. 使用内置函数-- nvl()函数/* 格式为：nvl(string1, replace_with) 功能：如果string1为null，则nvl函数返回replace_with的值，否则返回string1的值。 */SELECT sal * 13 + NVL(comm, 0) * 13 AS "年薪", ename, comm FROM emp-- 如何显示没有上级的雇员的情况SELECT ename,mgr FROM emp WHERE mgr IS NULL-- count()函数SELECT COUNT(*) FROM emp

-- 9. 使用逻辑操作符号-- 问题：查询工资高于500或者是岗位为manager的雇员，同时还要满足他们的姓名首字母为大写的J？SELECT * FROM emp WHERE (sal > 500 Or job = 'MANAGER') AND ename LIKE 'J%'

-- 10. 使用order by字句 默认asc-- 问题：如何按照工资从低到高的顺序显示雇员的信息？SELECT * FROM emp ORDER BY sal-- 问题：按照部门号升序而雇员的工资降序排列SELECT * FROM emp ORDER BY deptno ASC, sal DESC

-- 11. 使用列的别名排序-- 问题：按年薪排序SELECT ename, (sal + NVL(comm, 0)) * 12 AS "年薪" FROM emp ORDER BY "年薪" ASC

-- 12. 聚合函数用法：max，min，avg，sum，count-- 问题：如何显示所有员工中最高工资和最低工资？SELECT MAX(sal), MIN(sal) FROM emp e-- 最高工资那个人是谁？SELECT ename, sal FROM emp WHERE sal = (SELECT MAX(sal) FROM emp)/*错误写法：select ename, sal from emp where sal=max(sal)注意：select ename, max(sal) from emp;这语句执行的时候会报错，说ora-00937：非单组分组函数。因为max是分组函数，而ename不是分组函数.......但是select min(sal), max(sal) from emp;这句是可以执行的。因为min和max都是分组函数，就是说：如果列里面有一个分组函数，其它的都必须是分组函数，否则就出错。这是语法规定的*/-- 问题：如何显示所有员工的平均工资和工资总和SELECT AVG(e.sal) AS "所有员工平均工资", SUM(e.sal) AS "所有员工工资总和" FROM emp e-- 查询最高工资员工的名字，工作岗位SELECT e.ename, e.job FROM emp e WHERE e.sal = (SELECT MAX(sal) FROM emp)-- 显示工资高于平均工资的员工信息SELECT e.ename FROM emp e WHERE e.sal > (SELECT AVG(sal) FROM emp)

-- 13. GROUP BY的用法-- 问题：如何显示每个部门的平均工资和最高工资？SELECT  AVG(sal) AS "平均工资", MAX(sal) AS "最高工资", deptnoFROM empGROUP BY deptno--（注意：这里暗藏了一点，如果你要分组查询的话，分组的字段deptno一定要出现在查询的列表里面，否则会报错。因为分组的字段都不出现的话，就没办法分组了）-- 问题：显示每个部门的每种岗位的平均工资和最低工资？SELECT AVG(sal) AS "平均工资", MIN(sal) AS "最低工资", deptno, jobFROM empGROUP BY deptno, job-- 问题：显示平均工资低于2000的部门号和它的平均工资？SELECT AVG(sal), deptnoFROM empGROUP BY deptnoHAVING AVG(sal) < 2000/*对数据分组的总结(1)分组函数只能出现在选择列表、having、order by子句中(不能出现在where中)(2)如果在select语句中同时包含有group by, having, order by 那么它们的顺序是group by, having, order by(3)在选择列中如果有列、表达式和分组函数，那么这些列和表达式必须有一个出现在group by 子句中，否则就会出错。如select deptno, avg(sal), max(sal) from emp group by deptno having avg(sal) < 2000;这里deptno就一定要出现在group by中*/

-- 14. 多表查询-- 显示雇员名，雇员工资及所在部门的名字SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno-- 问题：显示部门号为10的部门名、员工名和工资SELECT d.deptno , d.dname, e.deptno, e.empno, e.ename, e.sal FROM emp e, dept d WHERE 1 = 1 AND e.deptno = d.deptno AND d.deptno = 10-- 问题：显示雇员名，雇员工资及所在部门的名字，并按部门排序SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno ORDER BY e.deptno ASC-- 自连接。自连接是指在同一张表的连接查询-- 问题：显示某个员工的上级领导的姓名？比如显示员工‘FORD’的上级SELECT  worker.ename, boss.ename FROM emp worker, emp boss WHERE worker.mgr = boss.empno AND worker.ename = 'FORD'

-- 15. 单行子查询？-- 单行子查询是指只返回一行数据的子查询语句-- 请思考：显示与SMITH同部门的所有员工？SELECT e.deptno, e.enameFROM emp eWHERE deptno = (SELECT deptno FROM emp WHERE ename = 'SMITH')

-- 16. 多行子查询-- 多行子查询指返回多行数据的子查询-- 请思考：如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号SELECT e.ename, e.job, e.sal, e.deptnoFROM emp eWHERE e.job IN(SELECT job FROM emp WHERE deptno = 10)--  在多行子查询中使用all操作符-- 问题：如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号？SELECT e.ename, e.sal, e.deptnoFROM emp eWHERE e.sal > ALL(SELECT sal FROM emp WHERE deptno = 30)SELECT e.ename, e.sal , e.deptnoFROM emp eWHERE e.sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30)

-- 17. 合并查询/*有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号union，union all，intersect，minus。多用于数据量比较大的数据局库，运行速度快。*/1). union该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。2).union all该操作符与union相似，但是它不会取消重复行，而且不会排序。3). intersect使用该操作符用于取得两个结果集的交集。4). minus使用该操作符用于取得两个结果集的差集，他只会显示存在第一个集合中，而不存在第二个集合中的数据。SELECT ename, sal, job FROM emp WHERE sal > 2500/* UNION */ /*  UNION ALL */ /* INTERSECT */ MINUSSELECT ename, sal, job FROM emp WHERE job = 'MANAGER'

四、分页查询

1.  方法一 根据rowid来分

SELECT *FROM EMPWHERE ROWID IN       (SELECT RID          FROM (SELECT ROWNUM RN, RID                  FROM (SELECT ROWID RID, EMPNO FROM EMP ORDER BY EMPNO DESC)                 WHERE ROWNUM <= 5         WHERE RN > 0  ORDER BY EMPNO DESC

2. 方法二 按分析函数来分

SELECT *FROM (SELECT T.*, ROW_NUMBER() OVER(ORDER BY empno DESC) RK FROM emp T)WHERE RK <= 5AND RK > 0

3. 方法三 按rownum 来分

SELECT *FROM (SELECT T.*, ROWNUM RN          FROM (SELECT * FROM EMP ORDER BY EMPNO DESC) T         WHERE ROWNUM <= 5)WHERE RN > 0

五、 复杂表查询

1、查询每个职员的姓名、工种、入社日期，工资和年工资。

SELECT e.ename,e.job,e.hiredate,e.sal,e.sal*12 FROM emp e

2、查找部门平均工资在2000元以上的部门号和部门名称，按平均工资的降序排列。

SELECT v.deptno,v.dnameFROM (        SELECT AVG(e.sal) avgsal, d.deptno, d.dname        FROM emp e        LEFT JOIN dept d ON e.deptno=d.deptno        GROUP BY e.deptno,d.deptno,d.dname) vWHERE v.avgsal > 2000

3、查找营业部（SALES）比研究部（RESEARCH）平均工资都高的职员的情况。（要求使用子查询来完成）

SELECT e.*FROM emp eLEFT JOIN dept d ON e.deptno=d.deptnoWHERE d.dname='SALES' AND e.sal > (      SELECT DISTINCT AVG(e.sal) OVER (PARTITION BY e.deptno)      FROM emp e      LEFT JOIN dept d ON e.deptno=d.deptno      WHERE d.dname='RESEARCH' )

4、在EMP表的MGR属性列上建一个名为mgr_bit的位图索引。

CREATE BITMAP INDEX mgr_bit ON emp(mgr)

5、查询各个部门的最高工资的职员情况

SELECT e.ename, e.job, e.sal, e.deptno FROM empWHERE (sal,deptno) IN (SELECT MAX(e.sal), e.deptno FROM emp e GROUP BY e.deptno)SELECT e.ename, e.job, e.sal, e.deptnoFROM scott.emp e, (select e.deptno, MAX(e.sal) sal from scott.emp e group by e.deptno) meWHERE e.deptno = me.deptno AND e.sal = me.sal;

SELECT e.ename, e.job, e.sal, e.deptnoFROM (SELECT e.ename,               e.job,               e.sal,               e.deptno,               RANK() OVER(PARTITION BY e.deptno ORDER BY e.sal desc) RANK FROM scott.emp e) e WHERE e.rank = 1;

SELECT e.ename, e.job, e.sal, e.deptnoFROM (SELECT e.ename,               e.job,               e.sal,               e.deptno,               DENSE_RANK() OVER(PARTITION BY e.deptno ORDER BY e.sal desc) RANK FROM scott.emp e) e WHERE e.rank = 1;

6、 查询各个部门的最高工资的职员情况，同时算出雇员工资与部门最高/最低工资的差额

select e.ename,         e.job,         e.sal,         e.deptno,         e.sal - me.min_sal diff_min_sal,         me.max_sal - e.sal diff_max_sal    from scott.emp e,         (select e.deptno, min(e.sal) min_sal, max(e.sal) max_sal            from scott.emp e           group by e.deptno) me   where e.deptno = me.deptno   order by e.deptno, e.sal;select e.ename,       e.job,       e.sal,       e.deptno,       nvl(e.sal - min(e.sal) over(partition by e.deptno), 0) diff_min_sal,       nvl(max(e.sal) over(partition by e.deptno) - e.sal, 0) diff_max_sal  from scott.emp e;

7、 计算个人工资与比自己高一位/低一位工资的差额

select e.ename,       e.job,       e.sal,       e.deptno,       lead(e.sal, 1, 0) over(partition by e.deptno order by e.sal) lead_sal,       lag(e.sal, 1, 0) over(partition by e.deptno order by e.sal) lag_sal,       nvl(lead(e.sal) over(partition by e.deptno order by e.sal) - e.sal,           0) diff_lead_sal,       nvl(e.sal - lag(e.sal) over(partition by e.deptno order by e.sal), 0) diff_lag_sal  from scott.emp e; 

8、查询各个部门比平均工资高的员工

SELECT e.ename, e.deptno, e.sal, ds.mysalFROM emp e, (SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno) dsWHERE e.deptno = ds.deptno AND e.sal > ds.mysal;

9、 显示deptno和job唯一的员工薪资，并按降序排列

SELECT a.ename, a.sal, a.deptno, a.jobFROM emp aWHERE a.empno in(SELECT MAX(e.empno)  FROM emp eGROUP BY e.deptno,e.job)ORDER BY a.sal DESCSELECT a.ename, a.sal, a.deptno, a.jobFROM emp a, (SELECT MAX(e.rowid) AS myrowid, e.deptno, e.job, count(*)FROM emp eGROUP BY e.deptno,e.jobHAVING count(*) >= 1) meWHERE a.rowid IN me.myrowidORDER BY a.sal DESC