c# mongodb or查询_PCB C# MongoDB 查询(SQL,NOSQL,C#对比)

一.准备阶段

1.查询数据清单准备

2.这里先将SQL语句执行顺序列出来，方便后面语句对比更好理解

(8)SELECT(9)DISTINCT(11)(1)FROM [left_table](3) JOIN(2) ON (4)WHERE (5)GROUP BY (6)WITH (7)HAVING (10)ORDER BY

二.查询全表

1. SQL:

select * from ppeflow

2. MongoDB NO SQL:

db.ppeflow.find()

3. MongoDB  C#

IMongoCollection ppeflows = db.GetCollection("ppeflow");//指定数据库

FilterDefinitionBuilder builderFilter = Builders.Filter; //创建过滤

FilterDefinition filter = builderFilter.Empty;//过滤为空

var resultList = ppeflows.Find(filter).ToList();//获取集合

三.查询数据总数

1. SQL:

select count(1) from ppeflow

2. MongoDB NO SQL:

db.ppeflow.find({}).count()

3. MongoDB  C#

IMongoCollection ppeflows = db.GetCollection("ppeflow");//指定数据库

FilterDefinitionBuilder builderFilter = Builders.Filter; //创建过滤

FilterDefinition filter = builderFilter.Empty;//过滤为空

var result = ppeflows.CountDocuments(filter); //获得集合数量

四.查询并排序

1. SQL:

select * from ppeflow order by orderid

select *from ppeflow order by orderid desc

2. MongoDB NO SQL:

db.ppeflow.find().sort({'orderid':1})

db.ppeflow.find().sort({'orderid':-1})

3. MongoDB  C#

方式一

IMongoCollection ppeflows = db.GetCollection("ppeflow");//指定数据库

FilterDefinitionBuilder builderFilter = Builders.Filter; //创建过滤

FilterDefinition filter = builderFilter.Empty;//过滤为空

var builderSort = Builders.Sort.Ascending(t => t.orderid); //创建排序

var resultList = ppeflows.Find(filter).Sort(builderSort).ToList();//获取集合

方式二

IMongoCollection ppeflows = db.GetCollection("ppeflow");//指定数据库

FilterDefinitionBuilder builderFilter = Builders.Filter; //创建过

FilterDefinition filter = builderFilter.Empty;//过滤为空

var resultList = ppeflows.Find(filter).SortByDescending(t => t.orderid).ToList();//获取集合

五.查询TOP前10条记录

1. SQL:

select top 10 * from ppeflow

2. MongoDB NO SQL:

db.ppeflow.find().limit(10)

3. MongoDB  C#

IMongoCollection ppeflows = db.GetCollection("ppeflow");//指定数据库

FilterDefinitionBuilder builderFilter = Builders.Filter; //创建过滤

FilterDefinition filter = builderFilter.Empty;//过滤为空

var resultList = ppeflows.Find(filter).Limit(10).ToList();//获取集合

六.查询从第6条到第10条记录(即：跳过前5条记录起的后5条记录)

1. SQL:

select * from ppeflow limit 5,5

2. MongoDB NO SQL:

db.ppeflow.find().skip(5).limit(5)

3. MongoDB  C#

IMongoCollection ppeflows = db.GetCollection("ppeflow");//指定数据库

FilterDefinitionBuilder builderFilter = Builders.Filter; //创建过滤

FilterDefinition filter = builderFilter.Empty;//过滤为空

var resultList = ppeflows.Find(filter).Skip(5).Limit(5).ToList();//获取集合

七.查询distinct去除重复字段

1. SQL:

select DISTINCT techname from ppeflow

2. MongoDB NO SQL:

db.ppeflow.distinct('techname')

3. MongoDB  C#

IMongoCollection ppeflows = db.GetCollection("ppeflow");//指定数据库

FilterDefinitionBuilder builderFilter = Builders.Filter; //创建过滤

FilterDefinition filter = builderFilter.Empty;//过滤为空

var resultList = ppeflows.Distinct(tt => tt.techname, filter).ToList();//获取集合

八.查询Group by分组

1. SQL:

select techname,count(1) as total from ppeflow group by techname

2. MongoDB NO SQL:

db.ppeflow.group({

key:{"techname":true}

,initial: {techname:'',total:0}

,reduce:function(doc, out)

{

out.techname = doc.techname;

out.total += doc.orderid;

}

})

3. MongoDB  C#

//待完善

九.查询条件>，>=，

1. SQL:

select techname from ppeflow where orderid > 10

select techname from ppeflow where orderid >= 10

select techname from ppeflow where orderid < 10

select techname from ppeflow where orderid <= 10

select techname from ppeflow where orderid <> 10

2. MongoDB NO SQL:

db.ppeflow.find({'orderid':{$gt:10}},{'techname':1})db.ppeflow.find({'orderid':{$gte:10}},{'techname':1})db.ppeflow.find({'orderid':{$lt:10}},{'techname':1})db.ppeflow.find({'orderid':{$lte:10}},{'techname':1})db.ppeflow.find({'orderid':{$ne:10}},{'techname':1})

3. MongoDB  C#

IMongoCollection ppeflows = db.GetCollection("ppeflow");//指定数据库

FilterDefinitionBuilder builderFilter = Builders.Filter; //创建过滤

FilterDefinition filter = builderFilter.Where(t => t.orderid > 10); ;//过滤条件 改为对应的>=,

var resultList = ppeflows.Find(filter).ToList();//获取集合

十.查询条件in与not in

1. SQL:

select * from ppeflow where orderid in (10,11,12)

select * from ppeflow where orderid not in (10,11,12)

2. MongoDB NO SQL:

db.ppeflow.find({'orderid':{$in:[10,11,12]}})

db.ppeflow.find({'orderid':{$nin:[10,11,12]}})

3. MongoDB  C#

IMongoCollection ppeflows = db.GetCollection("ppeflow");//指定数据库

FilterDefinitionBuilder builderFilter = Builders.Filter; //创建过滤

var filter = builderFilter.In(tt => tt.orderid, new List { 10, 11, 12 });//In 条件 或Nin

var resultList = ppeflows.Find(filter).ToList();//获取集合

十一.查询模糊查询 like

1. SQL:

select * from ppeflow wheretechname like '%钻孔%'

select * from ppeflow where techname like '钻孔%'

select * from ppeflow wheretechname like '%钻孔'

2. MongoDB NO SQL:

说明：i 忽略大小写 m 多行匹配模式 x 忽略非转义的空白字符 s 单 匹配模式

db.ppeflow.find( {'techname': /钻孔/i } )db.ppeflow.find( {'techname': /钻孔$/i } )db.ppeflow.find( {'techname': /^钻孔/i } )

3. MongoDB  C#

IMongoCollection ppeflows = db.GetCollection("ppeflow");//指定数据库

FilterDefinitionBuilder builderFilter = Builders.Filter; //创建过滤

var filter = builderFilter.Regex(tt => tt.techname, new BsonRegularExpression("/钻孔/i")); //正则匹配

var resultList = ppeflows.Find(filter).ToList();//获取集合

十二.查询多条件逻辑 or与and

1. SQL:

select * from ppeflow wheretechname = '开料' or techname = '钻孔'

select * from ppeflow where orderid = 2 and techname = '钻孔'

2. MongoDB NO SQL

db.ppeflow.find({$or:[{'techname':'开料'},{'techname':'钻孔'}]})

db.ppeflow.find({$and:[{'orderid':2},{'techname':'钻孔'}]})

3. MongoDB  C#

C#  or

IMongoCollection ppeflows = db.GetCollection("ppeflow");//指定数据库

FilterDefinitionBuilder builderFilter = Builders.Filter; //创建过滤

var filter1 = builderFilter.Eq(tt => tt.techname, "开料");var filter2 = builderFilter.Eq(tt => tt.techname, "钻孔");var filter =builderFilter.Or(filter1, filter2);var resultList = ppeflows.Find(filter).ToList();//获取集合//ppeflows.AsQueryable().Where(tt => tt.techname == "开料" || tt.techname == "钻孔").ToList();//lamda一种简写

C#  and

IMongoCollection ppeflows = db.GetCollection("ppeflow");//指定数据库

FilterDefinitionBuilder builderFilter = Builders.Filter; //创建过滤

var filter1 = builderFilter.Eq(tt => tt.orderid, 2);var filter2 = builderFilter.Eq(tt => tt.techname, "钻孔");var filter =builderFilter.And(filter1, filter2);var resultList = ppeflows.Find(filter).ToList();//获取集合//ppeflows.AsQueryable().Where(tt => tt.orderid == 2 && tt.techname == "钻孔").ToList();//lamda一种简写