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;