Oracle数据库学习Day02——select语句

1 select条件查询

公式：select 查询数据 from 数据来源 别名 where 行过滤条件;
执行流程：先走from — where —select
注：表起别名不能加as
判断条件：

a)、= 、 >、 <、 >=、 <=、 !=、 <>、 between and
b)、and 、or、 not、 union、 union all、 intersect 、minus
c)、null :is null、 is not null、 not is null
d)、like :模糊查询 % _ escape(‘单个字符’)

例1、查询员工的年薪大于20000的 员工名称、岗位 年薪

select ename 员工名称, job 岗位, sal 月薪, (sal+nvl(comm,0))*12 年薪 from emp where (sal+nvl(comm,0))*12 > 20000;

例2、查询工资比我们三个人都高的那些员工的信息

select * from emp where sal>all(900,1000,1100);

例3、查询比我们中随便一个工资高的员工信息

select * from emp where sal>some(900,1000,1100);

例4、 工种为’SALESMAN’的员工信息 （注意 内容区分大小写）

select * from emp where job ='SALESMAN';

null 值的特殊判断方式

select ename from emp where not comm is null;
select ename from emp where comm is not null;

交并集
Union，并集(去重) 对两个结果集进行并集操作，不包括重复行同时进行默认规则的排序；
Union All，全集(不去重) 对两个结果集进行并集操作，包括重复行，不进行排序 ；
Intersect，交集(找出重复) 对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；
Minus，差集(减去重复) 对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序
例5、查询显示不存在雇员的所有部门号

select deptno from dept
Minus
select distinct deptno from emp;

匹配查询
对某个字符串值进行匹配 like(效率较低)
结合_ %一起使用 _一个任意字符 %任意个任意字符
例6、查询员工姓名中包含字符A的员工信息

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

例7、查询员工姓名中第二个字母为A的员工信息

select * from emp where ename like '__A%';

例8、查询工资为 1500， 2000， 2500， 5000的员工的信息

select * from emp where sal=1500 or sal=2000 or sal=2500 or sal=5000;
select * from emp where sal = any(1500， 2000， 2500， 5000);

当对某写定值进行判断满足某一个,推荐使用in(值列表) 满足其中任意一个就可以

select * from emp where sal in (1500， 2000， 2500， 5000);

嵌套复杂查询
-查询工资等级为 2的员工信息
select * from salgrade;
1)工资等级为2的losal 和 hisal区间值

select losal from salgrade where grade =2;
select hisal from salgrade where grade =2;

2. 在emp表中查询薪资在losal 和 hisal之间的员工信息

select * from emp where sal between 1201 and 1400;

3. 汇总

select *
  from emp
 where sal between (select losal from salgrade where grade = 2) and
       (select hisal from salgrade where grade = 2);

获取所有的行记录

select * from dept;
select * from dept where 1=1;

排序 order by
公式：select 查询数据 from 数据来源 where 行过滤条件 order by 排序字段1 desc(降序),排序字段2 asc(升序)…;
执行流程: from --where --select–order by

查询所有员工的薪资,降序排序

select * from emp order by sal; --默认升序
select * from emp order by sal desc;

查询薪资>1100同时是30部门的员工,根据部门编号升序排序,部门相同的根据comm排序

select * from emp where sal>1100 and deptno in(10,30) order by deptno asc,comm desc;

测试排序时候null值如何处理

select * from emp order by comm nulls first;  --所有的null值在前面
select * from emp order by comm nulls last; --所有的null值在最后

exists()
存在即保留,存在即合法 ()中的内容可以根据题意任意添加,之判断最终是否有结果
公式：select * from 数据来源 where exists(结果集) ;
判断流程: 先从from拿出一条数据执行where判断,观察exists后面的()结果集中有值么,如果有当前判断的这条数据就能保留select,如果()结果集中没有结果,就不保留

select * from emp where exists(select * from dept where deptno = 30 and dept.deptno = emp.deptno);

dept.deptno = emp.deptno是让外面正在判断的语句与内部已经过滤过的结果集进行进一步判断

如果没有dept.deptno = emp.deptno，没有过滤效果

2 单行函数

当前时间

select distinct sysdate from emp;
select sysdate from dual;
select current_date from dual;

查询所有员工的试用期期到期（转正的日期） 3个月试用期

select ename,empno,hiredate 入职日期,hiredate+90 from emp;

add_months(hiredate,3) --直接加月份，更精确

select ename,empno,hiredate 入职日期,add_months(hiredate,3) from emp;

select ename,empno,hiredate 入职日期,add_months(hiredate,3) from emp;

查询所有员工到目前为止一共工作了几个月

select ename,hiredate , sysdate,months_between(sysdate,hiredate) from emp;

查询当前月的最后一天

select sysdate,last_day(sysdate) from dual;

下一个星期三是几号

select next_day(sysdate,'星期二') from dual;

设定一个特定的时间（用一个特定的时间字符串转换为日期）
-设定一个时间 就是今天 ‘2018-9-5 16:18:25’
to_date(日期字符串,日期转换模板) ‘yyyy-mm-dd hh24:mi:ss’

select to_date('2018-9-5 16:18:25','yyyy-mm-dd hh24:mi:ss') from dual;

将日期转为特定格式的字符串 to_char

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

10部门涨薪10%， 20涨薪20%，30降薪1% ， 40部门翻倍3倍

select deptno,dname,decode(deptno,10,sal * 1.1,20,sal * 1.2,30,sal * 0.99,40,sal * 3) from dept;

case when then else end

select ename,
       sal,
       deptno,
       (case deptno
         when 10 then
          sal * 1.1
         when 20 then
          sal * 1.2
         when 30 then
          sal * 0.99
         else
          sal * 3
       end) raisesal
  from emp;

查询82年入职员工的信息

select * from emp where hiredate between to_date('1981-01-01','yyyy-mm-dd') and to_date('1981-12-31','yyyy-mm-dd'); 
select ename,hiredate,to_char(hiredate,'yyyy') from emp where to_char(hiredate,'yyyy') = '1982';

3 组函数

sum() count() max() min() avg() 对确定的结果集求组函数
计算组函数,null值不参与运算
组函数不能与非分组字段一起使用,组函数只能和组函数一起使用,不能与其他普通字段一起使用 group by
组函数不能使用在where后面

统计一下一共有多少个员工

select count(deptno) from emp;
select count(empno) from emp;
select count(1) from emp;

最高、最低、总和、平均

select max(sal),min(sal),sum(sal),avg(sal) from emp;

查看高于本部门平均薪水员工姓名

select * from emp e1 where sal > (本部门薪资);
select avg(sal) from emp wehere deptno = 20; 
select ename from emp e1 where sal>(select avg(sal)from emp e2 where e1.deptno=e2.deptno);

4 分组（group by）

select 查询数据 from 数据来源 where 行过滤条件 group by 分组字段1,分组字段2… having 组过滤条件 order by 排序字段…;
执行流程: from – where – group by – having – select – order by
注意: 如果一旦分组,select后只能为分组字段或者组函数

找出20部门和30部门的最高工资

select max(sal),deptno from emp group by deptno having deptno in (20,30);  --先分组后过滤
select max(sal),deptno from emp where deptno in (20,30) group by deptno;  --先过滤后分组

求出10和20部门部门的哪些工资高于1000的员工的平均工资

select avg(sal) from emp where sal>1000 and deptno in(10,20) group by deptno;
select avg(sal) from emp where sal>1000 group by deptno having deptno in(10,20);

查询 最低平均工资的部门编号
所有部门中最低平均工资

select min(avg(sal)) from emp group by deptno;

求出每个部门的平均工资和部门编号

select avg(sal),deptno from emp group by deptno;

平均工资最低的部门

select avg(sal), deptno
  from emp
 group by deptno
having avg(sal) = (select min(avg(sal)) from emp group by deptno);

select *
  from (select avg(sal) avg_sal, deptno from emp group by deptno)
 where avg_sal = (select min(avg(sal)) from emp group by deptno);

5 行转列*

select name,
       min(decode(course, '语文' ，score)) ，min(decode(course, '数学' ，score)) ，min(decode(course, '英语' ，score))
  from tb_student
 group by name;