Introduction to Doris SQL Syntax (Compatibility with Standard SQL)

Doris is an open-source columnar storage system that supports multiple SQL standards, including MySQL, PostgreSQL, etc. Understanding Doris SQL syntax is essential for developers and operations personnel using the system.

1. Doris SQL Basics

Doris SQL syntax is similar to standard SQL, with the main difference being its support for multiple data types and operation methods.

1.1 SELECT Operator

The SELECT operator is used to retrieve data from tables.

SELECT * FROM table_name;

This statement retrieves the values of all columns (*) and returns them to the user.

1.2 INSERT Statement

The INSERT statement is used to add new data to a table.

INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2');

This statement inserts the values of specified columns into the table.

2. Doris Table Structures

Doris supports multiple table structures, including row-based, column-based, and hybrid tables.

2.1 Row-based Tables

Row-based tables are the most commonly used table structure type in Doris.

CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  ...
);

This statement creates a row-based table named table_name containing multiple columns.

2.2 Column-based Tables

Column-based tables are another commonly used table structure type in Doris.

CREATE TABLE table_name (
  column1 data_type,
  ...
);

This statement creates a column-based table named table_name containing multiple columns.

3. Doris Keywords

Doris supports many keywords, including SELECT, INSERT, UPDATE, DELETE, etc.

SELECT * FROM table_name;
INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2');
UPDATE table_name SET column1 = 'new_value' WHERE condition;

These statements use Doris keywords for retrieving data from tables, adding new data, updating data, and deleting data, respectively.

4. Doris Data Types

Doris supports multiple data types, including integers, floating-point numbers, dates, characters, etc.

CREATE TABLE table_name (
  column1 INT,
  column2 FLOAT,
  column3 DATE,
  column4 VARCHAR(255),
);

This statement creates a table named table_name containing multiple columns, each with a different data type.

5. Doris Indexes

Doris supports multiple index types, including B-tree indexes, Hash indexes, etc.

CREATE INDEX index_name ON table_name (column1);

This statement creates a B_tree index named index_name targeting the column1 column in the table.

6. Doris Pagination and Sorting

Doris supports various pagination and sorting methods, including LIMIT and ORDER BY, etc.

SELECT * FROM table_name LIMIT 10;
SELECT * FROM table_name ORDER BY column1 DESC;

These statements use Doris LIMIT and ORDER BY keywords for retrieving a specific number of data from tables and sorting data based on specified columns, respectively.

7. Doris Grouping and Aggregation

Doris supports various grouping and aggregation operations, including GROUP BY and HAVING, etc.

SELECT column1, COUNT(*) FROM table_name GROUP BY column1;

This statement uses Doris GROUP BY and HAVING keywords for retrieving data from tables and grouping data based on specified columns, respectively.

8. Doris JOIN Operations

Doris supports various JOIN operations, including inner joins, outer joins, etc.

SELECT * FROM table1 INNER JOIN table2 ON table1.column1 = table2.column1;

This statement uses Doris INNER JOIN keywords for retrieving data from tables and joining based on specified conditions, respectively.

9. Doris Exception Handling

Doris supports various exception handling methods, including TRY-CATCH, etc.

BEGIN TRY
    -- some code here
END CATCH

This statement uses Doris TRY-CATCH keywords for retrieving data from tables and handling errors when they occur, respectively.

10. Doris Performance Optimization

Doris supports various performance optimization methods, including indexes, caching, etc.

CREATE INDEX index_name ON table_name (column1);

This statement uses Doris index keywords for retrieving data from tables and improving performance when using indexes, respectively.