oracle学习笔记

oracle的数据类型

oracle的数据类型一览表

(4 gigabytes - 1) * (database block size)

大类型	具体类型	占用空间(Bytes)	说明
字符型	CHAR(N)	N	写入字节数小于N Bytes时，会用空格在右边补齐到N Bytes
	VARCHAR2(N)	0~N	变长存储的类型，可定义的N最大为4000 Bytes
	VARCHAR(N)	0~N	其实是VARCHAR的同义词
	NCHAR(N)	N*k（k为1 character所占Bytes）	为了支持多国语言，oracle用此类型来存储指定字符集下的多字节字符，例如utf8字符集中1个字符需要3个Bytes存储，那么需要占用的实际存储空间就为N*3Bytes
	NVARCHAR2(N)	0~N*k（k为1 character所占Bytes）	变长存储的NCHAR类型，可存储的最大长度4000 Bytes，至多可以存储4000 character（此时的1 character只需1 Byte）
	LONG	0~2G	此类型用来存储长度非常长的字符串，至多可以支持到2G Bytes，与VARCHAR2类型相似，但是它的使用为了向前兼容的需要，建议使用 LOB 数据类型来代替 LONG 类型。
	ROWID	16	该类型字段表示的值代表一行记录的相对唯一地址值， 其实它是一个伪列，不会实在存储于行数据中，它是在取出一行记录的时候编码得到的，可以在表中加这种类型的列，它就会被实际存储在表中，但是不能代表实际的位置值了， 相关的说明可以参考：https://www.cnblogs.com/xqzt/p/4449184.html
	UROWID	16	rowid的全局唯一地址值
数字型	NUMBER(P,S)	ROUND((p+s)/2)+1	1B存储指数值，1B可以存储2个十进制数(0-9)，所以尾数需要使用存储空间为(p+s)/2，总的存储大小为(p+s)/2+1
日期时间型	DATE	7	存储了YEAR, MONTH, DAY, HOUR, MINUTE，SECOND，没有秒的小数部分，也没有时区信息
	TIMESTAMP[(fractional_seconds_precision)]	7~11	可以精确到10^-9秒的时间类型，存储了YEAR, MONTH, DAY, HOUR, MINUTE 和 SECOND以及秒的小数部分
	TIMESTAMP[(fractional_seconds)] WITH LOCAL TIME ZONE	7~11	和TIMESTAMP类型类似，本身不存储时区信息，但是在存入和取出的时候会根据session与数据库的时区信息进行转换
	TIMESTAMP[(fractional_seconds)] WITH TIME ZONE	13	保存了YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, 和 TIMEZONE_MINUTE部分的信息，还有秒的小数部分信息
	INTERVAL YEAR[(year_precision)] TO MONTH	5	year_precision表示年数的最大位数，取值为0~9，默认是2
	INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds)]	11	day_precision表示天数的最大位数，取值范围为0~9，默认是2，fractional_seconds表示秒的小数部分最大位数，取值范围为0~9，默认是6
大对象型	CLOB	(4 gigabytes - 1) * (database block size)	大对象类型，保存单字节或者多字节字符的字符串类型，使用的字符集是数据库层面的字符集。
	NCLOB	(4 gigabytes - 1) * (database block size)	大对象类型，保存单字节或者多字节字符的字符串类型，使用的字符集是指定的字符集。
	BLOB		大对象类型，保存二进制数据。
	BFILE	(2^64)-1	保存了一个指向数据库外部文件的位置信息
二进制型	RAW(size)	0~size	存储二进制数据，size最大为2000Bytes
	LONG RAW	0~2G	存储二进制数据，不指定size，但是可以存储多达2GB的二进制的数据
	BINARY_FLOAT	5	存储二进制的float表示的float数据，使用4Bytes存储float，1Bytes存储长度
	BINARY_DOUBLE	9	存储二进制的double表示的double数据，使用8Bytes存储float，1Bytes存储长度

常用函数

非聚合函数

类型	描述	说明	示例(select ... from dual)
字符函数	ASCII(X)	返回字符X的ASCII码	ASCII('a') = 97
	CONCAT(X,Y)	连接字符串X和Y	CONCAT('hello', 'world') = 'hello world'
	INSTR(X,STR[,START][,N)	从X中查找str，可以指定从start开始，也可以指定从n开始	INSTR('Hello world','or') = 8
	LENGTH(X)	返回X的长度	LENGTH('Hello') = 5
	LOWER(X)	X转换成小写	LOWER('Hello') = hello
	UPPER(X)	X转换成大写	UPPER('Hello') = HELLO
	LTRIM(X[,TRIM_STR])	把X的左边截去trim_str字符串，缺省截去空格	LTRIM('=Hello=','=') = Hello=
	RTRIM(X[,TRIM_STR])	把X的右边截去trim_str字符串，缺省截去空格	RTRIM('=Hello=','=') = =Hello
	TRIM([TRIM_STR FROM]X)	把X的两边截去trim_str字符串，缺省截去空格	TRIM('='FROM'=Hello=') = Hello
	REPLACE(X,old,new)	在X中查找old，并替换成new	REPLACE('ABCDE','CD','AAA') = ABAAAE
	SUBSTR(X,start[,length])	返回X的字串，从start处开始，截取length个字符，缺省length，默认到结尾	SUBSTR('ABCDE',2,3) = BCD
数字函数	ABS(X)	X的绝对值	ABS(-3)=3
	ACOS(X)	X的反余弦	ACOS(1)=0
	COS(X)	余弦	COS(1)=0.54030230586814
	CEIL(X)	大于或等于X的最小值	CEIL(5.4)=6
	FLOOR(X)	小于或等于X的最大值	FLOOR(5.8)=5
	LOG(X,Y)	X为底Y的对数	LOG(2，4)=2
	MOD(X,Y)	X除以Y的余数	MOD(8，3)=2
	POWER(X,Y)	X的Y次幂	POWER(2，3)=8
	ROUND(X[,Y])	X在第Y位四舍五入	ROUND(3.456，2)=3.46
	SQRT(X)	X的平方根	SQRT(4)=2
	TRUNC(X[,Y])	X在第Y位截断	TRUNC(3.456，2)=3.45
日期函数	ADD_MONTHS(d,n)	在某一个日期 d 上，加上指定的月数 n，返回计算后的新日期	add_months(SYSDATE,5)
	LAST_DAY(d)	返回指定日期当月的最后一天	last_day(SYSDATE)
	ROUND(d[,fmt])	返回一个以 fmt 为格式的四舍五入日期值	ROUND(SYSDATE)
	EXTRACT(fmt FROM d)	提取日期中的特定部分,fmt 为：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。其中 YEAR、MONTH、DAY可以为 DATE 类型匹配，也可以与 TIMESTAMP 类型匹配；但是 HOUR、MINUTE、SECOND 必须与 TIMESTAMP 类型匹配。	EXTRACT(MONTH FROM SYSDATE)
	MONTHS_BETWEEN(date2,date1)	给出date2-date1的月份	months_between(19-12月-1999,19-3月-1999)
转换函数	TO_CHAR(d|n[,fmt])	把日期和数字转换为制定格式的字符串	TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日" HH24:MI:SS')
	TO_DATE(X,[,fmt])	把一个字符串以fmt格式转换成一个日期类型
	TO_NUMBER(X,[,fmt])	把一个字符串以fmt格式转换为一个数字
其他非聚合函数	NVL(X,VALUE)	如果X为空，返回value，否则返回X	NVL(COMM,100)
	NVL2(x,value1,value2)	如果x非空，返回value1，否则返回value2	NVL2(COMM,comm+100,200)

聚合函数

类型	描述	说明	示例(select ... from dual)
所有聚合类型	AVG	平均值	AVG（表达式）
	SUM	求和	SUM(表达式)
	MIN	最小值	MIN(表达式)
	MAX	最大值	MAX(表达式)
	COUNT	数据统计	COUNT（表达式）

oracle数据字典

ora sesstat：
v$statname
v$sesstat

ora sqltext:
V$SQLAREA

ora plan:
V$SQL_PLAN

oracle的一些特殊语句

connect by

层次查询

SELECT RPAD( ' ', 2*(LEVEL-1), '-' ) || DEPNAME "DEPNAME",
CONNECT_BY_ROOT DEPNAME "ROOT",
CONNECT_BY_ISLEAF "ISLEAF",
LEVEL ,
SYS_CONNECT_BY_PATH(DEPNAME, '/') "PATH"
FROM DEP
START WITH UPPERDEPID IS NULL
CONNECT BY PRIOR DEPID = UPPERDEPID;

开窗函数over()以及分析函数

开窗函数和分析函数的作用相当于对查询结果的每一行按照指定规则开一个小窗口，然后再指定分析函数进行聚合分析，得到结果称为单独一列。
over(分组 partition by 排序 order by 窗口 rows)

lag(sal) over(order by sal)

Oracle

参考链接

https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT113
https://blog.csdn.net/defonds/article/details/4302695
https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions048.htm#SQLRF00635
https://blog.csdn.net/u011955534/article/details/14168913
https://www.cnblogs.com/lettoo/archive/2010/08/03/1791239.html