Mongodb
1.创建普通索引
db.mogOrderInfo.createIndex({"parentUserId":1});
2.创建复合索引
db.mogOrderInfo.createIndex({"orderId":1,"commerceId":1,"deliveryId":1});
3.查询索引
db.mogOrderInfo.getIndexes({})
4.条件查询总数
db.mogOrderMsgInfo.count({"createTime":{"$lte":ISODate("2019-06-25T04:00:00.000Z")}})
5.条件查询信息
db.mogOrderInfo.find({"userIdRelation":{$in:[100000001,200000001,300000001,56097560522]}})
6.条件删除信息
db.mogOrderInfo.remove({"userIdRelation":{$in:[100000001,200000001,300000001,56097560522]}})
7.模糊查询
db.mogOrderInfo.find({"msgBody":{$regex:/11855454316/}})
8.查询不存在的信息集合
db.mogOrderInfo.find({"msgBody":{$exists: false}})
9.更新数据信息
db.user_info.update({},{$unset:{'user_type':''}},false, true)
10.按照条件查询并转换时间格式
db.mogOrderInfo.find(
{
"itemId":{$in:["9140146311","9140146311"]},
"orderTime":{"$gte":ISODate("2019-07-10T02:08:00.000Z")},
"showStatus":{$in:[1,2,5]}
},
{
"orderId":1,
"orderTime":1,
}
).forEach(function(a){
function tran_val(val){
if(parseInt(val)<10){
val="0" +val;
}
return val;
}
var datenew = new Date(a["orderTime"]);
var year=datenew.getFullYear();
var month=tran_val(datenew.getMonth()+1);
var date=tran_val(datenew.getDate());
var hour=tran_val(datenew.getHours());
var minute=tran_val(datenew.getMinutes());
var second=tran_val(datenew.getSeconds());
var datastr=year+"-"+month+"-"+date+" "+hour+":"+minute+":"+second;
a["orderTime"] = datastr
printjson(a)
}
).sort({itemId:-1})
Mysql
1.更新字段描述
ALTER TABLE mshop_task_info MODIFY COLUMN type INT COMMENT '任务行为类型';
2.更新字段类型等
alter table mshop_push_message modify template_id bigint(20) NOT NULL COMMENT '模板';
3.新增字段
ALTER TABLE mshop_task_info ADD order_gmv BIGINT(20) DEFAULT NULL COMMENT '活动';
4.模糊查询
select * from vshop_info where vshop_name like '%美%'
5.时间戳查询格式化函数
FROM_UNIXTIME(rebate_time/1000,'%Y-%m-%d %H:%i:%S')