SQL语句使用08-------函数

函数在SQL中可以帮助我们更好的实现很多功能。
常用的函数分为两类：

1.分类大概

SQL Aggregate 函数

AVG() - 返回平均值
COUNT() - 返回行数
MAX() - 返回最大值
MIN() - 返回最小值
SUM() - 返回总和

**//Mysql不支持，只有 MS Access 支持
FIRST() - 返回第一个记录的值
LAST() - 返回最后一个记录的值

SQL Scalar 函数

SQL Scalar 函数基于输入值，返回一个单一的值。
常用的 Scalar 函数：

UCASE() - 将某个字段转换为大写
LCASE() - 将某个字段转换为小写
MID() - 从某个文本字段提取字符，MySql 中使用
SubString(字段，1，end) - 从某个文本字段提取字符
LEN() - 返回某个文本字段的长度
ROUND() - 对某个数值字段进行指定小数位数的四舍五入
NOW() - 返回当前的系统日期和时间
DATE_FORMAT() - 格式化某个字段的显示方式

这两类函数的区别是

SQL Aggregate 函数（合计函数）：
从名字上我们就能知道，这是对一系列函数的操作。然后返回单一的值。
SQL Scalar 函数
这是对单一的值得操作，然后返回单一的值。

2.详细使用

2-1.SQL Aggregate 函数

还是用之前的表进行说明。
(表的基本结构：
Student(Sid,Sname,Sage,Ssex) 学生表
Course(Cid,Cname,Tid) 课程表
SC(Sid,Cid,score) 成绩表
Teacher(Tid,Tname) 教师表
)
//此表在第一节里面有SQL。

现在一步步开始

这是我用的一个资源表，组合了student和sc,按照名字分类

select * from student,sc where student.sid=sc.sid group by sname ;

现在使用各个函数：

 select *,count(sname),avg(score),max(score),min(score),sum(score) as 数量 from student,sc where student.sid=sc.sid group by sname ;
 //不多解释了，这是对每个小类进行了操作（group by 分类的知识点）

2-2.SQL-Scalar 函数

样表：

//因为student表没有大小写，所以重新建立了一个表。
 select * from test;
 select *,ucase(test_name),lcase(test_name)from test;

 select * from student;
 select *,mid(sname,2,3) from student;
 select *,SubString(sname,2,3) from student;

select *,length(sname)as lena from student;
select NOW() from student;

select DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p') from student;
//这个函数主要针对mysql!!!

对应的格式：

形式	格式	解释
%a	缩写星期名
%b	缩写月名
%c	月	数值
%D	带有英文前缀的月中的天
%d	月的天	数值(00-31)
%e	月的天	数值(0-31)
%f	微秒
%H	小时	(00-23)
%h	小时	(01-12)
%I	小时	(01-12)
%i	分钟	数值(00-59)
%j	年的天	(001-366)
%k	小时	(0-23)
%l	小时	(1-12)
%M	月名
%m	月	数值(00-12)
%p	AM 或 PM
%r	时间	12-小时（hh:mm:ss AM 或 PM）
%S	秒	(00-59)
%s	秒	(00-59)
%T	时间	24-小时 (hh:mm:ss)
%U	周	(00-53) 星期日是一周的第一天
%u	周	(00-53) 星期一是一周的第一天
%V	周	(01-53) 星期日是一周的第一天，与 %X 使用
%v	周	(01-53) 星期一是一周的第一天，与 %x 使用
%W	星期名
%w	周的天	（0=星期日, 6=星期六）
%X	年	其中的星期日是周的第一天，4 位，与 %V 使用
%x	年	其中的星期一是周的第一天，4 位，与 %v 使用
%Y	年	4 位
%y	年	2 位

3.其余函数（针对Mysql）

形式	解释
ascii(str)	返回字符串str的第一个字符的ascii值(str是空串时返回0)
ord(str)	如果字符串str句首是单字节返回与ascii()函数返回的相同值
conv(n,from_base,to_base)	对数字n进制转换,并转换为字串返回(任何参数为null时返回null,进制范围为2-36进制,当to_base是负数时n作为有符号数否则作无符号数,conv以64位点精度工作)
bin(n)	把n转为二进制值并以字串返回(n是bigint数字,等价于conv(n,10,2))
oct(n)	把n转为八进制值并以字串返回(n是bigint数字,等价于conv(n,10,8))
hex(n)	把n转为十六进制并以字串返回(n是bigint数字,等价于conv(n,10,16))
char(n,…)	返回由参数n,…对应的ascii代码字符组成的一个字串(参数是n,…是数字序列,null值被跳过)
concat(str1,str2,…)	把参数连成一个长字符串并返回(任何参数是null时返回null)
locate(substr,str) position(substr in str)	返回字符串substr在字符串str第一次出现的位置(str不包含substr时返回0)
locate(substr,str,pos)	返回字符串substr在字符串str的第pos个位置起第一次出现的位置(str不包含substr时返回0)
instr(str,substr)	返回字符串substr在字符串str第一次出现的位置(str不包含substr时返回0)
lpad(str,len,padstr)	用字符串padstr填补str左端直到字串长度为len并返回
rpad(str,len,padstr)	用字符串padstr填补str右端直到字串长度为len并返回
left(str,len)	返回字符串str的左端len个字符
right(str,len)	返回字符串str的右端len个字符
substring(str,pos,len) substring(str from pos for len) mid(str,pos,len)	返回字符串str的位置pos起len个字符mysql> select substring(‘quadratically’,5,6);
substring(str,pos) substring(str from pos)	返回字符串str的位置pos起的一个子串
substring_index(str,delim,count)	返回从字符串str的第count个出现的分隔符delim之后的子串(count为正数时返回左端,否则返回右端子串)
ltrim(str)	返回删除了左空格的字符串str
rtrim(str)	返回删除了右空格的字符串str
space(n)	返回由n个空格字符组成的一个字符串
replace(str,from_str,to_str)	用字符串to_str替换字符串str中的子串from_str并返回
repeat(str,count)	返回由count个字符串str连成的一个字符串(任何参数为null时返回null,count<=0时返回一个空字符串)
reverse(str)	颠倒字符串str的字符顺序并返回
insert(str,pos,len,newstr)	把字符串str由位置pos起len个字符长的子串替换为字符串newstr并返回
elt(n,str1,str2,str3,…)	返回第n个字符串(n小于1或大于参数个数返回null)
field(str,str1,str2,str3,…)	返回str等于其后的第n个字符串的序号(如果str没找到返回0)
lcase(str) lower(str)	返回小写的字符串str
ucase(str) upper(str)	返回大写的字符串str
load_file(file_name)	读入文件并且作为一个字符串返回文件内容(文件无法找到,路径不完整,没有权限,长度大于max_allowed_packet会返回null)

2、数学函数

形式	解释
abs(n)	返回n的绝对值
sign(n)	返回参数的符号(为-1、0或1)
mod(n,m)	取模运算,返回n被m除的余数(同%操作符)
floor(n)	返回不大于n的最大整数值
ceiling(n)	返回不小于n的最小整数值
round(n,d)	返回n的四舍五入值,保留d位小数(d的默认值为0)
exp(n)	返回值e的n次方(自然对数的底)
log(n)	返回n的自然对数
log10(n)	返回n以10为底的对数
pow(x,y) power(x,y)	返回值x的y次幂
sqrt(n)	返回非负数n的平方根
pi()	返回圆周率
cos(n)	返回n的余弦值
sin(n)	返回n的正弦值
tan(n)	返回n的正切值
acos(n)	返回n反余弦(n是余弦值,在-1到1的范围,否则返回null)
asin(n)	返回n反正弦值
atan(n)	返回n的反正切值
atan2(x,y)	返回2个变量x和y的反正切(类似y/x的反正切,符号决定象限)
cot(n)	返回x的余切
rand() rand(n)	返回在范围0到1.0内的随机浮点值(可以使用数字n作为初始值)
degrees(n)	把n从弧度变换为角度并返回
radians(n)	把n从角度变换为弧度并返回
truncate(n,d)	保留数字n的d位小数并返回
least(x,y,…)	返回最小值(如果返回值被用在整数(实数或大小敏感字串)上下文或所有参数都是整数(实数或大小敏感字串)则他们作为整数(实数或大小敏感字串)比较,否则按忽略大小写的字符串被比较)
greatest(x,y,…)	返回最大值(其余同least())

3、时期时间函数

形式	解释
dayofweek(date)	返回日期date是星期几(1=星期天,2=星期一,……7=星期六,odbc标准)
weekday(date)	返回日期date是星期几(0=星期一,1=星期二,……6= 星期天)。
dayofmonth(date)	返回date是一月中的第几日(在1到31范围内)
dayofyear(date)	返回date是一年中的第几日(在1到366范围内)
month(date)	返回date中的月份数值
dayname(date)	返回date是星期几(按英文名返回)
monthname(date)	返回date是几月(按英文名返回)
quarter(date)	返回date是一年的第几个季度
week(date,first)	返回date是一年的第几周(first默认值0,first取值1表示周一是周的开始,0从周日开始)
year(date)	返回date的年份(范围在1000到9999)
hour(time)	返回time的小时数(范围是0到23)
minute(time)	返回time的分钟数(范围是0到59)
second(time)	返回time的秒数(范围是0到59)
period_add(p,n)	增加n个月到时期p并返回(p的格式yymm或yyyymm)
period_diff(p1,p2)	返回在时期p1和p2之间月数(p1和p2的格式yymm或yyyymm)
date_add(date,interval expr type) date_sub(date,interval expr type) adddate(date,interval expr type) subdate(date,interval expr type)	对日期时间进行加减法运算 (adddate()和subdate()是date_add()和date_sub()的同义词,也可以用运算符+和-而不是函数 date是一个datetime或date值,expr对date进行加减法的一个表达式字符串type指明表达式expr应该如何被解释
to_days(date)	返回日期date是西元0年至今多少天(不计算1582年以前)
from_days(n)	给出西元0年至今多少天返回date值(不计算1582年以前)
time_format(time,format)	和date_format()类似,但time_format只处理小时、分钟和秒(其余符号产生一个null值或0)
curdate() current_date()	以’yyyy-mm-dd’或yyyymmdd格式返回当前日期值(根据返回值所处上下文是字符串或数字)
curtime() current_time()	以’hh:mm:ss’或hhmmss格式返回当前时间值(根据返回值所处上下文是字符串或数字)
now() sysdate() current_timestamp()	以’yyyy-mm-dd hh:mm:ss’或yyyymmddhhmmss格式返回当前日期时间(根据返回值所处上下文是字符串或数字)
unix_timestamp() unix_timestamp(date)	返回一个unix时间戳(从’1970-01-01 00:00:00’gmt开始的秒数,date默认值为当前时间)
from_unixtime(unix_timestamp)	以’yyyy-mm-dd hh:mm:ss’或yyyymmddhhmmss格式返回时间戳的值(根据返回值所处上下文是字符串或数字)
from_unixtime(unix_timestamp,format)	以format字符串格式返回时间戳的值
sec_to_time(seconds) 以’hh:mm:ss’或hhmmss格式返回秒数转成的time值(根据返回值所处上下文是字	符串或数字)
time_to_sec(time)	返回time值有多少秒

4.转换函数

cast
用法：cast(字段 as 数据类型) [当然是否可以成功转换，还要看数据类型强制转化时注意的问题]
实例：select cast(a as unsigned) as b from cardserver where order by b desc;
convert：
用法：convert(字段,数据类型)
实例：select convert(a ,unsigned) as b from cardserver where order by b desc;

3.总结

SQL的函数总结基本上都在这了
此文主要针对Mysql,因为之前一直在用Mysql,现在工作需要开始使用Oracle，等了解了会试着去比较两者的区别。
周末已过~~~~