源码-Oracle数据库管理-第九章-SQL查询-Part 2（基本查询）

查询是一个基本需求，但具体需求是千变万化的，所以查询同时又是复杂的。

曾经历过一个项目，几十号人，辛苦加班搞了两三个月，只是为了实现一个主要功能：保单查询。

不过，因为是新人新客户，还有环境搭建（开发、测试）、接口联调、需求变更等问题会拖慢开发进度。

以下查询看似繁多，其实比较有条理，对于初学者，还是很有参考价值的。

--9.1.5使用Where限制返回的行
--查询员工工资大于2000的人员列表
SELECT empno, ename, sal, hiredate FROM emp WHERE sal > 2000;
  
--查询当前的日期格式
SELECT value FROM v$nls_parameters WHERE parameter='NLS_DATE_FORMAT';

--查询雇佣日期大于1981-12-01的员工信息。
SELECT empno,ename,hiredate FROM emp WHERE hiredate>'1981-12-01';

--9.1.6 Between, in和like范围查询
--BETWEEN..AND的示例
SELECT empno, ename, sal FROM emp WHERE sal BETWEEN 2000 AND 3000;
  
--NOT BETWEEN..AND示例
SELECT empno, ename, hiredate
  FROM emp
 WHERE hiredate NOT BETWEEN '1981-01-01' AND '1981-06-30';
  
--使用IN查询示例
 SELECT empno,ename,deptno FROM emp WHERE deptno IN (30,40,50,60);

--IN语句被转换成了如下的语句：
SELECT empno,ename,deptno FROM emp WHERE deptno=30 OR deptno=40 OR deptno=50 OR deptno=60;  
  
--NOT IN 语句使用示例
SELECT empno, ename, job, sal
  FROM emp
 WHERE job NOT IN ('职员', '分析人员', '销售人员');

--与NOT IN 相匹配的SQL语句
SELECT empno, ename, job, sal
  FROM emp
 WHERE (job <> '职员')
   AND (job <> '分析人员')
   AND (job <> '销售人员');
    
--NULL在NOT IN中的示例
SELECT empno, ename, job, sal
  FROM emp
 WHERE job NOT IN ('职员', '分析人员', '销售人员', NULL);
 
--设置空值
update emp set job=null where empno=8092; 
select * from emp;

--NOT IN转换为NULL后的结查
--当列与Null进行比较运算时，就会产生未知的结果Unknown或False，使得整个查询条件都不成立，因此无法返回任何数据  
SELECT empno, ename, job, sal
  FROM emp
 WHERE (job <> '职员')
   AND (job <> '分析人员')
   AND (job <> '销售人员')
   AND (job <> NULL);
  
--LIKE查询语句使用示例
SELECT empno,ename,job,sal FROM emp WHERE ename LIKE '张%';    

--更新ename
update emp set ename='张三疯6' where empno=7999;  

--通配符_和%的使用示例
SELECT table_name AS 表名, status AS 状态
  FROM user_tables
 WHERE table_name LIKE '_M%';
 
 
--使用转义字符 
SELECT table_name AS 表名, status AS 状态
  FROM user_tables
 WHERE table_name LIKE '%\_%' ESCAPE '\';
 
 
--使用IS NULL判断NULL值
SELECT empno,ename,sal FROM emp WHERE mgr IS NULL;

--如果使用等于符号，则无法取回任何记录
SELECT empno,ename,sal FROM emp WHERE mgr= NULL;

--9.1.8 And, or, not逻辑运算符
--使用AND操作符（全运算符）
SELECT empno,ename,sal,deptno FROM emp WHERE deptno=20 AND sal>3000;

--使用OR操作符（半运算符）
SELECT empno,ename,sal,deptno FROM emp WHERE deptno=20 OR deptno=10;

--使用NOT操作符（取反运算符）
SELECT empno, ename, sal, deptno
  FROM emp
 WHERE NOT (deptno = 20 OR deptno = 30);
 
--运算符优先级
--算数运算符->连接运算符->比较运算符->null/like/in->between->not->and->or
SELECT empno, ename, sal, job
  FROM emp
 WHERE job = '经理'
    OR job = '分析人员'
   AND sal > 3000;
 
 
--使用order by排序 
--从低到高进行排序（升序排序）
SELECT empno,ename,sal FROM emp WHERE deptno=20 ORDER BY sal;

--从高到低进行排序（降序排序）
SELECT empno,ename,sal FROM emp WHERE deptno=20 ORDER BY sal DESC;
 
 
--按列别名进行排序
SELECT empno,ename,sal AS 工资 FROM emp WHERE deptno=20 ORDER BY 工资 DESC;

--按选择列表的索引顺序进行排序
SELECT empno,ename,sal FROM emp WHERE deptno=20 ORDER BY 3 DESC;

--多列排序示例（这个挺实用，而且Excel也是支持多列排序的）
SELECT empno, ename, sal, hiredate
  FROM emp
 WHERE deptno = 20
 ORDER BY sal ASC, hiredate DESC, ename asc;

--9.1.10 Rownum伪列
--伪列是指物理上并不存在的列，rownum伪列返回查询结果集的行号
--使用ROWNUM伪列
SELECT ROWNUM,empno,ename FROM emp WHERE deptno=20;

--在伪列中应用了ORDER BY
SELECT ROWNUM, empno, ename, sal FROM emp WHERE deptno = 20 ORDER BY sal;
 

--使用子查询实现ROWNUM列的按特定的字段进行排序（子查询就是强！）
SELECT ROWNUM, empno, ename, sal
  FROM (SELECT empno, ename, sal FROM emp WHERE deptno = 20 ORDER BY sal);


--查询员工表中的前5条记录
SELECT ROWNUM seq,empno,ename,sal FROM emp WHERE ROWNUM<=5;

--区间查询的错误的用法
SELECT ROWNUM seq, empno, ename, sal
  FROM emp
 WHERE ROWNUM >= 2
   AND ROWNUM <= 5;
   
--正确的ROWNUM区间查询的用法
SELECT seq, empno, ename, sal
  FROM (SELECT ROWNUM seq, empno, ename, sal FROM emp)
 WHERE seq >= 2
   AND seq <= 5;


--使用ROWID伪列
--Rowid伪列用来返回行的地址
SELECT ROWID, empno, ename FROM emp WHERE deptno = 20;

--查询物理地址信息
SELECT ROWID,
       SUBSTR(ROWID, 1, 6) "对象编号",
       SUBSTR(ROWID, 7, 3) "数据文件编号",
       SUBSTR(ROWID, 10, 6) "数据块编号",
       SUBSTR(ROWID, 16, 3) "行号"
  from emp
 WHERE deptno = 20;

--使用DBMS_ROWID获取ROWID的详细信息
SELECT DBMS_ROWID.rowid_object (ROWID) object_id,
       DBMS_ROWID.rowid_relative_fno (ROWID) file_id,
       DBMS_ROWID.rowid_block_number (ROWID) block_id,
       DBMS_ROWID.rowid_row_number (ROWID) num
  FROM emp
 WHERE ROWNUM < 5;

--使用rowid信息关联dba_data_file(可使用dba权限运行)
--可通过记录查询记录所在文件的信息
SELECT *
FROM   dba_data_files
WHERE  file_id IN (SELECT DISTINCT DBMS_ROWID.rowid_relative_fno(ROWID) file_id
                   FROM   scott.emp
                   WHERE  rownum < 5);

--**************************************************************************
--使用ROWID的一个示例
--1.创建一个与emp表具有相同结构和相同数据的表emp_rowid，这种创建方式没有主键列
CREATE TABLE emp_rowid2 AS SELECT * FROM emp;
--2.向这个表中再次插入重复的记录
INSERT INTO emp_rowid
  SELECT * FROM emp WHERE deptno = 20;
--3.通过ROWID来查询重复的记录
SELECT empno,ename,sal,deptno
  FROM emp_rowid
 WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM emp_rowid GROUP BY empno);
--**************************************************************************