目标表:
SQL> desc emp;
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO NUMBER(4)
ENAME VARCHAR2(10) Y
JOB VARCHAR2(9) Y
MGR NUMBER(4) Y
HIREDATE DATE Y
SAL NUMBER(7,2) Y
COMM NUMBER(7,2) Y
DEPTNO NUMBER(2) Y
SQL>
select * from emp;
只需要加过滤条件即可
select * from emp a where a.ename='ALLEN';
null值 “=”判断,需用is null判断
select * from emp where comm is null;
null值也不支持 加减乘除 大小比较 相等比较,否则只能为空。 对于其他函数,在使用时最好测试一下null值时会返回什么结果。
select coalesce(comm,0) from emp;
有人会问,为什么不是nvl 而是 coalesce呢? 因为coalesce更好用。
看下面这个例子,返回多个值中第一个不为空的值。
CREATE OR REPLACE VIEW xgj AS
SELECT NULL AS C1, NULL AS C2, 1 AS C3, NULL AS C4, 2 AS C5, NULL AS C6 FROM DUAL
UNION ALL
SELECT NULL AS C1, NULL AS C2, NULL AS C3, 3 AS C4, NULL AS C5, 2 AS C6 FROM DUAL;
SQL> select * from xgj;
C1 C2 C3 C4 C5 C6
-- -- ---------- ---------- ---------- ----------
1 2
3 2
SQL>
SQL> select coalesce(c1,c2,c3,c4,c5,c6) from xgj;
COALESCE(C1,C2,C3,C4,C5,C6)
---------------------------
1
3
SQL>
可以看到对于nvl来说,coalesce支持多个参数,能很方便的返回第一个不为空的值,如果上面的语句改用nvl.则需要套用很多层
SQL> select nvl(nvl(nvl(nvl(nvl(c1, c2), c3), c4), c5), c6) from xgj;
NVL(NVL(NVL(NVL(NVL(C1,C2),C3)
----------------------------------------
1
3
SQL>
对于简单的查询,操作起来比较简单,那么复杂一点儿的呢? 比如 ,查询部门号10中的所有员工、所有得到提成的员工、以及部门20中工资不超过2000的员工。
这是三个条件的组合,符合上述任一一条即可。
注意:对于多个条件的组合,要使用括号,这样在更改维护语句时可以不必再考虑优先级问题,而且可以很容易的借助龚总工具找到组合条件的起始位置
select *
from emp e
where (e.deptno = 10
or e.comm is not null
or (e.sal > 2000 and e.deptno = 20));
select empno ,ename from emp a ;
不是每个人都能看懂那些简写字母是什么意思,所有在必要的时候应该给列取个别名。
在as 后面跟别名,也可以不要as 直接在列名后跟别名。
SQL> select empno as 工号 ,ename 姓名 from emp a ;
工号 姓名
----- ----------
7369 SMITH
...........
写报表时,经常会加上各种条件, 引用别名时,千万别忘了嵌套一层,因为这个别名是在select之后才有效的.
比如:
SQL> select *
2 from (select ename 姓名, sal as 工资 from emp a)
3 where 工资 < 1000;
姓名 工资
---------- ---------
SMITH 800.00
JAMES 950.00
SQL>
如果不嵌套,提示 标识符无效
SQL> select ename 姓名, sal as 工资 from emp a where 工资 < 1000;
select ename 姓名, sal as 工资 from emp a where 工资 < 1000
ORA-00904: "工资": invalid identifier
SQL>
我们可以使用字符串连接符“||”将各个列拼在一起。
SQL> select ename || '的工资是' || sal as msg from emp ;
MSG
--------------------------------------------------------------
SMITH的工资是800
ALLEN的工资是1600
......
当然了对于拼接列,我们还可以如下使用
select 'truncate table ' || owner || '.' || table_name || '; ' from all_tables ;
SQL> select ename,
job,
case
when job = 'ANALYST' then
'分析员'
when job = 'CLERK' then
'服务员'
when job = 'MANAGER' then
'经理'
when job = 'PRESIDENT' then
'主席'
else
'其他'
end as 职位
from emp;
ENAME JOB 职位
---------- --------- ---------
SMITH CLERK 服务员
ALLEN SALESMAN 其他
WARD SALESMAN 其他
JONES MANAGER 经理
MARTIN SALESMAN 其他
BLAKE MANAGER 经理
CLARK MANAGER 经理
SCOTT ANALYST 分析员
KING PRESIDENT 主席
TURNER SALESMAN 其他
ADAMS CLERK 服务员
JAMES CLERK 服务员
FORD ANALYST 分析员
MILLER CLERK 服务员
14 rows selected
SQL>
在查询时,并不要求返回所有的数据,比如进行抽查的时候会要求只返回两条数据。
我们可以使用伪列rownum来过滤,rownum依次对返回的每一条数据做一个标识 。
SQL> select * from emp where rownum <=2 ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30
SQL>
如果只想取第二条呢?
SQL> select * from emp where rownum =2 ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
SQL>
显然是不行的。 因为rownum是依次对数据做标识的,所以要先把所有的数据取出来,才能确认第二条记录。
我们可以这样
SQL> select * from (select rownum as rn,a.* from emp a where rownum <=2) where rn=2;
RN EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ----- ---------- --------- ----- ----------- --------- --------- ------
2 7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30
SQL>
我们可以先用dbms_random来对数据先进行数据排序,然后取其中三列。
select *
from (select ename, job, sal, comm from emp order by dbms_random.value())
where rownum <= 3;
数据:
CREATE OR REPLACE VIEW xgj AS
SELECT 'ABCEDF' AS vname FROM dual
UNION ALL
SELECT '_BCEFG' AS vname FROM dual
UNION ALL
SELECT '_BCEDF' AS vname FROM dual
UNION ALL
SELECT '_\BCEDF' AS vname FROM dual
UNION ALL
SELECT 'XYCEG' AS vname FROM dual;
SQL> select * from xgj;
VNAME
-------
ABCEDF
_BCEFG
_BCEDF
_\BCEDF
XYCEG
SQL>
SQL> select * from xgj where vname like '%CED%';
VNAME
-------
ABCEDF
_BCEDF
_\BCEDF
SQL>
SQL> select * from xgj where vname like '_BCE%';
VNAME
-------
ABCEDF
_BCEFG
_BCEDF
SQL>
发现突然多了一个 ABCDEF , 因为在like子句中有个两个通配符
在这里“_”被当成通配符了,那怎么办呢? 我们可以使用转义字符。
SQL> select * from xgj where vname like '\_BCE%' escape '\';
VNAME
-------
_BCEFG
_BCEDF
SQL>
ESCAPE 把‘\’标识作为转义字符, 而 ‘\’把‘_’转义为字符,而非其愿义(通配符)。
我们注意到我们的数据中 有 一列的值为 _\BCEDF ,那么加了ESCAPE ‘\’ 后怎么返回呢?
SQL> select * from xgj where vname like '_\BCE%' escape '\';
select * from xgj where vname like '_\BCE%' escape '\'
ORA-01424: missing or illegal character following the escape character
双写转义字符即可
SQL> select * from xgj where vname like '_\\BCE%' escape '\';
VNAME
-------
_\BCEDF
SQL>
对于字符串中包含’%’ 的也一样的方式处理