hive & sql 笔记

第一章 名词

名词	笔记	扩展
表名	在数据库中 唯一
模式schema	作用 描述	- 可描述 表 or 数据库
		- 描述 数据在表中如何存储 / 数据如何分解/ 信息如何命名 等等
行	row
列	column
主键	能够唯一标识表中每一行数据的（1个或多个）字段	主键列不允许空值； 主键列值不允许更新和修改

第二章 Select

sql语句以分号 “ ; ” 分割 ；不区分大小写； 忽略空格

注释方法： 行注释：-- ；段注释：/* */

Distinct :

• distinct 关键词返回 列 with unique 值
• 作用于后面 所有 的字段，非后一列字段

Top\ fetch first \ limit: 限制结果数量

• Top 返回结果的前 n 行数据；语法：select top 5 xx from xx
• Fetch first x rows only; 语法：select a from b fetch first x rows only;
• Limit n 限制返回数量；语法： select a from b limit n offset m ，返回第m行起的n条结果 offset从0开始

第三章 排序

Order by :

• 必须是select语句中最后一条子句
• order by 对象可以是 检索字段，也可是是非检索字段
• 多个order by 字段时，按照字段顺序进行排序
• Order by 默认是升序(ASC)排列（A->Z）,使用 desc进行降序；语法： order by xx desc

第四章 过滤 where

NULL(空值)： 不是值 0 ，不是空字符串， 不是仅包含空格的值

Where :

• 在from 语句后给出where
• 空值检查NULL：where xx is NULL

第五章 高级过滤1

AND \ OR : and优先级大于or , 例：

where a or b and c 等价于 where a or ( b and c )

In : 匹配条件范围的数值

• 用逗号分隔，圆括号括住。语法：where a in (‘xx’,‘xx’)
• In 的逻辑和or 相同，但 in 执行的更快
• in 可以包含其他 select语句 如： where a in ( select xx )

Not in 组合使用，语法：where a not in xx

第六章 高级过滤2

字符串匹配 like

通配符	含义	语法
%	任何字符串	a like ‘xx%’ ，以xx开头的字符串
_	任何单个字符	a like ‘xx_’, 形式如xx1,xx2,xxz的字符串样式
[]	匹配[]内集合	a like ‘[xy]%’ 以x或y开头的字符串
		前缀字符^ 表示否定， a like ‘[^xy]%’ 不以x,y开头的字符串

注意1：如果某些表用空格来填补空位，那么对于长度不满的字符串，末尾为空格而不是有效字符，可以用%来匹配末尾空格字段

注意2：% 不会匹配字段NULL， where a like ‘%’ 不会匹配NULL的行

注意3: 通配符时间代价高，不要放为靠前的条件

第七章 拼接

RTRIM / LTRIM / TRIM： 去除字符串中空格

• RTRIM / LTRIM 去除字符串 右/左侧空格； TRIM 去除两侧空格

第八、九章 函数

函数	含义	语法
cast	数据类型转换	Cast(x,)
round	四舍五入	Round(x, n) 对x四舍五入保留小数点后n位
avg	计算平均值	Avg(a); 忽略NULL值；仅适用于单列
count	记数	Count(*) 包含NULL；count(a) 记数字段a，忽略NULL
Max / min	最值统计	Max(a) / min (b) ; 统计 数值和日期 的最值； 忽略NULL

注意：关键词distinct 必须使用列名，也就是distinct 不能用于count (*), 可以 count( distinct a)

第十章 分组

group by: 对相同字段值进行分组；在hive 笔记中提及，这里做补充

• Group by 在where之后，在order by之前
• 如果分组有null, null将作为一个分组
• group by 不能使用别名
• 除了聚合函数，select中每一个字段都必须在group by中出现

having / where :

• having 过滤分组，where 过滤行
• where在数据过滤前进行过滤，having在分组后进行过滤。先where，对where 后对值再having。
• having 应结合group by 语句

Select 子句顺序： select -> from -> where -> group by -> having -> order by

第十一章 高级查询

子查询：嵌套 select

• select a from x where a in (select b from xx)
• 嵌套查询中，注意限定表

第十二章 连结

可以由WHERE语句给出连结条件；对于没有连结条件的表，返回结果为笛卡尔积（n*m）完全连结

笛卡尔积连结（叉连结）：

• 完全连结；n*m

内连接（等值连接）：

• inner join on 语句于where语句过滤效果一样，但sql规范首选inner join语法作为等值连接
• 不要连接不必要的表，影响性能

第十三章 高级连结

自联结：连结同一张表的不同查询结果

• 可以使用嵌套查询，也可以使用表另命名并做连结，嵌套语法：from a where x in (select … from a), 连结用法: from a as A, a as B where A.x=B.x
• 许多数据库处理连结比嵌套查询快

自然连结\ 内连结：

• 内连结相同的列出现多次；自然连结每一列只返回一次
• 需要select指定唯一的列，语法：select a.id from a,b

外连接：具体在hive笔记中提及

第十四章 组合

union : 组合多条select结果

• union 每个查询必须包含相同的列，但列不需要以相同次序列出
• 列的类型需要兼容

Union / where :

• union 联结多个where过滤后的结果，组合多个where条件
• union all 联结重复的行，是where完成不了的

union 排序：使用union时，不允许使用多条order by子句（因为不允许用一种方式排序一部分，用另一种方式排序另一部分），只能在语句最后使用一条order by 语句

第十五章 插入

完整插入：插入完整的行

• 完整插入，语句：不提供列名 insert into xx values(x,x,xx) ; 提供列名 insert into xx(a,b,c) values(x,x,xx)
• 不提供列名的完整插入，高度依赖于表中列的定义次序，如果某列没有值，应该使用NULL填充，不安全
• 提供列名的完整插入，按照insert语句中列名匹配顺序，不需要符合各列在表中的定义顺序

部分插入：插入的行只有部分值

• 可以不插入值的列：允许NULL or 有默认值
• 需要指定列名：Insert into xx(a,b) values (x,xx)

insert into xx select xxx : 在hive笔记中提及

• Insert into 不需要和 select 使用相同列名，但位置顺序需要一样，即select结果的第一列插入表第一列，第二列插入第二列
• insert into select 可以插入多行，但通常Insert 只插入一行

复制到新表：将一个表的数据复制到一个新表

• create … select , 语法：Create table x as select * from xx

第十六章 更新和删除

Update: 更新特定行/所以行

• Update 更新的表 set 列名和新值 + 过滤的条件，语法：update x set a = ‘b’ where id=1
• 更新多个列时，用逗号，分割；语法：update x set a=‘a’,b=‘b’
• 如果删除某个值，可以更新它为NULL
• 有些数据库不支持在update中使用from

delete :删除特定/所有行

• delete from x where xxx;
• 如果没有条件，delect 将删除表中所有数据（表还存在）

delete / truncate : delete 删除特定条件的行，truncate table语句删除表中所有数据并且速度更快

在使用update / delete 之前，谨慎起见 先用select 测试一下过滤条件是否正确

第十七章 创建

Create table 语句（创建表）：

• Create table xx( key char not null， value double not null ); 表列的名字和定义用空格和逗号分隔
• 列中NULL为默认设置，如果没有指定 not null ，则列中值可以为NULL
• 主键必须为not null 的列

默认值 default , 语法： Create table xx( key char not null default ‘a’)

ALTER TABLE 更改表结构：

• 增加列 Alter table x add c char(10) 为表x添加一列c，该列数据类型为长度为10的char
• 删除列 alter table x drop c

注意：复制的表结构需要手动删除，一般要复制一个新表，重命名旧表，再对新表进行操作

Drop 删除表：删除整个表而非内容，语法：drop table x

第十八章 视图

视图是虚拟的表，它并不存储数据，数据任然存在于原表中。视图只是包含对各表的查询，相当于把查询的结果包装成一个虚拟的表。

性能： 视图不包含数据，所以每次使用视图，都是对原表的一次查询操作。如果使用多个联结、嵌套视图，会影响性能

视图 ：

• 对视图可以进行对表一样的操作，可以视图嵌套视图
• 不能改变视图中的数据，相反如果原表中的数据改变，视图的结果也会改变

创建视图语法 ：create view xx as select xx

第十九章 存储过程

存储过程是 保存的一条或多条sql语句，可以看作为批处理文件

执行存储过程：

execute addx('a', 'b')

执行一个名为addx的存储过程，有两个参数

存储过程可以完成的任务：

• 验证传入数据的正确性
• 生成主键id
• 执行增删改查
• 返回代码

创建存储过程：

create procedure addx(ss out string) # 定义一个名为ss 的string类型参数,作为返回参数
is 
row_id integer;
begin
	select ...
end;

第二十章 事务处理

事务处理：是一种机制，管理成批执行的sql操作，要么完成执行，要么完全不执行

事务transaction： 一组sql语句,语法（不同数据库语法不同）

begin transaction
...
end transaction

回退rollback:撤销sql 过程

delete from x;
rollback

提交commit:将未存储的sql写入数据库

begin transaction
delect xxx
commit transaction

保存点：savepoint 回退到保留处

begin transaction
insert into x values(xx)
save transaction save_point
insert ..

在第一条insert语句后定义一个保存点save_point，如果后续语句失败将回退到保存点。

第二十一章 游标

游标：类似于指针，是一组被检索出来的结果集

• 使用游标步骤：定义->必须使用->必须关闭->释放（非必须）

创建游标：不同数据库语法不同

declare cur cursor # 定义一个名为cur的游标
for 
select xx from x

使用游标：

open cursor cur

… 基于游标的使用比较复杂，这里不做深入

第二十二章 索引、约束

检查约束：保证列数据满足指定的条件；可以检查：最值/ 范围 /特定值

语法： create table a (key char(10) not null check (key=‘a’))

索引： 在一个或多个列上定义索引，使数据库保存一个排过序的列表

• 索引占用存储空间；存储优化select的性能但不适合增删改

create index pro_index on tableA(a);

定义一个名为pro_index的索引 ，对象为tableA的a列

触发器：特殊的存储过程，能自动执行。具体怎么创建和使用比较复杂，不做深入

触发器可以进行的操作：insert, update, delete

create trigger trion tableAfor insert,updateas update tableAset a='A'where ...

• 约束比触发器更快

附录：

字符串类型：

• Char ,长度1-255
• Varchar 可变长度字符

数值类型：

• Bit ,0/1
• Integer 4字节
• Numeric(decimal) 精度可变的浮点值
• Number(float): 浮点值
• Smallint: 2字节

二进制类型：

• binary ： 定长二进制
• varbinary: 变长二进制