oracle—decode、nvl、rownum、like、all、any使用
一、decode()——多值判断
语法：decode(表达式,比较值1,结果值1,比较值1,结果值1,...,默认值)
SQL> select employee_name,decode(sign(instr(employee_position,'工程师')),1,'技术部','行政部') partment from employees; EMPLOYEE_NAME PARTMENT ------------- -------- 江小白 行政部 行政部 大鳄 技术部 昂呜 技术部 李四 技术部 张三 行政部 过户 技术部 问题 技术部 语句 技术部 陈武 技术部 六六 技术部 11 rows selected
二、nvl()——空值判断
语法：nvl(表达式,新值/表达式)
SQL> select employee_name,employee_position,nvl(employee_position,'行政部') new from employees; EMPLOYEE_NAME EMPLOYEE_POSITION NEW ------------- ----------------- ---------- 江小白 行政部 大鳄 工程师 工程师 昂呜 高级工程师 高级工程师 李四 高级工程师 高级工程师 张三 开发经理 开发经理 过户 工程师 工程师 问题 工程师 工程师 语句 测试工程师 测试工程师 陈武 测试工程师 测试工程师 六六 测试工程师 测试工程师 10 rows selected
三、rownum()——结果集行号
1.增加rownum一列
SQL> select employee_id,employee_name,rownum new from employees; EMPLOYEE_ID EMPLOYEE_NAME NEW ----------- ------------- ---------- 10 江小白 1 4 大鳄 2 3 昂呜 3 2 李四 4 1 张三 5 5 过户 6 6 问题 7 7 语句 8 8 陈武 9 9 六六 10 10 rows selected
2.rownum与order by
需求：按照id排序并取出前3位。
SQL> select employee_id,employee_name,rownum new from employees where rownum <=3 order by employee_id; EMPLOYEE_ID EMPLOYEE_NAME NEW ----------- ------------- ---------- 10 江小白 1 3 昂呜 3 4 大鳄 2 SQL> select employee_id,employee_name,rownum new from(select * from employees order by employee_id) where rownum<=3 ; EMPLOYEE_ID EMPLOYEE_NAME NEW ----------- ------------- ---------- 1 张三 1 10 江小白 2 2 李四 3 SQL> select employee_id,employee_name,rownum new from(select * from employees order by cast(employee_id as number)) where rownum<=3 ; EMPLOYEE_ID EMPLOYEE_NAME NEW ----------- ------------- ---------- 1 张三 1 2 李四 2 3 昂呜 3
备注：where的过滤条件优先于order by 条件；id的类型为varchar，所以排序是10排在2前面，利用cast将字符转换为数值型后，排序正常。
3.rownum与“=”
能获取到rownum=1的数据，获取不到rownum=4的数据。
SQL> select * from employees where rownum=1; EMPLOYEE_ID EMPLOYEE_NAME EMPLOYEE_POSITION EMPLOYEE_AGE EMPLOYEE_ADD EMPLOYEE_BIRTH ----------- ------------- ----------------- ------------ -------------------- -------------- 10 江小白 SQL> select * from employees where rownum=4; EMPLOYEE_ID EMPLOYEE_NAME EMPLOYEE_POSITION EMPLOYEE_AGE EMPLOYEE_ADD EMPLOYEE_BIRTH ----------- ------------- ----------------- ------------ -------------------- --------------
4.rownum与“>”
rownum>1时没有数据，rownum>0时查询全部数据。
SQL> select * from employees where rownum>1; EMPLOYEE_ID EMPLOYEE_NAME EMPLOYEE_POSITION EMPLOYEE_AGE EMPLOYEE_ADD EMPLOYEE_BIRTH ----------- ------------- ----------------- ------------ -------------------- -------------- SQL> select * from employees where rownum>0; EMPLOYEE_ID EMPLOYEE_NAME EMPLOYEE_POSITION EMPLOYEE_AGE EMPLOYEE_ADD EMPLOYEE_BIRTH ----------- ------------- ----------------- ------------ -------------------- -------------- 10 江小白 4 大鳄 工程师 26 巴南 1988-07-01 3 昂呜 高级工程师 27 渝北 1998-06-01 2 李四 高级工程师 32 渝北 1994-09-01 1 张三 开发经理 37 巴南 1987-11-01 5 过户 工程师 26 渝中 1985-08-01 6 问题 工程师 25 渝中 1980-02-09 7 语句 测试工程师 24 九龙坡 2010-05-03 8 陈武 测试工程师 25 江北 2004-01-23 9 六六 测试工程师 32 南岸 1994-12-21 10 rows selected
四、like——模糊匹配
1."%"——匹配
需求：查询以“工程师”结尾的数据
SQL> select * from employees where employee_position like '%工程师'; EMPLOYEE_ID EMPLOYEE_NAME EMPLOYEE_POSITION EMPLOYEE_AGE EMPLOYEE_ADD EMPLOYEE_BIRTH ----------- ------------- ----------------- ------------ -------------------- -------------- 4 大鳄 工程师 26 巴南 1988-07-01 3 昂呜 高级工程师 27 渝北 1998-06-01 2 李四 高级工程师 32 渝北 1994-09-01 5 过户 工程师 26 渝中 1985-08-01 6 问题 工程师 25 渝中 1980-02-09 7 语句 测试工程师 24 九龙坡 2010-05-03 8 陈武 测试工程师 25 江北 2004-01-23 9 六六 测试工程师 32 南岸 1994-12-21 8 rows selected
2."_"——匹配一个字符
需求：查询以“XX工程师”的数据
SQL> select * from employees where employee_position like '__工程师'; EMPLOYEE_ID EMPLOYEE_NAME EMPLOYEE_POSITION EMPLOYEE_AGE EMPLOYEE_ADD EMPLOYEE_BIRTH ----------- ------------- ----------------- ------------ -------------------- -------------- 3 昂呜 高级工程师 27 渝北 1998-06-01 2 李四 高级工程师 32 渝北 1994-09-01 7 语句 测试工程师 24 九龙坡 2010-05-03 8 陈武 测试工程师 25 江北 2004-01-23 9 六六 测试工程师 32 南岸 1994-12-21
3.元义字符
需求：判断以%号结尾
SQL> select 1 result from dual where '10%' like '%%'; RESULT ---------- 1 SQL> select 1 result from dual where '10' like '%%'; RESULT ---------- 1 SQL> select 1 result from dual where '10' like '%\%' escape '\'; RESULT ---------- SQL> select 1 result from dual where '10%' like '%\%' escape '\'; RESULT ---------- 1
五、all、some、any——集合判断
需求：查询大于所有工程师年龄的员工信息。
SQL> select * from employees where employee_age>all(select employee_age from employees where employee_position='工程师'); EMPLOYEE_ID EMPLOYEE_NAME EMPLOYEE_POSITION EMPLOYEE_AGE EMPLOYEE_ADD EMPLOYEE_BIRTH ----------- ------------- ----------------- ------------ -------------------- -------------- 3 昂呜 高级工程师 27 渝北 1998-06-01 2 李四 高级工程师 32 渝北 1994-09-01 9 六六 测试工程师 32 南岸 1994-12-21 1 张三 开发经理 37 巴南 1987-11-01 SQL> select * from employees where employee_age> (select max(employee_age) from employees where employee_position='工程师'); EMPLOYEE_ID EMPLOYEE_NAME EMPLOYEE_POSITION EMPLOYEE_AGE EMPLOYEE_ADD EMPLOYEE_BIRTH ----------- ------------- ----------------- ------------ -------------------- -------------- 3 昂呜 高级工程师 27 渝北 1998-06-01 2 李四 高级工程师 32 渝北 1994-09-01 1 张三 开发经理 37 巴南 1987-11-01 9 六六 测试工程师 32 南岸 1994-12-21
需求：查询大于任意一位工程师年龄的员工信息。
SQL> select * from employees where employee_age>any(select employee_age from employees where employee_position='工程师'); EMPLOYEE_ID EMPLOYEE_NAME EMPLOYEE_POSITION EMPLOYEE_AGE EMPLOYEE_ADD EMPLOYEE_BIRTH ----------- ------------- ----------------- ------------ -------------------- -------------- 1 张三 开发经理 37 巴南 1987-11-01 2 李四 高级工程师 32 渝北 1994-09-01 9 六六 测试工程师 32 南岸 1994-12-21 3 昂呜 高级工程师 27 渝北 1998-06-01 5 过户 工程师 26 渝中 1985-08-01 4 大鳄 工程师 26 巴南 1988-07-01 6 rows selected SQL> select * from employees where employee_age> (select min(employee_age) from employees where employee_position='工程师'); EMPLOYEE_ID EMPLOYEE_NAME EMPLOYEE_POSITION EMPLOYEE_AGE EMPLOYEE_ADD EMPLOYEE_BIRTH ----------- ------------- ----------------- ------------ -------------------- -------------- 4 大鳄 工程师 26 巴南 1988-07-01 3 昂呜 高级工程师 27 渝北 1998-06-01 2 李四 高级工程师 32 渝北 1994-09-01 1 张三 开发经理 37 巴南 1987-11-01 5 过户 工程师 26 渝中 1985-08-01 9 六六 测试工程师 32 南岸 1994-12-21 6 rows selected
整理于《oracle入门很简单》一书
