一、数据类型
1.1 支持的数据类型
下图展示的是SinoDB支持的各种数据类型:
1.2 内置数据类型
数据类型 | 备注 |
INTEGER / INT | 数值型(Numeric) |
SERIAL | |
SERIAL8 | |
SMALLINT | |
BIGINT | |
BIGSERIAL | |
DECIMAL or NUMERIC(p,s) | |
REAL or SMALLFLOAT | |
FLOAT | |
DOUBLE PRECISION | |
MONEY(p,s) | |
DATE | 时间型(TIME) |
DATETIME | |
INTERVAL | |
CHAR(n) | 字符型(CHARACTER) |
CHARACTER VARYING(n,r) | |
VARCHAR(n,r) | |
LVARCHAR(m) | |
NCHAR(n) | |
NVARCHAR(m,r) | |
BYTE | 大对象(Large Objects) |
TEXT | |
BLOB | |
CLOB | |
BOOLEAN | 布尔型(BOOLEAN) |
1.2.1 数值型
数据类型 | 备注 |
INTEGER / INT | 4个字节,从 -(231 -1) 到 231 -1的整数。 |
INT8 or BIGINT | 8字节,从 -(263 -1) 到 263 -1的整数。 BIGINT 优于INT8。 |
SMALLINT | 2字节,从 -(215 -1) 到 215 -1的整数。 |
SERIAL | 4字节,从1到 231-1 的正整数,由服务器自动地递增。 |
SERIAL8 or BIGSERIAL | 8字节,从1到 263-1 的正整数,由服务器自动地递增。 BIGSERIAL 优于 SERIAL8。 |
FLOAT or DOUBLE PRECISION | 双精度浮点数,16个有效位。 |
SMALLFLOAT or REAL | 单精度浮点数,8 个有效位。 |
DECIMAL(p,s) or NUMERIC(p,s) | 定点小数,其中的 'p' 是总位数,'s' 是小数点右边的位数。 |
MONEY(p,s) | 货币值,与 DECIMAL 相似,用货币字符进行格式化。 |
1.2.2 字符型
数据类型 | 备注 |
CHAR(n) / CHARACTER(n) | 定长的字符串,n为字符串长度,默认为1字节。如果长度小于 n,那么用空格填充至 n 个字符 。n的最大值为32767。 |
VARCHAR(n,r) / CHARACTERVARYING(n,r) | 长度可变的字符串, n为最大字节长度,r 是最小保留空间;如果存储的值的长度小于 r,那么用空格填充至 r 个字符;如果存储的值的长度大于 r 但小于 n,那么只占用存储数据所需的空间。n的最大值为255。 |
LVARCHAR(n) | 长度可变的字符串,n为最大字节长度,默认为2048字节。只使用存储数据所需的空间,n的最大值为32739。 |
NCHAR(n) | 定长的字符串,包括使用 Global Language Support (GLS) 存储由数据库地区决定的单字节和多字节字符集;还允许使用本地的整理次序。 |
NVARCHAR(n,r) | 长度可变的字符串,参数含义同 VARCHAR ,具有与 NCHAR 相似的特殊性质。 |
1.2.3 时间
数据类型 | 备注 |
DATE | 日期,默认格式为 MM/DD/YYYY,可以通过 GL_DATE 环境变量修改。 |
DATETIME | 日期时间,默认格式为 YYYY-MM-DD HH:MM:SS.FFF,可以通过 GL_DATETIME 环境变量修改格式。 |
INTERVAL | 时间跨度,格式与 DATETIME 相同。 |
示例如下:
DATE:
DATETIME:
INTERVAL:
1.2.4 大对象
1.2.4.1 简单大对象
数据类型 | 备注 |
BYTE | BLOB 的老版本,理论大小限制为 231 字节,实际限制由磁盘容量决定。 |
TEXT | CLOB 的老版本,最大大小为 2GB。 |
1.2.4.2 智能大对象
数据类型 | 备注 |
BLOB | 二进制大对象,最大大小为 4TB,用于存储二进制数据。 |
CLOB | 字符大对象,最大大小为 4TB,用于文本数据。 |
1.2.5 布尔型
数据类型 | 备注 |
BOOLEAN | 't' 或 'f' |
1.2.6 内置数据类型的示例
下面是一个使用内置数据类型创建表的示例:
1.2.7 扩展数据类型
数据类型 | 备注 |
ROW 类型 | 复合数据类型 |
COLLECTION 类型 | |
DISTINCT 类型 | 用户定义数据类型 |
OPAQUE 类型 |
1.2.7.1 复合数据类型
复合数据类型由一个或多个数据类型组合而成。复合数据类型有ROW类型、COLLECTION类型。对ROW、COLLECTION等类型的描述如下表所示:
数据类型 | 备注 |
ROW类型 | 由一个或多个任意的数据类型组合而成。 |
COLLECTION类型 | 由一个或多个同样的数据类型组合而成包括SET、LIST、MULTISET; SET 不允许集合中有重复的数据, MULTISET 和 LIST 都允许重复的数据; COLLECTION 不允许 Null 元素,所以在定义 COLLECTION 时必须指定 NOT NULL 约束。 |
下面是一个使用复合数据类型创建表的示例:
1.2.7.2 用户定义数据类型
数据类型 | 备注 |
DISTINCT 类型 | DISTINCT类型只是对一个内置数据类型的重新命名。它继承内置类型的基本性质,但是不包括支持函数。 示例: create distinct type shoesize as DECIMAL(3,1) |
OPAQUE 类型 | OPAQUE 类型是SinoDB完全不了解的新类型,用户定义它并告诉SinoDB如何与它交互。 GeoSpatial Datablade 中的GeoPoint 就是OPAQUE数据类型,用来存储四维的地理坐标。 |
二、创建数据库、数据表、视图
2.1 数据库创建
我们可以创建四种类型的数据库(database):
Ø 不记录日志
Ø 缓冲式的记录日志
Ø 无缓冲式的记录日志
Ø ANSI (记录日志时无缓冲, )
2.2 数据表
2.2.1 普通数据表
普通数据表在system catalog里注册。一个普通数据表可对多个session或connection可见,创建时可以指定dbspace。
2.2.2临时数据表
临时数据表不在system catalog里注册。一个临时数据表只对对应的某个session或connection可见,在对应的session或connection结束时被自动清除。如果临时dbspace存在的话,临时数据表将被建于临时dbspace中。缺省情况下,临时数据表是没有日志(log)的,支持索引。
使用“create temp table”来创建临时数据表:
使用“select … into temp”来创建临时数据表:
2.2.3 约束
2.2.3.1 主键约束
主键约束定义在一个或一组数据列上。主键的值是不重复的、不能是NULL。
2.2.3.2 引用约束
引用约束是数据表之间或数据表内的关系,一个数据表的主键可以被同一个数据表或其它数据表引用。主键被引用的数据表被称为父表(parent table),引用了父表的主键的数据表被称为子表(child table)。
如果在定义引用约束时使用了ON DELETE CASCADE,当把父表的数据行删除时,子表的相关数据行也会被自动删除。
2.2.2.3 检查约束
检查约束是定义了check constraint后,数据库在把数据赋给一个数据列之前将根据check constraint检查数据是否满足条件。
上面例子里的检查约束(check constraint)要求discount值必须在0.0和0.5之间。
2.2.4 视图
虚拟表,创建视图时使用SELECT语句,在system catalog里注册。视图没有自己的行,对于同一些数据表,可为不同的用户建立不同的视图,可配置存取权限。
三、简单查询
3.1 简单查询语句简介
我们使用select语句从数据库中查询数据。在大多数情况下,select语句是我们最常用的SQL语句。Select语句的语法如下所示:
下面是几个SinoDB的sql简单示例:
3.2 关系运算符
在SinoDB中,关系运算符有:
=
!= 或 <>
>
>=
<
<=
在where子句中使用关系运算符的示例:
3.3 Where子句里可使用的关键字
Where子句里可使用的关键字有:
(1) AND:逻辑与
(2) OR:逻辑或
(3) [NOT] BETWEEN: [不]在。。。之间
(4) [NOT] IN: [不]在。。。里
(5) IS [NOT] NULL:[不]是NULL
(6) [NOT] MATCHES: [不]匹配
注:?表示单个字符
注:*表示0个或正整数个字符
注:[K-T]表示从K到T的任意字符
(7) [NOT] LIKE: [不]匹配
注:_表示单个字符
注:%表示0个或正整数个字符
3.4 对查询结果进行分组
我们可以使用group by对查询结果进行分组。分组后我们可以得到各个分组的统计信息,如平均值、总和、数据行数等。
使用group by order_num子句后,查询结果将按order_num分组。
使用group by对查询结果进行分组后,我们还可使用having字句来选出符合某些条件的分组。
使用group by order_num子句后,查询结果将按order_num分组。使用having sum(price) > 500子句后,查询结果中将只保留“价格总和”大于500的分组。
3.5 CASE子句
我们可以使用CASE表达式对返回值进行转换。
CASE表达式的语法如下:
上面的CASE表达式的意思是:
当expr为expr1时,返回result1;
当expr为expr2时,返回result2;
…
当expr为其它情况时,返回result_else。
CASE表达式的示例如下:
3.6 DECODE
我们可以使用DECODE函数对返回值进行转换。
DECODE函数的语法如下:
上面的DECODE函数的意思是:
当expr为expr1时,返回result1;
当expr为expr2时,返回result2;
…
当expr为其它情况时,返回result_else。
DECODE函数的示例如下:
3.7 Union和Union All
我们使用如果两个或多个select语句的结果相似,我们可以用“Union”或“Union All”把这些select语句合并起来。“Union”和“Union All”的区别是:“Union”将去掉结果中的非第一次出现的值,而“Union All”将保留结果中的非第一次出现的值。
四、复杂查询
4.1 表连接的语法
Ø 我们可以使用两种方式进行数据表连接:
ü 数据表之间使用逗号,在WHERE子句指定连接条件
ü 数据表之间使用JOIN,连接条件前使用ON
Ø 内连接:仅生成满足结合条件的行
4.1.1 外连接
如果我们有两个数据表employee和project,如图所示。
蓝色的员工表示负责有项目的员工,紫色的员工表示不负责项目的员工,
蓝色的项目表示有员工负责的项目,棕色的项目表示没有员工负责的项目。
Ø 左外连接:以左表为基表,生成左表的所有行,右表中的某些行
哪个员工负责哪个项目,哪些员工不负责项目?
Ø 右外连接:以右表为基表,生成右表的所有行,左表中的某些行
哪个员工负责哪个项目,哪些项目没有人负责?
全外连接:生成两个表中所有的行,不管有没有表与它满足连接条件
哪个员工负责哪个项目,哪些员工不负责项目,哪些项目没有人负责?
4.2 子查询
子查询包含相关子查询(correlated subquery)和不相关子查询(non-correlated subquery)两种类型。
在相关子查询中,子查询中涉及到父查询的数据列;
在不相关子查询中,子查询中不涉及到父查询的数据列。
相关子查询示例:
不相关子查询示例:
4.2.1 将相关子查询转化成表连接
在很多情况下,我们可以将相关子查询转化成表连接,这样数据库引擎有可能更方便的得到更优的查询计划,从而使SQL语句的执行时间减少。
例如:
可被转化成:
五、插入、删除、更新数据行
5.1 插入数据行
我们可以使用insert语句往数据表里插入数据行。
如果是将各值按序赋给数据表的所有数据列,则不用指明数据列。例如:
如果是将值赋给指定数据列,则需指明数据列。例如:
我们可以在insert语句中嵌套select子句,从而将从一个或多个数据表查询来的数据插入到目标数据表。
5.2 更新数据行
我们可以使用update语句为数据表更新数据行
update语句示例:
5.3 删除数据行
我们可以使用delete语句从数据表里删除数据行。
delete语句示例:
六、附属特性
6.1 MERGE语句
将数据从源表传输到目的表:
Ø 将来自表、视图、“select子句的结果”里的信息汇聚在一起
Ø 可以将UPDATE、INSERT、DELETE包含在一条MERGE语句中
在 ETL/ELT 操作中非常有用。
与 ANSI/ISO 兼容。
从SinoDB11.50.xC5开始,MERGE语句支持UPDATE、INSERT。从SinoDB11.50.xC6开始,MERGE语句支持DELETE。
6.2 数据的层次结构图
SELECT name, empid, mgrid
FROM emp
START WITH name = 'Goyal'
CONNECT BY PRIOR empid = mgrid
SinoDB从版本11.50.xC5开始支持 CONNECT BY 语句。
6.3 自动产生数据的数据类型
SERIAL、SERIAL8、BIGSERIAL都会自动产生数据。
用户可选择是否提供初始值。
SERIAL类型的数据占用4个字节的存储空间。
SERIAL8类型的数据一般情况下占用10个字节的存储空间。
(在Extended Parallel Server中占用8个字节的存储空间)。
BIGSERIAL类型的数据占用8个字节的存储空间。