サポートされている SQL 構文

Adobe AIR SQL データベースエンジンでサポートされている SQL 構文について、ステートメントと句の種類、式、ビルトイン関数および演算子に分けて説明します。ここで取り上げるトピックは次のとおりです。
  • SQL 構文全般

  • データ操作ステートメント(SELECT、INSERT、UPDATE および DELETE)

  • データ定義ステートメント(テーブル、インデックス、ビューおよびトリガーの CREATE ステートメント、ALTER ステートメントおよび DROP ステートメント)

  • 特殊な文と句

  • ビルトイン関数(集計関数、スカラ関数、および日付/時刻書式設定関数)

  • 演算子

  • パラメーター

  • サポートされていない SQL 機能

  • 追加の SQL 機能

SQL 構文全般

各種の文と式に関する具体的な構文に加えて、SQL 構文には一般に次のような規則があります。
大文字と小文字の区別
SQL ステートメント(オブジェクト名を含む)では、大文字と小文字は区別されません。ただし、SQL 文では SQL キーワードが大文字で表記されることが多いので、このドキュメントでもこの表記規則を使用します。SQL シンタックスでは大文字と小文字が区別されませんが、SQL のリテラルテキスト値は大文字と小文字が区別され、比較およびソート操作では、列または操作に対して定義されている照合シーケンスの指定に従って大文字と小文字を区別できます。詳しくは、「COLLATE」を参照してください。

空白
SQL ステートメントでは、個々の単語を区切るために空白文字(スペース、タブ、改行など)を使用する必要があります。ただし、単語と記号の間の空白はオプションです。SQL ステートメントに含まれる空白文字の種類と個数は意味を持ちません。インデントや改行などの空白文字を使用して SQL 文を読みやすいように整形できます。書式を整形しても、SQL 文の意味は変わりません。

データ操作文

データ操作文は最もよく使用する SQL 文です。データ操作文は、データベーステーブルのデータの取得、追加、変更、および削除に使用します。サポートされているデータ操作ステートメントは、SELECT、INSERT、UPDATE および DELETE です。

SELECT

SELECT 文は、データベースに問い合わせるときに使用します。SELECT 文の結果は、それぞれ固定数の列を持つ 0 行以上のデータ行です。結果として返される列の数は、SELECT とオプションの FROM キーワードとの間に列名または式リスト(下記の result)で指定します。

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

結果(result)には任意の式を使用できます。結果の式が * の場合は、その 1 つの式がすべてのテーブルのすべての列に置き換えられます。テーブル名の後に .* を続けて指定すると、その 1 つのテーブルのすべての列が返されます。

DISTINCT キーワードを指定すると、結果行の重複しないサブセットが返されます。NULL 値はすべて同じものとして扱われます。デフォルトでは、すべての結果行が返されます。これを明示的に指定するには、キーワード ALL を使用します。

クエリは FROM キーワードの後に指定した 1 つ以上のテーブルに対して実行されます。複数のテーブル名をカンマで区切ると、各テーブルがクロス結合されます。JOIN 構文を使用して、テーブルの結合方法を指定することもできます。外部結合の種類としては、LEFT OUTER JOIN のみがサポートされています。join-args 内の ON 句の式はブール値に解決される必要があります。FROM 句のテーブルとして、括弧で囲んだサブクエリを使用できます。FROM 句全体を省略することもできます。この場合は、result 式リストの値で構成された 1 つの行が返されます。

WHERE 句は、クエリで取得する行数を限定する場合に使用します。WHERE 句の式はブール値に解決される必要があります。WHERE 句によるフィルタリングはグループ化の前に行われるので、WHERE 句の式に集計関数を含めることはできません。

GROUP BY 句を使用すると、1 行以上の結果が集約されて 1 行の出力行になります。GROUP BY 句は、結果に集約関数が含まれている場合に特に役立ちます。GROUP BY 句の式は、SELECT 式リストに含まれていなくてもかまいません。

HAVING 句は、ステートメントによって返される行を制限するという点で WHERE と同様です。ただし、HAVING 句は、GROUP BY 句によって指定されたグループ化が行われた後に適用されます。したがって、HAVING 式は、集約関数を含む値を参照できます。HAVING 句の式は、SELECT リストに含まれていなくてもかまいません。WHERE 句の式と同様に、HAVING 句の式もブール値に解決される必要があります。

ORDER BY 句を使用すると、出力行がソートされます。ORDER BY 句の sort-expr-list 引数には、ソートキーとして使用する式のリストを指定します。これらの式は、単純な SELECT 文では結果に含まれていなくてもかまいませんが、複合 SELECT 文(いずれかの compound-op 演算子を使用した SELECT 文)では、各ソート式が結果の列の 1 つに正確に対応する必要があります。各ソートキーの後にはオプションで sort-order 句を付けることができます。この句には、COLLATE キーワードとテキストの並べ替えに使用する照合関数の名前およびソート順序を指定するキーワード ASC(昇順)または DESC(降順)を指定できます。sort-order を省略すると、デフォルト(昇順)が使用されます。COLLATE 句と照合関数の定義については、「COLLATE」を参照してください。

LIMIT 句は、結果で返される行数の上限を設定します。負の LIMIT は上限がないことを示します。LIMIT の後にオプションの OFFSET を付けると、結果セットの先頭から指定した行数がスキップされます。複合 SELECT ステートメントでは、LIMIT 句は必ず最後の SELECT ステートメントの後に付けますが、上限はクエリ全体に適用されます。LIMIT 句で OFFSET キーワードを使用すると、最初の整数が上限になり、2 番目の整数がオフセットになります。OFFSET キーワードの代わりにカンマを使用すると、最初の数がオフセットになり、2 番目の数が上限になります。この見かけの違いは意図的なもので、古い SQL データベースシステムとの互換性を最大限確保することを目的としています。

複合 SELECT ステートメントは、UNION、UNION ALL、INTERSECT、EXCEPT のいずれかの演算子で接続された複数の単純な SELECT ステートメントで構成されます。複合 SELECT ステートメントでは、構成するすべての SELECT ステートメントで同じ数の結果列を指定する必要があります。最後の SELECT ステートメントの後(また、単一の LIMIT 句が指定されている場合はその前)には、単一の ORDER BY 句のみを指定できます。UNION 演算子と UNION ALL 演算子はどちらも、前と後の SELECT ステートメントの結果を 1 つのテーブルに結合します。違いは、UNION ではすべての結果行が異なり、UNION ALL では重複する可能性があることです。INTERSECT 演算子は、前と後の SELECT ステートメントの結果の共通部分をとります。EXCEPT は、前の SELECT 文の結果から後の SELECT 文の結果を除去します。3 つ以上の SELECT 文を接続すると、最初から順に結果が結合されます。

指定可能な式の定義については、「式」を参照してください。

AIR 2.5 以降では、BLOB データを ActionScript ByteArray オブジェクトに変換するための読み取りの際に、SQL CAST 演算子がサポートされます。例えば、次のコードは 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 文で、次の 2 つの基本形式があります。
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 キーワードを使用する最初の形式では、既存のテーブルに新しい行が 1 行作成されます。column-list を指定しない場合は、テーブルの列と同じ数の値を指定する必要があります。column-list を指定する場合は、指定した列と同じ数の値を指定する必要があります。列リストに含まれないテーブルの列には、テーブルの作成時に定義されたデフォルト値が格納されます。デフォルト値が定義されていない場合は NULL が格納されます。

2 番目の形式の INSERT ステートメントは、SELECT ステートメントからデータを取得します。column-list を指定しない場合は、SELECT 文の結果に含まれる列の数とテーブルの列の数が一致する必要があります。column-list を指定する場合は、そこで指定した列の数と SELECT 文の結果に含まれる列の数が一致する必要があります。SELECT 文の結果行ごとに、テーブル内に新しいエントリが 1 つ作成されます。SELECT 文は単純な SELECT と複合 SELECT のどちらでもかまいません。指定可能な SELECT 文の定義については、「SELECT」を参照してください。

オプションの conflict-algorithm では、この特定のコマンドで使用する制約競合解決アルゴリズムを指定します。conflict-algorithm の説明および定義については、 特殊な文と句 を参照してください。

2 つの REPLACE INTO 形式は、標準の INSERT [OR conflict-algorithm] 形式を REPLACE 競合アルゴリズムと共に使用する(つまり、INSERT OR REPLACE... 形式を使用する)のと同じです。

2 つの 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

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]*

各列定義は、列名とその列のデータ型、およびオプションで 1 つ以上の列制約を並べたものです。列のデータ型は、その列に格納できるデータを制限します。異なるデータ型の列に値を格納しようとすると、可能な場合にはランタイムによって値が適切な型に変換され、可能でない場合にはエラーが発生します。詳しくは、「データ型のサポート」を参照してください。

NOT NULL 列制約は、その列に NULL 値を格納できないことを示します。

UNIQUE 制約を指定すると、指定された 1 つまたは複数の列にインデックスが作成されます。このインデックスには、一意キーが含まれている必要があります。つまり、任意の行と任意の別の行の間で、指定された列の値、または指定された複数列の値の組み合わせが必ず異なっていなくてはなりません。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 を指定すると、通常は対応する 1 つまたは複数の列に UNIQUE インデックスが作成されます。ただし、データ型が INTEGER(または int などのシノニムのいずれか)である単一の列に PRIMARY KEY 制約を指定すると、データベースではその列がテーブルの実際の主キーとして使用されます。これは、その列に一意の整数値しか格納できないことを意味します多くの SQLite 実装では、内部主キーとして使用するために指定できる列の型は、INTEGER だけです。ただし、Adobe AIR では、INTEGER のシノニム(int など)は同様にこの動作を指定できます。

テーブルに INTEGER PRIMARY KEY 列がない場合は、行が挿入されたときに整数キーが自動的に生成されます。行の主キーには、ROWID、OID、_ROWID_ のいずれかの特殊名を使用していつでもアクセスできます。これらの名前は、INTEGER PRIMARY KEY で明示的に宣言されているか、内部的に生成された値であるかに関係なく使用できます。ただし、テーブルに明示的な INTEGER PRIMARY KEY がない場合、結果データの列の名前は、特殊名ではなく、実際の列名になります。

INTEGER PRIMARY KEY 列には、キーワード AUTOINCREMENT も指定できます。AUTOINCREMENT キーワードを使用する場合、データベースは、列に明示的な値を指定しない INSERT ステートメントの実行時に、順番にインクリメントされる整数キーを自動生成して INTEGER PRIMARY KEY 列内に挿入します。

CREATE TABLE ステートメントには PRIMARY KEY 制約を 1 つだけ含めることができます。これは、ある列の定義の一部または単一のテーブルレベル PRIMARY KEY 制約にすることができます。主キー列は暗黙的に NOT NULL になります。

いくつかの制約の後にオプションの conflict-clause を続けると、その制約に対するデフォルトの制約競合解決アルゴリズムを指定できます。デフォルトは ABORT です。同じテーブル内の異なる制約に対して、異なるデフォルト競合解決アルゴリズムを指定することも可能です。INSERT ステートメントまたは UPDATE ステートメントで異なる競合解決アルゴリズムが指定された場合は、CREATE TABLE ステートメントで指定されたアルゴリズムの代わりにそのアルゴリズムが使用されます。詳しくは、 特殊な文と句 の「ON CONFLICT」を参照してください。

FOREIGN KEY 制約などの追加の制約を指定してもエラーにはなりませんが、実行時に無視されます。

TEMP または TEMPORARY キーワードが CREATE と TABLE の間にある場合、作成されるテーブルは、同じデータベース接続(SQLConnection インスタンス)内でのみ参照できます。データベース接続が終了すると、自動的に削除されます。一時テーブルでは、作成されたインデックスも一時的です。一時テーブルと一時インデックスは、メインのデータベースファイルとは別のファイルに格納されます。

オプションの database-name 接頭辞を指定すると、指定されたデータベース(データベース名を指定して attach() メソッドを呼び出すことによって SQLConnection インスタンスに接続されたデータベース)内にテーブルが作成されます。database-name 接頭辞と TEMP キーワードを両方とも指定すると、(database-name 接頭辞が temp である場合を除き)エラーになります。データベース名が指定されておらず、TEMP キーワードが存在しない場合は、メインデータベース(open() または openAsync() メソッドを使用して SQLConnection インスタンスに接続されたデータベース)にテーブルが作成されます。

列数またはテーブル内の制約数に恣意的な制限はありません。1 行のデータ量にも恣意的な制限はありません。

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」を参照してください。

1 つのテーブルにアタッチできるインデックス数に恣意的な制限はありません。インデックス内の列数にも制限はありません。

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 ステートメントに名前を割り当てます。この新しく割り当てた名前は、別の SELECT ステートメントの FROM 句でテーブル名の代わりに使用できます。通常、ビューは、複雑な(および最近使用された)データのセットを他の操作で使用できる構造に結合することで、クエリーを単純化するために使用されます。

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

CREATE と VIEW の間に TEMP または TEMPORARY キーワードを指定すると、作成されたビューはデータベースを開いた SQLConnection インスタンスからのみ見えるようになり、データベースが閉じると自動的に削除されます。

[database-name] を指定すると、指定されたデータベース(name 引数を指定した attach() メソッドを使用して SQLConnection インスタンスに接続されたデータベース)内にビューが作成されます。[database-name] と TEMP キーワードを両方とも指定すると、([database-name] が temp である場合を除き)エラーになります。データベース名が指定されておらず、TEMP キーワードが存在しない場合は、メインデータベース(open() または openAsync() メソッドを使用して SQLConnection インスタンスに接続されたデータベース)にビューが作成されます。

ビューは読み取り専用です。対応するタイプのトリガー(INSTEAD OF DELETE、INSTEAD OF INSERT、INSTEAD OF UPDATE)が少なくとも 1 つ定義されていない限り、ビューに対して DELETE ステートメント、INSERT ステートメント、または 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 文は、データベーススキーマにトリガーを追加するときに使用します。トリガーとは、指定されたデータベースイベント(database-event)が発生したときに自動的に実行されるデータベース操作(trigger-action)のことです。

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 が発生したときや、テーブルの指定された 1 つ以上の列に対して UPDATE が行われたときにその都度実行されるよう指定できます。トリガーは、TEMP または TEMPORARY キーワードが使用されていない限り永続的です。その場合、トリガーは、SQLConnection インスタンスのメインデータベース接続が終了すると削除されます。タイミング(BEFORE または AFTER)が指定されていない場合、デフォルトのタイミングは BEFORE になります。

FOR EACH ROW トリガーのみがサポートされているので、FOR EACH ROW キーワードはオプションです。FOR EACH ROW トリガーでは、WHEN 句の式が true に評価された場合、トリガーの起動元のステートメントによって挿入、更新または削除された各データベース行に対して、trigger-step ステートメントが実行されます。

WHEN 句を指定すると、WHEN 句が true に評価された行に対してのみ、trigger-steps として指定した SQL 文が実行されます。WHEN 句を指定しなければ、すべての行に対して SQL 文が実行されます。

トリガーの本文内で(trigger-action 句)、特別なテーブル名 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 ステートメントが、関連する行の挿入時、変更時、または削除時を基準としていつ実行されるかを決定します。trigger-step 内の UPDATE ステートメントまたは INSERT ステートメントの一部として ON CONFLICT 句を指定することもできますが、トリガーを発生させた文の一部として ON CONFLICT 句が指定されている場合は、代わりにその競合処理ポリシーが使用されます。

テーブルのトリガーに加えて、INSTEAD OF トリガーをビューに対して作成できます。1 つ以上の INSTEAD OF INSERT、INSTEAD OF DELETE、または INSTEAD OF UPDATE トリガーがビューに定義されている場合は、対応するタイプのステートメント(INSERT、DELETE、または UPDATE)をビューで実行してもエラーとは見なされません。その場合は、INSERT、DELETE、または UPDATE をビューで実行すると、対応するトリガーが発生します。トリガーは INSTEAD OF トリガーなので、ビューの基礎となるテーブルは、トリガーが起動する原因となったステートメントによって変更されません。ただし、トリガーは、基礎となるテーブルに対して変更操作を実行するために使用できます。

INTEGER PRIMARY KEY 列を持つテーブルにトリガーを作成するときは、注意すべき重要な問題があります。トリガーを起動したステートメントによって更新される行の INTEGER PRIMARY KEY 列が BEFORE トリガーによって変更された場合は、更新が行われません。この問題を回避するには、INTEGER PRIMARY KEY 列の代わりに 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 )

最初の 3 つの形式の 1 つがトリガーの実行中に呼び出されると、指定された ON CONFLICT 処理アクション(ABORT、FAIL、または ROLLBACK)が実行され、現在のステートメントの実行が終了します。ROLLBACK はステートメントの実行エラーと見なされるため、execute() メソッドが実行された SQLStatement インスタンスによって error(SQLErrorEvent.ERROR)イベントが送出されます。送出されたイベントオブジェクトの error プロパティ内にある SQLError オブジェクトの details プロパティは、RAISE() 関数で指定された error-message に設定されています。

RAISE(IGNORE) を呼び出すと、現在のトリガーの残りの部分、トリガーを発生させた文、および通常であれば引き続き実行されるはずの別のトリガーがすべて中止されます。データベースの変更はロールバックされません。トリガーを発生させた文自体がトリガーの一部である場合、そのトリガープログラムは次のステップの最初から再開されます。競合解決アルゴリズムについて詳しくは、「ON CONFLICT(競合アルゴリズム)」を参照してください。

DROP TRIGGER

DROP TRIGGER 文は、CREATE TRIGGER 文で作成されたトリガーを削除します。

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

トリガーはデータベースから削除されます。関連付けられたテーブルが削除されたときにも、トリガーは自動的に削除されます。

特殊な文と句

この節では、SQL の拡張機能としてランタイムによって提供されるいくつかの句と、多くの文で使用できる 2 つの言語要素(コメントと式)について説明します。

COLLATE

COLLATE 句は、値の比較またはソート時に使用する比較アルゴリズムを指定するために、SELECT ステートメント、CREATE TABLE ステートメントおよび CREATE INDEX ステートメントで使用します。

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

列のデフォルトの照合タイプは BINARY です。TEXT 格納クラスの値で BINARY 照合を使用すると、テキストがどのようにしてエンコードされているかにかかわらず、値を表すメモリ内のバイトを比較することによってバイナリ照合が行われます。

NOCASE 照合シーケンスは、TEXT 格納クラスの値に対してのみ適用されます。NOCASE 照合を使用すると、大文字と小文字を区別せずに比較されます。

NULL、BLOB、INTEGER、REAL 型の格納クラスでは、照合シーケンスは使用されません。

BINARY 以外の照合タイプを列で使用するには、CREATE TABLE ステートメントの列定義の一部として COLLATE 句を指定する必要があります。2 つの TEXT 値を比較するときは常に、照合順序を使用し、次の規則に従って比較結果が決定されます。

  • 二項比較演算子では、一方のオペランドが列である場合、その列のデフォルトの照合タイプによって、比較で使用される照合シーケンスが決まります。両方のオペランドが列である場合は、左のオペランドの照合タイプによって、使用される照合順序が決まります。どちらのオペランドも列でない場合は、BINARY 照合順序が使用されます。

  • BETWEEN...AND 演算子は、2 つの式に対して >= 演算子と <= 演算子を使用するのと同じです。例えば、x BETWEEN y AND z という式は x >= y AND x <= z と同じです。したがって、BETWEEN...AND 演算子も上記の規則に従って照合シーケンスが決定されます。

  • IN 演算子は、使用する照合シーケンスを決定するという点では、= 演算子と同じように動作します。例えば、x IN (y, z) という式で使用される照合シーケンスは、x が列の場合は x のデフォルトの照合タイプになります。それ以外の場合は、BINARY 照合が使用されます。

  • SELECT ステートメントでは、ORDER BY 句の中で、ソート操作で使用する照合シーケンスを明示的に指定できます。その場合は、明示的に指定した照合順序が常に使用されます。それ以外の場合は、ORDER BY 句によってソートされる式が列の場合には、その列のデフォルトの照合タイプを使用してソート順序が決定されます。式が列でない場合は、BINARY 照合順序が使用されます。

EXPLAIN

EXPLAIN コマンド修飾子は、SQL の非標準の拡張機能です。

sql-statement  ::=  EXPLAIN sql-statement

SQL ステートメントの前に EXPLAIN キーワードを付けると、コマンドを実際に実行する代わりに、EXPLAIN キーワードを指定せずにコマンドを実行した場合に使用される一連の仮想マシン命令が報告されます。この高度な EXPLAIN 機能を使用すると、SQL ステートメントのテキストを調整してパフォーマンスを最適化したり、適切に機能しない 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 に置き換えられた 2 番目の形式を使用します。例えば、INSERT ON CONFLICT IGNORE は INSERT OR IGNORE になります。キーワードは異なりますが、句の意味はどちらの形式でも同じです。

ON CONFLICT 句は、制約競合の解決に使用するアルゴリズムを指定します。指定できるアルゴリズムは、ROLLBACK、ABORT、FAIL、IGNORE、REPLACE の 5 種類です。デフォルトのアルゴリズムは ABORT です。これら 5 種類の競合アルゴリズムについて以下に説明します。

ROLLBACK
制約違反が発生すると、直ちに ROLLBACK が実行され、現在のトランザクションが終了します。コマンドは中止され、SQLStatement インスタンスによって error イベントが送出されます。トランザクションがアクティブでない場合(すべてのコマンドについて作成される暗黙的なトランザクションを除く)、このアルゴリズムは ABORT と同じです。

ABORT
制約違反が発生すると、そのコマンドによる変更がすべて取り消され、SQLStatement インスタンスによって error イベントが送出されます。ROLLBACK は実行されないので、トランザクション内の前のコマンドで行われた変更はそのまま残ります。ABORT はデフォルトの動作です。

FAIL
制約違反が発生すると、コマンドが中止され、SQLStatement によって error イベントが送出されます。ただし、制約違反が発生する前にそのステートメントによってデータベースに加えられた変更は取り消されずにそのまま残ります。例えば、UPDATE ステートメントの実行時に更新対象の 100 行目で制約違反が発生した場合、それまでの 99 行の変更はそのまま残りますが、100 行目以降の変更は行われません。

IGNORE
制約違反が発生したとき、制約違反を含む 1 行は挿入も変更もされません。行が無視されるという点を除き、コマンドは通常どおり続行されます。制約違反が発生した行の前後の行は通常どおり挿入または更新されます。エラーは返されません。

REPLACE
UNIQUE 制約違反が発生すると、制約違反の原因となる既存の行が削除された後で、現在の行が挿入または更新されます。したがって、挿入または更新は常に実行され、コマンドは通常どおり続行されます。エラーは返されません。NOT NULL 制約違反が発生した場合は、NULL 値がその列のデフォルト値で置き換えられます。列にデフォルト値がない場合は、ABORT アルゴリズムが使用されます。CHECK 制約違反が発生した場合は、IGNORE アルゴリズムが使用されます。この競合解決方法により、制約を満たすために行が削除されたときは、それらの行に対する削除トリガーは呼び出されません。

INSERT ステートメントまたは UPDATE ステートメントの OR 句で指定されたアルゴリズムは、CREATE TABLE ステートメントで指定されたアルゴリズムよりも優先されます。CREATE TABLE ステートメントおよび実行中の INSERT ステートメントまたは UPDATE ステートメントのどちらでもアルゴリズムが指定されていない場合は、ABORT アルゴリズムが使用されます。

REINDEX

REINDEX コマンドは、1 つ以上のインデックスを削除して再作成するときに使用します。このコマンドは、照合シーケンスの定義が変更されたときに便利です。

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

最初の形式では、アタッチされたデータベース内の指定された照合順序を使用するすべてのインデックスが再作成されます。2 番目の形式では、table-name を指定した場合、そのテーブルに関連付けられたすべてのインデックスが再構築されます。index-name を指定した場合は、指定したインデックスのみが削除され、再作成されます。

コメント

コメントは SQL コマンドではありませんが、SQL クエリの中に記述できます。コメントはランタイムでは空白として扱われます。コメントは、空白が許可される任意の場所(複数行にわたる式の内部など)に記述できます。

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

単一行のコメントには 2 つのダッシュを使用します。単一行コメントでは、その行の末尾までがコメントになります。

ブロックコメントは、任意の行数にわたることも、単一行に埋め込むこともできます。コメントの終わりを示す区切り記号がない場合は、入力の最後までがコメントになります。これはエラーとして扱われません。新しい 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)に解決されるものと、非ブール値に解決されるものの 2 種類に大別されます。

WHERE 句、HAVING 句、JOIN 句の中の ON 式、CHECK 式などで使用する式は、ブール値に解決される必要があります。この条件を満たす式の種類は次のとおりです。

  • ISNULL

  • NOTNULL

  • IN ()

  • EXISTS ()

  • LIKE

  • GLOB

  • いくつかの関数

  • いくつかの演算子(具体的には比較演算子)

リテラル値

リテラルの数値は、整数または浮動小数点数として記述します。科学的表記法もサポートされます。小数点には常に .(ピリオド)を使用します。

ストリングリテラルを示すときは、ストリングを一重引用符(')で囲みます。ストリングの中に一重引用符を含めるには、'' のように一重引用符を行で 2 つ重ねます。

ブールリテラルの値は true または false で示します。リテラルブール値は、列のデータ型が Boolean の場合に使用します。

BLOB リテラルは 16 進数データを含むストリングリテラルで、1 つの x または X を先頭に付けます(X'53514697465' など)。

リテラル値にはトークン NULL も使用できます。

列名

列名には、CREATE TABLE ステートメントで定義されたいずれかの名前、または特殊識別子の ROWID、OID、_ROWID_ のいずれかを使用できます。これらの特殊識別子はすべて、各テーブルのすべての行に関連付けられた一意のランダムな整数キー("行キー")を表します。特殊識別子は、CREATE TABLE 文で同じ名前を持つ実際の列が定義されていない場合のみ、行キーを参照します。行キーは読み取り専用列のように動作します。行キーは通常の列が使用できる場所であればどこでも使用できますが、行キーの値を UPDATE ステートメントまたは INSERT ステートメントで変更することはできません。SELECT * FROM table 文で返される結果セットには、行キーは含まれません。

SELECT 文

IN 演算子の右オペランド、スカラ量(単一の結果値)、または EXISTS 演算子のオペランドとして式を使用するときは、SELECT ステートメントを使用できます。スカラ量または IN 演算子のオペランドとして使用する場合、SELECT 文で返すことができるのは 1 つの列のみです。複合 SELECT ステートメント(UNION や EXCEPT などのキーワードで接続された SELECT ステートメント)も使用できます。EXISTS 演算子では、SELECT 文の結果セットに含まれる列は無視され、行が 1 行以上存在する場合は TRUE、結果セットが空の場合は FALSE が返されます。SELECT 式のどの項も外側のクエリの値を参照しない場合、式は他の処理が実行される前に 1 回だけ評価され、その結果が必要に応じて再利用されます。SELECT 式に外部クエリからの変数が含まれる場合(これを相関サブクエリと呼びます)、SELECT 文は必要になるたびに再評価されます。

SELECT ステートメントが IN 演算子の右オペランドである場合、IN 演算子は、左オペランドの結果が SELECT ステートメントの結果セットに含まれるいずれかの値と等しい場合に TRUE を返します。IN 演算子の前に NOT キーワードを付けると、テストの意味が逆になります。

IN 演算子の右オペランド以外の式で SELECT 文を使用する場合は、SELECT 文の結果の 1 行目が値として使用されます。SELECT ステートメントが複数行の結果を返す場合、2 行目以降の行はすべて無視されます。SELECT 文が行を 1 行も返さない場合、SELECT 文の値は NULL になります。

CAST 式

CAST 式は、指定された値のデータ型を別の指定されたデータ型に変更します。指定できるデータ型は、CREATE TABLE 文の列定義でデータ型として使用できる空でない型名です。詳しくは、「データ型のサポート」を参照してください。

その他の式要素

式で使用できるその他の SQL 要素は以下のとおりです。

  • ビルトイン関数:集計関数、スカラ関数、および日付/時刻書式設定関数

  • 演算子

  • パラメーター

ビルトイン関数

ビルトイン関数は次の 3 種類に大別されます。
  • 集計関数

  • スカラ関数

  • 日時関数

これらの関数に加えて、特殊関数の RAISE() もあります。これは、トリガーの実行時にエラー通知を提供するために使用します。この関数は、CREATE TRIGGER 文の本体でのみ使用できます。RAISE() 関数について詳しくは、「CREATE TRIGGER」の「RAISE()」を参照してください。

SQL のすべてのキーワードと同様に、関数名も大文字と小文字は区別されません。

集計関数

集計関数は、複数行の値に対して演算を実行します。これらは主に SELECT ステートメントの中で GROUP BY 句と一緒に使用します。

AVG(X)

グループ内の NULL でないすべての X の平均値を返します。数値に見えないストリング値および BLOB 値は 0 と見なされます。AVG() の結果は、入力がすべて整数であっても、常に浮動小数点値です。

COUNT(X) COUNT(*)

最初の形式は、グループ内で X が NULL でない回数を返します。* 引数の付いた 2 番目の形式は、グループ内の行の総数を返します。

MAX(X)

グループ内のすべての値の最大値を返します。通常のソート順序を使用して最大値が決定されます。

MIN(X)

グループ内のすべての値のうち、NULL でない最小の値を返します。通常のソート順序を使用して最小値が決定されます。グループ内の値がすべて NULL の場合は、NULL が返されます。

SUM(X)

TOTAL(X)

グループ内の NULL でないすべての値の合計を返します。値がすべて NULL の場合には、SUM() は NULL を返し、TOTAL() は 0.0 を返します。TOTAL() の結果は常に浮動小数点値です。SUM() の結果は、NULL 以外の入力がすべて整数の場合は整数値になります。SUM() への入力に整数でも NULL でもない値が含まれている場合、SUM() は浮動小数点値を返しますが、この値は正確な合計の近似値である場合があります。

上記の集計関数のうち引数を 1 つ受け取るものはいずれも、その引数の前にキーワード DISTINCT を付けることができます。その場合は、重複した要素が除外されてから集計関数に渡されます。例えば、関数が COUNT(DISTINCT x) を呼び出すと、列 x の NULL でない値の総数ではなく、列 X の互いに異なる値の数が返されます。

スカラ関数

スカラ関数は一度に 1 つの行の値に作用します。

ABS(X)

引数 X の絶対値を返します。

COALESCE(X, Y, ...)

NULL でない最初の引数のコピーを返します。引数がすべて NULL の場合は、NULL が返されます。引数は 2 つ以上指定する必要があります。

GLOB(X, Y)

この関数は、X GLOB Y 構文を実装するために使用されます。

IFNULL(X, Y)

NULL でない最初の引数のコピーを返します。引数がどちらも NULL の場合は、NULL が返されます。この関数の動作は COALESCE() と同じです。

HEX(X)

引数は BLOB 格納型の値と解釈されます。その値の内容の 16 進数表現が返されます。

LAST_INSERT_ROWID()

現在の SQLConnection を通じてデータベースに最後に挿入された行の行識別子(生成された主キー)を返します。この値は、 SQLConnection.lastInsertRowID プロパティによって返される値と同じです。

LENGTH(X)

X のストリングの長さ(文字数)を返します。

LIKE(X, Y [, Z])

この関数は、SQL の X LIKE Y [ESCAPE Z] 構文を実装するために使用されます。オプションの ESCAPE 句を指定すると、3 つの引数を使用して関数が呼び出されます。それ以外の場合は、2 つの引数のみを使用して関数が呼び出されます。

LOWER(X)

ストリング X の、すべての文字が小文字に変換されたコピーを返します。

LTRIM(X) LTRIM(X, Y)

X の左側からスペースを除去したストリングを返します。Y 引数を指定すると、Y に含まれるいずれかの文字が X の左側から除去されます。

MAX(X, Y, ...)

引数の中の最大値を返します。引数には、数字だけでなくストリングも指定できます。最大値は、定義されたソート順序に従って決定されます。MAX() は、引数が 2 個以上ある場合は単純な関数ですが、引数が 1 つだけの場合は集計関数になります。

MIN(X, Y, ...)

引数の中の最小値を返します。引数には、数字だけでなくストリングも指定できます。最小値は、定義されたソート順序に従って決定されます。MIN() は、引数が 2 個以上ある場合は単純な関数ですが、引数が 1 つだけの場合は集計関数になります。

NULLIF(X, Y)

引数が異なる場合は最初の引数を返し、それ以外の場合は NULL を返します。

QUOTE(X)

このルーチンは、引数の値を別の SQL 文に挿入するのに適した形に変更したストリングを返します。ストリングは一重引用符で囲まれ、必要に応じて内部引用符がエスケープされます。BLOB 格納クラスは 16 進数のリテラルとしてエンコードされます。この関数は、元に戻す/繰り返し機能を実装するためのトリガーを記述する場合に役立ちます。

RANDOM(*)

-9223372036854775808 から 9223372036854775807 までの疑似乱数(整数)を返します。これは暗号的に強い乱数ではありません。

RANDOMBLOB(N)

疑似ランダムバイトを含む N バイトの BLOB を返します。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 を含む BLOB を返します。

日付/時刻書式設定関数

日付/時刻書式設定関数は、書式設定された日付および時刻データの作成に使用するスカラ関数のグループです。これらはストリング値および数値を引数とし、ストリング値および数値を返します。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() 関数は、グリニッジでの紀元前 4714 年 11 月 24 日の正午から指定された日付までの日数を示す数字を返します。最初のパラメーター(T)では、「時刻書式」で示された書式の時刻ストリングを指定します。時刻ストリングの後に任意の数の修飾子を指定できます。修飾子については、「修飾子」を参照してください。

STRFTIME(F, T, ...)

STRFTIME() ルーチンは、最初の引数 F で指定された書式ストリングに従って書式設定された日付を返します。書式ストリングでは、次の置換がサポートされます。

%d - 月の日にち

%f - 秒の端数(SS.SSS)

%H - 時間(00 ~ 24)

%j - 1 月 1 日からの日数(001 ~ 366)

%J - ユリウス日

%m -月(01 ~ 12)

%M - 分(00 ~ 59)

%s - 1970 年 1 月 1 日からの秒数

%S - 秒(00 ~ 59)

%w - 曜日(0 ~ 6、日曜日 = 0)

%W - 1 月 1 日からの週数(00 ~ 53)

%Y - 年(0000 ~ 9999)

%% - %

2 番目のパラメーター(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

現在の日時(世界標準時)

DDDD.DDDD

ユリウス日(浮動小数点数)

上記の書式に含まれる T は、日付と時間を区切るリテラル文字の "T" です。時刻のみを含む書式では、日付は 2001-01-01 と見なされます。

修飾子

時刻ストリングの後に修飾子を 0 個以上続けることで、日付または日付の解釈を変更できます。使用できる修飾子は次のとおりです。

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

日付を世界標準時に変換します。

演算子

SQL は様々な演算子をサポートしており、ほとんどのプログラミング言語に存在する一般的な演算子のほか、SQL に固有の演算子もあります。

一般的な演算子

次のリストは、SQL ブロックで使用できる二項演算子を優先順位の高い順に並べたものです。

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

単項前置演算子は次のものがサポートされています。

 !    ~    NOT

COLLATE 演算子は単項後置演算子と見なすことができます。COLLATE 演算子は優先順位が最も高く、常に他のどの前置単項演算子や二項演算子よりも強く結び付きます。

等号演算子と不等号演算子はそれぞれ 2 種類あります。等号演算子は = または ==、不等号演算子は != または <> です。

|| はストリングを連結する演算子で、そのオペランドである 2 つのストリングを 1 つにつなげます。

% 演算子は、右オペランドを法とする左オペランドの剰余を返します。

二項演算子の結果は数値です。ただし、|| 連結演算子だけは例外で、結果がストリングになります。

SQL 演算子

LIKE

LIKE 演算子は、パターンマッチングによる比較を行います。

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

LIKE 演算子の右オペランドにはパターンを指定し、左オペランドにはそのパターンと照合するストリングを指定します。パターン内のパーセント記号(%)はワイルドカード文字で、ストリング内の 0 個以上連続した文字に一致します。パターン内のアンダースコア(_)はストリング内の任意の 1 文字に一致します。その他の文字はすべて、その文字そのものとして、大文字と小文字を区別せずに照合されます。(メモ:データベースエンジンでは、7 ビットラテン文字の大文字と小文字のみが理解されます。したがって、8 ビット iso8859 文字または UTF-8 文字では、LIKE 演算子は大文字と小文字を区別します。例えば、'a' LIKE 'A' という式は TRUE になりますが、'æ' LIKE 'Æ' は FALSE になります)。ラテン文字の大文字と小文字を区別するかどうかを切り替えるには、SQLConnection.caseSensitiveLike プロパティを使用します。

オプションの ESCAPE 句を指定する場合、ESCAPE キーワードの後に続く式は、1 文字で構成されるストリングに評価される必要があります。この文字は、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 文を記述します。IN 演算子の右オペランドとして SELECT ステートメントを使用する際の説明と制限については、「式」の「SELECT ステートメント」を参照してください。

BETWEEN...AND

BETWEEN...AND 演算子は、2 つの式に対して >= 演算子と <= 演算子を使用するのと同じです。例えば、x BETWEEN y AND z という式は x >= y AND x <= z と同じです。

NOT

NOT は否定の演算子です。GLOB、LIKE および IN 演算子の前に NOT キーワードを付けると、テストの意味が逆になります(つまり、値が指定のパターンと一致しないことが確認されます)。

パラメーター

パラメーターは、式の中でリテラル値を表すためのプレースホルダーを指定します。このプレースホルダーには、SQLStatement.parameters 結合配列に値を設定することで、実行時に値が格納されます。パラメーターは次の 3 つの形式をとります。

疑問符はインデックス付きのパラメーターを示します。この形式のパラメーターには、SQL ステートメントに登場した順に数値インデックス値が割り当てられます。インデックス値は 0 から始まります。

:AAAA

コロンの後に識別子名が付いた形式は、AAAA という名前の名前付きパラメーター用の場所を保持します。名前付きパラメーターにも、SQL 文に登場した順に数値インデックスが付けられます。混乱を避けるために、名前付きパラメーターとインデックス付きパラメーターを混在させないことをお勧めします。

@AAAA

"@" はコロンと同じです。

サポートされていない SQL 機能

Adobe AIR でサポートされていない標準 SQL 要素は次のとおりです。
FOREIGN KEY 制約
FOREIGN KEY 制約は、解析はされますが適用されません。

トリガー
FOR EACH STATEMENT トリガーはサポートされていません(トリガーはすべてFOR EACH ROW にする必要があります)。INSTEAD OF トリガーはテーブルではサポートされていません(INSTEAD OF トリガーはビューに対してのみ使用できます)。再帰トリガー(自分自身をトリガーするトリガー)はサポートされていません。

ALTER TABLE
ALTER TABLE コマンドでは、RENAME TABLE バリアントと ADD COLUMN バリアントのみがサポートされています。その他の種類の ALTER TABLE 操作(DROP COLUMN、ALTER COLUMN、ADD CONSTRAINT など)は無視されます。

ネストされたトランザクション
単一のアクティブトランザクションのみが許可されます。

RIGHT および FULL OUTER JOIN
RIGHT OUTER JOIN または FULL OUTER JOIN はサポートされていません。

更新可能なビュー
ビューは読み取り専用です。ビューに対して DELETE ステートメント、INSERT ステートメント、または UPDATE ステートメントは実行できません。ビューを DELETE、INSERT、または UPDATE しようとしたときに起動される INSTEAD OF トリガーはサポートされており、このトリガーの本体で基になるテーブルを更新できます。

GRANT と REVOKE
データベースは通常のディスクファイルです。つまり、アクセス許可として適用できるのは、基になるオペレーティングシステムの通常のファイルアクセス許可のみです。クライアント/サーバー型の RDBMS でよく見られる GRANT コマンドと REVOKE コマンドは実装されていません。

次に示す 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 とで違いがあります。

ステートメントパラメーターのインデックス
多くの実装では、ステートメントパラメーターのインデックスは 1 から始まります。それに対して、Adobe AIR の場合、ステートメントパラメーターのインデックスは 0 から始まります(最初のパラメーターのインデックス値は 0、2 番目のパラメーターのインデックス値は 1 というようになります)。

INTEGER PRIMARY KEY 列定義
多くの実装では、INTEGER PRIMARY KEY として正確に定義されている列だけが、テーブルの実際の主キー列として使用されます。このような実装では、別のデータ型(通常は int などの、INTEGER のシノニム)を使用した場合、その列は内部主キーとして使用されません。ただし、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
ActionScript(E4X)XML クラスに対応します。

XMLList
ActionScript(E4X)XMLList クラスに対応します。

次のリテラル値は、SQLite ではデフォルトでサポートされていませんが、Adobe AIR ではサポートされています。

true
BOOLEAN 型の列に使用するリテラルブール値 true を表します。

false
BOOLEAN 型の列に使用するリテラルブール値 false を表します。