经典SQL短小代码收集汇总
【SQL Server 數(shù)據(jù)庫導入導出部分】
1、在查詢分析器下查詢Excel文檔
SELECT * FROM
OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:\測試.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$
2、從數(shù)據(jù)庫中導出數(shù)據(jù)并存到文件中
EXEC master..xp_cmdshell 'bcp CAS2004..HGZ_LIAOJIAN out c:\temp1.xls -c -q -S"." -U"sa" -P""'
EXEC master..xp_cmdshell 'bcp CAS2004..HGZ_LIAOJIAN out c:\temp1.txt -c -q -S"." -U"sa" -P""'
3、從文件中導入數(shù)據(jù)到數(shù)據(jù)庫對應表中
EXEC master..xp_cmdshell 'bcp CAS2004..HGZ_LIAOJIAN in c:\temp1.xls -c -q -S"." -U"sa" -P""'
EXEC master..xp_cmdshell 'bcp CAS2004..HGZ_LIAOJIAN in c:\temp1.txt -c -q -S"." -U"sa" -P""'
【SQL SERVER 數(shù)據(jù)庫實用SQL語句】
1.按姓氏筆畫排序:
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as
2.分頁SQL語句
select * from(select (row_number() OVER (ORDER BY tab.ID Desc)) as rownum,tab.* from 表名 As tab) As t where rownum between 起始位置 And 結(jié)束位置
3.獲取當前數(shù)據(jù)庫中的所有用戶表
select * from sysobjects where xtype='U' and category=0
4.獲取某一個表的所有字段
select name from syscolumns where id=object_id('表名')
5.查看與某一個表相關(guān)的視圖、存儲過程、函數(shù)
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'
6.查看當前數(shù)據(jù)庫中所有存儲過程
select name as 存儲過程名稱 from sysobjects where xtype='P'
7.查詢用戶創(chuàng)建的所有數(shù)據(jù)庫
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
8.查詢某一個表的字段和數(shù)據(jù)類型
select column_name,data_type from information_schema.columns
where table_name = '表名'
9.使用事務
在使用一些對數(shù)據(jù)庫表的臨時的SQL語句操作時，可以采用SQL SERVER事務處理，防止對數(shù)據(jù)操作后發(fā)現(xiàn)誤操作問題
開始事務
Begin tran
? Insert Into TableName Values(…)
SQL語句操作不正常，則回滾事務。
回滾事務
Rollback tran
SQL語句操作正常，則提交事務，數(shù)據(jù)提交至數(shù)據(jù)庫。
提交事務
Commit tran
10. 按全文匹配方式查詢
字段名 LIKE N'%[^a-zA-Z0-9]China[^a-zA-Z0-9]%'
OR 字段名 LIKE N'%[^a-zA-Z0-9]China'
OR 字段名 LIKE N'China[^a-zA-Z0-9]%'
OR 字段名 LIKE N'China
11．計算執(zhí)行SQL語句查詢時間
declare @d datetime
set @d=getdate()
select * from SYS_ColumnProperties select [語句執(zhí)行花費時間(毫秒)]=datediff(ms,@d,getdate())
12、說明：幾個高級查詢運算詞
A： UNION 運算符
UNION 運算符通過組合其他兩個結(jié)果表（例如 TABLE1 和 TABLE2）并消去表中任何重復行而派生出一個結(jié)果表。當 ALL 隨 UNION 一起使用時（即 UNION ALL），不消除重復行。兩種情況下，派生表的每一行不是來自 TABLE1 就是來自 TABLE2。
B： EXCEPT 運算符
EXCEPT 運算符通過包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重復行而派生出一個結(jié)果表。當 ALL 隨 EXCEPT 一起使用時 (EXCEPT ALL)，不消除重復行。
C： INTERSECT 運算符
INTERSECT 運算符通過只包括 TABLE1 和 TABLE2 中都有的行并消除所有重復行而派生出一個結(jié)果表。當 ALL 隨 INTERSECT 一起使用時 (INTERSECT ALL)，不消除重復行。
SQL code
/******* 導出到excel
EXEC master..xp_cmdshell 'bcp SettleDB.dbo.shanghu out c:\temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P""'
/*********** 導入Excel
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
/*動態(tài)文件名
declare @fn varchar(20),@s varchar(1000)
set @fn = 'c:\test.xls'
set @s ='''Microsoft.Jet.OLEDB.4.0'',
''Data Source="'+@fn+'";User ID=Admin;Password=;Extended properties=Excel 5.0'''
set @s = 'SELECT * FROM OpenDataSource ('+@s+')...sheet1$'
exec(@s)
*/
SELECT cast(cast(科目編號 as numeric(10,2)) as nvarchar(255))+' ' 轉(zhuǎn)換后的別名
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
/********************** EXCEL導到遠程SQL
insert OPENDATASOURCE(
'SQLOLEDB',
'Data Source=遠程ip;User ID=sa;Password=密碼'
).庫名.dbo.表名 (列名1,列名2)
SELECT 列名1,列名2
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
/** 導入文本文件
EXEC master..xp_cmdshell 'bcp dbname..tablename in c:\DT.txt -c -Sservername -Usa -Ppassword'
/** 導出文本文件
EXEC master..xp_cmdshell 'bcp dbname..tablename out c:\DT.txt -c -Sservername -Usa -Ppassword'
或
EXEC master..xp_cmdshell 'bcp "Select * from dbname..tablename" queryout c:\DT.txt -c -Sservername -Usa -Ppassword'
導出到TXT文本，用逗號分開
exec master..xp_cmdshell 'bcp "庫名..表名" out "d:\tt.txt" -c -t ,-U sa -P password'
BULK INSERT 庫名..表名
FROM 'c:\test.txt'
WITH (
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
)
--/* dBase IV文件
select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBase IV;HDR=NO;IMEX=2;DATABASE=C:\','select * from [客戶資料4.dbf]')
--*/
--/* dBase III文件
select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBase III;HDR=NO;IMEX=2;DATABASE=C:\','select * from [客戶資料3.dbf]')
--*/
--/* FoxPro 數(shù)據(jù)庫
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\',
'select * from [aa.DBF]')
--*/
/**************導入DBF文件****************/
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP98\data;
SourceType=DBF',
'select * from customer where country != "USA" order by country')
go
/***************** 導出到DBF ***************/
如果要導出數(shù)據(jù)到已經(jīng)生成結(jié)構(gòu)(即現(xiàn)存的)FOXPRO表中,可以直接用下面的SQL語句
insert into openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\',
'select * from [aa.DBF]')
select * from 表
說明:
SourceDB=c:\ 指定foxpro表所在的文件夾
aa.DBF 指定foxpro表的文件名.
/*************導出到Access********************/
insert into openrowset('Microsoft.Jet.OLEDB.4.0',
'x:\A.mdb';'admin';'',A表) select * from 數(shù)據(jù)庫名..B表
/*************導入Access********************/
insert into B表 selet * from openrowset('Microsoft.Jet.OLEDB.4.0',
'x:\A.mdb';'admin';'',A表)
文件名為參數(shù)
declare @fname varchar(20)
set @fname = 'd:\test.mdb'
exec('SELECT a.* FROM opendatasource(''Microsoft.Jet.OLEDB.4.0'',
'''+@fname+''';''admin'';'''', topics) as a ')
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="f:\northwind.mdb";Jet OLEDB:Database Password=123;User ID=Admin;Password=;')...產(chǎn)品 SQL code
Transact-SQL語句進行導入導出：
1.在SQL SERVER里查詢access數(shù)據(jù):
-- ======================================================
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\DB.mdb";User ID=Admin;Password=')...表名
-------------------------------------------------------------------------------------------------
2.將access導入SQL server
-- ======================================================
在SQL SERVER 里運行:
SELECT *
INTO newtable
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
????? 'Data Source="c:\DB.mdb";User ID=Admin;Password=' )...表名
-------------------------------------------------------------------------------------------------
3.將SQL SERVER表里的數(shù)據(jù)插入到Access表中
-- ======================================================
在SQL SERVER 里運行：
insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
? 'Data Source=" c:\DB.mdb";User ID=Admin;Password=')...表名
(列名1,列名2)
select 列名1,列名2? from? sql表
實例：
insert into? OPENROWSET('Microsoft.Jet.OLEDB.4.0',
?? 'C:\db.mdb';'admin';'', Test)
select id,name from Test
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\trade.mdb'; 'admin'; '', 表名)
SELECT *
FROM sqltablename
-------------------------------------------------------------------------------------------------
二、SQL SERVER 和EXCEL的數(shù)據(jù)導入導出
1、在SQL SERVER里查詢Excel數(shù)據(jù):
-- ======================================================
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
下面是個查詢的示例，它通過用于 Jet 的 OLE DB 提供程序查詢 Excel 電子表格。
SELECT *
FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',
? 'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
-------------------------------------------------------------------------------------------------
2、將Excel的數(shù)據(jù)導入SQL server :
-- ======================================================
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
? 'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
實例:
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
? 'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
-------------------------------------------------------------------------------------------------
3、將SQL SERVER中查詢到的數(shù)據(jù)導成一個Excel文件
-- ======================================================
T-SQL代碼：
EXEC master..xp_cmdshell 'bcp 庫名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""'
參數(shù)：S 是SQL服務器名；U是用戶；P是密碼
說明：還可以導出文本文件等多種格式
實例:EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"'
EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa -Ppassword'
在VB6中應用ADO導出EXCEL文件代碼：
Dim cn? As New ADODB.Connection
cn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"
cn.execute "master..xp_cmdshell 'bcp "SELECT col1, col2 FROM 庫名.dbo.表名" queryout E:\DT.xls -c -Sservername -Usa -Ppassword'"
------------------------------------------------------------------------------------------------
4、在SQL SERVER里往Excel插入數(shù)據(jù):
-- ======================================================
insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...table1 (A1,A2,A3) values (1,2,3)
T-SQL代碼：
INSERT INTO?
OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',?
'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$]?
(bestand, produkt) VALUES (20, 'Test')?
-------------------------------------------------------------------------------------------------
總結(jié)：利用以上語句，我們可以方便地將SQL SERVER、ACCESS和EXCEL電子表格軟件中的數(shù)據(jù)進行轉(zhuǎn)換，為我們提供了極大方便！
EXEC?? master..xp_cmdshell?? 'bcp?? "select OrderID,ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry from Northwind.dbo.orders"?? queryout?? "d:\Oreders.txt" -t"|"??? -c?? -q?? -S"127.0.0.1"?? -U"sa"?? -P""'?
SELECT? *???
? FROM? OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data? Source="xxxx.xls";Extended? Properties="Excel? 8.0";IMEX=1;Persist? Security? Info=False')...[a1$]
SQL code
二、SQL SERVER 和EXCEL的數(shù)據(jù)導入導出
1、在SQL SERVER里查詢Excel數(shù)據(jù):
-- ======================================================
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
下面是個查詢的示例，它通過用于 Jet 的 OLE DB 提供程序查詢 Excel 電子表格。
SELECT *
FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',
? 'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
-------------------------------------------------------------------------------------------------
2、將Excel的數(shù)據(jù)導入SQL server :
-- ======================================================
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
? 'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
實例:
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
? 'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
-------------------------------------------------------------------------------------------------
3、將SQL SERVER中查詢到的數(shù)據(jù)導成一個Excel文件
-- ======================================================
T-SQL代碼：
EXEC master..xp_cmdshell 'bcp 庫名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""'
參數(shù)：S 是SQL服務器名；U是用戶；P是密碼
說明：還可以導出文本文件等多種格式
實例:EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"'
EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa -Ppassword'
在VB6中應用ADO導出EXCEL文件代碼：
Dim cn? As New ADODB.Connection
cn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"
cn.execute "master..xp_cmdshell 'bcp "SELECT col1, col2 FROM 庫名.dbo.表名" queryout E:\DT.xls -c -Sservername -Usa -Ppassword'"
------------------------------------------------------------------------------------------------
4、在SQL SERVER里往Excel插入數(shù)據(jù):
-- ======================================================
insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...table1 (A1,A2,A3) values (1,2,3)
T-SQL代碼：
INSERT INTO?
OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',?
'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$]?
(bestand, produkt) VALUES (20, 'Test')?
引用 18 樓 rovecat 的回復:消息 15281，級別 16，狀態(tài) 1，過程 xp_cmdshell，第 1 行 SQL Server 阻止了對組件 'xp_cmdshell' 的 過程'sys.xp_cmdshell' 的訪問，因為此組件已作為此服務器安全配置的一部分而被關(guān)閉。系統(tǒng)管理員可以通過使用 sp_configure 啟用 'xp_cmdshell'。有關(guān)啟用 'xp_cmdshell' 的詳細信息，請參閱 SQL Server 聯(lián)機叢書中的 "外圍應用配置器"。? 執(zhí)行一下下面的代碼就行了。 SQL code
--開啟xp_cmdshell
--SQL Server blocked access to procedure 'xp_cmdshell'
sp_configure 'show advanced options', 1
go
reconfigure
go
sp_configure 'xp_cmdshell', 1
go
reconfigure
go
--開啟sp_OACreate
--SQL Server blocked access to procedure 'sys.sp_OACreate'
sp_configure 'show advanced options', 1;
go
reconfigure;
go
sp_configure 'ole automation procedures', 1;
go
reconfigure;
go
?
1. 分頁查詢數(shù)據(jù) --查詢到行的結(jié)果 select * from(????? select ID,DocClassName,DocClassDesc, ROW_NUMBER() OVER(order by ID) as row from Sys_DocClass? ) a? where row between 20 and 30 select * from Sys_DocClass 2、增加異常處理 try ... catch SET XACT_ABORT ON? -- 打開try功能 BEGIN TRY????? begin tran????????? insert into Sys_DocClass values(...)? --數(shù)據(jù)表操作語句???? commit tran --提交事務???? print 'commited'? END TRY? BEGIN CATCH????? rollback tran????? --回滾事務????? print 'rolled back'? END CATCH 3、通用表達式CTE，可以簡化嵌套SQL --例：結(jié)合通用表達式進行分頁 WITH DocClasses AS(????? select ID,DocClassName,DocClassDesc, ROW_NUMBER() OVER(order by ID) as row from Sys_DocClass? ) select ID,DocClassName,DocClassDesc from DocClasses where row between 20 and 30
本文來自CSDN博客，轉(zhuǎn)載請標明出處：http://blog.csdn.net/yandong19861103/archive/2009/03/16/3995025.aspx
轉(zhuǎn)載于:https://www.cnblogs.com/68681395/archive/2010/07/15/1778155.html
《新程序員》：云原生和全面數(shù)字化實踐50位技術(shù)專家共同創(chuàng)作，文字、視頻、音頻交互閱讀總結(jié)
以上是生活随笔為你收集整理的经典SQL短小代码收集汇总的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： TCP/IP协议基础
- 下一篇： 多分辨率图像的快速查询