golang基础-Postgresql使用、Go中使用PostgreSQL（github.com/lib/pq学习）

postgresql介绍

官网
https://www.postgresql.org/

PostgreSQL is a powerful, open source object-relational database system.
PostgreSQL是一个功能强大的开源对象关系数据库管理系统(ORDBMS)。 用于安全地存储数据; 支持最佳做法，并允许在处理请求时检索它们。

特点

PostgreSQL可在所有主要操作系统(即Linux，UNIX(AIX，BSD，HP-UX，SGI IRIX，Mac OS X，Solaris，Tru64)和Windows等)上运行

PostgreSQL支持文本，图像，声音和视频，并包括用于C/C++，Java，Perl，Python，Ruby，Tcl和开放数据库连接(ODBC)的编程接口

PostgreSQL支持SQL的许多功能，例如复杂SQL查询，SQL子选择，外键，触发器，视图，事务，多进程并发控制(MVCC)，流式复制(9.0)，热备(9.0))

在PostgreSQL中，表可以设置为从“父”表继承其特征

可以安装多个扩展以向PostgreSQL添加附加功能

postgresql安装使用(mac下)

在Mac系统上通过通过brew into postgresql@9.6 安装数据库进行安装即可

配置环境变量

echo 'export PATH="/usr/local/opt/postgresql@9.6/bin:$PATH"' >> ~/.bash_profile

启动

pg_ctl -D /usr/local/var/postgresql\@9.6/ -l logfile start
server starting

关闭

pg_ctl -D /usr/local/var/postgresql\@9.6/ stop -s -m fast

下面先罗列一些简单的postgresql命令
创建用户

CREATE USER wyfzhiliao with password 'wyf';

创建数据库

CREATE DATABASE go OWNER wyfzhiliao;

授权

GRANT ALL PRIVILEGES ON DATABASE zldd_api TO wyfzhiliao;

系统用户登入

psql postgres 

系统用户直接登入访问某个库

psql -U wyfzhiliao -d zldd_user -h 127.0.0.1

链接数据库

zldd_api=> \c zldd_user 

创建表

CREATE TABLE u_order(
id      varchar(40) PRIMARY KEY  NOT NULL,
app_id           varchar(40),
mch_id           varchar(40),
nonce_str        varchar(40),
sign             varchar(40),
sign_type             varchar(40),
body             varchar(130),
out_trade_no             varchar(40),
total_fee            int,
fee_type          varchar(10) default 'CNY' ,
spbill_create_ip            varchar(20),
time_start timestamp with time zone,
time_expire timestamp with time zone,
trade_type      varchar(20),
wechat_open_id      varchar(130),
wechat_union_id      varchar(130),
wechat_pay_key      varchar(40),
user_id      varchar(40),
platform      varchar(20),
pay_courses      text[],
pay_status int
);

类型如下：

go_api=# \d u_order;
                             Table "public.u_order"
      Column      |           Type           |            Modifiers             
------------------+--------------------------+----------------------------------
 id               | character varying(40)    | not null
 app_id           | character varying(40)    | 
 mch_id           | character varying(40)    | 
 nonce_str        | character varying(40)    | 
 sign             | character varying(40)    | 
 sign_type        | character varying(40)    | 
 body             | character varying(130)   | 
 out_trade_no     | character varying(40)    | 
 total_fee        | integer                  | 
 fee_type         | character varying(10)    | default 'CNY'::character varying
 spbill_create_ip | character varying(20)    | 
 time_start       | timestamp with time zone | 
 time_expire      | timestamp with time zone | 
 trade_type       | character varying(20)    | 
 wechat_open_id   | character varying(130)   | 
 wechat_union_id  | character varying(130)   | 
 wechat_pay_key   | character varying(40)    | 
 user_id          | character varying(40)    | 
 platform         | character varying(20)    | 
 pay_courses      | text[]                   | 
 pay_status       | integer                  | 
Indexes:
    "u_order_pkey" PRIMARY KEY, btree (id)

go_api=# \d go

列举出数据库中的表

zldd_user=> \d  

列举创建的数据库

zldd_user=> \l  

退出

postgres-# \q 

创建表

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric
);

删除表

drop table products;

查询表

go=> select * from products;
 product_no | name | price 
------------+------+-------
(0 rows)

插入表

go=> INSERT INTO products VALUES (1, 'Cheese', 9.99);
INSERT 0 1
go=> select * from products;
 product_no |  name  | price 
------------+--------+-------
          1 | Cheese |  9.99
(1 row)

go=> 

Go中使用PostgreSQL（github.com/lib/pq学习）

安装

    go get -u github.com/lib/pq

参考资料

https://godoc.org/github.com/lib/pq

连接postgres

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/lib/pq"
)

const (
	host     = "localhost"
	port     = 5432
	user     = "wyfzhiliao"
	password = "wyfzhiliao"
	dbname   = "go"
)

func main()  {

	psqlInfo := fmt.Sprintf("host=%s port=%d user=%s "+
		"password=%s dbname=%s sslmode=disable",
		host, port, user, password, dbname)
	db, err := sql.Open("postgres", psqlInfo)
	if err != nil {
		panic(err)
	}
	defer db.Close()

	err = db.Ping()
	if err != nil {
		panic(err)
	}

	fmt.Println("Successfully connected!")
}

sql.Open的第一个参数是driver名称，第二个参数是driver连接数据库的信息。DB不是连接，并且只有当需要使用时才会创建连接，如果想立即验证连接，需要用Ping()方法，如下：

You can also connect to a database using a URL. For example:

connStr := "postgres://pqgotest:password@localhost/pqgotest?sslmode=verify-full"
db, err := sql.Open("postgres", connStr)

输出如下：

另外列出出来可以使用的参数：

* dbname - The name of the database to connect to
* user - The user to sign in as
* password - The user's password
* host - The host to connect to. Values that start with / are for unix
  domain sockets. (default is localhost)
* port - The port to bind to. (default is 5432)
* sslmode - Whether or not to use SSL (default is require, this is not
  the default for libpq)
* fallback_application_name - An application_name to fall back to if one isn't provided.
* connect_timeout - Maximum wait for connection, in seconds. Zero or
  not specified means wait indefinitely.
* sslcert - Cert file location. The file must contain PEM encoded data.
* sslkey - Key file location. The file must contain PEM encoded data.
* sslrootcert - The location of the root certificate file. The file
  must contain PEM encoded data.

查询postgres

现在我的postgresql数据库中有如下一条记录

go=> select * from products where name='Proto';
 product_no | name  | price 
------------+-------+-------
          2 | Proto |  9.99
(1 row)

接下来我们将它查询出来，看如下代码

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/lib/pq"
)

const (
	host     = "localhost"
	port     = 5432
	user     = "wyfzhiliao"
	password = "wyfzhiliao"
	dbname   = "go"
)

func connectDB() *sql.DB{
	psqlInfo := fmt.Sprintf("host=%s port=%d user=%s "+
		"password=%s dbname=%s sslmode=disable",
		host, port, user, password, dbname)

	db, err := sql.Open("postgres", psqlInfo)
	if err != nil {
		panic(err)
	}

	err = db.Ping()
	if err != nil {
		panic(err)
	}
	return db
}


func query(db *sql.DB){
	var id,name,price string


	rows,err:=db.Query(" select * from products where name=$1","Proto")

	if err!= nil{
		fmt.Println(err)
	}
	defer rows.Close()

	for rows.Next(){
		err:= rows.Scan(&id,&name,&price)

		if err!= nil{
			fmt.Println(err)
		}
	}

	err = rows.Err()
	if err!= nil{
		fmt.Println(err)
	}

	fmt.Println(id,name,price)
}

func main()  {
	db:=connectDB()
	query(db)

}

输出结果如下：

2 Proto 9.99

上面代码的过程为：db.Query()表示向数据库发送一个query，defer rows.Close()非常重要（关闭连接），遍历rows使用rows.Next()，把遍历到的数据存入变量使用rows.Scan()，遍历完成后检查error。有几点需要注意：

(1) 检查遍历是否有error
(2) 结果集(rows)未关闭前，底层的连接处于繁忙状态。当遍历读到最后一条记录时，会发生一个内部EOF错误，自动调用rows.Close()，但是如果提前退出循环，rows不会关闭，连接不会回到连接池中，连接也不会关闭。所以手动关闭非常重要。rows.Close()可以多次调用，是无害操作。

单条查询QueryRow

当我们查询单条记录时候，我们可以直接用QueryRow

func query_one(db *sql.DB){
	var price string
	err := db.QueryRow(" select price from products where name=$1", "Proto").Scan(&price)
	if err != nil {
		if err == sql.ErrNoRows {
		} else {
			log.Fatal(err)
		}
	}
	fmt.Println(price)
}

输出结果如下：

9.99

插入

func insertUser(db *sql.DB)  {
	stmt,err := db.Prepare("insert into products(product_no,name,price) values($1,$2,$3)")
	if err != nil {
		log.Fatal(err)
	}
	_,err = stmt.Exec(6,"dump","10.88")
	if err != nil {
		log.Fatal(err)
	}else {
		fmt.Println("insert into user_tbl success")
	}
}

插入结果如下，我们看到数据库多了一条记录

go=> select * from products;
 product_no |  name  | price 
------------+--------+-------
          1 | Cheese |  9.99
          2 | Proto  |  9.99
          6 | dump   | 10.88
(3 rows)

修改

func UpdateUser(db *sql.DB) {
	stmt,err := db.Prepare("UPDATE  products  set price=$1 WHERE  product_no=$2")
	if err != nil {
		log.Fatal(err)
	}
	_,err = stmt.Exec("11.88",6)
	if err != nil {
		log.Fatal(err)
	}else {
		fmt.Println("udpate user_tbl success")
	}

}

更新后的数据库记录

go=> select * from products;
 product_no |  name  | price 
------------+--------+-------
          1 | Cheese |  9.99
          2 | Proto  |  9.99
          6 | dump   | 11.88
(3 rows)

删除

func DeleteUser(db *sql.DB) {
	stmt,err := db.Prepare("DELETE  FROM products WHERE  product_no=$1")
	if err != nil {
		log.Fatal(err)
	}

	_,err = stmt.Exec(6)
	if err != nil {
		log.Fatal(err)
	}else {
		fmt.Println("delete form user_tbl success")
	}
}

删除记录后的数据库数据

go=> select * from products;
 product_no |  name  | price 
------------+--------+-------
          1 | Cheese |  9.99
          2 | Proto  |  9.99
(2 rows)

go=>