- 浏览: 313381 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
zhangliguoaccp:
对于女人不要太看重吧,喜欢你的自然留下,你若盛开,蝴蝶自来!
遇见她 -
yiqi1943:
springtest支持的spring版本最低是多少啊
Spring Test -
WITLP:
爱,我只知道你一部分的事情,没想到你从华智出来这么坎坷
2009 为什么待到毕业时? -
WITLP:
哈哈,原来你就是传说中的欧阳平?
ANT 简单使用 -
bo_hai:
谢谢。总结的很好。
工具 PL/SQL 快捷键
sql 基本函数(oracle)
1 数值型函数
abs:求绝对值函数,如:abs(?5) 5
sqrt:求平方根函数,如:sqrt(2) 1.41421356
power:求幂函数,如:power(2,3) 8
cos:求余弦三角函数,如:cos(3.14159) ?1
mod:求除法余数,如:mod(1600, 300) 100
ceil:求大于等于某数的最小整数,如:ceil(2.35) 3
floor:求小于等于某数的最大整数,如:floor(2.35) 2
round:按指定精度对十进制数四舍五入,如:round(45.923, 1),结果,45.9
round(45.923, 0),结果,46
round(45.923, ?1),结果,50
trunc:按指定精度截断十进制数,如:trunc(45.923, 1),结果,45.9
trunc(45.923),结果,45
trunc(45.923, ?1),结果, 40
2 字符型函数
ascii:获得字符的ASCII码,如:Ascii('A') 65
chr:返回与ASCII码相应的字符,如:Chr(65) A
lower:将字符串转换成小写,如:lower ('SQL Course') sql course
upper:将字符串转换成大写,如:upper('SQL Course') SQL COURSE
initcap:将字符串转换成每个单词以大写开头,如:initcap('SQL course') Sql Course
concat:连接两个字符串 concat('SQL', ' Course') SQL Course
substr:给出起始位置和长度,返回子字符串,如:substr('String',1,3) Str
length:求字符串的长度 length('Wellcom') 7
instr:给出起始位置和出现的次数,求子字符串在字符串中出现的位置,如:instr('String', 'r',1,1) 3
lpad:用字符填充字符串左侧到指定长度,如:lpad('Hi',10,'-') --------Hi
rpad:用字符填充字符串右侧到指定长度,如:rpad('Hi',10,'-') Hi--------
trim:在一个字符串中去除另一个字符串,如:trim('S' FROM 'SSMITH') MITH
replace:用一个字符串替换另一个字符串中的子字符串,如:replace('ABC', 'B', 'D') ADC
3 日期函数
months_between:返回两个日期间的月份,如:months_between ('04-11月-05','11-1月-01'),结果,57.7741935
add_months:返回把月份数加到日期上的新日期,如:add_months('06-2月-03',1),结果,06-3月-03
add_months('06-2月-03',-1),结果,06-1月-03
next_day:返回指定日期后的星期对应的新日期,如:next_day('06-2月-03','星期一'),结果,10-2月-03
last_day:返回指定日期所在的月的最后一天,如:last_day('06-2月-03'),结果,28-2月-03
round:按指定格式对日期进行四舍五入,如:round(to_date('13-2月-03'),'YEAR'),结果,01-1月-03
round(to_date('13-2月-03'),'MONTH'),结果,01-2月-03
round(to_date('13-2月-03'),'DAY'),结果,16-2月-03
(按周四舍五入)
trunc:对日期按指定方式进行截断,如:trunc(to_date('06-2月-03'),'YEAR'),结果,01-1月-03
trunc(to_date('06-2月-03'),'MONTH'),结果,01-2月-03
trunc(to_date('06-2月-03'),'DAY'),结果,02-2月-03
EXTRACT()抽取当前日期的年月日:注意时分秒是抽取不到的
4 转换函数
Oracle的类型转换分为自动类型转换和强制类型转换。常用的类型转换函数有TO_CHAR、TO_DATE或TO_NUMBER
To_char:转换成字符串类型,如:To_char(1234.5, '$9999.9'),结果:$1234.5
To_date:转换成日期类型,如:To_date('1980-01-01', 'yyyy-mm-dd'),结果:01-1月-80
To_number:转换成数值类型,如:To_number('1234.5'),结果:1234.5
5 自动类型转换
Oracle可以自动根据具体情况进行如下的转换:
* 字符串到数值。
* 字符串到日期。
* 数值到字符串。
* 日期到字符串。
以下是自动转换的训练。
自动转换字符型数据到数值型。
6 日期类型转换
将日期型转换成字符串时,可以按新的格式显示。
如格式YYYY-MM-DD HH24:MI:SS表示“年-月-日 小时:分钟:秒”。Oracle的日期类型是包含时间在内的。
主要的日期格式字符的含义
AM、PM:上午、下午,如:08 AM
D:数字表示的星期(1~7),如:1,2,3,4,5,6,7
DD:数字表示月中的日期(1~31),如:1,2,3,…,31
MM:两位数的月份,如:01,02,…,12
Y、YY、YYY、YYYY 年份的后几位,如:3,03,003,2003
RR:解决Y2K问题的年度转换
DY:简写的星期名,如:MON,TUE,FRI,…
DAY:全拼的星期名,如:MONDAY,TUESDAY,…
MON:简写的月份名,如:JAN,FEB,MAR,…
MONTH:全拼的月份名,如:JANUARY,FEBRUARY,…
HH、HH12:12小时制的小时(1~12),如:1,2,3,…,12
HH24:24小时制的小时(0~23),如:0,1,2,…,23
MI:分(0~59),如:0,1,2,…,59
SS:秒(0~59),如:0,1,2,…,59
,./-;: 原样显示的标点符号
'TEXT':引号中的文本原样显示,如:TEXT
7 数值转换符:
9:代表一位数字,如果是正数,前面是空格,如果是负数,前面是?号,如:9999
0:代表一位数字,在相应的位置上如果没有数字则出现0 0000
,:逗号,用作组分隔符,如:99,999
.:小数点,分隔整数和小数,如:999.9
$:$货币符号,如:$999.9
L:本地货币符号,如:L999.99
FM:去掉前后的空格,如:FM999.99
EEEE:科学计数法,如:9.9EEEE
S:负数符号?放在开头,如:S999.9
8 其他函数
nvl:空值转换函数,如:nvl(null, '空'),结果:空
userenv:返回环境信息,如:userenv('LANGUAGE'),结果:SIMPLIFIED CHINESE_CHINA.ZHS16GBK
greatest:返回参数的最大值,如:greatest(20,35,18,9),结果:35
least:返回参数的最小值,如:least(20,35,18,9),结果:9
9 空值的转换
如果对空值NULL不能很好的处理,就会在查询中出现一些问题。在一个空值上进行算术运算的结果都是NULL。最典型的例子是,在查询雇员表时,将工资sal字段和津贴字段comm进行相加,如果津贴为空,则相加结果也为空,这样容易引起误解。
使用nvl函数,可以转换NULL为实际值。该函数判断字段的内容,如果不为空,返回原值;为空,则返回给定的值。
如下3个函数,分别用新内容代替字段的空值:
nvl(comm, 0):用0代替空的Comm值。
nvl(hiredate, '01-1月-97'):用1997年1月1日代替空的雇佣日期。
nvl(job, '无'):用“无”代替空的职务。
使用nvl函数转换空值。
10 查看表字段以及属性结构
查询当前库中哪些表中含有某个具体的字段
11 sql查询时间两列最大值
12 start with connect by prior
树结构表,查询父亲结点下面的所有子结点
13 查询锁表,解锁
14 根据注释或者字段或者表来查询字段,注释
15 UNION ALL 和 UNION
16 返回第一个不为null的参数
1 数值型函数
abs:求绝对值函数,如:abs(?5) 5
sqrt:求平方根函数,如:sqrt(2) 1.41421356
power:求幂函数,如:power(2,3) 8
cos:求余弦三角函数,如:cos(3.14159) ?1
mod:求除法余数,如:mod(1600, 300) 100
ceil:求大于等于某数的最小整数,如:ceil(2.35) 3
floor:求小于等于某数的最大整数,如:floor(2.35) 2
round:按指定精度对十进制数四舍五入,如:round(45.923, 1),结果,45.9
round(45.923, 0),结果,46
round(45.923, ?1),结果,50
trunc:按指定精度截断十进制数,如:trunc(45.923, 1),结果,45.9
trunc(45.923),结果,45
trunc(45.923, ?1),结果, 40
2 字符型函数
ascii:获得字符的ASCII码,如:Ascii('A') 65
chr:返回与ASCII码相应的字符,如:Chr(65) A
lower:将字符串转换成小写,如:lower ('SQL Course') sql course
upper:将字符串转换成大写,如:upper('SQL Course') SQL COURSE
initcap:将字符串转换成每个单词以大写开头,如:initcap('SQL course') Sql Course
concat:连接两个字符串 concat('SQL', ' Course') SQL Course
substr:给出起始位置和长度,返回子字符串,如:substr('String',1,3) Str
--substr函数有三个参数,允许你将目标字符串的一部份输出, --第一个参数为目标字符串, --第二个字符串是将要输出的子串的起点,下标从1开始,如果是正数从前面数,如果是负数从后面数 --第三个参数是将要输出的子串的长度 select substr('0'||'2',2)|| substr('0'||'15',-2) from dual; --215 select substr('0'||'2',2)|| substr('0'||'3',2) from dual; --23 月日比较的话实际要的是203 --215 < 23 注意这里返回的是字符串的比较, --所以如果日期转换成月日比较的话 --这里日那里要写成-2 substr('0'||'2',2)|| substr('0'||'3',-2) select substr('123',4) from dual; --输出空
length:求字符串的长度 length('Wellcom') 7
instr:给出起始位置和出现的次数,求子字符串在字符串中出现的位置,如:instr('String', 'r',1,1) 3
lpad:用字符填充字符串左侧到指定长度,如:lpad('Hi',10,'-') --------Hi
rpad:用字符填充字符串右侧到指定长度,如:rpad('Hi',10,'-') Hi--------
trim:在一个字符串中去除另一个字符串,如:trim('S' FROM 'SSMITH') MITH
replace:用一个字符串替换另一个字符串中的子字符串,如:replace('ABC', 'B', 'D') ADC
3 日期函数
months_between:返回两个日期间的月份,如:months_between ('04-11月-05','11-1月-01'),结果,57.7741935
add_months:返回把月份数加到日期上的新日期,如:add_months('06-2月-03',1),结果,06-3月-03
add_months('06-2月-03',-1),结果,06-1月-03
next_day:返回指定日期后的星期对应的新日期,如:next_day('06-2月-03','星期一'),结果,10-2月-03
last_day:返回指定日期所在的月的最后一天,如:last_day('06-2月-03'),结果,28-2月-03
round:按指定格式对日期进行四舍五入,如:round(to_date('13-2月-03'),'YEAR'),结果,01-1月-03
round(to_date('13-2月-03'),'MONTH'),结果,01-2月-03
round(to_date('13-2月-03'),'DAY'),结果,16-2月-03
(按周四舍五入)
trunc:对日期按指定方式进行截断,如:trunc(to_date('06-2月-03'),'YEAR'),结果,01-1月-03
trunc(to_date('06-2月-03'),'MONTH'),结果,01-2月-03
trunc(to_date('06-2月-03'),'DAY'),结果,02-2月-03
EXTRACT()抽取当前日期的年月日:注意时分秒是抽取不到的
--EXTRACT()抽取当前日期的年月日:注意时分秒是抽取不到的 --抽取年月日 SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL; --2010 SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL; --2 SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL; --24 select EXTRACT(HOUR FROM SYSDATE) FROM DUAL; -- 这个是错误的 --抽取时分秒方法一 SELECT EXTRACT(HOUR FROM TIMESTAMP '2009-2-24 14:46:10')FROM DUAL; --14 SELECT EXTRACT(MINUTE FROM TIMESTAMP '2009-2-24 14:46:10')FROM DUAL; --46 SELECT EXTRACT(SECOND FROM TIMESTAMP '2009-2-24 14:46:10')FROM DUAL; --10 --抽取时分秒方法二 SELECT to_char(Sysdate,'hh24:mi') FROM DUAL; -- 14:49
4 转换函数
Oracle的类型转换分为自动类型转换和强制类型转换。常用的类型转换函数有TO_CHAR、TO_DATE或TO_NUMBER
To_char:转换成字符串类型,如:To_char(1234.5, '$9999.9'),结果:$1234.5
select nvl(to_char(sysdate,'YYYY-MM-DD hh24:mi:ss'),'123') from dual;
To_date:转换成日期类型,如:To_date('1980-01-01', 'yyyy-mm-dd'),结果:01-1月-80
To_number:转换成数值类型,如:To_number('1234.5'),结果:1234.5
5 自动类型转换
Oracle可以自动根据具体情况进行如下的转换:
* 字符串到数值。
* 字符串到日期。
* 数值到字符串。
* 日期到字符串。
以下是自动转换的训练。
自动转换字符型数据到数值型。
6 日期类型转换
将日期型转换成字符串时,可以按新的格式显示。
如格式YYYY-MM-DD HH24:MI:SS表示“年-月-日 小时:分钟:秒”。Oracle的日期类型是包含时间在内的。
主要的日期格式字符的含义
AM、PM:上午、下午,如:08 AM
D:数字表示的星期(1~7),如:1,2,3,4,5,6,7
DD:数字表示月中的日期(1~31),如:1,2,3,…,31
MM:两位数的月份,如:01,02,…,12
Y、YY、YYY、YYYY 年份的后几位,如:3,03,003,2003
RR:解决Y2K问题的年度转换
DY:简写的星期名,如:MON,TUE,FRI,…
DAY:全拼的星期名,如:MONDAY,TUESDAY,…
MON:简写的月份名,如:JAN,FEB,MAR,…
MONTH:全拼的月份名,如:JANUARY,FEBRUARY,…
HH、HH12:12小时制的小时(1~12),如:1,2,3,…,12
HH24:24小时制的小时(0~23),如:0,1,2,…,23
MI:分(0~59),如:0,1,2,…,59
SS:秒(0~59),如:0,1,2,…,59
,./-;: 原样显示的标点符号
'TEXT':引号中的文本原样显示,如:TEXT
7 数值转换符:
9:代表一位数字,如果是正数,前面是空格,如果是负数,前面是?号,如:9999
0:代表一位数字,在相应的位置上如果没有数字则出现0 0000
,:逗号,用作组分隔符,如:99,999
.:小数点,分隔整数和小数,如:999.9
$:$货币符号,如:$999.9
L:本地货币符号,如:L999.99
FM:去掉前后的空格,如:FM999.99
EEEE:科学计数法,如:9.9EEEE
S:负数符号?放在开头,如:S999.9
8 其他函数
nvl:空值转换函数,如:nvl(null, '空'),结果:空
select nvl('1',11) from dual; --1 select nvl('',11) from dual; --11decode:实现分支功能,如:decode(1,1, '男', 2, '女'),结果:男
userenv:返回环境信息,如:userenv('LANGUAGE'),结果:SIMPLIFIED CHINESE_CHINA.ZHS16GBK
greatest:返回参数的最大值,如:greatest(20,35,18,9),结果:35
least:返回参数的最小值,如:least(20,35,18,9),结果:9
9 空值的转换
如果对空值NULL不能很好的处理,就会在查询中出现一些问题。在一个空值上进行算术运算的结果都是NULL。最典型的例子是,在查询雇员表时,将工资sal字段和津贴字段comm进行相加,如果津贴为空,则相加结果也为空,这样容易引起误解。
使用nvl函数,可以转换NULL为实际值。该函数判断字段的内容,如果不为空,返回原值;为空,则返回给定的值。
如下3个函数,分别用新内容代替字段的空值:
nvl(comm, 0):用0代替空的Comm值。
nvl(hiredate, '01-1月-97'):用1997年1月1日代替空的雇佣日期。
nvl(job, '无'):用“无”代替空的职务。
使用nvl函数转换空值。
10 查看表字段以及属性结构
select y.TABLE_NAME, y.COLUMN_NAME, y.DATA_TYPE, (select comments from user_col_comments s where lower(s.table_name) = 'table_name' and s.column_name = y.COLUMN_NAME) as comments from user_tab_columns y where lower(y.TABLE_NAME) = 'table_name' order by y.COLUMN_ID;
查询当前库中哪些表中含有某个具体的字段
select table_name,column_name from USER_TAB_COLUMNS u where u.COLUMN_NAME = 'C_FIELD'
11 sql查询时间两列最大值
select max (greatest(t.t_crt_date,t.t_upd_date)) from tb_fuck t
12 start with connect by prior
树结构表,查询父亲结点下面的所有子结点
select menu.* from sys_menu menu start with menu.parent = '02' --父节点 connect by prior menu.id= menu.parent -- prior 优先的; 在前的; 较早的 union -- 包含父亲结点 select menu_2.* from sys_menu menu_2 where menu_2.id = '02'
13 查询锁表,解锁
SELECT /*+ rule */ lpad(' ', decode(l.xidusn, 0, 3, 0)) || l.oracle_username User_name, o.owner, o.object_name, o.object_type, s.sid, s.serial# FROM v$locked_object l, dba_objects o, v$session s WHERE l.object_id = o.object_id AND l.session_id = s.sid ORDER BY o.object_id, xidusn DESC alter system kill session 'sid,serial#'
14 根据注释或者字段或者表来查询字段,注释
select * from user_tab_comments where COMMENTS like '%退票%' /*查询某表的注释*/ comment on table ctable_name is '对表注释的内容';/*给表添加注释的方式*/ select * from user_tab_comments where table_name = 'CTABLE_NAME';/*查询某表的注释*/ comment on column ctable_name.field1 is '对field1列注释的内容';/*给列添加注释内容的方式,有多少个列应该写多少个*/ select * from user_col_comments where comments like '%摘要信息%';/*查询某表下某列的注释*/ BP_ACCTCURTRANSINFO
15 UNION ALL 和 UNION
select * from A UNION ALL select * from B UNION ALL 列出所有行 select * from A UNION select * from B UNION 去除重复行
16 返回第一个不为null的参数
SELECT COALESCE( NULL, 34, 13, 0 ) from dual;
发表评论
-
mysql 事务机制
2011-04-01 13:52 1610mysql事物机制 一、启动方式 1、使用 serv ... -
ibatis 简单修改ibatis框架
2011-03-31 15:45 913简单修改ibatis框架 使用ibatis框架生成 ... -
mysql 导入导出数据
2011-03-22 15:05 804mysql导入导出数据 1、导入数据 ... -
mysql 修改表结构
2011-03-18 11:50 3851mysql 修改表结构 1.增加一个字段(一列) ... -
mysql 乱码问题
2011-03-16 17:43 849mysql乱码问题。 统一编码,以gbk为例。 ... -
mysql 中文文档
2011-01-27 16:27 1631mysql的中文文档地址: http:/ ... -
mysql 常用指令
2011-01-19 15:21 1150windows 环境变量配置: pah ... -
oracle 卸载与安装三
2010-09-04 17:24 9553 配置(PL/SQL) 3.1 链接本地服务器 ... -
oracle 卸载与安装二
2010-09-04 17:00 11852 Oracle 安装 2.1 Oracle主目录路 ... -
oracle 卸载与安装一
2010-09-04 16:51 1050Oracle 卸载 1 彻底卸载Or ... -
数据库 sql for loop 常用脚本更新数据
2010-08-27 08:49 1430declare i number := 1; ... -
数据库 sql存储过程
2010-08-21 19:04 898create or replace procedure te ... -
数据库 sql游标
2010-08-14 17:57 10091 基本知识 declare 定义游标open 打开游标f ... -
数据库 sql基本语法
2010-08-14 15:04 6621 集合运算符 union all 结合两个select语句结 ... -
数据库 sql需求
2010-08-11 12:55 7991 需求:某人借钱(A表),还钱(B表) 要查询这个人还欠多少 ... -
数据库 sql函数
2010-07-28 08:28 9961 sql函数语法 1.1 条件语句 if else - ... -
Oracle 存储过程
2010-06-22 09:25 983--1 存储过程迁移数据 CREATE OR RE ... -
数据库 MySql2
2010-05-04 21:10 906数据库 MySql2 -
数据库 MySql1
2010-05-04 21:10 734数据库 MySql1 指令 1 mysql; 2 use ... -
Oracle 注意事项‘’null 空格
2010-04-10 19:59 896sql 空字符串与空格不一样 select ' ' fr ...
相关推荐
Oracle SQL 内置函数大全 SQL中的单记录函数 给出整数,返回对应的字符 连接两个字符串 增加或减去月份 用于对查询到的结果进行排序输出
sqlserver实现Oracle中Lpad和Rpad函数
sql 函数 大全 oracle 基于oracle 的函数大全,很使用。
SQLServer和Oracle常用函数对比SQLServer和Oracle常用函数对比
Oracle_Sql语句资料oracle+110个常用函数经典SQL语句大全,可供学习参考。
Oracle SQL 内置函数以及解析
Oracle基本函数,Oracle常用经典SQL查询,有利于入门学习及供日常的使用。
Oracle_Sql_中常用字符串处理函数
SQLServer和Oracle的常用函数对比
oracle 和sql Server 函数对比
oracle中的sql函数说明,详细描述函数的各个参数。
Oracle sql内置函数大全,每个函数都进行了举例说明。
SQL SEVER与ORACLE常用函数比较
数学函数 字符函数 日期函数 1.绝对值 S:select abs(-1) value O:select abs(-1) value from dual 2.取整(大) S:select ceiling(-1.001) value O:select ceil(-1.001) value from dual 3....
oracle sql内置函数大全
ORACLE SQL函数集 ORACLE SQL函数集 ORACLE SQL函数集
sql plsql 函数学习 Oracle 快速入门 有关表的操作 运算符 常用 ORACLE 函数(日期函数、字符函数、数字函数、转换函数、分组函数、其他函数……) 临时表 join SEQUENCE 同义词 等等
第一部分:ORACLE常用经典SQL查询 第二部分:Oracle 最常用功能函数经典汇总