mongodb出征篇——sql增删改查大全

写在前面：mongodb作为一个非关系型数据库，不同于key-value的redis、有着类似于关系数据库的sql语言，并且支持灵活的存储数据类型。但是也存在着缺点，比如表与表之间的join关系、中文全文索引就不行。
环境：win7、mongodb:3.2.11

一、数据类型的对弈

基本概念	mysql	mongo
	数据库(database)	db
	表(table)	collection
	行(rows)	document(Bson)
	列(field)	field
	索引	index
	聚合	group、count等
	主键	自动设为_id

二、sql的对比

1. 创建类

操作类型	mysql	mongo
创建	CREATE TABLE tablename(…)	db.tablename.insert({name:’hebby’,age:10}) #insert时候自动创建表；也可以执行db.createCollection(‘tablename’)
更新(增加列)	ALTER TABLE tablename ADD news DATETIME	db.users.update({},{$set:{news:new Date()}},{muliti:true})
更新(删除列)	ALTER TABLE tablename DROP COLUMN news	db.users.update({},{$set:{news:”}},{muliti:true})
创建索引	CREATE INDEX id_user_name ON users(name)	db.createIndex({name:1},{name:’id_user_name’})
删除表	DROP TABLE users	db.users.drop()

2. 插入类

操作类型	mysql	mongodb
单个插入	INSERT INTO users(id,name) VALUES (2,’lili’);	db.users.insert({id:2,name:’lili’})
批量插入	INSERT INTO users(id,name) VALUES(2,’lili’),(3,’mimi’)	db.users.insertMany([{id:2,name:’lili’},{id:3,name:’mimi’}])
批量插入	INSERT INTO users(id,name) VALUES(2,’lili’),(3,’mimi’)	db.users.insert([{id:2,name:’lili’},{id:3,name:’mimi’}])

3. 查询类

基础类型	mysql	mongodb
	SELECT * FROM users	db.users.find()
	SELECT id,name from users	db.users.find({},{id:1,name:1,_id:0})
	SELECT * FROM users WHERE name=’lili’	db.users.find({name:’lili’})
	SELECT * FORM users WHERE name !=’lili’	db.users.find({name:{$ne:’lili’}})
	SELECT * FROM users WHERE name=’lili’ AND age=10	db.users.find({name:’lili’,’age’:10})
	SELECT * FROM users WHERE name=’lili’ OR age=10	db.users.find({$or:[{name:’lili’},{age:10}]})
	SELECT * FROM users WHERE name='lili' AND age<10 OR type='off'	db.users.find({name:'lili',$or:[{age:$gt:{age:10}},{type:'off'}]})
	SELECT * FROM users WHERE age>10	db.users.find({age:{$gt:10}})
	SELECT * FROM users WHREE age<10	db.users.find({age:{$lt:10}})
	SELECT * FROM users WHERE name like “%li%”	db.users.find({name:/li/})
	SELECT * FROM users WHERE name like “li%”	db.users.find({name:/^li/})
	SELECT * FROM users WHERE name=’lili’ ORDER BY id DESC	db.user.find({name:’lili’}).sort({id:-1})
	SELECT * FROM users WHERE name=’lili’ ORDER BY id ASC	db.user.find({name:’lili’}).sort({id:1})
	SELECT COUNT(*) FROM users	db.users.find().count()
	SELECT COUNT(num) FROM users	db.users.find({num:{$exists:true}}).count()
	SELECT COUNT(num) FROM users WHERE age<10	db.users.find({age:{$gt:10}}).count()
	SELECT DISTINCT(name) FROM uses	db.users.distinct(name)
	SELECT * FROM users LIMIT 1	db.users.findOne()
	SELECT * FROM uses	db.users.findMany()
	SELECT * FROM uses limit 2 SKIP 10	db.users.find().limit(2).skip(10)

4.更新类

操作类型	mysql	mongodb
	UPDATE users SET age=10 WHERE name=’lili’	db.users.update({name:’lili’},{$set:{age:10}})
	UPDATE users SET age=age+10 WHERE name=’lili’	db.users.update({name:’lili’},{$inc:{age:10}})

5.删除类

操作类型	mysql	mongodb
	DELECT FROM users WHERE name=’lili’	db.users.remove({name:’lili’})
	DELECT FROM users	db.users.remove({})