SQL杂谈

1.drop、truncate、delete的区别

1.1 解释

• drop：删除表结构及所有数据，并将表所占用的空间全部释放，不会触发触发器，不能回滚。如无备份，谨慎使用。
• truncate：一次性地从表中删除所有的数据。不会触发触发器，不能回滚。如无备份，谨慎使用。
• delete：逐行删除，激活删除触发器，可回滚。

drop table TABLE_NAME;
TRUNCATE table TABLE_NAME;
DELETE FROM TABLE_NAME where CONDITIONS; #不加条件则逐行删除全表数据;

1.2 速度

• drop > truncate > delete

2.with as

• 指定临时命名结果集，称为公用表表达式(CTE)。
• 可认为在真正进行查询之前预先构造了一个临时表，之后便可以重复使用它。

2.1 优点

• 增加了sql的可读性
• 提高sql运行效率

2.2 缺点

• 无法在mysql中使用，用子查询等代替。

2.3 示例

• 不使用with as

SELECT substr(a.APPLY_DT, 0, 6) AS apply_mth
	, SUM(CASE 
		WHEN b.other_concat IS NULL THEN 0
		ELSE b.other_concat
	END) AS other_counts
FROM table1 a
	LEFT JOIN (
		SELECT t.cust_no, COUNT(t.contact_tel) AS other_concat
		FROM table2 t
		WHERE t.cust_no IN (
			SELECT t.cust_no
			FROM table1 t
			WHERE t.APPLY_DT BETWEEN '20140101' AND '20190808'
				AND t.PROD_LINE = 'PL201'
		)
		GROUP BY t.CUST_NO
	) b
	ON a.CUST_NO = b.CUST_NO
WHERE a.APPLY_DT BETWEEN '20140101' AND '20190808'
	AND a.PROD_LINE = 'PL201'
GROUP BY substr(a.APPLY_DT, 0, 6)
ORDER BY substr(a.APPLY_DT, 0, 6)

• 使用with as

WITH TEMP1 AS (
		SELECT t.cust_no, COUNT(t.contact_tel) AS other_concat
		FROM table2 t
		WHERE t.cust_no IN (
			SELECT t.cust_no
			FROM table1 t
			WHERE t.APPLY_DT BETWEEN '20140101' AND '20190808'
				AND t.PROD_LINE = 'PL201'
		)
		GROUP BY t.CUST_NO
	)
/*主查询*/
SELECT substr(a.APPLY_DT, 0, 6) AS apply_mth
	, SUM(CASE 
		WHEN b.other_concat IS NULL THEN 0
		ELSE b.other_concat
	END) AS other_counts
FROM table1 a
	LEFT JOIN TEMP1 b ON a.CUST_NO = b.CUST_NO
WHERE a.APPLY_DT BETWEEN '20140101' AND '20190808'
	AND a.PROD_LINE = 'PL201'
GROUP BY substr(a.APPLY_DT, 0, 6)
ORDER BY substr(a.APPLY_DT, 0, 6);