很有代表性的sql

--- UNION ALL 外连接（相同的数据不覆盖，展示所有数据）

SELECT sid,SUM(num) FROM 
(
SELECT sid,ROUND(count(sid)/2) as num FROM f_pile where manufacture = '深圳盛弘' GROUP BY sid
UNION ALL
SELECT sid,count(sid) AS num FROM f_pile where manufacture not in ('深圳盛弘') GROUP BY sid
) t GROUP BY t.sid

--- UNION 外连接 （完全相同的数据覆盖）

SELECT sid,num1 FROM num1 
UNION
SELECT sid,num2 FROM num2

 --- LEFT JION 左连接

SELECT
	* 
FROM
	f_order fo
	LEFT JOIN f_order_settlement fos ON fo.sn = fos.sn 
WHERE
	fo.createtime > '2020-01-10'

SELECT
	* 
FROM
	f_user fu
	LEFT JOIN f_order fo ON fu.uid = fo.uid
	LEFT JOIN f_order_settlement fos ON fo.sn = fos.sn 
WHERE
	fu.cid IS NOT NULL 
	AND fo.createtime > '2020-01-10'

--- RIGHT JION 右连接

SELECT
	* 
FROM
	f_order fo
	RIGHT JOIN f_user fu ON fu.uid = fo.uid
	LEFT JOIN f_order_settlement fos ON fo.sn = fos.sn 
WHERE
	fu.cid IS NOT NULL 
	AND fo.createtime > '2020-01-10'

--- INNER JION 内连接 （取相同的数据）

SELECT
	* 
FROM
	num1 u1
	INNER JOIN num2 u2 ON u1.sid = u2.sid

--- 向一张表，插入查询到的数据

INSERT INTO num1(sid,num1)
SELECT sid,num2 FROM num2

--- 将一张表的数据根据条件更新到另一张表中

UPDATE num1 n1
INNER JOIN ( SELECT sid, num2 FROM num2 ) n2 
SET n1.num1 = n2.num2 
WHERE
	n1.sid = n2.sid;

--- 更新表中数据

UPDATE f_order 
SET bef_eleprice = ele_price,
ele_price = 1.73,
bef_useele = useele,
repair_createtime = now(),
repair_remarke = '手动修改' 
WHERE
	oid = 9631;

--- 清空表

TRUNCATE TABLE IF EXISTS f_repair_e_order_history_20180722;

--- 删除表

DELETE FROM f_order where uid in (181,267,353,405);