Single-table queries|V3.2.4|OceanBase Database| docs|Distributed Database

Single-table queries

Last Updated：2023-10-27 09:57:43 Updated

A single-table SQL query is performed in only one table.

Syntax

Syntax for single-table queries:

SELECT [ALL | DISTINCT] select_list FROM table_name
              [ WHERE query_condition ]
              [ GROUP BY group_by_expression ]
              [ HAVING group_condition ]
              [ ORDER BY column_list ][ASC | DESC]
              [ LIMIT limit_clause ]

column_list:
 column_name[,column_name...]

If you use the WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT keywords in a query, make sure that you follow the specified sequence for executing these keywords:

Execute FROM to find the required table.
Execute WHERE to specify conditions.
Execute GROUP BY to group all records. If GROUP BY is not executed, the table is considered as a group.
Execute HAVING to filter the group result.
Execute SELECT.
Execute DISTINCT to remove duplicate rows.
Execute ORDER BY to sort the results in ascending or descending order.
Execute LIMIT to limit the number of records displayed per page.

Note the difference between WHERE and HAVING. WHERE filters data before grouping (aggregation), whereas HAVING filters data after grouping and the entire SQL query result is returned.

SELECT queries

Create tables named emp and dept and insert proper data.

CREATE TABLE emp(  
    empno         NUMBER(4,0),  
    empname       VARCHAR(10),  
    job           VARCHAR(9),  
    mgr           NUMBER(4,0),  
    hiredate      DATE,  
    sal           NUMBER(7,2),  
    comm          NUMBER(7,2),        
    deptno        NUMBER(2,0),   
    CONSTRAINT PK_emp PRIMARY KEY (empno), 
    CONSTRAINT FK_deptno  FOREIGN KEY (deptno)  REFERENCES dept (deptno)  
);

CREATE TABLE dept(  
    deptno           NUMBER(2,0),  
    dname            VARCHAR(14),  
    location         VARCHAR(13),   
    CONSTRAINT pk_dept PRIMARY KEY(deptno)  
);

Query all columns

An asterisk (*) indicates that all fields in the tables are returned. Sample code:

obclient> SELECT * FROM emp;
+-------+---------+----------+------+------------+---------+--------+--------+
| empno | empname | job      | mgr  | hiredate   | sal     | comm   | deptno |
+-------+---------+----------+------+------------+---------+--------+--------+
|  1369 | SMITH   | CLERK    | 1902 | 1980-12-17 |  800.00 |   NULL |     20 |
|  1499 | ALLEN   | SALESMAN | 1698 | 1981-02-20 | 1600.00 | 300.00 |     30 |
|  1566 | JONES   | MANAGER  | 1839 | 1981-04-02 | 2975.00 |   NULL |     40 |
|  1698 | BLAKE   | MANAGER  | 1839 | 1981-05-01 | 2850.00 |   NULL |     30 |
|  1788 | SCOTT   | ANALYST  | 1566 | 1987-07-15 | 3000.00 |   NULL |     20 |
|  1902 | FORD    | ANALYST  | 1566 | 1981-12-05 | 3000.00 |   NULL |     40 |
+-------+---------+----------+------+------------+---------+--------+--------+
6 rows in set

Equivalent to:

obclient> SELECT empname,empno,job,mgr,hiredate,sal,comm,deptno FROM emp;

Query specified columns

obclient> SELECT empname, deptno FROM emp;
+---------+--------+
| empname | deptno |
+---------+--------+
| SMITH   |     20 |
| ALLEN   |     30 |
| JONES   |     40 |
| BLAKE   |     30 |
| SCOTT   |     20 |
| FORD    |     40 |
+---------+--------+
6 rows in set

You can set an alias for the column in the query. Sample code:

obclient> SELECT empname AS employee name, deptno AS department No. FROM emp;
+--------------+--------------+
| Employee name     | Department No.     |
+--------------+--------------+
| SMITH        |           20 |
| ALLEN        |           30 |
| JONES        |           40 |
| BLAKE        |           30 |
| SCOTT        |           20 |
| FORD         |           40 |
+--------------+--------------+
6 rows in set

You can remove duplicate rows in the query. Sample code:

obclient> SELECT deptno FROM emp;
+---------+
| deptno |
+---------+
|     20 |
|     30 |
|     40 |
|     30 |
|     20 |
|     40 |
+--------+
6 rows in set

obclient> SELECT DISTINCT deptno FROM emp;
+---------+
| deptno |
+---------+
|     20 |
|     30 |
|     40 |
+---------+
3 rows in set

You can use the LIMIT clause to limit the number of rows returned per page. This feature is used to specify the number of pages to be returned. Sample code:

obclient> SELECT empname, deptno FROM emp LIMIT 3;
+---------+--------+
| empname | deptno |
+---------+--------+
| SMITH   |     20 |
| ALLEN   |     30 |
| JONES   |     40 |
+---------+--------+
3 rows in set

Query the calculated value

obclient> SELECT empname, sal-100,job FROM emp;
+---------+---------+--------+
| empname | sal-100 | deptno |
+---------+---------+--------+
| SMITH   |  700.00 |     20 |
| ALLEN   | 1500.00 |     30 |
| JONES   | 2875.00 |     40 |
| BLAKE   | 2750.00 |     30 |
| SCOTT   | 2900.00 |     20 |
| FORD    | 2900.00 |     40 |
+---------+---------+--------+
6 rows in set

You can apply functions to specified columns in the query. For example, in the following sample code, job is converted to lowercase. For more information, see Use operators and functions in a query.

obclient> SELECT empname, sal-100, LOWER(job) FROM emp;
+---------+---------+---------------+
| empname | sal-100 | LOWER(deptno) |
+---------+---------+---------------+
| SMITH   |  700.00 | 20            |
| ALLEN   | 1500.00 | 30            |
| JONES   | 2875.00 | 40            |
| BLAKE   | 2750.00 | 30            |
| SCOTT   | 2900.00 | 20            |
| FORD    | 2900.00 | 40            |
+---------+---------+---------------+
6 rows in set

The following clauses can be used to query specified columns.

WHERE. For more information, see WHERE queries.
ORDER BY. For more information, see ORDER BY queries.
GROUP BY. For more information, see GROUP BY queries.