A query (also known as an SQL statement) is a means used to obtain data in a database. It can be used with various clauses, such as conditional clauses (such as WHERE) and order clauses (such as ORDER BY) to get the query results. This topic describes the basic operations and components of queries.
Basic operations of queries
Queries are essentially relational operations. In databases, relations are stored in tables. Each row is a tuple of a set, and each column is an attribute of a set.
Basic relational operations include:
Selection: selects several tuples that meet the specified condition from a relation. In a database, this operation selects several rows from a table.
obclient> SELECT * FROM t WHERE c1 <10;Projection: selects several attributes that meet the specified condition from a relation. In a database, this operation selects several columns from a table.
obclient> SELECT c1,c2 FROM t;Join: selects a tuple that meets the specified condition from the Cartesian product of two relations. In a database, this operation selects objects that meet the specified condition from the Cartesian product of two tables.
obclient> SELECT t1.c2,t2.c4 FROM t1 JOIN t2 WHERE t1.c1 = t2.c3;
Components of queries
In a query, att_name specifies the attribute name, and rel_name specifies the relation name. Syntax:
SELECT att_name FROM rel_name;
A relation composed of a single tuple is called a single-tuple relation. A relation composed of a single element is called a single-element tuple. That is, a single element can serve as a relation. Therefore, a basic query can contain only a single element. The query in the following example is valid.
obclient> SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set
In the query results, the first value 1 indicates the attribute name of the element, and the second value 1 indicates the value of the element.
OceanBase Database supports the FROM DUAL syntax. DUAL indicates a virtual table and is used to constitute the syntax rules of the SELECT statement.
obclient> SELECT 1 FROM DUAL;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set
FROM clause
The FROM clause specifies the relation name of the query. Because the query results are also a relation, you can use the query results as the relation referenced by the FROM clause.
obclient> SELECT * FROM (SELECT 1 FROM DUAL);
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set
WHERE clause
The WHERE clause specifies the condition for querying tuples. It checks whether a specific attribute of each tuple meets the condition, and returns the tuples whose comparison results are TRUE. For example, in the following query, an empty set is always returned when the WHERE condition is FALSE or NULL.
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 sorts the query results. You can specify several columns c1,c2,... for sorting, so that the query results are sorted by column.
obclient> CREATE TABLE t (c1 INTEGER,c2 INTEGER);
obclient> SELECT * FROM t ORDER BY c1,c2;
OceanBase Database allows you to use numbers 1,2,... to indicate the first column, the second column, and so on in the relation t. Therefore, the preceding query statement can be rewritten as follows:
obclient> SELECT * FROM t ORDER BY 1,2;
GROUP BY clause
The GROUP BY clause groups the query results. You can specify several columns c1,c2,... for grouping, so that the query results are grouped by column.
obclient> CREATE TABLE t (c1 INTEGER,c2 INTEGER);
obclient> SELECT * FROM t GROUP BY c1,c2;
OceanBase Database allows you to use numbers 1,2,... to indicate the first column, the second column, and so on in the relation t. Therefore, the preceding query statement can be rewritten as follows:
obclient> SELECT * FROM t GROUP BY 1,2;
LIMIT clause
The LIMIT clause specifies the maximum number of rows in the query results. Syntax:
LIMIT N: returns only the firstNrows in the query results.LIMIT N,M: returnsMrows starting from theN+1th row in the query results.
HAVING clause
The HAVING clause is similar to the WHERE clause, but the HAVING clause applies to aggregate functions.