|
where salary between 100 and 1000;
where salary not between 100 and 1000;
eg:
select id,salary
from s_emp
where id between 4 and 10;
[4,10]
eg:
select id,salary
from s_emp
where salary not between 1000 and 2000;
3,列名 (not) in(41,42,43)某一列的值只能是括号里的数据
eg：where 列名 in(41,10)
select id,salary
from s_emp
where id in (1,2,3,4,5);
工资1250|1000|1200
select id,salary
from s_emp
where salary in(1250,1000,1200);
4,and 且
需要连接多个条件
语法:where 条件一 and 条件二 当条件一和条件二都满足的数据才会被查询出来
eg:工资不在1000-2000之间的员工信息
from s_emp
where salary < 1000 or salary > 2000 and id = 10 or last_name = ‘Ngao‘
5,or 或
需要连接多个条件
eg: where 条件一 or 条件二 or 条件三 当条件一或者条件二满足一个就可以出来
select id,salary
from s_emp
where id < 10 or salary>1000;
6,(not) like：模糊匹配
_ ：占位符 占一个符号位
% : 占位符 占0-多个位子
语法: where last_name like ‘tom‘;
where last_name like ‘_om‘;
where last_name like ‘%o%‘;
escape : 定义转义符号。将特殊含义的字符串 转义为普通字符串
语法: where last_name like ‘/_briup‘ escape ‘/‘;
eg:last_name N开头员工信息
select id,salary
from s_emp
where last_name like ‘N%‘
eg:last_name 第二个字符 是 g
select id,last_name
from s_emp
where last_name like ‘_g%‘;
eg:包含g
select id,salary
from s_emp
where last_name like ‘%g%‘
插入一条sql语句
insert into s_emp(id,last_name) values(99,‘_briup‘);
commit;
eg:查询下划线开头的用户信息
select id,last_name
from s_emp
where last_name like ‘#_%‘ escape ‘#‘;
7,is (not) null : 判断那一列是null；
语法: where commission_pct is null;
select id,salary * commission_pct
from s_emp
where commission_pct is not null;
注意：oracle中字符串使用单引号，单引号中的字符区分大小写
eg:41号部门，salary 大于1500 salary降序
select dept_id,salary
from s_emp
where dept_id = 41 and salary < 1500
order by salary desc;
优先级： (1)算术运算符 (2)连接符 || (3)比较符 (4)列名 [not]in 列名 [not]like (5)列名 [not]between 1 and 2 (6)not (7)and (8)or (函数)... *使用括号改变优先级
（编辑：辽源站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
