MongoDB step by step (2) mongodb compared with T-SQL, mongodbt-SQL

MongoDB step by step (2) mongodb compared with T-SQL, mongodbt-SQL

SQL Terms/Concepts	MongoDB Terms/Concepts
Database	Database
Table	Collection)
Row	Document)
Column	Key)
Value	Value)
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 is automatically set to the _ id field.
Aggregation (e.g. group)	Aggregation pipeline

Help	View help (case sensitive 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> = 1 ms
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

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 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 ()	Delete Database

Collection-defined operations (the Set must start with a letter or underline)
Db. createCollection ("tab", {size: 1024, max: 1000, capped: true }) Db. tab. insert ({"id": 1 })	Create a collections and make restrictions (for example, create a "tab ") (Collections will also be created when data is inserted)
Show collections	View All collections of the current database
Db. tab. drop ()	Delete collections
Db. tab. update ({},{$ rename: {"name": "newname" }}, false, true)	Change the field name of all rows to newname.
Db. getCollection ('tab '). renameCollection ('newtab ') Db. newtab. renameCollection ('tab ')	Change collection name

Collection Data Operations
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}]})	Insert data to collections (The unique column "_ id" is automatically generated ") Insert multiple rows (equivalent: insert into tab values ("S", 1), ("M", 50 )) Insert subset in 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 '})	Update the row id = 2 to the new row.
Db. tab. update ({"id": 1}, {$ unset: {name: '000000 '}})	Delete a key-Value Pair (delete the field name in id = 2)
Db. tab. update ({"id": 1}, {$ push: {name: 'C '}})	Insert the 'C' element into the name array for the row field with id = 1'
Db. tab. update ({"id": 1 },{$ pull: {name: 'C '}})	Removes all 'C' elements from the array whose row field id = 1 is name'
Db. tab. remove ({"id": 3 }) Db. tab. remove ({"id": 3}, 1) Db. tab. remove ({})	Delete all records with id = 3 Delete the first record with id = 3 Delete all records

Collection query 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 (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: ""}}) Db. tab. find ({name://})
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 the first m records	Db. tab. find (). skip (2) Db. tab. aggregate ({$ skip: 2 });
Skip the first m records and take n records from m + 1	Db. tab. find (). skip (2). limit (3)
All columns except the specified columns are displayed.	Db. tab. find ({id: null }) Db. tab. find ({}, {"_ id": 0 })
Search for rows whose field id is string (refer to the following table)	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) # Aggregate function {Prev. sumid + = obj. id;} # function logic, accumulate id , Initial: {sumid: 0 }}) # Initialization
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" }}}])
# Element Query # Db. tab. insert ({ratings: [5, 8, 9]}) Db. tab. find ({ratings: [5, 8, 9]}) # find A matched Array Db. tab. find ({ratings: 5}) # search for records containing "5" in the element Db. tab. find ({ratings: {$ elemMatch: {$ gt: 8, $ lt: 10 }}) # search for element matching # Embedded document # 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'}) # The address of the first element of 'producer = '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