sql函数扫盲

单行函数

lower(列名|表达式) 把字符转换成小写

 

upper(列名|表达式) 把字符转换成大写

 

initcap（列名|表达式) 把每个字的头一个字母转换成大写。

Initcat(‘sql is used’)       Sql Is  Used

 

concat(列名|表达式，列名|表达式)   把第一个字符串和第二个字符串连接起来成为一个字符串。

 

Substr(列名|表达式,m,[n]) 用于返回指定的字串，该字串从第m个字符开始，其长度为n ,如果省略n  那么就是从m 后的所有字符。

 

Length (列名|表达式) 返回表达式中字符串的长度。

 

Instr(列名|表达式,’字符串’,[m],[n])  该函数返回所给字符串在表达式中的数字位置。m 表示从第m 个字符开始搜索，n 表示所给字符串出现的次数，默认值为1.

 

Trim ([leading|trailing|both]要去掉的字符from 源字符串)  该函数能够从“源字符串”中的头（leading）,尾（trailing）或两者（both）中去掉“要去掉的字符”，其中both 为默认方式。

 

Replace（正文表达式，要搜索的字符串，替换字符串）  用于在“正文表达式”里查找“要搜寻的字符串”并用“替换字符串”替换之。

 

数字型函数

Round(列名|表达式，n) 该函数将列名或表达式所表示的数值四舍五入到小数点后的n位。

 

Trunc(列名|表达式,n)  将列名或表达式表示的数值截取到小数点后的n 位。

 

Mod (m,n) m 除以n 求余数。

 

系统日期函数sysdate  返回当前的系统时间。

 

To_date(日期字符串) 将日期字符串转换成日期。

 

Months_between(日期1，日期2)  返回日期1和日期2之间的数。如果日期2大于日期1 那么返回的月数为负。

 

Add_months(日期，n) 把n 加到日期上。

 

Next_day(日期，字符串（星期几）)  返回日期之后的下一个字符串（星期几）指定的日期。 

 

Last_dat(日期) 返回日期所在月的最后一天。

 

Round 和trunc也可用于日期型函数。

Select round(to_date(’23-0ct-01’),’month’)from dual;      返回  01-nov-01

 

To_char(日期，’fmt’)  把日期转换成变长字符串，其中“fmt”是日期模式（如：MM/DD/YY）

To_char(数字,’fmt’)  把数字转换成变长字符串，其中“fmt” 是数字模式。

常用字符模式：

l  9    一位数字

l  0    显示前导零

l  $    显示美元符号

l  L    显示本地货币号

l  .     显示小数点

l  ，  显示千位符

l  MI   在数的右边显示减号

l  PR   把负数用尖括号括起来

To_number(字符串[,’fmt’])    把字符串转换成数字。

To_date(字符串[,’fmt’])    把字符串转换成日期。

Oracle常用的5个分组函数分别为：Count 、Avg、Sum、Max、Min

 

Count({*[distinct|all]表达式})   用于返回查询的行数。Count(*) 返回表中所有的行，包括空行和重复的行。Count(表达式)  返回表中所有表达式为非空的行。

 

Avg([distinct|all] 表达式)      用于返回表达式的平均值。

Sum([distinct|all]表达式)       用于返回表达式的总和。

Min([distinct|all]表达式)       返回表达式的最小值。

Max([distinct|all]表达式)       返回表达式的最大值。

 

不像AVG和SUM　函数只能操作数字型数据，MIN和MAX函数不但可用于数字型数据，而且还可以用于字符型数据和日期型数据。字符型是按他们的ASCII码来计算的。

 

group by 的用法

 

group by 子句中的列可以不在select语句中。

for exemple :select avg(sal) "AverageSalary" from emp group by job;

 

如果在一个查询语句（select子句）中使用了分组函数，则任何不在分组函数中的列或表达式必须在group by 中出现，不容许单独出现于任何子句中。

for exemple :select job,avg(sal) from empgroup by job order by deptno;中列deptno 既没有在分组函数中也没有在分组子句中，所以是非法的语句。

 

where 子句不能用于限制分组函数。在Oracle中可以使用having子句限制分组函数。

 

假若emp表中有按此顺序的ename,job,emnber,deptno四列

select * from emp order by 2;   是按照job列排序。

select emnber,ename,job from emp order by3;是按job列排序。

就是说如果order by 中后跟列号，就按照此语句的列的顺序。

 

 

分组函数使系统的效率明显下降，特别是在对容量大的表格进行这样的操作时，因为使用这样的函数通常要扫描整个表，如果使用了group by  子句，那么还得进行分组。

 

select min(avg(sal)),max(avg(sal)) from empwhere job not like 'CLEAR%' group by job; 此句的执行下顺序是

1）在emp表中找到所有职位（job) 不是CLEAR的记录

2）把这些记录按照职位进行分组

3）然后进行分组函数的运算（从内到外）即先算出平均值

4）最后求出这些平均工资的最小值和最大值。

 

 

Having 子句的使用  select job,AVG(sal) from empwhere AVG(sal)>1500 group by job;  本查询语句出现错误为：where处不允许出现分组函数。这是因为where 子句不能用于限制分组函数。 在Oracle中可以使用having子句限制分组函数。Having用于限制分组函数时与group by 一起使用。 首先对数据行（记录）进行分组，把所得到的分组应用于分组函数，最后显示满足having子句所指定条件的结果。

 

分组函数可以嵌套使用，但有一点请注意：尽管分组函数给我们编写SQL语句带来很大的方便，但是使用起来可能会使系统的效率明显下降，特别是在对容量大的表格进行这样的操作时。因为使用分组函数通常要扫描整个表，如果使用了group　ｂｙ　子句，Oracle还要进行排序。

 

除了count 之外，其他的函数都不处理空值（NULL）。所以如果涉及处理空值问题的话就用NVL 和NVL2函数。