Oracle数据库学习总结_sql应用基础之相关函数

最新推荐文章于 2023-06-20 15:15:00 发布

就是爱吃肉ro

最新推荐文章于 2023-06-20 15:15:00 发布

阅读量822

点赞数 1

分类专栏： # Database

本文链接：https://blog.csdn.net/weixin_44510468/article/details/102807141

版权

Database 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

本文总结了Oracle数据库中的SQL应用基础，重点介绍了单行函数，包括数值型、字符型、日期型和转换函数，如abs、concat、add_months、to_char等，并给出了各函数的使用示例。

摘要由CSDN通过智能技术生成

文章目录

[一. 二. 三. 四. 增删改查, 点击跳转](https://blog.csdn.net/weixin_44510468/article/details/102794315)
五. 相关函数

一. 二. 三. 四. 增删改查, 点击跳转

五. 相关函数

1>. 单行函数

1. 数值型函数

(1 ) abs( number );

select abs(5) from dual;

( 2 ) ceil( number ); floor( number );

select ceil( 7.1 ) from dual; # 8
select floor( 5.2 ) from dual; # 5

( 3 ) mod( number, divisor );

select mod( 7, 2 ) from dual; # 1
select mod( 7, 7 ) from dual; # 0

( 4 ) round( number, precision ); trunc( number, precision );

select round( 5.446, 2 ) from dual; # 5.45
select trunc( 5.446, 2 ) from dual; # 5.44

2. 字符函数

( 1 ) concat( str1, str2 );

select concat( 'This is', 'test.' ); # This is test.

( 2 ) instr( string, substring[, start[, occurrence]] );

select instr( 'AAABAAAB', 'B' ) from dual; # 1
select instr( 'AAABAAAB', 'B', 1, 2) from dual; # 8

( 3 ) lower( string ); upper( string );

select lower( 'ORACLE_STUDY' ) from dual; # oracle_study
select upper( 'oracle_study' ) from dual; # ORACLE_STUDY

( 4 ) lpad( string, length[, padding] ); rpad( string, length[, padding] );

select lpad( 'oracle', 10 ) from dual; 	  # |    oracle|
select lpad( 'oracle', 10, '*' ); from dual # |****oracle|
select rpad( 'oracle', 10 ) from dual; 	  # |oracle    |
select rpad( 'oracle', 10, '*' ); from dual # |oracle****|

( 5 ) ltrim( string[, trimchars] ); rtrim( string[, trimchars] ); trim( [leading|trailing|both] [ trimchar from] string );

需要注意, trim 只能删除单个字符, 而 ltrim 和 rtrim 可以删除字符串
例如, select trim( both ‘323’ from '323abcdefg );
会报错 ORA-30001: 截取集仅能有一个字符
但是 ltrim 和 rtrim 都是删除的前缀/后缀, 而非整个字符串中的所有

select ltrim( '    abcdefg' ) from dual; 	  # |abcdefg
select ltrim( '****abcdefg', '*' ) from dual; # |abcdefg
select rtrim( 'abcdefg    ' ) from dual; 	  # |abcdefg
select rtrim( 'abcdefg****', '*' ) from dual; # |abcdefg

select trim( both ' ' from '    abcdefg' ) from dual;     # |abcdefg
select trim( leading ' ' from '    abcdefg' ) from dual;  # |abcdefg
select trim( trailing ' ' from 'abcdefg    ' ) from dual; # |abcdefg

( 6 ) substr( str, start[, length] );

注意 , 第一位是1, 不是0, 负数是从后开始,

select substr( 'myOracleStudy', 3, 6 ) from dual;   # Oracle
select substr( 'myOracleStudy', -11, 6 ) from dual; # Oracle

( 7 ) replace( str, sub[, replacement] );

若缺省 [replacement参数, 会默认用空来替换父串

select replace( 'I have a big apple, a little apple.', 'apple', 'banana' ) from dual;
# I have a big banana, a little apple.
select replace( 'I have a big apple, a little apple.', 'apple' ) from dual;
# I have a big , a little .

3. 日期函数

( 1 ) add_months( date, months );

这个函数比较智能, 如果开始日期是某月的最后一天, 那么加完几个月以后也会自动调整到最后一天

select add_months( to_date( '10/29/2019','MM/DD/YY' ), 13 ) from dual; # 11/29/2020
select add_months( to_date( '2/28/2019', 'MM/DD/YY' ), 1 ) from dual;  # 3/31/2019

( 2 ) current_date; sysdate;

注意 current_date 会根据时区转换, 而 sysdate 则是这个操作系统时间

alter session set nls_date_format = 'YYYY-MM-DD';

select current_date from dual;

select sysdate from dual;

( 3 ) extract( datepart from datetime );

datepart, 指定被提取的日期部件, 如, year, month, day, hour等
datetime, 日期类型
如果系统时间是 2019年10月29日, 那么, 结果如下

select extract( year from sysdate ) from dual; # 2019
select extract( month from sysdate ) from dual # 10

( 4 ) months_between( date1, date2 );

如果两个日期对应日号相同或者两个日期都是某月的最后一天, 返回一个整数
否则返回小数

select months_between( 
	to_date( '2019-10-29', 'yyyy-mm-dd' ), 
	to_date( '2019-11-11', 'yyyy-mm-dd' ) 
from 
	dual;
# emm... 待测试, 按理论来说应该是零点几几
select months_between( 
	to_date( '2019-10-29', 'yyyy-mm-dd' ), 
	to_date( '2020-10-29', 'yyyy-mm-dd' ) 
from 
	dual;
# 这个可以保证是 12 , &_&

4. 转换函数

( 1 ) to_char( number[, fmt[, nlsparams] ] ); to_char( date[, fmt[, nls_date_language=language] ] )

# 数值型 -> 字符型

select to_char( 999.999 ) from dual; # 999.99
select to_char( 123456789.99, '$999999999.99' ) from dual; # $123456789.99
# 至于 99999999.99 能否换成别的数字, ... emm, 待尝试

# 日期型 -> 字符型

select to_char( sysdate, 'dd-Mon-yyyy' ) from dual; 
# 29-19月-2019
select to_char( sysdate, 'dd-Mon-yyyy', 'nls_date_language=English' ) from dual; 
# 29-MAR-2019
select to_char( sysdate, 'yyyy''年''MM''月''DD'日' ) from dual;
# 2019年10月26日

贴上常用的日期格式模型
在这里插入图片描述

( 2 ) to_date( string[, [fmt[, nls_date_language=language’] ] );

select to_date( '10/29/2019', 'MM/DD/YY' ) from dual;
# 2019-10-29
select to_date( '10/29/2019', 'DD/MM/YY' ) from dual;
# 2019-10-29

( 3 ) to_number( string[, fmt[, ‘nlsparanms’] ] );

# 字符型 -> 数值型

select to_number( '666.66' ) from dual; # 666.66
select to_number( '$123.99', '$999.99' ) from dual; # 123.99

5. 其他函数

( 1 ) decode( input, value, result[, value[, result…], [default_result] );

select 
	emp_name as 雇员姓名,
	salary as 雇员工资, 
	decode( dep_no, 01, 'technical', 02, 'logistics', 03, 'after-sales', null 'None', 'Others') 部门性质
from 
	employee 
where 
	slary > 9000;
# 注意这个 Others 是指定的默认值, 比如有个部门编号是 04, 而部门编号是 04 的时候显示什么我没有指定, 所以就显示默认值 Others

# 统计输出 1980, 1981, 1982 入职的雇员人数
select 
	to_char( trunc( hiredate, 'year'), 'yyyy' ) as 年份, 
	count( * ) as 人数,
from 
	employee
where 
	to_char( trunc( hiredate, 'year' ), 'yyyy' ) in ( '2019', '2020', '2021' )
group by 
	to_char( trunc( hiredate, 'year' ), 'yyyy' );

# 将上述查询显示成 3 列, 可以使用 decode 函数
select  
	sum( decode ( to_char( trunc( hiredate, 'year' ), 'yyyy' ), '2019', 1, 0 ),
	sum( decode ( to_char( trunc( hiredate, 'year' ), 'yyyy' ), '2020', 1, 0 ),
	sum( decode ( to_char( trunc( hiredate, 'year' ), 'year' ), '2021', 1, 0 ),
from 
	employee
where 
	to_char( trunc( hiredate, 'year' ), 'yyyy' ) in ( '2019', '2020', '2021' );

( 2 ) nvl( express1, expreess2 );

如果结果非空, 则显示 express1, 结果为空, 就显示 express2

select 
	emp_name as 雇员姓名,
	location as 雇员地址,
	nvl( location, '此雇员暂未填写地址...' )
from 
	emp
where 
	dep_no = 30;

2>. 数据分组

1. 分组函数 ( 注意, 忽略 null 行 )

( 1 ) cout( [distinct | all] expression );

缺省值为 all

select 
	count( all employ  ) as 雇员人数, 
	count( all department ) as 部门数量 
from 
	emp;

( 2 ) avg( [distinct | all] expression ); sum( [distinct | all] expression );

// 缺省值为 all

select 
	avg( salary ) as 平均薪水, 
	sum( sal ) as 总薪水,
from emp;

# 查询工资高于本部门平均薪水的雇员信息

# 实现方式 1
select 
	emp_name as 雇员姓名,
	salary as 雇员薪水
from 
	employee as e1 
where 
	e1.salry > ( select 
						avg( salry ) 
					from 
						employee as e2 
					where 
						e2.dep_no = e1.dep_no
					);
# 实现方式 2 
select 
	emp_name as 雇员姓名,
	salary as 雇员薪水
from 
	employee, 
	( select 
		dep_no, 
		avg( salry ) avgSalary 
		from 
			employee 
		group by dep_no ) temp_table )
where 
	employee.dep_no = temp_table.dep_no 
	and 
	employee.salary > temp_table.avgSalary;

( 3 ) max( [distinc | all] expression ); min( [distinc | all] expression );

// 缺省值为 all

select 
	max( salary ) as 最高工资,
	min( salary ) as 最低工资
from 
	employee;

2. 分组语句

数据分组的限制

含有 group by 的语句, 在 select语句中出现的列, 必须出现在 group语句中;
使用分组函数时, 忽略 null 行
分组函数只能出现在 select, order by, having 语句中
order by 用于对查询结果进行排序, 必须放在分组语句之后

select 
	columns_,
	group_function 			# 指定统计函数
from table
[where condition]        
[group By columns_]			# 指定分组依据的列
[having group_condition];	# 用于过滤分组后的数据显示

( 1 ) group by

数据分组

# 查询显示 不同部门的雇员人数以及平均工资
select 
	dep_no as 部门编号,
	count( emp_name ) as 雇员人数, 
	trunc( avg( salary ), 2 ) 平均工资,
from 
	employee 
group by 
	dep_no;

# 2 查询显示 不同部门不同岗位的雇员人数及平均工资
select
	dep_no as 部门编号,
	job as 工作岗位,
	cout( emp_name ) as 雇员人数,
	trunc( avg( salary ), 2 ) as 平均工资
from 
	employee
group by 
	dep_no, 
	job
order by 
	job asc;

( 2 ) having

# 查询显示 部门平均工资 2000 以上的部门雇员人数以及平均工资
select 
	dep_n as 部门编号, 
	count( emp_name ) as 雇员人数,
	trunc( avg( salary ), 2 ) 平均工资
from 
	employee
group by 
	dep_no
having 
	trunc( avg( salary ), 2 ) > 2000;

总结

# 统计学生总人数
select 
    count( * )
from 
    student;

# 统计男生人数
select 
    count( * ) 
from 
    student
where 
    sex = '男';

# 统计男生女生各有多少人
select 
    sex as 性别,
    count( * ) as 人数
from 
    studetnt
group by sex

# 统计 计算数学 和 通信工程 专业, 男女各多少人
select 
    major as 专业,
    sex as 性别,
    count( * ) as 人数
from 
    student
where 
    major in ( '计算数学', '通信工程' )
group by major, sex;

# 统计各专业学生人数, 仅显示人数在 6 人一下的结果
select 
    major as 专业,
    count( * ) as 人数
from 
    student
group by major,
having count( * ) < 6;

# 统计 音乐, 计算数学, 通信工程 三个个专业人数, 仅显示人数不足三人的结果
select
    major as 专业,
    count( * ) as 人数,
where 
    major in ( '音乐', '计算数学', '通信工程' )
group by 专业
having count( * ) < 3
order by count( * );