这两天在折腾数据，写了很多很多SQL（当然是对我来说，比如象锅巴那种牛人，他们一写就是几百上千行的，我不能比），我只写了100多行。基本上所有的操作都通过SQL完成了，除非实在没办法完成的。才用PHP处理。

对我来说，这两天 insert into xxx (xxx) select xxx from xxx where xxx，写的很多了

还有update a,b set a.xx=b.xx where a.id=b.id之类的也很多

在锅巴哥哥的介绍下，还知道把一句SQL分成多句，或者用下面这张图解决。。。

嗯，本来想用存储过程的，但赶时间，来不及学了。几年前看的东西全忘光了。靠，晚上回去翻开我的mysql书看一下下先。

在使用ZendFramework开发的时候，会遇到要对SQL进行优化的处理，但是从ZEND的DB类里并没有找到输出SQL的方法，唯一看到的输出也是在SELECT类里面有一个__toString()的方法，但这个方法也只能输出SELECT的语句，对于Insert,Update,Delete之类的语句并不能处理。

开始的时候，很郁闷，后来问HaoHappy，因为他是国内第一批翻译ZF手册的人，果然我一问，他一下子就说，有一个Profiler类可以解决。
翻开手册，Zend_Db_Profiler，里面果然有操作。。（注：类名是凭记忆的，以下内容也是凭记忆写出来的）

1、在DB的CONFIG里面把profiler打开，如果没有打开，则需要用$profiler = $db->getProfiler();然后 $profiler->setEnable(true); (好象记错了，请看手册，我这里只说解决方法)

2、然后有一个设置 Profiler 的filter条件的，可以设定select,insert,update,delete的操作。

3、然后根据官方的例子，对SQL进行输出（手册上的例子，只是输出了执行时间最长的SQL哦）。如果需要每条都输出，你得在那个 if 判断外把sql 存储下来，即 $query->getQuery();

4、根据手册，还可以输出到fireBug和利用FirePHP来进行类似的debug，但我试了一下没有成功（ QEE 支持 firePHP ,不知道是否借鉴了ZEND的这个功能，猜测而己 ）

纯粹是一个记录，顺便感谢一下HaoHappy

看到这个的时候很吃惊，最初看到类似功能的时候是在PHPMYADMIN 3上面，想不到现在已经发展到这个地步了，实在让人大吃一“斤”呀。

WWW SQL Designer allows users to create database designs, which can be saved/loaded and exported to SQL scripts. Various databases and languages are supported. Ability to import existing database design.

About

Hi and welcome to WWW SQL Designer! This tool allows you to draw and create database schemas (E-R diagrams) directly in browser, without the need for any external programs (flash). You only need JavaScript enabled. The Designer works perfectly in Mozillas (Firefox, Seamonkey), Internet Explorers (6, 7, 8), Safari and Operas. Konqueror works, but the experience is limited.

Many database features are supported, such as keys, foreign key constraints, comments and indexes. You can either save your design (for further loading & modifications), print it or export as SQL script. It is possible to retrieve (import) schema from existing database.

WWW SQL Designer was created by Ondrej Zara and is built atop the oz.js JavaScript module. It is distributed under GNU GPL license.

网址：http://code.google.com/p/wwwsqldesigner/

我是从：http://parandroid.com/www-sql-designer-open-source-sql-based-web-creation-tools/看来的

它支持很多的数据库功能:

• 键
• 强制外关键字
• 注解
• 索引

当数据库图表创建后，你可以:

• 保存你的数据表设计或稍后修改它们
• 打印它
• 作为 SQL脚本导出

它甚至还可以从已存在的数据库中检索出概况图标，支持MySQL, MSSQL, SQLite 和 Web2py。感兴趣的朋友看查看DEMO.

备份资料：Copy from ---> http://php.mydict.com/ziliao/7/2006_05/SQLYuJuDaoRuDaoChuDaQuan3016_1.html

/******* 导出到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

/*动态文件名
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)) '　' 转换后的别名
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 数据库
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 ***************/
如果要导出数据到已经生成结构(即现存的)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 数据库名..B表

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

文件名为参数
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=;')...产品

********************* 导入 xml　文件

DECLARE @idoc int
DECLARE @doc varchar(1000)
--sample XML document
SET @doc ='
<root>
<Customer cid= "C1" name="Janine" city="Issaquah">
<Order oid="O1" date="1/20/1996" amount="3.5" />
<Order oid="O2" date="4/30/1997" amount="13.4">Customer was very satisfied
</Order>
</Customer>
<Customer cid="C2" name="Ursula" city="Oelde" >
<Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue
white red">
<Urgency>Important</Urgency>
Happy Customer.
</Order>
<Order oid="O4" date="1/20/1996" amount="10000"/>
</Customer>
</root>
'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/root/Customer/Order', 1)
WITH (oid char(5),
amount float,
comment ntext 'text()')
EXEC sp_xml_removedocument @idoc


???????

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


/********************导整个数据库*********************************************/

用bcp实现的存储过程


/*
实现数据导入/导出的存储过程
根据不同的参数,可以实现导入/导出整个数据库/单个表
调用示例:
--导出调用示例
----导出单个表
exec file2table 'zj','','','xzkh_sa..地区资料','c:\zj.txt',1
----导出整个数据库
exec file2table 'zj','','','xzkh_sa','C:\docman',1

--导入调用示例
----导入单个表
exec file2table 'zj','','','xzkh_sa..地区资料','c:\zj.txt',0
----导入整个数据库
exec file2table 'zj','','','xzkh_sa','C:\docman',0

*/
if exists(select 1 from sysobjects where name='File2Table' and objectproperty(id,'IsProcedure')=1)
drop procedure File2Table
go
create procedure File2Table
@servername varchar(200) --服务器名
,@username varchar(200) --用户名,如果用NT验证方式,则为空''
,@password varchar(200) --密码
,@tbname varchar(500) --数据库.dbo.表名,如果不指定:.dbo.表名,则导出数据库的所有用户表
,@filename varchar(1000) --导入/导出路径/文件名,如果@tbname参数指明是导出整个数据库,则这个参数是文件存放路径,文件名自动用表名.txt
,@isout bit --1为导出,0为导入
as
declare @sql varchar(8000)

if @tbname like '%.%.%' --如果指定了表名,则直接导出单个表
begin
set @sql='bcp ' @tbname
case when @isout=1 then ' out ' else ' in ' end
' "' @filename '" /w'
' /S ' @servername
case when isnull(@username,'')='' then '' else ' /U ' @username end
' /P ' isnull(@password,'')
exec master..xp_cmdshell @sql
end
else
begin --导出整个数据库,定义游标,取出所有的用户表
declare @m_tbname varchar(250)
if right(@filename,1)<>'\' set @filename=@filename '\'

set @m_tbname='declare #tb cursor for select name from ' @tbname '..sysobjects where xtype=''U'''
exec(@m_tbname)
open #tb
fetch next from #tb into @m_tbname
while @@fetch_status=0
begin
set @sql='bcp ' @tbname '..' @m_tbname
case when @isout=1 then ' out ' else ' in ' end
' "' @filename @m_tbname '.txt " /w'
' /S ' @servername
case when isnull(@username,'')='' then '' else ' /U ' @username end
' /P ' isnull(@password,'')
exec master..xp_cmdshell @sql
fetch next from #tb into @m_tbname
end
close #tb
deallocate #tb
end
go


/************* Oracle **************/
EXEC sp_addlinkedserver 'OracleSvr',
'Oracle 7.3',
'MSDAORA',
'ORCLDB'
GO

delete from openquery(mailser,'select * from yulin')

select * from openquery(mailser,'select * from yulin')

update openquery(mailser,'select * from yulin where id=15')set disorder=555,catago=888

insert into openquery(mailser,'select disorder,catago from yulin')values(333,777)


补充：

对于用bcp导出,是没有字段名的.

用openrowset导出,需要事先建好表.

用openrowset导入,除ACCESS及EXCEL外,均不支持非本机数据导入

刚才打开google reader的时候，突然看到一篇文章，让俺大吃一惊呀。详细内容如下：

请谨慎注意这一微软SQLBug
刚来博客园，有什么不对，需要改进的地方，欢迎各位同道指出来，谢谢。
今天在使用数据库时出现意外操作,将一张表的数据删除了。仔细查看SQL语句，发现问题。将问题类推到Northwind中。我们使用两个表,Employees和Products
SQL语句如下:
select * from Products where CategoryID in (select CategoryID from Employees)
不看表结构是看不出什么问题的，不清楚的看一下表结构。执行上面的SQL语句,你将能看到所有的产品记录。
当你执行上面语句的"select CategoryID from Employees",将会提示一个错误提示:
消息 207，级别 16，状态 3，第 1 行
列名 'CategoryID' 无效。
表Employees中根本就没有CategoryID列,而微软忽略了括号中的错误,执行前面的语句。如果是应用在删除，更新，那后果将是不可现象的。我曾为此付出了代价。
这个应该就是微软SQL的BUG吧,我们需要特别注意。
在SQL2000企业管理器,SQL2000的查询分析器,SQL2005的Management Studio Express中均有此Bug。

然后有人回复为：

我在Sql2000上测试，确实有这个问题。

有意思的问题，在我的management studio里面查询，也会有这个问题，要好好地研究一下。

Microsoft SQL Server Management Studio 9.00.1399.00
有楼主说的问题！
确实应该注意一下。

看了表结构才知道，Employees 表里面没有 CategoryID字段。为什么不报错呢。错误被吃掉了。

select * from Products where CategoryID in (select CategoryID from Employees)

select * from Products where CategoryID in (select EmployeeID from Employees)

我比较了一下这两个语句的执行计划，不完全一样。建议lz分析一下执行计划。
因为我还不能完全看懂执行计划。只能看出来两个执行计划是不一样的。呵呵。

联想起以前写CRUD之类的东西时候，在过滤条件时，自动将空值直接unset掉，然后再拼成SQL，后来被BOBBY严厉批评了一下，确实，查询的时候感觉不出有什么问题，可如果是在UPDATE或者是DELETE的时候呢？本来是有条件的，可突然被unset掉成为没有条件了，那岂不是很恐怖？

借这个机会再次提醒自己，细心、周全、小心

最近看老系统的代码，一些SQL确实让我汗颜，不过也确实可以知道一些另类的SQL操作方法。虽然。。。没啥效率

顺着《大腕》的风，我也来套点：
写SQL，没有7、8个left join你都不好意思拿出手。。
什么子查询、嵌套表啦，别嫌多，能用两个以上的人，根本不再乎再用几个
什么表外键啦、主键啦、索引啦，根本不用，要做就做最纯静的表。
我们的口号是，不占数据库索引空间。

　　经常在新闻系统中显示：上一条，上一条，在网上找了很久也没有发现有什么特别好的解决方案。在看了几个人的解决方案后，写了如下的sql语句。也算是抛砖引玉吧。

　　SQL是这样的： SELECT max(id) FROM table WHERE id < 当前ID UNION SELECT min(id) FROM table WHERE ID>当前ID

　　可是某人告诉我。ＭＳＳＱＬ居然不支持union，于是我写了这么一条，因为我不知道MSSQL是否能执行，希望给大家一个帮忙(反正MYSQL4是不能运行的)。呵呵。
　　SQL：select top 3 id from table where id < (select min(id) from table where id > 当前ID) order by id desc

　　权当做个笔记。 （希望有人指正，我也好多记一点多学一点）