I'm looking for a tool, which would help creating complex SQL queries. Sometimes it's difficult to even verify, whether the results of a query are correct. It's especially easy to get queries joining several tables to return too little or too much data.
我正在寻找一个有助于创建复杂SQL查询的工具。有时甚至很难验证查询结果是否正确。获取连接多个表的查询以返回太少或太多的数据尤其容易。
The tool should enable at least creation of test tables, some kind of visualization how the queries gather their data and hopefully give better parsing of error cases than for example Oracle does.
该工具应该至少能够创建测试表,某种可视化查询如何收集数据,并希望能够比Oracle更好地解析错误情况。
Are there tools like this or do I have to stick with creating test tables manually, filling them with test data and commiting all kinds of queries with SQuirrel SQL?
有这样的工具还是我必须坚持手动创建测试表,用测试数据填充它们并使用SQuirrel SQL提交各种查询?
4 个解决方案
#1
What RDBMS are you using? All of the major ones have some type of console available (e.g.-SSMS in SQL Server, Toad in Oracle, MySQL Query Browser/Administrator for MySQL, etc.), and they all have Query Execution Plans where you can see how the query will actually run. So, the answer to your question is that it's entirely dependent on what RDBMS you're using, but the safe bet answer is: Yes.
您使用的是什么RDBMS?所有主要的控制台都有某种类型的控制台(例如,SQL Server中的SSMS,Oracle中的Toad,MySQL的MySQL查询浏览器/管理员等),它们都有查询执行计划,您可以在其中查看查询将如何实际上跑。所以,你的问题的答案是它完全取决于你正在使用的RDBMS,但安全的答案是:是的。
#2
I recommend trying SQL Server 2008 Management Studio Express (SSMSE) if you are working with SQL Server. I have used it at work and I believe it does everything you are looking for.
如果您使用的是SQL Server,我建议您尝试使用SQL Server 2008 Management Studio Express(SSMSE)。我在工作中使用它,我相信它可以满足您的一切需求。
You can get it and SQL Server (express editions) here.
你可以在这里获得它和SQL Server(快速版)。
#3
When you have a very complex query it is usually easiest to validate by breaking it up into multiple queries that populate temp tables. These intermediary results can be individually verified and then you bring them together to produce the final result set. Depending on performance needs you can stick with the temp table approach or you can then rewrite to a single statement. Typically when I have a huge query it is for background processing so I stick with the temp table approach.
当您有一个非常复杂的查询时,通常最简单的方法是将其分解为填充临时表的多个查询。这些中间结果可以单独验证,然后将它们组合在一起以生成最终结果集。根据性能需求,您可以坚持使用临时表方法,或者然后可以重写为单个语句。通常,当我有一个巨大的查询时,它用于后台处理,所以我坚持使用临时表方法。
#4
Certainly not a free, open-source solution, but I believe Quest Software's TOAD will fit your requirements. Quest seems to offer alot of tools in that space...they have tools for modeling and analysis, however I've never used the modeler or analyzer.
当然不是免费的开源解决方案,但我相信Quest Software的TOAD将满足您的要求。 Quest似乎在那个空间提供了很多工具......他们有建模和分析工具,但我从未使用过建模器或分析器。
I personally have experience with the commercial version of TOAD for Oracle. It's GUI is overwhelming at first, but after you mentally filter out all of the extra buttons that you'll never use, it's manageable.
我个人对TOAD for Oracle的商业版有经验。它的GUI一开始是压倒性的,但是在你精神上过滤掉你永远不会使用的所有额外按钮之后,它是可管理的。
#1
What RDBMS are you using? All of the major ones have some type of console available (e.g.-SSMS in SQL Server, Toad in Oracle, MySQL Query Browser/Administrator for MySQL, etc.), and they all have Query Execution Plans where you can see how the query will actually run. So, the answer to your question is that it's entirely dependent on what RDBMS you're using, but the safe bet answer is: Yes.
您使用的是什么RDBMS?所有主要的控制台都有某种类型的控制台(例如,SQL Server中的SSMS,Oracle中的Toad,MySQL的MySQL查询浏览器/管理员等),它们都有查询执行计划,您可以在其中查看查询将如何实际上跑。所以,你的问题的答案是它完全取决于你正在使用的RDBMS,但安全的答案是:是的。
#2
I recommend trying SQL Server 2008 Management Studio Express (SSMSE) if you are working with SQL Server. I have used it at work and I believe it does everything you are looking for.
如果您使用的是SQL Server,我建议您尝试使用SQL Server 2008 Management Studio Express(SSMSE)。我在工作中使用它,我相信它可以满足您的一切需求。
You can get it and SQL Server (express editions) here.
你可以在这里获得它和SQL Server(快速版)。
#3
When you have a very complex query it is usually easiest to validate by breaking it up into multiple queries that populate temp tables. These intermediary results can be individually verified and then you bring them together to produce the final result set. Depending on performance needs you can stick with the temp table approach or you can then rewrite to a single statement. Typically when I have a huge query it is for background processing so I stick with the temp table approach.
当您有一个非常复杂的查询时,通常最简单的方法是将其分解为填充临时表的多个查询。这些中间结果可以单独验证,然后将它们组合在一起以生成最终结果集。根据性能需求,您可以坚持使用临时表方法,或者然后可以重写为单个语句。通常,当我有一个巨大的查询时,它用于后台处理,所以我坚持使用临时表方法。
#4
Certainly not a free, open-source solution, but I believe Quest Software's TOAD will fit your requirements. Quest seems to offer alot of tools in that space...they have tools for modeling and analysis, however I've never used the modeler or analyzer.
当然不是免费的开源解决方案,但我相信Quest Software的TOAD将满足您的要求。 Quest似乎在那个空间提供了很多工具......他们有建模和分析工具,但我从未使用过建模器或分析器。
I personally have experience with the commercial version of TOAD for Oracle. It's GUI is overwhelming at first, but after you mentally filter out all of the extra buttons that you'll never use, it's manageable.
我个人对TOAD for Oracle的商业版有经验。它的GUI一开始是压倒性的,但是在你精神上过滤掉你永远不会使用的所有额外按钮之后,它是可管理的。