一、 Table 增加列
1.增加列：
alter table tableName add columnName varchar(30)
1.2. 修改列类型：
alter table tableName alter column columnName varchar(4000)
1.3 修改列的名称：
EXEC sp_rename 'tableName.column1' , 'column2' (把表名为tableName的column1列名修改为column2)
1.4 删除列名
ALTER TABLE At_StaffDailyRangeDetail DROP COLUMN column_name
1.5 根据字段名查询表名
SELECT b.name as TableName,a.name as columnname
From syscolumns a INNER JOIN sysobjects b
ON a.id=b.id
AND b.type='U'
AND a.name='Roles字段名'
二、库转库：
----insert into 库名1.dbo.表名 （字段） select (字段) 库名2.dbo.表名
三、case when :
3.1 select (case Gender when 1 then '男' when 2 then '女' else '其他' end) as Gender from Table1
3.2 SELECT lft.FSName_Chinese, lft.fSNO,
(CASE lft.FSNO WHEN 0 THEN '全天' WHEN 1 THEN '上半天' WHEN 2 THEN '下半天' ELSE '其它' end) as 休假类型 FROM leave_fitshift lft
四、修改数据库字段空格
UPDATE hr_Staff SET StaffName = RTRIM(LTRIM(StaffName))
五、Excel拼接函数。
5.1 =CONCATENATE("update org_dictionary set Name_Language2='",C555,"' where Name_Chinese ='",B555,"'")
5.2 =CONCATENATE("UPDATE lb SET lb.NextYearAdjust='",C555,"' FROM Leave_Balance AS lb LEFT JOIN Leave_Code AS lc ON lc.id=lb.LeaveCode_id WHERE lc.LeaveBenefitCode='AL' and lb.StaffNo='",B555,"'")
5.3 = " update pay_Formula_Cur set Description_English = ' "&C2&" ' where Payroll_Code = ' "&B2&" ' "
六、 查询某列数据重复
--SELECT 某一列, COUNT( 某一列 ) FROM 表 GROUP BY 某一列 HAVING COUNT( 某一列 ) 〉1
SELECT staff_no,COUNT(staff_no) FROM Pay_Result_Cur GROUP BY staff_no HAVING COUNT(staff_no)>1
七：联表update
UPDATE lb SET lb.CurYearBalance = 5 from Leave_Balance lb
left join Leave_Code lc ON lc.Id = lb.LeaveCode_id
WHERE lb.StaffNo ='0092' AND lc.LeaveBenefitCode ='NAL'
八： 联表Delete
DELETE ap FROM At_PunchClockInfo ap INNER JOIN At_Card ac ON ap.At_Card_id = ac.Id WHERE ac.StaffNo ='407725'
七： 触发器：
CREATE TRIGGER replace0000 ON det_message
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
update det_message set Body=replace(body,'0000','0') where status<>'1' and ErrorTimesNow<>20
SET NOCOUNT OFF;
END;
GO
八：聚合函数：
SELECT SUM(ot.SwitchHours/36000000000) , Max(ot.staffno) FROM Ot_Transaction AS ot WHERE ot.SwitchType='1' AND ot.staffno='A0165'GROUP BY StaffNo