All Products
Search
Document Center

Simple queries

Last Updated: Jun 18, 2021

A simple query is an operation that retrieves data from one or more columns of data from one or more tables or views in ApsaraDB for OceanBase. The number of columns and their data type and length depend on the schema. Select lists are the expression lists that appear after the SELECT keyword and before the FROM clause.

Syntax

SELECT column name 1,column name 2,column name 3,... FROM table;

Table names, field names, and keywords SELECT and FROM are not case-sensitive. The end of a query can be followed by a semicolon (;). Multiple SQL statements can be executed at the same time. You can use hints in a SELECT statement to pass instructions or hints to the ApsaraDB OceanBase database optimizer. The optimizer selects execution plans for statements by using hints.

Examples

Create an employee table 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);
Simple query example
  • Query some columns:

SELECT first_name, last_name, salary FROM employee;
+------------+-----------+--------+
| FIRST_NAME | LAST_NAME | SALARY |
+------------+-----------+--------+
| DEL        | FA BEN    |   1500 |
| AXEL       | BELL      |   1000 |
| CRIS       | RACHAR    |   1000 |
+------------+-----------+--------+
  • Query all the columns:

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 |
+-------------+------------+-----------+------------+--------+
  • Perform a mathematical operation on a column:

SELECT salary+100 FROM employee;
+------------+
| SALARY+100 |
+------------+
|       1600 |
|       1100 |
|       1100 |
+------------+
  • Create an alias for a column:

SELECT salary*12 Annual salary FROM employee;
+--------+
| Annual salary   |
+--------+
|  18000 |
|  12000 |
|  12000 |
+--------+
  • Concatenate strings:

SELECT first_name || '-' || last_name  AS Full name FROM employee;
+-------------+
| Full name        |
+-------------+
| DEL-FA BEN  |
| AXEL-BELL   |
| CRIS-RACHAR |
+-------------+
  • Remove duplicates from data

SELECT DISTINCT MANAGER_ID FROM employee;
+------------+
| MANAGER_ID |
+------------+
|          1 |
+------------+
  • CASE WHEN statement
SELECT salary, CASE WHEN salary >= 1000 then 'High salary >= 800 THEN 'General' 
ELSE 'Keeping working hard' END AS typeFROM employee;
+--------+--------+
| SALARY | TYPE   |
+--------+--------+
|   1500 | High salary   |
|   1000 | High salary   |
|   1000 | High salary   |
+--------+--------+