SQL常用语法大全
- 格式：pdf
- 大小：39.47 KB
- 文档页数：2
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'
