SQLでデータベース探索

前回はdb.py を使ってデータベースを探索してみました。

• db.pyでデータベース探索

しかし、環境によってはインストールが難しい場合もありますし、それほど頻繁に探索するわけではないがちょっと情報が欲しいといった場合には直接クライアントで調べられた方がうれしいです。

というわけで、SQLで直接データベースを探索する方法を調べてみました。

テーブル・カラムの検索

MySQLの場合

MySQL ではinformation_schema の中にデータベースの情報が格納されている。

テーブルの検索

SELECT * FROM information_schema.TABLE WHERE TABLE_NAME LIKE '%Customer%';

カラムの検索

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_TYPE FROM information_schema.COLUMNS WHERE COLUMN_NAME LIKE '%list%';

SQLiteの場合

SQLiteの場合にはsqlite_master テーブルにテーブル情報が格納されている。
カラムの情報を直接検索することはできないが、sqlite_master テーブルを表示させれば必要な情報は大体得られる。

$ sqlite3 -line chinook.sqlite 'SELECT name, sql FROM sqlite_master WHERE type="table" AND name LIKE "%list%"' 
 name = Playlist
  sql = CREATE TABLE [Playlist]
(
    [PlaylistId] INTEGER  NOT NULL,
    [Name] NVARCHAR(120),
    CONSTRAINT [PK_Playlist] PRIMARY KEY  ([PlaylistId])
)

 name = PlaylistTrack
  sql = CREATE TABLE [PlaylistTrack]
(
    [PlaylistId] INTEGER  NOT NULL,
    [TrackId] INTEGER  NOT NULL,
    CONSTRAINT [PK_PlaylistTrack] PRIMARY KEY  ([PlaylistId], [TrackId]),
    FOREIGN KEY ([PlaylistId]) REFERENCES [Playlist] ([PlaylistId]) 
		ON DELETE NO ACTION ON UPDATE NO ACTION,
    FOREIGN KEY ([TrackId]) REFERENCES [Track] ([TrackId]) 
		ON DELETE NO ACTION ON UPDATE NO ACTION
)

ちなみに、db.py ではこのクエリでテーブル名、PRAGMA TABLE_INFO(テーブル名);でカラム名を取得し、一時的にinformation_schema のようなテーブルを作成している。

データの取り出し

先頭のデータを取り出す

LIMIT を使う。

sqlite> .mode tabs
sqlite> SELECT CustomerId, FirstName, LastName FROM Customer LIMIT 10;
1	Luís	Gonçalves
2	Leonie	Köhler
3	François	Tremblay
4	Bjørn	Hansen
5	František	Wichterlová
6	Helena	Holý
7	Astrid	Gruber
8	Daan	Peeters
9	Kara	Nielsen
10	Eduardo	Martins

サンプリングする

データをランダムに並べ替えて先頭をとってくる。乱数はsqlite3ではRANDOM()、MySQL では RAND() で生成できる。

巨大なテーブルを対象に実行するとかなり負荷がかかるので注意。

sqlite> SELECT CustomerId, FirstName, LastName FROM Customer ORDER BY RANDOM() LIMIT 10;
53	Phil	Hughes
14	Mark	Philips
34	João	Fernandes
32	Aaron	Mitchell
7	Astrid	Gruber
58	Manoj	Pareek
13	Fernanda	Ramos
18	Michelle	Brooks
10	Eduardo	Martins
12	Roberto	Almeida

db.pyのクエリから学ぶ

db.py が使っているクエリを見ると、各データベースでどのようなクエリを実行すれば良いか分かる。

まとめ

SQLだけでも思ったより簡単にテーブルを探索できることがわかりました。大体のケースではSQLだけで十分かもしれません。

データベースの種類ごとにクエリが異なるのでdb.py を使っていると同じコマンドで操作できて便利そうです。