MongoDB vs. mysql command comparison

MongoDB vs. mysql command comparison

The traditional relational database is usually composed of three hierarchical concepts of database, table, record, and MongoDB is made up of database, collection (collection), Document object composed of three levels. MongoDB is for tables in relational databases, but there is no concept of columns, rows, and relationships in the collection, which embodies the nature of pattern freedom.

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 *.	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	Querying master-Slave status
	Implicit setup: Use MyDB Db.user.insert (' name ': ' Tang '); Show DBS can see that the MyDB library has been built	Build Library
Create table Users (a int, b int)	Db.createcollection ("Mycoll", {capped:true, size:100000}) Another: The table can be created implicitly (Db.mycoll.save (a))	Create a table
Insert into users values (1, 1)	Db.users.insert ({a:1, b:1})	Inserting records
	Db.user.save (Val);	Save data
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
Drop database IF EXISTS test;	Use test Db.dropdatabase ()	Deleting a database
Delete from users where z= "abc"	Db.users.remove ({z: ' abc '})	Deleting records
	Db. Users.remove ()	Delete all the records
drop table IF EXISTS test;	Db.mytable.drop ()	Delete Table/collection
Select A, B from users	Db.users.find ({},{a:1, b:1})	Query table
Select * from users	Db.users.find ()	Query table
	Db.users.findOne ({name: "MONGO"})	Query a single
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 table Record Count
Select COUNT (1) from users where age>30	Db.users.find ({age: {' $GT ': $}}). Count ()	Get table Record Count
Select DISTINCT last_name from Users	Db.users.distinct (' last_name ')	Remove duplicate values
SELECT * from the 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
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
	Db.users.dropindex ()	Delete all indexes
Drop Index Idx_name	Db.users.dropindex ({firstname:1})	Delete the specified index
	Db.users.find ({FIRSTNAME:FN}). Hint ({firstname:1})	Specify index Query
	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 the collection data
	Db. Users.totalindexsize ()	Size of the query index
	Db.user.copyTo ("New_user")	Copy table

MongoDB syntax

MongoDB benefits Quite a lot, such as multi-column index, query can use some statistical functions, support multi-conditional query, but the current multi-table query is not supported, you can find a way to solve the problem of multi-table query through data redundancy.

MongoDB is very rich in data manipulation, here are some examples, the content is mostly from official documents, in addition to some of their own understanding.

Query Colls all data

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

Querying by specifying criteria

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

Specify a multi-criteria query

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

Specify criteria range Query

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

Query does not include a content

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

Support <, <=, >= query, with symbolic substitution for $lt, $lte, $GT, $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 make a range query for a field

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

Not equal to query character $ne

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

Null Value Handling

Specifies that the query is null for a field:

Db.c2.find ({age:null})

In this case, there will be no field of age records are also queried, if you want to exclude this record,

Db.c2.find ({age:{"$in": [null], "$exists": True}})

In query with character $in

Db.colls.find ({"field": {$in: Array}});

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

Not in query with character $nin

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

Character $mod for modulo query

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

$all Query

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

$size Query

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

$exists Query

Db.colls.find ({A: {$exists: true}}); There is data for the A object

Db.colls.find ({A: {$exists: false}}); There is no data for the A object

$type Query the type value of the $type value to bsonhttp://bsonspec.org/data

Db.colls.find ({A: {$type: 2}}); Match A to string type data

Db.colls.find ({A: {$type: 16}}); Match A to int type data

Using regular expression matching

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

Db.users.find ({name:{$not:/^b.*/}}); Query does not match name=b* with the record

Db.users.find ({name:{/^b.*/}}); Query matches name=b* with the record

Inline Object Query

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

1.3.3 versions and later versions include $not queries

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 ascending-1 is descending

Limit () returns the number of restricted query data

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

Skip () skips some data

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

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

Count () counts the number of query objects

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

Db.students.find ({' address.state ': ' CA '}). ToArray (). length;//is inefficient

Group () is similar to grouping query results and GROUPBY functions in SQL

Distinct () returns non-repeating values

MongoDB vs. mysql command comparison