insert into table_B select * from table_A

truncate table test #清空表，结构还存在
delete table test where ... #删除表中特定的数据
drop table test #删除整表，包括结构

select * from table_A where filed1 like ‘8422%‘ into outfile ‘/usr/local/mysql/8422.txt‘ fields terminated by ‘||‘

LOAD DATA INFILE "/root/user/output.txt" INTO TABLE table_A CHARACTER SET gbk fields terminated by x‘09‘ IGNORE 1 LINES;
#字符集有时添加后会报错，可以去掉

#一对一的情况
update table_A a, table_B b set a.field1 = b.field1 where a.field2 = b.field2

#一对多的情况
update table_A a inner join table_B b on a.route_id = b.valid_route_id set a.valid_route_id=b.valid_route_id

UPDATE table_A set SIC_DESC=concat_ws(‘|‘,SICCODE1,SICCODE2,SICCODE3,SICCODE4)

create table table_A ENGINE=MyISAM select locname,substring(file_name,1,3) from table_B where LOCNAME !=‘‘

alter table table_A drop column source_path

select * from table_A a where oid=(select min(oid) from table_A where chain_id=a.chain_id)

#先添加一个rowid
select *from  sh_20110401_bankup as T where 3>(select count(*) from sh_20110401_bankup 
where field1=T.field1  and rowid>T.rowid order by T.field1,T.rowid desc )

#通过两步
(1)create table sh_20110301_0331_ptp_t select  record_date,substring(time_stamp,1,2)as tim,count(*)as cc from sh_20110301_0331_ptp
group by substring(time_stamp,1,2),record_date order by record_date,substring(time_stamp,1,2)

(2)select * from sh_20110301_0331_ptp_t where cc in (select max(cc) from sh_20110301_0331_ptp_t
group by tim) order by tim 

update table_A set custom11=substring_index(custom11,‘;‘,1)

update table_A set convert_date=unix_timestamp(record_date)
 tt=TIME_TO_SEC(time_stamp)

update table_A set dd = MD5(CONCAT_ws(‘$‘,id,attribute_key,attribute_value)

select * from review where period_diff(date_format(now() , ‘%Y%m‘) , date_format(`Review_Date`, ‘%Y%m‘)) =1;

select Rating,count(*) from review where week(`Review_Date`)=7  group by Rating

select week(`Review_Date`) from review where date_sub(curdate(), INTERVAL 7 DAY) <= date(`Review_Date`) 

update table_A set tt= str_to_date(date_created,‘%m/%d/%Y‘)