db.Where("name <> ?","jinzhu").Where("age >= ? and role <> ?",20,"admin").Find(&users) //// SELECT * FROM users WHERE name <> 'jinzhu' AND age >= 20 AND role <> 'admin';
// 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}
5.9 Attrs
如果未找到记录，则使用参数初始化结构
1 2 3 4 5 6 7 8 9 10 11 12 13
// 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}
5.10 Assign
将参数分配给结果，不管它是否被找到
1 2 3 4 5 6 7
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}
5.11 Select
指定要从数据库检索的字段，默认情况下，将选择所有字段;
1 2 3 4 5 6 7 8 9
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;
5.12 Order
在从数据库检索记录时指定顺序，将重排序设置为true以覆盖定义的条件
1 2 3 4 5 6 7 8 9 10 11
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)
5.13 Limit
指定要检索的记录数
1 2 3 4 5 6 7
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)
5.14 offset
指定在开始返回记录之前要跳过的记录数
1 2 3 4 5 6 7
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)
5.15 Count
获取模型的记录数
1 2 3 4 5 6 7 8 9
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;
5.16 Group & Having
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
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)
5.17 Join
指定连接条件
1 2 3 4 5 6 7 8 9
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)
5.18 Pluck
将模型中的单个列作为地图查询，如果要查询多个列，可以使用 Scan
1 2 3 4 5 6 7 8 9 10
var ages []int64 db.Find(&users).Pluck("age", &ages)
var names []string db.Model(&User{}).Pluck("name", &names)
// 更新单个属性，类似于`Update` db.Model(&user).UpdateColumn("name", "hello") //// UPDATE users SET name='hello' WHERE id = 111;
// 更新多个属性，与“更新”类似 db.Model(&user).UpdateColumns(User{Name: "hello", Age: 18}) //// UPDATE users SET name='hello', age=18 WHERE id = 111;
6.5 Batch Updates 批量更新
Callbacks 在批量更新时不会运行
1 2 3 4 5 6 7 8 9
db.Table("users").Where("id IN (?)", []int{10, 11}).Updates(map[string]interface{}{"name": "hello", "age": 18}) //// UPDATE users SET name='hello', age=18 WHERE id IN (10, 11);
// 删除存在的记录 db.Delete(&email) //// DELETE from emails where id=10;
// 为Delete语句添加额外的SQL选项 db.Set("gorm:delete_option", "OPTION (OPTIMIZE FOR UNKNOWN)").Delete(&email) //// DELETE from emails where id=10 OPTION (OPTIMIZE FOR UNKNOWN);
7.2 批量删除
删除所有匹配记录
1 2 3 4 5
db.Where("email LIKE ?", "%jinzhu%").Delete(Email{}) //// DELETE from emails where email LIKE "%jinhu%";
db.Delete(Email{}, "email LIKE ?", "%jinzhu%") //// DELETE from emails where email LIKE "%jinhu%";
