查询是一个基本需求,但具体需求是千变万化的,所以查询同时又是复杂的。
曾经历过一个项目,几十号人,辛苦加班搞了两三个月,只是为了实现一个主要功能:保单查询。
不过,因为是新人新客户,还有环境搭建(开发、测试)、接口联调、需求变更等问题会拖慢开发进度。
以下查询看似繁多,其实比较有条理,对于初学者,还是很有参考价值的。
--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);
--**************************************************************************