SQL与mongoDB对比及映射

小灰灰 2022-07-16 02:16 149阅读 0赞

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/][https_docs.mongodb.com_manual_reference_sql-comparison]

[https_docs.mongodb.com_manual_reference_sql-comparison]: https://docs.mongodb.com/manual/reference/sql-comparison/