SQL函数

（一）数学函数

1.abs(x);

2.POWER(expression,power_raise_to);

3.SQRT(expression_to_square_root);   //平方根

4.rand();  //生成随机数，但是不保证生成的是唯一值

5.ceiling();   //向上舍入到临近的最大整数

   floor();    //像下舍入到临近的最小整数 

   round();   //舍入到最接近的整数

 

（二）字符串函数

1.substring(string,start_character_position,length_of_string_to_obtain);    //提取子字符串

 

2.upper(string),lower(string)     //将字符串转换为全部大写或者全部小写

select lastname upper(lastname),lower(lastname) from memberdetails;

 

3.reverse(string)        //字符串反转

select lastname,reverse(lastname) from memberdetails;

 

4.TRIM函数

LTRIM()和RTRIM()分别截去字符串左边及右边的空格。

 

5.length()函数

返回字符串的长度，不是返回字节的长度。 

create table name(
firstname nvarchar(50),
lastname nvarchar(50),
name varchar(50)
);

insert into name values('张','三','zhangsan张三');
select firstname,len(firstname) from name;
select lastname,len(lastname) from name;
select name,len(name) from name;

结果：

补充char(n)、varchar(n)、nvarchar(n)的区别：

• 由于char是以固定长度的，所以它的速度会比varchar快得多!但程序处理起来要麻烦一点，要用trim之类的函数把两边的空格去掉! 所以有时候需要使用char的贮存空间以换得检索速度的提高。但有时则要使用varchar获取占用较低存储空间。

• 长度为 n 个字节的可变长度且非 Unicode 的字符数据。n 必须是一个介于 1 和 8,000 之间的数值。存储大小为输入数据的字节的实际长度，而不是 n 个字节。

• 包含 n 个字符的可变长度 Unicode 字符数据。n 的值必须介于 1 与 4,000 之间。字节的存储大小是所输入字符个数的两倍。

eg：两字段分别有字段值：“我和coffee”

那么varchar字段占2×2+6=10个字节的存储空间，而nvarchar字段占8×2=16个字节的存储空间。

小原则：如字段值只是英文可选择varchar，而字段值存在较多的双字节（中文、韩文等）字符时用nvarchar。

eg：varchar(4) 可以输入4个字母，也可以输入两个汉字

nvarchar(4) 可以输四个汉字，也可以输4个字母，但最多四个。

 

6.soundex()函数和difference()函数

soundex()函数将字符串转换为一种特殊的4字符编码，该编码表示字符串发音的方式。

insert into name(firstname,lastname) values('Hello','kitty');
insert into name(firstname,lastname) values('Jone','William');
select firstname,SOUNDEX(firstname) as SOUND from name;

difference(some_name,comparision_name);求得两个字符串的发音相似度，值为0~4，值越大则越相似。

select firstname from name where difference(firstname,'kate')>=3;

 

7.日期函数

DAY(date),MONTH(date),YEAR(date)

select dateofbirth,day(dateofbirth),month(dateofbirth),year(dateofbirth) from memberdetails order by year(dateofbirth)

 

(三)不同数据类型的转换

 cast(expression as data_type); 将可以被转换的数据转换成另一种类型。

如果字符串中存储的是数字，在和数值型数据运算时可以被自动转换为数值类型。

 

（四）NULL

1.NULL用于数学函数时，结果总是NULL。

2.NULL用于字符串时

select firstname,lastname,city,firstname+' ' +lastname+' lives in ' +city from memberdetails;     --如果firstname和lastname和city中有一个为NULL，则最后的相连接的结果也是NULL

3.COALESCE()函数

select firstname, lastname,city,coalesce(firstname+' '+lastname+' lives in '+city,'We have no details for '+firstname+' '+lastname,'no data available') from MemberDetails;   --返回第一个非NULL值

 

（五）insert into 和select语句的综合使用

insert into destination_table_name select column_list from source_table_name where condition   --目的列和源列必须匹配。列的数目和对应的数据类型必须匹配。 where子句可选