mx52jing

Appearance

Return to top
On this page

Query

  • 下面的操作都以该Teacher结构体为基础
Go
type Teacher struct {
	gorm.Model
	Name    string `gorm:"type:varchar(32)"`
	Age     uint8
	Gender  uint8
	Created int64 `gorm:"autoCreateTime:milli"`
}

检索单个

First

  • 获取按主键升序排列的第一条记录
Go
func queryByFirst() {
	var firstTeacher Teacher
	DB.First(&firstTeacher)
	fmt.Println("firstTeacher =>", firstTeacher)
}

相当于下面的SQL

SQL
SELECT * FROM `teachers` ORDER BY `teachers`.`id` LIMIT 1;

Last

  • 获取按主键降序排列的最后一条记录
Go
func queryByLast() {
	var lastTeacher Teacher
	DB.Last(&lastTeacher)
	fmt.Println("lastTeacher =>", lastTeacher)
}

相当于下面的SQL

SQL
SELECT * FROM `teachers` ORDER BY `teachers`.`id` DESC LIMIT 1;

Take

  • 获取一条记录，不指定排序字段
Go
func queryByTake() {
	var takeTeacher Teacher
	DB.Take(&takeTeacher)
	fmt.Println("takeTeacher =>", takeTeacher)
}

相当于下面的SQL

SQL
SELECT * FROM `teachers` LIMIT 1

使用Take根据主键id查询

Go
func queryByPrimaryKey() {
	var primaryKeyTeacher Teacher
	DB.Take(&primaryKeyTeacher, 8)
	fmt.Println("primaryKeyTeacher =>", primaryKeyTeacher)
}

相当于下面的SQL

SQL
SELECT * FROM `teachers` WHERE `teachers`.`id` = 8 LIMIT 1;

使用Find根据主键id查询

go
func queryById() {
	id := 6
	var allT []Teacher
	DB.Find(&allT, id)
	fmt.Println("find by id's result allT =>", allT)
}

相当于下面的SQL

SQL
SELECT * FROM `teacher` WHERE `teacher`.`id` = 6

查询全部

Find

Go
func queryAll() {
	var allT []Teacher
	DB.Find(&allT)
	fmt.Println("allT =>", allT)
}

相当于下面的SQL

SQL
SELECT * FROM `teachers`;

条件查询

Where

Go
var conditionT []Teacher
DB.Where("age = ?", "22").Find(&conditionT)
// SELECT * FROM `teachers` WHERE age = '22'

DB.Where("name <> ?", "如燕").Take(&conditionT) // 查询名字不等于如燕的
//  SELECT * FROM `teachers` WHERE name <> '如燕' LIMI 1

// IN
DB.Where("name IN ?", []string{"张三", "如燕"}).Find(&conditionT)
// SELECT * FROM `teachers` WHERE name IN ('张三','如燕')

// LIKE
DB.Where("name LIKE ?", "mul%").Find(&conditionT)
// SELECT * FROM `teachers` WHERE name LIKE 'mul%'

// AND
DB.Where("name LIKE ? AND age >= ?", "mul%", "36").Find(&conditionT)
// SELECT * FROM `teachers` WHERE (name LIKE 'mul%' AND age >= '36')

// BETWEEN AND
DB.Where("age BETWEEN ? AND ?", "38", "60").Find(&conditionT)
// SELECT * FROM `teachers` WHERE (age BETWEEN '38' AND '60')

根据Struct、Map、Slice查询

Go
// Struct
DB.Where(&Teacher{Name: "如燕"}).Find(&conditionT)
// SELECT * FROM `teachers` WHERE `teachers`.`name` = '如燕'

DB.Where(&Teacher{Name: "如燕", Age: 22}).Find(&conditionT)
//SELECT * FROM `teachers` WHERE `teachers`.`name` = '如燕' AND `teachers`.`age` = 22

// Map
DB.Where(map[string]interface{}{"name": "如燕"}).Find(&conditionT)
// SELECT * FROM `teachers` WHERE `name` = '如燕'

// Slice 根据主键ID
// 单个主键ID
DB.Where([]int{22}).Find(&conditionT)
// SELECT * FROM `teachers` WHERE `teachers`.`id` = 22

// 多个主键ID
DB.Where([]int{22, 32}).Find(&conditionT)
// SELECT * FROM `teachers` WHERE `teachers`.`id` IN (22,32)

WARNING

  • 在使用结构体进行查询时，不支持查询0值，如果查询字段为0将忽略该条件
  • 要想支持0值，可以使用map
Go
DB.Where(&Teacher{Name: "如燕", Age: 0}).Find(&conditionT)
// SELECT * FROM `teachers` WHERE `teachers`.`name` = '如燕'

DB.Where(map[string]interface{}{"name": "如燕", "age": 0}).Find(&conditionT)
// SELECT * FROM `teachers` WHERE `age` = 0 AND `name` = '如燕'

内联条件查询

TIP

使用内联条件可以省略Where

根据主键id查询

go
func queryById() {
	id := 6
	var allT []Teacher
	DB.Find(&allT, id)
	fmt.Println("find by id's result allT =>", allT)
}

相当于下面的SQL

SQL
SELECT * FROM `teacher` WHERE `teacher`.`id` = 6

使用普通查询条件

Go
DB.Find(&conditionT, "name = ?", "如燕")
// SELECT * FROM `teachers` WHERE name = '如燕'

DB.Find(&conditionT, "name = ? AND age = ?", "如燕", 22)
//  SELECT * FROM `teachers` WHERE (name = '如燕' AND age = 22)

DB.First(&conditionT, "age >= ?", 22)
// SELECT * FROM `teachers` WHERE age >= 22 ORDER BY `teachers`.`id` LIMIT 1

DB.Last(&conditionT, "name = ? OR age >= ?", "mul%", 20)
// SELECT * FROM `teachers` WHERE (name = 'mul%' OR age >= 20) ORDER BY `teachers`.`id` DESC LIMIT 1

使用结构体、Slice、Map

Go
DB.Find(&conditionT, []int{10, 22})
// SELECT * FROM `teachers` WHERE `teachers`.`id` IN (10,22)

DB.Find(&conditionT, map[string]interface{}{
    "Name": "如燕", "Age": 22,
})
// SELECT * FROM `teachers` WHERE `Age` = 22 AND `Name` = '如燕'

DB.Find(&conditionT, Teacher{Name: "如燕"})
// SELECT * FROM `teachers` WHERE `teachers`.`name` = '如燕'

Not条件

Go
DB.Not("name LIKE ?", "mul%").Find(&conditionT)
// SELECT * FROM `teachers` WHERE NOT name LIKE 'mul%'

// 使用Slice根据主键查询
DB.Not([]int{22, 36}).First(&conditionT)
// SELECT * FROM `teachers` WHERE `teachers`.`id` NOT IN (22,36) LIMIT 1

// 使用Map查询
DB.Not(&conditionT, map[string]interface{}{
    "Name": "如燕",
}).Find(&conditionT)
// SELECT * FROM `teachers` WHERE `Name` <> '如燕' 

// 使用结构体查询(不支持0值)
DB.Not(Teacher{Age: 18}).Find(&conditionT)
// SELECT * FROM `teachers` WHERE `teachers`.`age` <> 18

Or条件

Go
DB.Where("name LIKE ?", "mul%").Or("age <> ?", 18).Find(&conditionT)
// SELECT * FROM `teachers` WHERE (name LIKE 'mul%' OR age <> 18)

DB.Where("name = ?", "如燕").Or("age >= 36").Find(&conditionT)
// SELECT * FROM `teachers` WHERE (name = '如燕' OR age >= 36)

DB.Where("name = ?", "如燕").Or(Teacher{Age: 18}).Find(&conditionT)
// SELECT * FROM `teachers` WHERE (name = '如燕' OR `teachers`.`age` = 18)

DB.Where([]int{22, 66}).Or(map[string]interface{}{
    "Name": "如燕",
    "Age":  22,
}).Find(&conditionT)
// SELECT * FROM `teachers` WHERE (`teachers`.`id` IN (22,66) OR (`Age` = 22 AND `Name` = '如燕'))

Select查询选择的字段

  • Select允许查询指定的字段
Go
DB.Select("name", "age").Where("age >= ?", 18).Find(&conditionT)
// SELECT `name`,`age` FROM `teachers` WHERE age >= 18

DB.Select("name").Find(&conditionT)
// SELECT `name` FROM `teachers`

Order 排序

Go
DB.Order("age desc").Find(&conditionT)
// SELECT * FROM `teachers` ORDER BY age desc

DB.Order("age desc, created_at").Find(&conditionT)
// SELECT * FROM `teachers` ORDER BY age desc, created_at

Limit、Offset

  • Limit：限制指定要检索的最大记录数

  • Offset：指定要跳过的条数

  • 通过Limit(-1)或者Offset(-1)可消除Limit/Offset

Go
DB.Limit(3).Find(&conditionT)
// SELECT * FROM `teachers` LIMIT 3

// 跳过前两条开始查询，并只查询前两条，用于分页，Limit(m) ，m表示要查多少条，Offset(n) n表示跳过多少条后开始查询
DB.Limit(2).Offset(2).Find(&conditionT) 
// SELECT * FROM `teachers` LIMIT 2 OFFSET 2

// 分页示例
limit := 2
page := 1
DB.Limit(limit).Offset((page - 1) * limit).Find(&conditionT)

// 通过 -1 消除 Limit 条件
DB.Limit(10).Find(&conditionT1).Limit(-1).Find(&conditionT2)
// SELECT * FROM teachers LIMIT 10; (conditionT1)
// SELECT * FROM teachers; (conditionT2)

Scan

  • 将结果扫描到结构体中，类似于使用Find的方式
Go
type Result struct {
    Name string
    Age  int
}

var res []Result
DB.Model(&Teacher{}).Select("name").Where("name LIKE ?", "mul%").Scan(&res)
// SELECT `name` FROM `teachers` WHERE name LIKE 'mul%'

DB.Table("teachers").Select("name", "name").Where("name LIKE ?", "mul%").Scan(&res)
// DB.Table("teachers").Select("name", "age").Where("name LIKE ?", "mul%").Scan(&res)

TIP

  • 如果Result结构体定义的列名，不存在数据库中，会返回该列名字段的零值

  • 如果Select方法中选择的字段没有定义在Result结构体中，也会返回该字段的零值

Group分组

  • 根据gender(性别)分组
Go
type GroupResult struct {
    Name   string
    Num    int
    Gender int
}
var groupRes []GroupResult
DB.Model(&Teacher{}).
    Select(
        "GROUP_CONCAT(name, '-', age) AS name",
        "count(id) AS num",
        "gender",
    ).
    Group("gender").
    Scan(&groupRes)
// SELECT GROUP_CONCAT(name, '-', age) AS name,count(id) AS num,`gender` FROM `teachers` GROUP BY `gender`
fmt.Println("conditionT =>", groupRes)
// [{李朗-28,杨方-16,仁阔-26,齐虎-22,潘越-28,multiMap1-22,multiMap4-52 7 0} {如燕-18,mapName-332,multiMap3-42 5 1}]

Raw使用原生SQL

Go
DB.Raw("SELECT name, age FROM teachers WHERE name LIKE ?", "mul%").Scan(&res)