A detailed _php example based on MySQL to mongodb simple comparison table

Source: Internet
Author: User
Tags comparison table findone
Inquire:
Mysql:
SELECT * from user
Mongo:
Db.user.find ()
MySQL:
SELECT * FROM user WHERE name = ' Starlee '
Mongo:
Db.user.find ({' name ': ' Starlee '})
Insert:
Mysql:
INSERT inot User (' name ', ' age ') VALUES (' Starlee ', 25)
Mongo:
Db.user.insert ({' name ': ' Starlee ', ' Age ': 25})
If you want to add a field to MySQL, you must:
ALTER TABLE user ....
But in MongoDB you just need:
Db.user.insert ({' name ': ' Starlee ', ' age ': +, ' email ': ' starlee@starlee.com '})
Delete:
Mysql:
DELETE * from user
Mongo:
Db.user.remove ({})
MySQL:
DELETE from user WHERE age < 30
Mongo:
Db.user.remove ({' age ': {$lt: 30}})
$GT: >; $gte: >=; $LT: <; $lte: <=; $ne:! =
Update:
Mysql:
UPDATE user SET ' age ' = * * WHERE ' name ' = ' Starlee '
Mongo:
Db.user.update ({' name ': ' Starlee '}, {$set: {' age ': 36}})
MySQL:
UPDATE user SET ' age ' = ' age ' + 3 WHERE ' name ' = ' Starlee '
Mongo:
Db.user.update ({' name ': ' Starlee '}, {$inc: {' Age ': 3}})
MySQL:
SELECT COUNT (*) from user WHERE ' name ' = ' Starlee '
Mongo:
Db.user.find ({' name ': ' Starlee '}). Count ()
MySQL:
SELECT * from user limit 10,20
Mongo:
Db.user.find (). Skip. Limit (20)
MySQL:
SELECT * from user WHERE ' age ' in (25, 35,45)
Mongo:
Db.user.find ({' age ': {$in: [25, 35, 45]}})
MySQL:
SELECT * from user ORDER by age DESC
Mongo:
Db.user.find (). Sort ({' Age ':-1})
MySQL:
SELECT DISTINCT (name) from user WHERE age > 20
Mongo:
Db.user.distinct (' name ', {' age ': {$lt: 20}})
MySQL:
SELECT name, sum (marks) from the user GROUP by name
Mongo:
Db.user.group ({
Key: {' name ': true},
Cond: {' name ': ' foo '},
Reduce:function (Obj,prev) {prev.msum + = Obj.marks;},
Initial: {msum:0}
});
MySQL:
SELECT name from user WHERE age < 20
Mongo:
Db.user.find (' This.age < 20′, {name:1})
Found a lot of people in the search MongoDB loop Insert data, below the MongoDB loop to insert the data method is added below:
for (Var i=0;i<100;i++) Db.test.insert ({uid:i,uname: ' Nosqlfan ' +i});
the above inserts 100 data at once, the approximate structure is as follows:
{"_id": ObjectId ("4c876e519e86023a30dde6b8″)," UID ":," uname ":" Nosqlfan55″}
{"_id": ObjectId ("4c876e519e86023a30dde6b9″"), "UID": "uname": "Nosqlfan56″}
{"_id": ObjectId ("4c876e519e86023a30dde6ba"), "UID": page, "uname": "Nosqlfan57″}
{"_id": ObjectId ("4C876E519E86023A30DDE6BB"), "UID": +, "uname": "Nosqlfan58″}
{"_id": ObjectId ("4C876E519E86023A30DDE6BC"), "UID":, "uname": "Nosqlfan59″}
{"_id": ObjectId ("4C876E519E86023A30DDE6BD"), "UID": $, "uname": "Nosqlfan60″}
Simple Comparison Table
SQL Statement Mongo Query Language Statement
CREATE TABLE USERS (a number, b number) implicit; Can is done explicitly
INSERT into USERS VALUES (Db.users.insert) ({a:1,b:1})
SELECT A, B from users Db.users.find ({}, {a:1,b:1})
SELECT * from Users Db.users.find ()
SELECT * from Users WHERE age=33 db.users.find ({age:33})
SELECT A, b from users WHERE age=33 db.users.find ({age:33}, {a:1,b:1})
SELECT * from the users WHERE age=33 ORDER by name Db.users.find ({age:33}). Sort ({name:1})
SELECT * from the Users WHERE age>33 db.users.find ({' age ': {$gt: 33}})})
SELECT * from the Users WHERE age<33 db.users.find ({' age ': {$lt: 33}})})
SELECT * from the users WHERE name like "%joe%" Db.users.find ({name:/joe/})
SELECT * from the users WHERE name like "joe%" Db.users.find ({name:/^joe/})
SELECT * from the users WHERE age>33 and age<=40 db.users.find ({' age ': {$gt:, $lte: 40}})})
SELECT * from the users ORDER by name DESC Db.users.find (). Sort ({name:-1})
CREATE INDEX myindexname on users (name) Db.users.ensureIndex ({name:1})
CREATE INDEX myindexname on users (Name,ts DESC) db.users.ensureIndex ({name:1,ts:-1})
SELECT * from Users WHERE a=1 and b= ' Q ' db.users.find ({a:1,b: ' Q '})
SELECT * from users limit ten db.users.find (). Limit (. SKIP (20)
SELECT * from users WHERE a=1 or b=2 db.users.find ({$or: [{a:1}, {b:2}]})
SELECT * from Users LIMIT 1 Db.users.findOne ()
EXPLAIN SELECT * from users WHERE z=3 db.users.find ({z:3}). EXPLAIN ()
SELECT DISTINCT last_name from Users db.users.distinct (' last_name ')
SELECT COUNT (*y) from Users Db.users.count ()
SELECT count (*y) from the users where age > Db.users.find ({age: {' $GT ': $}}). COUNT ()
SELECT count (age) from the users Db.users.find ({age: {' $exists ': true}}). Count ()
UPDATE users SET a=1 WHERE b= ' Q ' db.users.update ({b: ' Q '}, {$set: {a:1}}, False, True)
UPDATE users SET a=a+2 WHERE b= ' Q ' db.users.update ({b: ' Q '}, {$inc: {a:2}}, False, True)
DELETE from users WHERE z= "abc" Db.users.remove ({z: ' abc '});
###################################################
One, operator
Operators believe everyone must know, is equal to, greater than, less than, not equal to, greater than or equal to, less than equals, but in MongoDB can not directly use these operators. The operators in MongoDB are represented in this way:
(1) $gt > (greater than)
(2) $lt < (less than)
(3) $gte >= (greater than or equal)
(4) $lt <= (less than or equal)
(5) $ne! = (Not equal)
(6) $in in (included)
(7) $nin not in (not included)
(8) $exists exist (whether the field exists)
(9) Add Value to a Field field $inc to a number
(Ten) $set is the equivalent of SQL set field = value
(one) $unset is to delete a field
$push Append value To field, field must be an array type, and if field does not exist, a new array type will be added.
$pushAll with $push, it is possible to append multiple values to an array field at a time
$addToSet adds a value to the array, and only if the value is not in the array.
$pop Delete the last value: {$pop: {field:1}} deletes the first value: {$pop: {field:-1}} Note that only one value can be deleted, that is, only 1 or-1, not 2 or--and two. MongoDB 1.1 and later versions are only available
$pull Delete an equal value from the array field
$pullAll with $pull, you can delete multiple values in an array at once
(18) The $ operator is his own meaning, which represents a condition of finding an array inside of himself. This compares the mouth, will not say.
ii. curd Increase, change, read and delete
Increase
Copy CodeThe code is as follows:
Db.collection->insert ({' Name ' = ' Caleng ', ' email ' = ' admin#admin.com '});

is not gray often simple ah, yes is so simple, it has no field restrictions, you can arbitrarily name, and insert data
Copy CodeThe code is as follows:
Db.collection.update ({"Count": {$gt: 1}}, {$set: {"test2": "OK"}}); Only the first record greater than 1 is updated
Db.collection.update ({"Count": {$gt: 3}}, {$set: {"test2": "OK"}},false,true); Records greater than 3 are all updated.
Db.collection.update ({"Count": {$gt: 4}}, {$set: {"Test5": "OK"}},true,false); Records greater than 4 are only added to the first one.
Db.collection.update ({"Count": {$gt: 5}}, {$set: {"Test5": "OK"}},true,true); Records greater than 5 are all added in.

Enquiry
Copy CodeThe code is as follows:
Db.collection.find (Array (' name ' = ' bailing '), array (' email ' = ' email@qq.com '))
Db.collection.findOne (Array (' name ' = ' bailing '), array (' email ' ' email@qq.com '))

You can see the query I used two different ways of writing, which is why, in fact, this is the same as cooking is the same, put different spices, fried vegetables are different flavor. Let's talk about the different effects of the two spices.
FindOne () returns only one document object, and find () returns a list of collections.
That is to say, we just want to check the details of a particular piece of data, we can use FindOne ();
If you want to query a certain set of information, such as a news list, we can function as find ();
Then I think you will think that I want to sort this list, no problem MongoDB will serve you wholeheartedly
Copy CodeThe code is as follows:
Db.collection.find (). Sort ({age:1}); In chronological order
Db.collection.find (). Sort ({age:-1}); In reverse chronological order
Db.collection.count (); Get the total number of data
Db.collection.limit (1); Where to begin the data collection
Db.collection.skip (10); Where to end the data
This allows us to implement an operation that takes 10 data and sorts it.

Delete
Delete has two actions remove () and drop ()
Copy CodeThe code is as follows:
Db.collection.remove ({"Name", ' Jerry '})//delete specific data
Db.collection.drop ()//delete all data in the collection

DISTINCT operation
Copy CodeThe code is as follows:
Db.user.distinct (' name ', {' age ': {$lt: 20}})

2. Familiar with MongoDB data manipulation statements, class SQL
Database operation syntax
MONGO--path
Db. AddUser (username,password) Add user
Db.auth (Usrename,password) setting Up Database connection validation
Db.clonedatabase (fromhost) cloning a database from the target server
DB.COMMANDHELP (name) returns the Help for the command
Db.copydatabase (fromdb,todb,fromhost) Copy database fromdb---Source database name, TODB---target database name, fromhost---source database server address
Db.createcollection (name,{size:3333,capped:333,max:88888}) creates a data set that is equivalent to a table
Db.currentop () cancels the current operation of the current library
Db.dropdatabase () Delete the current database
Db.eval (Func,args) Run code server-side
Db.getcollection (CNAME) gets a collection of data, same usage: db[' cname '] or db.cname
Db.getcollenctionnames () Gets a list of all data collection names
Db.getlasterror () returns the last Error prompt message
Db.getlasterrorobj () returns the last Error object
Db.getmongo () Gets the connection object of the current server get the server connection object
Db.getmondo (). Setslaveok () Allow this connection to read from then Nonmaster membr of a replica pair
Db.getname () returns the name of the operation database
Db.getpreverror () returns the previous Error object
Db.getprofilinglevel ()? What level
Db.getreplicationinfo ()? What information
DB.GETSISTERDB (name) get the db at the same server as this onew
Db.killop () Stop (kill) The current operation in the current library
Db.printcollectionstats () returns the current library's dataset status
Db.printreplicationinfo ()
Db.printslavereplicationinfo ()
Db.printshardingstatus () returns whether the current database is a shared database
Db.removeuser (username) Delete User
Db.repairdatabase () repairing the current database
Db.reseterror ()
Db.runcommand (cmdobj) run a database command. If Cmdobj is a string, turns it into {cmdobj:1}
Db.setprofilinglevel (level) 0=off,1=slow,2=all
Db.shutdownserver () Close the current service program
Db.version () returns the version information for the current program
Data Set (table) manipulation syntax
Db.linlin.find ({id:10}) returns the DataSet id=10 the Linlin dataset
Db.linlin.find ({id:10}). Count () returns the total number of data id=10 the Linlin dataset
Db.linlin.find ({id:10}). Limit (2) returns the DataSet from the second data set id=10 the dataset of the Linlin dataset
Db.linlin.find ({id:10}). Skip (8) returns the dataset from 0 to eighth for the DataSet id=10 the Linlin dataset
Db.linlin.find ({id:10}). Limit (2). Skip (8) Returns the data from the second to eighth data sets id=1= the dataset of the Linlin dataset
Db.linlin.find ({id:10}). Sort () returns the sorted data set id=10 the Linlin dataset
Db.linlin.findOne ([query]) returns a piece of data that meets the criteria
Db.linlin.getDB () returns the name of the database to which this dataset belongs
Db.linlin.getIndexes () returns the index information for some datasets
Db.linlin.group ({key:...,initial:...,reduce: ... [, cond: ...]})
Db.linlin.mapReduce (Mayfunction,reducefunction, )
Db.linlin.remove (query) deletes a single piece of data in a data set
Db.linlin.renameCollection (newName) Rename some data set names
Db.linlin.save (obj) inserts a piece of data into the data set
Db.linlin.stats () Returns the state of this dataset
Db.linlin.storageSize () returns the storage size of this dataset
Db.linlin.totalIndexSize () returns the index file size of this dataset
Db.linlin.totalSize () returns the total size of some datasets
Db.linlin.update (Query,object[,upsert_bool]) update a data set in this dataset
Db.linlin.validate () validates this data set
Db.linlin.getShardVersion () returns the data set share version number
  • Related Article

    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.