T-SQL编程[整理系列]
“记录一些T-SQL编程中的零散知识[开篇]”
1、 变量的声明
1.1 USE语句用来切换当前数据库。
1.2 Declare 声明变量，变量名前缀@。
使用set、select都可以为变量赋值。
主要区别在于：·当执行简单的变量赋值时，使用set
·当基于查询进行变量赋值时，使用select
DECLARE @temp money; Set @temp=(select max( UnitPrice) from SaleOrderDetail); Select @temp; DECLARE @temp money; Select @temp= max( UnitPrice) from SaleOrderDetail; Select @temp;
1.3 系统函数（@@全局变量）
http://msdn.microsoft.com/zh-cn/library/ms187786.aspx
1.4 系统表及系统存储过程
【待补】
2、流程控制语句
- IF……ELSE
IF NOT EXISTS ( SELECT s.name AS SchemaName,t.name AS TableName FROM sys.schemas s JOIN sys.tables t ON s.schema_id=t.schema_id WHERE s.name='dbo' AND t.name='IFTestTable' ) BEGIN
CREATE TABLE IFTestTable( ID int PRIMARY KEY ); END
ELSE
BEGIN INSERT INTO IFTestTable VALUES('1');
END
利用Begin……End组织T-SQL代码块
CASE语句：基本上只是一个替换操作符而不是流程控制语句。最大的好处是可以与SELECT语句“内联”使用。
我们将利用CASE语句实现“行转列”的操作。
1、创建测试数据
View Code
--行转列 CREATE TABLE StuScore( stuid int NOT NULL, subject nvarchar(30) NULL, score decimal(5,1) NULL ); go INSERT INTO StuScore(stuid, subject,score)VALUES('3','chinese', '76.0') INSERT INTO StuScore(stuid, subject,score)VALUES('3','math', '86.0') INSERT INTO StuScore(stuid, subject,score)VALUES('3','chinese', '96.0') INSERT INTO StuScore(stuid, subject,score)VALUES('4','chinese', '66.0') INSERT INTO StuScore(stuid, subject,score)VALUES('3','chinese', '86.0') INSERT INTO StuScore(stuid, subject,score)VALUES('3','math', '79.0') INSERT INTO StuScore(stuid, subject,score)VALUES('4','chinese', '70.0') INSERT INTO StuScore(stuid, subject,score)VALUES('3','math', '78.0') INSERT INTO StuScore(stuid, subject,score)VALUES('4','chinese', '78.0') INSERT INTO StuScore(stuid, subject,score)VALUES('3','math', '76.0') INSERT INTO StuScore(stuid, subject,score)VALUES('4','chinese', '100.0') go --查询测试数据 SELECT stuid, subject, score FROM StuScore;
stuid subject score
----------- ------------------------------
3 chinese 76.0
3 math 86.0
3 chinese 96.0
4 chinese 66.0
3 chinese 86.0
3 math 79.0
4 chinese 70.0
3 math 78.0
4 chinese 78.0
3 math 76.0
4 chinese 100.0
我们需要将数据转变成这样：
stuid chinese math
----------- --------------------------------------- ---------------------------------------
3 258.0 319.0
4 314.0 0.0
2、实现“行转列”思路
--"行转列" 关键是首先知道有多少列，如何去创建列。 --实现1，我们先问题分解，假设我们知道chinese、math列 SELECT DISTINCT StuScore.stuid,0 AS chinese,0 AS match FROM StuScore
结果：
stuid chinese match
----------- ----------- -----------
3 0 0
4 0 0
我们发现这个结构和我们想象的表结构是一样，接下我们我们用数据进行填充。
--实现2 SELECT StuScore.stuid, CASE subject WHEN 'chinese' THEN score ELSE 0 END AS chinese, CASE subject WHEN 'math' THEN score ELSE 0 END AS math FROM StuScore;
结果：
stuid chinese math
----------- --------------------------------------- ---------------------------------------
3 76.0 0.0
3 0.0 86.0
3 96.0 0.0
4 66.0 0.0
3 86.0 0.0
3 0.0 79.0
4 70.0 0.0
3 0.0 78.0
4 78.0 0.0
3 0.0 76.0
4 100.0 0.0
以上结果，已经基本接近我们的想要的结果。我们对每一行做SUM（）。
--实现3 SELECT StuScore.stuid, sum(CASE subject WHEN 'chinese' THEN score ELSE 0 END) AS chinese, sum(CASE subject WHEN 'math' THEN score ELSE 0 END )AS math FROM StuScore GROUP BY stuid;
结果：
stuid chinese math
----------- --------------------------------------- ---------------------------------------
3 258.0 319.0
4 314.0 0.0
这样结构就出来了，那么我们怎么去知道chinese、math列。
SELECT DISTINCT subject FROM StuScore
我们利用动态拼接SQL语句实现“行转列”
declare @sql varchar(2000) set @sql='select stuid' select @sql =@sql+ ',sum(case subject when '''+subject+''' then score else 0 end) as ' + subject from (select distinct subject from dbo.StuScore) as sub set @sql=@sql + ' from dbo.StuScore group by stuid' exec(@sql)
SQL server 2005以上提供了“PIVOT”实现行转列，UNPIVOT实现列转行
以上功能可以利用pivot函数实现：
SELECT * FROM StuScore PIVOT (max(score)FOR subject IN (chinese,math)) a
- WHILE语句。当条件为TRUE时，进行循环。使用BREAK退出循环。
- WAITFOR语句。延迟操作。使用Delay参数指定时间段，使用TIME，指定时间的点。
- GOTO
- TRY……CATCH语句。用于捕获异常。
3、T-SQL编程
3.1 分页
1、SQL server 2005以上 利用RowNumber()函数实现分页。
---RowNumber()函数分页 --ANIC_Compound为分页源表 SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY ID asc)AS rowId,* FROM ANIC_Compound) AS T WHERE T.rowId BETWEEN 11 AND 20
2、“双TOP”分页（取10~30的记录集）
--双top分页 SELECT TOP 10 * FROM (SELECT TOP 30 * FROM ANIC_Compound ORDER BY ID asc)AS T ORDER BY T.ID DESC
3、分页存储过程
3.2 SQL编程技巧[记录一些实际用的SQL语句]
1、跨数据库操作数据
场景：需要DatabaseA中查询结果后，插入BatabaseB
DECLARE @imgCount int DECLARE @imgName nvarchar(20)--eg:027461.jpg DECLARE @productId nvarchar(20)=SELECT REPLACE(@imgName,'.jpg','') SELECT @imgCount = COUNT(1) FROM ProductImages WHERE imgName LIKE '@productId%' BEGIN DELETE SELECT * FROM ProductImages WHERE imgName = @imgCount SELECT @imgCount = @imgCount-1 END UPDATE ays..Product --ays数据库名称，..为dbo SET PicCount = @imgCount WHERE productId = @productId
2、字符串截取
http://www.cnblogs.com/penglink/archive/2009/03/07/1405360.html
3、时间函数及转换
1）SELECT GETDATE() 返回当前时间。eg:2012-08-23 16:39:12.103
2) SELECT CONVERT(varchar(10),getdate(),20) 获取当前日期 。eg:2012-08-23
3) SELECT DATEPART(month, GETDATE()) AS YueFen 取当前月份。
| 日期部分 参数： | 缩写 |
|---|---|
|
year |
yy, yyyy |
|
quarter |
qq, q |
|
month |
mm, m |
|
dayofyear |
dy, y |
|
day |
dd, d |
|
week |
wk, ww |
|
weekday |
dw |
|
hour |
hh |
|
minute |
mi, n |
|
second |
ss, s |
|
millisecond |
convert(nvarchar(10),DATEPART(yy,GETDATE()))+'-'+convert(nvarchar(10),DATEPART(mm,GETDATE())) +'-'+convert(nvarchar(10),DATEPART(dd,GETDATE()))
4、获取（删除）不重复的记录。
eg： Id name age salary
1 yzk 20 1000
2 yzk 20 3000
3 tom 30 4000
4 tom 30 4000
5 imi 20 5000
[待]
本文版权归本人和博客园共同所用，转载请注明出处。
