如何用SQL来思考？
如何停止根据游标、过程和函数来思考每个查询，并开始按应有的方式使用 SQL？ 我们是否只是通过练习来过渡到 SQL 思维，还是学习基于集合的查询语言有什么魔力？ 为了实现这一转变，您做了什么？
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
如何停止根据游标、过程和函数来思考每个查询，并开始按应有的方式使用 SQL？ 我们是否只是通过练习来过渡到 SQL 思维，还是学习基于集合的查询语言有什么魔力？ 为了实现这一转变，您做了什么？
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
发布评论
评论（10）
如果您是真正的
SQL极客，您首先应该想到的几个示例：圣经索引是
圣经的全文索引Luca Pacioli 的算术总结 描述复式记账实际上是标准化的数据库模式
当
薛西斯我清点他的军队时他使用了HASH AGGREGATE方法，用围墙围住了他的10,000部下占领的区域，然后带领其他人穿过这个围墙。杰克建造的房子应该使用自连接重写。圣诞节的十二天应该使用自连接和ROWNUM重写
There Was An Old Woman Who Swallowed a Fly应使用CTE进行重写如果
欧盟被称为European Union All，我们会在 欧元纸币，而不是2。最后，您可以在我的博客中阅读一篇蹩脚的文章，内容是我如何不再担心并学会热爱
SQL（我几乎忘记了我写的）：还有一篇关于该主题的文章：
A few examples of what should come to your mind first if you're real
SQLgeek:Bible concordance is a
FULLTEXTindex to theBibleLuca Pacioli's Summa de arithmetica which describes double-entry bookkeeping is in fact a normalized database schema
When
Xerxes Icounted his army by walling an area that10,000of his men occupied and then marching the other men through this enclosure, he usedHASH AGGREGATEmethod.The House That Jack Builtshould be rewritten using a self-join.The Twelve Days of Christmasshould be rewritten using a self-join and aROWNUMThere Was An Old Woman Who Swallowed a Flyshould be rewritten usingCTE'sIf the
European Unionwere calledEuropean Union All, we would see27spellings for the wordeuroon a Euro banknote, instead of2.And finally you can read a lame article in my blog on how I stopped worrying and learned to love
SQL(I almost forgot I wrote it):And one more article just on the subject:
关键是你正在操纵 SETS & 集合的元素； 并将不同的集合（和相应的元素）联系在一起。 这确实是它的核心，恕我直言。 这就是为什么每个表都应该有一个主键； 为什么你会在该语言中看到集合运算符； 以及为什么像 UNION 这样的集合运算符不会（默认情况下）返回重复的行。
当然，在实践中，集合的规则会被扭曲或破坏，但不难看出何时需要这样做（否则，SQL 将受到太大限制）。 恕我直言，只需打开你的离散数学书并重新熟悉一些固定练习即可。
The key thing is you're manipulating SETS & elements of sets; and relating different sets (and corresponding elements) together. That's really the heart of it, imho. That's why every table should have a primary key; why you see set operators in the language; and why set operators like UNION won't (by defualt) return duplicate rows.
Of course in practice, the rules of sets are bent or broken but it's not that hard to see when this is necessary (otherwise, SQL would be TOO limited). Imho, just crack open your discrete math book and reacquaint yourself with some set exercises.
我能给你的最好建议是，每次你考虑逐行处理某些事情时，你停下来问问自己是否有一种基于集合的方法可以做到这一点。
Best advice I can give you is that every time you think about processing something row-by-row, that you stop and ask yourself if there is a set-based way to do this.
乔·塞尔科的《集合思维》（书）
Joe Celko's Thinking in Sets (book)
当人们问我有关联接的问题时，我会在这里发送给他们对它们的本质有很好的视觉表现！
When people ask me about joins I send them here it has a great visual representation on what they are!
我学习的方法是进行大量查询，并从事需要您根据结果集进行思考的工作。
从您的问题来看，您似乎一直在编写大量使用顺序/过程/迭代数据操作的前端代码。 如果您没有参与任何需要使用结果集技能的项目，我个人不会担心。
您可能想要尝试的一件事是尝试编写分析查询，例如，生成有关数据的简单报告。 在这些情况下，您试图通过将大量数据划分为集合来汇总它们。
另一个好方法是阅读一本有关 RDBMS 理论/数学基础的书。 这些内容严格涉及集合论以及 SQL 查询语法的某些部分如何与其背后的数学直接相关。 当然，这需要你喜欢数学。 :)
The way that I learned was by doing a lot of queries, and working at a job that required you to think in terms of result sets.
From your question, it seems like you've been writing lots of front-end code that uses sequential/procedural/iterative data manipulation. If you don't get on any projects that require you to use result set skills, I personally wouldn't worry about it.
One thing you might want to try is by trying to write analytical queries, e.g., generating simplistic reports on your data. In those cases you are trying to summarize large amounts of data by cordoning them off into sets.
Another good way would be to read a book on the theoretical/mathematical foundations to RDBMSes. Those deal strictly with set theory and how parts of the SQL query syntax relate directly with the math behind it. Of course, this requires you to like math. :)
我发现《SQL 的艺术》对于进入正确的心态非常有用。
然而，这部分归结于风格。
显然，您需要开始考虑结果集而不仅仅是程序上。
然而，一旦你开始这样做，你常常会发现必须做出决定。
您是否编写了极其复杂的更新语句，除了您自己之外，其他人可能都难以理解并且难以维护，或者您是否编写了效率较低但更易于管理的过程？
我强烈建议您记住 SQL 语句中可以包含注释来阐明它们正在做什么，而不仅仅是存储过程。
链接：SQL 的艺术
I found that the Art Of SQL was a useful kick in the head for getting into the right mindset.
Part of this, however, comes down to style.
Obviously, you need to start thinking in result sets and not just procedurally.
However, once you've start that, you will often find decisions have to be made.
Do you write the incredibly complex update statement that may be difficult to understand by anyone but yourself, and difficult to maintain, or do you write a less efficient, but easier to manage procedure?
I would HIGHLY suggest that you remember that SQL statements can have comments in them to clarifiy what they are doing, not just stored procedures.
link: The Art Of SQL
您可能想要尝试的一项练习是：
从应用程序层获取一些现有的报告代码，最好是生成单个表格数据集的代码。 从最基本的元素开始，将其移植到 SQL 视图。
获取从单个表中提取的所有列并编写 SQL 语句来选择该数据。 然后一次连接一张表，并开始找出适合输出的条件和逻辑。
您可能会遇到一些特定的任务，这些任务乍一看在 SQL 中似乎是不可能的，但根据您正在编程的实现，几乎总有一种方法可以获得您正在寻找的结果。 检查您的 SQL 实现的文档，或尝试 Google。
此练习的好处是为您提供原始报告进行测试，以便您知道是否获得了预期的输出。
需要注意的一些事情：
BIT和 C 风格的bool之间通常存在很大差异。 至少，您可能必须显式地将输出从INT转换为BIT。OUTER JOIN非常有用，但尽量不要滥用它们。One exercise you might want to try is this:
Take some of your existing reporting code from your application layer, preferably something that produces a single, tabular data set. Starting with the most basic elements, port it over to an SQL View.
Take all of the columns pulled from a single table and write the SQL statement to select that data. Then join on one table at a time and start figuring out the appropriate conditions and logic for your output.
You might come up against some particular task that at first seems impossible in SQL, but depending on the implementation you are programming against, there is almost always a way to get the result you're looking for. Check the documentation for your SQL implementation, or try Google.
This exercise has the benefit of giving you an original report to test against, so you know if you're getting the output you expect.
A few things to watch out for:
BITand a C-stylebool. At the very least, you may have to explicitly cast your output fromINTtoBIT.OUTER JOINs are useful when a portion of the data might be empty, but try not to abuse them.我觉得需要一段时间来调整（对我来说那是很久以前的事了，所以我不太记得了）。 但也许关键点是 SQL 是声明性 - 即您指定什么 你想要完成的事情，而不是确切地如何 应该在程序上完成的事情。 举一个简单的例子：
“获取位于伦敦的部门中员工的姓名和薪水”
相关的 SQL几乎是自然的：
我们已经“告诉”SQL 如何将部门与员工连接起来，但只是声明式（NATURAL JOIN 消除了这样做的需要，但很危险，因此在实践中不使用）。 我们还没有程序定义它应该如何完成（例如“对于每个部门，找到所有员工...”）SQL 可以自由选择执行查询的最佳方法。
I think it takes a while to adjust (it was long ago for me, so I don't remember too well). But perhaps the key point is that SQL is declarative - i.e. you specify what you want done, not precisely how it should be done procedurally. So for a simple example:
"Get me the names and salaries of employees in departments located in London"
The relevant SQL is almost natural:
We have "told" SQL how to join departments to employees, but only declaratively (NATURAL JOIN removes the need to do that, but is dangerous so not used in practice). We haven't defined procedurally how it should be done (e.g. "for each department, find all employees...") SQL is free to choose the optimal method to perform the query.
当您使用 SQL 将表转储到文件系统，然后用您最喜欢的编程语言执行任何必须执行的操作时，考虑行是有意义的。
没有过多利用SQL； 浪费磁盘、内存、CPU 和人力资源。
将 SQL 视为英语（或您喜欢的任何人类语言）。
显示所有每天骑牛喝醉但从未与婆婆一起去过印度尼西亚的顾客，他们的电话号码与我朋友道格的电话号码相同，除了区号。
您可以通过一条 SQL 语句完成此操作（以及更多操作），只需了解如何操作即可。 这是非常有利可图的。
Thinking of rows makes sense when you use SQL to dump a table to your file system and then do whatever has to be done in your favorite programming language.
Not too much leverage of SQL; waste of disk, memory, cpu and human resources.
Think of SQL as of English (or whatever human language you prefer).
Show me all customers who ride bulls and get drunk every day but never visited Indonesia with their mother-in-law whose phone number is the same as my friend Doug's except for the area code.
You can do it (and much more) in one SQL statement, just learn how to. It's very lucrative.