现在的互联网产品早已经和数据库紧密的结合在了一起，后端每天都要和数据库打交道，而且更多的时候是进行查询操作，可以说互联网产品绝大多数时间都是在查询。这篇文章我们主要讲解一下gorm的查询操作。我会把各种情况下的SQL给大家贴出来，希望对大家学习工作有帮助。
type User struct {
Model
Name string
Gender uint8
}
package main
import (
"ginLearn.com/models"
)
func main() {
db := models.DB()
user := models.User{}
var userSlice []models.User
//查询id为1的用户 正序
//SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL AND `users`.`id` = 1 AND ((`users`.`id` = 1)) ORDER BY `users`.`id` ASC LIMIT 1
db.First(&user, 1)
//查询id为1的最后一位用户 逆序
//SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL AND `users`.`id` = 2 AND ((`users`.`id` = 1)) ORDER BY `users`.`id` DESC LIMIT 1
db.Last(&user, 1)
//Where 条件查询
//SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL AND ((name='hanyun'))
db.Where("name=?", "hanyun").Find(&userSlice)
//相等
//SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL AND ((name='hanyun')) ORDER BY `users`.`id` ASC LIMIT 1
db.Where("name=?", "hanyun").First(&user)
//不等
// SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL AND ((name<>'hanyun'))
db.Where("name<>?", "hanyun").Find(&userSlice)
//like
//SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL AND ((name like'%h%'))
db.Where("name like?", "%h%").Find(&userSlice)
//and
//SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL AND ((name like'%h%' and gender=1))
db.Where("name like? and gender=?", "%h%", 1).Find(&userSlice)
//between and
//SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL AND ((created_at between '2020-03-16 11:35:18' and '2020-03-17 17:40:55'))
db.Where("created_at between ? and ?", "2020-03-16 11:35:18", "2020-03-17 17:40:55").Find(&userSlice)
//Struct & Map
//SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL AND ((`users`.`name` = 'hanyun'))
db.Where(&models.User{Name: "hanyun"}).Find(&userSlice)
//为什么？当通过结构体进行查询时，GORM将会只通过非零值字段查询，这意味着如果你的字段值为0，''， false 或者其他 零值时，将不会被用于构建查询条件
//SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL
db.Where(&models.User{Gender: 0}).Find(&userSlice)
// SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL AND ((`users`.`Gender` = 0))
db.Where(map[string]interface{}{"Gender": 0}).Find(&userSlice)
// in
//数值的切片会被当做主键进行查询
//SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL AND ((`users`.`id` IN (8,9)))
db.Where([]int{8, 9}).Find(&userSlice)
// not in
//SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL AND ((`users`.`id` NOT IN (8,9)))
db.Not([]int{8, 9}).Find(&userSlice)
//or查询
//SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL AND ((`users`.`id` IN (8,9)) OR (`users`.`Gender` = 0))
db.Where([]int{8, 9}).Or(map[string]interface{}{"Gender": 0}).Find(&userSlice)
//选择字段
//SELECT name,gender FROM `users` WHERE `users`.`deleted_at` IS NULL AND ((`users`.`id` IN (8,9)))
db.Select("name,gender").Where([]int{8, 9}).Find(&userSlice)
//SELECT name, gender FROM `users` WHERE `users`.`deleted_at` IS NULL AND ((`users`.`id` IN (8,9)))
db.Select([]string{"name", "gender"}).Where([]int{8, 9}).Find(&userSlice)
//排序 order
//单一字段排序
//SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL ORDER BY name desc
db.Order("name desc").Find(&userSlice)
//多字段排序
//SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL ORDER BY name desc,id asc
db.Order("name desc,id asc").Find(&userSlice)
//数量限制
// SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL LIMIT 1
db.Limit(1).Find(&userSlice)
//取消数量限制
//SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL
db.Limit(1).Limit(-1).Find(&userSlice)
//偏移
//SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL LIMIT 1 OFFSET 2
db.Limit(1).Offset(2).Find(&userSlice)
//取消偏移
//SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL LIMIT 1
db.Limit(1).Offset(2).Offset(-1).Find(&userSlice)
//总数
count := 0
//SELECT count(*) FROM `users` WHERE `users`.`deleted_at` IS NULL
db.Find(&userSlice).Count(&count)
//查询的综合运用，包含了分页
//SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL LIMIT 1 OFFSET 1
//SELECT count(*) FROM `users` WHERE `users`.`deleted_at` IS NULL
db.Offset(1).Limit(1).Find(&userSlice).Limit(-1).Offset(-1).Count(&count)
//group
//SELECT name FROM `users` WHERE `users`.`deleted_at` IS NULL GROUP BY name
db.Select("name").Group("name").Find(&userSlice)
//Having
//SELECT count(name) c FROM `users` WHERE `users`.`deleted_at` IS NULL GROUP BY name HAVING (c>1)
db.Select("count(name) c").Group("name").Having("c>?", 1).Find(&userSlice)
}
链接：pan.baidu.com/s/1aoaUH7FK… 提取码：gb7q 复制这段内容后打开百度网盘手机App，操作更方便哦