单行函数
概述
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最核心的作用就是：完成分页查询
网友评论