本文主要是介绍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(二)函数、多表查询、结果集、伪列的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!