SQL Server笔试准备 Day1

经过前几天准备，大概.NET/C#的基础有所掌握了，今天准备一下SQL Server

今晚先弄些实战题，明晚整理一下理论基础

一、行转列（原文：http://www.cnblogs.com/zhangzt/archive/2010/07/29/1787825.html，http://www.cnblogs.com/maanshancss/archive/2013/03/13/2957108.html）

经典的行转列必考题

CREATE TABLE #tab(姓名 VARCHAR(10), 课程 VARCHAR(10), 分数 INT)
INSERT INTO #tab VALUES('张三','语文',74)
INSERT INTO #tab VALUES('张三','数学',83)
INSERT INTO #tab VALUES('张三','物理',93)
INSERT INTO #tab VALUES('李四','语文',74)
INSERT INTO #tab VALUES('李四','数学',84)
INSERT INTO #tab VALUES('李四','物理',94)

姓名    课程    分数
张三    语文    74
张三    数学    83
张三    物理    93
李四    语文    74
李四    数学    84
李四    物理    94

1、原始写法

SELECT 姓名,
    MAX(CASE 课程 WHEN '语文' THEN 分数 ELSE 0 END) 语文,
    MAX(CASE 课程 WHEN '数学' THEN 分数 ELSE 0 END) 数学,
    MAX(CASE 课程 WHEN '物理' THEN 分数 ELSE 0 END) 物理
FROM #tab
GROUP BY 姓名

姓名    语文    数学    物理
李四    74    84    94
张三    74    83    93

就是把第一列group by，然后判断每列，是当前列就把要加的那一列值加上，否则加0

2、动态拼接

DECLARE @sql VARCHAR(500)
SET @sql='SELECT 姓名'
SELECT @sql=@sql+', MAX(CASE 课程 WHEN '''+课程+''' THEN 分数 ELSE 0 END)['+课程+']'
FROM (SELECT DISTINCT 课程 FROM #tab)a
SET @sql=@sql+' FROM #tab GROUP BY 姓名'
PRINT(@sql)
EXEC(@sql)

PRINT出来的以下语句

SELECT 姓名, 
    MAX(CASE 课程 WHEN '数学' THEN 分数 ELSE 0 END)[数学], 
    MAX(CASE 课程 WHEN '物理' THEN 分数 ELSE 0 END)[物理], 
    MAX(CASE 课程 WHEN '语文' THEN 分数 ELSE 0 END)[语文] 
FROM #tab GROUP BY 姓名

和第一种是一样的，改进之处在于可以动态生成。

3、PIVOT函数（SQL2005以上支持）

SELECT * FROM #tab 
PIVOT( MAX(分数) FOR 课程 IN (语文,数学,物理) ) a

语法：

PIVOT(
    聚合函数（value_column）
    FOR pivot_column
    IN(<column_list>)
)

一句话就搞定，但要记住语法

4、PIVOT动态拼接，懒得弄了，原理一样

二、列转行（原文：http://www.cnblogs.com/zhangzt/archive/2010/07/29/1787825.html，http://www.cnblogs.com/maanshancss/archive/2013/03/13/2957108.html）

姓名    语文    数学    物理
李四    74    84    94
张三    74    83    93

1、原始写法

SELECT * FROM
(
   SELECT 姓名, 课程='语文', 分数=语文 FROM #tab1
   UNION ALL
   SELECT 姓名, 课程='数学', 分数=数学 from #tab1
   UNION ALL
   SELECT 姓名, 课程='物理', 分数=物理 from #tab1
) t

2、动态拼接（使用系统函数，就不能用临时表了）

DECLARE @sql VARCHAR(8000)
SELECT @sql=ISNULL(@sql+' UNION ALL ','')+' SELECT 姓名, [课程]='
+QUOTENAME(Name,'''')+' , [分数] = '+QUOTENAME(Name)+' FROM test2'
FROM syscolumns
WHERE Name!='姓名' AND ID=OBJECT_ID('test2')
PRINT(@sql)
EXEC(@sql)

SELECT 姓名, [课程]='数学' , [分数] = [数学] FROM test2 
UNION ALL
SELECT 姓名, [课程]='物理' , [分数] = [物理] FROM test2 
UNION ALL 
SELECT 姓名, [课程]='语文' , [分数] = [语文] FROM test2

3、UNPIVOT函数

SELECT  姓名, 课程, 分数 FROM test2 
UNPIVOT ( 分数 FOR 课程 IN ( [语文], [数学], [物理] ) ) t

4、UNPIVOT动态拼接，懒得弄了，原理一样

三、分页（取xx到xx数据，主键不连续）

MySql一个Limit就搞定了。。。SQL有几种写法

1、把前xx条排除完，再取前xx条

SELECT TOP 10 * FROM A
WHERE id NOT IN(
  SELECT TOP 30 id FROM A
)

2、把前xx条的最大主键找出来，然后主键大于这个值的前xx条

SELECT TOP 10 * FROM A
WHERE id>(
  SELECT MAX(id) FROM (
    SELECT TOP 30 id FROM A 
  ) B
)

3、ROW_NUMBER() OVER (ORDER BY ID)，SQL 2005以后支持

SELECT * FROM(
  SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ROWNUM FROM 表
) t   
WHERE ROWNUM BETWEEN 21 and 30

4、性能不好，仅作为笔试时要求N条方法时使用。按主键排序选出前XX条，再选出倒序的前XX条。

SELECT TOP 20 * FROM(
    SELECT TOP 30 * FROM 表名 ORDER BY ID 
) t1 ORDER BY ID DESC

三、统计比赛结果

date result
2005-05-09 win
2005-05-09 lose
2005-05-09 lose
2005-05-09 lose
2005-05-10 win
2005-05-10 lose
2005-05-10 lose

需生成

win lose
2005-05-09 2 2
2005-05-10 1 2

用SUM()来统计，分成1和0相加

SELECT date,
    SUM(CASE WHEN result = "win" THEN 1 ELSE 0 END) AS "win",
    SUM(CASE WHEN result = "lose" THEN 1 ELSE 0 END) AS "lose"
FROM info
GROUP BY date;

或者先把各项加好，再直接选择

select a.date, a.result as win, b.result as lose FROM(
  SELECT date, count(result) as result from info where result = "win" group by date
) as a
join(
  SELECT date, count(result) as result from info where result = "lose" group by date
) as b on a.date = b.date;

四、根据两个日期，得到中间所有日期（存储过程）

1、有个绝妙的技巧，系统表master.dbo.spt_values有一堆乱七八槽的值，其中type='p'的，是0~2047，可以拿来取数用。。。不知谁想到的，真是绝了。。。

select number from master.dbo.spt_values where type='p'

然后根据DATEADD、DATEDIFF得到相应的值，

SELECT CONVERT(VARCHAR(10), DATEADD(DAY, number, '2010-01-01'), 120)
FROM master.dbo.spt_values
WHERE type='P' AND number<=DATEDIFF(DAY, '2010-01-01', '2010-01-10')

2、使用while和union select拼字符串

DECLARE @i INT=0;
DECLARE @sql NVARCHAR(1000)='SELECT ''2010-01-01''';
WHILE(@i<10)
BEGIN
    SET @sql=@sql+' UNION SELECT CONVERT(VARCHAR(10), DATEADD(DAY, '+CONVERT(VARCHAR(2),@i)+', ''2010-01-01''), 120) '
    SET @i=@i+1;
END
PRINT @sql
EXEC (@sql)

五、基本语法

1、添加/修改/删除列

--添加列
ALTER TABLE test1
ADD  test NVARCHAR(10)
--修改列
ALTER TABLE test1
ALTER COLUMN test NVARCHAR(20)
--删除列
ALTER TABLE test1
DROP COLUMN test

常弄混啊，毕竟表结构很少改，一般都用可视化界面

添加的时候，没有COLUMN参数（很多网上资料说有，可能不是SqlServer），其它有COLUMN参数

2、删除表

DELETE test1
DELETE FROM test1
DELETE test1 FROM test2 --就像UPDATE XX FROM XX，后面表是条件，只是修改前面的表
--DELETE * FROM test1 加*是错误的

也是有一些坑。。。

3、取出数据库所有表名

SELECT name FROM sysobjects WHERE type='U'

4、取出表中所有列名

select name from syscolumns where id=object_id('test2')

5、