下表中列出了多个SQL中的术语和概念以及相对应的MongoDB中的术语和概念。
SQL 术语/概念 MongoDB 术语/概念
数据库 database
表 collection
行 document or BSON 文档
列 field
索引 index
表连接 内嵌文档和链接
主键 指定任何唯一列或复合列作为主键。
primary key 在MongoDB里,主键会自动设置为 _id 列。
创建和修改
SQL 建模语句 MongoDB 建模语句
CREATE TABLE users ( 第一次执行 insert() 操作时隐式创建
id MEDIUMINT NOT NULL 如果文档中不指定``_id``列,
AUTO_INCREMENT, 那么会自动添加``_id`` 列并默认为主键
user_id Varchar(30), db.users.insert( {
age Number, user_id: "abc123",
status char(1), age: 55, status: "A"}
PRIMARY KEY (id) )
) 当然,你也可以显式的创建一个集合:
db.createCollection("users")
SQL 查询语句 MongoDB find() 语句
// 查询一条语句
SELECT * db.users.find()
FROM users
// 查询指定字段
SELECT id, db.users.find(
user_id, { },
status { user_id: 1, status: 1 }
FROM users )
// 只允许指定出现,除去ID
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 )
// or查询
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 * b.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" } )
FROM users .sort( { user_id: 1 } )
WHERE status = "A"
ORDER BY user_id ASC
SELECT * db.users.find( { status: "A"} ).
FROM users sort( { user_id: -1 } )
WHERE status = "A"
ORDER BY user_id DESC
// 统计
SELECT COUNT(*) db.users.count()
FROM users or
db.users.find().count()
SELECT COUNT(user_id) b.users.count(
FROM users {user_id: { $exists: true } } )
SELECT COUNT(*) db.users.count( { age: { $gt:
FROM users 30 } } )
WHERE age > 30
// 去重
SELECT DISTINCT(status) db.users.distinct( "status" )
FROM users
// 指定数量LIMIT
SELECT * db.users.findOne()
FROM users or
LIMIT 1 db.users.find().limit(1)
SELECT * db.users.find().
FROM users limit(5).skip(10)
LIMIT 5
SKIP 10
EXPLAIN SELECT * db.users.find( { status: "A" }
FROM users ).explain()
WHERE status = "A"