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

Source: Internet
Author: User
Tags prev mongo shell



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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.