1.查询两个表符合条件数据总数
SELECT t1.num1 + t2.num2 FROM (SELECT count(*) FROM t1 WHERE t1.id = ?) t1,
SELECT count(*) FROM t2 WHERE t2.id = ?) t2)
2. 删除重复数据,并保留一条记录
例如:
id name value
1 a pp
2 a pp
3 b iii
4 b pp
5 b pp
6 c pp
7 c pp
目的:删除name与value完全相同的数据,但保留一条。
SQL语句如下:
delete from table where [id] not in ( select max([id]) from table group by (name,value));
3.更新一张表的字段值为另一张表的字段值
update table2 set pwd=table1.ip from table1 inner join table2 on table1.username=table2.username
4.更新一张表里的数据值为另一张表数据
如图所示,现在需要批量更新table2表内字段Pwd,更新为userName对的IP地址,username与Ip对应关系如table1所示。
update table2 set pwd=table1.ip from table1 inner join table2 on table1.username=table2.username
5.group by后的结果将某组字段连接起来
select id,group_concat(content)
from tt group by id
6.插入一张表的数据到另一张表
insert into t_a_tmp_agreement SELECT * from t_a_agreement where id = 530332158744895488;