Gorm框架-高级查询

高级查询

连接初始化

package main

import (
    "fmt"
    "gorm.io/driver/mysql"
    "gorm.io/gorm"
    "gorm.io/gorm/logger"
)

var Db *gorm.DB
var mySqlLogger logger.Interface

func init() {
    username := "root"   //账号
    password := "123456" //密码
    host := "127.0.0.1"  //数据库地址，可以是Ip或者域名
    port := 3306         //数据库端口
    Dbname := "gorm"     //数据库名
    timeout := "10s"     //连接超时，10秒

    mySqlLogger = logger.Default.LogMode(logger.Info)

    dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True&loc=Local&timeout=%s",
       username, password, host, port, Dbname, timeout)
    //连接MYSQL, 获得DB类型实例，用于后面的数据库读写操作。
    db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
       // 日志配置
       Logger: mySqlLogger,
    })
    if err != nil {
       panic("连接数据库失败, error=" + err.Error())
    }
    // 连接成功
    Db = db
}

package main

type Student struct {
    ID     uint   `gorm:"size:3"`
    Name   string `gorm:"size:8"`
    Age    int    `gorm:"size:3"`
    Gender bool
    Email  *string `gorm:"size:32"`
}

func main() {
    var studentList []Student
    DB.Find(&studentList).Delete(&studentList) // 清空表
    // 添加数据
    studentList = []Student{
       {ID: 1, Name: "李元芳", Age: 32, Email: PtrString("lyf@yf.com"), Gender: true},
       {ID: 2, Name: "张武", Age: 18, Email: PtrString("zhangwu@lly.cn"), Gender: true},
       {ID: 3, Name: "枫枫", Age: 23, Email: PtrString("ff@yahoo.com"), Gender: true},
       {ID: 4, Name: "刘大", Age: 54, Email: PtrString("liuda@qq.com"), Gender: true},
       {ID: 5, Name: "李武", Age: 23, Email: PtrString("liwu@lly.cn"), Gender: true},
       {ID: 6, Name: "李琦", Age: 14, Email: PtrString("liqi@lly.cn"), Gender: false},
       {ID: 7, Name: "晓梅", Age: 25, Email: PtrString("xiaomeo@sl.com"), Gender: false},
       {ID: 8, Name: "如燕", Age: 26, Email: PtrString("ruyan@yf.com"), Gender: false},
       {ID: 9, Name: "魔灵", Age: 21, Email: PtrString("moling@sl.com"), Gender: true},
    }
    DB.Create(&studentList)
}

func PtrString(email string) *string {
    return &email
}

Where

// 1. 查询用户名是枫枫
// SELECT * FROM `students` WHERE name = '枫枫'
DB.Where("name = ?", "枫枫").Find(&studentList)
DB.Find(&studentList, "name = ?", "枫枫") // 也可以

// 2. 查询用户名不是枫枫
// SELECT * FROM `students` WHERE NOT name = '枫枫'
fmt.Println(DB.Not("name = ?", "枫枫").Find(&studentList).RowsAffected) // 输出多少行

// 3. 查询名字包含 如燕,李元芳 的
// SELECT * FROM `students` WHERE name in ('如燕','李元芳')
DB.Where("name in ?", []string{"如燕", "李元芳"}).Find(&studentList)
DB.Find(&studentList, "name in ?", []string{"如燕", "李元芳"}) // 也可以

// 4. 查询姓李的
// SELECT * FROM `students` WHERE name like '李%'
DB.Where("name like ?", "李%").Find(&studentList)

// 5. 年龄大于23 且 是qq邮箱
// SELECT * FROM `students` WHERE age > 23 and email like '%@qq.com'
DB.Where("age > 23 and email like ?", "%@qq.com").Find(&studentList)

// 6. 女生 或 是QQ邮箱
// SELECT * FROM `students` WHERE gender = false or email like '%@qq.com'
DB.Where("gender = ? or email like ?", false, "%@qq.com").Find(&studentList)

结构体查询

// 会过滤零值
// SELECT * FROM `students` WHERE `students`.`name` = '李元芳'
DB.Where(&Student{Name: "李元芳", Age: 0}).Find(&users)
// SELECT * FROM `students` WHERE `students`.`name` = '李元芳' AND `students`.`age` = 32
DB.Where(&Student{Name: "李元芳", Age: 32}).Find(&users)

MAP查询

DB.Where(map[string]any{"name": "李元芳", "age": 0}).Find(&users)
// SELECT * FROM `students` WHERE `age` = 0 AND `name` = '李元芳'

Select选择字段

DB.Select("name", "age").Find(&users)
fmt.Println(users)
// 没有被选中，会被赋零值

可以使用扫描Scan，将选择的字段存入另一个结构体中

type User struct {
  Name string
  Age  int
}
var students []Student
var users []User
DB.Select("name", "age").Find(&students).Scan(&users)
fmt.Println(users)

这样写也是可以的，不过最终会查询两次，还是不这样写

SELECT `name`,`age` FROM `students`
SELECT `name`,`age` FROM `students`

这样写就只查询一次了

type User struct {
  Name string
  Age  int
}
var users []User
DB.Model(&Student{}).Select("name", "age").Scan(&users)
fmt.Println(users)

还可以这样

var users []User
DB.Table("students").Select("name", "age").Scan(&users)
fmt.Println(users)

Scan是根据column列名进行扫描的

type User struct {
  Name123 string `gorm:"column:name"`
  Age     int
}
var users []User
DB.Table("students").Select("name", "age").Scan(&users)
fmt.Println(users)

排序

var users []Student
DB.Order("age desc").Find(&users)
fmt.Println(users)
// desc    降序
// asc     升序

分页查询

var ageList []int
// 查询男生的个数和女生的个数
DB.Table("students").Select("count(id)").Group("gender").Scan(&ageList)

type AggeGroup struct {
  Gender int
  Count  int `gorm:"column:count(id)"`
}

var agge []AggeGroup
// 查询男生的个数和女生的个数
DB.Table("students").Select("count(id)", "gender").Group("gender").Scan(&agge)

type AggeGroup struct {
  Gender int
  Count  int    `gorm:"column:count(id)"`
  Name   string `gorm:"column:group_concat(name)"`
}

var agge []AggeGroup
// 查询男生的个数和女生的个数
DB.Table("students").Select("count(id)", "gender", "group_concat(name)").Group("gender").Scan(&agge)

原生SQL

type AggeGroup struct {
  Gender int
  Count  int    `gorm:"column:count(id)"`
  Name   string `gorm:"column:group_concat(name)"`
}

var agge []AggeGroup
DB.Raw(`SELECT count(id), gender, group_concat(name) FROM students GROUP BY gender`).Scan(&agge)

子查询

// 查询大于平均年龄的用户
原生sql
// select * from students where age > (select avg(age) from students);
var users []Student
DB.Model(Student{}).Where("age > (?)", DB.Model(Student{}).Select("avg(age)")).Find(&users)
fmt.Println(users)

命名参数

var users []Student

DB.Where("name = @name and age = @age", sql.Named("name", "枫枫"), sql.Named("age", 23)).Find(&users)
DB.Where("name = @name and age = @age", map[string]any{"name": "枫枫", "age": 23}).Find(&users)
fmt.Println(users)

Scope查询引用

func Age23(db *gorm.DB) *gorm.DB {
  return db.Where("age > ?", 23)
}

func main(){
  var users []Student
  DB.Scopes(Age23).Find(&users)
  fmt.Println(users)
}