本文共 6351 字,大约阅读时间需要 21 分钟。
转自:
Transact-SQL具体可以参阅《Transact-SQL参考》(tsql.hlp)(简写《T-SQL》) 建意: 在写SQL Script时最好能将数据操作SQL的保留字用大写 注: 此处语法格式只是常用格式,并不是SQL标准格式,标准格式请参阅《T-SQL》 以下所用的程序代码都使用VB6.0代码,在例子中的SQL无实际意义 选择 SELECT SELECT 可以选择指定的数据列 如: SELECT * FROM sysobjects SELECT [name] FROM syscolumns 当在SQL中存在系统保留字时应用“[]”引起,或在SQL中存在特殊字符也应用“[]”引起, 如: SELECT [Object Name] FROM Objects 在使用别名时也应注意以上原则,别名使用可以用以下两种方法: Column_name AS alias Column_name alias 中间的AS可以省略 在SELECT中可以使用条件选择语法,参见下面的“条件” 如: SELECT [name],xtype,CASE WHEN xtype=’U’ THEN ‘用户表’ ELSE CASE WHEN xtype=’S’ THEN ‘系统表’ END END AS 类型 FROM sysobjects 返回表: name xtype 类型 syscolumns S 系统表 tabledefine U 用户表 将两个查询合成单独的返回表: 用UNION关键字 如SELECT A,B FROM Table1 UNOIN SELECT C,D FROM Table2 说明: 在使用UNION时,若无ALL参数则默认将过虑相同的记录, 如: Table1 Table2 ID TF1 VALUE1 ID TF2 VALUE2 1 A 10 5 A 10 5 B 20 6 D 21 2 A 30 3 C 31 3 C 40 1 B 41 SELECT TF1,VALUE1 FROM Table1 UNION SELECT TF2,VALUE2 FROM Table2 返回表: TF1 VALUE1 A 10 B 20 A 30 C 40 D 21 C 31 B 41 其中可以看出少了一个”TF2=A ,VALUE2=10”的记录 但用以下查询时 SELECT TF1,VALUE1 FROM Table1 UNION ALL SELECT TF2,VALUE2 FROM Table2 返回表: TF1 VALUE1 A 10 B 20 A 30 C 40 A 10 D 21 C 31 B 41 刚此查询将返回所有记录 此问题可能会出现在报表统计上,如一个员工在不同日期内做了相同的产品与数据,但在使用非ALL方式进行合计时将会少合计一条记录 与INTO联用 SELECT …. INTO B FROM A 可以将A 表的指定数据存入B表中 应用类型: 备份数据表: SELECT * INTO Table1_bak FROM Table1 创建新表 SELECT * INTO New_Table1 FROM Table1 WHERE 1<>1 SELECT TOP 0 * INTO New_Table1 FROM Table1 保存查询结果 SELECT Field1,Field2 INTO Result FROM Table1 WHERE ID>1000 创建新表并在新表中加入自动序号 一表有些表需要一个自动编号列来区别于各行 SELECT IDENTITY (INT,1,1) AS AutoId,* INTO new_Table1 FROM Table1 其中IDENTITY函数说明: 格式: IDENTITY (<datatype> [seed,increment]) 参数说明: Datatype:数据类型,视记录数定类型,一般可以定INT型,具体可以参考SQL的极限参数 Seed:开始数值,即开始的基数,默认为1 Increment:增量,步长即数据间的间隔,默认为1 上面的SQL即表示,自动编号从1开始并每行加1 返回的表为: AutoId Field1 Field2 1 Hello Joy 2 Hello Tom 3 Hi Lily 4 Hello Lily 注: IDENTITY还可以在创建表时设置 格式: IDENTITY ([seed, increment]) 如: 创建表 CREATE TABLE Table1 ( AutoId int IDENTITY(1,1), 或 autoid int identity Field1 nvarchar(30), Field2 nvarchar(30) ) 修改表 ALTER TABLE Table1 ADD AutoId int IDENTITY (1,1) 在进行数据插入时应注意IDENTITY_INSERT这个属性的设置 当 SET IDENTITY_INSERT <table> ON 时,则不能进行隐式插入 如: SET IDENTITY_INSERT Table1 ON INSERT INTO Table1 SELECT (‘r1c1’,’r1c2’) --这样就会出错 必需使用: INSERT INTO Table1 SELECT (1,’R1C1’,’R1C2’) 只能在SET IDENTITY_INSERT <table> OFF 时才允许隐式插入 如: SET IDENTITY_INSERT Table OFF 必需使用: INSERT INTO Table1 SELECT (‘r1c1’,’r1c2’) 否则 INSERT INTO Table1 SELECT (1,’R1C1’,’R1C2’) --这样就会出错 在使用隐式插入后可以用@@IDENTITY这个系统值来返回插入行的编号 INSERT INTO Table1 SELECT(‘R1C1’,’R1C2’) 返回表: AutoID Field1 Field2 1 R1C1 R1C2 SELECT @@IDENTITY 返回值: 1 在应用程序中可以用以下方法做: set recs=cnn.execute(“INSERT INTO Table1 SELECT(‘R1C1’,’R1C2’)”) recordnum=cnn.execute(“SELECT @@IDENTITY”).fields(0).value 以上语句执行后recordnum的值将设置为最后一个自动编号 关联 用例: Table1 Table2 ID TF1 VALUE1 ID TF2 VALUE2 1 TFI1-1 10 5 TFI2-1 11 5 TFI1-2 20 6 TFI2-2 21 2 TFI1-3 30 3 TFI2-3 31 3 TFI1-4 40 1 TFI2-4 41 Table2 INNER JOIN 只显示两表一一对应的记录 SELECT * FROM Table1 INNER JOIN Table2 ON Table1.ID=Table2.ID ORDER BY Table1.ID 返回表: ID TF1 VALUE1 ID TF2 VALUE2 1 TFI1-1 10 1 TFI2-4 41 3 TFI1-4 40 3 TFI2-3 31 5 TFI1-2 20 5 TFI2-1 11 LEFT JOIN(LEFT OUTER JOIN) 显示左表所有记录与右表对应左表的记录,当在右表中无记录,则右表相应字段用NULL填充 SELECT * FROM Table1 LEFT JOIN Table2 ON Table1.ID=Table2.ID ORDER BY Table1.ID 返回表: ID TF1 VALUE1 ID TF2 VALUE2 1 TFI1-1 10 1 TFI2-4 41 2 TFI1-3 30 NULL NULL NULL 3 TFI1-4 40 3 TFI2-3 31 5 TFI1-2 20 5 TFI2-1 11 RIGHT JOIN(LEFT OUTER JOIN) 显示右表所有记录与左表对应右表的记录,当在左表中无记录,则左表相应字段用NULL填充 SELECT * FROM Table1 LEFT JOIN Table2 ON Table1.ID=Table2.ID ORDER BY Table1.ID 返回表: ID TF1 VALUE1 ID TF2 VALUE2 NULL NULL NULL 6 TFI2-2 21 1 TFI1-1 10 1 TFI2-4 41 3 TFI1-4 40 3 TFI2-3 31 5 TFI1-2 20 5 TFI2-1 11 FULL JOIN(FULL OUTER JOIN) 显示左右两表所有记录,当左表无记录,则左表相应字段用NULL填充,当右表无记录则右表相关字段用NULL填充 SELECT * FROM Table1 LEFT JOIN Table2 ON Table1.ID=Table2.ID ORDER BY Table1.ID 返回表: ID TF1 VALUE1 ID TF2 VALUE2 1 TFI1-1 10 1 TFI2-4 41 2 TFI1-3 30 NULL NULL NULL 3 TFI1-4 40 3 TFI2-3 31 5 TFI1-2 20 5 TFI2-1 11 NULL NULL NULL 6 TFI2-2 21 说明: 在进行多级关联的时候应该采用就近关联原则 如: SELECT * FROM Table1 INNER JOIN Table2 INNER JOIN Table2-1 ON Table2.ID=Table2-1.ID ON Table1.ID=Table2.ID 即Table2与Table2-1关联 Table1与Table2关联 建意: 在写此类关联时,最好将基语句格式结构化 如: SELECT * FROM Table1 INNER JOIN Table2 INNER JOIN Table2-1 ON Table2.ID=Table2-1.ID ON Table1.ID=Table2.ID WHERE ID IN (1,2,3) 注: 在写完查询语句后,可以由“企业管理器”进行SQL语句的格式化,但这一过程出来的语句一定要进行测试,因为在他自动格式化时可能会把某些复杂的关系搞错 分组 GROUP BY (没什么好说!!) 如: SELECT A,B,SUM(D) FROM Table1 GROUP BY A,B ORDER BY A 注: 在进行GROUP BY 时应该注意GROUP BY 中字段的使用, 只要在同一查询语句中则所有未进行骤合操作的字段都需要被GROUP, 如上面的SQL中,字段A,与B都未被骤合,并字段A被排序,而字段D被骤合函数SUM进行汇总统计 因此字段A,B需要被GROUP 而D则不用 如: SELECT A,B,SUM(D) FROM Table1 GROUP BY A,B,C ORDER BY C 在此查询中,虽然字段C没有被选择,但他被ORDER因此字段C也应该在GROUP的字段中 如: SELECT A,B,SUM(D) FROM Table1 WHERE A IN (SELECT D FROM Table1 T1 WHERE NOT C IS NULL) GROUP BY A,B,C ORDER BY C 在此查询中字段A,B为选择字段,字段C为排序字段,但字段D虽然也在同一张表Table1中,但他在子查询中因此不用进行对D的GROUP 若要对聚合结果进行筛选则应该使用HAVING关键字,而不是WHERE关键字, 如: SELECT A,B,SUM(D) FROM Table1 WHERE COUNT(*)>2 GROUP BY A,B ---这样将会出错,因为COUNT为一个聚合函数,在WHERE子句中不能对聚合函数进行筛选 应改为: SELECT A,B,SUM(D) FROM Table1 GROUP BY A,B HAVING COUNT(*)>2 应用GROUP可以进行分类统计 相关的关键字为CUBE,ROLLUP但不建意使用这两个关键字, 在一般情况下,如果程序中的GRID有分类汇总功能,那相应的速度会比使用这两个关键字要快, 与这两个关键字一起使用的聚合函数为GROUPING(),即当进行项目分类汇总时GROUPING()将会返回1,反之则为0,为可以写统计标题时提供参考, 具体说明请参见《T-SQL》 条件 CASE WHEN 此组关键字的功能可以代替IF…THEN….ELSE或SELECT CASE 语法结构: CASE [expression] WHEN <condition> THEN result [ELSE else_result ] END 在查询中使用此语句时应尽量在END后加别名, 如: SELECT [name],xtype,CASE WHEN xtype=’U’ THEN ‘用户表’ ELSE CASE WHEN xtype=’S’ THEN ‘系统表’ END END AS 类型 FROM sysobjects 返回表: name xtype 类型 syscolumns S 系统表 tabledefine U 用户表 用此语句与SELECT用UNION联用能做行列换位 过程性语句应用 变量定义 在SQL中用户变量是以@打头的字串,系统变量用@@打头 如: @i @tmpStr 定义方法: Declare @i int Declare @tmpStr nvarchar(30) 在完成变量定义后最好进行初始设置,如 Set @i=0 Set @tmpStr=’’ 或 Select @i=0,@tmpStr=’’ 在SQL中对变量的赋值应用SET或SELECT进行 游标定义 游标,可以将查询结果返回为游标类型 定义方法: Declare cursor <CurName> For <SQL SCRIPT> 如: declare cursor GetName for SELECT [name] FROM sysobjects 游标使用方法: 打开游标: Open <CurName> 如:open GetName 检索游标: Fetch [NEXT | PRIOR | FIRST | LAST] form <CurName> [into <valuename>…] 如: Fetch next from GetName into @tmpName 当取值成功后,相应记录值会填充在@tmpName变量中,并@@FETCH_STATUS变量置为0, 若失败则@@FETCH_STATUS变量为-1 关闭游标 在使用完游标后关闭他,以便其他进程使用此游标 CLOSE <curname> 如: Close GetName 删除游标 在使用完游标后,如不再需要应该删除已使用游标, DEALLOCATE <curname> 如: Deallocate GetName转载地址:http://ineji.baihongyu.com/