group by，统计函数的分组标准：
count , sum ,max, min, avg
重命名数据库
sp_renamedb 'old_name' , 'new_name'
复制表
select * into b from a(方法1）
select * into b from a where 1<>1(仅用于sqlserver)
子查询
select ksbm, ksdh,xm from DBF_CFYP where KSBM not in (select KSBM from KSZD1)
select a, b, c from table_a where a in (select d from b)
两张关联表，删除主表中已经在副表中没有的信息。
delete from table1 where not exists (select * from table2 where table1.field1 = table2.field1)
判断两张表是否相等
if (select checksum_agg(binary_checksum(*)) from DBF_CFYP)
=
(select checksum_agg(binary_checksum(*)) from DBF_CFYP)
print '相等'
else
print '不相等'
记录搜索
开头到N条记录
select top N* from table
N到M条记录（要有主索引ID）
select top M-N * from table where ID in (select top M ID from table) order by ID Desc
N到结尾记录
select top N* from table order by ID DESC
案例
例子1
一张表有一万多条记录，表的第一个字段RecID是自增字段，写一个sql语句，找出第31到40条记录。
select top 10 recid from A where recid not in (select top 30 recid from a where recid > -1) order by recid..........
获取数据库中所有表
select name from sysobjects where xtype = 'u' and status >= 0
获取某一个表的所有字段
select name from syscolumns where id = OBJECT_ID('DBF_CFYP')
select name from syscolumns where id = object_id(表名）
select name from syscolumns where id in (select id from sysobjects where type = 'u' and name = '表名')
查看与某一个表相关的视图，存储过程，函数。
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'
查询用户所创建的所有数据库
select * from master..sysdatabases D where sid not in (select sid from master..syslogins where name = 'sa' )
select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01
查询某一个表的字段和数据类型
select column_name, data_type from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'dbf_cfyp'
常识
在sql查询中，from 后面可以跟多少张表和视图：256.
在sql语句order by 查询时，先排序，然后再查询。
在sql中，一个字段最大的容量是8000.
本文转自鹅倌51CTO博客，原文链接： http://blog.51cto.com/kaixinbuliao/880558，如需转载请自行联系原作者
