SQL基础知识

一.联结表（join）

1.为什么要把数据分多个表进行存储？

为了避免数据冗余，更加高效地进行数据处理，可伸缩性能好。

可伸缩性：能够适应不断增加的工作量而不失败。

2.创建联结表的原因？

联结是一种机制，用来在一条select语句中关联表，用一条语句检索出存储在多个表中的数据。

1.创建联结表

注意：完全限定列名

• 引用的列可能会出现歧义（同名不同表），此时必须使用完全限定列名，使用表名.列名。

1.1 Where子句的重要性（等值联结与笛卡尔积）

Where子句的作用：
作为过滤条件，只包含那些匹配给定条件的行（联结条件）。

这种联结称为等值联结。

如果不使用Where子句：

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

第一个表中的每一行将与第二个表中的每一行配对，而不管逻辑上是否配在一起。即笛卡尔积，也称交叉联结（cross join）。

1.2 内联接

等同于等值联结，但语法稍微不同。

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

1.3 联结多个表

方法：首先列出表名；然后定义表之间关系。

SELECT vend_name, prod_name, prod_price，quantity
FROM Vendors, Products, OrderItems
Where Vendors.vend_id =  Products.vend_id
AND OrderItems.vend_id =  Products.vend_id
AND order_num = 20007；

联结的表越多，性能下降的越厉害。

2.创建高级联结

2.1 使用表别名

可以为列名、字段名、表名起别名，其目的是：

• 缩短SQL语句；
• 允许在一条SELECT 语句中多次使用相同的表。

SELECT vend_name, prod_name, prod_price，quantity
FROM Vendors AS V, Products AS P, OrderItems AS O
Where V.vend_id =  P.vend_id
AND O.vend_id =  P.vend_id
AND order_num = 20007；

注意：

1）Oracle中不支持AS关键字，如要使用别名，则可以直接指定即可：

• Vendors V （而不是Vendors AS V,）

2）表别名只在查询执行中使用，与列别名不一样，表别名不返回到客户端。

2.2 使用不同类型的联结

1.自联结（self join）

方法：使用同一个表的不同别名，将相同表连接在一起

SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers AS c1, Customers AS c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones'；

一般情况下，使用自联结而不使用子查询：

• 许多DBMS处理联结远比处理子查询快得多。

SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers
WHERE cust_name IN (SELECT cust_name 
						FROM Customers WHERE cust_contact= 'Jim Jones');

2.自然联结（nature join）

对于联结条件中的列，只返回唯一的一列。

方法：自己在SELECT语句中设定。

3.外联结（outer join）

为何使用：

• 当需要包含那些没有关联上的行的时候

包含：

• left outer join
• right outer join
• full outer join**
  Access、MySQL、SQLite 不支持 FULL OUTER JOIN。

4.使用带聚集函数的联结

查询：每个顾客，及顾客的下单数

SELECT Customers.cust_id, COUNT(Orders_order_num) AS num_ord
FROM Customers INNER JOIN Orders
ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;

总结：

1.使用正确的联结：

• 注意区分内联接inner join 与 外联结 outer join，以及笛卡尔积 cross join

2.注意使用正确的联结语法：

• 应该查看具体的使用文档

3.一个联结中可以包含多个表，甚至可以对每个联结采用不同的联结类型：

• 这样合法也很有用
• 但应该在一起测试他们之前，分别测试每个联结。
• 也可以使用联结进行反向筛选：左联结+右表中字段为NULL条件

二.组合查询

SQL语句也允许执行多个查询（多条SELECT语句），并将结果作为一个查询结果集返回。

1.UNION规则：

1）必须由两条或两条以上的SELECT语句组成，语句之间用UNION分隔；

2）UNION每个查询必须包含相同的列、表达式或聚集函数（每个列不需要以相同的次序列出）

3）列数据类型必须相同：类型不必完全相同，但必须是DBMS可以隐含转换的类型；可以使用cast()函数转化。

2.包含或取消重复的行

1）UNION 从查询结果集中，自动去除重复的行；

2）如果想返回所有匹配的行：

• 使用UNION ALL

3.对组合查询进行排序

因为不存在使用一种方式对一部分进行排序，再使用另一种方式对另一部分进行排序，所以只能使用一个ORDER BY子句。

在最后一条SELECT子句中，使用ORDER BY子句。

4.其他类型UNION

1）EXCEPT：差集，在表1中但不包含同时在表2中的部分；

检索只在第一个表中存在，而在第二个表中不存在的行

2）INTERSECT：交集，选取表中公共部分

• 默认去除重复部分；
• 改变默认：intersect all

三.操作多个表

第一，查询需求的逻辑必须清楚；

第二，将总体查询，分为多个子模块——子查询；

第三，根据查询逻辑，联结各个子表；

第四，设置总表的查询条件。

四.Hive函数

Hive函数汇总: https://www.iteblog.com/archives/2258.html

重要的常用函数

1.聚合函数：

1）聚合函数分类：

• count()：
  • count(列名)：当将列名作为参数时，不会将NULL计入到内；
  • count(*)：而使用星号则会将NULL计入到内；
• sum()：不受1）的影响，不会考虑NULL行；
• avg()：不会将NULL行计算到内；
• min/max 几乎可以使用在任何数据类型
• 与distinct联用：count（distinct …）

2）与group by \ case when

count(distinct order_id)
count(case when 条件 then  字段/数值 [when ...  then ... ] else ...  end) as 别名  -- null不计数

2.case when

与聚合函数的联用

3.日期函数

注意：每个函数的参数类型与返回类型

1）基础函数
date_add、date_sub、datediff

weekofyear()

year()、month()、day()、hour()、minute()、second()

to_date()

2）时间戳函数：

unix_timestamp()

from_unixtime()

3）两个日期+时间的字段差值（单位：小时）：

unix_timestamp(date1) - unix_timestamp(date2)

4.条件函数

1）if(条件，T值，F值)

是否可以起到与case when类似的效果？？？

2）coalesce（）合并之意

返回括号内第一个不为Null的值，如果全为null则返回null.

3）case when ——前面已经介绍

5.字符串函数

1.字符串连接：

1）直接拼接：contact（）

2）带分隔符拼接：contact_ws（‘-’，…，…，…）

2.字符串截取：

substr(string A, int B[, intC])：截取A的第B个及以后的[C个]字符

3.字符串去空格：

trim()、ltrim()、rtrim()

4.正则表达式替换/解析

5.Length()：返回字符串长度，注意是字节还是字符

1）区分字节与比特

• Byte——字节：1B = 8b
• bit——比特，位：1或0

2）区分字节与字符

• 半角英文字符——占用1个字节
• 汉字：全角字符——占用2个字节

等等

6.类型转换函数:cast（）

类型转换函数

7.nul()函数

hive中与null有关的函数,如nvl,coalesce,is null 等判断是否为null，是为true

1）NVL(E1, E2) ：如果E1为NULL，则函数返回E2，否则返回E1本身。

2）NVL2(E1, E2, E3) ：如果E1为NULL，则函数返回E3，若E1不为null，则返回E2。

8.其他各类运算函数

统计函数

逻辑运算符号

数值计算符号——如加减乘除

数学运算函数——如平方、取整

9.Hive复杂数据类型/查询/explode与lateral view/长度[未完]

1.Map类型：key-value型

查找：M[key]

• key为什么数据类型就填什么

2.Array类型：数组型

查找：A[int]

3.Struct类型：结构体型

查找：S.x

4.长度：size（）

5.explode与lateral view

https://blog.youkuaiyun.com/gdkyxy2013/article/details/78683165

https://blog.youkuaiyun.com/guodong2k/article/details/79459282

五. over() 函数

https://blog.youkuaiyun.com/burpee/article/details/55517434

https://blog.youkuaiyun.com/scgaliguodong123_/article/details/60135385

定义

窗口函数：也称为OLAP函数（online analytical processing 实时分析处理），兼具分组和排序两种功能。

开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。

1.具体用法：

1）语法：

<窗口函数> OVER( [PATITION BY <列清单>] 
										OVER BY <排序用列清单>)

2）能够作为窗口函数使用的函数：

• 聚合函数
• 专用窗口函数：RANK/DENSE_RANK/ROW_NUMBER

3）partition by：

• 设定排序的对象范围；
• 决定纵向上的分组；
• 分组之后的记录集合称为“窗口”（与group by分组后的“组”对应）；

4）order by：

• 指定排序对象按照哪一列、何种方式排序；
• 决定纵向排序规则；

5）使用位置：select语句中

• 在select子句之外使用窗口函数是没有意义的。

6）over()子句中的order by 只是用来决定窗口函数按照什么样的顺序进行计算，对结果的排列顺序并没有什么影响。

• 仅仅有些DBMS会按照子句中指定的顺序对结果排序，仅仅是个例；
• 为了保证查询结果按照指定列排序，还需在最末尾加上第二个order by子句

举例：

1.over(order by salary）

• 按照salary排序进行累计，order by是个默认的开窗函数

2.over(partition by deptno）

• 按照部门分区

3.over(partition by deptno order by salary）

• 按照部门分区
• 部门内按salary排序

2.计算移动平均

1）range between 数值 preceding and 数值 following： 数值范围

over（order by salary range between 5 preceding and 5 following）

• 窗口范围为当前行数据幅度减5加5后的范围内的。

举例：

sum(s)over(order by s range between 2 preceding and 2 following) 
--表示加2减2的范围内的求和

--sum(s)over(order by s range between 2 preceding and 2 following) 表示加2或2的范围内的求和

SELECT	
		 name,class,s, 
		 SUM(s)  OVER(ORDER BY s RANGE BETWEEN 2 Preceding AND 2 Following) mm 
From t2

结果：
adf        3        45        45  		--45加2减2即43到47，但是s在这个范围内只有45
asdf       3        55        55
cfe        2        74        74
3dd        3        78        158	 --78在76到80范围内有78，80，求和得158
fda        1        80        158
gds        2        92        92
ffd        1        95        190
dss        1        95        190
ddd        3        99        198
gf         3        99        198

2）rows between … preceding and … following ：行数范围

over(order by salary rows between 5 preceding and 5 following）

• 窗口范围为当前行前后各移动5行。

举例：

--sum(s)over(order by s rows between 2 preceding and 2 following)\
--表示在上下两行之间的范围内
select 	name,class,s, 
			sum(s)over(order by s rows between 2 preceding and 2 following) mm 
from t2

结果：
adf        3        45        174  （45+55+74=174）
asdf       3        55        252   （45+55+74+78=252）
cfe        2        74        332    （74+55+45+78+80=332）
3dd        3        78        379    （78+74+55+80+92=379）
fda        1        80        419
gds        2        92        440
ffd        1        95        461
dss        1        95        480
ddd        3        99        388
gf         3        99        293

3）窗口不做限制：

• over（order by salary range between unbounded preceding and unbounded following）
• over（order by salary rows between unbounded preceding and unbounded following）

3.排序函数 over()

1）row_number() over() 顺序排序：

1、2、3、4、5

2）rank() over() 跳跃排序（班级排名）：

1、2、2、4、5——并列第二

3）dense_rank() over() 连续排序：

1、2、2、3、4

4.聚合函数 over()

聚合函数计算当前记录之前的记录：

• 会以当前记录为基准，来决定汇总对象的记录。

1）sum() over()

作用：累加

• 按照分组中顺序进行累加——注意并列值看为同一位

select 
			name,class,s,
			 sum(s) over(partition by class order by s desc) mm 
from t2 

根据班级进行分数求和：
dss        1        95        190			--由于两个95都是第一名，所以累加时是两个第一名的相加
ffd        1        95        190 
fda        1        80        270		  	--第一名加上第二名的
gds        2        92        92
cfe        2        74        166
gf         3        99        198
ddd        3        99        198
3dd        3        78        276
asdf       3        55        331
adf        3        45        376

2）avg() over()

作用：与sum函数一致

3）count() over() ——？？？

5.Grouping运算符

1）作用

如何同时得到：

• 1）分组的小计；
• 2）总体的合计；

2）运算符：

• rollup;
• cube;
• grouping sets;

3)Rollup：

• 得到正常分组以外的超级分组记录，即合计行；
• 合计行（超级分组记录）对应的聚合键（分组依据的字段）的值，为NULL;

如何判断聚合键（分组依据的字段）的值为NULL时，是超级分组记录，还是普通的该字段的值就位NULL？？？

方法：grouping（）函数

• 该函数，在其参数列（聚合键,或者其他列）的值为超级分组纪录所产生的NULL时，返回1，其他情况返回0。

select  case when GROUPING(product_type) = 1   --值为1表示该行为总体合计行：group by()，参数为NULL，则对应聚合函数列之外的所有字段值为NULL
								then '商品种类 合计'
								else product_type 
								end as product_type，
			case when GROUPING(regist_date) = 1 		--值为1表示该行为各组商品种类合计行：group by(product_type)，参数为聚合键1，则对应sum列与聚合键1对应列之外的字段为NULL
								then '登记日期 合计'
								else cast(regist_date as VARCHAR(16)) 
								end as regist_date，
			sum(sale_price) as sum_price			--该行为各商品种类不同日期分组的合计行：group by(product_type,sale_price)，聚合键1、2的值确定
from product
group by ROLLUP(product_type, regist_date);		--分组小计与合计

**ROLLUP(product_type, regist_date)**所得到的汇总结果：

• group by()
• group by(product_type)
• group by(product_type,sale_price)

4）CUBE()

• 将GROUP BY子句中聚合键的“所有可能的组合”的汇总结果集合到一个结果中；

将3)中的ROLLUP直接换为CUBE，CUBE(product_type, regist_date) 所得到的汇总结果：

• group by()
• group by(product_type)
• group by(sale_price) ——追加的合计行
• group by(product_type,sale_price)

5）GROUPING SETS()

• 只希望选取参数列表中的单个字段作为聚合键的结果；

GROUPING SETS(product_type, regist_date) 所得到的汇总结果：

• group by(product_type)
• group by(sale_price)

6.Hive窗口函数Lead\Lag\First_Value\ Last_Value[未完]

https://blog.youkuaiyun.com/sunnyyoona/article/details/56484919

六.其他注意细节

注意细节，避免语法错误：
1）标点：如逗号，最后是否必须加分号
2）联结表的重复字段的使用，需要加表名
3）使用汉语别名时，需要用引号括起来：“订单数”
4）单行注释“–”，多行注释“/*多行内容*/”

1.逻辑的三种值：TRUE,FALSE,UNKNOWN

unknown：用于对NULL值的比较运算时产生的结果

2.值为NULL时：

1）算术运算：对其进行任何算术运算（加减乘除）——结果都为NULL

2）进行任何比较运算——均无法判定是真还是假，是除真假之外的第三种unknown，所以不会输出。

3）判定只能是 IS NULL 或 IS NOT NULL

4）聚合函数：只有count(*)会将NULL计入到内

• count()：
  • count(列名)：当将列名作为参数时，不会将NULL计入到内；
  • count(*)：而使用星号则会将NULL计入到内；
• sum()：不受1）的影响，不会考虑NULL行；
• avg()：不会将NULL行计算到内；
• 与distinct联用：count（distinct …）

5）使用distinct时，NULL也被视为一行，而且只会保留一行；

6）group by()：会将NULL视为一组特定数据

某种意义上，distinct 与 group by 作用是一样的。

7)ABS(Null)：结果为Null

3.与聚合函数和group by有关的常见错误

1）使用聚合函数时，select子句只能包含以下三种元素：

• 聚合函数；
• 常数；
• group by子句中指定的列名——也就是聚合键；

2）group by不能使用select子句中定义的列别名 ——因为，group by 和where并用时，Select的执行顺序为：

FROM—WHERE—GROUP BY—SELECT

这样select中的列别名就不会被group by识别。

3）group by子句的结果的显示是无序的

4）只有SELECT、HAVING、ORDER BY子句可以使用聚合函数，where中不可；

可以认为，where 子句是用来指定“记录”的条件

4.HAVING 子句

1）having子句能够使用的三种要素：

• 常数
• 聚合函数
• group by子句中指定的列名（聚合键）

2)可以认为，HAVING子句是用来指定“组”的条件

但是，聚合键对应的条件更适合放于where子句中。

3）having子句，位于group by之后

5.ORDER BY 对查询结果进行排序

1）位置必须位于整个查询语句末尾；

因为，对数据进行排序的操作必须在结果即将返回的时候进行。

2）order by 中的的列名——排序键；

3）升序：ascendent(上升的) ASC[默认]
降序：descendent(下降的) DESC

4）可以指定多个排序键

5）NULL值的排序：

因为NULL值不能用来被比较，所以会被排序到开头或是结尾汇总显示，具体的由DBMS决定。

6）不同于group by 子句不能使用别名，order by子句可以使用select子句中的别名——因为Select的执行顺序为：

FROM—WHERE—GROUP BY—HAVING—SELECT—ORDER BY

具体的执行策略，根据DBMS不同而不同，这只是粗略的总结。

7）注意，order by可以使用：

• 不包含在select子句中的列；
• 聚合函数（执行顺序在最后）；

8）可以使用**select子句中的列编号，但不推荐使用。

七.数据更新

1.insert into

1）数据插入

insert into 表名 （列名1，列名2，…） values（值1，值2，…）

insert into 表名 values（值1，值2，…）

2）从其他表复制

insert into 表名 （列名1，列名2，…） SELECT 其他表的列名 FROM 其他表 where … group by …

但order by没有任何作用

3）默认值的显式插入：值n为——DEFAULT

4）默认值的隐式插入：对应列名及值都省略

5）插入null值：值n为——NULL

2.delete

1）DROP TABLE 表名：将表完全删除，对象是“表”；

2）DELETE FROM 表名：清空表，对象是“记录”；

3）DELETE FROM 表名 WHERE 条件；

4）不能使用其他语句；

5）truncate 表名：只能删除表中的数据，执行速度快，不同DBMS定义不同

3.update

--更新全部
update 表名
	set 列名=值;

--筛选更新
update 表名
	set 列名=值
where 条件;

--多列更新
update 表名
	set 列1=值，列2=值，...
where 条件;

update 表名
	set (列1,列2，...)  = (值1，值2，...)
where 条件;

4.事务

1）什么是事务 transaction?

事务就是需要在同一个处理单元中执行的一系列更新处理集合，是对表中数据进行更新的单位。

如：把“运动T恤”的价格上浮100，“衬衫”价格下浮100

2）创建事务：

语法：

事务开始语句；

	DML语句1；		--DML语句包括：INSERT/DELETE/UPDATE
	DML语句2；		--“运动T恤”的价格上浮100
	DML语句3；		--“衬衫”价格下浮100
	...
	
事务结束语句（COMMIT 或 ROLLBACK）；

①事务开始语句：

• SQL Sever、PostgreSQL——begin transaction
• MySQL——Start transaction
• Oracle、DB2——无，那么事务开始的时间点在哪？？？

②DML语句包括：INSERT/DELETE/UPDATE

③事务结束语句（通用）：

• commit
• rollback

3）COMMIT——提交处理

COMMIT是提交事务包含的全部更新处理的结束指令；

一旦事务提交，文件将覆盖保存，无法恢复事务之前的状态。

4）ROLLBACK——取消处理

是取消事务包含的全部更新处理的结束指令；

一旦回滚，数据库就会恢复到事务开始之前的状态。

5）在不使用指令而悄悄开始事务的情况下，应该如何区分各个事务？？？

①每条SQL语句就是一个事务——自动提交模式；

• 此模式下，delete命令执行之后不可撤销

②直到用户执行事务结束语句为止，算作一个事务。

默认使用①模式的有：SQL Sever、MySQL、PostgreSQL

• 该模式下每一天DML语句都包含在事务开始与结束语句中

默认②模式的是：Oracle

• 事务都是直到用户自己执行提交或者回滚指令才会结束

6）ACID特性

DBMS的事务都遵循四种特性:

①原子性Atomicity：指事务结束时，其中包含的更新处理，要么全部执行，要么完全不执行；

②一致性Consistency：指事务中包含的处理，要满足数据库提前设置的约束，如主键约束或者NOT NULL约束。

③隔离性Isolation：指保证不同事务之间互不干扰的特性，在一个事务提交执行之前，其他事务不可见。

④持久性Durability：指的是事务结束后（不论是提交还是回滚），DBMS能够保证该事件点的数据状态会被保存的特性。用于保证数据能够恢复。

方法：将事务的执行记录保存起来——日志，发生故障时，可以通过日志恢复。

八.复杂查询

1.视图

1）优点：

• 1）无需保存数据，节省存储设备容量；
• 2）可以将频繁使用的select语句保存为视图，不用重复书写。

2）语法：

CREATE VIEW 视图名（列名1，...）
	AS
	SELECT 语句

3）注意：

• 多重视图会降低SQL性能；
• 应该避免在视图基础上创建视图；

4）视图的限制：

• 定义视图的时候，不能使用ORDER BY子句（因为数据行是没有顺序的）

5）往视图中插入数据，要注意一些限制：

• 定义视图的select语句中未使用DISTINCT；
• FROM子句中只有一个表；
• 未使用GROUP BY子句；
• 未使用HAVING子句；

6）删除视图：

DROP VIEW 视图名

2.子查询

1）原则上，子查询必须设定名称；

2）子查询执行顺序先于父查询；

3.标量子查询：

1）只返回一行一列的结果的子查询

**2）常用方法：**用于WHERE子句中，因为其不可以使用聚合函数；

3）使用位置：任意可以使用单一值的位置都可使用（所有地方）

4）**执行顺序：**先执行标量子查询，后将唯一结果带入外层查询进行执行

5）注意事项：绝对不能返回多个结果

4.关联子查询

--筛选，每类产品中，大于该类产品均价的商品的相关信息
SELECT  product_type，product_name，sale_price
FROM product AS P1
WHERE sale_price > (SELECT avg(sale_price)			--关联子查询，只会返回单一值
										from product AS P2
										where P1.product_type = P2.product_type  --结合条件一定要写在子查询中
										);
										
--使用标量子查询，不满足查询需求；

--如果子查询使用分组，则语法错误：
SELECT  product_type，product_name，sale_price
FROM product AS P1
WHERE sale_price > (SELECT avg(sale_price)			--子查询，返回多个值，错误
										from product AS P2
										group by product_type
										);

注意：关联子查询的结合条件一定要写在子查询中

九.谓词、case表达式

1.谓词：

• like
• between and
• in / not in
• is null / is not null
• exists 存在

注意：IN可以使用子查询

2.case when

1）最好不要省略 else null 语句

十.

七.全量与增量[还未理解透]

https://blog.youkuaiyun.com/yangtongli2012/article/details/51725408

1）什么是数据抽取

数据抽取是指从源数据源系统抽取目的数据源系统需要的数据。实际应用中，数据源较多采用的是关系数据库。

2）数据抽取的方式

(一) 全量抽取

全量抽取类似于数据迁移或数据复制，它将数据源中的表或视图的数据原封不动的从数 据库中抽取出来，并转换成自己的 ETL 工具可以识别的格式。全量抽取比较简单。

(二) 增量抽取

增量抽取只抽取自上次抽取以来数据库中要抽取的表中新增或修改的数据。

在ETL 使用过程中，增量抽取较全量抽取应用更广。如何捕获变化的数据是增量抽取的关键。

对捕获方法一般有两点要求：

• 准确性：能够将业务系统中的变化数据按一定的频率准确地捕获到；
• 性能：不能对业务系统造成太大的压力，影响现有业务。目前增量数据抽取中常用的捕获变化数据的方法有：