Oracle使用（二）_where子句

/**
=,!=,<>,<,>,any,some,all

is null,is not null

between and

in(list),not in(list)

*/

-- =使用
select * from emp where deptno =20;

-- != 使用
select * from emp where deptno !=20;

-- <> 使用 表示不等于,效果等价!=

select * from emp where deptno <> 20;

-- <

select sal from emp where sal < 1500;

-- >
select sal from emp where sal > 1500;

-- <=
select sal from emp where sal <= 1500;

-- >=
select sal from emp where sal >= 1500;

-- any 满足任意any中任意条件即可

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

--some 等价any
select sal from emp where sal >some(1000,1500,3000);

-- all 要同时满足所有条件
select sal from emp where sal >all(1000,1500,3000);

-- is null,在sql语法中null表示特殊含义，查询的条件中null不等于数据库中null值
-- 想查数据库中空值要用 is null 或者is not null

-- 什么都查不出 null != null
select * from emp where comm = null;

-- 等于null
select * from emp where comm is null;

-- 不等于null
select * from emp where comm is not null;

-- between 在..之间的值,and用与连接两个条件

select * from emp where sal between 1500 and 3000;

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

-- in(list)
select * from emp where deptno in(10,20);
-- 等价in(list)
select * from emp where deptno = 10 or deptno = 20;

/**
and 和or出现在同一个sql语句中，and优先级要高于or，
所以使用()将or条件包含以提高优先级
*/

-- not in(list) 查出不在in范围
select * from emp where deptno not in(10,20);

-- 等价not in(list)
select * from emp where deptno !=10 and deptno !=20;

-- 相当于双重循环 先查出emp表所有记录，再根据子查询过滤数据，
--本例子查询一直返回true所以没起到过滤效果，所以全部返回
--https://www.runoob.com/sql/sql-exists.html 起到过滤效果的例子
select * from emp where exists (select deptno from dept where deptno =10 or deptno = 20);

/**
exsits 子查询如果满足恒为true，那么外层数据就回全部返回
此时 子查询起不到过滤效果

and的优先级高于or，or需要括起来，否则还是永恒返回true起不到过滤效果
*/
select * from emp e where exists (select deptno from dept d where (deptno =10 or deptno = 20) and e.deptno=d.deptno);

/**
like()

_下划线，匹配一位数字或字母
%百分号，匹配一位或者多位数字或字母
使用like要慎重，效率比较低
使用like参考使用索引，但是不能以%开头
涉及到大文本检索 可使用框架
escape 自定转义字符义 lucence 、solr、 elastic search

*/
-- 查询以S开头的用户
select * from emp where ename like('S%');

-- 查询以字母S开头，倒数第二位为T的员工姓名
select * from emp where ename like('S%T_');

-- 查询以字母S开头，并包含字母T的员工姓名
select * from emp where ename like('S%T%');

-- 查询名字带有百分号的用户 escape 自定义转义字符
select * from emp where ename like('%%%') escape('');

/**
order by 进行升序排序

order by desc 降序排序

排序默认按孜然顺序排序的

数值：从小到大

字符串：按字典序

排序可指定多个字段，并且多个字段可采取不同的排序方式

order by相当于做了全排序，效率很低，如果排序字段很大，非常消耗内存，
需要在业务闲时做操作 12306凌晨不售票原因就是例子
*/

-- order by 排序 默认升序

select * from emp order by sal;

-- order by .. desc 降序
select * from emp order by sal desc;

-- 按员工姓名排序
select * from emp order by ename;
-- 多个字段排序时，会以第一个字段为主排序，其他为辅
select * from emp order by ename asc,sal desc;

/**
计算字段
为什么需要计算字段？
我们经常需要从数据库中检索出经过转换、计算、格式化的数据
计算字段不真实存在于数据表中
*/
-- 字符串连接
select 'my name is' || ename from emp;

-- concat 函数 完成字符串连接
select concat('my name is',ename) from emp;

-- 计算所有员工的年薪,会发现很多员工数据为空，原因null做任何运算都是null，需要进行转换
select ename,(e.sal+e.comm)*12 from emp e;

--- nvl 函数可将null默认值转为0
select ename,(nvl(e.sal,0)+nvl(e.comm,0))*12 from emp e;

--dual时oracle数据库中得虚拟表，没有实际数据，可用来测试
select 100+null from dual;

/**
并集
交集
全集
差集
*/
--A
select * from emp where deptno = 30;

--B
select * from emp where sal > 1000;

--并集 将A，B合并，不包含重复数据

select * from emp where deptno = 30 union
select * from emp where sal >1000;

--全集 将A，B合并，不去重

select * from emp where deptno = 30 union all
select * from emp where sal >1000;

-- 交集 AB公共部分

select * from emp where deptno = 30 intersect
select * from emp where sal >1000;

--差集 包含在A中，但不包含在B中的数据，跟A和B的顺序有关
select * from emp where deptno = 30 minus
select * from emp where sal >1000;

--查询部门名称位SALES和RESEARCH的雇员姓名、工资和部门编号
select ename,sal,deptno from emp where deptno in(select deptno from dept where dname ='SALES' or dname ='RESEARCH');

--使用exsists查询部门名称位SALES和RESEARCH的雇员姓名、工资和部门编号
select ename,sal,deptno from emp e where exists(select deptno from dept d where (dname ='SALES' or dname ='RESEARCH') and e.deptno = d.deptno);