前言

分享SQL学习过程，归纳重点，记录其在数据仓库（ETL）开发中的应用及本人所踩过的坑。

一、MySQL

（一）SQL必知必会

1. 了解SQL

（1）数据库（database）, 数据库管理系统（DBMS）
（2）表（table），mysql中，表的含义是存储数据的结构化文件。
（3）列（column），列表示表中的一个字段。
（4）行（row），行表示表中的一条记录。
（5）主键（primary key），主键是一个字段或多个字段的组合（联合主键），用来唯一标识表中的一条记录。
（6）SQL语言分类，数据查询语言（DQL，如SELECT，FROM，WHERE）；数据操纵语言（DML，如INSERT，UPDATE，DELETE）；数据定义语言（DDL，如CREATE TABLE）；数据控制语言（DCL，如GRANT）。

2. 检索数据

（1）DISTINCT，指示查询只返回不同的值。
注意点1：不能部分使用DISTINCT，DISTINCT关键字作用于所有的列，不仅仅是跟在其后面的那一列。例如，你指定如下查询，因为指定的两列不完全相同，所以所有列都会被检索出来。

SELECT DISTINCT
    vend_id
   ,prod_price
FROM Products;

注意点2：DISTINCT因为限制较多（不能和聚合函数并列使用）且性能较差，在数据仓库中Hive SQL通常不用DISTINCT，而用GROUP BY去重。

（2）LIMIT指定返回的行数，LIMIT带的OFFSET指定从哪里开始。
注意点1：第0行，第一个被检索的行是第0行，而不是第1行。因此如下查询会检索第2行，而不是第1行。

SELECT
    prod_name
FROM Product
LIMIT 1 OFFSET 1;

3. 排序检索数据

（1）ORDER BY，默认是ASC升序排列，降序需要指定DESC关键字。
注意点1：如果想在多个列上进行降序排序，必须对每一列指定DESC关键字。

SELECT
    prod_id
   ,prod_price
   ,prod_name
FROM Products
# ORDER BY prod_price;
ORDER BY prod_price DESC, prod_name DESC;

4. 过滤数据

（1）WHERE子句操作符

操作符	中文含义
=	等于
<> / !=	不等于
<	小于
<=	小于等于
!<	不小于
>	大于
>=	大于等于
!>	不大于
BETWEEN	在两个指定值之间
NULL	为NULL值

（2）NULL，过滤字段为NULL时用IS，不用等于号。

WHERE prod_price IS NULL

5. 高级数据过滤

（1）AND在求值过程中优先级更高，最好使用小括号对操作符进行明确分组，避免与OR等连用时执行顺序错误。

SELECT
    prod_name
   ,prod_price
FROM Products
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price >= 10;
# 不加小括号会先执行 vend_id='BRS01' AND prod_price >= 10

（2）IN操作符，在WHERE子句中用来指定要匹配值得清单，功能与OR相当，但执行更快。

SELECT
    prod_name
   ,prod_price
FROM Products
WHERE vend_id IN ('DLL01', 'BRS01');
# WHERE (vend_id='DLL01' OR vend_id='BRS01')

6. 用通配符进行过滤（耗时长，需考虑性能问题）

（1）%匹配多个字符

SELECT
    prod_name
FROM Products
WHERE prod_name LIKE 'Fish%';

输出
prod_name
------------------------
Fish bean bag toy

（2）_匹配单个字符

SELECT
    prod_name
FROM Products
WHERE prod_name LIKE '__ inch teddy bear';

输出
prod_name
------------------------
12 inch teddy bear
18 inch teddy bear

（3）[]匹配指定位置的1个字符，如下匹配[JM]中J或者M

SELECT
    cust_contact
FROM Customers
WHERE cust_contact LIKE '[JM]%'
ORDER BY cust_contact;

输出
cust_contact
------------------------
Jim Jones
John Smith
Michelle Green

7. 创建计算字段

（1）常用操作符

操作符	说明
+	加
-	减
*	乘
/	除

（2）执行算术计算，汇总订单物品总价。

SELECT
    prod_id
   ,quantity
   ,item_price
   ,quantity * item_price    AS expanded_price
FROM OrderItems
WHERE order_num = 20008;

8. 使用函数处理数据

（1）常用文本处理函数

函数	说明
LEFT() （或使用子字符串函数）	返回字符串左边的字符
RIGHT()（或使用子字符串函数）	返回字符串右边的字符
LENGTH()（也使用DATALENGTH()或LEN()）	返回字符串长度
LOWER()（Access使用LCASE()）	将字符串转为小写
UPPER()（Access使用UCASE()）	将字符串转为大写
LTRIM()	去左边空格（比如：字段首）
RTRIM()	去右边空格（比如：字段末）

（2）常用数值处理函数

函数	说明
ABS()	返回一个数的绝对值
PI()	返回圆周率
SQRT()	返回平方根

9. 汇总数据

（1）SQL聚集函数

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列之和

注意点1：AVG()，MAX()，MIN()和SUM()函数会忽略列值为NULL的行。
注意点2：使用COUNT(1)和COUNT(*)对表中的数据进行计算，不管表中列包含的是空值（NULL）还是非空值，都会计算进来。使用COUNT(column)进行计算时，会忽略NULL值。

# COUNT(*)
SELECT
    COUNT(*)    AS num_cust
FROM Customers;

输出
num_cust
------------------------
5

# COUNT(column)
SELECT
    COUNT(cust_email)    AS num_cust
FROM Customers;

输出
num_cust
------------------------
3

10. 分组数据

（1）HAVING和WHERE的区别。WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。WHERE排除的行不包括在分组中，这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。

SELECT
    vend_id
   ,COUNT(*)    AS num_prods
FROM Products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 2;

（2）SELECT子句顺序，在写SELECT语句时必须遵循如下次序。

子句	说明
SELECT	要返回的列或表达式
FROM	从中检索数据的表
WHERE	行级过滤
GROUP BY	分组说明
HAVING	组级过滤
ORDER BY	输出排序顺序

11. 使用子查询（需根据实际情况采用，使用联结通常性能更好）

（1）如下，把第一个查询变为子查询。

# 第一个查询
SELECT
    order_num
FROM OrderItems
WHERE prod_id = 'RGAN01';

输出
order_num
------------------------
20007
20008

# 第二个查询
SELECT
    cust_id
FROM Orders
WHERE order_num IN (20007, 20008);

输出
cust_id
------------------------
1000000004
1000000005

# 把第一个查询变为子查询
SELECT
    cust_id
FROM Order_num IN
(
    SELECT
        order_num
    FROM OrderItems
    WHERE prod_id = 'RGAN01'
);

输出
cust_id
------------------------
1000000004
1000000005

12. 联结表

（1）WHERE关联

SELECT
    vend_name
   ,prod_name
   ,prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;

（2）笛卡尔积，由没有联结条件的表关系返回的结果为笛卡尔积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

SELECT
    vend_name
   ,prod_name
   ,prod_price
FROM Vendors, Products;

（3）内联结（取交集）

SELECT
    vend_name
   ,prod_name
   ,prod_price
FROM Vendors
INNER JOIN Products
ON Vendors.vend_id = Products.vend_id;

13. 创建高级联结

（1）外联结 LEFT OUTER JOIN，左表所有行匹配右表。
（2）全联结 FULL OUTER JOIN，取全部，包含不关联的行。

14. 组合查询

（1）UNION和UNION ALL，UNION会自动去除重复的行，UNION ALL不去除重复的行。

15. 插入数据

（1）插入检索出的数据

INSERT INTO Customers (cust_id, cust_name, cust_address)
VALUES ('1006', 'Toy Land', '123 Any Street');

（2）从一个表复制到另一个表

CREATE TABLE CustCopy AS
SELECT
*
FROM Customers;

16. 更新删除数据

（1）更新数据，SET命令用来将新值赋给被更新的列。

UPDATE Customers
SET cust_email = 'kim@thetoystore.com'
WHERE cust_id = '1005';

（2）删除数据，如下操作为删除cust_id = '1006’的行。DELETE不删除表结构，如果需要删除表中的所有行，使用TRUNCATE TABLE速度更快（因为不记录数据的变动）。

DELETE FROM Customers
WHERE cust_id = '1006';

TRUNCATE TABLE Customers;

（3）删除表

# 删除数据同时删除表结构
DROP TABLE Customers;

17. 创建和操纵表

（1）创建表

CREATE TABLE Products
(
    prod_id       CHAR(10)         NOT NULL
   ,vend_id       CHAR(10)         NOT NULL
   ,prod_name     CHAR(254)        NOT NULL
   ,prod_price    DECIMAL(8, 2)    NOT NULL
   ,prod_desc     VARCHAR(1000)    NULL
);

（2）更新表

ALTER TABLE Vendors ADD vend_phone CHAR(20);
ALTER TABLE Vendors DROP COLUMN vend_phone;

18. 使用视图

（1）原理，视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询。
（2）缺点，因为视图不包含数据，所以每次使用视图时，都必须处理查询执行需要的所有检索，如果用多个联结和过滤创建了复杂的视图或者嵌套了视图，性能可能下降得很厉害。
（3）作用，视图提供了一种封装SELECT语句的层次，可用来简化数据处理提高复用率，重新格式化或保护基础数据。

# 查询订购了产品'RCAN01'的顾客
SELECT
    cust_name
   ,cust_contact
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num;
AND prod_id = 'RCAN01';

# 把上述查询封装成视图（虚拟表）以便复用
CREATE VIEW ProductCustomers AS
SELECT
    cust_name
   ,cust_contact
   ,prod_id
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num;

# 利用视图查询订购了产品'RCAN01'的顾客
SELECT
    cust_name
   ,cust_contact
FROM ProductCustomers
WHERE prod_id = 'RCAN01';

# 增加一个'()'的格式效果
SELECT
    RTRIM（vend_name） + '(' + RTRIM(vend_country) + ')'    AS vend_title
FROM Vendors
ORDER BY vend_name;

输出
vend_title
------------------------
Bear Emporium (USA)
Bears R US (USA)
Fun and Games (England)

# 如果经常需要这个格式效果，可以创建视图来使用
CREATE VIEW VendorLocations AS
SELECT
    RTRIM（vend_name） + '(' + RTRIM(vend_country) + ')'    AS vend_title
FROM Vendors;

# 查询增加'()'的格式效果的视图
SELECT
    vend_title
FROM VendorLocations;

19. 使用存储过程

（1）存储过程就是为以后使用而保存的一条或多条SQL语句。可将其视为批文件，虽然其作用不仅限于批处理。
（2）执行存储过程

# 将一个新产品添加到Producs表中，如下存储过程有四个参数：供应商ID，产品名，价格和描述
EXECUTE AddNewProduct
(
    'JTS01'
   ,'Stuffed Eiffel Tower'
   ,6.49
   ,'Plush stuffed toy with the text La Tour Eiffel in red' 
);

（3）创建存储过程

# Oracle版本，对邮件发送清单中具有邮件地址的顾客进行计算
CREATE PROCEDURE MailingListCount
(
    ListCount OUT INTEGER
)
IS v_rows INTEGER;

BEGIN
    SELECT
        COUNT(*) INTO v_rows
    FROM Customers
    WHERE NOT cust_email IS NULL;
    ListCount := v_rows;
END;

# 调用
var ReturnValue NUMBER
EXEC MailingListCount(:ReturnValue);
SELECT ReturnValue;

20. 管理事务处理

（1）使用事务处理，通过确保成批的SQL操作要么完全执行，要么完全不执行，来维护数据库的完整性。

21. 使用游标

（1）游标（sursor）是一个存储在DBMS服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。
（2）创建游标

# 创建游标来检索没有电子邮件地址的所有顾客
DECLARE CustCursor CURSOR
FOR
SELECT
    cust_name
FROM Customers
WHERE cust_email IS NULL;

# Oracle版本
DECLARE CustCursor CURSOR
IS
SELECT
    cust_name
FROM Customers
WHERE cust_email IS NULL;

（3）使用游标

OPEN CURSOR CustCursor

# Oracle版本，从游标中检索第一行
DECLARE TYPE CustCursor IS REF CURSOR
    RETURN Customers%ROWTYPE;
DECLARE CustRecord Customers%ROWTYPE
BEGIN
    OPEN CustCursor;
    FETCH CustCursor INTO CustRecord;
    CLOSE CustCursor;
END;

22. 高级SQL特征

（1）主键，主键是一种特殊的约束，用来保证一列（或者一组列）中的值是唯一的，而且永不改动，主键不能为空且不能重复。如果没有主键，要安全地UPDATE或DELETE特定行而不影响其他行会非常困难。

# 建表时直接设置主键
CREATE TABLE Vendors
(
    vend_id         CHAR(10)    NOT NULL PRIMARY KEY
   ,vend_name       CHAR(10)    NOT NULL
   ,vend_address    CHAR(50)    NULL
   ,vend_city       CHAR(50)    NULL
   ,vend_state      CHAR(5)     NULL
   ,vend_zip        CHAR(10)    NULL
   ,vend_country    CHAR(50)    NULL
);

# 给现有表添加主键
ALTER TABLE Vendors
ADD CONSTRAINT PRIMARY KEY (vend_id);

（2）外键，外键是表中的一列，其值必须列在另一表的主键中，外键是保证引用完整性极其重要的部分。如下：通过Customers表中cust_id，关联Orders表中的cust_id，来获取顾客的订单情况。

# 建表时直接设置外键
CREATE TABLE Orders
(
    order_num    INTEGER    NOT NULL PRIMARY KEY
   ,order_data   DATETIME   NOT NULL
   ,cust_id      CHAR(10)   NOT NULL REFERENCES
);

# # 给现有表添加外键
ALTER TABLE Orders
ADD CONSTRAINT FOREIGN KEY (cust_id) REFERENCES Customers (cust_id);

（二）上述样例表表结构

1. Vendors表

列	说明
vend_id	唯一供应商ID
vend_name	供应商名
vend_address	供应商地址
vend_city	供应商所在城市
vend_state	供应商所在州
vend_zip	供应商地址邮编
vend_country	供应商所在国家

2. Products表

列	说明
prod_id	唯一产品ID
vend_id	唯一供应商ID（关联到Vendor表的vend_id）
prod_name	产品名
prod_price	产品价格
prod_desc	产品描述

3. Customers表

列	说明
cust_id	唯一顾客ID
cust_name	顾客名
cust_address	顾客地址
cust_city	顾客所在城市
cust_state	顾客所在州
cust_zip	顾客地址邮编
cust_country	顾客所在国家
cust_contact	顾客联系名
cust_email	顾客电子邮件地址

4. Orders表

列	说明
order_num	唯一订单号
order_data	订单日期
cust_id	唯一顾客ID（关联到Customers表的cust_id）

5. OrderItems表

列	说明
order_num	唯一订单号（关联到Orders表的order_num）
order_item	订单物品号（订单内的顺序）
prod_id	产品ID（关联到Products表的Prod_id）
quantity	物品数量
item_price	物品价格

（三）数据类型

1. 字符串数据类型

（1）定长字符串和变长字符串，DBMS处理定长列远比处理变长列快得多。
注意点1：字符串值都必须括在引号内。

数据类型	说明
CHAR	1~255个字符的定长字符串，长度必须在创建时规定
NCHAR	CHAR的特殊形式，用来支持多字节或Unicode字符
NVARCHAR	TEXT的特殊形式，用来支持多字节或Unicode字符
TEXT（也称LONG、MEMO或VARCHAR）	变长文本

2. 数值数据类型

注意点1：数值不应该括在引号内。

数据类型	说明
BIT	单个二进制位值，或者为0或者为1，主要用于开/关标志
DECIMAL（或NUMRIC）	定点或精度可变的浮点值
FLOAT（或NUMBER）	浮点值
INT（或INTEGER）	4字节整数值，支持-2147483648~2147483647的数
REAL	4字节浮点值
SMALLINT	2字节整数值，支持-32768~32767的数
TINYINT	1字节整数值，支持0~255的数

3. 日期和时间数据类型

数据类型	说明
DATE	日期值
DATETIME（或TIMESTAMP）	日期时间值
SMALLDATETIME	日期时间值，精确到分（无秒或毫秒）
TIME	时间值

4. 二进制数据类型

（1）二进制数据类型可包含任何数据，甚至包含二进制信息，如图像、多媒体、字处理文档等。

数据类型	说明
BINARY（或VARBINARY）	定长二进制数据（最大长度从255B到8000B）
LONG RAW	变长二进制数据，最长2GB
RAW	定长二进制数据，最多255B

（四）注意点整理

1. 引擎InnoDB和MyISAM的选择

（1）考虑是否要支持事务，如果要选择InnoDB，如果不需要可以考虑MyISAM。
（2）如果日常绝大多数都只是查询，可以考虑MyISAM，如果既有读写也挺频繁，使用InnoDB。
（3）系统奔溃后，MyISAM恢复起来更困难。
（4）MySQL5.5版本开始InnoDB已经成为默认引擎。

2. Hive2mysql时遇到的特殊字段类型

（1）字段长度过长时建议使用TEXT（同样会很占用空间），不用过长的VARCHAR（比如：VARCHAR(5000)）
（2）含有错别字、特殊字符或者表情包类的字段可以使用BLOB字段类型（比如：用户评论字段的字段类型可以使用BLOB，防止出现表情包类的信息时，字段格式为VARCHAR会插入报错），BLOB最大字符约为65KB，TINYBLOB最大字符为255B。

3. mysql表结构设计

（1）设计mysql表结构的时候，应考虑后续查询的性能问题，字段长度设置的时候不应过长，够用就好，否则会占用过多空间，查询时增加IO负担。
（2）用作计算或者排序的字段应使用int等数值数据类型，不使用VARCHAR等字符串数据类型，避免计算或者排序时出错。
（3）小数类字段类型使用DECIMAL（比如：金额字段用DECIMAL(12, 2)）

4. mysql新增字段

（1）当表数据量为千万级以下时，加多个字段用一条语句完成。因为当有复杂SQL运行时，加字段时会导致锁表，假设一条加字段语句执行需要20min，用n条语句加n个字段则执行时间变为20min * n，所以建议用一条语句完成。

二、HiveSQL

（一）常用函数

函数	说明
SUM()	求和
COUNT()	求数量
SUBSTR()	截取字符串
MAX()	取最大
MIN()	取最小
ROW_NUMBER()	排序取最新值
LOWER()	转小写
UPPER()	转大写
RLIKE/NOT RLIKE	B是否在A里（支持正则）
LIKE/NOT LIKE	B是否在A里（不支持正则）
COALESCE(参数1, 参数2, 参数n)	依次查各参数，遇到非NULL值即停止并返回该值
CASE WHEN	当XX时返回XXX
FROM_UNIXTIME(UNIX_TIMESTAMP(xxx), ‘yyyy-mm-dd hh:mm:ss’)	时间戳
TO_DATE()	转yyyy-mm-dd的时间格式
DATE_FORMAT()	转自定义时间格式
CONCAT(参数1, 参数2)	拼接参数
REPLACE()	替换
GET_JSON_OBJECT	解析json

（二）注意点整理

注意点1：表JOIN前先过滤数据，减少JOIN的数据量。
注意点2：多次用到同一个查询，使用临时表以便复用。

三、参考书籍

（一）《SQL必知必会（第4版）》 【美】Ben Forta 著 钟鸣 刘晓霞 译
（二）《Hive编程指南》 【美】Edward Capriolo Dean Wampler Json Rutberglen 著 曹坤 译