常用SQL

SQLSERVER:

1.

查询所有表：

select   name   from   sysobjects   where   type='U'

 

2.

获取表的主外键:

exec sp_helpconstraint   'table_name';

 

3. 获取表的基本字段属性

获取SqlServer中表结构  SELECT  syscolumns.name,systypes.name,syscolumns.isnullable, syscolumns.length  FROM  syscolumns, systypes  WHERE  syscolumns.xusertype  =  systypes.xusertype  AND  syscolumns.id  =   object_id ( 'table_name ' )

4.获取某个表的所有属性

select name from syscolumns where id in (select id from sysobjects where name='tablename')

5.sqlserver 字符串连接

select name+'ttt' from syscolumns

6.设置ISOLATION LEVEL

alter database trunk set READ_COMMITTED_SNAPSHOT on

7.找出数据库表里重复记录和重复记录数的SQL

如果TABLE1有两个column COL1和COL2,那么下面的SQL可以找出TABLE1里的重复记录和重复记录数

SELECT COUNT(*) AS RowNumber,COL1,COL2
FROM TABLE1 T1
WHERE 
 (SELECT COUNT(*)
 FROM TABLE1 T2
 WHERE T1.COL1 = T2.COL1 AND T1.COL2 = T2.COL2)>1
GROUP BY COL1,COL2

 

Oracle

1.

select * from employees e where (select count(*) from employees e2 where e2.salary > e.salary ) between 1 and 2