SQL Common Grammar Encyclopedia

Table Add Column

1. Add Columns:
ALTER TABLE TableName add columnName varchar (30)

1.2. Modify the column type:
ALTER TABLE tableName ALTER COLUMN COLUMNNAME varchar (4000)

1.3 Modify the name of the column:
EXEC sp_rename ' tablename.column1 ', ' column2 ' (Modify the Column1 column name of the table named TableName to Column2)

Second, library transfer:

----INSERT INTO library name 1.dbo. Table name (field) select (field) library name 2.dbo. Table name

Iii. When:

3.1 Select (case Gender if 1 then ' Male ' when 2 then ' women ' else ' other ' end) as Gender from Table1

3.2 SELECT LfT. Fsname_chinese, Lft.fsno,
(Case LfT. Fsno when 0 Then ' all day ' when 1 Then ' half ' time 2 Then ' next half day ' else ' other ' end ' as leave type from Leave_fitshift lft

Iv. Modifying database field spaces

UPDATE hr_staff SET staffname = RTRIM (LTRIM (staffname))

Five, Excel splicing function.

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 joins Leave_code as LC on LC.ID=LB. LEAVECODE_ID WHERE LC. Leavebenefitcode= ' AL ' and lb. Staffno= ' ", B555," ' ")

Six, query a column of data duplication
--select a column, count (a column) from table GROUP by a column having COUNT (a column) 〉1
SELECT Staff_no,count (staff_no) from Pay_result_cur GROUP by Staff_no have COUNT (staff_no) >1

Seven: Trigger:

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

Eight: aggregate function:

SELECT SUM (OT. switchhours/36000000000), Max (OT.STAFFNO) from ot_transaction as ot WHERE ot. Switchtype= ' 1 ' and ot.staffno= ' A0165 ' GROUP by staffno

SQL Common Grammar Encyclopedia