Untitled query #16664

SQL

USE dawiki_p;  #just means 'use this wiki'
#SHOW TABLES;
#DESCRIBE page;
#SELECT * FROM page LIMIT 10;   #make sure to limit search, otherwise we'll get everything back
#SELECT * FROM revision LIMIT 10;  #this is from revision table - this is much bigger than the pages one
#SELECT * FROM revision ORDER by rev_timestamp DESC LIMIT 10;   #show in time order - the 'DESC' or 'ASC' call comes after
#SELECT rev_page FROM revision ORDER by rev_timestamp DESC limit 10;

	#I want rev_page, page_title, and rev_timestamp - however, page_title is in revision table and the others in page table
    #so I get in one and the join the other and say where to join
#SELECT rev_page, page_title, rev_timestamp FROM revision JOIN page ON rev_page = page_id ORDER by rev_timestamp ASC limit 10;

#SELECT * FROM user limit 10;	#note that most fields here I'm not authorised to access (e.g. password)

	#here's how to get information from two tables
#SELECT rev_page, page_title, rev_timestamp, rev_user, user_name FROM revision JOIN page ON rev_page = page_id JOIN user ON rev_user= user_id ORDER by rev_timestamp ASC limit 10;

	#COUNT counts how many occurrences there are of a given thing - here it's how many entries in rev_id are there where
    #  rev_user equals 66249?
#SELECT COUNT(rev_id) FROM revision WHERE rev_user = "12";

	#13. this puts everything with the same revision user together, and then it counts
#SELECT COUNT(rev_id) FROM revision GROUP by rev_user LIMIT 10;

	#14. this names the column with the editcounts 'editcounts' - top 10 users w/ largest number of edits
#SELECT COUNT(rev_id) AS editcounts FROM revision GROUP by rev_user ORDER BY editcounts DESC LIMIT 10;

	#15. this gives the counts from rev_id (renamed as editcounts) and rev_user, and then then adds user_name
    #	from the user table. Group the counts by user and order it by editcounts
#SELECT COUNT(rev_id) AS editcounts, rev_user, user_name FROM revision JOIN user ON user_id = rev_user GROUP by rev_user ORDER BY editcounts DESC LIMIT 10;

	#16. This kind of does the same as before, but excludes usernames that include bot - query needs revision to exclude
    #case sensitivity
#SELECT COUNT(rev_id) AS editcounts, rev_user, user_name, user_registration FROM revision JOIN user ON user_id = rev_user WHERE user_name NOT LIKE "%bot%" AND user_name NOT LIKE "%Bot%" GROUP by rev_user ORDER BY editcounts DESC LIMIT 10;

SELECT COUNT(rev_id) AS editcounts, page_title FROM revision JOIN user ON rev_user = user_id JOIN page ON page_id  = rev_page WHERE user_name="Auntof6" GROUP BY rev_page ORDER BY editcounts DESC LIMIT 10;
SQL

Checking query status...
