常用的sql操作

WordPress删除产品

DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE taxonomy LIKE 'pa_%');

DELETE FROM wp_term_taxonomy WHERE taxonomy LIKE 'pa_%';

DELETE FROM wp_term_relationships WHERE term_taxonomy_id not IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);

DELETE FROM wp_term_relationships WHERE object_id IN (SELECT ID FROM wp_posts WHERE post_type IN ('product','product_variation'));

DELETE FROM wp_postmeta WHERE post_id IN (SELECT ID FROM wp_posts WHERE post_type IN ('product','product_variation'));

DELETE FROM wp_posts WHERE post_type IN ('product','product_variation');

 #目录也要手动清除干净

 

Zencart删除产品

#清除产品和订单数据
TRUNCATE TABLE categories;
TRUNCATE TABLE categories_description;
TRUNCATE TABLE media_clips;
TRUNCATE TABLE media_manager;
TRUNCATE TABLE media_to_products;
TRUNCATE TABLE media_types;
TRUNCATE TABLE music_genre;
TRUNCATE TABLE product_music_extra;
TRUNCATE TABLE product_types_to_category;
TRUNCATE TABLE products;
TRUNCATE TABLE products_attributes;
TRUNCATE TABLE products_attributes_download;
TRUNCATE TABLE products_description;
TRUNCATE TABLE products_discount_quantity;
TRUNCATE TABLE products_notifications;
TRUNCATE TABLE products_options;
TRUNCATE TABLE products_options_types;
TRUNCATE TABLE products_options_values;
TRUNCATE TABLE products_options_values_to_products_options;
TRUNCATE TABLE products_to_categories;
TRUNCATE TABLE record_artists;
TRUNCATE TABLE record_artists_info;
TRUNCATE TABLE record_company;
TRUNCATE TABLE record_company_info;
TRUNCATE TABLE featured;
TRUNCATE TABLE salemaker_sales;
TRUNCATE TABLE specials;
TRUNCATE TABLE group_pricing;
TRUNCATE TABLE manufacturers;
TRUNCATE TABLE manufacturers_info;
TRUNCATE TABLE reviews;
TRUNCATE TABLE reviews_description;
# 清空meta
TRUNCATE TABLE meta_tags_products_description;
TRUNCATE TABLE meta_tags_categories_description;
# 清空客户资料
TRUNCATE TABLE customers;
TRUNCATE TABLE customers_info;
# 清空订单资料
TRUNCATE TABLE orders;
TRUNCATE TABLE orders_products;
TRUNCATE TABLE orders_products_attributes;
TRUNCATE TABLE orders_status_history;
TRUNCATE TABLE orders_total;

 

Opencart清除产品

TRUNCATE TABLE `oc_attribute`;
TRUNCATE TABLE `oc_attribute_description`;
TRUNCATE TABLE `oc_attribute_group`;
TRUNCATE TABLE `oc_attribute_group_description`;
TRUNCATE TABLE `oc_category`;
TRUNCATE TABLE `oc_category_description`;
TRUNCATE TABLE `oc_category_to_layout`;
TRUNCATE TABLE `oc_category_to_store`;
TRUNCATE TABLE `oc_option`;
TRUNCATE TABLE `oc_option_description`;
TRUNCATE TABLE `oc_option_value`;
TRUNCATE TABLE `oc_option_value_description`;
TRUNCATE TABLE `oc_product`;
TRUNCATE TABLE `oc_product_attribute`;
TRUNCATE TABLE `oc_product_description`;
TRUNCATE TABLE `oc_product_discount`;
TRUNCATE TABLE `oc_product_image`;
TRUNCATE TABLE `oc_product_option`;
TRUNCATE TABLE `oc_product_option_value`;
TRUNCATE TABLE `oc_product_reward`;
TRUNCATE TABLE `oc_product_special`;
TRUNCATE TABLE `oc_product_to_category`;
TRUNCATE TABLE `oc_product_to_download`;
TRUNCATE TABLE `oc_product_to_layout`;
TRUNCATE TABLE `oc_product_to_store`;

 

WordPress替换域名

UPDATE `wp_options` SET `option_value` = 'https://www.glful.com' WHERE `option_name` = 'siteurl';
UPDATE `wp_options` SET `option_value` = 'https://www.glful.com' WHERE `option_name` = 'home';
UPDATE `wp_users` SET `user_url` = 'https://www.glful.com';
#UPDATE `wp_options` SET `option_value` = 'xiahua1899@gmail.com' WHERE `option_name` = 'admin_email';
#UPDATE `wp_users` SET `user_email` = 'xiahua1899@gmail.com';

UPDATE `wp_options` SET `option_value` = REPLACE( option_value, 'Visbag', 'Glful' ) WHERE `wp_options`.`option_id` = 3;
UPDATE `wp_options` SET `option_value` = REPLACE( option_value, 'Visbag', 'Glful' ) WHERE `wp_options`.`option_id` = 231;
UPDATE `wp_options` SET `option_value` = REPLACE( option_value, 'Visbag', 'Glful' ) WHERE `wp_options`.`option_id` = 234;
UPDATE `wp_options` SET `option_value` = REPLACE( option_value, 'visbag', 'glful' ) WHERE `wp_options`.`option_id` = 234;
UPDATE `wp_options` SET `option_value` = REPLACE( option_value, 'Visbag', 'Glful' ) WHERE `wp_options`.`option_id` = 2550;

UPDATE `wp_posts` SET `post_content` = REPLACE( post_content, 'visbag', 'glful' ) WHERE `post_title` = 'Contact Us';
UPDATE `wp_posts` SET `post_content` = REPLACE( post_content, 'visbag', 'glful' ) WHERE `post_title` = 'About Us';
UPDATE `wp_posts` SET `post_content` = REPLACE( post_content, 'visbag', 'glful' ) WHERE `post_title` = 'Privacy Policy';
UPDATE `wp_posts` SET `post_content` = REPLACE( post_content, 'visbag', 'glful' ) WHERE `post_title` = 'Terms Of Service';
UPDATE `wp_posts` SET `post_content` = REPLACE( post_content, 'visbag', 'glful' ) WHERE `post_title` = 'Returns and refunds';
UPDATE `wp_posts` SET `post_content` = REPLACE( post_content, 'visbag', 'glful' ) WHERE `post_title` = 'Shipping Policy';
UPDATE `wp_posts` SET `post_content` = REPLACE( post_content, 'visbag', 'glful' ) WHERE `post_title` = 'Track Order';
UPDATE `wp_posts` SET `post_content` = REPLACE( post_content, 'visbag', 'glful' ) WHERE `post_title` = 'Payments';
UPDATE `wp_posts` SET `post_content` = REPLACE( post_content, 'visbag', 'glful' ) WHERE `post_title` = 'About Payments';
UPDATE `wp_posts` SET `post_content` = REPLACE( post_content, 'ihfover.com', 'glful.com' ) WHERE `post_title` = 'About Payments';

UPDATE `wp_posts` SET `post_content` = REPLACE( post_content, 'Visbag', 'Glful' ) WHERE `post_title` = 'Contact Us';
UPDATE `wp_posts` SET `post_content` = REPLACE( post_content, 'Visbag', 'Glful' ) WHERE `post_title` = 'About Us';
UPDATE `wp_posts` SET `post_content` = REPLACE( post_content, 'Visbag', 'Glful' ) WHERE `post_title` = 'Privacy Policy';
UPDATE `wp_posts` SET `post_content` = REPLACE( post_content, 'Visbag', 'Glful' ) WHERE `post_title` = 'Terms Of Service';
UPDATE `wp_posts` SET `post_content` = REPLACE( post_content, 'Visbag', 'Glful' ) WHERE `post_title` = 'Returns and refunds';
UPDATE `wp_posts` SET `post_content` = REPLACE( post_content, 'Visbag', 'Glful' ) WHERE `post_title` = 'Shipping Policy';
UPDATE `wp_posts` SET `post_content` = REPLACE( post_content, 'Visbag', 'Glful' ) WHERE `post_title` = 'Track Order';
UPDATE `wp_posts` SET `post_content` = REPLACE( post_content, 'Visbag', 'Glful' ) WHERE `post_title` = 'Payments';
UPDATE `wp_posts` SET `post_content` = REPLACE( post_content, 'Visbag', 'Glful' ) WHERE `post_title` = 'About Payments';

UPDATE `wp_posts` SET `post_content` = REPLACE( post_content, 'visbag', 'glful' );
UPDATE `wp_posts` SET `post_content` = REPLACE( post_content, 'Visbag', 'Glful' );

UPDATE `wp_posts` SET `guid` = REPLACE( guid, 'visbag', 'glful' );
UPDATE `wp_posts` SET `post_content` = REPLACE( post_content, 'visbag', 'glful' );
UPDATE `wp_postmeta` SET `meta_value` = REPLACE( meta_value, 'visbag', 'glful' );
UPDATE `wp_postmeta` SET `meta_value` = REPLACE( meta_value, 'Visbag', 'Glful' );
UPDATE `wp_options` SET `option_value` = REPLACE( option_value, 'visbag.com', 'glful.com' ) WHERE `wp_options`.`option_id` = 39;
UPDATE `wp_options` SET `option_value` = REPLACE( option_value, 'visbag.com', 'glful.com' ) WHERE `wp_options`.`option_id` = 260;
UPDATE `wp_options` SET `option_value` = REPLACE( option_value, 'visbag.com', 'glful.com' ) WHERE `wp_options`.`option_id` = 442138;
UPDATE `wp_options` SET `option_value` = REPLACE( option_value, 'visbag.com', 'glful.com' ) WHERE `wp_options`.`option_id` = 442139;
UPDATE `wp_options` SET `option_value` = REPLACE( option_value, 'Visbag', 'Glful' ) WHERE `wp_options`.`option_id` = 3;
UPDATE `wp_options` SET `option_value` = REPLACE( option_value, 'Visbag', 'Glful' ) WHERE `wp_options`.`option_id` = 259;
UPDATE `wp_options` SET `option_value` = REPLACE( option_value, 'Visbag', 'Glful' ) WHERE `wp_options`.`option_id` = 442137;

 #删除订单

DELETE FROM wp_woocommerce_order_itemmeta;
DELETE FROM wp_woocommerce_order_items;
DELETE FROM wp_comments WHERE comment_type = 'order_note';
DELETE FROM wp_postmeta WHERE post_id IN ( SELECT ID FROM wp_posts WHERE post_type = 'shop_order' );
DELETE FROM wp_posts WHERE post_type = 'shop_order';

#删除优惠卷
DELETE FROM wp_posts WHERE post_type = 'shop_coupon';

#清空统计插件
TRUNCATE `wp_statistics_exclusions`;
TRUNCATE `wp_statistics_pages`;
TRUNCATE `wp_statistics_search`;
TRUNCATE `wp_statistics_visit`;
TRUNCATE `wp_statistics_visitor`;
TRUNCATE `wp_statistics_visitor_relationships`;

#清空弃用购物车日志
TRUNCATE `wp_actionscheduler_logs`;