SQL variables, Substring, CHARINDEX, case functions, removing duplicates

isnull(aa,0) 删除表数据： truncate table aaa   添加字段： ALTER TABLE table1 ADD col1 varchar(200) DEFAULT ‘2008-05-22‘ 修改字段名： alter table table1 rename column col1 to col2; 修改字段属性： alter table table1 alter column col1 varchar(200) not null; 修改默认值： alter table table1 add constraint df default(‘嘿嘿‘) for col1;   insert into table1(COL1,COL2) Select Col1,col2 From Table2 where table2.id=1 update t    set t.col1 = o.col1   from table1 AS t          inner join        table1 AS o           on t.id = o.id The first parameter is the object to intercept, the second argument is the starting position of the intercept (starting at 1), and the third parameter is the length of the Intercept Select Substring (' 1234567890 ', -1,3) The first argument is the character to look for, the second argument is the object to be found, and the index of the string is starting at 1. Select CHARINDEX ('. ', ' 132.12.3 ') Gets the length of the string, with the argument being the object to find Len (' 123456 ') Reverses a string Select reverse (' Hello,world ') The output will be as follows: Dlrow,olleh Gets the location of the last "-" occurrence CHARINDEX ('-', reverse (@str)) Gets the character after the last "-" Reverse (substring (reverse (@str), 1,charindex ('-', reverse (@str))-1)) Type conversions CAST (@XX as char (20)) CONVERT (char), @XX) Cast (0 as bit) --Simple Case function Case Sex When ' 1 ' Then ' men ' When ' 2 ' then ' women ' Else ' other ' end --case search function case If sex = ' 1 ' Then ' man ' When sex = ' 2 ' Then ' women ' Else ' other ' end declaring and using variables declare @OldItemPath nvarchar (500); Select @OldItemPath = ' 123 '; Declaration and use of table variables Declare @table1 table (Id int); INSERT INTO @table1 Select Id from table1; SELECT * from @table1; Keyword DISTINCT, removing duplicates Select DISTINCT ip,city from table2//keyword DISTINCT, removing duplicates

SQL variables, Substring, CHARINDEX, case functions, removing duplicates