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

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