In-depth introduction to MongoDB (v) mongo syntax and mysql syntax comparison

Source: Internet
Author: User
Tags mysql commands
We always see our advantages and disadvantages in the comparison, which is the same for mongodb. The comparison study allows us to master the basic knowledge about mongodb as soon as possible. Comparison between mongodb and mysql commands relational databases are generally composed of three levels: database, table, and record. Instead of relational database mong

We always see our advantages and disadvantages in the comparison, which is the same for mongodb. The comparison study allows us to master the basic knowledge about mongodb as soon as possible. Comparison between mongodb and mysql commands relational databases are generally composed of three levels: database, table, and record. Instead of relational database mong

We always see our advantages and disadvantages in the comparison, which is the same for mongodb. The comparison study allows us to master the basic knowledge about mongodb as soon as possible.

Comparison between mongodb and mysql commands

Relational databases are generally composed of three levels: database, table, and record. Instead, apsaradb for mongodb is composed of three layers: database, collection, and document. Mongodb does not have the concept of the relationship between rows and columns for tables in relational databases, which reflects the free features of the mode.

The syntax commands are shown in the following table.

MySQL

MongoDB

Description

Mysqld

Mongod

Server daemon

Mysql

Mongo

Client tools

Mysqldump

Mongodump

Logical backup tools

Mysql

Mongorestore

Logical recovery tool

Db. repairDatabase ()

Restore database

Mysqldump

Mongoexport

Data export tool

Source

Export Import

Data Import Tool

Grant * privileges on *. *...

Db. addUser ()

Db. auth ()

Create a user and grant permissions

Show databases

Show dbs

Show library list

Show tables

Show collections

Display table list

Show slave status

Rs. status

Query Master/Slave status

Create table users (a int, B int)

Db. createCollection ("mycoll", {capped: true,

Size: 100000}) and implicitly create a table.

Create a table

Create INDEX idxname ON users (name)

Db. users. ensureIndex ({name: 1 })

Create an index

Create INDEX idxname ON users (name, ts DESC)

Db. users. ensureIndex ({name: 1, ts:-1 })

Create an index

Insert into users values (1, 1)

Db. users. insert ({a: 1, B: 1 })

Insert record

Select a, B from users

Db. users. find ({}, {a: 1, B: 1 })

Query a table

Select * from users

Db. users. find ()

Query a table

Select * from users where age = 33

Db. users. find ({age: 33 })

Conditional Query

Select a, B from users where age = 33

Db. users. find ({age: 33}, {a: 1, B: 1 })

Conditional Query

Select * from users where age <33

Db. users. find ({'age': {$ lt: 33 }})

Conditional Query

Select * from users where age> 33 and age <= 40

Db. users. find ({'age': {$ gt: 33, $ lte: 40 }})

Conditional Query

Select * from users where a = 1 and B = 'Q'

Db. users. find ({a: 1, B: 'q '})

Conditional Query

Select * from users where a = 1 or B = 2

Db. users. find ({$ or: [{a: 1 },{ B: 2}]})

Conditional Query

Select * from users limit 1

Db. users. findOne ()

Conditional Query

Select * from users where name like "% Joe %"

Db. users. find ({name:/Joe /})

Fuzzy search

Select * from users where name like "Joe %"

Db. users. find ({name:/^ Joe /})

Fuzzy search

Select count (1) from users

Db. users. count ()

Obtain the number of table records

Select count (1) from users where age> 30

Db. users. find ({age: {'$ gt': 30}). count ()

Obtain the number of table records

Select DISTINCT last_name from users

Db. users. distinct ('Last _ name ')

Remove duplicate values

Select * from users order by name

Db. users. find (). sort ({name:-1 })

Sort

Select * from users order by name DESC

Db. users. find (). sort ({name:-1 })

Sort

EXPLAIN select * from users where z = 3

Db. users. find ({z: 3}). explain ()

Obtain the storage path

Update users set a = 1 where B = 'Q'

Db. users. update ({B: 'q'}, {$ set: {a: 1 }}, false, true)

Update record

Update users set a = a + 2 where B = 'Q'

Db. users. update ({B: 'q'}, {$ inc: {a: 2 }}, false, true)

Update record

Delete from users where z = "abc"

Db. users. remove ({z: 'abc '})

Delete record

Db. users. remove ()

Delete all records

Drop database if exists test;

Use test

Db. dropDatabase ()

Delete Database

Drop table if exists test;

Db. mytable. drop ()

Delete table/collection

Db. addUser ('test', 'test ')

Add User

ReadOnly --> false

Db. addUser ('test', 'test', true)

Add User

ReadOnly --> true

Db. addUser ("test", "test222 ")

Change Password

Db. system. users. remove ({user: "test "})

Or db. removeUser ('test ')

Delete a user

Use admin

Superuser

Db. auth ('test', 'test ')

User authorization

Db. system. users. find ()

View User List

Show users

View All Users

Db. printCollectionStats ()

View the status of each collection

Db. printReplicationInfo ()

View master-slave replication status

Show profile

View profiling

Db. copyDatabase ('mail _ addr ', 'mail _ addr_tmp ')

Copy Database

Db. users. dataSize ()

View collection data size

Db. users. totalIndexSize ()

Query index size

Mongodb syntax description

There are many mongodb syntaxes, such as multi-column indexes, which can be used to calculate functions during queries and support multi-condition queries. However, multi-table queries are currently not supported, you can try to solve the problem of multi-Table query through data redundancy. Example.

Query all colls data
Db. colls. find () // select * from colls
Query by specified conditions
Db. colls. find ({'last _ name': 'Smith '}); // select * from colls where last_name = 'Smith'
Specify multi-condition Query
Db. colls. find ({x: 3, y: "foo"}); // select * from colls where x = 3 and y = 'foo'

Query by specified condition range
Db. colls. find ({j: {$ ne: 3}, k: {$ gt: 10}); // select * from colls where j! = 3 and k> 10

The query does not include any content.
Db. colls. find ({}, {a: 0}); // query all data except that a is 0.

Supported <, <=,>,> =, and must be replaced by $ lt, $ lte, $ gt, and $ gte.
Db. colls. find ({"field": {$ gt: value }});
Db. colls. find ({"field": {$ lt: value }});
Db. colls. find ({"field": {$ gte: value }});
Db. colls. find ({"field": {$ lte: value }});

You can also query the range of a field.
Db. colls. find ({"field": {$ gt: value1, $ lt: value2 }});

Not equal to $ ne
Db. colls. find ({x :{$ ne: 3 }});

Character $ in for in Query
Db. colls. find ({"field": {$ in: array }});
Db. colls. find ({j: {$ in: [2, 4, 6]});

Not in query character $ nin
Db. colls. find ({j: {$ nin: [2, 4, 6]});

Mod query character $ mod
Db. colls. find ({a: {$ mod: [10, 1]}) // where a % 10 = 1

$ All Query
Db. colls. find ({a :{$ all: [2, 3] }}); // when specifying a to satisfy any value in the array

$ Size Query
Db. colls. find ({a: {$ size: 1}); // queries the number of objects. This query queries records with the number of sub-objects of a being 1.

$ Exists Query
Db. colls. find ({a: {$ exists: true}); // data of object
Db. colls. find ({a :{$ exists: false }}); // The data of object a does not exist.

$ Type query $ type value: type value of bsonhttp: // bsonspec.org/data
Db. colls. find ({a: {$ type: 2}); // match data of the string type as
Db. colls. find ({a: {$ type: 16}); // match a as int type data

Match with regular expressions
Db. colls. find ({name:/acme. * corp/I}); // similar to like

Embedded object query
Db. colls. find ({"author. name": "joe "});

Version 1.3.3 and later include $ not query
Db. colls. find ({name: {$ not:/acme. * corp/I }});
Db. colls. find ({a :{$ not :{$ mod: [10, 1] }}});

Sort () sorting
Db. colls. find (). sort ({ts:-1}); // 1 is in ascending order. 2 is in descending order.

Limit () limit the number of returned data queries
Db. colls. find (). limit (10)

Skip () skips some data
Db. colls. find (). skip (10)

Snapshot () snapshot ensures that no duplicate data is returned or objects are lost

Count () count the number of queried objects
Db. students. find ({'address. state': 'CA'}). count (); // High Efficiency
Db. students. find ({'address. state': 'CA'}). toArray (). length; // very inefficient

The group () function for query result grouping is similar to the group by function in SQL.
Distinct () returns a non-repeated Value

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.