常用T-SQL语言命令函数集
常用T-SQL语言命令函数集(1)数据记录筛选：sql="select*from数据表where字段名=字段值orderby字段名[desc]"sql="select*from数据表where字段名like'%字段值%'orderby字段名[desc]"sql="selecttop10*from数据表where字段名orderby字段名[desc]"sql="select*from数据表where字段名in('值1','值2','值3')"sql="select*from数据表where字段名between值1and值2"(2)更新数据记录：sql="update数据表set字段名=字段值where条件表达式"sql="update数据表set字段1=值1,字段2=值2……字段n=值nwhere条件表达式"(3)删除数据记录：sql="deletefrom数据表where条件表达式"sql="deletefrom数据表"(将数据表所有记录删除)(4)添加数据记录：sql="insertinto数据表(字段1,字段2,字段3…)values(值1,值2,值3…)"sql="insertinto目标数据表select*from源数据表"(把源数据表的记录添加到目标数据表)(5)数据记录统计函数：AVG(字段名)得出一个表格栏平均值COUNT(*|字段名)对数据行数的统计或对某一栏有值的数据行数统计MAX(字段名)取得一个表格栏最大的值MIN(字段名)取得一个表格栏最小的值SUM(字段名)把数据栏的值相加引用以上函数的方法：sql="selectsum(字段名)as别名from数据表where条件表达式"
setrs=conn.excute(sql)用rs("别名")获取统的计值，其它函数运用同上。(5)数据表的建立和删除：CREATETABLE数据表名称(字段1类型1(长度),字段2类型2(长度)……)例：CREATETABLEtab01(namevarchar(50),datetimedefaultnow())DROPTABLE数据表名称(永久性删除一个数据表)==============================================================================================================================================================================添加、删除、修改使用db.Execute(Sql)命令执行操作╔----------------╗☆数据记录筛选☆╚----------------╝注意：单双引号的用法可能有误(没有测式)Sql="SelectDistinct字段名From数据表"Distinct函数，查询数据库存表内不重复的记录Sql="SelectCount(*)From数据表where字段名1>#18:0:0#and字段名1<#19:00#"“字段名1”是指同一字段count函数,查询数库表内有多少条记录，例：setrs=conn.execute("selectcount(id)asidnumfromnews")response.writers("idnum")
sql="select*from数据表where字段名between值1and值2"Sql="select*from数据表where字段名between#2003-8-10#and#2003-8-12#"在日期类数值为2003-8-1019:55:08的字段里查找2003-8-10至2003-8-12的所有记录，而不管是几点几分。select*fromtb_namewheredatetimebetween#2003-8-10#and#2003-8-12#字段里面的数据格式为：2003-8-1019:55:08，通过sql查出2003-8-10至2003-8-12的所有纪录，而不管是几点几分。
Sql="select*from数据表where字段名=字段值orderby字段名[desc]"Sql="select*from数据表where字段名like'%字段值%'orderby字段名[desc]"模糊查询Sql="selecttop10*from数据表where字段名orderby字段名[desc]"查找数据库中前10记录Sql="selecttopn*form数据表orderbynewid()"随机取出数据库中的若干条记录的方法topn，n就是要取出的记录数Sql="select*from数据表where字段名in('值1','值2','值3')"╔----------------╗☆添加数据记录☆╚----------------╝sql="insertinto数据表(字段1,字段2,字段3…)valuess(值1,值2,值3…)"
sql="insertinto数据表valuess(值1,值2,值3…)"不指定具体字段名表示将按照数据表中字段的顺序，依次添加sql="insertinto目标数据表select*from源数据表"把源数据表的记录添加到目标数据表╔----------------╗☆更新数据记录☆╚----------------╝Sql="update数据表set字段名=字段值where条件表达式"Sql="update数据表set字段1=值1,字段2=值2……字段n=值nwhere条件表达式"Sql="update数据表set字段1=值1,字段2=值2……字段n=值n"没有条件则更新整个数据表中的指定字段值╔----------------╗☆删除数据记录☆╚----------------╝Sql="deletefrom数据表where条件表达式"Sql="deletefrom数据表"没有条件将删除数据表中所有记录)╔--------------------╗
☆数据记录统计函数☆╚--------------------╝AVG(字段名)得出一个表格栏平均值COUNT(*|字段名)对数据行数的统计或对某一栏有值的数据行数统计MAX(字段名)取得一个表格栏最大的值MIN(字段名)取得一个表格栏最小的值SUM(字段名)把数据栏的值相加引用以上函数的方法：sql="selectsum(字段名)as别名from数据表where条件表达式"setrs=conn.excute(sql)用rs("别名")获取统的计值，其它函数运用同上。╔----------------------╗☆数据表的建立和删除☆╚----------------------╝CREATETABLE数据表名称(字段1类型1(长度),字段2类型2(长度)……)例：CREATETABLEtab01(namevarchar(50),datetimedefaultnow())DROPTABLE数据表名称(永久性删除一个数据表)╔--------------------╗☆记录集对象的方法☆╚--------------------╝rs.movenext将记录指针从当前的位置向下移一行rs.moveprevious将记录指针从当前的位置向上移一行rs.movefirst将记录指针移到数据表第一行
rs.movelast将记录指针移到数据表最后一行rs.absoluteposition=N将记录指针移到数据表第N行rs.absolutepage=N将记录指针移到第N页的第一行rs.pagesize=N设置每页为N条记录rs.pagecount根据pagesize的设置返回总页数rs.recordcount返回记录总数rs.bof返回记录指针是否超出数据表首端，true表示是，false为否rs.eof返回记录指针是否超出数据表末端，true表示是，false为否rs.delete删除当前记录，但记录指针不会向下移动rs.addnew添加记录到数据表末端rs.update更新数据表记录==============================================================================================================================================================================sql中日期函数的用法1.DateAdd（I,N,D）将一个日期加上一段期间后的日期。I：设定一个日期（Date）所加上的一段期间的单位。譬如interval="d"表示N的单位为日。I的设定值如下：yyyyYear年qQuarter季mMonth月dDay日wWeekday星期hHour时
nMinute分sSecond秒N：数值表达式，设定一个日期所加上的一段期间，可为正值或负值，正值表示加（结果为>date以后的日期），负值表示减（结果为>date以前的日期）。D：待加减的日期。例子：DateAdd（"m",1,"31-Jan-98")结果：28-Feb-98说明：将日期31-Jan-98加上一个月，结果为28-Feb-98而非31-Fe-98。例子：DateAdd（"d",20,"30-Jan-99")结果：1999/2/9说明：将一个日期30-Jan-99加上20天后的日期。2.Day(日期的字符串或表达式)传回日期的「日」部份。例子：Day("12/1/1999")结果：13.DateDiff(I,D1,D2[,FW[,FY]])计算两个日期之间的期间。I：设定两个日期之间的期间计算之单位。譬如>I="m"表示计算的单位为月。>I的设定值如：yyyy>Year年qQuarter季mMonth月dDay日wWeekday星期hHour时
nMinute分sSecond秒D1,D2：计算期间的两个日期表达式，若>date1较早，则两个日期之间的期间结果为正值；若>date2较早，则结果为负值。FW：设定每周第一天为星期几，若未设定表示为星期天。>FW的设定值如下：0使用>API的设定值。1星期天2星期一3星期二4星期三5星期四6星期五7星期六FY：设定一年的第一周，若未设定则表示一月一日那一周为一年的第一周。>FY的设定值如下：0使用>API的设定值。1一月一日那一周为一年的第一周2至少包括四天的第一周为一年的第一周3包括七天的第一周为一年的第一周例子：DateDiff("d","25-Mar-99","30-Jun-99")结果：97说明：显示两个日期之间的期间为97天DATEADD在向指定日期加上一段时间的基础上，返回新的datetime值。语法DATEADD(datepart,number,date)
DATEDIFF返回跨两个指定日期的日期和时间边界数。语法DATEDIFF(datepart,startdate,enddate)GETDATE按datetime值的Microsoft?SQLServer?标准内部格式返回当前系统日期和时间。语法GETDATE()DATENAME返回代表指定日期的指定日期部分的字符串。语法DATENAME(datepart,date)参考资料：SQL联机帮助丛书常用函数(function)转换函数convert(数据类型,值,格式)
统计函数AVG--求平均值COUNT--统计数目MAX--求最大值MIN--求最小值SUM--求和AVGusepanguselectavg(e_wage)asdept_avgWagefromemployeegroupbydept_idMAX--求工资最高的员工usepanguselecte_namefromemployeewheree_wage=(selectmax(e_wage)fromemployee)
STDEV()--STDEV()函数返回表达式中所有数据的标准差--STDEVP()--STDEVP()函数返回总体标准差VAR()--VAR()函数返回表达式中所有值的统计变异数VARP()--VARP()函数返回总体变异数算术函数三角函数SIN(float_expression)--返回以弧度表示的角的正弦COS(float_expression)--返回以弧度表示的角的余弦TAN(float_expression)--返回以弧度表示的角的正切COT(float_expression)--返回以弧度表示的角的余切反三角函数ASIN(float_expression)--返回正弦是FLOAT值的以弧度表示的角
ACOS(float_expression)--返回余弦是FLOAT值的以弧度表示的角ATAN(float_expression)--返回正切是FLOAT值的以弧度表示的角ATAN2(float_expression1,float_expression2)------返回正切是float_expression1/float_expres-sion2的以弧度表示的角DEGREES(numeric_expression)------把弧度转换为角度返回与表达式相同的数据类型可为------INTEGER/MONEY/REAL/FLOAT类型RADIANS(numeric_expression)------把角度转换为弧度返回与表达式相同的数据类型可为------INTEGER/MONEY/REAL/FLOAT类型EXP(float_expression)--返回表达式的指数值LOG(float_expression)--返回表达式的自然对数值LOG10(float_expression)--返回表达式的以10为底的对数值SQRT(float_expression)--返回表达式的平方根取近似值函数CEILING(numeric_expression)-------返回>=表达式的最小整数返回的数据类型与表达式相同可为-------INTEGER/MONEY/REAL/FLOAT类型FLOOR(numeric_expression)-------返回<=表达式的最小整数返回的数据类型与表达式相同可为-------INTEGER/MONEY/REAL/FLOAT类型ROUND(numeric_expression)-------返回以integer_expression为精度的四舍五入值返回的数据
-------类型与表达式相同可为INTEGER/MONEY/REAL/FLOAT类型ABS(numeric_expression)-------返回表达式的绝对值返回的数据类型与表达式相同可为-------INTEGER/MONEY/REAL/FLOAT类型SIGN(numeric_expression)-------测试参数的正负号返回0零值1正数或-1负数返回的数据类型-------与表达式相同可为INTEGER/MONEY/REAL/FLOAT类型PI()-------返回值为π即3.1415926535897936RAND([integer_expression])-------用任选的[integer_expression]做种子值得出0-1间的随机浮点数
字符串函数ASCII()------函数返回字符表达式最左端字符的ASCII码值CHAR()------函数用于将ASCII码转换为字符------如果没有输入0~255之间的ASCII码值CHAR函数会返回一个NULL值LOWER()------函数把字符串全部转换为小写UPPER()------函数把字符串全部转换为大写STR()------函数把数值型数据转换为字符型数据LTRIM()------函数把字符串头部的空格去掉RTRIM()------函数把字符串尾部的空格去掉LEFT(),RIGHT(),SUBSTRING()--函数返回部分字符串CHARINDEX(),PATINDEX()--函数返回字符串中某个指定的子串出现的开始位置SOUNDEX()------函数返回一个四位字符码------SOUNDEX函数可用来查找声音相似的字符串但SOUNDEX函数对数字和汉字均只返回0值
DIFFERENCE()------函数返回由SOUNDEX函数返回的两个字符表达式的值的差异------0两个SOUNDEX函数返回值的第一个字符不同------1两个SOUNDEX函数返回值的第一个字符相同------2两个SOUNDEX函数返回值的第一二个字符相同------3两个SOUNDEX函数返回值的第一二三个字符相同------4两个SOUNDEX函数返回值完全相同同QUOTENAME()------函数返回被特定字符括起来的字符串/**//**//**//*selectquotename('abc','{')quotename('abc')运行结果如下{{abc}[abc]*/REPLICATE()------函数返回一个重复character_expression指定次数的字符串/**//**//**//*selectreplicate('abc',3)replicate('abc',-2)运行结果如下abcabcabcNULL*/REVERSE()------函数将指定的字符串的字符排列顺序颠倒REPLACE()------函数返回被替换了指定子串的字符串/**//**//**//*selectreplace('abc123g','123','def')运行结果如下abcdefg*/
SPACE()------函数返回一个有指定长度的空白字符串STUFF()------函数用另一子串替换字符串指定位置长度的子串数据类型转换函数CAST()函数语法如下CAST()(AS[length])CONVERT()函数语法如下CONVERT()([length],[,style])selectcast(100+99aschar)convert(varchar(12),getdate())运行结果如下199Jan152000字符串进行处理Split日期函数DAY()------函数返回date_expression中的日期值MONTH()------函数返回date_expression中的月份值YEAR()------函数返回date_expression中的年份值DATEADD(,,)-----函数返回指定日期date加上指定的额外日期间隔number产生的新日期DATEDIFF(,,)-----函数返回两个指定日期在datepart方面的不同之处
SQL日期的比较问题一：SELECTDATEDIFF(Day,'2008.08.25','2008.09.01')问题二：SELECTDATEDIFF(Second,2009-8-2512:15:12',2009-9-17:18:20')--返回相差秒数或者SELECTDATEDIFF(Minute,2009-8-2512:15:12',2009-9-17:18:20')--返回相差分钟数或者SELECTDATEDIFF(Hour,2009-8-2512:15:12',2009-9-17:18:20')--返回相差小时数问题三：SELECTDATEDIFF(Day,2009-8-2512:15:12',2009-9-17:18:20')附：DATEDIFF函数使用说明：返回跨两个指定日期的日期和时间边界数。语法DATEDIFF(datepart,startdate,enddate)参数datepart是规定了应在日期的哪一部分计算差额的参数。下表列出了Microsoft?SQLServer?识别的日期部分和缩写。日期部分缩写yearyy,yyyyquarterqq,qMonthmm,mdayofyeardy,yDaydd,dWeekwk,wwHourhhminutemi,nsecondss,smillisecondmsstartdate是计算的开始日期。startdate是返回datetime或smalldatetime值或日期格式字符串的表达式。因为smalldatetime只精确到分钟，所以当用smalldatetime值时，秒和毫秒总是0。如果您只指定年份的最后两位数字，则小于或等于"两位数年份截止期"配置选项的值的最后两位数字的数字所在世纪与截止年所在世纪相同。大于该选项的值的最后两位数字的数字所在世纪为截止年所在世纪的前一个世纪。例如，如果twodigityearcutoff为2049（默认），则49被解释为2049，2050被解释为1950。为避免模糊，请使用四位数的年份。有关时间值指定的更多信息，请参见时间格式。有关日期指定的更多信息，请参见datetime和smalldatetime。enddate是计算的终止日期。enddate是返回datetime或smalldatetime值或日期格式字符串的表达式。返回类型integer注释startdate是从enddate减去。如果startdate比enddate晚，返回负值。当结果超出整数值范围，DATEDIFF产生错误。对于毫秒，最大数是24天20小时31分钟零23.647秒。对于秒，最大数是68年。计算跨分钟、秒和毫秒这些边界的方法，使得DATEDIFF给出的结果在全部数据类型中是一致的。结果是带正负号的整数值，其等于跨第一个和第二个日期间的datepart边界数。例如，在1月4日（星期日）和1月11日（星期日）之间的星期数是1。示例此示例确定在pubs数据库中标题发布日期和当前日期间的天数。USEpubsGOSELECTDATEDIFF(day,pubdate,getdate())ASno_of_daysFROMtitlesGODATENAME(,------函数以字符串的形式返回日期的指定部分DATEPART(,------函数以整数值的形式返回日期的指定部分GETDATE()------函数以DATETIME的缺省格式返回系统当前的日期和时间系统函数APP_NAME()------函数返回当前执行的应用程序的名称COALESCE()-----函数返回众多表达式中第一个非NULL表达式的值
COL_LENGTH(<'table_name'>,<'column_name'>----函数返回表中指定字段的长度值COL_NAME(,----函数返回表中指定字段的名称即列名DATALENGTH()-----函数返回数据表达式的数据的实际长度DB_ID(['database_name'])------函数返回数据库的编号DB_NAME(database_id)------函数返回数据库的名称HOST_ID()-----函数返回服务器端计算机的名称HOST_NAME()-----函数返回服务器端计算机的名称IDENTITY([,seedincrement])[AScolumn_name])--IDENTITY()函数只在SELECTINTO语句中使用用于插入一个identitycolumn列到新表中/**//**//**//*selectidentity(int,1,1)ascolumn_nameintonewtablefromoldtable*/ISDATE()----函数判断所给定的表达式是否为合理日期ISNULL(,--函数将表达式中的NULL值用指定值替换ISNUMERIC()----函数判断所给定的表达式是否为合理的数值NEWID()----函数返回一个UNIQUEIDENTIFIER类型的数值NULLIF(,----NULLIF函数在expression1与expression2相等时返回NULL值若不相等时则返回xpression1的值
--语句--数据操作SELECT
功能--从数据库表中检索数据行和列
INSERT--向数据库表添加新数据行DELETE--从数据库表中删除数据行UPDATE--更新数据库表中的数据--数据定义CREATETABLE--创建一个数据库表DROPTABLE--从数据库中删除表ALTERTABLE--修改数据库表结构CREATEVIEW--创建一个视图DROPVIEW--从数据库中删除视图CREATEINDEX--为数据库表创建一个索引DROPINDEX--从数据库中删除索引CREATEPROCEDURE--创建一个存储过程DROPPROCEDURE--从数据库中删除存储过程CREATETRIGGER--创建一个触发器DROPTRIGGER--从数据库中删除触发器CREATESCHEMA--向数据库添加一个新模式DROPSCHEMA--从数据库中删除一个模式CREATEDOMAIN--创建一个数据值域ALTERDOMAIN--改变域定义DROPDOMAIN--从数据库中删除一个域--数据控制GRANT--授予用户访问权限DENY--拒绝用户访问REVOKE--解除用户访问权限--事务控制COMMIT--结束当前事务
ROLLBACK--中止当前事务SETTRANSACTION--定义当前事务数据访问特征--程序化SQLDECLARE--为查询设定游标EXPLAN--为查询描述数据访问计划OPEN--检索查询结果打开一个游标FETCH--检索一行查询结果CLOSE--关闭游标PREPARE--为动态执行准备SQL语句EXECUTE--动态地执行SQL语句DESCRIBE--描述准备好的查询---局部变量declare@idchar(10)--set@id='10010001'select@id='10010001'---全局变量---必须以@@开头--IFELSEdeclare@xint@yint@zintselect@x=1@y=2@z=3if@x>@yprint'x>y'--打印字符串'x>y'elseif@y>@zprint'y>z'elseprint'z>y'--CASE
usepanguupdateemployeesete_wage=casewhenjob_level=’1’thene_wage*1.08whenjob_level=’2’thene_wage*1.07whenjob_level=’3’thene_wage*1.06elsee_wage*1.05end--WHILECONTINUEBREAKdeclare@xint@yint@cintselect@x=1@y=1while@x<3beginprint@x--打印变量x的值while@y<3beginselect@c=100*@x+@yprint@c--打印变量c的值select@y=@y+1endselect@x=@x+1select@y=1end--WAITFOR--例等待1小时2分零3秒后才执行SELECT语句
waitfordelay’01:02:03’select*fromemployee--例等到晚上11点零8分后才执行SELECT语句waitfortime’23:08:00’select*fromemployee
几种常用的查询
关于FROMa,b和aLEFTJOINb的区别...例表aaidadate1a12a23a3表bbidbdate1b12b24b4两个表a,b相连接,要取出id相同的字段select*froma,bwherea.aid=b.bid这是仅取出匹配的数据.此时的取出的是:1a1b12a2b2那么leftjoin指:select*fromaleftjoinbona.aid=b.bid首先取出a表中所有数据,然后再加上与a,b匹配的的数据此时的取出的是:1a1b12a2b23a3空字符同样的也有rightjoin指的是首先取出b表中所有数据,然后再加上与a,b匹配的的数据此时的取出的是:1a1b12a2b24空字符b4关于SQL数据库中crossjoin和innerjoin用法上的区别？a表namesex张三男李四女b表nameage李四30王五231\全外连接selecta.name,a.sex,b.name,b.agefromafullouterjoinbona.name=b.name结果如下，namesexnameage张三男NULLNULL
李四女李四30NULLNULL王五23关联字段name，左表有而右表没有的，如张三，b.name,b.age都为NULL，加上左右两边都有的就是左连接的结果;而右表有而左表表没有的，如王五，a.name,a.sex都为NULL，加上左右两边都有的就是右连接的结果;左右两边都有的如，李四，这就是内连接。相见如下2\左联接selecta.name,a.sex,b.name,b.agefromaleftouterjoinbona.name=b.name结果如下namesexnameage张三男NULLNULL李四女李四302\有联接selecta.name,a.sex,b.name,b.agefromarightouterjoinbona.name=b.name结果如下namesexnameage李四女李四30NULLNULL王五233\内联接selecta.name,a.sex,b.name,b.agefromainnerjoinbona.name=b.name结果如下namesexnameage李四女李四304\交叉联接selecta.name,a.sex,b.name,b.agefromacrossjoinbona.name=b.name结果如下namesexnameage张三男李四30李四女王五23张三男王五23李四女李四30
SQL中表的数据编写到XML文件中select*fromt_case_typeFORXMLAUTO,ELEMENTS其中t_case_type为表名
