Oracle2 函数、多表查询、结果集、伪列_oracle 通用结果集

1. 单行函数

1.1概述

oracle数据库中，内置了很多常用的函数，整体分为：

• 单行函数
  • 字符函数
  • 日期函数
  • 数字函数
• 转换函数
• 聚合函数

单行函数，也可以称为单值函数，每操作一行数据，都会返回一个结果

例如，查询id小于5的员工信息（id、last_name、salary）

select id,last_name,salary
from s_emp
where id<5;
//运行结果：
ID LAST_NAME SALARY
---------- --------------- ----------
1 Velasquez 2500
2 Ngao 1450
3 Nagayama 1400
4 Quick-To-See 1450
1
2
3
4
5
6
7
8
9
10

使用单行函数，将上面的结果中每一个last_name转换为大写

select id,upper(last_name) as last_name,salary
from s_emp
where id<5;
//运行结果：
ID LAST_NAME SALARY
---------- --------------- ----------
1 VELASQUEZ 2500
2 NGAO 1450
3 NAGAYAMA 1400
4 QUICK-TO-SEE 1450
1
2
3
4
5
6
7
8
9
10

1.2 哑表

Oracle中，有一张特殊的表：dual

dual被称之为哑表，它是一个单行单列的虚拟表，是Oracle内部自动创建的，这个表只有1列：DUMMY， 数据类型为VERCHAR2(1)，dual表中只有一个数据’X’，Oracle有内部逻辑保证dual表中永远只有一条数 据。

在实际使用中，Dual表主要用来选择系统变量或求一个表达式的值，因为要使用dual来构造完成的查询 语法

例如，查询表达式1+1的结果

select 1+1 from dual;
//运行结果：
1+1
----------
2
1
2
3
4
5

注意，按照sql语句的要求，没有表就没法查询，而表达式1+1，不属于任何表，那么就有了哑表 dual的概念了

注意，只有oracle数据库中有这个哑表 dual

1.3 字符函数

常用的字符函数有：

**ASCII(X) **，返回字符X的ASCII码：

select ascii('a') as result from dual;
//运行结果：
RESULT
----------
97
select ascii('ah') as result from dual;
//运行结果：
RESULT
----------
97
1
2
3
4
5
6
7
8
9
10

只会显示第一个字符的ascii码‘

**CONCAT(X,Y) **，连接字符串X和Y

select concat('Hello','World') as result from dual;
//运行结果：
RESULT
--------------------
HelloWorld
1
2
3
4
5

**INSTR(X,STR[,START][,N) **，从X中查找str，可以指定从start开始，也可以指定从n开始

select instr('Hello World','o',6) as result from dual;
//从前往后查找，从下标为6开始
//运行结果：
RESULT
----------
8

------如果有多个相同的结果只返回第一个
select instr('Hello world','o')
from dual;

------不存在则返回0
select instr('Hello world','z')
from dual;

------------从指定位置查找，如果指定的位置是-1，就从末尾开始查找
select instr('Hello world','o',-1)
from dual;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

**LENGTH(X) **， 返回X的长度

SELECT length('hello world')
from dual;

select * from s_emp where length(last_name)>5;
1
2
3
4

**LOWER(X) **，X转换成小写

select lower('Hello World') as result from dual;
//运行结果：
RESULT
----------------------
hello world
1
2
3
4
5

UPPER(X) ，X转换成大写

select upper('Hello World') as result from dual;
//运行结果：
RESULT
----------------------
HELLO WORLD
1
2
3
4
5

**INITCAP(X) **，X首字母转换为大写，其他字母小写

select initcap('hello') as result from dual;
运行结果：
RESULT
----------
Hello
1
2
3
4
5

**LTRIM(X[,TRIM_STR]) **，把X的左边截去trim_str字符串，缺省截去空格

select ltrim('=Hello=','=') as result from dual;
//运行结果：
RESULT
------------
Hello=

------如果存在多个相同的字符会全部戒掉
select ltrim('hhello','h') as result
from dual;
//运行结果：
RESULT
------------
ello


------默认截取空格
select ltrim('  hello') as result
from dual;
//运行结果：
RESULT
------------
hello
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

1.4 数字函数

专门操作数字的函数，常用的数字函数有：

ROUND(X[,Y]) ，X在第Y位四舍五入

第一个参数表示要进行四舍五入操作的数字

第二个参数表示保留到哪一位

//保留到小数点后面2位
select round(45.923,2) as result
from dual;
//运行结果：
RESULT
----------
45.92
1
2
3
4
5
6
7

注意，如果第二个参数不写，默认就是0，表示保留到个位

//保留到十位
select round(45.923,-1) as result
from dual;
//运行结果：
RESULT
----------
50
1
2
3
4
5
6
7

同理 -2就为保留到百位。参数以个位为基准，向左为- 向右为+

**TRUNC(X[,Y]) **，X在第Y位截断

trunc和round的用法一样，但是trunc只会舍去不会进位

//截取到小数点后面2位
select trunc(45.929,2) as result
from dual;
//运行结果：
RESULT
----------
45.92
1
2
3
4
5
6
7

MOD(X,Y) ，X除以Y的余数

第一个参数表示要进行取余操作的数字

第二个参数表示参数1和谁取余

//把10和3进行取余 (10除以3然后获取余数)
select mod(10,3) as result
from dual;
//运行结果：
RESULT
----------
1

--------------mod 任何数对0取余都是原数
select mod(123,0) as result
from dual;
-------------对小数也能进行取余操作
select mod(5.1,0.2) as result
from dual;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

oracle中 任何数对0 取余都是原数，对小数也能进行取余操作

1.5 日期函数

sysdate ，是Oracle中用来表示当前时间的关键字，并且可以使用它来参与时间运算。

例如，

//显示当前时间
select sysdate from dual;
//显示时间:明天的这个时候
select sysdate + 1 from dual;
//显示时间:昨天的这个时候
select sysdate - 1 from dual;
//显示时间:1小时之后的这个日期
select sysdate + 1/24 from dual;
1
2
3
4
5
6
7
8

sysdate 参与时间的加减操作的时候，单位是天

oracle中不同的会话环境中，日期数据默认的格式也不同

常见的日期函数：

----------显示系统当前时间
select sysdate
from dual;
------参加运算的单位是天
select sysdate -1/24
from dual;

------还计算闰月
select months_between(sysdate+61,sysdate) as result
from dual;

----将当前日期向后推2个月
select add_months(sysdate,2) as result
from dual;
------将当前日期向前推2个月
select add_months(sysdate,-2) as result
from dual;

-----下一个星期一
select next_day(sysdate,'星期一')
from dual;

---当前月的最后一天
select last_day(sysdate)
from dual;

----round 对日期四舍五入,默认对天四舍五入
select round(sysdate)
from dual;
-----可以设置参数，调整标准
--------对月份进位，天数>=16进1
select round(sysdate+2,'MONTH')
from dual;
------对年份四舍五入
select round(sysdate,'year')
from dual;

------trunc 对日期截取，只舍弃不进位
select trunc(sysdate,'year')
from dual;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

2.转换函数

2.1概述

转换函数，可以将一个类型的数据，转换为另一种类型的数据。

转换函数主要有三种：

• TO_CHAR ，把一个数字或日期数据转换为字符
• TO_NUMBER ，把字符转换为数字
• TO_DATE ，把字符转换为日期

2.2 to_char

把一个数字或日期数据转换为字符

数字转为字符的常用格式：

例子：

select to_char(salary,'$999,999.00') as result
from s_emp;
//运行结果：
RESULT
------------------------
$2,500.00
$1,450.00
$1,400.00
$1,450.00
$1,550.00

select to_char(salary,'L999,999.00') as result
from s_emp;
//运行结果：
RESULT
------------------------------------------
￥2,500.00
￥1,450.00
￥1,400.00
￥1,450.00
￥1,550.00
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

日期转为字符的常用格式：

例子：

select to_char(sysdate,'yy-mm-dd hh:mi:ss')
from dual;
----24小时制
select to_char(sysdate,'yy-mm-dd hh24:mi:ss')
from dual;

----to_char
select id,last_name,to_char(salary,'L9,999.99')
from s_emp;
-----可以传入任意符号，但是不能传入字符
---hh默认12小时制 在hh后面加上24可以调整成24小时制
select to_char(sysdate+0.5,'yyyy-mm-d hh,mi,ss am')
from dual;
1
2
3
4
5
6
7
8
9
10
11
12
13

2.3千年虫

在早期的计算机的程序中，规定了的年份仅用两位数来表示。

假如是1971年，在计算机里就会被表示为71。

但是到了2000年的时候这个情况就出现了问题，计算机读取出之前存的一个日期88，结果就是2088年。

这样的话，计算机内部对年份的计算就会出现问题，这个事情当时被称为千年虫

为了处理这个问题，后面就有了一种新的日期格式： rr

如果在存储日期数据的时候，年份采用的格式为rr，并且只提供了最后2位年份，那么年份中的前两位 数字就由两部分共同确定，其规则如下：

• 规则1

  如果指定年在00_{49之间，并且当前年份在00}49之间，那么指定年的世纪就与当前年份的世纪相 同。因此，指定年的前两位数字就等于当前年份的前两位数字。例如，如果指定年为15，而当前年 份为2007，那么指定年就是2015

• 规则2

  如果指定年在50_{99之间，并且当前年份在00}49之间，那么指定年的世纪就等于当前年份的世纪减 去1。因此，指定年的前两位数字等于当前年份的前两位数字减去1。例如，如果指定年为75，而当 前年份为2007，那么指定年就是1975

• 规则3

  如果指定年在00_{49之间，并且当前年份在50}99之间，那么指定年的世纪就等于当前年份的世纪加 上1。因此，指定年的前两位数字等于当前年份的前两位数字加上1。例如，如果指定年为15，而当 前年份为2075，那么指定年就是2115

• 规则4

  如果指定年在50_{99之间，并且当前年份在50}99之间，那么指定年的世纪就与当前年份的世纪相 同。因此，指定年的前两位数字就等于当前年份的前两位数字。例如，如果指定年为55，而当前年 份为2075，那么指定年就是2055

2.4 to_number

把字符转换为数字

例如：

select to_number('1000')
from dual;
1
2

例如，这个写法是错的，abc不能转换为数字

select to_number('abc')
from dual;
1
2

2.5 to_date

把字符转换为日期

例如：

select to_date('10-12-2022','dd-mm-yyyy') as result
from dual;

select to_date('22/5月/25','yy/month/dd') as result
from dual;
1
2
3
4
5

2.6 函数嵌套

以上介绍的常用函数，在数据类型正确的情况下，是可以嵌套使用的。

例如，先把’hello’和’world’连接起来，再转换为全部字母大写，然后再从第4个字符开始，连着截取4个 字符

select substr(upper(concat('hello','world')),4,4) as result
from dual;
1
2

注意，函数f1的返回类型，必须是函数f2的参数类型，那么它们之间才可以嵌套

3.多表查询

多表查询，又称表联合查询，即一条sql语句涉及到的表有多张，表中的数据通过特定的连接，进 行联合显示

3.1笛卡尔积

在数学中，两个集合X和Y的笛卡尓积（Cartesian product），又称直积，表示为X × Y

例如，假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。

在数据库中，如果直接查询俩张表，那么其查询结果就会产生笛卡尔积。

例如：

select count(*)
from s_emp,s_dept;
1
2

s_emp表中25条数据，s_dept表中12条数据，查询俩张表，数据俩俩组合，会得到300条数 据

其实，s_emp表中的每一条数据，和s_dept表中的每一条数据进行俩俩组合，这里面大多数的数据是没 有意义的，为了这种避免笛卡尔积的产生，在多表查询的时候，可以使用连接查询来解决这个问题。

连接查询又可以大致分为：

1. 等值连接
2. 不等值连接
3. 外连接
   • 左外连接
   • 右外连接
   • 全连接
4. 自连接

3.2 等值连接

利用一张表中某列的值，和另一张表中某列的值相等的关系，把俩张表连接起来，满足条件的数 据才会组合

例如 查询每个员工所属的部门员工以及该部门所属的区域名称

select e.last_name,d.name as 部门名称,r.name as 区域名称
from s_emp e,s_dept d,s_region r
where e.dept_id =d.id and d.region_id = r.id;
1
2
3

3.3 不等值连接

假设数据库中还有一张工资等级表， salgrade

工资等级表 salgrade ：

• gradeName 列表示等级名称
• losal 列表示这个级别的最低工资数
• hisal 列表示这个级别的最高工资数

现在创建这个表并添加数据：

create table salgrade(
    id number primary key,
    gradeName varchar2(255),
    losal number,
    hisal number
);


insert into salgrade values(1,'初级程序员',2000,5000);
insert into salgrade values(2,'中级程序员',5001,15000);
insert into salgrade values(3,'高级程序员',15001,40000);
commit;
1
2
3
4
5
6
7
8
9
10
11
12

然后进行查询

select  e.last_name,e.salary,g.gradename
from s_emp e,salgrade g
where e.salary >= g.losal and e.salary<= g.hisal;
1
2
3

3.4 外连接

外连接分为，左外连接、右外连接、全连接

先分别在俩s_emp和s_dept表中插入新的数据

insert into s_emp(id,last_name) values(26,'tom');
insert into s_dept(id,name) values(60,'st');
commit;
1
2
3

新员工tom不在任何部门中，新增部门st下面没有任何员工

这个时候，再使用等值连接的话，查询出来的数据就会少：

• 新增的员工tom和部门表中的数据连接不上
• 新增的部门st也和员工表中的数据连接不上

那么，这俩条数据都是在等值连接中查询不出来的。

3.4.1 左外连接

例如，查询所有员工，以及对应的部门的名字，没有部门的员工也要显示出来

-----左外连接 oracle特有的(+)
select e.last_name,d.name as deptName
from s_emp e,s_dept d
where e.dept_id = d.id(+);
1
2
3
4

或者

------通用的左外连接格式 outer可以省略
select emp.last_name,dept.name as deptName
from s_emp emp left outer JOIN s_dept dept
on emp.dept_id = dept.id;
1
2
3
4

3.4.2 右外连接

查询所有员工 以及对应的部门的名字,没有任何员工的部门也要显示出来

select emp.last_name,dept.name as deptName
from s_emp emp ,s_dept dept
where emp.dept_id(+) = dept.id;
1
2
3

或者

select emp.last_name,dept.name as deptName
from s_emp emp right outer JOIN s_dept dept
on emp.dept_id = dept.id;
1
2
3

3.4.3 全连接

查询员工所属的部门，没有员工的部门和没有部门的员工都要查询出来

select emp.last_name,dept.name as deptName
from s_emp emp right outer JOIN s_dept dept
on emp.dept_id = dept.id;
1
2
3

全连接 使用两个加号会报错

3.5 自连接

自连接就是一张表，自己和自己连接后进行查询

例如，查询每个员工和他的上级 使用自连接

select emp.last_name,manage.last_name
from s_emp emp,s_emp manage
where emp.manager_id = manage.id;
1
2
3

4.操作结果集

4.1概述

每一条sql语句，查询出的一个结果，都可以被称为结果集。

如果有俩条sql语句，它们分别查询出的结果集，都包含完全一致的字段名称和类型，那么我们可以使用 下面的关键字对俩个结果集进行操作：

• union ，取俩个结果集的并集
• union all ，把俩个结果集合在一起显示出来
• minus ，第一个结果集除去第二个结果集和它相同的部分
• intersect ，求俩个结果集的交集

前提条件是，俩个结果集中查询的列要完全一致（名称和类型）

4.2union 并集

union并集 操作时两个结果集的字段名一定要相同

select emp.last_name,dept.name as deptName
from s_emp emp ,s_dept dept
where emp.dept_id(+) = dept.id
union 
select emp.last_name,dept.name as deptName
from s_emp emp ,s_dept dept
where emp.dept_id = dept.id(+);
1
2
3
4
5
6
7

union all，把俩个结果集合在一起显示出来

select emp.last_name,dept.name as deptName
from s_emp emp ,s_dept dept
where emp.dept_id(+) = dept.id
union all 
select emp.last_name,dept.name as deptName
from s_emp emp ,s_dept dept
where emp.dept_id = dept.id(+);
1
2
3
4
5
6
7

4.3minus 差集

第一个结果集减去第二个结果集

select emp.last_name,dept.name as deptName
from s_emp emp ,s_dept dept
where emp.dept_id(+) = dept.id
minus
select emp.last_name,dept.name as deptName
from s_emp emp ,s_dept dept
where emp.dept_id = dept.id(+);
1
2
3
4
5
6
7

4.4 intersect 交集

求俩个结果集的交集

select emp.last_name,dept.name as deptName
from s_emp emp ,s_dept dept
where emp.dept_id(+) = dept.id
intersect
select emp.last_name,dept.name as deptName
from s_emp emp ,s_dept dept
where emp.dept_id = dept.id(+);
1
2
3
4
5
6
7

5. rownum 伪列

Oracle中，有一个特殊的关键字rownum，被称为：伪列。 rownum只有Oracle数据中才有。

rownum （伪列），就像表中的列一样，但是在表中并不存在。

伪列并不能像表中真实的列一样随便操作，伪列只能用于查询

select rownum,last_name
from s_emp;
//查询结果：
ROWNUM LAST_NAME
---------- ---------------
1 Velasquez
2 Ngao
3 Nagayama
4 Quick-To-See
……………………
1
2
3
4
5
6
7
8
9
10

伪列，可以根据查询结果的条数，自动生成，并且一定是从1开始连续不断的数字 伪列rownum的本质就是给查询的一行行结果标上行号

如果伪列用在where条件中，那么它只能有以下操作：

• rownum 如果是相同的条件，那么伪列只能等于1

select last_name
from s_emp
where rownum=1
1
2
3

如果等于其他值就查询不出任何结果

• rownum 如果是大于的条件，那么伪列只能大于0

select last_name
from s_emp
where rownum>0
1
2
3

如果大于其他值，那么就查询不出任何结果

• rownum 可以小于任何数

select last_name
from s_emp
where rownum<7
1
2
3

将来在实际的使用中，Oracle数据库中伪列rownum最核心的作用就是：完成分页查询。