This is the fifth day of my participation in the November Gwen Challenge. Check out the details: The last Gwen Challenge 2021
Database query
selectCommand and operation pipeline
Syntactically, the SELECT command combines a number of relational operations in a series of terms, each representing a particular relational operation. In SQLite, almost all clauses are optional.
The general format of the select command is as follows:
select [distinct] heading
from tables
where predicate
group by columns
having predicate
order by columns
limit count,offset;
Copy the codeEach keyword in the above command such as from, WHERE,having, etc., is a separate clause, and each clause consists of the keyword and the following clause.
The select command takes one or more input relationships, starting with from, and combines them into a single composite relationship that is passed to the subsequent operation chain.
All clauses except SELECT are optional. A valid query command must provide a SELECT. The most common select command consists of three clauses :select,from, and WHERE. The basic syntax and associated clauses are as follows:
select heading from tables where predicate;
The from clause above is a comma separated list of one or more tables, views, etc. If you specify multiple tables, they are combined to form a single relationship, which is done by linking. As the original data relationship, all subsequent operations work directly from or from the original data.
The WHERE clause will filter the rows of R, the result of the previous from operation. Arguments to WHERE are predicate, or logical expressions that define the selection criteria for rows in R that will appear (or be excluded) in the result. The selected rows from the WHERE clause form a new relationship, R2.
The following operation demonstrates getting data from a data table:
select id,name from food_types;
id name
---------- ----------
1fruit2vegetables3beef4chickenCopy the codeThe above query will return data for all rows in the food_types table because there is no WHERE clause to filter rows. The select clause also specifies the values of the ID and name columns to be queried in the food_types table, which are all columns in the food_types table. And the FROM clause does not join the table, so the above statement returns a copy of the food_types table. * for all columns is also supported in SQLite, so the above statement could also be written as:
select * from food_types;
In summary, the basic SELECT in SQLite handles collecting all the data in the FROM clause, filtering rows in the WHERE clause and columns in the SELECT clause.
filter
SQLite applies the WHERE clause to each row produced by the FROM clause. Where is both a limit and a filter. The arguments to WHERE are logical predictions, and the simplest form of prediction is to assert something.
Here’s an example:
The dog(subject) is purple and has a toothy grin.
In the example above, where dog is the subject, the prediction is made up of two statements (purple in colour) and a toothy grin. This claim may or may not be true, depending on the actual dog.
The subject of the where clause is row, which is the largest logical subject. The WHERE clause is a logical prediction that all rows evaluated true will be included in the result set and those evaluated false will be excluded. Therefore, the above declaration for dogs translates into an equivalent operation:
select * from dogs where color = 'purple' and grin ='toothy';
SQL > get all the rows in the DOGS table, then apply the WHERE clause prediction to make a logical prediction:
this row has color = 'purple' and grin = 'toothy'.
value
“Value” represents some kind of data in the real world. Values can be divided by their type, such as numeric values (1,2,3, etc.), or string values (‘ jujy-fruit ‘).
“Values” can be literal values (1,2,3, or ‘jujy-fruit ‘), variables (typically column names of the form foods.name), expressions (3+2/5), results of a function (counts(foods.name)), etc.
The operator
The operator takes one or more values as input and produces a new value as output. It is called an operator because it can do something and produce a result. Binary operators operate on two input values (or operands), ternary operators operate on three operands, unary operators operate on one value, and so on.
Operators can be bundled together, and the output of one operator can be used as the input of another operator to form value expressions.
By concatenating operators together, we can create value expressions that express machine-complex expressions in other values, such as:
x = count(episodes.name)
y = count(foods.name)
z = y / x * 11
Copy the codeBinary operator
Binary operators are by far the most commonly used SQL operators. The following table lists the binary operators supported by SQLite in descending order of priority, with groups of the same area having the same priority:
| The operator | type | role |
|---|---|---|
| ` | ` | String |
* |
Arithmetic(Arithmetic operator) | take |
/ |
Arithmetic | In addition to |
% |
Arithmetic | die |
+ |
Arithmetic | add |
- |
Arithmetic | Reduction of |
<< |
Bitwise(bit operator) | Moves to the right |
>> |
Bitwise | Shift to the left |
& |
Logical(Logical operator) | with |
| ` | ` | Logical |
< |
Relational(Relational operator) | Less than |
< = |
Relational | Less than or equal to |
> |
Relational | Is greater than |
> = |
Relational | Greater than or equal to |
= |
Relational | Is equal to the |
= = |
Relational | Is equal to the |
<> |
Relational | Is not equal to |
! = |
Relational | Is not equal to |
IN |
Logical | In |
AND |
Logical | with |
OR |
Logical | or |
IS |
Logical Equal | Is equal to the |
LIKE |
Relational St | String matching |
GLOB |
Relational Fi | File name Matching |
In the table above, the arithmetic operators (such as addition, subtraction, multiplication, division, and modulo) are binary operators that input numeric values and produce a numeric value.
sqlite3->select 3 + 3;
3 + 3
----------
6
Copy the codeRelational operators (e.g. >,<,=) are binary operators that compare values and value expressions and produce logical results (also known as true and false values). Relational operators form logical expressions such as:
x > 5
1 < 2
A logical expression is an arbitrary expression that returns true or false values. In SQLite, false can be replaced by the number 0, and true can be replaced by other non-0 values, such as:
sqlite3->select 1 > 0;
1 > 0
----------
1
sqlite3->select 1 < 0;
1 < 0
----------
0
sqlite3->select 2 > 0;
2 > 0
----------
1
sqlite3->select - 1 and 1;
- 1 and 1
----------
1
sqlite3->select 0 and 1;
0 and 1
----------
0
sqlite3->
sqlite3->
sqlite3->select 0 & 1;
0 & 1
----------
0
sqlite3->select - 1 & 1;
- 1 & 1
----------
1
sqlite3->select 0 or 1;
0 or 1
----------
1
sqlite3->select 0 | 1;
0 | 1
----------
1
Copy the codeLogical operator
The logical operators (AND,OR,NOT,IN) are binary operators that operate on true AND false values OR logical expressions. They produce specific true and false values based on input. They can construct more complex logical expressions from simpler expressions, for example:
(x < 5) AND (x ! = 3)
(y < 2) OR (y > 4) AND NOT (y = 0)
(color = 'purple') AND (grin = 'toothy')
x in (x,y,z)
True increments follow the usual rules of Boolean logic, but taking null into account can be troublesome. The WHERE clause is made up of these true and false values — using logical operators to answer real questions about the data in an SQLite database, such as:
sqlite3->select id,name from new_contacts where id < 4 AND id > = 2;
id name
---------- ----------
2 ddd
3 eee
sqlite3->select id,name from new_contacts where id = 5 OR id = 3;
id name
---------- ----------
3 eee
5 ddd
sqlite3->select id,name from new_contacts where id < 5 AND id > 1 AND NOT (id = 4);
id name
---------- ----------
2 ddd
3 eee
sqlite3->select id,name from new_contacts where id in (0.5.3);
id name
---------- ----------
3 eee
5 ddd
Copy the codeLIKEwithGLOBThe operator
The LIKE operator is a useful relational operator. LIKE is similar to equality (==) in that it uses a pattern to match strings. For example, to query information about contacts starting with Z in the new_contacts table, you can use the following query statement:
sqlite> SELECT * FROM new_contacts WHERE name LIKE 'Z%';
id name phone email address
---------- ---------- ----------- ---------- ----------
1 zyf 13567890987 no address
4 zyf UNKNOW no address
Copy the codeA percent sign (%) in a pattern can match any zero or more characters. Underscores (_) can match any single character. A percent sign is a greedy match that matches everything between characters except the character itself. If the percent sign is at the far left or right of the pattern, it will match the other side of the character. As follows:
sqlite> select * from new_contacts where name like '%ac%P%';
id name phone email address
----- -------------------- ---------- ---------- ----------
7 Guacamole Dip UNKNOW no address
8 Peach Schnapps UNKNOW no address
9 Mackinaw Peaches UNKNOW no address
10 Pie (Blackberry) Pie UNKNOW no address
Copy the codeThe above query statement is divided into two parts. The first part is the string containing ac and the second part is the string containing the character starting with p. Ac comes first and the second part is the string containing the character starting with P.
Above we matched strings using the LIKE operator. We can also negate certain patterns by using NOT LIKE, as follows:
sq-> select * from new_contacts where name like '%ac%' and name not like 'p%';
id name phone email address
---------- -------------------------------------------------- ---------- ---------- ----------
7 Guacamole Dip UNKNOW no address
9 Mackinaw Peaches UNKNOW no address
12 ac Pie Pie UNKNOW no address
Copy the codeIn the above query, we want to find the contact name that contains the ac string but does not start with p.
The GLOB operator behaves LIKE LIKE, with the key difference that it resembles the Unix/Linux file name substitution syntax. That is, it replaces the associated wildcards, such as * and? (* stands for one or more,? Represents a single number or character), and matching is case sensitive. As follows:
sq-> select * from new_contacts where name glob 'Pie*';
id name phone email address
---------- -------------------------------------------------- ---------- ---------- ----------
10 Pie (Blackberry) Pie UNKNOW no address
13 Pie accc UNKNOW no address
Copy the code? The match:
sq-> select * from new_contacts where name glob 'P? ie';
id name phone email address
---------- -------------------------------------------------- ---------- ---------- ----------
23 Pcie UNKNOW no address
Copy the codeQualifying and sorting
You can use the limit and offset keywords to limit the size and range of the result set. Limit specifies the maximum number of records to return, and offset specifies the number of offset records. For example, the following command illustrates the third row of the new_contacts table:
sq-> select * from new_contacts limit 1 offset 2;
id name phone email address
---------- -------------------------------------------------- ---------- ---------- ----------
3 eee UNKNOW no address
Copy the codeThe offset keyword specifies the specified number of rows to skip in the result set (the first two lines were skipped in the command above), and the limit keyword limits how many rows can be returned (the command above specifies that a maximum of one row can be returned).