[golang gin框架] 7.Gin GORM增删改查以及查询语句详解

1.GORM CURD

找到要操作数据库表的控制器，然后引入 models 模块

models/user.go

package models

type User struct { // 结构体首字母大写, 和数据库表名对应, 默认访问数据表users, 可以设置访问数据表的方法

    Id  int

    Username string

    Age int

    Email string

    AddTime int // 大驼峰命名

}

//配置数据库操作的表名称

func (User) TableName() string {

    return "user"

}

(1).添加数据

增加成功后会返回刚才增加的记录

//添加数据

func (con UserController) Add(c *gin.Context) {

    user := &models.User{

        Username: "张三",

        Age: 12,

        Email: "test@qq.com",

        AddTime: int(models.GetUnix()),

    }

    err := models.DB.Create(user).Error // 通过数据的指针来创建

    if err != nil {

        fmt.Println(err)

        return

    }

    fmt.Println(user)

    c.String(200, "增加数据成功")

}

更多添加语句见文档:创建 | GORM - The fantastic ORM library for Golang, aims to be developer friendly.

(2).查找

func (con UserController) Index(c *gin.Context) {

    //查找全部

    userList := []models.User{}

    //查询所有用户,把结果保存到userList切片中

    models.DB.Find(&userList)

    c.JSON(http.StatusOK, gin.H{

        "result": userList,

    })

    //条件查询

    //查询age大于30的用户

    //查询数据库

    userList := []models.User{}

    models.DB.Where("age > ?", 30).Find(&userList)

    c.JSON(http.StatusOK, gin.H{

        "result": userList,

    })

}

更多查询语句：查询 | GORM - The fantastic ORM library for Golang, aims to be developer friendly.

(3).修改

func (con UserController) Edit(c *gin.Context) {

    //查询id等于5的字段

    user := models.User{Id: 5}

    models.DB.Find(&user)

    c.JSON(200, gin.H{

        "user": user,

    })

    //更新所有数据

    user.Username = "你好"

    user.Age = 111

    models.DB.Save(&user)

    //更新单个列

    user1 := models.User{}

    models.DB.Model(&user1).Where("id = ?", 9).Update("username", "大")

    //一般情况的更新

    user2 := models.User{}

    models.DB.Where("id = ?", 1).Find(&user2)

    user2.Username = "好"

    user2.Age = 31

    models.DB.Save(&user2)

}

更多修改的方法参考：更新 | GORM - The fantastic ORM library for Golang, aims to be developer friendly.

(4).删除

//删除数据

func (conn UserController) Delete(c *gin.Context) {

    user := models.User{Id: 1}

    models.DB.Delete(&user)

    //删除数据

    user1 := models.User{}

    models.DB.Where("Age = ?", 31).Delete(&user1)

}

更多删除的方法参考:删除 | GORM - The fantastic ORM library for Golang, aims to be developer friendly.

2.GORM 查询语句详解

(1).查询全部数据

    //查询全部数据

    list := []models.Nav{}

    //把结果保存到list切片中

    models.DB.Find(&list)

    c.JSON(http.StatusOK, gin.H{

        "result": list,

    })

(2).查询一条数据

    //查询一条数据

    nav := models.Nav{Id: 21}

    models.DB.Find(&nav)

    c.JSON(http.StatusOK, gin.H{

        "result": nav,

    })

    nav1 := models.Nav{}

    models.DB.First(&nav1, "21")

    c.JSON(http.StatusOK, gin.H{

        "result": nav1,

    })

    nav2 := models.Nav{}

    models.DB.Model(models.Nav{Id: 21}).First(&nav2)

    c.JSON(http.StatusOK, gin.H{

        "result": nav2,

    })

(3).Where

=,<,>,<=,>=,!=,IS NOT NULL,IS NULL,BETWEEN AND,NOT BETWEEN AND,IN,OR,AND,NOT,LIKE查询

    查询id>3的数据

    nav := []models.Nav{}

    models.DB.Where("id > ?", 3).Find(&nav)

    c.JSON(http.StatusOK, gin.H{

        "result": nav,

    })

    //查询id>3 并且 id < 9 的数据

    nav1 := []models.Nav{}

    models.DB.Where("id > ? and id < ?", 3, 9).Find(&nav1)

    c.JSON(http.StatusOK, gin.H{

        "result": nav1,

    })

    //查询id=3,5,6的数据

    nav2 := []models.Nav{}

    models.DB.Where("id in ?", []int{3, 5, 6}).Find(&nav2)

    c.JSON(http.StatusOK, gin.H{

        "result": nav2,

    })

    //查询标题包含 '会'的内容

    nav3 := []models.Nav{}

    models.DB.Where("title like ?", "%会%").Find(&nav3)

    c.JSON(http.StatusOK, gin.H{

        "result": nav3,

    })

    //查询id>3 并且 id < 9 的数据,使用between and

    nav4 := []models.Nav{}

    models.DB.Where("id between ? and ?", 3, 9).Find(&nav4)

    c.JSON(http.StatusOK, gin.H{

        "result": nav4,

    })

    //查询id=2或者iD=3的数据

    //方法一

    nav5 := []models.Nav{}

    models.DB.Where("id = ? or id =  ?", 3, 9).Find(&nav5)

    c.JSON(http.StatusOK, gin.H{

        "result": nav5,

    })

    //方法二

    nav6 := []models.Nav{}

    models.DB.Where("id = ?", 2).Or("id = ?", 3).Find(&nav6)

    c.JSON(http.StatusOK, gin.H{

        "result": nav6,

    })

(4).选择字段查询

    //使用select返回指定的字段, 使用结构体时,没有的数据会默认为0或""

    nav7 := []models.Nav{}

    models.DB.Select("title", "id").Where("id = ?", 2).Or("id = ?", 3).Find(&nav7)

    c.JSON(http.StatusOK, gin.H{

        "result": nav7,

    })

(5).排序 Limit 、Offset

    //order排序, limit, Offset

    nav8 := []models.Nav{}

    models.DB.Order("id desc").Find(&nav8)

    c.JSON(http.StatusOK, gin.H{

        "result": nav8,

    })

    nav9 := []models.Nav{}

    models.DB.Order("id desc").Limit(3).Find(&nav9)

    c.JSON(http.StatusOK, gin.H{

        "result": nav9,

    })

    //分页

    nav10 := []models.Nav{}

    models.DB.Order("id desc").Offset(2).Limit(3).Find(&nav10)

    c.JSON(http.StatusOK, gin.H{

        "result": nav10,

    })

(6).count计数

    nav11 := []models.Nav{}

    var num int64

    models.DB.Where("id > ? and id < ?", 3, 9).Find(&nav11).Count(&num)

    c.JSON(http.StatusOK, gin.H{

        "result": num,

    })

(7).Distinct

从模型中选择不相同的值

nav := []models.Nav{}

models.DB.Distinct("title").Order("id desc").Find(&nav)

c.JSON(200, gin.H{ 

    "nav": nav,

 })

//SELECT DISTINCT `title` FROM `nav` ORDER BY id desc

(8).Scan

将结果扫描到结构中的方法与我们使用find的方法类似

type Result struct {

    Name string

    Age int

}

var result Result

models.DB.Table("users").Select("name", "age").Where("name = ?", "Antonio").Scan(&result)

// 原生 SQL

models.DB.Raw("SELECT name, age FROM users WHERE name = ?", "Antonio").Scan(&result)

var result []models.User

models.DB.Raw("SELECT * FROM user").Scan(&result)

fmt.Println(result)

(9).Joins

查询内联

type result struct {

  Name  string

  Email string

}

db.Model(&User{}).Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Scan(&result{})

// SELECT users.name, emails.email FROM `users` left join emails on emails.user_id = users.id

rows, err := db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Rows()

for rows.Next() {

  ...

}

db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Scan(&results)

// multiple joins with parameter

db.Joins("JOIN emails ON emails.user_id = users.id AND emails.email = ?", "jinzhu@example.org").Joins("JOIN credit_cards ON credit_cards.user_id = users.id").Where("credit_cards.number = ?", "411111111111").Find(&user)

//Joins 预加载

You can use Joins eager loading associations with a single SQL, for example:

db.Joins("Company").Find(&users)

// SELECT `users`.`id`,`users`.`name`,`users`.`age`,`Company`.`id` AS `Company__id`,`Company`.`name` AS `Company__name` FROM `users` LEFT JOIN `companies` AS `Company` ON `users`.`company_id` = `Company`.`id`;

// inner join

db.InnerJoins("Company").Find(&users)

// SELECT `users`.`id`,`users`.`name`,`users`.`age`,`Company`.`id` AS `Company__id`,`Company`.`name` AS `Company__name` FROM `users` INNER JOIN `companies` AS `Company` ON `users`.`company_id` = `Company`.`id`;

Join with conditions

db.Joins("Company", db.Where(&Company{Alive: true})).Find(&users)

// SELECT `users`.`id`,`users`.`name`,`users`.`age`,`Company`.`id` AS `Company__id`,`Company`.`name` AS `Company__name` FROM `users` LEFT JOIN `companies` AS `Company` ON `users`.`company_id` = `Company`.`id` AND `Company`.`alive` = true;

For more details, please refer to Preloading (Eager Loading).

Joins 一个衍生表

You can also use Joins to join a derived table.

type User struct {

    Id  int

    Age int

}

type Order struct {

    UserId     int

    FinishedAt *time.Time

}

query := db.Table("order").Select("MAX(order.finished_at) as latest").Joins("left join user user on order.user_id = user.id").Where("user.age > ?", 18).Group("order.user_id")

db.Model(&Order{}).Joins("join (?) q on order.finished_at = q.latest", query).Scan(&results)

// SELECT `order`.`user_id`,`order`.`finished_at` FROM `order` join (SELECT MAX(order.finished_at) as latest FROM `order` left join user user on order.user_id = user.id WHERE user.age > 18 GROUP BY `order`.`user_id`) q on order.finished_at = q.latest

(10).查看执行的失去了

package models

//gorm文档: https://gorm.io/zh_CN/docs/index.html

//连接数据库核心代码

import (

    "fmt"

    "gorm.io/gorm"

    "gorm.io/driver/mysql"

)

//全局使用DB,就需要把DB定义成公有的

var DB *gorm.DB

var err error

//自动初始化数据库

func init()  {

    // 参考 https://github.com/go-sql-driver/mysql#dsn-data-source-name 获取详情

    dsn := "root:123456@tcp(127.0.0.1:3306)/gin?charset=utf8mb4&parseTime=True&loc=Local"

    DB, err = gorm.Open(mysql.Open(dsn), &gorm.Config{})

    //查看执行的sql

    DB.Debug()

    if err != nil {

        fmt.Println(err)

    }

————————————————

                            版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

原文链接：https://blog.csdn.net/zhoupenghui168/article/details/129784764