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/

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

MySQL replace into的使用细则(r10笔记第48天)

在Oracle中有merge into的语法,可以达到一个语句完成同时修改,添加数据的功能,MySQL里面没有merge into的语法,却有replace i...

32950
来自专栏Java架构师历程

sql必会基础1

ALTER TABLE old_table_name RENAME [TO] new_table_name

14120
来自专栏杨建荣的学习笔记

很多人比较纠结的约束和索引的关系(r7笔记第75天)

最近有不少朋友公众号留言或者微信私信问我一个问题,出乎我的意料,问题竟然都是很相似的,所以我统一答复一下。 之前写了一篇文章 一个清理和查询都要兼顾的简单方案,...

35560
来自专栏程序你好

数据库MySQL中的JOIN详解

一个完整的SQL语句中会被拆分成多个子句,子句的执行过程中会产生虚拟表(vt),但是结果只返回最后一张虚拟表。从这个思路出发,我们试着理解一下JOIN查询的执行...

6610
来自专栏杨建荣的学习笔记

MySQL和Oracle中唯一性索引的差别(r12笔记第83天)

今天在修复MySQL数据的时候,发现一个看起来“奇怪”的问题。 有一个表里存在一个唯一性索引,这个索引包含3个列,这个唯一性索引的意义就是通过这3个列...

36560
来自专栏lgp20151222

Mysql 获取表的comment 字段

查看获取表内字段注释: > show full columns from tablename; 或是  show full fields from tab...

17410
来自专栏Netkiller

数据库安全·时间一致性

以下节选择《Netkiller Architect 手札》地址 http://www.netkiller.cn/architect/ 接下来几周的话题是数据库安...

37170
来自专栏Netkiller

数据库记录安全解决方案

目录 1. 什么是防删除,防撰改 2. 为什么要做防删除,防撰改限制 3. 何时做防删除,防撰改限制 4. 在哪里做防删除,防撰改限制 5. 谁去做防删除,防撰...

30750
来自专栏逸鹏说道

利用insert,update和delete注入获取数据

0x00 简介 利用SQL注入获取数据库数据,利用的方法可以大致分为联合查询、报错、布尔盲注以及延时注入,通常这些方法都是基于select查询语句中的SQL注射...

41880
来自专栏Netkiller

数据库记录安全解决方案

数据库记录安全解决方案 http://netkiller.github.io/journal/mysql.security.html 摘要 数据库记录防删除,放...

42460

扫码关注云+社区

领取腾讯云代金券