SELECT
SELECT
- Syntax Format
1 2 3 4 5 6
SELECT [ ALL | DISTINCT ] { * | projectItem [, projectItem ]* } FROM tableExpression [ WHERE booleanExpression ] [ GROUP BY { groupItem [, groupItem ]* } ] [ HAVING booleanExpression ]
- Syntax Description
SELECT is used to select data from a table.
ALL indicates that all results are returned.
DISTINCT indicates that the duplicated results are removed.
- Precautions
- The table to be queried must exist. Otherwise, an error is reported.
- WHERE is used to specify the search condition, which can be the arithmetic operator, relational operator, or logical operator.
- GROUP BY is used to specify the grouping field, which can be one or more multiple fields.
- Example
Select the order which contains more than 3 pieces of data.
1
SELECT * FROM Orders WHERE units > 3;
Queries a group of constant data.
1
SELECT 'Lily', 'male', 'student', 17;
DISTINCT
- Function
In a table, a column may contain multiple duplicate values. Sometimes, you may want to list only different values. The DISTINCT keyword is used to return only unique values.
- Syntax Format
1 2 3 4 5 6
SELECT [ ALL | DISTINCT ] { * | projectItem [, projectItem ]* } FROM tableExpression [ WHERE booleanExpression ] [ GROUP BY { groupItem [, groupItem ]* } ] [ HAVING booleanExpression ]
- Example
Query all asset types and deduplicate data based on type.
SELECT DISTINCT type FROM security_resource;
WHERE
- Syntax Format
1 2 3
SELECT { * | projectItem [, projectItem ]* } FROM tableExpression [ WHERE booleanExpression ]
- Syntax Description
This clause is used to filter the query results using the WHERE clause.
- Precautions
- The to-be-queried table must exist.
- WHERE filters the records that do not meet the requirements.
- Operators
Operator
Description
=
Queries logs with the value of a specific field equal to a given value.
<>
Queries logs with the value of a specific field not equal to a given value.
>
Queries logs with the value of a specific field greater than a given value.
<
Queries logs with the value of a specific field less than a given value.
>=
Queries logs with the value of a specific field equal to or greater than a given value.
<=
Queries logs with the value of a specific field equal to or less than a given value.
IN ('value1', 'value2')
Queries logs with the value of a specific field set to value1 or value2.
BETWEEN value1 and value2
Queries logs with the value of a specific field within the range of value1 and value2.
LIKE
Queries logs with a fuzzy keyword, for example, like '%one%'. The percent sign (%) is a wildcard that can replace any character.
IS NULL
Queries logs with the value of a specific field set to NULL.
IS NOT NULL
Queries logs with the value of a specific field set to NOT NULL.
- Example
Search orders which contain more than 3 pieces and fewer than 10 pieces of data.
1 2
SELECT * FROM Orders WHERE units > 3 and units < 10;
IN Operator
- Syntax Format
1 2 3
SELECT [ ALL | DISTINCT ] { * | projectItem [, projectItem ]* } FROM tableExpression WHERE column_name IN (value (, value)* ) | query
- Syntax Description
The IN operator allows multiple values to be specified in the WHERE clause. It returns true if the expression exists in the given table subquery.
- Precautions
The subquery table must consist of a single column, and the data type of the column must be the same as that of the expression.
- Example
Return user and amount information of the products in NewProducts of the Orders table.
1 2 3 4 5
SELECT user, amount FROM Orders WHERE product IN ( SELECT product FROM NewProducts );
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot