sql_server_存储过程及应用实例笔记.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 EndDORP 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 GO3、新建索引create index index_nameontable_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_numberselect 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,toolsmanage1where c.id=toolsmanage1.idand empnosid is null5、将一个表的自增列该为手动当 IDENTITY_INSERT 设置为 OFF 时，不能为表 table中的标班识列插入显示值set IDENTITY_INSERT part off/ON6、小计，总计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(quantity) AS quantity FROM #test group by Name, [procedure], model wi