Comparison between the query script Operation Commands of Mongodb and Mysql

1. Query all records
Db. userInfo. find ();
Equivalent to: select * from userInfo;
By default, 20 records are displayed on each page. If no record is displayed, you can use the it iteration command to query data on the next page. Note: The it command cannot contain ";"
However, you can set the size of data displayed on each page. Use DBQuery. shellBatchSize = 50 to display 50 records on each page.
 
2. Query duplicate data of a column in the current clustering set after removal
Db. userInfo. distinct ("name ");
The same data in name is filtered out.
Equivalent to: select distict name from userInfo;
 
3. Query records with age = 22
Db. userInfo. find ({"age": 22 });
Equivalent to: select * from userInfo where age = 22;
 
4. Query records of age> 22
Db. userInfo. find ({age: {$ gt: 22 }});
Equivalent to: select * from userInfo where age> 22;
 
5. Query records of age <22
Db. userInfo. find ({age: {$ lt: 22 }});
Equivalent to: select * from userInfo where age <22;
 
6. Query records with age> = 25
Db. userInfo. find ({age: {$ gte: 25 }});
Equivalent to: select * from userInfo where age> = 25;
 
7. Query records of age <= 25
Db. userInfo. find ({age: {$ lte: 25 }});
 
8. Query age> = 23 and age <= 26
Db. userInfo. find ({age: {$ gte: 23, $ lte: 26 }});
 
9. Query data whose name contains mongo
Db. userInfo. find ({name:/mongo /});
// Equivalent to %
Select * from userInfo where name like '% mongo % ';
 
10. Query names starting with mongo
Db. userInfo. find ({name:/^ mongo /});
Select * from userInfo where name like 'Mongo % ';
 
11. Query the name and age data of a specified column
Db. userInfo. find ({}, {name: 1, age: 1 });
Equivalent to: select name, age from userInfo;
Of course, "true" or "false" can be used for name. If "true" is used, "river name: 1" has the same effect. If "false" is used, "name" is excluded and column information other than "name" is displayed.
 
12. Query the name and age data of a specified column. age> 25
Db. userInfo. find ({age: {$ gt: 25 },{ name: 1, age: 1 });
Equivalent to: select name, age from userInfo where age> 25;
 
13. Sort by age
Ascending order: db. userInfo. find (). sort ({age: 1 });
Descending order: db. userInfo. find (). sort ({age:-1 });
 
14. Query data with name = zhangsan and age = 22
Db. userInfo. find ({name: 'hangsan', age: 22 });
Equivalent to: select * from userInfo where name = 'hangsan' and age = '22 ';
 
15. Query the first five data entries
Db. userInfo. find (). limit (5 );
Equivalent to: select top 5 * from userInfo;
 
16. Query 10 data records later
Db. userInfo. find (). skip (10 );
Equivalent to: select * from userInfo where id not in (
Select top 10 * from userInfo
);
 
17. Query data between 5-10
Db. userInfo. find (). limit (10). skip (5 );
It can be used for paging. limit is pageSize, and skip is the page * pageSize
 
18. or and query
Db. userInfo. find ({$ or: [{age: 22}, {age: 25}]});
Equivalent to: select * from userInfo where age = 22 or age = 25;
 
19. Query the first data entry.
Db. userInfo. findOne ();
Equivalent to: select top 1 * from userInfo;
Db. userInfo. find (). limit (1 );
 
20. Query the number of records in a result set
Db. userInfo. find ({age: {$ gte: 25}). count ();
Equivalent to: select count (*) from userInfo where age> = 20;
 
21. Sort by column
Db. userInfo. find ({sex: {$ exists: true}). count ();
Equivalent to: select count (sex) from userInfo

Next, let's take a look at the command comparison between the two.

 

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