mysql 实用sql 汇总
update select 从查询结果中更新数据
示例场景
两个表 ordrs, sum_orders, sum_orders 中的count是 orders中相同project_id的count之和;cost同理
一条语句直接更新sum_orders中的count & cost
#orders
#sum_orders
示例代码
UPDATE sum_orders a INNER JOIN ( SELECT project_id, sum( count ) AS sum_count, sum( cost ) AS sum_cost FROM orders GROUP BY project_id ) b ON a.project_id = b.project_id SET a.count = b.sum_count, a.cost = b.sum_cost;
再加条件
UPDATE T1, T2, [INNER JOIN | LEFT JOIN] T1 ON T1.C1 = T2. C1
SET T1.C2 = T2.C2, T2.C3 = expr
WHERE condition
delete join 写法
delete d from member_log d left join member m ON d.uid=m.uid where date(d.date)='2014' and m.uid is null ;
粗略估量查询行数--快速
select TABLE_ROWS from information_schema.`TABLES` WHERE TABLE_NAME = 'xxxx'
数据来描述表的信息,包括表的名字、表的大小等信息,这些信息被称为元数据,所以应该先想到的是mysql是如何存储表的元数据的