| SQL条款 | MongoDB条款 |
| database | database |
| table | Collection |
| row | document or BSON document |
| column | field |
| index | index |
| table joins | $lookup, embedded document |
| primary key | primary key |
| 在SQL中，我们可以指定任何唯一的列或列组合作为主键。 | 在MongoDB中，我们不需要设置主键。 _id字段自动设置为主键。 |
| aggregation | aggregation pipeline |
| SELECT INTO NEW_TABLE | $out |
| MERGE INTO TABLE | $merge |
| transactions | transactions |
{
_id: ObjectId("509a8fb2bd2f983a0"),
user_id: "admin123",
age: 18,
status: 'A'
}
| SQL语句 | MongoDB语句 |
| CREATE TABLE lidihuo ( id MEDIUMINT NOT NULL AUTO_INCREMENT, user_id Varchar(20), age Number, status char(1), PRIMARY KEY (id) ) |
db.createCollection(" lidihuo") |
| ALTER TABLE lidihuo添加ADD join_date DATETIME | db.lidihuo.updateMany( {}， {$set: {join_date: new Date()}} ) |
| ALTER TABLE lidihuo DROP COLUMN join_date | db.lidihuo.updateMany( {}， {$unset: {" join_date": ""}} ) |
| 在lidihuo上创建索引idx_user_id_asc(user_id) | db.people.createIndex({user_id: 1}) |
| 创建索引idx_user_id_asc开启人员(user_id) | db.people.createIndex({user_id: 123，age: 1}) |
| DROP TABLE人员 | db.people.drop() |
| SQL插入语句 | MongoDB插入语句 |
| INSERT INTO liidihuo (user_id,age,status)VALUES ("mongo",45,"A") | db.liidihuo.insertOne({ user_id: "mongo", age: 18, status: "A" }) |
| SQL SELECT语句 | MongoDB find()语句 |
| SELECT * FROM liidihuo | db.lidihuo.find() |
| SELECT id, user_id, status FROM lidihuo | db.lidihuo.find({}，{user_id: 1，status: 1}) |
| SELECT user_id,status from lidihuo | db.lidihuo.find({}，{user_id: 1，status: 1，_id: 0}) |
| SELECT * FROM lidihuo WHERE status="B" | db.lidihuo.find({status: " A"}) |
| SELECT user_id,status from lidihuo WHERE，status="A" | db.lidihuo.find({status: " A"}，{user_id: 1，status: 1，_id: 0}) |
| SELECT * FROM lidihuo WHERE status!="A" | db.lidihuo.find({status: {$ne: " A"}}) |
| SELECT * FROM lidihuo WHERE status = "A" AND age = 50 | db.lidihuo.find({status: " A",age: 50}) |
| SELECT * FROM lidihuo WHERE status = "A" OR age = 50 | db.lidihuo.find({$or: [{status: " A"}}，{age: 50}]}) |
| SELECT * FROM lidihuo WHERE age > 25 | db.lidihuo.find({age: {$gt: 25}}) |
| SELECT * FROM lidihuo WHERE age < 25 | Db.lidihuo.find({ age: { $lt: 25 } }) |
| SELECT * FROM lidihuo WHERE age > 25 AND age <= 50 | db.lidihuo.find({ age: { $gt: 25, $lte: 50 } }) |
| SELECT * FROM lidihuo WHERE user_id like "%bc%" | db.lidihuo.find( { user_id: /bc/ } ) 或者 db.lidihuo.find( { user_id: { $regex: /bc/ } } ) |
| SELECT * FROM lidihuo WHERE user_id like "bc%" | db.lidihuo.find( { user_id: /^bc/ } ) 或者 db.lidihuo.find( { user_id: { $regex: /^bc/ } } ) |
| SELECT * FROM lidihuo WHERE status = "A" ORDER BY user_id ASC | db. lidihuo. find( { status: "A" } ).sort( { user_id: 1 } ) |
| SELECT * FROM lidihuo WHERE status = "A" ORDER BY user_id ASC | db. lidihuo. find( { status: "A" } ). sort( { user_id: -1 } ) |
| SELECT * FROM lidihuo WHERE status = "A" ORDER BY user_id DESC | db. lidihuo. find( { status: "A" } ). sort( { user_id: -1 } ) |
| SELECT * FROM lidihuo WHERE status = "A" ORDER BY user_id DESC | db。 lidihuo。 find({status: " A"})。 sort({user_id: -1}) |
| SELECT COUNT(*) FROM lidihuo | db.lidihuo. count() 或者 db.lidihuo. find(). count() |
| SELECT COUNT(user_id) FROM lidihuo | db.lidihuo.count({user_id:{$exists:true}}) 或者 db. lidihuo.find( { user_id: { $exists: true } } ).count() |
| SELECT COUNT(*) FROM lidihuo WHERE age > 30 | db. lidihuo.count({age:{$gt:30}}) 或者 db.lidihuo.find( { age: { $gt: 30 } } ).count() |
| SELECT DISTINCT(status) FROM lidihuo | db. lidihuo.aggregate( [ { $group : { _id : "$status" } } ] ) 或者 db. lidihuo.distinct( "status" ) |
| SELECT * FROM lidihuo LIMIT 1 | db. lidihuo.findOne() 或者 db. lidihuo.find(). limit(1) |
| SELECT * FROM lidihuo LIMIT 5 SKIP 10 | db. lidihuo.find(). limit(5). skip(10) |
| EXPLAIN SELECT * FROM lidihuo WHERE status = "A" | db. lidihuo. find( { status: "A" } ). explain() |
| SQL更新语句 | MongoDB updateMany()语句 |
| UPDATE lidihuo SETstatus="C" 年龄> 25 | db.lidihuo.updateMany({age: {$gt: 25}}，{$set: {status: " C"}}}) |
| UPDATE lidihuo SET年龄=年龄+ 3 状态为" A" | db.lidihuo.updateMany({status: " A"}，{$inc: {age: 3}}) |
| SQL删除语句 | MongoDB deleteMany()语句 |
| 从lidihuo删除，status="D" | db.lidihuo.deleteMany({状态: " D"}) |
| 从lidihuo删除 | db.lidihuo.deleteMany({}) |
