| SQL语句的构造实在是太有趣了，让我们大家一起收集、分享和学习
语句区域
1、delete table1 from (select * from table2) as t2 where table1.id=t2.id
2、truncate table table1 (不在事务日志中做记录，比delete table快，但不能激活触发器)
3、update table1 set column=column+1 where id=(select id from table2)
4、update table1 set column=column+1 from table1,table2 where table1.id=table2.id
5、select top n [Percent] * from table1 '输出百分比记录
6、select id,column1 * column2 as column from table1 '可算明白as的用法了
7、select * from table1 where column1 like 'SQL#_G_O' escape '#' '单匹配
8、select table1.id from table1 where not exists (select table2.id from table2 where table1.id=table2.id) '这个应该比not in快一些
9、select table1.id from table1,table2 where table1.id<>table2.id '看复合查询机制
10、select table1.id from table1,table2,(select id from table3) as t3 where table1.id=table2.id and table2.id=t3.id '有些类似[1]了......
11、select * from table1 where column1 like '[A]%' or like '[^B]%'
12、select @column1=column1 from table1;select @column1 as column1 '存储到自定义变量
13、select * from table1 where contains(column1,'"char1" or "char2*"') '全文索引
14、select * from table1 where contains(column1,'"前有" near "中有" near "后有"')
15、select * from table1 where contains(column1,'formsof(inflectional,go)') '派生
16、select * from table1 where contains(description,'isabout(apple weight(.9),boy weight(.8),china weight(.7))') '权重
17、select * from table1 where freetext(column1,'char') '仅支持文字不支持表达式搜索
18、insert into table1 select column1,count(column1) from table2 group by column1 '统计
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
杂类区域
[删除当前游标记录]:delete from table1 where current of dinof_cursor
[清楚事务日志]:backup log dbname with truncate_only '暂不知更新多少数据会回滚
[设定一次读取容量]:set @@textsize '最大字节数2,147,483,647
[事务回滚]: save transaction del_here ; if @@error<>0 then rollback tran del_here |