Shulou(Shulou.com)05/31 Report--

This article will explain in detail what is the basic form of SELECT in SQLite. Xiaobian thinks it is quite practical, so share it with you for reference. I hope you can gain something after reading this article.

SELECT basic form in SQLite

Each database usually contains multiple tables, and each table contains multiple pieces of data. To retrieve data from a database, you need the SQL query SELECT. This chapter covers SELECT statements, including SELECT basic forms, expressions, joins, grouping, sorting and deduplication, getting subsets, subqueries, and union queries.

3.1 SELECT basic form

This section explains the basic forms of SELECT statements, including the basic complete form, SELECT clauses, FROM clauses, and WHERE clauses

3.1.1 Basic forms

The following is the basic complete form of SELECT syntax:

SELECT [DISTINCT] select_heading

FROM source_tables

WHERE filter_expression

GROUP BY grouping_expressions

HAVING filter_expression

ORDER BY ordering_expressions

LIMIT count

OFFSET count

The parameters are described as follows:

? select_heading: used to define the format and content of the final result table, mainly used to define the table header.

Note: Data structures in SQL are tables, which are used to store data and process data. A table consists of a head and a body. The header defines the name and type (in SQLite units) of each column. Column names must be unique in the table. The header defines the order of columns, which are fixed as part of the table definition. The body contains all lines. Each row consists of one data element per column. All rows in each column of a table must have the same number of data elements, and each element can hold one data value (or NULL).

FROM source_tables: Specify one or more source tables and combine them into one large worksheet.

WHERE filter_expression: Filters specific rows in the worksheet.

GROUP BY grouping_expressions: Used to specify grouping.

HAVING filter_expression: filters specific rows in the grouping table.

ORDER BY ordering_expressions: Sorts the rows of the result set.

LIMIT count: Limits the result set output to a specific number of rows.

OFFSET count: Skip the first row of the result set.

Note: DISTINCT is optional and is used to eliminate duplicate rows. In addition to DISTINCT, additional clauses (FROM, WHERE, GROUP BY, etc.) are optional in SELECT statements.

Clauses in SELECT statements are not executed in the order in which they are written. The implementation process is shown in Figure 3.1.

Figure 3.1 Execution Flow

Note: The numbers after R in Figure 3.1 can be considered to indicate the order of execution.

A query statement requires one or two source tables.

(2) Obtain an R1 table through the FROM clause.

(3) The WHERE clause filters the rows in table R1 and generates a new table R2.

(4) The R2 table is grouped by the GROUP BY clause, and the R2 table is grouped into corresponding groups. At this time, the R3 table will be generated.

(5) The R3 table is filtered through the HAVING clause to generate the R4 table.

(6) The R4 table performs the format and content of the final result table to be displayed through the SELECT clause, and the R5 table is generated.

(7) The R5 table is deduplicated by using the DISTINCT keyword, and the R6 table will be generated at this time.

(8) R6 sorts through the ORDER BY clause, which generates the R7 table.

(9) The R7 table skips the first row of the table through the OFFSET clause to generate a new table, the R8 table.

(10) The R8 table is limited to a certain number of rows by the LIMIT clause, and the final result table Result is displayed.

About "SQLite SELECT basic form is what" this article is shared here, I hope the above content can have some help to everyone, so that you can learn more knowledge, if you think the article is good, please share it to let more people see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.