数据库介绍

这是本人的个人学习笔记，如有错误，请多指正。

数据库简介

其实数据库就是存放数据的地方，有了数据库之后我们可以直接查找数据。 Excel也可以存放数据，但是相比之下excel无法多人协作，存放的数据量较少，所以数据库就是能大量存放数据的可以多人协作的一个存放数据的系统。

关系型数据库

关系型数据库是由多个表组成的，每个表都是有行和列组成的。关系型数据库中存放着一系列的表，只不过这些表之间是有联系的。 关系型数据库=多张表+表之间的联系 所以对于关系型数据库，只需要掌握多张表中每个表的结构与各表之间的关系就可以了。 表的结构是指每张表长什么样子。表的结构也主要分为三个部分，表名，行，列。 各表之间的关系 以学生表，成绩表这两张为例 在这两张表中，相同颜色代表同一个学生的信息。这两张表通过学号联系在一起。比如我们想知道学生表中学生号为0001的同学的成绩，就需要在成绩表中查找学号为0001的数据。对应的就找到了三门课的成绩。 关系就是数据能够对应的匹配，在关系型数据库中叫做联结，对应英文名称为join。

关系数据库管理系统

管理数据库的计算机软件叫做数据库管理系统，常见的有mysql，hive等等。

什么是sql

关系型数据库通过关系数据库管理系统来实现，人们通过SQL在关系数据库管理系统中查找数据。可以理解为python编译为c然后改变内存的过程。

hive中的基本数据类型

hivesql语法学习

对表本身的操作

如何建表

'''
CREATE TABLE IF NOT EXISTS tmp_dev.test(
name STRING COMMENT 'country name',
GDP FLOAT COMMENT 'gdp of each country'
)
PARTITIONED BY (state STRING)
'''

这是一个典型的建表命令 其中第一行 'CREATE TABLE IF NOT EXISTS tmp_dev.test'意思是在tmp_dev数据库下建名为test的表，如果表已经存在的话，建立相同名称的表会报错，加上'IF NOT EXISTS'可以避免报错。括号内部分则是表的具体内容。其中每个用逗号分隔的语句表示表的某一列的情况。 以'name STRING COMMENT 'country name''为例，其中name就是列名，STRING意思是此列数据的数据类型为STRING，COMMENT关键字后的字符串代表对此列的一个备注。在这里也就是说这列描述的是国家名称。 PARTITIONED BY (state STRING)即创建的是一个分区表，后续会介绍分区表的概念。

分区表

数据分区的一般概念存在已久。其可以有多种形式，但是通常使用分区来水平分散压力，将数据从物理上转移到和使用最频繁的用户更近的地方，以及实现其他目的。 Hive中有分区表的概念。我们可以看到分区表具有重要的性能优势，而且分区表还可以将数据以一种符合逻辑的方式进行组织，比如分层存储。以下面sql代码为例。

'''
CREATE TABLE employees 
(name　STRING,
salary  FLOAT,subordinates ARRAY <STRING>,
deductions MAP<STRING, FLOAT>,
address STRUCT <street:STRING,city:STRING, state:STRING, zip:INT>)
PARTITIONED BY (country STRING, state STRING)
'''

在这个表中，数据以country和state作为分区，每个country和state的组合都会有一张表。这些表会被储存在能够反映分区结构的子目录中，比如： .../employees/country=CA/state=AB
.../employees/country=CA/state=BC
.../employees/country=US/state=AC

分区字段（这个例子中就是country和state）一旦创建好，表现得就和普通的字段一样。

修改表

重命名

可以使用以下这个语句可以将表log_messages重命名为logmsgs：

'''
ALTER TABLE log_messages RENAME TO logmsgs;
'''

增加表分区

'''
ALTER TABLE log_messages ADD IF NOT EXISTSPARTITION (year = 2011, month = 1, day = 1) LOCATION '/logs/2011/01/01'PARTITION (year = 2011, month = 1, day = 2) LOCATION '/logs/2011/01/02'PARTITION (year = 2011, month = 1, day = 3) LOCATION '/logs/2011/01/03'...;
'''

查询语句

SELECT...FROM

SELECT是SQL中的射影算子。FROM子句标识了从哪个表、视图或嵌套查询中选择记录。 对于一个给定的记录，SELECT指定了要保存的列以及输出函数需要调用的一个或多个列（例如，像count(*)这样的聚合函数）。 例如我们想要从表格employee中读取全部信息，代码如下：

'''
SELECT * FROM employees;
'''

比如我们想要从表格中读取所有人的name，那么代码如下：

'''
SELECT salary FROM employees;
'''

使用列值来计算

'''
SELECT upper(name), salary, deductions["Federal Taxes"],round(salary * (1 - deductions["Federal Taxes"])) FROM employees;
'''

结果如下：

注意round(salary * (1 - deductions["Federal Taxes"]))这句，相当于每列中的对应的元素按照这个表达式来进行运算。

算术运算符

Hive支持以下算术运算符：

LIMIT语句

典型的查询语句会返回多行数据，可以使用LIMIT语句来限制返回的行数，当查询的表格过于庞大时，直接查询可能会导致集群崩溃，新手建议使用LIMIT语句进行限制。

'''
SELECT * FROM employees LIMIT 100;
'''

此语句只会返回表中的前100行。

列别名

当我们从表中查询某列时，我们可以重命名这个新查询到的列。在进行嵌套SELECT时，比较方便使用。同时在方便我们在打印表头时能够快速理解每列的数据的含义。

在这里主要使用AS关键字。比如：

'''
SELECT upper(name), salary, deductions["Federal Taxes"],round(salary * (1 - deductions["Federal Taxes"])) FROM employees;
'''

嵌套SELECT语句

对于嵌套语句，使用别名是非常有用的

'''
SELECT e.name, e.salary_minus_fed_taxes FROM  
(SELECT upper(name), salary,deductions["Federal Taxes"] as fed_taxes,round(salary * (1 - deductions["Federal Taxes"])) as salary_ minus_fed_taxes FROM employees)e　 
WHERE e.salary_minus_fed_taxes > 70000;
'''

在这里'SELECT upper(name), salary,deductions["Federal Taxes"] as fed_taxes,round(salary * (1 - deductions["Federal Taxes"])) as salary_ minus_fed_taxes FROM employees'此层读取的表格被重命名为别名e。在这个语句外面嵌套查询了name和salary_minus_fed_taxes两个字段，同时约束后者的值要大于70，000。

CASE … WHEN … THEN 句式

CASE … WHEN … THEN 句式用于处理单个列的查询结果。例如：

'''
SELECT name, salary,  
CASE WHEN salary  50000.0 THEN 'low' WHEN salary>= 50000.0 AND salary < 70000.0 THEN 'middle'　　WHEN salary >= 70000.0 AND salary < 100000.0 THEN 'high' ELSE 'very high' END  AS bracket  
FROM employees;
'''

WHERE语句

SELECT语句用于选取字段，WHERE语句用于过滤条件，两者结合使用可以查找到符合过滤条件的记录。我们刚才就使用了下面这个例子来限制查询的结果必须是美国的加利福尼亚州的：

'''
SELECT * FROM employees  
WHERE country = 'US' AND state = 'CA';
'''

GROUP BY语句

GROUP BY 语句通常会和聚合函数一起使用，按照一个或者多个列对结果进行分组，然后对每个组执行聚合操作。 如下这个查询语句按照苹果公司股票（股票代码APPL）的年份对股票记录进行分组，然后计算每年的平均收盘价：

'''
SELECT year(ymd), avg(price_close) FROM stocks 
WHERE exchange = 'NASDAQ' AND symbol = 'AAPL'　　 
GROUP BY year(ymd);
'''

ORDER BY 和 SORT BY

Hive中ORDER BY语句和其他的SQL方言中的定义是一样的。其会对查询结果集执行一个全局排序。这也就是说会有一个所有的数据都通过一个reducer进行处理的过程。对于大数据集，这个过程可能会消耗太过漫长的时间来执行。

Hive增加了一个可供选择的方式，也就是SORT BY，其只会在每个reducer中对数据进行排序，也就是执行一个局部排序过程。这可以保证每个reducer的输出数据都是有序的（但并非全局有序）。这样可以提高后面进行的全局排序的效率。

对于这两种情况，语法区别仅仅是，一个关键字是ORDER，另一个关键字是SORT。用户可以指定任意期望进行排序的字段，并可以在字段后面加上ASC关键字（默认的），表示按升序排序，或加DESC关键字，表示按降序排序。 下面是一个使用ORDER BY的例子：

'''
SELECT s.ymd, s.symbol, s.price_closeFROM stocks sORDER BY s.ymd ASC, s.symbol DESC;
'''

下面是一个类似的例子，不过使用的是SORT BY:

'''
SELECT s.ymd, s.symbol, s.price_closeFROM stocks sSORT BY s.ymd ASC, s.symbol DESC;
'''

JOIN语句

JOIN介绍

https://www.runoob.com/sql/sql-join.html

INNER JOIN

https://www.runoob.com/sql/sql-join-inner.html

LEFT JOIN

https://www.runoob.com/sql/sql-join-left.html

RIGHT JOIN

https://www.runoob.com/sql/sql-join-right.html

FULL JOIN

https://www.runoob.com/sql/sql-join-full.html

行转列和列转行

在Hive sql应用中会遇到“行转列”和“列转行”的场景，下面介绍其基本使用语法。

行转列：

关键字：collect_set() / collect_list()、concat_ws()

1. collect_set()/collect_list()： collect_set( )函数只接受基本数据类型，作用是对参数字段进行去重汇总，返回array类型字段; collect_list()函数和collect_set( )作用一样，只是前者不去重,后者去重。

2. concat_ws()： concat_ws (separator,字符串A/字段名A,字符串B/字段名B…)是concat的特殊形式，第一个参数是分隔符，分隔符会放到要连接的字符串之间,分隔符可以是字符串，也可以是其他参数。如果分隔符为NULL,则结果为NULL,函数会忽略任何分隔符参数后的 NULL值。但是concat_ws( )不会忽略任何空字符串。（然而会忽略所有的 NULL)，如果参数为string类型数组则合并数组内字符串。 例:concat_ws( ',', [ 'a ', 'b'])–> 'a,b'

行转列基本语法：

select 分类字段,concat_ws(',',collect_set(合并字段)) as 别名 from table_name group 分类字段;

原始数据

先用collect_set将列拼接在一起，然后再通过concat_ws进行展开拼接

'''
SELECT a.gender_times,
   concat_ws(';',collect_set(a.name)) name
FROM
  (SELECT name,
      concat(gender, '_','times') gender_times
   FROM ***fo) t
GROUP BY t.gender_times;
'''

查询结果

列转行

关键字：explode()、split()和LATERAL VIEW

1. split():

将一个字符串按照指定字符分割，结果为一个array；

2. explode():

将一列复杂的array或者map拆分为多行,它的参数必须为map或array；

3. lateral view:

lateral view udtf(字段名)表别名/表临时名as列别名/列临时名。lateral view用于和split, explode等UDTF一起使用，它能够将一行数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。lateral view首先为原始表的每行调用UDTF，UTDF会把一行拆分成一或者多行，lateralview再把结果组合，产生一个支持别名表的虚拟表。

列转行基本语法：

select 字段,字段别名 from table_name lateral view explode（split(拆分字段,分隔符)）表别名 as 字段别名;

原始数据 province | city| :-: | :-: | 河南 | 郑州市,开封市，洛阳市| 河北 | 石家庄市，保定市| 湖南 | 长沙市，岳阳市，常德市| -- addr为表名

'''

SELECT province,
       city_n
FROM addr LATERAL VIEW explode (split(city,',')) addr_tmp AS city_n;

'''

-- 查询结果

常见聚合函数

可参考 https://www.runoob.com/sql/sql-function.html