codecamp

GoFrame 数据查询-常用操作示例

// 获取默认配置的数据库对象(配置名称为"default")
db := g.DB()

in查询

使用字符串、​slice​参数类型。当使用​slice​参数类型时,预处理占位符只需要一个​?​即可。

// SELECT * FROM user WHERE uid IN(100,10000,90000)
db.Model("user").Where("uid IN(?,?,?)", 100, 10000, 90000).All()
db.Model("user").Where("uid", g.Slice{100, 10000, 90000}).All()

// SELECT * FROM user WHERE gender=1 AND uid IN(100,10000,90000)
db.Model("user").Where("gender=? AND uid IN(?)", 1, g.Slice{100, 10000, 90000}).All()

// SELECT COUNT(*) FROM user WHERE age in(18,50)
db.Model("user").Where("age IN(?,?)", 18, 50).Count()
db.Model("user").Where("age", g.Slice{18, 50}).Count()

使用任意​map​参数类型。

// SELECT * FROM user WHERE gender=1 AND uid IN(100,10000,90000)
db.Model("user").Where(g.Map{
    "gender" : 1,
    "uid"    : g.Slice{100,10000,90000},
}).All()

使用​struct​参数类型,注意查询条件的顺序和​struct​的属性定义顺序有关。

type User struct {
    Id     []int  `orm:"uid"`
    Gender int    `orm:"gender"`
}
// SELECT * FROM `user` WHERE uid IN(100,10000,90000) AND gender=1
db.Model("user").Where(User{
    "gender" : 1,
    "uid"    : []int{100, 10000, 90000},
}).All()

为提高易用性,当传递的​slice​参数为空或​nil​时,查询并不会报错,而是转换为一个​false​条件语句。

// SELECT * FROM `user` WHERE 0=1 
db.Model("user").Where("uid", g.Slice{}).All()
db.Model("user").Where("uid", nil).All()

ORM​同时也提供了常用条件方法​WhereIn/WhereNotIn/WhereOrIn/WhereOrNotIn​方法,用于常用的​In​查询条件过滤。方法定义如下:

func (m *Model) WhereIn(column string, in interface{}) *Model
func (m *Model) WhereNotIn(column string, in interface{}) *Model
func (m *Model) WhereOrIn(column string, in interface{}) *Model
func (m *Model) WhereOrNotIn(column string, in interface{}) *Model

使用示例:

// SELECT * FROM `user` WHERE `gender`=1 AND `type` IN(1,2,3)
db.Model("user").Where("gender", 1).WhereIn("type", g.Slice{1,2,3}).All()

// SELECT * FROM `user` WHERE `gender`=1 AND `type` NOT IN(1,2,3)
db.Model("user").Where("gender", 1).WhereNotIn("type", g.Slice{1,2,3}).All()

// SELECT * FROM `user` WHERE `gender`=1 OR `type` IN(1,2,3)
db.Model("user").Where("gender", 1).WhereOrIn("type", g.Slice{1,2,3}).All()

// SELECT * FROM `user` WHERE `gender`=1 OR `type` NOT IN(1,2,3)
db.Model("user").Where("gender", 1).WhereOrNotIn("type", g.Slice{1,2,3}).All()

like 查询

// SELECT * FROM `user` WHERE name like '%john%'
db.Model("user").Where("name like ?", "%john%").All()
// SELECT * FROM `user` WHERE birthday like '1990-%'
db.Model("user").Where("birthday like ?", "1990-%").All()

从​goframe v1.16​版本开始,​goframe​的​ORM​同时也提供了常用条件方法​WhereLike/WhereNotLike/WhereOrLike/WhereOrNotLike​方法,用于常用的​Like​查询条件过滤。方法定义如下:

func (m *Model) WhereLike(column string, like interface{}) *Model
func (m *Model) WhereNotLike(column string, like interface{}) *Model
func (m *Model) WhereOrLike(column string, like interface{}) *Model
func (m *Model) WhereOrNotLike(column string, like interface{}) *Model

使用示例:

// SELECT * FROM `user` WHERE `gender`=1 AND `name` LIKE 'john%'
db.Model("user").Where("gender", 1).WhereLike("name", "john%").All()

// SELECT * FROM `user` WHERE `gender`=1 AND `name` NOT LIKE 'john%'
db.Model("user").Where("gender", 1).WhereNotLike("name", "john%").All()

// SELECT * FROM `user` WHERE `gender`=1 OR `name` LIKE 'john%'
db.Model("user").Where("gender", 1).WhereOrLike("name", "john%").All()

// SELECT * FROM `user` WHERE `gender`=1 OR `name` NOT LIKE 'john%'
db.Model("user").Where("gender", 1).WhereOrNotLike("name", "john%").All()

min/max/avg/sum

我们直接将统计方法使用在​Fields​方法上,例如:

// SELECT MIN(score) FROM `user` WHERE `uid`=1
db.Model("user").Fields("MIN(score)").Where("uid", 1).Value()

// SELECT MAX(score) FROM `user` WHERE `uid`=1
db.Model("user").Fields("MAX(score)").Where("uid", 1).Value()

// SELECT AVG(score) FROM `user` WHERE `uid`=1
db.Model("user").Fields("AVG(score)").Where("uid", 1).Value()

// SELECT SUM(score) FROM `user` WHERE `uid`=1 
db.Model("user").Fields("SUM(score)").Where("uid", 1).Value()

从​goframe v1.16​版本开始,​goframe​的​ORM​同时也提供了常用统计方法​Min/Max/Avg/Sum​方法,用于常用的字段统计查询。方法定义如下:

func (m *Model) Min(column string) (float64, error)
func (m *Model) Max(column string) (float64, error)
func (m *Model) Avg(column string) (float64, error)
func (m *Model) Sum(column string) (float64, error)

上面的示例使用快捷统计方法改造后:

// SELECT MIN(`score`) FROM `user` WHERE `uid`=1
db.Model("user").Where("uid", 1).Min("score")

// SELECT MAX(`score`) FROM `user` WHERE `uid`=1
db.Model("user").Where("uid", 1).Max("score")

// SELECT AVG(`score`) FROM `user` WHERE `uid`=1
db.Model("user").Where("uid", 1).Avg("score")

// SELECT SUM(`score`) FROM `user` WHERE `uid`=1
db.Model("user").Where("uid", 1).Sum("score")

count查询

// SELECT COUNT(1) FROM `user` WHERE `birthday`='1990-10-01'
db.Model("user").Where("birthday", "1990-10-01").Count()
// SELECT COUNT(uid) FROM `user` WHERE `birthday`='1990-10-01'
db.Model("user").Fields("uid").Where("birthday", "1990-10-01").Count()

从​goframe v1.16​版本开始,​goframe​的​ORM​同时也提供了一个按照字段进行​Count​的常用方法​CountColumn​。方法定义如下:

func (m *Model) CountColumn(column string) (int, error)

使用示例:

db.Model("user").Where("birthday", "1990-10-01").CountColumn("uid")

distinct查询

// SELECT DISTINCT uid,name FROM `user`
db.Model("user").Fields("DISTINCT uid,name").All()
// SELECT COUNT(DISTINCT uid,name) FROM `user`
db.Model("user").Fields("DISTINCT uid,name").Count()

从​goframe v1.16​版本开始,​goframe​的​ORM​同时也提供了一个字段唯一性过滤标记方法​Distinct​。方法定义如下:

func (m *Model) Distinct() *Model

使用示例:

// SELECT COUNT(DISTINCT `name`) FROM `user`
db.Model("user").Distinct().CountColumn("name")

// SELECT COUNT(DISTINCT uid,name) FROM `user`
db.Model("user").Distinct().CountColumn("uid,name")

between查询

// SELECT * FROM `user ` WHERE age between 18 and 20
db.Model("user").Where("age between ? and ?", 18, 20).All()

从​goframe v1.16​版本开始,​goframe​的​ORM​同时也提供了常用条件方法​WhereBetween/WhereNotBetween/WhereOrBetween/WhereOrNotBetween​方法,用于常用的​Between​查询条件过滤。方法定义如下:

func (m *Model) WhereBetween(column string, min, max interface{}) *Model
func (m *Model) WhereNotBetween(column string, min, max interface{}) *Model
func (m *Model) WhereOrBetween(column string, min, max interface{}) *Model
func (m *Model) WhereOrNotBetween(column string, min, max interface{}) *Model

使用示例:

// SELECT * FROM `user` WHERE `gender`=0 AND `age` BETWEEN 16 AND 20
db.Model("user").Where("gender", 0).WhereBetween("age", 16, 20).All()

// SELECT * FROM `user` WHERE `gender`=0 AND `age` NOT BETWEEN 16 AND 20
db.Model("user").Where("gender", 0).WhereNotBetween("age", 16, 20).All()

// SELECT * FROM `user` WHERE `gender`=0 OR `age` BETWEEN 16 AND 20
db.Model("user").Where("gender", 0).WhereOrBetween("age", 16, 20).All()

// SELECT * FROM `user` WHERE `gender`=0 OR `age` NOT BETWEEN 16 AND 20
db.Model("user").Where("gender", 0).WhereOrNotBetween("age", 16, 20).All()

null查询

ORM​提供了常用条件方法​WhereNull/WhereNotNull/WhereOrNull/WhereOrNotNull​方法,用于常用的​Null​查询条件过滤。方法定义如下:

func (m *Model) WhereNull(columns ...string) *Model
func (m *Model) WhereNotNull(columns ...string) *Model
func (m *Model) WhereOrNull(columns ...string) *Model
func (m *Model) WhereOrNotNull(columns ...string) *Model

使用示例:

// SELECT * FROM `user` WHERE `created_at` > '2021-05-01 00:00:00' AND `inviter` IS NULL
db.Model("user").Where("created_at>?", gtime.New("2021-05-01")).WhereNull("inviter").All()

// SELECT * FROM `user` WHERE `created_at` > '2021-05-01 00:00:00' AND `inviter` IS NOT NULL
db.Model("user").Where("created_at>?", gtime.New("2021-05-01")).WhereNotNull("inviter").All()

// SELECT * FROM `user` WHERE `created_at` > '2021-05-01 00:00:00' OR `inviter` IS NULL
db.Model("user").Where("created_at>?", gtime.New("2021-05-01")).WhereOrNull("inviter").All()

// SELECT * FROM `user` WHERE `created_at` > '2021-05-01 00:00:00' OR `inviter` IS NOT NULL
db.Model("user").Where("created_at>?", gtime.New("2021-05-01")).WhereOrNotNull("inviter").All()

同时,这几个方法的参数支持多个字段输入,例如:

// SELECT * FROM `user` WHERE `created_at` > '2021-05-01 00:00:00' AND `inviter` IS NULL AND `creator` IS NULL
db.Model("user").Where("created_at>?", gtime.New("2021-05-01")).WhereNull("inviter", "creator").All()


GoFrame 数据查询-子查询特性
GoFrame 模型关联-ScanList
温馨提示
下载编程狮App,免费阅读超1000+编程语言教程
取消
确定
目录

GoFrame 核心组件

GoFrame 核心组件-数据库ORM

GoFrame 模块列表

GoFrame 模块列表-单元测试

GoFrame 模块列表-功能调试

GoFrame WEB服务开发

关闭

MIP.setData({ 'pageTheme' : getCookie('pageTheme') || {'day':true, 'night':false}, 'pageFontSize' : getCookie('pageFontSize') || 20 }); MIP.watch('pageTheme', function(newValue){ setCookie('pageTheme', JSON.stringify(newValue)) }); MIP.watch('pageFontSize', function(newValue){ setCookie('pageFontSize', newValue) }); function setCookie(name, value){ var days = 1; var exp = new Date(); exp.setTime(exp.getTime() + days*24*60*60*1000); document.cookie = name + '=' + value + ';expires=' + exp.toUTCString(); } function getCookie(name){ var reg = new RegExp('(^| )' + name + '=([^;]*)(;|$)'); return document.cookie.match(reg) ? JSON.parse(document.cookie.match(reg)[2]) : null; }