2. select语句中出现的操作符号

2.1 合并操作符
select a.ename||' '||to_char(sal) from emp a;
2.2 消除重复的行
select distinct deptno from emp;
2.3 空格、空串、null的区别
select ascii(' '),ascii(null),ascii('') from dual;
区别：
  从显式上看，空串跟null在数据库中存储的值是一样的，但是NULL可以赋给任何数据类型，而空串只能赋给字符串类型
过滤null值，需要用下面这种写法
select * from emp where comm is not null;
典型的错误（但是不会报错）
select * from emp where comm != null;
select * from emp where comm <> null;
select * from emp where comm = null;
2.4 比较运算
=  >=  <=  <> != >  <    in   like       is null    is not null        between and    not between  and
select * from emp where sal between 800 and 1500;   --包含800和1500
=
select * from emp where sal >= 800 and sal<= 1500;
select * from emp where sal not between 800 and 1500;
=
select * from emp where sal < 800 or sal > 1500;
select * from emp where empno in(7499,7698,7788);
=
select * from emp where empno=7499 or empno=7698 or empno=7788;
select * from emp where ename like 'T%';
select * from emp where ename not like 'T%';
select * from emp where ename like '%T%';
select * from emp where ename not like '%T%';
select * from emp where sal != 1000
=
select * from emp where sal <> 1000
2.5 逻辑运算符
布尔运算(boolean)     取值：TRUE 、FALSE
非    或     且
且
    true and false   =   false
    true and true    =   true
    flase and false  =   false
或
    true or false   =   true
    true or true    =   true
    flase or false  =   false
非
    not true    =   false
    not false   =   true
select * from emp where sal != 1000 and job='CLERK'
select * from emp where sal != 1000 or job='CLERK'
注意：
 
  优先级的顺序问题：
    NOT > AND >  OR 如果想要覆盖掉默认优先级顺序，就必须得用小括号
 
select * from emp where  not (job='SALEMAN' or job='CLERK') and sal > 1000
select * from emp where not job='SALEMAN' or job='CLERK' and sal > 1000