PostgreSQL初识

这几天通过TCP-H以及PostgreSQL完成了一系列数据库实践。跳了很多坑，所以写一点心得，和注意事项

以下是Linxu系统下的实现

准备工具

其实就是要安装PostgreSQL运行环境，以及TCP-H的下载

1.PostgreSQL

首先打开命令行菜单，输入命令
sudo apt-get install postgresql-client
sudo apt-get install postgresql
完成了数据库的安装，PostgreSQL是对象-关系型数据库管理系统。与作为竞争对手的MYSQL相比，两者都是不相上下，所以作为小白的我也不评价了，我只知道老师要我装PostgreSQL…….

安装完后，在命令行输入

再输入装机的时候设置的密码即可进入PostgreSQL系统，然后我们输入

就可以启动PostgreSQL

它的命令操作简直就是sql语言，下面介绍几种我觉得比较实用的命令：

• CREATE DATABASE god;

意思是创建一个名为god的数据库

• \c god

进入创建的数据库

• CREATE TABLE student (s_id CHAR(10), s_name CHAR(20))；

创建一个名为student的表，表内有两列，一列s_id，一列s_name

• INSERT INTO student VALUES (‘12345678’, ‘zhangsan’);

插入一组数据，s_id为12345678，s_name为’zhangsan’

• \d

列出当前所建的所有表，比如

• drop table 表名;

从数据库中删除该表

值得注意的是，这里除了\c \d这样的操作，所有语句结束必须有分号“；”，PostgreSQL是以分号判断语句输入是否结束的，其次我在网上看到的，语句不分大小写…..但是根据我的实验，也只有写语句的时候不分大小写，如果建一个表CREAT TABLE Lineitem(…)，在数据库中显示表名是小写的，但真要用的时候，还是得写Lineitem，也就是除非是自己定义的，其他的语句都不分大小写，但我认为，自己应该严格区分大小写，这是一个好习惯。

2.TPC-H

下载TPC-H
刚下完TPC-H的我很懵，这是干什么的？这些文件是啥玩意，按照下面慢慢做就能明白
首先解压下载的文件，在dbgen文件夹找到makefile文件

按如下修改，把该文件后缀删掉就行，然后make一下，不要管报错，就会生成很多新文件
在当前路径下打开终端，输入
./dbgen -s 1 -f
会生成很多tbl文件，我们打开region.tbl(强行用gedit打开，不是好习惯，用vim打开才是一个称职的程序员)

可以看到有五组数据“|”符号把每组数据分成了三个部分，比如第一组数据，第一个元素是“0”，第二个是“AFRICA”非洲？？，第三个一大啪啦不知道是啥。其实通过下图就能理解

可以看到，这里有一个REGION，里面有REGIONKEY，NAME，COMMENT。那么就好理解了，三个数据对应三个元素，至于最前面的那个数，我认为应该是每组数据的id，总之，就是类似于主键的存在(虽然这个表的主键应该是REGIONKEY)。

同样其它七个表的tbl文件我们也可以找到。现在我的思路清晰了

TPC-H中其实就是存放着一系列表的数据，一共有8个表，这八个表有着相互联系，总体来说就是一个记录着顾客预定轮船出航相关信息的数据库，里面有顾客的信息，轮船供应商的信息，订单信息，每次出航记录的信息。都以2进制编码存入了对应的tbl文件中。我认为，TPC-H就是官方提供的一系列数据样例，用于使用者测试查询语句性能的工具

我们在处理数据的时候，当然不希望看到像tbl里的那种数据，还需要靠”|“符号区分元素，对表理解不深的话甚至不知道每个数据的含义，那么我们就转换成JSON吧，作为一个小白，有大哥教我方法（这是版权问题，重要的事情！！），写八个c文件通过文件I/O把tbl数据转为JSON，这里以ORDERS表为例，首先编写order.c

#include<stdio.h>

void injson(FILE* out, FILE* in, char* temp, char* cur) {
    int i = 0;
    char t;
    while((t = getc(in)) != '|') {
        if (t == '\n')
            continue;
        temp[i++] = t;
    }

    temp[i] = '\0';
    fprintf(out, "\"%s\": \"%s\"", cur, temp);
    if (!(cur[0] == 'c' && cur[6] == 't'))
        fprintf(out, ", ");
}

int main(int argc, char* argv[]) {
    if (argc < 2) {
        fprintf(stderr, "%s\n", "Wrong!");
        return 0;
    }

    char orderkey[9] = "orderkey";
    char custkey[8] = "custkey";
    char orderstatus[12] = "orderstatus";
    char totalprice[11] = "totalprice";
    char orderdate[10] = "orderdate";
    char orderpriority[14] = "orderpriority";
    char clerk[6] = "clerk";
    char shippriority[13] = "shippriority";
    char comment[8] = "comment";

    char temp[100] = {'\0'};
    char t;
    int id = 0;

    FILE* in;
    FILE* out;
    in = fopen(argv[1], "r");
    if (in == NULL)
        fprintf(stderr, "%s\n", "readfile dosen't exist!");
    out = fopen(argv[2], "w");
    if (out == NULL)
        fprintf(stderr, "%s\n", "writefile dosen't exist!");    

    while (!feof(in)) {
        fseek(in, -1, SEEK_CUR);
        fprintf(out, "%d\t{", id++);
        injson(out, in, temp, orderkey);
        injson(out, in, temp, custkey);
        injson(out, in, temp, orderstatus);
        injson(out, in, temp, totalprice);
        injson(out, in, temp, orderdate);
        injson(out, in, temp, orderpriority);
        injson(out, in, temp, clerk);
        injson(out, in, temp, shippriority);
        injson(out, in, temp, comment);
        fprintf(out, "}\n");
        while ((t = getc(in)) == '\n');
    }

    fclose(in);
    fclose(out);
    return 0;
}

然后输入

即可完成转换，打开orders.json可以看到这样的数据

然后我们在命令行输入如下几条命令(在我创建的数据库god下)

COPY语句中的路径就是生成的orders.json的路径，直接查文件的属性复制粘贴就可以。

重要的事情

我对于这几条命令有些自己的理解：表Orders有两个元素，一个是int类型的id，一个是json类型的orders，orders类似与指针，如果要获取orderkey只需要写orders->>’orderkey’即可，同样，如果我们在sql语句中写了一个Orders O，可以理解为O是一个Orders的对象，那么O.orders->>’orderkey’也可以获得orderkey。COPY语句则是将该路径的json文件写入Orders表中

进行中，遇到的问题

引入了json文件后，SQL语句有了更多的花样，比如orders->>’orderkey’，因此也产生了很多疑问

如何同时调用两个表

比如

SELECT P.name
FROM Part P, Lineitem L
WHERE P.partkey = L.partkey AND L.shipdate = ‘1997-01-01’

后来发现了两种实现方式，

第一种：

建表时

CREATE TABLE Part(id int, part json)
CREATE TABLE Lineitem(id int, lineitem json)

这样子part、lineitem像一个数据指针，原来的语句可以写成

SELECT part->>’name’
FROM Part, Lineitem
WHERE part->>’partkey’ = lineitem->>’partkey’ AND lineitem->>’shipdate’ = ‘1997-01-01’

第二种：

建表时

CREATE TABLE Part(id int, data json)
CREATE TABLE Lineitem(id int, data json)

原语句改成

SELECT P.data->>’name’
FROM Part P, Lineitem L
WHERE P.data->>’partkey’ = L.data->>’partkey’ AND L.data->>’shipdate’ = ‘1997-01-01’

原理在上面重要的事情里说了。。。。。

所有的操作默认都是针对字符串

我想列出lineitem中在1997年之前最大的extendedprice
单纯的写

SELECT MAX(lineitem->>'extendedprice') AS MAX

结果是9999.99，为了验证我写了

SELECT lineitem->>’extendedprice’
FROM Lineitem
ORDER BY lineitem->>’extendedprice’ DESC

结果99999.99竟然还在9999.99下面，后来明白了，这是因为如果不加任何说明，比较大小都是按照字符串比较大小的规则来比较的，因此需要用到

cast(lineitem->>'extendedprice' as float)

可以把其变为float类型进行比较

GROUP BY 完全懵逼

写了一个语句

SELECT part->>’partkey’, part->>’name’, part->>’brand’, COUNT(*)
......
GROUP BY part->>’partkey’

结果是报错，原因很简单，看看group by 的执行的过程
先执行select 的操作返回一个程序集
然后去执行分组的操作，这时候他将根据group by 后面的字段
进行分组，并且将相同的字段并称一列数据，如果group by 后面没有这个字段的话就要分成好多的数据。
但是分组就只能将相同的数据分成两列数据，而一列中又只能放入一个字段，所以那些没有进行分组的
数据系统不知道将数据放入哪里，所以就出现此错误
目前一种分组情况只有一条记录，一个数据格是无法放入多个数值的
所以这里就需要通过一定的处理将这些多值的列转化成单值，然后将其放在对应的数据格中，那么完成这个步骤的就是聚合函数。这就是为什么这些函数叫聚合函数(aggregate functions)了
这是一位大哥写的，非常清楚

如何在语句中临时建表并使用

在FROM语句中建表

FROM(SELECT part->>’partkey’ AS key, part->>’name’ AS name
      FROM Part
 ) AS Temp

这样Temp就是一个临时表对象，AS后面的就是它对应的成员，比如Temp.key就是选出来的partkey，但是要注意它的生命期，不然会入大坑

总结

关于GROUP BY还是有点懵，我还会继续参考官方文档继续学习，总得来说，SQL的魅力在我面前开始展现，第一次将所学到的数据库知识运用到实际中，蛮有成就感。另外，由于TPC-H数据量很大，不同的语句，尽管可以达到相同效果，但是在大数据量的情况下，不好的查询方式会暴露出极大的问题，其中一个明显的问题就是查询时间过长，因此我需要在实现了要求的同时更多地思考：还有没有更好的办法？我相信，世界上没有最安全的系统，同样，也不会有最好的方法。慢慢学习，慢慢前进！