sql编写代码规范

“SQL, Lisp, and Haskell are the only programming languages that I’ve seen where one spends more time thinking than typing.“ — Philip Greenspun

“ SQL，Lisp和Haskell是我所见过的仅有的一种编程语言，人们花更多的时间思考而不是打字。” -Philip Greenspun

Even with thinking more than typing SQL (Structured Query Language) we software engineers use it as a way to only pull the data.

即使考虑的不仅仅是键入SQL(结构化查询语言)，我们的软件工程师也将其用作仅提取数据的方式。

We usually don’t leverage SQL’s power of data manipulation to do the necessary changes in our code.

我们通常不利用SQL的数据操作能力在代码中进行必要的更改。

This is quite common among software engineers who work in web applications. Another thing we miss is, if we do the manipulation in SQL directly, the pulled data will be the same format for any programming language. This post aims to enlighten you about the powers of SQL you might know but generally don’t use.

在从事Web应用程序工作的软件工程师中，这很普遍。 我们错过的另一件事是，如果我们直接在SQL中进行操作，则对于任何编程语言，提取的数据将具有相同的格式。 这篇文章旨在启发您了解您可能知道但通常不使用SQL的强大功能。

TL; DR (TL;DR)

Here are the highlights:

以下是重点内容：

• Use SQL to do math like sum, average etc.
  使用SQL进行求和，平均值等数学运算
• Utilize it for grouping one to many relational values like getting categories of products.
  利用它来将一个或多个关系值分组，例如获取产品类别。
• Leverage SQL for string manipulation like using CONCAT_WS for concating first name and last name.
  利用SQL进行字符串操作，例如使用CONCAT_WS来隐藏名字和姓氏。
• Exploit SQL to sort by a custom priority formula.
  利用SQL按自定义优先级公式排序。

Examples below…

以下示例...

一些假设 (Some Assumptions)

Below are some assumptions made for this post:

以下是对此职位做出的一些假设：

1. Just because you can do it in SQL, doesn’t mean you need to do it in SQL and use the database resources. Always profile your solution and then decide where is it best to use it. There have been suggestions that it is more difficult and costly to scale a database than application code.
   仅仅因为您可以在SQL中进行操作，并不意味着您需要在SQL中进行操作并使用数据库资源。 始终分析您的解决方案，然后决定最好在哪里使用它。 有人建议，扩展数据库比应用程序代码更困难，成本更高。

2. Use SQL wisely and optimally. Always think of the necessary resources like processor and memory. EXPLAIN is your friend for query optimization.

   明智和最佳地使用SQL。 始终考虑必要的资源，例如处理器和内存。 EXPLAIN是您进行查询优化的朋友。

3. This post does not advocate putting all logic in the database like in the form of triggers, stored procedures, or views. The code in the database generally cannot be put into a version control system and testing database code is difficult.
   这篇文章并不主张将所有逻辑都以触发器，存储过程或视图的形式放入数据库中。 数据库中的代码通常不能放入版本控制系统中，并且测试数据库代码很困难。

4. SQL is generally case insensitive, so be careful when performing operations like CONCAT or any other string manipulation.

   SQL通常不区分大小写，因此在执行诸如CONCAT或任何其他字符串操作之类的操作时要小心。

5. In distributed systems, it is a balance of trade-offs. Same applies for deciding to do something in SQL or the programming language. Evaluate your options and choose the best one depending on the use case.
   在分布式系统中，这是折衷的平衡。 同样适用于决定以SQL或编程语言进行操作。 评估您的选项，并根据用例选择最佳的选项。
6. The example below uses MYSQL, so syntax and implementation for other flavors of SQL will differ.
   下面的示例使用MYSQL，因此其他类型SQL的语法和实现将有所不同。

这个例子 (The Example)

It will be easier to explain the superpowers of SQL by putting it in action in an example. Below is a basic schema with 2 tables in MYSQL for a refund microservice:

通过在示例中使用SQL，将更容易解释SQL的超能力。 以下是在MYSQL中有2个表格的基本模式，用于退款微服务：

There are 2 refunds and 7 related payments as example .

有2个退款和7个相关付款作为示例 。

一些假设 (Some assumptions)

For the refund microservice example schema and applications, the following assumptions are made:

对于退款微服务示例架构和应用程序，进行以下假设：

1. Refund microservice and data structure store the fk_item (the id of the ordered/delivered item), but it is not a hard foreign key.
   退款微服务和数据结构存储fk_item(已订购/已交付商品的ID)，但这不是硬外键。
2. Item can be refunded in either cash or credit for the amount paid for the same.
   可以用现金或信用卡退还所支付金额的商品。
3. Items can be refunded many times as long as the remaining balance can cover the requested refund amount for each cash and credit. For example, let’s say the item was paid 50 in cash and 50 in credit. 2 refunds of 20 cash and 20 credit can be done. So after these transactions the balance will be 10 cash and 10 credit for that item (50–20–20).
   只要剩余余额可以支付每笔现金和贷项要求的退款金额，就可以多次退款。 例如，假设商品以现金50和信用50支付。 可以退款2现金和20积分。 因此，在这些交易之后，该项目的余额将为10现金和10贷项(50-20-20)。
4. Each refund can have multiple items for payment. Each payment can be of type either cash or credit.
   每次退款都可以有多个付款项目。 每种付款都可以是现金或信贷类型。
5. All amounts are stored in cents so they are integers.
   所有金额均以美分存储，因此它们是整数。

Now let’s use some SQL powers. You can find the example with the related queries running on this .

现在，让我们使用一些SQL功能。 您可以在此上找到带有相关查询的示例。

用SQL做数学 (Do the math in SQL)

As software engineers, let’s say if we need to find the total cash and credit amount refunded for an item, what would we do? We would run something like:

以软件工程师的身份，假设我们需要找到退还某项商品的总现金和信用额度，该怎么办？ 我们将运行类似：

SELECT fk_item, fk_refund, amount, is_cash FROM payment WHERE fk_item=2001;

SELECT fk_item, fk_refund, amount, is_cash FROM payment WHERE fk_item=2001;

With the current data, it will give 3 rows like below:

使用当前数据，它将给出如下三行：

With these 3 rows, we would loop over them. If it is cash, accumulate it to cashBalance variable, if not sum it up to creditBalace variable. But instead of that, it would be a lot easier (and probably faster) to do in SQL like this:

有了这三行，我们就可以遍历它们。 如果是现金，则将其累加到cashBalance变量中，如果不累加到creditBalace变量中。 但是相反，在SQL中这样做会容易得多(可能更快)：

SELECT fk_item, SUM(amount) AS total_paid, IF(is_cash = 1, 'cash', 'credit') as type FROM payment WHERE fk_item = 2001 GROUP BY fk_item, is_cash;

SELECT fk_item, SUM(amount) AS total_paid, IF(is_cash = 1, 'cash', 'credit') as type FROM payment WHERE fk_item = 2001 GROUP BY fk_item, is_cash;

Resulting in this:

结果是：

The result is achieved easily now. If you need the total refund for the item, just change the GROUP BY to be on fk_item and it’s done. For 2 and 3 records it won’t feel significant. If there were say 20 refunds for that item, the first solution with a loop is writing more code with no gain. Like sum, other SQL functions can be used too. Simple math operations like , multiply, etc can be easy with SQL. This means no more loops.

现在可以轻松实现结果。 如果您需要物品的全额退款，只需将GROUP BY更改为fk_item即可完成。 对于2和3条记录，感觉并不重要。 如果说有20笔退款，那么第一个有循环的解决方案是编写更多没有收益的代码。 像sum一样，也可以使用其他SQL函数。 使用SQL可以轻松实现简单的数学运算，例如 ，乘法， 等。 这意味着不再有循环。

使用GROUP_CONCAT来获取相关的1：m关系值 (Use GROUP_CONCAT to fetch related 1:m relation values)

is a robust operation in SQL databases. It is instrumental when you need to get data from a one to many relationship.

是SQL数据库中的可靠操作。 当您需要从一对多关系中获取数据时，这很有用。

For instance, say you want to get all tags for a blog post or you want to get all categories of a product. Concerning this refund example, one item can be refunded multiple times. So we will get all the refunds associated with the item id. To get this we will run only 1 query and get it without any loops in the code, like below:

例如，假设您要获取博客文章的所有标签，或者要获取产品的所有类别。 关于此退款示例，一件商品可以多次退款。 因此，我们将获得与商品ID相关的所有退款。 为此，我们将只运行1个查询，并使其在代码中没有任何循环，如下所示：

SELECT fk_item, GROUP_CONCAT(DISTINCT fk_refund) refund_ids FROM payment WHERE fk_item = 2001;

SELECT fk_item, GROUP_CONCAT(DISTINCT fk_refund) refund_ids FROM payment WHERE fk_item = 2001;

This results in:

结果是：

Now we know that item 2001 has been refunded twice for 2 refunds. It will be easy to explode the refund Ids with , and proceed with any related operation. Be aware of that GROUP_CONCAT max length in MYSQL is 1024 characters.

现在我们知道商品2001已退款两次，共2次退款。 使用轻松分解退款ID ,然后继续进行任何相关操作。 请注意，MYSQL中GROUP_CONCAT的最大长度为1024个字符。

字符串操作 (String manipulation)

Many tasks like substring, concatenation, change case, and string compare can be done in SQL. With this example, I am going to show the usage of CONCAT_WS. It is concat with a separator. It can also be used to select, for instance, a first_name and last_name with a space in between.

可以在SQL中完成许多任务，例如子字符串，串联，大小写更改和字符串比较。 在此示例中，我将展示CONCAT_WS的用法。 它与分隔符连在一起。 它还可以用于选择例如first_name和last_name之间的空格。

In the case of having an optional middle name, COALESCE can be used with CONCAT_WS. That is something for you to explore :).

在具有可选中间名的情况下，可以将COALESCE与CONCAT_WS一起使用。 那是您需要探索的东西:)。

In this example, I will select refund_nr with its related reason:

在此示例中，我将选择与其相关的原因退还退款：

SELECT CONCAT_WS("-", refund_nr, reason) AS refund_nr_with_reason FROM refund;

SELECT CONCAT_WS("-", refund_nr, reason) AS refund_nr_with_reason FROM refund;

Resulting in:

导致：

If this needs to be shown on the credit note document, for example, no extra code is needed to join the values again. SQL makes it one step easier again. Beware again that SQL is a case-insensitive language.

例如，如果需要在贷方通知单文档上显示此内容，则无需额外的代码即可再次合并值。 SQL使它又简单了一步。 再次注意，SQL是不区分大小写的语言。

使用自定义公式排序 (Sorting with a custom formula)

All software engineers know you can sort based on a column. But if you are given a custom priority formula to sort, what would you do? Probably again resort back to code and loop to sort. So let’s set the priority formula rules for above example:

所有软件工程师都知道您可以根据列进行排序。 但是，如果给您一个自定义的优先级公式进行排序，您会怎么做？ 可能再次求助于代码并循环排序。 因此，让我们为上述示例设置优先级公式规则：

1. Premium customer refunds get the highest priority (we hack it with a priority of 9999999999)
   优质客户退款的优先级最高(我们以9999999999的优先级破解)
2. Other than premium customers, cash refunds get a priority of amount * 25, and for credit it is amount * 20.
   除高级客户外，现金退款的优先级为数量* 25，而贷项优先级为数量* 20。

As per the above rules, it is decided that premium customers and priority above 50000 (in cents) will be processed first. Then other refunds will be processed. Let’s get the priority refunds as below:

根据上述规则，决定将首先处理优先级高于50000(以美分为单位)的高级客户。 然后其他退款将被处理。 让我们获得以下优先退款：

SELECT r.refund_nr, r.reason, p.fk_item, p.amount, p.is_cash, IF(p.premium_customer = 1, 9999999999, p.amount * (IF(is_cash = 1, 25, 20))) AS priority FROM refund AS r INNER JOIN payment AS p ON r.id = p.fk_refund HAVING priority > 50000 ORDER BY priority DESC

SELECT r.refund_nr, r.reason, p.fk_item, p.amount, p.is_cash, IF(p.premium_customer = 1, 9999999999, p.amount * (IF(is_cash = 1, 25, 20))) AS priority FROM refund AS r INNER JOIN payment AS p ON r.id = p.fk_refund HAVING priority > 50000 ORDER BY priority D ESC SELECT r.refund_nr, r.reason, p.fk_item, p.amount, p.is_cash, IF(p.premium_customer = 1, 9999999999, p.amount * (IF(is_cash = 1, 25, 20))) AS priority FROM refund AS r INNER JOIN payment AS p ON r.id = p.fk_refund HAVING priority > 50000 ORDER BY priority D

The results are below:

结果如下：

With the proper use of IF in SQL, sorting by a custom priority formula is a lot easier than trying to do it with loops in code. Notice that even smaller amounts like 7.5 (750 cents) and 9.0 (900 cents) came to the highest priority as these refund payment amounts were associated with premium customers.

通过在SQL中正确使用IF，按自定义优先级公式进行排序比尝试对代码循环进行排序要容易得多。 请注意，即使是较小的金额(如7.5(750美分)和9.0(900美分))也具有最高优先级，因为这些退款金额与优质客户相关。

Use the superpowers of SQL to make your life as a software engineer easier.

使用SQL的强大功能，可以简化软件工程师的生活。

You can play with the example and run your queries on .

您可以使用该示例，并在上运行查询。

结论 (Conclusion)

There are other tricks of SQL that can help you as a software engineer. Like, UPDATE with INSERT using ON DUPLICATE KEY UPDATE. Whenever you have an itch for doing some manipulation on data pulled in from a database in code with loops, think again. Like any other language or tool, SQL is powerful but use it wisely. The main takeaway from this story is:

SQL还有其他技巧可以帮助您成为软件工程师。 就像， UPDATE用INSERT使用ON DUPLICATE KEY UPDATE 。 每当您想对使用循环代码从数据库中提取的数据进行某种操作时，都应该再考虑一下。 像任何其他语言或工具一样，SQL功能强大，但请明智地使用它。 这个故事的主要收获是：

Exploit the power of SQL optimally and wisely to write less code because “the best code is the code that was never written”. If it is not written there is no need to maintain it.

最佳而明智地利用SQL的功能来编写更少的代码，因为“最好的代码是从未编写过的代码”。 如果未编写，则无需维护。

You can read more of my blog posts at .

您可以在阅读更多我的博客文章。

翻译自:

sql编写代码规范