Oracle 函数、 sql执行顺序、oracle分页、oracle删除重复记录

一.函数

1.单行函数

1.1 字符函数

(1) upper 将所有字母大写

select upper('dsaFDSDFA') from dual;

(2) lower,将所有字母小写

select lower('adsfADF') from dual;

(3) initcap 所有单词首字母大写,其他小写

select initcap('dasFDAFas') from dual;

(4) length 用于计算字符串的长度

 select length('asdfasdfsad') from dual;

(5) replace 用于进行字符串的替换

select replace('asdfasdfsad','d','D')from dual;

(6) concat 用于进行字符串的拼接

select concat('asdfasdfsad','mmm')from dual;

(7) substr 用于进行字符串的截取

select substr('asdfasdfsad',1,3) from dual;

select substr('asdfasdfsad',4) from dual;

(8) instr 用于查找子串的位置

select instr('adsfsadfsadfsadf','df') from dual;

select instr('adsfsadfsadfsadfsadf','df',8,3) from dual;

(9) lpad 从左侧开始填充

select lpad('ads',10,'fd') from dual;

(10) rpad 从右侧开始填充

select rpad('ads',10,'fd') from dual;

(11) trim 默认去除空格，使用 both…from…去除字符

select trim(both 'a' from 'abfda') from dual;

select trim(leading 'a' from 'abfda') from dual;

select trim(trailing 'a' from 'abfda') from dual;

1.2 数字函数

(1) round 四舍五入

第二个参数可以控制四舍五入的位数, 正数表示小数点后, 负数表示小数点前

select round(156.9444449, -2) from dual;

select round(156.9444449, 2) from dual;

(2) trunc 截断

select trunc(123.456, 1) from dual;

(3) ceil 向上取整

select ceil(123.0000001) from dual;

(4) floor向下取整

select floor(123.9999999) from dual;

(5) mod取余

select mod(123.9999999,2) from dual;

1.3 日期函数

(1) sysdate 获取系统时间

select sysdate from dual;

sysdate+1 表示天数+1

select sysdate+1 from dual;

(2) months_between 计算两个日期间的月数

select months_between(sysdate, e.hire_date) from employees e

(3) add_months 给日期加减月数

select sysdate, add_months(sysdate, -5) from dual;

(4) last_day 计算给定日期所在月份的最后一天是哪个日期

select sysdate, last_day(sysdate) from dual;

(5) next_day 基于给定日期计算下个给定的星期几是什么日期

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

1.4 转换函数(隐式转换会导致索引失效)

(1) to_number 将字符串转换为数字

select to_number('￥123,123,123.00', 'L999,999,999.00')+1 from dual;

(2) to_date 将字符串转换为日期

select to_date('2022-12-12 23:20:20', 'YYYY-MM-DD HH24:MI:SS') from dual;

(3) to_char 将数字或日期转换为字符串

select to_char(1231231231, 'L999,999,999,999.99') from dual;

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

select TO_CHAR(sysdate,'YYYY-MM-DD "of" HH24:mi:SS AM') from dual;

select TO_CHAR(sysdate,'YYYY"年"MM"月"DD"日"  HH24/mi/SS') from dual;

select to_char('1111.221','000,999,999.999') from dual;

1.5 通用函数

(1) nvl 用来处理空值, 如果某个字段为空, 则使用对应的数据进行替换

select e.last_name, e.salary, e.commission_pct, e.salary+nvl(e.commission_pct, 0) total from employees e;

(2) nvl2 有三个参数, 如果第一个参数不为空, 则使用第二个参数, 如果为空, 则使用第三个参数

(3)decode 类似于java中switch…case…语法

select e.last_name, e.job_id ,e.salary,decode(job_id,'SA_REP',e.salary*2,'AD_VP',e.salary*3,e.salary) from employees e;

(4) case

select case e.job_id when 'AC_MGR'   then  '管理' when 'AD_VP' then  '监督' else '其他' end from employees e;

2.聚合函数

聚合函数忽略空值，需要用一个值替换空值，nvl 、nvl2、coalesce

(1) sum 求和

(2) avg 求平均值

佣金不为空的雇员的平均值

select avg(e.commission_pct) from employees e;

所有雇员的平均值

select avg(nvl(e.commission_pct,0)) from employees e;

(3) max 求最大值

(4) min 求最小值

(5) count 计数

select count(*) from employees e;

select count(e.commission_pct) from employees e;

select count(distinct e.commission_pct) from employees e;

二.数据字典表

USER_TABLES 涵盖用户下的所有表

select table_name  from USER_TABLES;

select * from USER_CATALOG;

三. sql执行顺序

1.from
2.where
3.group by
4.having
5.select
6.order by

先确定行，再确定列，最后确定输出顺序

四. oracle分页(基于伪列 rownum)

  在查询的结果集中，ROWNUM为结果集中每一行标识一个行号，第一行返回1，第二行返回2，以此类推。通过ROWNUM伪列可以限制查询结果集中返回的行数

select m.* from (select ROWNUM rn,e.* from EMPLOYEES e) m where m.rn between 10 and 20;

五. oracle删除重复记录（基于伪列rowid）

rowid（表数据行的物理地址，在插入数据时生成，且唯一）内含 数据库对象号、数据文件号、数据块号、行号信息

select s.*,s.rowid from STU1 s;

delete  from STU1  where sid in (select sid from STU1 group by  sid having (count(*)>1)) and rowid not in (select
   min (rowid)  from STU1 group by sid having (count(*)>1));

delete from STU1 where rowid in (select a.ROWID from STU1 a,STU1 b where a.sid=b.sid and a.ROWID>b.rowid);