where子句

关系运算 >、<、=、！=、<>、>=、<=

--查询除办事员外的所有人员信息
select * FROM emp WHERE job != 'CLERK'
select * FROM emp WHERE job <> 'CLERK'
select * FROM emp WHERE job not in 'CLERK'

逻辑运算 not、and、or

--查询销售人员或办事人员
select * FROM emp WHERE job='SALESMAN' OR job='CLERK'
--查询销售人员或办事人员，并且工资大于800
select * FROM emp WHERE (job='SALESMAN' OR job='CLERK') AND sal>900

IS NULL(是否为空)

--查询奖金为空的所有雇员
select * FROM emp WHERE comm is NULL

BETWEEN(在某两个值之间)

NOT BETWEEN ... AND

--查询工资在1500~3000之间的全部雇员信息
select * FROM emp WHERE sal BETWEEN 1500 AND 3000
select * FROM emp WHERE sal>=1500 AND sal<=3000

IN(一系列值中)

NOT IN

--查询所有办事人员、销售人员、管理者
select * FROM emp WHERE job IN ('CLERK','SALESMAN','MANAGER')
select * FROM emp WHERE job='CLERK' OR job='SALESMAN' OR job='MANAGER'

LIKE(相似值的比较)

--查询姓名以M开头的所有雇员
-- % 匹配符，匹配0个、一个或多个任意字符
select * FROM emp WHERE ename like 'M%'
--查询姓名以S结尾的所有雇员
select * FROM emp WHERE ename like '%S'
--查询姓名包含A的所有雇员
select * FROM emp WHERE ename like '%A%'

EXITS(是否存在符合条件的数据)

select * FROM emp WHERE EXISTS (select * FROM emp WHERE sal>4000)

UNIQUE(是否唯一)

ALL/ANY(一组数据的所有/其中的任何一个)

字符串拼接

--字符串拼接 || s为自命名的列名
select ename,'基本工资：'||sal　from emp
--字段别名
select ename,'基本工资：'||sal s from emp

列表达式

select sal,sal+comm FROM emp
--实现员工各项工资明细和各项工资总和
--列表达式，开始：case  结束：end 当...条件:when 就then 否则else
select '基本工资：'||sal sal,'奖金'||comm comm,
CASE
  WHEN comm IS NULL
    THEN sal
  WHEN comm = 0
    THEN sal
  ELSE sal + comm
end total
from emp

--显示员工各项工资之和大于2000的雇员信息
select * FROM emp WHERE
case
  WHEN comm IS NULL 
    THEN sal 
  WHEN comm = 0
    THEN sal
  ELSE sal + comm
end >2000