欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

gorm系列-查询

程序员文章站 2022-03-26 20:05:40
[TOC] Gorm查询 一般查询 go package main import ( "database/sql" "fmt" "github.com/jinzhu/gorm" _ "github.com/jinzhu/gorm/dialects/mysql" ) //1. 定义模型 type Us ......

gorm查询

一般查询

// 根据主键查询第一条记录
db.first(&user)
//// select * from users order by id limit 1;

// 随机获取一条记录
db.take(&user)
//// select * from users limit 1;

// 根据主键查询最后一条记录
db.last(&user)
//// select * from users order by id desc limit 1;

// 查询所有的记录
db.find(&users)
//// select * from users;

// 查询指定的某条记录(仅当主键为整型时可用)
db.first(&user, 10)
//// select * from users where id = 10;
package main

import (
	"database/sql"
	"fmt"
	"github.com/jinzhu/gorm"
	_ "github.com/jinzhu/gorm/dialects/mysql"
)

//1. 定义模型
type user struct {
	id int64
	//name *string  `gorm:"default:'zisefeizhu'"`
	name sql.nullstring `gorm:"default:'zisefeizhu'"`
	age byte
}

func main() {
	//2. 连接mysql数据库
	db, err := gorm.open("mysql","root:123456@tcp(127.0.0.1:3306)/db?charset=utf8mb4&parsetime=true&loc=local")
	if err != nil {
		panic(err)
	}
	defer db.close()
	//3. 把模型与数据库中的表对应起来
	db.automigrate(&user{})

	//4. 创建结构体实例
	//u := user{name: sql.nullstring{"", false}, age: 60}
	//fmt.println(db.newrecord(&u)) // 主键为空返回`true`
	//db.debug().create(&u)   // 在数据库中创建一条user记录
	//fmt.println(db.newrecord(u)) // 创建`user`后返回`false`

	//查询
	//var user user   //声明模型结构体变量类型user (文件夹a)
	//db.first(&user)  //(文件夹b)
	//user:main.user{id:1, name:sql.nullstring{string:"zisefeizhu", valid:true}, age:0x1e}
	user := new(user)
	db.first(user)
	fmt.printf("user:%#v\n",user)
	//user:&main.user{id:1, name:sql.nullstring{string:"zisefeizhu", valid:true}, age:0x1e}

	//查询所有记录
	var users []user
	//users := make([]user, 10)
	db.find(&users)
	fmt.printf("user:%#v\n",users)
}

where条件

普通sql查询
/ get first matched record
db.where("name = ?", "jinzhu").first(&user)
// select * from users where name = 'jinzhu' limit 1;

// get all matched records
db.where("name = ?", "jinzhu").find(&users)
// select * from users where name = 'jinzhu';

// <>
db.where("name <> ?", "jinzhu").find(&users)
//select * from users where name <> 'jinzhu';

// in
db.where("name in (?)", []string{"jinzhu", "jinzhu 2"}).find(&users)
// select * from users where name in ('jinzhu','jinzhu 2');

// like
db.where("name like ?", "%jin%").find(&users)
// select * from users where name like '%jin%';

// and
db.where("name = ? and age >= ?", "jinzhu", "22").find(&users)
// select * from users where name = 'jinzhu' and age >= 22;

// time
db.where("updated_at > ?", lastweek).find(&users)
// select * from users where updated_at > '2000-01-01 00:00:00';

// between
db.where("created_at between ? and ?", lastweek, today).find(&users)
// select * from users where created_at between '2000-01-01 00:00:00' and '2000-01-08 00:00:00';
//查询where记录
	var users []user

	db.where("name = ?", "zisefeizhu ").find(&users)
	fmt.printf("user:%#v\n",users)
struch & map 查询
// struct
db.where(&user{name: "jinzhu", age: 20}).first(&user)
// select * from users where name = "jinzhu" and age = 20 limit 1;

// map
db.where(map[string]interface{}{"name": "jinzhu", "age": 20}).find(&users)
// select * from users where name = "jinzhu" and age = 20;

// 主键的切片
db.where([]int64{20, 21, 22}).find(&users)
//select * from users where id in (20, 21, 22);

注意:当通过结构体进行查询时,gorm将会只通过非零值字段查询,这意味着如果你的字段值为0,'',false或者其他零值时,将不会被用于构建查询条件。

db.where(&user{name: "jinzhu", age: 0}).find(&users)
// select * from users where name = "jinzhu";

可以使用指针或实现 scanner/valuer 接口来避免这个问题

// 使用指针
type user struct {
  gorm.model
  name string
  age  *int
}

// 使用 scanner/valuer
type user struct {
  gorm.model
  name string
  age  sql.nullint64  // sql.nullint64 实现了 scanner/valuer 接口
}

not条件

作用与 where 类似的情形

db.not("name", "jinzhu").first(&user)
//// select * from users where name <> "jinzhu" limit 1;

// not in
db.not("name", []string{"jinzhu", "jinzhu 2"}).find(&users)
//// select * from users where name not in ("jinzhu", "jinzhu 2");

// not in slice of primary keys
db.not([]int64{1,2,3}).first(&user)
//// select * from users where id not in (1,2,3);

db.not([]int64{}).first(&user)
//// select * from users;

// plain sql
db.not("name = ?", "jinzhu").first(&user)
//// select * from users where not(name = "jinzhu");

// struct
db.not(user{name: "jinzhu"}).first(&user)
//// select * from users where name <> "jinzhu";

or条件

db.where("role = ?", "admin").or("role = ?", "super_admin").find(&users)
//// select * from users where role = 'admin' or role = 'super_admin';

// struct
db.where("name = 'jinzhu'").or(user{name: "jinzhu 2"}).find(&users)
//// select * from users where name = 'jinzhu' or name = 'jinzhu 2';

// map
db.where("name = 'jinzhu'").or(map[string]interface{}{"name": "jinzhu 2"}).find(&users)
//// select * from users where name = 'jinzhu' or name = 'jinzhu 2';

内联条件

作用与where查询类似,当内联条件与多个立即执行方法一起使用时, 内联条件不会传递给后面的立即执行方法。

// 根据主键获取记录 (只适用于整形主键)
db.first(&user, 23)
//// select * from users where id = 23 limit 1;
// 根据主键获取记录, 如果它是一个非整形主键
db.first(&user, "id = ?", "string_primary_key")
//// select * from users where id = 'string_primary_key' limit 1;

// plain sql
db.find(&user, "name = ?", "jinzhu")
//// select * from users where name = "jinzhu";

db.find(&users, "name <> ? and age > ?", "jinzhu", 20)
//// select * from users where name <> "jinzhu" 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;

额外查询选项

// 为查询 sql 添加额外的 sql 操作
db.set("gorm:query_option", "for update").first(&user, 10)
//// select * from users where id = 10 for update;

firstorinit

获取匹配的第一条记录,否则根据给定的条件初始化一个新的对象 (仅支持 struct 和 map 条件)

//查询firstorinit记录
var user user
db.firstorinit(&user,user{name: "小王子"})
fmt.printf("user:%#v\n",user)
//user:main.user{id:0, name:"小王子", age:0x15}
attrs

如果记录未找到,将使用参数初始化 struct.

// 未找到
db.where(user{name: "non_existing"}).attrs(user{age: 20}).firstorinit(&user)
//// select * from users where name = 'non_existing';
//// user -> user{name: "non_existing", age: 20}

db.where(user{name: "non_existing"}).attrs("age", 20).firstorinit(&user)
//// select * from users where name = 'non_existing';
//// user -> user{name: "non_existing", age: 20}

// 找到
db.where(user{name: "jinzhu"}).attrs(user{age: 30}).firstorinit(&user)
//// select * from users where name = jinzhu';
//// user -> user{id: 111, name: "jinzhu", age: 20}
assign

不管记录是否找到,都将参数赋值给 struct.

// 未找到
db.where(user{name: "non_existing"}).assign(user{age: 20}).firstorinit(&user)
//// user -> user{name: "non_existing", age: 20}

// 找到
db.where(user{name: "jinzhu"}).assign(user{age: 30}).firstorinit(&user)
// select * from users where name = jinzhu';
// user -> user{id: 111, name: "jinzhu", age: 30}

firstorcreate

获取匹配的第一条记录, 否则根据给定的条件创建一个新的记录 (仅支持 struct 和 map 条件)

// 未找到
db.firstorcreate(&user, user{name: "non_existing"})
//// insert into "users" (name) values ("non_existing");
//// user -> user{id: 112, name: "non_existing"}

// 找到
db.where(user{name: "jinzhu"}).firstorcreate(&user)
//// user -> user{id: 111, name: "jinzhu"}
attrs

如果记录未找到,将使用参数创建 struct 和记录.

 // 未找到
db.where(user{name: "non_existing"}).attrs(user{age: 20}).firstorcreate(&user)
//// select * from users where name = 'non_existing';
//// insert into "users" (name, age) values ("non_existing", 20);
//// user -> user{id: 112, name: "non_existing", age: 20}

// 找到
db.where(user{name: "jinzhu"}).attrs(user{age: 30}).firstorcreate(&user)
//// select * from users where name = 'jinzhu';
//// user -> user{id: 111, name: "jinzhu", age: 20}
assign

不管记录是否找到,都将参数赋值给 struct 并保存至数据库.

// 未找到
db.where(user{name: "non_existing"}).assign(user{age: 20}).firstorcreate(&user)
//// select * from users where name = 'non_existing';
//// insert into "users" (name, age) values ("non_existing", 20);
//// user -> user{id: 112, name: "non_existing", age: 20}

// 找到
db.where(user{name: "jinzhu"}).assign(user{age: 30}).firstorcreate(&user)
//// select * from users where name = 'jinzhu';
//// update users set age=30 where id = 111;
//// user -> user{id: 111, name: "jinzhu", age: 30}

高级查询

子查询

基于 *gorm.expr 的子查询

db.where("amount > ?", db.table("orders").select("avg(amount)").where("state = ?", "paid").subquery()).find(&orders)
// select * from "orders"  where "orders"."deleted_at" is null and (amount > (select avg(amount) from "orders"  where (state = 'paid')));
选择字段

select,指定你想从数据库中检索出的字段,默认会选择全部字段。

db.select("name, age").find(&users)
//// select name, age from users;

db.select([]string{"name", "age"}).find(&users)
//// select name, age from users;

db.table("users").select("coalesce(age,?)", 42).rows()
//// select coalesce(age,'42') from users;
排序

order,指定从数据库中检索出记录的顺序。设置第二个参数 reorder 为 true ,可以覆盖前面定义的排序条件。

db.order("age desc, name").find(&users)
//// select * from users order by age desc, name;

// 多字段排序
db.order("age desc").order("name").find(&users)
//// select * from users order by age desc, name;

// 覆盖排序
db.order("age desc").find(&users1).order("age", true).find(&users2)
//// select * from users order by age desc; (users1)
//// select * from users order by age; (users2)
数量

limit,指定从数据库检索出的最大记录数。

db.limit(3).find(&users)
//// select * from users limit 3;

// -1 取消 limit 条件
db.limit(10).find(&users1).limit(-1).find(&users2)
//// select * from users limit 10; (users1)
//// select * from users; (users2)
偏移

offset,指定开始返回记录前要跳过的记录数。

db.offset(3).find(&users)
//// select * from users offset 3;

// -1 取消 offset 条件
db.offset(10).find(&users1).offset(-1).find(&users2)
//// select * from users offset 10; (users1)
//// select * from users; (users2)
总数

count,该 model 能获取的记录总数

db.where("name = ?", "jinzhu").or("name = ?", "jinzhu 2").find(&users).count(&count)
//// select * from users where name = 'jinzhu' or name = 'jinzhu 2'; (users)
//// select count(*) from users where name = 'jinzhu' or name = 'jinzhu 2'; (count)

db.model(&user{}).where("name = ?", "jinzhu").count(&count)
//// select count(*) from users where name = 'jinzhu'; (count)

db.table("deleted_users").count(&count)
//// select count(*) from deleted_users;

db.table("deleted_users").select("count(distinct(name))").count(&count)
//// select count( distinct(name) ) from deleted_users; (count)

注意:count 必须是链式查询的最后一个操作 ,因为它会覆盖前面的 select,但如果里面使用了 count 时不会覆盖

group & having
rows, err := db.table("orders").select("date(created_at) as date, sum(amount) as total").group("date(created_at)").rows()
for rows.next() {
  ...
}

// 使用scan将多条结果扫描进事先准备好的结构体切片中
type result struct {
	date time.time
	total int
}
var rets []result
db.table("users").select("date(created_at) as date, sum(age) as total").group("date(created_at)").scan(&rets)

rows, err := db.table("orders").select("date(created_at) as date, sum(amount) as total").group("date(created_at)").having("sum(amount) > ?", 100).rows()
for rows.next() {
  ...
}

type result struct {
  date  time.time
  total int64
}
db.table("orders").select("date(created_at) as date, sum(amount) as total").group("date(created_at)").having("sum(amount) > ?", 100).scan(&results)
连接

joins,指定连接条件

rows, err := db.table("users").select("users.name, emails.email").joins("left join emails on emails.user_id = users.id").rows()
for rows.next() {
  ...
}

db.table("users").select("users.name, emails.email").joins("left join emails on emails.user_id = users.id").scan(&results)

// 多连接及参数
db.joins("join emails on emails.user_id = users.id and emails.email = ?", "jinzhu@example.org").joins("join credit_cards on credit_cards.user_id = users.id").where("credit_cards.number = ?", "411111111111").find(&user)

pluck

pluck,查询 model 中的一个列作为切片,如果您想要查询多个列,您应该使用 scan

var ages []int64
db.find(&users).pluck("age", &ages)

var names []string
db.model(&user{}).pluck("name", &names)

db.table("deleted_users").pluck("name", &names)

// 想查询多个字段? 这样做:
db.select("name, age").find(&users)

扫描

scan,扫描结果至一个 struct.

type result struct {
  name string
  age  int
}

var result result
db.table("users").select("name, age").where("name = ?", "antonio").scan(&result)

var results []result
db.table("users").select("name, age").where("id > ?", 0).scan(&results)

// 原生 sql
db.raw("select name, age from users where name = ?", "antonio").scan(&result)

链式操作相关

链式操作

method chaining,gorm 实现了链式操作接口,所以你可以把代码写成这样:

// 创建一个查询
tx := db.where("name = ?", "jinzhu")

// 添加更多条件
if somecondition {
  tx = tx.where("age = ?", 20)
} else {
  tx = tx.where("age = ?", 30)
}

if yetanothercondition {
  tx = tx.where("active = ?", 1)
}

在调用立即执行方法前不会生成query语句,借助这个特性你可以创建一个函数来处理一些通用逻辑。

立即执行方法

immediate methods ,立即执行方法是指那些会立即生成sql语句并发送到数据库的方法, 他们一般是crud方法,比如:

create, first, find, take, save, updatexxx, delete, scan, row, rows…
这有一个基于上面链式方法代码的立即执行方法的例子

tx.find(&user)

生成的sql语句如下:

select * from users where name = 'jinzhu' and age = 30 and active = 1;

范围

scopes,scope是建立在链式操作的基础之上的。
基于它,你可以抽取一些通用逻辑,写出更多可重用的函数库。

func amountgreaterthan1000(db *gorm.db) *gorm.db {
  return db.where("amount > ?", 1000)
}

func paidwithcreditcard(db *gorm.db) *gorm.db {
  return db.where("pay_mode_sign = ?", "c")
}

func paidwithcod(db *gorm.db) *gorm.db {
  return db.where("pay_mode_sign = ?", "c")
}

func orderstatus(status []string) func (db *gorm.db) *gorm.db {
  return func (db *gorm.db) *gorm.db {
    return db.scopes(amountgreaterthan1000).where("status in (?)", status)
  }
}

db.scopes(amountgreaterthan1000, paidwithcreditcard).find(&orders)
// 查找所有金额大于 1000 的信用卡订单

db.scopes(amountgreaterthan1000, paidwithcod).find(&orders)
// 查找所有金额大于 1000 的 cod 订单

db.scopes(amountgreaterthan1000, orderstatus([]string{"paid", "shipped"})).find(&orders)
// 查找所有金额大于 1000 且已付款或者已发货的订单

多个立即执行方法

multiple immediate methods,在 gorm 中使用多个立即执行方法时,后一个立即执行方法会复用前一个立即执行方法的条件 (不包括内联条件) 。

db.where("name like ?", "jinzhu%").find(&users, "id in (?)", []int{1, 2, 3}).count(&count)

生成的 sql

select * from users where name like 'jinzhu%' and id in (1, 2, 3)

select count(*) from users where name like 'jinzhu%'