火车采集用到的access查询命令小结

#For zencart

#图片网址路径替换

UPDATE Content SET v_products_image=replace(v_products_image, '<img src="images/','http://localhost/z001/images/');
UPDATE Content SET v_products_image=replace(v_products_image, '">','');
UPDATE Content SET PageUrl = 'http://localhost/z001/';
SELECT * FROM Content WHERE v_products_image = '';
SELECT * FROM Content WHERE v_categories_name_1 = ''; 

#博客网址替换

update wp_posts set post_content=replace(post_content,'localhost/zc139','www.');
update wp_posts set guid=replace(guid,'localhost/zc139','www.');
update wp_options set option_value=replace(option_value,'localhost/zc139','www.');

 

#zencart后台邮箱替换

update admin set admin_email =replace(admin_email,'qq@qq.com','youremailaddress');
update configuration set configuration_value =replace(configuration_value,'qq@qq.com','youremailaddress');

 

#设置未采

UPDATE Content SET [已采] = 0 WHERE v_categories_name_1 = '';      //设置未采完整的记录为未采
UPDATE Content SET [已采] = 0 WHERE v_products_image = '';  //设置未采完整的记录为未采

 

#查询为空的记录

SELECT * FROM Content WHERE v_products_image = ''; //查询图片为空的产品记录
SELECT * FROM Content WHERE v_products_image = '|||';

 

#For ecshop

UPDATE Content SET v_image=replace(v_image, '<img src="images/','http://localhost/ecshop/images/');
UPDATE Content SET v_image=replace(v_image, '">','');
UPDATE Content SET PageUrl = 'http://localhost/ecshop/';

 

 #处理

DELETE FROM Content WHERE [已发] = -1; //删除已发布的
DELETE FROM Content WHERE [已采] = -1; //删除已采集的
DELETE FROM Content WHERE v_products_image = '|||';

 

#清空access火车头采集内容（保留采集网址）

#zencart清空代码

UPDATE Content SET 已采=0,已发=0,v_products_model='',v_products_image='',v_products_name_1='',v_products_description_1='',v_specials_price='',v_products_price='',v_categories_name_1='',v_specials_expires_date='',v_products_quantity='',v_products_weight='',v_manufacturers_name='',v_metatags_products_name_status='',v_metatags_title_status='',v_metatags_model_status='',v_metatags_price_status='',v_metatags_title_tagline_status='',v_metatags_title_1='',v_metatags_keywords_1='',v_metatags_description_1='',v_products_sort_order='',v_featured_products='',v_featured_expires_date='',v_attributes_default='',v_size='',v_color='';

 

#导入批量表后修改SEO三要素状态

UPDATE products SET metatags_title_status = '1',
metatags_products_name_status = '0',
metatags_model_status = '0',
metatags_price_status = '1',
metatags_title_tagline_status = '0';
UPDATE specials SET expires_date = '2088-12-31';

 

#更改zencart评论时间

UPDATE reviews SET date_added = '2013-09-05 19:59:12' WHERE reviews_id = 222;

 

#设置zencart产品型号为“Model-产品ID”

update products set products_model=CONCAT('Model-',products_id);

#Ecshop清空代码

UPDATE Content SET 已采=0,已发=0,v_model='',v_image='',v_categories='',v_name='',v_description='',v_market_price='',v_shop_price='',v_promote_price='',v_promote_start_date='',v_promote_end_date='',v_brand_name='',v_metatags_title='',v_metatags_keywords='',v_metatags_description='',v_reviews='',v_feature='',v_size='',v_color='',v_goods_type='',v_name_color='',v_name_style='',v_quantity='',v_weight='',v_sort_order='',v_warn_number='',v_is_on_sale='',v_is_alone_sale='',v_is_best='',v_is_new='',v_is_hot='',v_is_shipping='',v_suppliers_id='',v_give_integral='',v_rank_integral='',v_goods_brief='',v_seller_note='';

UPDATE Content SET 已采=0,已发=0,v_model='',v_image='',v_categories='',v_name='',v_description='',v_market_price='',v_shop_price='',v_promote_price='',v_promote_start_date='',v_promote_end_date='',v_brand_name='',v_metatags_title='',v_metatags_keywords='',v_metatags_description='',v_reviews='',v_feature='',v_size='',v_color='',v_goods_type='',v_name_color='',v_name_style='',v_quantity='',v_weight='',v_sort_order='',v_warn_number='',v_is_on_sale='',v_is_alone_sale='',v_is_best='',v_is_new='',v_is_hot='',v_is_shipping='',v_suppliers_id='',v_give_integral='',v_rank_integral='',v_goods_brief='',v_seller_note='' where v_model='';

 

#其他

UPDATE Content SET v_image=replace(v_image, '<img src="images/','http://xxxxxcom/images/');
UPDATE Content SET v_image=replace(v_image, '">','');
UPDATE Content SET PageUrl = 'xxxxxcom';

UPDATE Content SET images=replace(images, '<img src="','http://xxxxxcom/');
UPDATE Content SET images=replace(images, '">','');
UPDATE Content SET PageUrl = 'xxxxxcom';

UPDATE Content SET v_description=replace(v_description, 'src="images/imgdesc/','src="httpxxxxcom/images/imgdesc/');

UPDATE `ecs_goods` SET goods_desc=replace(goods_desc, 'src="images/imgdesc1008/','src="httpxxxxcom/images/imgdesc1008/');
UPDATE `ecs_ad` SET ad_link=replace(ad_link, 'http://xxxxcom/','http://yyyycom/');

#设置价格
UPDATE Content SET v_shop_price = v_market_price;
UPDATE Content SET v_market_price = '';
UPDATE Content SET v_market_price = v_market_price*1.5;
UPDATE Content SET v_market_price=round(v_market_price,2);
UPDATE Content SET v_shop_price = v_shop_price*1.225;
UPDATE Content SET v_shop_price=round(v_shop_price,2);
UPDATE Content SET v_weight = v_weight/1000;
UPDATE Content SET v_weight=round(v_weight,2);
UPDATE Content SET v_weight=replace(v_weight, '500','0.3');
UPDATE `ecs_goods` SET goods_weight=replace(goods_weight, '500','0.3');

UPDATE Content SET v_products_price = v_products_price*1.6028;
UPDATE Content SET v_products_price=round(v_products_price,2);
UPDATE Content SET v_specials_price = v_specials_price*1.6028;
UPDATE Content SET v_specials_price=round(v_specials_price,2);

UPDATE specials SET specials_new_products_price=specials_new_products_price+10;
UPDATE products p,specials s SET p.products_price_sorter = s.specials_new_products_price WHERE p.products_id = s.products_id;
UPDATE products SET products_price=products_price+10;

SELECT * FROM Content WHERE v_model = '';
DELETE FROM Content WHERE v_products_model = '';
SELECT * FROM Content WHERE v_shop_price='';
SELECT * FROM Content WHERE v_weight = '';
SELECT * FROM Content WHERE [已发] = 0;

UPDATE Content SET v_goods_type = 'CAT属性';
UPDATE Content SET v_goods_brief = '';

UPDATE Content SET image=replace(image, '.jpg">','.jpg">|||');
UPDATE Content SET image=replace(image, '.png">','.png">|||');
UPDATE Content SET image=replace(image, '.gif">','.gif">|||');

UPDATE Content SET gallery = replace(gallery, image, '');


Access数据库删除重复记录，只保留一条记录的做法：

只保留id最小的记录方法：

delete from [表名] where id not in (select min(id) from [表名] group by [带重复记录的字段名称])

只保留id最大的记录方法：

delete from [表名] where id not in (select max(id) from [表名] group by [带重复记录的字段名称])


UPDATE Content SET v_size = '';
UPDATE Content SET v_color = '';
UPDATE Content SET v_model=replace(v_model, 'ID','ID-B');
UPDATE Content SET v_products_description_1 = '';


UPDATE Content SET v_cate1 = '连衣裙' where Id between 1136 and 1402;


SELECT * FROM Content WHERE v_categories_name_1 = '|||';
SELECT * FROM Content WHERE v_categories_name_1='';
SELECT * FROM Content WHERE v_products_price='';


UPDATE Content SET v_description=replace(v_description, 'src="images/pro/','src="httpxxxxcom/images/propro/');


UPDATE Content SET v_promote_price = '';
UPDATE Content SET v_products_price = '0.01';
SELECT * FROM Content WHERE v_categories = '';
SELECT * FROM Content WHERE v_shop_price = '';
SELECT * FROM Content WHERE [已发] = 0;

UPDATE Content SET v_brand_name = '万斯';

UPDATE `ecsp_goods` SET `shop_price` = '0.4' WHERE `ecsp_goods`.`shop_price` = '0';


SELECT PageUrl FROM Content WHERE v_image = '';
UPDATE Content SET v_image= 'httpxxxxcom/images/no_picture.gif' WHERE v_image= '';


chown -R admin:admin /home/admin/domains/xxxxcom/public_html

rm -rf /home/admin/domains/xxxxcom/*

source /home/admin/public_html/backupftp/backupallsql.sh
sh /home/admin/public_html/backupftp/backupallsql.sh


XP设置定时关机  at 23:59 shutdown -s

XP中自带了自动关机功能,在开始→运行中使用SHUTDOWN命令, 
如定时2分钟关机,运行如下: 
shutdown -s -t 120(-s为关机,-t为时间,120表示2分钟,还可以设置更高的时间，小时*60*60即可 
想要定点关机，就如12点关机，命令如下： 
at 12:00 shutdown -s 
想要每天都进行同样的关机命令，如下进行： 
1、打开记事本，新建文件“关机.bat”（注意要选择保存文件类型为－所有文件（如果不选择，此文件就不能执行）。 
2、输入“at 12:00 shutdown -s”保存。 
3、把关机.bat文件复制到－启动－文件夹中，也可以直接拖放到”开始→所有程序→启动“。 
以后系统开机就会自动运行“关机”文件。 
想取消自动关机,在运行中输入: 
shutdown -a

update products set products_price=products_price+5 where products_id between 1 and 200; 
update specials set specials_new_products_price=specials_new_products_price+5 where products_id between 1 and 200;