实用sql语句查询结果导出到excel收缩数据库之欧阳家百创编.docx
《实用sql语句查询结果导出到excel收缩数据库之欧阳家百创编.docx》由会员分享,可在线阅读,更多相关《实用sql语句查询结果导出到excel收缩数据库之欧阳家百创编.docx(8页珍藏版)》请在冰豆网上搜索。
实用sql语句查询结果导出到excel收缩数据库之欧阳家百创编
--查询结果导出到excel
欧阳家百(2021.03.07)
SqlServer
execmaster..xp_cmdshell'bcp"select*frommydatabase.dbo.mytable"queryoutc:
\temp.xls-c-q-S"."-U"sa"-P"1"'
--excel导入到SqlServer
BULKINSERTtemp1FROM'c:
\temp1.xls'
--收缩数据库
--首先截断事务日志
backuplogmydatabasewithno_log
--收缩数据库
dbccshrinkdatabase('mydatabase',0)
--查SqlServer视图sql
selecttextfromsyscommentswhereid=object_id('reportsbaseview')
select*frominformation_schema.views
--查SqlServer:
表名
select*frominformation_schema.tableswheretable_namelike'%MYTABLE%'
--查Oracle:
表名
select*fromsys.all_tableswheretable_name='MYTABLE'
--查Sqlserver列名
select*frominformation_schema.columnswheretable_name=‘MYTABLE’
--查Orable:
列名
select*fromsys.all_tab_colswheretable_name='MYTABLE'
--查Sqlserver列描述
SELECT*
FROM:
:
fn_listextendedproperty(NULL,'user','dbo','table',‘MYTABLE’,'column',default)
--查Orable:
列描述
select*fromsys.all_col_commentswheretable_name='MYTABLE'
--为查询结果添加序号(pkId必须是整数类型)
selectnumber1=(selectcount(userId)fromtusersetast2wheret2.pkId<=t1.pkId),userId,setNamefromtusersetast1
--插入100条测试记录
declare@iint
set@i=500
while(@i<600)
begin
insertintoMYTABLE(invitesetid,invitesetno,invitesetname,managerid,projectid,invitesetstatus,projecttypeid)
values(@i,@i,@i,'100001',136,0,11)
set@i=@i+1
end
--查询每个表有几条记录
declare@colIdvarchar(50)
DECLAREdetailCustom_CursorCURSORFOR
selecttop90table_nameastableNamefrominformation_schema.tablesorderbytableName
--selecttable_nameastableNamefrominformation_schema.tableswheretable_namenotin(selecttop90table_namefrominformation_schema.tablesorderbytable_name)
OPENdetailCustom_Cursor
FETCHNEXTFROMdetailCustom_Cursor
into@colId
begintrant1
declare@sqlvarchar(8000)
set@sql=''
WHILE@@FETCH_STATUS=0
BEGIN
if(len(@sql)<=7800)
begin
set@sql=@sql+'select'''+@colId+'''astableName,count(*)asdatafrom'+@colId
set@sql=@sql+'unionall'
end
FETCHNEXTFROMdetailCustom_Cursor
into@colId
END
set@sql=@sql+'select''-1'',-1'
exec(@sql)
committrant1
CLOSEdetailCustom_Cursor
DEALLOCATEdetailCustom_Cursor
C#与sql相关
1,取1条sql语句——除了某几列,其他列的都查出来,在aspx页面中写:
<%
string[]temp1=newstring[2]{"InviteId","SELFDEFINEDINVITEID"};
stringtemp=GetExtraSql("tcontent",temp1);
Response.Write(temp);
%>
protectedstringGetExtraSql(stringtableName,string[]exceptionColumns)
{
stringreturnString=String.Empty;
stringsql="selecttop1*from"+tableName;
DataSettemp=wdxl.Commfile.Dblib.GetDataSet(sql);
if(temp!
=null)
{
sql="select";
DataTabletableObj=temp.Tables[0];
for(inti=0;i{
//如果找不到则添加
if(!
StringInArray(exceptionColumns,tableObj.Columns[i].ToString()))
{
sql+=tableObj.Columns[i]+",";
}
}
intflag=sql.Length;
sql=sql.Substring(0,flag-1);
sql+="from"+tableName;
}
returnString=sql;
returnreturnString;
}
protectedboolStringInArray(string[]arrayObj,stringdata)
{
boolreturnValue=false;
for(inti=0;i{
if(arrayObj[i].ToUpper()==data.ToUpper())
{
returnValue=true;
break;
}
}
returnreturnValue;
}
--查询另外一个数据库服务器的表数据:
SELECT*FROMOPENDATASOURCE('SQLOLEDB','DataSource=192.168.0.12;database=mydatabase;userid=sa;Password=1').mydatabase.dbo.mytable
--在sqlserver2005执行上面语句可能报权限错误,需要开启一下相关参数:
execsp_configure'showadvancedoptions',1
RECONFIGUREWITHOVERRIDE
execsp_configure'AdHocDistributedQueries',1
RECONFIGUREWITHOVERRIDE
--另一种方式:
用链接服务器查询另外一个数据库服务器的表数据
--建立链接服务器
EXECsp_addlinkedserver
'mycomputer',
'',
'MSDASQL',
NULL,
NULL,
'DRIVER={SQLServer};SERVER=192.168.0.203;UID=sa;PWD=1;'
GO
--建立链接服务器登录映射
execsp_addlinkedsrvlogin
@rmtsrvname='mycomputer',@useself='false',@locallogin='Administrators',@rmtuser='sa',
@rmtpassword='1'
select*frommycomputer.mydatabase.dbo.users
--SqlServer批量改某一列的类型,temp1为零时表
declare@col1varchar(50)
declare@col2varchar(50)
DECLAREdetailCustom_CursorCURSORFOR
selectcolumn_namecol1,table_namecol2frominformation_schema.columnswheredata_type='decimal'
OPENdetailCustom_Cursor
FETCHNEXTFROMdetailCustom_Cursor
into@col1,@col2
begintrant1
WHILE@@FETCH_STATUS=0
BEGIN
begin
declare@sqlvarchar(255)
set@sql='altertable'+@col2+'altercolumn'+@col1+'intnull'
insertintotemp1values(@sql)
end
FETCHNEXTFROMdetailCustom_Cursor
into@col1,@col2
END
committrant1
CLOSEdetailCustom_Cursor
DEALLOCATEdetailCustom_Cursor
--为查询结果添加一列序号
Select(selectCount(*)fromFIELDDICTIONARYTwhereT.FIELDDICTIONARYid<=FIELDDICTIONARY.FIELDDICTIONARYid)
asNbr,*
FromFIELDDICTIONARYorderbynbrasc
--Oracle中的newid()
SELECTSYS_GUID()FROMDUAL
--监控oracle中占用磁盘I/O较高的sql语句
selecta.username,b.block_gets,b.consistent_gets,b.physical_reads,b.block_changes,
b.consistent_changes,c.sql_text
fromv$sessiona,v$sess_iob,v$sqltextc
wherea.sid=b.sidANDa.sql_address=c.address
ANDa.usernameISNOTNULL
orderbya.username,c.sql_id,c.piece
--Oracle创建及获取表的描述,及字段描述信息
--创建表描述
COMMENTONTABLEmyTableis'表的描述信息'
--创建字段描述
COMMENTONCOLUMNmyTable.IDis'字段的描述信息'
--取得表描述
select*fromuser_tab_commentswherecommentsisnotnull
--取得字段描述
select*fromuser_col_commentswherecommentsisnotnull
--SqlServer创建及获取表的描述,及字段描述信息
--创建表描述
EXECsp_addextendedproperty'描述类别(可自定义)','表的描述信息','user',dbo,'table',myTableName,null,null
--创建字段描述
EXECsp_addextendedproperty'描述类别(可自定义)'','字段的描述信息','user',dbo,'table',myTableName,'column',myColumnName
--获取表描述信息
SELECT*FROM:
:
fn_listextendedproperty(NULL,'user','dbo','table','myTableName',null,default)
--获取字段描述信息
SELECT*FROM:
:
fn_listextendedproperty(NULL,'user','dbo','table','myTableName','column',default)
--修改oracle的dbf文件的位置
c:
\>sqlplus/nolog
SQL>conn/assysdba;
SQL>shutdownimmediate
SQL>startupmount;
SQL>SELECTFILE#,ERRORFROMV$RECOVER_FILE;--查看是否有挂接失败数据文件
SQL>selectr.file#,D.name,r.error
fromv$ercover_filer,v$datafileD
wherer.file#=D.file#;
--查看失败文件的路径(如果没有挂接失败的数据文件,可以直接查v$datafile)
SQL>alterdatabaserenamefile'E:
\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF'TO'D:
\oracle\product\10.2.0\oradata\orcl\USERS01.DBF';
--同上。
。
。
。
。
--改之
SQL>ALTERDATABASEOPEN;