Benutzer:MichaelDiederich/SQL

aus Wikipedia, der freien Enzyklopädie
Zur Navigation springen Zur Suche springen

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 Tags finden

[Bearbeiten | Quelltext bearbeiten]
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)

[Bearbeiten | Quelltext bearbeiten]
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;
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

[Bearbeiten | Quelltext bearbeiten]
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

[Bearbeiten | Quelltext bearbeiten]
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

[Bearbeiten | Quelltext bearbeiten]
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

[Bearbeiten | Quelltext bearbeiten]
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

[Bearbeiten | Quelltext bearbeiten]
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

[Bearbeiten | Quelltext bearbeiten]
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

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
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

[Bearbeiten | Quelltext bearbeiten]

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

[Bearbeiten | Quelltext bearbeiten]

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
[Bearbeiten | Quelltext bearbeiten]

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
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

[Bearbeiten | Quelltext bearbeiten]
UPDATE user
SET user_rights='sysop,bureaucrat,developer'
WHERE user_name='Benutzername'
LIMIT 1;

Unbenutzten Account löschen

[Bearbeiten | Quelltext bearbeiten]
DELETE FROM user
WHERE user_name='Benutzername'
LIMIT 1;

Die 50 neuesten Benutzer

[Bearbeiten | Quelltext bearbeiten]
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

[Bearbeiten | Quelltext bearbeiten]
SELECT cur_user_text, COUNT(*) AS count
  FROM cur
 WHERE cur_user != 0
 GROUP BY cur_user
 ORDER BY count DESC
 LIMIT 100

Keine Leerzeilen

[Bearbeiten | Quelltext bearbeiten]
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

[Bearbeiten | Quelltext bearbeiten]
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

[Bearbeiten | Quelltext bearbeiten]
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

[Bearbeiten | Quelltext bearbeiten]

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
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

[Bearbeiten | Quelltext bearbeiten]

todo

mgl. Tastaturtests

[Bearbeiten | Quelltext bearbeiten]

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

[Bearbeiten | Quelltext bearbeiten]
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


Liste sämtlicher Lemmata

[Bearbeiten | Quelltext bearbeiten]
SELECT cur_title
FROM cur
WHERE cur_namespace=0 into outfile "/tmp/lemmata.txt";

Artikel mit arabischem Inhalt

[Bearbeiten | Quelltext bearbeiten]
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
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

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

[Bearbeiten | Quelltext bearbeiten]

(mit mindestens 1500 Bytes ermitteln)

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

Rechtschreibfehler

[Bearbeiten | Quelltext bearbeiten]
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

[Bearbeiten | Quelltext bearbeiten]
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

[Bearbeiten | Quelltext bearbeiten]
 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

[Bearbeiten | Quelltext bearbeiten]
SELECT count(*) 
   FROM cur 
   WHERE cur_text LIKE "%[[Bild:%"
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

[Bearbeiten | Quelltext bearbeiten]

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

[Bearbeiten | Quelltext bearbeiten]

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

[Bearbeiten | Quelltext bearbeiten]

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

[Bearbeiten | Quelltext bearbeiten]

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

[Bearbeiten | Quelltext bearbeiten]

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 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


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