Oracle相关知识_max会走索引吗-优快云博客

本文链接：https://blog.youkuaiyun.com/u010773247/article/details/125952843

Oracle相关知识

1.优化

1.select 尽量不要使用*,需要那些字段就写出来

2.优化器

Oracle优化器有CBO和RBO,CBO是按成本来优化sql,主要根据Oracle对表本身的一些统计诸如表大小,CPU消耗,IO消耗等来优化sql脚本.目前默认选择CBO.RBO是按规则来优化sql,Oracle在最开始的时候自身编写了一套优化脚本的规则,不管表多大,IO的效率情况,Oracle都会按自身规则重新编译sql进行执行.

3.索引使用及失效原因

索引使用:

索引大致分为两类，一是B-tree索引，一是位图索引(Bitmapt)，Oracle默认创建的就是B-tree索引，简单来说二者的区别就是B-tree索引适合建立在数据差异较大的字段，比如姓名，学号，位图索引适合建立在数据差异较小的字段，比如性别。但是在实际业务运用中也还需要根据具体的情况进行辨别是否使用索引，具体使用哪个索引，比如一个字段sex，他只有男和女，数据量相当，那创建索引实际上最多只能过滤掉一半的数据。比如一个字段is_true，他的结果只有是和否，但是否的数据量相对较少，那建立索引后对需要经常查否的数据来说就会很有效。

B-tree 特点：
适合与大量的增、删、改（OLTP）
不能用包含OR操作符的查询；
适合高基数的列（唯一值多）
典型的树状结构；
每个结点都是数据块；
大多都是物理上一层、两层或三层不定，逻辑上三层；
叶子块数据是排序的，从左向右递增；
在分支块和根块中放的是索引的范围；

Bitmapt 特点：
适合与决策支持系统；
做UPDATE代价非常高；
非常适合OR操作符的查询；
基数比较少的时候才能建位图索引；

不常见的还有复合索引、函数索引、哈希索引和分区索引，复合索引也就是基于多个列创建的索引，这种索引只有在第一个列被where子句使用到时，优化器才会使用到该索引。

注：索引是把双刃剑，虽然建立索引能有效地提高查询效率，但索引也需要占大量的表空间和物理空间，后期还需要经常维护，所以建立索引要慎重选择。

失效原因:

没有 WHERE 子句
众所周知，添加索引的字段必需要在where条件后适当使用才会生效，如果连查询条件都没有，那肯定不会用到索引的。
使用 IS NULL 和 IS NOT NULL
select … from emp where colnum is null; colnum列的索引会失效
WHERE 子句中使用函数
如果没有使用基于函数的索引，那么 where 子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。例如：
select * from staff where trunc(birthdate) = ‘01-MAY-82’;
但是把函数应用在条件上，索引是可以生效的，把上面的语句改成下面的语句，就可以通过索引进行查找。
select * from staff where birthdate < (to_date(‘01-MAY-82’) + 0.9999);
注意：对于 MIN, MAX 函数，Oracle 仍然使用索引。
使用 LIKE ‘%T’ 进行模糊查询
select * from student where name like ‘aaa%’ ; // ‘aaa%’ 会用到索引
select * from student where name like ‘%aaa’ ; //’%aaa’ 或者 ‘_aaa’ 不会使用索引
WHERE 子句中使用不等于操作
不等于操作包括：<>, !=, NOT colum >= ?, NOT colum <= ?
对于这个限制条件可以通过 OR 替代，例如： colum <> 0 ===> colum>0 OR colum<0
等于和范围索引不会被合并使用
SELECT emp_id, emp_m, salary_q … FROM emp WHERE job=‘manager’ AND deptno>10
job 和 deptno 都是非唯一索引，这种条件下 oracle 不会合并索引，它只会使用第一个索引。
比较不匹配数据类型
dept_id是一个varchar2型的字段，在这个字段上有索引，但是下面的语句会执行全表扫描。
select * from temp where dept_id = 100101;
这是因为 oracle 会自动把 where 子句转换成 to_number(dept_id)=900198，相当于使用函数，这样就限制了索引的使用。正确写法如下：
select * from temp where dept_id = ‘100101’;
还有一种可能就是条件如果查出来的数据量占整表的60%的话,那么也不会走索引,因为这种情况与其先从索引树上先查一遍再去表里找对应的记录,不如直接在全表里扫描.

4.hash连接

use_hash(table1,table2) 指定table1,table2连接以HASH 方式连接，采用这种方式，我们不能控制table1,table2哪个表为驱动表，优化器会根据cost自动选择驱动表.使用hint一定要将相关连接的表写完整,否则有可能不会强制hash连接.

 select /*+ use_hash(emp,dept) */ ename,dept.deptno from emp,dept where dept.deptno=emp.deptno;

如果我想要用emp作为驱动表，可以使用leading,ordered提示

SQL> select /*+ ordered use_hash(emp,dept)  */ ename,dept.deptno from emp,dept where dept.deptno=emp.deptno;
SQL> select /*+ leading(emp) use_hash(emp,dept) */ ename,dept.deptno from dept,emp where dept.deptno=emp.deptno;

5.共享SQL语句

为了不重复解析相同的SQL语句,在第一次解析之后, ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享. 因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及最好的执行路径. ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用.

可惜的是ORACLE只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询.

数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了.

当你向ORACLE 提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句.

这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须完全相同(包括空格,换行等).

这里需要注意的是大小写也是严格限制的,所以有时候经常被要求写sql的时候要全部大写或者全部小写.

6.分区表

目的：提高大表的查询效率
概念：将一个表划分为多个分区表，“分而治之”
优缺点
优点：
(1) ‘改善查询性能’: 分区对象的查询仅搜索自己关系的分区
(2) ‘增强可用性’: 如果某个分区出现故障，其它分区的数据仍然可用
(3) ‘维护方便’: 如果某个分区出现故障，仅修复该分区即可
(4) ‘均衡I/O’: 将不同的分区放置不同的磁盘，以均衡 I/O，改善整个系统性能

缺点：
(1) 已经存在的表无法直接转化为分区表 – 不过有很多间接方法，如：重定义表
适用情况
(1) 表的大小超过 2GB

传统分区表

范围分区,列表分区,hash分区,复合分区(range + list or hash)

create table pt_range_test1(
  pid   number(10),
  pname varchar2(30)
) partition by range(pid)(
  partition p1 values less than(1000) tablespace tetstbs1,
  partition p2 values less than(2000) tablespace tetstbs2,
  partition p3 values less than(maxvalue) tablespace tetstbs3
) enable row movement;

11g 新特性分区

引用分区 reference,间隔分区 interval,虚拟列分区 virtual,系统分区 system

https://blog.youkuaiyun.com/qq_34745941/article/details/109027781

7.分区索引

索引与表一样，也可以分区。索引分为两类：locally partition index（局部分区索引）、globally partition index（全局分区索引）。

普通表可以建全局分区索引,不能建局部分区索引

1.局部分区索引（locally partition index）

局部分区索引随表对索引完成相应的分区，即索引会使用与表相同的机制进行分区，每个表分区都有一个索引分区，并且只索引该表分区。

1.1 局部索引分类
▶ 局部前缀索引（local prefixed index）：以分区键作为索引定义的第一列

▶ 局部非前缀索引（local nonprefixed index）：分区键没有作为索引定义的第一列

注意：判断局部索引是前缀还是非前缀的只需要看分区键是否作为索引定义的第一列

示例语句

–范围分区

–创建表

create table student_range_part(

stu_id varchar2(4),

stu_name varchar2(100), --姓名

sex varchar2(1), --性别 1 男 2 女 0 未知

credit integer default 0

)

partition by range (credit)

(

partition student_part1 values less than (60) tablespace kdhist_data,

partition student_part2 values less than (70) tablespace kdhist_data,

partition student_part3 values less than (80) tablespace kdhist_data,

partition student_part4 values less than (maxvalue) tablespace kdhist_data

);

–创建局部前缀索引；分区键（credit）作为索引定义的第一列

create index local_prefixed_index on student_range_part (credit, stu_id) local;

–创建局部非前缀索引；分区键未作为索引定义的第一列

create index local_nonprefixed_index on student_range_part (stu_id, credit) local;

1.2 局部索引示例
–①

select * from student_range_part where credit = &credit and stu_id = &stu_id;

–②

select * from student_range_part where stu_id = &stu_id;

对于以上两个查询来说，如果查询第一步是走索引的话，则：

局部前缀索引 local_prefixed_index 只对 ① 有用；

局部非前缀索引 local_nonprefixed_index 则对 ① 和 ② 均有用；

如果你有多个类似 ① 和 ② 的查询的话，则可以考虑建立局部非前缀索引；如果平常多使用查询 ① 的话，则可以考虑建立局部前缀索引；

小结：

$\textcolor{red}{分区表一般使用局部索引。重点在于如何选择分区表和局部索引类型。}$

2.全局分区索引（globally partition index）

索引按范围（Range）或散列（Hash，Oracle 10g中引入）进行分区，一个分区索引（全局）可能指向任何（或全部的）表分区。

对于全局分区索引来说，索引的实际分区数可能不同于表的分区数量；

全局索引的分区机制有别于底层表，例如表可以按 credit 列划分为10个分区，表上的一个全局索引可以按stu_id 列划分为5个分区。

与局部索引不同，全局索引只有一类，即全局前缀索引（prefixed global index），索引分区键必须作为索引定义的第一列，否则执行会报错。

–范围分区

–创建表

create table student_range_part(

stu_id varchar2(4),

stu_name varchar2(100), --姓名

sex varchar2(1), --性别 1 男 2 女 0 未知

credit integer default 0

)

partition by range (credit)

(

partition student_part1 values less than (60) tablespace kdhist_data,

partition student_part2 values less than (70) tablespace kdhist_data,

partition student_part3 values less than (80) tablespace kdhist_data,

partition student_part4 values less than (maxvalue) tablespace kdhist_data

);

–创建按age进行范围分区的全局分区索引

create index global_index on student_range_part(credit) global

partition by range (credit)

(

partition index_part1 values less than (60),

partition index_part2 values less than (80),

partition index_partmax values less than (maxvalue)

);

注意：

全局索引要求最高分区（即最后一个分区）必须有一个值为 maxvalue 的最大上限值，这样可以确保底层表的所有行都能放在这个索引中；

一般情况下，大多数分区操作（如删除一个旧分区）都会使全局索引无效，除非重建全局索引，否则无法使用

全局索引示例
全局索引一般用于数据仓库，许多数据仓库系统都存在大量的数据出入，如典型的数据“滑入滑出”（即删除表中最旧的分区，并为新加载的数据增加一个新分区）。

① 去除老数据：最旧的分区要么被删除，要么创建一个新表，将最旧的分区数据存入，从而对旧数据进行归档；

② 加载新数据并建立索引：将新数据加载到一个“工作”表中，建立索引并进行验证；

③ 关联新数据：一旦加载并处理了新数据，数据所在的表会与分区表中的一个空分区交换，将表中的这些新加载的数据变成分区表中的一个分区（分区表会变得更大）

对于全局索引来说，这样增删分区的过程，意味着该全局索引的失效，需重建全局索引；

在 Oracle 9i 之后，可以在分区操作期间使用 UPDATE GLOBAL INEXES 子句来维护全局索引，这意味着当在分区上执行删除、分解或其他操作时，Oracle会对原先建立的全局索引执行必要的修改，以保证它是最新的。

–删除student_range_part表中的index_part1分区，同时同步维护全局索引

alter table student_range_part drop partition index_part1 update global indexes;

使用 UPDATE GLOBAL INEXES子句后，在删除一个分区时，必须删除可能指向该分区的所有全局索引条目；

执行表与分区的交换时，必须删除指向原数据的所有全局索引条目，再插入指向刚加载的数据的新条目；

如此一来 ALTER 命令执行的工作量会大幅增加；

小结：

$\textcolor{red}{分区操作执行完成后重建全局索引方式占用的数据库资源更少，因此完成的相对“更快”，但是会带来显著的“停机时间”（重建索引时会有一个可观的不可用窗口）；}$

$\textcolor{red}{在分区操作执行的同时执行 UPDATE GLOBAL INEXES 子句方式会占用更多的资源，且可能需要花费更长的时间才能完成操作，但好处是不会带来任何的停机时间。}$

$\textcolor{red}{即使是数据仓库，除非特殊需求，否则这个创建局部索引即可。}$

8.创建临时表,通过空间来换取时间

9.尽量使用GROUP BY 去重而不是DISTINCT

10.MERGE INTO用法

针对数据量大的表更新时使用MERGE INTO用法

语法：

MERGE INTO [your table-name] [rename your table here]

USING ( [write your query here] )[rename your query-sql and using just like a table]

ON ([conditional expression here] AND [...]...)

WHEN MATHED THEN [here you can execute some update sql or something else ]

WHEN NOT MATHED THEN [execute something else here ! ]

merge into products p using newproducts np on (p.product_id = np.product_id) 
when matched then 
update set p.product_name = np.product_name 
when not matched then 
insert values(np.product_id, np.product_name, np.category)

2.常用函数

一、数值函数

1、mod(n1,n2):n1除以n2的余数。

如果n2为0，则返回n1。

select` `mod(23,8),mod(24,8) ``from` `dual;``--返回：7,0

2、power(n1,n2):返回数字n1的n2次幂;
exp(y)：返回e的y次幂。（e为数学常量）;
log(x,y)：返回以x为底的y的对数；
ln(y)：返回e为底的自然对数。

select` `power(2.5,2),power(1.5,0),power(20,-1) ``from` `dual;

3、sqrt(n):平方根。

select` `sqrt(64),sqrt(10) ``from` `dual;``--返回：8 , 3.16227766

4、ceil(n):返回大于等于n的最小整数。;

floor(n):返回小于等于n的最大整数。

select` `ceil(3.1),ceil(2.8+1.3),ceil(0) ``from` `dual;``--返回4，5，0

5、sign(x)：返回x的正负值

若为正值返回1，负值返回-1，0返回0。

select` `sign(100),sign(-100),sign(0) ``from` `dual;

6、trunc(n[,len]):n截取到小数点len位。

len默认为0。len>0，截取到小数点右len位。len<0，截取到小数点左len位。

select` `trunc(5555.66666,2.1),trunc(5555.66666,-2.6),trunc(5555.033333) ``from` `dual;``--返回：5555.66 5500  5555

7、round(n[,len]):n四舍五入到小数点len位，规则同trunc。

select` `round(5555.6666,2.1),round(5555.6666,-2.6),round(5555.6666) ``from` `dual;``--返回：  5555.67 ,5600 ,5556

8、sys.dbms.random.value()：产生0-1之间的随机数。

DBMS_RANDOM.VALUE()是随机产生( 0,1 )之间的数。
DBMS_RANDOM.VALUE(n1,n2)：产生n1-n2之间的随机数。

trunc(dbms_random.value(10,100))  //80：生成10-100之间的随机数。

二、字符函数

1：lower(c1):返回字符串,并将所有的字符小写

SELECT` `LOWER``(``'AbcDedf Gbad'``) ``FROM` `DUAL;

2：upper(c1):返回字符串,并将所有的字符大写

SELECT` `UPPER``(``'abcdEf'``) ``FROM` `DUAL;

3: initcap(c1):返回字符串并将字符串的第一个字母变为大写

全部单词的首字母大写

SELECT` `INITCAP(``'your didn'``'t try your best'``) ``FROM` `DUAL;

4: initcap(c1,n[,c2]):在列的左边填充字符

C1 字符串
n 追加后字符总长度
c2 追加字符串,默认为空格

SELECT` `LPAD(``'WELCOME'``, 20, ``'HELLO'``) ``FROM` `DUAL;

5: rpad(c1,n[,c2]):在列的右边填充字符

注意长度值并不是粘贴字符的长度，而是整个字符串的长度，如果长度小于原始字符串

--SELECT RPAD('HELLO', 4, '*') FROM DUAL; 的值为HELL``SELECT` `RPAD(``'HELLO'``, 10, ``'*'``) ``FROM` `DUAL;``SELECT` `RPAD(``'HELLO'``, 10, ``'E'``) ``FROM` `DUAL;

6: ltrim(X,[TRIM_STRING]):删除左边出现的字符串。

默认为空字符串

SELECT` `LTRIM(``' hello world!'``) ``FROM` `DUAL;``SELECT` `LTRIM(``'hello, world'``, ``'hello'``) ``FROM` `DUAL;

7: rtrim(X, [TRIM_STRING]):删除右边出现的字符串

TRIM_STRING,默认为空字符串。

SELECT` `RTRIM(``'hello world!  '``) ``FROM` `DUAL;

8: trim(‘s’ from ‘string’):删除两边出现的字符串

LEADING 剪掉前面的字符
TRAILING 剪掉后面的字符
如果不指定,默认为空格符

SELECT` `TRIM(``'Y'` `FROM` `'YOU'``) ``FROM` `DUAL;

9: instr(C1,C2,I,J):在一个字符串中搜索指定的字符,返回发现指定的字符的位置;

C1 被搜索的字符串
C2 希望搜索的字符串
I 搜索的开始位置,默认为1
J 出现的位置,默认为1

SELECT` `INSTR(``'HELLO WORLD! WELCOME'``, ``'WORLD'``, 1) ``FROM` `DUAL;

10：substr(string,start,count):取子字符串,从start开始,取count个

SELECT` `SUBSTR(``'you are right!, come on'``, 3, 30) ``FROM` `DUAL;

11：replace(‘string’,‘s1’,‘s2’):替换

string 希望被替换的字符或变量
s1 被替换的字符串
s2 要替换的字符串

SELECT` `REPLACE``(``'HE LOVE YOU'``, ``'HE'` `,``'I'``) ``FROM` `DUAL;

12：translate(c1,c2,c3)：将指定字符替换为新字符

c1 希望被替换的字符或变量
c2 查询原始的字符集
c3：替换新的字符集，将c2对应顺序字符，替换为c3对应顺序字符

select` `TRANSLATE(``'he love you'``,``'he'``,``'i'``),``TRANSLATE(``'重庆的人'``,``'重庆的'``,``'上海男'``),``TRANSLATE(``'重庆的人'``,``'重庆的重庆'``,``'北京男士们'``),``TRANSLATE(``'重庆的人'``,``'重庆的重庆'``,``'1北京男士们'``),``TRANSLATE(``'重庆的人'``,``'1重庆的重庆'``,``'北京男士们'``) ``from` `dual;``--i love you,上海男人,北京男人,1北京人,京男士人

13: length(c1)：返回字符串的长度;

返回表某条数据某个列实际长度，如果该表没有数据，返回0

SELECT` `LENGTH(TYPE_NAME) ``FROM` `USER_TYPES

14：ascii（x1)：返回字符串的ASCII值

SELECT` `ASCII(``'A'``) ``FROM` `DUAL;``SELECT` `ASCII(``'a'``) ``FROM` `DUAL;

15: chr(n1)：返回整数所对应的ASCII字符

SELECT` `CHR(``'65'``) ``FROM` `DUAL;``SELECT` `CHR(400) ``FROM` `DUAL; ``--如果超出ACII值，则返回空

16: concat(c1,c2)：连接字符串A和字符串B

SELECT` `CONCAT(``'您好'``, ``'欢迎来到ORACLE世界'``) ``AS` `TEXT ``FROM` `DUAL;``--如果要连接表里面的两个字段可以用||``SELECT` `TYPECODE || ``'____'` `|| TYPE_NAME ``AS` `"TYPE"` `FROM` `USER_TYPES;

三、日期时间函数

1：sysdate、current_date：系统的当前日期

（1）日期加上范围日期，得到新日期

data+n,加减n天。（n为负数，表示减去）
date+n/24:加减n小时
date+n/24/60:加减n分钟
date+n/24/60/3600:加减n秒

（2）date1-date2:两日期相差的天数：

(date1-date2)243600:两日期相差的秒数

SELECT` `SYSDATE ``FROM` `DUAL;

2：add_months(date,n1)：增加或减去月份

SELECT` `TO_CHAR(ADD_MONTHS(TO_DATE(``'20080818'``,``'YYYYMMDD'``),2), ``'YYYY-MM-DD'``) ``FROM` `DUAL;``SELECT` `TO_CHAR(ADD_MONTHS(SYSDATE, -1), ``'YYYY-MM-DD'``) ``FROM` `DUAL

3: months_between(date2,date1)：给出date2-date1的月份

SELECT` `MONTHS_BETWEEN(TO_DATE(``'2011-05-03'``, ``'YYYY-MM-DD'``), TO_DATE(``'2011-01-23'``, ``'YYYY-MM-DD'``)) ``FROM` `DUAL;``SELECT` `MONTHS_BETWEEN(``'19-12月-1999'``,``'19-3月-1999'``) mon_between ``FROM` `DUAL;``--SELECT MONTHS_BETWEEN('2011-1月-23', '2011-9月-1') FROM DUAL; 文字与格式字符串不匹配

4: last_day(date)：返回日期的最后一天

SELECT` `LAST_DAY(SYSDATE) ``FROM` `DUAL;``SELECT` `LAST_DAY(ADD_MONTHS(SYSDATE, -2)) ``FROM` `DUAL;

5：next_day(date[,fmt]）:返回日期d1在下周，星期几(参数c1)的日期

星期日 = 1 星期一 = 2 星期二 = 3 星期三 = 4 星期四 = 5 星期五 = 6 星期六 = 7

SELECT` `NEXT_DAY(SYSDATE, 2) ``FROM` `DUAL;``--下周星期一,

6、round(date[,fmt]）:日期时间四舍五入结果。

fmt默认是day.

7: trunc(date[,fmt]）:TRUNC函数为指定元素而截去的日期值。

TRUNC(TO_DATE(``'24-Nov-1999 08:00 pm'``),``'dd-mon-yyyy hh:mi am'``)　=``'24-Nov-1999 12:00:00 am'``TRUNC(TO_DATE(``'24-Nov-1999 08:37 pm'``,``'dd-mon-yyyy hh:mi am'``),``'hh'``) =``'24-Nov-1999 08:00:00 am'

8：extract(c1 from date) :找出日期或间隔值的字段值

SELECT` `EXTRACT(``MONTH` `FROM` `SYSDATE) ``"MONTH"` `FROM` `DUAL;``SELECT` `EXTRACT(``DAY` `FROM` `SYSDATE) ``AS` `"DAY"` `FROM` `DUAL;``SELECT` `EXTRACT(``YEAR` `FROM` `SYSDATE) ``AS` `"YEAR"` `FROM` `DUAL;

9：new_time(date,‘this’,‘that’):给出在this时区=other时区的日期和时间

SELECT` `TO_CHAR(SYSDATE, ``'YYYY.MM.DD HH24:MI:SS'``) BeiJing_Time,TO_CHAR(NEW_TIME(SYSDATE, ``'PDT'``, ``'GMT'``), ``'YYYY.MM.DD HH24:MI:SS'``) LOS_ANGELS ``FROM` `DUAL;

简写时区

AST OR ADT 大西洋标准时间
HST OR HDT 阿拉斯加—夏威夷时间
BST OR BDT 英国夏令时
MST OR MDT 美国山区时间
CST OR CDT 美国中央时区
NST 新大陆标准时间
EST OR EDT 美国东部时间
PST OR PDT 太平洋标准时间
GMT 格伦威治标准时间
YST OR YDT Yukon标准时间

10: dbtimezone() :返回时区

SELECT` `DBTIMEZONE ``FROM` `DUAL;

11: sessiontimezone:返回会话时区

其中DBTIMEZONE是数据库的，session是针对当前会话的，因为时区在会话级可以改变

SELECT` `SESSIONTIMEZONE ``FROM` `DUAL; ``ALTER` `SESSION ``SET` `TIME_ZONE = ``'8:00'``;``SELECT` `SESSIONTIMEZONE ``FROM` `DUAL;

12、常用时间查询：

---- 上月最后一天``SELECT` `TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -1)), ``'YYYY/MM/DD'``) ``FROM` `DUAL;``----: 上各月的今天``SELECT` `TO_CHAR(ADD_MONTHS(SYSDATE, -1), ``'YYYY-MM-DD'``) ``FROM` `DUAL;``---- 上个月第一天``SELECT` `TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE)+1,-2),``'YYYY-MM-DD'``) FirstDay ``FROM` `DUAL;``--- 要找到某月中所有周五的具体日期 ``SELECT` `TO_CHAR(T.D, ``'YY-MM-DD'``)``FROM` `(``SELECT` `TRUNC(SYSDATE, ``'MM'``) + ROWNUM -1 ``AS` `D ``FROM` `DBA_OBJECTS ``WHERE` `ROWNUM < 32) T``WHERE` `TO_CHAR(T.D, ``'MM'``) = TO_CHAR(SYSDATE, ``'MM'``) ``AND` `TRIM(TO_CHAR(T.D, ``'DAY'``)) = ``'星期五'

四、转换函数

1： to_char(date,‘format’) ：把对应的数据转换为字符串类型

TO_CHAR的fmt:格式字符串，不分大小写。

select` `to_char(sysdate,``'yyyy-mm-dd hh24:mi;ss'``) ``from` `dual

Y或YY或YYY 年的最后一位，两位或三位
SYEAR或YEAR: SYEAR使公元前的年份前加一负号 --TWENTY ELEVEN
Q：季度，1～3月为第一季度 – 2表示第二季度
MM：月份数 --04表示4月
RM：月份的罗马表示 --IV表示4月
MON：月份 --4月
Month：用9个字符长度表示的月份名 – 4月
WW：当年第几周 – 24表示2002年6月13日为第24周
W：本月第几周 – 2011年04月26日为第4周
DDD：当年第几天. 1月1日为001，2月1日为032
DD：当月第几天
D：周内第几天
DY：周内第几天缩写
HH或HH12： 12进制小时数
HH24： 24小时制
MI：分钟数(0～59) ：提示注意不要将MM格式用于分钟(分钟应该使用MI)。MM是用于月份的格式，将它用于分钟也能工作，但结果是错误的。
SS：秒数(0～59)

SELECT` `TO_CHAR(SYSDATE, ``'YYYY-MM-DD HH24:MI:SS'``) ``FROM` `DUAL;

2： to_char(n,‘format’)：把对应的数字为字符串类型

FORMAT格式符：

9：带有指定位数的值
0：前导零的值
. （句点）：小数点
, （逗号）：分组（千）分隔符
PR：尖括号内负值
S：带负号的负值（使用本地化）
L：货币符号（使用本地化）
D：小数点（使用本地化）
G：分组分隔符（使用本地化）
MI：在指明的位置的负号（如果数字 < 0）
PL：在指明的位置的正号（如果数字 > 0）
SG：在指明的位置的正/负号
RN：罗马数字（输入在 1 和 3999 之间）
TH or th：转换成序数

SELECT` `TO_CHAR(122323.45, ``'$99999999.99'``) ``FROM` `DUAL;

3： to_date(string,‘format’)：将字符串转化为日期

主要用于比较和修改日期。

SELECT` `TO_DATE(``'2011/03/24'``, ``'YYYY-MM-DD'``) ``FROM` `DUAL;

4： to_number：将给出的字符转换为数字

SELECT` `TO_NUMBER(``'￥2008.00'``，``'L9999D99'``) ``AS` `Year` `FROM` `DUAL;

五、辅助函数

1、decode(条件,值1,翻译值1,值2,翻译值2,…值n,翻译值n,缺省值):根据条件返回相应值

值1……n 不能为条件表达式,这种情况只能用case when then end解决。

select` `decode(xqn,1,``'星期一'``,2,``'星期二'``,3,``'星期三'``,``'星期三以后'``) 星期``FROM` `xqb

2、greatest(exp1,exp2,exp3,……,expn):返回表达式列表中值最大的一个。 ; least(exp1,exp2,exp3,……,expn):返回表达式列表中值最小的一个。

如果表达式类型不同，会隐含转换为第一个表达式类型。

SELECT` `greatest(10,32,``'123'``,``'2006'``) ``FROM` `dual;

3、nullif (expr1, expr2)：expr1和expr2相等返回NULL，不相等返回expr1。

SELECT` `NULLIF``( ``'a'``, ``'b'` `) ;``--返回值 a``SELECT` `NULLIF``( ``'a'``, ``'a'``);``--返回 NULL

实际应用：

--添加函数查询结果，要求（将日期类型默认'0001/1/1',改成null,不相等，返回本身日期）--``select` `NULLIF``(RECEIVEDATE,TO_DATE(``'0001/1/1'``,``'yyyy-mm-dd hh24:mi:ss'``)) 收货日期,``NAME` `单据名称 ``from` `tab

4、nvl (expr1, expr2):若expr1为NULL，返回expr2；expr1不为NULL，返回expr1。

select` `NVL(``null``,``'未知'``) SexType ``from` `dual;``--expr1为空，返回expr2，结果：'未知'``select` `NVL(``'1'``,``'2'``) SexType ``from` `dual;``--expr1不为空，返回expr1， 结果：1

实际应用：

--加函数时情况，要求（field 为null，返回0 ；field 不为null,返回本身``select` `NVL(UseFlag,``'0'``) 使用标志,``NAME` `单据名称 ``from` `tab

5、nvl2(expr1, expr2, expr3) :expr1不为NULL，返回expr2；expr2为NULL，返回expr3。

expr2和expr3类型不同的话，expr3会转换为expr2的类型

select` `NVL2(0,1,2) ``from` `dual;``--不为null时，返回expr2 ，结果：1``select` `NVL2(``null``,1,2) ``from` `dual;``--为null时，返回expr3 ，结果：2

实际应用：

--应用到实际查询中，要求（field 为null,返回0; field 不为null,返回本身）`` ``select` `NVL2(UseFlag,UseFlag,``'0'``) 使用标志,A.``NAME` `单据名称 ``from` `tab

6、coalesce(c1, c2, …,cn)：返回列表中第一个非空的表达式，如果所有表达式都为空值则返回1个空值。

select` `COALESCE``(``null``,3*5,44) hz ``from` `dual; ``--返回15

7、sys_context(‘USERENV’,c2)：返回系统’USERENV’变量中c2对应的的值。

SYS_CONTEXT(``'USERENV'``,``'LANGUAGE'``) language,

8、sys_connect_by_path(column_name,‘分隔符’)：把一个父节点下的所有子节点通过某个字符进行区分，然后连接在一个列中显示

详见 Oracle递归查询connect by

第一个参数是形成树形式的字段，第二个参数是父级和其子级分隔显示用的分隔符
伪列CONNECT_BY_ROOT,CONNECT_BY_LEAF,CONNECT_BY_ISCYCLE

结构化查询：START WITH …CONNNECT BY PRIOR基本语法是：

SELECT` `...``FROM``WHERE` `（过滤返回记录，仅过滤被限定节点，其根节点和子节点均不受影响）``START ``WITH` `(根节点，可以指定多个节点)``CONNECT` `BY` `PRIOR``= （连接条件，``PRIOR``置于等号前，则从根节点到叶节点开始检索；置于等号后，则从叶节点到根节点开始检索）

该查询访问路径如下：从根节点开始，向下扫描子节点，该子节点已被访问则转向其最左侧未被访问的子节点，否则判断该节点是否为根节点，是则访问完毕，否则返回父节点重新执行判断。

SELECT` `ename  ``FROM` `scott.emp  START ``WITH` `ename = ``'KING'`  `CONNECT` `BY` `PRIOR` `empno = mgr;

–得到结果为：

KING
JONES
SCOTT
ADAMS
FORD
SMITH
BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES

SELECT` `SYS_CONNECT_BY_PATH(ename, </``'>'``) ``"Path"`  `FROM` `scott.emp  START ``WITH` `ename = ``'KING'`  `CONNECT` `BY` `PRIOR` `empno = mgr;

–得到结果为：

KING
KING>JONES
KING>JONES>SCOTT
KING>JONES>SCOTT>ADAMS
KING>JONES>FORD
KING>JONES>FORD>SMITH
KING>BLAKE
KING>BLAKE>ALLEN
KING>BLAKE>WARD
KING>BLAKE>MARTIN
KING>BLAKE>TURNER
KING>BLAKE>JAMES
KING>CLARK
KING>CLARK>MILLER

六、聚合函数

AVG(DISTINCT|ALL)：平均值，DISTINCT表示对不同的值求平均值,重复值的列的只取一次。

SELECT` `AVG``(``DISTINCT` `SAL) ``FROM` `SCOTT.EMP;

MAX(DISTINCT|ALL)：最大值
MIN(DISTINCT|ALL)：最小值
SUM(DISTINCT|ALL)：求和
COUNT(DISTINCT|ALL)：求记录数
wmsys.wm_concat(DISTINCT|ALL)：合并列。
将一列的多行记录合并到一列，用逗号隔开。例如表的有两个字段，要按airport_id合并成两行可用sql语句

select` `airport_id,  wmsys.wm_concat(``distinct` `account) ``from` `AIRPORT_MODIFY ``group` `by` `airport_id

3.分析函数

–ADD BY WANGYAN 20190702
–这个函数好用，将每个分组中的值列出来显示在一条数据里，而且可以自定义分隔符
–登录oracle自建的测试数据库，使用hr用户

SELECT T.JOB_ID,LISTAGG(T.LAST_NAME,'#@#') WITHIN GROUP (ORDER BY T.JOB_ID)FROM EMPLOYEES t GROUP BY T.JOB_ID;

--by wangyan  20190529
select * from dba_tables t where t.table_name = 'JOB_HISTORY';
select * from hr.job_history;

select * from dba_tables t where t.owner = 'HR';
--用户 sh
SELECT * FROM SALES;
SELECT * FROM TIMES;
SELECT * FROM PRODUCTS;

– AVG 按窗口函数中的范围取当前行所在组的平均数
– 默认情况下,这个分析函数如果有ORDER BY 排序，那么处理到哪一行时，按排序情况下这一行和在这行之前的数据作为数据窗口进行求平均数
– 可以使用ROWS BETWEEN设置数据窗口范围
select T.EMPLOYEE_ID,
T.FIRST_NAME,
T.LAST_NAME,
T.MANAGER_ID,
T.HIRE_DATE,
T.SALARY,
AVG(T.SALARY) OVER(PARTITION BY T.MANAGER_ID ORDER BY T.HIRE_DATE) AS C_MAVG
from HR.EMPLOYEES T;

select T.EMPLOYEE_ID,
T.FIRST_NAME,
T.LAST_NAME,
T.MANAGER_ID,
T.HIRE_DATE,
T.SALARY,
AVG(T.SALARY) OVER(PARTITION BY T.MANAGER_ID ORDER BY T.HIRE_DATE ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS C_MAVG
from HR.EMPLOYEES T;
–WHERE T.FIRST_NAME = ‘Lex’ AND T.LAST_NAME = ‘De Haan’;
–按照分组将正在处理的这行数据同一组里的所有行都加入数据窗口进行求平均值
–ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 代表数据窗口从第一行到最后一行
select T.EMPLOYEE_ID,
T.FIRST_NAME,
T.LAST_NAME,
T.MANAGER_ID,
T.HIRE_DATE,
T.SALARY,
AVG(T.SALARY) OVER(PARTITION BY T.MANAGER_ID ORDER BY T.HIRE_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS C_MAVG
from HR.EMPLOYEES T;

SELECT COUNT(*) FROM HR.EMPLOYEES T;

SELECT * FROM HR.EMPLOYEES T WHERE T.FIRST_NAME = ‘Michael’ AND T.LAST_NAME = ‘Hartstein’;
SELECT * FROM HR.EMPLOYEES T WHERE T.MANAGER_ID = ‘100’ ORDER BY T.HIRE_DATE;
–#############################################################################################################
–用户 HR
–COUNT() OVER()在窗口数据范围内统计数据条目数
SELECT T.EMPLOYEE_ID,
T.FIRST_NAME,
T.LAST_NAME,
COUNT() OVER() CNT1, --里面什么都不加相当于统计整张表的条目数
COUNT() OVER(ORDER BY T.SALARY) CNT2, --只统计按排序比这行靠前的行，参与排序的字段值相同的行也会统计进去
COUNT() OVER(ORDER BY T.SALARY RANGE BETWEEN 100 PRECEDING AND 150 FOLLOWING) CNT3–通过排序找到该行
–排序的字段值向前浮动 50和向后浮动150范围内的数据进行统计,例如Hazel Philtanker这一条（SALARY=2200.00）找到范围在
–2200-50 <=SALARY<= 2200+150内的行数进行统计。这种排序加范围的SALARY正好等于2200的会统计进去的
– ,COUNT(*) OVER(ORDER BY T.SALARY DESC) CNT4 – 按倒叙排序，最先出现的是SALARY值比较大的记录，
–所以Hazel Philtanker这条统计的是106，包括SALARY相同的那一条
FROM EMPLOYEES T;

SELECT COUNT(*)
FROM EMPLOYEES T;–107

SELECT * FROM EMPLOYEES T ORDER BY T.SALARY;
SELECT * FROM EMPLOYEES T ORDER BY T.SALARY DESC;

SELECT T.EMPLOYEE_ID,
T.FIRST_NAME,
T.LAST_NAME,
COUNT(*) OVER(ORDER BY T.SALARY RANGE BETWEEN 100 PRECEDING AND 200 FOLLOWING) CNT
FROM EMPLOYEES T;–2200-100 <=SALARY<= 2200+200 只要达到这个条件的数据就统计进来

SELECT T.EMPLOYEE_ID,
T.FIRST_NAME,
T.LAST_NAME,
COUNT(*) OVER(ORDER BY T.SALARY DESC RANGE BETWEEN 100 PRECEDING AND 200 FOLLOWING ) CNT
FROM EMPLOYEES T; --倒序排序 DESC 写在 RANGE、ROWS前面
–#############################################################################################################
–计算一行在组中的相对位置

SELECT * FROM EMPLOYEES T WHERE T.JOB_ID LIKE ‘%PU%’;
–按工种分组将工薪进行排序，得到的依次累计出现分布百分比
SELECT T.EMPLOYEE_ID,
T.LAST_NAME,
T.JOB_ID,
T.SALARY,
CUME_DIST() OVER(PARTITION BY T.JOB_ID ORDER BY T.SALARY) CUME_DIST1
FROM EMPLOYEES T
WHERE T.JOB_ID LIKE ‘%PU%’;
–#############################################################################################################
–DENSE_RANK
–给在同一个组中的进行过排序的记录进行编号，如果被排序的字段值是一样的，那么它们的编号也是一样的，排序的编号是连续没有间隔的
–对于被排序的字段有null值时，null之间被认为是相等的（赋值的序号一样），并且遵循ORDER BY对null的排序方式（null默认为最大值）
SELECT * FROM EMPLOYEES T;
SELECT * FROM DEPARTMENTS T;

SELECT B.DEPARTMENT_ID,
A.EMPLOYEE_ID,
A.LAST_NAME,
A.SALARY,
DENSE_RANK() OVER(PARTITION BY B.DEPARTMENT_ID ORDER BY A.SALARY)
FROM EMPLOYEES A, DEPARTMENTS B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID;
–试试有null的情况下
CREATE TABLE TEST_DENSE_RANK_NULL
(
ID INTEGER,
NAME VARCHAR2(100),
DESCRI VARCHAR2(50)
);

SELECT T.*,T.ROWID FROM TEST_DENSE_RANK_NULL T ;

SELECT T.ID,
T.NAME,
T.DESCRI,
DENSE_RANK() OVER(PARTITION BY T.ID ORDER BY T.NAME) DR
FROM TEST_DENSE_RANK_NULL T;
SELECT T.ID,
T.NAME,
T.DESCRI,
DENSE_RANK() OVER(PARTITION BY T.ID ORDER BY T.NAME DESC) DR
FROM TEST_DENSE_RANK_NULL T;
–#############################################################################################################
–FIRST 和 LAST 使用
SELECT T.EMPLOYEE_ID,
T.LAST_NAME,
T.DEPARTMENT_ID,
T.SALARY,
T.COMMISSION_PCT

FROM EMPLOYEES T
WHERE T.DEPARTMENT_ID IN (20,80)
ORDER BY t.commission_pct,t.salary;
–MINFIRST 以DEPARTMENT_ID为分组，在组内对COMMISSION_PCT进行排序，通过FIRST找到序号最小的所有行，在这些行里面
–找到SALARY最小的值
–MAXLAST 以DEPARTMENT_ID为分组，在组内对COMMISSION_PCT进行排序，通过LAST找到序号最大的所有行，在这些行里面
–找到SALARY最大的值
SELECT T.EMPLOYEE_ID,
T.LAST_NAME,
T.DEPARTMENT_ID,
T.SALARY,
T.COMMISSION_PCT,
MIN(T.SALARY) KEEP(DENSE_RANK FIRST ORDER BY T.COMMISSION_PCT) OVER(PARTITION BY T.DEPARTMENT_ID) MINFIRST,
MAX(T.SALARY) KEEP(DENSE_RANK LAST ORDER BY T.COMMISSION_PCT) OVER(PARTITION BY T.DEPARTMENT_ID) MAXLAST
FROM EMPLOYEES T
WHERE T.DEPARTMENT_ID IN (20,80);

SELECT T.EMPLOYEE_ID,
T.LAST_NAME,
T.DEPARTMENT_ID,
T.SALARY,
T.COMMISSION_PCT,
MIN(T.SALARY) KEEP(DENSE_RANK FIRST ORDER BY T.COMMISSION_PCT DESC) OVER(PARTITION BY T.DEPARTMENT_ID) MINFIRST,
MAX(T.SALARY) KEEP(DENSE_RANK LAST ORDER BY T.COMMISSION_PCT DESC) OVER(PARTITION BY T.DEPARTMENT_ID) MAXLAST
FROM EMPLOYEES T
WHERE T.DEPARTMENT_ID IN (80);

–#############################################################################################################
–FIRST_VALUE
–返回组中数据窗口的第一个值
SELECT
T.EMPLOYEE_ID,
T.LAST_NAME,
T.SALARY,
T.DEPARTMENT_ID,
FIRST_VALUE(T.LAST_NAME) OVER(PARTITION BY T.DEPARTMENT_ID ORDER BY T.SALARY) MIN_SALARY
FROM EMPLOYEES T
WHERE T.DEPARTMENT_ID IN (20，30);

SELECT
T.EMPLOYEE_ID,
T.LAST_NAME,
T.SALARY,
T.DEPARTMENT_ID,
FIRST_VALUE(T.LAST_NAME) OVER(PARTITION BY T.DEPARTMENT_ID ORDER BY T.SALARY DESC) MIN_SALARY
FROM EMPLOYEES T
WHERE T.DEPARTMENT_ID IN (20，30);
–#############################################################################################################
–LAG
–可以通过分组和排序找到排在当前行之前的行的数据
–LEAD
–可以通过分组和排序找到排在当前行之后的行的数据
–LAG(EXP_STR,OFFSET,DEFVAL) OVER()
–LEAD(EXP_STR,OFFSET,DEFVAL) OVER()
–EXP_STR 要取的列
–OFFSET 取偏移后的第几行数据,必须是正整数
–DEFVAL 没有符合条件的默认值
SELECT
T.EMPLOYEE_ID,
T.LAST_NAME,
T.SALARY,
T.HIRE_DATE,
LAG(T.SALARY,1,99999999) OVER(ORDER BY T.HIRE_DATE)
FROM EMPLOYEES T;

SELECT
T.EMPLOYEE_ID,
T.LAST_NAME,
T.SALARY,
T.HIRE_DATE,
T.DEPARTMENT_ID,
LAG(T.SALARY,1,99999999) OVER(PARTITION BY T.DEPARTMENT_ID ORDER BY T.HIRE_DATE)
FROM EMPLOYEES T;

SELECT
T.EMPLOYEE_ID,
T.LAST_NAME,
T.SALARY,
T.HIRE_DATE,
LEAD(T.SALARY,1,99999999) OVER(ORDER BY T.HIRE_DATE)
FROM EMPLOYEES T;

SELECT * FROM EMPLOYEES T ORDER BY T.HIRE_DATE;
–#############################################################################################################
–LAST_VALUE
SELECT
T.EMPLOYEE_ID,
T.LAST_NAME,
T.SALARY,
T.DEPARTMENT_ID,
LAST_VALUE(T.LAST_NAME) OVER(PARTITION BY T.DEPARTMENT_ID ORDER BY T.SALARY DESC) HIGHEST
FROM EMPLOYEES T;

–#############################################################################################################
–MAX 通过分组在数据窗口范围内找到最大值
–MIN 通过分组在数据窗口范围内找到最小值

SELECT
T.EMPLOYEE_ID,
T.LAST_NAME,
T.SALARY,
T.DEPARTMENT_ID,
MAX(T.SALARY) OVER(PARTITION BY T.DEPARTMENT_ID)
FROM EMPLOYEES T
/WHERE T.DEPARTMENT_ID = ‘30’/;

SELECT
T.EMPLOYEE_ID,
T.LAST_NAME,
T.SALARY,
T.DEPARTMENT_ID,
MIN(T.SALARY) OVER(PARTITION BY T.DEPARTMENT_ID)
FROM EMPLOYEES T;
–#############################################################################################################
–RANK
–按窗口函数中，在每个分组中为排好序的行进行编号形成序列
–这种排序如果遇到排序值相同的情况下在同一组内的排序值是不连续的，例如排序值是1,2,2,4(这种编号分两方面看，一方面是
–可见的编号，就是这种遇到相同值不连续的编号：另一方面是隐藏的编号，就是在同一个组中根据排序从1开始按相对位置连续编号
–例如：同一组中有11行数据，可见的编号1,2,2,4,4,6,6,6,6,6,11:不可见的编号1,2,3,4,5,6,7,8,9,10,11)
SELECT
T.EMPLOYEE_ID,
T.DEPARTMENT_ID,
T.SALARY,
RANK() OVER(PARTITION BY T.DEPARTMENT_ID ORDER BY T.SALARY) DRANK
FROM EMPLOYEES T;

–#############################################################################################################
–RATIO_TO_REPORT
–计算expression/sum(expression)的值，计算该行在组中个贡献
–这个可以按组分，在组中每行值占总数的贡献情况
SELECT
T.EMPLOYEE_ID,
T.LAST_NAME,
T.DEPARTMENT_ID,
T.SALARY,
RATIO_TO_REPORT(T.SALARY) OVER(PARTITION BY T.DEPARTMENT_ID)
FROM EMPLOYEES T;
–hr用户
–#############################################################################################################
–ROW_NUMBER
–为组内的每一行进行排序编号
SELECT
T.EMPLOYEE_ID,
T.LAST_NAME,
T.DEPARTMENT_ID,
T.SALARY,
ROW_NUMBER() OVER(PARTITION BY T.DEPARTMENT_ID ORDER BY T.SALARY) RN
FROM EMPLOYEES T ;
–#############################################################################################################
–SUM() OVER()
–求每个组中的累积和
–如果在同一个组中排序值是相等的那么它们的累加值也是相等的
–，而且这个累加值是所有相同排序值的总和加上相邻上一个排序值的和

–按工资排序出现了排序值相同的情况
SELECT
T.EMPLOYEE_ID,
T.LAST_NAME,
T.DEPARTMENT_ID,
T.SALARY,
SUM(T.SALARY) OVER(PARTITION BY T.DEPARTMENT_ID ORDER BY T.SALARY)
FROM EMPLOYEES T;

–按EMPLOYEE_ID排序，排序值没有重复情况
SELECT
T.EMPLOYEE_ID,
T.LAST_NAME,
T.DEPARTMENT_ID,
T.SALARY,
SUM(T.SALARY) OVER(PARTITION BY T.DEPARTMENT_ID ORDER BY T.EMPLOYEE_ID)
FROM EMPLOYEES T;

–加上RANGE UNBOUNDED PRECEDING的效果
SELECT
T.EMPLOYEE_ID,
T.LAST_NAME,
T.DEPARTMENT_ID,
T.SALARY,
SUM(T.SALARY) OVER(PARTITION BY T.DEPARTMENT_ID ORDER BY T.SALARY RANGE UNBOUNDED PRECEDING)
FROM EMPLOYEES T;
–#############################################################################################################