SQL基础1

单行函数：

Single-Row SQL functions

每行返回一个结果

DUAL是一个公共表，可用它做些计算或返回函数处理结果

null参与运算会返回空值

字符函数：

大小写类：LOWER、UPPER、INITCAP（驼峰型）

字符处理类：CONCAT、SUBSTR、LENGTH、INSTR、LPAD | RPAD、TRIM、REPLACE

e.g. select employee_id,concat(first_name,last_name) name,

job_id,length(last_name),

instr(last_name,'a') "Contains 'a'?"

from employees

where substr(job_id,4) = upper('rep');

注意：

INSTR()函数的结果不可能为空，若不存在，则返回0

INSTR(C1,C2,I,J)

在一个字符串中搜索指定的字符,返回发现指定的字符的位置;

C1 被搜索的字符串

C2 希望搜索的字符串

I 搜索的开始位置,默认为1

J 出现的位置,默认为1

SQL> select instr(oracle traning,ra,1,2) instring from dual;

INSTRING

---------

9

TRIM()只能去除一个字符，所以第一个参数只能有一个字符

数字函数：

round 按照指定的小数位四舍五入

ROUND()没有第二个参数，即没有小数位，默认保留到整数trunc 按指定的小数位截断数据

mod 两数相除，返回余数

日期函数：

Oracle存储日期格式：世纪,年,月,日,时,分,秒

函数：sysdate，time

默认显示：DD-MON-RR

RR纪年规则：

e.g. 现有日期加上或减去一个数值，可以得到一个日期；

两个日期详见，可以得到两个日期的天数

将值除以24可以得到小时数

select last_name,(sysdate-hire_date)/7 as weeks

from hr.employees

where department_id = 90;

日期处理函数：

指定日期增加月数

round舍入：month按日舍入，16号以后进一个月，即下月1号，16号以前则为本月1号；year按月舍入，7月-12月则返回下一年1月，1-6月则返回本年1月

注意：round函数只能处理数字和日期，不能处理字符，所以要将日期用to_date函数转换过来

oracle 日期格式FM/FX和日期后缀SP/TH/SPTH/THSP：

TH是序数方式显示,SP是用字母拼写方式显示，SPTH/THSP用字母序数方式显示：--普通方式显示，月份后面会有空格

--序数（th）方式显示，月份后面一样有个空格

--字母（sp）方式显示，月份后面一样有个空格

--字母拼写和序数方式（spth/thsp）显示

FM和FX主要是用来控制填充和精确格式：

fm是填充模式

如果是字符格式化，指定fm参数后将仅返回指定属性实际所占长度(不再以空格

填充)

如果是数值格式化，fm参数会自动舍弃被格式化元素的前置0

--序数方式显示，并且fm方式显示，会把多余的前缀0和多余的空格去掉

--字母拼写方式显示，并且fm方式显示,会把多余的前缀0和多余的空格去

掉

fx是精确匹配模式，使用FX格式后:

1.字符及对应的格式必须严格一一对应，甚至连分隔符都要相符。

2.不允许有多余的空格。

3.数值参与格式需要完全对应(或通过fm参数去掉前置0)

转换函数与条件表达式：

隐式转换：

varchar2/char与number、date之间可以互相转换

显式转换：

to_char处理日期：

必须用单引号引起来、区分大小写、可用有效日期格式元素、能去除填充的空格或前置的0、用逗号与日期隔开

to_char(n[,fmt[,'nlsparam']])

日期元素：

YYYY或RRRR--数字形式年份、四位数

YEAR--年份的全拼写格式

MM--数字形式月份，两位数

MONTH--月份的全拼格式

MON--月份的三位缩写格式

DY--星期的三位缩写格式

DAY--星期的全拼写格式

DD--数字形式日期，两位数

nlsparam:

该函数将NUMBER类型的n按数值格式fmt转换成VARCHAR2类型的值。

'nlsparams'指定由数值格式的元素返回的字符,包括:

NLS_NUMERIC_CHARACTERS 小数点字符、组分隔符

NLS_CURRENCY 本地钱币符号

NLS_ISO_CURRECY 国际钱币符号

e.g. TO_CHAR (17145,'L099G999','NLS_NUMERIC_CHARACTERS=".,"

NLS_CURRENCY="NUD"')=NUD017,145

时间元素：

HH24:MI:SS AM e.g. 15.27:32 PM

加入的字符串用双引号引起来：

DD "of" MONTH e.g. 12 of OCTOBER

序号：

ddspth e.g. fourteenth

e.g. to_char函数处理日期

select last_name,to_char(hire_date,'fmDD Month YYYY') as hiredate from employees;

to_char处理数字：

转换数字为字符的元素：

9--代表一个数值

0--显示0或一个数值

$--使用$符号

L--使用本地货币符号

D\.--小数点

G\,--千位分隔符

e.g. 数字到字符