快速入门

    首先您应下载ZNBase的二进制可执行文件（），然后存放在您要部署的机器的任意路径下，进入到该目录后再进行如下操作。

    注意事项：对于生产环境，必须参考安装部署章节进行实际部署！

    A. 启动集群服务

    B. 初始化数据库

    1. bini init --insecure --host=<Address and Port of any Node>

    C. 登入数据库

    1. bini sql --insecure --host=<Address and Port of any Node>

    注意事项：

    1、<Node List of Cluster>:=<Node1 Address>[:<Node1 Port>,][…] 。代表集群的节点列表。

    2、<Node Port>为节点的服务端口号，默认为26257，使用默认值时可省略。

    3、<Node HTTP Port>为节点的HTTP服务端口号，默认为8080，使用默认值时可省略。

    4、<Percent or Actual Usage Size of Physical Memory>代表为该参数分配的内存大小，可以为以小数表示的百分比形式，也可以为以MB、GB等表示表示的固定数值形式。

    5、不指定--store参数时，系统会默认生成一个数据存放目录，目前是*“bini-data"*。

    6、不指定--advertise-addr时，该参数默认值等于--listen-addr。

    Ansible部署：

    一、使用条件

    执行节点已安装ansible，执行节点已配置与NewSQL节点间的ssh互信

    二、param.yaml配置文件参数说明

    1.  server_port: 26257  ----NewSQL服务端口
    2.  monitor_port: 8080  ----NewSQL监控页面端口
    3.  store_path: /opt/node_data  ----NewSQL存储路径
    4.  cache: .25  ----NewSQL的缓存大小，.25代表25%
    5.  max_sql_memory: .1  ----NewSQL*的临时SQL缓存大小，.1代表10%
    6.  certs_dir: /root/certs  ----用户证书文件存放路径（用于客户端连接）
    7.  make_certs_dir: /opt/certs  ----证书文件存放路径
    8.  make_ca_keys_dir: /opt/my-safe-directory  ----CA秘钥文件存放路径
    9.  db_admin: bini  ----拥有admin权限的数据库用户名
    10.  db_password: 123456  ---db_admin数据库用户的密码
    11.  ntpserver: 0.cn.pool.ntp.org  ----NTP服务器IP

    三、使用方法

    （1）在Ansible的host文件中增加组**bini_setup**，组内以IP形式配置部署bini的节点信息

    （2）将bini的二进制文件放在playbook文件同级目录下

    img

    （3）修改param.yaml中的设置参数

    （4）执行ansible-playbook bini_secure.playbook -e "@param.yaml"命令，进行bini部署

    img

    img （5）在NewSQL节点查看集群状态

    注：< IP Address of Node1 >表示第一节点的IP地址，< Service Port of Node1 >表示第一节点的服务端口号。

    （6）在Ansible执行节点查看NewSQL节点进程

    1. ansible bini_setup -m shell -a "ps -ef |grep bini |grep -vi grep"

    3-6

    基本操作

    成功部署ZNBase集群之后，便可以在 ZNBase中执行 SQL 语句了。本文档介绍基本的 SQL 操作。完整的 SQL 语法规则，请参照 ZNBase SQL Language Reference.

    1）创建数据库

    1. \> CREATE DATABASE testdb;

    2）创建新用户test

    1. #非安全模式无法使用密码认证功能，故无法为新用户设置密码
    3. \> CREATE USER test;

    3）授予test用户在数据库testdb及public模式上的所有权限

    4) 使用test用户连接数据库testdb

    1. bini sql --insecure --host=test1:26300 -u test -d testdb
    1. \> CREATE TABLE t1(id INT PRIMARY KEY,amount DECIMAL(32,2) NOT NULL,from_account STRING(100) NOT NULL,to_account STRING(100) NOT NULL,create_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP());

    6) 初始化数据，执行增删改查DML操作

    1. \> INSERT INTO t1 (id,amount,from_account,to_account) VALUES(1,10000.01,'Jack','Tom'),(2,500.89,'Jack','Tom'),(3,3000.1,'Jack','Tom'),
    3. (4,600000,'Tom','Bob'),(5,30000,'Jack','Allen'),(6,1000000,'Bob','Jack'),(7,1000,'Bob','Tom'),(8,999.99,'Allen','Bob'),(9,666.13,'Allen','Tom'),(10,88888,'Jack','Tom');
    5. \> SELECT * FROM t1;
    7.  id |  amount  | from_account | to_account |    create_timestamp      
    9. +----+------------+--------------+------------+---------------------------------+
    11.   1 |  10000.01 | Jack     | Tom    | 2020-11-13 10:01:12.52328+00:00 
    13.   2 |   500.89 | Jack     | Tom    | 2020-11-13 10:01:12.52328+00:00 
    15.   3 |  3000.10 | Jack     | Tom    | 2020-11-13 10:01:12.52328+00:00 
    17.   4 | 600000.00 | Tom     | Bob    | 2020-11-13 10:01:12.52328+00:00 
    19.   5 |  30000.00 | Jack     | Allen   | 2020-11-13 10:01:12.52328+00:00 
    21.   6 | 1000000.00 | Bob     | Jack    | 2020-11-13 10:01:12.52328+00:00 
    23.   7 |  1000.00 | Bob     | Tom    | 2020-11-13 10:01:12.52328+00:00 
    25.   8 |   999.99 | Allen    | Bob    | 2020-11-13 10:01:12.52328+00:00 
    27.   9 |   666.13 | Allen    | Tom    | 2020-11-13 10:01:12.52328+00:00 
    29.  10 |  88888.00 | Jack     | Tom    | 2020-11-13 10:01:12.52328+00:00 
    31. (10 rows)
    35. Time: 3.011334ms
    37. \> SELECT * FROM t1 LIMIT 1;
    39.  id | amount | from_account | to_account |    create_timestamp     
    41. +----+----------+--------------+------------+---------------------------------+
    43.   1 | 10000.01 | Jack     | Tom    | 2020-11-13 10:01:12.52328+00:00 
    45. (1 row)
    49. Time: 986.941µs
    51. \>INSERT INTO t1(id,amount,from_account,to_account) VALUES(11,4800,'Carl','Mike'),(12,3300,'Carl','James'),(13,900,'Diego','Carl');
    53. INSERT 3
    57. Time: 5.402443ms
    63. DELETE FROM t1 WHERE id = 13;
    65. UPDATE 1
    68. Time: 23.905949ms
    72. \> DELETE FROM t1 WHERE id = 13;
    74. DELETE 1
    78. Time: 6.483638ms
    82. \> SELECT * FROM t1 WHERE id > 10;
    84.  id | amount | from_account | to_account |     create_timestamp     
    86. +----+----------+--------------+------------+----------------------------------+
    88.  11 | 4800.00 | Carl     | Mike    | 2020-11-13 10:03:46.200896+00:00 
    90.  12 | 33000.00 | Carl     | James   | 2020-11-13 10:03:46.200896+00:00 
    92. (2 rows)
    94. --分页查询
    96. Time: 1.132672ms
    98. \>SELECT * FROM t1 WHERE id > 10 LIMIT 1;
    100.  id | amount | from_account | to_account |     create_timestamp     
    102. +----+---------+--------------+------------+----------------------------------+
    104.  11 | 4800.00 | Carl     | Mike    | 2020-11-27 10:05:49.914003+00:00 
    106. (1 row)
    110. Time: 1.333644ms
    114. \>SELECT * FROM t1 WHERE id > 10 LIMIT 1 OFFSET 1;
    116.  id | amount | from_account | to_account |     create_timestamp     
    118. +----+----------+--------------+------------+----------------------------------+
    120.  12 | 33000.00 | Carl     | James   | 2020-11-27 10:05:49.914003+00:00 
    122. (1 row)
    126. Time: 782.286µs
    130. \>SELECT * FROM t1 WHERE id > 10 FETCH FIRST 1 ROW ONLY;
    132.  id | amount | from_account | to_account |     create_timestamp     
    134. +----+---------+--------------+------------+----------------------------------+
    136.  11 | 4800.00 | Carl     | Mike    | 2020-11-27 10:05:49.914003+00:00 
    138. (1 row)
    142. Time: 950.55µs
    146. \> SELECT * FROM t1 WHERE id > 10 OFFSET 1 FETCH NEXT 1 ROW ONLY;
    148.  id | amount | from_account | to_account |     create_timestamp     
    150. +----+----------+--------------+------------+----------------------------------+
    152.  12 | 33000.00 | Carl     | James   | 2020-11-27 10:05:49.914003+00:00 
    154. (1 row)
    160. --聚集函数及统计函数
    161. \>SELECT from_account,SUM(amount) expense FROM t1 GROUP BY from_account order by 1;
    163.  from_account | expense  
    165. +--------------+------------+
    167.  Allen    |  1666.12 
    169.  Bob     | 1001000.00 
    171.  Carl     |  37800.00 
    173.  Jack     | 132389.00 
    175.  Tom     | 600000.00 
    177. (5 rows)
    181. Time: 1.76955ms
    185. \>SELECT from_account,SUM(amount) OVER(PARTITION BY from_account) total_expense,
    187. ROW_NUMBER() OVER(PARTITION BY from_account ORDER BY create_timestamp,to_account) rn,amount,to_account FROM t1;
    189.  from_account | total_expense | rn |  amount  | to_account 
    191. +--------------+---------------+----+------------+------------+
    193.  Allen    |    1666.12 | 1 |   999.99 | Bob     
    195.  Allen    |    1666.12 | 2 |   666.13 | Tom     
    197.  Bob     |  1001000.00 | 2 |  1000.00 | Tom     
    199.  Bob     |  1001000.00 | 1 | 1000000.00 | Jack    
    201.  Carl     |   37800.00 | 2 |  4800.00 | Mike    
    203.  Carl     |   37800.00 | 1 |  33000.00 | James    
    205.  Jack     |   132389.00 | 2 |  88888.00 | Tom     
    207.  Jack     |   132389.00 | 3 |  10000.01 | Tom     
    209.  Jack     |   132389.00 | 4 |   500.89 | Tom     
    211.  Jack     |   132389.00 | 5 |  3000.10 | Tom     
    213.  Jack     |   132389.00 | 1 |  30000.00 | Allen    
    215.  Tom     |   600000.00 | 1 | 600000.00 | Bob     
    217. (12 rows)
    221. Time: 1.212536ms
    223. --公共表表达式、视图及多表查询
    225. \>WITH u AS (SELECT DISTINCT from_account account FROM t1 UNION SELECT DISTINCT to_account FROM t1)
    227. SELECT u.account, IFNULL(b.income,0) income, IFNULL(a.expense,0) expense,IFNULL(b.income,0)-IFNULL(a.expense,0) net_income FROM u 
    229. LEFT JOIN (SELECT from_account,SUM(amount) expense FROM t1 GROUP BY from_account) a ON u.account = a.from_account
    231. LEFT JOIN (SELECT to_account,SUM(amount) income FROM t1 GROUP BY to_account) b ON u.account = b.to_account;
    233.  account |  income  | expense  | net_income 
    235. +---------+------------+------------+------------+
    237.  Carl  |     0 |  37800.00 | -37800.00 
    239.  Jack  | 1000000.00 | 132389.00 | 867611.00 
    241.  Tom   | 104055.13 | 600000.00 | -495944.87 
    243.  Bob   | 600999.99 | 1001000.00 | -400000.01 
    245.  Allen  |  30000.00 |  1666.12 |  28333.88 
    247.  Mike  |  4800.00 |     0 |  4800.00 
    249.  James  |  33000.00 |     0 |  33000.00 
    251. (7 rows)