SQL基础操作01-条件查询、子查询、exists_排序、单行函数（日期操作）、组函数（数据计算）

表结构： 

条件查询：

-- 按条件查询
--需求: 数据源中的数据满足条件的查询不满足条件不查询,需要使用条件查询
--sql : select 数据 from 数据源 where 行过滤条件;

/*
      条件判断运算符:
         = >  <  >=  <=  !=  <>  
         
      条件连接|逻辑运算符   and   or   not 
      
      区间:  >|>= and <|<=     between .. and ..
*/

-- 查询20部门的员工信息
select * from emp where deptno = 20;

-- 查询工资大于1000的员工的姓名 工作岗位  工资   所属部门编号
--数据: 员工的姓名 工作岗位  工资   所属部门编号
--来源: 员工表
--条件: 工资大于1000
select ename,job,sal,deptno from emp where sal>1000;

-- 查询不在20部门工作的员工信息
select * from emp where deptno != 20;
select * from emp where deptno <> 20;
select * from emp where not deptno = 20;

-- 查询员工的年薪大于20000的 员工名称、岗位 年薪
select ename,job,(sal+nvl(comm,0))*12 from emp where (sal+nvl(comm,0))*12>20000;

--查询出所有员工的员工信息以及年薪信息
select ename,job,(sal+nvl(comm,0))*12 sum from emp;

--对已经查询出的每个员工的奖金进行判断
select * from (select ename,job,(sal+nvl(comm,0))*12 sum from emp) where sum>20000;

--select 数据 from 数据源 where 行过滤条件;
--执行流程: from -- where -- select

-- 查询  any(任意一个)   some(任意一个)  all(所有)
-- 查询工资比我们三个人都高的那些员工的信息
select * from emp where sal > 1000 and sal>1500 and sal>2000;
select * from SCOTT.emp where sal > all(1000,2000,1500);

-- 查询工资比我们三个人都低的那些员工的信息
select * from SCOTT.emp where sal < all(1000,2000,1500);

-- 查询比我们中随便一个工资高的员工信息
select * from SCOTT.emp where sal > some(1000,2000,1500);

-- 查询 工种为’SALESMAN’的员工信息 （注意 内容区分大小写）
select * from emp where job = 'SALESMAN';

-- -检索 工资等于800, 2000, 3000员工名称 岗位 工资
select ename,job,sal from emp where sal=2000 or sal=3000 or sal = 800;
--in(值列表) 满足值列表中的任何一个就可以
select ename,job,sal from emp where sal in(800,2000,3000,5000);

-- 工资在2000到3000之间的员工信息
select * from emp where sal>2000 and sal<3000;
select * from emp where sal between 2000 and 3000;  --相当于<=2000 and >=3000

---查询 岗位 为 CLERK 且部门编号为 20的员工名称 部门编号，工资
select ename,deptno,sal,job from emp where job = 'CLERK' and deptno = 20;

-- 查询 岗位 为 CLERK 或部门编号为 20的员工名称 部门编号，工资
select ename,deptno,sal,job from emp where job = 'CLERK' or deptno = 20;

--查询 岗位 不是 CLERK 员工名称 部门编号，工资
select ename,deptno,sal,job from emp where job != 'CLERK' ;
select ename,deptno,sal,job from emp where job <> 'CLERK' ;
select ename,deptno,sal,job from emp where not job = 'CLERK' ;

-- 查询 岗位 不为 CLERK 并且部门编号不为 20的员工名称 部门编号，工资
select * from emp where not(job = 'CLERK' or deptno = 20);

--存在奖金的员工名称  
select * from emp where not comm is null;
select * from emp where comm is not null;
--select * from emp where comm not is null; 不能这么写!!!

--不存在奖金的员工名称
select ename from emp where comm is null;


-- 查询能领到奖金的员工姓名（>0） 
select * from emp where comm is not null and comm>0;


--查询工资大于1500 或 含有佣金的人员姓名
select * from emp where sal>1500 or comm is not null;

select * from emp where sal>1500;
select * from emp where comm is not null;

--集合操作Union、Union All、Intersect、Minus

-- 通过求并集的方式实现
select * from emp where sal>1500
Union 
select * from emp where comm is not null;

--查询显示不存在雇员的所有部门号。
--所有部门编号
select deptno from dept;

--有员工存在的部门编号
select distinct deptno from emp;

--不存在员工的 部门编号
select deptno from dept
Minus
select distinct deptno from emp;


-- 查询显示存在雇员的所有部门号。
-- 查询内容  deptno
-- 存在雇员


--模糊匹配|模糊查询
--字段 like '值'
--配合% _ 一起使用,才有模糊匹配效果
--% : 任意个任意字符
--_ : 一个任意字符

--查询员工姓名中包含字符A的员工信息
select * from emp where ename like 'KING';  --精确匹配,不推荐,效率低
select * from emp where ename like '%A%';

--查询员工姓名中第二个字母为A的员工信息
select * from emp where ename like '_A%';

--查询员工姓名中包含字符%的员工信息

--1)、编写测试数据
insert into emp (empno, ename, sal) values (1000, 't_%test', 8989);
insert into emp (empno, ename, sal) values (1200, 't_tes%t', 8000);

--2)查询
select * from emp where ename like '%%%';
select * from emp where ename like '%Q%%' escape('Q');

子查询：

--子查询: 查询语句中嵌套查询语句
--查询条件字段与数据源不在同一张表中时候,可以使用子查询

--部门名称为 SALES 或 ACCOUNTING 的员工信息
--数据: 员工信息  *
--来源: 员工表
--条件: dname = SALES 或 ACCOUNTING
select *
  from emp
 where deptno in (select deptno
                    from dept
                   where dname = 'SALES'
                      or dname = 'ACCOUNTING');
--查询部门名称为dname = SALES 或 ACCOUNTING的部门编号
select deptno from dept where dname = 'SALES' or dname= 'ACCOUNTING';
--查询部门 编号为10,30的员工信息
select * from emp where deptno = 10 or deptno = 30;
select * from emp where deptno in( 10 ,30);

-- 查询工资等级为 2的员工信息
--员工信息   工资等级为2 工资等级->工资等级表
select losal from salgrade where grade = 2;
select hisal from salgrade where grade = 2;
select *
  from emp
 where sal between (select losal from salgrade where grade = 2) and
       (select hisal from salgrade where grade = 2);

-- 查询 销售部（SALES） 中 工资大于1500的员工信息
select * from emp where sal>1500 and deptno = (select deptno from dept where dname = 'SALES');

-- 查询工资比SMITH高的同一部门的员工信息
--sal>SMITH薪资 and 的普通农 = SMITH部门编号
select *
  from emp
 where sal > (select sal from emp where ename = 'SMITH')
   and deptno = (select deptno from emp where ename = 'SMITH');

exists_排序： 

--exists 存在   (结果集)   执行效率较高->推荐使用
--select 数据 from 数据源 where exists(结果集);
--从form后数据源中拿出一条数据,然后判断exists()结果集中是否存在数据,如果存在当前数据能被保留在结果集中,不存在不能被保留
select * from emp where exists (select * from dept where deptno = 50);
--查询所有的员工信息
select *
  from emp
 where exists (select deptno, dname
          from dept
         where dname in ('SALES', 'ACCOUNTING'));
--'SALES', 'ACCOUNTING'的员工信息
select *
  from emp e
 where exists (select deptno, dname
          from dept d
         where dname in ('SALES', 'ACCOUNTING')
           and e.deptno = d.deptno);

--10,30以外的员工信息
select *
  from emp e
 where not exists (select deptno, dname
          from dept d
         where dname in ('SALES', 'ACCOUNTING')
           and e.deptno = d.deptno);
           
--查询出所有的        
select *
  from emp e
 where exists (select deptno, dname
          from dept d
         where dname in ('SALES', 'ACCOUNTING')
           and e.deptno != d.deptno);  


--与有奖金的员工同部门的员工信息
select empno, ename, sal,deptno
  from emp e1
 where exists (select empno, ename, sal, comm,deptno
          from emp e2
         where comm is not null
           and e1.deptno = e2.deptno);


--所有的行记录
select * from emp;
select * from emp where 1=1 ;
select * from emp where ename like '%';

--排序  ***
--对结果集中的数据进行排序
--sql: select 数据 from 数据源 where 行过滤 order by 排序字段1 (asc|desc)..;  默认升序
--根据薪资做降序
select * from emp order by sal desc;

--根据薪资降序排序,如果薪资相等,根据奖金做升序排序
select * from emp order by sal desc,comm ;

--如果出现null值,可以让所有null值在最前nulls first或者最后 nulls last;
select * from emp where deptno in(10,30) order by comm nulls last;

单行函数：

/*
1. 单行函数:一条记录返回一个结果
2. 多行函数 组函数 聚合函数 (重点) :多条记录 返回一个结果 (重点)
*/

--单行函数
--sysdate/current_date 以date类型返回当前的日期
select sysdate from dual;
select current_date from dual;

--日期可以相加减
select sysdate-2 from dual;

--add_months(d,x) 返回加上x月后的日期d的值
--转正日期
select ename,hiredate 入职日期,add_months(hiredate,-3) 转正日期 from emp;

--LAST_DAY(d) 返回的所在月份的最后一天
select ename,hiredate,last_day(hiredate) from emp ;

--months_between(date1,date2) 返回date1和date2之间月的数目
select ename,months_between(sysdate,hiredate) from emp;

--next_day(sysdate,星期一) 下一个即将要过的日期
select next_day(sysdate,'星期三') from dual;

--日期与字符串之间的转换问题
--to_char(date,'格式模板')
--to_date(字符串,'格式模板')

select to_date('2017-3-21 18:12:12', 'yyyy-mm-dd hh24:mi:ss')+1 time from dual;
select to_char(sysdate, 'yyyy_mm_dd hh12:mi:ss') time from dual;

select to_char(sysdate, 'yyyy"年"mm"月"dd"日" hh12:mi:ss') time from dual;

--判定函数 decode(判定字段,case1,express1,case2 ,express2,….casen , expressn, expressionm)  如果判定字段的结果为值2,decode函数结果为表达式1,字段为值2,结果为表达式2,如果以上所有值不满足,可以添加默认值
----给所有的员工 涨薪,10-->10% 20-->8% 30 -->15% 其他-->20% --decode
select ename,sal 原薪资,decode(deptno,10,sal*1.1,20,sal*1.08,30,sal*1.15,sal*1.2) 涨薪后的薪资 from emp;

--case when then else end
select ename,
       sal,
       deptno,
       (case deptno
         when 10 then
          sal * 1.1
         when 20 then
          sal * 1.08
         when 30 then
          sal * 1.15
         else
          sal * 1.2
       end) raisesal
  from emp;
  
--单行函数: 重点转换函数  判定函数
-- 查询81入职的员工信息

组函数：

-- avg(字段) min max sum count ---确定结果集,对结果集的数据求组函数
--注意: 当select后面一旦出现组函数,只能和其他的组函数,或者分组字段一起使用
      ----null 值不参与组函数计算
-- 统计一下一共有多少个员工
select count(*) from emp;
select count(empno) from emp;
select count(1) from emp;
select count(deptno) from emp;
select deptno from emp;

-- 统计一共有几个部门 
select count(deptno) from dept;
select count(loc) from dept;

-- 统计有员工存在的部门总数
select count( distinct deptno) from emp;

-- 统计20部门一共有多少人
select count(1) from emp where deptno = 20;

-- 计算本公司每个月一共要在工资上花费多少钱
select sum(sal) from emp;

-- 查询本公司的最高工资和最低工资
select max(sal),min(sal) from emp;

--查看30部门的最高工资和最低工资
select max(sal),min(sal) from emp  where deptno = 30;

-- 请查询出 20部门的平均工资
select avg(sal) from emp where deptno = 20;

--null 值不参与组函数计算
-- 计算出所有员工的奖金总和
select comm from emp;
select sum(comm) from emp;

-- 统计有奖金的员工有几个
select count(comm) from emp;

--查询 最高薪水的员工姓名,及薪水
select max(sal) from emp; --最高薪资
select ename,sal from emp where sal = (最高薪资);
select ename,sal from emp where sal = (select max(sal) from emp);

-- 查询工资低于平均工资的员工编号，姓名及工资
--平均工资
select avg(sal) from emp;
-- 查询工资低于平均工资的员工编号，姓名及工资
select *  from emp where sal<(平均工资);
select *  from emp where sal<(select avg(sal) from emp);

--练习
--查询高于SMITH所在部门平均薪资的员工信息
select *
  from emp
 where sal >
       (select avg(sal)
          from emp
         where deptno = (select deptno from emp where ename = 'SMITH'));
--查看高于本部门平均薪水员工姓名
select ename
  from emp e1
 where sal > (select avg(sal) from emp e2 where e1.deptno = e2.deptno);