SQL与mongoDB对比及映射

  1. 云栖社区>
  2. 博客>
  3. 正文

SQL与mongoDB对比及映射

leshami 2016-09-28 18:56:31 浏览763
展开阅读全文

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/500
评论
leshami
+ 关注