1SQL::Statement::Syntax(U3s)er Contributed Perl DocumentatSiQoLn::Statement::Syntax(3)
2
6 SQL::Statement::Syntax - documentation of SQL::Statement's SQL Syntax
7
9 See SQL::Statement for usage.
10
12 The SQL::Statement module can be used either from a DBI driver like
13 DBD::CSV or directly. The syntax below applies to both situations. In
14 the case of DBDs, each DBD can implement its own sub-dialect so be sure
15 to check the DBD documentation also.
16 SQL::Statement is meant primarily as a base class for DBD drivers and
18 as such concentrates on a small but useful subset of SQL. It does
19 *not* in any way pretend to be a complete SQL parser for all dialects
20 of SQL. The module will continue to add new supported syntax, and
21 users may also extend the syntax (see "#Extending the SQL syntax").
22
24 Default Supported SQL syntax - Summary
25 SQL Statements
26 CALL <function>
28 CREATE [TEMP] TABLE <table> <column_def_clause>
29 CREATE [TEMP] TABLE <table> AS <select statement>
30 CREATE [TEMP] TABLE <table> AS IMPORT()
31 CREATE FUNCTION <user_defined_function> [ NAME <perl_subroutine> ]
32 CREATE KEYWORD <user_defined_keyword> [ NAME <perl_subroutine> ]
33 CREATE OPERATOR <user_defined_operator> [ NAME <perl_subroutine> ]
34 CREATE TYPE <user_defined_type> [ NAME <perl_subroutine> ]
35 DELETE FROM <table> [<where_clause>]
36 DROP TABLE [IF EXISTS] <table>
37 DROP FUNCTION <function>
38 DROP KEYWORD <keyword>
39 DROP OPERATOR <operator>
40 DROP TYPE <type>
41 INSERT [INTO] <table> [<column_list>] VALUES <value_list>
42 LOAD <user_defined_functions_module>
43 SELECT <function>
44 SELECT <select_clause>
45 <from_clause>
46 [<where_clause>]
47 [ ORDER BY ocol1 [ASC|DESC], ... ocolN [ASC|DESC]] ]
48 [ GROUP BY gcol1 [, ... gcolN] ]
49 [ LIMIT [start,] length ]
50 UPDATE <table> SET <set_clause> [<where_clause>]
51 Explicit Join Qualifiers
53 NATURAL, INNER, OUTER, LEFT, RIGHT, FULL
55 Built-in Functions
57 * Aggregate : MIN, MAX, AVG, SUM, COUNT
59 * Date/Time : CURRENT_DATE, CURDATE, CURRENT_TIME, CURTIME, CURRENT_TIMESTAMP, NOW,
60 UNIX_TIMESTAMP
61 * String : ASCII, CHAR, BIT_LENGTH, CHARACTER_LENGTH, CHAR_LENGTH, COALESCE,
62 NVL, IFNULL, CONV, CONCAT, DECODE, HEX, OCT, BIN, INSERT, LEFT, RIGHT,
63 LOCATE, POSITION, LOWER, UPPER, LCASE, UCASE, LTRIM, RTRIM, OCTET_LENGTH,
64 REGEX, REPEAT, REPLACE, SOUNDEX, SPACE, SUBSTITUTE, SUBSTRING, SUBSTR,
65 TRANSLATE, TRIM, UNHEX
66 * Numeric : ABS, CEILING, CEIL, FLOOR, ROUND, EXP, LOG, LN, LOG10, MOD, POWER,
67 RAND, SIGN, SQRT, TRUNCATE, TRUNC
68 * Trig : ACOS, ACOSEC, ACOSECH, ACOSH, ACOT, ACOTAN, ACOTANH, ACOTH, ACSC,
69 ACSCH, ASEC, ASECH, ASIN, ASINH, ATAN, ATAN2, ATANH, COS, COSEC,
70 COSECH, COSH, COT, COTAN, COTANH, COTH, CSC, CSCH, DEG2DEG, DEG2GRAD,
71 DEG2RAD, DEGREES, GRAD2DEG, GRAD2GRAD, GRAD2RAD, PI, RAD2DEG, RAD2GRAD,
72 RAD2RAD, RADIANS, SEC, SECH, SIN, SINH, TAN, TANH
73 * System : DBNAME, USERNAME, USER
74 Special Utility Functions
76 * IMPORT - imports a table from an external RDBMS or perl structure
78 * RUN - prepares and executes statements in a file of SQL statements
79 Operators and Predicates
81 = , <> , < , > , <= , >= , IS [NOT] (NULL|TRUE|FALSE) , LIKE , CLIKE , IN , BETWEEN
83 Identifiers and Aliases
85 * regular identifiers are case insensitive (though see note on table names)
87 * delimited identifiers (inside double quotes) are case sensitive
88 * column and table aliases are supported
89 Concatenation
91 * use either ANSI SQL || or the CONCAT() function
93 * e.g. these are the same: {foo || bar} {CONCAT(foo,bar)}
94 Comments
96 * comments must occur before or after statements, cannot be embedded
98 * SQL-style single line -- and C-style multi-line /* */ comments are supported
99 NULLs
101 * currently NULLs and empty strings are identical in non-ANSI dialect.
103 * use {col IS NULL} to find NULLs, not {col=''} (though both may work depending on dialect)
104 See below for further details.
106 Syntax - Details
108 CREATE TABLE
109 Creates permanent and in-memory tables.
111 CREATE [TEMP] TABLE <table_name> ( <column_definitions> )
113 CREATE [TEMP] TABLE <table_name> AS <select statement>
114 CREATE [TEMP] TABLE <table_name> AS IMPORT()
115 Column definitions are standard SQL column names, types, and
117 constraints, see "Column Definitions".
118 # create a permanent table
120 #
121 $dbh->do("CREATE TABLE qux (id INT PRIMARY KEY,word VARCHAR(30))");
122 The "AS SELECT" clause creates and populates the new table using the
124 data and column structure specified in the select statement.
125 # create and populate a table from a query to two other tables
127 #
128 $dbh->do("CREATE TABLE qux AS SELECT id,word FROM foo NATURAL JOIN bar");
129 If the optional keyword TEMP (or its synonym TEMPORARY) is used, the
131 table will be an in-memory table, available for the life of the
132 current database handle or until a DROP TABLE command is issued.
133 # create a temporary table
135 #
136 $dbh->do("CREATE TEMP TABLE qux (id INT PRIMARY KEY,word VARCHAR(30))");
137 TEMP tables can be modified with SQL commands but the updates are not
139 automatically reflected back to any permanent tables they may be
140 associated with. To save a TEMP table - just use an AS SELECT clause:
141 $dbh = DBI->connect( 'dbi:CSV:' );
143 $dbh->do("CREATE TEMP TABLE qux_temp AS (id INT, word VARCHAR(30))");
144 #
145 # ... modify qux_temp with INSERT, UPDATE, DELETE statements, then save it
146 #
147 $dbh->do("CREATE TABLE qux_permanent AS SELECT * FROM qux_temp");
148 Tables, both temporary and permanent may also be created directly from
150 perl arrayrefs and from heterogeneous queries to any DBI accessible
151 data source, see the IMPORT() function.
152 CREATE [ {LOCAL|GLOBAL} TEMPORARY ] TABLE $table
154 (
155 $col_1 $col_type1 $col_constraints1,
156 ...,
157 $col_N $col_typeN $col_constraintsN,
158 )
159 [ ON COMMIT {DELETE|PRESERVE} ROWS ]
160 * col_type must be a valid data type as defined in the
162 "valid_data_types" section of the dialect file for the
163 current dialect
164 * col_constraints may be "PRIMARY KEY" or one or both of
166 "UNIQUE" and/or "NOT NULL"
167 * IMPORTANT NOTE: temporary tables, data types and column
169 constraints are checked for syntax violations but are
170 currently otherwise *IGNORED* -- they are recognized by
171 the parser, but not by the execution engine
172 * The following valid ANSI SQL92 options are not currently
174 supported: table constraints, named constraints, check
175 constraints, reference constraints, constraint
176 attributes, collations, default clauses, domain names as
177 data types
178 DROP TABLE
180 DROP TABLE $table [ RESTRICT | CASCADE ]
182 * IMPORTANT NOTE: drop behavior (cascade or restrict) is
184 checked for valid syntax but is otherwise *IGNORED* -- it
185 is recognized by the parser, but not by the execution
186 engine
187 INSERT INTO
189 INSERT INTO $table [ ( $col1, ..., $colN ) ] VALUES ( $val1, ... $valN )
191 * default values are not currently supported
193 * inserting from a subquery is not currently supported
194 DELETE FROM
196 DELETE FROM $table [ WHERE search_condition ]
198 * see "search_condition" below
200 UPDATE
202 UPDATE $table SET $col1 = $val1, ... $colN = $valN [ WHERE search_condition ]
204 * default values are not currently supported
206 * see "search_condition" below
207 SELECT
209 SELECT select_clause
211 FROM from_clause
212 [ WHERE search_condition ]
213 [ ORDER BY $ocol1 [ASC|DESC], ... $ocolN [ASC|DESC] ]
214 [ LIMIT [start,] length ]
215 * select clause ::=
217 [DISTINCT|ALL] *
218 | [DISTINCT|ALL] col1 [,col2, ... colN]
219 | set_function1 [,set_function2, ... set_functionN]
220 * set function ::=
222 COUNT ( [ALL] * )
223 | COUNT | MIN | MAX | AVG | SUM ( [DISTINCT|ALL] col_name )
224 * from clause ::=
226 table1 [, table2, ... tableN]
227 | table1 NATURAL [join_type] JOIN table2
228 | table1 [join_type] table2 USING (col1,col2, ... colN)
229 | table1 [join_type] JOIN table2 ON table1.colA = table2.colB
230 * join type ::=
232 INNER
233 | [OUTER] LEFT | RIGHT | FULL
234 * if join_type is not specified, INNER is the default
236 * if DISTINCT or ALL is not specified, ALL is the default
237 * if start position is omitted from LIMIT clause, position 0 is
238 the default
239 * ON clauses may only contain equal comparisons and AND combiners
240 * self-joins are not currently supported
241 * if implicit joins are used, the WHERE clause must contain
242 an equijoin condition for each table
243 * multiple ANSI joins are not supported; use implicit joins for these
244 * this also means that combinations of INNER and non-INNER joins are
245 not supported
246 SEARCH CONDITION
248 [NOT] $val1 $op1 $val1 [ ... AND|OR $valN $opN $valN ]
250 OPERATORS
252 $op = | <> | < | > | <= | >=
254 | IS [NOT] NULL | IS [NOT] TRUE | IS [NOT] FALSE
255 | LIKE | CLIKE | BETWEEN | IN
256 The "CLIKE" operator works exactly the same as the "LIKE"
258 operator, but is case insensitive. For example:
259 WHERE foo LIKE 'bar%' # succeeds if foo is "barbaz"
261 # fails if foo is "BARBAZ" or "Barbaz"
262 WHERE foo CLIKE 'bar%' # succeeds for "barbaz", "Barbaz", and "BARBAZ"
264 BUILT-IN AND USER-DEFINED FUNCTIONS
266 There are many built-in functions and you can also create your own new
268 functions from perl subroutines. See SQL::Statement::Functions for
269 documentation of functions.
270 Identifiers (table & column names)
272 Regular identifiers (table and column names *without* quotes around
274 them) are case INSENSITIVE so column foo, fOo, FOO all refer to the
275 same column. Internally they are used in their lower case
276 representation, so do not rely on SQL::Statement retaining your case.
277 Delimited identifiers (table and column names *with* quotes around
279 them) are case SENSITIVE so column "foo", "fOo", "FOO" each refer to
280 different columns.
281 A delimited identifier is *never* equal to a regular identifier (so
283 "foo" and foo are two different columns). But do not do that :-).
284 Remember thought that, in DBD::CSV if table names are used directly as
286 file names, the case sensitivity depends on the OS e.g. on Windows
287 files named foo, FOO, and fOo are the same as each other while on Unix
288 they are different.
289 Special Utility SQL Functions
291 IMPORT()
293 Imports the data and structure of a table from an external data source
295 into a permanent or temporary table.
296 $dbh->do("CREATE TABLE qux AS IMPORT(?)",{},$oracle_sth);
298 $dbh->do("CREATE TABLE qux AS IMPORT(?)",{},$AoA);
300 $dbh->do("CREATE TABLE qux AS IMPORT(?)",{},$AoH);
302 IMPORT() can also be used anywhere that table_names can:
304 $sth=$dbh->prepare("
306 SELECT * FROM IMPORT(?) AS T1 NATURAL JOIN IMPORT(?) AS T2 WHERE T1.id ...
307 ");
308 $sth->execute( $pg_sth, $mysql_sth );
309 The IMPORT() function imports the data and structure of a table from an
311 external data source. The IMPORT() function is always used with a
312 placeholder parameter which may be 1) a prepared and executed statement
313 handle for any DBI accessible data source; or 2) an AoA whose first
314 row is column names and whose succeeding rows are data 3) an AoH.
315 The IMPORT() function may be used in the AS clause of a CREATE
317 statement, and in the FROM clause of any statement. When used in a
318 FROM clause, it should be used with a column alias e.g. SELECT * FROM
319 IMPORT(?) AS TableA WHERE ...
320 You can also write your own IMPORT() functions to treat anything as a
322 data source. See User-Defined Function in SQL::Statement::Functions.
323 Examples:
325 # create a CSV file from an Oracle query
327 #
328 $dbh = DBI->connect('dbi:CSV:');
329 $oracle_sth = $oracle_dbh->prepare($any_oracle_query);
330 $oracle_sth->execute(@params);
331 $dbh->do("CREATE TABLE qux AS IMPORT(?)",{},$oracle_sth);
332 # create an in-memory table from an AoA
334 #
335 $dbh = DBI->connect( 'dbi:File:' );
336 $arrayref = [['id','word'],[1,'foo'],[2,'bar'],];
337 $dbh->do("CREATE TEMP TABLE qux AS IMPORT(?)",{},$arrayref);
338 # query a join of a PostgreSQL table and a MySQL table
340 #
341 $dbh = DBI->connect( 'dbi:File:' );
342 $pg_dbh = DBI->connect( ... DBD::pg connect params );
343 $mysql_dbh = DBI->connect( ... DBD::mysql connect params );
344 $pg_sth = $pg_dbh->prepare( ... any pg query );
345 $pg_sth = $pg_dbh->prepare( ... any mysql query );
346 #
347 $sth=$dbh->prepare("
348 SELECT * FROM IMPORT(?) AS T1 NATURAL JOIN IMPORT(?) AS T2
349 ");
350 $sth->execute( $pg_sth, $mysql_sth );
351 RUN()
353 Run SQL statements from a user supplied file. Please Note: this
355 function is experimental, please let me know if you have problems.
356 RUN( sql_file )
358 If the file contains non-SELECT statements such as CREATE and INSERT,
360 use the RUN() function with $dbh->do(). For example, this prepares and
361 executes all of the SQL statements in a file called "populate.sql":
362 $dbh->do(" CALL RUN( 'populate.sql') ");
364 If the file contains SELECT statements, the RUN() function may be used
366 anywhere a table name may be used, for example, if you have a file
367 called "query.sql" containing "SELECT * FROM Employee", then these two
368 lines are exactly the same:
369 my $sth = $dbh->prepare(" SELECT * FROM Employee ");
371 my $sth = $dbh->prepare(" SELECT * FROM RUN( 'query.sql' ) ");
373 If the file contains a statement with placeholders, the values for the
375 placeholders can be passed in the call to $sth->execute() as normal. If
376 the query.sql file contains "SELECT id,name FROM x WHERE id=?", then
377 these two are the same:
378 my $sth = $dbh->prepare(" SELECT id,name FROM x WHERE id=?");
380 $sth->execute(64);
381 my $sth = $dbh->prepare(" SELECT * FROM RUN( 'query.sql' ) ");
383 $sth->execute(64);
384 Note This function assumes that the SQL statements in the file are
386 separated by a semi-colon+newline combination (/;\n/). If you wish to
387 use different separators or import SQL from a different source, just
388 override the RUN() function with your own user-defined-function.
389 Further details
391 Integers
392 Reals Syntax obvious
393 Strings Surrounded by either single quotes; some characters need to be
395 escaped with a backslash, in particular the backslash itself
396 (\\), the NULL byte (\0), Line feeds (\n), Carriage return
397 (\r), and the quotes (\').
398 Note: Quoting "Strings" using double quotes are recognized as
400 quoted identifiers (column or table names).
401 Parameters
403 Parameters represent scalar values, like Integers, Reals and
404 Strings do. However, their values are read inside Execute() and
405 not inside Prepare(). Parameters are represented by question
406 marks (?).
407 Identifiers
409 Identifiers are table or column names. Syntactically they
410 consist of alphabetic characters, followed by an arbitrary
411 number of alphanumeric characters. Identifiers like SELECT,
412 INSERT, INTO, ORDER, BY, WHERE, ... are forbidden and reserved
413 for other tokens. Identifiers are always compared case-
414 insensitively, i.e. "select foo from bar" will be evaluated the
415 same as "SELECT FOO FROM BAR" ("FOO" will be evaluated as
416 "foo", similar for "BAR").
417 Since SQL::Statement is internally using lower cased
419 identifiers (unquoted), everytime a wildcard is used, the
420 delivered names of the identifiers are lower cased.
421
423 The Supported SQL syntax shown above is the default for SQL::Statement
424 but it can be extended (or contracted) either on-the-fly or on a
425 permanent basis. In other words, you can modify the SQL syntax
426 accepted as valid by the parser and accepted as executable by the
427 executer. There are two methods for extending the syntax - 1) with SQL
428 commands that can be issued directly in SQL::Statement or form a DBD or
429 2) by subclassing SQL::Parser.
430 The following SQL commands modify the default SQL syntax:
432 CREATE/DROP FUNCTION
434 CREATE/DROP KEYWORD
435 CREATE/DROP TYPE
436 CREATE/DROP OPERATOR
437 A simple example would be a situation in which you have a table named
439 'TABLE'. Since table is an ANSI reserved key word, by default
440 SQL::Statement will produce an error when you attempt to create or
441 access it. You could put the table name inside double quotes since
442 quoted identifiers can validly be reserved words, or you could rename
443 the table. If neither of those are options, you would do this:
444 DROP KEYWORD table
446 Once that statement is issued, the parser will no longer object to
448 'table' as a table name. Careful though, if you drop too many keywords
449 you may confuse the parser, especially keywords like FROM and WHERE
450 that are central to parsing the statement.
451 In the reverse situation, suppose you want to parse some SQL that
453 defines a column as type BIG_BLOB. Since 'BIG_BLOB' is not a
454 recognized ANSI data type, an error will be produced by default. To
455 make the parser treat it as a valid data type, you do this:
456 CREATE TYPE big_blob
458 Keywords and types are case-insensitive.
460 Suppose you are working with some SQL that contains the cosh() function
462 (an Oracle function for hyperbolic cosine, whatever that is :-). The
463 cosh() function is not currently implemented in SQL::Statement so the
464 parser would die with an error. But you can easily trick the parser
465 into accepting the function:
466 CREATE FUNCTION cosh
468 Once the parser has read that CREATE FUNCTION statement, it will no
470 longer object to the use of the cosh() function in SQL statements.
471 If your only interest is in parsing SQL statements, then "CREATE
473 FUNCTION cosh" is sufficient. But if you actually want to be able to
474 use the cosh() function in executable statements, you need to supply a
475 perl subroutine that performs the cosh() function:
476 CREATE FUNCTION cosh AS perl_subroutine_name
478 The subroutine name can refer to a subroutine in your current script,
480 or to a subroutine in any available package. See
481 SQL::Statement::Functions for details of how to create and load
482 functions.
483 Functions can be used as predicates in search clauses, for example:
485 SELECT * FROM x WHERE c1=7 AND SOUNDEX(c3,'foo') AND c8='bar'
487 In the SQL above, the "SOUNDEX()" function full predicate - it plays
489 the same role as "c1=7 or c8='bar'".
490 Functions can also serve as predicate operators. An operator, unlike a
492 full predicate, has something on the left and right sides. An equal
493 sign is an operator, so is LIKE. If you really want to you can get the
494 parser to not accept LIKE as an operator with
495 DROP OPERATOR like
497 Or, you can invent your own operator. Suppose you have an operator
499 "REVERSE_OF" that is true if the string on its left side when reversed
500 is equal to the string on the right side:
501 CREATE OPERATOR reverse_of
503 SELECT * FROM x WHERE c1=7 AND c3 REVERSE_OF 'foo'
504 The operator could just as well have been written as a function:
506 CREATE FUNCTION reverse_of
508 SELECT * FROM x WHERE c1=7 AND REVERSE_OF(c3,'foo')
509 Like functions, if you want to actually execute a user-defined operator
511 as distinct from just parsing it, you need to assign the operator to a
512 perl subroutine. This is done exactly like assigning functions:
513 CREATE OPERATOR reverse_of AS perl_subroutine_name
515
517 In addition to using the SQL shown above to modify the parser's
518 behavior, you can also extend the SQL syntax by subclassing
519 SQL::Parser. See SQL::Parser for details.
520
522 Copyright (c) 2005, Jeff Zucker <jzuckerATcpan.org>, all rights
523 reserved. Copyright (c) 2009-2020, Jens Rehsack <rehsackATcpan.org>,
524 all rights reserved.
525 This document may be freely modified and distributed under the same
527 terms as Perl itself.
528perl v5.32.0 2020-10-22 SQL::Statement::Syntax(3)