04-高级查询
目录
1. 用Struct或Map接收数据
1.1 Find到Struct
定义一个小的结构体来接收表中的查询结果
db.Model(User{}).Find(&UserModels)
- 完整示例
已有数据表
mysql> select * from users;
+----+----------+------+--------------------+
| id | name | age | email |
+----+----------+------+--------------------+
| 1 | LiuBei | 28 | liubei@xishu.com |
| 2 | GuanYu | 22 | guanyu@xishu.com |
| 3 | ZhangFei | 20 | zhangfei@xishu.com |
+----+----------+------+--------------------+
3 rows in set (0.00 sec)
代码
package main
import (
"database/sql"
"fmt"
"gorm.io/driver/mysql"
"gorm.io/gorm"
"time"
)
type User struct {
ID int64
Age int64
Name string
Email string
}
type userModel struct {
ID int64
Name string
}
func main() {
db,sqlDB,_ := connect()
defer sqlDB.Close()
var UserModels []userModel
db.Model(User{}).Find(&UserModels)
fmt.Println(UserModels)
}
func connect() (db *gorm.DB,sqlDB *sql.DB,err error) {
dsn := "root:40010355@tcp(127.0.0.1:3306)/crow?charset=utf8&parseTime=True&loc=Local"
db, err = gorm.Open(mysql.Open(dsn), &gorm.Config{})
sqlDB,_ = db.DB()
if err != nil {
fmt.Printf(err.Error())
defer sqlDB.Close()
}else {
fmt.Printf("OK\n")
sqlDB.SetMaxIdleConns(10)
sqlDB.SetMaxOpenConns(100)
sqlDB.SetConnMaxLifetime(time.Hour)
}
return
}
结果输出
OK
[{1 LiuBei} {2 GuanYu} {3 ZhangFei}]
1.2 Find到Map
定义一个Map来接收数据
func main() {
db,sqlDB,_ := connect()
defer sqlDB.Close()
var users map[string]interface{}
db.Table("users").Find(&users)
fmt.Println(users)
}
2. 子查询
db.Where("age > (?)", db.Table("users").Select("AVG(age)")).Find(&users)
// SELECT * FROM "users" WHERE age > (SELECT AVG(age) FROM "users");
- 示例
func main() {
db,sqlDB,_ := connect()
defer sqlDB.Close()
var users []User
db.Where("age > (?)", db.Table("users").Select("AVG(age)")).Find(&users)
fmt.Println(users)
}
- 查询结果
OK
[{1 28 LiuBei liubei@xishu.com}]
3. Group/Having
3.1 Group
求和、平均值等情况的分组
db.Table("users").Select("AVG(age) as avg_age","company").Group("company").Find(&users)
完整示例
求下表中各公司的平均年龄
- 数据表
mysql> select * from users;
+----+----------+------+--------------------+---------+
| id | name | age | email | company |
+----+----------+------+--------------------+---------+
| 1 | LiuBei | 28 | liubei@xishu.com | shu |
| 2 | GuanYu | 22 | guanyu@xishu.com | shu |
| 3 | ZhangFei | 20 | zhangfei@xishu.com | shu |
| 4 | SunQuan | 22 | sunquan@dongwu.com | wu |
| 5 | ZhouYu | 15 | zhouyu@dongwu.com | wu |
+----+----------+------+--------------------+---------+
5 rows in set (0.00 sec)
- 代码
package main
import (
"database/sql"
"fmt"
"gorm.io/driver/mysql"
"gorm.io/gorm"
"time"
)
type User struct {
ID int64
Age int64
Name string
Email string
Company string
}
type groupBy struct {
AvgAge float64
Company string
}
func main() {
db,sqlDB,_ := connect()
defer sqlDB.Close()
var users []groupBy
db.Table("users").Select("AVG(age) as avg_age","company").Group("company").Find(&users)
fmt.Println(users)
}
func connect() (db *gorm.DB,sqlDB *sql.DB,err error) {
dsn := "root:40010355@tcp(127.0.0.1:3306)/crow?charset=utf8&parseTime=True&loc=Local"
db, err = gorm.Open(mysql.Open(dsn), &gorm.Config{})
sqlDB,_ = db.DB()
if err != nil {
fmt.Printf(err.Error())
defer sqlDB.Close()
}else {
fmt.Printf("OK\n")
sqlDB.SetMaxIdleConns(10)
sqlDB.SetMaxOpenConns(100)
sqlDB.SetConnMaxLifetime(time.Hour)
}
return
}
- 输出结果
OK
[{23.3333 shu} {18.5 wu}]
3.2 Having
对Group的结果进行过滤。上例中得到的结果，用Having得到平均值大于20的结果。
db.Table("users").Select("AVG(age) as avg_age","company").Group("company").Having("avg_age > ?",20).Find(&users)
4. 变量
4.1 使用sql.Named定义
db.Where("age > @age AND company = @company", sql.Named("company", "shu"),sql.Named("age", 20)).Find(&users)
4.2 使用map定义变量
db.Where("age > @age AND company = @company",map[string]interface{}{"company": "shu","age":20}).First(&users)
- 示例
func main() {
db,sqlDB,_ := connect()
defer sqlDB.Close()
var users []User
checkUser := map[string]interface{}{
"company":"shu",
"age":20,
}
db.Where("age > @age AND company = @company",checkUser).First(&users)
fmt.Println(users)
}
5. 用Rows() 迭代
-
使用
用Rows() 记录数据 ----> rows.Next()逐行读出-----> 用ScanRows赋值给结构体 -
示例
func main() {
db,sqlDB,_ := connect()
defer sqlDB.Close()
rows, _ := db.Model(&User{}).Where("company = ?", "shu").Rows()
defer rows.Close()
for rows.Next() {
var user User
// ScanRows 方法用于将一行记录扫描至结构体
db.ScanRows(rows, &user)
fmt.Println(user)
}
}
6. 查钩子
写一个查询钩子，当查询结果字段
Company为空时，替换为"群"
func (u *User) AfterFind(tx *gorm.DB) (err error) {
if u.Company == "" {
u.Company = "qun"
}
return
}
7. Pluck（单列查询）
- 作用
查询单列数据，并用一个切片接收 - 样例
db.Model(&User{}).Pluck("name", &names)
- 代码
func main() {
db,sqlDB,_ := connect()
defer sqlDB.Close()
var names []string
db.Model(&User{}).Pluck("name", &names)
fmt.Println(names)
}
- 输出
[LiuBei GuanYu ZhangFei YuJi ZhaoYun]
8. Scopes（调用查询函数）
8.1 使用
- 作用
我们可以使用Scopes调用事先创建好的函数。 - 样例
db.Scopes(函数1,函数2).Find(&users)
8.2 示例
- 创建函数
创建一个函数用来查询年龄大约20岁的用户数据
func ageGT20db (db *gorm.DB) *gorm.DB {
return db.Where("age > ?",20)
}
- 调用函数
func main() {
db,sqlDB,_ := connect()
defer sqlDB.Close()
var users []User
db.Scopes(ageGT20db).Find(&users)
fmt.Println(users)
}
9. Count
-
作用
统计查到了多少条数据 -
代码
func main() {
db,sqlDB,_ := connect()
defer sqlDB.Close()
var count int64
var users []User
db.Find(&users).Count(&count)
fmt.Println(count)
}
