// 查询用户名是张三、李四的 var studentList []Student DB.Debug().Where("name in ?", []string{"张三", "李四"}).Find(&studentList) // SELECT * FROM `tb_student` WHERE name in ('张三','李四') fmt.Println(studentList) //[{1 张三 20 false 0x140002849e0} {2 李四 22 false 0x14000284a00}]
// 等价于 DB.Find(&studentList, "name in (?)", []string{"张三", "李四"})
1.2 模糊查询
1 2 3 4 5 6 7 8
// 模糊匹配 用户名带杨的 var studentList []Student DB.Debug().Where("name like ?", "杨%").Find(&studentList) // SELECT * FROM `tb_student` WHERE name like '杨%' fmt.Println(studentList) // [{4 杨七一 24 false 0x14000220b20} {5 杨四 99 true 0x14000220b40}]
// 查找age大于20,且邮箱是qq的 var studentList []Student DB.Debug().Where("age > ? and email like ?", 22, "%@qq.com").Find(&studentList) // SELECT * FROM `tb_student` WHERE age > 22 and email like '%@qq.com' fmt.Println(studentList)
// 等价于 DB.Where("age > ?", 22).Where("email like ?", "%@qq.com").Find(&studentList)
1 2 3 4 5 6
// 查询gender 为false,且邮箱是qq的 var studentList []Student DB.Debug().Where("gender = ? or email like ?", false, "%@qq.com").Find(&studentList) // SELECT * FROM `tb_student` WHERE gender = false or email like '%@qq.com'
// 等价于 DB.Where("gender = ?", false).Or("email like ?", "%qq@.com").Find(&studentList)
1.3 结构体查询
1 2 3 4 5 6
// 使用结构体查询 var studentList []Student DB.Debug().Where(&Student{Name: "张三"}).Find(&studentList) // SELECT * FROM `tb_student` WHERE `tb_student`.`name` = '张三'
// 将 gender 统计分组 type Group struct { Count int`gorm:"column:count(id)"` Gender string } var groupList []Group DB.Model(Student{}).Select("count(id)", "gender").Group("gender").Scan(&groupList) fmt.Println(groupList) // [{4 0} {1 1}]
// 等价于 type Group struct { Count int Gender string } var groupList []Group DB.Model(Student{}).Select("count(id) as count", "gender").Group("gender").Scan(&groupList)
1 2 3 4 5 6 7 8 9
// 通过group_concat字段可以将查询出的明细列出来 type Group struct { Count int Gender string NameList string } var groupList []Group DB.Model(Student{}).Select("group_concat(name) as name_list", "count(id) as count", "gender").Group("gender").Scan(&groupList) fmt.Println(groupList) // [{4 0 张三,李四,老五,杨七一} {1 1 杨四}]
8. 执行原生Sql
1 2 3
// 执行原生sql var studentList []Student DB.Debug().Raw("select * from tb_student where name =?", "张三").Find(&studentList) // select * from tb_student where name ='张三'
9. 子查询
使用上次查询的结果作为本次查询的参数。
1 2 3
// 查询大于平均年龄的用户 var studentList []Student DB.Debug().Where("age > (?)", DB.Model(Student{}).Select("avg(age)")).Find(&studentList) // SELECT * FROM `tb_student` WHERE age > (SELECT avg(age) FROM `tb_student`)
10. 命名参数
可以使用问号进行传参,但是如果参数太多容易混淆,可以使用匿名参数的方式。
1 2 3 4 5 6 7 8
var student Student DB.Debug().Take(&student, "name = @name and age = @age", sql.Named("name", "杨七一"), sql.Named("age", "24")) // SELECT * FROM `tb_student` WHERE name = '杨七一' and age = '24' LIMIT 1