Wikipedia:データベースクエリ

Wikimedia Downloads からデータベースをダウンロードし、MySQLのクエリを実行することができます。

また、一部のシステム管理者はデータベースに対して直接クエリを実行する権限をもっており、tswiki:Query service にてリクエストすることが可能です。クエリ・サービス利用者のリストでこの権限をもち、かつリクエストに応える意思を示している開発者の一覧を確認できます。

以下には利便のため、そして誤った形式のクエリを実行してしまわないために、いくつかのヒントを掲載しています。情報をお持ちの方は随時追加をお願いいたします。

ガイド[編集]

Wikimedia Downloads からクエリを実行したいデータベースダンプをダウンロードします。
MySQL.com から MySQL をダウンロードします。
MySQL をインストールします。Windows では再起動が必要になる場合があります。
Unix の場合、イニシャル・データベースを作成 (mysql_install_db) し、mysql サーバー・プロセスを開始します。 (eg mysqld_safe)
Windows の場合、スタート > プログラム > MySQL > MySQL コマンドライン（またはスタート > run > 'cmd' とタイプ　> エンターキー　> 'mysql' とタイプ　> エンターキー）、Unix および MacOS X の場合、ターミナルで mysql とタイプします。
以下をタイプします： CREATE DATABASE wikipedia;
以下をタイプします： USE wikipedia
データベースダウンプがダウンロード完了したら、解凍します（gunzip）。以下をタイプします：SOURCE C:\Database Location\cur_en_whatever.sql
完了したら実行したいクエリを実行できます。クエリの最初の行を mysql> コマンドラインに入力、エンターキーを押し、以降の行を順に入力していくだけです。最後の行にはセミコロンを入力します。 table_rows と database.name は以下の例とは異なるものになっています。もし自分の使うデータベースに wikipedia と名前を付けたのなら、以下の命令で変換できます。
例: (sample output)

mysql> SELECT c.cur_title FROM wikipedia.cur c
    -> WHERE c.cur_text LIKE "%directory.google.com%";

このページからクエリをコピーすると、ブラウザによっては SELECT の前に "*" をコピーしてしまいますが、これは削除して実行する必要があります。
出力結果をウィキテキストに変換するには次の命令を使います。 s/(\ |\|)//g;s/^/*\[\[/;s/$/\]\]/
以下のクエリのほとんどはページ名だけを出力し、名前空間は含みません。名前空間は cur_namespace フィールドに数字で記録されています。通常の記事がある名前空間は 0 です。それ以外については Help:名前空間を参照してください。
MySQL Workbench はMySQLクエリのためのグラフィカルユーザインタフェースを備えています。使用するには、インストール後、コマンドラインで MySQL サーバーを起動し、このブラウザをその上で走らせます。

クエリの例[編集]

出力結果を限定する[編集]

回答の上限を設定する

SELECT ... LIMIT 20

オフセットを追加する。以下の例では101番目から20個を表示

SELECT ... LIMIT 100, 20

新規利用者を探す[編集]

ウェルカムメッセージを投げるのに便利な、新規利用者を20人探して表示します。このクエリでは実際にページの編集をした利用者だけをリストアップします。

SELECT user_name, COUNT(*) FROM user, cur WHERE user_id=cur_user 
GROUP BY user_id ORDER BY user_id DESC LIMIT 20

スタブ記事を探す[編集]

短い記事を探すのに使います。以下のクエリでは50バイト未満のページをリストアップします。実行に2秒ほどかかります。

SELECT cur_title FROM cur WHERE cur_is_redirect=0 AND LENGTH(cur_text)<50 AND cur_text LIKE '%see%'

適切でないリンクを探す[編集]

二重リダイレクトは以下のクエリで探せます。（実行時間は約4秒）

SELECT l_from,l_to,cb.cur_text AS rt,cb.cur_title AS ti FROM links,cur AS ca, cur AS cb WHERE ca.cur_is_redirect=1 AND cb.cur_is_redirect=1 AND l_to=cb.cur_id AND l_from=ca.cur_title LIMIT 250

l_from ははじめの、ti は2段目のリダイレクトになります。rt には最終的に行き着く記事があります。

自己リンクを含むページ。（24秒ほどかかります！！）

SELECT cur_title FROM cur,links WHERE cur_is_redirect=0 AND cur_namespace=0 AND l_from=cur_title AND l_to=cur_id LIMIT 20

リンク先がないリダイレクトを探す（2-4秒ほど）

SELECT cur_title FROM cur,brokenlinks WHERE bl_from=cur_id AND cur_is_redirect=1

強調を含まないページ[編集]

最初のセンテンスに強調がないページを探します。

SELECT cur_title FROM cur WHERE cur_namespace=0 AND cur_is_redirect=0 AND cur_text NOT LIKE "%'''%" AND cur_text NOT LIKE "%<b>%" LIMIT 50

強調を含まないページで、曖昧さ回避のページにリンクしていないページ。（要調整）

SELECT cur_title FROM cur WHERE cur_namespace=0 AND cur_is_redirect=0 AND cur_text NOT LIKE "%'''%" AND cur_text NOT LIKE "%<b>%" AND cur_text NOT LIKE "%disambig%" LIMIT 50

サブページ[編集]

"/"を項目名に含むもの。多くはサブページです。

SELECT cur_title FROM cur WHERE cur_namespace=0 and cur_title like "%/%" and cur_is_redirect=0

統計を抽出する[編集]

1,500キャラクタ以上をもつ記事数をカウントする。

SELECT COUNT(*) FROM cur WHERE LENGTH(cur_text)>1500 AND cur_namespace=0

ブロック関連[編集]

ブロックされているIPアドレス、最初のブロックのログを日付順で表示。

SELECT DISTINCT ipb_address, COUNT(*) AS times, MIN(ipb_reason) AS reason, user_name AS blocker,  DATE_FORMAT(MIN(ipb_timestamp), '%b %e %Y %k:%i') AS date  FROM ipblocks, user WHERE user_id=ipb_by GROUP BY ipb_address ORDER BY ipb_timestamp

it's[編集]

"it's" を含むページを表示。この種のクエリは時間がかかるので、表示数を限定した方がよい。

SELECT cur_title from cur where cur_namespace=0 and cur_text regexp "[[:<:]]it's[[:>:]]"

荒らし[編集]

荒らしを行っている利用者やIPアドレスを発見した時に、修正漏れを発見するために、最終投稿者が当該利用者（IPアドレス）であるページを探す。name には利用者名またはIPアドレスを入れる。

SELECT cur_title FROM cur WHERE cur_user_text="name" LIMIT 100

管理者を探す[編集]

SELECT CONCAT("*[[User:",user_name,"|",user_name,"]]") from user where user_rights != "" ORDER BY user_name LIMIT 100