源码-PL/SQL从入门到精通-第六章-查询数据表-Part 2

这部分的代码还是有些干货的，如，rowid, rownum等功能都是首次使用，而且Mysql数据库中并没有这些功能。

--6.1.2 指定查询条件
--简单where子句
SELECT *
  FROM emp6
 WHERE deptno = 20;

SELECT *
  FROM emp;


--使用范围操作符

SELECT empno, ename, job, mgr, hiredate, sal
  FROM emp
 WHERE sal BETWEEN 1500 AND 2500;

--上述SQL语句等价于
SELECT empno, ename, job, mgr, hiredate, sal
  FROM emp
 WHERE sal >= 1500 AND sal <= 2500;

--日期和字符串比较
SELECT empno, ename, job, mgr, hiredate, sal
  FROM emp
 WHERE hiredate BETWEEN TO_DATE ('1981-01-01', 'YYYY-MM-DD')
                    AND TO_DATE ('1981-12-31', 'YYYY-MM-DD');


--有点类似于Java中的case语句（多重选择）或枚举类
SELECT empno, ename, job, mgr, hiredate, sal
  FROM emp
 WHERE job IN ('SALESMAN', 'CLERK', 'ANALYST');


SELECT empno, ename, job, mgr, hiredate, sal
  FROM emp
 WHERE mgr IN (7698, 7839);

--等价于上述SQL语句（由Oracle服务器负责转换）
SELECT empno, ename, job, mgr, hiredate, sal
  FROM emp
 WHERE mgr = 7698 OR mgr = 7839;

--模糊查询
SELECT empno, ename, job, mgr, hiredate, sal
  FROM emp
 WHERE ename LIKE 'J%';
 
SELECT empno, ename, job, mgr, hiredate, sal
  FROM emp
 WHERE hiredate LIKE '%81';

SELECT empno, ename, job, mgr, hiredate, sal
  FROM emp
 WHERE ename LIKE '__A%';



SELECT *
  FROM emp;

--判断Null值
SELECT empno, ename, job, mgr, hiredate
  FROM emp6
 WHERE mgr IS NULL;



SELECT empno, ename, job, mgr, hiredate
  FROM emp
 WHERE mgr IS NOT NULL;

--使用逻辑组合
SELECT empno, ename, job, mgr, hiredate, sal, deptno
  FROM emp
 WHERE deptno = 20 AND hiredate LIKE '%82';
 
SELECT empno, ename, job, mgr, hiredate, sal, deptno
  FROM emp
 WHERE deptno = 20 OR hiredate LIKE '%82';


SELECT empno, ename, job, mgr, hiredate, sal, deptno
  FROM emp
 WHERE NOT (deptno = 20 AND hiredate LIKE '%82');


SELECT *
  FROM emp;

SELECT empno, ename, job, mgr, hiredate, sal, deptno
  FROM emp
 WHERE job NOT IN ('CLERK', 'MANAGER', 'SALESMAN');

SELECT empno, ename, job, mgr, hiredate, sal, deptno
  FROM emp
 WHERE sal NOT BETWEEN 1000 AND 2500;

SELECT empno, ename, job, mgr, hiredate, sal, deptno
  FROM emp
 WHERE ename NOT LIKE '%A%';

--查询结果排序
SELECT   empno, ename, job, mgr, hiredate, sal, deptno
    FROM emp
   WHERE deptno = 20
ORDER BY empno;


SELECT   empno, ename, job, mgr, hiredate, sal, deptno
    FROM emp
   WHERE deptno = 20
ORDER BY empno, ename DESC;

--根据字段列表的索引顺序指定排序
SELECT   empno, ename, job, mgr, hiredate, sal, deptno
    FROM emp
   WHERE deptno = 20
ORDER BY 4 DESC;

SELECT   empno, ename, job, mgr, hiredate, sal, deptno
    FROM emp
   WHERE deptno = 20
ORDER BY 8 DESC;


--6.1.4 使用函数
SELECT empno, ename, hiredate
  FROM emp6 for update order by ename;


SELECT empno, INITCAP (ename) ename, hiredate
  FROM emp
 WHERE deptno = 20;

--首字母大写转换函数及字符串连接函数
SELECT CONCAT (empno, INITCAP (ename)) ename, hiredate,comm
  FROM emp6
 WHERE deptno = 20;
 
--四舍五入函数 
SELECT empno, ename, hiredate,ROUND(comm) comm
  FROM emp6
 WHERE deptno = 20;
 
--记录数统计 
--统计所有记录（包括空值、重复值）
SELECT COUNT (*) 记录条数 FROM emp;

SELECT COUNT (*) 记录条数 FROM emp WHERE deptno=20;



SELECT * FROM emp;


SELECT * FROM dept;


--统计结果包括重复值，但不包括空值
SELECT COUNT(comm) 提成员工数 FROM emp;

--与上述SQL等价（包括重复值）
SELECT COUNT(ALL comm) 提成员工数 FROM emp;


SELECT * from emp;

--不包含重复值
SELECT COUNT(DISTINCT job) 职位个数 FROM emp;

SELECT * FROM emp;

--汇总和平均值
SELECT AVG(sal) 平均薪资,AVG(comm) 平均提成 FROM emp;

SELECT MIN(sal) 最低薪资,MAX(sal) 最高薪资 FROM emp;


SELECT MIN(hiredate) 最早雇佣日期,MAX(hiredate) 最晚雇佣日期 FROM emp;

--最小值和最大值,NVL函数
SELECT MIN (ename), MAX (ename) FROM emp;

SELECT MIN(NVL(comm,0)) 最低提成,MAX(NVL(comm,0)) 最高提成 FROM emp;

DELETE FROM emp WHERE deptno IS NULL;
commit;


--6.1.6分组统计
SELECT   nvl(deptno,0), SUM (sal) 部门薪资小计
    FROM emp
GROUP BY deptno;

SELECT   deptno, SUM (sal) 部门薪资小计
    FROM emp
GROUP BY deptno
ORDER BY SUM (sal);


SELECT  deptno, SUM (sal) 部门薪资小计,AVG(sal) 部门薪资平均值
    FROM emp
GROUP BY deptno
ORDER BY SUM (sal);


SELECT   deptno, job, SUM (sal) 薪资小计
    FROM emp
GROUP BY deptno, job;

--Having 子句（对分组结果进行过滤）
SELECT   deptno, job, SUM (sal) 薪资小计
    FROM emp
   WHERE deptno IN (20, 30)
GROUP BY deptno, job
  HAVING SUM (sal) > 2000;
  
--6.1.8 使用dual表
SELECT TO_CHAR (SYSDATE, 'yyyy-mm-dd hh24:mi:ss') FROM DUAL;


--6.1.9 ROWNUM伪列
SELECT ROWNUM, x.*
  FROM emp x
 WHERE x.deptno = 20;
 
 
SELECT ROWNUM, empno, ename, job, mgr, hiredate
  FROM emp
 WHERE deptno = 20;
 
SELECT ROWNUM, empno, ename, job, mgr, hiredate
  FROM emp
 WHERE ROWNUM <= 10;
 
 
SELECT ROWIDTOCHAR (ROWID), x.ename, x.empno, x.job, x.hiredate
  FROM emp x
 WHERE ROWNUM <= 5;
 
--指定rownum伪列的范围 
select recno,empno,ename,job,mgr,hiredate 
from (select rownum recno,empno,ename,job,mgr,hiredate from emp)
where recno>=5 and recno<=10; 
 
--rowidW伪列

 SELECT ROWID,x.* FROM emp x;

SELECT ROWIDTOCHAR (ROWID), x.ename, x.empno, x.job, x.hiredate
  FROM emp_rowid x;

--利用rowid删除重复行
CREATE TABLE emp_rowid AS SELECT * FROM emp;
select * from emp_rowid;
select count(*) from emp_rowid;
INSERT INTO emp_rowid SELECT * FROM emp;

SELECT *
  FROM emp_rowid
 WHERE ROWID NOT IN (SELECT   MIN (ROWID)
                         FROM emp_rowid
                     GROUP BY empno);
select empno  from emp_rowid group by empno having count(*) >1;

DELETE FROM emp_rowid
      WHERE ROWID NOT IN (SELECT   MIN (ROWID)
                              FROM emp_rowid
                          GROUP BY empno);


--ROWID的结构
SELECT rowidtochar(rowid) ROWID_NAME,
SUBSTR (ROWIDTOCHAR (ROWID), 0, 6) 数据对象编号,
       SUBSTR (ROWIDTOCHAR (ROWID), 7, 3) 文件编号,
       SUBSTR (ROWIDTOCHAR (ROWID), 10, 6) 块编号,
       SUBSTR (ROWIDTOCHAR (ROWID), 16, 3) 行编号
  FROM emp
 WHERE ROWNUM <= 5;