当前位置: 首页 > news >正文

SQL和MongoDB对比

关系型数据库如MySQL和非关系型数据库MongoDB的对应关系:

SQL

MongoDB

database

database

table

collection

row

document or Bson document

column

field

index

index

table joins

$lookup

primary key

primary key

指定任何唯一的列或列组合作为主键

主键会自动设置为_id字段

aggregation (group by)

aggregation pipeline

select into table

$out

merge into table

$merge

union all

$unionWith

transactions

transacctions

Example

CREATE TABLE people (id MEDIUMINT NOT NULLAUTO_INCREMENT,user_id Varchar(30),age Number,status char(1),PRIMARY KEY (id)
)

Implicitly created on first insertOne() or insertMany() operation. The primary key _id is automatically added if _id field is not specified.

db.people.insertOne( {user_id: "abc123",age: 55,status: "A"} )
ALTER TABLE people
DROP COLUMN join_date

使用$set添加field

db.people.updateMany({ },{ $set: { join_date: new Date() } }
)
ALTER TABLE people
DROP COLUMN join_date

使用$unset删除field

db.people.updateMany({ },{ $unset: { "join_date": "" } }
)

创建索引

CREATE INDEX idx_user_id_asc
ON people(user_id)
db.people.createIndex( { user_id: 1 } )
CREATE INDEXidx_user_id_asc_age_desc
ON people(user_id, age DESC)

-1表示降序

db.people.createIndex( { user_id: 1, age: -1 } )
DROP TABLE people
db.people.drop()
INSERT INTO people(user_id,age,status)
VALUES ("bcd001",45,"A")
db.people.insertOne({ user_id: "bcd001", age: 45, status: "A" }
)
SELECT * FROM people
db.people.find()
SELECT id,user_id,status FROM people
db.people.find({},{user_id:1,status:1})
SELECT user_id,status FROM people
//0表示不显示改行
db.people.find({},{user_id:1,status:1,_id:0})
SELECT * FROM people WHERE status="A"
db.people.find({status:"A"})
SELECT user_id,status FROM people WHERE status = "A"
db.people.find({status:"A"},{user_id:1,status:1,_id:0})
SELECT * FROM people WHERE status != "A"
db.people.find({status:{$ne:"A"}})
SELECT * FROM people WHERE status = "A" AND age=50
db.people.find({status:"A",age:50})
SELECT * FROM people OR age = 50
db.people.find({$or:[{status:"A"},{age:50}]})
SELECT * FROM people WHERE age >= 25
db.people.find({age:{$gte:25}})
SELECT * FROM people WHERE age > 25 AND age <= 50
db.people.find({age:{$gt:25,$lte:50}})
SELECT * FROM people WHERE user_id like "%bc%"
db.people.find({user_id:/bc/} )
db.people.find({user_id:{$regex:/bc/}})
SELECT * FROM people WHERE user_id LIKE "%bc"
db.people.find({user_id:/^bc/})
db.people.find({user_id:{$regex:/^bc/}})
SELECT * FROM people WHERE status = "A" ORDER BY user_id ASC
SELECT * FROM people WHERE status = "A" ORDER BY user_id DESC
db.people.find({status:"A"}).sort({user_id:1})
db.people.find({status:"A"}).sort({user_id:-1})
SELECT count(*) FROM people
db.people.count() or 
db.people.find().count() or 
db.people.count({user_id:{$exists:true}})
SELECT count(*) FROM people WHERE age>30
db.people.count({age:{$gt:30}})
SELECT DISTINCT(status) FROM people
db.people.distinct("status")
SELECT * FROM people LIMIT 1
db.people.findOne() or
db.people.find().limit(1)
SELECT * FROM people LIMIT 5 SKIP 10
db.people.find(().limit(5).skip(10)
EXPLAIN SELECT * FROM people WHERE status = "A"
db.people.find({status:"A"}).explain()

http://www.lryc.cn/news/34244.html

相关文章:

  • 研究链表空间销毁问题
  • Linux面试总结
  • anaconda的linux版本以及jupyter的安装和DataSpell连接linux的jupyter服务器
  • Zookeeper集群和Hadoop集群安装(保姆级教程)
  • 利用matlab的newff构建BP神经网络来实现数据的逼近和拟合
  • 【经验分享】电路板上电就挂?新手工程师该怎么检查PCB?
  • 运筹系列68:TSP问题Held-Karp下界的julia实现
  • 神经影像信号处理总成(EEG、SEEG、MRI、CT)
  • ZooKeeper 进阶:基本介绍
  • CSS的常用元素属性,显示模式,盒模型,弹性布局
  • 【20230308】串口接收数据分包问题处理(Linux)
  • 数据库复试问题总结
  • Linux操作系统安装——服务控制
  • 【C语言】编译+链接
  • 为「IT女神勋章」而战
  • JS 动画 之 setInterval、requestAnimationFram
  • 【LeetCode——排序链表】
  • 二叉树的遍历(前序、中序、后序)| C语言
  • 【建议收藏】深入浅出Yolo目标检测算法(含Python实现源码)
  • Vue常见的事件修饰符
  • 【卷积神经网络】激活函数 | Tanh / Sigmoid / ReLU / Leaky ReLU / ELU / SiLU / GeLU
  • 刷题记录:牛客NC24048[USACO 2017 Jan P]Promotion Counting 求子树的逆序对个数
  • MpAndroidChart3最强实践攻略
  • Spring笔记(9):事务管理ACID
  • io流 知识点+代码实例
  • 【MySQL】P8 多表查询(2) - 连接查询 联合查询
  • QML动画(Animator)
  • Git 分支操作【解决分支冲突问题】
  • 盘点全球10大女性技术先驱
  • C++之dynamic_cast