Oracle19C入门到熟练008-过滤查询

学习要求

有一定关系型数据的操作功底，会SQL语句

教学目标

学习SQL 过滤查询

语法

select 列1,列2,列3.... from 表名  别名  where 过滤条件

注意点

• 字符串和日期要用单引号扩起来

• 数字类型直接书写

• 字符串是大小写敏感的，日期值是格式大小写敏感的

• 字符串若要不区分大小写，可以使用upper(“字符串”)/lower(“字符串”) 进行间接替换

空值

空值是无效的，未指定的，未知的或不可预知的值，用 NULL 表示

注意

• 空值是指不可用、未分配的值，也就是没有值；

• 空值不等于零或空格，也不表示空字符串；

• 任意类型都可以支持空值，也就是说任何类型的字段都可以允许空值作为值的存在；

• 包括空值的任何算术表达式都等于空。

• NULL的处理使用NVL函数

• 比较时使用关键字用“is null”和“is not null”

• 空值不能被索引，所以查询时有些符合条件的数据可能查不出来，count(*)中，用nvl(列名,0)处理后再查

• 排序时比其他数据都大（索引默认是降序排列，小→大），所以NULL值总是排在最后

需求：查询没有提成(奖金) 员工信息

select * from  emp where comm is null;

select * from  emp where comm = null;  --错误写法，是否为null使用 is 或者 is not

需求：查询有提成(奖金) 员工信息

select * from  emp where comm is not null;

需求：查询员工一年总收入

select ename, (sal+comm) * 12 annualSalary from emp; -- 错误写法
select ename, (sal + NVL(comm, 0)) * 12 annualSalary from emp;

需求：查询员工信息， 按照提成正序排

select * from emp order by comm asc;

比较运算符

常见比较运算符

操作符	含义	示例
=	等于	where age = 18
>	大于	where age > 18
>=	大于等于	where age >= 18
<	小于	where age < 18
<=	小于等于	where age <= 18
<>	不等于	where age <> 18
!=	不等于	where age != 18
between...and...	介于2值之间(边界值能取等)	where age between 18 and 30
in(set)	等于set列表中一个值	where age in (18, 19, 20)
like	模糊查询	where name like '%fei%'
is null	空值	where name is null

需求：查询有奖金的员工信息

select * from emp where comm is null;

 需求：查询公司的老板

select * from emp where mgr is null;

需求：查询出基本工资高于 1500 的所有员工信息

select * from emp where sal > 1500;

需求：查询名字叫 SMITH的员工所从事的工作

select * from emp where ename = 'SMITH';  -- 字符串区分大小写
select * from emp where upper(ename) = upper('smith');   -- 不区分字符串大小写
select * from emp where lower(ename) = lower('SMITH');   -- 不区分字符串大小写

需求：查询 1981 年入职的员工信息

 SELECT  *  from emp where extract(year from hiredate) = '1981';

注意：日期提取函数：extract， 后面再讲

需求：查询年薪小于 3W 的员工

select ename, (sal + nvl(comm, 0)) * 12  annualSalary from emp where  annualSalary < 30000;  -- 错误写法
select ename, (sal + nvl(comm, 0)) * 12  annualSalary from emp where  (sal + nvl(comm, 0)) * 12 < 30000;

注意： select中where 语法先与select执行，所有不能直接使用列别名进行where 条件判断

需求：查询所有不是销售人员的员工信息

select * from emp where job <> 'SALESMAN';
select * from emp where job != 'SALESMAN';

需求：查询工资在 2000-3000 之间的员工信息

select * from emp where sal between 2000 and 3000;    

注意： 边界值2000 3000是可以取得到到的， sal 取值范围应该是： [2000, 3000]

需求：查询工资为 800 或 1600 或 3000 的员工

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

需求：查询出所有雇员姓名是以 A 开头的全部雇员信息。

select * from emp where ename like 'A%';  -- 字符区分大小写
select * from emp where upper(ename) like upper('A%');  -- 不区分大小写查询

需求：查询出雇员姓名第二个字母是 M 的全部雇员信息。

select *from emp where ename like '_M%';  -- 字符区分大小写
select *from emp where upper(ename) like upper('_M%');  -- 不区分大小写查询

需求：查询出雇员姓名以N字母结尾的全部雇员信息。

select * from emp where ename like '%N';  -- 字符区分大小写
select * from emp where upper(ename) like upper('%N');  -- 不区分大小写查询

需求：查询出雇员姓名任意位置上包含字母 A 的全部雇员信息。

select *from emp where ename like '%A%';  -- 字符区分大小写
select *from emp where upper(ename) like upper('%A%');  -- 不区分大小写查询

注意

like 语法匹配规则

关键字% ：匹配以关键字开头的字符

%关键字 ：匹配以关键字结尾的字符

%关键字% ：匹配包含关键字的字符

_ ：匹配任意一个字符

一个问题， 如果查询字符里面有特殊的% _ 字符怎么办？

需求：查询出岗位名称中含有特殊字符:%的员工信息

select * from emp where job like '%%%';    -- 这种语法查出所有数据

此时需要指定转义符：ESCAPE

select * from emp where job like '%\%%' ESCAPE '\';

逻辑运算符

常用运算符

操作符	含义	示例
and	逻辑并	where age > 18 and age < 30
or	逻辑或	where age < 18 and age > 30
not	逻辑非	where deptno is not null

注意

• AND：如果组合的条件都是 true，返回 true；

• OR：如果组合的条件 之一是 true ，返回 true；

• NOT：如果下面的条件是 false，返回 true。

• 优先级规则：比较运算符 > NOT > AND > OR。

需求：查询姓名中有 E 或者 A 的员工姓名

select * from emp where ename like '%E%' or ename like '%A%';

需求：查询工资在 1500~3000 之间的全部员工信息

select * from emp where sal >= 1500 and sal <= 3000;

需求：查询工资不在 2000-3000 之间的员工信息

select * from emp where sal < 2000 or sal > 3000;

需求：查询工资不为 800 或 1600 或 3000 的员工

select * from emp where sal in(800, 1600, 3000);  -- in
select * from emp where sal not in(800, 1600, 3000);  -- not in

需求：查询出职位是办事员 (CLERK) 或者是销售人员 (SALESMAN) 的全部信息，且工资在 1000 以上

select * from emp where job = 'CLERK' or job = 'SALESMAN' and sal > 1000;  -- 错误写法
select * from emp where (job = 'CLERK' or job = 'SALESMAN') and sal > 1000; 

注意：优先级规则

优先级	条件
1	算数运算( + - * /)
2	连接符( || )
3	比较符(> = >= < <= <>)
4	in，not in，null，not null， like
5	between... and ... not between...and...
6	not
7	and
8	or

从上到下，优先级依次下降