mongodb(基础用法)
驱动和客户端库
https://mongodb-documentation.readthedocs.org/en/latest/ecosystem/drivers.html#id2
窗体顶端
|
窗体底端
SQL to mongo Shell to C++
MongoDB queries are expressed as JSON (BSON) objects. This quick reference chart shows examples as SQL, mongo shell syntax, and MongoDB C++ driver syntax.
A query expression in MongoDB (and other things, such as an index key pattern) is represented as BSON. In C++ you can use BSONObjBuilder (aka bson::bob) to build BSON objects, or the BSON() macro. The examples below assume a connection c already established:
using namespace bson;
DBClientConnection c;
c.connect("somehost");
Several of the C++ driver methods throw mongo::DBException, so you will want a try/catch statement as some level in your program. Also be sure to call c.getLastError() after writes to check the error code.
|
SQL |
mongo Shell |
C++ Driver |
|
INSERT INTO USERS VALUES( 1, 1) |
db.users.insert( { a: 1, b: 1 } ) |
// GENOID is optional. if not done by client, // server will add an _id
c.insert("mydb.users", BSON(GENOID<<"a"<<1<<"b"<<1)); // then: string err = c.getLastError(); |
|
SELECT a,b FROM users |
db.users.find( {}, {a: 1, b: 1 } ) |
auto_ptr<DBClientCursor> cursor = c.query("mydb.users", Query(), 0, 0, BSON("a"<<1<<"b"<<1)); |
|
SELECT * FROM users |
db.users.find() |
auto_ptr<DBClientCursor> cursor = c.query("mydb.users", Query()); |
|
SELECT * FROM users WHERE age=33 |
db.users.find( { age: 33 } ) |
auto_ptr<DBClientCursor> cursor = c.query("mydb.users", QUERY("age"<<33)) // or: auto_ptr<DBClientCursor> cursor = c.query("mydb.users", BSON("age"<<33)) |
|
SELECT * FROM users WHERE age=33 ORDER BY name |
db.users.find( { age: 33 } ).sort( { name: 1 } ) |
auto_ptr<DBClientCursor> cursor = c.query("mydb.users", QUERY("age"<<33).sort("name")); |
|
SELECT * FROM users WHERE age>33 AND age<=40 |
db.users.find( { 'age': { $gt:33, $lte:40 } } ) |
auto_ptr<DBClientCursor> cursor = c.query("mydb.users", QUERY("age"<<GT<<33<<LTE<<40)); |
|
CREATE INDEX myindexname ON users(name) |
db.users.ensureIndex( {name: 1 } ) |
c.ensureIndex("mydb.users", BSON("name"<<1)); |
|
SELECT * FROM users LIMIT 10 SKIP 20 |
db.users.find().limit(10).skip(20) |
auto_ptr<DBClientCursor> cursor = c.query("mydb.users", Query(), 10, 20); |
|
SELECT * FROM users LIMIT 1 |
db.users.findOne() |
bo obj = c.findOne("mydb.users", Query()); |
|
SELECT DISTINCT last_name FROM users WHERE x=1 |
db.users.distinct( 'last_name', {x: 1} ) |
// no helper for distinct yet in c++ driver, // so send command manually bo cmdResult; bool ok = c.runCommand( "mydb", BSON("distinct" << "users" << "key" << "last_name" << "query" << BSON("x"<<1)), cmdResult); list<bo> results; cmdResult["values"].Obj().Vals(results); |
|
SELECT COUNT(*) FROM users where AGE > 30 |
db.users.find( { age: { $gt: 30 } } ).count() |
unsigned long long n = c.count("mydb.users", BSON("age"<<GT<<30)); |
|
UPDATE users SET a=a+2 WHERE b='q' |
db.users.update( { b: 'q' }, { $inc: { a:2 } }, false, true) |
c.update("mydb.users", QUERY("b"<<"q"), BSON("$inc"<<BSON("a"<<2)), false, true); // then optionally: string err = c.getLastError(); bool ok = err.empty(); |
|
DELETE FROM users WHERE z="abc" |
db.users.remove( { z: 'abc' } ) |
c.remove("mydb.users", QUERY("z"<<"abc")); // then optionally: string err = c.getLastError(); |
也可以参考
- Several more examples (in shell syntax) are on the SQL to Mongo Mapping Chart page.
- C++ Language Center
- < Getting Started with the C++ Driver
SQL to MongoDB Mapping Chart
In addition to the charts that follow, you might want to consider the Frequently Asked Questions section for a selection of common questions about MongoDB.
Terminology and Concepts
The following table presents the various SQL terminology and concepts and the corresponding MongoDB terminology and concepts.
|
SQL Terms/Concepts |
MongoDB Terms/Concepts |
|
database |
|
|
table |
|
|
row |
|
|
column |
|
|
index |
|
|
table joins |
embedded documents and linking |
|
primary key Specify any unique column or column combination as primary key. |
In MongoDB, the primary key is automatically set to the _idfield. |
|
aggregation (e.g. group by) |
aggregation pipeline See the SQL to Aggregation Mapping Chart. |
Executables
The following table presents the MySQL/Oracle executables and the corresponding MongoDB executables.
|
|
MySQL/Oracle |
MongoDB |
|
Database Server |
mysqld/oracle |
|
|
Database Client |
mysql/sqlplus |
Examples
The following table presents the various SQL statements and the corresponding MongoDB statements. The examples in the table assume the following conditions:
- The SQL examples assume a table named users.
- The MongoDB examples assume a collection named users that contain documents of the following prototype:
· {
· _id: ObjectID("509a8fb2f3f4948bd2f983a0"),
· user_id: "abc123",
· age: 55,
· status: 'A'
· }
Create and Alter
The following table presents the various SQL statements related to table-level actions and the corresponding MongoDB statements.
|
SQL Schema Statements |
MongoDB Schema Statements |
Reference |
CREATE TABLE users ( id MEDIUMINT NOT NULL AUTO_INCREMENT, user_id Varchar(30), age Number, status char(1), PRIMARY KEY (id) ) |
Implicitly created on first insert() operation. The primary key_id is automatically added if _id field is not specified. db.users.insert( {
user_id: "abc123", age: 55, status: "A" } ) However, you can also explicitly create a collection: db.createCollection("users")
|
See insert() anddb.createCollection()for more information. |
ALTER TABLE users ADD join_date DATETIME |
Collections do not describe or enforce the structure of its documents; i.e. there is no structural alteration at the collection level. However, at the document level, update() operations can add fields to existing documents using the $set operator. db.users.update( { },
{ $set: { join_date: new Date() } },
{ multi: true }
) |
See the Data Modeling Concepts, update(), and$set for more information on changing the structure of documents in a collection. |
ALTER TABLE users DROP COLUMN join_date |
Collections do not describe or enforce the structure of its documents; i.e. there is no structural alteration at the collection level. However, at the document level, update() operations can remove fields from documents using the $unset operator. db.users.update( { },
{ $unset: { join_date: "" } },
{ multi: true }
) |
See Data Modeling Concepts, update(), and$unset for more information on changing the structure of documents in a collection. |
CREATE INDEX idx_user_id_asc ON users(user_id) |
db.users.ensureIndex( { user_id: 1 } )
|
See ensureIndex() andindexes for more information. |
CREATE INDEX idx_user_id_asc_age_desc ON users(user_id, age DESC) |
db.users.ensureIndex( { user_id: 1, age: -1 } )
|
See ensureIndex() andindexes for more information. |
DROP TABLE users |
db.users.drop() |
See drop() for more information. |
Insert
The following table presents the various SQL statements related to inserting 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 to reading 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" } }
) |
|
SELECT * FROM users WHERE status = "A" AND age = 50 |
db.users.find( { status: "A",
age: 50 } ) |
|
SELECT * FROM users WHERE status = "A" OR age = 50 |
db.users.find( { $or: [ { status: "A" } ,
{ age: 50 } ] }
) |
|
SELECT * FROM users WHERE age > 25 |
db.users.find( { age: { $gt: 25 } }
) |
|
SELECT * FROM users WHERE age < 25 |
db.users.find( { age: { $lt: 25 } }
) |
|
SELECT * FROM users WHERE age > 25 AND age <= 50 |
db.users.find( { age: { $gt: 25, $lte: 50 } }
) |
|
SELECT * FROM users WHERE user_id like "%bc%" |
db.users.find( { user_id: /bc/ }
) |
|
SELECT * FROM users WHERE user_id like "bc%" |
db.users.find( { user_id: /^bc/ }
) |
|
SELECT * FROM users WHERE status = "A" ORDER BY user_id ASC |
db.users.find( { status: "A" } ).sort( { user_id: 1 } )
|
|
SELECT * FROM users WHERE status = "A" ORDER BY user_id DESC |
db.users.find( { status: "A" } ).sort( { user_id: -1 } )
|
|
SELECT COUNT(*) FROM users |
db.users.count() or db.users.find().count() |
|
SELECT COUNT(user_id) FROM users |
db.users.count( { user_id: { $exists: true } } )
or db.users.find( { user_id: { $exists: true } } ).count()
|
|
SELECT COUNT(*) FROM users WHERE age > 30 |
db.users.count( { age: { $gt: 30 } } )
or db.users.find( { age: { $gt: 30 } } ).count()
|
|
SELECT DISTINCT(status) FROM users |
db.users.distinct( "status" ) |
See find()anddistinct()for more information. |
SELECT * FROM users LIMIT 1 |
db.users.findOne() or db.users.find().limit(1) |
|
SELECT * FROM users LIMIT 5 SKIP 10 |
db.users.find().limit(5).skip(10) |
|
EXPLAIN SELECT * FROM users WHERE status = "A" |
|
|
