Skip to main content

SQL Command Syntax

This section covers basic syntax for SQL in a SingleStoreDB environment, including:

  • Arrays

  • Identifiers

  • Parameters

  • Operators

  • Strings

  • Variables

Arrays

Arrays are a data type which consists of elements that are the same data type (i.e., alpha, numeric, etc.).

Arrays must be of the same type type. You cannot have integers and alphabetic in the same array.

Example

Acceptable formats:

sample_int ARRAY(INT) = [1,2,3,4];

sample_varchar ARRAY(VARCHAR(25)) = ["apples", "oranges", "bananas"];

Unacceptable formats:

sample_int ARRAY(VARCHAR(10)) = [1,"apple",2, "oranges", 3,"bananas"];

Note

For more detailed information about arrays see ARRAY.

Identifiers

Identifiers are the names given by a database creator or user to database objects. Some examples are tables, columns, indexes, views, aliases, and other objects.

Identifiers can start with capital or lowercase letters, a dollar sign, or a combination of letters and numbers. Identifiers cannot have a solely numeric name. The use of decimals, symbols, and all numeric names requires back tics.

Examples

Acceptable formats:

CREATE TABLE Assets_104;
CREATE TABLE 104_Assets;
CREATE TABLE Assets$104;
CREATE TABLE `1.04_assets`;
CREATE TABLE `99999_assets`;
CREATE TABLE `#@*_assets`;
CREATE TABLE `*&%$@`;

Unacceptable formats:

CREATE TABLE 104;
CREATE TABLE 1.04_assets;
CREATE TABLE assets_1.04;

Operators

Operators are either a reserved character or word used mainly with the WHERE clause to perform comparative operations. SingleStoreDB uses most of the standard mathematic, bit, and comparison operators. Below are examples of the syntax. For a complete list see: Comparison Operators and Functions.

Examples

Acceptable formats:

SELECT 3&1;
SELECT 1<<2;
SELECT 4>>2;
SELECT 1^1;
SELECT 11^3;
SELECT 29|15;

Parameters

A parameter is used to exchange data among stored procedures and functions.

Examples

CREATE FUNCTION num_parameter(emp_id INT);

Note

For detailed information on parameters see: CREATE FUNCTION (UDF).

Strings

A string is a data type that is used to store data in a table. Strings are enclosed by single or double quotes. There are a variety of sting functions and details which may be found here: String Functions.

Examples

Basic string function format is shown below:

SELECT 'exp_str\3 exp_1';

SELECT "exp_str\3 exp_1";

Variables

A variable is an object that can be a single data value or a specific type (i.e., date, integer, etc.). Variables can start with capital or lowercase letters, a dollar sign, or a combination of letters and numbers. Variables cannot have a solely numeric name. The use of decimals, symbols, and all numeric names requires back tics.

Examples

Acceptable formats:

SET @str = CAST(123 AS CHAR(5));
SET @var = 0;
SET @$Some7_var = 0;
SET @`29999` = 1010;

Unacceptable formats:

SET @'var_name' =1010;
SET @"var_name" = 1010;
SET @29999 = 1010;