Home > Article > Backend Development > Detailed explanation of simple comparison table based on MySQL to MongoDB_PHP tutorial
Detailed explanation of simple comparison table based on MySQL to MongoDB_PHP tutorial
- WBOYOriginal
- 2016-07-21 15:09:54715browse
Query:
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 in MySQL, you must:
ALTER TABLE user….
But in MongoDB you only need:
db.user.insert({'name' : 'starlee', 'age' : 25, '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` = 36 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(10).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 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})
I found that many people are searching for MongoDB to insert data in a loop. Let’s insert data into MongoDB in a loop. The method is added below:
for(var i=0;i<100;i++)db.test.insert({uid:i,uname:'nosqlfan'+i});
The above inserts one hundred pieces of data at a time, with the approximate structure as follows:
{ “_id” : ObjectId(“4c876e519e86023a30dde6b8″), “uid” : 55, “uname” : “nosqlfan55″ }
{ “_id” : ObjectId(“4c876e519e86023a30dde6b9″), “uid” : 56, “uname” : “nosqlfan56″ }
{ “_id” : ObjectId(“4c876e519e86023a30dde6ba”), “uid” : 57, “uname” : “nosqlfan57″ }
{ “_id” : ObjectId(“4c876e519e86023a30dde6bb”), “uid” : 58, “uname” : “nosqlfan58″ }
{ “_id” : ObjectId(“4c876e519e86023a30dde6 bc” ), “uid” : 59, “uname” : “nosqlfan59″ }
{ “_id” : ObjectId(“4c876e519e86023a30dde6bd”), “uid” : 60, “uname” : “nosqlfan60″ }
Simple comparison table
SQL Statement 🎜>INSERT INTO USERS VALUES(1 ,1) db.users.insert({a:1,b:1})
SELECT a,b FROM users 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 users WHERE age=33 ORDER BY name db.users.find({age:33}).sort({name:1})
SELECT * FROM users WHERE age>33 db.users.find({'age':{$gt:33}})})
SELECT * FROM users WHERE age<33 db.users.find({'age':{$lt:33}})})
SELECT * FROM users WHERE name LIKE "%Joe%" db.users.find({name:/Joe/})
SELECT * FROM users WHERE name LIKE "Joe%" db.users.find({name:/^Joe/})
SELECT * FROM users WHERE age>33 AND age<=40 db.users.find({'age':{$gt:33,$lte:40}})})
SELECT * FROM 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 10 SKIP 20 db.users.find().limit(10).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 users where AGE > 30 db.users.find({age: {'$gt': 30}}).count()
SELECT COUNT(AGE) from 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'});
###################################################
一、操作符
操作符相信大家肯定都知道了，就是等于、大于、小于、不等于、大于等于、小于等于，但是在mongodb里不能直接使用这些操作符。在mongodb里的操作符是这样表示的：
(1) $gt > (大于)
(2) $lt < (小于)
(3) $gte >= (大于等于)
(4) $lt <= (小于等于)
(5) $ne != (不等于)
(6) $in in (包含)
(7) $nin not in (不包含)
(8) $exists exist (字段是否存在)
(9) $inc 对一个数字字段field增加value
(10) $set 就是相当于sql的set field = value
(11) $unset 就是删除字段
(12) $push 把value追加到field里面去，field一定要是数组类型才行，如果field不存在，会新增一个数组类型加进去
(13) $pushAll 同$push,只是一次可以追加多个值到一个数组字段内
(14) $addToSet 增加一个值到数组内，而且只有当这个值不在数组内才增加。
(15) $pop 删除最后一个值：{ $pop : { field : 1 } }删除第一个值：{ $pop : { field : -1 } }注意，只能删除一个值，也就是说只能用1或-1，而不能用2或-2来删除两条。mongodb 1.1及以后的版本才可以用
(16) $pull 从数组field内删除一个等于value值
(17) $pullAll 同$pull,可以一次删除数组内的多个值
(18) $ 操作符 是他自己的意思，代表按条件找出的数组里面某项他自己。这个比较坳口，就不说了。
二、CURD 增、改、读、删
增加
db.collection->insert({'name' => 'caleng', 'email' => 'admin#admin.com'});
是不是灰常简单呀，对就是这么简单，它没有字段的限制，你可以随意起名，并插入数据
db.collection.update( { "count" : { $gt : 1 } } , { $set : { "test2" : "OK"} } ); 只更新了第一条大于1记录
db.collection.update( { "count" : { $gt : 3 } } , { $set : { "test2" : "OK"} },false,true ); 大于3的记录 全更新了
db.collection.update( { "count" : { $gt : 4 } } , { $set : { "test5" : "OK"} },true,false ); 大于4的记录 只加进去了第一条
db.collection.update( { "count" : { $gt : 5 } } , { $set : { "test5" : "OK"} },true,true ); 大于5的记录 全加进去
Query
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 that I used two different ways of writing the query. Why is this? In fact, this is the same as The cooking is the same, but with different seasonings, the stir-fried dishes have different flavors. Let me tell you the different functions of these two seasonings.
findOne() only returns a document object, and find() returns a collection list.
That is to say, for example, if we only want to check the detailed information of a specific piece of data, we can use findOne();
If we want to query a certain set of information, such as a news list, we can It can be used as find();
Then I think everyone will think that I want to sort this list. No problem mongodb will serve you wholeheartedly
db.collection.find().sort({age:1}); //Arrange in positive order according to age
db.collection.find( ).sort({age:-1}); //Arrange in reverse order by age
db.collection.count(); //Get the total number of data
db.collection.limit(1); //Get data The starting position of
db.collection.skip(10); //Get the end position of the data
//In this way, we have implemented an operation of taking 10 pieces of data and sorting them.
Delete
Deletion has two operations remove() and drop()
db.collection.remove({"name",'jerry'}) //Delete specific data
db.collection.drop() //Delete all data in the collection
distinct operation
db.user.distinct('name', {'age': {$lt : 20}})
2. Familiar with MongoDB data operation statements, sql-like
database operation syntax
mongo --path
db.AddUser(username,password) Add user
db.auth(usrename,password) Set database connection verification
db.cloneDataBase(fromhost) Clone a database from the target server
db.commandHelp(name) returns the help for the command
db.copyDatabase(fromdb,todb,fromhost) Copy the database fromdb---source database name, todb---target database name, fromhost---source database server address
db.createCollection(name,{size:3333, capped:333,max:88888}) Create a data set, which is equivalent to a table
db.currentOp() Cancel 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 data collection, the same usage: db['cname'] or db.cname
db.getCollenctionNames() gets the names of all data collections List
db.getLastError() returns the last error message
db.getLastErrorObj() returns the last error object
db.getMongo() gets the current server connection object 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 database when operating
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() Return the data set status of the current library
db.printReplicationInfo()
db.printSlaveReplicationInfo()
db.printShardingStatus() Return whether the current database is a shared database
db.removeUser(username) Delete user
db.repairDatabase() Repair 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() Shut down the current service program
db.version() returns the version information of the current program
Dataset (table) operation syntax
db.linlin.find({id:10}) returns linlin Data set with data set ID=10
db.linlin.find({id:10}).count() returns the total number of data with linlin data set ID=10
db.linlin.find({id:10 }).limit(2) Returns the data set starting from the second item of the data set with linlin data set ID=10
db.linlin.find({id:10}).skip(8) Returns the linlin data set ID =10 data set from 0 to the eighth data set
db.linlin.find({id:10}).limit(2).skip(8) returns the data set of linlin data set ID=1= The data from the second to the eighth item
db.linlin.find({id:10}).sort() returns the sorted data set of linlin data set ID=10
db.linlin.findOne([ query]) Returns a piece of data that meets the conditions
db.linlin.getDB() Returns the database name to which this data set belongs
db.linlin.getIndexes() Returns the index information of some data sets
db.linlin .group({key:...,initial:...,reduce:...[,cond:...]})
db.linlin.mapReduce(mayFunction,reduceFunction,
db.linlin.remove(query) Delete a piece of data in the data set
db.linlin.renameCollection(newName) Rename some data set names
db.linlin.save(obj) Insert a piece of data into the data set Data
db.linlin.stats() Returns the status of this data set
db.linlin.storageSize() Returns the storage size of this data set
db.linlin.totalIndexSize() Returns the index of this data set File size
db.linlin.totalSize() Returns the total size of some data sets
db.linlin.update(query,object[,upsert_bool]) Updates a piece of data in this data set
db.linlin. validate() validates this data set
db.linlin.getShardVersion() returns the shared version number of the data set