SQL 学习笔记

删除DataTable

Example :DROP TABLE TablenName

数据表取别名，在该 sql 语句中可以通过别名取字段

Example  ：SELECT u.email , p.productname FROM UserInfo u , ProductList p;

BETWEEN  :取范围内的数据

SELECT username , dateline FROM UserInfo WHERE dateline BETWEEN Value1 and Value2;

从结果中取值:

SELECT username , dateline FROM UserInfo WHERE username IN  (SELECT username FROM UserList);

SORT       :先排序后取值

Example  ：SELECT username,dateline from UserInfo SORT BY dateline 

Example  ：SELECT username,dateline from UserInfo SORT BY dateline DESC

Group by :一般用于取数量。取不重复值，没有重复项，Group by 后面的字段必须和 Select 后的项相同

Example  ：SELECT COUNT(*) email , producname from UserInfo GROUP BY email,productname;

DISTINCT:取不重复值，按照 DISTINCT 后所有列取唯一值

SELECT DISTINCT email FROM UserInfo;

限制小数位：转换为总长度为 18 小数位为 4 的数值型数字

SELECT CAST ((CAST(24 AS NUMERIC(18,4))/CAST(38 AS NUMERIC(18,4))) AS NUMERIC(18,4))

LEFT OUTER JOIN :结果中包含左边 UserInfo 所有 email ，右边 ProductList 没有就置空

SELECT email , pid FROM UserInfo U LEFT OUTER JOIN ProdcutList P ON U.email = P.email;

建立临时缓存表:

SELECT email , productname INTO #TempTable FROM UserInfo;