经典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 结束位置

3.获取当前数据库中的所有用户表
select * from sysobjects where xtype='U' and category=0

4.获取某一个表的所有字段
select name from syscolumns where id=object_id('表名')

5.查看与某一个表相关的视图、存储过程、函数
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'

6.查看当前数据库中所有存储过程
select name as 存储过程名称 from sysobjects where xtype='P'

7.查询用户创建的所有数据库
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.查询某一个表的字段和数据类型
select column_name,data_type from information_schema.columns
where table_name = '表名'

9. 按全文匹配方式查询
字段名 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

10、说明：几个高级查询运算词
A： UNION 运算符
UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
B： EXCEPT 运算符
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。
C： INTERSECT 运算符
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。

 

11、使用存储过程关闭所有指定数据库的连接

在查询分析器下创建终止数据库所有接连的存储过程，通过调用该存储过程可以关闭所有使用该数据库的连接操作。
--创建终止使用数据库下所有进程的存储过程，参数为数据库名称
use  master
go
create  proc  KillSpByDbName(@dbname  varchar(20)) 
as 
begin 
declare  @sql  nvarchar(500),@temp varchar(1000)
declare  @spid  int 
set  @sql='declare  getspid  cursor  for   
select  spid  from  sysprocesses  where  dbid=db_id('''+@dbname+''')' 
exec  (@sql) 
open  getspid 
fetch  next  from  getspid  into  @spid 
while  @@fetch_status <>-1 
begin 
  set @temp='kill  '+rtrim(@spid)
  exec(@temp)
fetch  next  from  getspid  into  @spid 
end 
close  getspid 
deallocate  getspid 
end 

--举例使用，关闭数据库下的所有连接操作
Use  master 
Exec  KillSpByDbName  '数据库名称'

 

12、sqlserver、access、excel交互

Transact-SQL语句进行导入导出：

在SQL SERVER里查询access数据:
-- ======================================================
SELECT * 
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:/DB.mdb";User ID=Admin;Password=')...表名
-------------------------------------------------------------------------------------------------
将access导入SQL server 
-- ======================================================
在SQL SERVER 里运行:
SELECT *
INTO newtable
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 
      'Data Source="c:/DB.mdb";User ID=Admin;Password=' )...表名
-------------------------------------------------------------------------------------------------
将SQL SERVER表里的数据插入到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的数据导入导出
在SQL SERVER里查询Excel数据:
-- ======================================================
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')...[Sheet1$]
-------------------------------------------------------------------------------------------------

将Excel的数据导入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')...[Sheet1$]
-------------------------------------------------------------------------------------------------

3、将SQL SERVER中查询到的数据导成一个Excel文件
-- ======================================================
T-SQL代码：
EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:/Temp.xls -c -q -S"servername" -U"sa" -P""'
参数：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插入数据:
-- ======================================================
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')  
-------------------------------------------------------------------------------------------------

总结：利用以上语句，我们可以方便地将SQL SERVER、ACCESS和EXCEL电子表格软件中的数据进行转换，为我们提供了极大方便！
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$] 

--执行以上的代码，如果提示xp_cmdshell没有开启的话，开启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