SQLite-概要

この章は、SQLiteとは何か、SQLとの違い、必要な理由、アプリケーションデータベースの処理方法を理解するのに役立ちます。

SQLiteは、自己完結型のサーバーなしのゼロ構成のトランザクションSQLデータベースエンジンを実装するソフトウェアライブラリです。 SQLiteは、最も急速に成長しているデータベースエンジンの1つですが、人気の点では成長しているので、そのサイズとは関係ありません。 SQLiteのソースコードはパブリックドメインです。

SQLiteとは何ですか？

SQLiteは、自己完結型のサーバーなしのゼロ構成のトランザクションSQLデータベースエンジンを実装するインプロセスライブラリです。これは、ゼロ構成のデータベースです。つまり、他のデータベースと同様に、システムで構成する必要はありません。

SQLiteエンジンは、他のデータベースのようなスタンドアロンプロセスではなく、要件に応じて静的または動的にアプリケーションとリンクできます。 SQLiteはストレージファイルに直接アクセスします。

なぜSQLiteなのか？

SQLiteでは、個別のサーバープロセスまたはシステムを操作する必要はありません（サーバーレス）。
SQLiteにはゼロ構成が付属しているため、セットアップや管理は不要です。
完全なSQLiteデータベースは、単一のクロスプラットフォームディスクファイルに保存されます。
SQLiteは非常に小さく軽量で、完全に構成された400KiB未満、またはオプション機能を省略した250KiB未満です。
SQLiteは自己完結型であるため、外部依存関係はありません。
SQLiteトランザクションはACIDに完全に準拠しており、複数のプロセスまたはスレッドから安全にアクセスできます。
SQLiteは、SQL92（SQL2）標準にあるクエリ言語機能のほとんどをサポートしています。
SQLiteはANSI-Cで記述されており、シンプルで使いやすいAPIを提供します。
SQLiteは、UNIX（Linux、Mac OS-X、Android、iOS）およびWindows（Win32、WinCE、WinRT）で使用できます。

SQLite Aの簡単な歴史

2000-D. Richard Hippは、プログラムを操作するために管理を必要としない目的でSQLiteを設計しました。
2000-8月、SQLite 1.0がGNUデータベースマネージャーと共にリリースされました。 *2011-Hippは、SQLite DBにUNQlインターフェイスを追加し、UNQLite（ドキュメント指向データベース）を開発することを発表しました。

SQLiteの制限

SQLiteには、次の表にリストされているSQL92のサポートされていない機能がほとんどありません。

Sr.No.	Feature & Description
1	RIGHT OUTER JOIN* LEFT OUTER JOINのみが実装されます。
2	FULL OUTER JOIN LEFT OUTER JOINのみが実装されます。
3	ALTER TABLE ALTER TABLEコマンドのRENAME TABLEおよびADD COLUMNバリアントがサポートされています。 DROP COLUMN、ALTER COLUMN、ADD CONSTRAINTはサポートされていません。
4	Trigger support FOR EACH ROWトリガーはサポートされていますが、FOR EACH STATEMENTトリガーはサポートされていません。
5	VIEWs SQLiteのVIEWは読み取り専用です。ビューでDELETE、INSERT、またはUPDATEステートメントを実行することはできません。
6	GRANT and REVOKE 適用できるアクセス許可は、基になるオペレーティングシステムの通常のファイルアクセス許可のみです。

SQLiteコマンド

リレーショナルデータベースとやり取りする標準のSQLiteコマンドは、SQLに似ています。それらは、CREATE、SELECT、INSERT、UPDATE、DELETE、およびDROPです。これらのコマンドは、動作特性に基づいてグループに分類できます-

DDL-データ定義言語

Sr.No.

Command & Description

1

CREATE

データベースに新しいテーブル、テーブルのビュー、またはその他のオブジェクトを作成します。

2

ALTER

テーブルなどの既存のデータベースオブジェクトを変更します。

3

DROP

テーブル全体、テーブルのビュー、またはデータベース内の他のオブジェクトを削除します。

DML-データ操作言語

Sr.No.

Command & Description

1

INSERT

レコードを作成します

2

UPDATE

レコードを変更します

3

DELETE

レコードを削除します

DQL-データクエリ言語

Sr.No.	Command & Description
1	SELECT 1つ以上のテーブルから特定のレコードを取得します

SQLite-インストール

SQLiteは、優れた機能ゼロ構成で有名です。つまり、複雑なセットアップや管理は必要ありません。この章では、Windows、Linux、およびMac OS XでSQLiteをセットアップするプロセスについて説明します。

WindowsにSQLiteをインストールする

*ステップ1 *-https://www.sqlite.org/downloadl[SQLiteダウンロードページ]に移動し、Windowsセクションからプリコンパイル済みバイナリをダウンロードします。
ステップ2 *-sqlite-shell-win32-。zipおよびsqlite-dll-win32-*。zip zipファイルをダウンロードします。
*ステップ3 *-フォルダーC：\> sqliteを作成し、このフォルダー内の2つの圧縮ファイルの上に解凍します。これにより、sqlite3.def、sqlite3.dll、およびsqlite3.exeファイルが作成されます。
*ステップ4 *-C：\> sqliteをPATH環境変数に追加し、最後にコマンドプロンプトに移動してsqlite3コマンドを発行すると、次の結果が表示されます。

C:\>sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

LinuxにSQLiteをインストールする

現在、Linux OSのほぼすべての種類がSQLiteに同梱されています。そのため、次のコマンドを発行して、マシンにSQLiteが既にインストールされているかどうかを確認します。

$sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

上記の結果が表示されない場合、LinuxマシンにSQLiteがインストールされていないことを意味します。以下は、SQLiteをインストールするための次の手順です-

ステップ1 *-https://www.sqlite.org/downloadl[SQLiteダウンロードページ]に移動し、ソースコードセクションからsqlite-autoconf-。tar.gzをダウンロードします。
*ステップ2 *-次のコマンドを実行します-

$tar xvfz sqlite-autoconf-3071502.tar.gz
$cd sqlite-autoconf-3071502
$./configure --prefix=/usr/local
$make
$make install

上記のコマンドは、LinuxマシンへのSQLiteインストールで終了します。上記で説明したように確認できます。

Mac OS XにSQLiteをインストールする

Mac OS Xの最新バージョンにはSQLiteがプリインストールされていますが、インストールが利用できない場合は、次の手順に従ってください-

ステップ1 *-https://www.sqlite.org/downloadl[SQLiteダウンロードページ]に移動し、ソースコードセクションからsqlite-autoconf-。tar.gzをダウンロードします。
*ステップ2 *-次のコマンドを実行します-

$tar xvfz sqlite-autoconf-3071502.tar.gz
$cd sqlite-autoconf-3071502
$./configure --prefix=/usr/local
$make
$make install

上記の手順は、Mac OS XマシンへのSQLiteのインストールで終了します。次のコマンドを発行することで確認できます-

$sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

最後に、演習用のSQLiteコマンドを発行できるSQLiteコマンドプロンプトがあります。

SQLite-コマンド

この章では、SQLiteプログラマーが使用するシンプルで便利なコマンドについて説明します。これらのコマンドはSQLiteドットコマンドと呼ばれ、これらのコマンドの例外はセミコロン（;）で終了しないことです。

コマンドプロンプトで簡単な sqlite3 コマンドを入力するところから始めましょう。これにより、さまざまなSQLiteコマンドを発行するSQLiteコマンドプロンプトが表示されます。

$sqlite3
SQLite version 3.3.6
Enter ".help" for instructions
sqlite>

使用可能なドットコマンドのリストについては、いつでも「.help」と入力できます。たとえば-

sqlite>.help

上記のコマンドは、次の表にリストされているさまざまな重要なSQLiteドットコマンドのリストを表示します。

Sr.No.	Command & Description
1	.backup ?DB? FILE データベース（デフォルトの「メイン」）をFILEにバックアップします
2	*.bail ON
OFF* エラーが発生した後に停止します。デフォルトOFF	3
.databases 接続されているデータベースの名前とファイルを一覧表示する	4
.dump ?TABLE? データベースをSQLテキスト形式でダンプします。 TABLEが指定されている場合、LIKEパターンTABLEに一致するテーブルのみをダンプします	5
*.echo ON	OFF* コマンドエコーをオンまたはオフにする
6	.exit SQLiteプロンプトを終了
7	*.explain ON
OFF* EXPLAINに適した出力モードをオンまたはオフにします。引数なしで、EXPLAINをオンにします	8
*.header(s) ON	OFF* ヘッダーの表示をオンまたはオフにする
9	.help このメッセージを表示
10	.import FILE TABLE FILEからTABLEにデータをインポートする
11	.indices ?TABLE? すべてのインデックスの名前を表示します。 TABLEが指定されている場合、LIKEパターンTABLEに一致するテーブルのインデックスのみを表示します
12	.load FILE ?ENTRY? 拡張ライブラリをロードする
13	*.log FILE
off* ロギングをオンまたはオフにします。 FILEはstderr/stdoutにすることができます	14
.mode MODE MODEが次のいずれかである出力モードを設定します- csv -コンマ区切り値 column -左揃えの列。 html -HTML <table>コード挿入-TABLEのSQL挿入ステートメント line -行ごとに1つの値 list -.separator文字列で区切られた値 tabs -タブ区切り値 tcl -TCLリスト要素	15
.nullvalue STRING NULL値の代わりにSTRINGを出力します	16
.output FILENAME FILENAMEに出力を送信	17
.output stdout 画面に出力を送信する	18
.print STRING… 文字列STRINGを印刷	19
.prompt MAIN CONTINUE 標準プロンプトを置き換えます	20
.quit SQLiteプロンプトを終了	21
.read FILENAME FILENAMEでSQLを実行する	22
.schema ?TABLE? CREATEステートメントを表示します。 TABLEが指定されている場合、LIKEパターンTABLEに一致するテーブルのみを表示します	23
.separator STRING 出力モードと.importで使用される区切り文字を変更する	24
.show さまざまな設定の現在の値を表示する	25
*.stats ON	OFF* 統計をオンまたはオフにする
26	.tables ?PATTERN? LIKEパターンに一致するテーブルの名前をリストする
27	.timeout MS ロックされたテーブルをMSミリ秒開いてみてください
28	.width NUM NUM 「列」モードの列幅を設定する
29	*.timer ON

*.show* コマンドを試して、SQLiteコマンドプロンプトのデフォルト設定を見てみましょう。

sqlite>.show
     echo: off
  explain: off
  headers: off
     mode: column
nullvalue: ""
   output: stdout
separator: "|"
    width:
sqlite>

sqlite>プロンプトとドットコマンドの間にスペースがないことを確認してください。スペースがないと機能しません。

出力のフォーマット

次の一連のドットコマンドを使用して、出力をフォーマットできます。

sqlite>.header on
sqlite>.mode column
sqlite>.timer on
sqlite>

上記の設定により、次の形式で出力が生成されます。

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0
CPU Time: user 0.000000 sys 0.000000

sqlite_masterテーブル

マスターテーブルはデータベーステーブルに関するキー情報を保持し、 sqlite_master と呼ばれます。次のようにそのスキーマを見ることができます-

sqlite>.schema sqlite_master

これにより、次の結果が生成されます。

CREATE TABLE sqlite_master (
   type text,
   name text,
   tbl_name text,
   rootpage integer,
   sql text
);

SQLite-構文

SQLiteの後には、構文と呼ばれるルールとガイドラインの一意のセットが続きます。この章では、すべての基本的なSQLite構文をリストします。

大文字と小文字の区別

注意すべき重要な点は、SQLiteは*大文字と小文字を区別しない*ですが、 GLOB と glob のような大文字と小文字を区別するコマンドはSQLiteステートメントで異なる意味を持っていることです。

SQLiteコメントは追加のメモであり、SQLiteコードに追加して読みやすくすることができ、どこにでも表示できます。内部式や他のSQLステートメントの途中を含む空白が発生する可能性がありますが、ネストすることはできません。

SQLコメントは、2つの連続する「-」文字（ASCII 0x2d）で始まり、次の改行文字（ASCII 0x0a）まで、または入力の終わりまでのいずれか早い方まで拡張されます。

「/*」で始まり、次の「 */」文字のペアまで、または入力の終わりまで、どちらか早い方まで拡張するCスタイルのコメントを使用することもできます。 Cスタイルのコメントは複数行にわたることができます。

sqlite> .help -- This is a single line comment

SQLiteステートメント

すべてのSQLiteステートメントは、SELECT、INSERT、UPDATE、DELETE、ALTER、DROPなどのキーワードで始まり、すべてのステートメントはセミコロン（;）で終わります。

SQLite ANALYZEステートメント

ANALYZE;
or
ANALYZE database_name;
or
ANALYZE database_name.table_name;

SQLite AND/OR句

SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION-1 {AND|OR} CONDITION-2;

SQLite ALTER TABLEステートメント

ALTER TABLE table_name ADD COLUMN column_def...;

SQLite ALTER TABLEステートメント（名前の変更）

ALTER TABLE table_name RENAME TO new_table_name;

SQLite ATTACH DATABASEステートメント

ATTACH DATABASE 'DatabaseName' As 'Alias-Name';

SQLite BEGIN TRANSACTIONステートメント

BEGIN;
or
BEGIN EXCLUSIVE TRANSACTION;

SQLite BETWEEN句

SELECT column1, column2....columnN
FROM table_name
WHERE column_name BETWEEN val-1 AND val-2;

SQLite COMMITステートメント

COMMIT;

SQLite CREATE INDEXステートメント

CREATE INDEX index_name
ON table_name ( column_name COLLATE NOCASE );

SQLite CREATE UNIQUE INDEXステートメント

CREATE UNIQUE INDEX index_name
ON table_name ( column1, column2,...columnN);

SQLite CREATE TABLEステートメント

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

SQLite CREATE TRIGGERステートメント

CREATE TRIGGER database_name.trigger_name
BEFORE INSERT ON table_name FOR EACH ROW
BEGIN
   stmt1;
   stmt2;
   ....
END;

SQLite CREATE VIEWステートメント

CREATE VIEW database_name.view_name AS
SELECT statement....;

SQLite CREATE VIRTUAL TABLEステートメント

CREATE VIRTUAL TABLE database_name.table_name USING weblog( access.log );
or
CREATE VIRTUAL TABLE database_name.table_name USING fts3( );

SQLite COMMIT TRANSACTIONステートメント

COMMIT;

SQLite COUNT句

SELECT COUNT(column_name)
FROM table_name
WHERE CONDITION;

SQLite DELETEステートメント

DELETE FROM table_name
WHERE {CONDITION};

SQLite DETACH DATABASEステートメント

DETACH DATABASE 'Alias-Name';

SQLite DISTINCT句

SELECT DISTINCT column1, column2....columnN
FROM table_name;

SQLite DROP INDEXステートメント

DROP INDEX database_name.index_name;

SQLite DROP TABLEステートメント

DROP TABLE database_name.table_name;

SQLite DROP VIEWステートメント

DROP INDEX database_name.view_name;

SQLite DROP TRIGGERステートメント

DROP INDEX database_name.trigger_name;

SQLite EXISTS句

SELECT column1, column2....columnN
FROM table_name
WHERE column_name EXISTS (SELECT* FROM   table_name );

SQLite EXPLAINステートメント

EXPLAIN INSERT statement...;
or
EXPLAIN QUERY PLAN SELECT statement...;

SQLite GLOB句

SELECT column1, column2....columnN
FROM table_name
WHERE column_name GLOB { PATTERN };

SQLite GROUP BY句

SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name;

SQLite HAVING句

SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name
HAVING (arithematic function condition);

SQLite INSERT INTOステートメント

INSERT INTO table_name( column1, column2....columnN)
VALUES ( value1, value2....valueN);

SQLite IN句

SELECT column1, column2....columnN
FROM table_name
WHERE column_name IN (val-1, val-2,...val-N);

SQLite Like句

SELECT column1, column2....columnN
FROM table_name
WHERE column_name LIKE { PATTERN };

SQLite NOT IN句

SELECT column1, column2....columnN
FROM table_name
WHERE column_name NOT IN (val-1, val-2,...val-N);

SQLite ORDER BY句

SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION
ORDER BY column_name {ASC|DESC};

SQLite PRAGMAステートメント

PRAGMA pragma_name;

For example:

PRAGMA page_size;
PRAGMA cache_size = 1024;
PRAGMA table_info(table_name);

SQLite RELEASE SAVEPOINTステートメント

RELEASE savepoint_name;

SQLite REINDEXステートメント

REINDEX collation_name;
REINDEX database_name.index_name;
REINDEX database_name.table_name;

SQLite ROLLBACKステートメント

ROLLBACK;
or
ROLLBACK TO SAVEPOINT savepoint_name;

SQLite SAVEPOINTステートメント

SAVEPOINT savepoint_name;

SQLite SELECTステートメント

SELECT column1, column2....columnN
FROM table_name;

SQLite UPDATEステートメント

UPDATE table_name
SET column1 = value1, column2 = value2....columnN=valueN
[ WHERE  CONDITION ];

SQLite VACUUMステートメント

VACUUM;

SQLite WHERE句

SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION;

SQLite-データ型

SQLiteデータ型は、オブジェクトのデータ型を指定する属性です。 SQLiteでは、各列、変数、および式に関連するデータ型があります。

テーブルを作成するときにこれらのデータ型を使用します。 SQLiteは、より一般的な動的型システムを使用します。 SQLiteでは、値のデータ型はコンテナではなく、値自体に関連付けられています。

SQLiteストレージクラス

SQLiteデータベースに格納されている各値は、次のストレージクラスのいずれかを持っています-

Sr.No.	Storage Class & Description
1	NULL 値はNULL値です。
2	INTEGER 値は符号付き整数で、値の大きさに応じて1、2、3、4、6、または8バイトで保存されます。
3	REAL 値は浮動小数点値で、8バイトのIEEE浮動小数点数として保存されます。
4	TEXT 値は、データベースエンコーディング（UTF-8、UTF-16BEまたはUTF-16LE）を使用して保存されたテキスト文字列です
5	BLOB 値はデータのblobであり、入力されたとおりに保存されます。

SQLiteストレージクラスは、データ型よりも少し一般的です。たとえば、INTEGERストレージクラスには、長さの異なる6つの異なる整数データ型が含まれます。

SQLiteアフィニティタイプ

SQLiteは、列の type affinity の概念をサポートしています。どの列にも任意のタイプのデータを格納できますが、列の優先ストレージクラスは affinity と呼ばれます。 SQLite3データベース内の各テーブル列には、次のいずれかのタイプのアフィニティが割り当てられています-

Sr.No.	Affinity & Description
1	TEXT この列には、ストレージクラスNULL、TEXT、またはBLOBを使用してすべてのデータが格納されます。
2	NUMERIC この列には、5つのストレージクラスすべてを使用した値が含まれる場合があります。
3	INTEGER CAST式の例外を除き、NUMERICアフィニティを持つ列と同じように動作します。
4	REAL 整数値を浮動小数点表現に強制することを除いて、NUMERICアフィニティを持つ列のように動作します。
5	NONE アフィニティがNONEの列は、あるストレージクラスを別のストレージクラスよりも優先せず、あるストレージクラスから別のストレージクラスにデータを強制する試みは行われません。

SQLiteのアフィニティとタイプ名

次の表は、対応するアフィニティが適用されたSQLite3テーブルの作成中に使用できるさまざまなデータ型名を示しています。

Data Type	Affinity
INT 整数ティニイント SMALLINT 中 BIGINT 符号なしの大きなINT INT2 INT8	INTEGER
キャラクター（20） VARCHAR（255）さまざまな文字（255） NCHAR（55）ネイティブキャラクター（70） NVARCHAR（100） TEXT CLOB	TEXT
BLOB データ型が指定されていません	NONE
REAL ダブル倍精度浮く	REAL
数値 10進数（10,5）ブーリアン DATE *日付時刻	NUMERIC

ブールデータ型

SQLiteには、別個のブールストレージクラスがありません。代わりに、ブール値は整数0（偽）および1（真）として保管されます。

日付と時刻のデータ型

SQLiteには、日付や時刻を保存するための個別のストレージクラスはありませんが、SQLiteは日付と時刻をTEXT、REAL、またはINTEGER値として保存できます。

Sr.No.

Storage Class & Date Formate

1

TEXT*

「YYYY-MM-DD HH：MM：SS.SSS」などの形式の日付

2

REAL

紀元前4714年11月24日のグリニッジ正午からの日数

3

INTEGER

1970-01-01 00:00:00 UTCからの秒数

これらの形式のいずれかで日付と時刻を保存し、組み込みの日付と時刻の関数を使用して形式間で自由に変換することを選択できます。

SQLite-データベースの作成

SQLiteでは、 sqlite3 コマンドを使用して新しいSQLiteデータベースを作成します。データベースを作成するのに特別な権限は必要ありません。

構文

以下は、データベースを作成するsqlite3コマンドの基本的な構文です。

$sqlite3 DatabaseName.db

常に、データベース名はRDBMS内で一意である必要があります。

例

新しいデータベース<testDB.db>を作成する場合、SQLITE3ステートメントは次のようになります-

$sqlite3 testDB.db
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

上記のコマンドは、現在のディレクトリに testDB.db ファイルを作成します。このファイルは、SQLiteエンジンによってデータベースとして使用されます。データベースの作成中に気付いた場合、sqlite3コマンドは、データベースファイルを正常に作成した後に sqlite> プロンプトを表示します。

データベースが作成されたら、次のSQLite .databases コマンドを使用して、データベースのリストでデータベースを確認できます。

sqlite>.databases
seq  name             file
---  ---------------  ----------------------
0    main            /home/sqlite/testDB.db

次のようにsqliteプロンプトから出てくるためにSQLite .quit コマンドを使用します-

sqlite>.quit
$

.dumpコマンド

*.dump* ドットコマンドを使用して、コマンドプロンプトで次のSQLiteコマンドを使用して、データベース全体をテキストファイルにエクスポートできます。

$sqlite3 testDB.db .dump > testDB.sql

上記のコマンドは、 testDB.db データベースの内容全体をSQLiteステートメントに変換し、ASCIIテキストファイル testDB.sql にダンプします。次のように、簡単な方法で生成されたtestDB.sqlから復元を実行できます-

$sqlite3 testDB.db < testDB.sql

この時点ではデータベースは空なので、データベースにテーブルとデータがほとんどなければ、上記の2つの手順を試すことができます。とりあえず、次の章に進みましょう。

SQLite-ATTACHデータベース

複数のデータベースが使用可能で、一度にいずれか1つのデータベースを使用する場合を考えてください。 SQLite ATTACH DATABASE ステートメントは特定のデータベースを選択するために使用され、このコマンドの後、すべてのSQLiteステートメントは接続されたデータベースの下で実行されます。

構文

以下は、SQLite ATTACH DATABASEステートメントの基本的な構文です。

ATTACH DATABASE 'DatabaseName' As 'Alias-Name';

上記のコマンドは、データベースがまだ作成されていない場合にもデータベースを作成します。それ以外の場合、データベースファイル名を論理データベース「エイリアス名」に添付します。

例

既存のデータベース testDB.db をアタッチする場合、ATTACH DATABASEステートメントは次のようになります-

sqlite> ATTACH DATABASE 'testDB.db' as 'TEST';

SQLite .database コマンドを使用して、接続されたデータベースを表示します。

sqlite> .database
seq  name             file
---  ---------------  ----------------------
0    main            /home/sqlite/testDB.db
2    test            /home/sqlite/testDB.db

データベース名 main および temp は、一時データベースおよびその他の一時データオブジェクトを保持するプライマリデータベースおよびデータベース用に予約されています。これらのデータベース名は両方のデータベース接続に存在するため、接続には使用しないでください。そうしないと、次の警告メッセージが表示されます。

sqlite> ATTACH DATABASE 'testDB.db' as 'TEMP';
Error: database TEMP is already in use
sqlite> ATTACH DATABASE 'testDB.db' as 'main';
Error: database TEMP is already in use

SQLite-データベースの取り外し

SQLite DETACH DATABASE ステートメントは、以前にATTACHステートメントを使用してアタッチされていたデータベース接続から名前付きデータベースをデタッチおよび分離するために使用されます。同じデータベースファイルに複数のエイリアスが添付されている場合、DETACHコマンドは指定された名前のみを切断し、残りの添付ファイルは引き続き続行されます。 main または temp データベースをデタッチすることはできません。

データベースがインメモリまたは一時データベースの場合、データベースは破棄され、コンテンツは失われます。

構文

以下は、SQLite DETACH DATABASE 'Alias-Name’ステートメントの基本的な構文です。

DETACH DATABASE 'Alias-Name';

ここで、「エイリアス名」は、ATTACHステートメントを使用してデータベースをアタッチするときに使用したものと同じエイリアスです。

例

*.database* コマンドを使用して確認できるように、前の章で作成し、「test」と「currentDB」で接続したデータベースがあると考えてください。

sqlite>.databases
seq  name             file
---  ---------------  ----------------------
0    main            /home/sqlite/testDB.db
2    test            /home/sqlite/testDB.db
3    currentDB       /home/sqlite/testDB.db

次のコマンドを使用して、testDB.dbから「currentDB」をデタッチしてみましょう。

sqlite> DETACH DATABASE 'currentDB';

現在、現在の添付ファイルを確認すると、testDB.dbがまだ「test」と「main」で接続されていることがわかります。

sqlite>.databases
seq  name             file
---  ---------------  ----------------------
0    main            /home/sqlite/testDB.db
2    test            /home/sqlite/testDB.db

SQLite-CREATE TABLE

SQLite CREATE TABLE ステートメントは、指定されたデータベースのいずれかに新しいテーブルを作成するために使用されます。基本的なテーブルを作成するには、テーブルに名前を付け、その列と各列のデータ型を定義する必要があります。

構文

以下は、CREATE TABLEステートメントの基本的な構文です。

CREATE TABLE database_name.table_name(
   column1 datatype PRIMARY KEY(one or more columns),
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype
);

CREATE TABLEは、データベースシステムに新しいテーブルを作成するよう指示するキーワードです。テーブルの一意の名前または識別子は、CREATE TABLEステートメントの後に続きます。オプションで、_table_name_とともに_database_name_を指定できます。

例

以下は、IDを主キーとしてCOMPANYテーブルを作成する例です。NOTNULLは、このテーブルにレコードを作成する際にこれらのフィールドをNULLにできないことを示す制約です。

sqlite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

次の章の演習で使用するテーブルをもう1つ作成します。

sqlite> CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

SQLiteコマンド .tables コマンドを使用して、テーブルが正常に作成されたかどうかを確認できます。このコマンドは、接続されたデータベース内のすべてのテーブルをリストダウンするために使用されます。

sqlite>.tables
COMPANY     DEPARTMENT

ここでは、メインデータベース用のCOMPANYテーブルとtestDB.db用に作成された 'test’エイリアス用のtest.COMPANYテーブルが表示されているため、COMPANYテーブルを2回表示できます。次のSQLite .schema コマンドを使用して、テーブルに関する完全な情報を取得できます。

sqlite>.schema COMPANY
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

SQLite-DROPテーブル

SQLite DROP TABLE ステートメントを使用して、テーブル定義と、そのテーブルのすべての関連データ、インデックス、トリガー、制約、およびアクセス許可の仕様を削除します。

テーブルを削除すると、テーブルで使用可能なすべての情報も永久に失われるため、このコマンドの使用には注意が必要です。

構文

DROP TABLEステートメントの基本的な構文は次のとおりです。オプションで、次のようにテーブル名とともにデータベース名を指定できます-

DROP TABLE database_name.table_name;

例

最初にCOMPANYテーブルを確認してから、データベースから削除します。

sqlite>.tables
COMPANY       test.COMPANY

これは、データベースでCOMPANYテーブルが使用できることを意味するため、次のようにドロップします。

sqlite>DROP TABLE COMPANY;
sqlite>

これで、.TABLESコマンドを試しても、COMPANYテーブルはもう見つかりません。

sqlite>.tables
sqlite>

データベースからテーブルが正常に削除されたことを意味するものは何も表示されません。

SQLite-クエリの挿入

SQLite INSERT INTO ステートメントは、データベースのテーブルに新しいデータ行を追加するために使用されます。

構文

INSERT INTOステートメントの2つの基本的な構文は次のとおりです。

INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]
VALUES (value1, value2, value3,...valueN);

ここで、column1、column2、… columnNは、データを挿入するテーブル内の列の名前です。

テーブルのすべての列に値を追加する場合、SQLiteクエリで列名を指定する必要がない場合があります。ただし、値の順序が表の列と同じ順序であることを確認してください。 SQLiteのINSERT INTO構文は次のようになります-

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

例

次のようにtestDB.dbにCOMPANYテーブルをすでに作成していることを考慮してください-

sqlite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

これで、次のステートメントはCOMPANYテーブルに6つのレコードを作成します。

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'David', 27, 'Texas', 85000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );

次のように2番目の構文を使用して、COMPANYテーブルにレコードを作成できます-

INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );

上記のすべてのステートメントは、COMPANYテーブルに次のレコードを作成します。次の章では、これらすべてのレコードをテーブルから表示する方法を学びます。

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

別のテーブルを使用して1つのテーブルを作成する

別のテーブルにフィールドのセットがあり、最初のテーブルにデータを入力する必要がある場合、別のテーブルのselectステートメントを使用して、データをテーブルに入力できます。ここに構文があります-

INSERT INTO first_table_name [(column1, column2, ... columnN)]
   SELECT column1, column2, ...columnN
   FROM second_table_name
   [WHERE condition];

今のところ、上記のステートメントはスキップできます。最初に、後続の章で説明するSELECT句とWHERE句を学習しましょう。

SQLite-SELECTクエリ

SQLite SELECT ステートメントは、結果テーブルの形式でデータを返すSQLiteデータベーステーブルからデータをフェッチするために使用されます。これらの結果テーブルは、*結果セット*とも呼ばれます。

構文

以下は、SQLite SELECTステートメントの基本的な構文です。

SELECT column1, column2, columnN FROM table_name;

ここでは、column1、column2 … 値を取得するテーブルのフィールドです。あなたがフィールドで利用可能なすべてのフィールドを取得したい場合は、次の構文を使用することができます-

SELECT *FROM table_name;

例

次のレコードを持つCOMPANYテーブルを検討してください-

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

以下は、SELECTステートメントを使用してこれらすべてのレコードを取得して表示する例です。ここでは、最初の3つのコマンドを使用して、適切にフォーマットされた出力を設定しています。

sqlite>.header on
sqlite>.mode column
sqlite> SELECT* FROM COMPANY;

最後に、次の結果が得られます。

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

COMPANYテーブルの選択したフィールドのみを取得する場合は、次のクエリを使用します-

sqlite> SELECT ID, NAME, SALARY FROM COMPANY;

上記のクエリは、次の結果を生成します。

ID          NAME        SALARY
----------  ----------  ----------
1           Paul        20000.0
2           Allen       15000.0
3           Teddy       20000.0
4           Mark        65000.0
5           David       85000.0
6           Kim         45000.0
7           James       10000.0

出力列幅の設定

*.mode column* の場合、表示される列のデフォルトの幅が原因で発生する切り捨てられた出力に関連する問題に直面することがあります。 あなたができることは、次のように *.width num、num ....* コマンドを使用して列表示可能な列幅を設定することができます-

sqlite>.width 10, 20, 10
sqlite>SELECT * FROM COMPANY;

上記の .width コマンドは、最初の列幅を10、2番目の列幅を20、3番目の列幅を10に設定します。最後に、上記のSELECTステートメントは次の結果を返します。

ID          NAME                  AGE         ADDRESS     SALARY
----------  --------------------  ----------  ----------  ----------
1           Paul                  32          California  20000.0
2           Allen                 25          Texas       15000.0
3           Teddy                 23          Norway      20000.0
4           Mark                  25          Rich-Mond   65000.0
5           David                 27          Texas       85000.0
6           Kim                   22          South-Hall  45000.0
7           James                 24          Houston     10000.0

スキーマ情報

すべての* dotコマンド*はSQLiteプロンプトで使用できるため、SQLiteでプログラミングする場合、 sqlite_master テーブルで次のSELECTステートメントを使用して、データベースで作成されたすべてのテーブルをリストダウンします。

sqlite> SELECT tbl_name FROM sqlite_master WHERE type = 'table';

testDB.dbにCOMPANYテーブルのみがあると仮定すると、次の結果が生成されます。

tbl_name
----------
COMPANY

次のように、COMPANYテーブルに関する完全な情報をリストできます-

sqlite> SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'COMPANY';

testDB.dbにCOMPANYテーブルのみがあると仮定すると、次の結果が生成されます。

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
)

SQLite-演算子

SQLiteのオペレーターとは何ですか？

演算子は、比較や算術演算などの操作を実行するためにSQLiteステートメントのWHERE句で主に使用される予約語または文字です。

演算子は、SQLiteステートメントで条件を指定し、ステートメント内の複数の条件の組み合わせとして機能するために使用されます。

算術演算子
比較演算子
論理演算子
ビットごとの演算子

SQLiteの算術演算子

変数 a が10を保持し、変数 b が20を保持すると仮定すると、SQLite算術演算子は次のように使用されます-

リンク：/sqlite/sqlite_arithmetic_operators [例を表示]

Operator	Description	Example
+ (Addition)	Adds values on either side of the operator	a + b will give 30
- (Subtraction)	Subtracts the right hand operand from the left hand operand	a - b will give -10
*(Multiplication)	Multiplies values on either side of the operator	a* b will give 200
/(Division)	Divides the left hand operand by the right hand operand	b/a will give 2
% (Modulus)	Divides the left hand operand by the right hand operand and returns the remainder	b % a will give 0

SQLite比較演算子

変数 a が10を保持し、変数 b が20を保持すると仮定すると、SQLite比較演算子は次のように使用されます

リンク：/sqlite/sqlite_comparison_operators [例を表示]

Operator	Description	Example
==	Checks if the values of two operands are equal or not, if yes then the condition becomes true.	(a == b) is not true.
=	Checks if the values of two operands are equal or not, if yes then the condition becomes true.	(a = b) is not true.
!=	Checks if the values of two operands are equal or not, if the values are not equal, then the condition becomes true.	(a != b) is true.
<>	Checks if the values of two operands are equal or not, if the values are not equal, then the condition becomes true.	(a <> b) is true.
>	Checks if the values of the left operand is greater than the value of the right operand, if yes then the condition becomes true.	(a > b) is not true.
<	Checks if the values of the left operand is less than the value of the right operand, if yes then the condition becomes true.	(a < b) is true.
>=	Checks if the value of the left operand is greater than or equal to the value of the right operand, if yes then the condition becomes true.	(a >= b) is not true.
⇐	Checks if the value of the left operand is less than or equal to the value of the right operand, if yes then the condition becomes true.	(a ⇐ b) is true.
!<	Checks if the value of the left operand is not less than the value of the right operand, if yes then the condition becomes true.	(a !< b) is false.
!>	Checks if the value of the left operand is not greater than the value of the right operand, if yes then the condition becomes true.	(a !> b) is true.

SQLite論理演算子

以下は、SQLiteで使用可能なすべての論理演算子のリストです。

リンク：/sqlite/sqlite_logical_operators [例を表示]

シニア

演算子と説明

1

そして

AND演算子を使用すると、SQLステートメントのWHERE句に複数の条件を含めることができます。

2

の間に

BETWEEN演算子は、最小値と最大値を指定して、値のセット内にある値を検索するために使用されます。

3

存在

EXISTS演算子は、特定の基準を満たす特定のテーブル内の行の存在を検索するために使用されます。

4

IN

IN演算子は、値を指定されたリテラル値のリストと比較するために使用されます。

5

ありませんで

値を指定されたリテラル値のリストと比較するために使用されるIN演算子の否定。

6

好き

LIKE演算子は、ワイルドカード演算子を使用して値を同様の値と比較するために使用されます。

7

*GLOB*

GLOB演算子は、ワイルドカード演算子を使用して値を同様の値と比較するために使用されます。また、LIKEとは異なり、GLOBでは大文字と小文字が区別されます。

8

*NOT*

NOT演算子は、使用される論理演算子の意味を逆にします。 Eg. 存在しない、間にない、ない、など これは否定演算子です。

9

OR

OR演算子は、SQLステートメントのWHERE句で複数の条件を結合するために使用されます。

10

無効です

NULL演算子は、値をNULL値と比較するために使用されます。

11

IS

IS演算子は=のように動作します

12

ではない

IS演算子は！=のように機能します

13

||

2つの異なる文字列を追加して、新しい文字列を作成します。

14

ユニーク

UNIQUE演算子は、指定されたテーブルのすべての行の一意性（重複なし）を検索します。

SQLiteビット演算子

ビット演算子はビットに対して機能し、ビットごとの操作を実行します。以下は、＆*および *| の真理値表です。

p	q	p & q	p
q	0	0	0
0	0	1	0
1	1	1	1
1	1	0	0

*A* = 60であると仮定します。および *B* = 13、その後バイナリ形式で、それらは次のようになります-

A = 0011 1100

B = 0000 1101

A＆B = 0000 1100

A | B = 0011 1101

〜A = 1100 0011

SQLite言語でサポートされているビット単位の演算子を次の表にリストします。 変数 *A* が60を保持し、変数 *B* が13を保持すると仮定します-

リンク：/sqlite/sqlite_bitwise_operators [例を表示]

[cols=",,",options="header",]
|===
|Operator |Description |Example |& |Binary AND Operator copies a bit to the result, if it exists in both operands. |(A & B) will give 12 which is 0000 1100 || |Binary OR Operator copies a bit, if it exists in either operand. |(A | B) will give 61 which is 0011 1101 |~ |Binary Ones Complement Operator is unary and has the effect of 'flipping' bits. |(~A ) will give -61 which is 1100 0011 in 2's complement form due to a signed binary number |<< |Binary Left Shift Operator. The left operands value is moved left by the number of bits specified by the right operand. |A << 2 will give 240 which is 1111 0000 |>> |Binary Right Shift Operator. The left operands value is moved right by the number of bits specified by the right operand. |A >> 2 will give 15 which is 0000 1111
|===

SQLite-式

式は、値に評価される1つ以上の値、演算子、およびSQL関数の組み合わせです。

SQL式は式に似ており、クエリ言語で記述されています。特定のデータセットについてデータベースを照会するために使用することもできます。

構文

次のようにSELECTステートメントの基本的な構文を考慮してください-

SELECT column1, column2, columnN
FROM table_name
WHERE [CONDITION | EXPRESSION];

以下は、さまざまなタイプのSQLite式です。

SQLite-ブール式

SQLiteブール式は、一致する単一の値に基づいてデータをフェッチします。以下は構文です-

SELECT column1, column2, columnN
FROM table_name
WHERE SINGLE VALUE MATCHTING EXPRESSION;

次のレコードを持つCOMPANYテーブルを検討してください-

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

以下は、SQLiteブール式の使用法を示す簡単な例です-

sqlite> SELECT * FROM COMPANY WHERE SALARY = 10000;

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
4           James        24          Houston   10000.0

SQLite-数値式

これらの式は、クエリで数学演算を実行するために使用されます。以下は構文です-

SELECT numerical_expression as OPERATION_NAME
[FROM table_name WHERE CONDITION] ;

ここで、numeric_expressionは数式または任意の式に使用されます。以下は、SQLite数値式の使用法を示す簡単な例です。

sqlite> SELECT (15 + 6) AS ADDITION
ADDITION = 21

avg（）、sum（）、count（）、*などのいくつかの組み込み関数があり、テーブルまたは特定のテーブル列に対して*集計データ計算*と呼ばれるものを実行します。

sqlite> SELECT COUNT(*) AS "RECORDS" FROM COMPANY;
RECORDS = 7

SQLite-日付式

日付式は、現在のシステムの日付と時刻の値を返します。これらの式は、さまざまなデータ操作で使用されます。

sqlite> SELECT CURRENT_TIMESTAMP;
CURRENT_TIMESTAMP = 2013-03-17 10:43:35

SQLite-WHERE句

SQLiteの WHERE 句を使用して、1つまたは複数のテーブルからデータをフェッチするときに条件を指定します。

指定された条件が満たされた場合、trueを意味し、テーブルから特定の値を返します。 WHERE句を使用してレコードをフィルタリングし、必要なレコードのみを取得する必要があります。

WHERE句はSELECTステートメントで使用されるだけでなく、UPDATE、DELETEステートメントなどでも使用されます。これについては、以降の章で説明します。

構文

WHERE句を使用したSQLite SELECTステートメントの基本的な構文は次のとおりです。

SELECT column1, column2, columnN
FROM table_name
WHERE [condition]

例

link：/sqlite/sqlite_operators [比較演算子または論理演算子]を使用して、>、<、=、LIKE、NOTなどの条件を指定できます。次のレコードを持つCOMPANYテーブルを検討してください-

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

以下は、SQLite論理演算子の使用法を示す簡単な例です。次のSELECTステートメントは、AGEが25以上、給与が65000.00以上のすべてのレコードをリストします。

sqlite> SELECT *FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

次のSELECTステートメントは、AGEが25以上、または給与が65000.00以上のすべてのレコードをリストします。

sqlite> SELECT* FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

SELECTステートメントに続くと、AGEがNULLでないすべてのレコードがリストされます。これは、どのレコードもAGEがNULLでないため、すべてのレコードを意味します。

sqlite>  SELECT *FROM COMPANY WHERE AGE IS NOT NULL;

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

次のSELECTステートメントは、NAMEが「Ki」で始まるすべてのレコードをリストしますが、「Ki」の後に何があっても問題ありません。

sqlite> SELECT* FROM COMPANY WHERE NAME LIKE 'Ki%';

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
6           Kim         22          South-Hall  45000.0

次のSELECTステートメントは、NAMEが「Ki」で始まるすべてのレコードをリストしますが、「Ki」の後に何があっても問題ありません。

sqlite> SELECT * FROM COMPANY WHERE NAME GLOB 'Ki*';

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
6           Kim         22          South-Hall  45000.0

次のSELECTステートメントは、AGE値が25または27であるすべてのレコードをリストします。

sqlite> SELECT *FROM COMPANY WHERE AGE IN ( 25, 27 );

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

次のSELECTステートメントは、AGE値が25でも27でもないすべてのレコードをリストします。

sqlite> SELECT* FROM COMPANY WHERE AGE NOT IN ( 25, 27 );

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
3           Teddy       23          Norway      20000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

次のSELECTステートメントは、AGE値が25と27の間にあるすべてのレコードをリストします。

sqlite> SELECT *FROM COMPANY WHERE AGE BETWEEN 25 AND 27;

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

次のSELECTステートメントはSQLサブクエリを使用します。サブクエリは、SALARY> 65000以降のWHERE句を持つすべてのレコードを検索し、外部クエリからのAGEが存在するすべてのレコードをリストするためにEXISTS演算子とともに使用されます。サブクエリによって返された結果で-

sqlite> SELECT AGE FROM COMPANY
   WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

AGE
----------
32
25
23
25
27
22
24

次のSELECTステートメントはSQLサブクエリを使用します。サブクエリは、SALARY> 65000以降のWHERE句を含むすべてのレコードを検索し、>演算子とともに>演算子を使用して、外部クエリからのAGEが大きいすべてのレコードをリストしますサブクエリによって返される結果の年齢よりも。

sqlite> SELECT* FROM COMPANY
   WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0

SQLite-ANDおよびOR演算子

SQLiteの AND および OR 演算子を使用して、複数の条件をコンパイルし、SQLiteステートメントで選択したデータを絞り込みます。これらの2つの演算子は、*結合演算子*と呼ばれます。

これらの演算子は、同じSQLiteステートメントで異なる演算子と複数の比較を行う手段を提供します。

AND演算子

*AND* 演算子を使用すると、SQLiteステートメントのWHERE句に複数の条件を含めることができます。 AND演算子を使用している間、すべての条件が真の場合、完全な条件が真であると想定されます。 たとえば、[condition1] AND [condition2]は、condition1とcondition2の両方がtrueの場合にのみtrueになります。

構文

WHERE句を使用したAND演算子の基本構文は次のとおりです。

SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];

AND演算子を使用して、 N 個の条件を組み合わせることができます。 SQLiteステートメントがアクションを実行するためには、トランザクションであろうとクエリであろうと、ANDで区切られたすべての条件がTRUEでなければなりません。

例

次のレコードを持つCOMPANYテーブルを検討してください-

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

次のSELECTステートメントは、AGEが25以上、給与が65000.00以上のすべてのレコードをリストします。

sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

OR演算子

OR演算子は、SQLiteステートメントのWHERE句で複数の条件を結合するためにも使用されます。 OR演算子を使用している場合、少なくともいずれかの条件が真である場合、完全な条件が真であると見なされます。たとえば、condition1またはcondition2のいずれかが真の場合、[condition1] OR [condition2]は真になります。

構文

以下は、WHERE句を使用したOR演算子の基本構文です。

SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]

OR演算子を使用して、 N 個の条件を組み合わせることができます。 SQLiteステートメントがアクションを実行するためには、トランザクションであろうとクエリであろうと、ORで区切られた条件のいずれか1つのみがTRUEでなければなりません。

例

次のレコードを持つCOMPANYテーブルを検討してください。

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

次のSELECTステートメントは、AGEが25以上、または給与が65000.00以上のすべてのレコードをリストします。

sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

SQLite-UPDATEクエリ

SQLite UPDATE クエリは、テーブル内の既存のレコードを変更するために使用されます。 UPDATEクエリでWHERE句を使用して、選択した行を更新できます。そうしないと、すべての行が更新されます。

構文

以下は、WHERE句を使用したUPDATEクエリの基本構文です。

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

ANDまたはOR演算子を使用して、 N 個の条件を組み合わせることができます。

例

次のレコードを持つCOMPANYテーブルを検討してください-

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

以下は、IDが6である顧客のADDRESSを更新する例です。

sqlite> UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6;

これで、COMPANY表には次のレコードが含まれます。

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          Texas       45000.0
7           James       24          Houston     10000.0

COMPANYテーブルのすべてのADDRESSおよびSALARY列の値を変更する場合、WHERE句を使用する必要はありません。UPDATEクエリは次のようになります-

sqlite> UPDATE COMPANY SET ADDRESS = 'Texas', SALARY = 20000.00;

さて、COMPANY表には次のレコードがあります-

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          Texas       20000.0
2           Allen       25          Texas       20000.0
3           Teddy       23          Texas       20000.0
4           Mark        25          Texas       20000.0
5           David       27          Texas       20000.0
6           Kim         22          Texas       20000.0
7           James       24          Texas       20000.0

SQLite-DELETEクエリ

SQLite DELETE クエリは、テーブルから既存のレコードを削除するために使用されます。 DELETEクエリでWHERE句を使用して、選択した行を削除できます。そうしないと、すべてのレコードが削除されます。

構文

WHERE句を使用したDELETEクエリの基本的な構文は次のとおりです。

DELETE FROM table_name
WHERE [condition];

ANDまたはOR演算子を使用して、 N 個の条件を組み合わせることができます。

例

次のレコードを持つCOMPANYテーブルを検討してください。

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

以下は、IDが7の顧客を削除する例です。

sqlite> DELETE FROM COMPANY WHERE ID = 7;

COMPANYテーブルには、次のレコードが含まれるようになります。

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0

COMPANYテーブルからすべてのレコードを削除する場合、次のようにDELETEクエリでWHERE句を使用する必要はありません-

sqlite> DELETE FROM COMPANY;

現在、すべてのレコードがDELETEステートメントによって削除されているため、COMPANYテーブルにはレコードがありません。

SQLite-LIKE句

SQLite LIKE 演算子は、ワイルドカードを使用してテキスト値とパターンを照合するために使用されます。検索式がパターン式と一致する場合、LIKE演算子はtrue、つまり1を返します。 LIKE演算子と組み合わせて使用される2つのワイルドカードがあります-

パーセント記号（％）
アンダースコア（_）

パーセント記号は、0、1、または複数の数字または文字を表します。下線は、単一の数字または文字を表します。これらのシンボルは組み合わせて使用できます。

構文

以下は、％および_の基本構文です。

SELECT FROM table_name
WHERE column LIKE 'XXXX%'
or
SELECT FROM table_name
WHERE column LIKE '%XXXX%'
or
SELECT FROM table_name
WHERE column LIKE 'XXXX_'
or
SELECT FROM table_name
WHERE column LIKE '_XXXX'
or
SELECT FROM table_name
WHERE column LIKE '_XXXX_'

ANDまたはOR演算子を使用して、 N 個の条件を組み合わせることができます。ここで、XXXXは任意の数値または文字列値です。

例

次の表に、 '％'および '_'演算子を含む異なるLIKE句を持つWHERE部分を示すいくつかの例を示します。

Sr.No.	Statement & Description
1	WHERE SALARY LIKE '200%' 200で始まる値を検索します
2	WHERE SALARY LIKE '%200%' 任意の位置に200がある値を検索します
3	WHERE SALARY LIKE '_00%' 2番目と3番目の位置に00がある値を検索します
4	WHERE SALARY LIKE '2_%_%' 2で始まり、長さが3文字以上の値を検索します
5	WHERE SALARY LIKE '%2' 2で終わる値を検索します
6	WHERE SALARY LIKE '_2%3' 2番目の位置に2があり、3で終わる値を検索します
7	WHERE SALARY LIKE '2___3' 2で始まり3で終わる5桁の数値の値を検索します

実際の例を見てみましょう。次のレコードを持つCOMPANYテーブルを考えてみましょう。

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

以下は、AGEが2で始まるCOMPANYテーブルのすべてのレコードを表示する例です。

sqlite> SELECT *FROM COMPANY WHERE AGE LIKE '2%';

これにより、次の結果が生成されます。

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

以下は、ADDRESSのテキスト内にハイフン（-）が含まれるCOMPANYテーブルのすべてのレコードを表示する例です。

sqlite> SELECT* FROM COMPANY WHERE ADDRESS  LIKE '%-%';

これにより、次の結果が生成されます。

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
4           Mark        25          Rich-Mond   65000.0
6           Kim         22          South-Hall  45000.0

SQLite-GLOB句

SQLiteの GLOB 演算子は、テキスト値のみをワイルドカードを使用したパターンと照合するために使用されます。検索式がパターン式と一致する場合、GLOB演算子はtrue、つまり1を返します。 LIKE演算子とは異なり、GLOBは大文字と小文字を区別し、次のワイルドカードを指定するためのUNIXの構文に従います。

アスタリスク記号（*）
疑問符（？）

アスタリスク記号（*）は、ゼロまたは複数の数字または文字を表します。疑問符（？）は、単一の数字または文字を表します。

構文

以下は、**および？*の基本構文です。

SELECT FROM table_name
WHERE column GLOB 'XXXX*'
or
SELECT FROM table_name
WHERE column GLOB '*XXXX*'
or
SELECT FROM table_name
WHERE column GLOB 'XXXX?'
or
SELECT FROM table_name
WHERE column GLOB '?XXXX'
or
SELECT FROM table_name
WHERE column GLOB '?XXXX?'
or
SELECT FROM table_name
WHERE column GLOB '????'

ANDまたはOR演算子を使用して、 N 個の条件を組み合わせることができます。ここで、XXXXは任意の数値または文字列値です。

例

次の表に、「*」と「？」の異なるLIKE句を持つWHERE部分を示すいくつかの例を示します。演算子。

Sr.No.	Statement & Description
1	WHERE SALARY GLOB '200'* 200で始まる値を検索します
2	*WHERE SALARY GLOB '200'*** 任意の位置に200がある値を検索します
3	WHERE SALARY GLOB '?00'* 2番目と3番目の位置に00がある値を検索します
4	WHERE SALARY GLOB '2??' 2で始まり、長さが3文字以上の値を検索します
5	*WHERE SALARY GLOB '2'** 2で終わる値を検索します
6	*WHERE SALARY GLOB '?23'** 2番目の位置に2があり、3で終わる値を検索します
7	WHERE SALARY GLOB '2???3' 2で始まり3で終わる5桁の数値の値を検索します

実際の例を見てみましょう、次のレコードを持つCOMPANYテーブルを考えてみましょう-

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

以下は、AGEが2で始まるCOMPANYテーブルのすべてのレコードを表示する例です。

sqlite> SELECT * FROM COMPANY WHERE AGE  GLOB '2*';

これにより、次の結果が生成されます。

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

以下は、ADDRESSのテキスト内にハイフン（-）が含まれるCOMPANYテーブルのすべてのレコードを表示する例です-

sqlite> SELECT * FROM COMPANY WHERE ADDRESS  GLOB '*-*';

これにより、次の結果が生成されます。

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
4           Mark        25          Rich-Mond   65000.0
6           Kim         22          South-Hall  45000.0

SQLite-LIMIT句

SQLiteの LIMIT 句は、SELECTステートメントによって返されるデータ量を制限するために使用されます。

構文

以下は、LIMIT句を使用したSELECTステートメントの基本構文です。

SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows]

以下は、OFFSET句とともに使用される場合のLIMIT句の構文です。

SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows] OFFSET [row num]

SQLiteエンジンは、次の最後の例に示すように、次の行から指定されたOFFSETまでの行を返します。

例

次のレコードを持つCOMPANYテーブルを検討してください-

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

以下は、テーブルからフェッチする行の数に従ってテーブルの行を制限する例です。

sqlite> SELECT *FROM COMPANY LIMIT 6;

これにより、次の結果が生成されます。

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0

ただし、特定の状況では、特定のオフセットからレコードのセットを取得する必要がある場合があります。次に、3 ^ rd ^の位置から3つのレコードを取得する例を示します。

sqlite> SELECT* FROM COMPANY LIMIT 3 OFFSET 2;

これにより、次の結果が生成されます。

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

SQLite-ORDER BY句

SQLiteの ORDER BY 句は、1つ以上の列に基づいてデータを昇順または降順に並べ替えるために使用されます。

構文

ORDER BY句の基本的な構文は次のとおりです。

SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

ORDER BY句では複数の列を使用できます。ソートに使用している列が何であれ、その列が列リストで使用可能であることを確認してください。

例

次のレコードを持つCOMPANYテーブルを検討してください。

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

以下に例を示します。これは、SALARYの降順で結果をソートします。

sqlite> SELECT *FROM COMPANY ORDER BY SALARY ASC;

これにより、次の結果が生成されます。

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
7           James       24          Houston     10000.0
2           Allen       25          Texas       15000.0
1           Paul        32          California  20000.0
3           Teddy       23          Norway      20000.0
6           Kim         22          South-Hall  45000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

以下は、結果をNAMEとSALARYで降順に並べ替える例です。

sqlite> SELECT* FROM COMPANY ORDER BY NAME, SALARY ASC;

これにより、次の結果が生成されます。

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000.0
5           David       27          Texas       85000.0
7           James       24          Houston     10000.0
6           Kim         22          South-Hall  45000.0
4           Mark        25          Rich-Mond   65000.0
1           Paul        32          California  20000.0
3           Teddy       23          Norway      20000.0

以下は、結果をNAMEで降順に並べ替える例です。

sqlite> SELECT * FROM COMPANY ORDER BY NAME DESC;

これにより、次の結果が生成されます。

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
3           Teddy       23          Norway      20000.0
1           Paul        32          California  20000.0
4           Mark        25          Rich-Mond   65000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0
5           David       27          Texas       85000.0
2           Allen       25          Texas       15000.0

SQLite-GROUP BY句

SQLiteの GROUP BY 句は、SELECTステートメントと連携して使用され、同一のデータをグループに配置します。

GROUP BY句は、SELECTステートメントのWHERE句に続き、ORDER BY句に先行します。

構文

GROUP BY句の基本的な構文は次のとおりです。 GROUP BY句は、WHERE句の条件に従う必要があり、ORDER BY句を使用する場合はORDER BY句の前になければなりません。

SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN

GROUP BY句では複数の列を使用できます。グループ化に使用している列が何であれ、その列が列リストで使用可能であることを確認してください。

例

次のレコードを持つCOMPANYテーブルを検討してください。

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

あなたが各顧客の給与の総額を知りたい場合、GROUP BYクエリは次のようになります-

sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;

これは、次の結果を生成します-

NAME        SUM(SALARY)
----------  -----------
Allen       15000.0
David       85000.0
James       10000.0
Kim         45000.0
Mark        65000.0
Paul        20000.0
Teddy       20000.0

次に、次のINSERTステートメントを使用してCOMPANYテーブルにさらに3つのレコードを作成します。

INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00 );
INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00 );
INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00 );

これで、テーブルには重複した名前を持つ次のレコードが含まれます。

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0
8           Paul        24          Houston     20000.0
9           James       44          Norway      5000.0
10          James       45          Texas       5000.0

繰り返しますが、同じステートメントを使用して、NAME列を使用するすべてのレコードを次のようにグループ化します。

sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME;

これにより、次の結果が生成されます。

NAME        SUM(SALARY)
----------  -----------
Allen       15000
David       85000
James       20000
Kim         45000
Mark        65000
Paul        40000
Teddy       20000

次のようにGROUP BY句とともにORDER BY句を使用してみましょう-

sqlite>  SELECT NAME, SUM(SALARY)
   FROM COMPANY GROUP BY NAME ORDER BY NAME DESC;

これにより、次の結果が生成されます。

NAME        SUM(SALARY)
----------  -----------
Teddy       20000
Paul        40000
Mark        65000
Kim         45000
James       20000
David       85000
Allen       15000

SQLite-HAVING句

HAVING句を使用すると、最終結果に表示されるグループ結果をフィルタリングする条件を指定できます。

WHERE句は選択した列に条件を配置しますが、HAVING句はGROUP BY句によって作成されたグループに条件を配置します。

構文

SELECTクエリでのHAVING句の位置は次のとおりです。

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

HAVING句は、クエリ内のGROUP BY句の後に指定する必要があり、使用する場合はORDER BY句の前にも指定する必要があります。以下は、HAVING句を含むSELECTステートメントの構文です。

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

例

次のレコードを持つCOMPANYテーブルを検討してください。

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0
8           Paul        24          Houston     20000.0
9           James       44          Norway      5000.0
10          James       45          Texas       5000.0

以下に例を示します。これは、名前カウントが2未満のレコードを表示します。

sqlite > SELECT *FROM COMPANY GROUP BY name HAVING count(name) < 2;

これにより、次の結果が生成されます。

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000
5           David       27          Texas       85000
6           Kim         22          South-Hall  45000
4           Mark        25          Rich-Mond   65000
3           Teddy       23          Norway      20000

以下に例を示します。名前のカウントが2より大きいレコードが表示されます。

sqlite > SELECT* FROM COMPANY GROUP BY name HAVING count(name) > 2;

これにより、次の結果が生成されます。

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
10          James       45          Texas       5000

SQLite-DISTINCTキーワード

SQLite DISTINCT キーワードは、すべての重複レコードを削除し、一意のレコードのみを取得するためにSELECTステートメントと組み合わせて使用されます。

テーブルに複数の重複レコードがある場合があります。そのようなレコードをフェッチするとき、重複レコードをフェッチするのではなく、一意のレコードのみをフェッチする方が理にかなっています。

構文

以下は、重複レコードを排除するためのDISTINCTキーワードの基本的な構文です。

SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]

例

次のレコードを持つCOMPANYテーブルを検討してください。

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0
8           Paul        24          Houston     20000.0
9           James       44          Norway      5000.0
10          James       45          Texas       5000.0

まず、次のSELECTクエリが重複する給与レコードを返す方法を見てみましょう。

sqlite> SELECT name FROM COMPANY;

これにより、次の結果が生成されます。

NAME
----------
Paul
Allen
Teddy
Mark
David
Kim
James
Paul
James
James

次に、上記のSELECTクエリで DISTINCT キーワードを使用して、結果を確認します。

sqlite> SELECT DISTINCT name FROM COMPANY;

これにより、重複するエントリがない次の結果が生成されます。

NAME
----------
Paul
Allen
Teddy
Mark
David
Kim
James

SQLite-PRAGMA

SQLite PRAGMA コマンドは、SQLite環境内のさまざまな環境変数と状態フラグを制御するために使用される特別なコマンドです。 PRAGMA値は読み取ることができ、要件に基づいて設定することもできます。

構文

現在のPRAGMA値を照会するには、プラグマの名前を指定するだけです。

PRAGMA pragma_name;

PRAGMAの新しい値を設定するには、次の構文を使用します。

PRAGMA pragma_name = value;

設定モードは名前または同等の整数のいずれかです。ただし、返される値は常に整数になります。

auto_vacuumプラグマ

*auto_vacuum* プラグマは、自動バキュームモードを取得または設定します。 以下は簡単な構文です。

PRAGMA [database.]auto_vacuum;
PRAGMA [database.]auto_vacuum = mode;

ここで、 mode は次のいずれかです。

Sr.No.

Pragma Value & Description

1

0 or NONE

自動バキュームは無効です。これはデフォルトのモードであり、VACUUMコマンドを使用して手動でバキュームされない限り、データベースファイルのサイズが縮小することはありません。

2

1 or FULL

自動バキュームが有効になっており、完全に自動化されているため、データベースからデータが削除されるとデータベースファイルが縮小します。

3

2 or INCREMENTAL

自動バキュームは有効になっていますが、手動でアクティブにする必要があります。このモードでは、参照データは維持されますが、空きページは単に空きリストに追加されます。これらのページは、いつでも* incremental_vacuumプラグマ*を使用して復元できます。

cache_sizeプラグマ

*cache_size* プラグマは、メモリ内ページキャッシュの最大サイズを取得または一時的に設定できます。 以下は簡単な構文です。

PRAGMA [database.]cache_size;
PRAGMA [database.]cache_size = pages;

*pages* 値は、キャッシュ内のページ数を表します。 組み込みのページキャッシュのデフォルトサイズは2,000ページで、最小サイズは10ページです。

case_sensitive_likeプラグマ

*case_sensitive_like* プラグマは、組み込みのLIKE式の大文字と小文字の区別を制御します。 デフォルトでは、このプラグマはfalseです。つまり、組み込みのLIKE演算子は大文字と小文字を無視します。 以下は簡単な構文です。

PRAGMA case_sensitive_like = [true|false];

このプラグマの現在の状態を照会する方法はありません。

count_changesプラグマ

*count_changes* プラグマは、INSERT、UPDATE、DELETEなどのデータ操作ステートメントの戻り値を取得または設定します。 以下は簡単な構文です。

PRAGMA count_changes;
PRAGMA count_changes = [true|false];

デフォルトでは、このプラグマはfalseであり、これらのステートメントは何も返しません。 trueに設定すると、上記の各ステートメントは、操作によって影響を受ける行を示す単一の整数値で構成される1列、1行のテーブルを返します。

database_listプラグマ

*database_list* プラグマは、接続されているすべてのデータベースをリストダウンするために使用されます。 以下は簡単な構文です。

PRAGMA database_list;

このプラグマは、開いているデータベースまたは接続されているデータベースごとに1行の3列のテーブルを返し、データベースシーケンス番号、名前、および関連付けられているファイルを提供します。

エンコーディングプラグマ

*encoding* プラグマは、文字列をエンコードしてデータベースファイルに保存する方法を制御します。 以下は簡単な構文です。

PRAGMA encoding;
PRAGMA encoding = format;

形式の値は、 UTF-8、UTF-16le 、または UTF-16be のいずれかです。

freelist_countプラグマ

*freelist_count* プラグマは、現在空きおよび利用可能としてマークされているデータベースページの数を示す単一の整数を返します。 以下は簡単な構文です。

PRAGMA [database.]freelist_count;

形式の値は、 UTF-8、UTF-16le 、または UTF-16be のいずれかです。

index_infoプラグマ

*index_info* プラグマは、データベースインデックスに関する情報を返します。 以下は簡単な構文です。

PRAGMA [database.]index_info( index_name );

結果セットには、列の順序、テーブル内の列インデックス、および列名を与えるインデックスに含まれる列ごとに1行が含まれます。

index_listプラグマ

*index_list* プラグマは、テーブルに関連付けられているすべてのインデックスをリストします。 以下は簡単な構文です。

PRAGMA [database.]index_list( table_name );

結果セットには、インデックスシーケンス、インデックス名、およびインデックスが一意かどうかを示すフラグを与えるインデックスごとに1行が含まれます。

journal_modeプラグマ

*journal_mode* プラグマは、ジャーナルファイルの保存および処理方法を制御するジャーナルモードを取得または設定します。 以下は簡単な構文です。

PRAGMA journal_mode;
PRAGMA journal_mode = mode;
PRAGMA database.journal_mode;
PRAGMA database.journal_mode = mode;

次の表にリストされているように、5つのサポートされているジャーナルモードがあります。

Sr.No.	Pragma Value & Description
1	DELETE これがデフォルトのモードです。ここで、トランザクションの終了時に、ジャーナルファイルが削除されます。
2	TRUNCATE ジャーナルファイルは、ゼロバイトの長さに切り捨てられます。
3	PERSIST ジャーナルファイルはそのまま残りますが、ヘッダーが上書きされ、ジャーナルが無効になったことを示します。
4	MEMORY ジャーナルレコードはディスクではなくメモリに保持されます。
5	OFF ジャーナルレコードは保持されません。

max_page_countプラグマ

*max_page_count* プラグマは、データベースの最大許容ページ数を取得または設定します。 以下は簡単な構文です。

PRAGMA [database.]max_page_count;
PRAGMA [database.]max_page_count = max_page;

デフォルト値は1,073,741,823です。これは1ギガページです。つまり、デフォルトの1 KBページサイズであれば、データベースは1テラバイトまで拡張できます。

page_countプラグマ

*page_count* プラグマは、データベース内の現在のページ数を返します。 以下は、単純な構文です-

PRAGMA [database.]page_count;

データベースファイルのサイズは、page_count * page_sizeである必要があります。

page_sizeプラグマ

*page_size* プラグマは、データベースページのサイズを取得または設定します。 以下は簡単な構文です。

PRAGMA [database.]page_size;
PRAGMA [database.]page_size = bytes;

デフォルトでは、許可されるサイズは512、1024、2048、4096、8192、16384、および32768バイトです。既存のデータベースのページサイズを変更する唯一の方法は、ページサイズを設定し、すぐにデータベースをVACUUMすることです。

parser_traceプラグマ

*parser_trace* プラグマは、SQLコマンドを解析する際のデバッグ状態の出力を制御します。 以下は簡単な構文です。

PRAGMA parser_trace = [true|false];

デフォルトではfalseに設定されていますが、trueに設定して有効にすると、SQLパーサーはSQLコマンドを解析するときにその状態を出力します。

recursive_triggersプラグマ

*recursive_triggers* プラグマは、再帰トリガー機能を取得または設定します。 再帰トリガーが有効になっていない場合、トリガーアクションは別のトリガーを起動しません。 以下は簡単な構文です。

PRAGMA recursive_triggers;
PRAGMA recursive_triggers = [true|false];

schema_versionプラグマ

*schema_version* プラグマは、データベースヘッダーに格納されているスキーマバージョン値を取得または設定します。 以下は簡単な構文です。

PRAGMA [database.]schema_version;
PRAGMA [database.]schema_version = number;

これは、スキーマの変更を追跡する32ビットの符号付き整数値です。スキーマ変更コマンドが実行されるたび（CREATE …など）またはDROP …）、この値は増加します。

secure_deleteプラグマ

*secure_delete* プラグマは、データベースからコンテンツを削除する方法を制御するために使用されます。 以下は簡単な構文です。

PRAGMA secure_delete;
PRAGMA secure_delete = [true|false];
PRAGMA database.secure_delete;
PRAGMA database.secure_delete = [true|false];

安全な削除フラグのデフォルト値は通常オフですが、これはSQLITE_SECURE_DELETEビルドオプションで変更できます。

sql_traceプラグマ

*sql_trace* プラグマは、SQLトレース結果を画面にダンプするために使用されます。 以下は簡単な構文です。

PRAGMA sql_trace;
PRAGMA sql_trace = [true|false];

このプラグマを含めるには、SQLiteをSQLITE_DEBUGディレクティブでコンパイルする必要があります。

同期プラグマ

*synchronous* プラグマは、現在のディスク同期モードを取得または設定します。これは、SQLiteが物理ストレージにデータを完全に書き込む方法を制御します。 以下は簡単な構文です。

PRAGMA [database.]synchronous;
PRAGMA [database.]synchronous = mode;

SQLiteは、表にリストされている次の同期モードをサポートします。

Sr.No.

Pragma Value & Description

1

0 or OFF

同期がまったくありません

2

1 or NORMAL

重要なディスク操作の各シーケンスの後に同期する

3

2 or FULL

重要なディスク操作のたびに同期する

temp_storeプラグマ

*temp_store* プラグマは、一時データベースファイルで使用されるストレージモードを取得または設定します。 以下は簡単な構文です。

PRAGMA temp_store;
PRAGMA temp_store = mode;

SQLiteは次のストレージモードをサポートしています。

Sr.No.

Pragma Value & Description

1

0 or DEFAULT

コンパイル時のデフォルトを使用します。通常はFILE。

2

1 or FILE

ファイルベースのストレージを使用します。

3

2 or MEMORY

メモリベースのストレージを使用します。

temp_store_directoryプラグマ

*temp_store_directory* プラグマは、一時データベースファイルに使用される場所を取得または設定します。 以下は簡単な構文です。

PRAGMA temp_store_directory;
PRAGMA temp_store_directory = 'directory_path';

user_versionプラグマ

*user_version* プラグマは、データベースヘッダーに格納されているユーザー定義のバージョン値を取得または設定します。 以下は簡単な構文です。

PRAGMA [database.]user_version;
PRAGMA [database.]user_version = number;

これは32ビットの符号付き整数値で、バージョントラッキングのために開発者が設定できます。

writable_schemaプラグマ

*writable_schema* プラグマは、システムテーブルを変更する機能を取得または設定します。 以下は簡単な構文です。

PRAGMA writable_schema;
PRAGMA writable_schema = [true|false];

このプラグマを設定すると、sqlite_masterテーブルなど、sqlite_で始まるテーブルを作成および変更できます。プラグマを使用すると、データベースが完全に破損する可能性があるため、注意してください。

SQLite-制約

制約は、テーブルのデータ列に適用されるルールです。これらは、テーブルに入ることができるデータのタイプを制限するために使用されます。これにより、データベース内のデータの正確性と信頼性が保証されます。

制約は、列レベルまたは表レベルです。列レベルの制約は1つの列にのみ適用されますが、表レベルの制約は表全体に適用されます。

以下は、SQLiteで使用可能な一般的に使用される制約です。

* NOT NULL制約*-列にNULL値を設定できないようにします。
DEFAULT Constraint -何も指定されていない場合、列のデフォルト値を提供します。
UNIQUE Constraint -列のすべての値が異なることを保証します。
* PRIMARYキー*-データベーステーブル内の各行/レコードを一意に識別します。
* CHECK制約*-列のすべての値が特定の条件を満たすことを確認します。

NOT NULL制約

デフォルトでは、列はNULL値を保持できます。列にNULL値を持たせたくない場合は、この列にNULLが許可されないように指定して、この列にそのような制約を定義する必要があります。

NULLはデータがないと同じではなく、不明なデータを表します。

例

たとえば、次のSQLiteステートメントは、COMPANYという新しいテーブルを作成し、5つの列を追加します。3つの列であるIDとNAMEとAGEは、NULLを受け入れないように指定します。

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

デフォルトの制約

DEFAULT INT制約は、INSERT INTOステートメントが特定の値を提供しない場合に列にデフォルト値を提供します。

例

たとえば、次のSQLiteステートメントはCOMPANYという新しいテーブルを作成し、5つの列を追加します。ここで、SALARY列はデフォルトで5000.00に設定されているため、INSERT INTOステートメントがこの列の値を提供しない場合、デフォルトでこの列は5000.00に設定されます。

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 50000.00
);

一意の制約

UNIQUE制約は、2つのレコードが特定の列に同じ値を持つことを防ぎます。たとえば、COMPANYテーブルでは、2人以上が同じ年齢になるのを防ぎたい場合があります。

例

たとえば、次のSQLiteステートメントはCOMPANYという新しいテーブルを作成し、5つの列を追加します。ここでは、AGE列はUNIQUEに設定されているため、同じ年齢の2つのレコードを持つことはできません-

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL UNIQUE,
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 50000.00
);

主キー制約

PRIMARY KEY制約は、データベーステーブル内の各レコードを一意に識別します。 UNIQUE列はさらに存在できますが、テーブルには主キーが1つしかありません。データベーステーブルを設計する場合、主キーは重要です。主キーは一意のIDです。

これらを使用して、テーブル行を参照します。主キーは、テーブル間のリレーションを作成するときに、他のテーブルの外部キーになります。「長年にわたるコーディングの監視」のため、SQLiteでは主キーがNULLになる可能性があります。これは他のデータベースには当てはまりません。

主キーは、データベーステーブル内の各行/レコードを一意に識別するテーブル内のフィールドです。主キーには一意の値が含まれている必要があります。主キー列にNULL値を含めることはできません。

テーブルには、単一または複数のフィールドで構成される主キーを1つだけ含めることができます。複数のフィールドが主キーとして使用される場合、それらは*複合キー*と呼ばれます。

テーブルのいずれかのフィールドで定義された主キーがある場合、そのフィールドの同じ値を持つ2つのレコードを持つことはできません。

例

上記のさまざまな例で、IDを主キーとして使用してCOMPANYテーブルを作成しました。

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

チェック制約

CHECK制約を使用すると、レコードに入力される値を条件で確認できます。条件が偽と評価された場合、レコードは制約に違反しており、テーブルに入力されません。

例

たとえば、次のSQLiteはCOMPANYという新しいテーブルを作成し、5つの列を追加します。ここでは、SALARYゼロを持つことはできないように、SALARY列でCHECKを追加します。

CREATE TABLE COMPANY3(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL    CHECK(SALARY > 0)
);

制約の削除

SQLiteは、ALTER TABLEの限定されたサブセットをサポートします。 SQLiteのALTER TABLEコマンドを使用すると、ユーザーはテーブルの名前を変更したり、既存のテーブルに新しい列を追加したりできます。列の名前を変更したり、列を削除したり、表に制約を追加または削除することはできません。

SQLite-結合

SQLite Joins 句は、データベース内の2つ以上のテーブルのレコードを結合するために使用されます。 JOINは、それぞれに共通の値を使用して2つのテーブルのフィールドを結合するための手段です。

SQLは結合の3つの主要なタイプを定義します-

クロスジョイン
インナージョイン *アウタージョイン

先に進む前に、2つのテーブルCOMPANYとDEPARTMENTについて考えてみましょう。 COMPANYテーブルにデータを挿入するINSERTステートメントを見てきました。 COMPANYテーブルで利用可能なレコードのリストを想定してみましょう-

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

別のテーブルは、次の定義を持つDEPARTMENTです-

CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

DEPARTMENTテーブルに入力するINSERTステートメントのリストは次のとおりです-

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (1, 'IT Billing', 1 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (2, 'Engineering', 2 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (3, 'Finance', 7 );

最後に、DEPARTMENTテーブルで利用可能なレコードの次のリストがあります-

ID          DEPT        EMP_ID
----------  ----------  ----------
1           IT Billing  1
2           Engineering 2
3           Finance     7

クロスジョイン

CROSS JOINは、最初のテーブルのすべての行と2番目のテーブルのすべての行を一致させます。入力テーブルにそれぞれx行とy行がある場合、結果のテーブルにはx* y行があります。 CROSS JOINは非常に大きなテーブルを生成する可能性があるため、適切な場合にのみ使用するように注意する必要があります。

CROSS JOINの構文は次のとおりです-

SELECT ... FROM table1 CROSS JOIN table2 ...

上記の表に基づいて、次のようにCROSS JOINを書くことができます-

sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;

上記のクエリは、次の結果を生成します-

EMP_ID      NAME        DEPT
----------  ----------  ----------
1           Paul        IT Billing
2           Paul        Engineering
7           Paul        Finance
1           Allen       IT Billing
2           Allen       Engineering
7           Allen       Finance
1           Teddy       IT Billing
2           Teddy       Engineering
7           Teddy       Finance
1           Mark        IT Billing
2           Mark        Engineering
7           Mark        Finance
1           David       IT Billing
2           David       Engineering
7           David       Finance
1           Kim         IT Billing
2           Kim         Engineering
7           Kim         Finance
1           James       IT Billing
2           James       Engineering
7           James       Finance

インナージョイン

INNER JOINは、結合述語に基づいて2つのテーブル（table1とtable2）の列値を組み合わせて、新しい結果テーブルを作成します。クエリは、table1の各行とtable2の各行を比較して、結合述語を満たす行のすべてのペアを見つけます。 join-predicateが満たされると、AとBの行の各一致ペアの列値が結果行に結合されます。

INNER JOINは、最も一般的なデフォルトの結合タイプです。オプションでINNERキーワードを使用できます。

以下はINNER JOINの構文です-

SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...

冗長性を避けてフレージングを短くするために、 USING 式でINNER JOIN条件を宣言できます。この式は、1つ以上の列のリストを指定します。

SELECT ... FROM table1 JOIN table2 USING ( column1 ,... ) ...

NATURAL JOINは JOIN … USING に似ていますが、両方のテーブルに存在するすべての列の値が等しいかどうかを自動的にテストするだけです-

SELECT ... FROM table1 NATURAL JOIN table2...

上記の表に基づいて、次のようにINNER JOINを書くことができます-

sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID;

上記のクエリは、次の結果を生成します-

EMP_ID      NAME        DEPT
----------  ----------  ----------
1           Paul        IT Billing
2           Allen       Engineering
7           James       Finance

アウタージョイン

OUTER JOINはINNER JOINの拡張です。 SQL標準では、LEFT、RIGHT、FULLの3種類のOUTER JOINが定義されていますが、SQLiteは LEFT OUTER JOIN のみをサポートしています。

OUTER JOINには、ON、USING、またはNATURALキーワードを使用して表されるINNER JOINと同じ条件があります。初期結果テーブルも同じ方法で計算されます。プライマリJOINが計算されると、OUTER JOINは一方または両方のテーブルから結合されていない行を取得し、NULLで埋めて、結果のテーブルに追加します。

LEFT OUTER JOINの構文は次のとおりです-

SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...

冗長性を避け、フレージングを短くするために、USING式を使用してOUTER JOIN条件を宣言できます。この式は、1つ以上の列のリストを指定します。

SELECT ... FROM table1 LEFT OUTER JOIN table2 USING ( column1 ,... ) ...

上記の表に基づいて、次のように内部結合を記述できます-

sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID;

上記のクエリは、次の結果を生成します-

EMP_ID      NAME        DEPT
----------  ----------  ----------
1           Paul        IT Billing
2           Allen       Engineering
            Teddy
            Mark
            David
            Kim
7           James       Finance

SQLite-UNION句

SQLite UNION 句/演算子は、重複行を返さずに2つ以上のSELECTステートメントの結果を結合するために使用されます。

UNIONを使用するには、各SELECTで選択した列の数、列式の数、データ型が同じで、順序が同じである必要がありますが、同じ長さである必要はありません。

構文

以下は UNION の基本的な構文です。

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

ここで、特定の条件は、要件に基づいて任意の特定の式にすることができます。

例

次の2つのテーブル、（a）link：/sqlite/company.sql [COMPANY]テーブルを次のように検討してください-

sqlite> select * from COMPANY;
ID          NAME                  AGE         ADDRESS     SALARY
----------  --------------------  ----------  ----------  ----------
1           Paul                  32          California  20000.0
2           Allen                 25          Texas       15000.0
3           Teddy                 23          Norway      20000.0
4           Mark                  25          Rich-Mond   65000.0
5           David                 27          Texas       85000.0
6           Kim                   22          South-Hall  45000.0
7           James                 24          Houston     10000.0

（b）別のテーブルはlink：/sqlite/department.sql [DEPARTMENT]です。

ID          DEPT                  EMP_ID
----------  --------------------  ----------
1           IT Billing            1
2           Engineering           2
3           Finance               7
4           Engineering           3
5           Finance               4
6           Engineering           5
7           Finance               6

次のように、UNION句とともにSELECTステートメントを使用してこれらの2つのテーブルを結合しましょう-

sqlite>  SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
         ON COMPANY.ID = DEPARTMENT.EMP_ID

         UNION

         SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
         ON COMPANY.ID = DEPARTMENT.EMP_ID;

これにより、次の結果が生成されます。

EMP_ID      NAME                  DEPT
----------  --------------------  ----------
1           Paul                  IT Billing
2           Allen                 Engineering
3           Teddy                 Engineering
4           Mark                  Finance
5           David                 Engineering
6           Kim                   Finance
7           James                 Finance

UNION ALL句

UNION ALL演算子は、重複行を含む2つのSELECTステートメントの結果を結合するために使用されます。

UNIONに適用される同じ規則は、UNION ALL演算子にも適用されます。

構文

*UNION ALL* の基本構文は次のとおりです。

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION ALL

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

ここで、特定の条件は、要件に基づいて任意の特定の式にすることができます。

例

さて、次のようにSELECTステートメントで上記の2つのテーブルを結合しましょう-

sqlite>  SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
         ON COMPANY.ID = DEPARTMENT.EMP_ID

         UNION ALL

         SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
         ON COMPANY.ID = DEPARTMENT.EMP_ID;

これにより、次の結果が生成されます。

EMP_ID      NAME                  DEPT
----------  --------------------  ----------
1           Paul                  IT Billing
2           Allen                 Engineering
3           Teddy                 Engineering
4           Mark                  Finance
5           David                 Engineering
6           Kim                   Finance
7           James                 Finance
1           Paul                  IT Billing
2           Allen                 Engineering
3           Teddy                 Engineering
4           Mark                  Finance
5           David                 Engineering
6           Kim                   Finance
7           James                 Finance

SQLite-NULL値

SQLite NULL は、欠損値を表すために使用される用語です。テーブルのNULL値は、空白のように見えるフィールドの値です。

NULL値を持つフィールドは、値のないフィールドです。 NULL値は、ゼロ値やスペースを含むフィールドとは異なることを理解することが非常に重要です。

構文

以下は、テーブルを作成するときに NULL を使用する基本的な構文です。

SQLite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

ここで、 NOT NULL は、列が特定のデータ型の明示的な値を常に受け入れることを意味します。 NOT NULLを使用しなかった2つの列があります。これは、これらの列がNULLになる可能性があることを意味します。

NULL値を持つフィールドは、レコードの作成中に空白のままになっているフィールドです。

例

NULL値は、データを選択するときに問題を引き起こす可能性があります。未知の値を他の値と比較すると、結果は常に未知であり、最終結果に含まれないためです。次の表、link：/sqlite/company.sql [COMPANY]を次のレコードと一緒に考えてください-

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

私たちは次のようにいくつかのNULL値をNULLとして設定するUPDATEステートメントを使用してみましょう-

sqlite> UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN(6,7);

これで、COMPANY表には次のレコードが含まれます。

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22
7           James       24

次に、 IS NOT NULL 演算子を使用して、SALARYがNULLでないすべてのレコードをリスト表示します。

sqlite> SELECT  ID, NAME, AGE, ADDRESS, SALARY
        FROM COMPANY
        WHERE SALARY IS NOT NULL;

上記のSQLiteステートメントは、次の結果を生成します-

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

次に、 IS NULL 演算子の使用法を示します。これは、SALARYがNULLであるすべてのレコードをリストします。

sqlite> SELECT  ID, NAME, AGE, ADDRESS, SALARY
        FROM COMPANY
        WHERE SALARY IS NULL;

上記のSQLiteステートメントは、次の結果を生成します。

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
6           Kim         22
7           James       24

SQLite-ALIAS構文

*ALIAS* と呼ばれる別の名前を付けることで、一時的にテーブルまたは列の名前を変更できます。 テーブルエイリアスの使用とは、特定のSQLiteステートメントのテーブルの名前を変更することを意味します。 名前の変更は一時的な変更であり、データベース内の実際のテーブル名は変更されません。

列エイリアスは、特定のSQLiteクエリの目的でテーブルの列の名前を変更するために使用されます。

構文

以下は table エイリアスの基本的な構文です。

SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];

以下は column エイリアスの基本的な構文です。

SELECT column_name AS alias_name
FROM table_name
WHERE [condition];

例

次の2つのテーブルを検討してください、（a）リンク：/sqlite/company.sql [COMPANY]テーブルは次のとおりです-

sqlite> select * from COMPANY;
ID          NAME                  AGE         ADDRESS     SALARY
----------  --------------------  ----------  ----------  ----------
1           Paul                  32          California  20000.0
2           Allen                 25          Texas       15000.0
3           Teddy                 23          Norway      20000.0
4           Mark                  25          Rich-Mond   65000.0
5           David                 27          Texas       85000.0
6           Kim                   22          South-Hall  45000.0
7           James                 24          Houston     10000.0

（b）別のテーブルはlink：/sqlite/department.sql [DEPARTMENT]です。

ID          DEPT                  EMP_ID
----------  --------------------  ----------
1           IT Billing            1
2           Engineering           2
3           Finance               7
4           Engineering           3
5           Finance               4
6           Engineering           5
7           Finance               6

さて、以下は TABLE ALIAS の使用法です。CおよびDをそれぞれCOMPANYおよびDEPARTMENTテーブルのエイリアスとして使用します-

sqlite> SELECT C.ID, C.NAME, C.AGE, D.DEPT
        FROM COMPANY AS C, DEPARTMENT AS D
        WHERE  C.ID = D.EMP_ID;

上記のSQLiteステートメントは、次の結果を生成します-

ID          NAME        AGE         DEPT
----------  ----------  ----------  ----------
1           Paul        32          IT Billing
2           Allen       25          Engineering
3           Teddy       23          Engineering
4           Mark        25          Finance
5           David       27          Engineering
6           Kim         22          Finance
7           James       24          Finance

COMPANY_IDがID列のエイリアスであり、COMPANY_NAMEが名前列のエイリアスである COLUMN ALIAS の使用例を検討してください。

sqlite> SELECT C.ID AS COMPANY_ID, C.NAME AS COMPANY_NAME, C.AGE, D.DEPT
        FROM COMPANY AS C, DEPARTMENT AS D
        WHERE  C.ID = D.EMP_ID;

上記のSQLiteステートメントは、次の結果を生成します-

COMPANY_ID  COMPANY_NAME  AGE         DEPT
----------  ------------  ----------  ----------
1           Paul          32          IT Billing
2           Allen         25          Engineering
3           Teddy         23          Engineering
4           Mark          25          Finance
5           David         27          Engineering
6           Kim           22          Finance
7           James         24          Finance

SQLite-トリガー

SQLite *トリガー*はデータベースコールバック関数で、指定されたデータベースイベントが発生すると自動的に実行/呼び出されます。以下は、SQLiteトリガーに関する重要なポイントです-

SQLiteトリガーは、特定のデータベーステーブルのDELETE、INSERT、またはUPDATEが発生したとき、またはテーブルの1つ以上の指定された列でUPDATEが発生したときに起動するように指定できます。
現時点では、SQLiteはFOR EACH STATEMENTトリガーではなく、FOR EACH ROWトリガーのみをサポートしています。したがって、FOR EACH ROWを明示的に指定することはオプションです。
WHEN句とトリガーアクションの両方は、 NEW.column-name および OLD.column-name の形式の参照を使用して、挿入、削除、または更新される行の要素にアクセスできます。column-nameはトリガーが関連付けられているテーブルの列。
WHEN句が指定されている場合、指定されたSQLステートメントは、WHEN句がtrueである行に対してのみ実行されます。 WHEN句が指定されていない場合、SQLステートメントはすべての行に対して実行されます。
BEFOREまたはAFTERキーワードは、関連付けられた行の挿入、変更、または削除に関連してトリガーアクションがいつ実行されるかを決定します。
トリガーは、関連付けられているテーブルが削除されると自動的に削除されます。
変更するテーブルは、トリガーがアタッチされるテーブルまたはビューと同じデータベースに存在する必要があり、 database.tablename ではなく tablename のみを使用する必要があります。
特別なSQL関数RAISE（）をトリガープログラム内で使用して、例外を発生させることができます。

構文

以下は、*トリガー*を作成する基本的な構文です。

CREATE TRIGGER trigger_name [BEFORE|AFTER] event_name
ON table_name
BEGIN
 -- Trigger logic goes here....
END;

ここで、 event_name は、前述の表 table_name に対する_INSERT、DELETE、_、および_UPDATE_データベース操作です。オプションで、テーブル名の後にFOR EACH ROWを指定できます。

以下は、テーブルの1つ以上の指定された列のUPDATE操作でトリガーを作成するための構文です。

CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
BEGIN
   -- Trigger logic goes here....
END;

例

COMPANYテーブルに挿入されるすべてのレコードの監査トライアルを保持したい場合を考えてみましょう。次のように新しく作成します（既にある場合はCOMPANYテーブルを削除します）。

sqlite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

監査トライアルを続けるために、新しいレコードのCOMPANYテーブルにエントリがあるたびに、ログメッセージが挿入されるAUDITという新しいテーブルを作成します。

sqlite> CREATE TABLE AUDIT(
   EMP_ID INT NOT NULL,
   ENTRY_DATE TEXT NOT NULL
);

ここで、IDはAUDITレコードID、EMP_IDはCOMPANYテーブルから取得されるIDであり、DATEはレコードがCOMPANYテーブルに作成されるときにタイムスタンプを保持します。次のようにCOMPANYテーブルにトリガーを作成しましょう-

sqlite> CREATE TRIGGER audit_log AFTER INSERT
ON COMPANY
BEGIN
   INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));
END;

ここで、実際の作業を開始します。AUDITテーブルに監査ログレコードを作成する必要があるCOMPANYテーブルにレコードを挿入しましょう。次のようにCOMPANYテーブルに1つのレコードを作成します-

sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );

これにより、次のようなCOMPANYテーブルに1つのレコードが作成されます-

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0

同時に、AUDITテーブルに1つのレコードが作成されます。このレコードは、COMPANYテーブルのINSERT操作で作成したトリガーの結果です。同様に、要件に基づいてUPDATEおよびDELETE操作でトリガーを作成できます。

EMP_ID      ENTRY_DATE
----------  -------------------
1           2013-04-05 06:26:00

トリガーのリスト

次のように sqlite_master テーブルからすべてのトリガーをリストダウンすることができます-

sqlite> SELECT name FROM sqlite_master
WHERE type = 'trigger';

上記のSQLiteステートメントは、次のように1つのエントリのみをリストダウンします-

name
----------
audit_log

あなたが特定のテーブルのトリガーをダウンリストしたい場合は、次のようにテーブル名でAND句を使用します-

sqlite> SELECT name FROM sqlite_master
WHERE type = 'trigger' AND tbl_name = 'COMPANY';

上記のSQLiteステートメントは、次のように1つのエントリのみをリストダウンします-

name
----------
audit_log

トリガーの削除

以下は、既存のトリガーを削除するために使用できるDROPコマンドです。

sqlite> DROP TRIGGER trigger_name;

SQLite-インデックス

インデックスは、データ検索を高速化するためにデータベース検索エンジンが使用できる特別なルックアップテーブルです。簡単に言えば、*インデックス*はテーブル内のデータへのポインタです。データベースのインデックスは、本の裏にあるインデックスに非常によく似ています。

たとえば、特定のトピックについて説明している本のすべてのページを参照する場合は、最初にインデックスを参照します。インデックスはすべてのトピックをアルファベット順にリストし、1つ以上の特定のページ番号を参照します。

インデックスは、SELECTクエリとWHERE句を高速化するのに役立ちますが、UPDATEおよびINSERTステートメントを使用すると、データ入力が遅くなります。インデックスは、データに影響を与えずに作成または削除できます。

インデックスの作成には、CREATE INDEXステートメントが含まれます。これにより、インデックスに名前を付け、テーブルとインデックスを作成する列を指定し、インデックスが昇順か降順かを示すことができます。

インデックスは、一意の制約と同様に一意にすることもできます。インデックスは、インデックスが存在する列または列の組み合わせの重複エントリを防止します。

CREATE INDEXコマンド

以下は CREATE INDEX の基本的な構文です。

CREATE INDEX index_name ON table_name;

単一列インデックス

単一列インデックスは、1つのテーブル列のみに基づいて作成されるインデックスです。基本的な構文は次のとおりです-

CREATE INDEX index_name
ON table_name (column_name);

一意のインデックス

一意のインデックスは、パフォーマンスだけでなく、データの整合性にも使用されます。一意のインデックスでは、重複する値をテーブルに挿入できません。基本的な構文は次のとおりです-

CREATE UNIQUE INDEX index_name
on table_name (column_name);

複合インデックス

複合インデックスは、テーブルの2つ以上の列のインデックスです。基本的な構文は次のとおりです-

CREATE INDEX index_name
on table_name (column1, column2);

単一列インデックスを作成するか、複合インデックスを作成するかは、クエリのWHERE句でフィルター条件として非常に頻繁に使用する可能性のある列を考慮してください。

列を1つだけ使用する場合は、単一列のインデックスを選択する必要があります。 WHERE句でフィルターとして頻繁に使用される2つ以上の列がある場合、複合インデックスが最適な選択になります。

暗黙的なインデックス

暗黙的なインデックスは、オブジェクトの作成時にデータベースサーバーによって自動的に作成されるインデックスです。主キー制約と一意制約のインデックスが自動的に作成されます。

例

以下は、給与列のlink：/sqlite/company.sql [COMPANY]テーブルにインデックスを作成する例です-

sqlite> CREATE INDEX salary_index ON COMPANY (salary);

さて、次のように .indices コマンドを使用して、COMPANYテーブルで利用可能なすべてのインデックスをリストダウンしましょう-

sqlite> .indices COMPANY

これにより、次の結果が生成されます。_sqlite_autoindex_COMPANY_1_は、テーブル自体の作成時に作成された暗黙的なインデックスです。

salary_index
sqlite_autoindex_COMPANY_1

次のように、データベース全体のすべてのインデックスを一覧表示できます-

sqlite> SELECT *FROM sqlite_master WHERE type = 'index';

DROP INDEXコマンド

インデックスは、SQLite* DROP *コマンドを使用して削除できます。パフォーマンスが低下または改善される可能性があるため、インデックスを削除するときは注意が必要です。

基本的な構文は次のとおりです-

DROP INDEX index_name;

次のステートメントを使用して、以前に作成したインデックスを削除できます。

sqlite> DROP INDEX salary_index;

インデックスを回避する必要がある場合

インデックスはデータベースのパフォーマンスを向上させることを目的としていますが、避ける必要がある場合があります。次のガイドラインは、インデックスの使用をいつ再検討する必要があるかを示しています。

でインデックスを使用しないでください-

小さなテーブル。
頻繁に大規模なバッチ更新または挿入操作が行われるテーブル。
多数のNULL値を含む列。
頻繁に操作される列。

SQLite-INDEXED BY句

「INDEXED BY index-name」句は、名前付きインデックスを使用して、前のテーブルの値を検索する必要があることを指定します。

index-nameが存在しないか、クエリに使用できない場合、SQLiteステートメントの準備は失敗します。

「NOT INDEXED」句は、UNIQUEおよびPRIMARY KEY制約によって作成された暗黙のインデックスを含め、前のテーブルにアクセスするときにインデックスを使用しないことを指定します。

ただし、「NOT INDEXED」が指定されている場合でも、INTEGER PRIMARY KEYを使用してエントリを検索できます。

構文

INDEXED BY句の構文は次のとおりで、DELETE、UPDATE、またはSELECTステートメントで使用できます。

SELECT|DELETE|UPDATE column1, column2...
INDEXED BY (index_name)
table_name
WHERE (CONDITION);

例

テーブルlink：/sqlite/company.sql [COMPANY]を検討してください。インデックスを作成し、それをINDEXED BY操作の実行に使用します。

sqlite> CREATE INDEX salary_index ON COMPANY(salary);
sqlite>

ここで、テーブルCOMPANYからデータを選択すると、次のようにINDEXED BY句を使用できます-

sqlite> SELECT * FROM COMPANY INDEXED BY salary_index WHERE salary > 5000;

これにより、次の結果が生成されます。

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
7           James       24          Houston     10000.0
2           Allen       25          Texas       15000.0
1           Paul        32          California  20000.0
3           Teddy       23          Norway      20000.0
6           Kim         22          South-Hall  45000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

SQLite-ALTER TABLEコマンド

SQLiteの ALTER TABLE コマンドは、データの完全なダンプとリロードを実行せずに既存のテーブルを変更します。 ALTER TABLEステートメントを使用してテーブルの名前を変更でき、ALTER TABLEステートメントを使用して既存のテーブルに追加の列を追加できます。

テーブルの名前を変更し、既存のテーブルに列を追加することを除いて、SQLiteのALTER TABLEコマンドでサポートされる他の操作はありません。

構文

以下は、既存のテーブルの名前を変更するための ALTER TABLE の基本的な構文です。

ALTER TABLE database_name.table_name RENAME TO new_table_name;

以下は、既存のテーブルに新しい列を追加するための ALTER TABLE の基本的な構文です。

ALTER TABLE database_name.table_name ADD COLUMN column_def...;

例

次のレコードを含むlink：/sqlite/company.sql [COMPANY]テーブルを考慮してください-

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

さて、次のようにALTER TABLEステートメントを使用してこのテーブルの名前を変更してみましょう-

sqlite> ALTER TABLE COMPANY RENAME TO OLD_COMPANY;

上記のSQLiteステートメントは、COMPANYテーブルの名前をOLD_COMPANYに変更します。さて、次のようにOLD_COMPANYテーブルに新しい列を追加してみましょう-

sqlite> ALTER TABLE OLD_COMPANY ADD COLUMN SEX char(1);

COMPANYテーブルが変更され、SELECTステートメントからの出力が次のようになります。

ID          NAME        AGE         ADDRESS     SALARY      SEX
----------  ----------  ----------  ----------  ----------  ---
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

新しく追加された列にはNULL値が入力されることに注意してください。

SQLite-TRUNCATE TABLEコマンド

残念ながら、SQLiteにはTRUNCATE TABLEコマンドはありませんが、SQLite DELETE コマンドを使用して既存のテーブルから完全なデータを削除できますが、DROP TABLEコマンドを使用して完全なテーブルを削除し、もう一度作成することをお勧めします。

構文

DELETEコマンドの基本的な構文は次のとおりです。

sqlite> DELETE FROM table_name;

DROP TABLEの基本的な構文は次のとおりです。

sqlite> DROP TABLE table_name;

DELETE TABLEコマンドを使用してすべてのレコードを削除する場合は、 VACUUM コマンドを使用して未使用のスペースをクリアすることをお勧めします。

例

次のレコードを持つlink：/sqlite/company.sql [COMPANY]テーブルを検討してください。

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

以下は、上記の表を切り捨てる例です-

SQLite> DELETE FROM COMPANY;
SQLite> VACUUM;

これで、COMPANYテーブルは完全に切り捨てられ、SELECTステートメントからの出力はなくなります。

SQLite-ビュー

ビューは、関連する名前でデータベースに保存されるSQLiteステートメントに過ぎません。実際には、事前定義されたSQLiteクエリの形式のテーブルの構成です。

ビューには、テーブルのすべての行、または1つ以上のテーブルから選択した行を含めることができます。ビューは、作成されたSQLiteクエリに依存して1つまたは複数のテーブルから作成でき、ビューを作成します。

仮想テーブルの一種であるビューは、ユーザーがすることができます-

ユーザーまたはユーザーのクラスが自然または直感的に見えるようにデータを構造化します。
ユーザーが完全なテーブルではなく、限られたデータのみを表示できるように、データへのアクセスを制限します。
レポートの生成に使用できるさまざまなテーブルのデータを要約します。

SQLiteビューは読み取り専用であるため、ビューでDELETE、INSERT、またはUPDATEステートメントを実行できない場合があります。ただし、ビューをDELETE、INSERT、またはUPDATEしようとして起動するビューでトリガーを作成し、トリガーの本体で必要なことを行うことができます。

ビューを作成する

SQLiteビューは、 CREATE VIEW ステートメントを使用して作成されます。 SQLiteビューは、単一のテーブル、複数のテーブル、または別のビューから作成できます。

基本的なCREATE VIEW構文は次のとおりです。

CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

通常のSQL SELECTクエリで使用するのと同様の方法で、SELECTステートメントに複数のテーブルを含めることができます。オプションのTEMPまたはTEMPORARYキーワードが存在する場合、ビューは一時データベースに作成されます。

例

次のレコードを持つlink：/sqlite/company.sql [COMPANY]テーブルを検討してください-

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

COMPANYテーブルからビューを作成する例を次に示します。このビューは、COMPANYテーブルのいくつかの列のみを持つために使用されます。

sqlite> CREATE VIEW COMPANY_VIEW AS
SELECT ID, NAME, AGE
FROM  COMPANY;

これで、実際のテーブルを照会するのと同様の方法でCOMPANY_VIEWを照会できます。以下は例です-

sqlite> SELECT * FROM COMPANY_VIEW;

これにより、次の結果が生成されます。

ID          NAME        AGE
----------  ----------  ----------
1           Paul        32
2           Allen       25
3           Teddy       23
4           Mark        25
5           David       27
6           Kim         22
7           James       24

ビューの削除

ビューを削除するには、 view_name を指定してDROP VIEWステートメントを使用します。基本的なDROP VIEW構文は次のとおりです-

sqlite> DROP VIEW view_name;

次のコマンドは、前のセクションで作成したCOMPANY_VIEWビューを削除します。

sqlite> DROP VIEW COMPANY_VIEW;

SQLite-トランザクション

トランザクションは、データベースに対して実行される作業単位です。トランザクションとは、ユーザーが手動で実行する場合でも、何らかのデータベースプログラムで自動的に実行する場合でも、論理的な順序で実行される作業の単位またはシーケンスです。

トランザクションは、データベースに対する1つ以上の変更の伝播です。たとえば、テーブルからレコードを作成、更新、または削除する場合、テーブルでトランザクションを実行します。データの整合性を確保し、データベースエラーを処理するには、トランザクションを制御することが重要です。

実際には、多くのSQLiteクエリを1つのグループにまとめ、トランザクションの一部としてそれらすべてをまとめて実行します。

トランザクションのプロパティ

トランザクションには、次の4つの標準プロパティがあり、通常、頭字語ACIDで参照されます。

Atomicity -ワークユニット内のすべての操作が正常に完了することを保証します。そうでない場合、トランザクションは失敗した時点で中止され、以前の操作は以前の状態にロールバックされます。
一貫性-トランザクションが正常にコミットされると、データベースの状態が適切に変更されるようにします。
分離-トランザクションが相互に独立して透過的に動作できるようにします。
耐久性-システム障害の場合に、コミットされたトランザクションの結果または効果が持続することを保証します。

トランザクション制御

以下は、トランザクションを制御するために使用される次のコマンドです。

BEGIN TRANSACTION -トランザクションを開始します。
COMMIT -変更を保存するには、代わりに END TRANSACTION コマンドを使用できます。
ROLLBACK -変更をロールバックします。

トランザクション制御コマンドは、DMLコマンドのINSERT、UPDATE、およびDELETEでのみ使用されます。これらの操作はデータベースで自動的にコミットされるため、テーブルの作成中または削除中は使用できません。

BEGIN TRANSACTIONコマンド

トランザクションは、BEGIN TRANSACTIONまたは単にBEGINコマンドを使用して開始できます。このようなトランザクションは通常、次のCOMMITまたはROLLBACKコマンドが検出されるまで持続します。ただし、データベースが閉じている場合やエラーが発生した場合も、トランザクションはROLLBACKします。以下は、トランザクションを開始する簡単な構文です。

BEGIN;
or
BEGIN TRANSACTION;

COMMITコマンド

COMMITコマンドは、トランザクションによって呼び出された変更をデータベースに保存するために使用されるトランザクションコマンドです。

COMMITコマンドは、最後のCOMMITまたはROLLBACKコマンド以降のすべてのトランザクションをデータベースに保存します。

COMMITコマンドの構文は次のとおりです。

COMMIT;
or
END TRANSACTION;

ROLLBACKコマンド

ROLLBACKコマンドは、データベースにまだ保存されていないトランザクションを元に戻すために使用されるトランザクションコマンドです。

ROLLBACKコマンドは、最後のCOMMITまたはROLLBACKコマンドが発行されてからトランザクションを元に戻すためにのみ使用できます。

ROLLBACKコマンドの構文は次のとおりです。

ROLLBACK;

例

次のレコードを持つlink：/sqlite/company.sql [COMPANY]テーブルを検討してください。

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

では、トランザクションを開始して、年齢= 25のレコードをテーブルから削除しましょう。次に、ROLLBACKコマンドを使用して、すべての変更を元に戻します。

sqlite> BEGIN;
sqlite> DELETE FROM COMPANY WHERE AGE = 25;
sqlite> ROLLBACK;

さて、COMPANY表を確認すると、まだ以下のレコードがあります-

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

別のトランザクションを開始して、年齢= 25のテーブルからレコードを削除し、最後にCOMMITコマンドを使用してすべての変更をコミットします。

sqlite> BEGIN;
sqlite> DELETE FROM COMPANY WHERE AGE = 25;
sqlite> COMMIT;

あなたが今会社表を確認すると、まだ次のレコードがあります-

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
3           Teddy       23          Norway      20000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

SQLite-サブクエリ

サブクエリまたは内部クエリまたはネストされたクエリは、別のSQLiteクエリ内のクエリであり、WHERE句内に埋め込まれます。

サブクエリは、取得するデータをさらに制限する条件としてメインクエリで使用されるデータを返すために使用されます。

サブクエリは、=、<、>、> =、⇐、IN、BETWEENなどの演算子とともにSELECT、INSERT、UPDATE、およびDELETEステートメントで使用できます。

サブクエリが従わなければならないいくつかのルールがあります-

サブクエリは括弧で囲む必要があります。
サブクエリは、選択された列を比較するサブクエリのメインクエリに複数の列がある場合を除き、SELECT句に1つの列しか含めることができません。
メインクエリはORDER BYを使用できますが、ORDER BYはサブクエリでは使用できません。 GROUP BYを使用して、サブクエリのORDER BYと同じ機能を実行できます。
複数の行を返すサブクエリは、IN演算子などの複数の値演算子でのみ使用できます。 *BETWEEN演算子はサブクエリでは使用できません。ただし、BETWEENはサブクエリ内で使用できます。

SELECTステートメントを使用したサブクエリ

サブクエリは、SELECTステートメントで最も頻繁に使用されます。基本的な構文は次のとおりです-

SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
   (SELECT column_name [, column_name ]
      FROM table1 [, table2 ]
      [WHERE])

例

次のレコードを持つlink：/sqlite/company.sql [COMPANY]テーブルを検討してください。

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

次に、SELECTステートメントを使用して次のサブクエリを確認します。

sqlite> SELECT*
   FROM COMPANY
   WHERE ID IN (SELECT ID
      FROM COMPANY
      WHERE SALARY > 45000) ;

これにより、次の結果が生成されます。

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

INSERTステートメントを使用したサブクエリ

サブクエリは、INSERTステートメントでも使用できます。 INSERTステートメントは、サブクエリから返されたデータを使用して別のテーブルに挿入します。サブクエリで選択したデータは、任意の文字、日付、または数字関数で変更できます。

基本的な構文は次のとおりです-

INSERT INTO table_name [ (column1 [, column2 ]) ]
   SELECT [ *|column1 [, column2 ]
   FROM table1 [, table2 ]
   [ WHERE VALUE OPERATOR ]

例

COMPANYテーブルと同様の構造を持つテーブルCOMPANY_BKPを考えてみましょう。COMPANY_BKPをテーブル名として使用して、同じCREATE TABLEを使用して作成できます。 COMPANYテーブル全体をCOMPANY_BKPにコピーするには、次の構文を使用します-

sqlite> INSERT INTO COMPANY_BKP
   SELECT *FROM COMPANY
   WHERE ID IN (SELECT ID
      FROM COMPANY) ;

UPDATEステートメントを使用したサブクエリ

サブクエリは、UPDATEステートメントと組み合わせて使用できます。 UPDATEステートメントでサブクエリを使用すると、テーブル内の単一または複数の列を更新できます。

基本的な構文は次のとおりです-

UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
      FROM TABLE_NAME)
   [ WHERE) ]

例

COMPANYテーブルのバックアップであるCOMPANY_BKPテーブルがあると仮定します。

次の例では、AGEが27以上のすべての顧客のCOMPANYテーブルでSALARYを0.50回更新します。

sqlite> UPDATE COMPANY
   SET SALARY = SALARY* 0.50
   WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
      WHERE AGE >= 27 );

これは2つの行に影響し、最後にCOMPANYテーブルには次のレコードが含まれます-

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  10000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       42500.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

DELETEステートメントを使用したサブクエリ

サブクエリは、上記の他のステートメントと同様に、DELETEステートメントと組み合わせて使用できます。

基本的な構文は次のとおりです-

DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
      FROM TABLE_NAME)
   [ WHERE) ]

例

COMPANYテーブルのバックアップであるCOMPANY_BKPテーブルがあると仮定します。

次の例では、AGEが27以上のすべての顧客のCOMPANYテーブルからレコードを削除します。

sqlite> DELETE FROM COMPANY
   WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
   WHERE AGE > 27 );

これは2つの行に影響し、最後にCOMPANYテーブルには次のレコードが含まれます-

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       42500.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

SQLite-自動インクリメント

SQLite AUTOINCREMENT は、テーブル内のフィールドの値を自動インクリメントするために使用されるキーワードです。自動インクリメントする特定の列名を持つテーブルを作成するときに AUTOINCREMENT キーワードを使用して、フィールド値を自動インクリメントできます。

キーワード AUTOINCREMENT は、INTEGERフィールドでのみ使用できます。

構文

*AUTOINCREMENT* キーワードの基本的な使用法は次のとおりです-

CREATE TABLE table_name(
   column1 INTEGER AUTOINCREMENT,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);

例

COMPANYテーブルが次のように作成されることを考慮してください-

sqlite> CREATE TABLE COMPANY(
   ID INTEGER PRIMARY KEY AUTOINCREMENT,
   NAME           TEXT      NOT NULL,
   AGE            INT       NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

ここで、次のレコードをテーブルCOMPANYに挿入します-

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Paul', 32, 'California', 20000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ('Allen', 25, 'Texas', 15000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ('Teddy', 23, 'Norway', 20000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Mark', 25, 'Rich-Mond ', 65000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'David', 27, 'Texas', 85000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Kim', 22, 'South-Hall', 45000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'James', 24, 'Houston', 10000.00 );

これは7つのタプルをテーブルCOMPANYに挿入し、COMPANYは次のレコードを持ちます-

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

SQLite-インジェクション

Webページからユーザー入力を取得し、それをSQLiteデータベースに挿入すると、SQLインジェクションと呼ばれるセキュリティ問題が発生する可能性があります。この章では、これを防ぎ、スクリプトとSQLiteステートメントを保護する方法を学びます。

通常、インジェクションは、ユーザーに名前などの入力を求めるときに発生します。名前の代わりに、ユーザーが知らないうちにデータベースで実行するSQLiteステートメントを提供します。

ユーザーが提供したデータを信頼せず、検証後にのみこのデータを処理します。原則として、これはパターンマッチングによって行われます。次の例では、ユーザー名は英数字とアンダースコアに制限され、8〜20文字の長さに制限されています。必要に応じてこれらのルールを変更します。

if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches)){
   $db = new SQLiteDatabase('filename');
   $result = @$db->query("SELECT *FROM users WHERE username = $matches[0]");
} else {
   echo "username not accepted";
}

問題を実証するために、この抜粋を考慮してください-

$name = "Qadir'; DELETE FROM users;";
@$db->query("SELECT* FROM users WHERE username = '{$name}'");

関数呼び出しは、名前列がユーザーによって指定された名前と一致するユーザーテーブルからレコードを取得することになっています。通常の状況では、 $ name には英数字と、おそらく文字列iliaなどのスペースのみが含まれます。ただし、この場合、まったく新しいクエリを$ nameに追加すると、データベースへの呼び出しは災害に変わります。注入されたDELETEクエリは、ユーザーからすべてのレコードを削除します。

単一の関数呼び出しでクエリのスタックや複数のクエリの実行を許可しないデータベースインターフェイスがあります。クエリをスタックしようとすると、呼び出しは失敗しますが、SQLiteとPostgreSQLはスタッククエリを喜んで実行し、1つの文字列で提供されるすべてのクエリを実行し、深刻なセキュリティ問題を引き起こします。

SQLインジェクションの防止

PERLやPHPなどのスクリプト言語では、すべてのエスケープ文字をスマートに処理できます。プログラミング言語PHPは、SQLite専用の入力文字をエスケープする関数* string sqlite_escape_string（）*を提供します。

if (get_magic_quotes_gpc()) {
   $name = sqlite_escape_string($name);
}
$result = @$db->query("SELECT * FROM users WHERE username = '{$name}'");

エンコードによりデータを安全に挿入できますが、クエリ内の単純なテキスト比較と LIKE 句は、バイナリデータを含む列では使用できなくなります。

注意-* addslashes（）*は、SQLiteクエリの文字列を引用するために使用しないでください。データを取得すると、奇妙な結果につながります。

SQLite-説明

SQLiteステートメントの前には、キーワード「EXPLAIN」またはテーブルの詳細を説明するために使用されるフレーズ「EXPLAIN QUERY PLAN」を付けることができます。

いずれかの変更により、SQLiteステートメントはクエリとして動作し、EXPLAINキーワードまたは句が省略された場合のSQLiteステートメントの動作に関する情報を返します。

EXPLAINおよびEXPLAIN QUERY PLANからの出力は、インタラクティブな分析とトラブルシューティングのみを目的としています。
出力形式の詳細は、SQLiteのリリースごとに変更される場合があります。
正確な動作は可変であり、部分的にしか文書化されていないため、アプリケーションではEXPLAINまたはEXPLAIN QUERY PLANを使用しないでください。

構文

*EXPLAIN* の構文は次のとおりです-

EXPLAIN [SQLite Query]

*EXPLAIN QUERY PLAN* の構文は次のとおりです-

EXPLAIN  QUERY PLAN [SQLite Query]

例

次のレコードを持つlink：/sqlite/company.sql [COMPANY]テーブルを検討してください-

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

さて、SELECTステートメントで次のサブクエリを確認しましょう-

sqlite> EXPLAIN SELECT * FROM COMPANY WHERE Salary &gt= 20000;

これにより、次の結果が生成されます。

addr        opcode      p1          p2          p3
----------  ----------  ----------  ----------  ----------
0           Goto        0           19
1           Integer     0           0
2           OpenRead    0           8
3           SetNumColu  0           5
4           Rewind      0           17
5           Column      0           4
6           RealAffini  0           0
7           Integer     20000       0
8           Lt          357         16          collseq(BI
9           Rowid       0           0
10          Column      0           1
11          Column      0           2
12          Column      0           3
13          Column      0           4
14          RealAffini  0           0
15          Callback    5           0
16          Next        0           5
17          Close       0           0
18          Halt        0           0
19          Transactio  0           0
20          VerifyCook  0           38
21          Goto        0           1
22          Noop        0           0

ここで、SELECTステートメントを使用して次の Explain Query Plan を確認します-

SQLite> EXPLAIN QUERY PLAN SELECT * FROM COMPANY WHERE Salary &gt= 20000;

order       from        detail
----------  ----------  -------------
0           0           TABLE COMPANY

SQLite-VACUUM

VACUUMコマンドは、その内容を一時データベースファイルにコピーし、コピーから元のデータベースファイルを再ロードすることにより、メインデータベースをクリーンアップします。これにより、空きページが削除され、テーブルデータが連続するように調整され、データベースファイル構造がクリーンアップされます。

VACUUMコマンドは、明示的なINTEGER PRIMARY KEYを持たないテーブルのエントリのROWIDを変更する場合があります。 VACUUMコマンドはメインデータベースでのみ機能します。添付されたデータベースファイルをVACUUMすることはできません。

アクティブなトランザクションがある場合、VACUUMコマンドは失敗します。 VACUUMコマンドは、メモリ内データベースの操作なしです。 VACUUMコマンドはデータベースファイルを最初から再構築するため、VACUUMを使用して多くのデータベース固有の構成パラメーターを変更することもできます。

手動バキューム

以下は、コマンドプロンプトからデータベース全体に対してVACUUMコマンドを発行する簡単な構文です-

$sqlite3 database_name "VACUUM;"

次のようにSQLiteプロンプトからVACUUMを実行できます-

sqlite> VACUUM;

また、次のように特定のテーブルでVACUUMを実行することができます-

sqlite> VACUUM table_name;

オートバキューム

SQLite Auto-VACUUMはVACUUMと同じではなく、空きページをデータベースの最後に移動するだけで、データベースのサイズを縮小します。そうすることで、VACUUMが最適化を保証しながら、データベースを大幅に断片化できます。したがって、Auto-VACUUMはデータベースを小さく保つだけです。

SQLiteプロンプトで次のプラグマを実行することにより、SQLite自動バキュームを有効/無効にできます-

sqlite> PRAGMA auto_vacuum = NONE; -- 0 means disable auto vacuum
sqlite> PRAGMA auto_vacuum = FULL; -- 1 means enable full auto vacuum
sqlite> PRAGMA auto_vacuum = INCREMENTAL; -- 2 means enable incremental vacuum

コマンドプロンプトから次のコマンドを実行して、自動バキューム設定を確認できます-

$sqlite3 database_name "PRAGMA auto_vacuum;"

SQLite-日付と時刻

SQLiteは次のように5つの日付と時刻の機能をサポートしています-

Sr.No.	Function	Example
1	date(timestring, modifiers…)	This returns the date in this format: YYYY-MM-DD
2	time(timestring, modifiers…)	This returns the time as HH:MM:SS
3	datetime(timestring, modifiers…)	This returns YYYY-MM-DD HH:MM:SS
4	julianday(timestring, modifiers…)	This returns the number of days since noon in Greenwich on November 24, 4714 B.C.
5	strftime(timestring, modifiers…)	This returns the date formatted according to the format string specified as the first argument formatted as per formatters explained below.

上記の5つの日付および時刻関数はすべて、引数として時刻文字列を取ります。時間文字列の後には、ゼロ個以上の修飾子が続きます。 strftime（）関数は、最初の引数としてフォーマット文字列も受け取ります。次のセクションでは、さまざまなタイプの時間文字列と修飾子について詳しく説明します。

タイムストリング

時間文字列は、次の形式のいずれかにすることができます-

Sr.No.	Time String	Example
1	YYYY-MM-DD	2010-12-30
2	YYYY-MM-DD HH:MM	2010-12-30 12:10
3	YYYY-MM-DD HH:MM:SS.SSS	2010-12-30 12:10:04.100
4	MM-DD-YYYY HH:MM	30-12-2010 12:10
5	HH:MM	12:10
6	YYYY-MM-DDTHH:MM	2010-12-30 12:10
7	HH:MM:SS	12:10:01
8	YYYYMMDD HHMMSS	20101230 121001
9	now	2013-05-07

日付と時刻を区切るリテラル文字として「T」を使用できます。

修飾子

時間文字列の後には、上記の5つの関数のいずれかによって返される日付や時刻を変更する0個以上の修飾子を続けることができます。修飾子は左から右に適用されます。

次の修飾子はSQLiteで利用可能です-

NNN日
NNN時間
NNN分
NNN.NNNN秒
NNNか月
NNN年
月の始まり
年の初め
一日の始まり
平日N
ユニセポック
現地時間
utc

フォーマッター

SQLiteは、日付と時刻をフォーマットするための非常に便利な関数* strftime（）*を提供します。次の置換を使用して、日付と時刻をフォーマットできます。

置換

説明

%d

月の日、01-31

%f

小数秒、SS.SSS

%H

時、00-23

%j

年の日、001-366

%J

ユリウス日、DDDD.DDDD

%m

月、00-12

%M

分、00-59

%s

1970-01-01からの秒数

%S

秒、00-59

%w

曜日、0〜6（0は日曜日）

%W

年の週、01-53

%Y

年、YYYY

%%

％記号

例

SQLiteプロンプトを使用して、さまざまな例を試してみましょう。次のコマンドは、現在の日付を計算します。

sqlite> SELECT date('now');
2013-05-07

次のコマンドは、現在の月の最終日を計算します。

sqlite> SELECT date('now','start of month','+1 month','-1 day');
2013-05-31

次のコマンドは、指定されたUNIXタイムスタンプ1092941466の日付と時刻を計算します。

sqlite> SELECT datetime(1092941466, 'unixepoch');
2004-08-19 18:51:06

次のコマンドは、指定されたUNIXタイムスタンプ1092941466の日付と時刻を計算し、ローカルタイムゾーンを補正します。

sqlite> SELECT datetime(1092941466, 'unixepoch', 'localtime');
2004-08-19 13:51:06

次のコマンドは、現在のUNIXタイムスタンプを計算します。

sqlite> SELECT strftime('%s','now');
1393348134

次のコマンドは、米国独立宣言に署名してからの日数を計算します。

sqlite> SELECT julianday('now') - julianday('1776-07-04');
86798.7094695023

次のコマンドは、2004年の特定の瞬間からの秒数を計算します。

sqlite> SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');
295001572

次のコマンドは、現在の年の10月の最初の火曜日の日付を計算します。

sqlite> SELECT date('now','start of year','+9 months','weekday 2');
2013-10-01

次のコマンドは、UNIXエポックからの経過時間を秒単位で計算します（strftime（ '％s'、 'now'）と同様に、小数部を含む）。

sqlite> SELECT (julianday('now') - 2440587.5)*86400.0;
1367926077.12598

日付をフォーマットするときにUTCと現地時間の値を変換するには、次のようにutcまたはlocaltime修飾子を使用します-

sqlite> SELECT time('12:00', 'localtime');
05:00:00

sqlite> SELECT time('12:00', 'utc');
19:00:00

SQLite-便利な関数

SQLiteには、文字列または数値データの処理を実行するための多くの組み込み関数があります。以下はいくつかの便利なSQLite組み込み関数のリストであり、すべて大文字と小文字が区別されません。つまり、これらの関数は小文字、大文字、または混合形式で使用できます。詳細については、SQLiteの公式ドキュメントを確認できます。

Sr.No.	Function & Description
1	SQLite COUNT Function SQLite COUNT集計関数は、データベーステーブルの行数をカウントするために使用されます。
2	SQLite MAX Function SQLite MAX集計関数を使用すると、特定の列の最高（最大）値を選択できます。
3	SQLite MIN Function SQLite MIN集計関数を使用すると、特定の列の最低（最小）値を選択できます。
4	SQLite AVG Function SQLite AVG集計関数は、特定のテーブル列の平均値を選択します。
5	SQLite SUM Function SQLite SUM集計関数を使用すると、数値列の合計を選択できます。
6	SQLite RANDOM Function SQLiteのRANDOM関数は、-9223372036854775808と+9223372036854775807の間の擬似乱数整数を返します。
7	SQLite ABS Function SQLite ABS関数は、数値引数の絶対値を返します。
8	SQLite UPPER Function SQLite UPPER関数は、文字列を大文字に変換します。
9	SQLite LOWER Function SQLite LOWER関数は、文字列を小文字に変換します。
10	SQLite LENGTH Function SQLite LENGTH関数は、文字列の長さを返します。
11	SQLite sqlite_version Function SQLite sqlite_version関数は、SQLiteライブラリのバージョンを返します。

上記の関数の例を提供する前に、次のレコードを持つCOMPANYテーブルを検討してください。

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0