Go语言数据库查询的终极指南：深入解析GORM的强大查询功能

89391311 • 2024年5月29日 am12:50 • golang • 200 阅读

一、GORM基本查询

1、基本查询


func (c *UserController) UserSelect() {
  //1、查询id=3的用户
  user := models.User{Id: 3}
  models.DB.Find(&user)

  //2、查询所有数据
  user := []models.User{}
  result := models.DB.Find(&user)  //5 （统计有几条数据）
  fmt.Println(result.RowsAffected )
    
  //3、查询第一条数据
  users := models.User{}
  models.DB.First(&users)
    
  c.Data["json"] = user
  c.ServeJSON()
}

2、用主键检索


func (c *UserController) UserSelect() {
  user := []models.User{}

  //1) SELECT * FROM users WHERE id = 2;
  models.DB.First(&user, 2)
  //2) SELECT * FROM users WHERE id IN (1,2,3);
  models.DB.Find(&user, []int{1,2,3})

  c.Data["json"] = user
  c.ServeJSON()
}

二、条件查询

1、String条件


func (c *UserController) UserSelect() {
  user := []models.User{}

  //1) Get first matched record
  models.DB.Where("username = ?", "Snail").First(&user)
  // SELECT * FROM users WHERE name = 'Snail' limit 1;

  //2) Get all matched records
  models.DB.Where("username = ?", "Snail").Find(&user)
  // SELECT * FROM users WHERE name = 'Snail';

  //3） <>
  models.DB.Where("username <> ?", "Snail").Find(&user)
  //SELECT * FROM users WHERE name <> 'Snail';

  //4） IN
  models.DB.Where("username IN (?)", []string{"Snail", "Snail2"}).Find(&user)
  //5） SELECT * FROM users WHERE name in ('Snail','Snail2');

  //6） LIKE
  models.DB.Where("username LIKE ?", "%Snail%").Find(&user)
  // SELECT * FROM users WHERE name LIKE '%Snail%';

  //7） AND
  models.DB.Where("username = ? AND age >= ?", "Snail", "22").Find(&user)
  // SELECT * FROM users WHERE name = 'Snail' AND age >= 22;

  //8） Time
  models.DB.Where("updated_at > ?", lastWeek).Find(&user)
  // SELECT * FROM users WHERE updated_at > '2000-01-01 00:00:00';

  //9） BETWEEN
  models.DB.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&user)
  // SELECT * FROM users WHERE created_at BETWEEN '2000-01-01 00:00:00' AND '2000-01-08 00:00:00';

  c.Data["json"] = user
  c.ServeJSON()
}

2、Struch & Map查询

func (c *UserController) UserSelect() {
  user := []models.User{}

  // 1）Struct
  models.DB.Where(&models.User{Username: "snail", Age: 26}).First(&user)
  // SELECT * FROM users WHERE name = "snail" AND age = 20 LIMIT 1;

  // 2）Map
  models.DB.Where(map[string]interface{}{"username": "snail", "age": 26}).Find(&user)
  // SELECT * FROM users WHERE name = "snail" AND age = 20;

  // 3）主键的切片
  models.DB.Where([]int64{1, 2, 3}).Find(&user)
  //SELECT * FROM users WHERE id IN (20, 21, 22);

  c.Data["json"] = user
  c.ServeJSON()
}

定义的结构体


type User struct {
  Id       int
  Username string
  Age      int
  Email    string
  AddTime  int
}

3、Not条件


func (c *UserController) UserSelect() {
  user := []models.User{}

  //1）不等于
  models.DB.Not("sanil", "lisi").First(&user)
  // SELECT * FROM users WHERE name <> "sanil" LIMIT 1;

  //2） Not In
  models.DB.Not("username", []string{"sanil", "sanil"}).Find(&user)
  //// SELECT * FROM users WHERE name NOT IN ("sanil", "sanil2");

  //3） Not In slice of primary keys
  models.DB.Not([]int64{1,2,3}).First(&user)
  // SELECT * FROM users WHERE id NOT IN (1,2,3);
  
  //4） Plain SQL
  models.DB.Not("name = ?", "sanil").First(&user)
  // SELECT * FROM users WHERE NOT(name = "jinzhu");

  //5） Struct
  models.DB.Not(models.User{Username: "sanil"}).First(&user)
  // SELECT * FROM users WHERE name <> "sanil";

  c.Data["json"] = user
  c.ServeJSON()
}

4、Or条件


func (c *UserController) UserSelect() {
  user := []models.User{}
  
  //1）
  models.DB.Where("role = ?", "admin").Or("role = ?", "super_admin").Find(&user)
  // SELECT * FROM users WHERE role = 'admin' OR role = 'super_admin';

  //2） Struct
  models.DB.Where("name = 'snail'").Or(models.User{Username: "snail2"}).Find(&user)
  // SELECT * FROM users WHERE name = 'snail' OR name = 'snail2';

  //3） Map
  models.DB.Where("name = 'snail'").Or(map[string]interface{}{"name": "snail2"}).Find(&user)
  // SELECT * FROM users WHERE name = 'snail' OR name = 'snail2';
  
  c.Data["json"] = user
  c.ServeJSON()
}

三、高级查询

1、选择特定字段

选择您想从数据库中检索的字段，默认情况下会选择全部字段


db.Select("name", "age").Find(&users)
// SELECT name, age FROM users;

db.Select([]string{"name", "age"}).Find(&users)
// SELECT name, age FROM users;

db.Table("users").Select("COALESCE(age,?)", 42).Rows()
// SELECT COALESCE(age,'42') FROM users;

2、Order

指定从数据库检索记录时的排序方式


db.Order("age desc, name").Find(&users)
// SELECT * FROM users ORDER BY age desc, name;

// 多个 order
db.Order("age desc").Order("name").Find(&users)
// SELECT * FROM users ORDER BY age desc, name;

db.Clauses(clause.OrderBy{
  Expression: clause.Expr{SQL: "FIELD(id,?)", Vars: []interface{}{[]int{1, 2, 3}}, WithoutParentheses: true},
}).Find(&User{})
// SELECT * FROM users ORDER BY FIELD(id,1,2,3)

3、Limit & Offset


db.Limit(3).Find(&users)
// SELECT * FROM users LIMIT 3;

// 通过 -1 消除 Limit 条件
db.Limit(10).Find(&users1).Limit(-1).Find(&users2)
// SELECT * FROM users LIMIT 10; (users1)
// SELECT * FROM users; (users2)

db.Offset(3).Find(&users)
// SELECT * FROM users OFFSET 3;

db.Limit(10).Offset(5).Find(&users)
// SELECT * FROM users OFFSET 5 LIMIT 10;

// 通过 -1 消除 Offset 条件
db.Offset(10).Find(&users1).Offset(-1).Find(&users2)
// SELECT * FROM users OFFSET 10; (users1)
// SELECT * FROM users; (users2)

4、Group & Having


type result struct {
  Date  time.Time
  Total int
}

db.Model(&User{}).Select("name, sum(age) as total").Where("name LIKE ?", "group%").Group("name").First(&result)
// SELECT name, sum(age) as total FROM `users` WHERE name LIKE "group%" GROUP BY `name`


db.Model(&User{}).Select("name, sum(age) as total").Group("name").Having("name = ?", "group").Find(&result)
// SELECT name, sum(age) as total FROM `users` GROUP BY `name` HAVING name = "group"

rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Rows()
for rows.Next() {
  ...
}

rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Rows()
for rows.Next() {
  ...
}

type Result struct {
  Date  time.Time
  Total int64
}
db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Scan(&results)

5、Distinct


db.Distinct("name", "age").Order("name, age desc").Find(&results)

1、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)

// 带参数的多表连接
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)

2、JOins预加载

您可以使用 Joins 实现单条 SQL 预加载关联记录，例如：

 LEFT JOIN `companies` AS `Company` ON `users`.`company_id` = `Company`.`id`;

版权声明：
作者：89391311
链接：https://www.csev.cn/code-2/golang/20240529338.html/
来源：彩色动力-测试分享
版权声明：本文欢迎任何形式转载，转载时完整保留本声明信息（包含原文链接、原文出处、原文作者、版权声明）即可。本文后续所有修改都会第一时间在原始地址更新。

THE END

根据我国《计算机软件保护条例》第十七条规定：“为了学习和研究软件内含的设计思想和原理，通过安装、显示、传输或者存储软件等方式使用软件的，可以不经软件著作权人许可，不向其支付报酬。本站资源仅供个人学习交流，请于下载后 24 小时内删除，不允许用于商业用途，否则法律问题自行承担。

二维码

打赏

用Go语言Gin框架实现Cookie与Session管理

< <上一篇

PHP语言ORM类库ThinkORM玩转JSON数据类型

下一篇>>

文章目录

关闭

微信扫一扫关注蓝威网官方公众号

搜索内容

Go语言数据库查询的终极指南：深入解析GORM的强大查询功能

取消回复

共有 0 条评论

热门文章

标签云

近期文章