##oracle查询操作
emp 员工表（empno 员工号/ename 员工姓名/job 工作/mgr 上级编号/hiredate 受雇日期/sal 薪金/comm 佣金/deptno 部门编号） dept 部门表（deptno 部门编号/dname 部门名称/loc 地点） ---------------SELECT语句的完整查询 --------------- --1.列描述 DESC EMP; --2.检索所有列 Select * from EMP; --3.检索特定列:员工号, 员工姓名 SELECT EMPNO, ENAME, JOB FROM EMP T; --5.distinct 取消重复的行 ,显示不重复的工作 Select distinct job from emp; --6.计算列,显示员工姓名，12月薪金 select ename,sal*12 from emp; --7.列别名 员工姓名 别名“员工” select ename "员工" from emp --8.使用空值 a.使用nvl和nvl2处理空值 select ename,sal+NVL(comm,0) from emp; select ename,comm,sal,NVL2(comm,sal+comm,sal) from emp; --NVL2(E1, E2, E3)的功能为：如果E1为NULL，则函数返回E3，若E1不为null，则返回E2。 --9.使用连接字符串 select ename ||'工作是'||job from emp; select concat(concat(ename, '工作是'),job) from emp --注意结果为甚么和实际不同 SELECT JOB, SUM(T.SAL + T.COMM) INCOME FROM EMP T WHERE T.DEPTNO IN (2, 3) GROUP BY JOB; SELECT JOB, SUM(T.SAL + T.COMM) INCOME FROM EMP T WHERE T.DEPTNO IN (2, 3) GROUP BY JOB HAVING SUM(T.SAL+T.COMM)>1000; ---------------关于WHERE查询条件使用 --------------- --1.子句查询为数值 select ename,sal from emp where sal > 2000 --2.子句查询为字符 select ename,sal from emp where ename = 'SCOTT'; --3.子句查询为日期: select ename,sal from emp where hiredate= to_date(19801217,'yyyymmdd'); select ename,sal from emp where hiredate > to_date(19801217,'yyyymmdd'); --4.Between and 查询 Select ename,sal,hiredate from emp where sal between 2000 and 3000 --5.LIKE 查询(通配符 %,_) select ename,sal,hiredate from emp where ename like 'S%' select ename,sal,hiredate from emp where ename like '___T%' --6.IN的使用 select ename,sal,hiredate from emp where sal in (800,3000); --7.NULL值的判断 select ename,sal,hiredate,mgr from emp where mgr is null;(注意这里不能使用=null) --8.AND OR NOT 使用(顺序 NOT > OR > AND) select ename,sal,deptno,job from emp where deptno = 20 and job = 'CLERK'; select ename,sal,deptno,job from emp where deptno = 20 or job = 'CLERK'; ---------------统计分析 --------------- --ORDER BY 排序: 1.默认按升序排序(ASC)，这里NULL放最后 Select ename,sal,COMM from emp order by COMM; Select ename,sal,COMM from emp order by COMM ASC; 2.降序排序 ，按佣金降序 Select ename,sal,COMM from emp order by COMM DESC; 3.多列排序， Select ename,sal,COMM from emp order by COMM DESC,SAL; 4.非选择列排序 Select ename,sal from emp order by COMM DESC ; 5.使用别名排序 Select ename,sal,COMM "奖金" from emp order by "奖金" DESC ; 6.使用位置编号排序， Select ename,sal,COMM "奖金" from emp order by 2 DESC ; --group by 数据分组 分组求最大，最小值 select max(sal),min(sal) from emp; 2.平均值，求和，数量 select max(sal),min(sal),sum(sal),avg(sal),count(sal) from emp 3.求总数 select count(comm) from emp; Select count(distinct comm) from emp; Select count(*) from emp; --SELECT * FROM EMP FOR UPDATE --having子句的使用 1.Group by 单列 select deptno,max(sal),avg(sal) from emp group by deptno; 2.Group by 多列 select deptno,job,max(sal),avg(sal) from emp group by deptno,job order by deptno,job; 3.Having 限制显示 select deptno,job,max(sal),avg(sal) from emp group by deptno,job having avg(sal) > 1000 order by deptno,job; 4.Order by 排序 select deptno,job,max(sal),avg(sal) from emp group by deptno,job having avg(sal) > 1000 order by avg(sal); --报表排序 如果是ROLLUP(A, B, C)的话，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。 如果是GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。 1.Rollup 与 Cube 生成小计与总计： SELECT DEPTNO, JOB, AVG(SAL) FROM EMP GROUP BY ROLLUP(DEPTNO, JOB); SELECT DEPTNO, JOB, AVG(SAL) FROM EMP GROUP BY CUBE(DEPTNO, JOB) ORDER BY DEPTNO ---------------多表连接--------------- 1.等值连接 --员工姓名，部门 SELECT ENAME, SAL, DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO ORDER BY D.DEPTNO; --1部门 SELECT ENAME, SAL, DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND D.DEPTNO = 10 3.自连接 SELECT A.*, B.ENAME MNAME FROM EMP A, EMP B WHERE A.MGR = B.EMPNO ORDER BY A.EMPNO --某员工，层级关系 SELECT a.*, LEVEL FROM EMP A CONNECT BY A.EMPNO = PRIOR MGR START WITH A.EMPNO = 7934 4.外连接 --公司员工信息 (左连接) SELECT A.DNAME, B.ENAME, B.SAL FROM DEPT A LEFT JOIN EMP B ON A.Deptno = B.Deptno; --公司员工信息(右连接) SELECT A.DNAME, B.ENAME, B.SAL FROM DEPT A RIGHT JOIN EMP B ON A.DEPTNO = B.DEPTNO AND B.DEPTNO = 1; --公司员工信息(完全连接) SELECT A.DNAME, B.ENAME, B.SAL FROM DEPT A FULL JOIN EMP B ON A.DEPTNO = B.DEPTNO AND A.DEPTNO = 1 5.子查询 --单行子查询 SELECT * FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = 'SCOTT') AND ENAME != 'SCOTT' --多行子查询 SELECT * FROM EMP WHERE JOB IN (SELECT JOB FROM EMP WHERE DEPTNO = 10); SELECT * FROM EMP WHERE sal >= ALL(SELECT sal FROM EMP WHERE DEPTNO = 30); SELECT * FROM EMP WHERE sal >= ANY(SELECT sal FROM EMP WHERE DEPTNO = 30); --相关子查询 EXISTS与IN的效率对比 SELECT * FROM EMP A WHERE EXISTS (SELECT 1 FROM DEPT B WHERE A.DEPTNO = B.DEPTNO AND B.DNAME = 'ACCOUNTING') SELECT * FROM EMP A WHERE a.DEPTNO in (SELECT B.DEPTNO FROM DEPT B WHERE B.DNAME = '技术部') --FROM子查询 SELECT ENAME, JOB, SAL FROM EMP, (SELECT DEPTNO, AVG(SAL) AVGSAL FROM EMP GROUP BY DEPTNO) DEPTSTD WHERE DEPTSTD.DEPTNO = EMP.DEPTNO AND EMP.SAL > DEPTSTD.AVGSAL --DML子查询 --insert中使用子查询 INSERT INTO employee SELECT * FROM emp --update中使用子查询 UPDATE emp SET (sal,comm) = (SELECT sal,comm FROM emp WHERE ename = 'SMITH') WHERE job = (SELECT job FROM emp WHERE ename = 'SMITH') --delete 中使用子查询 DELETE FROM emp WHERE job = (SELECT job FROM emp WHERE ename='SMITH') --DDL中使用子查询 --其它查询 -- Case when SELECT ENAME, SAL, CASE WHEN SAL > 1500 THEN 1 WHEN SAL = 1500 THEN 2 ELSE 2 END GRADE FROM EMP --With 子句 WITH EMPL AS ( SELECT DEPTNO,AVG(SAL) AVG_SAL FROM EMP GROUP BY DEPTNO ) SELECT * FROM EMPL A WHERE AVG_SAL > (SELECT SUM(AVG_SAL)/3 FROM EMPL B )