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

最新推荐文章于 2022-05-10 17:08:52 发布

1360785665

最新推荐文章于 2022-05-10 17:08:52 发布

阅读量213

点赞数

文章标签： 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

----------

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

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

//运行结果：

RESULT

----------

注意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

----------

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

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

参数

示例

说明

999

指定位置处显示数字

9.9

指定位置返回小数点

99,99

指定位置返回一个逗号

$999

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

EEEE

9.99EEEE

科学计数法表示

L999

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

999PR

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

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

格式

说明

yyyy

四位数的年份

rrrr

四位数的年份

两位数的年份

两位数的月份(数字)

一周的星期几

一月的第几天

DDD

一年的第几天

YEAR

英文的年份

MONTH

英文全称的月份

mon

英文简写的月份

ddsp

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

ddspth

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

DAY

全英文的星期

简写的英文星期

小时

分钟

秒

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):确定使用哪种连接查询?(内连接)