Here are some examples of SQL queries for a WordPress database, where ‘TEXT’ provide your data.
Changing the user password:
UPDATE wp_users SET user_pass = MD5('password') WHERE user_login = 'name';
UPDATE wp_users SET user_pass = MD5('password') WHERE ID=1;
Changing the user login:
UPDATE wp_users SET user_login='name' WHERE user_login='name';
Building a list of email commenters:
SELECT DISTINCT comment_author_email FROM wp_comments;
Disable comments for all posts:
UPDATE wp_posts SET comment_status = 'closed';
Enable comments for all posts:
UPDATE wp_posts SET comment_status = 'open';
Enable comments for all posts for registered users only:
UPDATE wp_posts SET comment_status = 'registered_only';
Delete all spam comments:
DELETE FROM wp_comments WHERE comment_approved = 'spam';
Delete all unapproved comments:
DELETE FROM wp_comments WHERE comment_approved = 0;
Clear URL field values for all comments:
UPDATE wp_comments SET comment_author_url='';
Closing comments in old posts:
UPDATE wp_posts SET comment_status = 'closed'
WHERE post_date < '2014-01-01' AND post_status = 'publish';
Closing of comments in a certain category:
UPDATE wp_posts p
LEFT JOIN wp_term_relationships rel ON ( p.ID = rel.object_id )
LEFT JOIN wp_term_taxonomy tax ON ( tax.term_taxonomy_id = rel.term_taxonomy_id )
LEFT JOIN wp_terms tm ON ( tm.term_id = tax.term_id )
SET p.comment_status = 'closed' WHERE tm.slug = 'category_name';
Removing comments in the url of the following characters:
DELETE a,b FROM wp_comments a
LEFT JOIN wp_commentmeta b ON ( a.comment_ID = b.comment_id )
WHERE a.comment_author_url LIKE '%characters%';
Bulk editing of the commentor url:
UPDATE wp_comments SET comment_author_url = 'http://new_address'
WHERE comment_author = 'admin' AND comment_author_email = '[email protected]';
Another version of the massive change of url commentators:
UPDATE wp_comments
SET comment_author_url = REPLACE( comment_author_url, 'old_address', 'new_address' );
Bulk editing of the commenter name:
UPDATE wp_comments SET comment_author = REPLACE ( comment_author, 'old_name', 'new_name' );
Bulk edit email comment:
UPDATE wp_comments SET comment_author_email = REPLACE ( comment_author_email, '[email protected]', '[email protected]' );
Closing pings:
UPDATE wp_posts p SET p.ping_status = 'closed';
Opening pings:
UPDATE wp_posts p SET p.ping_status = 'open';
Delete all comments from pings:
DELETE FROM wp_comments WHERE comment_type = 'pingback';
Changing a specific website address or any expression in all posts to another:
UPDATE wp_posts
SET post_content = REPLACE (post_content, 'http://OLD', 'http://NEW');
Mass change of the author of records:
UPDATE wp_posts SET post_author=1 WHERE post_author=2;
UPDATE wp_posts SET post_author=1 WHERE post_author=16 AND post_modified<'2014-11-17 00:00:00';
Deleting record revisions:
SELECT COUNT(*) FROM wp_posts WHERE post_type = 'revision';
DELETE FROM wp_posts WHERE post_type = 'revision';
SELECT COUNT(*) FROM wp_postmeta pm LEFT JOIN wp_posts p ON pm.post_id = p.ID WHERE p.ID IS NULL;
DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts p ON pm.post_id = p.ID WHERE p.ID IS NULL;
Clearing the feed cache:
DELETE FROM `wp_options`
WHERE `option_name` LIKE ('_transient%_feed_%');
An example of a search for published articles in which the specified text or characters are found:
SELECT * FROM `wp_posts` WHERE post_type='post' AND post_status='publish' AND post_content LIKE '%TEXT%';
SELECT * FROM `wp_posts` WHERE post_type='post' AND post_status='publish' AND post_content LIKE '%>%';
SELECT * FROM `wp_posts` WHERE post_type='post' AND post_status='publish' AND post_content LIKE '%<%';
See also my articles:
How to disable the WordPress plug-in via MySQL
How to change a WordPress theme through MySQL