Oracle基础知识点

一、ORACLE连接总结

SELECT * FROM EMP,DEPT;
SELECT * FROM EMP CROSS JOIN DEPT;
SELECT * FROM EMP NATURAL JOIN DEPT;
SELECT * FROM EMP JOIN DEPT USING(DEPTNO);
SELECT * FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;

1.1 内连接 （inner join （可简写为join））

内连接查询操作列出与连接条件匹配的数据行，它使用比较运算符比较被连接列的列值。

　　1、等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复属性。 （区别于自然连接）

　　2、不等连接： 在连接条件使用除等于运算符以外的其它比较运算符比较被连接的 列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。

SELECT S.*, T.* FROM STUDENT S INNER JOIN TEACHER  T ON S.TEACHERID = T.ID; 
//注意oracle重命名table 不能加as只需加空格即可，as可用于重命名列

ORACLE：

SELECT S.*, T.* FROM STUDENT S,TEACHER  T WHERE S.TEACHERID = T.ID; 

1.2 自然连接（natural join）

自然连接是在广义笛卡尔积R×S中选出同名属性上符合相等条件元组，再进行投影，去掉重复的同名属性，组成新的关系。即自然连接是在两张表中寻找那些数据类型和列名都相同的字段，然后自动地将他们连接起来，并返回所有符合条件按的结果。

SELECT STUDNET.CLASSID,TEACHER.DEPTNO FROM STUDENT  NATURAL JOIN TEACHER;
报错：（不能加限定条件）
SQL 错误: ORA-25155: NATURAL 联接中使用的列不能有限定词
25155. 00000 -  "column used in NATURAL join cannot have qualifier"
*Cause:    Columns that are used for a named-join (either a NATURAL join
           or a join with a USING clause) cannot have an explicit qualifier.
*Action:   Remove the qualifier.

更正：

SELECT * FROM STUDENT  NATURAL JOIN TEACHER;
SELECT * FROM STUDENT JOIN TEACHER ON STUDENT.DEPTNO= TEACHER.DEPTNO;

使用USING(字段)

SELECT * FROM EMP JOIN DEPT USING(DEPTNO);

ORACLE:

SELECT * FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO;

1.3 外连接 （outer join）

外连接，返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接或左连接))、右表(右外连接或右连接)或两个边接表(全外连接)中的所有数据行。

　1)left join(左联接)等价于（left outer join） 返回包括左表中的所有记录和右表中联结字段相等的记录；

　2)right join(右联接)等价于（right outer join）返回包括右表中的所有记录和左表中联结字段相等的记录；

　3) full join （全连接）等价于（full outer join）查询结果等于左外连接和右外连接的和

SELECT  S.*, T.* FROM STUDENT S LEFT JOIN TEACHER  T ON S.CLASSNO = T.CLASS;
SELECT  S.*, T.* FROM STUDENT S RIGHT JOIN TEACHER  T ON S.CLASSNO = T.CLASS;
SELECT  S.*, T.* FROM STUDENT S FULL JOIN TEACHER  T ON S.CLASSNO = T.CLASS;

ORACLE：

SELECT  S.*, T.* FROM STUDENT S,TEACHER  T WHERE S.CLASSNO = T.CLASS(+);
SELECT  S.*, T.* FROM STUDENT S,TEACHER  T WHERE S.CLASSNO(+) = T.CLASS;

1.4 交叉连接（cross join）

交叉连接不带ON子句，它返回被连接的两个表所有数据行的笛卡尔积，返回到 结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查 询条件的数据行数。

SELECT  S.*, T.* FROM STUDENT S CROSS JOIN TEACHER  T ;
等价于：
SELECT  S.*, T.* FROM STUDENT S , TEACHER  T ;

ORACLE:

SELECT  S.*, T.* FROM STUDENT S , TEACHER  T ;

1.5 自连接

连接的表是同一张表，使用自连接可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。

用途举例：计算公交链路换乘问题（见eg2）

SELECT  S1.*, S2.* FROM STUDENT S1 JOIN STUDENT S2 ON S1.DEPTNO=S2.DEPTNO AND S1.CLASSNO=S2.CLASSNO;

ORACLE:

SELECT  S1.*, S2.* FROM STUDENT S1 , STUDENT S2 WHERE S1.DEPTNO=S2.DEPTNO AND S1.CLASSNO=S2.CLASSNO;

二、常用统计函数

函数	含义	备注
MAX()	最大	SQL99
MIN()	最小	SQL99
AVERAGE()	平均	SQL99
COUNT()	计数	SQL99
SUM()	求和	SQL99
MEDIAN()	中间值	ORACLE私有
STDDEV()	标准差	ORACLE私有
VARIANCE()	方差	ORACLE私有

在所有的统计函数，只有COUNT()函数可以在表中没有任何数据的时候依然返回内容。

面试题1：请问COUNT(*),COUNT(字段),COUNT(DISTINCT 字段)有什么区别？

对于COUNT()函数而言，可以传递三类内容：*、字段、DISTINCT字段

COUNT(*)：统计所有记录数（含空值），但不建议使用，建议使用COUNT(字段)，最好用非空的列。

COUNT(字段)：如果列上存在空，则空值不统计。

COUNT(DISTINCT 字段)：如果列上存在重复，则重复值不统计。

方差：

标准差：标准差是方差的根

三、日期型函数

Oracle中的时间类型只有date和timestamp，timestamp是比date更精准的类型;

默认日期显示格式“DD-MON-YY”;

在Oracle中准确来说一个礼拜从星期日开始到星期六结束的，其中时间差以天数为单位。

3.1 日期格式：

+ 【/】【-】【（】可以作为连接的符号

+ 当有* 年*月* 日*汉字时，需要用双引号

日期表示形式必须是半角符号，不然会产生错误。

格式	含义
YYYY/SYYYY / SYYY
YYYY	四位表示的年份
YYY、YY、Y	年份的最后三位、两位或一位，缺省为当前世纪
IYYY	ISO标准的四位年份
MM	01~12的月份编号
MON	缩写字符集表示
MONTH	全拼字符集表示的月份，右边用空格填补
Q	季度
W	当月第几周
IW	ISO标准中的年中的第几周
D	当周第几天
DD	当月第几天
DDD	当年第几天
DY	缩写字符集表示
DAY	全拼字符集表示的天 如（星期六）
HH,HH12	一天中的第几个小时，12进制表示法
HH24	一天中的第几个小时，取值为00-23
SCC/ CC	世纪。如果四位数年份的最后两位数字是01到99（包括01和99），则该世纪是该年份的前两位数字加1。如果四位数年份的最后两位数字是00，则该世纪等于该年份的前两位数字。例如，2002年返回21，2000年返回20。
SYEAR / YEAR	年拼写出来。在S的情况下，在BC的日期的开始附加一个减号（ - ）。
RM	使用罗马数字表示月份
AM / PM	时间为午前还是午后
A.M./ P.M.	用A.M.或者P.M.形式表示(日语环境不变）

3.2 时区

GMT:格林威治时间（老的时间计量标准 ）

UTC:协调世界时间（我们现在用的时间标准），比GMT更加标准，UTC=GMT

CST:中央标准时间 CDT:中部夏令时间

PST:太平洋时间 PDT:太平洋夏令时间

EST:东部标准时间 EDT:东部夏令时间

3.3 函数

函数	含义	备注
SYSDATE	当前本地数据库的日期和时间，类型date；	没有参数
SYSTIMESTAMP	当前系统的日期、时间及市区。该时间包含时区信息，精确到微秒	没有参数
DBTIMEZONE	数据库时区	没有参数
SESSIONTIMEZONE	客户端时区	没有参数
TZ_OFFSET()	返回特定时区与UTC相比的时区偏移。	输入时区，如：'GMT','CST','CDT','PST','PDT','EST','EDT'
ADD_MONTHS（r,n）	给日期加上指定的月份 该函数返回在指定日期r上加上一个月份数n后的日期。	r：指定的日期。 n：要增加的月份数，如果N为负数，则表示减去的月份数。
LAST_DAY(r)	月份最后一天 返回指定r日期的当前月份的最后一天日期	r：指定的日期。
NEXT_DAY(r,c)	指定日期后一周的日期 返回指定R日期的后一周的与r日期字符（c：表示星期几）对应的日期。	r日期字符 c：表示星期几
EXTRACT（time）	返回指定日期中特定部分的函数。 返回指定time时间当中的年、月、日、分等日期部分.	输入：YEAR,MONTH,DAY,HOUR,MINUTE,SECOND等 例子：SELECT EXTRACT(HOUR FROM TIMESTAMP '2018-11-12 15:36:01') FROM DUAL;
MONTHS_BETWEEN(r1,r2)	返回两个日期间的月份数。 该函数返回r1日期和r2日期直接的月份。当r1>r2时，返回的是正数，假如r1和r2是不同月的同一天，则返回的是整数，否则返回的小数。当r1
ROUND（r[,f]）	日期截取函数 将日期r按f的格式进行四舍五入。如果f不填，则四舍五入到最近的一天。	例： select sysdate, --当前时间 round(sysdate, 'yyyy') as year, --按年 round(sysdate, 'mm') as month, --按月 round(sysdate, 'dd') as day, --按天 round(sysdate) as mr_day, --默认不填按天 round(sysdate, 'hh24') as hour --按小时 from dual;
TRUNC（r[,f]）	将日期r按f的格式进行截取。如果f不填，则截取到当前的日期。	select sysdate, --当前时间 trunc(sysdate, 'yyyy') as year, --按年 trunc(sysdate, 'mm') as month, --按月 trunc(sysdate, 'dd') as day, --按天 trunc(sysdate) as mr_day, --默认不填按天 trunc(sysdate, 'hh24') as hour --按小时 from dual;
ADD_MONTHS(日期,数字)	在指定日期上加入指定的月数，求出新的日期

3.4 公式

日期-数字=日期

日期+数字=日期

日期-日期=数字（天数）

四、类型转换

4.1 隐式数据类型转换

（1）varchar2变长/char定长——>number，例如：'123'——>123

（2）varchar2/char——>date，例如：'25-4月-15'——>'25-4月-15'

（3）number——>varchar2/char，例如：123——>'123'

（4）date——>varchar2/char，例如：'25-4月-15'——>'25-4月-15'

oracle如何隐式转换

1.“=”号两边的类型是否相同

2.如果“=”号两边的类型不同，尝试的去做转换

3.在转换时，要确保合法合理，否则转换会失败，例如：12月不会有32天，一年中不会有13月

4.2 显式数据类型转换

4.3 TO_CHAR函数对日期的转换

格式：TO_CHAR(日期, '转换格式');

转换格式

• year：y，表示年的最后一位；yy，表示年的最后2位；yyy，表示年的最后3位；yyyy，用4位数表示年。
• month：mm，用2位数字表示月；mon，简写形式比如11月或者nov；month，全称比如11月或者november。
• day：dd，表示当月第几天；ddd，表示当年第几天；dy，当周第几天，简写形式比如星期五或者fri；day，当周第几天，全写比如星期五或者friday。
• hour：hh，2位数表示小时12小时；hh24，2位数表示小时24小时。
• minute：mi，2位数表示分钟。
• second：ss，2位数表示秒60进制。
• 季度：q，一位数表示季度（1 - 4）。
• 周：ww，用来表示当年第几周；w，用来表示当月第几周。
• 24小时制下的时间范围（00：00：00 - 23：59：59）
• 12小时制下的时间范围（1：00：00 - 12：59：59）

使用双引号向日期中添加字符

--显示如下格式：2015 年 04 月 25 日 星期六
select to_char(sysdate,'yyyy "年" mm "月" dd "日" day') from dual;
--显示如格式：2015-04-25今天是星期六 15:15:15
select to_char(sysdate,'yyyy-mm-dd"今天是"day hh24:mi:ss') from dual;
select to_char(sysdate,'yyyy-mm-dd"今天是"day HH12:MI:SS AM') from dual;

4.4 TO_CHAR 函数对数字的转换

格式：TO_CHAR(数值, '转换格式');

转换格式

格式	含义
9	数字，0到9中的任意一个值
0	数字，0到9中的任意一个值（没有对应值，则零）
$	美元符
L	本地货币符号
.	小数点
,	千位符

--显示如下格式：$1,234
select to_char(1234,'$9,999') from dual;
--显示如下格式：￥1,234
select to_char(1234,'L9,999') from dual;

4.5 TO_NUMBER 函数将字符转换成数字

格式：

TO_NUMBER(varchar2|char);

TO_NUMBER(varchar2|char , '转换格式');

--将字符串‘123’转成数字123

select to_number('123') from dual;

4.6 TO_DATE 函数将字符转换成日期

格式：
TO_DATE(varchar2|char);
TO_DATE(varchar2|char , '转换格式');
--查询1980年12月17日入职的员工
select * from emp where hiredate = to_date('1980年12月17日','yyyy"年"mm"月"dd"日"');
select * from emp where hiredate = to_date('1980#12#17','yyyy"#"mm"#"dd');
select * from emp where hiredate = to_date('1980-12-17','yyyy-mm-dd');

注意

select '123' + 123 from dual; --246
select '123' || 123 from dual; --123123 

五、通用函数

函数名称	描述
NVL(数字|列,默认值)	如果显示的数字是null的话，则使用默认数值表示
NVL2(数字|列,返回结果一（不为空显示）,返回结果二（为空显示）)	判断指定的列是否是null，如果不为null则返回结果一，为空则返回结果二
NULLIF(表达式一，表达式二)	比较表达式一和表达式二的结果是否相等，如果相等返回NULL，如果不等返回表达式一
DECODE(列|值,判断值1,显示结果1,判断值2,显示结果2,...,默认值)	多值判断，如果某一个列（或某一个值）与判断值相同，则使用指定的显示结果输出，如果没有满足条件，则显示默认值
CASE 列|数值 WHEN 表达式1 THEN 显示结果1 ... ELSE 表达式N ... END	用于实现多条件判断，在WHEN之后编写条件，而在THEN之后编写条件满足的显示操作，如果都不满足则使用ELSE中的表达式处理。
COALESCE(表达式1,表达式2,...,表达式n)	将表达式逐个判断，如果表达式1的内容是null，则显示表达式2，如果表达式2的内容是null，则显示表达式3，依次类推，如果表达式n的结果还是null，则返回null

六、字符函数

七、数值函数

函数名	说明
ABS(n)	返回数字的绝对值
SIGN(x)函数	检测x的正负.如果x<0返回－1.如果x=0返回0.如果x>0返回1.
CEIL(n)	返回大于或等于n的最小的整数值
FLOOR(n)	返回小于或等于n的最大的整数值
MOD(number,divisor)	取余。number为被除数，divisor为除数。如果divisor为0，则返回number
SQRT(X)	X的平方根
COS(n)	返回n的余弦值
ACOS(n)	反余弦函数，n between -1 and 1，返回值between 0 and pi，输出以弧度为单位.
COSH(n)	计算n的双曲余弦值.
SIN(n)	返回n的正弦值，n为弧度
ASIN(n)	返回n的反正弦值. n的范围应该是－1到1之间,返回的结果在－pi/2到pi/2之间,以弧度为单位
SINH(n)	返回n的双曲正弦值，n为弧度
TAN(n)	返回n的正切值，n为弧度
ATAN(n)	计算x的反正切值.返回值在－pi/2到pi/2之间,单位是弧度
ATAN2(x,y)	返回x除以y的反正切值.结果在负的pi/2到正的pi/2之间,单位是弧度.
TANH(n)	返回n的双曲正切值，n为弧度
POWER(X,Y)	X的Y次幂
LOG(X,Y)	X为底Y的对数，X>0 and not 1,Y>0
EXP(x)	计算e的x次幂. e为自然对数,e=2.71828...
LN(x)	返回x的自然对数. x必须是正数,并且大于0
TRUNC(X[,Y])	X在第Y位截断。直接截取，不四舍五入。y缺省值为0。y>0，就是四舍五入到小数点右边y位。若y<0，四舍五入到小数点左边|y|位。
ROUND(X[,Y])	X在第Y位四舍五入
BITAND(n1,n2)	位与运算符。3和9转为二进制分别为0011和1001，做位与运算得到0001，转换为十进制数为1.
BIN_TO_NUM(n1,n2,……n)	二进制转向十进制
TO_char()	格式化数值：常见的字符匹配有 0、9、，、$、FM（去掉前面空格和后面的0）、L、C。其中FM可以与$,L,C一起使用