mysql两表间select/update/delect

--创建测试表，插入测试数据
create table t1(
    col1 varchar(10),
    col2 varchar(10)
);
create table t2(
    col1 varchar(10),
    col3 varchar(10)
);

--查询，效率对比未知
select t1.*,t2.* from t1,t2 where t1.col1 = t2.col1;    --先连接再查询
select t1.*,t2.* from t1 join t2 on t1.col1 = t2.col1;  --先查询在连接

--更新
update t1,t2 set t1.col2 = t2.col3 where t1.col1 = t2.col1;
update t1 join t2 on t1.col1 = t2.col1 set t1.col2 = t2.col3;

--oracle中可行，mysql报错
--[Err] 1288 - The target table t of the UPDATE is not updatable
update (select t1.col2 f2,t2.col3 f3 from t1,t2 where t1.col1=t2.col1) t set t.f2=t.f3;
update (select t1.col2 f2,t2.col3 f3 from t1 left join t2 on t1.col1 = t2.col1) t set t.f2=t.f3;

--删除
delete t1.*,t2.* from t1,t2 where t1.col1 = t2.col1;
delete t1.* from t1 join t2 on t1.col1 = t2.col1;