数据库逻辑删除的sql语句_通过数据库的眼睛查询sql的逻辑流程

数据库逻辑删除的sql语句

Structured Query Language (SQL) is famously known as the romance language of data. Even thinking of extracting the single correct answer from terabytes of relational data seems a little overwhelming. So understanding the logical flow of query is very important.

小号 tructured查询语言(SQL)是著名的被称为数据的浪漫语言。 甚至想从TB的关系数据中提取单个正确答案似乎也有些不知所措。 因此，了解查询的逻辑流程非常重要。

查询执行计划 (Query Execution Plan)

SQL is a declarative language, this means SQL query logically describes the question to the SQL Query Optimizer which later decides on the best way to physically execute the query. This method of execution is called the query execution plan. There can be more than one execution plan, so when we say optimize a query that in turn referring to make the query execution plan more efficient.

SQL是一种声明性语言，这意味着SQL查询从逻辑上向SQL查询优化器描述了问题，SQL优化器随后决定了物理执行查询的最佳方法。 这种执行方法称为查询执行计划。 可以有多个执行计划，因此当我们说优化查询时，该查询反过来又使查询执行计划更有效。

Let’s look into the 2 flows that a SQL query can be looked through:

让我们看一下可以查询SQL查询的2个流程：

查询的句法流程 (Syntactical Flow of Query)

The SELECT statement basically tells the database what data to be retrieved along with which columns, rows, and tables to get the data from, and how to sort the data.

SELECT语句基本上告诉数据库要检索哪些数据以及从中获取数据的列，行和表，以及如何对数据进行排序。

Abbreviated Syntax for the SELECT command [1]

SELECT命令的缩写语法[1]

• SELECT statement begins with a list of columns or expressions.

  SELECT语句以列或表达式列表开头。

• FROM portion of the SELECT statement assembles all the data sources into a result set that is used by the rest of the SELECT statement.

  SELECT语句的FROM部分将所有数据源组合成一个结果集，供其余SELECT语句使用。

• WHERE clause acts upon the record set assembled by the FROM clause to filter certain rows based upon conditions.

  WHERE子句作用于FROM子句组合的记录集，以根据条件过滤某些行。

• GROUP BY clause can group the larger data set into smaller data sets based on the columns specified.

  GROUP BY子句可以根据指定的列将较大的数据集分组为较小的数据集。

• HAVING clause can be used to restrict the result of aggregation by GROUP BY.

  HAVING子句可用于限制GROUP BY的聚合结果。

• ORDER BY clause determines the sort order of the result set.

  ORDER BY子句确定结果集的排序顺序。

Simplest possible valid SQL statement is :

最简单的有效SQL语句是：

SELECT 1; (Oracle a requires FROM DUAL appended to accomplish this)

选择1; (Oracle要求附加FROM FROM DUAL来完成此操作)

Hmmm... What is the problem in the flow of “Syntactical Flow”? Can you SELECT data without knowing WHERE it is coming FROM ?? ...Hmmm .. Somewhere there is LOGIC missing right !!! Soo ..

嗯...“句法流”中的问题是什么？ 您可以在不知道数据来自何处的情况下选择数据吗？ ...嗯..某个地方缺少LOGIC吧！！！ o ..

Smart on Smart在 Unsplash Unsplash拍摄

查询的逻辑流程 (Logical Flow of Query)

The best way to think the SQL statement is through the query’s logical flow. Logical may not be the same as physical flow and also it is not the same as the query syntax. Think the query in the following order:

认为SQL语句的最佳方法是通过查询的逻辑流程。 逻辑可能与物理流程不同，也与查询语法不同。 按以下顺序考虑查询：

A simplified view of Logical flow [1]

逻辑流的简化视图[1]

1. FROM: Logically query starts from the FROM clause by assembling the initial set of data.

   FROM ：逻辑查询是通过组装初始数据集从FROM子句开始的。

2. WHERE: Then where clause is applied to select only the rows that meet the criteria.

   WHERE ：然后应用where子句以仅选择满足条件的行。

3. Aggregations: Later aggregation is performed on the data such as finding the sum, grouping the data values in columns, and filtering the groups.

   聚合 ：稍后对数据执行聚合，例如查找总和，将数据值分组到列中以及过滤组。

4. Column Expressions: After the above operations the SELECT list is processed along with calculations of any expressions involved in it. (Except column expressions everything else is optional in SQL query.)

   列表达式 ：完成上述操作后，将处理SELECT列表以及其中涉及的任何表达式的计算。 (除了列表达式，SQL查询中的其他所有内容都是可选的。)

5. ORDER BY: After getting the final resulting rows are sorted ascending or descending by ORDER BY clause.

   ORDER BY ：得到最终的结果行之后，按ORDER BY子句对行进行升序或降序排序。

6. OVER: Windowing and ranking functions can later be applied to get a separately ordered view of the result with additional functions for aggregation.

   结束：以后可以使用开窗和排名功能来获取结果的单独排序视图，并带有用于聚合的其他功能。

7. DISTINCT: This is applied to remove any duplicate rows present in result data.

   DISTINCT ：用于删除结果数据中存在的所有重复行。

8. TOP: After all this process of selecting data filtering it and performing all the calculations and ordering them, SQL can restrict the result to top few rows.

   顶部：在完成所有这些选择数据的过程之后，对数据进行过滤并执行所有计算并对它们进行排序，SQL可以将结果限制在前几行。

9. INSERT, UPDATE, DELETE: This is the final logical step of the query to perform data modification using the resulting output.

   INSERT，UPDATE，DELETE：这是查询的最后逻辑步骤，用于使用结果输出执行数据修改。

10. UNION: The output from the multiple queries can be stacked to using the UNION command.

    UNION ：可以使用UNION命令将多个查询的输出堆叠在一起。

For all the data anlysts who are working on database or datawarehouse projects. It is very important to understand the logical flow and the basic logic behind it. In any data anlysis project, data collection would be first step (FROM) and removing unessecarry data (WHERE) and then ordering the data (ORDER BY) follows.

对于从事数据库或数据仓库项目的所有数据分析师。 了解逻辑流程及其背后的基本逻辑非常重要。 在任何数据分析项目中，数据收集都是第一步(FROM)，然后删除未保密的数据(WHERE)，然后对数据进行排序(ORDER BY)。

翻译自: https://medium.com/swlh/logical-flow-of-sql-query-sql-through-the-eye-of-database-e7d111c87516

数据库逻辑删除的sql语句