探索 Go 語言數據庫查詢的終極指南：深入解析 GORM 的強大查詢功能

一、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 預加載關聯記錄，例如：

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`;

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/YaklLsjUJeBA3J2jultaAQ