目录 | 索引
拷贝到Excel中
1. INSERT INTO VALUES
--Create table Employee CREATE TABLE [dbo].[Employee] ( [EmployeeNo] INT PRIMARY KEY, [EmployeeName] [nvarchar](50) NULL, [CreateUser] [nvarchar](50) NULL, [CreateDatetime] [datetime] NULL ); --Insert INSERT INTO Employee(EmployeeNo, EmployeeName, CreateUser, CreateDatetime) VALUES ('1', 'Employee 1', 'System', GETDATE()), ('2', 'Employee 2', 'System', GETDATE()), ('3', 'Employee 3', 'System', GETDATE()) --Select SELECT * FROM Employee
2. SELECT FROM VALUES
SELECT * FROM (VALUES ('1', 'Employee 1', 'System', GETDATE()), ('2', 'Employee 2', 'System', GETDATE()), ('3', 'Employee 3', 'System', GETDATE()) ) Employee(EmployeeNo, EmployeeName, CreateUser, CreateDatetime)
3. INERT INTO DEFAULT VALUES GO
新建测试表
CREATE TABLE Employee ( EmployeeNo INT IDENTITY, EmployeeName NVARCHAR(100), CreateUser NVARCHAR(100) DEFAULT 'System', CreateDatetime DATETIME DEFAULT GETDATE() ) GO
用Default Values插入测试数据(8笔)
INSERT INTO Employee DEFAULT VALUES; GO 8
查询结果
SELECT * FROM Employee
1. 行转列
新建测试表和测试数据
CREATE TABLE StudentScore ( UserName NVARCHAR(50), Course NVARCHAR (50), Score FLOAT ) INSERT INTO StudentScore(UserName, Course, Score) VALUES ('Helen', '语文', 100), ('Helen', '数学', 90), ('Helen', '英语', 85), ('Helen', '物理', 80), ('Jack', '语文', 75), ('Jack', '数学', 90), ('Jack', '英语', 85), ('Jack', '物理', 100) SELECT * FROM StudentScore
传统行转列的实现方法如下
SELECT UserName, MAX(CASE Course WHEN '语文' THEN Score ELSE 0 END) AS '语文', MAX(CASE Course WHEN '数学' THEN Score ELSE 0 END) AS '数学', MAX(CASE Course WHEN '英语' THEN Score ELSE 0 END) AS '英语', MAX(CASE Course WHEN '物理' THEN Score ELSE 0 END) AS '物理' FROM StudentScore GROUP BY UserName
使用PIVOT数据透视的方法实现行转列
SELECT UserName, 语文, 数学, 英语, 物理 FROM StudentScore PIVOT ( SUM(Score) FOR Course IN (语文, 数学, 英语, 物理) ) T --使用 * 也可以 SELECT * FROM StudentScore PIVOT ( SUM(Score) FOR Course IN (语文, 数学, 英语, 物理) ) T
注意:在PIVOT运算符的圆括号内要指定:聚合函数(本例为SUM)、聚合元素(Score)、扩展元素(Course)以及目标列名称的列表(语文, 数学, 英语, 物理)。
对于PIVOT运算符有个重要的地方需要注意:不需为它显式地指定分组元素,也就不许要在查询中使用GROUP BY字句。PIVOT运算符隐式地把源表(或表表达式)中既没有指定为扩展元素,也没有指定为聚合元素的那些元素作为分组元素。所以在使用PIVOT运算符时,须要保证PIVOT运算符的源表除了分组、扩展和聚合元素以外,不能再包含其它属性(列)。以便在指定了扩展元素和聚合元素以后,剩下的属性全部都是欲指定为分组元素的属性。为此,一般不直接把PIVOT运算符应用到源表(本例为StudentScore表),而是将其应用到一个表表达式(改表表达式只包含透视转换需要的3中元素,不包含其他属性)。
2. 列转行
新建测试表与测试数据
CREATE TABLE StudentScore ( [UserName] NVARCHAR(50), [语文] FLOAT, [数学] FLOAT, [英语] FLOAT, [物理] FLOAT ) INSERT INTO StudentScore(UserName, [语文], [数学], [英语], [物理]) VALUES ('Helen', 100, 90, 85, 80), ('Jack', 75, 90, 85, 100) SELECT * FROM StudentScore
传统列转行方法 (UNION ALL与GROUP BY)
SELECT UserName, '语文' AS Course, MAX([语文]) AS Score FROM StudentScore GROUP BY UserName UNION ALL SELECT UserName, '数学' AS Course, MAX([数学]) AS Score FROM StudentScore GROUP BY UserName UNION ALL SELECT UserName, '英语' AS Course, MAX([英语]) AS Score FROM StudentScore GROUP BY UserName UNION ALL SELECT UserName, '物理' AS Course, MAX([物理]) AS Score FROM StudentScore GROUP BY UserName
使用UNPIVOT实现列转行
SELECT UserName, Score, Course FROM StudentScore UNPIVOT ( Score FOR Course IN (语文, 数学, 英语, 物理) ) T --也可以使用 * SELECT * FROM StudentScore UNPIVOT ( Score FOR Course IN (语文, 数学, 英语, 物理) ) T
下面的SQL语句摘抄自网上 (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=75273),感觉有瑕疵,至少对索引和索引的包含列不起作用。不过貌似Red Gate的SQL Search工具好像也无法找到包含字段的索引,看样子要找出所有的引用还是比较困难的,如果有哪位童鞋有更好的办法请不吝赐教。
DECLARE @string varchar(1000), @ShowReferences char(1) SET @string = 'searchstring' --> searchstring SET @ShowReferences = 'N' /****************************************************************************/ /* */ /* TITLE: sp_FindReferences */ /* */ /* DATE: 18 February, 2004 */ /* */ /* AUTHOR: WILLIAM MCEVOY */ /* */ /****************************************************************************/ /* */ /* DESCRIPTION: SEARCH SYSCOMMENTS FOR INPUT STRING, OUTPUT NAME OF OBJECT */ /* */ /****************************************************************************/ set nocount on declare @errnum int , @errors char(1) , @rowcnt int , @output varchar(255) select @errnum = 0 , @errors = 'N' , @rowcnt = 0 , @output = '' /****************************************************************************/ /* INPUT DATA VALIDATION */ /****************************************************************************/ /****************************************************************************/ /* M A I N P R O C E S S I N G */ /****************************************************************************/ -- Create temp table to hold results DECLARE @Results table ( Name varchar(55), Type varchar(12), DateCreated datetime, ProcLine varchar(4000) ) IF (@ShowReferences = 'N') BEGIN insert into @Results select distinct 'Name' = convert(varchar(55),SO.name), 'Type' = SO.type, crdate, '' from sysobjects SO join syscomments SC on SC.id = SO.id where SC.text like '%' + @string + '%' union select distinct 'Name' = convert(varchar(55),SO.name), 'Type' = SO.type, crdate, '' from sysobjects SO where SO.name like '%' + @string + '%' union select distinct 'Name' = convert(varchar(55),SO.name), 'Type' = SO.type, crdate, '' from sysobjects SO join syscolumns SC on SC.id = SO.ID where SC.name like '%' + @string + '%' order by 2,1 END ELSE BEGIN insert into @Results select 'Name' = convert(varchar(55),SO.name), 'Type' = SO.type, crdate, 'Proc Line' = text from sysobjects SO join syscomments SC on SC.id = SO.id where SC.text like '%' + @string + '%' union select 'Name' = convert(varchar(55),SO.name), 'Type' = SO.type, crdate, 'Proc Line' = '' from sysobjects SO where SO.name like '%' + @string + '%' union select 'Name' = convert(varchar(55),SO.name), 'Type' = SO.type, crdate, 'Proc Line' = '' from sysobjects SO join syscolumns SC on SC.id = SO.ID where SC.name like '%' + @string + '%' order by 2,1 END IF (@ShowReferences = 'N') BEGIN select Name, 'Type' = Case (Type) when 'P' then 'Procedure' when 'TR' then 'Trigger' when 'X' then 'Xtended Proc' when 'U' then 'Table' when 'C' then 'Check Constraint' when 'D' then 'Default' when 'F' then 'Foreign Key' when 'K' then 'Primary Key' when 'V' then 'View' else Type end, DateCreated from @Results order by 2,1 END ELSE BEGIN select Name, 'Type' = Case (Type) when 'P' then 'Procedure' when 'TR' then 'Trigger' when 'X' then 'Xtended Proc' when 'U' then 'Table' when 'C' then 'Check Constraint' when 'D' then 'Default' when 'F' then 'Foreign Key' when 'K' then 'Primary Key' when 'V' then 'View' else Type end, DateCreated, ProcLine from @Results order by 2,1 END
在设计表主键时,我们一般有两种选择:
- 自增键 INT/BIGINT
- 优势
- 占用空间小(4/8字节)
- 顺序性:减少叶级别索引的页争用
- 性能高:前两点的优势自然性能比较高
- 可读性
- 返回生成的主键值
-
- 劣势
- 合并表的时候,不能保证其不重复
- 大量并发的时候,很难控制其唯一性
- 可猜测性:因为是数字,用户易于猜测
- GUID
- 优势
- 唯一性:不论是合并表的时候,还是高并发,都能保证唯一性
- 不可猜测性:因为是随机生成的,根本不可能猜测到
- 劣势
- 占用空间大:16字节
- 可读性差
- 性能低
- 随机性:增加叶级别索引的页争用
上面提到当用GUID作为主键时,由于其随机性,导致增加页级别索引的页争用。其实我们也可以创建顺序性的GUID - NEWSEQUENTIALID()
CREATE TABLE GUID_NEWSEQUENTIALID ( ID UNIQUEIDENTIFIER PRIMARY KEY NOT NULL DEFAULT NEWSEQUENTIALID(), NUMBER INT, CREATE_USER NVARCHAR(50), CREATE_DATE_TIME DATETIME DEFAULT GETDATE() )
注意:NEWSEQUENTIALID() 只能与 uniqueidentifier 类型表列上的 DEFAULT 约束一起使用,不能在查询中使用
SELECT NEWSEQUENTIALID()
报错信息如下
下面我们来检验一下看看插入的是不是具有顺序性(循环插入10000条测试记录,注意ID使用Default值)
DECLARE @NUM INT SET @NUM = 0 WHILE(@NUM < 100000) BEGIN INSERT INTO GUID_NEWSEQUENTIALID(NUMBER,CREATE_USER) VALUES(@NUM,'SHG.CPAN') SET @NUM = @NUM + 1 END
检索一下看看(确实按顺序)
SELECT * FROM GUID_NEWSEQUENTIALID ORDER BY ID
注意:使用 NEWSEQUENTIALID() 生成的每个 GUID 在该计算机上都是唯一的。 仅当源计算机具有网卡时,使用 NEWSEQUENTIALID() 生成的 GUID 在多台计算机上才是唯一的。
在启动 Windows 后在指定计算机上创建大于先前通过该函数生成的任何 GUID 的 GUID。 在重新启动 Windows 后,GUID 可以再次从一个较低的范围开始,但仍是全局唯一的。 在 GUID 列用作行标识符时,使用 NEWSEQUENTIALID 可能比使用 NEWID 函数的速度更快。 其原因在于,NEWID 函数导致随机行为并且使用更少的缓存数据页。 使用 NEWSEQUENTIALID 还有助于完全填充数据和索引页。(https://msdn.microsoft.com/zh-cn/library/ms189786(v=sql.120).aspx)
NEWSEQUENTIALID 是对 Windows UuidCreateSequential 函数的包装
下面的代码展示如何在C#中生成顺序性的GUID(参考链接:http://stackoverflow.com/questions/211498/is-there-a-net-equalent-to-sql-servers-newsequentialid)
[DllImport("rpcrt4.dll", SetLastError=true)] static extern int UuidCreateSequential(out Guid guid);