Wikiforrás:Adatbázis lekérdezések

Lehetőségek SQL lekérdezés futtatására

Letöltheted az adatbázist a download.wikipedia.org és a saját gépeden futtathatod a lekérdezéseket. (A GFDL licenc az adatbázisra is vonatkozik!)
A www.wikisign.org oldalon keresztül telepítés nélkül is tudsz lekérdezéseket futtatni.
Egyes fejlesztők tudnak lekérdezéseket futtatni az éles adatbázison. (Kellően indokolt esetben) a m:Requests for queries oldalon kérheted meg őket, hogy futtassák le a lekérdezésedet.
Ha nem tudsz egyedül megfogalmazni vagy futtatni egy lekérdezést, a Wikipédia:SQL futtatási kérések oldalon kérhetsz segítséget.

Megjegyzések

Ha a vágólap segítségével másolsz ki egy lekérdezést erről az oldalról, bizonyos böngészők az elejére egy * jelet tesznek. Ezt ki kell törölni! (A lekérdezésnek a SELECT szóval kell kezdődnie.)
A legtöbb lenti lekérdezés szócikkek címeit adja vissza. Ezek nem tartalmazzák a névteret! A névtér az adatbázisban egy numerikus kódként szerepel, a cur_namespace mezőben. A kódokat itt találod meg.
Windows alatt a Mysql által visszaadott listát a legegyszerűbben úgy tudod sima szöveggé alakítani, hogy elmented (exportálod) az eredményt Excel fájlba, betöltöd Excel alá, és ott elmented Unicode text formátumban.
Egy regexp a sima szöveg wiki listává alakítására:

s/(\ |\|)//g;s/^/*\[\[/;s/$/\]\]/

Gyorstalpaló lekérdezések futtatásához MySQL-en Windows alatt

Az előkészületek körülbelül egy órát vesznek igénybe szélessávú kapcsolattal. Ezeket csak egyszer, a legelső alkalommal kell elvégezni:

Töltsd le a MySQL-t is a mysql.com-ról. Az ajánlott változat jelenleg (2005. július 19.) a 4.1-es változat Essential csomagja.
Telepítsd a MySQL-t.
Töltsd le és telepítsd a MySQL Query Browsert (ez nem kötelező, de hasznos). A Query Browser egy grafikus felhasználói felület a MySQL lekérdezésekhez. A szerver címének 'localhost'-ot adj meg, semmi mást nem kell átállítani.
Töltsd le az mwdumper programot.
(Bízz benne, hogy van JRE a gépen... itt egyszer majd részletes leírás lesz.)

A továbbiakat minden alkalommal meg kell ismételni, amikor frissíteni akarod az adatbázist:

Hozd létre a wikipedia adatbázist (például add ki a következő parancsot a MySQL parancssorából, vagy, ha telepítetted, a MySQL Query Browserből):

CREATE DATABASE wikipedia;

(Ha már létezik ilyen néven adatbázis, töröld ki előbb, vagy válassz más nevet.)

Töltsd le azt az adatbázis dumpot a download.wikimedia.org-ról, amin az SQL lekérdezéseket futtatni akarod. (Ez általában a legfrissebb pages_current fájl, ha csak a lapok aktuális állapotaira vagy kíváncsi; és a legfrissebb pages_full, ha a korábbiakra is.)
Tömörítsd ki a letöltött adatbázist vagy adatbázisokat. A gz és a 7z kiterjesztésűekhez a 7-zip-et. A bz2 kiterjesztésű fájlokat nem szükséges kitömörítened; ha valamiért mégis szeretnéd, használd a bzip tömörítőt. A gz és bz2 fájlokat a WinZip programmal vagy (a megfelelő pluginek birtokában) Total Commanderrel is kitömörítheted. VIGYÁZAT: a kitömörített fájl sokkal nagyobb lesz, mint az eredeti. Az aktuális verzióhoz néhány száz mega, a teljes laptörténethez több giga hely kell!)
Ha a kitömörített fájl nem sql, hanem xml kiterjesztésű, konvertáld sql-lé az alábbi paranccsal:

mwdumper fájlnév.xml --output=file:fájlnév.sql --format=sql:1.4

Ahol fájlnév a fájl neve (pl. pages_full). Ha a fájl bzip tömörítésű, kitömörítés nélkül is konvertálhatod:

mwdumper fájlnév.bz2 --output=file:fájlnév.sql --format=sql:1.4

Állítsd vissza az adatbázist a letöltött SQL fájlból. Indítsd el a Windows parancssort (például Start Menü > Futtatás... > cmd vagy Windows billentyű+R > cmd), majd add ki a következő parancsot:

mysql -uroot -pjelszavad -D wikipedia --default-character-set=utf8 <C:\ahol\van\fájlnév.sql

C:\ahol\van\fájlnév.sql helyett értelemszerűen a letöltött és kicsomagolt dump fájl elérési útját és nevét, jelszavad helyett a MySQL root (adminisztrátori) jelszavát (amit telepítéskor megadtál) használd.

Várd meg, amíg a MySQL végrehajtja az adatbázis visszaállítását (ez jó néhány percbe beletelhet).
Ha a MySQL elkészült a visszaállítással, kezdheted futtatni a lekérdezéseket. Kezdetnek próbálkozz meg néhány példával erről az oldalról. Egyszerűen csak gépeld be őket soronként, a sorok végén entert ütve. A legutolsó sort pontosvesszővel zárd.

Ha bármi problémád vagy kérdésed van az itt leírtakkal, jelezd a vitalapon.

A lekérdezések szintaxisa

Egy egyszerű lekérdezés az alábbi formájú:

SELECT oszlopnevek FROM táblanév;

A táblanév többnyire cur; az oszlopnevek az oszlopnevek vesszőkkel elválasztott listája (ha minden oszlopra kíváncsiak vagyunk, írhatunk *-ot a helyükre).

A legegyszerűbb lekérdezés tehát így néz ki:

SELECT * FROM cur;

Az összes szócikk címének és névterének lekérdezése:

SELECT cur_namespace, cur_title FROM cur;

Korlátozás

Az eredmények számának korlátozása:

SELECT * FROM cur LIMIT 20;

Ofszetet is megadhatsz:

SELECT * FROM cur LIMIT 100, 20;

a 101.-120. rekordokat fogja visszaadni.

Rendezés

Az eredmények rendezése:

SELECT * FROM cur ORDER BY cur_title ASC LIMIT 20;

cím szerint növekvő abc-sorrendbe rendezi az eredményeket. (ASC helyett DESC-et írva a rendezés csökkenő lesz.)

Feltételek

Keresési feltételeket a WHERE kulcsszóval adhatunk meg.

Azok a cikkek, amiket Példa felhasználó szerkesztett utoljára:

SELECT cur_id, cur_namespace, cur_title FROM cur WHERE cur_user_text = 'Példa';

A feltételben használhatóak a szokásos matematikai jelölések és az AND (és) és OR (vagy) műveletek.

SELECT cur_id FROM cur WHERE cur_restrictions <> 0 AND cur_namespace=4;

kilistázza a Wikipédia névtérbeli védett cikkeket.

A szövegben a LIKE kulcsszóval lehet keresni:

SELECT cur_id FROM cur WHERE cur_text LIKE '%{{azonnali}}%';

kilistázza az azonnali törlés sablont tartalmazó cikkeket.

Sorrend

A fentieket az alábbi sorrendben (és csak abban) lehet összekombinálni:

SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ...

Fontosabb táblák és oszlopok

Részletesen lásd: m:Help:Database layout vagy Wikipedia database schema

cur

A cikkek (a dump készítésének pillanatában) aktuális állapotát tartalmazza.

cur_id - az elsődleges kulcs, egy sorszám, ami egyértelműen azonosítja a cikket.
cur_name - a cikk címe. (Nem egyértelmű: pl. a Szavazás, Vita:Szavazás, Wikipédia:Szavazás, Wikipédia vita:Szavazás oldalak mindegyikének "Szavazás" a címe.)
cur_namespace - a névtér kódja, amiben a cikk van.
cur_text - a cikk szövege.
cur_user - a cikk utolsó szerkesztését végző felhasználó sorszáma.
cur_user_text - a cikk utolsó szerkesztését végző felhasználó neve.
cur_timestamp - a cikk utolsó szerkesztésének ideje
cur_restricitions - ha nem nulla, a cikk le van védve.
cur_is_redirect - ha nem nulla, a cikk átirányítás.

image

A képeket és más feltöltött médiafájlokat tartalmazza. (Az összes adat a fájl legutolsó változatára vonatkozik!)

img_name - a feltöltött fájl neve.
img_size - a fájl mérete.
img_description - a kép leírása.
img_user - a feltöltő sorszáma.
img_user_text - a feltöltő neve.
img_timestamp - a feltöltés időpontja.

links, brokenlinks, categorylinks, imagelinks

Rendre a létező, illetve a nemlétező cikkekre mutató redirekteket, a kategória- és a képhivatkozásokat tartalmazzák. (Megjegyzés: a [[:Kategória:abc]] alakú hivatkozás sima linknek számít.)

l_from, bl_from, cl_from, il_from - a cikk azonosítója (cur_id), amiben a redirekt van.
l_to, bl_to, cl_to, il_to - a cikk azonosítója, amire a redirekt mutat.

Példa lekérdezések

(...a téma, vagy lap további része lefordítandó...)

Queries to find new users

Here's a couple of queries to find the 20 most newly created users - useful to find people to give welcome messages. It only counts users that have made edits (most users who create a login never make a single contribution and are just passing by, so it's not really worth welcoming them).

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

The above query gives the user names of the 20 most recently created users and the number of edits they have made. The query is not perfect - only edits that have not been 'overwritten' by other edits are counted here. To do the same thing looking only at 'overwritten' edits use:

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

Queries to find stub articles

For short pages containing "see" (takes about 2 seconds):

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

finds all pages with less than 50 chars.

Queries to find articles with one edit (new)

SELECT cur_title FROM cur WHERE cur_is_new AND cur_namespace=0;

Finds all pages that have only been edited once.

Queries to find inappropriate links

Double Redirects

Article namespace:

SELECT ca.cur_namespace, ca.cur_title FROM cur ca, cur cb, links l WHERE ca.cur_is_redirect=1 AND cb.cur_is_redirect=1 AND l.l_from=ca.cur_title AND l.l_to=cb.cur_id AND ca.cur_namespace=0 LIMIT 250

Talk namespace:

SELECT ca.cur_namespace, ca.cur_title, FROM cur ca, cur cb, links l WHERE ca.cur_is_redirect=1 AND cb.cur_is_redirect=1 AND l.l_from=CONCAT("Talk:",ca.cur_title) AND l.l_to=cb.cur_id AND ca.cur_namespace=1 LIMIT 250

These queries are slow. You may have to reduce the limit for them to work.

Other

List all articles that link to a file on http://meta.wikipedia.com (takes 2-3 seconds):

SELECT cur_title FROM cur WHERE cur_text LIKE "%http://meta.wikipedia.com/upload/%" AND cur_namespace=0

To find articles that link to jpegs, both external and internal, with the old non-[[Image:]] style (this isn't perfect, it also finds a few other pages):

SELECT cur_title FROM cur WHERE cur_text LIKE "%http://%.jp%g%" AND cur_namespace=0

For articles containing a link to itself (!!24 seconds!!)

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

For redirects to non-existing pages (note: when a page is found that is redirecting correctly, it usually means that there is text going with the redirect, 2-4 seconds):

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

Query to find articles without bold markings

Which means they don't have bold titles in the first sentence!

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

Query to find articles without bold markings that are not disambiguation pages

Same as above only removes disambiguation pages which don't need bolds. 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

Query to find sub-page articles

This finds all articles containing a "/" character; most of these are subpages.

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

Queries to give statistics

For a count of how many articles are more than 1500 characters long, use:

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

Queries about blocked users

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

returns the blocked IP addresses, and the first entry for the blocking (some IP addresses have been blocked by multiple people), ordered by date.

This is mostly unnecessary (but may be of interest as an example query), as the Special:Ipblocklist returns similar information.

it's...

Find all pages with "it's" for spellchecking purposes. WARNING: takes a long time. May want to apply a limit (see above)

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

Vandalism

When a certain IP-number or user has been found to be vandalising, it might be that some of their problems may already have been solved by someone else and others not. The following query shows those pages which the vandal (or otherwise problematic user) was the last to edit:

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

If the vandal is a registered user, use their Username for name, if not, use their IP-number.

Query to find sysops/developers

SELECT user_name, user_rights from user where user_rights != "" and user_rights != "bot" ORDER BY user_name LIMIT 100

Images

The queries here produce valid links when the results are copied and pasted into a page. Not very pretty, but functional. With 70,000 images in en, these take about 60 seconds to run the first time.

All images by a given contributor (including old versions)

select distinct concat('<br>[[:Image:', img_name, ']]') as name
from image where img_user_text='USER_NAME_HERE'
union
select distinct concat('<br>[[:Image:', oi_name , ']]') as name
from oldimage where oi_user_text='USER_NAME_HERE'
order by name asc;

All images by a given contributor (only current version)

select distinct concat('<br>[[:Image:', img_name, ']]') as name
from image where img_user_text='USER_NAME_HERE'
order by name asc;

All image pages edited by a given contributor

select distinct concat('<br>[[:Image:', cur_title, ']]') as title
from cur where cur_namespace=6 and cur_user_text='USER_NAME_HERE'
union
select distinct concat('<br>[[:Image:', old_title , ']]') as title
from old where old_namespace=6 and old_user_text='USER_NAME_HERE'
order by title asc;