A query (SQL) is a method used in databases to retrieve data. It can be combined with clauses such as the WHERE clause for conditional restrictions and the ORDER BY clause for sorting to obtain the desired query results. This section primarily introduces the basic operations of queries and their composition.
Basic operations of queries
A query is essentially an operation on relations. In a database, relations are stored in the form of tables, where each row is a tuple in the set, and each column is an attribute in the set.
The basic operations on relations include:
Selection: Selects tuples from a relation that satisfy specified conditions. In databases, selection refers to the operation of selecting specific rows from a table.
obclient> SELECT * FROM t WHERE c1 <10;Projection: Selects certain attributes from a relation that meet specified conditions. In databases, projection is the operation of selecting specific columns from a table.
obclient> SELECT c1,c2 FROM t;Join: Select tuples from the Cartesian product of two relations that satisfy certain conditions between the selected attributes. In databases, a join is an operation that selects tuples from the Cartesian product of two tables that meet specified conditions.
obclient> SELECT t1.c2,t2.c4 FROM t1 JOIN t2 WHERE t1.c1 = t2.c3;
Components of a query
The basic syntax for 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 unit tuple relation. A tuple consisting of a single element is called a singleton tuple. In other words, a single element can also serve as a relation, so the simplest query can be just a single element. The query in the following example is valid.
obclient> SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set
In the query result, the 1 above is the attribute name of the element, and the 1 below is the value of the element.
OceanBase AI Database supports the FROM DUAL syntax. DUAL is a virtual table used to construct the syntax rules for SELECT.
obclient> SELECT 1 FROM DUAL;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set
FROM clause
The FROM clause is used to specify the name of the relation being queried. Since the result of a query is also a relation, you can reference the query result as a relation in the FROM clause.
obclient> SELECT * FROM (SELECT 1 FROM DUAL);
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set
WHERE clause
The WHERE clause is used to specify the conditions for querying tuples. The WHERE statement compares each attribute of a tuple against its condition and returns tuples whose comparison result is TRUE. For example, in the query below, if the WHERE condition is FALSE or NULL, it will always return an empty set.
obclient> SELECT * FROM (SELECT 1 FROM DUAL) WHERE TRUE;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set
obclient> SELECT * FROM (SELECT 1 FROM DUAL) WHERE FALSE;
Empty set
obclient> 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 one or more columns c1, c2, ... for sorting, so that the query results are sorted in order of these columns.
obclient> CREATE TABLE t (c1 INTEGER,c2 INTEGER);
obclient> SELECT * FROM t ORDER BY c1,c2;
In OceanBase AI Database, you can use numbers 1, 2, ... to refer to the first, second columns of a relation t, and so on. Therefore, the preceding query can also be rewritten as:
obclient> SELECT * FROM t ORDER BY 1,2;
GROUP BY clause
The GROUP BY clause is used to group the query results. You can specify multiple columns for grouping, such as c1, c2, ..., so that the query results are grouped in sequence according to these columns.
obclient> CREATE TABLE t (c1 INTEGER,c2 INTEGER);
obclient> SELECT * FROM t GROUP BY c1,c2;
In OceanBase AI Database, numbers 1, 2, ... can be used to refer to the first, second columns of a relation t, and so on. Therefore, the preceding query can also be rewritten as:
obclient> SELECT * FROM t GROUP BY 1,2;
LIMIT clause
The LIMIT clause is used to limit the number of rows returned by a query. The LIMIT statement has two syntaxes:
LIMIT N: Returns only the firstNrows of the query result.LIMIT N,M: Returns theMrows starting from theN+1row of the query results.
HAVING clause
The HAVING clause is similar to the WHERE clause, but aggregate functions can be used in the HAVING clause.