Oracle的select语句进阶和函数

1.分页

1.分页是通过限制返回的行数实现的
2.在oracle数据库中每一个表都有一个虚列rownum,它是从1开始的数字排序列。
3.rownum只能使用小于，而不能使用大于
4.开始测试，先把今天要用到的数据表创建一下吧（数据自己填一点）

CREATE TABLE EMP2
   (	"EMPNO" NUMBER(4,0), 
	"ENAME" VARCHAR2(10 BYTE), 
	"JOB" VARCHAR2(9 BYTE), 
	"MGR" NUMBER(4,0), 
	"HIREDATE" DATE, 
	"SAL" NUMBER(7,2), 
	"COMM" NUMBER(7,2), 
	"DEPTNO" NUMBER(2,0), 
	"SEX" CHAR(10 BYTE)
   )

1.返回四条数据

select * from emp2 where rownum<5;

上面我们说了不能使用大于，那么我们怎么拿到第几条到第几条的数据呢

2.返回第5到第10条数据（包含5和10）

select * from(select e.*,rownum r from emp2 e where rownum<=10) where r>=5;

3.--按工资降序排序 要5-8条 

select * from (select e.*,rownum r from emp e where rownum <=8 order by e.sal desc)
看似这个已经实现了对工资的排序，但是由于rownum是在order by
之前便存在的，所以（可以随便找一列的工资和R对应看看就明白了），所以在排序中跟着工资列被打乱了。

4.那么如何解决呢

--首先将整个emp表进行排序
select * from emp order by sal desc;
--然后进行封装成对象 e,在进行r的查询
select e.*,rownum r from (select * from emp order by sal desc) e where rownum<=8;
--最后在执行
where r>=5
--完整过程就是如下
select * from (select e.*,rownum r from (select * from emp order by sal desc) e where rownum<=8) where r>=5;

2.日期函数

1.首先说一下dual列
–dual是oracle内置的最小的表 单行单列

1.--在查询的当前月份上加上两个月

select add_months(sysdate,2) from dual;

2.--减两个月

select add_months(sysdate,-2) from dual;

3.--为指定日期加上指定月份

select ename,hiredate,add_months(hiredate,1) as newdate from emp;

4.--指定日期与今日的日期的月份差

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

5.--指定日期与今天的天数差

select ename,hiredate,sysdate-hiredate from emp;

6.--返回某个指定日期月的最后一天

select last_day(sysdate) from dual;

7.--返回指定日期的下一个星期几的日期

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

8.--截取时间值函数
  --截取年份的时间值

select extract(year from sysdate) from dual;
--截取月份的时间值
select extract(month from sysdate) from dual;
--截取日期的时间值
select extract(day from sysdate)+1 from dual;
--截取日期的时间值+1
select extract(day from sysdate)+1 from dual;

--获取今天过生日的员工的信息
select * from emp where extract(month from hiredate)=extract(month from sysdate) 
and extract(day from hiredate)=extract(day from sysdate);

3.字符函数

--将首字母大写Hello
select initcap('hello') from dual;

--将字符转为小写 (输出全部小写)hhh
select lower('HHh') from dual;
--将字符转为大写 （输出为全部大写）HHI
select upper('HHi') from dual;

--从左边截掉指定的字符串helloabc
select ltrim('abchelloabc','abc') from dual;
--从右边截掉指定的字符串 abca
select rtrim('abcabd','bd') from dual;

--替换指定的字符串 back (有多少相同的字符串替换多少)
select translate('jack','j','b') from dual;
select replace('jack','j','b') from dual;

--indexof 查询指定的字符串首次出现的位置 3
select instr('abcdc','c') from dual;

--substring 字符串截取 （从第几个字符开始，截取几个，包含开始的字符）bcd
select substr('abcde',2,3) from dual;
--字符串连接 aabb (将两个字符串连接起来)
select concat('aa','bb') from dual;

--从左边补充字符串数量至9个，所需的字符串从后面的参数中取得 hellabcde
select lpad('abcde','9','hello') from dual;
--从右边补充字符串数量至9个，所需的字符串从后面的参数中取得 abcdehell
select rpad('abcde','9','hello') from dual;
--从两边去除指定的字符 仅限两边 不包括中间 444646994
select trim('9' from '99944464699499') from dual;
--查询字符串的长度
select length('abcdef') from dual;

--判断是否条件成立，成立显示信息1 不成立显示信息2 
 select empno,comm ,decode(comm,'','暂无补助',comm) from emp;
 
 --转换函数
 --转换为字符串格式
 select to_char(sysdate,'yyyy-mm-dd') from dual;
 
insert into emp2(empno,ename,hiredate) values(20,'heihei',to_date('2021-4-14','yyyy-mm-dd'));

4.聚合函数

--sum
select sum(sal) from emp;

--avg
select avg(sal) from emp;

--max
select max(sal) from emp;

--min
select min(sal) from emp;

--count
select count(*) from emp;
select count(empno) from emp;

5.一些应用sql实例

1.聚合函数实例

--查询部门名叫开发部的员工的信息
select * from emp where deptno=(select deptno from dept where dname='开发部');
--查询部门名为开发部的员工工资总额
select sum(sal) from emp where deptno =(select deptno from dept where dname='开发部');
--查询部门名为开发部的员工人数
select count(empno) from emp where deptno=(select deptno from dept where dname='开发部');

--查询部门名为开发部的员工的平均工资
select avg(sal) from emp where deptno =(select deptno from dept where dname='开发部');

--查询部门名为开发部的员工的最低工资
select min(sal) from emp where deptno =(select deptno from dept where dname='开发部');

--查询部门名为开发部的员工的最高工资
select max(sal) from emp where deptno =(select deptno from dept where dname='开发部');

2.分组

--查询每个部门的总工资
select deptno,sum(sal) from emp group by deptno;

--查询每个部门的男女人数
select deptno,sex,count(empno)from emp2 group by deptno,sex order by deptno;

--查询部门总工资大于10000的部门
--1.
select * from (
    select deptno,sum(sal) money from emp group by deptno
)where money>10000;

--2.
select deptno,sum(sal) money from emp group by deptno having sum(sal)>10000;

3.内外连接

--内连接
--1.
select * from emp e,dept d where e.deptno=d.deptno;
--
2.
select * from emp e inner join dept d on(e.deptno=d.deptno);

--3.
select * from emp e  join dept d on(e.deptno=d.deptno);

--外连接
--左连接 以左边为基本表，查询左边不为空时最多条数据的sql
--1.
select * from emp e left join dept d on(e.deptno=d.deptno);
--2.(+)在那边不以那边为基本表
select * from emp e ,dept d where e.deptno=d.deptno(+);

--右连接 以右边为基本表，查询右边不为空时最多条数据的sql
--1.
select * from emp e right join dept d on(e.deptno=d.deptno);
--2.
select * from emp e,dept d where e.deptno(+)=d.deptno;

6.SQL格式说明

select <列名> from <表名> where <条件表达式> group by <分组条件>
  having <过滤条件> order by <排序列> <排序关键词>