zencart+magento 如何清空商品演示数据和没有商品数据的sql执行语句
#清空商品商品、属性、分类
TRUNCATE TABLE categories;
TRUNCATE TABLE categories_description;
TRUNCATE TABLE products_to_categories;
TRUNCATE TABLE meta_tags_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 reviews;
TRUNCATE TABLE reviews_description;
# 清空推荐商品
TRUNCATE TABLE featured;
# 清空厂家及资料
TRUNCATE TABLE manufacturers;
TRUNCATE TABLE manufacturers_info;
# 清空团体价格
TRUNCATE TABLE group_pricing;
# 清空促销商品
TRUNCATE TABLE salemaker_sales;
# 清空特价商品
TRUNCATE TABLE specials;
=================================结束=======================
========================================================
DELETE FROM categories_description WHERE categories_id in(64,96);
DELETE FROM products_to_categories WHERE categories_id in(64,96);
DELETE FROM meta_tags_categories_description WHERE categories_id in(64,96);
DELETE p,pd FROM products p ,products_description pd WHERE p.products_id=pd.products_id AND p.master_categories_id in(64,96);
4.修改价格：
update specials set specials_new_products_price=specials_new_products_price-20;#整体减
UPDATE specials set specials_new_products_price=specials_new_products_price-40 where specials_new_products_price>275;#限定价格整体减
update `specials` set `specials_new_products_price`=77.08 where ` specials_new_products_price ` = 67.78;#限定价格替换
3.替换名称：
目录的@update `categories_description` SET `categories_name` = replace (`categories_name`,'ù','ù');
产品的@update products_description set products_name=replace(products_name,'Sunglasses','Sonnenbrillen');#全部替换
描述的@UPDATE `products_description` SET `products_description` = replace (`products_description`,'127.0.0.1','www.jackeneurope.net');
5.特价过期时间设置
update specials set expires_date=replace(expires_date ,'1970-01-01','2022-12-22')
========================================================
categories.php要开 reset 后要开拿个layoutbox
UPDATE`layout_boxes`SET`layout_box_status`='1' WHERE`layout_box_name`='categories.php.php';
UPDATE`layout_boxes`SET`layout_box_status`='0' WHERE`layout_box_name`='banner_box.php';
UPDATE`layout_boxes`SET`layout_box_status`='0' WHERE`layout_box_name`='banner_box2.php';
UPDATE`layout_boxes`SET`layout_box_status`='0' WHERE`layout_box_name`='banner_box_all.php';
UPDATE`layout_boxes`SET`layout_box_status`='0' WHERE`layout_box_name`='best_sellers.php';
UPDATE`layout_boxes`SET`layout_box_status`='0' WHERE`layout_box_name`='currencies.php';
UPDATE`layout_boxes`SET`layout_box_status`='0' WHERE`layout_box_name`='document_categories.php';
UPDATE`layout_boxes`SET`layout_box_status`='0' WHERE`layout_box_name`='ezpages.php';
UPDATE`layout_boxes`SET`layout_box_status`='0' WHERE`layout_box_name`='featured.php';
UPDATE`layout_boxes`SET`layout_box_status`='0' WHERE`layout_box_name`='information.php';
UPDATE`layout_boxes`SET`layout_box_status`='0' WHERE`layout_box_name`='languages.php';
UPDATE`layout_boxes`SET`layout_box_status`='0' WHERE`layout_box_name`='manufacturer_info.php';
UPDATE`layout_boxes`SET`layout_box_status`='0' WHERE`layout_box_name`='manufacturers.php';
UPDATE`layout_boxes`SET`layout_box_status`='0' WHERE`layout_box_name`='more_information.php';
UPDATE`layout_boxes`SET`layout_box_status`='0' WHERE`layout_box_name`='music_genres.php';
UPDATE`layout_boxes`SET`layout_box_status`='0' WHERE`layout_box_name`='order_history.php';
UPDATE`layout_boxes`SET`layout_box_status`='0' WHERE`layout_box_name`='product_notifications.php';
UPDATE`layout_boxes`SET`layout_box_status`='0' WHERE`layout_box_name`='record_companies.php';
UPDATE`layout_boxes`SET`layout_box_status`='0' WHERE`layout_box_name`='reviews.php';
UPDATE`layout_boxes`SET`layout_box_status`='0' WHERE`layout_box_name`='search.php';
UPDATE`layout_boxes`SET`layout_box_status`='0' WHERE`layout_box_name`='search_header.php';
UPDATE`layout_boxes`SET`layout_box_status`='0' WHERE`layout_box_name`='shopping_cart.php';
UPDATE`layout_boxes`SET`layout_box_status`='0' WHERE`layout_box_name`='specials.php';
UPDATE`layout_boxes`SET`layout_box_status`='0' WHERE`layout_box_name`='whats_new.php';
UPDATE`layout_boxes`SET`layout_box_status`='0' WHERE`layout_box_name`='whos_online.php';
---------------------------------------------------------------------------------------------------------------------
mysql 删除指定目录数据：
DELETE FROM categories WHERE categories_id in(72,74,76,78,80,82);
DELETE FROM categories_description WHERE categories_id in(72,74,76,78,80,82);
DELETE FROM products_to_categories WHERE categories_id in(72,74,76,78,80,82);
DELETE FROM meta_tags_categories_description WHERE categories_id in(72,74,76,78,80,82);
DELETE p,pd FROM products p ,products_description pd WHERE p.products_id=pd.products_id AND p.master_categories_id in (72,74,76,78,80,82);
----------------------------------------------
UPDATE products SET products_family = master_categories_id; # 1.51导出相关产品的
========================magento=========================
TRUNCATE TABLE `catalog_product_bundle_option`;
TRUNCATE TABLE `catalog_product_bundle_option_value`;
TRUNCATE TABLE `catalog_product_bundle_selection`;
TRUNCATE TABLE `catalog_product_entity_datetime`;
TRUNCATE TABLE `catalog_product_entity_decimal`;
TRUNCATE TABLE `catalog_product_entity_gallery`;
TRUNCATE TABLE `catalog_product_entity_int`;
TRUNCATE TABLE `catalog_product_entity_media_gallery`;
TRUNCATE TABLE `catalog_product_entity_media_gallery_value`;
TRUNCATE TABLE `catalog_product_entity_text`;
TRUNCATE TABLE `catalog_product_entity_tier_price`;
TRUNCATE TABLE `catalog_product_entity_varchar`;
TRUNCATE TABLE `catalog_product_link`;
TRUNCATE TABLE `catalog_product_link_attribute`;
TRUNCATE TABLE `catalog_product_link_attribute_decimal`;
TRUNCATE TABLE `catalog_product_link_attribute_int`;
TRUNCATE TABLE `catalog_product_link_attribute_varchar`;
TRUNCATE TABLE `catalog_product_link_type`;
TRUNCATE TABLE `catalog_product_option`;
TRUNCATE TABLE `catalog_product_option_price`;
TRUNCATE TABLE `catalog_product_option_title`;
TRUNCATE TABLE `catalog_product_option_type_price`;
TRUNCATE TABLE `catalog_product_option_type_title`;
TRUNCATE TABLE `catalog_product_option_type_value`;
TRUNCATE TABLE `catalog_product_super_attribute`;
TRUNCATE TABLE `catalog_product_super_attribute_label`;
TRUNCATE TABLE `catalog_product_super_attribute_pricing`;
TRUNCATE TABLE `catalog_product_super_link`;
TRUNCATE TABLE `catalog_product_enabled_index`;
TRUNCATE TABLE `catalog_product_website`;
TRUNCATE TABLE `catalog_product_entity`;
TRUNCATE TABLE `cataloginventory_stock`;
TRUNCATE TABLE `cataloginventory_stock_item`;
TRUNCATE TABLE `cataloginventory_stock_status`;
-------------------------------------------------------------------------------------------------------------
INSERT INTO configuration
VALUES
(NULL, 'Homepage title', 'HOME_PAGE_TITLE', '', 'set the home page title', 1,210, NOW(), NOW(), NULL, NULL),
(NULL, 'Homepage keywords', 'HOME_PAGE_META_KEYWORDS', '', 'set the home page keywords', 1,211, NOW(), NOW(), NULL, NULL),
(NULL, 'Homepage description', 'HOME_PAGE_META_DESCRIPTION', '', 'set the home page description', 1,212, NOW(), NOW(), NULL, NULL);
(NULL , 'TITLE', 'TITLE', '', 'TITLE', '1', '213', NOW() , NOW(), NULL , NULL ),
(NULL , 'SITE_TAGLINE', 'SITE_TAGLINE', '', 'SITE_TAGLINE', '1', '214', NOW() , NOW(), NULL , NULL ),
(NULL , 'HEADER_ALT_TEXT图片alt', 'HEADER_ALT_TEXT', '', 'HEADER_ALT_TEXT', '1', '217', NOW() , NOW(), NULL , NULL );
删除指定目录数据：mysql
DELETE FROM categories WHERE categories_id in(72,74,76,78,80,82);
DELETE FROM categories_description WHERE categories_id in(72,74,76,78,80,82);
DELETE FROM products_to_categories WHERE categories_id in(72,74,76,78,80,82);
DELETE FROM meta_tags_categories_description categories_id in(72,74,76,78,80,82);
DELETE products,products_description FROM products p ,products_description pd WHERE p.`products_id`=pd.`products_id` AND p.`master_categories_id` in(72,74,76,78,80,82);
删除products_to_categories重复的products_id，model重复
查询：
select * from products_to_categories where `categories_id` not in
(SELECT `master_categories_id` FROM `products` WHERE `products_id` in(select `products_id` from products_to_categories group by `products_id` having count(`products_id`) > 1) )
and `products_id` in (select `products_id` from products_to_categories GROUP by `products_id` HAVING count(`products_id`)>1)
删除：
delete from products_to_categories where `categories_id` not in (select * from
(SELECT `master_categories_id` FROM `products`
WHERE `products_id` in
(select `products_id` from products_to_categories group by `products_id` having count(`products_id`) > 1) )a)
and `products_id` in (select * from (select `products_id` from products_to_categories GROUP by `products_id` HAVING count(`products_id`)>1)b)
删除指定数据库的指定前缀表的操作
SELECT concat( 'drop table ', table_name, ';' ) as d FROM information_schema.TABLES WHERE table_schema = '$zcdbnamearr[1]' and table_name like 'wp_%'
字符串前面加00
update toa_order set `kefu_id`=concat('00',`kefu_id`);
字符串后面加00
update toa_order set `kefu_id`=concat(`kefu_id`，'00');
