1 单行函数

1.1 概述

oracle数据库中，内置了很多常用的函数，整体分为：
1. 单行函数
        字符函数
        日期函数
        数字函数
2. 转换函数
3. 聚合函数

单行函数，也可以称为单值函数，每操作一行数据（某个字段值），都会返回一个结果
例如，查询id小于5的员工信息（id、last_name、salary）
 

select id,last_name,salary
from s_emp
where id<5;
//运行结果：
ID LAST_NAME SALARY
---------- --------------- ----------
1 Velasquez 2500
2 Ngao 1450
3 Nagayama 1400
4 Quick-To-See 1450

例如，使用单行函数，将上面的结果中每一个last_name转换为大写
 

select id,upper(last_name) as last_name,salary
from s_emp
where id<5;
//运行结果：
ID LAST_NAME SALARY
---------- --------------- ----------
1 VELASQUEZ 2500
2 NGAO 1450
3 NAGAYAMA 1400
4 QUICK-TO-SEE 1450

可以看到，单行函数，对每一行中的last_name字段值都进行了操作，并返回了转换结果
 

聚合函数，也可以称为多行函数、分组函数、组函数，它可以操作多行数据，并返回一个结果，一般会。


结合着group分组来使用，当然也可以单独使用，那么默认全部数据就是一个小组。
例如，查询id小于5的所有员工的平均工资
 

select avg(salary)
from s_emp
where id<5;
//运行结果：
AVG(SALARY)
-----------
1700

例如，查询每个部分的员工人数、以及该部门的平均工资，并且按照平均工资的降序排序
 

select dept_id,count(*),avg(salary) as avgSalary
from s_emp
group by dept_id
order by avgSalary desc;
//运行结果：
DEPT_ID COUNT(*) AVGSALARY
---------- ---------- ----------
50 2 2025
33 1 1515
32 1 1490
10 1 1450
35 1 1450
31 2 1400
41 4 1247.5
34 2 1160
45 3 1089
42 3 1081.66667
44 2 1050
DEPT_ID COUNT(1) AVGSALARY
---------- ---------- ----------
43 3 900

可以看出，聚合函数，可以操作多行数据，并按要求计算出一个结果，一般会结合分组操作，把一堆数据按照某个条件，划分为几个不同的小组，然后对每个小组执行聚合函数，最后每个小组得出一个结果。
转换函数，可以将一个类型的数据转换为另一种类型的数据
例如，把一个字符转换为数字
 

select to_number('1000')
from dual;

1.2 哑表

Oracle中，有一张特殊的表：dual
dual被称之为哑表，它是一个单行单列的虚拟表，是Oracle内部自动创建的，这个表只有1列：DUMMY，数据类型为VERCHAR2(1)，dual表中只有一个数据'X'，Oracle有内部逻辑保证dual表中永远只有一条数据。

 注意，dummy单词的意思为：假人；哑巴；虚拟

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

select 1+1 from dual;
//运行结果：
1+1
----------
2

注意，按照sql语句的要求，没有表就没法查询，而表达式1+1，不属于任何表，那么就有了哑表dual的概念了
注意，只有oracle数据库中有这个哑表 dual


1.3 字符函数

常用的字符函数有：

 ASCII(X) ，返回字符X的ASCII码

select ascii('a') as result from dual;
//运行结果：
RESULT
----------
97

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',6) as result from dual;
//从前往后查找，从下标为6开始
//运行结果：
RESULT
----------
8
select instr('Hello World','o',-1) as result from dual;
//从后往前查找，从最后一个开始
//运行结果：
RESULT
----------
8

LENGTH(X) ， 返回X的长度
 

select length('Hello World') as result from dual;
//运行结果：
RESULT
----------
11

LOWER(X) ，X转换成小写
 

select lower('Hello World') as result from dual;
//运行结果：
RESULT
----------------------
hello world

UPPER(X) ，X转换成大写
 

select upper('Hello World') as result from dual;
//运行结果：
RESULT
----------------------
HELLO WORLD

INITCAP(X) ，X首字母转换为大写，其他字母小写
 

select initcap('bRIUP') as result from dual;
运行结果：
RESULT
----------
Briup

LTRIM(X[,TRIM_STR]) ，把X的左边截去trim_str字符串，缺省截去空格

select ltrim('=Hello=','=') as result from dual;
//运行结果：
RESULT
------------
Hello=

RTRIM(X[,TRIM_STR]) ，把X的右边截去trim_str字符串，缺省截去空格
 

select rtrim('=Hello=','=') as result from dual;
//运行结果：
RESULT
------------
=Hello

TRIM([TRIM_STR FROM]X) ，把X的两边截去trim_str字符串，缺省截去空格
 

select trim('=' FROM '=Hello=') as result from dual;
//运行结果：
RESULT
------------
Hello

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

select replace('hello tt world','tt','briup') as result from dual;
//运行结果：
RESULT
----------------------------------
hello briup world

SUBSTR(X,start[,length]) ，返回X的字串，从start处开始，截取length个字符，缺省length，默认到结尾
 

select substr('briup',2,3) as result from dual;
//运行结果：
RESULT
------
riu

其他的一些案例：
例如，把s_emp表中的last_name列的值转换为小写
 

select lower(last_name)
from s_emp;

例如，把s_emp表中的last_name列的值转换为大写
 

select upper(last_name)
from s_emp;

例如，查询s_emp表中名字为NGAO的人信息
 

select last_name,salary,dept_id
from s_emp
where upper(last_name)='NGAO';

例如，获得s_emp表中last_name列的每个值的字符长度
 

select last_name,length(last_name)
from s_emp;

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

select initcap(concat(first_name,last_name)) as name
from s_emp;

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


1.4 数字函数

专门操作数字的函数，常用的数字函数有：

 ROUND(X[,Y]) ，X在第Y位四舍五入
第一个参数表示要进行四舍五入操作的数字
第二个参数表示保留到哪一位

//保留到小数点后面2位
select round(45.923,2) as result
from dual;
//运行结果：
RESULT
----------
45.92
//保留到个位
select round(45.923,0) as result
from dual;
//运行结果：
RESULT
----------
46

注意，如果第二个参数不写，默认就是0，表示保留到个位

//保留到十位
select round(45.923,-1) as result
from dual;
//运行结果：
RESULT
----------
5

TRUNC(X[,Y]) ，X在第Y位截断
trunc和round的用法一样，但是trunc只会舍去不会进位
 

//截取到小数点后面2位
select trunc(45.929,2) as result
from dual;
//运行结果：
RESULT
----------
45.92

MOD(X,Y) ，X除以Y的余数
第一个参数表示要进行取余操作的数字
第二个参数表示参数1和谁取余

//把10和3进行取余 (10除以3然后获取余数)
select mod(10,3) as result
from dual;
//运行结果：
RESULT
----------
1

1.5 日期函数

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

//显示当前时间
select sysdate from dual;

例如，

//显示时间:明天的这个时候
select sysdate + 1 from dual;
//显示时间:昨天的这个时候
select sysdate - 1 from dual;
//显示时间:1小时之后的这个日期
select sysdate + 1/24 from dual;

注意， sysdate 参与时间的加减操作的时候，单位是天
特别注意，oracle中不同的会话环境中，日期数据默认的格式也不同
例如，中文环境下：
 

SQL> alter session set nls_language='simplified chinese';
会话已更改。
SQL> select sysdate from dual;
SYSDATE
--------------
02-9月 -20
SQL>

注意，观察中文环境的会话中的默认日期格式的特点。
例如，英文环境下：
 

SQL> alter session set nls_language=english;
Session altered.
SQL> select sysdate from dual;
SYSDATE
------------
02-SEP-20
SQL>

注意，观察英文环境的会话中的默认日期格式的特点。
英文环境下常见的一些单词及简写

 常见的日期函数：

 months_between

例如，30天之后和现在相差多少个月

select months_between(sysdate+30,sysdate) as result
from dual;
//运行结果：
RESULT
----------
1

add_months
例如，指定日期，往后推2个月
 

alter session set nls_language='simplified chinese';
select add_months('01-10月-2020',2) as result
from dual;
//运行结果：
RESULT
--------------
01-12月-20
alter session set nls_language=english;
select add_months('01-OCT-2020',2) as result
from dual;
//运行结果：
RESULT
------------
01-DEC-20

注意，这个数字也可以是负数，表示之前多少月


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

alter session set nls_language='simplified chinese';
select next_day(sysdate,'星期五')
from dual;
alter session set nls_language=english;
select next_day(sysdate,'FRIDAY')
from dual;

last_day
例如，当前日期所在月份的最后一天(月底)
 

select last_day(sysdate)
from dual;

round
例如，把当前日期四舍五入到月
 

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

例如，把当前日期四舍五入到年
 

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

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

select trunc(sysdate,'MONTH')
from dual;
select trunc(sysdate,'YEAR')
from dual;

2 转换函数

2.1 概述

转换函数，可以将一个类型的数据，转换为另一种类型的数据。
转换函数主要有三种：
1. TO_CHAR ，把一个数字或日期数据转换为字符
2. TO_NUMBER ，把字符转换为数字
3. TO_DATE ，把字符转换为日期


2.2 to_char

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

 例如，几个转换的例子

select to_char(salary,'$999,999.00') as result
from s_emp;
//运行结果：
RESULT
------------------------
$2,500.00
$1,450.00
$1,400.00
$1,450.00
$1,550.00
select to_char(salary,'L999,999.00') as result
from s_emp;
//运行结果：
RESULT
------------------------------------------
￥2,500.00
￥1,450.00
￥1,400.00
￥1,450.00
￥1,550.00
......
select to_char(-10,'999PR') as result
from dual;
//运行结果：
RESULT
----------
<10>
1 2 3 4 5 6 7

日期转为字符的常用格式

 例如，几个转换的例子

 注意，这里虽然写的是AM，但是它是一个变化的值，下午的时候就会自动显示为PM

2.3 千年虫

在早期的计算机的程序中，规定了的年份仅用两位数来表示。
假如是1971年，在计算机里就会被表示为71。
但是到了2000年的时候这个情况就出现了问题，计算机读取出之前存的一个日期88，结果就是2088年。
这样的话，计算机内部对年份的计算就会出现问题，这个事情当时被称为千年虫
当时还是专门有部电视剧，就是讲述了一个和千年虫有关系的事情。

 

 

例如，新建表t_test进行测试
 

create table t_test(
id number,
dob date
);
alter session set nls_language=english;
insert into t_test(id,dob) values(1,'01-OCT-89');
insert into t_test(id,dob) values(2,to_date('01-10-89','dd-mm-yy'));
insert into t_test(id,dob) values(3,to_date('01-10-89','dd-mm-rr'));
commit;
//测试完,可以使用下面语句删除表
drop table t_test;

例如，进行查询，直接显示dob字段值
 

select id,dob from t_test;
//查询结果：
ID DOB
---------- ------------
1 01-OCT-89
2 01-OCT-89
3 01-OCT-89

例如，进行查询，把dob字段的值进行转换
 

select id,to_char(dob,'yyyy') as year from t_test;
//查询结果：
ID YEAR
---------- --------
1 1989
2 2089
3 1989

 思考，rr格式并没有完全的解决俩位数年份保存的问题，这里还存在什么问题？

2.4 to_number

 2.5 to_date

 

 思考，oracle数据库中，表示一个日期数据有哪些常见的方式？
        使用 sysdate
        使用oracle默认的日期格式，使用字符串表示一个日期
        例如，'25-5月-22' 或者 '25-MAY-22'
        使用日期函数 ADD_MONTHS/NEXT_DAY/LAST_DAY/ROUND/TRUNC
        使用转换函数 to_date

2.6 函数嵌套

以上介绍的常用函数，在数据类型正确的情况下，是可以嵌套使用的。
例如，先把'hello'和'world'连接起来，再转换为全部字母大写，然后再从第4个字符开始，连着截取4个字符

 

3 多表查询

 3.1 笛卡尔积

在数学中，两个集合X和Y的笛卡尓积（Cartesian product），又称直积，表示为X × Y
例如，假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b,2)}。

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

select count(*)
from s_emp;
select count(*)
from s_dept;
select count(*)
from s_emp,s_dept;

 

 

 3.2 等值连接

 3.3 不等值连接

 

 

col last_name for a15
col name for a15
select last_name,dept_id,name
from s_emp,s_dept
where s_emp.dept_id=s_dept.id;
//运行结果：
LAST_NAME DEPT_ID NAME
--------------- ---------- ---------------
Velasquez 50 Administration
Ngao 41 Operations
Nagayama 31 Sales
Quick-To-See 10 Finance
Ropeburn 50 Administration
Urguhart 41 Operations
Menchu 42 Operations
Biri 43 Operations
Catchpole 44 Operations
Havel 45 Operations
Magee 31 Sales
LAST_NAME DEPT_ID NAME
--------------- ---------- ---------------
Giljum 32 Sales
Sedeghi 33 Sales
Nguyen 34 Sales
Dumas 35 Sales
Maduro 41 Operations
Smith 41 Operations
Nozaki 42 Operations
Patel 42 Operations
Newman 43 Operations
Markarian 43 Operations
Chang 44 Operations
LAST_NAME DEPT_ID NAME
--------------- ---------- ---------------
Patel 34 Sales
Dancs 45 Operations
Schwartz 45 Operations
25 rows selected

可以看出，新增加的员工tom和新增的部门st都没被查询出来


3.4.1 左外连接

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

//这里的outer，是可以省去不写
select last_name,dept_id,name
from s_emp left outer join s_dept
on s_emp.dept_id=s_dept.id;
//运行结果：
LAST_NAME DEPT_ID NAME
--------------- ---------- ---------------
Quick-To-See 10 Finance
Magee 31 Sales
Nagayama 31 Sales
Giljum 32 Sales
Sedeghi 33 Sales
Patel 34 Sales
Nguyen 34 Sales
Dumas 35 Sales
Smith 41 Operations
Maduro 41 Operations
Urguhart 41 Operations
LAST_NAME DEPT_ID NAME
--------------- ---------- ---------------
Ngao 41 Operations
Patel 42 Operations
Nozaki 42 Operations
Menchu 42 Operations
Markarian 43 Operations
Newman 43 Operations
Biri 43 Operations
Chang 44 Operations
Catchpole 44 Operations
Schwartz 45 Operations
Dancs 45 Operations
LAST_NAME DEPT_ID NAME
--------------- ---------- ---------------
Havel 45 Operations
Ropeburn 50 Administration
Velasquez 50 Administration
tom
26 rows selected.

 

3.4 2 右外连接

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

/这里的outer，是可以省去不写
select last_name,dept_id,name
from s_emp right outer join s_dept
on s_emp.dept_id=s_dept.id;
//运行结果：
LAST_NAME DEPT_ID NAME
--------------- ---------- ---------------
Velasquez 50 Administration
Ngao 41 Operations
Nagayama 31 Sales
Quick-To-See 10 Finance
Ropeburn 50 Administration
Urguhart 41 Operations
Menchu 42 Operations
Biri 43 Operations
Catchpole 44 Operations
Havel 45 Operations
Magee 31 Sales
LAST_NAME DEPT_ID NAME
--------------- ---------- ---------------
Giljum 32 Sales
Sedeghi 33 Sales
Nguyen 34 Sales
Dumas 35 Sales
Maduro 41 Operations
Smith 41 Operations
Nozaki 42 Operations
Patel 42 Operations
Newman 43 Operations
Markarian 43 Operations
Chang 44 Operations
LAST_NAME DEPT_ID NAME
--------------- ---------- ---------------
Patel 34 Sales
Dancs 45 Operations
Schwartz 45 Operations
st
26 rows selected.

 3.4.3 全连接

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

//这里的outer，是可以省去不写
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
--------------- ---------- ---------------
Quick-To-See 10 Finance
Magee 31 Sales
Nagayama 31 Sales
Giljum 32 Sales
Sedeghi 33 Sales
Patel 34 Sales
Nguyen 34 Sales
Dumas 35 Sales
Smith 41 Operations
Maduro 41 Operations
Urguhart 41 Operations
LAST_NAME DEPT_ID NAME
--------------- ---------- ---------------
Ngao 41 Operations
Patel 42 Operations
Nozaki 42 Operations
Menchu 42 Operations
Markarian 43 Operations
Newman 43 Operations
Biri 43 Operations
Chang 44 Operations
Catchpole 44 Operations
Schwartz 45 Operations
Dancs 45 Operations
LAST_NAME DEPT_ID NAME
--------------- ---------- ---------------
Havel 45 Operations
Ropeburn 50 Administration
Velasquez 50 Administration
tom
st

 

3.5 自连接

 

 4 操作结果集

4.1 概述

 4.2 union

例如，取俩个结果集的并集

 4.3 union all

 4.4 minus

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

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;
//运行结果：
LAST_NAME DEPT_ID NAME
--------------- ---------- ---------------
tom

交换俩个结果的位置，运行后对比效果：

 

4.5 intersect

例如，求俩个结果集的交集

 5 rownum

 

 

 

 

# sql # 数据库 # database
Logo
腾讯云开发者社区

腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。

更多推荐

Js分割字符串(单个分割符、多个分割符、正则)

1.单个分割符1.1定义和用法split() 方法用于把一个字符串分割成字符串数组。1.2语法stringObject.split(separator,howmany)参数描述separator必需。字符串或正则表达式，从该参数指定的地方分割 stringObject。howmany可选。该参数可指定返回的数组的最大长度。如果设置了该参数，返回的子串不会多于这个参数指定的数组。如果没有设置该参数，

avatar 腾讯云开发者社区

curl

什么是curl命令？curl是利用URL语法在命令行方式下工作的开源文件传输工具。它被广泛应用在Unix、多种Linux发行版中，并且有DOS和Win32、Win64下的移植版本。如何在windows下使用curl命令？第一步： 进入curl下载官网，下载合适的版本，我这里下载的是windows 64位的curl。其中我下载的zip文件。 另外CAB文件也是压缩文件，这...

avatar 腾讯云开发者社区

Zynq7000 USB2.0协议解析及USB控制器详解

USB 2.0规范及控制器文章目录USB 2.0规范及控制器USB2.0Univerasl Serial BusUSBHostUSB总线接口标准总线信号USB拓扑结构数据流模型数据编解码和位填充USB逻辑部件USB时间基准USB 描述符USB设备USB设备供电方式USB设备分层USB设备插入检测机制USB设备状态USB总线枚举USB传输传输类型包(Packet)事务(transaction)传输(

avatar 腾讯云开发者社区