droptable TABLE_NAME;TRUNCATEtable TABLE_NAME;DELETEFROM 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(CASEWHEN b.other_concat ISNULLTHEN0ELSE b.other_concat
END)AS other_counts
FROM table1 a
LEFTJOIN(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')GROUPBY t.CUST_NO
) b
ON a.CUST_NO = b.CUST_NO
WHERE a.APPLY_DT BETWEEN'20140101'AND'20190808'AND a.PROD_LINE ='PL201'GROUPBY substr(a.APPLY_DT,0,6)ORDERBY 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')GROUPBY t.CUST_NO
)/*主查询*/SELECT substr(a.APPLY_DT,0,6)AS apply_mth
,SUM(CASEWHEN b.other_concat ISNULLTHEN0ELSE b.other_concat
END)AS other_counts
FROM table1 a
LEFTJOIN TEMP1 b ON a.CUST_NO = b.CUST_NO
WHERE a.APPLY_DT BETWEEN'20140101'AND'20190808'AND a.PROD_LINE ='PL201'GROUPBY substr(a.APPLY_DT,0,6)ORDERBY substr(a.APPLY_DT,0,6);