A simple query refers to a query that retrieves data of one or multiple columns from one or multiple select lists or views in OceanBase Database. The number of columns, as well as their data type and length, are subject to the elements of the select list. A select list is the list of expressions between the SELECT keyword and the FROM clause.
If two or more tables contain columns with identical names, the column names must be qualified by table names. You can use a column alias (col_alias) to mark the expression that precedes the select list. You can use an alias in the ORDER BY clause, but not in other clauses of a query statement.
Syntax
Syntax of SELECT for a simple query:
SELECT column 1,column 2,column 3,... FROM table;
In the syntax, the table name, column names, and keywords SELECT and FROM are case-insensitive. A query statement can end with a semicolon (;). Multiple SQL statements can be executed concurrently. You can use a comment in the SELECT statement to transfer a directive or hint to the optimizer of OceanBase Database. The optimizer uses the hint to select an execution plan for the statement. For more information about hints, see Hint.
Examples
Create an employee table named employee, and insert data into the employee_id, first_name, last_name, manager_id, and salary columns.
CREATE TABLE employee (
employee_id INT,
first_name VARCHAR(50),
last_name VARCHAR(50),
manager_id INT,
salary NUMERIC
);
INSERT INTO employee VALUES(111, 'DEL', 'FA BEN', 1, 1500);
INSERT INTO employee VALUES(112, 'AXEL', 'BELL', 1, 1000);
INSERT INTO employee VALUES(113, 'CRIS', 'RACHAR', 1, 1000);
INSERT INTO employee VALUES(114, 'BlAIR', 'WALDORF', 1, 800);
Example 1: Query specified columns in the table.
obclient> SELECT first_name, last_name, salary FROM employee;
+------------+-----------+--------+
FIRST_NAME LAST_NAME SALARY
+------------+-----------+--------+
DEL FA BEN 1500
AXEL BELL 1000
CRIS RACHAR 1000
BlAIR WALDORF 800
+------------+-----------+--------+
4 rows in set
Example 2: Query all columns in the table.
obclient> SELECT * FROM employee;
+-------------+------------+-----------+------------+--------+
EMPLOYEE_ID FIRST_NAME LAST_NAME MANAGER_ID SALARY
+-------------+------------+-----------+------------+--------+
111 DEL FA BEN 1 1500
112 AXEL BELL 1 1000
113 CRIS RACHAR 1 1000
114 BlAIR WALDORF 1 800
+-------------+------------+-----------+------------+--------+
4 rows in set
Example 3: Perform arithmetical operations on a column in the table.
obclient> SELECT salary+100 FROM employee;
+------------+
SALARY+100
+------------+
1600
1100
1100
900
+------------+
4 rows in set
Example 4: Specify an alias for a column in the table.
obclient> SELECT annual salary*12 salary FROM employee;
+--------+
Annual salary
+--------+
18000
12000
12000
9600
+--------+
4 rows in set
Example 5: Concatenate the strings in the table.
obclient> SELECT first_name '-' last_name AS Name FROM employee;
+---------------+
Name
+---------------+
DEL-FA BEN
AXEL-BELL
CRIS-RACHAR
BlAIR-WALDORF
+---------------+
4 rows in set
Example 6: Deduplicate the data in the table.
obclient> SELECT DISTINCT MANAGER_ID FROM employee;
+------------+
MANAGER_ID
+------------+
1
+------------+
1 row in set
Example 7: Use the CASE WHEN statement to query the data in the table.
obclient> SELECT salary, CASE WHEN salary >= 1200 then 'High' WHEN salary >= 1000 THEN 'General'
ELSE 'Keep trying' END AS type FROM employee;
+--------+--------------+
SALARY TYPE
+--------+--------------+
1500 High
1000 General
1000 General
800 Keep trying
+--------+--------------+
4 rows in set