sql sever 表处理（增删改查等）

-------------------------------------------------------------------------查
--查找表里是否有某个字段:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME  ='字段名'
AND Table_Name like '[^V]%'

-------------------------------------------------------------------------增
--新增字段:
ALTER TABLE 表名 ADD 字段 char(1) NOT NULL DEFAULT '0' WITH VALUES;	
ALTER TABLE 表名 ADD  字段 NVARCHAR (500) CONSTRAINT [DF_表名_字段] DEFAULT ('') NOT NULL

--新增带有约束的字段:
ALTER TABLE 表名 ADD 字段 CHAR (60) NULL  
ALTER TABLE 表名 ADD  CONSTRAINT DF_表名_字段  DEFAULT ('')  FOR 字段
		
ALTER TABLE 表名 ALTER COLUMN 字段  CHAR (60)  NOT NULL

--新增多个字段:
ALTER TABLE 表名 ADD 
	[字段1] [varchar](255) NOT NULL　DEFAULT '' WITH VALUES,
	[字段2] [varchar](255) NOT NULL DEFAULT '' WITH VALUES,
	[字段3] [varchar](255) NOT NULL DEFAULT '' WITH VALUES,
	[字段4] [char](2) NOT NULL DEFAULT '00' WITH VALUES;

-------------------------------------------------------------------------删
--删除表字段:
ALTER TABLE 表名 DROP COLUMN 字段
--删除有约束的字段:
1.查询约束：EXEC sp_helpconstraint @objname='表名'
2.ALTER TABLE 表名 DROP CONSTRAINT DF__约束名称
3.ALTER TABLE 表名 DROP COLUMN 字段

-------------------------------------------------------------------------改
--修改表字段名:
EXECUTE  sp_rename '表名.字段名','新字段名'
--修改表字段类型:
ALTER TABLE 表名  ALTER COLUMN 字段名  类型

--修改有约束表字段类型和默认值：
ALTER TABLE 表名 DROP CONSTRAINT [DF_表名_字段]
ALTER TABLE 表名 ALTER COLUMN [字段]  CHAR(60) NOT NULL
ALTER TABLE 表名 ADD CONSTRAINT DF_表名_字段  DEFAULT ('')  FOR [字段]

-------------------------------------------------------------------------主键
--查找表主键:
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + CONSTRAINT_NAME), 'IsPrimaryKey') = 1
AND TABLE_NAME = 表名

--创建主键约束:
ALTER TABLE 表名 ADD CONSTRAINT [主键约束名称] PRIMARY KEY (主键字段,主键字段,...)

--创建外键约束:
ALTER TABLE 表名 ADD CONSTRAINT [外键约束名称] FOREIGN KEY (字段) REFERENCES 外链表名(字段)

--创建复合主键
ALTER TABLE 表名1 ADD CONSTRAINT [复合主键约束名称] FOREIGN KEY (字段1, 字段2) REFERENCES 表名2(字段1, 字段2)

-------------------------------------------------------------------------复制表
--复制表数据到另一个表:
INSERT INTO 目标表名 (字段1, 字段2, 字段3)
SELECT 字段1, 字段2, 字段3
FROM 被拷贝表名;
	--char类型会自动补足位数,varchar类型不会自动补足位数

-------------------------------------------------------------------------临时表
--创建临时表:
CREATE TABLE #Temp
(
    id INT NOT NULL,
    customer_name NVARCHAR(50) NOT NULL,
    age INT,
    CONSTRAINT PK_Globals PRIMARY KEY(id)
)
INSERT INTO #Temp VALUES(1,'老王',20),(2,'老张',30),(3,'老李',25)

--查询临时表:
SELECT * FROM #Temp

--查询数据并写入临时表:
SELECT * INTO #Temp FROM 表名;

--删除临时表:
DROP TABLE #Temp;

-------------------------------------------------------------------------解锁表
1.查询被锁表:
SELECT request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName
FROM sys.dm_tran_locks WHERE resource_type='OBJECT';

2.解锁:
DECLARE @spid INT
SET @spid = 89 --锁表进程（89上面查询的spid）
DECLARE @sql VARCHAR(1000)
SET @sql='kill '+CAST(@spid as VARCHAR)
EXEC(@sql)

-------------------------------------------------------------------------数形转换
/*创建带有虚拟列的结果集*/
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowKey, --虚拟列
	 Sumu ,ID
INTO #TempResult
FROM PlanContact_F1_R;


DECLARE @Allergen NVARCHAR(MAX);
DECLARE @RetVal CHAR(60);

DECLARE @RowCount INT = 1;
DECLARE @TotalRows INT;

/*获取结果集总行数*/
SELECT @TotalRows = COUNT(*) FROM #TempResult; 

/*循环更新*/
WHILE @RowCount <= @TotalRows
BEGIN

 /*从结果集中获取虚拟列和目标值*/
 SELECT @Allergen = Sumu, @RetVal = '000000000000000000000000000099999999999999999999999999999999'
 FROM #TempResult
 WHERE RowKey = @RowCount;

 /*如果@Allergen里面包含小麦,将@RetVal对应的下标设为'1'*/
 IF PATINDEX('%小麦%', @Allergen) > 0
    SET @RetVal = STUFF(@RetVal, 1, 1, '1');

 /*更新原始表中的数据*/
 UPDATE P
 SET P.Sumu = @RetVal
 FROM PlanContact_F1_R AS P
 INNER JOIN #TempResult AS T ON T.ID = P.ID 
 WHERE T.RowKey = @RowCount;

 SET @RowCount += 1;

END

/*删除临时表*/
DROP TABLE #TempResult;