SQL in SeaTable¶

SELECT

The SELECT statement allows you to retrieve an eventually filtered, sorted and/or grouped list of the rows from a specific table. Each returned row is a JSON object. The keys of the object are the column keys, NOT the column names. To use column names as keys, the convert_keys parameter (available since version 2.4) in query request should be true (which is the default value when using base.query for both JavaScript and Python scripts). The syntax of SELECT statement is:

SELECT [Column List] FROM tableName [Where Clause] [Group By Clause] [Having Clause] [Order By Clause] [Limit Option]

[Column List] is the list of columns you want to retrieve, separated by commas. If you want to retrieve all the columns, you can use a wildcard (*). You can consult specific sections for Where, Group By, Having or Order By clauses Limit Option uses MySQL format. The general syntax is LIMIT ... OFFSET .... This parameters are optional. Unless you specify a higher limit, the method returns a maximum of 100 rows. The maximum number of rows returned is 10000 no matter the limit specified in the SQL statement. The OFFSET will help you retrieving the following rows in other queries

Example SELECT * FROM Table1 LIMIT 10000 returns the first 10000 rows, SELECT * FROM Table1 LIMIT 10000 OFFSET 10000 returns the next 10000 rows

Since version 4.3, basic implicit join query is supported, for example:

SELECT ... FROM Table1, Table2 WHERE Table1.column1 = Table2.column2 AND ...

The join queries have the following restrictions:

• You must not explicitly write JOIN keyword
• Only inner join is supported; left join, right join, and full join are not supported.
• Tables in the FROM clause should be unique (no duplicate tables).
• Each table in the FROM clause should be associated with at least one join condition.
• Join conditions should be placed in the WHERE clause, and eventually connected with one or more AND operators.
• Join conditions can only use equality operator on columns, e.g. Table1.column1 = Table2.column2.
• Columns in join conditions must be indexed, unless the table is not archived.

INSERT

INSERT allows you to append a new row to a table. INSERT statement only supports bases that have been archived. The rows will be inserted into big-data storage. It'll return error if the base is not archived yet.

If you want to insert rows in a non-archived base, please use the API dedicated functions (e.g. the Python API).

INSERT INTO table_name [column_list] VALUES value_list [, ...]

• column_list is a list of column names surrounded by parentheses. If omitted, it defaults to all updatable columns.
• value_list is a list of values surrounded by parentheses. Values must be in the same order as the column list, for example: (1, "2", 3.0).
• Columns with multiple values, such as "multiple select"-type column , requires values to be surrounded by parentheses, for example: (1, "2", 3.0, ("foo", "bar")).
• Values of "single select" and "multiple select"-type columns must be option names, not option keys.

• Few column types are not allowed to insert:

  • built-in columns, such as _id, _ctime.
  • image, file, formula, link, link-formula, geolocation, auto-number, button

Example

INSERT INTO Table1 (Name, Age) values ('Erika', 38)

UPDATE

UPDATE allows you to update one or multiple existing rows of a table. Unlike the INSERT statement, UPDATE allows you to update rows in both normal and big-data storage. WhereClause is optional. However, keep in mind that if omitted, all rows will be updated!

UPDATE table_name SET column_name = value [, ...] [WhereClause]

• Columns with multiple values, such as "multiple select"-type column , requires values to be surrounded by parentheses, for example: ("foo", "bar").
• Values of "single select" and "multiple select"-type columns must be option names, not option keys.

• Few column types are not allowed to update:

  • built-in columns, such as _id, _ctime.
  • image, file, formula, link, link-formula, geolocation, auto-number, button

Example

UPDATE INTO Contacts SET Adult=true WHERE Age>=18

Example

UPDATE INTO Contacts SET Adult=true, `Age group`="18+" WHERE Age>=18

If Age group is a "single select"-type column, the option you want to select (here "18+") has to exist already.

DELETE

DELETE allows you to delete one or multiple existing rows of a table. Unlike the INSERT statement, DELETE allows you to delete rows in both normal and big-data storage. WhereClause is optional. However, keep in mind that if omitted, all rows will be deleted!

DELETE FROM table_name [WhereClause]

Example

DELETE FROM Contacts WHERE Age<18

WHERE clause

Most SQL syntax can be used in the WHERE clause, including arithmetic expressions, comparison operators, [NOT] LIKE, IN, BETWEEN ... AND ..., AND, OR, NOT, IS [NOT] TRUE, IS [NOT] NULL.

• Arithmetic expressions only support numbers.
• Time constants should be strings in ISO format (e.g. "2020-09-08 00:11:23"). Since 2.8 version, strings in RFC 3339 format are supported (such as "2020-12-31T23:59:60Z").

GROUP BY clause

GROUP BY uses strict syntax. The selected fields must appear in the clause list, except for aggregation functions (COUNT, SUM, MAX, MIN, AVG) and formulas (see extended syntax section below).

HAVING clause

HAVING filters rows resulting from the GROUP BY clause. Only fields referred in the GROUP BY clause or aggregation functions (such as "SUM") can be used in HAVING clause. Other syntax is the same as specified for the WHERE clause.

ORDER BY clause

Fields in ORDER BY list must be a column or an expression in the selected fields. For example, select a from table order by b is invalid; while select a, b from table order by b and select abs(a), b from table order by abs(a) are valid.

LIKE operator

LIKE only supports strings. The key word ILIKE can be used instead of LIKE to make the match case insensitive. The percent sign % you will use in the LIKE expression represents zero, one, or multiple characters.

Example

SELECT `Full Name` from Contacts WHERE `Full Name` LIKE "% M%"

BETWEEN operator

BETWEEN lowerLimit AND upperLimit only supports numbers and time. lowerLimit and upperLimit are included in the search. They have to be in the right order (if upperLimit<lowerLimit, no records will be found).

Example

SELECT * from Contacts WHERE Age BETWEEN 18 AND 25