MySQL查询条件为null时

作者: 编程应用  发布:2019-12-26

在mysql中询问条件为null时,无法运用= null, 必要动用 isnull,可能规格为不是空时供给接受isnotnull。在sqlserver 和oracle 中千篇生机勃勃律

  • 接纳find举办查询

空文书档案查询, 相称集结全数因素

> db.persons.find()
{ "_id" : ObjectId("58527a364698e19ca147b3d1"), "name" : "joe", "age" : 26, "sex" : "female", "comments" : [ "commentA", "comment6", "comment7" ] }
> db.persons.find({})
{ "_id" : ObjectId("58527a364698e19ca147b3d1"), "name" : "joe", "age" : 26, "sex" : "female", "comments" : [ "commentA", "comment6", "comment7" ] }
>

点名条件查询

> db.persons.find({"name":"joe"})
{ "_id" : ObjectId("58527a364698e19ca147b3d1"), "name" : "joe", "age" : 26, "sex" : "female", "comments" : [ "commentA", "comment6", "comment7" ] }
> db.persons.find({"name":"li"})
>

点名供给回到的键
生龙活虎对时候我们不想看看不保护的键, 能够利用find或findOne的第4个参数内定
那样既可以节省传输的数据量, 又能收缩客商端深入分析数据的支出

> db.persons.find({}, {"age":1})
{ "_id" : ObjectId("58527a364698e19ca147b3d1"), "age" : 26 }
>

钦赐没有必要需求再次来到的键

> db.persons.find({}, {"age":0})
{ "_id" : ObjectId("58527a364698e19ca147b3d1"), "name" : "joe", "sex" : "female", "comments" : [ "commentA", "comment6", "comment7" ] }
>
  • 查询条件

越来越多的查询条件: $lt, $lte, $gt, $gte

$lt, $lte, $gt, $gte分别表示less than(<卡塔尔(قطر‎, less than or equal(<=卡塔尔, greater than(>卡塔尔(قطر‎,
greater than or equal(>=)

以$lt为例讲一下用法

> db.persons.find({"age":{$lt:18}})
> db.persons.find({"age":{$lt:28}})
{ "_id" : ObjectId("58527a364698e19ca147b3d1"), "name" : "joe", "age" : 26, "sex" : "female", "comments" : [ "commentA", "comment6", "comment7" ] }
> db.persons.find({"age":{$lt:28, $gt:25}})
{ "_id" : ObjectId("58527a364698e19ca147b3d1"), "name" : "joe", "age" : 26, "sex" : "female", "comments" : [ "commentA", "comment6", "comment7" ] }
> db.persons.find({"age":{$lt:28, $gt:27}})
>

更加多的查询条件: $ne

$ne代表not equal, 不仅可以够用来数字, 还可以够用于别的全部数据类型

> db.persons.find({"age":{$ne:28}})
{ "_id" : ObjectId("58527a364698e19ca147b3d1"), "name" : "joe", "age" : 26, "sex" : "female", "comments" : [ "commentA", "comment6", "comment7" ] }
> db.persons.find({"age":{$ne:26}})
> db.persons.find({"name":{$ne:"liu"}})
{ "_id" : ObjectId("58527a364698e19ca147b3d1"), "name" : "joe", "age" : 26, "sex" : "female", "comments" : [ "commentA", "comment6", "comment7" ] }
>

越多的询问条件: $in/$nin和$or

$in前边跟条件数组, $nin再次来到与数组中装有加多都不包容的文书档案

> db.persons.find({"name":{$in:["liu"]}})
> db.persons.find({"name":{$in:["liu","joe"]}})
{ "_id" : ObjectId("58527a364698e19ca147b3d1"), "name" : "joe", "age" : 26, "sex" : "female", "comments" : [ "commentA", "comment6", "comment7" ] }
>

$or 接收蕴涵全体超级大概率条件构成的数组

> db.persons.find({$or:[{"name":"joe"},{"age":26}]})
{ "_id" : ObjectId("58527a364698e19ca147b3d1"), "name" : "joe", "age" : 26, "sex" : "female", "comments" : [ "commentA", "comment6", "comment7" ] }
>

越来越多的查询条件: $not

$not 是元语句, 也正是足以用在别的其余规范之上

> db.persons.find({"name":{"$not":{$in:["joe"]}}})
> db.persons.find({"name":{"$not":{$nin:["joe"]}}})
{ "_id" : ObjectId("58527a364698e19ca147b3d1"), "name" : "joe", "age" : 26, "sex" : "female", "comments" : [ "commentA", "comment6", "comment7" ] }
>

规范化语义

基准语句是内层文书档案的键, 而改善器是外围文书档案的键. 能够对二个键运用四个查询条件, 不过回天无力对三个键行使两个更正器.

有意气风发对元操作符, 如$and, $or, $nor也放在外层文档

  • 特定类型的查询

null查询

null 不止会相称某些键是null的文书档案, 何况还可能会合营紧缺钦赐键的文书档案.
假如只想询问键值为null的文书档案, 那么还亟需检查键是还是不是业已存在

> db.persons.insert({"name":"liu", "age":28, "sex":"male", comments:null})
WriteResult({ "nInserted" : 1 })
> db.persons.find({"comments":null})
{ "_id" : ObjectId("585a822238bd2361cd7aee6e"), "name" : "liu", "age" : 28, "sex" : "male", "comments" : null }
> db.persons.find({"books":null})
{ "_id" : ObjectId("58527a364698e19ca147b3d1"), "name" : "joe", "age" : 26, "sex" : "female", "comments" : [ "commentA", "comment6", "comment7" ] }
{ "_id" : ObjectId("585a822238bd2361cd7aee6e"), "name" : "liu", "age" : 28, "sex" : "male", "comments" : null }
> db.persons.find({"books":{$in:[null], $exists:true}})
> db.persons.find({"comments":{$in:[null], $exists:true}})
{ "_id" : ObjectId("585a822238bd2361cd7aee6e"), "name" : "liu", "age" : 28, "sex" : "male", "comments" : null }
>

正则表明式

MongoDB 使用Perl宽容的正则表达式

> db.persons.find({"name":/jo*/})
{ "_id" : ObjectId("58527a364698e19ca147b3d1"), "name" : "joe", "age" : 26, "sex" : "female", "comments" : [ "commentA", "comment6", "comment7" ] }
>

询问数组

能够钦定数组中某二个成分作为查询条件, 如若须要整个神工鬼斧, 那么要求动用$all

> db.persons.find({"comments":"commentA"})
{ "_id" : ObjectId("58527a364698e19ca147b3d1"), "name" : "joe", "age" : 26, "sex" : "female", "comments" : [ "commentA", "comment6", "comment7" ] }
> db.persons.find({"comments":{$all:["commentA","commentB"]}})
>

查询数组钦命地方上的要素为特定值

> db.persons.find({"comments.0":"commentA"})
{ "_id" : ObjectId("58527a364698e19ca147b3d1"), "name" : "joe", "age" : 26, "sex" : "female", "comments" : [ "commentA", "comment6", "comment7" ] }
> db.persons.find({"comments.1":"commentA"})
>

询问特定长度的数组

> db.persons.find({"comments":{$size:3}})
{ "_id" : ObjectId("58527a364698e19ca147b3d1"), "name" : "joe", "age" : 26, "sex" : "female", "comments" : [ "commentA", "comment6", "comment7" ] }
> db.persons.find({"comments":{$size:2}})
>

但$size不帮忙与$lt等查询条件一同利用

本文由金沙澳门官网送注册58发布于编程应用,转载请注明出处:MySQL查询条件为null时

关键词:

上一篇:求大神帮助
下一篇:没有了