03-基本查询
@
目录
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;
