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