数据库基础06——函数

数据库基础06——函数

1、字符函数

字符函数主要用于对字段里的字符串或值进行比较、 连接、 搜索、 提取片断等

1.1串接函数

DBMS	函数说明
Oracle	|| 如：‘john’||‘son’
MySQL	CONCAT() 如： CONCAT(‘john’,‘son’)
SQL Server	+ 如：‘john’+‘son’

其实MySQL 和Oracle 中都有串接函数，作用和SQL Server中的+号 和Oracle中的 ||号作用一样。区别在于，Oracle中的串接函数只能用于两个字符串，而MySQL中的CONCAT可以连接多个字符串。

注意：如果要串接数字，就需要将数字首先转换为字符串。

1.2 REPLACE函数

REPLACE函数用于把某个字符或字符串替换为指定的一个字符（或多个字符） ， 其使用类似于TRANSLATE函数， 只是它是把一个字符或字符串替换到另一个字符串里 ；

SELECT REPLACE(FIRST_NAME,'T','B') FROM EMPLOYEE_TBL --把所有的T 替换为B
--Microsoft SQL Server、 MySQL和Oracle全都支持。 

1.3 UPPER函数

把字符串里的小写字母转化为大写

SELECT UPPER(CITY) FROM EMPOYEE_TBL;-- 把查出的city 大写
--Microsoft SQL Server、 MySQL和Oracle全都支持。 

1.4 LOWER函数

把字符串里的大写字母转化为小写

SELECT LOWER(CITY) FROM EMPLOYEE_TBL;-- 把查出的city 转换为小写
--Microsoft SQL Server、 MySQL和Oracle全都支持。 

1.5 SUBSTR:

获取字符串子串，但是各个DBMS实现方式不同

--Oracle
	SUBSTR(列名,位置，长度)-- 即，从第几个开始，长度为几；位置为1的话就从第一个开始
--SQLServer 和 MySQL 
	SUBSTRING(列名,位置,长度) --除了函数名不同，其他一样
--例如：
SELECT SUBSTRING(EMP_ID,1,3) FROM EMPLOYEE_TBL; --返回EMO_ID 的前3个字符

1.6 INSTR

INSTR函数用于在字符串里寻找指定的字符集， 返回其所在的位置

INSTR(列名, '字符串');
--比如：
SELECT INSTR(STATE,'I') FROM EMPLOYEE_TBL;--返回每个州名里字母I 从第一个开始算，字母出现的位置。

1.7 LTRIM

LTRIM函数是另一种截取部分字符串的方式 ,从左边检出字符串中的字符，常用来去除空格，语法如下:

SELECT LTRIM(列名) FROM 表名 WHERE 条件
-- 有的数据库管理系统是可以去除 左边的字符串。
--mysql中好像只能去除空格，不能加参数

1.8 RTRIM

同LTRIM一样的用法，就是去除字符串右边的空格。

1.9 LENGTH

LENGTH函数是很常见的， 用于得到字符串、 数字、 日期或表达式的长度， 单位是字节

SELECT PROD_DESC,LENGTH(PROD_DESC) FROM PRODUCETS_TBL; --返回产品描述几长度
LENGTH(CHARACTER STRING)-- 这是语法
--MySQL和Oracle都支持该函数。 而Microsoft SQL Server则使用LEN函数来实现相同的功能

1.10 IFNULL（检查NULL 值）

IFNULL函数用于在一个表达式是NULL时从另一个表达式获得值。 它可以用于大多数数据类型， 但值与替代值必须是同一数据类型

IFNULL('值','替换值')；
SELECT PAGER,IFNULL(PAGER,9999999999) FROM EMPOYEE_TBL;
/*只有NULL值被替换为 999 999 999。
只有MySQL支持该函数。 要实现相同的功能， Microsoft SQL Server使用 ISNULL函数， 而Oracle则使用
COALESCE函数。*/

1.11 COALESCE

COALESCE函数也是用指定值替代NULL值， 这一点与IFNULL是一样的。 其不同点在于， 它可以接受一个数据集， 依次检查其中每一个值， 直到发现一个非NULL值。 如果没有找到非NULL值， 它会返回一个NULL值。

SELECT COALESCE(A字段,B字段,C字段) FROM EMPLOYEE;--这只是个例子，表示返回employee表中a,b,c三个字段中的第一个非null值。
SELECT COALESCE(NULL, NULL, 1); -- Return 1 
SELECT COALESCE(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1); -- Return 1 
--如果传入的参数所有都是null，则返回null，比如 
SELECT COALESCE(NULL, NULL, NULL, NULL); -- Return NULL 
--所以同样可以用来对null值进行判断并且替换。

1.12 LPAD 函数

LPAD（左填充） 用于在字符串左侧添加字符或空格， 其语法如下所示：

MySQL和Oracle全都支持该函数。 遗憾的是， Microsoft SQL Server中没有对应的函数。

LPAD(string, padded_length, [ pad_string ] )
--string：要被填充的字符串，padded_length:填充之后字符串长度。也就是函数返回的字符串长度，如果这个数量比源字符串短，就会把字符串截取
--pad_string:填充字符串，是个可选参数，这个字符串要粘贴到string的左边，如果这个参数未写，就会粘贴空格
--例如：
SELECT LPAD(PROD_DESC,30,'.') PRODUCT FROM PRODUCTS_TBL;
--就会再每个产品描述左侧加上'.'，总长度30个字符

1.13 RPAD 函数

同LPAD函数，使用相同，不同的是右填充，在字符串右侧添加字符或空格。

MySQL和Oracle全都支持该函数。 遗憾的是， Microsoft SQL Server中没有对应的函数。

1.14 ASCII 函数

ASCII函数返回字符串最左侧字符的“美国信息交换标准码（ASCII），Microsoft SQL Server、 MySQL和Oracle全都支持该函数。

ASSCII('A') --返回65
ASSCII(‘B’) --返回66

2、算术函数

函数	说明
ABS()	返回一个绝对值
ROUND()	四舍五入
SQRT()	平方根
SIGN()	符号-1,0,1，如select sign(-6),sign(0),sign(34); 判断正负
POW(x,y)/POWER(x,y)	幂函数，power(2,3) 计算2的3次方
CEIL() 和FLOOR()	上限和下限，比如1.55 下限1上限2，相当于取整;ceiling ==ceil
EXP(n)	返回e的n次方。 e (2.718281…) 是自然对数的底数。
LOG(n)	默认情况下，LOG() 返回自然对数。自然对数是底数为 e 的对数 LOG( EXP( n ) ) = n。
LOG10(n)	返回n以10为底的对数。即10的多少次方是N
SIN()	返回一个角度的正弦
COS()	返回一个角度的余弦
TAN()	返回一个角度的正切
PI()	圆周率
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列之和

使用方式都是 FUNCTION(EXPRESSION) 直接用，并且Microsoft SQL Server、 MySQL和Oracle都支持所有的算数函数 。

注意:聚合函数不统计null值

3、转换函数

字符到数字；数字到字符；字符到日期；日期到字符。

Cast函数；CONVERT函数。

用法：CAST(expr AS type), CONVERT(expr,type) , CONVERT(expr USING transcoding_name).

`SELECT` `CONVERT``(``'abc'` `USING utf8);`

将varchar 转为Int 用 cast(str as unsigned) str为varchar类型的字符串 。

比如常用的百分比转换：

`select` `cast((1/3)*100 ``as` `UNSIGNED) ``as` `percent ``from` `dual;`

MySQL类型转换函数参数 : CAST(xxx AS 类型) , CONVERT(xxx,类型)

这个类型 可以是以下值其中的 一个：
BINARY[(N)]
CHAR[(N)]
DATE
DATETIME
DECIMAL
SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]

整数 : SIGNED
无符号整数 : UNSIGNED
二进制,同带binary前缀的效果 : BINARY
字符型,可带参数 : CHAR()
日期 : DATE
时间: TIME
日期时间型 : DATETIME
浮点数 : DECIMAL

4、日期函数

4.1获取当前日期

数据库系统	函数说明
Microsoft SQL Server	GETDATE() 函数
MySQL	NOW() 函数
Oracle	SYSDATE() 函数

4.2 其他日期函数

DBMS	日期函数	用途
SQL Server	DATEPART	返回日期的某个元素整数值
	DATENAME	返回日期的某个元素的文本值
	GETDATE()	返回系统日期
	DATEDIFF	返回两个日期之间由指定日期元素表示的间隔， 比如天数、分钟数和秒数
Oracle	NEXT_DAT	返回指定日期滞后的下一天
	MONTHS_BETWEEN	返回两个日期之间相差的月数
MySQL	DAYNAME(date)	显示星期几
	DAYOFMONTH(date)	显示几日
	DAYOFWEEK(date)	显示星期几
	DAYOFYEAR(da’te)	显示一年中的第几天

小结：不同的数据库管理系统，函数的实现并不是一致的，具体的使用函数名和使用方式要看具体的数据库系统，我们使用的时候知道有这么个函数就行，根据公司使用的数据库系统，去查相应的文档，用的多了就记住了。