操作前记得备份数据库
操作前记得备份数据库
操作前记得备份数据库

按产品产品ID批量加价

UPDATE `products` set  products_price = products_price+100 where master_categories_id in ('7','15','6','20','21','24')

重置管理员用户名为sadmin密码为adminkk

update `admin` set `admin_name` = 'sadmin',`admin_pass` = '3ab6c58afba64fb4970714396a36c5c2:9f' where `admin_id` ='1'

批量设置可选项价格不受特价影响

update `products_attributes` set attributes_discounted = '0' where options_values_price != '0.0000';

按产品ID批量增加销量

UPDATE `products` set products_ordered = RAND()*7 where products_id in ('6','9','14','30','49','52','68','103','123','129','130')

批量修改邮箱设置

update `configuration` set configuration_value = replace(configuration_value,'old@Email','new@Email');

导出所有产品名称和售价

select b.products_name as name,format(a.products_price,2) as price from `products` a,`products_description` b where a.products_id = b.products_id and a.products_status = '1'

清空所有产品和可选项

TRUNCATE `categories`;TRUNCATE `categories_description`;TRUNCATE `featured`;TRUNCATE `products`;TRUNCATE `products_description`;TRUNCATE `products_to_categories`;TRUNCATE `specials`;TRUNCATE `products_attributes`;TRUNCATE `products_options`;TRUNCATE `products_options_values`;TRUNCATE `products_options_values_to_products_options`;INSERT INTO `products_options_values` (`products_options_values_id`, `language_id`, `products_options_values_name`, `products_options_values_sort_order`) VALUES (0, 1, 'TEXT', 0);

复制网站后清空所有客户、订单、品牌、类目、产品以及选项

TRUNCATE `address_book`;TRUNCATE `customers`;TRUNCATE `customers_basket`;TRUNCATE `customers_basket_attributes`;TRUNCATE `customers_info`;TRUNCATE `orders`;TRUNCATE `orders_products`;TRUNCATE `orders_products_attributes`;TRUNCATE `orders_total`;TRUNCATE `admin_activity_log`;TRUNCATE `categories`;TRUNCATE `categories_description`;TRUNCATE `featured`;TRUNCATE `specials`;TRUNCATE `manufacturers`;TRUNCATE `manufacturers_info`;TRUNCATE `products`;TRUNCATE `products_attributes`;TRUNCATE `products_attributes_download`;TRUNCATE `products_description`;TRUNCATE `products_discount_quantity`;TRUNCATE `products_notifications`;TRUNCATE `products_options`;TRUNCATE `products_options_values`;TRUNCATE `products_options_values_to_products_options`;TRUNCATE `products_to_categories`;TRUNCATE `product_music_extra`;TRUNCATE `product_types_to_category`;

批量按父目录id修改商品属性（商品目录的父目录）

update `products` set products_price = '11999' where products_id in (SELECT products_id FROM `products_to_categories` WHERE `categories_id` IN (28, 29)); 
update `products` set products_price = '11999' where products_id in (SELECT products_id FROM `products_to_categories` WHERE `categories_id` in (SELECT categories_id FROM categories  WHERE parent_id IN (28, 2, 3, 4, 5, 6)));

重置客户的密码为123456

update `customers` set `customers_password` = '3670660b2094ac5c0060c88a15b418c2:be' where `customers_email_address` = '[email protected]'

批量把Marcus的新闻添加日期同步成生效日期

UPDATE `box_news` SET news_added_date = news_start_date;