简单查询
查询单条
var user User // 根据主键查询第一条记录SELECT * FROM users ORDER BY id LIMIT 1; db.First(&user) // 随机获取一条记录SELECT * FROM users LIMIT 1; db.Take(&user) // 根据主键查询最后一条记录SELECT * FROM users ORDER BY id DESC LIMIT 1; db.Last(&user) // 查询指定的某条记录(仅当主键为整型时可用)SELECT * FROM users WHERE id = 10; db.First(&user, 10)
查询多条
users := make([]User, 0) // 查询所有的记录SELECT * FROM users; db.Find(&users)
Where条件
var user User users := make([]User, 0) // Get first matched record //SELECT * FROM users WHERE name = 'wxf' limit 1; db.Where("name = ?", "wxf").First(&user) // Get all matched records //SELECT * FROM users WHERE name = 'wxf'; db.Where("name = ?", "wxf").Find(&users) // <> //SELECT * FROM users WHERE name <> 'wxf'; db.Where("name <> ?", "wxf").Find(&users) // IN //SELECT * FROM users WHERE name in ('wxf','wxf 2'); db.Where("name IN (?)", []string{"wxf", "wxf 2"}).Find(&users) // LIKE //SELECT * FROM users WHERE name LIKE '%w%'; db.Where("name LIKE ?", "%w%").Find(&users) // AND //SELECT * FROM users WHERE name = 'wxf' AND age >= 20; db.Where("name = ? AND age >= ?", "wxf", "20").Find(&users) // Time //SELECT * FROM users WHERE updated_at > '2000-01-01 00:00:00'; db.Where("updated_at > ?", lastWeek).Find(&users) // BETWEEN //SELECT * FROM users WHERE created_at BETWEEN '2000-01-01 00:00:00' AND '2000-01-08 00:00:00'; db.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&users)
// Struct //SELECT * FROM users WHERE name = "wxf" AND age = 20 LIMIT 1; db.Where(&User{Name: "wxf", Age: 20}).First(&user) // Map //SELECT * FROM users WHERE name = "wxf" AND age = 20; db.Where(map[string]interface{}{"name": "wxf", "age": 20}).Find(&users) // 主键的切片 //SELECT * FROM users WHERE id IN (20, 21, 22); db.Where([]int64{20, 21, 22}).Find(&users)
当通过结构体查询的时候，GORM只会查询结构体中非零值的字段，
//SELECT * FROM users WHERE name = "wxf"; db.Where(&User{Name: "wxf", Age: 0}).Find(&users)
那如果就想查询零值(0,“”,false)怎么办呢，以使用指针或实现 Scanner/Valuer 接口来解决这个问题
// 使用指针 type User struct { gorm.Model Name string Age *int } // 使用 Scanner/Valuer type User struct { gorm.Model Name string Age sql.NullInt64 // sql.NullInt64 实现了 Scanner/Valuer 接口 }
Not
//SELECT * FROM users WHERE name <> "wxf" LIMIT 1; db.Not("name", "wxf").First(&user) // Not In //SELECT * FROM users WHERE name NOT IN ("wxf", "wxf 2"); db.Not("name", []string{"wxf", "wxf 2"}).Find(&users) // Not In slice of primary keys //SELECT * FROM users WHERE id NOT IN (1,2,3); db.Not([]int64{1,2,3}).First(&user) //SELECT * FROM users; db.Not([]int64{}).First(&user) // Plain SQL //SELECT * FROM users WHERE NOT(name = "wxf"); db.Not("name = ?", "wxf").First(&user) // Struct //SELECT * FROM users WHERE name <> "wxf"; db.Not(User{Name: "wxf"}).First(&user) }
Or
// SELECT * FROM users WHERE role = 'admin' OR role = 'super_admin'; db.Where("role = ?", "admin").Or("role = ?", "super_admin").Find(&users) // Struct // SELECT * FROM users WHERE name = 'wxf' OR name = 'wxf 2'; db.Where("name = 'wxf'").Or(User{Name: "wxf 2"}).Find(&users) // Map // SELECT * FROM users WHERE name = 'wxf' OR name = 'wxf 2'; db.Where("name = 'wxf'").Or(map[string]interface{}{"name": "wxf 2"}).Find(&users)
内联查询
Immediate methods ，立即执行方法：是指那些会立即生成SQL语句并发送到数据库的方法, 他们一般是CRUD方法，比如：
Create, First, Find, Take, Save, UpdateXXX, Delete, Scan, Row, Rows
内联条件：作用与Where查询类似
当内联条件与多个立即执行方法一起使用时, 内联条件不会传递给后面的立即执行方法。
// 根据主键获取记录 (只适用于整形主键) // SELECT * FROM users WHERE id = 23 LIMIT 1; db.First(&user, 23) // Struct // SELECT * FROM users WHERE age = 20; db.Find(&users, User{Age: 20}) // Map // SELECT * FROM users WHERE age = 20; db.Find(&users, map[string]interface{}{"age": 20}) // 根据主键获取记录, 如果它是一个非整形主键 // SELECT * FROM users WHERE id = 'string_primary_key' LIMIT 1; db.First(&user, "id = ?", "string_primary_key") // Plain SQL // SELECT * FROM users WHERE name = "wxf"; db.Find(&user, "name = ?", "wxf") // SELECT * FROM users WHERE name <> "wxf" AND age > 20; db.Find(&users, "name <> ? AND age > ?", "wxf", 20)
额外查询选项
// 为查询 SQL 添加额外的 SQL 操作 // SELECT * FROM users WHERE id = 10 FOR UPDATE; db.Set("gorm:query_option", "FOR UPDATE").First(&user, 10)
子查询
// SELECT * FROM "orders" WHERE "orders"."deleted_at" IS NULL AND (amount > (SELECT AVG(amount) FROM "orders" WHERE (state = 'paid'))); db.Where("amount > ?", db.Table("orders").Select("AVG(amount)").Where("state = ?", "paid").SubQuery()).Find(&orders)
Select选择字段
Select，指定你想从数据库中检索出的字段，默认会选择全部字段。
// SELECT name, age FROM users; db.Select("name, age").Find(&users) // SELECT name, age FROM users; db.Select([]string{"name", "age"}).Find(&users) // SELECT COALESCE(age,'42') FROM users; db.Table("users").Select("COALESCE(age,?)", 42).Rows()
Order排序
Order，指定从数据库中检索出记录的顺序。设置第二个参数 reorder 为 true ，可以覆盖前面定义的排序条件。
// SELECT * FROM users ORDER BY age desc, name; 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; (users1) // SELECT * FROM users ORDER BY age; (users2) db.Order("age desc").Find(&users1).Order("age", true).Find(&users2)
Limit数量
指定从数据库检索出的最大记录数。
// SELECT * FROM users LIMIT 3; db.Limit(3).Find(&users) // -1 取消 Limit 条件 // SELECT * FROM users LIMIT 10; (users1) // SELECT * FROM users; (users2) db.Limit(10).Find(&users1).Limit(-1).Find(&users2)
Offset
Offset，指定开始返回记录前要跳过的记录数。
// SELECT * FROM users OFFSET 3; db.Offset(3).Find(&users) // -1 取消 Offset 条件 // SELECT * FROM users OFFSET 10; (users1) // SELECT * FROM users; (users2) db.Offset(10).Find(&users1).Offset(-1).Find(&users2)
Count
Count，该 model 能获取的记录总数。
// SELECT * from USERS WHERE name = 'wxf' OR name = 'wxf 2'; (users) // SELECT count(*) FROM users WHERE name = 'wxf' OR name = 'wxf 2'; (count) db.Where("name = ?", "wxf").Or("name = ?", "wxf 2").Find(&users).Count(&count) // SELECT count(*) FROM users WHERE name = 'wxf'; (count) db.Model(&User{}).Where("name = ?", "wxf").Count(&count) // SELECT count(*) FROM deleted_users; db.Table("deleted_users").Count(&count) // SELECT count( distinct(name) ) FROM deleted_users; (count) db.Table("deleted_users").Select("count(distinct(name))").Count(&count)
注意 Count 必须是链式查询的最后一个操作 ，因为它会覆盖前面的 SELECT，但如果里面使用了 count 时不会覆盖
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.Scan() ... } // 使用Scan将多条结果扫描进事先准备好的结构体切片中 type Result struct { Date time.Time Total int } var rets []Result db.Table("users").Select("date(created_at) as date, sum(age) as total").Group("date(created_at)").Scan(&rets) 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() { ... }
Joins连接
Joins，指定连接条件
//select users.name, emails.email form user left join emails on emails.user_id = users.id rows, err := db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Rows() for rows.Next() { ... } //select users.name, emails.email form user left join emails on emails.user_id = users.id results := make([]User, 0) 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)
Pluck
Pluck，查询 model 中的一个列作为切片，如果您想要查询多个列，您应该使用 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)
Scan扫描
Scan，扫描结果至一个 struct
var result Result db.Table("users").Select("name, age").Where("name = ?", "Antonio").Scan(&result) var results []Result db.Table("users").Select("name, age").Where("id > ?", 0).Scan(&results) // 原生 SQL db.Raw("SELECT name, age FROM users WHERE name = ?", "Antonio").Scan(&result)
