SQLでデータベース探索 #MySQL

前回は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 を使っていると同じコマンドで操作できて便利そうです。