|
本帖最后由 一年你要努力了 于 2019-6-19 17:21 编辑
一、SQL分类
DDL—数据定义语言(CREATE,ALTER,DROP,DECLARE)
DML—数据操纵语言(SELECT,DELETE,UPDATE,INSERT)
DCL—数据控制语言(GRANT,REVOKE,COMMIT,ROLLBACK)
二、基础语句
2.1、说明:创建数据库
2.2、说明:删除数据库
2.3、说明:备份sql server
- --- 创建 备份
- USE master
- EXEC sp_addumpdevice 'disk', 'testBack', 'c:mssql7backupMyNwind_1.dat'
- --- 开始 备份
- BACKUP DATABASE pubs TO testBack
复制代码
2.4、说明:创建新表
- 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
复制代码
2.5、说明:删除新表
2.6、说明:增加一个列
- Alter table tabname add column col type
复制代码
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。
2.7、说明:添加/删除主键
- Alter table tabname add primary key(col)
- Alter table tabname drop primary key(col)
复制代码
2.8、说明:创建/删除索引
- create [unique] index idxname on tabname(col….)
- drop index idxname
复制代码
注:索引是不可更改的,想更改必须删除重新建。
2.9、说明:创建/删除视图
- create view viewname as select statement
- drop view viewname
复制代码
2.10、说明:基本sql语句选择
- 查询:select * from table1 where 范围
- 插入:insert into table1(field1,field2) s(1,2)
- 删除:delete from table1 where 范围
- 更新:update table1 set field1=1 where 范围
- 查找:select * from table1 where field1 like ’%1%’ ---like的语法
- 排序:select * from table1 order by field1,field2 [desc]
- 总数:select count * as totalcount from table1
- 求和:select sum(field1) as sum from table1
- 平均:select avg(field1) as avg from table1
- 最大:select max(field1) as max from table1
- 最小:select min(field1) as min from table1
复制代码
2.11、说明:高级查询运算
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),不消除重复行。
注:使用运算词的几个查询结果行必须是一致的。
2.12、说明:使用外连接
A、left outer join: 左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
B:right outer join: 右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。
C:full outer join: 全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。
补充:
1)、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用)
- 法一:select * into b from a where 1<>1
- 法二:select top 0 * into b from a
复制代码
2)、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用)
- insert into b(a, b, c) select d,e,f from b;
复制代码
3)、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
- insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
- 例子:..from b in '"&Server.MapPath(".")&"data.mdb" &"' where..
复制代码
4)、说明:子查询(表名1:a 表名2:b)
- select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)
复制代码
5)、说明:显示文章、提交人和最后回复时间
- select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
复制代码
6)、说明:外连接查询(表名1:a 表名2:b)
- select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
复制代码
7)、说明:在线视图查询(表名1:a )
- select * from (SELECT a,b,c FROM a) T where t.a > 1;
复制代码
8)、说明: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
复制代码
9)、说明:in 的使用方法
- select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)
复制代码
10)、说明:两张关联表,删除主表中已经在副表中没有的信息
- delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
复制代码
11)、说明:四表联查问题
- select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
复制代码
12)、说明:日程安排提前五分钟提醒
- SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5
复制代码
13)、说明:一条sql 语句搞定数据库分页
- select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段
复制代码
14)、说明:前10条记录
- select top 10 * form table1 where 范围
复制代码
15)、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
- select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
复制代码
16)、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表
- (select a from tableA ) except (select a from tableB) except (select a from tableC)
复制代码
17)、说明:随机取出10条数据
- select top 10 * from tablename order by newid()
复制代码
18)、说明:随机选择记录
19)、说明:删除重复记录
- Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
复制代码
20)、说明:列出数据库里所有的表名
- select name from sysobjects where type='U'
复制代码
21)、说明:列出表里的所有的
- select name from syscolumns where id=object_id('TableName')
复制代码
22)、说明:列示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 end),
- sum(case vender when 'B' then pcs else 0 end)
- FROM tablename group by type
复制代码
显示结果:
- type vender pcs
- 电脑 A 1
- 电脑 A 1
- 光盘 B 2
- 光盘 A 2
- 手机 B 3
- 手机 C 3
复制代码
23)、说明:初始化表table1
24)、说明:选择从10到15的记录
- select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc
复制代码
三、sql技巧
3.1、如何删除一个表中重复的记录?
- create table a_dist(id int,name varchar(20))
- insert into a_dist values(1,'abc')
- insert into a_dist values(1,'abc')
- insert into a_dist values(1,'abc')
- insert into a_dist values(1,'abc')
- exec up_distinct 'a_dist','id'
- select * from a_dist
复制代码
存储过程如下:
- create procedure up_distinct(@t_name varchar(30),@f_key varchar(30))
- --f_key表示是分组字段﹐即主键字段
-
- as
- begin
- declare @max integer,@id varchar(30) ,@sql varchar(7999) ,@type integer
- select @sql = 'declare cur_rows cursor for select '+@f_key+' ,count(*) from ' +@t_name +' group by ' +@f_key +' having count(*) > 1'
- exec(@sql)
-
- open cur_rows
- fetch cur_rows into @id,@max
- while @@fetch_status=0
- begin
- select @max = @max -1
- set rowcount @max
- select @type = xtype from syscolumns where id=object_id(@t_name) and name=@f_key
- if @type=56
- select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+ @id
- if @type=167
- select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+''''+ @id +''''
- exec(@sql)
-
- fetch cur_rows into @id,@max
- end
- close cur_rows
- deallocate cur_rows
-
- set rowcount 0
- end
-
- select * from systypes
- select * from syscolumns where id = object_id('a_dist')
复制代码
3.2、查询数据的最大排序问题(只能用一条语句写)
- CREATE TABLE hard (qu char (11) ,co char (11) ,je numeric(3, 0))
- insert into hard values ('A','1',3)
- insert into hard values ('A','2',4)
- insert into hard values ('A','4',2)
- insert into hard values ('A','6',9)
- insert into hard values ('B','1',4)
- insert into hard values ('B','2',5)
- insert into hard values ('B','3',6)
- insert into hard values ('C','3',4)
- insert into hard values ('C','6',7)
- insert into hard values ('C','2',3)
复制代码
要求查询出来的结果如下:
- qu co je
- ----------- ----------- -----
- A 6 9
- A 2 4
- B 3 6
- B 2 5
- C 6 7
- C 3 4
- 就是要按qu分组,每组中取je最大的前2位,且只能用一句sql语句。
复制代码
select * from hard a where je in (select top 2 je from hard b where a.qu=b.qu order by je)
3.3、求删除重复记录的sql语句
怎样把具有相同字段的记录删除,只留下一条。
例如,表test里有id,name字段
如果有name相同的记录 只留下一条,其余的删除。
name的内容不定,相同的记录数不定。
==============================
一个完整的解决方案:
- 1)将重复的记录记入temp1表:
- select [标志字段id],count(*) into temp1 from [表名]
- group by [标志字段id]
- having count(*)>1
- 2)将不重复的记录记入temp1表:
- insert temp1 select [标志字段id],count(*) from [表名] group by [标志字段id] having count(*)=1
- 3)作一个包含所有不重复记录的表:
- select * into temp2 from [表名] where 标志字段id in(select 标志字段id from temp1)
- 4)删除重复表:
- delete [表名]
- 5)恢复表:
- insert [表名] select * from temp2
- 6)删除临时表:
- drop table temp1
- drop table temp2
复制代码
3.4、行列转换--普通
- 假设有张学生成绩表(CJ)如下
- Name Subject Result
- 张三 语文 80
- 张三 数学 90
- 张三 物理 85
- 李四 语文 85
- 李四 数学 92
- 李四 物理 82
- 想变成
- 姓名 语文 数学 物理
- 张三 80 90 85
- 李四 85 92 82
复制代码- declare @sql varchar(4000)
- set @sql = 'select Name'
- select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']'
- from (select distinct Subject from CJ) as a
- select @sql = @sql+' from test group by name'
- exec(@sql)
复制代码
3.5、行列转换--合并
- 有表A,
- id pid
- 1
- 2
- 3
- 1
- 2
- 1
- 如何化成表B:
- id pid
- 1,2,3
- 1,2
- 1
复制代码
创建一个合并的函数
- create function fmerg(@id int)
- returns varchar(8000)
- as
- begin
- declare @str varchar(8000)
- set @str=''
- select @str=@str+','+cast(pid as varchar) from 表A where id=@id
- set @str=right(@str,len(@str)-1)
- return(@str)
- End
- go
复制代码
调用自定义函数得到结果
- select distinct id,dbo.fmerg(id) from 表A
复制代码
3.6、如何取得一个数据表的所有列名
先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再SYSCOLUMN表中取得该数据表的所有列名。
SQL语句如下:
- declare @objid int,@objname char(40)
- set @objname = 'tablename'
- select @objid = id from sysobjects where id = object_id(@objname)
- select 'Column_name' = name from syscolumns where id = @objid order by colid
复制代码
或
- SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='users'
复制代码
3.7、通过SQL语句来更改用户的密码
- 修改别人的,需要sysadmin role
- EXEC sp_password NULL, 'newpassword', 'User'
- 如果帐号为SA执行
- EXEC sp_password NULL, 'newpassword', sa
复制代码
3.8、怎么判断出一个表的哪些字段不允许为空
- select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE='NO' and TABLE_NAME=tablename
复制代码
3.9、如何在数据库里找到含有相同字段的表
a. 查已知列名的情况
- SELECT b.name as TableName,a.name as columnname
- From syscolumns a INNER JOIN sysobjects b
- ON a.id=b.id
- AND b.type='U'
- AND a.name='字段名'
复制代码
b.未知列名查所有在不同表出现过的列名
- Select o.name As tablename,s1.name As columnname
- From syscolumns s1, sysobjects o
- Where s1.id = o.id
- And o.type = 'U'
- And Exists (
- Select 1 From syscolumns s2
- Where s1.name = s2.name
- And s1.id <> s2.id
- )
复制代码
3.10、查询第xxx行数据
假设id是主键:
- select * from (select top xxx * from yourtable) aa where not exists(select 1 from (select top xxx-1 * from yourtable) bb where aa.id=bb.id)
复制代码
如果使用游标也是可以的
- fetch absolute [number] from [cursor_name]
复制代码
注:行数为绝对行数
3.11、日期计算
- a. 一个月的第一天
- SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
- b. 本周的星期一
- SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
- c. 一年的第一天
- SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
- d. 季度的第一天
- SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
- e. 上个月的最后一天
- SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
- f. 去年的最后一天
- SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
- g. 本月的最后一天
- SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
- h. 本月的第一个星期一
- select DATEADD(wk, DATEDIFF(wk,0,
- dateadd(dd,6-datepart(day,getdate()),getdate())
- ), 0)
- i. 本年的最后一天
- SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))。
复制代码
3.12、获取表结构[把 'sysobjects' 替换 成 'tablename' 即可]
- SELECT CASE IsNull(I.name, '')
- When '' Then ''
- Else '*'
- End as IsPK,
- Object_Name(A.id) as t_name,
- A.name as c_name,
- IsNull(SubString(M.text, 1, 254), '') as pbc_init,
- T.name as F_DataType,
- CASE IsNull(TYPEPROPERTY(T.name, 'Scale'), '')
- WHEN '' Then Cast(A.prec as varchar)
- ELSE Cast(A.prec as varchar) + ',' + Cast(A.scale as varchar)
- END as F_Scale,
- A.isnullable as F_isNullAble
- FROM Syscolumns as A
- JOIN Systypes as T
- ON (A.xType = T.xUserType AND A.Id = Object_id('sysobjects') )
- LEFT JOIN ( SysIndexes as I
- JOIN Syscolumns as A1
- ON ( I.id = A1.id and A1.id = object_id('sysobjects') and (I.status & 0x800) = 0x800 AND A1.colid <= I.keycnt) )
- ON ( A.id = I.id AND A.name = index_col('sysobjects', I.indid, A1.colid) )
- LEFT JOIN SysComments as M
- ON ( M.id = A.cdefault and ObjectProperty(A.cdefault, 'IsConstraint') = 1 )
- ORDER BY A.Colid ASC
复制代码
3.13、提取数据库内所有表的字段详细说明的SQL语句
- SELECT
- (case when a.colorder=1 then d.name else '' end) N'表名',
- a.colorder N'字段序号',
- a.name N'字段名',
- (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else ''
- end) N'标识',
- (case when (SELECT count(*)
- FROM sysobjects
- WHERE (name in
- (SELECT name
- FROM sysindexes
- WHERE (id = a.id) AND (indid in
- (SELECT indid
- FROM sysindexkeys
- WHERE (id = a.id) AND (colid in
- (SELECT colid
- FROM syscolumns
- WHERE (id = a.id) AND (name = a.name))))))) AND
- (xtype = 'PK'))>0 then '√' else '' end) N'主键',
- b.name N'类型',
- a.length N'占用字节数',
- COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',
- isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',
- (case when a.isnullable=1 then '√'else '' end) N'允许空',
- isnull(e.text,'') N'默认值',
- isnull(g.[value],'') AS N'字段说明'
- 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'
- left join syscomments e
- on a.cdefault=e.id
- left join sysproperties g
- on a.id=g.id AND a.colid = g.smallid
- order by object_name(a.id),a.colorder
复制代码
3.14、快速获取表test的记录总数[对大容量表非常有效]
快速获取表test的记录总数:
- select rows from sysindexes where id = object_id('test') and indid in (0,1)
- update 2 set KHXH=(ID+1)\2 2行递增编号
- update [23] set id1 = 'No.'+right('00000000'+id,6) where id not like 'No%' //递增
- update [23] set id1= 'No.'+right('00000000'+replace(id1,'No.',''),6) //补位递增
- delete from [1] where (id%2)=1
- 奇数
- 替换表名字段
- update [1] set domurl = replace(domurl,'Upload/Imgswf/','Upload/Photo/') where domurl like '%Upload/Imgswf/%'
- 截位
- SELECT LEFT(表名, 5)
- 截位
- SELECT LEFT(表名, 5)
复制代码
四、(MS SQL Server)SQL语句导入导出大全
4.1、导出到excel
- EXEC master..xp_cmdshell 'bcp SettleDB.dbo.shanghu out c:\temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P""'
复制代码
4.2、 导入Excel
- SELECT *
- FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
- 'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
- 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
- select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=c:\Book1.xls',Sheet1$)
-
- HDR=YES;Excel第一行当成标题行
- HDR=NO;第一行不当成标题行
复制代码
4.3、 导入文本文件
- EXEC master..xp_cmdshell 'bcp "dbname..tablename" in c:\DT.txt -c -Sservername -Usa -Ppassword'
复制代码
4.4、 导出文本文件
- 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 数据库
- select * from openrowset('MSDASQL',
- 'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\',
- 'select * from [aa.DBF]')
- --*/
复制代码
4.5、导入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
复制代码
4.6、 导出到DBF 文件
如果要导出数据到已经生成结构(即现存的)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表的文件名.
4.7、导出到Access
- insert into openrowset('Microsoft.Jet.OLEDB.4.0',
- 'x:\A.mdb';'admin';'',A表) select * from 数据库名..B表
复制代码
4.8、导入Access
- insert into B表 selet * from openrowset('Microsoft.Jet.OLEDB.4.0',
- 'x:\A.mdb';'admin';'',A表)
复制代码
4.9、 导入 xml文件
- 1 DECLARE @idoc int
- 2 DECLARE @doc varchar(1000)
- 3
- 4 --sample XML document
- 5 SET @doc ='
- 6 <root>
- 7 <Customer cid= "C1" name="Janine" city="Issaquah">
- 8 <Order oid="O1" date="1/20/1996" amount="3.5" />
- 9 <Order oid="O2" date="4/30/1997" amount="13.4">Customer was very satisfied
- 10 </Order>
- 11 </Customer>
- 12 <Customer cid="C2" name="Ursula" city="Oelde" >
- 13 <Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue
- 14 white red">
- 15 <Urgency>Important</Urgency>
- 16 Happy Customer.
- 17 </Order>
- 18 <Order oid="O4" date="1/20/1996" amount="10000"/>
- 19 </Customer>
- 20 </root>
- 21 '
- 22
- 23 -- Create an internal representation of the XML document.
- 24 EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
- 25
- 26
- 27 -- Execute a SELECT statement using OPENXML rowset provider.
- 28 SELECT *
- 29 FROM OPENXML (@idoc, '/root/Customer/Order', 1)
- 30 WITH (oid char(5),
- 31 amount float,
- 32 comment ntext 'text()')
- 33
- 34 EXEC sp_xml_removedocument @idoc
复制代码
4.10、导整个数据库
用bcp实现的存储过程
实现数据导入/导出的存储过程
根据不同的参数,可以实现导入/导出整个数据库/单个表
调用示例:
4.10.1、导出调用示例
---导出单个表
- exec file2table 'zj','','','xzkh_sa..地区资料','c:\zj.txt',1
复制代码
----导出整个数据库
- exec file2table 'zj','','','xzkh_sa','C:\docman',1
复制代码
4.10.2、导入调用示例
----导入单个表
- exec file2table 'zj','','','xzkh_sa..地区资料','c:\zj.txt',0
复制代码
----导入整个数据库
- exec file2table 'zj','','','xzkh_sa','C:\docman',0
复制代码- 1 if exists(select 1 from sysobjects where name='File2Table' and objectproperty(id,'IsProcedure')=1)
- 2 drop procedure File2Table
- 3 go
- 4 create procedure File2Table
- 5 @servername varchar(200) --服务器名
- 6 ,@username varchar(200) --用户名,如果用NT验证方式,则为空''
- 7 ,@password varchar(200) --密码
- 8 ,@tbname varchar(500) --数据库.dbo.表名,如果不指定:.dbo.表名,则导出数据库的所有用户表
- 9 ,@filename varchar(1000) --导入/导出路径/文件名,如果@tbname参数指明是导出整个数据库,则这个参数是文件存放路径,文件名自动用表名.txt
- 10 ,@isout bit --1为导出,0为导入
- 11 as
- 12 declare @sql varchar(8000)
- 13 if @tbname like '%.%.%' --如果指定了表名,则直接导出单个表
- 14 begin
- 15 set @sql='bcp '+@tbname
- 16 +case when @isout=1 then ' out ' else ' in ' end
- 17 +' "'+@filename+'" /w'
- 18 +' /S '+@servername
- 19 +case when isnull(@username,'')='' then '' else ' /U '+@username end
- 20 +' /P '+isnull(@password,'')
- 21
- 22 exec master..xp_cmdshell @sql
- 23
- 24 end
- 25 else
- 26 begin --导出整个数据库,定义游标,取出所有的用户表
- 27
- 28 declare @m_tbname varchar(250)
- 29 if right(@filename,1)<>'\' set @filename=@filename+'\'
- 30 set @m_tbname='declare #tb cursor for select name from '+@tbname+'..sysobjects where xtype=''U'''
- 31 exec(@m_tbname)
- 32
- 33 open #tb
- 34
- 35 fetch next from #tb into @m_tbname
- 36 while @@fetch_status=0
- 37 begin
- 38 set @sql='bcp '+@tbname+'..'+@m_tbname
- 39 +case when @isout=1 then ' out ' else ' in ' end
- 40 +' "'+@filename+@m_tbname+'.txt " /w'
- 41 +' /S '+@servername
- 42 +case when isnull(@username,'')='' then '' else ' /U '+@username end
- 43 +' /P '+isnull(@password,'')
- 44
- 45 exec master..xp_cmdshell @sql
- 46
- 47 fetch next from #tb into @m_tbname
- 48 end
- 49
- 50 close #tb
- 51 deallocate #tb
- 52 end
- 53 go
复制代码
4.11、Excel导到Txt
想用
select * into opendatasource(...) from opendatasource(...)
实现将一个Excel文件内容导入到一个文本文件
假设Excel中有两列,第一列为姓名,第二列为很行帐号(16位)
且银行帐号导出到文本文件后分两部分,前8位和后8位分开。
如果要用你上面的语句插入的话,文本文件必须存在,而且有一行:姓名,银行账号1,银行账号2
然后就可以用下面的语句进行插入
注意文件名和目录根据你的实际情况进行修改.
- insert into
- opendatasource('MICROSOFT.JET.OLEDB.4.0'
- ,'Text;HDR=Yes;DATABASE=C:\'
- )...[aa#txt]
- --,aa#txt)
- --*/
- select 姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8)
- from
- opendatasource('MICROSOFT.JET.OLEDB.4.0'
- ,'Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:\a.xls'
- --,Sheet1$)
- )...[Sheet1$]
复制代码
如果你想直接插入并生成文本文件,就要用bcp
- declare @sql varchar(8000),@tbname varchar(50)
复制代码
首先将excel表内容导入到一个全局临时表
- select @tbname='[##temp'+cast(newid() as varchar(40))+']'
- ,@sql='select 姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8)
- into '+@tbname+' from
- opendatasource(''MICROSOFT.JET.OLEDB.4.0''
- ,''Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:\a.xls''
- )...[Sheet1$]'
- exec(@sql)
复制代码
然后用bcp从全局临时表导出到文本文件
- set @sql='bcp "'+@tbname+'" out "c:\aa.txt" /S"(local)" /P"" /c'
- exec master..xp_cmdshell @sql
复制代码
删除临时表
- exec('drop table '+@tbname)
复制代码
4.12、用bcp将文件导入导出到数据库的存储过程:
bcp-二进制文件的导入导出
支持image,text,ntext字段的导入/导出
image适合于二进制文件;text,ntext适合于文本数据文件
注意:导入时,将覆盖满足条件的所有行
导出时,将把所有满足条件的行也出到指定文件中
此存储过程仅用bcp实现
/*--调用示例
--数据导出
- exec p_binaryIO 'zj','','','acc_演示数据..tb','img','c:\zj1.dat'
复制代码
--数据导出
- exec p_binaryIO 'zj','','','acc_演示数据..tb','img','c:\zj1.dat','',0
复制代码
--*/
生成数据处理应答文件
- set @m_tbname='[##temp'+cast(newid() as varchar(40))+']'
- set @sql='select * into '+@m_tbname+' from(
- select null as 类型
- union all select 0 as 前缀
- union all select '+@fsize+' as 长度
- union all select null as 结束
- union all select null as 格式
- ) a'
- exec(@sql)
- select @fname_in=@fname+'_temp'
- ,@sql='bcp "'+@m_tbname+'" out "'+@fname_in
- +'" /S"'+@servename
- +case when isnull(@username,'')='' then ''
- else '" /U"'+@username end
- +'" /P"'+isnull(@password,'')+'" /c'
- exec master..xp_cmdshell @sql
复制代码
删除临时表
- set @sql='drop table '+@m_tbname
- exec(@sql)
- if @isout=1
- begin
- set @sql='bcp "select top 1 '+@fdname+' from '
- +@tbname+case isnull(@tj,'') when '' then ''
- else ' where '+@tj end
- +'" queryout "'+@fname
- +'" /S"'+@servename
- +case when isnull(@username,'')='' then ''
- else '" /U"'+@username end
- +'" /P"'+isnull(@password,'')
- +'" /i"'+@fname_in+'"'
- exec master..xp_cmdshell @sql
- end
- else
- begin
复制代码
为数据导入准备临时表
- set @sql='select top 0 '+@fdname+' into '
- +@m_tbname+' from ' +@tbname
- exec(@sql)
复制代码
将数据导入到临时表
- set @sql='bcp "'+@m_tbname+'" in "'+@fname
- +'" /S"'+@servename
- +case when isnull(@username,'')='' then ''
- else '" /U"'+@username end
- +'" /P"'+isnull(@password,'')
- +'" /i"'+@fname_in+'"'
- exec master..xp_cmdshell @sql
复制代码
将数据导入到正式表中
- set @sql='update '+@tbname
- +' set '+@fdname+'=b.'+@fdname
- +' from '+@tbname+' a,'
- +@m_tbname+' b'
- +case isnull(@tj,'') when '' then ''
- else ' where '+@tj end
- exec(@sql)
复制代码
删除数据处理临时表
- set @sql='drop table '+@m_tbname
- end
复制代码
删除数据处理应答文件
- set @sql='del '+@fname_in
- exec master..xp_cmdshell @sql
- go
复制代码
导入文本文件
- EXEC master..xp_cmdshell 'bcp "dbname..tablename" in c:\DT.txt -c -Sservername -Usa -Ppassword'
- 改为如下,不需引号
- 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'
- 此句需加引号
复制代码 |
|