SQL常用语法大全

一、 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

九：2个数据库名表关联

SELECT user_id, actual_discount_amount, source , a.mobile , b.create_time FROM auth_user.t_passenger_mobile_credential as a LEFT JOIN t_coupon AS b ON a.uid = b.user_id
WHERE a.mobile = 13871003025 ORDER BY b.create_time DESC

十、

select a.user_name , b.user_id , b.role_id, c.source ,a.*from t_user as a LEFT JOIN t_user_role as b on a.id = b.user_id LEFT JOIN t_role as c on b.role_id = c.id
where a.user_name = 'vame'