好记性不如烂笔头，这话说的太有道理了，一段时间不写它，还真容易忘记，本文只是一篇笔记而已。
1.将数据从t1表导入到t2表
insert into t2 (c1,c2) select c1,c2 from t1 [where c1 = xx and c2 = xx order by c1]
2.使用t2表的name来更新t1表的name
update t1 as a, t2 as b set a.name = b.name where a.tid = b.id
3.两表的关联更新
update t_role_user as a, ( select id from t_user where departid in ( select id from t_depart where length(org_code) = 9 )) as bset a.roleid = '123456'where a.userid = b.id
4.自己和自己关联更新
update t_depart as a, ( select id, substring(org_code, 1, 6) org_code from t_depart where length(org_code) = 8 and parent_depart_id is not null) as bset a.parent_depart_id = b.idwhere substring(a.org_code, 1, 6) = b.org_code
5.两表关联删除，将删除两表中有关联id并且t2表name为空的两表记录
delete a,b from t1 as a left join t2 as b on a.tid = b.id where b.name is null
6.将统计结果插入到表
insert into se_stat_org ( record_date, org_id, org_name, sign_cont_count, sign_arri_cont_count, sign_cont_money, sign_arri_cont_money, total_arri_cont_count, total_arri_money, publish_total_count, project_count) select *from ( select '2012-06-09' record_date, parent_org_id, parent_org_name, sum(sign_cont_count) sign_cont_count, sum(sign_arri_cont_count) sign_arri_cont_count, sum(sign_cont_money) sign_cont_money, sum(sign_arri_cont_money) sign_arri_cont_money, sum(total_arri_cont_count) total_arri_cont_count, sum(total_arri_money) total_arri_money, sum(publish_total_count) publish_total_count, sum(project_count) project_count, from se_stat_user where date_format(record_date, '%y-%m-%d') = '2012-06-09' group by parent_org_id ) m
7. 三表关联更新
update se_stat_user a, ( select user_id, sum(invest_org_count + financial_org_count + intermediary_org_count + enterprise_count) as common_count from se_stat_user where date_format(record_date, '%y-%m-%d') = '2012-06-09' group by user_id) b, ( select user_id, sum(establish_count + stock_count + merger_count + achieve_count) as project_count from se_stat_user where date_format(record_date, '%y-%m-%d') = '2012-06-09' group by user_id) cset a.common_count = b.common_count, a.project_count = c.project_countwhere a.user_id = b.user_idand a.user_id = c.user_idand date_format(a.record_date, '%y-%m-%d') = '2012-06-09'
8.带条件的关联更新
update se_stat_user a, ( select p.channel, count(p.cont_id) as cont_count, c.cust_mgr_id from ( select channel, cont_id from sk_project where project_status = 6 and date_format(audit_time, '%y-%m-%d') = '2012-06-11' ) p inner join se_contract c on p.cont_id = c.cont_id group by p.channel, c.cust_mgr_id) bset a.stock_count = case when b.channel = 2 then b.cont_count else 0 end, a.establish_count = case when b.channel = 3 then b.cont_count else 0 end, a.achieve_count = case when b.channel = 4 then b.cont_count else 0 end, a.brand_count = case when b.channel = 5 then b.cont_count else 0 end, a.merger_count = case when b.channel = 6 then b.cont_count else 0 endwhere a.user_id = b.cust_mgr_idand date_format(a.record_date, '%y-%m-%d') = '2012-06-11'
9. 加索引
alter table project add index index_user_id (user_id), add index index_project_status (project_status);
10.删除列
alter table project drop column project_status, drop column expect_return,drop column currency;
11.增加列
alter table project add column dict_id int default null comment 'xxx' after project_site, add column introduce text default null comment 'xx' after dict_id, add column stage int default null comment 'xx' after id, add column attach_uri varchar(8) default null comment 'xxx' after introduce;
12.修改列
一般用modify修改数据类型，change修改列名。
alter table project change dict_id dict_id1 int not null, modify project_status tinyint not null comment 'xxx';
13.修改自增字段开始值不同数据库此属性不一样，mysql 与oracle,ms sql server不一样，见具体文档
http://www.w3school.com.cn/sql/sql_autoincrement.asp
自增
alter table persons auto_increment=1;