select col_name1.col_name2… from table_name where
where: 条件查询关键字，后边跟条件（完整的条件）
求工资大于1250的员工姓名,工资
select ename，sal from emp where sal>1250
1:数学运算符：+，-，，/
假设一个月为30天，求员工日薪
select sal,sal/30 日薪,sal12 from emp
select ename,ename+5 from emp-----只能数字做运算
2:数学比较符：>,<,>=,<=,=,<>/!=
假设一个月为30天,求日薪大于50的员工信息
select ,sal/30 from emp where sal/30>50 -----后不能加任何列
select e.,sal/30 from emp e where sal/30>50----当表后面加,可加别的列
select sal,sal/30 日薪 from emp where sal/30>50-----select 列加的别名无法在where后使用(考虑执行顺序)
select sal, 日薪 from emp where sal/30 日薪>50-----列别名只能在select 后面添加
求大于十号部门的员工工资,部门,工作
select sal,deptno,job from emp where deptno>10
求工作是职员的员工姓名,工作,工资
select ename,job,sal from emp where job=‘CLERK’

select ename,job,sal from emp where job>‘CLERK’
select ascii(‘C’) from dual
select ename from emp where ‘MZNAGER’>‘MLERK’----字符串比较大小写,用SACII码
3:连接符: and,or,not.
求10号部门的经理和20号部门的职员
select * from emp where deptno=10 and job=‘MANAGER’ or deptno=20 and job=‘CLERK’
先执行and,后执行or,有括号先执行括号
求工资在1000到2000之间的员工信息
select * from emp where sal>=1000 and sal <=2000
select * from emp where sal<=2000 and sal >=1000
求不是 10号部门的职员 和 20号部门的销售
select * from emp where deptno=10 and job=‘CLERK’ or deptno=20 and job=‘SALESMAN’ ----做差(MINUS)
select * from emp
minus
(select * from emp where deptno=10 and job=‘CLERK’ or deptno=20 and job=‘SALESMAN’)
-----思路1:分别求出10号部门不是CLERK,20号部门不是SALESMAN,其余部门所有工作 ,将所有集合求并集
select *
from emp
where deptno = 10
and job <> ‘CLERK’----10号部门不是CLERK
or deptno = 20
and job <> ‘SALESMAN’------20号部门不是SALESMAN
or deptno <> 10
and deptno <> 20-----其余部门所有工作
-----思路2:分别求出职员不是10号部门的,销售不是二十号部门的,其余工作的部门,将所有集合求并集

4,between and
求工资在1000到1600之间的员工信息
select * from emp where sal between 1000 and 1600
select * from emp where sal between 1600 and 1000
select * from emp where sal between 1600 and 1600
between A and B —1 包含边界 <==> >=A and <=B
----2 A <= B
求20号部门工资在1500到2000之间的员工信息
select * from emp where sal between 1500 and 2000 and deptno=20

5,in
后面跟集合,表示值和集合里面的值做比较
求20号或者30号的部门信息
select * from dept where deptno=20 or deptno=30
select * from dept where deptno in (20,30)
求不是 20号或者30号的部门信息
select * from dept where deptno<>20 and deptno<>30
select * from dept where deptno not in (20,30)
求不是 10号部门的职员 和 20号部门的销售
select deptno,job from emp where deptno=10 and job=‘CLERK’ or deptno=20 and job=‘SALESMAN’
select *
from emp
where (deptno, job) not in (select deptno, job
from emp
where deptno = 10
and job = ‘CLERK’
or deptno = 20
and job = ‘SALESMAN’)

(A,B) not in (C,D)
求不是10号部门且工作不是CLERK的员工信息
select * from emp where empno not in ( select empno from emp where deptno=10 and job=‘CLERK’)
select * from emp where (deptno,job) not in ( select deptno,job from emp where deptno=10 and job=‘CLERK’)

select * from emp where (deptno) not in ( select deptno from emp where deptno=10 and job=‘CLERK’)
求跟SMITH同样工作的员工信息
select * from emp where job in (select job from emp where ename=‘SMITH’)
求跟SMITH同样工作且同一部门的员工信息
select * from emp where (deptno) in (select deptno from emp where ename=‘SMITH’)
---------注意:
select comm from emp
select * from emp where comm not in (select comm from emp)----出现了空值?

6 null:空值
求没有提成的员工信息
select * from emp where comm is null
求有提成的员工信息
select * from emp where comm is not null
select * from emp where comm=null-----=null,没有值
select 1 from dual where 1>null
select 1 from dual where 1<=null ----空值不参与比较,任何值与空值比较,结果仍为空
A in(B)/* B 是一个查询语句,返回值为 null,300,500 / 相当于 A=null or A=300 or A=500,所以空值不影响最终结果
A not in(B)/ B 是一个查询语句,返回值为 null,300,500 */ 相当于 A<>null and A<>300 and A<>500.任何值跟null做交集,结果都为空.所以空值影响最终结果
求和SMITH同一领导的员工信息
select * from emp where mgr in(select mgr from emp where ename=‘SMITH’)
求不和SMITH同一领导的员工信息
select * from emp where mgr not in(select mgr from emp where ename=‘KING’ and mgr is not null)----一般会给字句加非空条件
select * from emp where comm not in (select comm from emp where comm is not null)
求员工的总年薪
select ename,sal,comm,(sal+comm)*12 from emp
----任意值和null做运算,结果仍为空 ,会用nvl()转换空值
select comm,sal,nvl(comm,0),(sal+nvl(comm,0))*12 from emp

7 like:模糊查询
%:表示任意长度的字符
_:表示一位长度的字符

select * from emp where ename like ‘J%’—查询以J开头
select * from emp where ename like ‘%H’—查询以H结尾
select * from emp where ename like ‘%R%’—查询包含R
select * from emp where ename like ‘%A%R%’ or ename like ‘%R%A%’—查询姓名中包含A和R的员工信息
select * from emp where ename like ‘%A%’ and ename like ‘%R%’
select * from emp where ename like ‘M%’—查询第二位是M
select * from emp where ename like '%E’—求倒数第二位是E
–求第二位是U,倒数第三位是N的员工信息
方法一:
select *
from emp
where ename like ‘U%N_’
and ename like ‘_____%’ —情况一:确保长度>=5
or ename like ‘NU_’ —情况二:长度为3

方法二:
select * from emp where ename like ‘U%’ and ename like '%N_’—不用考虑长度问题
select * from emp where ename like ‘%%’—如何求第三位是%的信息?
select * from emp where ename like '"%"%’
insert into emp (empno,ename) values(1123,‘aa"%"ss’)
escape
select * from emp where ename like ‘/%/%’ escape ‘/’
select * from emp where ename like '/%/%’ escape ‘/’
求以%开头,结尾,中间包含两个% 以及三个_的姓名
select * from emp where ename like '/%%/%%/%%/’ escape ‘/’ and ename like '/%%/%/%/%/_’ escape ‘/’
做模糊查询时,尽量保证不以%开头,如果以%开头,用instr替换like

8 any,all
假设集合B,有三个值(1000,1500,2000)
求大于集合B中所有值的员工工资
select * from emp where sal>all(1000,1500,2000)—>all:大于最大
求小于集合B中所有值的员工工资
select * from emp where sal<all(1000,1500,2000)—<all:小于最小
求大于集合B中任意值的员工工资
select * from emp where sal>any(1000,1500,2000)—>any:大于最小
求小于集合B中任意值的员工工资
select * from emp where sal<any(1000,1500,2000)—<any:小于最大
求等于集合B中所有值的员工工资
select * from emp where sal=all(1000,1500,2000)—=all:等于所有

select * from emp where sal=all(1250,1250)
–求工资和佣金一致的员工信息
select * from emp where sal=all(select sal,comm from emp)

求等于集合B中任意值的员工工资
select * from emp where sal=any(1000,1250,2000)—=any:等于任意

<>all?<>any?

9:exists
A exists(B)
当B返回空值,则整个结果为空,当B返回非空,则输出A的值
select * from emp where exists(select 1 from dual )
select sal from emp where exists(select 1 from dual where 1=0)
select * from emp where not exists(select 1 from dual ) —not exists 与exists相反