sql

替换：update b2b_theme_template set template_content = REPLACE(template_content,'http://cdn.njw88.com/njw_20151119/564d8c7c85b34.jpg','http://njwcdn.njw88.com/njw_20151223/567a2c27a6c76.jpg');

当前执行sql语句及连接数：show processlist;

#每周第一天：
SELECT CURDATE(), DATE_ADD(CURDATE(),INTERVAL -WEEKDAY(CURDATE()) DAY);

#每月第一天：
SELECT CURDATE(), DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY);

SELECT * ,FROM_UNIXTIME(createdate,'%Y-%m-%d %H:%i:%s') FROM ttq_account limit 2;
UNIX_TIMESTAMP(createdate)

多个表，只导出数据
mysqldump -uxx -pxx -t -single-TRANSACTION --FLUSH-LOGS --MASTER-DATA=2 dbname table1 table2 table3 .... > data.sql

导入
mysql -uxx -p databasename < data.sql

导出sql
mysqldump -uroot -pnjw88 ttq_ucenter sys_param success_killed>sql.sql

所有权限
CREATE DATABASE IF NOT EXISTS bbcdb DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.24.166.26' IDENTIFIED BY 'njw88' WITH GRANT OPTION;

创建用户
CREATE USER 'nowphp'@'%' IDENTIFIED BY 'jiangshilin';
GRANT ALL ON *.* TO 'nowphp'@'%';
flush privileges;

不存在就插入，存在更新 唯一索引，主键更新
INSERT INTO pc_sku_base (grade,sku) VALUES ('A','A301') ON DUPLICATE KEY UPDATE grade=VALUES(grade)

删除数据清理表
UPDATE bb_sku_info_copy SET attr_value='',desc_main='',desc_en='',
desc_zh='',desc_attr='',img_attr='',mtime='',unplatsite=''
,enname='',cnname='',category_1='',category_2='',category_3=''
,logic_attr='',weight='',asweight='',price='',length='',width='',height=''
,aslength='',aswidth='',asheight='',is_multiplicity='',color='',size='',type='';

清理表多余空间
OPTIMIZE TABLE bb_sku_info_copy;

简单存储过程+游标
CREATE TABLE `pr_user` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`user` varchar(255) DEFAULT NULL,
`dp` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

CREATE TABLE `pr_user_tmp` (
`user` varchar(255) DEFAULT NULL,
`dp` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP PROCEDURE IF EXISTS sys_user ;
CREATE PROCEDURE sys_user()
BEGIN
DECLARE done int;
DECLARE u VARCHAR(255);
DECLARE d VARCHAR(255);
DECLARE u_c CURSOR FOR SELECT user u,dp d from pr_user ORDER BY id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN u_c;
c:LOOP
FETCH u_c INTO u,d;
if done=1 then
leave c;
ELSE
IF d='d1' THEN
INSERT INTO pr_user_tmp VALUES(u,d);
END if;
end if;
END LOOP c;
CLOSE u_c;
end;
CALL sys_user();
SELECT * from pr_user_tmp;
mysql所有服务器授权：grant all privileges on *.* to root@"%" identified by 'njw88.com' with grant option; flush privileges;
mysql重置密码：mysqladmin -u root password "newpass"

 

唯一主键sku,warehouseid 插入或更新：INSERT INTO stock_order(sku,warehouseid,sku_qty) VALUES('A301',1,'442') ON DUPLICATE KEY UPDATE sku_qty=VALUES(sku_qty);

 

sql变量累加（sku，仓库，且小于当前数据库时间字段）

UPDATE finance_day_invoicing a, (
SELECT id,
@qs:=if(@sku<=>sku and @warehouseid<=>warehouseid and @reference_date<reference_date , @qs, iqs) as iqs,
@cs:=if(@sku<=>sku and @warehouseid<=>warehouseid and @reference_date<reference_date , @cs, ics) as ics,
@qs:=@qs+wq10+wq20+wq30+wq80+wq110+wqother-dq10-dq20-dq100-dq110-dq120-dqother as iqe,
@cs:=@cs+wc10+wc20+wc30+wc80+wc110+wcother-dc10-dc20-dc100-dc110-dc120-dcother as ice,
@sku:=sku, @warehouseid:=warehouseid, @reference_date:=reference_date
FROM finance_day_invoicing
,( SELECT @sku:=null, @warehouseid:=null, @reference_date:=null ) _r
ORDER BY sku, warehouseid, reference_date
) b
set a.iqs=b.iqs, a.ics=b.ics, a.iqe=b.iqe, a.ice=b.ice
where a.id=b.id