注意：MongoD语句大小写敏感

1、插入语句

SQL INSERT Statements	MongoDB insertOne() Statements
INSERT INTO table_name(user_id, age,name)VALUES ("bcd001", 45,"A")	db.table_name.insert( { user_id: "bcd001", age: 45, name: "A" })
INSERT INTO table_name(user_id, age,name)VALUES ("bcd001", 45,"A"), ("bcd002", 45,"B")	db.table_name.insert( [{ user_id: "bcd001", age: 45, name: "A" },{ user_id: "bcd002", age: 45, name: "B" }])

2、查询语句

SQL SELECT Statements	MongoDB find() Statements
SELECT * FROM table_name	db.table_name.find()
SELECT id,user_id,name FROM table_name	db.table_name.find( { },{ user_id: 1, name: 1 })
SELECT user_id, name FROM table_name	db.table_name.find( { },{ user_id: 1, name: 1, _id: 0 })
SELECT * FROM table_name WHERE name = "A"	db.table_name.find( { name: "A" })
SELECT user_id, name FROM table_name WHERE name = "A"	db.table_name.find({ name: "A" },{ user_id: 1, name: 1, _id: 0 })
SELECT * FROM table_name WHERE name != "A"	db.table_name.find({ name: { $ne: "A" } })
SELECT * FROM table_nameWHERE name = "A"AND age = 50	db.table_name.find( { name: "A",age: 50 })
SELECT * FROM table_name WHERE name = "A" OR age = 50	db.table_name.find( { $or: [ { name: "A" } , { age: 50 } ] })
SELECT * FROM table WHERE age > 25	db.table_name.find( { age: { $gt: 25 } })
SELECT * FROM table_name WHERE age < 25	db.table_name.find( { age: { $lt: 25 } })
SELECT * FROM table_name WHERE age > 25 AND age <= 50	db.table_name.find( { age: { $gt: 25, $lte: 50 } })
SELECT * FROM table_name WHERE user_id like "%bc%"	db.table_name.find( { user_id: /bc/ } )
SELECT * FROM table_name WHERE user_id like "bc%"	db.table_name.find( { user_id: /^bc/ } )
SELECT * FROM table_name WHERE name = "A" ORDER BY user_id ASC	db.table_name.find( { name: "A" } ).sort( { user_id: 1 } )
SELECT * FROM table_name WHERE name = "A" ORDER BY user_id DESC	db.table_name.find( { name: "A" } ).sort( { user_id: -1 } )
SELECT COUNT(*) FROM table_name	db.table_name.find().count()
SELECT COUNT(user_id) FROM table_name	db.table_name.find( { user_id: { $exists: true } } ).count()
SELECT COUNT(*) FROM table_name WHERE age > 30	db.table_name.find( { age: { $gt: 30 } } ).count()
SELECT DISTINCT(name) FROM table_name	db.table_name.distinct( "name" )
SELECT * FROM table_name LIMIT 1	db.table_name.findOne() or db.table_name.find().limit(1)
SELECT * FROM table_name LIMIT 5 OFFSET 10	db.table_name.find().limit(5).skip(10)
EXPLAIN SELECT * FROM table_name WHERE name = "A"	db.table_name.find( { name: "A" } ).explain()

3、更新语句

SQL Update Statements	MongoDB updateMany() Statements
UPDATE table_name SET name = "C" WHERE age > 25	db.table_name.update( { age: { $gt: 25 } }, { $set: { name: "C" } })
UPDATE table_name SET age = age + 3 WHERE name = "A"	db.table_name.update( { name: "A" } , { $inc: { age: 3 } })

4、删除语句

SQL Delete Statements	MongoDB deleteMany() Statements
DELETE FROM table_name WHERE name = "D"	db.table_name.remove({ name:"D" } )
DELETE FROM table_name	db.table_name.remove({})

5、操纵语句

SQL	MongoDB
CREATE TABLE table_name (id MEDIUMINT NOT NULL AUTO_INCREMENT, user_id Varchar(30),age Number, name char(1),PRIMARY KEY (id))	直接执行insert操作即可，如果表不存在，自动创建db.table_name.insert( { user_id: "abc123",age: 55, name: "A"} ); 也可以用以下语句创建collectiondb.createCollection("table_name")
ALTER TABLE table_name ADD join_date DATETIME	db.table_name.updateMany({ }, { $set: { join_date: new Date() } })
ALTER TABLE table_name DROP COLUMN join_date	db.table_name.updateMany( { }, { $unset: { "join_date": "" } })
CREATE INDEX idx_user_id_asc ON table_name(user_id)	db.table_name.createIndex( { user_id: 1 } )
CREATE INDEX idx_user_id_asc_age_desc ON table_name(user_id, age DESC)	db.table_name.createIndex( { user_id: 1, age: -1 } )
DROP TABLE table_name	db.table_name.drop()

6、insert into select 语句

由于mongodb不支持insert into table_name1 select* from table_name2;

要实现该功能方法如下：

var docs = db.table_name1.find( )docs.forEach(function(d){    var newDoc = d;    db.table_name2.insert(newDoc);});

ITPub博客

MongoDB的CRUD语句

1、插入语句

2、查询语句

3、更新语句

4、删除语句

5、操纵语句

最新文章