Overview

A query (SQL) is a method for retrieving data from a database. It can be combined with clauses such as WHERE (for condition restrictions) and ORDER BY (for sorting) to obtain query results. This section introduces the basic operations and components of queries.

Basic operations of queries

Queries are essentially operations on relations. In a database, relations are stored in tables, where each row is a tuple (member) of the set, and each column is an attribute of the set.

The basic operations of relations include:

• Selection: Selects tuples that meet specified conditions from a relation. In a database, selection involves selecting certain rows from a table.

  SELECT * FROM t WHERE c1 <10;

• Projection: Selects attributes that meet specified conditions from a relation. In a database, projection involves selecting certain columns from a table.

  SELECT c1,c2 FROM t;

• Join: Selects tuples from the Cartesian product of two relations that meet certain conditions. In a database, join involves selecting tuples from the Cartesian product of two tables that meet specified conditions.

  SELECT t1.c2,t2.c4 FROM t1 JOIN t2 WHERE t1.c1 = t2.c3;

Components of queries

The basic syntax of a query is as follows, where att_name is the attribute name and rel_name is the relation name.

SELECT att_name FROM rel_name;

A relation consisting of a single tuple is called a singleton relation, and a tuple consisting of a single element is called a single-element tuple. In other words, a single element can also be considered a relation, so the simplest query can be a single element. The following example is a valid query.

SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.001 sec)

In the query result, the 1 above is the attribute name of the element, and the 1 below is the value of the element.

seekdb supports the FROM DUAL syntax, where DUAL is a virtual table used to construct the syntax rules of SELECT.

SELECT 1 FROM DUAL;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.001 sec)

FROM clause

The FROM clause specifies the name of the relation to be queried. Since the result of a query is also a relation, you can reference the result of a query as the relation specified in the FROM clause.

SELECT * FROM (SELECT 1 FROM DUAL);
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.001 sec)

WHERE clause

The WHERE clause specifies the conditions for the tuples to be queried. The WHERE statement compares each attribute of the tuples to see if it meets the conditions and returns the tuples for which the comparison result is TRUE. For example, in the following query, if the WHERE condition is FALSE or NULL, it will always return an empty set.

SELECT * FROM (SELECT 1 FROM DUAL) WHERE TRUE;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.001 sec)

SELECT * FROM (SELECT 1 FROM DUAL) WHERE FALSE;
Empty set

SELECT * FROM (SELECT 1 FROM DUAL) WHERE NULL;
Empty set

ORDER BY clause

The ORDER BY clause is used to sort the query results. You can specify several columns c1,c2,... for sorting, and the query results will be sorted in order of these columns.

CREATE TABLE t (c1 INTEGER,c2 INTEGER);
SELECT * FROM t ORDER BY c1,c2;

seekdb supports using numbers 1,2,... to refer to the first, second, etc., columns of the relation t. Therefore, you can also rewrite the above query as:

SELECT * FROM t ORDER BY 1,2;

GROUP BY clause

The GROUP BY clause is used to group the query results. You can specify several columns c1,c2,... for grouping, and the query results will be grouped in order of these columns.

CREATE TABLE t (c1 INTEGER,c2 INTEGER);
SELECT * FROM t GROUP BY c1,c2;

seekdb supports using numbers 1,2,... to refer to the first, second, etc., columns of the relation t. Therefore, you can also rewrite the above query as:

SELECT * FROM t GROUP BY 1,2;

LIMIT clause

The LIMIT clause is used to limit the number of rows in the query results. The LIMIT clause has two syntaxes:

• LIMIT N: Returns only the first N rows of the query results.

• LIMIT N,M: Returns M rows starting from the N+1 row of the query results.

HAVING clause

The HAVING clause is similar to the WHERE clause, but the HAVING clause can be used with aggregate functions.