where关键字

--where关键字

select ename,job,sal from emp;

--例：查询工资大于2000的员工信息

select * from emp where sal >2000

 

 

-- ＝，！＝,<>，<,>,<=,>=,any,some,all

--查询员工信息，条件：薪水要大于1000，薪水还要大于1500，薪水还要大于2000

select * from emp where sal > any(1000,1500,2000);

 

 

-- is null,is not null

select * from emp where comm is not null;

--错误:select * from emp where comm = null;

 

 

--查询员工薪水在2000-3000的员工信息

-- between x and y

select * from emp where sal between 2000 and 3000

--and,or,not

select * from emp where sal >=2000 and sal <=3000

 

 

-- in（list），not in（list）

--查询职务为 MANAGER 和 ANALYST 的员工信息

select * from emp where job = 'MANAGER' or job = 'ANALYST'

select * from emp where job in('MANAGER','ANALYST')

--查询工资为3000到50000的员工信息

select * from emp where sal in (3000,5000);

select * from emp where sal not in (3000,5000);

 

-- exists（sub－query）、not exists（sub-query）

select * from emp where exists(select * from dept where deptno =10)

select * from emp where not exists(select * from dept where deptno !=10)

 

 

-- like _ ,%,escape ‘\‘ _\% escape ‘\’

--模糊查询

--查询：员工姓名中含有“M”的员工信息

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

select * from emp where ename like '_M%'

select * from emp where ename like '__O%'

select * from emp where ename like '%E_'

select * from emp where ename like '%\%%' escape '\'

 

--插入一条信息：

insert into emp(empno,ename) values(9527,'hua%an');