常用Sql个人札记 共享之.doc

Sql 执行顺序1 执行where xx对全表数据做筛选，返回第1个结果集。2.针对第1个结果集使用group by分组，返回第2个结果集。3.针对第2个结果集中的每1组数据执行select xx，有几组就执行几次，返回第3个结果集。4.针对第3个结集执行having xx进行筛选，返回第4个结果集。5.针对第4个结果集排序。存储过程举例：output 就是这个存储过程返回的值 也可以说输出的值-创建存储过程 求最大值CREATE PROCEDURE dbo.P_Maxa int, - 输入b int, - 输入Returnc int output -输出AS if (ab) set Returnc =aelse set Returnc =b- 调用declare Returnc int exec P_Max 2,3,Returnc output select Returnc查询abc数据库中含有fmachineid字段的表名字USE abcGOSELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN(SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES )AND COLUMN_NAME =fmachineid累积求和%用自定义函数create FUNCTION getsum(月份 int)RETURNS intASbegindeclare sum intselect sum=sum(个数) from 表名 where 月份=月份return sumend然后查询select 月份,dbo.getsum(月份) as 个数 from 表名 %SQL Server 2005 自定义函数语汇小结 由于工作的需要，了解下SQLServer2005函数的写法，现在总结一下：对于SQLServer2005数据库而言，函数与存储过程在语法方面是有很大的相同点，最大的不同就是函数有返回值，直接使用returns,而存储过程则使用output来声明输出变量一、下面先说明下，如何创建函数1、创建没有返回值与没有参数的函数CREATEFUNCTIONmy_function()ASBEGINDECLAREvariablevarchar(255)-声明字符型变量DECLAREvariableint-声明整形型变量(dosomething)SETvariable=12345-对变量variable赋值END2、创建没有返回值有参数的函数CREATEFUNCTIONmy_function(user_Namevarchar(128),passwordint(6)ASBEGINDECLAREvariable_1varchar(255)-声明字符型变量(dosomething)SETvariable_1=user_Name+convert(varchar(255),password)-将变量user_Name与password连接赋给variable_1，其中convert()函数是将int型转为varchar型END3、创建有返回值与有参数的函数CREATEFUNCTIONmy_function(user_Namevarchar(128),passwordint(6)returnsvarchar(255)-设置返回值，记住是returns而不是returnASBEGINDECLAREresultvarchar(5)DECLAREfaglevarchar(5)SETresult=selectusers.user_NamefromUSERSasuserswhereusers.user_Name=user_Nameandusers.password=passwordIFresult=BEGINSETfagle=NOENDELSEBEGINSETfalge=YESENDreturnresult-返回结果END二、删除一个函数语法DROPFUNCTIONmy_function三、执行一个函数语法selectdbo.my_function()-根据有没参数来处理go-注：在SQLServer2005中，有内部函数与外部函数，数据库系统自带函数，如sum()，count()等等，这些称为内部函数，而我们自定义的函数称为外部函数。-在执行函数语法中，也有些区别，如执行内部函数：selectsum(total)from，那么执行外部函数则需要在函数名前加dbo.+自定义函数名，如-selectdbo.my_function()-go四、下面提供二个例子1、日期判断，判断传进来的日期是否在上个月日到本月日之间-请注意convert()函数的用法createfunctionisNewContract(datevarchar(32)returnsvarchar(32)ASbegindeclarebegin_timevarchar(32)declareend_timevarchar(32)declaretempvarchar(4)declareresultvarchar(32)declaremonthvarchar(2)setbegin_time=convert(varchar,DATEPART(year,getdate()+-setend_time=convert(varchar,DATEPART(year,getdate()+convert(varchar,DATEPART(month,getdate()+convert(varchar,14)settemp=convert(varchar(2),(month(getDate()-1)if(temp=begin_timeanddate0这种方法理论上比上一种方法多了一个判断语句,即0, 但这个判断过程是最快的, 我相信80%以上的运算都是花在查找字符串及其它的运算上, 所以运用charindex函数也没什么大不了。用这种方法也有好处, 那就是对%,|等在不能直接用like 查找到的字符中可以直接在这charindex中运用, 如下:usemydatabase select*fromtable1wherecharindex(%,username)0大家还可以写成:usemydatabase select*fromtable1wherecharindex(char(37),username)0ASCII的字符即为%sqlserver sql常用函数,语法 -返回一个表中所有的字段selectnamefromsyscolumnswhereid=object_id(tb_usertable_online)-获取最近添加的标识列的值setrs=identity-printlen(abcdef)-大小写转换printlower(ABCDEF)printupper(abcdef)-去空格printltrim(abcddfddf)printrtrim(abcddfddf)-求绝对值printabs(-12)-幂-3的2次方printpower(3,2)printpower(3,3)-随机数-0-1000之间的随机数printconvert(int,rand()*10)-获取圆周率printpi()-获取系统时间printgetdate()-获取3天前的时间printdateadd(day,-3,getdate()-获取3天后的时间printdateadd(day,3,getdate()-获取3年前的时间printdateadd(year,-3,getdate()-获取3年后的时间printdateadd(year,3,getdate()-获取3月后的时间printdateadd(month,3,getdate()-获取9小时后的时间printdateadd(hour,9,getdate()-获取9分钟后的时间printdateadd(minute,9,getdate()-获取指定时间之间相隔多少年printdatediff(year,2005-01-01,2008-01-01)-获取指定时间之间相隔多少月printdatediff(month,2005-01-01,2008-01-01)-获取指定时间之间相隔多少天printdatediff(day,2005-01-01,2008-01-01)-字符串合并printabc+defprintabcderprintabc+456printabc+456-类型转换printabc+convert(varchar(10),456)selecttitle_id,type,pricefromtitles-字符串连接必须保证类型一致(以下语句执行将会出错)-类型转换selecttitle_id+type+pricefromtitles-正确selecttitle_id+type+convert(varchar(10),price)fromtitlesprint123+convert(varchar(3),123)print123+123-是否可以定义一个函数-将作者编号作为参数统计其作品数量并将其返回selectau_id,au_lname,dbo.GetTitleCountByAuID(au_id)asTitleCountfromauthorsorderbyTitleCount-根据给定的作者编号获取其相应的作品数量createfunctionGetTitleCountByAuID(au_idvarchar(12)returnsintbeginreturn(selectcount(title_id)fromtitleauthorwhereau_id=au_id)end-查看表结构sp_helptitles-查看存储过程的定义内容sp_helptextGetRankByTitleIdsp_helptextsp_helptextsp_helptextxp_cmdshell-声明declarecur_titlescursorforselecttitle,pricefromtitles-打开opencur_titlesdeclaretitlevarchar(80)declarepricenumeric(9,4)declaretitle_tempvarchar(80)declareprice_tempnumeric(9,4)-提取fetchcur_titlesintotitle,pricefetchcur_titlesintotitle_temp,price_tempwhilefetch_status=0beginifprice=012：查看当前数据库中所有存储过程selectnameas存储过程名称fromsysobjectswherextype=P14：查询某一个表的字段和数据类型selectcolumn_name,data_typefrominformation_schema.columnswheretable_name=表名SQL字符函数：返回字符串中从左边开始指定个数的字符。LEFT(character_expression,integer_expression)SelectLEFT(Name,5)FROMProduction.ProductorDERBYProductID;RIGHT：返回字符表达式中从起始位置（从右端开始）到指定字符位置（从右端开始计数）的部分。RIGHT(character_expression,integer_expression)RIGHT(MountainBike,4)返回结果BikeLEN：返回给定字符串表达式的字符数（不包括尾随空格），而不是返回字节数。LEN(string_expression)SelectCompanyName,LEN(CompanyName)FROMCustomersREPLICATE：按指定次数重复字符表达式。REPLICATE(character_expression,integer_expression)下面的示例将Employee表中每一名雇员的姓氏复制两次：SelectREPLICATE(LastName,2)ASLastNameTwiceFROMEmployeesSUBSTRING：返回$sourceString的子串，从$startingLoc指定的位置开始，长度为$length指定的字符数。SUBSTRING(expression,start,length)下面的示例返回Employees表中每位雇员的名字首字母及完整姓氏：SelectSUBSTRING(FirstName,1,1)ASInitial,LastNameFROMEmployeesSTUFF：删除指定长度的字符并在指定的起始点插入另一组字符。-替换STUFF(character_expression,start,length,character_expression)SelectSTUFF(ProductID,2,1,000)FROMProductsPATINDEX：对于所有有效的文本和字符数据类型，返回指定表达式中模式第一次出现的起始位置，如果未找到模式，则返回零。PATINDEX(%pattern%,expression)下面的示例搜索其名称包含单词“Anton”的产品的列表。SelectProductName,PATINDEX(%Anton%,ProductName)FROMProductsCHARINDEX：返回字符串中指定表达式的起始位置。CHARINDEX(expression1,expression2,start_location)下面的示例从数据库中的员工姓氏中搜索表达式“an”：SelectLastName,CHARINDEX(an,LastName)ASPositionFROMEmployeessqlserver sql常用函数,语法 -返回一个表中所有的字段selectnamefromsyscolumnswhereid=object_id(tb_usertable_online)-获取最近添加的标识列的值setrs=identity-printlen(abcdef)-大小写转换printlower(ABCDEF)printupper(abcdef)-去空格printltrim(abcddfddf)printrtrim(abcddfddf)-求绝对值printabs(-12)-幂-3的2次方printpower(3,2)printpower(3,3)-随机数-0-1000之间的随机数printconvert(int,rand()*10)-获取圆周率printpi()-获取系统时间printgetdate()-获取3天前的时间printdateadd(day,-3,getdate()-获取3天后的时间printdateadd(day,3,getdate()-获取3年前的时间printdateadd(year,-3,getdate()-获取3年后的时间printdateadd(year,3,getdate()-获取3月后的时间printdateadd(month,3,getdate()-获取9小时后的时间printdateadd(hour,9,getdate()-获取9分钟后的时间printdateadd(minute,9,getdate()-获取指定时间之间相隔多少年printdatediff(year,2005-01-01,2008-01-01)-获取指定时间之间相隔多少月printdatediff(month,2005-01-01,2008-01-01)-获取指定时间之间相隔多少天printdatediff(day,2005-01-01,2008-01-01)-字符串合并printabc+defprintabcderprintabc+456printabc+456-类型转换printabc+convert(varchar(10),456)selecttitle_id,type,pricefromtitles-字符串连接必须保证类型一致(以下语句执行将会出错)-类型转换selecttitle_id+type+pricefromtitles-正确selecttitle_id+type+convert(varchar(10),price)fromtitlesprint123+convert(varchar(3),123)print123+123-是否可以定义一个函数-将作者编号作为参数统计其作品数量并将其返回selectau_id,au_lname,dbo.GetTitleCountByAuID(au_id)asTitleCountfromauthorsorderbyTitleCount-根据给定的作者编号获取其相应的作品数量createfunctionGetTitleCountByAuID(au_idvarchar(12)returnsintbeginreturn(selectcount(title_id)fromtitleauthorwhereau_id=au_id)end-查看表结构sp_helptitles-查看存储过程的定义内容sp_helptextGetRankByTitleIdsp_helptextsp_helptextsp_helptextxp_cmdshell-声明declarecur_titlescursorforselecttitle,pricefromtitles-打开opencur_titlesdeclaretitlevarchar(80)declarepricenumeric(9,4)declaretitle_tempvarchar(80)declareprice_tempnumeric(9,4)-提取fetchcur_titlesintotitle,pricefetchcur_titlesintotitle_temp,price_tempwhilefetch_status=0beginifprice=012：查看当前数据库中所有存储过程selectnameas存储过程名称fromsysobjectswherextype=P14：查询某一个表的字段和数据类型selectcolumn_name,data_typefrominformation_schema.columnswheretable_name=表名SQL字符函数：返回字符串中从左边开始指定个数的字符。LEFT(character_expression,integer_expression)SelectLEFT(Name,5)FROMProduction.ProductorDERBYProductID;RIGHT：返回字符表达式中从起始位置（从右端开始）到指定字符位置（从右端开始计数）的部分。RIGHT(character_expression,integer_expression)RIGHT(MountainBike,4)返回结果BikeLEN：返回给定字符串表达式的字符数（不包括尾随空格），而不是返回字节数。LEN(string_expression)SelectCompanyName,LEN