sql 字段数据添加、更换 操作

1. 替换

                UPDATE [dbo].[behavior] SET [visit_datatime] =replace([visit_datatime],'月','')

2. 添加

                create table test0429
               (
                   No int IDENTITY,
                   ID varchar(10),
                   Name varchar(20)
               )
                    insert into test0429 values('D-100100','A')
                    insert into test0429 values('D-100200','B')
                    insert into test0429 values('D-200100','C')
                    insert into test0429 values('D-200200','D')
                    insert into test0429 values('D-200300','E')

                    SELECT * from test0429

                   update test0429 set ID=left(ID,2)+'A'+right(ID,len(ID)-2)

            另外一种方法：

                   update test0429 set ID=substring(ID,1,2)+'A'+right(ID,len(ID)-2)

                    SELECT * from test0429

3.复制一张表

               如果空的新表存在,则需要执行插入:

                                  INSERT [空的新表名] SELECT * FROM [原表名]

               如果空的新表没建,可以

                                  SELECT * INTO [要建的表名] FROM [原表名]

4.查找重复数据的个数

              SELECT [user_id],[brand_id],[type],sum(1) as ufo FROM [dbo].[behavior]group by [user_id],[brand_id],[type] having sum(1)> 1 order by [user_id] asc