Oracle数据第二讲

概述：单行函数、多表查询、分组函数。

一、单行函数

a) 字符串函数，它是数据库最广泛的函数

LOWER:小写

UPPER：大写

INITCAP：首字母大写

CONCAT：连接

SUBSTR：截取  (参数，开始，数目)

LENGTH：返回字符串的长度

INSTR：（参数，字母） 返回字母出现的位置

LPAD：（参数,长度，在前补齐参数字母） 

| RPAD：（参数，长度,在后补齐参数字母）

TRIM ：

REPLACE：（参数,参数[,参数]）:第一个参数操作数,第二是要查找的字符,第三个是替换的字符，如果没有第三个就删除查找的字符。

b) 数字函数

ROUND: 四舍五入 

            ROUND(45.926, 2) 45.93 

TRUNC:  截断 

            TRUNC(45.926, 2) 45.92

MOD: 求余 

            MOD(1600, 300) 100

ABS：绝对值

CEIL：返回大于或等于value的最小整数

FLOOR：返回小于或等于value的最大整数

SQRT :返回value的平方根 负数无意义。

c) 日期时间函数

Oracle 中的日期型数据实际含有两个值: 日期和时间。默认的日期格式是 DD-MON-RR.日期时间函数用来返回当前系统的日期和时间、以及对日期和时间类型的数据进行处理运算。

add_months(date,count);在指定的日期上增加count个月

last_day(date);返回日期date所在月的最后一天

months_between(date1,dates);返回date1到date2之间间隔多少个月

new_time(date,this’,’other’);将时间date从this时区转换成other时区

next_day(day,’day’);返回指定日期或最后一的第一个星期几的日期,这里day为星期几

sysdate();获取系统的当前日期

current_timestamp();获取当前的时间和日期值

round：日期的四舍五入

trunc 日期的截取

日期的数学运算：

l 在日期上加上或减去一个数字结果仍为日期。 

l 两个日期相减返回日期之间相差的天数。 

l 可以用数字除24来向日期中加上或减去小时。

显式转换

to_char(date,’format’):按照指定的格式format把数字或日期类型的数据转换成字符串

格式:

必须包含在单引号中而且大小写敏感。 

可以包含任意的有效的日期格式。 

日期之间用逗号隔开。 

d) 通用函数

 这些函数适用于任何数据类型，同时也适用于空值： 

NVL (expr1, expr2)

NVL2 (expr1, expr2, expr3)

NULLIF (expr1, expr2)

COALESCE (expr1, expr2, ..., exprn)

nvl()

将空值转换成一个已知的值： 

l 可以使用的数据类型有日期、字符、数字。 

l 函数的一般形式:

• NVL(commission_pct,0)

• NVL(hire_date,'01-JAN-97')

• NVL(job_id,'No Job Yet')

e) 条件表达式 IF-THEN-ELSE 逻辑

语法：

CASE expr WHEN comparison_expr1 THEN return_expr1

         [WHEN comparison_expr2 THEN return_expr2

          WHEN comparison_exprn THEN return_exprn 

          ELSE else_expr]

END

DECODE(col|expression, search1, result1 

          [, search2, result2,...,]

          [, default])

二、多表查询

a) 连接查询

1、 from子句应当包括所有的表名

2、 where子句应定义连接条件 两个表1一个等值条件 三个表 2个等值条件…依次类推。

l 备注：连接 n个表,至少需要 n-1个连接条件。 例如：连接三个表，至少需要两个连接条件。 

3、 当列名为多个表共有时,列名必须被限制。

4、 可以使用join  on 语句查询

    语法：

         From join_table join_type join_table on join_condition

        备注： Join_table连接的表名 join_type连接的类型

b) 内连接

i. 等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列

SQL> select * from emp e inner join dept d on e.deptno = d.deptno; 

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

SQL> select * from emp e inner join dept d on e.deptno>d.deptno;

iii. 自然连接：在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列

SQL> select * from emp natural join dept;

Distinct是去掉重复的行,而自然连接是去掉重复的列。

c) 外连接

概述：内连接的查询结果都是满足连接条件的记录。但是，有时我们也希望输出那些不满足连接条件的记录的信息。比如，我们想知道这个部门中所有员工的情况,也包括没有员工的部门，这时就需要使用外连接。外连接是只限制一张表中的数据必须满足连接条件，而另一张表中的数据可以不满足连接条件的连接方式。

i. 左外连接（LEFT OUTER JOIN）

查询语句使用关键字 LEFT OUTER JOIN，也就是说，左外连接的含义是限制连接关键字右端的表中的数据必须满足连接条件，而不关左端的表中的数据是否满足连接条件，均输出左端表中的内容。 

SQL> select * from dept d left outer join emp e on e.deptno=d.deptno order by d.deptno;

备注：部门表中记录保留,如果部门中没有员工,部门显示 员工记录用null补充。

ii. 右外连接（RIGHT OUTER JOIN）

右外连接与左外连接类似，只是右端表中的所有元组都列出，限制左端表的数据必须满足连接条件，而不管右端表中的数据是否满足连接条件，均输出表中的内容。

SQL> select * from emp e right outer join dept d on e.deptno=d.deptno order by d.deptno;

iii. 全外连接（FULL OUTER JOIN） 

全外连接查询的特点是左、右两端表中的记录都输出，如果没能找到匹配的记录，就使用NULL来代替。 

　SQL> select * from emp e full outer join dept d on e.deptno=d.deptno order by d.deptno; 

注意：一定分清：左与右   在join后边的是右,还有一种写法是+则分清+的那方不保留

select * from emp e,dept d where e.deptno(+)=d.deptno on d.deptno=e.deptno;保留的是d方的数据

d) 交叉连接

交叉连接即笛卡儿乘积，是指两个关系中所有记录的任意组合。一般情况下，交叉查询是没有实际意义的。

SQL> select * from emp e cross join dept d;

备注：可以添加where子句筛选出有意义的数据。建议不使用。

e) 自连接查询

如果在一个连接查询中，涉及到的两个表都是同一个表，这种查询称为自连接查询。同一张表在FROM字句中多次出现，为了区别该表的每一次出现，需要为表定义一个别名。自连接是一种特殊的内连接，它是指相互连接的表在物理上为同一张表，但可以在逻辑上分为两张表。

　SQL> select e1.* from emp e inner join emp e1 on e.mgr=e1.empno where e.empno=7369;

注意：对于连接查询中使用到的 inner outer是可以省略的。但为规范最好不要省略。

f) 常用语法总结

i. 实例1：

SQL> select * from emp e inner join dept d on e.deptno(+)=d.deptno;的理解?

l 使用外连接可以查询不满足连接条件的数据。

l 外连接的符号是 (+)。

ii. Natural、join、using关键字使用

l 在NATURAL JOIN 子句创建等值连接时，可以使用 USING 子句指定等值连接中需要用到的列。 

l 使用 USING 可以在有多个列满足条件时进行选择。 

l 不要给选中的列中加上表名前缀或别名。

NATURAL JOIN 和 USING 子句经常同时使用。

三、分组函数

概述：聚合函数对一组行中的某个列执行计算执行计算并返回单一的值。聚合函数忽略空值。聚合函数经常与 SELECT 语句的 GROUP BY 子句一同使用,所以有的时候也把其称之为分组函数。

使用：分组函数作用于一组数据，并对一组数据返回一个值.

a) 常用分组函数

Count  返回找到的记录数

Min   返回一个数字列或计算列的最小

Max   返回一个数字列或计算列的最大值

Sum   返回一个数字列或计算列总和

Avg   返回一个数字列或计算列的平均值

b) Group by分组详细介绍

概述：Group By语句从英文的字面意义上理解就是“根据(by)一定的规则进行分组(Group)”。它的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对若干个小区域进行数据处理。 如果在查询的过程中需要按某一列的值进行分组,以统计该组内数据的信息时,就要使用group by子句。不管select是否使用了where子句都可以使用group by子句

语法：

SELECT column, group_function 

FROM table 

[WHERE condition]

[GROUP BY group_by_expression] 

[HAVING group_condition]

[ORDER BY column]; 

Having 子句

    HAVING 子句对 GROUP BY 子句设置条件的方式与 WHERE 子句和 SELECT 语句交互的方式类似。WHERE 子句搜索条件在进行分组操作之前应用；而 HAVING 搜索条件在进行分组操作之后应用。HAVING 语法与 WHERE 语法类似，但 HAVING 可以包含聚合函数。HAVING 子句可以引用选择列表中出现的任意项。

备注：having子句通常与group by子句结合使用

嵌套函数

　　函数可以嵌套。一个函数的输出可以是另一个函数的输入。操作数有一个可继承的执行过程。但函数的优先权只是基于位置，函数遵循由内到外，由左到右的原则.

　嵌套函数可以包括在组函数中嵌套单行函数，或者组函数嵌套入单行函数或组函数中

总结：整个查询语句的语法格式如下：

SELECT column, group_function(column) 

FROM table 

[WHERE condition]

[GROUP BY group_by_expression] 

[HAVING group_condition]

[ORDER BY column];