Benutzer:MichaelDiederich/SQL

Da ich bald einigen Wikipedianern die Möglichkeit bieten möchte, SQL Abfragen wieder durchzuführen, sammele ich alle wichtigen SQL-Statements.

Ergebnisse der Abfragen unter Benutzer:MichaelDiederich/SQL-Abfragen.

HTML

HTML Tags finden

SELECT CONCAT('[[', cur_title, ']]') AS Titel
FROM cur
WHERE cur_namespace=0
AND cur_is_redirect=0
AND cur_title>'A' AND cur_title<'Z' /* anpassen */
/* Tags anpassen */
AND ( cur_text RLIKE '.*<[bi]>.*'
OR cur_text LIKE '%<em>%'
OR cur_text LIKE '%<strong>%'
OR cur_text LIKE '%<hr%>%'
OR cur_text LIKE '%<pre>%'
OR cur_text LIKE '%<tt>%'
OR cur_text RLIKE '.*<h[1-5]>.*'
OR cur_text LIKE '%<table%>%'
OR cur_text LIKE '%<ul>%'
OR cur_text LIKE '%<blockquote>%'
OR cur_text LIKE '%<ol%>%'
OR cur_text LIKE '%<a%>%' )
ORDER BY cur_title ASC, LENGTH(cur_text) DESC
LIMIT 0,100;

Zahlenangaben ohne nbsp finden (z.B. 10 cm)

SELECT CONCAT('[[', cur_title, ']]') AS Titel
FROM cur
WHERE cur_namespace=0
AND cur_is_redirect=0
AND cur_title>'A' and cur_title<'Z' /* anpassen */
AND cur_text RLIKE '[0-9] [afpnµmcdhkMGTPE]?([msAKNJWCVFTH]|(kg|mol|cd|rad|Hz|Pa|Wb|Bq|Gy|Sv))'
ORDER BY cur_title ASC
LIMIT 0,100;

Links

>5 Weblinks

SELECT CONCAT('[[', cur_title, ']]') AS Titel
FROM cur
WHERE cur_namespace=0
AND cur_is_redirect=0
AND cur_title>'A' AND cur_title<'Z' /* anpassen */
AND cur_text RLIKE '(http:.*){6,}'  /* Anzahl anpassen */
ORDER BY cur_title ASC, LENGTH(cur_text) DESC
LIMIT 0,100;

verlinkte Begriffsklärungen

SELECT CONCAT("[[",c.cur_title,"]]",REPEAT(".",25-LENGTH(c.cur_title))) AS BKS, 
        COUNT(l.l_to) AS Anzahl_Links,"<br/>"
FROM cur c, links l
WHERE c.cur_text LIKE "%{{Begriffskl%"
AND l.l_to=c.cur_id
AND c.cur_namespace=0
GROUP BY l.l_to
ORDER BY Anzahl_Links DESC
LIMIT 100;

Nicht vorhandene Artikel

SELECT CONCAT("[[",bl_to,"]]",REPEAT(".",40-LENGTH(bl_to))) AS Artikel, 
   COUNT(bl_from) AS Anzahl_Links_darauf,"<br/>"
FROM brokenlinks
WHERE bl_to NOT LIKE "Benutzer:%"
GROUP BY bl_to
ORDER BY Anzahl_Links_darauf DESC, bl_to ASC
LIMIT 200

Nicht vorhandene Artikel mit Sonderzeichen am Anfang

SELECT CONCAT('[[',CONVERT(bl_to USING latin1),']]') AS Artikel, COUNT(bl_to) AS Anzahl, "<br/>"
FROM brokenlinks
WHERE CONVERT(bl_to USING latin1) NOT REGEXP '^[[:alnum:]]'
AND bl_to < 1
GROUP BY bl_to
ORDER BY Anzahl ASC, bl_to ASC

Häufig verlinkte Redirects

SELECT CONCAT("[[",c.cur_title,"]]",REPEAT(".",25-LENGTH(c.cur_title))) AS Redirect, 
        COUNT(l.l_to) AS Anzahl_Links_auf_Redirect,"<br/>"
FROM cur c, links l
WHERE l.l_to=c.cur_id
AND c.cur_namespace=0
AND c.cur_is_redirect=1
GROUP BY l.l_to
ORDER BY Anzahl_Links_auf_Redirect DESC
LIMIT 100;

Verwaiste Diskussionsseiten

SELECT DISTINCT CONCAT('[[Diskussion:',bl_to,']]'), cur_timestamp
FROM cur, brokenlinks
WHERE cur_title = bl_to AND cur_namespace=1 AND cur_is_redirect=1
ORDER BY cur_timestamp LIMIT 1000

Artikel verlinkt auf sich selber

SELECT CONCAT('[[',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 100

Defekte Links

Diese Liste enthält Links auf nichtexistente Artikel und existente Artikel mit einem oder zwei Zeichen weniger.

 SELECT DISTINCT
        CONCAT('* [[Spezial:Whatlinkshere/',bl_to,'|',bl_to,']] -> [[',cto.cur_title,']]')
        AS data
   FROM brokenlinks, cur AS cto, cur AS cfrom
  WHERE cto.cur_namespace = 0
    AND cfrom.cur_namespace = 0 and cfrom.cur_id = bl_from
    AND LENGTH(bl_to)>=4
    AND bl_to REGEXP '[a-z]'
    AND cto.cur_title = SUBSTRING(bl_to, 1, LENGTH(bl_to)-1)
  ORDER BY SUBSTRING(bl_to, LENGTH(bl_to),1), bl_to;

zweite Variante: [foo (typ|foo]

SELECT cur_title
  FROM cur
 WHERE cur_namespace=0
   AND cur_is_redirect=0
   AND cur_text RLIKE '\\[[a-z ]+\\([^\)]+\\|'
 ORDER BY cur_title ASC
 LIMIT 100

Wanted Pages

SELECT bl_to AS Artikel,
 CONCAT (COUNT(DISTINCT bl_from), ' Verweise, ') AS Linkanzahl
   FROM brokenlinks
  WHERE bl_to LIKE 'A%'
  GROUP BY bl_to
 HAVING Linkanzahl > 9
  ORDER BY bl_to ASC
  LIMIT 100

Einmalig gewünschte Seiten

Links auf nicht vorhandene Seiten finden, die nur genau einmal vorkommen. Dahinter verbergen sich viele Tippfehler oder Alternativschreibweisen, die korrigiert werden sollten - vielleicht existiert der verlinkte Artikel bereits. Der Bereich muss angepasst werden, hier von "B" bis "C".

SELECT cur_title, bl_to, COUNT(DISTINCT bl_from) AS nlinks
  FROM brokenlinks, cur
 WHERE cur_namespace=0
   AND bl_to > 'B'
   AND bl_to < 'C'
   AND bl_from=cur_id
 GROUP BY bl_to
HAVING nlinks = 1
 ORDER BY bl_to
 LIMIT 500

falsche Redirects

Stubs finden: Kürzer als 50 Zeichen und enthält den Text "Siehe" oder "http":

SELECT cur_title
  FROM cur
 WHERE cur_is_redirect=0
   AND cur_namespace=0
   AND LENGTH(cur_text)<150
   AND (cur_text LIKE '%http%' OR cur_text LIKE '%siehe%')
 ORDER BY cur_title ASC
 LIMIT 100

Links auf Benutzerseiten

Artikel in denen auf eine Benutzer-Seite verlinkt wird.

SELECT cur_title
  FROM cur
 WHERE cur_namespace = 0
   AND cur_text LIKE  '% [[Benutzer:%'
 LIMIT 10

Benutzer

Benutzerliste

SELECT CONCAT('*[[Benutzer:', user_name, '|', user_name, ']] \'\'(User-ID ', user_id,
')\'\': ', '\'\'\'Rechte\'\'\': ', user_rights)
FROM user
ORDER BY LENGTH(user_rights) DESC
LIMIT 0,100;

Benutzerrechte ändern

UPDATE user
SET user_rights='sysop,bureaucrat,developer'
WHERE user_name='Benutzername'
LIMIT 1;

Unbenutzten Account löschen

DELETE FROM user
WHERE user_name='Benutzername'
LIMIT 1;

Die 50 neuesten Benutzer

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

Die 100 aktivsten Wikipedianer

SELECT cur_user_text, COUNT(*) AS count
  FROM cur
 WHERE cur_user != 0
 GROUP BY cur_user
 ORDER BY count DESC
 LIMIT 100

Format

Keine Leerzeilen

SELECT cur title
FROM cur
WHERE cur namespace=0 AND cur is redirect=0 AND cur text not LIKE '%\r\n\r\n%'
LIMIT 100

Tabelle am Anfang

SELECT cur_title
FROM cur
WHERE cur_namespace=0 AND cur_is_redirect=0 AND cur_text RLIKE '^<table'
LIMIT 100

Tabelle am Anfang und Ende

SELECT cur_title
FROM cur
WHERE cur_namespace=0 AND cur_is_redirect=0 AND cur_text RLIKE '^<table' AND cur_text RLIKE '/table>$'
ORDER BY cur_title ASC
LIMIT 100

Unbeantworte Fragen

Folgende Diskussionsseiten enthalten keine Leerzeilen, dafür aber ein Fragezeichen und somit möglicherweise eine unbeantwortete Frage:

SELECT cur_title,LENGTH(cur_text)
FROM cur
WHERE cur_namespace=1 AND cur_is_redirect=0 AND cur_text LIKE '%?%' AND cur_text not LIKE '%\r\n\r\n%' AND cur_text not LIKE '%\n\n%' AND cur_text NOT LIKE '%:%'
ORDER BY cur_title ASC
LIMIT 100

Bilder

Ohne Lizenzen

SELECT cur_user_text, cur_title
FROM cur
WHERE cur_namespace=6 AND 
cur_text NOT LIKE '%public%' AND 
cur_text NOT LIKE '%domain%' AND 
cur_text NOT LIKE '%pd%' AND 
cur_text NOT LIKE '%gemeinfrei%' AND 
cur_text NOT LIKE '%gnu%' AND 
cur_text NOT LIKE '%fdl%' 
ORDER BY cur_user_text;

Verwaiste Bilder

todo

Vandalsimus

mgl. Tastaturtests

Artikel mit mindestens 9 Konsonanten oder 7 Vokalen hintereinander oder mit Wörtern mit mindestens 40 Buchstaben

SELECT cur_title, cur_text
  FROM cur
 WHERE cur_is_redirect=0
   AND cur_namespace=0
   AND cur_text RLIKE '[B-DF-HJ-NP-TV-XZb-df-hj-np-tv-xz]{9,}|[AEIOUYaeiouy]{7,}|[A-Za-z]{40,}'

subtiler:

SELECT cur_title 
  FROM cur 
  WHERE cur_is_redirect=0 
    AND cur_namespace=0
    AND cur_text NOT LIKE '%\'\'\'%'
    AND cur_title NOT LIKE '%Flagge%'
    AND LENGTH(cur_text)<180
  ORDER BY LENGTH(cur_text) ASC
  LIMIT 500

Fast leere Artikel

SELECT cur_title 
  FROM cur 
  WHERE cur_is_redirect=0 
    AND cur_namespace=0
    AND cur_is_redirect=0
    AND LENGTH(cur_text)<50
  ORDER BY LENGTH(cur_text) ASC
  LIMIT 100

Sonstiges

Liste sämtlicher Lemmata

SELECT cur_title
FROM cur
WHERE cur_namespace=0 into outfile "/tmp/lemmata.txt";

Artikel mit arabischem Inhalt

SELECT CONCAT('[[',cur_title,']]')
FROM cur
WHERE cur_namespace=0 AND cur_is_redirect=0 AND cur_text LIKE '%arab.%'
ORDER BY cur_title ASC
LIMIT 500

Löschanträge

Syntax überarbeiten?

SELECT CONCAT('[[',cur_title,']]') as Artikel, CONCAT('[[Diskussion:',cur_title,']]') as Diskussion, DATE_FORMAT(cur_timestamp, '%d.%m.%Y, %h:%m') as 'Letzte Änderung', CONCAT('[[Benutzer Diskussion:',cur_user_text,'|',cur_user_text,']]') as 'Letzter Bearbeiter'
FROM cur
WHERE cur_is_redirect=0 AND cur_namespace=0 AND cur_text LIKE '%msg:Löschantrag%'
ORDER BY cur_timestamp ASC
LIMIT 250

zweite Variante, auch syntax noch überarbeiten:

SELECT CONCAT('[[',cur_title,']]') as Artikel, cur_timestamp as 'Letzte Änderung', cur_user_text as 'Letzter Bearbeiter'
FROM cur
WHERE cur_is_redirect=0 AND cur_namespace=0 AND cur_text LIKE '%msg:Vfd%'
ORDER BY cur_timestamp ASC
LIMIT 150

Kurze Artikel

SELECT CONCAT ('[[',cur_title,']]') AS Artikel,
CONCAT (LENGTH(cur_text),' Bytes') AS Laenge,
CONCAT (' ') AS Bearbeitungshinweis
  FROM cur
 WHERE cur_is_redirect = 0
   AND cur_namespace = 0
   AND LENGTH(cur_text) < 200
   AND cur_title NOT REGEXP '/[A-Z]$'
   AND cur_title NOT LIKE '%/Zahl'
   AND cur_title NOT LIKE '%/0-9'
   AND cur_title NOT LIKE 'Liste_%'
   AND cur_title NOT LIKE 'Fotoglossar/%'
   AND cur_text NOT LIKE '%Begriffsklaerung%'
   AND cur_text NOT LIKE '%Begriffsklärung%'
   AND cur_text NOT LIKE '%Begriffsklärung%'
   AND cur_text NOT LIKE '%URV%'
   AND cur_text NOT LIKE '%Falschschreibung%'
   AND cur_text NOT LIKE '{{Wiktionary}}'
 ORDER BY LENGTH(cur_text) ASC, cur_timestamp ASC
 LIMIT 200;

zweite Variante: (weniger als 100 Zeichen, keine Jahreszahlen) der letzten 5 Tage

SELECT cur_title, cur_text
  FROM cur
 WHERE cur_is_redirect=0
   AND cur_namespace=0
   AND LENGTH(cur_text)<100
   AND cur_timestamp > (NOW() - INTERVAL 5 DAY)+0

Anzahl der kurzen Artikel

(mit mindestens 1500 Bytes ermitteln)

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

Rechtschreibfehler

SELECT cur_title
  FROM cur
  WHERE cur_namespace=0
    AND cur_is_redirect=0
    AND (cur_text LIKE '%d. h.%' OR cur_text LIKE '%z. b.%')
    AND cur_text NOT LIKE '%schweizbezogen%'
  ORDER BY cur_title ASC
  LIMIT 250

SELECT cur_title
  FROM cur
  WHERE cur_namespace=0
    AND cur_is_redirect=0
    AND (cur_text LIKE '% .%' OR cur_text LIKE '% ,%' OR cur_text LIKE '% )%'  OR cur_text LIKE '%( %')
  ORDER BY cur_title ASC
  LIMIT 250

SELECT concat('[[',cur_title,']]')
FROM cur
WHERE cur_namespace=0 AND cur_is_redirect=0 AND cur_text LIKE '% groß%'
ORDER BY cur_title ASC
LIMIT 500

Benutzerunterseiten

SELECT cur_title
  FROM cur
  WHERE cur_is_redirect=0
    AND cur_namespace=2
    AND cur_title LIKE '%/%'
  ORDER BY cur_title ASC

Geschützte Seiten

 SELECT '[[', cur_namespace as Namespace, cur_title as Artikel, ']]' 
    FROM cur 
    WHERE cur_restrictions = "sysop" 
    ORDER by cur_Namespace, cur_title ASC 
    LIMIT 800

Anzahl Artikel mit Bildern

SELECT count(*) 
   FROM cur 
   WHERE cur_text LIKE "%[[Bild:%"

Zu lange Titel

SELECT concat('[[',cur_title,']]')
FROM cur
WHERE LENGTH(cur_title)>49
AND cur_namespace=0
ORDER by length(cur_title) DESC, cur_title ASC
LIMIT 100;

Einmalig editiert

Dies ist eine Liste der ältesten Artikel, die nach ihrer Erstellung noch nicht editiert wurden.

select concat(
'|-| [[',
cur_title,
']] || ',
length(cur_text),
' Bytes || ',
substring(cur_timestamp,7,2),'.',
substring(cur_timestamp,5,2),'.',
substring(cur_timestamp,1,4),
' || '
)
from cur
where
cur_namespace = 0
and cur_is_redirect = 0
and cur_is_new = 1
order by cur_timestamp asc
limit 100;

Doppeleinträge

Aufgelistet sind verschiedene Einträge der cur-Tabelle, die identische Namen haben, mitsamt der jeweiligen id-Nummer und der Bearbeitungszeit (man beachte die Zeitzonen-abhängige Online-Anzeige im Gegensatz zur UTC-Zeit der Datenbank).

SELECT DISTINCT
 c1.cur_namespace,
 c1.cur_title,
 c1.cur_id,
 c1.cur_timestamp
FROM
 cur c1,
 cur c2 USE INDEX (name_title_timestamp)
WHERE
 c1.cur_namespace = c2.cur_namespace
 AND c1.cur_title = c2.cur_title
 AND c1.cur_id <> c2.cur_id
ORDER BY c1.cur_namespace, c1.cur_title, c1.cur_timestamp;

Fehlende Artikel mit zu langem Titel

Diese Seite listet Seiten, die auf fehlende Artikel mit sehr langem Titel (mindestens 150 Zeichen) verweisen. Die Software kann nur Titel mit einer Länge von bis zu 255 Zeichen verarbeiten (ob es eine Längenbeschränkung für URLs gibt, weiß ich noch nicht), so dass zu den längsten Links keine entsprechenden Artikel erzeugt werden können.

SELECT cur_namespace, cur_title, replace(bl_to,'_',' '), length(bl_to) as len
FROM brokenlinks STRAIGHT_JOIN cur
WHERE length(bl_to)>=150
and (cur_id = bl_from)

Falscher Namensraum

Liste von Datenbankeinträgen, deren Titel einen Namensraum als Anfang hat, mit Ausnahme von Kategorien.

Solche Artikel im Artikel-Namensraum 0 sind nicht erreichbar, da die Software nach einem Eintrag im entsprechenden Namensraum sucht.

In anderen Namensräumen sind diese Einträge problemlos erreichbar, und bei den Kategorien (Namensraum 14 und 15, hier nicht aufgelistet) ist das so beabsichtigt.

SELECT concat('|-\n|',
 cur_namespace, ' || ', cur_title, ' || ', cur_id, ' || ', cur_timestamp, ' || ', LENGTH(cur_text))
FROM cur
WHERE
 (cur_namespace < 14)
 AND (
 cur_title LIKE 'Diskussion:%'
 OR cur_title LIKE 'Benutzer:%'
 OR cur_title LIKE 'Benutzer_Diskussion:%'
 OR cur_title LIKE 'Wikipedia:%'
 OR cur_title LIKE 'Wikipedia_Diskussion:%'
 OR cur_title LIKE 'Bild:%'
 OR cur_title LIKE 'Bild_Diskussion:%'
 OR cur_title LIKE 'MediaWiki:%'
 OR cur_title LIKE 'MediaWiki_Diskussion:%'
 OR cur_title LIKE 'Vorlage:%'
 OR cur_title LIKE 'Vorlage_Diskussion:%'
 OR cur_title LIKE 'Hilfe:%'
 OR cur_title LIKE 'Hilfe_Diskussion:%'
 OR cur_title LIKE 'Kategorie:%'
 OR cur_title LIKE 'Kategorie_Diskussion:%'
 )
ORDER BY cur_namespace, cur_title
LIMIT 100;
and (cur_namespace <> 4 or cur_title not like 'Archiv:L%')
ORDER BY len DESC
LIMIT 100;

Viele Ausrufezeichen

Kurze Artikel, die 4 oder mehr Ausrufezeichen enthalten. Neutralisierte Artikel bitte entfernen.

SELECT cur_title
FROM cur
WHERE cur_namespace=0 AND cur_text REGEXP \"([a-z]\!.*){4,}\" AND cur_text NOT LIKE '%{|%!!%|}%' AND cur_text NOT LIKE '%<!--sic!-->%' AND cur_title NOT LIKE '%!%'
ORDER BY LENGTH(cur_text) ASC
LIMIT 200

Interwikilinks

Interwikilinks einer bestimmten Sprache (Sprachkürzel kommt dreimal vor!)

SELECT cur_title,
 CONCAT ('<a href=http://sv.wikipedia.org/wiki/',
REPLACE (@l:=SUBSTRING(cur_text,5+@p:=INSTR(cur_text, '[[sv:'),
 LOCATE (']]',cur_text, @p)-5-@p),' ','_'),'>',@l,'</a>') AS Interlink
   FROM cur
  WHERE cur_is_redirect=0
    AND cur_namespace=0
    AND cur_text LIKE '%[[sv:%'
  LIMIT 20

Hilfe

Namespace-Codes:

0 (normaler Artikel)

1 Diskussion (Diskussionsseite eines Artikels)

2 Benutzer

3 Benutzer_Diskussion

4 Wikipedia

5 Wikipedia_Diskussion

6 Bild

7 Bild_Diskussion

8 MediaWiki

9 MediaWiki_Diskussion

10 Vorlage

11 Vorlage_Diskussion

12 Hilfe

13 Hilfe_Diskussion

14 Kategorie

15 Kategorie_Diskussion

Eine Beschreibung der Datenbanken findet man hier