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

Source: Internet
Author: User
Tags mongo shell

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



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.