golang基础-Postgresql-ORM框架github.com/go-pg/pg学习二(增删改)

创建表

在操作数据库的增删改查之前,我们先创建数据库表
这里就直接贴出代码来,如果有疑问可以参考我下面的博客:
golang基础-Postgresql-ORM框架github.com/go-pg/pg学习一(连接数据库、创建、删除表)

package main

import (
	"fmt"
	"github.com/go-pg/pg"
	"github.com/go-pg/pg/orm"
	_ "github.com/lib/pq"
)

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

func connet() *pg.DB{
	db:=pg.Connect(&pg.Options{
		User:user,
		Password:password,
		Database:dbname,
	})

	var n int
	_,err:=db.QueryOne(pg.Scan(&n),"SELECT 1")
	if err != nil{
		panic(err)
	}
	return db
}

type User struct {
	Id     int64
	Name   string
	Emails []string
	tableName struct{} `sql:"users"`
}
type Story struct {
	Id       int64
	Title    string
	AuthorId int64
	Author   *User
	tableName struct{} `sql:"storys"`
}


func (s Story) String() string {
	return fmt.Sprintf("Story<%d %s %s>", s.Id, s.Title, s.Author)
}
func (u User) String() string {
	return fmt.Sprintf("User(%d %s %v)", u.Id, u.Name, u.Emails)
}
func CreateTabel(db *pg.DB)  error{
	for _,model:= range []interface{}{&User{}, &Story{}}{
		err:=db.CreateTable(model,&orm.CreateTableOptions{
			IfNotExists:true,
			FKConstraints:true,
		})
		if err!= nil{
			return err
		}
	}
	return nil
}

func main()  {
	db:=connet()
	CreateTabel(db)
}

表如下:

go=# \d
               List of relations
 Schema |     Name      |   Type   |   Owner    
--------+---------------+----------+------------
 public | storys        | table    | wyfzhiliao
 public | storys_id_seq | sequence | wyfzhiliao
 public | users         | table    | wyfzhiliao
 public | users_id_seq  | sequence | wyfzhiliao
(4 rows)

插入

插入的方法有
func (db *DB) Insert(model …interface{}) error
func (q *Query) Insert(values …interface{}) (Result, error)
直接通过例子来了解吧,我下面的代码都做了注释

func main()  {
	db:=connet()
	CreateTabel(db)

	user1:=&User{
		Name:"user1",
		Emails:[]string{"user1@qq.com","user1@163.com"},
	}


	//插入方法1   db.Insert
	db.Insert(user1)
	fmt.Println("user1-->",user1)


	//批量插入  db.Insert
	user2:=&User{
		Name:"user2",
		Emails:[]string{"user2@qq.com","user2@163.com"},
	}

	user3:=&User{
		Name:"user3",
		Emails:[]string{"user3@qq.com","user3@163.com"},
	}

	db.Insert(user2,user3)
	fmt.Println("user2->",user2,"-user2->",user3)



	//批量插入db.Model.insert
	user4:=&User{
		Name:"user4",
		Emails:[]string{"user4@qq.com","user4@163.com"},
	}

	user5:=&User{
		Name:"user5",
		Emails:[]string{"user5@qq.com","user5@163.com"},
	}

	db.Model(user4,user5).Insert()
	fmt.Println("user4->",user4,"-user5->",user5)

	//db.insert 插入切片
	user6_7:=[]User{
		{
			Name:"user6",Emails:[]string{"user6@qq.com","user6@163.com"},
		},{
			Name:"user7",Emails:[]string{"user7@qq.com","user7@163.com"},
		},
	}

	db.Insert(&user6_7)
	fmt.Println("user6->",user6_7)

}

我们可以通过打印对应的model,可以查询插入成功后的信息,比如数据库生成的ID等等

user1--> User(20 user1 [user1@qq.com user1@163.com])
user2-> User(21 user2 [user2@qq.com user2@163.com]) -user2-> User(22 user3 [user3@qq.com user3@163.com])

user4-> User(23 user4 [user4@qq.com user4@163.com]) -user5-> User(24 user5 [user5@qq.com user5@163.com])

user6-> [User(25 user6 [user6@qq.com user6@163.com]) User(26 user7 [user7@qq.com user7@163.com])]

通过查询数据库,新增数据如下:

go=# select * from users;
 id | name  |              emails               
----+-------+-----------------------------------
 20 | user1 | ["user1@qq.com", "user1@163.com"]
 21 | user2 | ["user2@qq.com", "user2@163.com"]
 22 | user3 | ["user3@qq.com", "user3@163.com"]
 23 | user4 | ["user4@qq.com", "user4@163.com"]
 24 | user5 | ["user5@qq.com", "user5@163.com"]
 25 | user6 | ["user6@qq.com", "user6@163.com"]
 26 | user7 | ["user7@qq.com", "user7@163.com"]
(7 rows)

删除

全匹配删除


func main()  {
	db:=connet()

	user:=User{
		Id:20,
		Name:"user1",
		Emails:[]string{"user1@qq.com","user1@163.com"},
	}
	db.Delete(&user)

}

查询数据库发现还剩下6条记录

go=# select * from users;
 id | name  |              emails               
----+-------+-----------------------------------
 21 | user2 | ["user2@qq.com", "user2@163.com"]
 22 | user3 | ["user3@qq.com", "user3@163.com"]
 23 | user4 | ["user4@qq.com", "user4@163.com"]
 24 | user5 | ["user5@qq.com", "user5@163.com"]
 25 | user6 | ["user6@qq.com", "user6@163.com"]
 26 | user7 | ["user7@qq.com", "user7@163.com"]
(6 rows)

或者使用如下的方法

func main()  {
	db:=connet()

	user2:=User{
		Id:21,
		Name:"user2",
		Emails:[]string{"user2@qq.com","user2@163.com"},
	}
	user3:=User{
		Id:22,
		Name:"user3",
		Emails:[]string{"user3@qq.com","user3@163.com"},
	}

	db.Model(&user2,&user3).Delete()
}

然后记录查询数据库发现被删除了2条

go=# select * from users;
 id | name  |              emails               
----+-------+-----------------------------------
 23 | user4 | ["user4@qq.com", "user4@163.com"]
 24 | user5 | ["user5@qq.com", "user5@163.com"]
 25 | user6 | ["user6@qq.com", "user6@163.com"]
 26 | user7 | ["user7@qq.com", "user7@163.com"]
(4 rows)

go=# 

带条件删除

我们还可以带条件删除
这里只是列举一个简单的,后续会详细的学习select的用法

	ids := pg.In([]int{23,24})
	db.Model(&User{}).Where("id IN (?)", ids).Delete()

然后记录查询数据库发现被删除了2条

go=# select * from users;
 id | name  |              emails               
----+-------+-----------------------------------
 25 | user6 | ["user6@qq.com", "user6@163.com"]
 26 | user7 | ["user7@qq.com", "user7@163.com"]
(2 rows)

go=# 

或者如下:
通过某个字段查找然后删除即可

	db.Model(&User{}).Where("name=?","user2").Delete()

修改

func (db *DB) Update(model interface{}) error

通过ID修改部分字段

通过ID,修改25 | user6 | ["user6@qq.com", "user6@163.com"]这一条记录的name为user6_

func main()  {
	db:=connet()

	user:=User{
		Id:25,
	}

	err:=db.Select(&user)
	if err!=nil{
		panic(err)
	}

	user.Name = "user6_"

	err=db.Update(&user)

	if err!=nil{
		panic(err)
	}
}

执行上述代码,查看数据库已经修改了

go=# select * from users;
 id |  name  |              emails               
----+--------+-----------------------------------
 26 | user7  | ["user7@qq.com", "user7@163.com"]
 25 | user6_ | ["user6@qq.com", "user6@163.com"]
(2 rows)

go=# 

通过ID全匹配修改

上述修改的方法,也可以直接用如下的方式,但是效果与上面不一样,这里会使emails字段为空

func main()  {
	db:=connet()

	user:=&User{
		Id:25,
		Name:"user6+",
	}

	db.Update(user)
}

查询数据库返现已经修改了

go=# select * from users;
 id |  name  |              emails               
----+--------+-----------------------------------
 26 | user7  | ["user7@qq.com", "user7@163.com"]
 25 | user6+ | 
(2 rows)

更新几个字段1

我们也可以更新几个字段,如下的方法:


func main()  {
	db:=connet()

	user:=&User{
		Id:25,
		Name:"user66",
		Emails:[]string{"user6@qq.com", "user6@163.com"},
	}

	db.Model(user).Set("name=?name,emails=?emails").Where("id=?id").Update()

}


现在在查看下数据库,ID为25的记录已经发生了变化

go=# select * from users;
 id |  name  |              emails               
----+--------+-----------------------------------
 26 | user7  | ["user7@qq.com", "user7@163.com"]
 25 | user66 | ["user6@qq.com", "user6@163.com"]
(2 rows)

更新几个字段2

我们还可以利用如下的方法,更新某几个字段


func main()  {
	db:=connet()

	user:=&User{
		Id:25,
		Name:"user66",
		Emails:[]string{"user6@qq.com", "user6@163.com"},
	}
	db.Model(user).Column("name","emails").WherePK().Update()
}

我们在来查看下数据库发现,已经发生了变化

go=# select * from users;
 id | name  |               emails                
----+-------+-------------------------------------
 26 | user7 | ["user7@qq.com", "user7@163.com"]
 25 | user6 | ["user66@qq.com", "user66@163.com"]
(2 rows)

以下也可以

	user:=&Order{
		//Id:25,
		Body:"BodyBodyBodyBodyBody",
		Platform:"sfs",
	}

	db.Model(user).Set("body=?body").Where("platform=?platform").Update()

今天就到这里吧,好累,下次在继续分析其他的方法,注入select等等

相关推荐
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页