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;

=================结论==================

||是拼接字段

=======================================