MongoDB Step by Step (ii) MongoDB and SQL Basic comparison

SQL terms/concepts	MongoDB terms/concepts
Database	Database
Table	Collection (Collection)
Row	Document (documentation)
Column	Key (Keys)
Value	Value (values)
Index	Index
Table joins	Embedded documents and linking
Primary key	Primary key
Specify any unique column or column combination as primary key.	In MongoDB, the primary key was automatically set to the _id field.
Aggregation (e.g GROUP by)	Aggregation pipeline

Help	View Help (note capitalization in 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 logs 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

Database
Show DBS	View all databases
Use MyDB	Set as current database
Db	View the current database name
Db.createcollection ("tab") Db.tab.insert ({"id": 1})	Create a database (Create collection) Create a database (insert data will also be created)
Db.copydatabase ("MyDB", "Newmydb", "127.0.0.1")	Copy to another new database
Db.dropdatabase ()	Deleting a database

Collection defining an action (the collection must begin with a letter or an underscore)
Db.createcollection ("tab", {size:1024,max:1000,capped:true}) Db.tab.insert ({"id": 1})	Create collections and make restrictions (such as creating "tab") (Inserting data will also create collections)
Show collections	View current database All collections
Db.tab.drop ()	Delete Collections
Db.tab.update ({},{$rename: {"name": "NewName"}},false,true)	Change the field name for all rows name is NewName
Db.getcollection (' tab '). Renamecollection (' Newtab ') Db.newtab.renameCollection (' tab ')	Change the collection name

Collection Data manipulation
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}]})	Inserting data into collections (The unique column "_id" is automatically generated) Insert multiple lines (equivalent: INSERT into tab values ("S", 1), ("M", 50)) Insert a subset in a field
Db.tab.update ({"id": 1},{$set: {"id": 5}})	change data (id value changed from 1 to 5)
Db.tab.update ({id:2},{id:6, Name: ' E '})	To update a row of id=2 to a new row
Db.tab.update ({"id": 1},{$unset: {name: ' 120 '}})	Delete a key-value pair (delete the field in id=2 name)
Db.tab.update ({"id": 1},{$push: {name: ' C '}})	Insert element ' C ' in the name array to the Id=1 line field
Db.tab.update ({"id": 1},{$pull: {name: ' C '}})	Remove all elements ' C ' from the line field in the name array from the Id=1
Db.tab.remove ({"id": 3}) Db.tab.remove ({"id": 3},1) Db.tab.remove ({})	Delete all records for id=3 Delete the first record of id=3 Delete all records

Collection Querying data operations
Select * from tab	Db.tab.find () Db.tab.find ({})
Select ID from tab	Db.tab.find ({},{"id": 1}) # ({Condition},{field: 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 (	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)
Skip previous M notes	Db.tab.find (). Skip (2) Db.tab.aggregate ({$skip: 2});
Skip the previous m record, starting from m+1 to fetch n	Db.tab.find (). Skip (2). Limit (3)
In addition to the specified columns, the other columns are displayed	Db.tab.find ({id:null}) Db.tab.find ({},{"_id": 0})
Find a row with a field ID of type string (refer to the table below)	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;} #函数逻辑, accumulate 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 () or 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}) #查找元素中含 record for "5" Db.tab.find ({ratings:{$elemMatch: {$gt: 8, $lt: Ten}}}) #元素匹配查找 #内嵌文档 #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's first element of address= ' street '

Type description	Type value
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

MongoDB Step by Step (ii) MongoDB and SQL Basic comparison