查询-单行函数-组函数-子查询-行转列

查询

**模糊查询：**模糊查询使用通配符:  %:零个及以上(任意个数的)的字符
 _:一个字符
 遇到内容中包含 % _ 使用escape(‘单个字符’)指定转义符

--模糊匹配  like %任意任意字符  _一个任意字符   一起使用
--查询员工姓名中包含字符A的员工信息
select * from emp where ename like '%A%';
--完全匹配
select * from emp where ename like 'SMITH';

--查询员工姓名以'A'结尾的员工信息
select * from emp where ename like 'A%';


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

** in 与 exists**

	in相当于使用or的多个等值，定值集合 ,如果存在 子查询，确保 类型相同、字 段数为1，如果记录多，效率不高，用于 一些 少量定值判断上：

--1)先查询出部门名称=SALES 或 ACCOUNTING对应的部门编号
select deptno from dept where dname in('SALES','ACCOUNTING');

--子查询
select *
  from emp
 where deptno in
       (select deptno from dept where dname in ('SALES', 'ACCOUNTING'));

exists条件为true,存在记录则返回结果，后续不再继续 比较查询，与查询的字
段无关，与记录有关:

--exists 存在即保留,存在即合法
--exists后面括号内的查询出结果返回true否则为false返回ture查询出exists前面的内容
select *
  from emp
 where exists (select deptno from dept where dname = 'SALES');

别名

select *
  from emp 别名1
 where exists (select deptno
          from dept 别名2
         where dname not in ('SALES', 'ACCOUNTING')
            and 别名1.deptno =别名2.deptno);

时间

	日期函数: 注意区分 db数据库时间 ，java应用服务器的时间。以一方为准
	oracle以内部数字格式存储日期:年,月,日,小时,分钟,秒 
	 sysdate/current_date 以date类型返回当前的日期
	 add_months(d,x) 返回加上x月后的日期d的值
	 LAST_DAY(d) 返回的所在月份的最后一天
	 months_between(date1,date2) 返回date1和date2之间月的数目
	 next_day(sysdate,'星期一') 下周星期一

1)、当前时间
select current_date from dual where 1=1;
select sysdate from dual where 1=1;
2)、修改日期（天数+-）
--两天后的时刻
select sysdate+2 from dual;
3)、修改月份
--当前5个月后的时间
select add_months(sysdate,5) from dual;
--雇佣日期 2个月的时间
select ename,hiredate, add_months(hiredate,2) after from emp;
4)、月份之差
--雇佣日期 距离现在的 月份数
select ename, months_between(sysdate , hiredate) from emp;
5)、最后一天
--返回雇佣日期 当月最后一天的时间
select ename, last_day(hiredate) d from emp;
6)、下一个星期的时间
--下一个星期二
select next_day(sysdate, '星期二') from dual;

2、转换函数（重点***）
to_date(c,m)  字符串以指定格式转换为日期
to_char(d,m)  日期以指定格式转换为字符串
select to_date('2017-3-21 18:12:12','yyyy-mm-dd hh24:mi:ss') time
from dual;
select to_char(sysdate,'yyyy-mm-dd') from dual;
select to_char(sysdate,'yyyy/mm/dd') from dual;
select to_char(sysdate,'yyyy\mm\dd') from dual;
注意中文的问题
--select to_char(sysdate,'yyyy年mm月dd日') from dual;select
to_char(sysdate,'yyyy"年"mm"月"dd"日"') from dual;
--查询 82的员工信息
select * from emp where hiredate like '%82%' ; 或
select * from emp where to_char(hiredate,'yyyy')='1982'; 或
select *
from emp
where hiredate between to_date('1982-01-01', 'yyyy-mm-dd') and
to_date('1982-12-31', 'yyyy-mm-dd');

3、其他函数 (保证类型兼容)

    --1)、nvl nvl(string1,string2)  如果string1为null,则结果为string2的值
    select ename, nvl(null,0) from emp;
    select ename, nvl(to_char(comm),'hello') from emp;
    --2)、decode decode(condition,case1,express1,case2 , express2,….casen , expressn, expressionm)
    select ename,decode(deptno, 10,'十',20,'二十') from emp;
    --3)、case when then else end

组函数

组函数|多行函数|聚合函数 即多条记录 返回一个结果。我们需要掌握如下几个组函数：
avg 、sum、 min、 max、 count
1)、count :统计记录数 count() -->* 或一个列名
2)、max min: 最大值 最小值
3)、sum：求和
4)、avg:平均值
注意: 1、组函数仅在选择列表和Having子句中有效
2、出现组函数，select 只能有组函数或分组字段
说明：
 组信息 与单条记录不能同时查询
 组函数 不能用在 where中，能使用的地方 select having
 null 不参与运算

1、count
–1、count统计所有的员工数
–1)、*
–2)、主键
–3)、推荐
select ename,1 from emp;
select count(1) from emp where 1=1;

2、max min: 最大值 最小值
–查询所有员工的 最高薪水 ，最低薪水，员工总数 -->组信息
select max(sal) maxSal , min(sal) minSal , count(1) from emp;

3、sum：求和-- 查询10部门的所有员工的工资总和select sum(sal) from emp where deptno=10;

4、avg:平均
– 查询工资低于平均工资的员工编号，姓名及工资
select empno, ename,sal from emp where sal<(select avg(sal)from
emp);
–查看 高于本部门平均薪水员工姓名
select * from emp e1 where sal>(select avg(sal) from emp e2 where
e1.deptno=e2.deptno );

分组

 分组: group by , 将符合条件的记录 进一步的分组
 过滤组:having , 过滤组信息 ，表达式 同 where 一致
现在的结构如下
select distinct * | 字段 | 表达式 | 函数 as 别名
from 表 表别名
where 过滤行记录条件
group by 分组字段列表
having 过滤组
order by 字段列表 asc | desc

group by :分组
1)、select 出现分组函数,就不能使用 非分组信息，可以使用 group by 字段
2)、group by字段 可以不出现 select 中 ，反之select 除组函数外的，其他字段必
须出现在group by 中
过滤组 having :
where :过滤行记录，不能使用组函数， having:过滤组 可以使用组函数

--按 部门 查询 平均工资
select avg(sal) from emp group by deptno;
--按 部门岗位 查询 平均工资
select avg(sal) from emp group by deptno,job;
--按 部门 查询 平均工资，且平均工资大于2000的部门编号
--1、先分组 后过滤 (不推荐)
select *
from (select deptno, avg(sal) avsal from emp where 1 = 1 group by
deptno)
where avsal > 2000;
--2、过滤组 ,分组同时 过滤
select avg(sal), deptno from emp group by deptno having
avg(sal)>2000;
--查询 最低平均工资的部门编号
--1)、按部门求出平均薪水
select avg(sal) from emp group by deptno;
--2)、找出最低的平均薪水
select min(avg(sal)) from emp group by deptno;

行转列

create table tb_student(
id number(4) ,
name varchar2(20),
course varchar2(20),
score number(5,2)
);
insert into tb_student values(1,'张三','语文',81);
insert into tb_student values(2,'张三','数学',75);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);

insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(7,'王五','英语',90);
commit;
drop table tb_student cascade constraints;
*/


--找出课程名(表头)
select distinct course from tb_student;
--数据(行记录) 分组（学生+行转列 decode）
select * from tb_student;
--1、行转列 decode
select name,decode(course,'语文',score) 语文,decode(course,'数学
',score) 数学,
decode(course,'英语',score) 英语 from tb_student;
--2、分组
select name,
min(decode(course, '语文', score)) 语文,
min(decode(course, '数学', score)) 数学,
min(decode(course, '英语', score)) 英语
from tb_student
group by name;