MongoDB 基础（二）mongodb 与 T-SQL 对比

SQL Terms/Concepts

database

table

row

column

Value

index

table joins

primary key

Specify any unique column or column combination as primary key.

Help

查看帮助（mongodb 中注意大小写）

db.help()

help on db methods

db.mycoll.help()

help on collection methods

sh.help()

sharding helpers

rs.help()

replica set helpers

help admin

administrative help

help connect

connecting to a db help

help keys

key shortcuts

help misc

misc things to know

help mr

mapreduce

show dbs

show database names

show collections

show collections in current database

show users

show users in current database

show profile

show most recent system.profile entries with time >= 1ms

show logs

show the accessible logger names

show log [name]

prints out the last segment of log in memory, 'global' is default

use <db_name>

set current database

db.foo.find()

list objects in collection foo

db.foo.find( { a : 1 } )

list objects in foo where a == 1

it

result of the last line evaluated; use to further iterate

DBQuery.shellBatchSize = x

set default number of items to display on shell

exit

quit the mongo shell

                                                                                                            数据库

show dbs

查看所有数据库

use mydb

设置为当前数据库

db

查看当前数据库名称

db.createCollection("tab")

db.tab.insert({"id":1})

创建数据库（创建collection）

创建数据库（插入数据也会被创建）

db.copyDatabase("mydb", "newmydb", "127.0.0.1")

复制为另一个新的数据库

db.dropDatabase()

删除数据库

Collection定义操作（集合必须以字母或下划线开头）

db.createCollection("tab",{size:1024,max:1000,capped:true})

db.tab.insert({"id":1})

创建collections 并做限制（如创建“tab”）

(插入数据也会创建collections)

show collections

查看当前数据库所有collections

db.tab.drop()

删除collections

db.tab.update({},{$rename:{"name":"newname"}},false,true)

更改所有行的字段名name为newname

db.getCollection('tab').renameCollection('newtab')

db.newtab.renameCollection('tab')

更改collection名称

Collection 数据操作

db.tab.save({"id":2})

db.tab.insert({"id":3})

db.tab.insert([{ size: "S", qty: 25 }, { size: "M", qty: 50 }])

db.tab.insert({ array :[{ size: "S", qty: 25 }, { size: "M", qty: 50 } ]})

插入数据到collections

（会自动生成唯一列 “_id”）

插入多行（相当： insert into tab values(“S”,1), (“M”,50)）

字段中插入子集

db.tab.update({"id":1},{$set:{"id":5}})

更改数据（id值由1更改为5）

db.tab.update({ id:2},{id:6, name:'E'})

将id=2的行更新为新的行

db.tab.update({"id":1},{$unset:{ name:'120'}})

删除一个键值对（删除id=2中的字段name）

db.tab.update({"id":1},{$push:{ name:'C'}})

往id=1的行 字段为name数组中插入元素’C’

db.tab.update({"id":1},{$pull:{ name:'C'}})

从id=1的行 字段为name数组中删除所有元素’C’

db.tab.remove({"id":3})

db.tab.remove({"id":3},1)

db.tab.remove({})

删除id=3的所有记录

删除id=3的第一条记录

删除所有记录

Collection查询数据操作

Select * from tab

db.tab.find()

db.tab.find({})

Select id from tab

db.tab.find({},{"id":1})   #({条件},{字段:0/1})

db.tab.find({},{"_id":0,"id":1})

db.tab.aggregate({ $project : {id : 1 }});

db.tab.find({id:{$exists:1}});

Select * from tab where id=1

db.tab.find( { id :1})

Select id from tab where id=1

db.tab.find({id :1},{"_id":0,"id":1})

Select * from tab where id<>1

db.tab.find({id:{$ne:1}})

Select * from tab where id>2

db.tab.find( { id: { $gt: 2} } )

Select * from tab where id<3

db.tab.find( { id: { $lt: 3} } )

Select * from tab where id>=2

db.tab.find( { id: { $gte: 2 } } )

Select * from tab where id<=3

db.tab.find( { id: { $lte: 3} } )

Select * from tab where id = 1 or id = 2

db.tab.find({$or:[{id:3},{id:2}]})

Select * from tab where id < 2 or id >4

db.tab.find({$or:[{id:{$gt:4}},{id:{$lt:2}}]})

Select * from tab where id in (1,2)

db.tab.find( { id: { $in: [ 1, 2 ] } } )

Select * from tab where id not in (2,3)

db.tab.find({id:{"$nin":[2,3]}})

Select * from tab where id between 2 and 3

Select * from tab where id >= 2 and id <= 3

db.tab.find({$and:[{id:{$gte:2}},{id:{$lte:5}}]})

Select * from tab where id = 1 and name = ‘kk’

db.tab.find({id:2, name:'kk'})

Select distinct id from tab

db.tab.distinct("id");

db.runCommand ( { distinct: "tab", key: "id" } )

Select distinct id from tab where name = ‘A’

db.runCommand({distinct:'tab',key:'id',query:{name:'A'}})

Select * from tab where name like ‘%A%’

db.tab.find({ name:{$regex:"A"}})

db.tab.find({ name:/A/})

Select * from tab order by id asc

db.tab.find().sort({id:1})

Select * from tab order by id desc

db.tab.find().sort({id:-1})

Select  top 5 * from tab

db.tab.find().limit(5)

跳过前m条记

db.tab.find().skip(2)

db.tab.aggregate({ $skip : 2 });

跳过前m条记录，从m+1开始取n条

db.tab.find().skip(2).limit(3)

除了指定的列，其他列都显示

db.tab.find({id:null})

db.tab.find({},{"_id":0})

查找字段id为string类型的行(参考下表格)

db.tab.find({ id: {$type: 2}});

select name,sum(id) as sumid

from tab

where id >0 group by name

db.tab.group({

key:{ "name":true}  # group by name

,cond:{id:{ $gt:0}}   # where id >0

,reduce:function(obj,prev) #聚合函数

{ prev.sumid += obj.id; } #函数逻辑，累加id

, initial: {sumid: 0 }})  #初始化

Select sum(*) from tab

db.tab.group({key:{},cond:{}

,reduce:function(obj,prev)

{ prev.sumid += obj.id; },initial: {sumid: 0 }});

Select sum(*) as newcol from tab

db.tab.aggregate([{$group:{_id:"$by_user",newcol:{$sum:"$id"}}}])

Select count(*) from tab

db.tab.count()  或者 db.tab.find().count()

Select count(*) from tab

db.tab.group({key:{},cond:{},

reduce:function(obj,prev)

{ prev.sumid += 1; },initial: {sumid: 0 }});

Select avg(*) from tab

db.tab.aggregate([{$group:{_id:"$by_user",newcol:{$avg:"$id"}}}])

Select max(*) from tab

db.tab.find().sort({id:-1}).limit(1)

db.tab.aggregate([{$group:{_id:"$by_user",newcol:{$max:"$id"}}}])

Select min(*) from tab

db.tab.find().sort({id:1}).limit(1)

db.tab.aggregate([{$group:{_id:"$by_user",newcol:{$min:"$id"}}}])

#元素查询

#db.tab.insert({ratings: [ 5, 8, 9 ] })

db.tab.find({ ratings: [ 5, 8, 9 ] } )   #查找匹配的数组

db.tab.find({ ratings: 5 })     #查找元素中含“5”的记录

db.tab.find({ ratings:{$elemMatch:{$gt:8,$lt:10}}})   #元素匹配查找

#内嵌文档

#db.tab.insert({producer:{company: 'ABC',address: 'Street'}})

#db.tab.insert({producer:[{ company: 'ABC',address: 'Street'},{ company: 'KK',address: 'Street2'}] })

db.tab.find({producer:{company: 'ABC',address: 'Street'}})

db.tab.find({'producer.company': 'ABC'})

db.tab.find( { 'producer.0.address': 'Street'} )  #字段'producer的第一个元素的address=’ Street’

类型描述

类型值

Double

1

String

2

Object

3

Array

4

Binary data

5

Object id

7

Boolean

8

Date

9

Null

10

Regular expression

11

JavaScript code

13

Symbol

14

JavaScript code with scope

15

32-bit integer

16

Timestamp

17

64-bit integer

18

Min key

255

Max key

127