1- Delete unapproved comment

DELETE from wp_comments WHERE comment_approved IN  ('0','trash','spam','post-trashed')

2- Delete all approved comments from wordpress

DELETE from wp_comments WHERE comment_approved ="1";

3- Closing comments for all post type in wordpress

UPDATE wp_posts SET comment_status = 'closed', ping_status = 'closed' WHERE comment_status = 'open';

4- Delete all revision

DELETE a,b,c
FROM wp_posts a
LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)
WHERE a.post_type = 'revision';

5- To check orphan entries in postmeta table

SELECT * FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL

6- To remove postmeta that has no reference in wp-posts

DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL

7- Delete Transient

DELETE FROM `wp_options` WHERE `option_name` LIKE ('%\_transient\_%')

8- Select Replace for Old Url vs New URL (Can be potentially harmful)
The below SQL can be potentially harmful to site as it would reset all serialized data that it replaced to just blank

UPDATE wp_posts SET post_content = REPLACE( post_content, 'http://localhost/crazeforfashion', 'http://craze4fashion.com.cp-in-4.webhostbox.net' )

UPDATE wp_postmeta SET meta_value = REPLACE( meta_value, '2021/01', '2021/06' )  WHERE   meta_key='_wp_attached_file'

9- delete posts from specific category id

delete a,b,c,d
FROM wp_posts a
LEFT JOIN wp_term_relationships b ON ( a.ID = b.object_id )
LEFT JOIN wp_postmeta c ON ( a.ID = c.post_id )
LEFT JOIN wp_term_taxonomy d ON ( d.term_taxonomy_id = b.term_taxonomy_id )
LEFT JOIN wp_terms e ON ( e.term_id = d.term_id )
WHERE e.term_id =2906

10- Move posts form one post type to another post type

UPDATE wp_posts set post_type='product' WHERE post_type='post'

11- Delete all media from database ( you also need to remove uploads folder and create back again) this can be useful if client wants to build a new site with same theme and previous settings.

DELETE FROM wp_posts WHERE post_type = 'attachment'

12 Select orphan taxonomy and terms.

SELECT * FROM tas_term_relationships 
WHERE term_taxonomy_id=1 
AND object_id NOT IN (SELECT id FROM tas_posts)

13 Delete Orphan taxonomy and terms

DELETE FROM tas_term_relationships WHERE term_taxonomy_id=1 AND object_id NOT IN (SELECT id FROM tas_posts)