SQL中的函数.doc

/*字符串处理函数*/-Ltrim RTRIM 去除空格SELECT Ltrim(MusicName),RTRIM(MusicName),* FROM MusicORDER BY MusicName DESCSELECT LTRIM(RTRIM( abc ) + 123-SUBSTRING截取字符串SELECT SUBSTRING(张小小,1,1)SELECT SUBSTRING(张小小,2,2)-1检索所有歌手的姓SELECT SUBSTRING(Singer,1,1) FROM Music-2使用SUBSTRING检索出所有姓周的作者的歌曲SELECT MusicName FROM Music -WHERE Author LIKE 周%WHERE SUBSTRING(Author,1,1) = 周-Left Right 左移右移SELECT LEFT(abc123,3)SELECT RIGHT(abc123,3)- “+”连接字符串SELECT ABC + DEFSELECT 歌曲名:+ MusicName + 歌手: + Singer + 作者: + Author AS 歌曲简介 FROM Music-检索出所有作者姓以(伟大的姓先生)SELECT MusicName , 伟大的 + SUBSTRING(Author,1,1) + 先生 as 作者 FROM Music-stuff 插入字符串SELECT STUFF(abc123def,4,3,456)-使用stuff检索作者格式(姓周名杰伦)SELECT MusicName, 姓 + STUFF(Author,2,0,名) FROM Music -Len字符串长度SELECT LEN(ABC)SELECT Author,LEN(Author) FROM Music -（）检索出所有歌曲的名称以及他们的名字的长度。-（）检索出所有名字长度在个字到个字的歌曲信息。SELECT * FROM Music WHERE LEN(MusicName) = 3 AND LEN(MusicName) = 5-（）查询出所有的名字在个字以下的歌曲的信息。-REPLACESELECT REPLACE(abc123def123hij,123,456)SELECT * FROM Music -（）检索出所有的歌曲，并把所有歌曲中的明天替换成昨天SELECT REPLACE(MusicName,明天,昨天) From Music -（）高级：与len配合使用检索出第二首歌名中出现了几次明字。明天的明天的明天-用LEN检索第二个歌曲名中有几个明字select LEN ( REPLACE (MusicName,明,明天) - LEN (MusicName)as歌曲中明字的个数 from Music where MusicID = 2 -排列顺序 ALTER TABLE MusicADD MusicNo varchar(10)UPDATE Music SET MusicNo = G + CONVERT (varchar, MusicID)select MusicNo,MusicID,MusicName from Music ORDER BY MusicNoAlter Table Music add Musicno varchar(10) update Music set Musicno = g+ convert (varchar,musicId) -select Musicno ,MusicID , MusicName from Music - order by LEN(Musicno) asc, substring (Musicno,2,1) ascupdate Music set Musicno = STUFF (Musicno,2,0,0) where LEN (Musicno) = 2select Musicno ,MusicID , MusicName from Music order by Musicno SELECT STUFF(abc123def,4,3,456) SELECT REPLACE(abc123def123hij,123,456)select PATINDEX (%123%,abc123def123hij)select REPLACE (abc123def123hij,123,)select STUFF (abc123def123hij,PATINDEX (%123%,abc123def123hij),3,)select Musicno ,MusicID , MusicName from Music order by LEN (Musicno)select RIGHT (0 + REPLACE (Musicno,g,),2),Musicno ,MusicID , MusicName from Music order by RIGHT (0 + REPLACE (Musicno,g,),2)-convert 数据类型转化-定义CONVERT(datetype(length),expression,stype)select123 + 4select CONVERT (int , 123)+4select CONVERT (varchar , 123)+4select CONVERT (int , REPLACE (Musicno,g,),Musicno ,MusicID , MusicName from Music order by CONVERT (int , REPLACE (Musicno,g,)select RIGHT (REPLACE (Musicno,g,),2) from Music select REPLACE (Musicno,g,) from Music -数学函数-得到ASCII码select ASCII(A)-根据ASCII码得到字符select char(1)-ABS 绝对值select ABS (-5574)-roundselect ROUND (145.26589,2)-日期函数-getdate 得到当前的时期时分秒select GETDATE ()-得到日期中的部分值 -datepart（日期部分，日期值）select DATEPART (YEAR ,GETDATE()select DATEPART (MONTH ,GETDATE()select DATEPART (DAY ,GETDATE()-计算年龄use StudentDB select * from StudentData select * from StudentData update StudentData set Age = DATEPART(YEAR,GETDATE() - DATEPART(YEAR ,Birthday )-查找出生年月select * from StudentData where DATEPART (YEAR ,Birthday) = 1990 and DATEPART (MONTH ,Birthday) = 12 and DATEPART (DAY,Birthday) = 12select CONVERT (varchar(10),Birthday,120) from StudentData-常用的样式-111 yyyy/mm/dd 112 yyyymmdd 108 HH:mm:ss 120 yyyy-dd-mm -转化数据类型后查找出生年月select * from StudentData where CONVERT (varchar(10) , Birthday,120) = 1990-12-12-时差查询（计算年龄）select DA