• 参考サイト
  SQLite入門

項目sqlite3.exe の使い方python での使い方テーブル名変更とカラム追加テーブル削除データの追加・更新データの削除ビューを作成するインデックストリガーデータの取得SQLite関数 個数カウント

sqlite3.exe の使い方

• ファイル名の区切り文字は \\ ふたつね
• 終了は .exit

python での使い方

cursor.execute("select *************")
res = cursor.fetchall()
print(res)
print(len(res)) # 件数取得

• where
  select * from user where address = 'Tokyo';
  c.execute('select * from musics where title=? ' , '')
  print(c.fetchall())

• Like
  select * from user where address like 'S%';
  ---- ------- ---
  table column expression
  特殊文字
  % 任意の0文字以上の文字列
  _ 任意の1文字

否定
select * from user where name not like '_____';

エスケープ
select * from mytable where mycolumn like '%10\(%' escape '\) '
この例は $ をエスケープ文字にしている

• VACUUM
  VACUUM 文を実行することで使用していない領域を解放しファイルのサイズを小さくできる場合があります

• カラムを追加する
  ALTER TABLE テーブル名 ADD COLUMN カラム名[ データ型];

• PRIMARY KEY制約とは
  主キーが設定されたカラムでは他のデータと重複する値を取ることはできません。
  CREATE TABLE テーブル名(カラム名 PRIMARY KEY, ...);

INTEGER型のカラムにPRIMARY KEY制約を設定する場合
新しいデータを追加する時に対象のカラムの値を省略すると、 AUTOINCREMENT 制約を設定した時と同じように自動的に数値が格納されます。
CREATE TABLE テーブル名(カラム名 INTEGER PRIMARY KEY, ...);

ROWID
ほぼ同じ機能？？

NOT NULL 制約
対象のカラムには NULL を格納することができなくなります

UNIQUE 制約
UNIQUE 制約を設定すると、対象のカラムには重複した値を設定することができなくなります

DEFAULT制約
DEFAULT 制約を設定すると、データを追加する時に値を省略した場合のデフォルト値を設定することができます

DEFAULT 制約で指定するデフォルトの値として次の値を指定すると、データを追加した時にその時点の日付や時刻を取得してデフォルトの値としてカラムに可能することができます。
CURRENT_TIME HH:MM:SS形式
CURRENT_DATE YYYY-MM-DD形式
CURRENT_TIMESTAMP YYYY-MM-DD HH:MM:SS形式
create table user(id integer, name text, ts default CURRENT_TIMESTAMP);

CHECK制約
カラムに CHECK 制約を設定すると、データを追加する時に値が指定した条件を満たしているかどうかのチェックをすることができます
create table user(id integer, name text, old integer check(old > 18));
---> old が１０のデータは追加できない
データ入力時のエラーチェックとして使う

テーブル名変更とカラム追加

ALTER TABLE テーブル名 RENAME TO 新しいテーブル名 ;
ALTER TABLE テーブル名 ADD COLUMN カラム名[ データ型];

テーブル削除

DROP TABLE テーブル名;

データの追加・更新

INSERT INTO テーブル名 VALUES(値1, 値2, ...)
UPDATE テーブル名 SET カラム名1 = 値1, カラム名2 = 値2, ... WHERE 条件式

データの削除

DELETE FROM テーブル名 WHERE 条件式;
where 条件式無し ---> 全削除

ビューを作成する

create view tokyouser as select id, name from user where address = 'Tokyo';
select * from tokyouser;
これは↓と同じ
select id, name from user where address = 'Tokyo';

インデックス

create index nameindex on user(name);
--------- ---- -----
new table column
インデックスの確認
.indices

インデックスの削除
drop index nameindex;

トリガー

特定のテーブルのデータが更新されたら、同時に別のテーブルのデータを更新しておきたい場合があります。このような場合に使用されるのがトリガーです

データの取得

SELECT * FROM テーブル名;

IS NULL句を使った条件式の記述

SELECT カラム名 , ... FROM テーブル名 WHERE カラム IS NULL;

SELECT カラム名 , ... FROM テーブル名 ORDER BY カラム名 [ASC | DESC];

SELECT カラム名 , ... FROM テーブル名 WHERE 条件式;

SELECT カラム名 , ... FROM テーブル名 WHERE カラム NOT BETWEEN 値1 AND 値2;

SELECT カラム名 , ... FROM テーブル名 WHERE カラム IN(値1, 値2, ...);

SELECT カラム名 , ... FROM テーブル名 WHERE カラム LIKE パターン;
% 任意の0文字以上の文字列
_ 任意の1文字

SELECT カラム名 , ... FROM テーブル名 WHERE カラム GLOB パターン;

•  任意の0文字以上の文字列
  

? 任意の1文字
[abc] a or b or cのいずれかに一致
[a-d] aからdまでにいずれかに一致

SELECT カラム名 , ... FROM テーブル名 WHERE カラム IS NULL;
NULL そもそも値を設定しない

SELECT DISTINCT カラム名, ... FROM テーブル名;
重複データの除外

SELECT カラム名, ... FROM テーブル名 LIMIT 行数;
取得するデータの数を指定

SELECT カラム名, ... FROM テーブル名 LIMIT 行数 OFFSET 開始位置;
SELECT カラム名, ... FROM テーブル名 LIMIT 開始位置, 行数;
取得を開始する位置の指定する（必ずlimitと組み合わせる）

SQLite関数 個数カウント

select count(name), count(address) from user;
カラム毎のデータ数 whwre も使えます
select gender, count(*) from user group by gender;
カラム毎にグループ化して行数を取得