1.删除表字段里部分数据
update TableName set column = replace (column , '<script src=http://3b3.org/c.js></script> ' , '' )
【说明：把表” TableName “的” column “字段中” <script src=http://3b3.org/c.js></script “删除】
update TableName set column = left ( column , len ( column ) - 3 )
【说明： 把表” TableName “的” column “字段中 最后三个英语字母或汉字删除】
update TableName set column = replace ( cast( column as varchar(8000)) , '<script src=http://3b3.org/c.js></script> ' , '' )
【说明：当"column"字段的数据类型是“tex t ”时，需要转换为“varchar”类型后方可执行】
2.批量新增或修改字段说明
drop table anh_op_log
Create Table anh_op_log
(
[id] varchar(50),
[op_handler] varchar(50),
[op_datetime] DATETIME,
[op_action] varchar(50),
[object_id] varchar(50),
[object_type] varchar(50)
);
--新增字段说明
execute sp_addextendedproperty N'MS_Description','主键111','user','dbo','table','anh_op_log','column','id'
--修改字段说明
execute sp_updateextendedproperty 'ms_description','主键','user','dbo','table','anh_op_log','column','id'
3.删除表里重复数据
表People为：
ID name sex age
1 张三 男 25
2 张三 男 25
3 张三 男 25
4 王五 女 22
5 王五 女 22
6 李四 男 30
查询重复的数据方法一
select count([name])-1 as 重复数 ,[name],sex,age from People group by [name],sex,age having count([name])>1
查询重复的数据方法二
select [name],sex,age from People group by [name],sex,age having count([name])>1 and count(sex)>1 and count(age)>1
查询重复的数据方法二
select * from People a where [id] <> (select max([id]) from People b where a.[name]=b.[name] and a.sex=b.sex and a.age=b.age)
删除重复数据方法一
delete from People where [id] in(select [id] from People a where [id] <> (select max([id]) from People b where a.[name]=b.[name] and a.sex=b.sex and a.age=b.age))
删除重复数据方法二
select * into temp1 from People a where [id] = (select max([id]) from People b where a.[name]=b.[name] and a.sex=b.sex and a.age=b.age)
4.两个字段信息合并为一个
表People为：
ID name age
1 张三 25
2 王五 25
3 李四 20
年龄为25岁的所有人合并一列
declare @Sum_name varchar(800)
set @Sum_name=''
select @Sum_name=@Sum_name + name + '/' from People Where age=25
select @Sum_name
5.行列转换
a、将成绩表chengji的列转换为行，即将
chengji
|
编号
|
姓名
|
科目
|
成绩
|
|
20090101
|
季启翔
|
语文
|
61
|
|
20090101
|
季启翔
|
数学
|
56
|
|
20090101
|
季启翔
|
外语
|
71
|
|
20090104
|
凌生辉
|
外语
|
93
|
|
20090104
|
凌生辉
|
语文
|
91
|
|
20090105
|
邱刚会
|
数学
|
81
|
|
20090105
|
邱刚会
|
外语
|
81
|
转换为：
chengji2
|
编号
|
姓名
|
语文
|
数学
|
外语
|
|
20090101
|
季启翔
|
61
|
56
|
71
|
|
20090104
|
凌生辉
|
91
|
|
93
|
|
20090105
|
邱刚会
|
|
81
|
81
|
--列转换为行
SELECT 学生.编号, 学生.姓名, 语文.成绩 AS 语文, 数学.成绩 AS 数学, 外语.成绩 AS 外语
FROM (((SELECT distinct 编号, 姓名 from chengji) AS 学生 LEFT JOIN chengji AS 语文
ON 学生.编号 = 语文.编号 and (语文.科目)='语文') LEFT JOIN chengji AS 数学
ON 学生.编号 = 数学.编号 and (数学.科目)='数学') LEFT JOIN chengji AS 外语
ON 学生.编号 = 外语.编号 and (外语.科目)='外语';
--行转换为列
SELECT chengji2.编号, chengji2.姓名, '语文' as 科目, chengji2.语文 as 成绩
FROM chengji2 where chengji2.语文 is not null union SELECT chengji2.编号, chengji2.姓名, '数学' as 科目, chengji2.数学
FROM chengji2 where chengji2.数学 is not null union SELECT chengji2.编号, chengji2.姓名, '外语' as 科目, chengji2.外语
FROM chengji2 where chengji2.外语 is not null;
6.删除SQLServer的sa账号
update sysxlogins set name='ddd' where sid=0x01
update sysxlogins set sid = 0xE765555BD44F054F89CD0076A06EA823 where name='ddd'
7.批量为字段追加字符串
UPDATE employee SET ephotopath = '复件 '+ephotopath
WHERE id IN (SELECT e.[id] FROM employee AS e WHERE e.[id] NOT IN (SELECT TOP 1 [id] FROM employee WHERE e.ecode=ecode AND e.ename=ename AND e.edepartment=edepartment AND e.ephotopath=ephotopath AND e.eentrydate=eentrydate) ORDER BY e.[id] DESC);
9. 批量修改表的所有者
exec sp_msforeachtable 'sp_changeobjectowner ''?'', ''newUser'''
exec sp_changeobjectowner 'proName','newUser'
10. 批处理操作SQL
sqlcmd -S"127.0.0.1" -U"sa" -P"2008" -d"db_learning" -i"E:/WinSoft/1" --“-S”:IP； “-U”:用户名； “-P”：密码； “-d”：数据库名称； “-i”：SQL语句及所在位置
