gorm 教程 一（1）https://developer.aliyun.com/article/1391735
Where
// 获取第一条匹配的记录 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) // BETWEEN db.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&users)
Struct & Map
// 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);
当通过struct进行查询的时候，GORM 将会查询这些字段的非零值， 意味着你的字段包含 0， ‘’， false 或者其他 零值, 将不会出现在查询语句中， 例如:
db.Where(&User{Name: "jinzhu", Age: 0}).Find(&users) SELECT * FROM users WHERE name = "jinzhu";
你可以考虑适用指针类型或者 scanner/valuer 来避免这种情况。
// 使用指针类型 type User struct { gorm.Model Name string Age *int } // 使用 scanner/valuer type User struct { gorm.Model Name string Age sql.NullInt64 }
Not
和 Where查询类似
db.Not("name", "jinzhu").First(&user) SELECT * FROM users WHERE name <> "jinzhu" LIMIT 1; // 不包含 db.Not("name", []string{"jinzhu", "jinzhu 2"}).Find(&users) SELECT * FROM users WHERE name NOT IN ("jinzhu", "jinzhu 2"); //不在主键 slice 中 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; // 原生 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";
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) SELECT * FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2';
行内条件查询
和 Where 查询类似。
需要注意的是，当使用链式调用传入行内条件查询时，这些查询不会被传参给后续的中间方法。
// 通过主键进行查询 (仅适用于主键是数字类型) db.First(&user, 23) SELECT * FROM users WHERE id = 23 LIMIT 1; // 非数字类型的主键查询 db.First(&user, "id = ?", "string_primary_key") SELECT * FROM users WHERE id = 'string_primary_key' 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;
额外的查询选项
// 为查询 SQL 添加额外的选项 db.Set("gorm:query_option", "FOR UPDATE").First(&user, 10) SELECT * FROM users WHERE id = 10 FOR UPDATE;
FirstOrInit
获取第一条匹配的记录，或者通过给定的条件下初始一条新的记录（仅适用与于 struct 和 map 条件）。
// 未查询到 db.FirstOrInit(&user, User{Name: "non_existing"}) user -> User{Name: "non_existing"} // 查询到 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}
Attrs
如果未找到记录，则使用参数初始化 struct
// 未查询到 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} // 查询到 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}
Assign
无论是否查询到数据，都将参数赋值给 struct
// 未查询到 db.Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrInit(&user) user -> User{Name: "non_existing", Age: 20} // 查询到 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} FirstOrCreate 获取第一条匹配的记录，或者通过给定的条件创建一条记录 （仅适用与于 struct 和 map 条件）。 // 未查询到 db.FirstOrCreate(&user, User{Name: "non_existing"}) INSERT INTO "users" (name) VALUES ("non_existing"); user -> User{Id: 112, Name: "non_existing"} // 查询到 db.Where(User{Name: "Jinzhu"}).FirstOrCreate(&user) user -> User{Id: 111, Name: "Jinzhu"}
e
高级查询
子查询
使用 *gorm.expr 进行子查询
db.Where("amount > ?", DB.Table("orders").Select("AVG(amount)").Where("state = ?", "paid").QueryExpr()).Find(&orders) // SELECT * FROM "orders" WHERE "orders"."deleted_at" IS NULL AND (amount > (SELECT AVG(amount) FROM "orders" WHERE (state = 'paid')));
查询
指定要从数据库检索的字段，默认情况下，将选择所有字段。
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;
Order
使用 Order 从数据库查询记录时，当第二个参数设置为 true 时，将会覆盖之前的定义条件。
db.Order("age desc, name").Find(&users) SELECT * FROM users ORDER BY age desc, name; // 多个排序条件 db.Order("age desc").Order("name").Find(&users) SELECT * FROM users ORDER BY age desc, name; // 重新排序 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)
Limit
指定要查询的最大记录数
db.Limit(3).Find(&users) SELECT * FROM users LIMIT 3; // 用 -1 取消 LIMIT 限制条件 db.Limit(10).Find(&users1).Limit(-1).Find(&users2) SELECT * FROM users LIMIT 10; (users1) SELECT * FROM users; (users2)
Offset
指定在开始返回记录之前要跳过的记录数。
db.Offset(3).Find(&users) SELECT * FROM users OFFSET 3; // 用 -1 取消 OFFSET 限制条件 db.Offset(10).Find(&users1).Offset(-1).Find(&users2) SELECT * FROM users OFFSET 10; (users1) SELECT * FROM users; (users2)
gorm 教程 一（3）https://developer.aliyun.com/article/1391737
