常用的MySQL语句

mysql的sql语句写法，除了那些基本的之外，还有一些也算比较常用的，这里记录下来，以便以后查找。

将数据从t1表导入到t2表

insert into t2 (c1,c2) select c1,c2 from t1 [where c1 = xx and c2 = xx order by c1]

使用t2表的name来更新t1表的name

update t1 as a, t2 as b set a.name = b.name where a.tid = b.id

两表的关联更新

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 b
set a.roleid = '123456'
where
a.userid = b.id

自己和自己关联更新

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 b
set a.parent_depart_id = b.id
where
substring(a.org_code, 1, 6) = b.org_code

两表关联删除，将删除两表中有关联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

将统计结果插入到表

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

三表关联更新

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
) c
set a.common_count = b.common_count, a.project_count = c.project_count
where a.user_id = b.user_id
and a.user_id = c.user_id
and date_format(a.record_date, '%y-%m-%d') = '2012-06-09'

带条件的关联更新

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
) b
set
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 end
where
a.user_id = b.cust_mgr_id
and date_format(a.record_date, '%y-%m-%d') = '2012-06-11'

加索引

alter table project add index index_user_id (user_id),
add index index_project_status (project_status);

删除列

alter table project drop column project_status,
drop column expect_return,drop column currency;

增加列

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;

修改列，一般用modify修改数据类型，change修改列名

alter table project change dict_id dict_id1 int not null,
modify project_status tinyint not null comment 'xxx';