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

2020-09-22 本文已影响0人徒手說梦话

单行函数

概述

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最核心的作用就是：完成分页查询