MediaWiki SQL queries
Reset a user password
Before reading further consider the password reset extension which gives a simple form to reset wiki passwords.
You need to log in to the database directly. First obtain the user's id as in the following example (alternatively, go to Special:Userlist and get the id from there):
SELECT user_id FROM prefix_user WHERE user_name='WikiSysop';
Then apply the query shown in the following example to set the password for that user id (here the WikiSysop user id is 1): Note that the password is a nested concatenation of both the md5 of the user_id combined with the md5 password.
UPDATE prefix_user SET user_password=md5(CONCAT('1-',md5('newpassword'))) WHERE user_id=1;
Set a user preference
You can change a preference such as the skin with the following SQL query. Remove the WHERE clause to apply to all users.
UPDATE user SET user_options = REPLACE(user_options,'skin=monobook','skin=organicdesign') WHERE user_name = 'Foo';
Select all user who have not made edits
SELECT user_name FROM user WHERE user_id NOT IN (SELECT DISTINCT rev_user FROM revision);
Select the text of the latest revision of a title
SELECT old_text FROM page,text,revision WHERE page_title='title' AND page_namespace=numeric_namespace AND rev_id=page_latest AND old_id=rev_text_id;
Search all pages with specific text in their current revision
This is basically a full text search which is expensive, but useful for making lists of pages containing text that's difficult to search for such as symbols or parser functions.
SELECT page_namespace,page_title FROM page,text,revision WHERE rev_id=page_latest AND old_id=rev_text_id AND old_text like '%search phrase%'