SQL与mongoDB对比及映射
mongoDB是最接近与关系型数据库的开源NoSQL数据库，几乎绝大多数常用的命令或语句在SQL中都可以找到相应的对应或映射。本文主要与MySQL做了些参照，供大家参考。
一、逻辑对象术语
SQL Terms/Concepts MongoDB Terms/Concepts
------------------- -----------------------------------
database database
table collection
row document or BSON document
column field
index index
table joins embedded documents and linking
primary key(指定一个唯一列或复合列) primary key(由_id自动生成)
aggregation (e.g. group by) aggregation pipeline 二、可执行程序对照
MongoDB MySQL Oracle Informix DB2
----------- ---------- ---------- -------------- ------------
Database Server mongod mysqld oracle IDS DB2 Server
Database Client mongo mysql sqlplus DB-Access DB2 Client 三、表级别对照
SQL语句 mongoDB java script shell
--------------------------------------- --------------------------------------------------
CREATE TABLE users ( db.users.insert( {
id MEDIUMINT NOT NULL user_id: "abc123",
AUTO_INCREMENT, age: 55,
user_id Varchar(30), status: "A"
age Number, } )
status char(1), //也可以使用下面的方式来创建集合，不过没有结构，即free-schema
PRIMARY KEY (id) db.createCollection("users")
)
//表修改(增加列) Blog:http://blog.csdn.net/leshami
--------------------------------------- --------------------------------------------------
ALTER TABLE users db.users.update(
ADD join_date DATETIME { },
{ $set: { join_date: new Date() } },
{ multi: true }
) //由于集合无模式，可以直接通过update $set修改器来增加键
//表修改(删除列)
--------------------------------------- --------------------------------------------------
ALTER TABLE users db.users.update(
DROP COLUMN join_date { },
{ $unset: { join_date: "" } },
{ multi: true }
) //同表增加列，不过此时使用unset修改器
//创建索引 Author : Leshami
--------------------------------------- --------------------------------------------------
CREATE INDEX idx_user_id_asc db.users.createIndex( { user_id: 1 } )
ON users(user_id)
//创建索引(多列倒序)
--------------------------------------- --------------------------------------------------
CREATE INDEX db.users.createIndex( { user_id: 1, age: -1 } )
idx_user_id_asc_age_desc
ON users(user_id, age DESC) 四、记录插入对照
SQL语句 mongoDB java script shell
--------------------------------------- --------------------------------------------------
INSERT INTO users(user_id, db.users.insert(
age, { user_id: "bcd001", age: 45, status: "A" }
status) )
VALUES ("bcd001",
45,
"A") 五、记录查询对照
SQL语句 mongoDB java script shell
--------------------------------------- --------------------------------------------------
SELECT * db.users.find()
FROM users
--------------------------------------- --------------------------------------------------
SELECT id, db.users.find(
user_id, { },
status { user_id: 1, status: 1 }
FROM users )
--------------------------------------- --------------------------------------------------
SELECT user_id, status db.users.find(
FROM users { },
{ user_id: 1, status: 1, _id: 0 }
)
--------------------------------------- --------------------------------------------------
SELECT * db.users.find(
FROM users { status: "A" }
WHERE status = "A" )
--------------------------------------- --------------------------------------------------
SELECT user_id, status db.users.find(
FROM users { status: "A" },
WHERE status = "A" { user_id: 1, status: 1, _id: 0 }
)
--------------------------------------- --------------------------------------------------
SELECT * db.users.find(
FROM users { status: { $ne: "A" } }
WHERE status != "A" )
--------------------------------------- --------------------------------------------------
SELECT * db.users.find(
FROM users { status: "A",
WHERE status = "A" age: 50 }
AND age = 50 )
--------------------------------------- --------------------------------------------------
SELECT * db.users.find(
FROM users { $or: [ { status: "A" } ,
WHERE status = "A" { age: 50 } ] }
OR age = 50 )
--------------------------------------- --------------------------------------------------
SELECT * db.users.find(
FROM users { age: { $gt: 25 } }
WHERE age > 25 )
--------------------------------------- --------------------------------------------------
SELECT * db.users.find(
FROM users { age: { $lt: 25 } }
WHERE age < 25 )
--------------------------------------- --------------------------------------------------
SELECT * db.users.find(
FROM users { age: { $gt: 25, $lte: 50 } }
WHERE age > 25 )
AND age <= 50
--------------------------------------- --------------------------------------------------
SELECT * db.users.find( { user_id: /bc/ } )
FROM users
WHERE user_id like "%bc%"
--------------------------------------- --------------------------------------------------
SELECT * db.users.find( { user_id: /^bc/ } )
FROM users
WHERE user_id like "bc%"
--------------------------------------- --------------------------------------------------
SELECT * db.users.find( { status: "A" } ).sort( { user_id: 1 } )
FROM users
WHERE status = "A"
ORDER BY user_id ASC
--------------------------------------- --------------------------------------------------
SELECT * db.users.find( { status: "A" } ).sort( { user_id: -1 } )
FROM users
WHERE status = "A"
ORDER BY user_id DESC
--------------------------------------- --------------------------------------------------
SELECT COUNT(*) db.users.count()
FROM users or db.users.find().count()
--------------------------------------- --------------------------------------------------
SELECT COUNT(user_id) db.users.count( { user_id: { $exists: true } } )
FROM users or db.users.find( { user_id: { $exists: true } } ).count()
--------------------------------------- --------------------------------------------------
SELECT COUNT(*) db.users.count( { age: { $gt: 30 } } )
FROM users or db.users.find( { age: { $gt: 30 } } ).count()
WHERE age > 30
--------------------------------------- --------------------------------------------------
SELECT DISTINCT(status) db.users.distinct( "status" )
FROM users
--------------------------------------- --------------------------------------------------
SELECT * db.users.findOne()
FROM users or db.users.find().limit(1)
LIMIT 1
--------------------------------------- --------------------------------------------------
SELECT * db.users.find().limit(5).skip(10)
FROM users
LIMIT 5
SKIP 10
--------------------------------------- --------------------------------------------------
EXPLAIN SELECT * db.users.find( { status: "A" } ).explain()
FROM users
WHERE status = "A"六、记录更新对照
SQL语句 mongoDB java script shell
--------------------------------------- --------------------------------------------------
UPDATE users db.users.update(
SET status = "C" { age: { $gt: 25 } },
WHERE age > 25 { $set: { status: "C" } },
{ multi: true }
)
--------------------------------------- --------------------------------------------------
UPDATE users db.users.update(
SET age = age + 3 { status: "A" } ,
WHERE status = "A" { $inc: { age: 3 } },
{ multi: true }
) 七、记录删除对照
SQL语句 mongoDB java script shell
--------------------------------------- --------------------------------------------------
DELETE FROM users db.users.remove( { status: "D" } )
WHERE status = "D"
--------------------------------------- --------------------------------------------------
DELETE FROM users db.users.remove({}) 八、mongoDB与MySQL的其他命令对照
功能描述 MySQL工具或方法 mongoDB工具或方法
------------- ---------------- ---------------------------------
逻辑备份工具 mysqldump mongodump
逻辑还原工具 mysql mongorestore
数据导出工具 mysqldump mongoexport
数据导入工具 source mongoimport
新建用户并授权 grant all on *.* to username@'localhost' db.addUser("user","psw")
identified by 'passwd'; db.auth("user","psw")
显示库列表 show databases; show dbs
建库 create database dbname; use dbname
切换库 use dbname; use dbname
显示表列表 show tables; show collections or show tables
查询主从状态 show slave status; rs.status 参考网址：https://docs.mongodb.com/manual/reference/sql-comparison/
本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2016年09月28日，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读