Wagendorf:SQL Abfragen
aus Wagendorf, der freien Wissensdatenbank
Hier werden SQL abfragen gesammelt um das Forum zu bereinigen.
MySQL Infos
Zeigt alle Tabellen an
show tables
Zeigt an welche Felder einer Tabelle definiert sind
show fields from tabelle
mehr als die ersten 100 zeilen anzeigen
ersetze LIMIT 100 durch LIMIT 100,200 oder LIMIT 200,300 und so weiter
Anzeigen
Zeigt alle Artikel ohne Text an
select id, user, date, thread, topic from forum_article
Zeigt alle Artikel eines Threads zeitlich sortiert, neueste zuerst an
select * from forum_article WHERE thread=? ORDER BY date DESC
Zeigt alle Threads zeitlich sortiert an, neueste zuerst
select * from forum_thread ORDER BY relevance DESC
Verschieben
Artikel von einem thread in einen anderen verschieben
Vorher muss natürlich die Artikelid abgefragt werden, und die ID des neuen Threads in erfahrung gebracht werden.
update forum_article set thread=? where id=?
Alle artikel eines Threads in einen anderen verschieben
update forum_article set thread=? where thread=?
Benutzer eines Beitrages ändern
update forum_article set user=? where id=?
Löschen
Thread löschen
delete from forum_article where thread=29 delete from forum_thread where id=29
Sonstige
verwaiste diskussionsseiten finden
select a.cur_namespace, a.cur_title from mw_cur as a where a.cur_namespace = 1 and not exists (select b.cur_title from mw_cur as b where b.cur_namespace = 0 and a.cur_title = b.cur_title) LIMIT 100
nicht kategorisierte seiten finden
select cur_namespace, cur_touched, cur_title, cur_restrictions from mw_cur where cur_namespace = 0 and not cur_is_redirect AND cur_restrictions != "move=sysop:edit=sysop" and cur_title not like "Events%" and not exists (select * from mw_categorylinks where cl_from = cur_id) order by cur_touched LIMIT 100
gesperrte seiten ohne Vorlage:GeschützteSeite, Vorlage:LeereGeschützteSeite und Vorlage:Kategorie
select cur_namespace, cur_title, cur_restrictions, cur_touched from mw_cur where cur_restrictions != "" and cur_namespace != 8 and cur_restrictions != "move=:edit=" and not exists (select * from mw_links where cur_id = l_from and ( l_to = 7608 or l_to = 7476 or l_to = 11777)) order by cur_touched LIMIT 100
seiten-statistik
select (select count(1) from mw_cur where cur_namespace = 0 and cur_is_redirect = 0 and cur_title like "Anzeige:%") as anzeigen, (select count(1) from mw_cur where cur_namespace = 0 and cur_is_redirect = 0 and cur_title like "Events%") as events, (select count(1) from mw_cur where cur_namespace = 0 and cur_is_redirect = 0 and cur_title not like "Events%" and cur_title not like "Anzeige:%") as andere_artikel, (select count(1) from mw_cur where cur_is_redirect = 1) as weiterleitungen, (select count(1) from mw_cur where cur_is_redirect = 0 and cur_namespace = 6) as bilderseiten, (select count(1) from mw_cur where cur_is_redirect = 0 and cur_namespace % 2 = 1) as diskussionen, (select count(1) from mw_cur where cur_is_redirect = 0 and cur_namespace != 0 and cur_namespace != 6 and cur_namespace % 2 = 0) as andere_seiten, (select count(1) from mw_cur) as zusammen
forums-statistik
select (select count(1) from forum_article as a, forum_thread as t where a.thread = t.id and t.forum="default") as beitraege, (select count(1) from forum_thread where forum="default") as threads LIMIT 100
im forum aktive user
select user_name, count(1) as cnt_article, (select count(1) from forum_thread as t where a.user = t.user) as cnt_threads, ur_rights from forum_article as a, mw_user, mw_user_rights where user_id = user and ur_user = user_id group by user_id order by cnt_article desc LIMIT 100
im wiki aktive user
select user_name, (select count(1) from mw_old where old_user = user_id) + (select count(1) from mw_cur where cur_user = user_id) as edit_cnt, (select count(1) from mw_logging where log_user = user_id) as log_cnt, ur_rights from mw_user, mw_user_rights where user_id = ur_user order by edit_cnt desc LIMIT 100
übersicht über die admins
select user_id, user_name, user_email, (select count(1) from forum_article as a where a.user = user_id) as cnt_article, (select count(1) from forum_thread as t where t.user = user_id) as cnt_threads, (select count(1) from mw_old where old_user = user_id) + (select count(1) from mw_cur where cur_user = user_id) as edit_cnt, (select count(1) from mw_logging where log_user = user_id) as log_cnt, ur_rights from mw_user, mw_user_rights where ur_user = user_id and (ur_rights != "" or user_name = "SvenBot" or user_name = "AdminMail" or user_name = "SvenMail") order by user_name LIMIT 100
die neusten beiträge im forum
select fa.user, (select user_name from mw_user where user_id = fa.user) as user_name, guest_name, date, fa.topic, text from forum_thread as ft, forum_article as fa where forum="default" and fa.thread = ft.id order by date desc LIMIT 100
zuletzt bearbeitete threads mit ersteller und erstell-datum
select
ft.id,
ft.topic as thema,
ft.relevance as letzer_beitrag,
ft.article_count as beitraege,
(
select
u.user_name
from mw_user as u
where u.user_id =
(
select
fa.user
from forum_article as fa
where fa.thread = ft.id
and fa.date = (
select min(fas.date)
from forum_article as fas
where fas.thread = ft.id
)
)
) as ersteller_name,
(
select
fa.guest_name
from forum_article as fa
where fa.thread = ft.id
and fa.date = (
select min(fas.date)
from forum_article as fas
where fas.thread = ft.id
)
) as ersteller_gast_name,
(
select
fa.date
from forum_article as fa
where fa.thread = ft.id
and fa.date = (
select min(fas.date)
from forum_article as fas
where fas.thread = ft.id
)
) as erstell_datum
from forum_thread as ft
where ft.forum="default"
order by ft.relevance desc LIMIT 100
