sql的基础语句-select语句中出现的操作符号
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 commnull;
select * from emp where comm = null;
2.4 比较运算
=>= < in like is null is not null between and not betweenand
select * from emp where sal between 800 and 1500; --包含800和1500
=
select * from emp where sal >= 800 and 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 sal1000
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 wherenot (job='SALEMAN' or job='CLERK') and sal > 1000
select * from emp where not job='SALEMAN' or job='CLERK' and sal > 1000
页:
[1]