文章来源于网络收集而来，版权归原创者所有，如有侵权请及时联系！

Gorm 查询

发布于 2023-06-25 22:01:53 字数 19042 浏览 0 评论 0 收藏 0

1.1.1. 查询

    // 获取第一条记录，按主键排序
    db.First(&user)
    //// SELECT * FROM users ORDER BY id LIMIT 1;

    // 获取最后一条记录，按主键排序
    db.Last(&user)
    //// SELECT * FROM users ORDER BY id DESC LIMIT 1;

    // 获取所有记录
    db.Find(&users)
    //// SELECT * FROM users;

    // 使用主键获取记录
    db.First(&user, 10)
    //// SELECT * FROM users WHERE id = 10;

1.1.2. Where查询条件 (简单SQL)

    // 获取第一个匹配记录
    db.Where("name = ?", "jinzhu").First(&user)
    //// SELECT * FROM users WHERE name = 'jinzhu' limit 1;

    // 获取所有匹配记录
    db.Where("name = ?", "jinzhu").Find(&users)
    //// SELECT * FROM users WHERE name = 'jinzhu';

    db.Where("name <> ?", "jinzhu").Find(&users)

    // IN
    db.Where("name in (?)", []string{"jinzhu", "jinzhu 2"}).Find(&users)

    // LIKE
    db.Where("name LIKE ?", "%jin%").Find(&users)

    // AND
    db.Where("name = ? AND age >= ?", "jinzhu", "22").Find(&users)

    // Time
    db.Where("updated_at > ?", lastWeek).Find(&users)

    db.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&users)

1.1.3. Where查询条件 (Struct & Map)

注意：当使用struct查询时，GORM将只查询那些具有值的字段

    // Struct
    db.Where(&User{Name: "jinzhu", Age: 20}).First(&user)
    //// SELECT * FROM users WHERE name = "jinzhu" AND age = 20 LIMIT 1;

    // Map
    db.Where(map[string]interface{}{"name": "jinzhu", "age": 20}).Find(&users)
    //// SELECT * FROM users WHERE name = "jinzhu" AND age = 20;

    // 主键的Slice
    db.Where([]int64{20, 21, 22}).Find(&users)
    //// SELECT * FROM users WHERE id IN (20, 21, 22);

1.1.4. Not条件查询

    db.Not("name", "jinzhu").First(&user)
    //// SELECT * FROM users WHERE name <> "jinzhu" LIMIT 1;

    // Not In
    db.Not("name", []string{"jinzhu", "jinzhu 2"}).Find(&users)
    //// SELECT * FROM users WHERE name NOT IN ("jinzhu", "jinzhu 2");

    // Not In slice of primary keys
    db.Not([]int64{1,2,3}).First(&user)
    //// SELECT * FROM users WHERE id NOT IN (1,2,3);

    db.Not([]int64{}).First(&user)
    //// SELECT * FROM users;

    // Plain SQL
    db.Not("name = ?", "jinzhu").First(&user)
    //// SELECT * FROM users WHERE NOT(name = "jinzhu");

    // Struct
    db.Not(User{Name: "jinzhu"}).First(&user)
    //// SELECT * FROM users WHERE name <> "jinzhu";

1.1.5. 带内联条件的查询

注意：使用主键查询时，应仔细检查所传递的值是否为有效主键，以避免SQL注入

    // 按主键获取
    db.First(&user, 23)
    //// SELECT * FROM users WHERE id = 23 LIMIT 1;

    // 简单SQL
    db.Find(&user, "name = ?", "jinzhu")
    //// SELECT * FROM users WHERE name = "jinzhu";

    db.Find(&users, "name <> ? AND age > ?", "jinzhu", 20)
    //// SELECT * FROM users WHERE name <> "jinzhu" AND age > 20;

    // Struct
    db.Find(&users, User{Age: 20})
    //// SELECT * FROM users WHERE age = 20;

    // Map
    db.Find(&users, map[string]interface{}{"age": 20})
    //// SELECT * FROM users WHERE age = 20;

1.1.6. Or条件查询

    db.Where("role = ?", "admin").Or("role = ?", "super_admin").Find(&users)
    //// SELECT * FROM users WHERE role = 'admin' OR role = 'super_admin';

    // Struct
    db.Where("name = 'jinzhu'").Or(User{Name: "jinzhu 2"}).Find(&users)
    //// SELECT * FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2';

    // Map
    db.Where("name = 'jinzhu'").Or(map[string]interface{}{"name": "jinzhu 2"}).Find(&users)

1.1.7. 查询链

Gorm有一个可链接的API，你可以这样使用它

    db.Where("name <> ?","jinzhu").Where("age >= ? and role <> ?",20,"admin").Find(&users)
    //// SELECT * FROM users WHERE name <> 'jinzhu' AND age >= 20 AND role <> 'admin';

    db.Where("role = ?", "admin").Or("role = ?", "super_admin").Not("name = ?", "jinzhu").Find(&users)

1.1.8. 扩展查询选项

    // 为Select语句添加扩展SQL选项
    db.Set("gorm:query_option", "FOR UPDATE").First(&user, 10)
    //// SELECT * FROM users WHERE id = 10 FOR UPDATE;

1.1.9. FirstOrInit

获取第一个匹配的记录，或者使用给定的条件初始化一个新的记录（仅适用于struct，map条件）

    // Unfound
    db.FirstOrInit(&user, User{Name: "non_existing"})
    //// user -> User{Name: "non_existing"}

    // Found
    db.Where(User{Name: "Jinzhu"}).FirstOrInit(&user)
    //// user -> User{Id: 111, Name: "Jinzhu", Age: 20}
    db.FirstOrInit(&user, map[string]interface{}{"name": "jinzhu"})
    //// user -> User{Id: 111, Name: "Jinzhu", Age: 20}

1.1.10. Attrs

如果未找到记录，则使用参数初始化结构

    // Unfound
    db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrInit(&user)
    //// SELECT * FROM USERS WHERE name = 'non_existing';
    //// user -> User{Name: "non_existing", Age: 20}

    db.Where(User{Name: "non_existing"}).Attrs("age", 20).FirstOrInit(&user)
    //// SELECT * FROM USERS WHERE name = 'non_existing';
    //// user -> User{Name: "non_existing", Age: 20}

    // Found
    db.Where(User{Name: "Jinzhu"}).Attrs(User{Age: 30}).FirstOrInit(&user)
    //// SELECT * FROM USERS WHERE name = jinzhu';
    //// user -> User{Id: 111, Name: "Jinzhu", Age: 20}

1.1.11. Assign

将参数分配给结果，不管它是否被找到

    db.Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrInit(&user)
    //// user -> User{Name: "non_existing", Age: 20}

    // Found
    db.Where(User{Name: "Jinzhu"}).Assign(User{Age: 30}).FirstOrInit(&user)
    //// SELECT * FROM USERS WHERE name = jinzhu';
    //// user -> User{Id: 111, Name: "Jinzhu", Age: 30}

1.1.12. FirstOrCreate

获取第一个匹配的记录，或创建一个具有给定条件的新记录（仅适用于struct, map条件）

    // Unfound
    db.FirstOrCreate(&user, User{Name: "non_existing"})
    //// INSERT INTO "users" (name) VALUES ("non_existing");
    //// user -> User{Id: 112, Name: "non_existing"}

    // Found
    db.Where(User{Name: "Jinzhu"}).FirstOrCreate(&user)
    //// user -> User{Id: 111, Name: "Jinzhu"}

1.1.13. Attrs

如果未找到记录，则为参数分配结构

    // Unfound
    db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrCreate(&user)
    //// SELECT * FROM users WHERE name = 'non_existing';
    //// INSERT INTO "users" (name, age) VALUES ("non_existing", 20);
    //// user -> User{Id: 112, Name: "non_existing", Age: 20}

    // Found
    db.Where(User{Name: "jinzhu"}).Attrs(User{Age: 30}).FirstOrCreate(&user)
    //// SELECT * FROM users WHERE name = 'jinzhu';
    //// user -> User{Id: 111, Name: "jinzhu", Age: 20}

1.1.14. Assign

将其分配给记录，而不管它是否被找到，并保存回数据库。

    // Unfound
    db.Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrCreate(&user)
    //// SELECT * FROM users WHERE name = 'non_existing';
    //// INSERT INTO "users" (name, age) VALUES ("non_existing", 20);
    //// user -> User{Id: 112, Name: "non_existing", Age: 20}

    // Found
    db.Where(User{Name: "jinzhu"}).Assign(User{Age: 30}).FirstOrCreate(&user)
    //// SELECT * FROM users WHERE name = 'jinzhu';
    //// UPDATE users SET age=30 WHERE id = 111;
    //// user -> User{Id: 111, Name: "jinzhu", Age: 30}

1.1.15. Select

指定要从数据库检索的字段，默认情况下，将选择所有字段;

    db.Select("name, age").Find(&users)
    //// SELECT name, age FROM users;

    db.Select([]string{"name", "age"}).Find(&users)
    //// SELECT name, age FROM users;

    db.Table("users").Select("COALESCE(age,?)", 42).Rows()
    //// SELECT COALESCE(age,'42') FROM users;

1.1.16. Order

在从数据库检索记录时指定顺序，将重排序设置为true以覆盖定义的条件

    db.Order("age desc, name").Find(&users)
    //// SELECT * FROM users ORDER BY age desc, name;

    // Multiple orders
    db.Order("age desc").Order("name").Find(&users)
    //// SELECT * FROM users ORDER BY age desc, name;

    // ReOrder
    db.Order("age desc").Find(&users1).Order("age", true).Find(&users2)
    //// SELECT * FROM users ORDER BY age desc; (users1)
    //// SELECT * FROM users ORDER BY age; (users2)

1.1.17. Limit

指定要检索的记录数

    db.Limit(3).Find(&users)
    //// SELECT * FROM users LIMIT 3;

    // Cancel limit condition with -1
    db.Limit(10).Find(&users1).Limit(-1).Find(&users2)
    //// SELECT * FROM users LIMIT 10; (users1)
    //// SELECT * FROM users; (users2)

1.1.18. Offset

指定在开始返回记录之前要跳过的记录数

    db.Offset(3).Find(&users)
    //// SELECT * FROM users OFFSET 3;

    // Cancel offset condition with -1
    db.Offset(10).Find(&users1).Offset(-1).Find(&users2)
    //// SELECT * FROM users OFFSET 10; (users1)
    //// SELECT * FROM users; (users2)

1.1.19. 18. Count

获取模型的记录数

    db.Where("name = ?", "jinzhu").Or("name = ?", "jinzhu 2").Find(&users).Count(&count)
    //// SELECT * from USERS WHERE name = 'jinzhu' OR name = 'jinzhu 2'; (users)
    //// SELECT count(*) FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2'; (count)

    db.Model(&User{}).Where("name = ?", "jinzhu").Count(&count)
    //// SELECT count(*) FROM users WHERE name = 'jinzhu'; (count)

    db.Table("deleted_users").Count(&count)
    //// SELECT count(*) FROM deleted_users;

1.1.20. Group & Having

    rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Rows()
    for rows.Next() {
        ...
    }

    rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Rows()
    for rows.Next() {
        ...
    }

    type Result struct {
        Date  time.Time
        Total int64
    }
    db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Scan(&results)

1.1.21. Join

指定连接条件

    rows, err := db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Rows()
    for rows.Next() {
        ...
    }

    db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Scan(&results)

    // 多个连接与参数
    db.Joins("JOIN emails ON emails.user_id = users.id AND emails.email = ?", "jinzhu@example.org").Joins("JOIN credit_cards ON credit_cards.user_id = users.id").Where("credit_cards.number = ?", "411111111111").Find(&user)

1.1.22. Pluck

将模型中的单个列作为地图查询，如果要查询多个列，可以使用Scan

    var ages []int64
    db.Find(&users).Pluck("age", &ages)

    var names []string
    db.Model(&User{}).Pluck("name", &names)

    db.Table("deleted_users").Pluck("name", &names)

    // 要返回多个列，做这样：
    db.Select("name, age").Find(&users)

1.1.23. Scan

将结果扫描到另一个结构中。

    type Result struct {
        Name string
        Age  int
    }

    var result Result
    db.Table("users").Select("name, age").Where("name = ?", 3).Scan(&result)

    // Raw SQL
    db.Raw("SELECT name, age FROM users WHERE name = ?", 3).Scan(&result)

1.1.24. Scopes

将当前数据库连接传递到func(*DB) *DB，可以用于动态添加条件

    func AmountGreaterThan1000(db *gorm.DB) *gorm.DB {
        return db.Where("amount > ?", 1000)
    }

    func PaidWithCreditCard(db *gorm.DB) *gorm.DB {
        return db.Where("pay_mode_sign = ?", "C")
    }

    func PaidWithCod(db *gorm.DB) *gorm.DB {
        return db.Where("pay_mode_sign = ?", "C")
    }

    func OrderStatus(status []string) func (db *gorm.DB) *gorm.DB {
        return func (db *gorm.DB) *gorm.DB {
            return db.Scopes(AmountGreaterThan1000).Where("status in (?)", status)
        }
    }

    db.Scopes(AmountGreaterThan1000, PaidWithCreditCard).Find(&orders)
    // 查找所有信用卡订单和金额大于1000

    db.Scopes(AmountGreaterThan1000, PaidWithCod).Find(&orders)
    // 查找所有COD订单和金额大于1000

    db.Scopes(OrderStatus([]string{"paid", "shipped"})).Find(&orders)
    // 查找所有付费，发货订单

1.1.25. 指定表名

    // 使用User结构定义创建`deleted_users`表
    db.Table("deleted_users").CreateTable(&User{})

    var deleted_users []User
    db.Table("deleted_users").Find(&deleted_users)
    //// SELECT * FROM deleted_users;

    db.Table("deleted_users").Where("name = ?", "jinzhu").Delete()
    //// DELETE FROM deleted_users WHERE name = 'jinzhu';

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

列表为空，暂无数据