【转】T-SQL 教程
时间：2022-03-16 09:56
1 USE [test] 2 GO 3 /****** Object: StoredProcedure [dbo].[PageIndex] Script Date: 12/07/2011 10:26:36 ******/ 4 SET ANSI_NULLS ON 5 GO 6 SET QUOTED_IDENTIFIER ON 7 GO 8 ALTER PROCEDURE [dbo].[PageIndex] 9 ( 10 @Tbname varchar(100),--表名字 11 @Strfield varchar(100),--查询字段 12 @idfield varchar(50),--主键ID 13 @Toppage int=10,--每页行数 14 @PageNow int=0,--查询页数 15 @strWhere varchar(100) =‘1=1‘,--查询条件 16 @Pagecount int=0 output,--总页数 17 @order varchar(200)=‘‘--排序条件 18 ) 19 AS 20 DECLARE @sql nvarchar(1000), 21 @sqloder varchar(200) 22 BEGIN 23 if(@Pagecount=0)--总页数 24 begin 25 declare @page int 26 set @sql= ‘select @Page=COUNT(‘+@idfield+‘) from ‘+@Tbname+ ‘ where ‘+@strWhere 27 print @sql 28 exec sp_executesql @sql,N‘@Page int output‘,@Page output--查询总行数 29 SELECT @pageCount=CEILING((@page+0.0)/@Toppage) --计算页数 30 print @pageCount 31 end 32 if(@order!=‘‘)--排序条件 33 begin 34 set @sqloder=‘ORDER BY ‘+@order 35 end 36 else 37 begin 38 set @sqloder=‘‘ 39 end 40 if(@strWhere!=‘‘)--查询条件 41 begin 42 set @strWhere=‘where ‘+@strWhere 43 end 44 begin--执行SQL语句 45 --/* 46 set @sql=‘SELECT TOP ‘ 47 +cast(@Toppage as varchar(200))+ 48 ‘ ‘+@Strfield+‘ FROM (SELECT ‘+@Strfield+‘,ROW_NUMBER() OVER (ORDER BY ‘ 49 +@order+‘) AS RowNumber 50 FROM ‘+@Tbname+‘) A WHERE RowNumber > ‘ 51 +cast(@Toppage as varchar(200))+‘ * (‘ 52 +cast(@PageNow as varchar(200))+‘-1) ‘+@sqloder 53 --*/ 54 --print @sql 55 /*set @sql =‘SELECT TOP ‘+cast(@Toppage as varchar(200))+‘ ‘+@Strfield+ ‘ 56 FROM ‘+@Tbname+‘ 57 WHERE ‘+@idfield+‘ > 58 ( 59 SELECT ISNULL(MAX(‘+@idfield+‘),0) 60 FROM 61 ( 62 SELECT TOP (‘+cast(@Toppage as varchar(200))+‘*(‘+cast(@PageNow as varchar(200))+‘-1)) 63 ‘+@idfield+‘ FROM ‘+@Tbname+‘ ‘+@sqloder+‘ 64 ) A 65 ) ‘+@sqloder*/ 66 print (@sql) 67 exec (@sql) 68 69 end 70 ENDView Code
以资料整理为目的，汇总一下自己从事于这近两年多的软件开发中常用的T-SQL，涵盖存储过程，分页，行转列、列转行，锁，全局变量，聚焦索引……
可能一说到T-SQL与PL/SQL大家可能有很多认知上的不足（本人也是迷糊了好长时间，才懂得其中内涵）
百度百科 名片介绍：
简单点理解，就是T-SQL是用于MS SQLServer中，而PL/SQl适用于Oracle中的了
首先看看全局变量
1 select APP_NAME ( ) as w --当前会话的应用程序 2 select @@ERROR --返回最后执行的 Transact-SQL 语句的错误代码(integer)（如果最后一条语句不出错，就一直是0） 3 select @@IDENTITY --返回最后插入的标识值 4 select USER_NAME() --返回用户数据库用户名 5 select @@CONNECTIONS --返回自上次SQL启动以来连接或试图连接的次数。 6 select GETDATE() --当前时间 7 select @@CPU_BUSY/100 --返回自上次启动SQL 以来 CPU 的工作时间，单位为毫秒 8 USE tempdb select @@DBTS as w --为当前数据库返回当前 timestamp 数据类型的值。这一 timestamp 值保证在数据库中是唯一的。 9 select @@IDLE as w --返回SQL自上次启动后闲置的时间，单位为毫秒 10 select @@IO_BUSY AS w --返回SQL自上次启动后用于执行输入和输出操作的时间，单位为毫秒 11 select @@LANGID AS w --返回当前所使用语言的本地语言标识符(ID) 12 select @@LANGUAGE AS w --返回当前使用的语言名 13 select @@LOCK_TIMEOUT as w --当前会话的当前锁超时设置，单位为毫秒。 14 select @@MAX_CONNECTIONS as w --返回SQL上允许的同时用户连接的最大数。返回的数不必为当前配置的数值 15 EXEC sp_configure --显示当前服务器的全局配置设置 16 select @@MAX_preCISION as w --返回 decimal 和 numeric 数据类型所用的精度级别，即该服务器中当前设置的精度。默认最大精度38。 17 select @@OPTIONS as w --返回当前 SET 选项的信息。 18 select @@PACK_RECEIVED as w --返回SQL自启动后从网络上读取的输入数据包数目。 19 select @@PACK_SENT as w --返回SQ自上次启动后写到网络上的输出数据包数目。 20 select @@PACKET_ERRORS as w --返回自SQL启动后，在SQL连接上发生的网络数据包错误数。 21 select @@SERVERNAME as w --返回运行SQL服务器名称。 22 select @@SERVICENAME as w --返回SQL正在其下运行的注册表键名 23 select @@TIMETICKS as w --返回SQL服务器一刻度的微秒数 24 select @@TOTAL_ERRORS AS w --返回 SQL服务器自启动后，所遇到的磁盘读/写错误数。 25 select @@TOTAL_READ as w --返回 SQL服务器自启动后读取磁盘的次数。 26 select @@TOTAL_WRITE as w --返回SQL服务器自启动后写入磁盘的次数。 27 select @@TRANCOUNT as w --返回当前连接的活动事务数。 28 select @@VERSION as w --返回SQL服务器安装的日期、版本和处理器类型。View Code
再来看看针对MSSQL的备份语句
1 --查询文件是否存在 2 CREATE TABLE #tmp ([File Exists] BIT, [File is a Directory] BIT, [Parent Directory Exists] BIT) 3 INSERT INTO #tmp ([File Exists], [File is a Directory], [Parent Directory Exists]) 4 EXEC master.dbo.xp_fileexist ‘D:\Test.bak‘ 5 SELECT * FROM #tmp 6 DROP TABLE #tmp 7 8 --完整备份数据库 9 10 BACKUP DATABASE MyBakTest TO DISK=‘D:\Test.bak‘ WITH FORMAT 11 12 --对tb表插入第一条记录 13 14 INSERT INTO tb_user(uname) VALUES(‘第一1次差异备份‘) 15 16 --第一次差异备份 17 18 BACKUP DATABASE MyBakTest TO DISK=‘D:\Test_One_Dif.bak‘ WITH FORMAT,DIFFERENTIAL 19 20 --对tb表插入第二条记录 21 22 INSERT INTO tb_user(uname) VALUES(‘第二3次差异备份‘) 23 24 --第二次差异备份 25 26 BACKUP DATABASE MyBakTest TO DISK=‘D:\Test_Two_Dif.bak‘ WITH FORMAT,DIFFERENTIAL 27 28 --OK.现在直接还原.完整备份+第二次差异备份.看看第二条记录在不在. 29 30 GO 31 32 --完整备份还原 33 RESTORE DATABASE MyBackTest2 FROM DISK=‘D:\Test.bak‘ WITH REPLACE,NORECOVERY 34 35 RESTORE DATABASE MyBackTest2 FROM DISK=‘D:\Test_one_Dif.bak‘ WITH RECOVERY 36 37 --第二次差异备份还原 38 RESTORE DATABASE MyBackTest2 FROM DISK=‘D:\Test_Two_Dif.bak‘ WITH RECOVERYView Code
用的次数不是很多，但是每次都会让我纠结一会的行转列，列转行，有了这个示例，就可以轻松应对这类问题了：
1 --行转列 2 create table #CarLog(LogDate datetime,PathLine nvarchar(10),Amount int) 3 --drop table #CarLog 4 insert into #CarLog(LogDate,PathLine,Amount)VALUES(‘2011-12-24‘,‘1号线‘,1) 5 insert into #CarLog(LogDate,PathLine,Amount)VALUES(‘2011-12-25‘,‘1号线‘,91) 6 insert into #CarLog(LogDate,PathLine,Amount)VALUES(‘2011-12-26‘,‘1号线‘,66) 7 insert into #CarLog(LogDate,PathLine,Amount)VALUES(‘2011-12-24‘,‘3号线‘,44) 8 insert into #CarLog(LogDate,PathLine,Amount)VALUES(‘2011-12-24‘,‘3号线‘,33) 9 insert into #CarLog(LogDate,PathLine,Amount)VALUES(‘2011-12-24‘,‘5号线‘,12) 10 insert into #CarLog(LogDate,PathLine,Amount)VALUES(‘2011-12-24‘,‘6号线‘,22) 11 12 select * from #CarLog 13 14 --方法1 15 SELECT LogDate, 16 (CASE WHEN PathLine=‘1号线‘ THEN Amount ELSE 0 END)AS ‘1号线‘, 17 (CASE WHEN PathLine=‘2号线‘ THEN Amount ELSE 0 END)AS ‘2号线‘, 18 (CASE WHEN PathLine=‘3号线‘ THEN Amount ELSE 0 END)AS ‘3号线‘, 19 (CASE WHEN PathLine=‘4号线‘ THEN Amount ELSE 0 END)AS ‘4号线‘, 20 (CASE WHEN PathLine=‘5号线‘ THEN Amount ELSE 0 END)AS ‘5号线‘, 21 (CASE WHEN PathLine=‘6号线‘ THEN Amount ELSE 0 END)AS ‘6号线‘ 22 FROM #CarLog 23 Group By PathLine,LogDate,Amount 24 25 26 27 --方法2 28 declare @Str nvarchar(max) 29 set @str=‘select [LogDate]‘ 30 select @str=@str+‘,[‘+PathLine+‘]‘ from #CarLog group by PathLine 31 --exec @str 32 print @str 33 set @str=@str+‘ FROM ( 34 SELECT LogDate,Amount,PathLine 35 FROM #CarLog ) AS c 36 PIVOT ( sum(Amount) FOR PathLine IN 37 (‘ 38 select @str=@str+‘[‘+PathLine+‘],‘ from #CarLog group by PathLine 39 40 set @str=left(@str,Len(@str)-1) 41 set @str=@str+ ‘)) AS thePivot 42 ORDER BY LogDate‘ 43 print @str 44 45 declare @Table table(Logdate datetime,1号线 int, 3号线 int,4号线 int,5号线 int,6号线 int) 46 INSERT INTO @Table exec(@str) 47 SELECT * FROM @Table 48 --drop table #T 49 50 51 52 53 select [LogDate],[1号线],[3号线],[5号线],[6号线] FROM ( 54 SELECT LogDate,Amount,PathLine 55 FROM #CarLog ) AS cl 56 PIVOT ( sum(Amount) FOR PathLine IN 57 ([1号线],[3号线],[5号线],[6号线])) AS thePivot 58 ORDER BY LogDate 59 ----------------------------------分割线----------------------------------------------------- 60 CREATE TABLE [#StudentScores] 61 ( 62 [UserName] NVARCHAR(20), --学生姓名 63 [Subject] NVARCHAR(30), --科目 64 [Score] FLOAT, --成绩 65 ) 66 67 INSERT INTO [#StudentScores] SELECT ‘Nick‘, ‘语文‘, 81 68 INSERT INTO [#StudentScores] SELECT ‘Nick‘, ‘数学‘, 91 69 INSERT INTO [#StudentScores] SELECT ‘Nick‘, ‘英语‘, 72 70 INSERT INTO [#StudentScores] SELECT ‘Nick‘, ‘生物‘, 83 71 INSERT INTO [#StudentScores] SELECT ‘Kent‘, ‘语文‘, 84 72 INSERT INTO [#StudentScores] SELECT ‘Kent‘, ‘数学‘, 99 73 INSERT INTO [#StudentScores] SELECT ‘Kent‘, ‘英语‘, 77 74 INSERT INTO [#StudentScores] SELECT ‘Kent‘, ‘生物‘, 79 75 --drop table #StudentScores 76 --------------------------- 77 select * from #StudentScores 78 79 SELECT 80 UserName, 81 MAX(CASE Subject WHEN ‘语文‘ THEN Score ELSE 0 END) AS ‘语文‘, 82 MAX(CASE Subject WHEN ‘数学‘ THEN Score ELSE 0 END) AS ‘数学‘, 83 MAX(CASE Subject WHEN ‘英语‘ THEN Score ELSE 0 END) AS ‘英语‘, 84 MAX(CASE Subject WHEN ‘生物‘ THEN Score ELSE 0 END) AS ‘生物‘ 85 FROM dbo.[#StudentScores] 86 GROUP BY UserName 87 88 -----------------------------分割线---------------------------------------------------- 89 CREATE TABLE [#Inpours] 90 ( 91 [ID] [int] IDENTITY(1,1), 92 [UserName] NVARCHAR(20), --游戏玩家 93 [CreateTime] DATETIME, --充值时间 94 [PayType] NVARCHAR(20), --充值类型 95 [Money] DECIMAL, --充值金额 96 [IsSuccess] BIT, --是否成功 1表示成功， 0表示失败 97 CONSTRAINT [PK_Inpours_ID] PRIMARY KEY(ID) 98 ) 99 INSERT INTO [#Inpours] SELECT ‘张三‘, ‘2010-05-01‘, ‘支付宝‘, 50, 1 100 INSERT INTO [#Inpours] SELECT ‘张三‘, ‘2010-06-14‘, ‘支付宝‘, 50, 1 101 INSERT INTO [#Inpours] SELECT ‘张三‘, ‘2010-06-14‘, ‘手机短信‘, 100, 1 102 INSERT INTO [#Inpours] SELECT ‘李四‘, ‘2010-06-14‘, ‘手机短信‘, 100, 1 103 INSERT INTO [#Inpours] SELECT ‘李四‘, ‘2010-07-14‘, ‘支付宝‘, 100, 1 104 INSERT INTO [#Inpours] SELECT ‘王五‘, ‘2010-07-14‘, ‘工商银行卡‘, 100, 1 105 INSERT INTO [#Inpours] SELECT ‘赵六‘, ‘2010-07-14‘, ‘建设银行卡‘, 100, 1 106 107 select * from #Inpours 108 109 SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime, 110 CASE PayType WHEN ‘支付宝‘ THEN SUM(Money) ELSE 0 END AS ‘支付宝‘, 111 CASE PayType WHEN ‘手机短信‘ THEN SUM(Money) ELSE 0 END AS ‘手机短信‘, 112 CASE PayType WHEN ‘工商银行卡‘ THEN SUM(Money) ELSE 0 END AS ‘工商银行卡‘, 113 CASE PayType WHEN ‘建设银行卡‘ THEN SUM(Money) ELSE 0 END AS ‘建设银行卡‘ 114 FROM #Inpours 115 GROUP BY CreateTime, PayType 116 117 118 ------ 119 SELECT 120 CreateTime, 121 ISNULL(SUM([支付宝]), 0) AS [支付宝], 122 ISNULL(SUM([手机短信]), 0) AS [手机短信], 123 ISNULL(SUM([工商银行卡]), 0) AS [工商银行卡], 124 ISNULL(SUM([建设银行卡]), 0) AS [建设银行卡] 125 FROM 126 ( 127 SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime, 128 CASE PayType WHEN ‘支付宝‘ THEN SUM(Money) ELSE 0 END AS ‘支付宝‘, 129 CASE PayType WHEN ‘手机短信‘ THEN SUM(Money) ELSE 0 END AS ‘手机短信‘, 130 CASE PayType WHEN ‘工商银行卡‘ THEN SUM(Money) ELSE 0 END AS ‘工商银行卡‘, 131 CASE PayType WHEN ‘建设银行卡‘ THEN SUM(Money) ELSE 0 END AS ‘建设银行卡‘ 132 FROM #Inpours 133 GROUP BY CreateTime, PayType 134 ) T 135 GROUP BY CreateTime 136 ------ 137 DECLARE @cmdText VARCHAR(8000); 138 DECLARE @tmpSql VARCHAR(8000); 139 140 SET @cmdText = ‘SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime,‘ + CHAR(10); 141 print @cmdText 142 SELECT @cmdText = @cmdText + ‘ CASE PayType WHEN ‘‘‘ + PayType + ‘‘‘ THEN SUM(Money) ELSE 0 END AS ‘‘‘ + PayType 143 + ‘‘‘,‘ + CHAR(10) FROM (SELECT DISTINCT PayType FROM #Inpours ) T 144 print @cmdText 145 SET @cmdText = LEFT(@cmdText, LEN(@cmdText) -2) --注意这里，如果没有加CHAR(10) 则用LEFT(@cmdText, LEN(@cmdText) -1) 146 147 SET @cmdText = @cmdText + ‘ FROM #Inpours GROUP BY CreateTime, PayType ‘; 148 print @cmdText 149 SET @tmpSql =‘SELECT CreateTime,‘ + CHAR(10); 150 SELECT @tmpSql = @tmpSql + ‘ ISNULL(SUM(‘ + PayType + ‘), 0) AS ‘‘‘ + PayType + ‘‘‘,‘ + CHAR(10) 151 FROM (SELECT DISTINCT PayType FROM #Inpours ) T 152 print @tmpSql 153 SET @tmpSql = LEFT(@tmpSql, LEN(@tmpSql) -2) + ‘ FROM (‘ + CHAR(10); 154 print @tmpSql 155 SET @cmdText = @tmpSql + @cmdText + ‘) T GROUP BY CreateTime ‘; 156 PRINT @cmdText 157 EXECUTE (@cmdText); 158 ------- 159 SELECT 160 CreateTime, [支付宝] , [手机短信],[工商银行卡] , [建设银行卡] 161 FROM 162 ( 163 SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime,PayType, Money 164 FROM #Inpours 165 ) P 166 PIVOT ( 167 SUM(Money) 168 FOR PayType IN 169 ([支付宝], [手机短信], [工商银行卡], [建设银行卡]) 170 ) AS T 171 ORDER BY CreateTime 172 173 ---- 174 /* 175 消息 325，级别 15，状态 1，第 9 行 176 177 ‘PIVOT‘ 附近有语法错误。您可能需要将当前数据库的兼容级别设置为更高的值，以启用此功能。有关存储过程 sp_dbcmptlevel 的信息，请参见帮助。 178 这个是因为：对升级到 SQL Server 2005 或更高版本的数据库使用 PIVOT 和 UNPIVOT 时，必须将数据库的兼容级别设置为 90 或更高。 179 有关如何设置数据库兼容级别的信息，请参阅 sp_dbcmptlevel (Transact-SQL)。 180 例如，只需在执行上面脚本前加上 EXEC sp_dbcmptlevel Test, 90; 就OK了， Test 是所在数据库的名称。 181 **/ 182 183 184 ------------------------------------ 185 CREATE TABLE #ProgrectDetail 186 ( 187 ProgrectName NVARCHAR(20), --工程名称 188 OverseaSupply INT, --海外供应商供给数量 189 NativeSupply INT, --国内供应商供给数量 190 SouthSupply INT, --南方供应商供给数量 191 NorthSupply INT --北方供应商供给数量 192 ) 193 --drop table #ProgrectDetail 194 195 INSERT INTO #ProgrectDetail 196 SELECT ‘A‘, 100, 200, 50, 50 197 UNION ALL 198 SELECT ‘B‘, 200, 300, 150, 150 199 UNION ALL 200 SELECT ‘C‘, 159, 400, 20, 320 201 UNION ALL 202 SELECT ‘D‘, 250, 30, 15, 15 203 204 select * from #ProgrectDetail 205 ------ 206 SELECT ProgrectName, ‘OverseaSupply‘ AS Supplier, 207 MAX(OverseaSupply) AS ‘SupplyNum‘ 208 FROM #ProgrectDetail 209 GROUP BY ProgrectName 210 UNION ALL 211 SELECT ProgrectName, ‘NativeSupply‘ AS Supplier, 212 MAX(NativeSupply) AS ‘SupplyNum‘ 213 FROM #ProgrectDetail 214 GROUP BY ProgrectName 215 UNION ALL 216 SELECT ProgrectName, ‘SouthSupply‘ AS Supplier, 217 MAX(SouthSupply) AS ‘SupplyNum‘ 218 FROM #ProgrectDetail 219 GROUP BY ProgrectName 220 UNION ALL 221 SELECT ProgrectName, ‘NorthSupply‘ AS Supplier, 222 MAX(NorthSupply) AS ‘SupplyNum‘ 223 FROM #ProgrectDetail 224 GROUP BY ProgrectName 225 -------- 226 SELECT ProgrectName,Supplier,SupplyNum 227 FROM 228 ( 229 SELECT ProgrectName, OverseaSupply, NativeSupply, 230 SouthSupply, NorthSupply 231 FROM #ProgrectDetail 232 )T 233 UNPIVOT 234 ( 235 SupplyNum FOR Supplier IN 236 (OverseaSupply, NativeSupply, SouthSupply, NorthSupply ) 237 ) PView Code
1 ----------------------行转列问题----------------- 2 CREATE TABLE #test( 3 EmployeeID INT, 4 CardTime DATETIME 5 ); 6 go 7 8 INSERT INTO #test 9 SELECT 1, ‘2011-08-01 08:01:27‘ UNION ALL 10 SELECT 1, ‘2011-08-01 11:33:27‘ UNION ALL 11 SELECT 1, ‘2011-08-01 13:32:27‘ UNION ALL 12 SELECT 1, ‘2011-08-01 17:30:27‘ UNION ALL 13 SELECT 2, ‘2011-08-01 08:22:27‘ UNION ALL 14 SELECT 2, ‘2011-08-01 11:21:27‘ UNION ALL 15 SELECT 2, ‘2011-08-01 17:32:27‘ 16 go 17 18 19 -------------------------------- 20 with myCTE AS ( 21 SELECT 22 c.EmployeeID, 23 c.CardTime, 24 CASE 25 WHEN (convert(varchar(10), c.CardTime ,8) between ‘07:00:00‘ and ‘10:00:00‘) 26 THEN ‘上午上班时间‘ 27 WHEN ( convert(varchar(10), c.CardTime ,8) between ‘11:00:00‘ and ‘13:00:00‘) 28 THEN ‘上午下班时间‘ 29 WHEN ( convert(varchar(10), c.CardTime ,8) between ‘13:00:00‘ and ‘15:00:00‘) 30 THEN ‘下午上班时间 ‘ 31 ELSE 32 ‘下午下班时间‘ 33 END as TimePart 34 FROM 35 #test c 36 ) 37 SELECT 38 * 39 FROM 40 myCTE 41 PIVOT( 42 MIN(CardTime) 43 FOR TimePart IN ([上午上班时间], [上午下班时间], [下午上班时间], [下午下班时间] ) 44 ) tmp 45 46 ----------------------------- 47 with myCTE AS ( 48 SELECT 49 c.EmployeeID, 50 CONVERT(varchar(10), c.CardTime, 120) AS CardDate, 51 c.CardTime, 52 CASE 53 WHEN (convert(varchar(10), c.CardTime ,8) between ‘07:00:00‘ and ‘10:00:00‘) 54 THEN ‘上午上班时间‘ 55 WHEN ( convert(varchar(10), c.CardTime ,8) between ‘11:00:00‘ and ‘13:00:00‘) 56 THEN ‘上午下班时间‘ 57 WHEN ( convert(varchar(10), c.CardTime ,8) between ‘13:00:00‘ and ‘15:00:00‘) 58 THEN ‘下午上班时间 ‘ 59 ELSE 60 ‘下午下班时间‘ 61 END as TimePart 62 FROM 63 #test c 64 ), 65 baseCTE AS ( 66 SELECT 67 EmployeeID, 68 CardDate, 69 MAX( CASE WHEN TimePart=‘上午上班时间‘ THEN CardTime ELSE NULL END ) AS [上午上班时间], 70 MIN( CASE WHEN TimePart=‘上午下班时间‘ THEN CardTime ELSE NULL END ) AS [上午下班时间], 71 MAX( CASE WHEN TimePart=‘下午上班时间‘ THEN CardTime ELSE NULL END ) AS [下午上班时间], 72 MIN( CASE WHEN TimePart=‘下午下班时间‘ THEN CardTime ELSE NULL END ) AS [下午下班时间] 73 FROM 74 myCTE 75 GROUP BY 76 EmployeeID, 77 CardDate 78 ) 79 SELECT 80 EmployeeID, 81 CardDate, 82 [上午上班时间], 83 [上午下班时间], 84 [下午上班时间], 85 [下午下班时间], 86 CASE 87 WHEN (convert(varchar(10), [上午上班时间] ,8) between ‘08:35:00‘ and ‘10:00:00‘) 88 THEN 1 89 ELSE 0 90 END AS [上午迟到], 91 CASE 92 WHEN (convert(varchar(10), [上午下班时间] ,8) between ‘10:00:00‘ and ‘12:00:00‘) 93 THEN 1 94 ELSE 0 95 END AS 96 [上午早退], 97 CASE 98 WHEN (convert(varchar(10), [下午上班时间] ,8) between ‘14:05:00‘ and ‘15:00:00‘) 99 THEN 1 100 ELSE 0 101 END AS 102 [下午迟到], 103 CASE 104 WHEN (convert(varchar(10), [下午下班时间] ,8) between ‘17:00:00‘ and ‘17:30:00‘) 105 THEN 1 106 ELSE 0 107 END AS [下午早退] 108 FROM 109 baseCTE;View Code
将查询出来的结果集转换为XML类型
1 /** 2 Select 的查询结果会作为行集返回，但是你同样可以在sql中指定for xml子句使得查询作为xml来检索。 3 在for xml子句中，可以指定以下模式之一：RAW 、AUTO、EXPLICIT和PATH。 4 RAW模式返回行为元素，每一列的值作为元素的属性； 5 AUTO模式返回表名为节点的元素，每一列的属性作为属性输出； 6 EXPLICIT模式通过SELECT语法定义输出XML结构； 7 PATH模式中列名或列别名作为XPATH表达式来处理。 8 **/ 9 --xml raw 10 SELECT TOP 5 CCode,CName FROM dbo.city FOR XML RAW 11 --xml auto 12 SELECT TOP 5 ProductName,UnitPrice FROM dbo.Products FOR XML AUTO 13 --xml explict 14 SELECT TOP 5 1 AS Tag,0 AS Parent, PCode AS [Province!1!ID],PName AS [Province!1!Name],PCode AS [Province!1!Customer],NULL AS [City!2!ID],NULL AS [City!2!CName],NULL AS [City!2!CityID] 15 FROM dbo.Province WHERE dbo.Province.PCOde=‘105‘ 16 UNION ALL 17 SELECT TOP 5 2 AS Tag,1 AS Parent, NULL,NULL ,NULL,ID,CName,0 FROM dbo.city WHERE PCOde=‘105‘ 18 FOR XML EXPLICIT 19 --PATH 20 SELECT TOP 5 CName AS CityName, CCode AS CityID FROM dbo.City FOR XML PATH(‘City‘) 21 /** 22 RAW模式和AUTO模式相对比较简单，灵活性当然也是比较差的。 23 EXPLICIT模式会将查询执行生成的行集转换为XML文档，也就是说结构可以自定义，当然这必须符合一定的格式。 24 就拿上面的语句来说：Tag指定生成节点的嵌套级别，列名必须为"Tag"例如上面的级别为1。 25 Parent指定当前Tag的父级层次，列名必须为"Parent"，NULL表示顶级。 26 其他列名，例如[Order!1!ID]分别代表元素名称、Tag标记（也就是层级）、属性名称。 27 例如上面的这一列就代表会在第一级节点中生成一个节点名称为Order的节点并且有一个属性ID。 28 此外还需要指出的是这个列名还有一个可选部分，完整的形式是[ElementName!TagNumber!AttributeName!Directive] 29 ，最后一部分Directive是可选的，如果不指定的话默认作为ElementName中的属性名称； 30 当然如果制定了xml、cdata或者element那么它将作为ElementName的一个子元素而不是属性。 31 并且指定了此选项之后AttributeName可以为空。 32 上面的例子中如果去掉For XML EXPLICIT之后查询到的数据时如下形式： 33 34 按照上面的规则不难看出，FOR XML EXPLICIT会按照顺序生成xml： 35 第一次执行（第一行数据）时tag为1，parent为0， 36 则此时会按照列名创建一个Order节点，并依次创建三个属性：ID、Date和Customer， 37 其值为：10248、1996-07-04 00:00:00.000、VINET。此时不会生成ProductID属性， 38 因为它的Tag为2，需要在第二级节点中创建，其他另个属性也是如此。 39 第二次执行（第二行数据）时Tag为2，Parent为1， 40 也就是要在Tag为1的节点中创建一个2级节点。 41 然后根据查找列名中Tag为2的列，依次创建ProductID、UnitPrice、Quantity属性，并赋值。 42 第三次、第四次同第二次执行类似，不同的只是数据而已。 43 PATH模式应该说是使用率相当高的，原因比较简单，它的使用既简单又灵活。 44 在PATH模式中，节点名称可以使列名也可以通过别名指定又或者根本不指定，不指定的情况下就只显示节点内数据； 45 根节点可以指定也可以不指定，不指定情况下默认为"row"，并且根节点指定为""的情况下可以完全取消根节点的显示。 46 有了这一属性就可以使用PATH模式完成很多有用的操作。 47 没有指定根节点时： 48 49 */ 50 --没有指定根节点时 51 SELECT TOP 5 CName FROM dbo.City FOR XML PATH 52 --去掉根节点名称 53 SELECT TOP 5 CName FROM dbo.City FOR XML PATH(‘‘) 54 --去掉根节点和子节点名称 55 SELECT TOP 5 CName+‘,‘ FROM dbo.City FOR XML PATH(‘‘) 56 --可以发现去掉节点名称之后for xml path起到了字符串连接的作用，这样一来就可以利用这一特性完成字符串拼接 57 SELECT LEFT(CName,(LEN(CName)-1)) FROM( 58 SELECT 59 (SELECT TOP 5 CName+‘,‘ FROM dbo.City FOR XML PATH(‘‘)) AS CName 60 ) AS T 61 62 63 select * from CityView Code
当遇到夸库查询时，可以采用这种方法：
库名.dbo.表名，当然更加建议您使用 不同的数据库连接语句来操作
前段时间，才更加清楚地认识到了COUNT(字段)与COUNT(*)之间的区别，亡羊补牢，为时不晚
1 最后一个介绍的函数就是统计记录数量的COUNT，这个函数有一点特别，因为它的即可以 2 像其他聚合函数一样使用字段名做参数，也可以使用星号“*”做为参数。我们执行下面的SQL： 3 SELECT COUNT(*),COUNT(FNumber) FROM T_Employee 4 执行完毕我们就能在输出结果中看到下面的执行结果： 5 8 8 6 可以看到COUNT(*)、COUNT(FNumber)两种方式都能统计出记录的条数，据此为数不少 7 的开发人员都认为COUNT(*)、COUNT(字段名)这两种使用方式是等价的。下面通过例子来说 8 明，为了看到两种使用方式的区别需要首先向表T_Employee 中插入一条新的数据，执行下面 9 的SQL： 10 INSERT INTO T_Employee(FNumber,FAge,FSalary) VALUES(‘IT002‘,27,2800) 11 需要注意的就是这句INSERT语句没有为FName 字段赋值，也就是说新插入的这条数据的 12 FName 字段值为空，可以执行SELECT * FROM T_Employee 来查看表T_Employee 中的 13 内容： 14 FNumber FName FAge FSalary 15 DEV001 Tom 25 8300.00 16 DEV002 Jerry 28 2300.80 17 HR001 Jane 23 2200.88 18 HR002 Tina 25 5200.36 19 IT001 Smith 28 3900.00 20 IT002 <NULL> 27 2800.00 21 SALES001 John 23 5000.00 22 SALES002 Kerry 28 6200.00 23 SALES003 Stone 22 1200.00 24 可以看到FNumber 为IT002 的行的FName字段是空值。接着执行下面的SQL： 25 SELECT COUNT(*),COUNT(FNumber),COUNT(FName) FROM T_Employee 26 执行完毕我们就能在输出结果中看到下面的执行结果： 27 9 9 8 28 可以看到COUNT(*)、COUNT(FNumber)两个表达式的计算结果都是9，而COUNT(FName) 29 的计算结果是8。也就反应出了两种使用方式的区别：COUNT(*)统计的是结果集的总条数，而 30 COUNT(FName)统计的则是除了结果集中FName 不为空值（也就是不等于NULL）的记录的总 31 条数。由于FNumber 为IT002 的行的FName 字段是空值，所以COUNT(FName)的计算结果 32 是8。因此在使用聚合函数COUNT 的时候一定要区分两种使用方式的区别，以防止出现数据错 33 误。 34 [[提示：如果查询出来的条件没有NULL值 ，那么俩种查询条件无任何区别。]]View Code
不知道，您有没有在你的项目中这样来使用GOTO语句
1 /** 2 GOTO语句可以实现无条件跳转 3 语法格式 GOTO lable 其中lable为要跳转到的语句标号 4 遇到GOTO语句时直接跳转到lable标签处继续执行,而goto之后的语句不再执行 5 **/ 6 7 declare @result int=0 8 declare @sn int =0 9 loop_1:--定义标号 10 set @result=@result+@sn 11 set @sn=@sn+1 12 if(@sn<=100) 13 goto loop_1--如果小于100就跳转到loop_1标号处 14 print @resultView Code
不知道，您有没有在SQLServer中尝试连接过Access？
1 ---------------在SQLServer中连接Access数据库---------------- 2 3 --启用Ad Hoc Distributed Queries： 4 exec sp_configure ‘show advanced options‘,1 5 reconfigure 6 exec sp_configure ‘Ad Hoc Distributed Queries‘,1 7 reconfigure 8 --使用完成后，关闭Ad Hoc Distributed Queries： 9 exec sp_configure ‘Ad Hoc Distributed Queries‘,0 10 reconfigure 11 exec sp_configure ‘show advanced options‘,0 12 reconfigure 13 14 15 SELECT * 16 FROM OpenDataSource( ‘Microsoft.Jet.OLEDB.4.0‘,‘Data Source="D:\worksystem.mdb";User ID=Admin;Password=;Extended properties=Excel 5.0‘)...TimeRecords 17 18 SELECT * 19 FROM OpenDataSource( ‘Microsoft.Jet.OLEDB.4.0‘,‘Data Source="D:\worksystem.mdb";User ID=Admin;Password=;‘)...TimeRecords 20 21 SELECT * 22 FROM OpenDataSource( ‘Microsoft.Jet.OLEDB.4.0‘,‘Data Source="D:\worksystem.mdb";Jet OLEDB:Database Password=;‘)...TimeRecordsView Code
针对分页，有很多类型，不过下面这几种，都是自己在网上集锦下来的，在这里感谢博客园感谢自己的老总MVP，本着分享的目的，再在这里啰嗦一下^
1 USE [test] 2 GO 3 /****** Object: StoredProcedure [dbo].[PageIndex] Script Date: 12/07/2011 10:26:36 ******/ 4 SET ANSI_NULLS ON 5 GO 6 SET QUOTED_IDENTIFIER ON 7 GO 8 ALTER PROCEDURE [dbo].[PageIndex] 9 ( 10 @Tbname varchar(100),--表名字 11 @Strfield varchar(100),--查询字段 12 @idfield varchar(50),--主键ID 13 @Toppage int=10,--每页行数 14 @PageNow int=0,--查询页数 15 @strWhere varchar(100) =‘1=1‘,--查询条件 16 @Pagecount int=0 output,--总页数 17 @order varchar(200)=‘‘--排序条件 18 ) 19 AS 20 DECLARE @sql nvarchar(1000), 21 @sqloder varchar(200) 22 BEGIN 23 if(@Pagecount=0)--总页数 24 begin 25 declare @page int 26 set @sql= ‘select @Page=COUNT(‘+@idfield+‘) from ‘+@Tbname+ ‘ where ‘+@strWhere 27 print @sql 28 exec sp_executesql @sql,N‘@Page int output‘,@Page output--查询总行数 29 SELECT @pageCount=CEILING((@page+0.0)/@Toppage) --计算页数 30 print @pageCount 31 end 32 if(@order!=‘‘)--排序条件 33 begin 34 set @sqloder=‘ORDER BY ‘+@order 35 end 36 else 37 begin 38 set @sqloder=‘‘ 39 end 40 if(@strWhere!=‘‘)--查询条件 41 begin 42 set @strWhere=‘where ‘+@strWhere 43 end 44 begin--执行SQL语句 45 --/* 46 set @sql=‘SELECT TOP ‘ 47 +cast(@Toppage as varchar(200))+ 48 ‘ ‘+@Strfield+‘ FROM (SELECT ‘+@Strfield+‘,ROW_NUMBER() OVER (ORDER BY ‘ 49 +@order+‘) AS RowNumber 50 FROM ‘+@Tbname+‘) A WHERE RowNumber > ‘ 51 +cast(@Toppage as varchar(200))+‘ * (‘ 52 +cast(@PageNow as varchar(200))+‘-1) ‘+@sqloder 53 --*/ 54 --print @sql 55 /*set @sql =‘SELECT TOP ‘+cast(@Toppage as varchar(200))+‘ ‘+@Strfield+ ‘ 56 FROM ‘+@Tbname+‘ 57 WHERE ‘+@idfield+‘ > 58 ( 59 SELECT ISNULL(MAX(‘+@idfield+‘),0) 60 FROM 61 ( 62 SELECT TOP (‘+cast(@Toppage as varchar(200))+‘*(‘+cast(@PageNow as varchar(200))+‘-1)) 63 ‘+@idfield+‘ FROM ‘+@Tbname+‘ ‘+@sqloder+‘ 64 ) A 65 ) ‘+@sqloder*/ 66 print (@sql) 67 exec (@sql) 68 69 end 70 ENDView Code
其实分页也可以这样来写
1 --假设数据库中有张表，表名是UserName，字段分别是ID（int），Name（nvarchar），Age（int）。 2 --如果不带查询条件存储过程是： 3 CREATE PROCEDURE [dbo].[UserName] 4 @pageIndex int, 5 @pageSize int 6 AS 7 declare @min int; 8 declare @max int; 9 set @min=@pageSize*(@pageIndex-1)+1; 10 set @max=@pageSize*@pageIndex; 11 with myTable as(select ID,Name,Age,Row_Number() over (order by ID) as rownum from [UserName] ) 12 select ID,Name,Age from myTable where rownum between @min and @max 13 RETURN 14 --这个分页存储过程很不实用，并且表是固定的。 15 --下面十二个万能分页存储过程， 16 CREATE PROCEDURE [dbo].[UP_GetRecordByPage] 17 @tblName varchar(255), -- 表名 18 @fldName varchar(255), -- 主键字段名 19 @PageSize int , -- 页尺寸 20 @PageIndex int , -- 页码 21 @IsReCount bit, -- 返回记录总数, 非 0 值则返回 22 @OrderType bit, -- 设置排序类型, 非 0 值则降序 23 @strWhere varchar(1000) = ‘‘ -- 查询条件 (注意: 不要加 where) 24 AS 25 declare @strSQL varchar(6000) -- 主语句 26 declare @strTmp varchar(2000) -- 临时变量(查询条件过长时可能会出错，可修改100为1000) 27 declare @strOrder varchar(400) -- 排序类型 28 if @OrderType != 0 29 begin 30 set @strTmp = ‘<(select min‘ 31 set @strOrder = ‘ order by [‘ + @fldName +‘] desc‘ 32 end 33 else 34 begin 35 set @strTmp = ‘>(select max‘ 36 set @strOrder = ‘ order by [‘ + @fldName +‘] asc‘ 37 end 38 set @strSQL = ‘select top ‘ + str(@PageSize) + ‘ * from [‘ 39 + @tblName + ‘] where [‘ + @fldName + ‘]‘ + @strTmp + ‘([‘ 40 + @fldName + ‘]) from (select top ‘ + str((@PageIndex-1)*@PageSize) + ‘ [‘ 41 + @fldName + ‘] from [‘ + @tblName + ‘]‘ + @strOrder + ‘) as tblTmp)‘ 42 + @strOrder 43 if @strWhere != ‘‘ 44 set @strSQL = ‘select top ‘ + str(@PageSize) + ‘ * from [‘ 45 + @tblName + ‘] where [‘ + @fldName + ‘]‘ + @strTmp + ‘([‘ 46 + @fldName + ‘]) from (select top ‘ + str((@PageIndex-1)*@PageSize) + ‘ [‘ 47 + @fldName + ‘] from [‘ + @tblName + ‘] where ‘ + @strWhere + ‘ ‘ 48 + @strOrder + ‘) as tblTmp) and ‘ + @strWhere + ‘ ‘ + @strOrder 49 if @PageIndex <> 0 50 begin 51 set @strTmp =‘‘ 52 if @strWhere != ‘‘ 53 set @strTmp = ‘ where ‘ + @strWhere 54 set @strSQL = ‘select top ‘ + str(@PageSize) + ‘ * from [‘ 55 + @tblName + ‘]‘ + @strTmp + ‘ ‘ + @strOrder 56 exec (@strSQL) 57 end 58 if @IsReCount != 0 59 begin 60 set @strSQL = ‘select count(*) as Total from [‘ + @tblName + ‘]‘+‘ where ‘ + @strWhere 61 exec (@strSQL) 62 end 63 --使用方法： 64 EXEC dbo.UP_GetRecordByPage @tblName = ‘UserName’, -- varchar(255) 65 @fldName = ‘ID‘, -- varchar(255) 66 @PageSize = 2, -- int 67 @PageIndex = 1, -- int 68 @IsReCount = 0, -- bit 69 @OrderType = 1, -- bit 70 @strWhere = ‘Age=13‘ -- varchar(1000) 71 --如果哪里有不正确的地方，欢迎批评指正，共同进步。View Code
当然还有更简单的分页
1 --查询第20-30条 2 select top 10 * from Sales where ID> 3 (select max(ID) from 4 (select top 20 ID from Sales order by ID ASC ) t0 5 ) 6 order by ID ASCView Code
不知道您对于在SQL语句中拼接字符串是否常用
1 --行转列 2 create table #CarLog(LogDate datetime,PathLine nvarchar(10),Amount int) 3 --drop table #CarLog 4 insert into #CarLog(LogDate,PathLine,Amount)VALUES(‘2011-12-24‘,‘1号线‘,1) 5 insert into #CarLog(LogDate,PathLine,Amount)VALUES(‘2011-12-25‘,‘1号线‘,91) 6 insert into #CarLog(LogDate,PathLine,Amount)VALUES(‘2011-12-26‘,‘1号线‘,66) 7 insert into #CarLog(LogDate,PathLine,Amount)VALUES(‘2011-12-24‘,‘3号线‘,44) 8 insert into #CarLog(LogDate,PathLine,Amount)VALUES(‘2011-12-24‘,‘3号线‘,33) 9 insert into #CarLog(LogDate,PathLine,Amount)VALUES(‘2011-12-24‘,‘5号线‘,12) 10 insert into #CarLog(LogDate,PathLine,Amount)VALUES(‘2011-12-24‘,‘6号线‘,22) 11 12 select * from #CarLog 13 14 declare @s nvarchar(4000) 15 set @s=‘‘ 16 select @s=@s+‘,‘ +PathLine from #CarLog 17 print @sView Code
当然也可以使用SQL来生成随机密码(以前博客园有文章说过……)
http://www.cnblogs.com/insus
-----------------------随机产生密码的存储过程
create PROCEDURE [dbo].[usp_RandomPassword]
(
@Length INT = 8
)
AS
BEGIN
DECLARE @RandomPassword NVARCHAR(MAX) = N‘‘,@L INT = 1
--下面的变量，你可以自定制需要的特殊字符
DECLARE @SpecialCharacter NVARCHAR(255) = ‘@#$%&*?‘
WHILE @L <= @Length
BEGIN
--下面这句，Insus.NET把2改为3。
DECLARE @R INT = ROUND(RAND() * 3, 0)
SET @RandomPassword = @RandomPassword + CASE @R
WHEN 0 THEN CHAR(ROUND(RAND() * 9 + 48,0))
WHEN 1 THEN CHAR(ROUND(RAND() * 25 + 65,0))
WHEN 2 THEN CHAR(ROUND(RAND() * 25 + 97,0))
WHEN 3 THEN SUBSTRING(@SpecialCharacter, CONVERT(TINYINT,ROUND(RAND() * 6 + 1,0)),1) END --添加此句
SET @L = @L + 1
END
SELECT @RandomPassword
END
--- dbo.[usp_RandomPassword]
alter PROCEDURE [dbo].[usp_RandomPassword]
(
@Length INT = 8
)
AS
BEGIN
DECLARE @RandomPassword NVARCHAR(MAX) = N‘‘,@L INT = 1
WHILE @L <= @Length --循环密码长度
BEGIN
--随机产生每一位字符，ASCII码48至122
DECLARE @RndChar CHAR(1) = CHAR(ROUND(RAND() * (122-48+1) + 48,0))
--随机产生的字符不包括下面字符
IF ASCII(@RndChar) NOT IN(58,59,60,61,62,63,64,91,92,93,94,95,96) -- : , ; , < , = , > , ? ,@ , [ , \ , ] , ^ , _ , `
BEGIN
SET @RandomPassword = @RandomPassword + @RndChar
SET @L = @L + 1
END
END
SELECT @RandomPassword
END
-----------------------随机产生密码的存储过程
alter PROCEDURE [dbo].[usp_RandomPassword]
(
@Length INT = 8
)
AS
BEGIN
DECLARE @RandomPassword NVARCHAR(MAX) = N‘‘,@L INT = 1
--随机密码将由下面字符串产生，数字0-9,大写字母A-Z,小写字母a-z
DECLARE @BaseString VARCHAR(255) = ‘0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz‘
WHILE @L <= @Length --循环密码长度
BEGIN
--61是变量@BaseString的长度减一
SET @RandomPassword = @RandomPassword + SUBSTRING(@BaseString, CONVERT(INT,ROUND(RAND() * 61 + 1,0)),1)
SET @L = @L + 1
END
SELECT @RandomPassword
END
-----------------------随机产生密码的存储过程
CREATE PROCEDURE [dbo].[usp_RandomPassword]
(
@Length INT = 8
)
AS
BEGIN
DECLARE @RandomPassword NVARCHAR(MAX) = N‘‘
DECLARE @R TINYINT,@L INT = 1
WHILE @L <= @Length --循环密码长度
BEGIN
SET @R = ROUND(RAND() * 2, 0) --随机产生0,1,2整数
IF @R = 0 --当变量为0时，将随机产生一位数字
SET @RandomPassword = @RandomPassword + CHAR(ROUND(RAND() * 9 + 48,0))
ELSE IF @R = 1 --当变量为1时，将随机产生一位大写字母
SET @RandomPassword = @RandomPassword + CHAR(ROUND(RAND() * 25 + 65,0))
ELSE IF @R = 2 --当变量为2时，将随机产生一位小写字母
SET @RandomPassword = @RandomPassword + CHAR(ROUND(RAND() * 25 + 97,0))
SET @L = @L + 1
END
SELECT @RandomPassword
END
----------重构
CREATE PROCEDURE [dbo].[usp_RandomPassword]
(
@Length INT = 8
)
AS
BEGIN
DECLARE @RandomPassword NVARCHAR(MAX) = N‘‘,@L INT = 1
WHILE @L <= @Length --循环密码长度
BEGIN
DECLARE @R INT = ROUND(RAND() * 2, 0)
SET @RandomPassword = @RandomPassword + CASE @R
WHEN 0 THEN CHAR(ROUND(RAND() * 9 + 48,0))
WHEN 1 THEN CHAR(ROUND(RAND() * 25 + 65,0))
WHEN 2 THEN CHAR(ROUND(RAND() * 25 + 97,0)) END
SET @L = @L + 1
END
SELECT @RandomPassword
END
-----------------------
--MS SQL有一个函数CHAR()是将int(0-255) ASCII代码转换为字符
--下面MS SQL语句，可以随机生成小写、大写字母，特殊字符和数字。
--大写字母：
select CHAR(ROUND(RAND() * 25 + 65,0))
--小写字母：
select CHAR(ROUND(RAND() * 25 + 97,0))
--特殊字符：
select CHAR(ROUND(RAND() * 13 + 33,0))
--数字：
select CHAR(ROUND(RAND() * 9 + 48,0))
----------------------------------
--随机密码首字符不能为数字与特殊字符但必须包含且只有一个特殊字符
--修正了特殊字符集长度问题，使用特殊字符集长度减一(LEN(@SpecialCharacter) - 1) 这样当你增减特殊字符集时，再不需多次地方一同修改
ALTER PROCEDURE [dbo].[usp_RandomPassword]
(
@Length INT = 8
)
AS
BEGIN
DECLARE @RandomPassword NVARCHAR(MAX) = N‘‘
DECLARE @SpecialCharacter NVARCHAR(255) = N‘@#$%&*?‘ --特殊字符集
DECLARE @HasSpcCht BIT = 0 --判断产生的随机数是否有包含随机数
DECLARE @L INT = 1
DECLARE @R TINYINT,@R1 TINYINT,@R2 TINYINT
WHILE @L <= @Length --循环密码长度，每一位字符将随机产生
BEGIN
IF @L = 1 --随机数第一位只为字母，大写或小写
BEGIN
SET @R = ROUND(RAND() * 1, 0)
SET @RandomPassword = @RandomPassword + CASE @R
WHEN 0 THEN CHAR(ROUND(RAND() * 25 + 97,0))
WHEN 1 THEN CHAR(ROUND(RAND() * 25 + 65,0))
END
END
ELSE
BEGIN
IF @L = @Length AND @HasSpcCht = 0 --当最后一位时，如果没有产生过特殊字符，那为随机数产生一个。
BEGIN
SET @RandomPassword = @RandomPassword + SUBSTRING(@SpecialCharacter, CONVERT(TINYINT,ROUND(RAND() * (LEN(@SpecialCharacter) - 1) + 1,0)),1)
SET @HasSpcCht = 1
END
ELSE
BEGIN
SET @R1 = ROUND(RAND() * 3, 0)
IF @R1 = 0
SET @RandomPassword = @RandomPassword + CHAR(ROUND(RAND() * 25 + 97,0))
IF @R1 = 1
SET @RandomPassword = @RandomPassword + CHAR(ROUND(RAND() * 25 + 65,0))
IF @R1 = 2
SET @RandomPassword = @RandomPassword + CHAR(ROUND(RAND() * 9 + 48,0))
IF @R1 = 3 --随机产生特殊字符
BEGIN
IF @HasSpcCht = 0 --如果没有产生过，那为随机数产生一个。
BEGIN
SET @RandomPassword = @RandomPassword + SUBSTRING(@SpecialCharacter, CONVERT(TINYINT,ROUND(RAND() * (LEN(@SpecialCharacter) - 1) + 1,0)),1)
SET @HasSpcCht = 1
END
ELSE --如果已经产生过特殊字符，只循环产生字母，或数字
BEGIN
SET @R2 = ROUND(RAND() * 2, 0)
SET @RandomPassword = @RandomPassword + CASE @R2
WHEN 0 THEN CHAR(ROUND(RAND() * 25 + 97,0))
WHEN 1 THEN CHAR(ROUND(RAND() * 25 + 65,0))
WHEN 2 THEN CHAR(ROUND(RAND() * 9 + 48,0))
END
END
END
END
END
SET @L = @L + 1 --随机产生一位，随机数位数加一位。
END
SELECT @RandomPassword
END
View Code
不知道您是否还记得数据库连接字符串如何来写？
1 1.连接sqlserver的连接字符串是： 2 “server=.;database=数据库名;uid=用户名;pwd=密码” 3 1.连接VS内置的数据库的连接字符串是： 4 DataSource=.\SQLEXpreSS;AttachDbFilename=|DataDirectory|\App.mdf;Integrated Security=True;User Instance=True" 5 下面我说明下这连接字符串的意思： 6 "Data Source=.\SQLEXpreSS;AttachDbFilename=|DataDirectory|\Student.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"; 7 DataSource相当于server 8 DataSource=.\SQLEXpreSS:是本机下的数据库实例SQLEXpreSS 9 AttachDbFilename=|DataDirectory|\App.mdf附加项目App_Data下的App.mdf 10 Integrated Security=True;User Instance=True"一个是将整体安全性设置为true,一个是将用户实例设置为true
曾经在博客园火热一时的T-SQL文章，经过本人整理的
1 --查询所有数据库
2 use master
3 select * from sysdatabases where dbid>4;--系统自带的数据库分别是master->1,model->3,msdb->4,tempdb->2
4
5
6 --查询数据库中所有数据库（存储过程）
7 exec sp_helpdb;
8
9 --查询指定数据库中的表
10 use master
11
12 select * from sysobjects where xtype=‘u‘ ;
13 if object_id(‘#test1‘) is not null
14 drop table #test1
15 go
16
17 create table #test1
18 (
19 id int not null primary key,
20 name nvarchar default(‘haha‘)
21 )
22
23 drop table #test1
24
25 set nocount off
26
27 select name from sysobjects where xtype=‘u‘--读数据库中表名
28
29 select name from syscolumns where id=(select max(id) from sysobjects where xtype=‘u‘ and name=‘表名‘)--读取某表的列名
30
31 --exec调用保存在变量中的批处理代码
32 declare @sql as varchar(100);
33 set @sql=‘print‘‘this is a message......;‘‘;‘;
34 exec(@sql)
35
36 ----------------------------------------
37 --DDL--数据定义语言
38 --DML--数据操作语言
39
40 --数据库定期备份
41 if day(current_timestamp)=9
42 begin
43 print ‘今天是一个月的第一天，数据库备份‘
44 print ‘开始完全备份‘
45 backup database dbtest to disk=‘E:\backup\backup_dbtest_full.bak‘ with init;
46 print ‘完全备份成功‘
47 end
48 else
49 begin
50 print ‘今天是一个月的最后一天，数据库备份‘
51 print ‘开始差异备份‘
52 backup database dbtest to disk=‘E:\backup\backup_dbtest_diff.bak‘ with differential;
53 print ‘差异备份成功‘
54 end
55
56 ------------------------------------------------------------
57 use tempdb;
58
59 if object_id(‘dbo.Orders‘,‘u‘) is not null drop table dbo.Orders;
60 create table dbo.Orders
61 (
62 orderid int not null
63 constraint pk_order primary key,
64 orderdate datetime not null
65 constraint def_orderdate default(current_timestamp)
66 )
67
68
69 ----------------子查询--------------------------------------------
70 --子查询分为1-独立子查询2-相关子查询
71 --返回结果可以是一个单独的值（标量）或者多个值或者整个表变量
72 declare @maxid as int = (select max(orderid) from Sales.Orders);
73 select orderid,orderdate,empid,custid
74 from Sales.Orders
75 where orderid=@maxid;
76
77 --sql2005
78 select orderid,orderdate,empid,custid
79 from Sales.Orders
80 where orderid=(select max(orderid) from Sales.Orders);
81 --对于有效的标量子查询，它的返回值不能超过一个，如果标量子查询返回了多个值，在运行时可能会失败。
82 --比如
83 select orderid
84 from Sales.Orders
85 where empid=(select E.empid from HR.Employees as E where E.lastname like N‘B%‘)
86 select E.lastname from HR.Employees as E
87 --因为恰巧该表中只有一个人的名字是以B开头的所以,sql会认为右边子查询是标量值
88 --假如右边表达式没有返回任何值，那么两者比较得出的结果是NULL,而与NULL比较的结果都为UNKNOW，所以不会返回任何值
89 --当然上面的查询还可以用联结查询
90 select O.orderid
91 from HR.Employees as E
92 join Sales.Orders as O
93 on E.empid=O.empid
94 where E.lastname like N‘D%‘
95
96 --独立多值子查询IN------
97 --<标量表达式> IN <多值子查询>
98
99
100 --用某表的偶数行数据填充tempdb
101 use tempdb
102 select *
103 into dbo.tempdb
104 from TSQLFundamentals2008.Sales.Orders
105 where orderid%2=0;
106
107 select * from tempdb;
108
109 --返回tempdb中介于min(orderid)与max(orderid)并且不在表中的orderid
110 select n
111 from tempdb
112 where n between (select min(O.orderid) from tempdb as O) and (select max(E.orderid) from tempdb as E)
113 and n not in(select orderid from tempdb);
114
115 -------------------------游标使用-------------------------------------------
116 --游标通常步骤
117 --1.在某个查询的基础上声明游标
118 --2.打开游标
119 --3.从第一个游标记录中把列值提取到指定的变量
120 --4.当还没有超出游标最后一行时，（@@fetch_status函数返回值为0），循环遍历游标记录，在每一次遍历中，从当前游标记录把列
121 --值提取到指定的变量，再为当前执行相应的处理
122 --5.关闭游标
123 --6.释放游标
124 use TSQLFundamentals2008
125 declare @result table
126 (
127 custid int,
128 ordermonth datetime,
129 qty int,
130 runqty int,
131 primary key(custid,ordermonth)
132 );
133
134 declare
135 @custid as int,
136 @prvcustid as int,
137 @ordermonth as datetime,
138 @qty as int,
139 @runqty as int;
140 declare c cursor fast_forward for
141 select custid,ordermonth,qty
142 from Sales.CustOrders
143 order by custid,ordermonth;
144
145 open c
146 fetch next from c into @custid,@ordermonth,@qty;
147 select @prvcustid=@custid,@runqty=0;
148 while @@fetch_status=0
149 begin
150 if @custid<>@prvcustid
151 set @runqty=@runqty+@qty;
152 insert into @result values(@custid,@ordermonth,@qty,@runqty);
153 fetch next from c into @custid,@ordermonth,@qty;
154 end
155 close c;
156 deallocate c;
157 select custid,convert(varchar(7),ordermonth,121) as ordermonth,qty,runqty
158 from @result
159 order by custid,ordermonth;
160
161
162 -------------------------------------------
163 --1）接受数据导入的表已经存在。
164 insert into t1 select * from OPENROWSET(‘MICROSOFT.JET.OLEDB.4.0‘ ,
165 ‘Excel 5.0;HDR=YES;DATABASE=c:\\test.xls‘,sheet1$);
166 --2）导入数据并生成表。
167 select * into t1 from OPENROWSET(‘MICROSOFT.JET.OLEDB.4.0‘,
168 ‘Excel 5.0;HDR=YES;DATABASE=c:\\test.xls‘,sheet1$);
169 --
170 --3) 导入Excel中指定的列到数据库表中指定的列。
171 INSERT INTO t1(a1,a2,a3) SELECT a1,a2,a3 FROM OPENROWSET ‘MICROSOFT.JET.OLEDB.4.0‘ ,‘Excel5.0; HDR=YES; DATABASE=c:\\test.xls‘,sheet1$);
172 --
173 --需要注意的地方。
174 --1）外围应用配置器的设置。
175 -- 从“功能外围应用配置器”中选择“启动 OPENROWSET 和 OPENDATASOURCE 支持”选项。
176 --2）关闭Excel表。
177 -- 如果在导入时要导入的Excel表格处于打开状态，会提示：
178 -- “无法初始化链接服务器 "(null)" 的 OLE DB 访问接口 "microsoft.jet.oledb.4.0" 的数据源对象。”
179 --3）导入数据时，Excel的首行会作为表头，若导入到已存在的数据库表，则忽略首行。
180
181
182 --------------------------------------直接从数据库将表导出到EXCEL-------------------------------------------------------------
183 EXEC master..xp_cmdshell ‘bcp master.dbo.t2 out d:\Temp.xls -c -q -S"127.0.0.1" -U"sa" -P"123456"‘
184
185 --参数：S 是SQL服务器名；U是用户；P是密码
186
187
188
189
190 --查询优化------------------------------------------
191 set nocount on;
192 use master;
193 if db_id(‘performance‘) is null
194 create database performance;
195 go
196 use performance;
197 go
198
199 --创建填充的数字辅助表
200 set nocount on;
201 if object_id(‘dbo.nums‘,‘u‘) is not null
202 drop table nums;
203 create table dbo.nums(n int not null primary key);
204
205 declare @max as int,@rc as int;
206 set @max=1000;
207 set @rc=1;
208
209 insert into dbo.nums(n) values(1);
210 while @rc*2<=@max
211 begin
212
213 insert into dbo.nums(n) select n+@rc from dbo.nums;
214 set @rc=@rc*2;
215 end
216
217 insert into dbo.nums(n) select n+@rc from dbo.nums where n+@rc<=@max;
218 go
219
220 --如果数据表存在，则先删除
221
222
223 ------------------------------------------------------------
224 use insideTSQL2008;
225
226 set nocount off;
227
228 select orderid,custid
229 from sales.orders
230 where orderid=(select max(orderid) from sales.orders );--取出orderid最大的订单信息（标量子查询）
231
232 -------相关子查询,返回每个客户最大的订单信息
233 select orderid,custid
234 from sales.orders as T1
235 where orderid=(select max(orderid) from sales.orders as T2 where T1.custid=T2.custid)
236 order by custid;
237
238 ---在期待多个值的地方可以使用多值子查询,返回下过订单的客户
239 select custid,companyname
240 from sales.customers
241 where custid not in (select custid from sales.orders);
242
243 --在期待表的地方还可以使用 表值子查询 或 表表达式
244 --查询每个订单年份返回最大的订单ID
245 select * from sales.orders;
246 select * from sales.customers;
247 select order_year,max(orderid) as max_orderid
248 from (select orderid,year(orderdate) as order_year from sales.orders) as T1
249 group by order_year;
250
251 --子查询可以按两种方式进行分类，按期望值的数量可以分为标量子查询，多值子查询
252 --按子查询对外部的依赖，分为独立子查询，相关子查询，标量子查询和多值子查询既可以是独立子查询，也可以是相关子查询
253 --查询由每个美国雇员至少为其处理过的一个订单的所有客户
254 --假设知道美国雇员的empid是1，2，3，4，8
255 --(1)
256 select custid
257 from sales.orders
258 where empid in(1,2,3,4,8)
259 group by custid
260 having count( distinct empid)=5;
261 --(2)
262 select custid
263 from sales.orders
264 where empid in (select empid from hr.employees where country=‘usa‘)
265 group by custid
266 having count(distinct empid)=5
267
268 --返回每个月最后实际订单日期发生的订单
269 select orderid,custid,empid,orderdate
270
271 from sales.orders
272 where orderdate in(
273 select max(orderdate)
274 from sales.orders
275 group by year(orderdate),month(orderdate));
276
277 --相关子查询，是引用了在外部查询中出现的列的子查询，从逻辑上讲，子查询会为外部查询的每一行进行一次计算。
278 --决胜属性（tiebreaker）,是一个属性或属性列表，可以惟一的对元素进行排名
279
280 --先创建2张表
281 use master;
282
283 if db_id(‘DbTest‘) is not null drop database DbTest;
284
285 create database DbTest;
286 go
287
288 use DbTest;
289 go
290
291 --创建Customers表
292 create table Customers
293 (
294 custid INT NOT NULL IDENTITY,
295 companyname NVARCHAR(40) NOT NULL,
296 country NVARCHAR(15) NOT NULL,
297 constraint pk_customer primary key(custid)
298 );
299 --创建Orders表
300 CREATE TABLE Orders
301 (
302 orderid INT NOT NULL IDENTITY,
303 custid INT NULL,
304
305 CONSTRAINT PK_Orders PRIMARY KEY(orderid),
306 CONSTRAINT FK_Orders_Customers FOREIGN KEY(custid)
307 REFERENCES Customers(custid),
308
309 );
310 set identity_insert Customers on;
311
312 INSERT INTO Customers(custid, companyname,country)
313 VALUES(1, N‘大众‘, N‘中国‘);
314 INSERT INTO Customers(custid, companyname,country)
315 VALUES(2, N‘宝马‘, N‘美国‘);
316 INSERT INTO Customers(custid, companyname,country)
317 VALUES(3, N‘奔驰‘, N‘中国‘);
318 INSERT INTO Customers(custid, companyname,country)
319 VALUES(4, N‘奇瑞‘, N‘德国‘);
320 INSERT INTO Customers(custid, companyname,country)
321 VALUES(5, N‘福特‘, N‘美国‘);
322
323 set identity_insert Customers off;
324 set identity_insert Orders on;
325 --custid代表员工号
326 INSERT INTO Orders(orderid, custid)
327 VALUES(1,1);
328 INSERT INTO Orders(orderid, custid)
329 VALUES(2,2);
330 INSERT INTO Orders(orderid, custid)
331 VALUES(3,3);
332 INSERT INTO Orders(orderid, custid)
333 VALUES(4,4);
334 INSERT INTO Orders(orderid, custid)
335 VALUES(5,5);
336 --查看表的数据
337 select custid,companyname,country from Customers;
338 select orderid,custid from Orders;
339 --插入数据成功
340
341 --咱们回到正题,比较Exists与in,not exists与 not in
342
343 --查询来自中国,而且下过订单的所有客户
344 select custid,companyname
345 from Customers as C
346 where country=N‘中国‘
347 and exists (select * from Orders as O where O.custid=C.custid);
348 --返回
349 --custid companyname
350 --1 大众
351 --3 奔驰
352
353 --外部查询返回来自中国的客户信息,对于这个客户,exists谓词在Orders表查找是否至少存在一个与外部客户行信息相同的custid订单行
354
355 --用IN查询刚刚的需求
356 select custid,companyname
357 from Customers as C
358 where country=N
