03.GORM查询

# 01.GORM基本查询

官方文档 (opens new window)

# 1.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()
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# 1.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
2
3
4
5
6
7
8
9
10
11

# 02.条件查询

# 2.1 String 条件

func (c *UserController) UserSelect() {
	user := []models.User{}

	//1) Get first matched record
	models.DB.Where("username = ?", "zhangsan").First(&user)
	// SELECT * FROM users WHERE name = 'jinzhu' limit 1;

	//2) Get all matched records
	models.DB.Where("username = ?", "zhangsan").Find(&user)
	// SELECT * FROM users WHERE name = 'jinzhu';

	//3） <>
	models.DB.Where("username <> ?", "jinzhu").Find(&user)
	//SELECT * FROM users WHERE name <> 'jinzhu';

	//4） IN
	models.DB.Where("username IN (?)", []string{"jinzhu", "jinzhu 2"}).Find(&user)
	//5） SELECT * FROM users WHERE name in ('jinzhu','jinzhu 2');

	//6） LIKE
	models.DB.Where("username LIKE ?", "%jin%").Find(&user)
	// SELECT * FROM users WHERE name LIKE '%jin%';

	//7） AND
	models.DB.Where("username = ? AND age >= ?", "jinzhu", "22").Find(&user)
	// SELECT * FROM users WHERE name = 'jinzhu' 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()
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

# 2.2 Struch & Map 查询

func (c *UserController) UserSelect() {
	user := []models.User{}

	// 1）Struct
	models.DB.Where(&models.User{Username: "zhangsan", Age: 26}).First(&user)
	// SELECT * FROM users WHERE name = "jinzhu" AND age = 20 LIMIT 1;

	// 2）Map
	models.DB.Where(map[string]interface{}{"username": "zhangsan", "age": 26}).Find(&user)
	// SELECT * FROM users WHERE name = "jinzhu" 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()
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

定义的结构体

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

1
2
3
4
5
6
7

# 2.3 Not条件

func (c *UserController) UserSelect() {
	user := []models.User{}

	//1）不等于
	models.DB.Not("zhangsan", "lisi").First(&user)
	// SELECT * FROM users WHERE name <> "jinzhu" LIMIT 1;

	//2） Not In
	models.DB.Not("username", []string{"zhangsan", "lisi"}).Find(&user)
	//// SELECT * FROM users WHERE name NOT IN ("jinzhu", "jinzhu 2");

	//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 = ?", "jinzhu").First(&user)
	// SELECT * FROM users WHERE NOT(name = "jinzhu");

	//5） Struct
	models.DB.Not(models.User{Username: "jinzhu"}).First(&user)
	// SELECT * FROM users WHERE name <> "jinzhu";

	c.Data["json"] = user
	c.ServeJSON()
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

# 2.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 = 'jinzhu'").Or(models.User{Username: "jinzhu 2"}).Find(&user)
	// SELECT * FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2';

	//3） Map
	models.DB.Where("name = 'jinzhu'").Or(map[string]interface{}{"name": "jinzhu 2"}).Find(&user)
	// SELECT * FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2';
	
	c.Data["json"] = user
	c.ServeJSON()
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

# 03.高级查询

# 3.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;

1
2
3
4
5
6
7
8

# 3.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)

1
2
3
4
5
6
7
8
9
10
11

# 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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

# 3.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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

# 3.5 Distinct

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

# 04.连表查询

# 4.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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 4.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`;

1
2

上次更新: 2024/3/13 15:35:10

← 02.GORM操作 04.GORM 关联查询→