本文主要是介绍oracle经典查询练手-练习4，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
通配符
SQL> select ename from emp where enamelike '%A%N%';
ENAME
----------
ALLEN
MARTIN
SQL> select ename from emp where enamelike '%A%' AND '%N%';
select ename from emp where ename like'%A%' AND '%N%'
*
第 1 行出现错误:
ORA-00920: 无效的关系运算符
SQL> select ename from emp where ename like '%A%' AND like '%N%';
select ename from emp where ename like'%A%' AND like '%N%'
*
第 1 行出现错误:
ORA-00936: 缺失表达式
SQL> select ename from emp where ename like '%A%' AND ename like '%N%';
ENAME
----------
ALLEN
MARTIN
=====================结论=======================
1、 通配符%是通配多个字符，通配符_是通配一个字符。
2、 使用通配符必须加【列名 like】
3、 ename like '%A%N%'和ename like '%A%' AND ename like '%N%'结果一样。
================================================
Desc为降序
SQL> select ename,sal,comm from empwhere comm>0 order by sal,comm desc;
ENAME SAL COMM
---------- ---------- ----------
MARTIN 1250 1400
WARD 1250 500
ALLEN 1600 300
=================结论===============
Desc为降序（默认为升序）
====================================
Where 不能使用别名
SQL> select ename,sal+comm as gold fromemp where sal+commnot between 1000and
1500 order by gold;
ENAME GOLD
---------- ----------
WARD 1750
ALLEN 1900
MARTIN 2650
SQL> select ename,sal+comm as gold fromemp where gold not between 1000 and 1500 order by gold;
select ename,sal+comm as gold from empwhere gold not between 1000 and 1500 order by gold
*
第 1 行出现错误:
ORA-00904: "GOLD": 标识符无效
=================结论=================
Where语句不能识别别名
======================================
Is null 和 = null
SQL> select ename,job,sal+comm as wagefrom emp where job='MANAGER' or job='SALESMAN'and sal+comm between 15000 and 20000;
ENAME JOB WAGE
---------- --------- ----------
JONES MANAGER
BLAKE MANAGER
CLARK MANAGER
SQL> select ename,job,(sal+comm)*12 aswage from emp where job='MANAGER' or job='SALESMAN' and (sal+comm)*12 between15000 and 20000;
ENAME JOB WAGE
---------- --------- ----------
JONES MANAGER
BLAKE MANAGER
CLARK MANAGER
TURNER SALESMAN 18000
SQL> select ename,job,(sal+comm)*12 aswage from emp where job in ('MANAGER','SALESMAN')and(sal+comm)*12 between 15000 and 20000;
ENAME JOB WAGE
---------- --------- ----------
TURNER SALESMAN 18000
=============结论=============
在in中可以一对多的匹配
==============================
SQL> select empno,comm from emp wherecomm is null;
EMPNO COMM
---------- ----------
7369
7566
7698
7782
7788
7839
7876
7900
7902
7934
已选择10行。
SQL> select empno,comm from emp where comm = null;
未选定行
===============结论=============
isnull 是判断值是不是null，用=null则是跟null进行比较运算，而null跟任何值作比较运算结果都是false，也就不会有任何查询纪录。
================================
拼接字段
SQL> select 'select * from ' table_namefrom user_tables;
TABLE_NAME
--------------
select * from
select * from
select * from
select * from
SQL> select 'select * from '|| table_name|| fromuser_tables;
select 'select * from '|| table_name|| fromuser_tables
*
第 1 行出现错误:
ORA-00936: 缺失表达式
SQL> select 'select * from '|| table_name from user_tables;
'SELECT*FROM'||TABLE_NAME
--------------------------------------------
select * from DEPT
select * from EMP
select * from BONUS
select * from SALGRADE
SQL> select 'select * from '|| table_name||';' fromuser_tables;
'SELECT*FROM'||TABLE_NAME||';'
---------------------------------------------
select * from DEPT;
select * from EMP;
select * from BONUS;
select * from SALGRADE;
SQL> select 'select * from '||table_name||';' as a from user_tables;
A
---------------------------------------------
select * from DEPT;
select * from EMP;
select * from BONUS;
select * from SALGRADE;
=================结论==================
||是拼接字段
=======================================
这篇关于oracle经典查询练手-练习4的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
