【数据库笔记】

数据库笔记

一、Mysql

1.拼接查询数据

SELECT concat(username,',',mobile) FROM `user` WHERE id = 4;

二、Mongo

1.修改语句

按单条件修改单条：

db.getCollection("stockout").update({shopNO:'s0000016'},{$set:{sysCode:'196040619011731456',companyCode:'46883'}});

按单条件修改全部：

db.getCollection("stockout").update({shopNO:'s0000016'},{$set:{sysCode:'196040619011731456',companyCode:'46883'}},{multi:true});

按多条件修改单条：

db.getCollection("stockout").update({shopNO:'s0000016'},{bill_NO:'NSO202104280000000'},{$set:{sysCode:'196040619011731456',companyCode:'46883'}});

按多条件修改单条：

db.getCollection("stockout").update({shopNO:'s0000016'},{bill_NO:'NSO202104280000000'},{$set:{sysCode:'196040619011731456',companyCode:'46883'}},{multi:true});

批量修改：

db.getCollection("shouldpay").update({tradeNo:{"$in":['1288225668037254688','1763304123181353934']}},{$set:{sysCode:'196040619011731456',companyCode:'46883'}},{multi:true});

2.查询

单条件查询：

db.getCollection("stockout").find({"shopNO":"s0000016"});

多条件查询：

db.getCollection("stockout").find({$and:[{"shopNO":"s0000016"},{"billNO":"NSO202104280000000"}]});

不等于查询：

db.getCollection("stockout").find({$and:[{"shopNO":"s0000016"},{"billNO":{$ne:"NSO202104280000000"}}]});

in查询：

db.getCollection("stockout").find({"itemList.platformNO":{"$in":['1237111155476155211','1472286241566779578']}});

and+in查询：

not in查询：

db.getCollection("stockout").find({"itemList.platformNO":{"$nin":['1237111155476155211','1472286241566779578']}});

单like查询1：

db.getCollection("stockout").find({"shopNO" : /.*s0000016.*/i});

多like查询2：

db.getCollection("stockout").find({ $and : [{"shopNO" : /.*s0000016.*/i}, {"billNO" : /.*NSO202104280000000.*/i}] });

单not like查询：

db.getCollection("stockout").find({"shopNO" : { $not : /.*s0000016.*/i }});

多not like查询：

db.getCollection("stockout").find({ $and : [{"shopNO" : { $not : /.*s0000016.*/i }}, {"billNO" : { $not : /.*NSO202104280000000.*/i }}] });

查询后返回想要的字段：

db.getCollection("stockout").find({$and:[{"shopNO":"s0000016"},{"billNO":"NSO202104280000000"}]},{"billNO":1,"billDate":1});

3.排序

查询后排序：

db.getCollection("stockout").find({"shopNO":"s0000016"}).sort({createdTime:-1});

4.组合查询

组合查询：

db.getCollection("costinvoice").find({ $and : [{"platform" : "Amazon"},{"businessType": 0}, {"auditStatus": 2}, {"delStatus": {$ne: 1}}, {"transactionType" : "Order"},{"relativeUnitCode": {$in:  ['1']}},{"billDate": { $gte: new Date("2020-08-31 00:00:00.000")}},{"billDate": {$lt: new Date("2020-09-10 00:00:00.000")}} ] }).sort({billDate: -1});

组合查询后汇总：

db.getCollection("costinvoice").aggregate([{$match:{$and:[{"platform":"Amazon"},{"businessType":0},{"auditStatus":2}, {"delStatus":{$ne:1}},{"transactionType":"Order"} ,{"sysCode":"196040619011731456"},{"companyCode":"46883"}, {"relativeUnitCode":{$in:['1']}} ,{"billDate":{$gte:newDate("2020-10-0100:00:00.000")}},{"billDate":{$lt:newDate("2020-10-3123:59:59.000")}}, ]}},{$unwind:"$itemList"}, {$group:{_id:{"relativeUnitNO":"$relativeUnitNO", "relativeUnitName":"$relativeUnitName", "shopCode":"$shopCode","currencyCode":"$currencyCode", },totalFee:{$sum:"$itemList.billFeeAmount"}}}]);

5.求和

普通求和：

db.getCollection("syncstockouttaskdetail").aggregate({$group :{_id:null,amount:{$sum:"$afterTotalAmount"}}});

6.分组

三、其他

 

五、其他