Поддерживаемый синтаксис SQL

Следующие примеры синтаксиса SQL поддерживаются ядром базы данных SQL в Adobe AIR. Примеры содержат объяснения разных типов инструкций и предложений, выражений, встроенных функций и операторов. Рассматриваются следующие темы:
  • Общий синтаксис SQL

  • Инструкции обработки данных (SELECT, INSERT, UPDATE и DELETE)

  • Инструкции определения данных (инструкции CREATE, ALTER и DROP для таблиц, индексов, видов и триггеров)

  • Специальные инструкции и предложения

  • Встроенные функции (агрегатные, скалярные и функции форматирования даты и времени)

  • Операторы

  • Параметры

  • Неподдерживаемые возможности SQL

  • Дополнительные возможности SQL

Общий синтаксис SQL

Помимо специфического синтаксиса для различных инструкций и выражений применяются следующие общие правила синтаксиса SQL.
Чувствительность к регистру
В инструкциях SQL, включая имена объектов, не учитывается регистр. Тем не менее, инструкции SQL часто записываются с использованием ключевых слов в верхнем регистре, поэтому данный документ следует этому правилу. Хотя в синтаксисе SQL регистр не учитывается, он учитывается в текстовых значениях литералов SQL. Кроме того, регистр может иметь значение при выполнении операций сравнения и сортировки в соответствии с последовательностью сортировки, определенной для столбца или операции. Дополнительные сведения см. в разделе «COLLATE».

Пробел
Символ пробела (например, обычный пробел, разделитель табуляции, новая строка и т. д.) следует использовать для разделения отдельных слов в инструкции SQL. Однако пробелом не обязательно разделять слова и символы. Тип и количество символов пробела в инструкции SQL значения не имеют. Пробелы, такие как отступы и разрывы строк, можно использовать для форматирования инструкций SQL с целью удобочитаемости, при этом они не будут оказывать влияния на значение инструкции.

Инструкции обработки данных

Инструкции обработки данных являются самыми распространенными из всех инструкций SQL. Эти инструкции предназначены для получения, сложения, изменения и удаления данных в таблицах базы данных. Поддерживаются следующие инструкции обработки данных: SELECT, INSERT, UPDATE и DELETE.

SELECT

Инструкция SELECT используется для запроса информации в базе данных. В качестве ответа на SELECT возвращается 0 или больше строк данных, где каждая строка содержит фиксированное число столбцов. Число столбцов в результате определяется по списку имен столбцов или выражений в результате, который находится между SELECT и необязательными ключевыми словами FROM.

sql-statement   ::=  SELECT [ALL | DISTINCT] result 
                     [FROM table-list] 
                     [WHERE expr] 
                     [GROUP BY expr-list] 
                     [HAVING expr] 
                     [compound-op select-statement]* 
                     [ORDER BY sort-expr-list] 
                     [LIMIT integer [( OFFSET | , ) integer]] 
result          ::=  result-column [, result-column]* 
result-column   ::=  * | table-name . * | expr [[AS] string] 
table-list      ::=  table [ join-op table join-args ]* 
table           ::=  table-name [AS alias] | 
                     ( select ) [AS alias] 
join-op         ::=  , | [NATURAL] [LEFT | RIGHT | FULL] [OUTER | INNER | CROSS] JOIN 
join-args       ::=  [ON expr] [USING ( id-list )] 
compound-op     ::=  UNION | UNION ALL | INTERSECT | EXCEPT 
sort-expr-list  ::=  expr [sort-order] [, expr [sort-order]]* 
sort-order      ::=  [COLLATE collation-name] [ASC | DESC] 
collation-name  ::=  BINARY | NOCASE

В качестве результата может использоваться произвольное выражение. Если результирующим выражением является символ «*», то все столбцы всех таблиц заменяются одним этим выражением. Если выражение содержит имя таблицы после «.*»,то результатом будут все столбцы в этой таблице.

Ключевое слово DISTINCT возвращает подмножество строк, каждая из которых отлична от остальных. Значения NULL не считаются отличными друг от друга. По умолчанию возвращаются все строки результата, этого также можно достичь при помощи ключевого слова ALL.

Запрос выполняется по одной или нескольким таблицам, имена которых следуют за ключевым словом FROM. Если несколько имен таблиц разделены запятыми, то при выполнении запроса используется перекрестное объединение различных таблиц. Чтобы определить способ объединения таблиц, также можно использовать синтаксис JOIN. Единственными поддерживаемым типом внешнего объединения является LEFT OUTER JOIN. Выражение предложения ON в join-args должно разрешаться в логическое значение. Подзапрос в круглых скобках может использоваться как таблица в предложении FROM. Предложение FROM можно полностью опустить. В таком случае возвращается одна строка, содержащая значения результирующего списка выражений.

Предложение WHERE служит для ограничения числа строк, извлекаемых с помощью запроса. Выражения предложения WHERE должны разрешаться в логическое значение. Фильтрация с помощью предложения WHERE выполняется перед группировкой, потому выражения WHERE не могут включать агрегатные функции.

Предложения GROUP BY служат для объединения одной или нескольких строк результата в одну строку вывода. Предложение GROUP BY особенно полезно, когда результат содержит агрегатные функции. Выражения в приложении GROUP BY могут не входить в список выражений, содержащийся в инструкции SELECT.

Предложение HAVING похоже на WHERE тем, что оно ограничивает число строк, возвращаемых инструкцией. Однако предложение HAVING применяется после выполнения группировки, определенной предложением GROUP BY. По этой причине выражение HAVING может применяться к значениям, которые включают агрегатные функции. Выражения предложения HAVING могут не входить в список SELECT. Как и WHERE, выражение HAVING должно разрешаться в логическое значение.

Предложение ORDER BY обеспечивает сортировку строк вывода. Аргумент sort-expr-list, передаваемый в предложении ORDER BY, представляет собой список выражений, используемых в качестве ключа для сортировки. Выражения могут не входить в результат простой инструкции SELECT, но в составной инструкции SELECT (в которой используется один из операторов compound-op) каждое выражение сортировки должно точно соответствовать одному из столбцов результата. За каждым аргументом sort может следовать необязательное предложение порядка сортировки sort-order, которое содержит ключевое слово COLLATE и имя функции сортировки, используемое для упорядочения текста, и/или ключевое слово ASC или DESC, указывающее порядок сортировки (по возрастанию или по убыванию). Аргумент sort-order может быть опущен, в этом случае будет использоваться порядок сортировки по умолчанию (по возрастанию). Определение предложения COLLATE и функций сортировки см. в разделе «COLLATE».

Предложение LIMIT определяет верхний предел для числа строк, возвращаемых в результате. Отрицательное значение LIMIT указывает на то, что верхний предел не установлен. Необязательное предложение OFFSET после LIMIT указывает, сколько строк следует пропустить в начале набора результатов. В составном запросе SELECT предложение LIMIT может следовать только за последней инструкцией SELECT, при этом ограничение применяется ко всему запросу. Обратите внимание, что если в предложении LIMIT используется ключевое слово OFFSET, то ограничением является первое целое число, а смещением — второе. Если вместо ключевого слова OFFSET стоит запятая, то смещением является первое число, а ограничением — второе. Такое кажущееся противоречие является намеренным: оно позволяет обеспечить максимальную совместимость с устаревшими системами баз данных SQL.

Составная инструкция SELECT образуется из двух или нескольких простых инструкций SELECT, объединенных с помощью одного из операторов UNION, UNION ALL, INTERSECT или EXCEPT. Во всех инструкциях SELECT, которые входят в составную инструкцию SELECT, должно быть указано одинаковое число столбцов результата. Может использоваться только одно предложение ORDER BY, оно должно следовать за последней инструкцией SELECT (и перед единственным предложением LIMIT, если оно имеется). Операторы UNION и UNION ALL объединяют результаты предшествующей и последующей инструкций SELECT в одну таблицу. Разница между ними заключается в том, что при использовании UNION все строки результата являются уникальными, а при использовании UNION ALL могут встречаться повторения. Оператор INTERSECT возвращает результаты, совпавшие в предшествующей и последующей инструкциях SELECT. Оператор EXCEPT возвращает результат предшествующей инструкции SELECT после удаления из него результата последующей инструкции SELECT. Когда объединяется три и более инструкции SELECT, группировка выполняется начиная с первой и кончая последней.

Определения допустимых выражений см. в разделе «Выражения».

Начиная с версии AIR 2.5, оператор SQL CAST поддерживается при считывании данных BLOB для их преобразования в объекты ActionScript ByteArray. Например, следующий код считывает необработанные данные, которые не сохранены в формате AMF, и сохраняет их в объекте ByteArray:

stmt.text = "SELECT CAST(data AS ByteArray) AS data FROM pictures;"; 
stmt.execute(); 
var result:SQLResult = stmt.getResult(); 
var bytes:ByteArray = result.data[0].data;

INSERT

Инструкция INSERT встречается в двух основных формах и служит для заполнения таблиц данными.
sql-statement  ::=  INSERT [OR conflict-algorithm] INTO [database-name.] table-name [(column-list)] VALUES (value-list) | 
                    INSERT [OR conflict-algorithm] INTO [database-name.] table-name [(column-list)] select-statement 
                    REPLACE INTO [database-name.] table-name [(column-list)] VALUES (value-list) | 
                    REPLACE INTO [database-name.] table-name [(column-list)] select-statement

Первая форма (с ключевым словом VALUES) создает новую строку в существующей таблице. Если аргумент column-list не определен, то число значений должно соответствовать числу столбцов в таблице. Если аргумент column-list определен, то число значений должно соответствовать числу указанных столбцов. Столбцы таблицы, которые не отображаются в списке столбцов, заполняются значением по умолчанию, определенным при создании таблицы, или значением NULL, если значение по умолчанию не определено.

Вторая форма инструкции INSERT получает свои данные из инструкции SELECT. Если аргумент column-list не определен, число столбцов, полученное в результате SELECT, должно точно соответствовать числу столбцов в таблице; в противном случае оно должно соответствовать числу столбцов, перечисленных в аргументе column-list. Для каждой строки результата SELECT создается новая запись в таблице. Инструкция SELECT может быть простой или составной. Определения разрешенных инструкций SELECT см. в разделе «SELECT».

Необязательный аргумент conflict-algorithm позволяет определить альтернативный алгоритм разрешения конфликтов между ограничениями, который должен использоваться при выполнении данной команды. Описание и определение алгоритмов конфликтов см. в разделе «Специальные инструкции и предложения».

Две формы REPLACE INTO инструкции равнозначны использованию стандартной формы INSERT [OR conflict-algorithm] с алгоритмом решения конфликтов REPLACE (то есть формы INSERT OR REPLACE...).

Две формы REPLACE INTO инструкции равнозначны использованию стандартной формы INSERT [OR conflict-algorithm] с алгоритмом решения конфликтов REPLACE (то есть формы INSERT OR REPLACE...).

UPDATE

Команда update изменяет существующие записи в таблице.

sql-statement  ::=  UPDATE [database-name.] table-name SET column1=value1, column2=value2,... [WHERE expr]

Команда состоит из ключевого слова UPDATE, а за ним следует имя таблицы, записи в которой требуется обновить. После ключевого слова SET укажите название столбца и значение, на которое будет изменено значение в столбце, в виде списка с разделением запятой. Выражение предложения WHERE указывает строку или строки, записи в которых будут обновлены.

DELETE

Команда удаления служит для удаления записей из таблицы.
sql-statement  ::=  DELETE FROM [database-name.] table-name [WHERE expr]

Команда состоит из ключевых слов DELETE FROM, а за ними следует имя таблицы, из которой требуется удалить записи.

Если отсутствует предложение WHERE, удаляются все строки таблицы. Если имеется предложение WHERE, то удаляются только строки, соответствующие выражению. Выражение предложения WHERE должно разрешаться в логическое значение. Определения допустимых выражений см. в разделе «Выражения».

Инструкции определения данных

Инструкции определения данных служат для создания, модификации и удаления объектов базы данных, включая таблицы, виды, индексы и триггеры. Поддерживаются следующие инструкции определения данных.
  • Таблицы:
    • CREATE TABLE

    • ALTER TABLE

    • DROP TABLE

  • Индексы:
    • CREATE INDEX

    • DROP INDEX

  • Виды:
    • CREATE VIEWS

    • DROP VIEWS

  • Триггеры:
    • CREATE TRIGGERS

    • DROP TRIGGERS

CREATE TABLE

Инструкция CREATE TABLE состоит из ключевых слов CREATE TABLE, за которыми следует имя новой таблицы, а затем (в круглых скобках) список определений и ограничений столбцов. Имя таблицы может быть идентификатором или строкой.
sql-statement       ::=  CREATE [TEMP | TEMPORARY] TABLE [IF NOT EXISTS] [database-name.] table-name 
                         ( column-def [, column-def]* [, constraint]* ) 
sql-statement       ::=  CREATE [TEMP | TEMPORARY] TABLE [database-name.] table-name AS select-statement 
column-def          ::=  name [type] [[CONSTRAINT name] column-constraint]* 
type                ::=  typename | typename ( number ) | typename ( number , number ) 
column-constraint   ::=  NOT NULL [ conflict-clause ] | 
                         PRIMARY KEY [sort-order] [ conflict-clause ] [AUTOINCREMENT] | 
                         UNIQUE [conflict-clause] | 
                         CHECK ( expr ) | 
                         DEFAULT default-value | 
                         COLLATE collation-name 
constraint          ::=  PRIMARY KEY ( column-list ) [conflict-clause] | 
                         UNIQUE ( column-list ) [conflict-clause] | 
                         CHECK ( expr ) 
conflict-clause     ::=  ON CONFLICT conflict-algorithm 
conflict-algorithm  ::=  ROLLBACK | ABORT | FAIL | IGNORE | REPLACE 
default-value       ::=  NULL | string | number | CURRENT_TIME | CURRENT_DATE | CURRENT_TIMESTAMP 
sort-order          ::=  ASC | DESC 
collation-name      ::=  BINARY | NOCASE 
column-list         ::=  column-name [, column-name]*

Каждое определение столбца содержит имя столбца, за которым следует тип данных и одно или несколько необязательных ограничений. Тип данных ограничивает данные, которые могут храниться в данном столбце. При попытке сохранить в столбце значение другого типа среда выполнения по возможности преобразует значение в соответствующий тип и выдает ошибку, если это не удается. Дополнительные сведения см. в разделе «Поддержка типов данных».

Ограничение столбца NOT NULL указывает, что столбец не может содержать значения NULL.

Ограничение UNIQUE создает индекс для указанных столбцов. Этот индекс должен содержать уникальные ключи: любые две строки не должны содержать повторяющихся значений или комбинаций значений в указанных столбцах. Инструкция CREATE TABLE может иметь несколько ограничений UNIQUE, включая несколько столбцов, в определении которых есть ограничение UNIQUE, и/или несколько ограничений UNIQUE на уровне таблицы.

Ограничение CHECK определяет выражение, которое должно вычисляться и быть верным (true), чтобы можно было вставить или обновить данные строки. Выражение CHECK должно разрешаться в логическое значение.

Предложение COLLATE в определении столбца указывает, какую функцию сортировки текста следует использовать при сравнении текстовых записей в столбце. По умолчанию используется функция сортировки BINARY. Сведения о предложении COLLATE и функциях сортировки см. в разделе «COLLATE».

Ограничение DEFAULT определяет значение по умолчанию, которое должно использоваться при выполнении инструкции INSERT. В качестве значения может использоваться NULL, строковая константа или число. В качестве значения по умолчанию может также использоваться одно специальных из ключевых слов (без учета регистра): CURRENT_TIME, CURRENT_DATE или CURRENT_TIMESTAMP. Если используется значение NULL, строковая константа или число, оно буквально вставляется в столбец каждый раз, когда в инструкции INSERT не определено значение для столбца. Если используется значение CURRENT_TIME, CURRENT_DATE или CURRENT_TIMESTAMP, то в столбец вставляется текущая дата и/или время UTC. Для CURRENT_TIME используется формат HH:MM:SS (ЧЧ:ММ:СС). Для CURRENT_DATE используется формат YYYY-MM-DD (ГГГГ-ММ-ДД). Для CURRENT_TIMESTAMP используется формат YYYY-MM-DD HH:MM:SS (ГГГГ-ММ-ДД ЧЧ:ММ:СС.)

При указании ограничения PRIMARY KEY обычно просто создается индекс UNIQUE для соответствующих столбцов. Однако если ограничение PRIMARY KEY применяется только к одному столбцу с типом данных INTEGER (или если указан любой синоним, такой как int), то этот столбец используется базой данных как действительный первичный ключ для таблицы. Это означает, что данный столбец может содержать только уникальные целочисленные значения. (Обратите внимание, что во многих случаях внедрения SQLite только тип данных столбца INTEGER определяет функцию столбца как внутреннего первичного ключа, но в Adobe AIR это поведение также определяется синонимами INTEGER, такими как int.)

Если в таблице нет столбца INTEGER PRIMARY KEY, целочисленный ключ автоматически создается при вставке каждой строки. Первичный ключ для строки можно всегда получить с помощью одного из специальных имен: ROWID, OID или _ROWID_. Эти имена можно использовать независимо от того, объявляются они явно как INTEGER PRIMARY KEY или генерируются как внутреннее значение. Однако если в таблице явно объявлен столбец INTEGER PRIMARY KEY, имя столбца в данных результата соответствует действительному имени, а не специальному.

Столбец INTEGER PRIMARY KEY также может содержать ключевое слово AUTOINCREMENT. При использовании ключевого слова AUTOINCREMENT база данных автоматически генерирует и вставляет последовательно увеличиваемый целочисленный ключ в столбце INTEGER PRIMARY KEY, когда выполняется инструкция INSERT, в которой не указано явного значения для столбца.

В инструкции CREATE TABLE может быть только одно ограничение PRIMARY KEY. Оно может входить в состав определения одного столбца или быть единственным ограничением PRIMARY KEY на уровне таблицы. К столбцу первичного ключа применяется неявное ограничение NOT NULL.

Необязательный аргумент conflict-clause, следующий за ограничением, позволяет указать для данного ограничения альтернативный алгоритм решения конфликтов между ограничениями по умолчанию. По умолчанию используется значение ABORT. Для всех ограничений в одной таблице можно определить разные алгоритмы решения конфликтов по умолчанию. Если в инструкции INSERT или UPDATE указан другой алгоритм решения конфликтов, то он используется вместо алгоритма, определенного в инструкции CREATE TABLE. Дополнительные сведения см. в подразделе ON CONFLICT раздела «Специальные инструкции и предложения».

Дополнительные ограничения, такие как FOREIGN KEY, не приводят к появлению ошибки, но среда выполнения их игнорирует.

Если между ключевыми словами CREATE и TABLE встречается слово TEMP или TEMPORARY, то создаваемая таблица будет отображаться только в рамках текущего подключения к базе данных (экземпляра SQLConnection). Она автоматически удаляется после закрытия подключения к базе данных. Все индексы, созданные для временной таблицы, также являются временными. Временные таблицы и индексы хранятся в отдельном файле, а не в главном файле базы данных.

Если указан необязательный префикс имени базы данных database-name, то таблица создается в именованной базе данных (которая была подключена к экземпляру SQLConnection с помощью метода attach() с указанием имени базы данных). Нельзя одновременно использовать префикс database-name и ключевое слово TEMP, если префикс database-name не является temp. Если имя базы данных не указано и не используется ключевое слово TEMP, то таблица создается в главной базе данных (которая была подключена к экземпляру SQLConnection с помощью метода open() или openAsync()).

Не существует произвольных пределов для числа столбцов или ограничений в таблице. Также не установлено произвольных пределов для объема данных в строке.

Форма CREATE TABLE AS определяет таблицу как набор результатов запроса. Имена столбцов таблицы соответствуют именам столбцов в результате.

Если присутствует необязательное предложение IF NOT EXISTS и существует другая таблица с тем же именем, то база данных игнорирует команду CREATE TABLE.

Таблицу можно удалить с помощью инструкции DROP TABLE, а с помощью инструкции ALTER TABLE можно внести ряд изменений.

ALTER TABLE

Команда ALTER TABLE дает пользователю возможность переименовать или добавить столбец в существующей таблице. Удалить столбец из таблицы невозможно.

sql-statement ::= ALTER TABLE [database-name.] table-name alteration 
alteration    ::= RENAME TO new-table-name 
alteration    ::= ADD [COLUMN] column-def

Синтаксис RENAME TO можно использовать, чтобы присвоить таблице с именем, определенном параметром [database-name.] в аргументе table-name, новое имя, переданное в аргументе new-table-name. Эту команду нельзя использовать для перемещения таблицы из одной подключенной базы данных в другую. Она предназначена только для переименования таблицы в рамках одной базы данных.

Если в переименовываемой таблице есть триггеры или индексы, они останутся подключенными к таблице после ее переименования. Однако, если существуют определения или инструкции видов, которые выполняются триггерами, связанными с переименовываемой таблицей, они не изменяются автоматически для использования нового имени таблицы. Если переименованная таблица имеет связанные виды или триггеры, необходимо вручную удалить и воссоздать эти определения видов или триггеры, используя новое имя таблицы.

Синтаксис ADD [COLUMN] служит для добавления нового столбца в существующую таблицу. Новый столбец всегда добавляется в конце списка существующих столбцов. Предложение column-def может принимать любые формы, допустимые в инструкции CREATE TABLE, с учетом следующих ограничений.

  • Столбец не должен иметь ограничения PRIMARY KEY или UNIQUE.

  • Столбец не должен иметь значения по умолчанию CURRENT_TIME, CURRENT_DATE или CURRENT_TIMESTAMP.

  • Если указано ограничение NOT NULL, столбец не может иметь значения по умолчанию NULL.

На время выполнения инструкции ALTER TABLE не влияет объем данных в таблице.

DROP TABLE

Инструкция DROP TABLE удаляет таблицу, добавленную с помощью инструкции CREATE TABLE. Удаляется таблица, имя которой указано в аргументе table-name. Она полностью удаляется из базы данных и файла на диске. Таблицу невозможно восстановить. Все индексы, связанные с этой таблицей, также удаляются.
sql-statement  ::=  DROP TABLE [IF EXISTS] [database-name.] table-name

По умолчанию инструкция DROP TABLE не уменьшает размер файла базы данных. Пустое место в базе данных сохраняется и используется при последующих операциях INSERT. Чтобы удалить свободное место из базы данных, используйте метод SQLConnection.clean(). Если при создании базы данных параметру autoClean было задано значение true, место освобождается автоматически.

Необязательное предложение IF EXISTS подавляет появление ошибки, которая обычно выдается, если указанная таблица не существует.

CREATE INDEX

Команда CREATE INDEX содержит ключевые слова CREATE INDEX, за которыми следуют имя нового индекса, ключевое слово ON, имя созданной ранее таблицы, которую требуется проиндексировать, и заключенный в круглые скобки список имен столбцов в таблице, значения которых используются для ключа индекса.

sql-statement  ::=  CREATE [UNIQUE] INDEX [IF NOT EXISTS] [database-name.] index-name 
                    ON table-name ( column-name [, column-name]* ) 
column-name    ::=  name [COLLATE collation-name] [ASC | DESC]

После каждого имени столбца может следовать ключевое слово ASC или DESC, которое указывает порядок сортировки, но во время выполнения этот порядок игнорируется. Сортировка всегда осуществляется в порядке возрастания.

Предложение COLLATE после каждого имени столбца определяет последовательность сортировки, которая используется для текстовых значений в этом столбце. По умолчанию используется последовательность сортировки, определенная для данного столбца в инструкции CREATE TABLE. Если последовательность сортировки не указана, используется значение BINARY. Определение предложения COLLATE и функций сортировки см. в разделе «COLLATE».

Не существует произвольных ограничений для числа индексов, которые можно подключить к одной таблице. Также не установлено ограничений для числа столбцов в индексе.

DROP INDEX

Инструкция DROP INDEX удаляет индекс, добавленный с помощью инструкции CREATE INDEX. Указанный индекс полностью удаляется из файла базы данных. Восстановить индекс можно только путем повторного ввода соответствующей команды CREATE INDEX.

sql-statement ::= DROP INDEX [IF EXISTS] [database-name.] index-name

По умолчанию инструкция DROP INDEX не уменьшает размер файла базы данных. Пустое место в базе данных сохраняется и используется при последующих операциях INSERT. Чтобы удалить свободное место из базы данных, используйте метод SQLConnection.clean(). Если при создании базы данных параметру autoClean было задано значение true, место освобождается автоматически.

CREATE VIEW

Команда CREATE VIEW присваивает имя предварительно определенной инструкции SELECT. Это новое имя можно использовать в предложении FROM другой инструкции SELECT вместо имени таблицы. Виды часто применяются для упрощения запросов путем объединения сложных (и часто вызываемых) наборов данных в структуру, которую можно использовать в других операциях.

sql-statement ::= CREATE [TEMP | TEMPORARY] VIEW [IF NOT EXISTS] [database-name.] view-name AS select-statement

Если между ключевыми словами CREATE и VIEW встречается слово TEMP или TEMPORARY, то создаваемый вид будет отображаться только для текущего экземпляра SQLConnection, с помощью которого была открыта база данных. Он автоматически удаляется после закрытия базы данных.

Если указан префикс [database-name], вид создается в названной базе данных (которая была подключена к экземпляру SQLConnection с помощью метода attach() с указанием имени базы данных). Нельзя одновременно использовать префикс [database-name] и ключевое слово TEMP, если префикс [database-name] не является временным. Если имя базы данных не указано и не используется ключевое слово TEMP, то вид создается в главной базе данных (которая была подключена к экземпляру SQLConnection с помощью метода open() или openAsync()).

Виды доступны только для чтения. Для вида нельзя использовать инструкцию DELETE, INSERT или UPDATE, если определен хотя бы один триггер связанного типа (INSTEAD OF DELETE, INSTEAD OF INSERT, INSTEAD OF UPDATE). Сведения о создании триггера для вида см. в разделе «CREATE TRIGGER».

Вид удаляется из базы данных с помощью инструкции DROP VIEW.

DROP VIEW

Инструкция DROP VIEW удаляет вид, созданный с помощью инструкции CREATE VIEW.

sql-statement ::= DROP VIEW [IF EXISTS] view-name

Удаляется вид, имя которого указано в аргументе view-name. Вид удаляется из базы данных, но данные в самих таблицах остаются без изменений.

CREATE TRIGGER

Инструкция CREATE TRIGGER используется для добавления триггеров в схему базы данных. Триггер представляет собой операцию базы данных (trigger-action), которая автоматически выполняется в случае определенного события базы данных (database-event).

sql-statement   ::=  CREATE [TEMP | TEMPORARY] TRIGGER [IF NOT EXISTS] [database-name.] trigger-name 
                     [BEFORE | AFTER] database-event 
                     ON table-name 
                     trigger-action 
sql-statement   ::=  CREATE [TEMP | TEMPORARY] TRIGGER [IF NOT EXISTS] [database-name.] trigger-name 
                     INSTEAD OF database-event 
                     ON view-name 
                     trigger-action 
database-event  ::=  DELETE | 
                     INSERT | 
                     UPDATE | 
                     UPDATE OF column-list 
trigger-action  ::=  [FOR EACH ROW] [WHEN expr] 
                     BEGIN 
                       trigger-step ; 
                       [ trigger-step ; ]* 
                     END 
trigger-step    ::=  update-statement | 
                     insert-statement | 
                     delete-statement | 
                     select-statement 
column-list     ::=  column-name [, column-name]*

Триггер можно определить так, чтобы он запускался каждый раз, когда выполняется инструкция DELETE, INSERT или UPDATE для определенной таблицы базы данных, или каждый раз, когда обновляется инструкция UPDATE одного или нескольких указанных столбцов таблицы. Триггеры являются постоянными, если при их создании не используется ключевое слово TEMP или TEMPORARY. В таком случае триггер удаляется, когда закрывается подключение к главной базе данных, открытое экземпляром SQLConnection. Если не заданы временные рамки (BEFORE или AFTER), по умолчанию используется триггер BEFORE.

Поддерживаются только триггеры FOR EACH ROW, поэтому синтаксис FOR EACH ROW не является обязательным. При использовании триггера FOR EACH ROW инструкции trigger-step выполняются для каждой строки базы данных, вставляемой, обновляемой или удаляемой с помощью инструкции, запускающей триггер, если выражение предложения WHEN является верным.

Если используется предложение WHEN, инструкции SQL, указанные как trigger-step, выполняются только для строк, для которых верно предложение WHEN. Если предложение WHEN не определено, инструкции SQL выполняются для всех строк.

В теле триггера (предложение trigger-action) значения до и после изменения (pre-change и post-change) затрагиваемой таблицы можно получить с помощью специальных имен таблиц OLD и NEW. Структура таблиц OLD и NEW соответствует структуре таблицы, в которой создан триггер. Таблица OLD содержит строки, измененные или удаленные инструкцией, запустившей триггер, в том состоянии, в котором они находились до выполнения операций инструкции. Таблица NEW содержит строки, измененные или созданные инструкцией, запустившей триггер, в том состоянии, в котором они находятся после выполнения операций инструкции. И предложение WHEN и инструкции trigger-step могут получать значения из вставленной, удаленной или обновленной строки, используя ссылки формы NEW.column-name и OLD.column-name, где column-name — это имя столбца из таблицы, с которой связан триггер. Доступность ссылок таблиц OLD и NEW зависит от типа события database-event, обрабатываемого триггером.

  • INSERT: ссылки NEW являются действительными.

  • UPDATE: ссылки NEW и OLD являются действительными.

  • DELETE: ссылки OLD являются действительными.

Указанное время (BEFORE, AFTER или INSTEAD OF) определяет, когда выполняются инструкции trigger-step относительно вставки, изменения или удаления связанной строки. Предложение ON CONFLICT может быть включено в состав инструкции UPDATE или INSERT в trigger-step. Однако если предложение ON CONFLICT указано в составе инструкции, которая запускает триггер, то используется именно эта политика решения конфликтов.

Помимо триггеров таблицы, можно создать триггер INSTEAD OF для вида. Если для вида определен один или несколько триггеров INSTEAD OF INSERT, INSTEAD OF DELETE или INSTEAD OF UPDATE, то выполнение соответствующего типа инструкции (INSERT, DELETE, или UPDATE) для вида не считается ошибкой. В таком случае выполнение инструкции INSERT, DELETE или UPDATE для вида запускает соответствующие триггеры. Так как триггер относится к типу INSTEAD OF, таблицы, используемые в качестве основы вида, не изменяются инструкцией, которая запускает триггер. Однако триггеры можно использовать для выполнения операций модификации базовых таблиц.

При создании триггера для таблицы со столбцом INTEGER PRIMARY KEY важно помнить следующее. Если триггер типа BEFORE изменяет столбец INTEGER PRIMARY KEY строки, которая должна обновляться инструкцией, запускающий триггер, обновление не происходит. Чтобы обойти эту проблему, необходимо создать таблицу со столбцом PRIMARY KEY вместо INTEGER PRIMARY KEY.

Триггер можно удалить с помощью инструкции DROP TRIGGER. При удалении таблицы или вида автоматически удаляются и все связанные триггеры.

Функция RAISE ()

Специальную функцию SQL RAISE() можно использовать в инструкции триггера trigger-step. Эта функция имеет следующий синтаксис:

raise-function  ::=  RAISE ( ABORT, error-message ) | 
                     RAISE ( FAIL, error-message ) | 
                     RAISE ( ROLLBACK, error-message ) | 
                     RAISE ( IGNORE )

Когда одна из первых трех форм вызывается в процессе выполнения триггера, выполняется указанное действие обработки ON CONFLICT (ABORT, FAIL или ROLLBACK), а выполнение текущей инструкции прерывается. Действие ROLLBACK рассматривается как сбой выполнения инструкции, поэтому экземпляр SQLStatement, для которого выполнялся метод execute(), отправляет событие ошибки (SQLErrorEvent.ERROR). Объект SQLError в свойстве error отправленного объекта события имеет свойство details, которое содержит сообщение ошибки error-message, переданное в функции RAISE().

Когда вызывается функция RAISE(IGNORE), прекращается выполнение оставшейся части текущего триггера, инструкции, запустившей его выполнение, а также любых последующих триггеров. Откат изменений базы данных не выполняется. Если инструкция, запустившая выполнение триггера, является частью другого триггера, то выполнение этого триггера возобновляется с начала следующего шага. Дополнительные сведения об алгоритмах решения конфликтов см. в разделе «ON CONFLICT (алгоритмы конфликтов)».

DROP TRIGGER

Инструкция DROP TRIGGER удаляет триггер, созданный с помощью инструкции CREATE TRIGGER.

sql-statement  ::=  DROP TRIGGER [IF EXISTS] [database-name.] trigger-name

Триггер удаляется из базы данных. Обратите внимание, что триггеры автоматически удаляются при удалении связанной с ними таблицы.

Специальные инструкции и предложения

В этом разделе описано несколько предложений, которые являются расширениями SQL, предоставляемыми средой выполнения, а также два элемента языка, которые можно использовать во многих инструкциях, комментариях и выражениях.

COLLATE

Предложение COLLATE используется в инструкциях SELECT, CREATE TABLE и CREATE INDEX для определения алгоритма сравнения, который должен использоваться при сравнении или сортировке значений.

sql-statement   ::=  COLLATE collation-name 
collation-name  ::=  BINARY | NOCASE

По умолчанию для столбцов используется тип сортировки BINARY. Когда сортировка BINARY используется со значениями класса хранения TEXT, двоичная сортировка выполняется путем сравнения байтов в памяти, которые представляют значение независимо от кодировки текста.

Последовательность сортировки NOCASE применяется только к значениям класса хранения TEXT. При использовании сортировки NOCASE выполняется сравнение без учета регистра.

Последовательность сортировки не используется для классов хранения типа NULL, BLOB, INTEGER или REAL.

Чтобы использовать для столбца другой тип сортировки вместо BINARY, предложение COLLATE необходимо включить в определение столбца в инструкции CREATE TABLE. При любом сравнении двух значений TEXT используется последовательность сортировки для определения результатов сравнения в соответствии со следующими правилами.

  • Если при использовании операторов двоичного сравнения любой из операндов является столбцом, то тип сортировки, по умолчанию используемый для данного столбца, определяет последовательность сортировки, применяемую для выполнения сравнения. Если оба операнда являются столбцами, то используемая последовательность сортировки определяется по типу сортировки левого операнда. Если ни один из операндов не является столбцом, используется последовательность сортировки BINARY.

  • Оператор BETWEEN...AND равносилен использованию двух выражений с операторами >= и <=. Например, выражение x BETWEEN y AND z эквивалентно выражению x >= y AND x <= z. Таким образом, оператор BETWEEN...AND следует описанному правилу для определения последовательности сортировки.

  • Оператор IN по своему поведению напоминает оператор =, используемый для определения последовательности сортировки. Например, последовательность сортировки, используемая для выражения x IN (y, z), соответствует типу сортировки по умолчанию для x, если x является столбцом. В противном случае используется сортировка BINARY.

  • Предложению ORDER BY, которое входит в состав инструкции SELECT, можно явно назначить последовательность сортировки, которая должна использоваться для данной операции. В таком случае всегда используется явно назначенная последовательность сортировки. И наоборот, если выражение, сортируемое предложением ORDER BY, является столбцом, порядок сортировки определяется по типу сортировки, используемому по умолчанию для столбца. Если выражение не является столбцом, используется последовательность сортировки BINARY.

EXPLAIN

Модификатор команды EXPLAIN является нестандартным расширением SQL.

sql-statement  ::=  EXPLAIN sql-statement

Если ключевое слово EXPLAIN стоит перед любой другой инструкцией SQL, то вместо фактического выполнения команды результат сообщает последовательность инструкций виртуальной машины, которые бы использовались для выполнения команды, если бы не было ключевого слова EXPLAIN. EXPLAIN представляет собой расширенную функцию, с помощью которой разработчики могут изменять текст инструкции SQL, чтобы оптимизировать производительность или отладить некорректно работающую инструкцию.

ON CONFLICT (алгоритмы конфликтов)

Предложение ON CONFLICT не является отдельной командой SQL. Это нестандартное предложение, которое может встречаться во многих других командах SQL.

conflict-clause     ::=  ON CONFLICT conflict-algorithm 
conflict-clause     ::=  OR conflict-algorithm 
conflict-algorithm  ::=  ROLLBACK | 
                         ABORT | 
                         FAIL | 
                         IGNORE | 
                         REPLACE

Первая форма предложения ON CONFLICT, в которой используются ключевые слова ON CONFLICT, применяется в инструкции CREATE TABLE. Для инструкции INSERT или UPDATE используется вторая форма, где ключевые слова ON CONFLICT меняются на OR, чтобы придать синтаксису более естественный вид. Например, вместо INSERT ON CONFLICT IGNORE инструкция принимает вид INSERT OR IGNORE. Хотя используются разные ключевые слова, обе формы имеют один и тот же смысл предложения.

Предложение ON CONFLICT определяет алгоритм, которые используется для разрешения конфликтов ограничений. Существует пять алгоритмов: ROLLBACK, ABORT, FAIL, IGNORE и REPLACE. По умолчанию используется алгоритм ABORT. Ниже приводится объяснение пяти алгоритмов конфликтов.

ROLLBACK
В случае нарушения ограничения сразу выполняется откат (ROLLBACK), что завершает текущую транзакцию. Выполнение команды прерывается, и экземпляр SQLStatement отправляет событие ошибки. Если нет активных транзакций (кроме подразумеваемой транзакции, которая создается для каждой команды), этот алгоритм ведет себя так же, как ABORT.

ABORT
В случае нарушения ограничения, команда отменяет все свои внесенные ранее изменения, и экземпляр SQLStatement отправляет событие ошибки. Откат (ROLLBACK) не выполняется, поэтому изменения, выполненные предшествующими командами в рамках транзакции, сохраняются. Алгоритм ABORT используется как поведение по умолчанию.

FAIL
В случае нарушения ограничения выполнение команды прекращается и экземпляр SQLStatement отправляет событие ошибки. Однако все изменения, которые внесла инструкция в базу данных до нарушения, сохраняются и не отменяются. Например, если при выполнении инструкции UPDATE встречается нарушение ограничения в строке 100, которую требуется обновить, то изменения в первых 99 строках сохраняются, а строка 100 и следующие за ней остаются без изменений.

IGNORE
В случае нарушения ограничения та строка, которая содержит это нарушение, не вставляется или не изменяется. Данная строка игнорируется, и выполнение команды продолжается. Другие строки до и после той, которая содержит нарушение ограничения, вставляются или обновляются в обычном порядке. Ошибка при этом не возникает.

REPLACE
Когда возникает нарушение ограничения UNIQUE, перед вставкой или обновлением текущей строки удаляются существующие строки, вызвавшие это нарушение. Следовательно, вставка или обновление происходит всегда, и выполнение команды продолжается. Ошибка при этом не возникает. Если происходит нарушение ограничения NOT NULL, значение NULL заменяется значением по умолчанию, установленным для данного столбца. Если для столбца не задано значение по умолчанию, используется алгоритм ABORT. Если происходит нарушение ограничения CHECK, используется алгоритм IGNORE. Когда эта стратегия решения конфликтов удаляет строку, чтобы реализовать ограничение, триггер удаления в этой строке не запускается.

Алгоритм, определенный в предложении OR инструкции INSERT или UPDATE, переопределяет любой алгоритм, указанный в инструкции CREATE TABLE. Если алгоритм не указан в инструкции CREATE TABLE или в выполняемой инструкции INSERT или UPDATE, используется алгоритм ABORT.

REINDEX

Команда REINDEX служит для удаления и воссоздания одного или нескольких индексов. Эта команда полезна, когда происходит изменение определения последовательности сортировки.

sql-statement  ::=  REINDEX collation-name 
sql-statement  ::=  REINDEX [database-name .] ( table-name | index-name )

Первая форма воссоздает все индексы во всех подключенных базах данных, которые используют названную последовательность сортировки. Вторая форма перестраивает все индексы, связанные с таблицей, имя которой указано в аргументе table-name. Если в аргументе index-name задано имя индекса, удаляется и воссоздается только указанный индекс.

КОММЕНТАРИИ

Комментарии не являются командами SQL, но они могут встречаться в запросах SQL. Среда выполнения рассматривает их как пробел. Комментарии могут начинаться в любом месте, где может быть пробел, в том числе внутри многострочных выражений.

comment             ::=  single-line-comment | 
                         block-comment 
single-line-comment ::=  -- single-line 
block-comment       ::=  /* multiple-lines or block [*/]

Однострочный комментарий обозначается двумя дефисами. Однострочный комментарий может продолжаться только до конца текущей строки.

Блоки комментариев могут быть многострочными или встраиваться в одну строку. Если отсутствует конечный ограничитель, блок комментариев продолжается до конца ввода. Такая ситуация не рассматривается как ошибка. Новая инструкция SQL может начаться в строке после окончания блока комментариев. Блоки комментариев могут встраиваться в любом месте, где может находиться пробел, в том числе внутри выражений, и в середине других выражений SQL. Блоки комментариев не могут быть вложенными. Однострочные комментарии внутри блока игнорируются.

ВЫРАЖЕНИЯ

Выражения — это подкоманды внутри других блоков SQL. Ниже описывается допустимый синтаксис для выражений внутри инструкции SQL.

expr            ::=  expr binary-op expr | 
                     expr [NOT] like-op expr [ESCAPE expr] | 
                     unary-op expr | 
                     ( expr ) | 
                     column-name | 
                     table-name.column-name | 
                     database-name.table-name.column-name | 
                     literal-value | 
                     parameter | 
                     function-name( expr-list | * ) | 
                     expr ISNULL | 
                     expr NOTNULL | 
                     expr [NOT] BETWEEN expr AND expr | 
                     expr [NOT] IN ( value-list ) | 
                     expr [NOT] IN ( select-statement ) | 
                     expr [NOT] IN [database-name.] table-name | 
                     [EXISTS] ( select-statement ) | 
                     CASE [expr] ( WHEN expr THEN expr )+ [ELSE expr] END | 
                     CAST ( expr AS type ) | 
                     expr COLLATE collation-name 
like-op         ::=  LIKE | GLOB 
binary-op       ::=  see Operators 
unary-op        ::=  see Operators 
parameter       ::=  :param-name | @param-name | ? 
value-list      ::=  literal-value [, literal-value]* 
literal-value   ::=  literal-string | literal-number | literal-boolean | literal-blob | literal-null 
literal-string  ::=  'string value' 
literal-number  ::=  integer | number 
literal-boolean  ::=  true | false 
literal-blob  ::=  X'string of hexadecimal data' 
literal-null  ::=  NULL

Выражением является любая комбинация значений и операторов, которые могут разрешаться в одно значение. Выражения можно разделить на два общих типа в соответствии с тем, разрешаются они в логическое значение (true или false) или в нелогическое значение.

В нескольких распространенных ситуациях (включая предложение WHERE, предложение HAVING, выражение ON в предложении JOIN и выражение CHECK) выражение должно разрешаться в логическое значение. Этому условию отвечают следующие типы выражений:

  • ISNULL

  • NOTNULL

  • IN ()

  • EXISTS ()

  • LIKE

  • GLOB

  • Определенные функции

  • Определенные операторы (в частности, операторы сравнения)

Значения литерала

Числовое значение литерала записывается как целое число или число с плавающей запятой. Поддерживается экспоненциальное представление. В качестве разделителя десятичной дроби всегда используется символ. (точка).

Строковый литерал обозначается заключением строки в одинарные кавычки '. Чтобы включить в строку одинарную кавычку, поставьте две одинарные кавычки подряд, например: ''.

Логический литерал определяется по значению true (истина) или false (ложь). Логические значения литерала используются вместе с логическим типом данных столбца.

Литерал большого двоичного объекта (BLOB) представляет собой строковый литерал, содержащий шестнадцатеричные данные и один символ x или X в начале, например X'53514697465'.

В качестве значения литерала также может использоваться маркер NULL.

Имя столбца

В качестве имени столбца могут использоваться любые имена, определенные в инструкции CREATE TABLE, или один из следующих специальных идентификаторов: ROWID, OID или _ROWID_. Все эти специальные идентификаторы описывают уникальный произвольный целочисленный ключ («ключ строки»), связанный с каждой строкой в каждой таблице. Специальные идентификаторы ссылаются на ключ строки, только если в инструкции CREATE TABLE не определен реальный столбец с тем же именем. Ключи строк ведут себя как столбцы, доступные только для чтения. Ключ строки можно использовать везде, где используются обычные столбцы, с тем исключением, что значение ключа строки нельзя изменить в инструкции UPDATE или INSERT. Инструкция таблицы SELECT * FROM не включает ключ строки в набор результатов.

Инструкция SELECT

Инструкция SELECT может встречаться в выражении как правый операнд оператора IN, как скалярное количество (одно значение результата) или как операнд оператора EXISTS. При использовании в качестве скалярного количества или операнда оператора IN инструкция SELECT может иметь в результате только один столбец. Допускается использование составной инструкции SELECT (соединенной с помощью таких ключевых слов, как UNION или EXCEPT). При использовании оператора EXISTS столбцы в наборе результатов инструкции SELECT игнорируются и выражение возвращает значение TRUE, если существует одна или несколько строк, или FALSE, если набор результатов пустой. Если в выражении SELECT ни один термин не ссылается на значение в запросе-контейнере, то выражение вычисляется один раз перед любой другой обработкой, при необходимости результат можно использовать повторно. Если выражение SELECT содержит переменные из внешнего запроса, который называется связанным подзапросом, оно повторно вычисляется при каждом использовании.

Когда SELECT является правым операндом оператора IN, оператор IN возвращает значение TRUE, если результат левого операнда равен любому значению в наборе результатов инструкции SELECT. Перед оператором IN может стоять ключевое слово NOT, которое меняет смысл проверки на обратный.

Когда инструкция SELECT встречается в выражении, но не в правой части оператора IN, в выражении используется первая строка результата SELECT. Если инструкция SELECT возвращает несколько строк результатов, все строки, следующие за первой, игнорируются. Если инструкция SELECT не возвращает строк, SELECT имеет значение NULL.

Выражение CAST

Выражение CAST меняет тип данных указанного значения на заданный. В качестве типа может быть указано любое непустое имя, которое является действительным для типа в определении столбца в инструкции CREATE TABLE. Дополнительные сведения см. в разделе «Поддержка типов данных».

Дополнительные элементы выражений

Следующие элементы SQL также можно использовать в выражениях.

  • Встроенные функции: агрегатные функции, скалярные функции, функции форматирования даты и времени

  • Операторы

  • Параметры

Встроенные функции

Встроенные функции делятся на три основных категории:
  • агрегатные функции;

  • скалярные функции;

  • функции даты и времени.

Помимо этих функций, существует специальная функция RAISE(), которая служит для уведомления об ошибках, возникающих при выполнении триггера. Эту функцию можно использовать только в теле инструкции CREATE TRIGGER. Сведения о функции RAISE() см. в разделе «CREATE TRIGGER > RAISE()».

Как и во всех ключевых словах SQL, в именах функций не учитывается регистр.

Агрегатные функции

Агрегатные функции выполняют операции со значениями из нескольких строк. Эти функции главным образом используются в инструкциях SELECT вместе с предложением GROUP BY.

AVG(X)

Возвращает среднее значение всех ненулевых строк X в группе. Строковые значения и значения больших двоичных объектов (BLOB), которые не похожи на числа, интерпретируются как 0. Результатом функции AVG() всегда является значение с плавающей запятой, даже если все вводные числа являются целыми.

COUNT(X) COUNT(*)

Первая форма возвращает количество ненулевых строк X в группе. Вторая форма (с аргументом *) возвращает общее число строк в группе.

MAX(X)

Возвращает максимальное из всех значений в группе. Для определения максимального значения используется обычный порядок сортировки.

MIN(X)

Возвращает минимальное ненулевое значение из всех значений в группе. Для определения минимального значения используется обычный порядок сортировки. Если все значения в группе нулевые, возвращается значение NULL.

SUM(X)

TOTAL(X)

Возвращает числовую сумму всех ненулевых значений в группе. Если все значения нулевые, функция SUM() возвращает NULL, а функция TOTAL() возвращает 0.0. Результат функции TOTAL() всегда является значением с плавающей запятой. Результат функции SUM() является целым числом, если все ненулевые значения являются целыми числами. Если любое число, переданное функции SUM(), не является целым и не равно нулю, то SUM() возвращает значение с плавающей запятой. Действительная сумма может быть округлена до приближенного значения.

В любой предшествующей агрегатной функции, которая принимает один аргумент, перед этим аргументом может стоять ключевое слово DISTINCT. В таком случае повторяющиеся элементы отфильтровываются перед передачей результата следующей агрегатной функции. Например, вызов функции COUNT(DISTINCT x) возвращает число разных значений в столбце X, а не общее число ненулевых значений.

Скалярные функции

Скалярные функции выполняют операции со значениями только одной строки.

ABS(X)

Возвращает абсолютное значение аргумента X.

COALESCE(X, Y, ...)

Возвращает копию первого ненулевого аргумента. Если все аргументы нулевые, возвращается результат NULL. Должно быть не меньше двух аргументов.

GLOB(X, Y)

Эта функция служит для реализации синтаксиса X GLOB Y.

IFNULL(X, Y)

Возвращает копию первого ненулевого аргумента. Если оба аргумента нулевые, возвращается результат NULL. Эта функция действует аналогично COALESCE().

HEX(X)

Аргумент интерпретируется как значение типа хранения BLOB. В качестве результата возвращается шестнадцатеричное представление содержимого этого значения.

LAST_INSERT_ROWID()

Возвращает идентификатор (сгенерированный первичный ключ) последней строки, вставленной в базу данных с помощью текущего экземпляра SQLConnection. Это значение идентично значению, возвращаемому свойством SQLConnection.lastInsertRowID.

LENGTH(X)

Возвращает длину строки X (число символов).

LIKE(X, Y [, Z])

Эта функция служит для реализации синтаксиса X LIKE Y [ESCAPE Z] в SQL. Если используется необязательное предложение ESCAPE, то функция вызывается с тремя аргументами. В противном случае она вызывается только с двумя аргументами.

LOWER(X)

Возвращает копию строки X, все символы которой преобразованы в нижний регистр.

LTRIM(X) LTRIM(X, Y)

Возвращает строку, полученную в результате удаления пробела с левой стороны X. Если указан аргумент Y, функция удаляет все символы в Y, которые находятся с левой стороны от X.

MAX(X, Y, ...)

Возвращает аргумент с максимальным значением. В качестве аргументов могут использоваться как числа, так и строки. Максимальное значение определяется с использованием порядка сортировки по умолчанию. Обратите внимание, что MAX() является простой функцией, когда имеет 2 или более аргументов, но если используется только один аргумент, она работает как агрегатная функция.

MIN(X, Y, ...)

Возвращает аргумент с минимальным значением. В качестве аргументов могут использоваться как числа, так и строки. Минимальное значение определяется с использованием порядка сортировки по умолчанию. Обратите внимание, что MIN() является простой функцией, когда имеет 2 или более аргументов, но если используется только один аргумент, она работает как агрегатная функция.

NULLIF(X, Y)

Возвращает первый аргумент, если аргументы разные. В противном случае возвращает NULL.

QUOTE(X)

Эта подпрограмма возвращает строку, которая является значением ее аргумента, пригодного для включения в другую инструкцию SQL. Строки окружены одинарными кавычками. При необходимости для внутренних кавычек применяется маскировка. Классы хранения BLOB кодируются как шестнадцатеричные литералы. Эта функция полезна для обеспечения возможности отмены или повтора действия при создании триггеров.

RANDOM(*)

Возвращает псевдопроизвольное целое число из диапазона от 9223372036854775808 до 9223372036854775807. Это произвольное значение не обеспечивает надежное шифрование.

RANDOMBLOB(N)

Возвращает большой двоичный объект размером N байт, который содержит псевдопроизвольные байты. В качестве N должно использоваться положительное целое число. Это произвольное значение не обеспечивает надежное шифрование. Если в N имеет отрицательное значение, возвращается один байт.

ROUND(X) ROUND(X, Y)

Округляет число X до Y знаков справа от разделителя десятичной дроби. Если аргумент Y опущен, используется значение 0.

RTRIM(X) RTRIM(X, Y)

Возвращает строку, полученную в результате удаления пробела с правой стороны X. Если указан аргумент Y, функция удаляет все символы в Y, которые находятся с правой стороны от X.

SUBSTR(X, Y, Z)

Возвращает подстроку вводной строки X, которая начинается с Y-го символа и имеет длину Z символов. Крайний левый символ строки X имеет индекс 1. Если Y является отрицательным числом, первый символ подстроки определяется путем отсчета от правого края, а не левого.

TRIM(X) TRIM(X, Y)

Возвращает строку, полученную в результате удаления пробела с правой стороны X. Если указан аргумент Y, функция удаляет все символы в Y, которые находятся с правой стороны от X.

TYPEOF(X)

Возвращает тип выражения X. Возможные возвращаемые значения: null, integer, real, text и blob. Дополнительные сведения о типах данных см. в разделе «Поддержка типов данных».

UPPER(X)

Возвращает копию вводной строки X, преобразованную в верхний регистр.

ZEROBLOB(N)

Возвращает большой двоичный объект, содержащий N байт со значением 0x00.

Функции форматирования даты и времени

Функции форматирования даты и времени представляют собой группу скалярных функций, которые служат для создания форматированных данных даты и времени. Обратите внимание, что эти функции выполняют операции со значениями, содержащими строки и числа, и возвращают соответствующие результаты. Эти функции не предназначены для использования с типом данных DATE. Если их использовать для данных в столбце, для которого объявлен тип данных DATE, они работают некорректно.

DATE(T, ...)

Функция DATE() возвращает строку, содержащую дату в формате YYYY-MM-DD (ГГГГ-ММ-ДД). Первый параметр (T) определяет строку времени в формате, описанном в разделе «Форматы времени». После строки времени можно указать любое число модификаторов. Модификаторы перечислены в разделе «Модификаторы».

TIME(T, ...)

Функция TIME() возвращает строку, содержащую время в формате HH:MM:SS (ЧЧ:ММ:СС). Первый параметр (T) определяет строку времени в формате, описанном в разделе «Форматы времени». После строки времени можно указать любое число модификаторов. Модификаторы перечислены в разделе «Модификаторы».

DATETIME(T, ...)

Функция DATETIME() возвращает строку, содержащую дату и время в формате YYYY-MM-DD HH:MM:SS (ГГГГ-ММ-ДД ЧЧ:ММ:СС). Первый параметр (T) определяет строку времени в формате, описанном в разделе «Форматы времени». После строки времени можно указать любое число модификаторов. Модификаторы перечислены в разделе «Модификаторы».

JULIANDAY(T, ...)

Функция JULIANDAY() возвращает число, обозначающее количество дней, прошедшее с полудня по Гринвичу 24 ноября 4714 года до нашей эры, и заданную дату. Первый параметр (T) определяет строку времени в формате, описанном в разделе «Форматы времени». После строки времени можно указать любое число модификаторов. Модификаторы перечислены в разделе «Модификаторы».

STRFTIME(F, T, ...)

Подпрограмма STRFTIME() возвращает дату, отформатированную в соответствии со строкой формата, указанной в качестве первого аргумента F. Строка формата поддерживает следующие подстановки:

%d — день месяца

%f — дробные секунды SS.SSS

%H — час 00-24

%j — день года 001-366

%J — номер дня по юлианскому календарю

%m — месяц 01-12

%M — минута 00-59

%s — число секунд с 1970-01-01

%S — секунды 00-59

%w — день недели 0-6 (воскресенье = 0)

%W — неделя года 00-53

%Y — год 0000-9999

%% - %

Второй параметр (T) определяет строку времени в формате, описанном в разделе «Форматы времени». После строки времени можно указать любое число модификаторов. Модификаторы перечислены в разделе «Модификаторы».

Форматы времени

Строка времени может иметь любой из перечисленных ниже форматов.

YYYY-MM-DD

2007-06-15

YYYY-MM-DD HH:MM

2007-06-15 07:30

YYYY-MM-DD HH:MM:SS

2007-06-15 07:30:59

YYYY-MM-DD HH:MM:SS.SSS

2007-06-15 07:30:59.152

YYYY-MM-DDTHH:MM

2007-06-15T07:30

YYYY-MM-DDTHH:MM:SS

2007-06-15T07:30:59

YYYY-MM-DDTHH:MM:SS.SSS

2007-06-15T07:30:59.152

HH:MM

07:30 (дата 2000-01-01)

HH:MM:SS

07:30:59 (дата 2000-01-01)

HH:MM:SS.SSS

07:30:59:152 (дата 2000-01-01)

now

Текущая дата и время по универсальному времени (UCT).

DDDD.DDDD

Номер дня по юлианскому календарю, представленный в виде числа с плавающей запятой.

Символ T в этих форматах является символом литерала «T», который разделяет дату и время. Форматы, которые содержат только время, предполагают дату 2001-01-01.

Модификаторы

После строки времени может следовать ноль или больше модификаторов, которые изменяют дату или ее интерпретацию. Доступные модификаторы перечислены ниже.

NNN days

Число дней, которые требуется добавить ко времени.

NNN hours

Число часов, которые требуется добавить ко времени.

NNN minutes

Число минут, которые требуется добавить ко времени.

NNN.NNNN seconds

Число секунд и миллисекунд, которые требуется добавить ко времени

NNN months

Число месяцев, которые требуется добавить ко времени.

NNN years

Число лет, которые требуется добавить ко времени.

start of month

Смещает время назад к началу месяца.

start of year

Смещает время назад к началу года.

start of day

Смещает время назад к началу дня.

weekday N

Смещает время вперед к указанному дню недели. (0 = воскресенье, 1 = понедельник и т. д.)

localtime

Преобразует дату в местное время.

utc

Преобразует дату в универсальное время (UCT).

Операторы

SQL поддерживает большой набор операторов, включая общие операторы, которые используются в большинстве языков программирования, а также несколько операторов, которые применяются только в SQL.

Общие операторы

В блоках SQL разрешается использовать следующие двоичные операторы, перечисленные в порядке приоритета от самого высокого до самого низкого.

*    /    % 
+    - 
<< >> &     | 
< >=   > >= 
=    ==   !=   <> IN 
AND 
OR

Ниже перечислены поддерживаемые унарные префиксные операторы.

 !    ~    NOT

Оператор COLLATE можно рассматривать как унарный постфиксный оператор. Оператор COLLATE имеет самый высокий приоритет. Он всегда обеспечивает более крепкую привязку, чем любой другой унарный префиксный оператор или двоичный оператор.

Обратите внимание, что существует два варианта операторов равенства и неравенства. Равенство может быть выражено как = или ==. В качестве оператора неравенства может использоваться != или <>.

Оператор || является оператором сцепления строк, он объединяет две строки операндов.

Оператор % выводит остаток левого операнда по модулю правого операнда.

Результат любого двоичного оператора является числовым значением, за исключением оператора сцепления ||, результатом которого является строка.

Операторы SQL

LIKE

Оператор LIKE выполняет сравнение для сопоставления с шаблоном.

expr     ::=  (column-name | expr) LIKE pattern 
pattern  ::=  '[ string | % | _ ]'

Операнд справа от оператора LIKE содержит шаблон, а левый операнд содержит строку, которую требуется сравнить с шаблоном. Символ процента (%) в шаблоне является символом подстановки. Он заменяет любую последовательность из нуля или более символов в строке. Символ подчеркивания (_) в шаблоне соответствует любому одному символу в строке. Любой другой символ соответствует самому себе или своему эквиваленту нижнего или верхнего регистра, то есть сопоставление выполняется без учета регистра. (Примечание. Ядро базы данных распознает верхний и нижний регистр только для 7-битовых латинских символов. По этой причине оператор LIKE учитывает регистр для 8-битовых символов iso8859 и для символов UTF-8. Например, выражение 'a' LIKE 'A' возвращает значение TRUE, а 'æ' LIKE 'Æ' возвращает FALSE). Чувствительность к регистру латинских символов можно изменить с помощью свойства SQLConnection.caseSensitiveLike.

Если присутствует необязательное предложение ESCAPE, то выражение, следующее за ключевым словом ESCAPE, должно содержать строку, состоящую из одного символа. Этот символ можно использовать в шаблоне LIKE для сопоставления символов процента или прочерка литерала. Символ маскировки, за которым следует символ процента, прочерка или он сам, соответствует символу процента, прочерка или маскировки литерала в строке.

GLOB

Оператор GLOB похож на LIKE, но использует синтаксис глобализации файлов Unix для подстановочных знаков. В отличие от LIKE, оператор GLOB учитывает регистр.

IN

Оператор IN вычисляет, равен ли левый операнд одному из значений в правом операнде (из набора значений в круглых скобках).

in-expr         ::=  expr [NOT] IN ( value-list ) | 
                     expr [NOT] IN ( select-statement ) | 
                     expr [NOT] IN [database-name.] table-name 
value-list      ::=  literal-value [, literal-value]*

Правый операнд может содержать разделенные запятыми значения литералов или результат инструкции SELECT. Объяснения и ограничения использования инструкции SELECT в качестве правого операнда оператора IN см. в подразделе «Инструкция SELECT» раздела «Выражения».

BETWEEN...AND

Оператор BETWEEN...AND равносилен использованию двух выражений с операторами >= и <=. Например, выражение x BETWEEN y AND z эквивалентно выражению x >= y AND x <= z.

NOT

Оператор NOT выполняет функцию отрицания. Перед операторами GLOB, LIKE и IN может стоять ключевое слово NOT, которое меняет смысл проверки на обратный (другими словами, требуется проверить, что значение не соответствует указанному шаблону).

Параметры

Параметр указывает местозаполнитель в выражении для значения литерала, который заполняется во время выполнения путем присвоения значения в ассоциативный массив SQLStatement.parameters. Параметры могут принимать три формы.

?

Знак вопроса обозначает индексированный параметр. Параметрам присваиваются числовые значения индекса (от нуля) в соответствии с их порядком в инструкции.

:AAAA

Двоеточие, за которым следует имя идентификатора, сохраняет место для именованного параметра с именем AAAA. Именованные параметры также нумеруются в соответствии с их порядком в инструкции SQL. Во избежание путаницы лучше не смешивать именованные параметры с нумерованными.

@AAAA

Символ «собака» равносилен двоеточию.

Неподдерживаемые возможности SQL

Ниже приводится список стандартных элементов SQL, которые не поддерживаются в Adobe AIR.
Ограничения FOREIGN KEY
Ограничения FOREIGN KEY подвергаются синтаксическому анализу, но не применяются.

Триггеры
Триггеры FOR EACH STATEMENT не поддерживаются (все триггеры должны быть FOR EACH ROW). Триггеры INSTEAD OF не поддерживаются в таблицах (их использование допускается только в видах). Рекурсивные триггеры (то есть те, которые запускают сами себя) не поддерживаются.

ALTER TABLE
Поддерживаются только варианты RENAME TABLE и ADD COLUMN команды ALTER TABLE. Другие типы операций ALTER TABLE, такие как DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT и т. д., игнорируются.

Вложенные транзакции
Допускается только одна активная транзакция.

RIGHT и FULL OUTER JOIN
RIGHT OUTER JOIN и FULL OUTER JOIN не поддерживаются.

Обновляемый вид
Виды доступны только для чтения. Для видов нельзя выполнять инструкции DELETE, INSERT или UPDATE. Поддерживается триггер INSTEAD OF, который запускается при попытке удаления, вставки или обновления вида (DELETE, INSERT или UPDATE). С его помощью можно обновлять базовые таблицы в теле триггера.

GRANT и REVOKE
База данных представляет собой обычный файл на диске. К ней можно применять только обычные права доступа к файлу, которые поддерживаются существующей операционной системой. Команды GRANT и REVOKE, которые широко используются в клиентских и серверных RDBMS, не реализуются.

Следующие элементы SQL и возможности SQLite поддерживаются в некоторых версиях SQLite, но не поддерживаются в Adobe AIR. Большинство этих возможностей обеспечиваются с помощью методов класса SQLConnection.
Элементы SQL, связанные с транзакциями (BEGIN, END, COMMIT, ROLLBACK)
Эти возможности обеспечиваются с помощью методов класса SQLConnection, связанных с транзакциями: SQLConnection.begin(), SQLConnection.commit() и SQLConnection.rollback().

ANALYZE
Эта функция обеспечивается с помощью метода SQLConnection.analyze().

ATTACH
Эта функция обеспечивается с помощью метода SQLConnection.attach().

COPY
Эта инструкция не поддерживается.

CREATE VIRTUAL TABLE
Эта инструкция не поддерживается.

DETACH
Эта функция обеспечивается с помощью метода SQLConnection.detach().

PRAGMA
Эта инструкция не поддерживается.

VACUUM
Эта функция обеспечивается с помощью метода SQLConnection.compact().

Доступ к системным таблицам не поддерживается.
Системные таблицы, включая sqlite_master и другие таблицы с префиксом «sqlite_», недоступны в инструкциях SQL. Среда выполнения включает API-интерфейс схемы, который обеспечивает объектно-ориентированный метод доступа к данным схемы. Дополнительные сведения см. в описании метода SQLConnection.loadSchema().

Функции регулярных выражений (MATCH() и REGEX())
Эти функции недоступны в инструкциях SQL.

Следующие функциональные возможности отличаются во многих версиях SQLite и Adobe AIR.

Индексированные параметры инструкций
Во многих версиях параметры инструкций индексируются от единицы. Однако в Adobe AIR параметры инструкции индексируются от нуля (то есть первому параметру присваивается индекс 0, второму — 1 и т. д.).

Определения столбцов INTEGER PRIMARY KEY
Во многих версиях в качестве действительного столбца первичного ключа таблицы используются только столбцы, которые явно определены как INTEGER PRIMARY KEY. В таких версиях указание других типов данных, которые являются синонимами INTEGER (например, int), не приводит к тому, что столбец используется в качестве внутреннего первичного ключа. Однако в Adobe AIR тип данных int (или другие синонимы INTEGER) рассматриваются как точное соответствие INTEGER. По этой причине столбец, определенный как int PRIMARY KEY, используется в качестве внутреннего первичного ключа таблицы. Дополнительные сведения см. в разделах «CREATE TABLE» и «Сходство столбцов».

Дополнительные возможности SQL

Следующие типы сходства столбцов по умолчанию в SQLite не поддерживаются, но поддерживаются в Adobe AIR. (Обратите внимание, что, как и во всех ключевых словах в SQL, в этих именах типов данных не учитывается регистр).
Boolean
соответствует классу Boolean.

Date
соответствует классу Date.

int
соответствует классу int (эквивалентно сходству столбца INTEGER).

Number
соответствует классу Number (эквивалентно сходству столбца REAL).

Object
соответствует классу Object или любому подклассу, сериализацию и десериализацию которого можно выполнить с помощью AMF3. (Сюда относится большинство классов, в том числе пользовательских, кроме некоторых классов, которые включают экранные объекты и объекты и объекты, свойства которых содержат экранные объекты.)

String
соответствует классу String (эквивалентно сходству столбца TEXT).

XML
соответствует классу XML в ActionScript (E4X).

XMLList
соответствует классу XMLList в ActionScript (E4X).

Следующие значения литералов не поддерживаются по умолчанию в SQLite, но поддерживаются в Adobe AIR.

true
используется для представления логического значения литерала true, предназначено для работы со столбцами BOOLEAN.

false
используется для представления логического значения литерала false, предназначено для работы со столбцами BOOLEAN.