MongoDB Series Tutorial (v): MONGO Grammar and MySQL grammar contrast learning _mongodb

We are always in contrast to see their strengths and weaknesses, for MongoDB is also the same, comparative learning let us as soon as possible to grasp the basic knowledge about MongoDB.

MongoDB vs. mysql command

A relational database is generally composed of three levels of databases (database), tables (table), records (record). Non-relational database MongoDB are composed of three levels of databases (database), Collections (collection), document objects (documents). MongoDB for tables in relational databases, there is no relationship concept between rows and columns, which embodies the free characteristics of the pattern.

Syntax commands as shown in the following table

Mysql	Mongodb	Description
Mysqld	Mongod	Server daemon
Mysql	Mongo	Client Tools
Mysqldump	Mongodump	Logical Backup tool
Mysql	Mongorestore	Logical Recovery Tool
	Db.repairdatabase ()	Repairing the database
Mysqldump	Mongoexport	Data Export Tool
Source	Mongoimport	Data Import Tool
Grant * privileges on *.* to ...	Db.adduser () Db.auth ()	New User and Permissions
Show databases	Show DBS	Show Library List
Show tables	Show collections	Show 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}) Another: The table can be created implicitly.	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 table
Select * from users	Db.users.find ()	Query 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: $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 the users where name like "%joe%"	Db.users.find ({name:/joe/})	Fuzzy query
SELECT * from the users where name like "joe%"	Db.users.find ({name:/^joe/})	Fuzzy query
Select COUNT (1) from users	Db.users.count ()	Get the number of table records
Select COUNT (1) from the users where age>30	Db.users.find ({age: {' $gt ':}}). Count ()	Get 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 ()	Get Storage Path
Update users set a=1 where b= ' Q '	Db.users.update ({b: ' Q '}, {$set: {a:1}}, False, True)	Update records
Update users set a=a+2 where b= ' Q '	Db.users.update ({b: ' Q '}, {$inc: {a:2}}, False, True)	Update records
Delete from users where z= "abc"	Db.users.remove ({z: ' abc '})	Delete a 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 User
	Use admin	Super User
	Db.auth (' Test ', ' test ')	User authorization
	Db.system.users.find ()	View the list of users
	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 ()	To view the size of collection data
	Db. Users.totalindexsize ()	Size of query Index

MongoDB Syntax Description

MongoDB syntax many, such as multiple-column index, query can be statistical functions, support for multiple conditions query, but the current query for multiple tables is not supported, you can find ways to solve the problem of multiple table queries through data redundancy. Examples are shown below.

Query Colls all data

Copy Code code as follows:

Db.colls.find ()//select * from Colls

Query by specifying criteria

Copy Code code as follows:

Db.colls.find ({' last_name ': ' Smith '});//select * from Colls where last_name= ' Smith '

Specifying a multiple-condition query

Copy Code code as follows:

Db.colls.find ({x:3, y: "foo"});//select * from Colls where x=3 and y= ' foo '

Specify criteria range Query

Copy Code code as follows:

Db.colls.find ({j: {$ne: 3}, K: {$gt: a});//select * from Colls where j!=3 and k>10

The query does not include a content

Copy Code code as follows:

Db.colls.find ({}, {a:0});//query for all data except a 0

Support for, <=, ", >= query, need to replace the symbol for $LT, $lte, $GT, $gte

Copy Code code as follows:

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 do a range query on a field

Copy Code code as follows:

Db.colls.find ({"field": {$gt: value1, $lt: value2}});

is not equal to the character $ne for the query

Copy Code code as follows:

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

In Query character $in

Copy Code code as follows:

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

Not in query character $nin

Copy Code code as follows:

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

Character $mod for modulo query

Copy Code code as follows:

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

$all Query

Copy Code code as follows:

Db.colls.find ({A: {$all: [2, 3]}});//Specifies that a satisfies any value in the array

$size Query

Copy Code code as follows:

Db.colls.find ({A: {$size: 1}});//To query the number of objects, this query queries a record of the number of child objects of a 1

$exists Query

Copy Code code as follows:

Db.colls.find ({A: {$exists: true}}); Data exists for object a
Db.colls.find ({A: {$exists: false}}); No data exists for object a

$type query $type value is the type value of bsonhttp://bsonspec.org/data

Copy Code code as follows:

Db.colls.find ({A: {$type: 2}}); Match A is a string type of data
Db.colls.find ({A: {$type: 16}}); Match A is type int data

Using regular expression matching

Copy Code code as follows:

Db.colls.find ({name:/acme.*corp/i});//similar to SQL

Inline Object Query

Copy Code code as follows:

Db.colls.find ({"Author.name": "Joe"});

1.3.3 version and later version contains $not query

Copy Code code as follows:

Db.colls.find ({name: {$not:/acme.*corp/i}});
Db.colls.find ({A: {$not: {$mod: [10, 1]}});

Sort () sorting

Copy Code code as follows:

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

Limit () to limit the number of query data returned

Copy Code code as follows:

Db.colls.find (). Limit (10)

Skip () Skip some data

Copy Code code as follows:

Db.colls.find (). Skip (10)

Snapshot () snapshot guarantees no duplicate data return or object loss

Count () to Count query objects

Copy Code code as follows:

Db.students.find ({' address.state ': ' CA '}). Count ();//High efficiency
Db.students.find ({' address.state ': ' CA '}). ToArray (). length;//Inefficient

Group () is a grouping of query results similar to the group by function in SQL
Distinct () returns a value that is not repeated