SQL 【www.9159.com】Server 数据库实用SQL语句_MsSql_脚

作者: www.9159.com  发布:2019-11-23

--查看指定表的外键约束 select * from sysobjects where parent_obj in( select id from sysobjects where name='表名') and xtype='PK' --查看所有表 select * from sysobjects where xtype='PK' --删除列中含数字的 delete news where patindex>0 --删除删去 字段 title值重复的行,且只保留 id 较小的这个 delete news where exists(select 1 from news t where t.title=news.title and t.id 0x01 8.查询某一个表的字段和数据类型 select column_name,data_type from information_schema.columns where table_name = '表名' 9.使用事务 在使用一些对数据库表的临时的SQL语句操作时,可以采用SQL SERVER事务处理,防止对数据操作后发现误操作问题 开始事务 Begin tran Insert Into TableName Values SQL语句操作不正常,则回滚事务。 回滚事务 Rollback tran SQL语句操作正常,则提交事务,数据提交至数据库。 提交事务 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.计算执行SQL语句查询时间 declare @d datetime set @d=getdate() select * from SYS_ColumnProperties select [语句执行花费时间]=datediff 12、说明:几个高级查询运算词 A: UNION 运算符 UNION 运算符通过组合其他两个结果表并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时,不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 B: EXCEPT 运算符 EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 ,不消除重复行。 C: INTERSECT 运算符 INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 ,不消除重复行。

/*创建表*/

【SQL Server 数据库导入导出部分】

1.增加字段
alter table Table_Name add dspcode char(200)

CREATE TABLE [UserInfo](
    [UserID] [uniqueidentifier] not null,
    [UserName] [varchar](100) not null,
    [LoginNumber] [varchar](50) not null,
    [Password] [varchar](50) not null,
    [a] [decimal](18,2) null,
    [b] [decimal](18,2) null,
    [c] as ([a]*[b]),
    [d] as ([a]*[b]) PERSISTED   /*不设置PERSISTED代表该列是一个虚拟列,也就是这个列实际上是不存在的,只是每次要取这列的值时,sql会按照计算列的公式计算一次,再把结果返回给我们。这样会存在一些问题,比如计算会消耗一定的时间,而且不能在该列上创建索引。设置PERSISTED后代表该列是实际存在的列*/

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.删除字段
ALTER TABLE Table_Name DROP COLUMN Column_Name

    );

2、从数据库中导出数据并存到文件中
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.修改字段类型
ALTER TABLE Table_Name ALTER COLUMN Column_Name New_Data_Type

 

3、从文件中导入数据到数据库对应表中
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""'

4.sp_rename 改名
EXEC sp_rename '[dbo].[Table_1].[filedName1]', 'filedName2', 'COLUMN' 
更改当前数据库中用户创建对象(如表、列或用户定义数据类型)的名称。
语法
sp_rename [ @objname = ] 'object_name' ,[ @newname = ] 'new_name',分类信息; [ , [ @objtype =] 'object_type' ]
如:EXEC sp_rename  'newname','PartStock'

/*删除表*/
drop table [UserInfo];

【SQL SERVER 数据库实用SQL语句】

5.sp_help 显示表的一些基本情况
sp_help 'object_name'    
如:EXEC sp_help 'PartStock'

/*添加主键约束*/
ALTER TABLE [UserInfo] ADD  CONSTRAINT [PK_UserInfo]  primary key([UserID]);

1.按姓氏笔画排序:
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as

6.判断某一表PartStock中字段PartVelocity是否存在
if exists (select * from syscolumns where id = object_id('PartStock') and name='PartVelocity')  
    print 'PartVelocity exists'
else
    print 'PartVelocity not exists'
另法:
判断表的存在性:
select

/*添加默认值约束*/
ALTER TABLE [UserInfo] ADD  CONSTRAINT [DF_UserInfo_UserID]  default (newid()) for [UserID];

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

    count(*)

/*添加字段*/
alter table [UserInfo] add [Sex] [varchar](10) null;

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

from sysobjects

/*修改字段*/
alter table [UserInfo] alter column [Sex] [varchar](20) null;

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

where type='U' and name='你的表名'
判断字段的存在性:
select

/*删除字段*/
alter table [UserInfo] drop column [Sex];

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

    count(*)

/*批量插入*/
insert into [UserInfo]([Username],[LoginNumber],[Password],[Sex] select [UserName],[LoginNumber],[Password],[Sex] from [UserInfo];

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

from syscolumns
where id = (select id from sysobjects where type='U' and name='你的表名')
and name = '你要判断的字段名'
一个小例子
--假设要处理的表名为:
tb
--判断要添加列的表中是否有主键
if exists(select 1 from sysobjects where parent_obj=object_id('tb') and xtype='PK')
begin
    print '表中已经有主键,列只能做为普通列添加'
    --添加int类型的列,默认值为0
    alter table tb add 列名 int default 0  
end
else
begin
    print '表中无主键,添加主键列'
    --添加int类型的列,默认值为0
    alter table tb add 列名 int primary key default 0  
end

/*批量更新*/
update [UserInfo1] set [UserInfo1].[Password]=b.[Password],[UserInfo1].[Sex]=b.[Sex] from [UserInfo] b where [UserInfo1].[LoginNumber]=b.[LoginNumber];

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

7.随机读取若干条记录
Access语法:SELECT top 10 * From 表名 ORDER BY Rnd(id)
Sql server:select top n * from 表名 order by newid()
mysql select * From 表名 Order By rand() Limit n

/*创建临时表*/
create table #TempTable(
    [LoginNumber] [varchar](50),
    [Password] [varchar](50)

8.查询某一个表的字段和数据类型
select column_name,data_type from information_schema.columns
where table_name = '表名'

8.说明:日程安排提前五分钟提醒
SQLselect * from 日程安排 where datediff(minute,f开始时间,getdate())>5

    );

9.使用事务
在使用一些对数据库表的临时的SQL语句操作时,可以采用SQL SERVER事务处理,防止对数据操作后发现误操作问题
开始事务
Begin tran
  Insert Into TableName Values(…)
SQL语句操作不正常,则回滚事务。
回滚事务
Rollback tran
SQL语句操作正常,则提交事务,数据提交至数据库。
提交事务
Commit tran

9.前10条记录
select top 10 * form. table1 where 范围

/*自增列*/
create table [TestTable](
  [ID] [int] identity(1,1) not null

  1. 按全文匹配方式查询
    字段名 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.包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表
(select a from tableA ) except (select a from tableB) except (select a from tableC)

  );

11.计算执行SQL语句查询时间
declare @d datetime
set @d=getdate()
select * from SYS_ColumnProperties select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

11.说明:随机取出10条数据
select top 10 * from tablename order by newid()

/* 获取所有用户名

12、说明:几个高级查询运算词
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),不消除重复行。

12.列出数据库里所有的表名
select name from sysobjects where type=U

 * islogin='1'表示帐户
 * islogin='0'表示角色
 * status='2'表示用户帐户
 * status='0'表示系统帐户
 */
select [name] from sysusers where status='2' and islogin='1'

SQL code

13.列出表里的所有的字段名
select name from syscolumns where id=object_id(TableName)

/*获取所有数据库名*/
select [name] from master..sysdatabases order by [name]

/******* 导出到excel
EXEC master..xp_cmdshell 'bcp SettleDB.dbo.shanghu out c:temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P""'

14.说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select中的case
select
    type,
    sum(case vender
            when A then pcs
            else 0
        end),
    sum(case vender
            when C then pcs
            else 0

/* 获取所有表名
 * xtype='U'表示所有用户表
 * xtype='S'表示所有系统表
 */
select [name] from 数据库名..sysobjects where [xtype]='U' order by [name]

/*********** 导入Excel
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

        end),
    sum(case vender
            when B then pcs
            else 0

/*获取所有字段名*/
select [name] from syscolumns where id=object_id('表名')

/*动态文件名
declare @fn varchar(20),@s varchar(1000)
set @fn = 'c:test.xls'
set @s ='''Microsoft.Jet.OLEDB.4.0'',
''Data Source="'+@fn+'%22;User ID=Admin;Password=;Extended properties=Excel 5.0'''
set @s = 'SELECT * FROM OpenDataSource ('+@s+')...sheet1$'...sheet1%24')
exec(@s)
*/

        end)
FROM tablename
group by type

/*获取数据库所有类型*/

SELECT cast(cast(科目编号 as numeric(10,2)) as nvarchar(255))+' ' 转换后的别名
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

15.说明:初始化表table1
TRUNCATE TABLE table1

select [name] from systypes 

/********************** 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

16.说明:几个高级查询运算词
A:UNION 运算符
UNION 运算符通过组合其他两个结果表(例如TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。
ALLUNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就
是来自 TABLE2。

 

/** 导入文本文件
EXEC master..xp_cmdshell 'bcp dbname..tablename in c:DT.txt -c -Sservername -Usa -Ppassword'

B:EXCEPT 运算符
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2中的行并消除所有重复行而派生出一个结果表。当 ALL
EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。

/*获取指定表中代表主键的字段名*/
select name from syscolumns a where exists(select 1 from sysindexkeys where id=a.id and colid=a.colid) and id=object_id('表名');

/** 导出文本文件
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'

CINTERSECT 运算符
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2中都有的行并消除所有重复行而派生出一个结果表。当 ALL
INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。
注:使用运算词的几个查询结果行必须是一致的。

 

导出到TXT文本,用逗号分开
exec master..xp_cmdshell 'bcp "库名..表名" out "d:tt.txt" -c -t ,-U sa -P password'

17.说明:在线视图查询(表名1:a )
select * from (SELECT a,b,c FROM a) T where t.a > 1;

/*获取表主键*/

BULK INSERT 库名..表名
FROM 'c:test.txt'
WITH (
FIELDTERMINATOR = ';',
ROWTERMINATOR = 'n'
)

18.说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 数值1 and 数值2

select o.name as 表名,c.name as 字段名,k.colid as 字段序号,k.keyno as 索引顺序,t.name as 类型 
from sysindexes i 
join sysindexkeys k on i.id = k.id and i.indid = k.indid 
join sysobjects o on i.id = o.id 
join syscolumns c on i.id=c.id and k.colid = c.colid 
join systypes t on c.xusertype=t.xusertype 
where o.xtype = 'U' and o.name='要查询的表名' 
and exists(select 1 from sysobjects where xtype = 'PK' and parent_obj=i.id and name = i.name) 

--/* dBase IV文件
select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBase IV;HDR=NO;IMEX=2;DATABASE=C:','select * from [客户资料4.dbf]')
--*/

19.说明:in 的使用方法
select * from table1 where a [not] in ('值1','值2','值4','值6')

order by o.name,k.colid

--/* dBase III文件
select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBase III;HDR=NO;IMEX=2;DATABASE=C:','select * from [客户资料3.dbf]')
--*/

20.说明:两张关联表,删除主表中已经在副表中没有的信息
delete from table1
where not exists (select * from table2 where table1.field1=table2.field1)

 

--/* FoxPro 数据库
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:',
'select * from [aa.DBF]')
--*/

21.说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用)
法一:select * into b from a where 1<>1
法二:select top 0 * into b from a

/*获取表中字段名和类型*/

/**************导入DBF文件****************/
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:VFP98data;
SourceType=DBF',
'select * from customer where country != "USA" order by country')
go
/***************** 导出到DBF ***************/
如果要导出数据到已经生成结构(即现存的)FOXPRO表中,可以直接用下面的SQL语句

22.说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用)
insert into b(a, b, c)
select d,e,f from b;

select c.name as 字段名,t.name as 类型 
from sysindexes i 
join sysobjects o on i.id = o.id 
join syscolumns c on i.id=c.id 
join systypes t on c.xusertype=t.xusertype 
where o.xtype = 'U' and o.name='要查询的表名' 
and exists(select 1 from sysobjects where xtype = 'PK' and parent_obj=i.id and name = i.name) 
order by o.name

insert into openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:',
'select * from [aa.DBF]')
select * from 表

23.说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
insert into b(a, b, c)
select d,e,f from b in '具体数据库' where 条件
例子:..from b in "&Server.MapPath(".")&"data.mdb" &" where..

/*获取表中字段名、类型、默认值*/

说明:
SourceDB=c: 指定foxpro表所在的文件夹
aa.DBF 指定foxpro表的文件名.

24.创建数据库
CREATE DATABASE database-name

select c.name as 字段名,t.name as 类型,e.text as 默认值
from sysindexes i 
join sysobjects o on i.id = o.id 
join syscolumns c on i.id=c.id 
join systypes t on c.xusertype=t.xusertype 
left join syscomments e on e.id=c.cdefault
where o.xtype = 'U' and o.name='DBConfig' 
and exists(select 1 from sysobjects where xtype = 'PK' and parent_obj=i.id and name = i.name) 
order by o.name
select * from syscomments

/*************导出到Access********************/
insert into openrowset('Microsoft.Jet.OLEDB.4.0',
'x:A.mdb';'admin';'',A表) select * from 数据库名..B表

25.说明:删除数据库
drop database dbname

 

/*************导入Access********************/
insert into B表 selet * from openrowset('Microsoft.Jet.OLEDB.4.0',
'x:A.mdb';'admin';'',A表)

26.说明:备份sql server
--创建
备份数据的 device
USE master
EXEC sp_addumpdevice disk, testBack, c:mssql7backupMyNwind_1.dat
--开始 备份
BACKUP DATABASE pubs TO testBack

/*获取表所有列*/
SELECT 

文件名为参数
declare @fname varchar(20)
set @fname = 'd:test.mdb'
exec('SELECT a.* FROM opendatasource(''Microsoft.Jet.OLEDB.4.0'',
'''+@fname+''';''admin'';'''', topics) as a ')

27.说明:创建新表
create table tabname
(
    col1 type1 [not null] [primary key],
    col2 type2 [not null],
    ..
)
根据已有的表创建新表:
A:create table tab_new like tab_old (使用旧表创建新表)
B:create table tab_new as select col1,col2… from tab_old definition only

    表名=case when a.colorder=1 then d.name else '' end, 

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="f:northwind.mdb";Jet OLEDB:Database Password=123;User ID=Admin;Password=;')...产品 SQL code
Transact-SQL语句进行导入导出:

28.说明:
删除新表:
drop table tabname

    字段名=a.name, 

1.在SQL SERVER里查询access数据:
-- ======================================================
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

29.说明:
增加一个列:
Alter table tabname add column type
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。

    标识=case when COLUMNPROPERTY(a.id,a.name,'IsIdentity')=1 then '√' else '' end, 

'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',

30.说明:
添加主键:
Alter table tabname add primary key(col)
删除主键:
Alter table tabname drop primary key(col)

    主键=case when exists(SELECT 1 FROM sysobjects where xtype= 'PK' and name in ( 
SELECT name FROM sysindexes WHERE indid in( 
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid 
))) then '√' else '' end, 

      'Data Source="c:DB.mdb";User ID=Admin;Password=' )...表名

3.将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 *

31.说明:
创建索引:
create [unique] index idxname on tabname(col….)
删除索引:
drop index idxname
注:索引是不可更改的,想更改必须删除重新建。

    类型=b.name, 

FROM sqltablename

二、SQL SERVER 和EXCEL的数据导入导出
1、在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:Financeaccount.xls";User

32.说明:
创建视图:
create view viewname
as
    具体的SQL语句
删除视图:
drop view viewname

    占用字节数=a.length, 

ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

2、将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:Financeaccount.xls";User

来自为知笔记(Wiz)

    长度=COLUMNPROPERTY(a.id,a.name, 'PRECISION'), 

ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

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

    小数位数=isnull(COLUMNPROPERTY(a.id,a.name, 'Scale'),0), 

库名.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:traininginventur.xls')...[Filiale1$] 

    允许空=case when a.isnullable=1 then '√'else '' end, 

(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$]

SQL code

二、SQL SERVER 和EXCEL的数据导入导出
1、在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:Financeaccount.xls";User

    默认值=isnull(e.text, ''), 

ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

2、将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:Financeaccount.xls";User

    字段说明=isnull(g.[value], '') 
FROM syscolumns a 
    left join systypes b on a.xtype=b.xusertype 
    inner join sysobjects d on a.id=d.id and d.xtype= 'U' and d.name <> 'dtproperties' and d.name = '要查询的表名'
    left join syscomments e on a.cdefault=e.id 
    left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id and g.name='MS_Description' 
order by a.id,a.colorder 

ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

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

/*获取表自增列名称*/
select COLUMN_NAME as 标识 
FROM INFORMATION_SCHEMA.columns 
where TABLE_NAME='要查询的表' and COLUMNPROPERTY(OBJECT_ID('要查询的表'),COLUMN_NAME,'IsIdentity')=1

库名.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:traininginventur.xls')...[Filiale1$] 
(bestand, produkt) VALUES (20, 'Test') 

引用 18 楼 rovecat 的回复:消息 15281,级别 16,状态 1,过程 xp_cmdshell,第 1 行 SQL Server 阻止了对组件 'xp_cmdshell' 的 过程'sys.xp_cmdshell' 的访问,因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用 'xp_cmdshell'。有关启用 'xp_cmdshell' 的详细信息,请参阅 SQL Server 联机丛书中的 "外围应用配置器"。  执行一下下面的代码就行了。 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. 分页查询数据 --查询到行的结果 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(...)  --数据表操作语句     commit tran --提交事务     print 'commited'  END TRY  BEGIN CATCH      rollback tran      --回滚事务      print 'rolled back'  END CATCH 3、通用表达式CTE,可以简化嵌套SQL --例:结合通用表达式进行分页 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博客,转载请标明出处:

/*获取表的字段默认值*/
select b.text as 字段默认值 
from syscolumns a left join syscomments b on a.cdefault = b.id 
where a.id = object_id('要查询的表') and a.name = '字段' 

 

/*按姓氏笔画排序*/
Select * from TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as 

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

/*查询用户创建的所有数据库*/
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

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

/*计算执行SQL语句查询时间*/
declare @d datetime 
set @d=getdate() 
select * from SYS_ColumnProperties select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

/*几个高级查询运算词*/

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),不消除重复行。

 

/*记录不存在就直接插入新记录,记录存在就更新*/ 

if exists (select 1 from [tb] where [name]='aa')
    begin
        update [tb] set [name]='bb' where [name]='aa'
    end
else
    begin
        insert into [tb]([name]) select 'aa'
   end 

 

/*判断指定表中是否存在指定的列名*/

if(not exists(select * from syscolumns where name = 'IsUpdate' and id in 

(select id from sysobjects where id = object_id(N'[TestTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1))) 

    alter table [TestTable] add [IsUpdate] [int];

 

本文由9159.com发布于www.9159.com,转载请注明出处:SQL 【www.9159.com】Server 数据库实用SQL语句_MsSql_脚

关键词: