经典sql语句

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.