SQL语句 运算符

6.2 运算符

 

6.2.1 算术运算符

加 / 减 / 乘 / 除

6.2.2 连接运算符

是用来连接字符串的。跟java中的 + 是一致的。

select 'abc' || ' bcd ' as 连接后的结果 from dual; select d.dname || ' 部门' from dept d; 

6.2.3 比较运算符

> / < / >= / <= / != / <> /IS NULL / Like / Between / In / Exsist

-- 判断 null 值
select * from n5 where s is null; select * from n5 where s is not null; -- like 模糊查询。慎用，有可能会导致全表扫描，效率低。 -- % 匹配0到多个字符，_ 匹配一个字符 select username from dba_users where username like 'VI_'; select username from dba_users where username like 'SC%'; select username from dba_users where username like '%SC%'; -- in，是 where x = a or x = b or x = c 的一种缩写。下面两条是等价的。 select * from emp where empno in (700, 800, 900); select * from emp where empno = 700 or empno = 800 or empno = 900; -- in 后面跟的不一定是逗号分隔的单项，也可能是一个完整的查询语句。 -- 下面两条结果是一致的 -- 这种 in 慎用 select * from emp where deptno in (select deptno from dept where dname = 'SALES'); select a.* from emp a,dept d where a.deptno =d.deptno and d.dname='SALES'; -- between...and select * from emp where empno between 7800 and 9000; -- 等同于： select * from emp where empno >= 7800 and empno <= 9000; 

6.2.4 逻辑运算符

and / or / not

not 的优先级 > and 的优先级 > or 的优先级

6.2.5 集合操作符

Union / UnionAll / Intersect / Minus

-- 生成测试数据
create table dept_01 as select * from dept where rownum < 6; create table dept_02 as select * from dept where rownum < 4; insert into dept_02 values (98, '小吃部', '斗门'); insert into dept_02 values (99, '外卖部', '香洲'); commit; select * from dept_01 union select * from dept_02; select * from dept_01 union all select * from dept_02; select * from dept_01 intersect select * from dept_02; select * from dept_01 minus select * from dpet_02;

转载于:https://www.cnblogs.com/qq895139140/p/7483585.html