10-gorm-03-基本查询
文章目录
1. 简单示例
从一个示例开始说明
- 以有表格如下
mysql> select * from xi_shu;
+----+------------+------+
| id | name | age |
+----+------------+------+
| 1 | LiuBei | 28 |
| 2 | GuanYu | 22 |
| 3 | ZhangFei | 20 |
| 4 | ZhaoYun | 18 |
| 5 | ZhuGeLiang | 20 |
| 6 | MaChao | 20 |
+----+------------+------+
6 rows in set (0.00 sec)
- 代码
package main
import (
"fmt"
"github.com/jinzhu/gorm"
_ "github.com/jinzhu/gorm/dialects/mysql"
)
type xiShu struct {
ID int64
Name string
Age int64
}
func(xiShu) TableName() string {
return "xi_shu"
}
func main() {
db,_ := connect()
defer db.Close()
// 获取第一条记录，按主键排序
var user xiShu
result := db.First(&user)
fmt.Println(result.Value)
}
func connect() (db *gorm.DB,err error) {
db, err = gorm.Open("mysql", "root:40010355@tcp(127.0.0.1:3306)/crow?charset=utf8&parseTime=True&loc=Local")
if err != nil {
fmt.Printf(err.Error())
defer db.Close()
}else {
fmt.Printf("OK\n")
db.DB().SetMaxIdleConns(10)
db.DB().SetMaxOpenConns(100)
//defer db.Close()
}
return
}
- 输出结果如下
OK
&{1 LiuBei 28}
2. 基本查询
2.1 获取第一条记录，按主键排序
db.First(&user)
SELECT * FROM users ORDER BY id LIMIT 1;
- 示例：
见"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;
- 示例
"1. 简单示例"中 main函数替换如下：
func main() {
db,_ := connect()
defer db.Close()
var users []xiShu
result := db.Find(&users)
fmt.Println(result.Value)
}
结果
OK
&[{1 LiuBei 28} {2 GuanYu 22} {3 ZhangFei 20} {4 ZhaoYun 18} {5 ZhuGeLiang 20} {6 MaChao 20}]
2.5 通过主键进行查询 (仅适用于主键是数字类型)
db.First(&user, 10)
SELECT * FROM users WHERE id = 10;
2.6 查询指定字段
db.Select("name,age").Find(&users)
- 示例
func main() {
db,_ := connect()
defer db.Close()
var users []xiShu
//var user xiShu
result := db.Select("name,age").Find(&users)
fmt.Println(result.Value)
}
- 示例（利用切片）
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. 简单示例"中 main函数替换如下：
func main() {
db,_ := connect()
defer db.Close()
var users []xiShu
result01 := db.Where("name = ?", "LiuBei").First(&users)
fmt.Println(result01.Value)
result02 := db.Where("age = ?", 20).First(&users)
fmt.Println(result02.Value)
}
注意：连续两条查询，需要用结构体的切片来接收数据。而不可以用结构体来接收，结构体我测试第二次查询不会重写，而是报错了。
查询结果：
OK
&[{1 LiuBei 28}]
&[{3 ZhangFei 20}]
3.1.2 获取所有匹配
- 语法
db.Where("age = ?", 20).Find(&users)
SELECT * FROM users WHERE age = 20;
func main() {
db,_ := connect()
defer db.Close()
var users []xiShu
result := db.Where("age = ?", 20).Find(&users)
fmt.Println(result.Value)
}
结果
OK
&[{3 ZhangFei 20} {5 ZhuGeLiang 20} {6 MaChao 20}]
3.1.3 <> （不为）
db.Where("name <> ?", "LiuBei").Find(&users)
3.1.4 IN
db.Where("name in (?)", []string{"CaoCao","LiuBei","SunQuan"}).Find(&users)
3.1.5 LIKE
db.Where("name LIKE ?", "%Liu%").Find(&users)
3.1.6 AND
db.Where("name = ? AND Age > ? ", "LiuBei",20).Find(&users)
3.1.7 BETWEEN
db.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&users)
3.1.8 Time
db.Where("updated_at < ?", currentTime).Find(&users)
3.2 通过go的对象查询
3.2.1 通过Struct查询
- 语法
db.Where(STRUCT_NAME).First(&users)
- 示例
func main() {
db,_ := connect()
defer db.Close()
var users []xiShu
whoAmI := xiShu{
Name: "GuanYu",
Age: 22,
}
result := db.Where(whoAmI).First(&users)
fmt.Println(result.Value)
}
3.2.2 通过Map查询
db.Where(map[string]interface{}{"name": "GuanYu", "age": 22}).Find(&users)
- 示例
func main() {
db,_ := connect()
defer db.Close()
var users []xiShu
result := db.Where(map[string]interface{}{"name": "GuanYu", "age": 22}).Find(&users)
fmt.Println(result.Value)
}
3.2.3 注意问题
- 值0或者nil 不会 出现在查询中
func main() {
db,_ := connect()
defer db.Close()
var users []xiShu
result := db.Where(map[string]interface{}{"name": 0, "age": 20}).Find(&users)
fmt.Println(result.Value)
}
结果
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 []xiShu
result := db.Not([]int64{1,2,3}).Find(&users)
fmt.Println(result.Value)
}
结果
&[{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 []xiShu
//var user xiShu
result := db.First(&users, 2)
fmt.Println(result.Value)
}
- 主键查询((数字/非数字型)
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;
