現(xiàn)在的互聯(lián)網(wǎng)產(chǎn)品早已經(jīng)和數(shù)據(jù)庫緊密的結合在了一起，后端每天都要和數(shù)據(jù)庫打交道，而且更多的時候是進行查詢操作，可以說互聯(lián)網(wǎng)產(chǎn)品絕大多數(shù)時間都是在查詢。這篇文章我們主要講解一下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
    //數(shù)值的切片會被當做主鍵進行查詢
    //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)

    //數(shù)量限制
    // SELECT * FROM `users`  WHERE `users`.`deleted_at` IS NULL LIMIT 1
    db.Limit(1).Find(&userSlice)
    //取消數(shù)量限制
    //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)

    //總數(shù)
    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)
}

image

鏈接：https://pan.baidu.com/s/1aoaUH7FKEFzgGoQqqIP2ZQ
提取碼：gb7q
復制這段內(nèi)容后打開百度網(wǎng)盤手機App，操作更方便哦

image