sql的基础语句-select语句中出现的操作符号

ewrQ

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