# SQL Overview
SQL is a domain-specific language used in programming and designed for managing data held in a database management system. A standard for the specification of SQL is maintained by the American National Standards Institute (ANSI). Also, there are many variants and extensions to SQL to express more specific programs.
The SQL grammar of HStreamDB (opens new window) is based on a subset of SQL-92 with some extensions to support stream operations.
SQL inputs are made up of a series of statements. Each statement is made up of a series of tokens and ends in a semicolon (
A token can be a keyword argument, an identifier, a literal, an operator, or a special character. The details of the rules can be found in the BNFC grammar file (opens new window). Normally, tokens are separated by whitespace.
The following examples are syntactically valid SQL statements:
SELECT * FROM my_stream; CREATE STREAM abnormal_weather AS SELECT * FROM weather WHERE temperature > 30 AND humidity > 80 WITH (REPLICATE = 3); INSERT INTO weather (cityId, temperature, humidity) VALUES (11254469, 12, 65);
Some tokens such as
WHERE are reserved keywords, which have specific meanings in SQL syntax. Keywords are case insensitive, which means that
select are equivalent. A keyword can not be used as an identifier.
For a complete list of keywords, see the appendix.
Identifiers are tokens that represent user-defined objects such as streams, fields, and other ones. For example,
my_stream can be used as a stream name, and
temperature can represent a field in the stream.
By now, identifiers only support C-style naming rules. It means that an identifier name can only have letters (both uppercase and lowercase letters), digits, and the underscore. Besides, the first letter of an identifier should be either a letter or an underscore.
By now, identifiers are case sensitive, which means that
MY_STREAM are different identifiers.
# Literals (Constants)
Literals are objects with known values before being executed. There are six types of constants: integers, floats, strings, dates, time, and intervals so far.
Integers are in the form of
digits are one or more single-digit integers (0 through 9). Negatives such as
-1 are also supported. Note that scientific notation is not supported yet.
Floats are in the form of
digits . digits. Negative floats such as
-11.514 are supported. Note that
- scientific notation is not supported yet.
- Forms such as
.99are not supported yet.
Strings are arbitrary character series surrounded by double quotes (
"), such as
Dates represent a date exact to a day in the form of
DATE <year>-<month>-<day>, where
<day> are all integer constants. Note that the leading
DATE should not be omitted.
Time constants represent time exact to a second in the form of
TIME <hour>-<minute>-<second>, where
<second> are all integer constants. Note that the leading
TIME should not be omitted.
Intervals represent a time section in the form of
INTERVAL <num> <time_unit>, where
<num> is an integer constant and
<time_unit> is one of
SECOND. Note that the leading
INTERVAL should not be omitted.
INTERVAL 5 SECOND
# Operators and Functions
Functions are special keywords that mean some computation, such as
MIN. And operators are infix functions composed of special characters, such as
For a complete list of functions and operators, see the appendix.
# Special characters
There are some special characters in the SQL syntax with particular meanings:
- Parentheses (
()) are used outside an expression for controlling the order of evaluation or specifying a function application.
- Brackets (
) are used with maps and arrays for accessing their substructures, such as
some_array. Note that it is not supported yet.
- Commas (
,) are used for delineating a list of objects.
- The semicolons (
;) represent the end of a SQL statement.
- The asterisk (
*) represents "all fields", such as
SELECT * FROM my_stream;.
- The period (
.) is used for accessing a field in a stream, such as
A single-line comment begins with
// This is a comment
Also, C-style multi-line comments are supported:
/* This is another comment */