SQL学习记录（一）

跟着Udacity上的SQL课程学完有一段时间了，通过总结来加深一哈印象。（个人认为这个课程不错，网址(https://classroom.udacity.com/courses/ud198)）

课程只有七节课，会按照顺序记录

• Basic
• Join
• Aggregation
  • 常用
  • 日期
  • CASE
• Subqueries
• Data Cleaning
• Windows Functions
• SQL Advanced

1、Basic

SQL是结构化的查询语句，它主要是对关系型的数据库来说的。这个语句主要可以分为四类：

• DDL

  数据定义语言，主要是用来定义数据库的对象，例如数据库，表，列之类的。主要是对应数据库和表的CRUD。（现在的客户端操作方便，这方面很多都没有去注意。）

• DML

  数据操作语言，主要是增删改。主要是

//增(一一对应，也可省略不写，默认添加所有列)
INSERT INTO 表名(列名1,列名2,列名3,...) VALUES (值1,值2,值3,...) ;
//删
DELETE FROM 表名 WHERE 条件 ；

TRUNCATE TABLE 表名；//删除整张表的记录的效率会更高
//改
UPDATE 表名 SET 列1=值1,列2=值2,列3=值3... WHERE 条件;
           

• DQL

  数据查询语言，主要是查询表，这也是课程主要讲的内容。

  最基础的查询主要有

  

  这个写（详细点的后面会总结）

  

• DCL

  数据控制语言，主要是定义访问权限和安全级别。

SQL语言支持的是三级模式结构，如下图所示

2、Join

这一块的内容主要是针对于两张表以上的查询，这有利于我们从多张表里取数据，主要分为：

1. 内连接

内连接分为隐式内连接和显式内连接，二者只是写法不同，查询的都是两张表的交集部分

//显示(条件中的字段名加单引号)
SELECT 字段名 FROM 表1 JOIN 表2 ON 条件;
//隐式(条件中的字段名加单引号)
SELECT 字段名 FROM 表1,表2 WHERE 条件;
           

为了书写简单，最好都起别名进行书写。

2.外连接

外连接主要有 LEFT JOIN 、RIGHT JOIN 、FULL JOIN

写法跟内连接显示写法相同，返回的结果集不同。

3、Aggregation

SQL中提供得聚合函数有，COUNT、SUM、AVG、MAX、MIN。一般运用在select后面的对象中，其中需要注意的是在聚合函数遇到空值的时候，除了COUNT()外所有的函数皆跳过空值，只处理非空值。（可以用IFNULL(列名,置为的值)*函数处理）。

聚合函数一般与GROUP BY和HAVING 一起使用，实现分组聚合

• GROUP BY 是对查询结果按照某一列或者某几列进行分组，值相等的分为一组。
• HAVING对分组的结果进行选择，仅输出满足条件的组。（必须与GROUP BY配合使用）通常更多是用于对于聚合之后的元素进行选择。例如

//有多少用户有20个以上的订单（COUNT的对象就是连接之后的虚拟表）
SELECT a.id, a.name, COUNT(*) num_orders
FROM accounts a
JOIN orders o
ON a.id = o.account_id
GROUP BY a.id, a.name
HAVING COUNT(*) > 20
ORDER BY num_orders;
           

特殊并常用的。对于日期的聚合，对于时间戳聚合显然是不常用的。对此SQL引入了两个函数

• DATE_TRUNC

//查询2014年到2017年之间每个月销售总数量
SELECT DATE_TRUNC('month', occurred_at) ord_month, SUM(total_amt_usd) total_spent
FROM orders
WHERE occurred_at BETWEEN '2014-01-01' AND '2017-01-01'
GROUP BY 1
ORDER BY 2 DESC; 
           

• DATE_PART

  对于这函数以及更多有关日期的函数的使用可以参看文档（https://www.postgresql.org/docs/9.1/functions-datetime.html）

CASE情况也是聚合常用的。CASE必须包含以下组件:WHEN、THEN和END。ELSE是一个可选组件，用于捕获不满足任何其他先前案例条件的案例。通过一个例子可以了解

SELECT s.name, COUNT(*), SUM(o.total_amt_usd) total_spent, 
     CASE WHEN COUNT(*) > 200 OR SUM(o.total_amt_usd) > 750000 THEN 'top'
     WHEN COUNT(*) > 150 OR SUM(o.total_amt_usd) > 500000 THEN 'middle'
     ELSE 'low' END AS sales_rep_level
FROM orders o
JOIN accounts a
ON o.account_id = a.id 
JOIN sales_reps s
ON s.id = a.sales_rep_id
GROUP BY s.name
ORDER BY 3 DESC;
           

通过以下的结果集可以看到，实现的是一个分级的效果

4、Subqueries

这一节主要是讲的子查询和临时表，其中需要注意的是可以用WITH来书写来避免多级的嵌套带来的理解上的困难

例如下面这个语句就是将t1、t2作为临时表

WITH t1 AS (
   SELECT r.name region_name, SUM(o.total_amt_usd) total_amt
   FROM sales_reps s
   JOIN accounts a
   ON a.sales_rep_id = s.id
   JOIN orders o
   ON o.account_id = a.id
   JOIN region r
   ON r.id = s.region_id
   GROUP BY r.name), 
t2 AS (
   SELECT MAX(total_amt)
   FROM t1)
SELECT r.name, COUNT(o.total) total_orders
FROM sales_reps s
JOIN accounts a
ON a.sales_rep_id = s.id
JOIN orders o
ON o.account_id = a.id
JOIN region r
ON r.id = s.region_id
GROUP BY r.name
HAVING SUM(o.total_amt_usd) = (SELECT * FROM t2);
           

（这么多JOIN，其实我觉得视图可能更简单）

另外，子查询中不要使用ORDER BY

5、Data Cleaning

在这一章介绍了几个常见的函数

用几个例子来记录一哈

WITH t1 AS (
//STRPOS返回数字
 SELECT LEFT(primary_poc, STRPOS(primary_poc, ' ') -1 ) first_name,  RIGHT(primary_poc, LENGTH(primary_poc) - STRPOS(primary_poc, ' ')) last_name, 
 name
 FROM accounts)
 
SELECT first_name, last_name, 
//REPLACE此处是去掉空格
CONCAT(first_name, '.', last_name, '@', REPLACE(name, ' ', ''), '.com')
FROM  t1;
           

SELECT date orig_date, 
//::DATE是说拼接之后的作为日期CAST(date_column AS DATE) 和 date_column::DATE是一样的
(SUBSTR(date, 7, 4) || '-' || LEFT(date, 2) || '-' || SUBSTR(date, 4, 2))::DATE new_date
FROM sf_crime_data;
           

6、Windows Functions

不想写了，想起来再补