SQL Server 2016数据库应用与开发

目录 目录.................................................................................................................................................- 2 - 1 Greenplum 整体架构信息..........................................................................................................- 9 - 1.1 架构图示..........................................................................................................................- 9 - 1.2 Master 主机与 Segment 主机任务............................................................................... - 10 - 1.3 数据库分布键分布数据策略.......................................................................................- 10 - 1.3.1 HASH 策略........................................................................................................... - 10 - 1.3.2 随机分布............................................................................................................ - 10 - 1.4 master 主节点获取 segment 节点上的数据顺序........................................................- 11 - 2 Greenplum 数据库常用知识....................................................................................................- 11 - 2.1 Greenplum 概念............................................................................................................- 11 - 2.2 OLTP 与 OLAP 的理解.....................................................................................................- 11 - 2.2.1 描述概念............................................................................................................ - 11 - 2.2.2 图示解释............................................................................................................ - 12 - 2.3 其他名词概念............................................................................................................... - 12 - 2.3.1 HTAP 概念............................................................................................................- 12 - 2.3.2 Append-only 的概念.......................................................................................... - 13 - 2.4 数据仓库设计规则....................................................................................................... - 13 - 2.4.1 数据仓库概念....................................................................................................- 13 - 2.4.2 数据仓库设计规则相关资料............................................................................- 13 - 2.4.3 数据仓库设计规范参考....................................................................................- 13 - 3 Greenplum 集群安装...............................................................................................................- 15 - 4 Greenplum 初级使用...............................................................................................................- 15 - 4.1 Greenplum 常用命令列表............................................................................................- 15 - 4.1.1 常用命令列表....................................................................................................- 15 - 4.2 PSQL 命令实例...............................................................................................................- 15 - 4.2.1 PSQL 客户端的安装............................................................................................- 15 - 4.2.1.1 在线安装客户端.....................................................................................- 15 - 4.2.1.2 导入密码.................................................................................................- 15 - 4.2.2 PSQL 命令的使用................................................................................................- 15 - 4.2.2.1 PSQL 登录到数据库.................................................................................- 15 - 4.2.2.2 COPY 命令参数说明............................................................................... - 17 - 4.2.2.3 使用 COPY 导出数据..............................................................................- 18 - 4.2.2.4 使用 COPY 导入数据..............................................................................- 20 - 4.2.3 常用加载数据方式............................................................................................- 21 - 4.2.3.1 加载 SQL 文件形式.................................................................................- 21 - 4.2.3.2 直接执行 SQL 实例.................................................................................- 21 - 4.3 数据库支持的数据类型...............................................................................................- 22 - 4.3.1 numeric 类型.......................................................................................................- 22 - 4.3.1.1 类型列表.................................................................................................- 22 - 4.3.1.2 类型示例.................................................................................................- 22 - 4.3.2 二进制类型........................................................................................................ - 23 - - 3 - 4.3.2.1 类型列表.................................................................................................- 23 - 4.3.2.2 类型示例.................................................................................................- 23 - 4.3.3 日期/时间类型...................................................................................................- 24 - 4.3.3.1 类型列表.................................................................................................- 24 - 4.3.3.2 类型示例.................................................................................................- 24 - 4.3.4 boolean 类型......................................................................................................- 25 - 4.3.4.1 类型列表.................................................................................................- 25 - 4.3.4.2 类型示例.................................................................................................- 25 - 4.3.5 几何类型............................................................................................................ - 26 - 4.3.5.1 数据列表.................................................................................................- 26 - 4.3.5.2 类型示例.................................................................................................- 26 - 4.3.6 网络类型............................................................................................................ - 27 - 4.3.6.1 数据列表.................................................................................................- 27 - 4.3.6.2 数据示例.................................................................................................- 27 - 4.3.7 常用数据类型....................................................................................................- 28 - 4.3.7.1 数据列表.................................................................................................- 28 - 4.3.7.2 数据示例.................................................................................................- 28 - 4.4 常用函数....................................................................................................................... - 29 - 4.4.1 字符串函数........................................................................................................ - 29 - 4.4.1.1 函数列表.................................................................................................- 29 - 4.4.1.2 函数示例.................................................................................................- 30 - 4.4.2 时间函数............................................................................................................ - 31 - 4.4.2.1 函数列表.................................................................................................- 31 - 4.4.2.2 函数示例.................................................................................................- 31 - 4.4.3 数值计算函数....................................................................................................- 32 - 4.4.3.1 函数列表.................................................................................................- 32 - 4.4.3.2 函数示例.................................................................................................- 33 - 4.4.4 其他常用函数....................................................................................................- 34 - 4.4.4.1 序列号生成函数.....................................................................................- 34 - 4.4.4.2 字符串列转行函数.................................................................................- 34 - 4.4.4.3 md5 函数.................................................................................................- 35 - 4.4.4.4 字符串行转列函数.................................................................................- 35 - 4.5 常见 DDL 语句...............................................................................................................- 36 - 4.5.1 更改表名............................................................................................................ - 36 - 4.5.2 修改表字段........................................................................................................ - 36 - 4.5.2.1 修改为 DATE 类型...................................................................................- 36 - 4.5.2.2 修改为字符串的长度.............................................................................- 36 - 4.5.2.3 增加与删除字段.....................................................................................- 36 - 4.5.2.4 修改为 TIMESTAMP 类型....................................................................... - 37 - 4.5.2.5 修改为 NUMERIC 类型...........................................................................- 37 - 4.5.2.6 更改表字段的约束.................................................................................- 37 - 4.5.2.7 字段重命名.............................................................................................- 37 - 4.5.2.8 字段默认值设置.....................................................................................- 38 - 4.5.3 创建与删除 DATABASE 语句.............................................................................. - 38 - - 4 - 4.5.4 创建与删除 SCHEMA 语句.................................................................................- 38 - 4.5.5 更改表的分布键................................................................................................- 38 - 4.6 gpload 命令使用............................................................................................................ - 39 - 5 常见创建 TABLE 方式...............................................................................................................- 39 - 5.1 创建 Heap 表.................................................................................................................- 39 - 5.2 创建 AO 表.....................................................................................................................- 39 - 5.2.1 AO 表不压缩....................................................................................................... - 39 - 5.2.2 AO 表压缩............................................................................................................- 40 - 5.2.3 AO 表列存压缩................................................................................................... - 40 - 5.2.3.1 对 orientation 参数进行测试.................................................................- 40 - 5.2.3.2 统计压缩的大小.....................................................................................- 42 - 5.2.3.3 压缩类型比较.........................................................................................- 43 - 5.3 创建 HDFS 外表实例.....................................................................................................- 44 - 5.3.1 创建外部表实例................................................................................................- 44 - 5.3.2 查看错误数据的实例........................................................................................- 45 - 5.3.3 创建可写外部表实例........................................................................................- 46 - 5.3.3.1 创建可写外部表实例.............................................................................- 46 - 5.3.3.2 创建可写外部表.....................................................................................- 46 - 5.3.3.3 查看 HDFS 上的数据.............................................................................. - 47 - 5.4 创建分区表................................................................................................................... - 47 - 5.4.1 分区表概念........................................................................................................ - 47 - 5.4.2 创建分区表........................................................................................................ - 47 - 5.4.3 查看创建的分区表............................................................................................- 48 - 5.4.3.1 查看创建表的名称.................................................................................- 48 - 5.4.3.2 查看表的结构.........................................................................................- 48 - 5.4.4 插入分区表数据................................................................................................- 49 - 5.4.4.1 生成数据脚本.........................................................................................- 49 - 5.4.4.2 插入数据.................................................................................................- 49 - 5.4.5 查看数据及数据分布情况................................................................................- 50 - 5.4.5.1 查看主表的数据.....................................................................................- 50 - 5.4.5.2 查看分布表的数据.................................................................................- 50 - 5.4.5.3 查看数据分布情况.................................................................................- 50 - 5.4.6 对分区表常用操作............................................................................................- 51 - 5.4.6.1 增加分区.................................................................................................- 51 - 5.4.6.2 删掉分区.................................................................................................- 51 - 5.4.6.3 清空分区.................................................................................................- 52 - 5.4.6.4 修改分区.................................................................................................- 52 - 5.4.6.5 修改 default 分区....................................................................................- 52 - 5.4.7 创建数字范围的分区表....................................................................................- 52 - 5.4.8 创建字母范围的分区表....................................................................................- 52 - 5.5 快速复制表........................................................................................................... - 53 - 6 Greenplum 对 JSON 的支持.....................................................................................................- 53 - 6.1 JSON 概述.......................................................................................................................- 53 - 6.2 JSON 常用运算符与函数...............................................................................................- 54 - - 5 - 6.2.1 JSON 常用运算符................................................................................................- 54 - 6.2.2 JSON 常用的创建函数........................................................................................- 55 - 6.2.3 JSON 处理函数....................................................................................................- 56 - 6.3 JSON 运算符常用实例..................................................................................................- 57 - 6.3.1 单组 JSON 解析..................................................................................................- 57 - 6.3.2 多组 JSON 解析..................................................................................................- 57 - 6.3.3 复杂的 JSON 解析..............................................................................................- 58 - 6.3.3.1 多个 JSON 子集的解析...........................................................................- 58 - 6.3.3.2 获取 JSON 子集的数据...........................................................................- 58 - 6.3.3.3 获取一个 JSON 集合的子元素...............................................................- 58 - 6.4 JSON 创建函数的使用..................................................................................................- 59 - 6.4.1 创建 int 类型的 JSON 格式数据....................................................................... - 59 - 6.4.2 把行的数据转化为 JSON 类型的数据..............................................................- 59 - 6.5 JSON 处理函数的使用...................................................................................................- 60 - 6.5.1 获取 JSON 中的数据..........................................................................................- 60 - 6.5.2 获取 JSON 中的数据(去除双引号)...................................................................- 60 - 6.5.3 获取 JSON 数据中的 KEY 的值..........................................................................- 61 - 6.6 JSON 查询数据的方式...................................................................................................- 61 - 6.6.1 创建支持 JSON 数据的表..................................................................................- 61 - 6.6.1.1 创建表的 SQL..........................................................................................- 61 - 6.6.1.2 插入数据 SQL..........................................................................................- 61 - 6.6.1.3 获取 JSON 数据的 KEY 值.......................................................................- 62 - 6.6.2 获取 JSON 结构中的数据..................................................................................- 63 - 6.6.3 按照条件查询数据............................................................................................- 63 - 6.6.4 集合函数查询 JSON 数据..................................................................................- 65 - 6.6.5 使用默认的函数查找数据................................................................................- 65 - 6.6.5.1 JSON_EACH 函数的使用.........................................................................- 65 - 6.6.5.2 JSON_OBJECT_KEYS 函数的使用............................................................- 66 - 6.6.6 把查询数据转化为 JSON...................................................................................- 67 - 6.6.6.1 查看原始数据.........................................................................................- 67 - 6.6.6.2 把查询的数据转化为 JSON....................................................................- 68 - 7 Greenplum PGBENCH 命令详解............................................................................................... - 70 - 7.1 PGBENCH 概念详解........................................................................................................- 70 - 7.2 PGBENCH 参数详解.......................................................................................................- 70 - 7.2.1 查看参数详细信息............................................................................................- 70 - 7.3 PGBENCH 测试案例.......................................................................................................- 71 - 7.3.1 创建表信息........................................................................................................ - 71 - 7.3.2 修改以下参数....................................................................................................- 71 - 7.3.2.1 修改配置文件..........................................................................................- 71 - 7.3.2.2 重启服务..................................................................................................- 72 - 7.3.3 创建 SQL 测试文件.............................................................................................- 72 - 7.3.4 查看测试效果.....................................................................................................- 72 - 8 Greenplum 常用函数...............................................................................................................- 74 - 8.1 字符串函数................................................................................................................... - 74 - - 6 - 8.1.1 常见字符串函数................................................................................................- 74 - 8.1.2 函数常见示例....................................................................................................- 74 - 8.1.2.1 字符串拼接.............................................................................................- 74 - 8.1.2.2 查看字符串的长度.................................................................................- 75 - 8.1.2.3 查看字符在字符串的位置.....................................................................- 75 - 8.1.2.4 在制定的位置截取字符串.....................................................................- 75 - 8.1.2.5 删除字符串的制定字符.........................................................................- 76 - 8.1.2.6 字符串转大小写.....................................................................................- 76 - 8.1.2.7 替换制定的字符串.................................................................................- 76 - 8.1.2.8 按照分隔符分割字符串.........................................................................- 77 - 8.2 日期及时间函数........................................................................................................... - 77 - 8.2.1 常见日期及时间函数........................................................................................- 77 - 8.2.2 函数常见使用....................................................................................................- 78 - 8.2.2.1 查看两个日期的.....................................................................................- 78 - 8.2.2.2 查看当前的日期.....................................................................................- 78 - 8.2.2.3 获取当前的时间.....................................................................................- 79 - 8.2.2.4 获取精确的时间戳.................................................................................- 79 - 8.2.2.5 获取时间戳的制定参数.........................................................................- 79 - 8.2.2.6 获取当前的时间戳.................................................................................- 81 - 8.2.2.7 获取当前的时间.....................................................................................- 81 - 8.2.2.8 获取当前的时间戳.................................................................................- 81 - 9 Greenplum 高级使用................................................................................................................- 81 - 9.1 常用查询语句............................................................................................................... - 81 - 9.1.1 查看表的数据分布情况....................................................................................- 81 - 9.1.2 查看默认的错误数据........................................................................................- 82 - 9.1.3 查看表的大小....................................................................................................- 82 - 9.1.4 查看数据库的占用大小....................................................................................- 83 - 9.1.5 获取一个表的 schema 的信息..........................................................................- 83 - 9.1.5.1 通过 SQL 方式获取.................................................................................- 83 - 9.1.5.2 通过终端方式获取.................................................................................- 84 - 9.1.5.3 查看 schema 下的所有的表.................................................................. - 84 - 9.1.5.4 查询 schema 下的外部表的列表.......................................................... - 85 - 9.1.5.5 查询 schema 下的普通表的列表.......................................................... - 85 - 9.1.5.6 获取参数说明.........................................................................................- 85 - 9.1.6 查看每个 schema 的占用大小..........................................................................- 85 - 9.1.7 查看当前正在执行的语句................................................................................- 86 - 9.1.8 释放表的膨胀空间............................................................................................- 86 - 9.2 常见创建表语句........................................................................................................... - 87 - 9.2.1 快速复制一张表................................................................................................- 87 - 9.2.2 重新设计一张表................................................................................................- 87 - 9.3 分析执行计划............................................................................................................... - 87 - 9.3.1 查看执行计划....................................................................................................- 87 - 9.3.2 名词解释............................................................................................................ - 88 - 9.4 函数使用....................................................................................................................... - 88 - - 7 - 9.4.1 创建函数............................................................................................................ - 88 - 9.4.1.1 创建函数方式一.....................................................................................- 88 - 9.4.1.2 创建函数方式二.....................................................................................- 90 - 9.4.2 使用函数............................................................................................................ - 91 - 9.4.3 删除函数............................................................................................................ - 91 - 9.5 视图使用....................................................................................................................... - 91 - 9.5.1 视图命名规范....................................................................................................- 91 - 9.5.2 视图的定义........................................................................................................ - 91 - 9.5.3 创建及查询视图语句........................................................................................- 92 - 9.5.4 创建视图............................................................................................................ - 92 - 9.5.5 查看视图结构....................................................................................................- 92 - 9.6 索引使用....................................................................................................................... - 93 - 9.6.1 支持的索引类型................................................................................................- 93 - 9.6.2 获取索引的列表................................................................................................- 93 - 9.6.3 bitmap 索引的使用.............................................................................................- 93 - 9.6.3.1 获取测试表的信息.................................................................................- 93 - 9.6.3.2 查看不使用索引耗时.............................................................................- 94 - 9.6.3.3 创建索引.................................................................................................- 94 - 9.6.3.4 查看使用索引耗时.................................................................................- 94 - 9.6.3.5 删除索引.................................................................................................- 95 - 9.6.3.6 查看执行计划.........................................................................................- 95 - 9.6.4 b-tree 索引的使用..............................................................................................- 96 - 9.6.4.1 b-tree 索引的特点...................................................................................- 96 - 9.6.4.2 获取测试表的信息.................................................................................- 96 - 9.6.4.3 查看不使用索引时的耗时.....................................................................- 97 - 9.6.4.4 创建索引.................................................................................................- 97 - 9.6.4.5 查看使用索引的耗时.............................................................................- 97 - 9.6.4.6 查看执行计划信息.................................................................................- 97 - 9.7 引用第三方库............................................................................................................... - 98 - 9.8 常用设置....................................................................................................................... - 98 - 9.8.1 终端设置字符编码............................................................................................- 98 - 9.8.2 设置终端执行时长............................................................................................- 98 - 10 工作中常见知识汇总............................................................................................................ - 99 - 10.1 在使用外表时注意的问题..........................................................................................- 99 - 10.1.1 注意\线的转义问题.........................................................................................- 99 - 10.1.1.1 问题示例...............................................................................................- 99 - 10.1.1.2 解答方法...............................................................................................- 99 - 10.1.2 \1 转特殊字符的问题.....................................................................................- 100 - 10.1.2.1 问题示例.............................................................................................- 100 - 10.1.2.2 解答方法.............................................................................................- 100 - 10.2 COPY 命令常见错误汇总...........................................................................................- 101 - 10.2.1 数据中有双引字符.........................................................................................- 101 - 10.2.1.1 问题示例.............................................................................................- 101 - 10.2.1.2 解答方法.............................................................................................- 101 - - 8 - 10.3 查看字段中有特殊字符 SQL....................................................................................- 102 - 10.3.1 先把表字段获取出来....................................................................................- 102 - 10.3.2 查询表中的错误数据....................................................................................- 102 - 11 Greenplum 权限管理............................................................................................................- 102 - 11.1 关于 greenplum 权限说明.........................................................................................- 102 - 11.1.1 概述................................................................................................................ - 102 - 11.1.2 赋予权限的步骤总结....................................................................................- 102 - 11.1.3 管理赋予在用户特殊属性上的权限............................................................- 103 - 11.2 权限操作实例........................................................................................................... - 103 - 11.2.1 在用户(USER)特殊属性上的权限.................................................................- 103 - 11.2.1.1 权限说明.............................................................................................- 103 - 11.2.1.2 权限创建实例.....................................................................................- 103 - 11.2.1.3 撤销用户的权限.................................................................................- 104 - 11.2.2 在 DATABASE(数据库)上的权限....................................................................- 105 - 11.2.2.1 权限说明.............................................................................................- 105 - 11.2.2.2 权限创建实例.....................................................................................- 105 - 11.2.2.3 权限撤销实例.....................................................................................- 106 - 11.2.2.4 总结数据库上权限.............................................................................- 107 - 11.2.3 在 SCHEMA(模式)上的权限.......................................................................... - 107 - 11.2.3.1 验证权限说明.....................................................................................- 107 - 11.2.3.2 创建 USER2..........................................................................................- 107 - 11.2.3.3 把 USAGE(使用权限)赋予用户..........................................................- 107 - 11.2.3.4 查看权限的效果.................................................................................- 108 - 11.2.3.5 把 CREATE(创建权限)赋予用户.........................................................- 108 - 11.2.3.6 在用户下查询数据.............................................................................- 108 - 11.2.3.7 总结 SCHEMA(模式)上的权限汇总...................................................- 108 - 11.2.3.8 权限撤销实例.....................................................................................- 108 - 11.2.3.9 验证撤销的权限.................................................................................- 109 - 11.2.4 在 TABLE(表)上的权限...................................................................................- 109 - 11.2.4.1 权限验证说明.....................................................................................- 109 - 11.2.4.2 把 SELECT(查询)权限赋予用户..........................................................- 109 - 11.2.4.3 查看权限效果.....................................................................................- 109 - 11.2.4.4 赋予用户更多的权限.........................................................................- 110 - 11.2.4.5 撤销用户的权限.................................................................................- 110 - 11.2.4.6 查看撤销后的效果.............................................................................- 110 - 11.3 权限参数详解........................................................................................................... - 111 -