Sqlite-quick-guide
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 |
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が次のいずれかである出力モードを設定します-
|
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.000000sqlite_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 commentSQLiteステートメント
すべての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 |
|---|---|
|
INTEGER |
|
TEXT |
|
NONE |
|
REAL |
|
NUMERIC |
ブールデータ型
SQLiteには、別個のブールストレージクラスがありません。 代わりに、ブール値は整数0（偽）および1（真）として保管されます。
日付と時刻のデータ型
SQLiteには、日付や時刻を保存するための個別のストレージクラスはありませんが、SQLiteは日付と時刻をTEXT、REAL、またはINTEGER値として保存できます。
| Sr.No. | Storage Class & Date Formate |
|---|---|
| 1 |
「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 useSQLite-データベースの取り外し
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.dbSQLite-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.0COMPANYテーブルの選択したフィールドのみを取得する場合は、次のクエリを使用します-
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.0SQLite-数値式
これらの式は、クエリで数学演算を実行するために使用されます。 以下は構文です-
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 = 7SQLite-日付式
日付式は、現在のシステムの日付と時刻の値を返します。 これらの式は、さまざまなデータ操作で使用されます。
sqlite> SELECT CURRENT_TIMESTAMP;
CURRENT_TIMESTAMP = 2013-03-17 10:43:35SQLite-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.0SELECTステートメントに続くと、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.0SQLite-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.0OR演算子
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.0SQLite-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.0COMPANYテーブルのすべての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.0SQLite-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.0COMPANYテーブルからすべてのレコードを削除する場合、次のように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.0SQLite-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 '?2*3' 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.0SQLite-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.0SQLite-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.0SQLite-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....columnNGROUP 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 15000SQLite-HAVING句
HAVING句を使用すると、最終結果に表示されるグループ結果をフィルタリングする条件を指定できます。
WHERE句は選択した列に条件を配置しますが、HAVING句はGROUP BY句によって作成されたグループに条件を配置します。
構文
SELECTクエリでのHAVING句の位置は次のとおりです。
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BYHAVING句は、クエリ内の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 5000SQLite-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
JamesSQLite-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 FinanceSQLite-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 FinanceUNION 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 FinanceSQLite-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 24SQLite-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 FinanceCOMPANY_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 FinanceSQLite-トリガー
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.0SQLite-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.0COMPANYテーブルからビューを作成する例を次に示します。 このビューは、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.0SQLite-サブクエリ
サブクエリまたは内部クエリまたはネストされたクエリは、別の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.0INSERTステートメントを使用したサブクエリ
サブクエリは、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.0DELETEステートメントを使用したサブクエリ
サブクエリは、上記の他のステートメントと同様に、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.0SQLite-自動インクリメント
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.0SQLite-インジェクション
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 >= 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 >= 20000;
order from detail
---------- ---------- -------------
0 0 TABLE COMPANYSQLite-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:00sqlite> SELECT time('12:00', 'utc');
19:00:00SQLite-便利な関数
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.0SQLite COUNT関数
SQLite COUNT集計関数は、データベーステーブルの行数をカウントするために使用されます。 以下は例です-
sqlite> SELECT count(*) FROM COMPANY;上記のSQLite SQLステートメントは以下を生成します。
count(*)
----------
7SQLite MAX関数
SQLite MAX集計関数を使用すると、特定の列の最高（最大）値を選択できます。 以下は例です-
sqlite> SELECT max(salary) FROM COMPANY;上記のSQLite SQLステートメントは以下を生成します。
max(salary)
-----------
85000.0SQLite MIN関数
SQLite MIN集計関数を使用すると、特定の列の最低（最小）値を選択できます。 以下は例です-
sqlite> SELECT min(salary) FROM COMPANY;上記のSQLite SQLステートメントは以下を生成します。
min(salary)
-----------
10000.0SQLite AVG関数
SQLite AVG集計関数は、特定のテーブル列の平均値を選択します。 以下は例です-
sqlite> SELECT avg(salary) FROM COMPANY;上記のSQLite SQLステートメントは以下を生成します。
avg(salary)
----------------
37142.8571428572SQLite SUM関数
SQLite SUM集計関数を使用すると、数値列の合計を選択できます。 以下は例です-
sqlite> SELECT sum(salary) FROM COMPANY;上記のSQLite SQLステートメントは以下を生成します。
sum(salary)
-----------
260000.0SQLiteランダム関数
SQLiteのRANDOM関数は、-9223372036854775808と+9223372036854775807の間の擬似乱数整数を返します。 以下は例です-
sqlite> SELECT random() AS Random;上記のSQLite SQLステートメントは以下を生成します。
Random
-------------------
5876796417670984050SQLite ABS関数
SQLite ABS関数は、数値引数の絶対値を返します。 以下は例です-
sqlite> SELECT abs(5), abs(-15), abs(NULL), abs(0), abs("ABC");上記のSQLite SQLステートメントは以下を生成します。
abs(5) abs(-15) abs(NULL) abs(0) abs("ABC")
---------- ---------- ---------- ---------- ----------
5 15 0 0.0SQLite UPPER関数
SQLite UPPER関数は、文字列を大文字に変換します。 以下は例です-
sqlite> SELECT upper(name) FROM COMPANY;上記のSQLite SQLステートメントは以下を生成します。
upper(name)
-----------
PAUL
ALLEN
TEDDY
MARK
DAVID
KIM
JAMESSQLite LOWER関数
SQLite LOWER関数は、文字列を小文字に変換します。 以下は例です-
sqlite> SELECT lower(name) FROM COMPANY;上記のSQLite SQLステートメントは以下を生成します。
lower(name)
-----------
paul
allen
teddy
mark
david
kim
jamesSQLite LENGTH関数
SQLite LENGTH関数は、文字列の長さを返します。 以下は例です-
sqlite> SELECT name, length(name) FROM COMPANY;上記のSQLite SQLステートメントは以下を生成します。
NAME length(name)
---------- ------------
Paul 4
Allen 5
Teddy 5
Mark 4
David 5
Kim 3
James 5SQLite sqlite_version関数
SQLite sqlite_version関数は、SQLiteライブラリのバージョンを返します。 以下は例です-
sqlite> SELECT sqlite_version() AS 'SQLite Version';上記のSQLite SQLステートメントは以下を生成します。
SQLite Version
--------------
3.6.20SQLite-C/C ++
この章では、C/C ++プログラムでSQLiteを使用する方法を学習します。
インストール
C/C ++プログラムでSQLiteの使用を開始する前に、マシンにSQLiteライブラリがセットアップされていることを確認する必要があります。 SQLiteインストールの章を確認して、インストールプロセスを理解してください。
C/C ++インターフェイスAPI
以下は重要なC/C SQLiteインターフェイスルーチンで、C/C プログラムからSQLiteデータベースを操作するための要件を満たすことができます。 より洗練されたアプリケーションをお探しの場合は、SQLiteの公式ドキュメントをご覧ください。
| Sr.No. | API & Description |
|---|---|
| 1 |
sqlite3_open(const char *filename, sqlite3 **ppDb) このルーチンは、SQLiteデータベースファイルへの接続を開き、他のSQLiteルーチンで使用されるデータベース接続オブジェクトを返します。 _filename_引数がNULLまたは '：memory：'の場合、sqlite3_open（）は、セッションの期間だけ持続するRAM内にメモリ内データベースを作成します。 ファイル名がNULLでない場合、sqlite3_open（）はその値を使用してデータベースファイルを開こうとします。 その名前のファイルが存在しない場合、sqlite3_open（）はその名前の新しいデータベースファイルを開きます。 |
| 2 |
sqlite3_exec(sqlite3, const char sql, sqlite_callback, void *data, char **errmsg) このルーチンは、複数のSQLコマンドで構成できるsql引数によって提供されるSQLコマンドを実行するための迅速で簡単な方法を提供します。 ここで、最初の引数_sqlite3_はオープンデータベースオブジェクトであり、_sqlite_callback_は_data_が最初の引数であるコールバックであり、ルーチンによって発生したエラーをキャプチャするためにerrmsgが返されます。 SQLite3_exec（）ルーチンは、文字列の最後に到達するかエラーが発生するまで、 sql 引数で指定されたすべてのコマンドを解析して実行します。 |
| 3 |
sqlite3_close(sqlite3)* このルーチンは、sqlite3_open（）の呼び出しによって以前に開かれたデータベース接続を閉じます。 接続に関連付けられているすべての準備済みステートメントは、接続を閉じる前に確定する必要があります。 ファイナライズされていないクエリが残っている場合、sqlite3_close（）はSQLITE_BUSYを返し、エラーメッセージUnfinalizedステートメントが原因で閉じることができません。 |
データベースに接続
次のCコードセグメントは、既存のデータベースに接続する方法を示しています。 データベースが存在しない場合は、データベースが作成され、最終的にデータベースオブジェクトが返されます。
#include <stdio.h>
#include <sqlite3.h>
int main(int argc, char *argv[]) {
sqlite3* db;
char *zErrMsg = 0;
int rc;
rc = sqlite3_open("test.db", &db);
if( rc ) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
return(0);
} else {
fprintf(stderr, "Opened database successfully\n");
}
sqlite3_close(db);
}それでは、上記のプログラムをコンパイルして実行し、現在のディレクトリにデータベース test.db を作成しましょう。 要件に応じてパスを変更できます。
$gcc test.c -l sqlite3
$./a.out
Opened database successfullyあなたがC ++ソースコードを使用しようとしている場合は、次のようにコードをコンパイルできます-
$g++ test.c -l sqlite3ここでは、プログラムをsqlite3ライブラリとリンクして、必要な機能をCプログラムに提供しています。 これにより、ディレクトリにデータベースファイルtest.dbが作成され、次の結果が得られます。
-rwxr-xr-x. 1 root root 7383 May 8 02:06 a.out
-rw-r--r--. 1 root root 323 May 8 02:05 test.c
-rw-r--r--. 1 root root 0 May 8 02:06 test.dbテーブルを作成する
次のCコードセグメントを使用して、以前に作成したデータベースにテーブルを作成します-
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
static int callback(void *NotUsed, int argc, char **argv, char **azColName) {
int i;
for(i = 0; i<argc; i++) {
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
}
int main(int argc, char *argv[]) {
sqlite3* db;
char *zErrMsg = 0;
int rc;
char *sql;
/*Open database*/
rc = sqlite3_open("test.db", &db);
if( rc ) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
return(0);
} else {
fprintf(stdout, "Opened database successfully\n");
}
/*Create SQL statement*/
sql = "CREATE TABLE COMPANY(" \
"ID INT PRIMARY KEY NOT NULL," \
"NAME TEXT NOT NULL," \
"AGE INT NOT NULL," \
"ADDRESS CHAR(50)," \
"SALARY REAL );";
/*Execute SQL statement*/
rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
if( rc != SQLITE_OK ){
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
} else {
fprintf(stdout, "Table created successfully\n");
}
sqlite3_close(db);
return 0;
}上記のプログラムをコンパイルして実行すると、test.dbにCOMPANYテーブルが作成され、ファイルの最終リストは次のようになります-
-rwxr-xr-x. 1 root root 9567 May 8 02:31 a.out
-rw-r--r--. 1 root root 1207 May 8 02:31 test.c
-rw-r--r--. 1 root root 3072 May 8 02:31 test.dbINSERT操作
次のCコードセグメントは、上記の例で作成されたCOMPANYテーブルにレコードを作成する方法を示しています-
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
static int callback(void *NotUsed, int argc, char **argv, char **azColName) {
int i;
for(i = 0; i<argc; i++) {
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
}
int main(int argc, char *argv[]) {
sqlite3* db;
char *zErrMsg = 0;
int rc;
char *sql;
/*Open database*/
rc = sqlite3_open("test.db", &db);
if( rc ) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
return(0);
} else {
fprintf(stderr, "Opened database successfully\n");
}
/*Create SQL statement*/
sql = "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 );";
/*Execute SQL statement*/
rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
if( rc != SQLITE_OK ){
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
} else {
fprintf(stdout, "Records created successfully\n");
}
sqlite3_close(db);
return 0;
}上記のプログラムがコンパイルおよび実行されると、COMPANYテーブルに特定のレコードが作成され、次の2行が表示されます-
Opened database successfully
Records created successfullySELECT操作
実際の例を使ってレコードを取得する前に、例で使用しているコールバック関数について詳しく見てみましょう。 このコールバックは、SELECTステートメントから結果を取得する方法を提供します。 次の宣言があります-
typedef int (*sqlite3_callback)(
void*, /*Data provided in the 4th argument of sqlite3_exec()*/
int, /*The number of columns in row*/
char**, /*An array of strings representing fields in the row*/
char** /*An array of strings representing column names*/
);上記のコールバックが3番目の引数としてsqlite_exec（）ルーチンで提供される場合、SQLiteはSQL引数内で実行される各SELECTステートメントで処理される各レコードに対してこのコールバック関数を呼び出します。
次のCコードセグメントは、上記の例で作成されたCOMPANYテーブルからレコードを取得して表示する方法を示しています-
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
static int callback(void *data, int argc, char **argv, char **azColName){
int i;
fprintf(stderr, "%s: ", (const char*)data);
for(i = 0; i<argc; i++){
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
}
int main(int argc, char *argv[]) {
sqlite3* db;
char *zErrMsg = 0;
int rc;
char *sql;
const char* data = "Callback function called";
/*Open database*/
rc = sqlite3_open("test.db", &db);
if( rc ) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
return(0);
} else {
fprintf(stderr, "Opened database successfully\n");
}
/*Create SQL statement*/
sql = "SELECT * from COMPANY";
/*Execute SQL statement*/
rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
if( rc != SQLITE_OK ) {
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
} else {
fprintf(stdout, "Operation done successfully\n");
}
sqlite3_close(db);
return 0;
}上記のプログラムをコンパイルして実行すると、次の結果が生成されます。
Opened database successfully
Callback function called: ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 20000.0
Callback function called: ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0
Callback function called: ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0
Callback function called: ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0
Operation done successfully更新操作
次のCコードセグメントは、UPDATEステートメントを使用してレコードを更新し、更新されたレコードをCOMPANYテーブルから取得して表示する方法を示しています。
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
static int callback(void *data, int argc, char **argv, char **azColName){
int i;
fprintf(stderr, "%s: ", (const char*)data);
for(i = 0; i<argc; i++) {
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
}
int main(int argc, char *argv[]) {
sqlite3* db;
char *zErrMsg = 0;
int rc;
char *sql;
const char* data = "Callback function called";
/*Open database*/
rc = sqlite3_open("test.db", &db);
if( rc ) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
return(0);
} else {
fprintf(stderr, "Opened database successfully\n");
}
/*Create merged SQL statement*/
sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1; " \
"SELECT * from COMPANY";
/*Execute SQL statement*/
rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
if( rc != SQLITE_OK ) {
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
} else {
fprintf(stdout, "Operation done successfully\n");
}
sqlite3_close(db);
return 0;
}上記のプログラムをコンパイルして実行すると、次の結果が生成されます。
Opened database successfully
Callback function called: ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 25000.0
Callback function called: ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0
Callback function called: ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0
Callback function called: ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0
Operation done successfully削除操作
次のCコードセグメントは、DELETEステートメントを使用してレコードを削除し、COMPANYテーブルから残りのレコードをフェッチして表示する方法を示しています。
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
static int callback(void *data, int argc, char **argv, char **azColName) {
int i;
fprintf(stderr, "%s: ", (const char*)data);
for(i = 0; i<argc; i++) {
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
}
int main(int argc, char *argv[]) {
sqlite3* db;
char *zErrMsg = 0;
int rc;
char *sql;
const char* data = "Callback function called";
/*Open database*/
rc = sqlite3_open("test.db", &db);
if( rc ) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
return(0);
} else {
fprintf(stderr, "Opened database successfully\n");
}
/*Create merged SQL statement*/
sql = "DELETE from COMPANY where ID=2; " \
"SELECT * from COMPANY";
/*Execute SQL statement*/
rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
if( rc != SQLITE_OK ) {
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
} else {
fprintf(stdout, "Operation done successfully\n");
}
sqlite3_close(db);
return 0;
}上記のプログラムをコンパイルして実行すると、次の結果が生成されます。
Opened database successfully
Callback function called: ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 20000.0
Callback function called: ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0
Callback function called: ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0
Operation done successfullySQLite-Java
この章では、JavaプログラムでSQLiteを使用する方法を学習します。
インストール
JavaプログラムでSQLiteの使用を開始する前に、マシンにSQLite JDBCドライバーとJavaがセットアップされていることを確認する必要があります。 マシンにJavaをインストールするためのJavaチュートリアルを確認できます。 次に、SQLite JDBCドライバーのセットアップ方法を確認しましょう。
- sqlite-jdbcリポジトリから_sqlite-jdbc-（VERSION）.jar_の最新バージョンをダウンロードします。
- ダウンロードしたjarファイル_sqlite-jdbc-（VERSION）.jar_をクラスパスに追加するか、次の例で説明するように-classpathオプションと一緒に使用できます。
次のセクションでは、Java JDBCの概念についてほとんど知識がないことを前提としています。 そうでない場合は、30分をlink：/jdbc/jdbc-create-database [JDBCチュートリアル]に費やして、以下で説明する概念に慣れることをお勧めします。
データベースに接続する
次のJavaプログラムは、既存のデータベースに接続する方法を示しています。 データベースが存在しない場合は、データベースが作成され、最終的にデータベースオブジェクトが返されます。
import java.sql.*;
public class SQLiteJDBC {
public static void main( String args[] ) {
Connection c = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Opened database successfully");
}
}それでは、上記のプログラムをコンパイルして実行し、現在のディレクトリにデータベース test.db を作成しましょう。 要件に応じてパスを変更できます。 JDBCドライバーの現在のバージョン_sqlite-jdbc-3.7.2.jar_が現在のパスで利用可能であると想定しています。
$javac SQLiteJDBC.java
$java -classpath ".:sqlite-jdbc-3.7.2.jar" SQLiteJDBC
Open database successfullyあなたがWindowsマシンを使用しようとしている場合は、次のようにコードをコンパイルして実行することができます-
$javac SQLiteJDBC.java
$java -classpath ".;sqlite-jdbc-3.7.2.jar" SQLiteJDBC
Opened database successfullyテーブルを作成する
次のJavaプログラムを使用して、以前に作成したデータベースにテーブルを作成します。
import java.sql.*;
public class SQLiteJDBC {
public static void main( String args[] ) {
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
System.out.println("Opened database successfully");
stmt = c.createStatement();
String sql = "CREATE TABLE COMPANY " +
"(ID INT PRIMARY KEY NOT NULL," +
" NAME TEXT NOT NULL, " +
" AGE INT NOT NULL, " +
" ADDRESS CHAR(50), " +
" SALARY REAL)";
stmt.executeUpdate(sql);
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Table created successfully");
}
}上記のプログラムをコンパイルして実行すると、 test.db にCOMPANYテーブルが作成され、ファイルの最終リストは次のようになります-
-rw-r--r--. 1 root root 3201128 Jan 22 19:04 sqlite-jdbc-3.7.2.jar
-rw-r--r--. 1 root root 1506 May 8 05:43 SQLiteJDBC.class
-rw-r--r--. 1 root root 832 May 8 05:42 SQLiteJDBC.java
-rw-r--r--. 1 root root 3072 May 8 05:43 test.dbINSERT操作
次のJavaプログラムは、上記の例で作成されたCOMPANYテーブルにレコードを作成する方法を示しています。
import java.sql.*;
public class SQLiteJDBC {
public static void main( String args[] ) {
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
c.setAutoCommit(false);
System.out.println("Opened database successfully");
stmt = c.createStatement();
String sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
"VALUES (1, 'Paul', 32, 'California', 20000.00 );";
stmt.executeUpdate(sql);
sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
"VALUES (2, 'Allen', 25, 'Texas', 15000.00 );";
stmt.executeUpdate(sql);
sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
"VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );";
stmt.executeUpdate(sql);
sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
"VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";
stmt.executeUpdate(sql);
stmt.close();
c.commit();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Records created successfully");
}
}上記のプログラムをコンパイルして実行すると、COMPANYテーブルに特定のレコードが作成され、次の2行が表示されます-
Opened database successfully
Records created successfullySELECT操作
次のJavaプログラムは、上記の例で作成されたCOMPANYテーブルからレコードを取得して表示する方法を示しています。
import java.sql.*;
public class SQLiteJDBC {
public static void main( String args[] ) {
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
c.setAutoCommit(false);
System.out.println("Opened database successfully");
stmt = c.createStatement();
ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
while ( rs.next() ) {
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
String address = rs.getString("address");
float salary = rs.getFloat("salary");
System.out.println( "ID = " + id );
System.out.println( "NAME = " + name );
System.out.println( "AGE = " + age );
System.out.println( "ADDRESS = " + address );
System.out.println( "SALARY = " + salary );
System.out.println();
}
rs.close();
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Operation done successfully");
}
}上記のプログラムをコンパイルして実行すると、次の結果が生成されます。
Opened database successfully
ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 20000.0
ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0
ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0
ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0
Operation done successfully更新操作
以下のJavaコードは、UPDATEステートメントを使用してレコードを更新し、更新されたレコードをCOMPANYテーブルからフェッチして表示する方法を示しています。
import java.sql.*;
public class SQLiteJDBC {
public static void main( String args[] ) {
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
c.setAutoCommit(false);
System.out.println("Opened database successfully");
stmt = c.createStatement();
String sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1;";
stmt.executeUpdate(sql);
c.commit();
ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
while ( rs.next() ) {
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
String address = rs.getString("address");
float salary = rs.getFloat("salary");
System.out.println( "ID = " + id );
System.out.println( "NAME = " + name );
System.out.println( "AGE = " + age );
System.out.println( "ADDRESS = " + address );
System.out.println( "SALARY = " + salary );
System.out.println();
}
rs.close();
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Operation done successfully");
}
}上記のプログラムをコンパイルして実行すると、次の結果が生成されます。
Opened database successfully
ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 25000.0
ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0
ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0
ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0
Operation done successfully削除操作
次のJavaコードは、DELETEステートメントを使用してレコードを削除し、COMPANYテーブルから残りのレコードをフェッチして表示する方法を示しています。
import java.sql.*;
public class SQLiteJDBC {
public static void main( String args[] ) {
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
c.setAutoCommit(false);
System.out.println("Opened database successfully");
stmt = c.createStatement();
String sql = "DELETE from COMPANY where ID=2;";
stmt.executeUpdate(sql);
c.commit();
ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
while ( rs.next() ) {
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
String address = rs.getString("address");
float salary = rs.getFloat("salary");
System.out.println( "ID = " + id );
System.out.println( "NAME = " + name );
System.out.println( "AGE = " + age );
System.out.println( "ADDRESS = " + address );
System.out.println( "SALARY = " + salary );
System.out.println();
}
rs.close();
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Operation done successfully");
}
}上記のプログラムをコンパイルして実行すると、次の結果が生成されます。
Opened database successfully
ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 25000.0
ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0
ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0
Operation done successfullySQLite-PHP
この章では、PHPプログラムでSQLiteを使用する方法を学習します。
インストール
PHP 5.3.0以降、SQLite3拡張機能はデフォルトで有効になっています。 コンパイル時に*-without-sqlite3 *を使用して無効にすることができます。
Windowsユーザーは、この拡張機能を使用するためにphp_sqlite3.dllを有効にする必要があります。 このDLLは、PHP 5.3.0以降のWindowsのPHPディストリビューションに含まれています。
インストール手順の詳細については、PHPチュートリアルとその公式Webサイトをご覧ください。
PHPインターフェイスAPI
以下は、PHPプログラムからSQLiteデータベースを操作するための要件を満たすことができる重要なPHPルーチンです。 より洗練されたアプリケーションをお探しの場合は、PHPの公式ドキュメントをご覧ください。
| Sr.No. | API & Description |
|---|---|
| 1 |
public void SQLite3::open ( filename, flags, encryption_key ) SQLite 3データベースを開きます。 ビルドに暗号化が含まれている場合、そのキーの使用が試行されます。
SQLiteデータベースを開く方法を決定するために使用されるオプションのフラグ。 デフォルトでは、openはSQLITE3_OPEN_READWRITEを使用します。 SQLITE3_OPEN_CREATE。 |
| 2 |
public bool SQLite3::exec ( string $query ) このルーチンは、複数のSQLコマンドで構成されるsql引数によって提供されるSQLコマンドを実行するための迅速で簡単な方法を提供します。 このルーチンは、特定のデータベースに対して結果のないクエリを実行するために使用されます。 |
| 3 |
public SQLite3Result SQLite3::query ( string $query ) このルーチンはSQLクエリを実行し、クエリが結果を返す場合、 SQLite3Result オブジェクトを返します。 |
| 4 |
public int SQLite3::lastErrorCode ( void ) このルーチンは、失敗した最新のSQLite要求の数値結果コードを返します。 |
| 5 |
public string SQLite3::lastErrorMsg ( void ) このルーチンは、失敗した最新のSQLite要求を説明する英語のテキストを返します。 |
| 6 |
public int SQLite3::changes ( void ) このルーチンは、最新のSQLステートメントによって更新、挿入、または削除されたデータベース行の数を返します。 |
| 7 |
public bool SQLite3::close ( void )
|
| 8 |
public string SQLite3::escapeString ( string $value ) このルーチンは、SQLステートメントに安全に含めるために適切にエスケープされた文字列を返します。 |
データベースに接続する
次のPHPコードは、既存のデータベースに接続する方法を示しています。 データベースが存在しない場合は、データベースが作成され、最終的にデータベースオブジェクトが返されます。
<?php
class MyDB extends SQLite3 {
function __construct() {
$this->open('test.db');
}
}
$db = new MyDB();
if(!$db) {
echo $db->lastErrorMsg();
} else {
echo "Opened database successfully\n";
}
?>次に、上記のプログラムを実行して、現在のディレクトリにデータベース test.db を作成します。 要件に応じてパスを変更できます。 データベースが正常に作成された場合、次のメッセージが表示されます-
Open database successfullyテーブルを作成する
次のPHPプログラムを使用して、以前に作成したデータベースにテーブルを作成します。
<?php
class MyDB extends SQLite3 {
function __construct() {
$this->open('test.db');
}
}
$db = new MyDB();
if(!$db) {
echo $db->lastErrorMsg();
} else {
echo "Opened database successfully\n";
}
$sql =<<<EOF
CREATE TABLE COMPANY
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL);
EOF;
$ret = $db->exec($sql);
if(!$ret){
echo $db->lastErrorMsg();
} else {
echo "Table created successfully\n";
}
$db->close();
?>上記のプログラムが実行されると、 test.db にCOMPANYテーブルが作成され、次のメッセージが表示されます-
Opened database successfully
Table created successfullyINSERT操作
次のPHPプログラムは、上記の例で作成されたCOMPANYテーブルにレコードを作成する方法を示しています。
<?php
class MyDB extends SQLite3 {
function __construct() {
$this->open('test.db');
}
}
$db = new MyDB();
if(!$db){
echo $db->lastErrorMsg();
} else {
echo "Opened database successfully\n";
}
$sql =<<<EOF
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 );
EOF;
$ret = $db->exec($sql);
if(!$ret) {
echo $db->lastErrorMsg();
} else {
echo "Records created successfully\n";
}
$db->close();
?>上記のプログラムを実行すると、COMPANYテーブルに特定のレコードが作成され、次の2行が表示されます。
Opened database successfully
Records created successfullySELECT操作
次のPHPプログラムは、上記の例で作成されたCOMPANYテーブルからレコードを取得して表示する方法を示しています-
<?php
class MyDB extends SQLite3 {
function __construct() {
$this->open('test.db');
}
}
$db = new MyDB();
if(!$db) {
echo $db->lastErrorMsg();
} else {
echo "Opened database successfully\n";
}
$sql =<<<EOF
SELECT *from COMPANY;
EOF;
$ret = $db->query($sql);
while($row = $ret->fetchArray(SQLITE3_ASSOC) ) {
echo "ID = ". $row['ID'] . "\n";
echo "NAME = ". $row['NAME'] ."\n";
echo "ADDRESS = ". $row['ADDRESS'] ."\n";
echo "SALARY = ".$row['SALARY'] ."\n\n";
}
echo "Operation done successfully\n";
$db->close();
?>上記のプログラムを実行すると、次の結果が生成されます。
Opened database successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 20000
ID = 2
NAME = Allen
ADDRESS = Texas
SALARY = 15000
ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000
ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000
Operation done successfully更新操作
以下のPHPコードは、UPDATEステートメントを使用してレコードを更新し、更新されたレコードをCOMPANYテーブルからフェッチして表示する方法を示しています。
<?php
class MyDB extends SQLite3 {
function __construct() {
$this->open('test.db');
}
}
$db = new MyDB();
if(!$db) {
echo $db->lastErrorMsg();
} else {
echo "Opened database successfully\n";
}
$sql =<<<EOF
UPDATE COMPANY set SALARY = 25000.00 where ID=1;
EOF;
$ret = $db->exec($sql);
if(!$ret) {
echo $db->lastErrorMsg();
} else {
echo $db->changes(), " Record updated successfully\n";
}
$sql =<<<EOF
SELECT* from COMPANY;
EOF;
$ret = $db->query($sql);
while($row = $ret->fetchArray(SQLITE3_ASSOC) ) {
echo "ID = ". $row['ID'] . "\n";
echo "NAME = ". $row['NAME'] ."\n";
echo "ADDRESS = ". $row['ADDRESS'] ."\n";
echo "SALARY = ".$row['SALARY'] ."\n\n";
}
echo "Operation done successfully\n";
$db->close();
?>上記のプログラムを実行すると、次の結果が生成されます。
Opened database successfully
1 Record updated successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 25000
ID = 2
NAME = Allen
ADDRESS = Texas
SALARY = 15000
ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000
ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000
Operation done successfully削除操作
次のPHPコードは、DELETEステートメントを使用してレコードを削除し、COMPANYテーブルから残りのレコードを取得して表示する方法を示しています。
<?php
class MyDB extends SQLite3 {
function __construct() {
$this->open('test.db');
}
}
$db = new MyDB();
if(!$db) {
echo $db->lastErrorMsg();
} else {
echo "Opened database successfully\n";
}
$sql =<<<EOF
DELETE from COMPANY where ID = 2;
EOF;
$ret = $db->exec($sql);
if(!$ret){
echo $db->lastErrorMsg();
} else {
echo $db->changes(), " Record deleted successfully\n";
}
$sql =<<<EOF
SELECT * from COMPANY;
EOF;
$ret = $db->query($sql);
while($row = $ret->fetchArray(SQLITE3_ASSOC) ) {
echo "ID = ". $row['ID'] . "\n";
echo "NAME = ". $row['NAME'] ."\n";
echo "ADDRESS = ". $row['ADDRESS'] ."\n";
echo "SALARY = ".$row['SALARY'] ."\n\n";
}
echo "Operation done successfully\n";
$db->close();
?>上記のプログラムを実行すると、次の結果が生成されます。
Opened database successfully
1 Record deleted successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 25000
ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000
ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000
Operation done successfullySQLite-Perl
この章では、PerlプログラムでSQLiteを使用する方法を学習します。
インストール
SQLite3は、Perlプログラミング言語用のデータベースアクセスモジュールであるPerl DBIモジュールを使用してPerlと統合できます。 標準のデータベースインターフェイスを提供する一連のメソッド、変数、および規則を定義します。
以下は、Linux/UNIXマシンにDBIモジュールをインストールする簡単な手順です-
$ wget http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-1.625.tar.gz
$ tar xvfz DBI-1.625.tar.gz
$ cd DBI-1.625
$ perl Makefile.PL
$ make
$ make installあなたがDBI用のSQLiteドライバをインストールする必要がある場合、それは次のようにインストールすることができます-
$ wget http://search.cpan.org/CPAN/authors/id/M/MS/MSERGEANT/DBD-SQLite-1.11.tar.gz
$ tar xvfz DBD-SQLite-1.11.tar.gz
$ cd DBD-SQLite-1.11
$ perl Makefile.PL
$ make
$ make installDBIインターフェイスAPI
以下は重要なDBIルーチンであり、PerlプログラムからSQLiteデータベースを操作するための要件を満たすことができます。 より洗練されたアプリケーションをお探しの場合は、Perl DBIの公式ドキュメントをご覧ください。
| Sr.No. | API & Description |
|---|---|
| 1 |
DBI→connect($data_source, "", "", \%attr) 要求された$ data_sourceへのデータベース接続またはセッションを確立します。 接続が成功した場合、データベースハンドルオブジェクトを返します。 データソースの形式は- DBI：SQLite：dbname = 'test.db' です。SQLiteはSQLiteドライバー名で、test.dbはSQLiteデータベースファイルの名前です。 ファイル名が '：memory：' として指定されている場合、メモリ内にセッション中だけ持続するメモリ内データベースが作成されます。 ファイル名が実際のデバイスファイル名である場合、その値を使用してデータベースファイルを開こうとします。 その名前のファイルが存在しない場合、その名前の新しいデータベースファイルが作成されます。 2番目と3番目のパラメーターは空の文字列として保持し、最後のパラメーターは次の例に示すようにさまざまな属性を渡すことです。 |
| 2 |
$dbh→do($sql) このルーチンは、単一のSQLステートメントを準備して実行します。 影響を受けた行の数またはエラー時にundefを返します。 -1の戻り値は、行数が不明、適用できない、または利用できないことを意味します。 ここで、$ dbhはDBI→ connect（）呼び出しによって返されるハンドルです。 |
| 3 |
$dbh→prepare($sql) このルーチンは、データベースエンジンによる後の実行のためにステートメントを準備し、ステートメントハンドルオブジェクトへの参照を返します。 |
| 4 |
$sth→execute() このルーチンは、準備されたステートメントの実行に必要な処理を実行します。 エラーが発生すると、undefが返されます。 実行が成功すると、影響を受ける行の数に関係なく常にtrueが返されます。 ここで、$ sthは、$ dbh→ prepare（$ sql）呼び出しによって返されるステートメントハンドルです。 |
| 5 |
$sth→fetchrow_array() このルーチンは、データの次の行をフェッチし、フィールド値を含むリストとしてそれを返します。 nullフィールドは、リスト内のundef値として返されます。 |
| 6 |
$DBI::err これは、$ h→ errと同等です。$ hは、$ dbh、$ sth、$ drhなどのハンドルタイプのいずれかです。 これは、最後に呼び出されたドライバーメソッドからネイティブデータベースエンジンのエラーコードを返します。 |
| 7 |
$DBI::errstr これは、$ h→ errstrと同等です。$ hは、$ dbh、$ sth、$ drhなどのハンドルタイプのいずれかです。 これは、最後に呼び出されたDBIメソッドからのネイティブデータベースエンジンエラーメッセージを返します。 |
| 8 |
$dbh→disconnect() このルーチンは、DBI→ connect（）の呼び出しによって以前に開かれたデータベース接続を閉じます。 |
データベースに接続
次のPerlコードは、既存のデータベースに接続する方法を示しています。 データベースが存在しない場合は、データベースが作成され、最終的にデータベースオブジェクトが返されます。
#!/usr/bin/perl
use DBI;
use strict;
my $driver = "SQLite";
my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr;
print "Opened database successfully\n";次に、上記のプログラムを実行して、現在のディレクトリにデータベースtest.dbを作成します。 要件に応じてパスを変更できます。 上記のコードをsqlite.plファイルに保持し、以下に示すように実行します。 データベースが正常に作成された場合、次のメッセージが表示されます-
$ chmod +x sqlite.pl
$ ./sqlite.pl
Open database successfullyテーブルを作成する
次のPerlプログラムを使用して、以前に作成したデータベースにテーブルを作成します。
#!/usr/bin/perl
use DBI;
use strict;
my $driver = "SQLite";
my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr;
print "Opened database successfully\n";
my $stmt = qq(CREATE TABLE COMPANY
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL););
my $rv = $dbh->do($stmt);
if($rv < 0) {
print $DBI::errstr;
} else {
print "Table created successfully\n";
}
$dbh->disconnect();上記のプログラムが実行されると、test.dbにCOMPANYテーブルが作成され、次のメッセージが表示されます-
Opened database successfully
Table created successfully注-操作のいずれかで次のエラーが表示される場合-
DBD::SQLite::st execute failed: not an error(21) at dbdimp.c line 398- このような場合、DBD-SQLiteインストールで使用可能な* dbdimp.cファイル*を開き、* sqlite3_prepare（）関数を見つけて、その3番目の引数を0ではなく *-1 に変更します。 最後に、 make を使用してDBD
- SQLiteをインストールし、 make install を実行して問題を解決します。
INSERT操作
次のPerlプログラムは、上記の例で作成されたCOMPANYテーブルにレコードを作成する方法を示しています。
#!/usr/bin/perl
use DBI;
use strict;
my $driver = "SQLite";
my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr;
print "Opened database successfully\n";
my $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 ));
my $rv = $dbh->do($stmt) or die $DBI::errstr;
$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 ));
$rv = $dbh->do($stmt) or die $DBI::errstr;
$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 ));
$rv = $dbh->do($stmt) or die $DBI::errstr;
$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 ););
$rv = $dbh->do($stmt) or die $DBI::errstr;
print "Records created successfully\n";
$dbh->disconnect();上記のプログラムが実行されると、COMPANYテーブルに特定のレコードが作成され、次の2行が表示されます-
Opened database successfully
Records created successfullySELECT操作
次のPerlプログラムは、上記の例で作成されたCOMPANYテーブルからレコードを取得して表示する方法を示しています。
#!/usr/bin/perl
use DBI;
use strict;
my $driver = "SQLite";
my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr;
print "Opened database successfully\n";
my $stmt = qq(SELECT id, name, address, salary from COMPANY;);
my $sth = $dbh->prepare( $stmt );
my $rv = $sth->execute() or die $DBI::errstr;
if($rv < 0) {
print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) {
print "ID = ". $row[0] . "\n";
print "NAME = ". $row[1] ."\n";
print "ADDRESS = ". $row[2] ."\n";
print "SALARY = ". $row[3] ."\n\n";
}
print "Operation done successfully\n";
$dbh->disconnect();上記のプログラムを実行すると、次の結果が生成されます。
Opened database successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 20000
ID = 2
NAME = Allen
ADDRESS = Texas
SALARY = 15000
ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000
ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000
Operation done successfully更新操作
次のPerlコードは、ステートメントを更新してレコードを更新し、更新されたレコードをCOMPANYテーブルからフェッチして表示する方法を示しています。
#!/usr/bin/perl
use DBI;
use strict;
my $driver = "SQLite";
my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr;
print "Opened database successfully\n";
my $stmt = qq(UPDATE COMPANY set SALARY = 25000.00 where ID=1;);
my $rv = $dbh->do($stmt) or die $DBI::errstr;
if( $rv < 0 ) {
print $DBI::errstr;
} else {
print "Total number of rows updated : $rv\n";
}
$stmt = qq(SELECT id, name, address, salary from COMPANY;);
my $sth = $dbh->prepare( $stmt );
$rv = $sth->execute() or die $DBI::errstr;
if($rv < 0) {
print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) {
print "ID = ". $row[0] . "\n";
print "NAME = ". $row[1] ."\n";
print "ADDRESS = ". $row[2] ."\n";
print "SALARY = ". $row[3] ."\n\n";
}
print "Operation done successfully\n";
$dbh->disconnect();上記のプログラムを実行すると、次の結果が生成されます。
Opened database successfully
Total number of rows updated : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 25000
ID = 2
NAME = Allen
ADDRESS = Texas
SALARY = 15000
ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000
ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000
Operation done successfully削除操作
次のPerlコードは、DELETEステートメントを使用してレコードを削除し、COMPANYテーブルから残りのレコードを取得して表示する方法を示しています-
#!/usr/bin/perl
use DBI;
use strict;
my $driver = "SQLite";
my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr;
print "Opened database successfully\n";
my $stmt = qq(DELETE from COMPANY where ID = 2;);
my $rv = $dbh->do($stmt) or die $DBI::errstr;
if( $rv < 0 ) {
print $DBI::errstr;
} else {
print "Total number of rows deleted : $rv\n";
}
$stmt = qq(SELECT id, name, address, salary from COMPANY;);
my $sth = $dbh->prepare( $stmt );
$rv = $sth->execute() or die $DBI::errstr;
if($rv < 0) {
print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) {
print "ID = ". $row[0] . "\n";
print "NAME = ". $row[1] ."\n";
print "ADDRESS = ". $row[2] ."\n";
print "SALARY = ". $row[3] ."\n\n";
}
print "Operation done successfully\n";
$dbh->disconnect();上記のプログラムを実行すると、次の結果が生成されます。
Opened database successfully
Total number of rows deleted : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 25000
ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000
ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000
Operation done successfullySQLite-Python
この章では、PythonプログラムでSQLiteを使用する方法を学習します。
インストール
SQLite3は、Gerhard Haringによって作成されたsqlite3モジュールを使用してPythonと統合できます。 PEP 249で説明されているDB-API 2.0仕様に準拠したSQLインターフェイスを提供します。 このモジュールはPythonバージョン2.5.x以降でデフォルトで出荷されるため、このモジュールを個別にインストールする必要はありません。
sqlite3モジュールを使用するには、最初にデータベースを表す接続オブジェクトを作成する必要があります。次に、オプションでカーソルオブジェクトを作成できます。これは、すべてのSQLステートメントの実行に役立ちます。
Python sqlite3モジュールAPI
以下は重要なsqlite3モジュールルーチンであり、PythonプログラムからSQLiteデータベースを操作するための要件を満たすことができます。 より洗練されたアプリケーションをお探しの場合は、Python sqlite3モジュールの公式ドキュメントをご覧ください。
| Sr.No. | API & Description |
|---|---|
| 1 |
sqlite3.connect(database [,timeout ,other optional arguments]) このAPIは、SQLiteデータベースファイルへの接続を開きます。 "：memory："を使用して、ディスクではなくRAMにあるデータベースへのデータベース接続を開くことができます。 データベースが正常に開くと、接続オブジェクトが返されます。 データベースが複数の接続によってアクセスされ、プロセスの1つがデータベースを変更すると、SQLiteデータベースはそのトランザクションがコミットされるまでロックされます。 timeoutパラメーターは、例外が発生するまでロックが解除されるまで接続が待機する時間を指定します。 タイムアウトパラメータのデフォルトは5.0（5秒）です。 指定されたデータベース名が存在しない場合、この呼び出しはデータベースを作成します。 現在のディレクトリ以外の場所にデータベースを作成する場合は、必要なパスでファイル名を指定することもできます。 |
| 2 |
connection.cursor([cursorClass]) このルーチンは、Pythonを使用したデータベースプログラミング全体で使用される*カーソル*を作成します。 このメソッドは、単一のオプションパラメータcursorClassを受け入れます。 指定する場合、これはsqlite3.Cursorを拡張するカスタムカーソルクラスである必要があります。 |
| 3 |
cursor.execute(sql [, optional parameters]) このルーチンは、SQLステートメントを実行します。 SQLステートメントはパラメーター化できます（i。 e. SQLリテラルの代わりのプレースホルダー）。 sqlite3モジュールは、疑問符と名前付きプレースホルダー（名前付きスタイル）の2種類のプレースホルダーをサポートしています。 たとえば-cursor.execute（ "insert into people values（？、？）"、（who、age）） |
| 4 |
connection.execute(sql [, optional parameters]) このルーチンは、カーソルオブジェクトによって提供される上記のexecuteメソッドのショートカットであり、cursorメソッドを呼び出して中間カーソルオブジェクトを作成し、指定されたパラメーターでカーソルのexecuteメソッドを呼び出します。 |
| 5 |
cursor.executemany(sql, seq_of_parameters) このルーチンは、シーケンスsqlで見つかったすべてのパラメーターシーケンスまたはマッピングに対してSQLコマンドを実行します。 |
| 6 |
connection.executemany(sql[, parameters]) このルーチンは、cursorメソッドを呼び出して中間カーソルオブジェクトを作成し、指定されたパラメーターでcursor.s executemanyメソッドを呼び出すショートカットです。 |
| 7 |
cursor.executescript(sql_script) このルーチンは、スクリプトの形式で提供される複数のSQLステートメントを一度に実行します。 最初にCOMMITステートメントを発行し、次にパラメーターとして取得したSQLスクリプトを実行します。 すべてのSQLステートメントはセミコロン（;）で区切る必要があります。 |
| 8 |
connection.executescript(sql_script) このルーチンは、カーソルメソッドを呼び出して中間カーソルオブジェクトを作成し、指定されたパラメーターを使用してカーソルのexecutescriptメソッドを呼び出すショートカットです。 |
| 9 |
connection.total_changes() このルーチンは、データベース接続が開かれてから変更、挿入、または削除されたデータベース行の総数を返します。 |
| 10 |
connection.commit() このメソッドは、現在のトランザクションをコミットします。 このメソッドを呼び出さないと、最後のcommit（）の呼び出し以降に行った操作は、他のデータベース接続からは見えません。 |
| 11 |
connection.rollback() このメソッドは、commit（）の最後の呼び出し以降のデータベースへの変更をロールバックします。 |
| 12 |
connection.close() このメソッドは、データベース接続を閉じます。 これはcommit（）を自動的に呼び出さないことに注意してください。 最初にcommit（）を呼び出さずにデータベース接続を閉じると、変更は失われます！ |
| 13 |
cursor.fetchone() このメソッドは、クエリ結果セットの次の行をフェッチして単一のシーケンスを返します。使用可能なデータがなくなったらNoneを返します。 |
| 14 |
cursor.fetchmany([size = cursor.arraysize]) このルーチンは、クエリ結果の次の行セットをフェッチし、リストを返します。 使用可能な行がなくなると、空のリストが返されます。 このメソッドは、sizeパラメーターで指定された数の行をフェッチしようとします。 |
| 15 |
cursor.fetchall() このルーチンは、クエリ結果のすべての（残りの）行をフェッチし、リストを返します。 使用可能な行がない場合、空のリストが返されます。 |
データベースに接続
次のPythonコードは、既存のデータベースに接続する方法を示しています。 データベースが存在しない場合は、データベースが作成され、最終的にデータベースオブジェクトが返されます。
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";ここで、特別な名前*：memory：としてデータベース名を指定して、RAMにデータベースを作成することもできます。 次に、上記のプログラムを実行して、現在のディレクトリにデータベース *test.db を作成します。 要件に応じてパスを変更できます。 上記のコードをsqlite.pyファイルに保持し、以下に示すように実行します。 データベースが正常に作成されると、次のメッセージが表示されます。
$chmod +x sqlite.py
$./sqlite.py
Open database successfullyテーブルを作成する
次のPythonプログラムを使用して、以前に作成したデータベースにテーブルを作成します。
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
conn.execute('''CREATE TABLE COMPANY
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL);''')
print "Table created successfully";
conn.close()上記のプログラムが実行されると、 test.db にCOMPANYテーブルが作成され、次のメッセージが表示されます-
Opened database successfully
Table created successfullyINSERT操作
次のPythonプログラムは、上記の例で作成されたCOMPANYテーブルにレコードを作成する方法を示しています。
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (1, 'Paul', 32, 'California', 20000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (2, 'Allen', 25, 'Texas', 15000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )");
conn.commit()
print "Records created successfully";
conn.close()上記のプログラムが実行されると、COMPANYテーブルに特定のレコードが作成され、次の2行が表示されます-
Opened database successfully
Records created successfullySELECT操作
次のPythonプログラムは、上記の例で作成されたCOMPANYテーブルからレコードを取得して表示する方法を示しています。
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
print "ID = ", row[0]
print "NAME = ", row[1]
print "ADDRESS = ", row[2]
print "SALARY = ", row[3], "\n"
print "Operation done successfully";
conn.close()上記のプログラムを実行すると、次の結果が生成されます。
Opened database successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 20000.0
ID = 2
NAME = Allen
ADDRESS = Texas
SALARY = 15000.0
ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000.0
ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000.0
Operation done successfully更新操作
次のPythonコードは、UPDATEステートメントを使用してレコードを更新し、COMPANYテーブルから更新されたレコードをフェッチして表示する方法を示しています。
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
conn.execute("UPDATE COMPANY set SALARY = 25000.00 where ID = 1")
conn.commit()
print "Total number of rows updated :", conn.total_changes
cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
print "ID = ", row[0]
print "NAME = ", row[1]
print "ADDRESS = ", row[2]
print "SALARY = ", row[3], "\n"
print "Operation done successfully";
conn.close()上記のプログラムを実行すると、次の結果が生成されます。
Opened database successfully
Total number of rows updated : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 25000.0
ID = 2
NAME = Allen
ADDRESS = Texas
SALARY = 15000.0
ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000.0
ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000.0
Operation done successfully削除操作
次のPythonコードは、DELETEステートメントを使用してレコードを削除し、COMPANYテーブルから残りのレコードを取得して表示する方法を示しています。
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
conn.execute("DELETE from COMPANY where ID = 2;")
conn.commit()
print "Total number of rows deleted :", conn.total_changes
cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
print "ID = ", row[0]
print "NAME = ", row[1]
print "ADDRESS = ", row[2]
print "SALARY = ", row[3], "\n"
print "Operation done successfully";
conn.close()上記のプログラムを実行すると、次の結果が生成されます。
Opened database successfully
Total number of rows deleted : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 20000.0
ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000.0
ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000.0
Operation done successfully