sql serve 存储过程及应用实例笔记.docxVIP

2015—EASYMAN学习笔记（sql ） 1、 CREATE proc 新建存储过程 ALTER PROC（EDURE –可省）修改存储过程 列：ALTER PROCEDURE [dbo].[getcrdh] @dh nvarchar(12) output --输出 AS declare @zh int declare @xh varchar(4) BEGIN End DORP PROC 删除存储过程 2、插入数据的两种方式： A.Insert INTO table(field1,field2,...) values(value1,value2,...) B.SELECT INTO FROM语句 语句形式为：SELECT vale1, value2 into Table2 from Table1 要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中。示例如下： SELECT INTO FROM语句创建表Table2并复制数据 select a,c INTO Table2 from Table1 GO 3、 新建索引 create index index_name on table_name | view_name(colume_name[1,,,n]) index_name 是索引名称table_name|view_name 是表的名称或视图名称colume_name是表或视图的哪些列上建索引 4、如何对一批记录，加上顺序递增的行号呢？ 方法一 select t.* , px = (select count(1) from tb where id t.id) + 1 from tb t 方法二 用row_number select t.* , px = row_number() over(order by id) from tb t 列： update toolsmanage1 set empnosid=c.idd from (select row_number() over(order by id) as idd,id from toolsmanage1 where empnosid is null) c, toolsmanage1 where c.id=toolsmanage1.id and empnosid is null 5、将一个表的自增列该为手动 当 IDENTITY_INSERT 设置为 OFF 时，不能为表 table中的标班识列插入显示值 set IDENTITY_INSERT part off/ON 6、小计，总计SQL语句（一个很有意思的sql语句） CREATE TABLE #test ( Name VARCHAR(10), [procedure] CHAR(1), model VARCHAR(5), quantity INT); INSERT INTO #test SELECT A, 1, φ50, 500 UNION ALL SELECT A, 1, φ50, 600 UNION ALL SELECT A, 1, φ100, 500 UNION ALL SELECT A, 2, φ50, 700 UNION ALL SELECT A, 2, φ100, 200 UNION ALL SELECT B, 1, φ50, 1000; SELECT CASE WHEN GROUPING(Name) = 1 THEN 总计 WHEN GROUPING(Name) = 0 AND GROUPING([procedure]) = 1 THEN Name + 合计 WHEN GROUPING(Name) = 0 AND GROUPING([procedure]) = 0 AND GROUPING([model]) = 1 THEN Name + 的 + [procedure] + 小计 ELSE Name END AS k, CASE WHEN GROUPING([model]) = 1 THEN ELSE [procedure] END AS [procedure], ISNULL(model, ) AS model, SUM(quan