oracle求两边查集,oracle（二）函数、多表查询、结果集、伪列

admin 管理员组

文章数量: 886992

oracle求两边查集,oracle（二）函数、多表查询、结果集、伪列

单行函数

概述

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

单行函数

字符函数

日期函数

数字函数

转换函数

聚合函数

单行函数，也可以称为单值函数，每操作一行数据(某个字段值)，都会返回一个结果，意思是对每一行数据都做相同的操作

聚合函数，也可以称为多行函数、分组函数、组函数，它可以操作多行数据，并返回一个结果，这个结果是一个具有相同属性的小组，一般会结合着group分组来使用，当然也可以单独使用，那么默认全部数据就是一个小组。

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

哑表

Oracle中，有一张特殊的表：dualdual；它是一个单行单列的虚拟表，是Oracle内部自动创建的，这个表只有1列：DUMMY，数据类型为VERCHAR2(1)，dual表中只有一个数据'X'

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

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

select 1+1 from dual;

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

字符函数

函数

说明

ASCII(x)

返回字符x的ASCII码。

CONCAT(x,y)

连接字符串x和y。

INSTR(x, str [,start] [,n)

在x中查找str，可以指定从start开始，也可以指定从第n次开始。

LENGTH(x)

返回x的长度。

LOWER(x)

x转换为小写。

UPPER(x)

x转换为大写。

LTRIM(x[,trim_str])

把x的左边截去trim_str字符串，缺省截去空格。

RTRIM(x[,trim_str])

把x的右边截去trim_str字符串，缺省截去空格。

TRIM([trim_str FROM] x)

把x的两边截去trim_str字符串，缺省截去空格。

REPLACE(x,old,new)

在x中查找old，并替换为new。

SUBSTR(x,start[,length])

返回x的字串，从staart处开始，截取length个字符，缺省length，默认到结尾。

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

select concat('Hello','World') as result from dual;

//运行结果：

RESULT

--------------------

HelloWorld

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

select instr('Hello World','o') as result from dual;

//运行结果：

RESULT

----------

5

select instr('Hello World','o',-1) as result from dual;

//从后往前查找，从最后一个开始

//运行结果：

RESULT

----------

8

注意instr可以替换like模糊查询，instr(X,START)>0;说明能够查询得到，如果显示2就是在规定的字符串中，存在两个成功匹配的信息

INITCAP(X)，X首字母转换为大写，其他字母小写，通过空格进行转换

select initcap('bRIUP study') as result from dual;

//运行结果：

RESULT

----------

Briup Study

REPLACE(X,old,new)，在X中查找old，并替换成new，全局替换

select replace('hello tt world','tt','www') as result from dual;

//运行结果：

RESULT

----------------------------------

hello www world

这些函数是可以嵌套使用的

// 把first_name和last_name俩个列的值连接到一起，并且首字大写，其他子小写

select initcap(concat(first_name,last_name)) as namefrom s_emp;

数字函数

函数

说明

示例

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只会舍去不会进位

TRUNC(3.456，2)=3.45

日期函数

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

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

-- 显示当前时间

select sysdate from dual;

--显示时间:明天的这个时候

select sysdate + 1 from dual;

--显示时间:昨天的这个时候

select sysdate - 1 from dual;

--显示时间:1小时之后的这个日期

select sysdate + 1/24 from dual;

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

alter session set nls_language='simplified chinese';

select sysdate from dual;

SYSDATE

--------------

02-9月 -20

alter session set nls_language=english;

select sysdate from dual;

SYSDATE

------------

02-SEP-20

常见的日期函数

函数

说明

MONTHS_BETWEEN

俩个日期之间相差多少个月(单位是月)

ADD_MONTHS

返回一个日期数据：表示一个时间点，往后推x月的日期

NEXT_DAY

返回一个日期数据：表示一个时间点后的下一个星期几在哪一天

LAST_DAY

返回一个日期数据：表示一个日期所在月份的最后一天

ROUND

对日期进四舍五入，返回操作后的日期数据

TRUNC

对日期进行截取和round类似，但是只舍弃不进位

months_between

-- 30天之后和现在相差多少个月

select months_between(sysdate+30,sysdate) from dual;

--运行结果：

RESULT

----------

1

add_months

-- 指定日期，往后推2个月

select add_months('01-10月-2020',2) as result from dual;

-- 运行结果：

RESULT

--------------

01-12月-20

next_day

离当前时间最近的下一个星期5是哪一个天

select next_day(sysdate,'星期五') from dual;

round

-- 把当前日期四舍五入到月

select round(sysdate,'MONTH') from dual;

-- 把当前日期四舍五入到年

select round(sysdate,'YEAR') from dual;

转换函数

概述

转换函数主要有三种：

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

TO_NUMBER，把字符转换为数字

TO_DATE，把字符转换为日期

to_char

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

参数

示例

说明

9

999

指定位置处显示数字

.

9.9

指定位置返回小数点

,

99,99

指定位置返回一个逗号

$

$999

数字开头返回一个美元符号

EEEE

9.99EEEE

科学计数法表示

L

L999

数字前加一个本地货币符号

PR

999PR

如果数字式负数则用尖括号进行表示

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

格式

说明

yyyy

四位数的年份

rrrr

四位数的年份

yy

两位数的年份

rr

两位数的年份

mm

两位数的月份(数字)

D

一周的星期几

DD

一月的第几天

DDD

一年的第几天

YEAR

英文的年份

MONTH

英文全称的月份

mon

英文简写的月份

ddsp

英文的第几天(一个月的)

ddspth

英文序列数的第几天(一个月的)

DAY

全英文的星期

DY

简写的英文星期

hh

小时

mi

分钟

ss

秒

select to_char(sysdate,'yyyy mm MONTH mon MON D DD DDD DAY DY') from dual;

select to_char(sysdate,'dd-mm-yy') from dual;

select to_char(sysdate,'yy-mm-dd') from dual;

select to_char(sysdate,'yy-mm-dd hh:mi:ss') from dual;

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

select to_char(sysdate,'yy-mm-dd hh:mi:ss AM')from dual;

to_number

把字符转换为数字

select to_number('1000') from dual;

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

select to_number('abc') from dual;

to_date

把字符转换为日期

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

select to_date('25-5月-22','dd-month-yy') as result from dual;

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

select to_date('25-MAY-22','dd-MONTH-yy') as result from dual;

函数嵌套

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

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

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

多表查询

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

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

select count(*) from s_emp;

select count(*) from s_dept;

select count(*) from s_emp,s_dept;

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

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

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

等值连接

不等值连接

外连接

左外连接

右外连接

全连接

自连

等值连接

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

查询员工的名字、部门编号、部门名字

select last_name,dept_id,s_dept.id,name

from s_emp,s_dept

where s_emp.dept_id = s_dept.id;

不等值连接

工资等级表salgrade：

gradeName列表示等级名称

losal列表示这个级别的最低工资数

hisal列表示这个级别的最高工资数

-- 查询出员工的名字、职位、工资、工资等级名称

select e.last_name, e.title, e.salray, s.gradeName

from s_emp e, salgrade s

where e.salray

between s.losal and s.hisal

外连接

/*

连接查询

如需要多张数据表的数据进行查询,则可通过连接运算符实现多个查询

内连接 inner join

查询两个表中的结果集中的交集

外连接 outer join

左外连接 left join

(以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充)

右外连接 right join

(以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充)

等值连接和非等值连接

自连接

*/

-- 查询参加了考试的同学信息(学号,学生姓名,科目编号,分数)

SELECT * FROM student;

SELECT * FROM result;

/*思路:

(1):分析需求,确定查询的列来源于两个类,student result,连接查询

(2):确定使用哪种连接查询?(内连接)

*/

SELECT s.studentno,studentname,subjectno,StudentResult

FROM student s

INNER JOIN result r

ON r.studentno = s.studentno

-- 右连接(也可实现)

SELECT s.studentno,studentname,subjectno,StudentResult

FROM student s

RIGHT JOIN result r

ON r.studentno = s.studentno

-- 等值连接

SELECT s.studentno,studentname,subjectno,StudentResult

FROM student s , result r

WHERE r.studentno = s.studentno

-- 左连接 (查询了所有同学,不考试的也会查出来)

SELECT s.studentno,studentname,subjectno,StudentResult

FROM student s

LEFT JOIN result r

ON r.studentno = s.studentno

-- 查一下缺考的同学(左连接应用场景)

SELECT s.studentno,studentname,subjectno,StudentResult

FROM student s

LEFT JOIN result r

ON r.studentno = s.studentno

WHERE StudentResult IS NULL

-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数，三表查询)

SELECT s.studentno,studentname,subjectname,StudentResult

FROM student s

RIGHT JOIN result r

ON r.studentno = s.studentno

INNER JOIN `subject` sub

ON sub.subjectno = r.subjectno

口诀：我们想把表中的多出的一个数据查询并显示出来，那么就在另一张表上面添加一个加号(+)，意思就是查一下缺考的同学，那么说result中不存在当前学生考试成绩，为null，那么就要在null表上加上一个加号(+)

select s.studentno,studentname,subjectno,StudentResult

from student s,result r

where s.studentno = r.studentno(+);

全连接

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

select last_name,dept_id,name

from s_emp full outer

join s_dept

on s_emp.dept_id=s_dept.id

LAST_NAME DEPT_ID NAME

--------------- ---------- ---------------

Havel 45 Operations

Ropeburn 50 Administration

Velasquez 50 Administration

tom

st

可以看出，左右俩边的表中，新增的数据tom和st，原来等值连接不上，现在也全都被查询出来了。

自连接

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

例如，查询每个员工的名字以及员工对应的管理者的名字

select s1.last_name,s2.last_name manager_name

from s_emp s1,s_emp s2

where s1.manager_id = s2.id;

其实，可以给这一张表，起俩个不同的别名，然后当成俩张不同的表，进行查询就行了

操作结果集

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

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

union，取俩个结果集的并集

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

minus，第一个结果集除去第二个结果集和它相同的部分

intersect，求俩个结果集的交集

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

取俩个结果集的并集

select last_name,dept_id,name

from s_emp,s_dept

where s_emp.dept_id=s_dept.id(+)

union

select last_name,dept_id,name

from s_emp,s_dept

where s_emp.dept_id(+)=s_dept.id;

第一个结果集除去第二个结果集和它相同的部分

select last_name,dept_id,name

from s_emp,s_dept

where s_emp.dept_id=s_dept.id(+)

minus

select last_name,dept_id,name

from s_emp,s_dept

where s_emp.dept_id(+)=s_dept.id;

rownum

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

rownum只有Oracle数据中才有。

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

image-20200916113038625.png

伪列，可以根据查询结果的条数，自动生成，并且一定是从1开始连续不断的数字

伪列rownum的本质就是给查询的一行行结果标上行号

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

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

select last_name

from s_emp

where rownum=1

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

select last_name

from s_emp

where rownum>0

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

rownum可以小于任何数

Oracle数据库中伪列rownum最核心的作用就是：完成分页查询

本文标签： oracle求两边查集 oracle（二）函数多表查询结果集伪列