Oracle查询语句——条件查询

本文记录Oracle学习过程中遇到的Oracle查询语句——条件查询~

语句格式:

select *|{[distinct] column|expression [alias],...} from table [where condition(s)];

比较运算符

  • =:等于,注意不是==;
  • >:大于;
  • >=:大于等于;
  • <:小于;
  • <=:小于等于;
  • <>或!=:不等于;
  • between ... and ...:在两个值之间,包含边界;
  • in (set):等于值列表中的一个;
  • like:模糊查询;
  • is null:是否为空值。

逻辑运算符

  • and:逻辑并;
  • or:逻辑或;
  • not:逻辑否。

模糊查询的两种通配符:

  • %:可以匹配任意长度的内容;
  • _:可以匹配一个长度的内容。

示例代码(空与非空):

select * from emp where comm is null;
select * from emp where comm is not null;

实例代码(逻辑并):

select * from emp where comm is not null and sal > 1500;

示例代码(逻辑或):

select * from emp where comm is null or sal > 1500;

示例代码(逻辑非):

select * from emp where comm is null and not (sal > 1500);

示例代码(范围限制):

select * from emp where sal between 1500 and 3000;
select * from emp where hiredate between '1-1月-1981' and '31-12月-1981';

示例代码(等于):

select * from emp where ename = 'smith';

示例代码(范围查询):

select * from emp where empno in (7369, 7499, 7521);
select * from emp where ename in ('SMITH', 'ALLEN', 'WARD');

示例代码(模糊查询):

select * from emp where ename like '_M%';
select * from emp where ename like '%';
select * from emp where ename like '%M%';

示例代码(不等于):

select * from emp where empno <> 7369;
select * from emp where empno != 7369;

标签: none