sql对应mongodb的写法

最新推荐文章于 2023-12-28 15:46:12 发布

weiweicao0429

最新推荐文章于 2023-12-28 15:46:12 发布

阅读量2.6k

点赞数

本文链接：https://blog.csdn.net/weiweicao0429/article/details/19012969

版权

mongodb 同时被 2 个专栏收录

3 篇文章 0 订阅

订阅专栏

sql

2 篇文章 0 订阅

订阅专栏

这篇博客对比了SQL与MongoDB在插入、选择、更新和删除记录时的语句，详细列举了各自的操作方法。

摘要由CSDN通过智能技术生成

参考自官网http://docs.mongodb.org/manual/reference/sql-comparison/

Insert

The following table presents the various SQL statements related toinserting records into tables and the corresponding MongoDB statements.

SQL INSERT Statements	MongoDB insert() Statements	Reference
INSERT INTO users(user_id, age, status) VALUES ("bcd001", 45, "A")	db.users.insert( { user_id: "bcd001", age: 45, status: "A" } )	See `insert()` for more information.

Select

The following table presents the various SQL statements related toreading records from tables and the corresponding MongoDB statements.

SQL SELECT Statements	MongoDB find() Statements	Reference
SELECT * FROM users	db.users.find()	See `find()`for more information.
SELECT id, user_id, status FROM users	db.users.find( { }, { user_id: 1, status: 1 } )	See `find()`for more information.
SELECT user_id, status FROM users	db.users.find( { }, { user_id: 1, status: 1, _id: 0 } )	See `find()`for more information.
SELECT * FROM users WHERE status = "A"	db.users.find( { status: "A" } )	See `find()`for more information.
SELECT user_id, status FROM users WHERE status = "A"	db.users.find( { status: "A" }, { user_id: 1, status: 1, _id: 0 } )	See `find()`for more information.
SELECT * FROM users WHERE status != "A"	db.users.find( { status: { $ne: "A" } } )	See `find()`and`$ne` for more information.
SELECT * FROM users WHERE status = "A" AND age = 50	db.users.find( { status: "A", age: 50 } )	See `find()`and`$and` for more information.
SELECT * FROM users WHERE status = "A" OR age = 50	db.users.find( { $or: [ { status: "A" } , { age: 50 } ] } )	See `find()`and`$or` for more information.
SELECT * FROM users WHERE age > 25	db.users.find( { age: { $gt: 25 } } )	See `find()`and`$gt` for more information.
SELECT * FROM users WHERE age < 25	db.users.find( { age: { $lt: 25 } } )	See `find()`and`$lt` for more information.
SELECT * FROM users WHERE age > 25 AND age <= 50	db.users.find( { age: { $gt: 25, $lte: 50 } } )	See `find()`,`$gt`, and `$lte` formore information.
SELECT * FROM users WHERE user_id like "%bc%"	db.users.find( { user_id: /bc/ } )	See `find()`and`$regex` for more information.
SELECT * FROM users WHERE user_id like "bc%"	db.users.find( { user_id: /^bc/ } )	See `find()`and`$regex` for more information.
SELECT * FROM users WHERE status = "A" ORDER BY user_id ASC	db.users.find( { status: "A" } ).sort( { user_id: 1 } )	See `find()`and`sort()`for more information.
SELECT * FROM users WHERE status = "A" ORDER BY user_id DESC	db.users.find( { status: "A" } ).sort( { user_id: -1 } )	See `find()`and`sort()`for more information.
SELECT COUNT(*) FROM users	db.users.count() or db.users.find().count()	See `find()`and`count()` formore information.
SELECT COUNT(user_id) FROM users	db.users.count( { user_id: { $exists: true } } ) or db.users.find( { user_id: { $exists: true } } ).count()	See `find()`,`count()`, and`$exists` for more information.
SELECT COUNT(*) FROM users WHERE age > 30	db.users.count( { age: { $gt: 30 } } ) or db.users.find( { age: { $gt: 30 } } ).count()	See `find()`,`count()`, and`$gt` for more information.
SELECT DISTINCT(status) FROM users	db.users.distinct( "status" )	See `find()`and`distinct()`for more information.
SELECT * FROM users LIMIT 1	db.users.findOne() or db.users.find().limit(1)	See `find()`,`findOne()`,and`limit()`for more information.
SELECT * FROM users LIMIT 5 SKIP 10	db.users.find().limit(5).skip(10)	See `find()`,`limit()`, and`skip()` formore information.
EXPLAIN SELECT * FROM users WHERE status = "A"	db.users.find( { status: "A" } ).explain()	See `find()`and`explain()`for more information.

Update Records

The following table presents the various SQL statements related toupdating existing records in tables and the corresponding MongoDBstatements.

SQL Update Statements MongoDB update() Statements Reference

SQL Update Statements	MongoDB update() Statements	Reference
UPDATE users SET status = "C" WHERE age > 25	db.users.update( { age: { $gt: 25 } }, { $set: { status: "C" } }, { multi: true } )	See `update()`,`$gt`, and `$set` for moreinformation.
UPDATE users SET age = age + 3 WHERE status = "A"	db.users.update( { status: "A" } , { $inc: { age: 3 } }, { multi: true } )	See `update()`,`$inc`, and `$set` for moreinformation.

 
       UPDATE users
SET status = "C"
WHERE age > 25

 
       db.users.update(
   { age: { $gt: 25 } },
   { $set: { status: "C" } },
   { multi: true }
)
 
      

See update(),$gt, and $set for moreinformation.

 
       UPDATE users
SET age = age + 3
WHERE status = "A"

 
       db.users.update(
   { status: "A" } ,
   { $inc: { age: 3 } },
   { multi: true }
)
 
      

See update(),$inc, and $set for moreinformation.

Delete Records

The following table presents the various SQL statements related todeleting records from tables and the corresponding MongoDB statements.

SQL Delete Statements	MongoDB remove() Statements	Reference
DELETE FROM users WHERE status = "D"	db.users.remove( { status: "D" } )	See `remove()`for more information.
DELETE FROM users	db.users.remove( )	See `remove()`for more information.