SQL基础知识

1. 引言

1.1 数据库的优点：

1. 数据完整性
2. 同时访问
3. 访问快速

1.2 SQL格式要求

1. 虽然SQL对大小写不敏感，但要求对关键词如SELECT和FROM要使用大写。方便阅读。
2. 避免在表名和变量名中使用空格。
3. 建议在语法结束后加“;”

SELECT account_id //示范，orders是数据库中一个表的名字，account_id是orders表中一个列的名字
FROM orders;

2. 数据查询SELECT

2.1 SELECT

就像顺序词汇书的第一个单词肯定是abandon，SQL要学习的第一个关键词肯定是SELECT了。SELECT的中文意思是选择，在SQL中用来选择列。
语法格式：

SELECT 列名
FRMO  表名；

如果要查询多个列：

SELECT 列名1,列名2,列名3
FROM 表名；

如果要查询表中所有的列：

SELECT * //*（星号）表示所有列
FROM 表名；

2.2 LIMIT

LIMIT中文意思是限制，在SQL中用来限制结果输出的行数。
语法格式：

SELECT *
FROM 表名
LIMIT 10;//显示表中所有列的前10行

2.3 WHERE

WHERE关键词的作用是筛选符合条件的数据。
语法格式：
1.等于（=）

SELECT *
FROM 表名
WHERE name = 'Mike'//字符、字符串加单引号,数字不用加引号。LIKE要加通配符%，IN （'string','string'）
//找到列name中等于Mike的所有行

2.部分匹配（LIKE）
比如我不想找名字是Mike的同学了，我想找名字首字母是M的同学。那么可以用LIKE加通配符（%）的方法来实现我的愿望。

SELECT *
FROM 表名
WHERE name LIKE 'M%'//LIKE要加通配符%，IN （'string','string'）
//找到列name中首字母是M的所有行

我现在又想找名字中间含有k的同学，

SELECT *
FROM 表名
WHERE name LIKE '%k%'//LIKE要加通配符%，IN （'string','string'）
//找到列name中含有字母k的所有行

诶呀，老师让我找名字最后一个字母是e的同学

SELECT *
FROM 表名
WHERE name LIKE '%e'//LIKE要加通配符%，IN （'string','string'）
//找到列name中末字母是e的所有行

3.多重选择（IN）
如果我想找Mike同学和Lisa同学的信息，除了可以一个一个用“=”来查询外，可以使用IN来一次性获取。

SELECT *
FROM 表名
WHERE name IN ('Mike','Lisa')//IN ('string','string')
//找到列name中等于Mike或Lisa的所有行

4.利用OR实现IN的作用
AND和OR，顾名思义，分别表示对条件判断从句进行“与"或者"或"运算

SELECT *
FROM 表名
WHERE name = 'Mike' OR name = 'Lisa'//IN 
//找到列name中等于Mike或Lisa的所有行

2.4 排序（ORDER BY）

我有一张学生成绩表：
表名：student
name、grade、age都是列名

现在我想查询这张表的所有列，并将结果按成绩由低到高排列。这就需要ORDER BY 来排下顺序。

SELECT name, grade
FROM student
ORDER BY grade；//ORDER BY默认排序是从低到高排

输出结果

如果想要从高到低排序，在ORDER BY 语句的最后加上 DESC ，即descend的缩写

SELECT name, grade
FROM student
ORDER BY grade DESC；//从高到低排序

输出结果

ORDER BY后面可以接多个列

SELECT name, grade
FROM student
ORDER BY grade,age DESC；//从高到低排序

先按grade排序，再按age排序
输出结果

2.5 总结

SELECT col1, col2
FROM table1
WHERE col3  > 5 AND col4 LIKE '%os%'
ORDER BY col5,col6 DESC
LIMIT 10;

3. 多表查询JOIN

3.1 JOIN

SELECT DISTINCT* //distinct去除重复的行
FROM orders (AS) o //o是orders的别名，AS可以省略
(LEFT) JOIN accounts a//a是accounts的别名
ON o.account_id=a.id 
AND a.occured_at=2016_10_10

JOIN…ON…用来连接两个表。JOIN后接需要连接的表名，ON后接连接两个表的桥梁，也就是主键和外键。
默认的JOIN是INNER JOIN，可以理解为两张表取交集。还有LEFT JOIN、RIGHT JOIN和OUTER JOIN。OUTER JOIN可以理解为两张表取并集，LEFT JOIN可以理解为两张表的交集并上左边的表（左边的表也就是在程序中第一次出现的表）。RIGHT JOIN正好相反，是两张表的交集并上右边的表（右边的表也就是在程序中最后出现的表）

3.2 ALIAS别名

通过给变量起别名可以简化程序。语法格式：

变量名+空格+别名
JOIN accounts a
变量+AS+别名
JOIN accounts AS a

4. Aggregation分组

4.1 GROUP BY

分组是为了方便将一张表按某一属性划分为多个组合，然后组合自己内部进行排序等运算。
比如上表，可以按年龄进行分组，相同年龄的为一组，然后在每个组内进行排序。

SELECT *
FROM student
GROUP BY age //按年龄分组
ORDER BY grade;//按成绩排序

结果如下：

4.2 COUNT\SUM\MIN\MAX\AVG

常用的统计函数

//NULL 非数值，并不是0，表示空
//COUNT 统计非空行数
SELECT COUNT
a.name a_name,o.name o_name //a.name,o.name在输出时的列名都是name，所以需要取别名区分
SUM(total) sum_total, //对total取和
MIN(total) min_total, //取total最小值
MAX(total) max_total, //取total最大值
AVG(total) avg_total, //取total平均值
DATE_TRUNC('day',occurred_at)， //时间戳截断，day之后的时间归零 2016-10-30 12：01：59-> 2016-10-30 00:00:00
DATE_PART('month',occurred_at)，//截取时间戳的月份;'dow':day of week,0表示周日，6表示周六
CASE WHEN total>500 THEN 'TOP' 
     WHEN total>300 THEN 'Middle'
     ELSE 'Low' END  //同if else从句
GROUP BY a.name //以a分组
HAVING a.name='lisa' //group的对象不能使用where
ORDER BY 2

以下部分有时间后再完善。

5. subquery子表

//在条件从句中，不要给subquery起别名，因为这里它只相当于一个独立的数值，而不是表
//创建一个子表：
WITH table1 AS (SELECT * //table1是子表的名字，括号内是子表
FROM accounts)，
     table2 AS (SELECT name
     FROM orders
     )
//用with创建多个表格时，每个表格中间用逗号隔开，最后一个表格除外

6. Data cleaning

一些关于字符串处理的命令

POSITION(',' IN city_state)
STRPOS(city_state, ',')
SUBSTR(date, 4, 2)//从第四个符号开始取2位
//对大小写敏感，可以使用UPPER(),LOWER()使所有符号编程大写或小写
CONCAT(first_name, '.', last_name, '@', name, '.com')
first_name|| '.'|| last_name||'@'|| name|| '.com'
//连接字符串
CAST或::可以实现string和number之间的转换
COALESCE(a.id, a.id)第一个a.id为空，则使用第二个a.id的值//处理NULL时使用

7. 窗口函数

窗口的含义和分组有点像，利用PARTITOIN BY将表格按某一列分成一个个窗口（逻辑上的），ORDER BY指定函数操作的对象

RANK() OVER(PARTITOIN BY  ORDER BY ) AS
ROW_NUMBER //评级函数
NTITLE(100) OVER() AS //百分比函数
LAG() OVER() //the previous 在当前行显示前一行的内容
LEAD() OVER//the next 在当前行显示后一行的内容
WINDOW w1 AS (PARTITOIN BY  ORDER BY )//ALIAS

8. JOIN

JOIN用法进阶
不等式JOIN
SELECT accounts.name as account_name,
       accounts.primary_poc as poc_name,
       sales_reps.name as sales_rep_name
  FROM accounts
  LEFT JOIN sales_reps
    ON accounts.sales_rep_id = sales_reps.id
   AND accounts.primary_poc < sales_reps.name
UNION
UNION ALL