JQL概述
JQL，全称 javascript query language，是一种js方式操作数据库的规范。
使用场景
客户端clientDB，包括js内以及unicloud-db组件内
HBuilderX JQL数据库管理器
启用了jql扩展的云函数
不同场景的区别
- JQL数据库管理器：
○ 不会校验任何权限，相当于以数据库管理员的身份执行
○ 即使是admin不能读写的password类型数据也可以读写
○ 不会触发数据库触发器
○ 不可以执行action云函数 - 客户端clientDB：
○ 完整的权限校验，执行操作的用户不可以操作自己权限之外的数据
○ admin用户不可操作password类型的数据 - 云函数JQL：
○ 同clientDB，但是password类型的数据可以配置权限，默认权限是false，可以被admin用户操作。
○ 可以通过setUser指定当前执行数据库操作的用户身份。
JQL包含的模块
JQL的限制
○ 会对数据库操作进行序列化，除Date类型、RegExp之外的所有不可JSON序列化的参数类型均不支持（例如：undefined）
○ 为了严格控制权限，禁止使用set方法
○ 为了数据校验能严格限制，更新数据库时不可使用更新操作符db.command.inc等
○ 更新数据时键值不可使用{'a.b.c': 1}的形式，需要写成{a:{b:{c:1}}}形式
客户端调用
const db = uniCloud.database()
// 使用`jql`查询list表内`name`字段值为`hello-uni-app`的记录
db.collection('list')
.where('name == "hello-uni-app"')
.get()
.then((res)=>{
// res 为数据库查询结果
}).catch((err)=>{
// err.message 错误信息
// err.code 错误码
})
JQL方法使用限制
下面这些方法必须严格按照下面的顺序进行调用，其他方法需要在这些方法之后调用（不限制顺序）
单表查询：collection aggregate geoNear doc where field groupBy groupField
联表查询
临时表：collection geoNear where field orderBy skip limit getTemp
虚拟联表：collection foreignKey where field groupBy groupField distinct orderBy skip limit get
新增数据记录
方法：collection.add(data) 其中data类型object|array
data中不需要包括_id字段，数据库会自动维护该字段。
const db = uniCloud.database();
const collection = db.collection('user');
//单条记录
db.collection('user').add({name:"王五"})
//多条记录
let res = await collection.add([{
name: '张三'
},{
name: '李四'
},{
name: '王五'
}])
删除数据记录
通过指定文档id删除：collection.doc(_id).remove()
比如：
const db = uniCloud.database();
await db.collection("table1").doc("5f79fdb337d16d0001899566").remove()
条件查询删除
collection.where().remove()
// 删除字段a的值大于2的文档
try {
await db.collection("table1").where("a>2").remove()
} catch (e) {
uni.showModal({
title: '提示',
content: e.message
})
}
删除所有的数据
const dbCmd = db.command
const db = uniCloud.database();
await db.collection("table1").where({
_id: dbCmd.neq(null)
}).remove()
更新数据记录
通过指定文档id更新：collection.doc(_id).update(Object data)
条件查询更新
const db = uniCloud.database();
let collection = db.collection("table1")
let res = await collection.where({_id:'doc-id'})
.update({
name: "Hey",
count: {
fav: 1
}
});
内置云端环境变量
在字符串内使用
db.collection('user').where('_id==$cloudEnv_uid').get()
在对象内使用
db.collection('user').where({ _id: db.getCloudEnv('$cloudEnv_uid') }).get()
使用正则查询
const res = await db.collection('goods').where(`${new RegExp(searchVal, 'i')}.test(name)`).get()
联表查询
临时表：getTemp方法返回的结果，例：const article = db.collection('article').getTemp()，此处 article 就是一个临时表
虚拟联表：主表与副表联表产生的表，例：db.collection(article, 'comment').get()
JQL联表查询有以下两种写法：
// 直接关联多个表为虚拟联表再进行查询，旧写法，目前更推荐使用getTemp进行联表查询
const res = await db.collection('order,book').where('_id=="1"').get()// 直接关联order和book之后再过滤
// 使用getTemp先过滤处理获取临时表再联表查询，推荐用法
const order = db.collection('order').where('_id=="1"').getTemp() // 注意结尾的方法是getTemp，对order表过滤得到临时表
const res = await db.collection(order, 'book').get() // 将获取的order表的临时表和book表进行联表查询
表关联示例：定义order表和book表 schema
// order表schema
{
"bsonType": "object",
"required": [],
"permission": {
"read": true
},
"properties": {
"book_id": {
"bsonType": "string",
"foreignKey": "book._id" // 使用foreignKey表示，此字段关联book表的_id。
},
"quantity": {
"bsonType": "int"
}
}
}
// book表schema
{
"bsonType": "object",
"required": [],
"permission": {
"read": true
},
"properties": {
"title": {
"bsonType": "string"
},
"author": {
"bsonType": "string"
}
}
}
定义json数据
//book表
{
"_id": "1",
"title": "西游记",
"author": "吴承恩"
}
{
"_id": "2",
"title": "水浒传",
"author": "施耐庵"
}
{
"_id": "3",
"title": "三国演义",
"author": "罗贯中"
}
{
"_id": "4",
"title": "红楼梦",
"author": "曹雪芹"
}
//order表
{
"book_id": "1",
"quantity": 111
}
{
"book_id": "2",
"quantity": 222
}
{
"book_id": "3",
"quantity": 333
}
{
"book_id": "4",
"quantity": 444
}
{
"book_id": "3",
"quantity": 555
}
// 客户端联表查询
const db = uniCloud.database()
const order = db.collection('order').field('book_id,quantity').getTemp() // 临时表field方法内需要包含关联字段，否则无法建立关联关系
const book = db.collection('book').field('_id,title,author').getTemp() // 临时表field方法内需要包含关联字段，否则无法建立关联关系
db.collection(order, book) // 注意collection方法内需要传入所有用到的表名，用逗号分隔，主表需要放在第一位
.where('book_id.title == "三国演义"') // 查询order表内书名为“三国演义”的订单
.get()
.then(res => {
console.log(res);
}).catch(err => {
console.error(err)
})
查询结果如下：
{
"code": "",
"message": "",
"data": [{
"_id": "b8df3bd65f8f0d06018fdc250a5688bb",
"book_id": [{
"_id": "3",
"author": "罗贯中",
"title": "三国演义"
}],
"quantity": 555
}, {
"_id": "b8df3bd65f8f0d06018fdc2315af05ec",
"book_id": [{
"_id": "3",
"author": "罗贯中",
"title": "三国演义"
}],
"quantity": 333
}]
}
存在外键时结果返回的区别
主表某字段foreignKey指向副表时，返回结果格式为：
{
"主表字段名1": "xxx",
"主表字段名2": "xxx",
"主表内foreignKey指向副表的字段名": [{
"副表字段名1": "xxx",
"副表字段名2": "xxx",
}]
}
当副表某字段foreignKey指向主表时，其返回结果格式为：
{
"主表字段名1": "xxx",
"主表字段名2": "xxx",
"主表内被副表foreignKey指向的字段名": {
"副表1表名": [{ // 一个主表字段可能对应多个副表字段的foreignKey
"副表1字段名1": "xxx",
"副表1字段名2": "xxx",
}],
"副表2表名": [{ // 一个主表字段可能对应多个副表字段的foreignKey
"副表2字段名1": "xxx",
"副表2字段名2": "xxx",
}],
"_value": "主表字段原始值" // 使用副表foreignKey查询时会在关联的主表字段内以_value存储该字段的原始值，新增于HBuilderX 3.1.16
}
}
可简单理解为：
主表foreignKey指向副表时，查询结果：关联字段只包含副表查询结果，不含表名
副表foreignKey指向主表时，查询结果：关联字段中会包含副表的表名和查询结果
临时联表查询
使用临时表进行联表查询，可以先对主表或者副表进行过滤，然后在处理后的临时表的基础上生成虚拟联表。
// 先过滤article表，再获取虚拟联表联表获取评论
const article = db.collection('article').where('article_id=="1"').getTemp() // 注意是getTemp不是get
const res = await db.collection(article, 'comment').get()
设置表字段别名
语法：原字段名 as 新字段名
const db = uniCloud.database()
const order = db.collection('order').field('book_id,quantity').getTemp()
const book = db.collection('book').field('_id,title as book_title,author as book_author').getTemp()
db.collection(order, book)
.where('book_id.book_title == "三国演义"') // 如果field内对副表字段title进行了重命名，where方法内则需要使用重命名之后的字段名
.get()
.then(res => {
console.log(res);
}).catch(err => {
console.error(err)
})
查询结果如下
{
"code": "",
"message": "",
"data": [{
"_id": "b8df3bd65f8f0d06018fdc250a5688bb",
"book_id": [{
"book_author": "罗贯中",
"book_title": "三国演义"
}],
"order_quantity": 555
}, {
"_id": "b8df3bd65f8f0d06018fdc2315af05ec",
"book_id": [{
"book_author": "罗贯中",
"book_title": "三国演义"
}],
"order_quantity": 333
}]
}
where 简单查询
这里的test方法比较强大，格式为：正则规则.test(fieldname)。
具体到这个正则 /abc/.test(content)，类似于sql中的content like '%abc%'，即查询所有字段content包含abc的数据记录。
注意
• 不支持非操作
• 编写查询条件时，除test外，均为运算符左侧为数据库字段，右侧为常量
简单查询条件内要求二元运算符两侧不可均为数据库内的字段
上述写法的查询语句可以在权限校验阶段与schema内配置的permission进行一次对比校验，如果校验通过则不会再查库进行权限校验。
复杂查询条件
复杂查询内可以使用数据库运算方法
示例：数据表test内有以下数据
{
"_id": "1",
"name": "n1",
"chinese": 60, // 语文
"math": 60 // 数学
}
{
"_id": "2",
"name": "n2",
"chinese": 60,
"math": 70
}
{
"_id": "3",
"name": "n3",
"chinese": 100,
"math": 90
}
//筛选语文数学总分大于150的数据
const db = uniCloud.database()
const res = await db.collection('test')
.where('add(chinese,math) > 150')
.get()
注意：复杂查询条件不可以使用正则查询
查询列表分页
可以通过skip+limit来进行分页查询
const db = uniCloud.database()
db.collection('book')
.where('status == "onsale"')
.skip(20) // 跳过前20条
.limit(20) // 获取20条
.get()
// 上述用法对应的分页条件为：每页20条取第2页
字段过滤field
// 联表查询
db.collection('order,book') // 注意collection方法内需要传入所有用到的表名，用逗号分隔，主表需要放在第一位
.field('book_id{title,author},quantity') // 这里联表查询book表返回book表内的title、book表内的author、order表内的quantity
.get()
//或者
db.collection('order,book')
.where('book_id.title == "三国演义"')
.field('book_id.title,book_id.author,quantity as order_quantity') // book_id.title、book_id.author为副表字段，使用别名时效果和上一个示例不同，请见下方说明
.orderBy('order_quantity desc') // 按照order_quantity降序排列
.get()
字段别名的话，还是用as 别名，和之前用法一致
比如
db.collection('order,book')
.where('book_id.title == "三国演义"')
.field('book_id{title as book_title,author as book_author},quantity as order_quantity')
// 这里联表查询book表返回book表内的title、book表内的author、order表内的quantity，
//并将title重命名为book_title，author重命名为book_author，quantity重命名为order_quantity
.orderBy('order_quantity desc') // 按照order_quantity降序排列
排序orderBy
orderBy允许进行多个字段排序，以逗号分隔。每个字段可以指定 asc(升序)、desc(降序)。默认是升序。
写在前面的排序字段优先级高于后面。
orderBy('quantity asc, create_date desc') //按照quantity字段升序排序，quantity相同时按照create_date降序排序
限制查询记录的条数limit
使用limit方法，可以查询有限条数的数据记录。
limit默认值是100，即不设置的情况下，默认返回100条数据。
limit有最大值，腾讯云限制为最大1000条，阿里云限制为最大500条。
// 这以上面的book表数据为例，查价格最高的一本书
db.collection('book')
.orderBy('price desc')
.limit(1)
.get()
只查一条记录getone
// 这以上面的book表数据为例
const db = uniCloud.database()
db.collection('book')
.where({
title: '西游记'
})
.get({
getOne:true
})
.then(res => {
console.log(res);
}).catch(err => {
console.error(err)
})
如果使用uniCloud-db组件，则在组件的属性上增加一个 getone
统计数量getcount
affectedDocs表示从服务器返回给前端的数据条数。默认100条，可通过limit方法调整。
count则是指符合查询条件的记录总数，至于这些记录是否返回给前端，和count无关。
count计数又有2种场景：
单纯统计数量，不返回数据明细
使用count()方法，如db.collection('order').count()
可以继续加where等条件进行数据记录过滤。
查询记录返回详情，同时返回符合查询条件的数量、使用getCount参数
// 这以上面的order表数据为例
const db = uniCloud.database()
db.collection('order')
.get({
getCount:true
})
.then(res => {
console.log(res);
}).catch(err => {
console.error(err)
})
如果使用uniCloud-db组件，则在组件的属性上增加一个 getcount
数据去重distinct
通过.distinct()方法，对数据查询结果中重复的记录进行去重。
const res = await db.collection('score')
.field('grade,class')
.distinct() // 注意distinct方法没有参数
.get()
查询返回结果
{data:[{grade:"1",class:"A"},{grade:"1",class:"B"},{grade:"2",class:"A"}]}
注意：distinct指对返回结果中完全相同的记录进行去重，重复的记录只保留一条。因为_id字段是必然不同的，所以使用distinct时必须同时指定field，且field中不可存在_id字段。
