DefaultSupportedSQLsyntax-SummarySQLStatements
CALL <function>
CREATE [TEMP] TABLE <table> <column_def_clause>
CREATE [TEMP] TABLE <table> AS <select statement>
CREATE [TEMP] TABLE <table> AS IMPORT()
CREATE FUNCTION <user_defined_function> [ NAME <perl_subroutine> ]
CREATE KEYWORD <user_defined_keyword> [ NAME <perl_subroutine> ]
CREATE OPERATOR <user_defined_operator> [ NAME <perl_subroutine> ]
CREATE TYPE <user_defined_type> [ NAME <perl_subroutine> ]
DELETE FROM <table> [<where_clause>]
DROP TABLE [IF EXISTS] <table>
DROP FUNCTION <function>
DROP KEYWORD <keyword>
DROP OPERATOR <operator>
DROP TYPE <type>
INSERT [INTO] <table> [<column_list>] VALUES <value_list>
LOAD <user_defined_functions_module>
SELECT <function>
SELECT <select_clause>
<from_clause>
[<where_clause>]
[ ORDER BY ocol1 [ASC|DESC], ... ocolN [ASC|DESC]] ]
[ GROUP BY gcol1 [, ... gcolN] ]
[ LIMIT [start,] length ]
UPDATE <table> SET <set_clause> [<where_clause>]
ExplicitJoinQualifiers
NATURAL, INNER, OUTER, LEFT, RIGHT, FULL
Built-inFunctions
* Aggregate : MIN, MAX, AVG, SUM, COUNT
* Date/Time : CURRENT_DATE, CURDATE, CURRENT_TIME, CURTIME, CURRENT_TIMESTAMP, NOW,
UNIX_TIMESTAMP
* String : ASCII, CHAR, BIT_LENGTH, CHARACTER_LENGTH, CHAR_LENGTH, COALESCE,
NVL, IFNULL, CONV, CONCAT, DECODE, HEX, OCT, BIN, INSERT, LEFT, RIGHT,
LOCATE, POSITION, LOWER, UPPER, LCASE, UCASE, LTRIM, RTRIM, OCTET_LENGTH,
REGEX, REPEAT, REPLACE, SOUNDEX, SPACE, SUBSTITUTE, SUBSTRING, SUBSTR,
TRANSLATE, TRIM, UNHEX
* Numeric : ABS, CEILING, CEIL, FLOOR, ROUND, EXP, LOG, LN, LOG10, MOD, POWER,
RAND, SIGN, SQRT, TRUNCATE, TRUNC
* Trig : ACOS, ACOSEC, ACOSECH, ACOSH, ACOT, ACOTAN, ACOTANH, ACOTH, ACSC,
ACSCH, ASEC, ASECH, ASIN, ASINH, ATAN, ATAN2, ATANH, COS, COSEC,
COSECH, COSH, COT, COTAN, COTANH, COTH, CSC, CSCH, DEG2DEG, DEG2GRAD,
DEG2RAD, DEGREES, GRAD2DEG, GRAD2GRAD, GRAD2RAD, PI, RAD2DEG, RAD2GRAD,
RAD2RAD, RADIANS, SEC, SECH, SIN, SINH, TAN, TANH
* System : DBNAME, USERNAME, USER
SpecialUtilityFunctions
* IMPORT - imports a table from an external RDBMS or perl structure
* RUN - prepares and executes statements in a file of SQL statements
OperatorsandPredicates
= , <> , < , > , <= , >= , IS [NOT] (NULL|TRUE|FALSE) , LIKE , CLIKE , IN , BETWEEN
Identifiers and Aliases
* regular identifiers are case insensitive (though see note on table names)
* delimited identifiers (inside double quotes) are case sensitive
* column and table aliases are supported
Concatenation
* use either ANSI SQL || or the CONCAT() function
* e.g. these are the same: {foo || bar} {CONCAT(foo,bar)}
Comments
* comments must occur before or after statements, cannot be embedded
* SQL-style single line -- and C-style multi-line /* */ comments are supported
NULLs
* currently NULLs and empty strings are identical in non-ANSI dialect.
* use {col IS NULL} to find NULLs, not {col=''} (though both may work depending on dialect)
See below for further details.
Syntax-DetailsCREATETABLE
Creates permanent and in-memory tables.
CREATE [TEMP] TABLE <table_name> ( <column_definitions> )
CREATE [TEMP] TABLE <table_name> AS <select statement>
CREATE [TEMP] TABLE <table_name> AS IMPORT()
Column definitions are standard SQL column names, types, and constraints, see "Column Definitions".
# create a permanent table
#
$dbh->do("CREATE TABLE qux (id INT PRIMARY KEY,word VARCHAR(30))");
The "AS SELECT" clause creates and populates the new table using the data and column structure specified
in the select statement.
# create and populate a table from a query to two other tables
#
$dbh->do("CREATE TABLE qux AS SELECT id,word FROM foo NATURAL JOIN bar");
If the optional keyword TEMP (or its synonym TEMPORARY) is used, the table will be an in-memory table,
available for the life of the current database handle or until a DROP TABLE command is issued.
# create a temporary table
#
$dbh->do("CREATE TEMP TABLE qux (id INT PRIMARY KEY,word VARCHAR(30))");
TEMP tables can be modified with SQL commands but the updates are not automatically reflected back to any
permanent tables they may be associated with. To save a TEMP table - just use an AS SELECT clause:
$dbh = DBI->connect( 'dbi:CSV:' );
$dbh->do("CREATE TEMP TABLE qux_temp AS (id INT, word VARCHAR(30))");
#
# ... modify qux_temp with INSERT, UPDATE, DELETE statements, then save it
#
$dbh->do("CREATE TABLE qux_permanent AS SELECT * FROM qux_temp");
Tables, both temporary and permanent may also be created directly from perl arrayrefs and from
heterogeneous queries to any DBI accessible data source, see the IMPORT() function.
CREATE [ {LOCAL|GLOBAL} TEMPORARY ] TABLE $table
(
$col_1 $col_type1 $col_constraints1,
...,
$col_N $col_typeN $col_constraintsN,
)
[ ON COMMIT {DELETE|PRESERVE} ROWS ]
* col_type must be a valid data type as defined in the
"valid_data_types" section of the dialect file for the
current dialect
* col_constraints may be "PRIMARY KEY" or one or both of
"UNIQUE" and/or "NOT NULL"
* IMPORTANT NOTE: temporary tables, data types and column
constraints are checked for syntax violations but are
currently otherwise *IGNORED* -- they are recognized by
the parser, but not by the execution engine
* The following valid ANSI SQL92 options are not currently
supported: table constraints, named constraints, check
constraints, reference constraints, constraint
attributes, collations, default clauses, domain names as
data types
DROPTABLE
DROP TABLE $table [ RESTRICT | CASCADE ]
* IMPORTANT NOTE: drop behavior (cascade or restrict) is
checked for valid syntax but is otherwise *IGNORED* -- it
is recognized by the parser, but not by the execution
engine
INSERTINTO
INSERT INTO $table [ ( $col1, ..., $colN ) ] VALUES ( $val1, ... $valN )
* default values are not currently supported
* inserting from a subquery is not currently supported
DELETEFROM
DELETE FROM $table [ WHERE search_condition ]
* see "search_condition" below
UPDATE
UPDATE $table SET $col1 = $val1, ... $colN = $valN [ WHERE search_condition ]
* default values are not currently supported
* see "search_condition" below
SELECT
SELECT select_clause
FROM from_clause
[ WHERE search_condition ]
[ ORDER BY $ocol1 [ASC|DESC], ... $ocolN [ASC|DESC] ]
[ LIMIT [start,] length ]
* select clause ::=
[DISTINCT|ALL] *
| [DISTINCT|ALL] col1 [,col2, ... colN]
| set_function1 [,set_function2, ... set_functionN]
* set function ::=
COUNT ( [ALL] * )
| COUNT | MIN | MAX | AVG | SUM ( [DISTINCT|ALL] col_name )
* from clause ::=
table1 [, table2, ... tableN]
| table1 NATURAL [join_type] JOIN table2
| table1 [join_type] table2 USING (col1,col2, ... colN)
| table1 [join_type] JOIN table2 ON table1.colA = table2.colB
* join type ::=
INNER
| [OUTER] LEFT | RIGHT | FULL
* if join_type is not specified, INNER is the default
* if DISTINCT or ALL is not specified, ALL is the default
* if start position is omitted from LIMIT clause, position 0 is
the default
* ON clauses may only contain equal comparisons and AND combiners
* self-joins are not currently supported
* if implicit joins are used, the WHERE clause must contain
an equijoin condition for each table
* multiple ANSI joins are not supported; use implicit joins for these
* this also means that combinations of INNER and non-INNER joins are
not supported
SEARCHCONDITION
[NOT] $val1 $op1 $val1 [ ... AND|OR $valN $opN $valN ]
OPERATORS
$op = | <> | < | > | <= | >=
| IS [NOT] NULL | IS [NOT] TRUE | IS [NOT] FALSE
| LIKE | CLIKE | BETWEEN | IN
The "CLIKE" operator works exactly the same as the "LIKE"
operator, but is case insensitive. For example:
WHERE foo LIKE 'bar%' # succeeds if foo is "barbaz"
# fails if foo is "BARBAZ" or "Barbaz"
WHERE foo CLIKE 'bar%' # succeeds for "barbaz", "Barbaz", and "BARBAZ"
BUILT-INANDUSER-DEFINEDFUNCTIONS
There are many built-in functions and you can also create your own new functions from perl subroutines.
See SQL::Statement::Functions for documentation of functions.
Identifiers(table&columnnames)
Regular identifiers (table and column names *without* quotes around them) are case INSENSITIVE so column
foo, fOo, FOO all refer to the same column. Internally they are used in their lower case representation,
so do not rely on SQL::Statement retaining your case.
Delimited identifiers (table and column names *with* quotes around them) are case SENSITIVE so column
"foo", "fOo", "FOO" each refer to different columns.
A delimited identifier is *never* equal to a regular identifier (so "foo" and foo are two different
columns). But do not do that :-).
Remember thought that, in DBD::CSV if table names are used directly as file names, the case sensitivity
depends on the OS e.g. on Windows files named foo, FOO, and fOo are the same as each other while on Unix
they are different.
SpecialUtilitySQLFunctionsIMPORT()
Imports the data and structure of a table from an external data source into a permanent or temporary
table.
$dbh->do("CREATE TABLE qux AS IMPORT(?)",{},$oracle_sth);
$dbh->do("CREATE TABLE qux AS IMPORT(?)",{},$AoA);
$dbh->do("CREATE TABLE qux AS IMPORT(?)",{},$AoH);
IMPORT() can also be used anywhere that table_names can:
$sth=$dbh->prepare("
SELECT * FROM IMPORT(?) AS T1 NATURAL JOIN IMPORT(?) AS T2 WHERE T1.id ...
");
$sth->execute( $pg_sth, $mysql_sth );
The IMPORT() function imports the data and structure of a table from an external data source. The
IMPORT() function is always used with a placeholder parameter which may be 1) a prepared and executed
statement handle for any DBI accessible data source; or 2) an AoA whose first row is column names and
whose succeeding rows are data 3) an AoH.
The IMPORT() function may be used in the AS clause of a CREATE statement, and in the FROM clause of any
statement. When used in a FROM clause, it should be used with a column alias e.g. SELECT * FROM
IMPORT(?) AS TableA WHERE ...
You can also write your own IMPORT() functions to treat anything as a data source. See User-Defined
Function in SQL::Statement::Functions.
Examples:
# create a CSV file from an Oracle query
#
$dbh = DBI->connect('dbi:CSV:');
$oracle_sth = $oracle_dbh->prepare($any_oracle_query);
$oracle_sth->execute(@params);
$dbh->do("CREATE TABLE qux AS IMPORT(?)",{},$oracle_sth);
# create an in-memory table from an AoA
#
$dbh = DBI->connect( 'dbi:File:' );
$arrayref = [['id','word'],[1,'foo'],[2,'bar'],];
$dbh->do("CREATE TEMP TABLE qux AS IMPORT(?)",{},$arrayref);
# query a join of a PostgreSQL table and a MySQL table
#
$dbh = DBI->connect( 'dbi:File:' );
$pg_dbh = DBI->connect( ... DBD::pg connect params );
$mysql_dbh = DBI->connect( ... DBD::mysql connect params );
$pg_sth = $pg_dbh->prepare( ... any pg query );
$pg_sth = $pg_dbh->prepare( ... any mysql query );
#
$sth=$dbh->prepare("
SELECT * FROM IMPORT(?) AS T1 NATURAL JOIN IMPORT(?) AS T2
");
$sth->execute( $pg_sth, $mysql_sth );
RUN()
Run SQL statements from a user supplied file. PleaseNote: this function is experimental, please let me
know if you have problems.
RUN( sql_file )
If the file contains non-SELECT statements such as CREATE and INSERT, use the RUN() function with
$dbh->do(). For example, this prepares and executes all of the SQL statements in a file called
"populate.sql":
$dbh->do(" CALL RUN( 'populate.sql') ");
If the file contains SELECT statements, the RUN() function may be used anywhere a table name may be used,
for example, if you have a file called "query.sql" containing "SELECT * FROM Employee", then these two
lines are exactly the same:
my $sth = $dbh->prepare(" SELECT * FROM Employee ");
my $sth = $dbh->prepare(" SELECT * FROM RUN( 'query.sql' ) ");
If the file contains a statement with placeholders, the values for the placeholders can be passed in the
call to $sth->execute() as normal. If the query.sql file contains "SELECT id,name FROM x WHERE id=?",
then these two are the same:
my $sth = $dbh->prepare(" SELECT id,name FROM x WHERE id=?");
$sth->execute(64);
my $sth = $dbh->prepare(" SELECT * FROM RUN( 'query.sql' ) ");
$sth->execute(64);
Note This function assumes that the SQL statements in the file are separated by a semi-colon+newline
combination (/;\n/). If you wish to use different separators or import SQL from a different source, just
override the RUN() function with your own user-defined-function.
Furtherdetails
Integers
Reals Syntax obvious
Strings Surrounded by either single quotes; some characters need to be escaped with a backslash, in
particular the backslash itself (\\), the NULL byte (\0), Line feeds (\n), Carriage return (\r),
and the quotes (\').
Note: Quoting "Strings" using double quotes are recognized as quoted identifiers (column or table
names).
Parameters
Parameters represent scalar values, like Integers, Reals and Strings do. However, their values
are read inside Execute() and not inside Prepare(). Parameters are represented by question marks
(?).
Identifiers
Identifiers are table or column names. Syntactically they consist of alphabetic characters,
followed by an arbitrary number of alphanumeric characters. Identifiers like SELECT, INSERT,
INTO, ORDER, BY, WHERE, ... are forbidden and reserved for other tokens. Identifiers are always
compared case-insensitively, i.e. "select foo from bar" will be evaluated the same as "SELECT FOO
FROM BAR" ("FOO" will be evaluated as "foo", similar for "BAR").
Since SQL::Statement is internally using lower cased identifiers (unquoted), everytime a wildcard
is used, the delivered names of the identifiers are lower cased.
Install Now
PNG Icons
