1. sql trim()函数去掉两头空格
sql语法中没有直接去除两头空格的函数,但有ltrim()去除左空格rtrim()去除右空格。
合起来用就是sql的trim()函数,即select ltrim(rtrim(UsrName))
2.
sql触发器常用语法
这是一个自动采购的触发器,主要需要注意的触发条件,以及临时表的作用。
创建触发器 CREATE TRIGGER tr_update_Stock
删除触发器 DROP
TRIGGER tr_update_Stock
禁用:
ALTER TABLE trig_example DISABLE TRIGGER trig1
GO
恢复:
ALTER TABLE trig_example ENABLE TRIGGER trig1
GO
—————————————————————
–禁用某个表上的所有触发器
ALTER TABLE 你的表 DISABLE TRIGGER all
–启用某个表上的所有触发器
ALTER TABLE 你的表 enable TRIGGER all
–禁用所有表上的所有触发器
exec sp_msforeachtable ‘ALTER TABLE ? DISABLE TRIGGER all’
–启用所有表上的所有触发器
exec sp_msforeachtable ‘ALTER TABLE ? enable TRIGGER all’
3. 在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢:
1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断
select * from people
where peopleId in (select peopleId from people group by peopleId having
count(peopleId) > 1)
2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录
delete from people
where peopleName in (select peopleName from
people group by peopleName having
count(peopleName) > 1)
and peopleId not in (select min(peopleId) from people group by
peopleName having count(peopleName)>1)
3、查找表中多余的重复记录(多个字段)
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq
having count(*) > 1)
4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录
delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by
peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having
count(*)>1)
5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by
peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having
count(*)>1)
6.消除一个字段的左边的第一位:
update tableName set
[Title]=Right([Title],(len([Title])-1)) where Title like ‘村%’
7.消除一个字段的右边的第一位:
update tableName set
[Title]=left([Title],(len([Title])-1)) where Title like ‘%村’
8.假删除表中多余的重复记录(多个字段),不包含rowid最小的记录
update vitae set ispass=-1
where peopleId in (select peopleId from vitae group by peopleId
4.
批量更新:
UPDATE TABLE_1
SET COL_1=TT2.RESULT_COL_1,COL_2=TT2.RESULT_COL_2
FROM TABLE1 AS TT1
INNER JOIN (SELECT
RESULT_COL_1,RESULT_COL_2 FROM TABLE_2) AS TT2
ON TT2.RELATION_COL=TT1.RELATION_COL
5.
全角,半角字符控制
从输出可以看到
1.半角字符是从33开始到126结束
2.与半角字符对应的全角字符是从65281开始到65374结束
3.其中半角的空格是32.对应的全角空格是12288
4.半角和全角的关系很明显,除空格外的字符偏移量是65248(65281-33 = 65248)