MongoDB

查

查看库/集合大小
db.stats()
db.col.stats()

按时间倒序查询n条
db.user_phone_apps.find({},{title:1,_id:0,publish_time:1,author_name:1}).sort({publish_time:-1}).limit(10).pretty()
db.user_phone_apps.find({},{_id:0}).sort({timestamp:-1}).limit(10)
db.offline_phone_apps.find({city:’未知’}).count()
db.app_push.find().sort({publish_at:-1}).limit(3).pretty()

随机选10条
db.user_phone_apps.aggregate([{$sample:{size:10}}])
db.user_phone_apps.aggregate([{$sample:{size:10}},{$project:{_id:0}}])

按字段长度查询
db.item.find({$where:"this.item_name.length < 5"}).limit(5)

查询某字段去重后的数量
db.col.distinct('字段名').length

查询某字段不存在(存在)的数量
db.user_phone_apps.find({apps:{$exists:0}}).count()
db.user_phone_apps.find({kugou:{$exists:1}}).count()

查询<x<区间的数量
db.user_phone_apps.find({timestamp:{$gt:1627056000,$lt:1627142400}}).count()
db.user_phone_apps.find({timestamp:{$gt:1626796800}}).count()

条件查询
$ne：匹配不等于指定值

删除

删除集合
db.collection.drop()
按条件删除
db.app_push.remove({publish_at:{$lt:'2020-10-01 00:00:00'}})
删除字段
db.user_phone_apps.update({},{$unset:{"apps":""}},false,true)

改

增加一个字段, 值为另一个字段
db.cyys_doctor.find().forEach(function(item){db.cyys_doctor.update({_id:item._id},{$set:{unique_id:item.doctor_detail.id}},true)})

复制col, 原:db.aaa,复制后：db.bbb
db.aaa.find({}).forEach(function(x){db.bbb.insert(x)})

以group字段去重
db.dxys_doctor.aggregate([{$group:{_id:{unique_id:'$unique_id’},count:{$sum:1},dups:{$addToSet:'$_id'}}},{$match:{count:{$gt:1}}}],{allowDiskUse: true}).forEach(function(doc){ doc.dups.shift(); db.dxys_doctor.remove({_id: {$in: doc.dups}});})
                                        
重命名集合
db.orders.renameCollection( "orders2014" )

索引

db.col.createIndex({"title":1}) # 创建索引 1 :升序，-1: 降序 
db.col.getIndexes() # 查看索引
db.col.dropIndex('XXX') # 删除指定索引

MySQL

查看某张表的字段

SELECT column_name FROM information_schema.columns WHERE table_name='xxx'

查看某库表和数据量

SELECT table_name,table_rows FROM information_schema.tables WHERE table_schema='xxx'

查看某库所有表所占内存空间

SELECT table_name, data_length + index_length AS len, table_rows,CONCAT(ROUND((data_length + index_length)/1024/1024,2),'MB') AS datas

FROM information_schema.tables WHERE table_schema = 'special_data' ORDER BY len DESC

获取每个人最早一天

SELECT * FROM (SELECT * FROM account_ticker GROUP BY Account_name, As_Of_Date ASC) AS base GROUP BY Account_name

Redis

del xxx

llen ugc_tt:start_url

lpush ugc_ks:start_urls '{"author_name":"只如初见","author_url":"https://live.kuaishou.com/profile/3xyuf6qvrdr96vm","crawl":"newest"}'