526互联

11-gorm-v2-03-基本查询

发布时间 2023-06-28 13:53:32作者: 运维开发玄德公

@

目录

1. 简单示例

从一个示例开始说明

  • 以有表格如下
mysql> select * from xi_shu;
+----+------------+------+
| id | name       | age  |
+----+------------+------+
|  1 | LiuBei     |   28 |
|  2 | GuanYu     |   22 |
|  3 | ZhangFei   |   20 |
+----+------------+------+
6 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
}



func main() {
	db,sqlDB,_ := connect()
	defer sqlDB.Close()
	var users []User

	db.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
[{1 28 LiuBei} {2 22 GuanYu} {3 20 ZhangFei}]

2. 基本查询

2.1 获取第一条记录，按主键排序

db.First(&user)
//// SELECT * FROM users ORDER BY id LIMIT 1;

2.2 获取一条记录，不指定排序

db.Take(&user)
//// SELECT * FROM users LIMIT 1;

2.3 获取最后一条记录，按主键排序

db.Last(&user)
//// SELECT * FROM users ORDER BY id DESC LIMIT 1;

2.4 获取所有的记录

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

2.5 通过主键进行查询 (仅适用于主键是数字类型)

db.First(&user, 2)
//// SELECT * FROM users WHERE id = 10;

2.6 查询指定字段

db.Select("name,age").Find(&users)
  • 示例
func main() {
	db,_ := connect()
	defer db.Close()
	var users []xiShu
	db.Select("name,age").Find(&users)
	fmt.Println(user)
}
  • 示例（利用切片）
db.Select([]string{"name", "age"}).Find(&users)

3. where

3.1 原生sql

3.1.1 简单使用

  • 语法
db.Where("name = ?", "LiuBei").First(&users)
//// SELECT * FROM users WHERE name = 'LiuBei' limit 1;
  • 示例1
    "1. 简单示例"中 main函数替换如下：
func main() {
	db,sqlDB,_ := connect()
	defer sqlDB.Close()
	var users []User

	db.Where("name = ?", "LiuBei").First(&users)
	fmt.Println(users)

	db.Where("name = ?", "GuanYu").First(&users)
	fmt.Println(users)
}

查询结果：

OK
&[{1 LiuBei 28}]
&[{2 GuanYu 22}]
  • 示例2
    如果连续查询用结构体收数据
func main() {
	db,sqlDB,_ := connect()
	defer sqlDB.Close()
	var user  User

	db.Where("name = ?", "LiuBei").First(&user)
	fmt.Println(user)

	db.Where("name = ?", "GuanYu").First(&user)
	fmt.Println(user)
}

输出为

OK
{1 28 LiuBei}

2022/02/11 23:54:54 /data/goproject/src/crow-gorm/main.go:29 record not found
[0.796ms] [rows:0] SELECT * FROM `users` WHERE name = 'GuanYu' AND `users`.`id` = 1 ORDER BY `users`.`id` LIMIT 1
{1 28 LiuBei}

报错可见"WHERE name = 'GuanYu' AND `users`.`id` = 1 "，说明第一次查到的结果会带到第二次查询

试了一下，如果第二次查询之前清除结构体数据，则可以第二次查询

func main() {
	db,sqlDB,_ := connect()
	defer sqlDB.Close()
	var user  User

	db.Where("name = ?", "LiuBei").First(&user)
	fmt.Println(user)

	user = User{} //清除user

	db.Where("name = ?", "GuanYu").First(&user)
	fmt.Println(user)
}

3.1.2 <> （不为）

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

3.1.3 IN

db.Where("name in (?)", []string{"CaoCao","LiuBei","SunQuan"}).Find(&users)

3.1.4 LIKE

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

3.1.5 AND

db.Where("name = ? AND Age > ? ", "LiuBei",20).Find(&users)

3.1.6 BETWEEN

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

3.1.7 Time

db.Where("updated_at < ?", currentTime).Find(&users)

3.2 通过go的对象查询

3.2.1 通过Struct查询

  • 语法
db.Where(STRUCT_NAME).First(&users)
  • 示例
func main() {
	db,sqlDB,_ := connect()
	defer sqlDB.Close()

	user := User {
		Name: "GuanYu",
		Age: 22,
	}
	db.Where(user).First(&user)
	fmt.Println(user)
}

3.2.2 通过Map查询

db.Where(map[string]interface{}{"name": "GuanYu", "age": 22}).Find(&users)

3.2.3 注意问题

  • 值为0、''、false 该字段不会被用于构建查询条件
func main() {
	db,_ := connect()
	defer db.Close()
	var users []User

	result := db.Where(map[string]interface{}{"name": 0, "age": 20}).Find(&users)
	fmt.Println(users)
}

结果

OK
[{3 ZhangFei 20} {5 ZhuGeLiang 20} {6 MaChao 20}]

可见，name的值为0，ID为空也不会，都不写入sql语句。

4. Not

和WHERE类似，不展开讲述了

  • 获取过滤后第一条记录，按主键排序
db.Not("name", "LiuBei").First(&user)
//// SELECT * FROM users WHERE name <> "LiuBei" LIMIT 1;
  • 用数组/切片过滤
db.Not("name", []string{"CaoCao", "LiuBei","SunQuan"}).Find(&users)
//// SELECT * FROM users WHERE name NOT IN ("CaoCao", "LiuBei","SunQuan");
  • 不在主键 slice 中
db.Not([]int64{1,2,3}).First(&user)
//// SELECT * FROM users WHERE id NOT IN (1,2,3);

示例

func main() {
	db,_ := connect()
	defer db.Close()
	var users []User

	result := db.Not([]int64{1,2,3}).Find(&users)
	fmt.Println(users)
}

结果

[{4 ZhaoYun 18} {5 ZhuGeLiang 20} {6 MaChao 20}]

如上可知，ID是1.2.3的被过滤

  • 原生 SQL
db.Not("name = ?", "LiuBei").First(&user)
//// SELECT * FROM users WHERE NOT(name = "LiuBei");
  • Struct/Map
db.Not(map[string]interface{}{"name": "LiuBei", "age": 20}).Find(&users)
//// SELECT * FROM users WHERE name <> "LiuBei";

5. Or

连接两个条件，两边可以是不同写法，如一边是原始sql一边是结构体:

db.Where("name = ?", "LiuBei").Or(xiShu{Name: "GuanYu"}).Find(&users)

6. 行内条件查询

  • 非数字型主键查询
db.First(&user, 2)
//// SELECT * FROM users WHERE id = 23 LIMIT 1;

示例

func main() {
	db,_ := connect()
	defer db.Close()
	var users []User
	result := db.First(&users, 2)
	fmt.Println(users)
}
  • 主键查询（数字/非数字型）
db.First(&user, "id = ?", "xxxx")
//// SELECT * FROM users WHERE id = 'xxxxx' LIMIT 1;
  • 原生 SQL
db.Find(&users, "name = ?", "LiuBei")
//// SELECT * FROM users WHERE name = "LiuBei";

db.Find(&users, "name <> ? AND age > ?", "LiuBei", 20)
//// SELECT * FROM users WHERE name <> "LiuBei" 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;