sqlserver2008
(1)查询所有的数据库名字：
use master;
select * from sysdatabases;
(2)查询指定库中用户建的的所有表：
select t.name from [指定库]..sysobjects t where xtype='u' and status>=0
(3)查询指定表的列描述：
select a.name as table_name,b.name as column_name,cast(isnull(c.[value],'') as varchar(23)) as comments
from sys.tables a inner join sys.columns b on b.object_id=a.object_id left join sys.extended_properties c on c.major_id=b.object_id and c.minor_id=b.column_id where a.name= '指定表';
(4)获取连续日期:
SELECT CONVERT(VARCHAR(10),DATEADD(DD,NUMBER,'2015-02-01'),120)AS TIME FROM MASTER..SPT_VALUES WHERE TYPE='P' AND DATEADD(DD,NUMBER,'2015-02-01')<='2015-02-08';
(5)获取连续月份:
在上一个语句上对字符串截取位数上稍作修改就可以。
(6)从一张表查部分字段到另一张表:
INSERT INTO 表二 (username,roleid) select WorkID,'2' from 表一；
(7)从一张表查部分字段到另一张表,如果已经存在了，就不再插入:
INSERT INTO 表二 (username, roleid) SELECT DISTINCT
(WorkID),
'2'
FROM
表一 a
WHERE NOT EXISTS (
SELECT
1
FROM
表三 b
WHERE
a.WorkID = b.username
)
-------------------------------------------------------------------------------------------------
mysql
(1)把某个表中查询的记录插入另一张表：
insert into 某库名.表名(guId,gxId,loginName,userName,phone,telephone,email,Duties,Type,createDate,serino,idnum,sex,birth,grade,status,oldid)
select UUID(),oid,name,name,phone,phone,'',Position,'3','','',Idcard,'','','','','' from 某库名.另一表名;
--------------------------------------------------------------------------------------------------------
oracle
(1)根据用户名查询表和表描述：
select t.TABLE_NAME as tablename , t.COMMENTS as comments from all_tab_comments t where t.OWNER = '用户名';
(2)根据用户名查询表名：
select table_name as tablename FROM all_tables WHERE owner = upper('用户名')
