Untitled query #17569

This query is marked as a draft This query has been published by Larske.
SQL

USE svwiki_p;

SHOW tables;

/*
SELECT * from page WHERE page_title='História_de_Portugal';

SELECT page_title, cl_to FROM pagelinks INNER JOIN page on pl_title=page_title INNER JOIN categorylinks ON page_id=cl_from 
WHERE pl_from = 7073 AND (cl_to = 'Mulheres' OR cl_to = 'Homens')
/*

/*
SELECT CONCAT('[[',REPLACE(page_title,'_',' '),']]') AS artikel FROM page WHERE
(page_title LIKE '%s_Järnväg' OR page_title LIKE '%s_Jernväg')
AND page_is_redirect=0
AND page_namespace=0
ORDER BY page_title
*/

/*
SELECT tl_title, COUNT(tl_from) antal FROM templatelinks WHERE
tl_from_namespace = 0 
AND tl_namespace = 10 
AND tl_title LIKE '%_WD'
GROUP BY tl_title
*

/*
SELECT * FROM wb_property_info INNER JOIN page ON pi_property_id = page_title WHERE 
pi_type = 'external-id'
*/

/*
SELECT * FROM page WHERE
page_namespace = 0
AND NOT page_title LIKE 'Q%'
LIMIT 100
*/

/*
SELECT page_title, pp_propname, pp_value, pp_sortkey FROM page_props INNER JOIN page ON pp_page=page_id
*/
/*
SELECT pp_propname, COUNT(page_title) AS antal FROM page_props INNER JOIN page ON pp_page=page_id
*/
/*
WHERE pp_propname='displaytitle'
*/
/*
WHERE pp_propname LIKE 'kartographer'
AND page_namespace=0
LIMIT 100
*/

/*
SELECT cat_title FROM category WHERE
cat_title LIKE '%-%'
AND NOT cat_title LIKE '%-tal%'
AND NOT cat_title LIKE 'Användar%'
AND NOT cat_title LIKE 'Artiklar%'
AND NOT cat_title LIKE 'Berg%'
AND NOT cat_title LIKE 'Borg-%'
AND NOT cat_title LIKE '%stubbar-%'
AND cat_title LIKE 'Biografiska_artiklar_som_behöver_källor_2017-05%'
*/

/*
SELECT CONCAT('[[Användare:',page_title,'|',SUBSTR(page_title,1,50),']]') AS Användarsida, rev_timestamp AS Redigerad, GROUP_CONCAT(CONCAT('[[:Kategori:',cl_to,'|',cl_to,']]') SEPARATOR '<br />') AS Kategorier FROM page INNER JOIN categorylinks ON cl_from=page_id INNER JOIN revision ON page_latest=rev_id WHERE
page_namespace=2
AND NOT page_title LIKE 'Lsjbot%'
AND NOT cl_to LIKE 'Användar%'
AND NOT cl_to LIKE 'Wiki%'
AND NOT cl_to LIKE '%Projekt_%'
AND NOT cl_to LIKE 'Skrivstuga%'
AND NOT cl_to LIKE 'Alla_%'
AND NOT cl_to LIKE 'Sidor%'
AND NOT cl_to LIKE 'Listor_auto%'
AND NOT cl_to LIKE 'Artiklar%'
AND NOT cl_to LIKE 'SVA_staff'
AND NOT cl_to LIKE 'Huvudsida%'
AND NOT cl_to LIKE '%Webbref%'
AND NOT cl_to LIKE '%Listeria%'
AND NOT cl_to LIKE 'Snickeriintresserade_användare'
AND NOT cl_to LIKE 'Blandade_sidor%'
AND NOT cl_to LIKE 'Indexerade_sidor'
AND NOT cl_to LIKE 'Icke-indexerade_sidor'
GROUP BY page_title
ORDER BY page_title
*/

/*
SELECT COUNT(pl_from) FROM pagelinks WHERE
pl_title LIKE 'Swaziland'
*

/*
SELECT CONCAT('[[',REPLACE(page_title,'_',' '),']]') AS sidnamn FROM page
WHERE page_title LIKE '%\_\-\_%'
AND page_namespace = 0
AND page_is_redirect = 0
ORDER BY page_title
*/

/*
SELECT rc_timestamp, rc_user_text, comment_text FROM recentchanges INNER JOIN comment ON rc_comment_id=comment_id
WHERE rc_type<5
AND comment_text LIKE '%#wikiväst19'
ORDER BY rc_timestamp DESC
LIMIT 100
*/

/*
SELECT COUNT(*) AS antal FROM pagelinks WHERE
pl_namespace=828 AND
pl_title LIKE 'Location_map/data/%';

SELECT pl_title, COUNT(*) AS antal FROM pagelinks WHERE
pl_namespace=828 AND
pl_title LIKE 'Location_map/data/%'
GROUP BY pl_title
ORDER BY antal DESC;
*/

/*
SELECT page_title, rd_from, rd_title FROM svwiki_p.page INNER JOIN svwiki_p.categorylinks on page_id=cl_from INNER JOIN svwiki_p.redirect ON page_id=rd_from WHERE
cl_to='Rådhusrätter_i_Sverige';
*/

/*
SELECT * FROM recentchanges WHERE rc_title LIKE 'Utvald_artikel/%'
ORDER BY rc_timestamp DESC
*/

/*
SELECT rc_timestamp, rc_user_text, REPLACE(rc_title,',','§') AS rc_title, rc_this_oldid, rc_last_oldid FROM recentchanges WHERE
rc_timestamp>'20190202230000' AND rc_timestamp<'20190209230000'
AND rc_type<5 
AND rc_namespace=0
AND rc_user_text in ('Allexim','Astridx','Boelnorra','Historiker','Larske','N0WIS','Nordelch','Paranaja','Sjunnesson','Skivsamlare','Thoasp','Ulkl','Zquid')
*/

/*
SELECT  cl_from, page_title FROM geo_tags INNER JOIN page ON gt_page_id=page_id INNER JOIN
*/
/*
SELECT *, page_title FROM categorylinks INNER JOIN page on page_id=cl_from WHERE
cl_to='Orter_i_Irland'
*/

/*
SELECT * FROM user WHERE
user_id = 649138
*/ 

/*
SELECT * FROM logging
WHERE log_user=0
AND log_action='create'
AND (log_title LIKE '%Ogrodzeni%' OR log_title LIKE '%Sztachety%')
AND log_namespace=0
AND LENGTH(log_title)>0
ORDER BY LENGTH(log_title)
*/

/*
SELECT * FROM categorylinks INNER JOIN page on cl_from=page_id WHERE
  cl_to = page_title 
  AND page_namespace = 14
*/

/*
SELECT * FROM page WHERE
page_id = 1441071
*/


/*
SELECT * FROM pagelinks WHERE
pl_title LIKE '%Upper_Bavaria%' OR
pl_title LIKE '%Lower_Bavaria%' OR
pl_title LIKE '%Upper_Palatinate%' OR
pl_title LIKE '%Upper_Franconia%' OR
pl_title LIKE '%Swabia' OR
pl_title LIKE '%Middle_Franconia%' OR
pl_title LIKE '%Lower_Franconia%'
/*

/*
SELECT rc_timestamp AS Tid, CONCAT('[[',REPLACE(rc_title,'_',' '),']]') AS Artikel, CONCAT('align="right"|{{nts|',page_len,'}}') AS Storlek, GROUP_CONCAT(cl_to) AS Kategori, rc_user_text AS Skapare 
FROM recentchanges INNER JOIN categorylinks ON rc_cur_id=cl_from INNER JOIN page on cl_from=page_id
WHERE rc_new=1 
AND rc_namespace=0
AND cl_to LIKE '%chackspelare%'
GROUP BY rc_timestamp, rc_title, rc_user_text
ORDER BY rc_timestamp
*/

/*
SELECT rc_user_text AS användare, COUNT(*) AS antal_red,
        SUM(IF(rc_new_len>rc_old_len+19,1,0)) AS antal_plusred, 
        SUM(IF(rc_new_len>rc_old_len+19,rc_new_len-rc_old_len,0)) AS volym_plusred, 
        SUM(IF(ABS(rc_new_len-rc_old_len)<20,1,0)) AS antal_putsred,
        SUM(IF(ABS(rc_new_len-rc_old_len)<20,ABS(rc_new_len-rc_old_len),0)) AS volym_putsred,
        SUM(IF(rc_new_len<rc_old_len-19,1,0)) AS antal_minusred, 
        SUM(IF(rc_new_len<rc_old_len-19,rc_old_len-rc_new_len,0)) AS volym_minusred FROM recentchanges WHERE
rc_title='Artikelnomineringar' 
AND rc_namespace=4
AND rc_type<5
GROUP BY användare
ORDER BY volym_plusred DESC
*/

/*
SELECT tl_title, COUNT(page_id) AS antal FROM templatelinks INNER JOIN page on tl_from=page_id WHERE
tl_title LIKE 'Illustrationsbehov%'
AND tl_namespace=10
AND page_namespace=0
GROUP BY tl_title
ORDER BY tl_title
*/

/*
SELECT page_title, rev_timestamp, rev_user_text FROM categorylinks INNER JOIN page ON cl_from=page_id INNER JOIN revision on page_id=rev_page 
WHERE cl_to = 'Mallar_för_illustrationsbehov'
AND rev_parent_id=0
ORDER BY rev_timestamp
*/

/*
SELECT * FROM recentchanges WHERE
rc_user_text LIKE 'Larske%' 
*/

/*
SELECT el_to FROM externallinks WHERE el_to LIKE '%www.iucnredlist%' AND
LENGTH(el_to)>27
*/

/*
SELECT NOW();
SELECT COUNT(*) FROM externallinks WHERE el_to LIKE '%www.iucnredlist%';
SELECT NOW();
SELECT COUNT(*) FROM externallinks WHERE el_to LIKE '%oldredlist%';
SELECT NOW();
*/

/*
SELECT rev_user_text, rev_timestamp FROM revision WHERE
rev_comment LIKE '%WP:HG%'
*
/*
SELECT log_action,log_title, log_user, user_name, log_timestamp, log_params FROM logging INNER JOIN user ON log_user=user_id WHERE
#log_action = 'block'
log_timestamp > '20160701000000'
#AND log_timestamp < '20180201000000'
#AND log_title = '213.204.35.226'
AND log_params LIKE '%06-15%'
#AND log_title LIKE '%:%'
#AND LENGTH(log_title)>30
#AND SUBSTR(log_title,5,1)=':'
#log_timestamp > '20180518000000'
ORDER BY log_timestamp DESC
#GROUP BY log_title
#ORDER BY log_title
*/
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query Stop Query
All SQL code is licensed under CC0 License.
SQL

Checking query status...
