Basic SELECT Syntax Tutorial
All operations are ANSI SQL-compliant syntax. For an introduction to ANSI SQL, please refer to W3School SQL Tutorial.
For advanced viewing of the complete support items, please click here Complete SELECT Syntax
SELECT
Select Specific Fields
SELECT column1, column2
FROM table_name;
Select all fields
SELECT *
FROM table_name;
WHERE
Select information according to the conditions
SELECT *
FROM table_name
WHERE column1 > value1;
AND, OR
Join multiple conditions
SELECT *
FROM table_name
WHERE column1 > value1 AND column2 > value1 OR column3 > value1;
Operators
<
less thanSELECT *
FROM table_name
WHERE column1 < value1;>
greater thanSELECT *
FROM table_name
WHERE column1 > value1;<=
less than or equal toSELECT *
FROM table_name
WHERE column1 <= value1;>=
greater than or equal toSELECT *
FROM table_name
WHERE column1 >= value1;=
is equal toSELECT *
FROM table_name
WHERE column1 = value1;<>
is not equal toSELECT *
FROM table_name
WHERE column1 <> value1;!=
is not equal toSELECT *
FROM table_name
WHERE column1 != value1;BETWEEN a AND b
between a and bSELECT *
FROM table_name
WHERE column1 BETWEEN value1 AND value2;IN (a, b, c, ...)
is one of a, b, cSELECT *
FROM table_name
WHERE column IN (12, 15, 18)IS NULL
determines whether it isNull
SELECT *
FROM table_name
WHERE column1 IS NULL;
Operators for Specified Types
Below are some common usages for you to search for different types of fields.
UUID
Use
uuid
prefix conversionSELECT * FROM table
WHERE id = uuid 'affa0ba3-2744-47c6-9f2a-35b8e2d56581'Timestamp
Use the
timestamp
prefix to castSELECT * FROM table
WHERE create_at > timestamp '2019-08-01 00:00'Use
date
prefix conversionSELECT * FROM table
WHERE create_at > date '2019-08-01'Combined with
interval
to get other timeSELECT * FROM table
WHERE create_at > date '2019-08-01'
AND create_at < date '2019-08-01' + interval '1' monthVARCHAR
Use
LIKE
with%
to match none or any characterSELECT * FROM (VALUES ('abc'), ('bcd'), ('cde')) AS table_name (column1)
WHERE column1 LIKE '%b%'
--returns 'abc' AND 'bcd'Use
LIKE
with_
to match a single characterSELECT * FROM (VALUES ('abc'), ('bcd'), ('cde')) AS table_name (column1)
WHERE column1 like '_b%'
--returns 'abc'
ORDER BY
Sorted from small to large
SELECT *
FROM table_name
ORDER BY column1 ASC
sorted from largest to smallest
SELECT *
FROM table_name
ORDER BY column1 DESC
NULL
sorted first
SELECT *
FROM table_name
ORDER BY column1 ASC NULLS FIRST
NULL
sorts last
SELECT *
FROM table_name
ORDER BY column1 ASC NULLS LAST
GROUP BY
Group by specific field
SELECT *
FROM table_name
GROUP BY column1
Group by specific field
SELECT count(*)
FROM table_name
GROUP BY column1
HAVING
Filter the grouped data.
SELECT count(column1), column2
FROM table_name
GROUP BY column2
HAVING count(column1) > value1
JOIN
Combine two tables according to specific fields
SELECT *
FROM table1
JOIN table2
ON table1.column1 = table2.column1
##UNION
Merge two tables
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;
LIMIT, OFFSET
LIMIT number
How many records can be obtained at mostOFFSET number
ignores the first few data
Obtain ten records after the 5th record
SELECT *
FROM table_name
OFFSET 5
LIMIT 10