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，等了解了会试着去比较两者的区别。
周末已过~~~~