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 classid RENAME ab_student TO ab_student2 -- 删除表 DROP TABLE ab_student DROP 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_student ROLLBACK TO sp_201308020941 -- 恢复到保存点 SELECT * FROM ab_student SELECT * FROM ab_student WHERE birthday IS NULL SELECT * 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_student SELECT * FROM ab_class -- 内连接 SELECT * FROM ab_student a INNER JOIN ab_class b ON b.id = a.classid -- 左外连接 SELECT * FROM ab_student a LEFT OUTER JOIN ab_class b ON b.id = a.classid -- 右外连接 SELECT * FROM ab_student a RIGHT OUTER JOIN ab_class b ON b.id = a.classid -- 交叉连接 SELECT * FROM ab_student a CROSS JOIN ab_class b UPDATE ab_student SET birthday = '1992-05-25' WHERE id = 3 UPDATE ab_student SET classid =1 WHERE id = 3 UPDATE ab_student SET classid =3 WHERE id = 5 UPDATE ab_student SET classid =5 WHERE id = 10 DROP TABLE ab_student --删除表的结构和数据; DELETE FROM ab_student where id = 1 --删除一条记录; TRUNCATE TABLE ab_student --删除表中的所有记录,表结构还在,不写日志,无法找回删除的记录,速度快。
二、 表的使用技巧
-- 1. 查看表结构 DESC SCOTT.EMP SELECT * FROM user_tab_columns WHERE table_name= 'EMP' GO SELECT '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 ) GO INSERT INTO TABLE aa_dept(deptno, dname, loc) SELECT a.deptno, a.dname, a.loc FROM DEPT a GO SELECT deptno, dname, loc FROM AA_DEPT
-- 3. 用查询结果创建新表,这个命令是一种快捷的建表方式 CREATE TABLE AB_DEPT (id, name, sal, job, deptno) AS SELECT empno, ename, sal, job, deptno FROM emp SELECT * 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. 排除重复行DISTINCT SELECT DISTINCT deptno, job FROM emp
-- 5. 使用列的别名AS SELECT 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 "最高工资", deptno FROM emp GROUP BY deptno --(注意:这里暗藏了一点,如果你要分组查询的话,分组的字段deptno一定要出现在查询的列表里面,否则会报错。因为分组的字段都不出现的话,就没办法分组了) -- 问题:显示每个部门的每种岗位的平均工资和最低工资? SELECT AVG(sal) AS "平均工资", MIN(sal) AS "最低工资", deptno, job FROM emp GROUP BY deptno, job -- 问题:显示平均工资低于2000的部门号和它的平均工资? SELECT AVG(sal), deptno FROM emp GROUP BY deptno HAVING 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.ename FROM emp e WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'SMITH')
-- 16. 多行子查询 -- 多行子查询指返回多行数据的子查询 -- 请思考:如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号 SELECT e.ename, e.job, e.sal, e.deptno FROM emp e WHERE e.job IN(SELECT job FROM emp WHERE deptno = 10) -- 在多行子查询中使用all操作符 -- 问题:如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号? SELECT e.ename, e.sal, e.deptno FROM emp e WHERE e.sal > ALL(SELECT sal FROM emp WHERE deptno = 30) SELECT e.ename, e.sal , e.deptno FROM emp e WHERE 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 */ MINUS SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'
四、分页查询
1. 方法一 根据rowid来分
SELECT * FROM EMP WHERE 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 <= 5 AND 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.dname FROM ( 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) v WHERE v.avgsal > 2000
3、查找营业部(SALES)比研究部(RESEARCH)平均工资都高的职员的情况。(要求使用子查询来完成)
SELECT e.* FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno WHERE 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 emp WHERE (sal,deptno) IN (SELECT MAX(e.sal), e.deptno FROM emp e GROUP BY e.deptno) SELECT e.ename, e.job, e.sal, e.deptno FROM scott.emp e, (select e.deptno, MAX(e.sal) sal from scott.emp e group by e.deptno) me WHERE e.deptno = me.deptno AND e.sal = me.sal;
SELECT e.ename, e.job, e.sal, e.deptno FROM (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.deptno FROM (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.mysal FROM emp e, (SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno) ds WHERE e.deptno = ds.deptno AND e.sal > ds.mysal;
9、 显示deptno和job唯一的员工薪资,并按降序排列
SELECT a.ename, a.sal, a.deptno, a.job FROM emp a WHERE a.empno in ( SELECT MAX(e.empno) FROM emp e GROUP BY e.deptno,e.job ) ORDER BY a.sal DESC SELECT a.ename, a.sal, a.deptno, a.job FROM emp a, ( SELECT MAX(e.rowid) AS myrowid, e.deptno, e.job, count(*) FROM emp e GROUP BY e.deptno,e.job HAVING count(*) >= 1 ) me WHERE a.rowid IN me.myrowid ORDER BY a.sal DESC
![]() |
作者:沙漏哟 出处:计算机的未来在于连接 本文版权归作者和博客园共有,欢迎转载,请留下原文链接 微信随缘扩列,聊创业聊产品,偶尔搞搞技术 |