mongodb-4.4.10版本与MySQL的SQL语法对比，以及mongodb增删改查入门demo
先了解下mongdb的数据结构与mysql的数据结构的对应关系，这样能帮我们更好的快速理解mongodb：
| Mongodb | MySql |
| DB（库） | Database（数据库） |
| Collection（集合） | Table（表） |
| Document（文档）
单个文档最大不能超过16MB，否则就应该考虑使用引用（DBRef）了，在主表里存储一个id值，指向另一个表中的id值 | Row/Record（行/记录） |
| Field（字段） | Col（列） |
| Index（索引） | Index |
| Embedding & Linkding | Join |
| Shard | Partition |
| Sharding Key | Partition Key |
进入mongodb的sql 命令行（退出命令是 exit ）:
mongo
> use lison 相当于mysql里面创建了一个db 命名叫 lison
switched to db lison
> db.users.drop(); 删除users集合的所有行数据，mongodb里的集合对应的是Mysql里table表的概念
> var user1 = { 定义一个BSON格式的命名为user1的对象
"username" : "lison",
"country" : "china",
"address" : {
"aCode" : "411000",
"add" : "长沙"
},
"favorites" : {
"movies" : ["杀破狼 2","战狼","雷神 1"],
"cites" : ["长沙","深圳","上海"] },
"age" : 18,
"salary":NumberDecimal("18889.09"),
"lenght" :1.79
};
> db.users.insert(user1); 保存user1对象到users集合里去
> var date = new Date(); 定义一个日期
> print(date) 打印日期对象
Wed Oct 20 2021 16:22:28 GMT+0000 (UTC)
| Mongodb与Mysql的SQL语法对比 | ||
|---|---|---|
| Mongodb | MySql | |
| 新建Database | use lision; show dbs; | create database lison; show datebases; |
| 新建Users表 | db.users; show collections; 或者 show tables; | create table users{ id bigint(32), username varchar(20), money double(20,2), birthday datetime, address_id bigint(32) -- address表的主键 }; show tables; |
| 清空表数据 | db.users.drop() | truncate table users; 或者 delete from users; |
| insert | var user1 = { “id”: 1, "username": "ZhangSan", "money": NumberDecimal("10.88") "birthday": ISODate("2019-11-26T13:26:21.086Z"), "address": { "code":"411000" “adress_detail”:"长沙" }, "favorites": { "movies":["蜘蛛侠","钢铁侠","蝙蝠侠"], "cites":["北京","杭州","上海"] } }; db.users.insert(user1); | insert into users (id, username, money, birthday, address_id) values (1, "ZhangSan", 10.88, 10); |
| select | db.users.find({"username":"ZhangSan"}) db.users.find({ "favorites.cites" : { "$all" : [ "北京" , "东京"]} }).pretty(); db.users.find({ "$and" : [ { "username" : { "$regex" : ".*s.*"}} , { "$or" : [ { "country" : "English"} , { "country" : "USA"} ]} ] }).pretty(); ------------分隔符 1--------------- db.users.find({"username":"ZhangSan"})[0].age; 或者： >var d = db.users.find({"username":"ZhangSan"})[0].age; >d ------------分隔符 2--------------- >var date = new Date(); >date 打印日期 >ISODate("2021-10-01T10:20:20.086Z") >var i = 0; >print(i); 打印对象 >0 | select * from users where username = "ZhangSan"; select * from users where favorites.cites in ("北京","东京"); select * from users where username like '%s%' and (country= English or country= USA); ------------分隔符 1--------------- select age from users where username = "ZhangSan" limit 1; ------------分隔符 2--------------- select now() from dual; >2021-10-01 10:20:20 select 0 from dual; >0 |
| update | db.users.updateMany( {"username":"lison"}, {"$set":{"age":6}} ); ------------分隔符 1--------------- >var old_age = db.users.find({"username":"ZhangSan"})[0].age; >db.users.updateMany( {"username":"lison"}, {"$set":{"age":old_age+1}} ); ------------分隔符 2--------------- db.users.updateMany( { "favorites.cites" : "纽约"}, { "$addToSet" : { "favorites.movies" : { "$each" : [ "黑寡妇" , "超人"]} } } ,true); | update users set age = 6 where username = 'lison'; ------------分隔符 1--------------- update users set age = (age+1) where username = 'lison'; ------------分隔符 2--------------- update users set favorites.movies = concat(favorites.movies,"黑寡妇","超人") where favorites.cites = '纽约'; |
| delete | db.users.deleteMany({ "username" : "lison"} ); db.users.deleteMany( {"$and" : [ {"age" : {"$gt": 8}} , {"age" : {"$lt" : 25}} ]} ); | delete from users where username = 'lison'; delete from users where age >8 and age <25; |
注意：mongodb里的事务必须是在mongodb的集群模式里才支持。
其他常用命令
db.help() 数据库的帮助命令
db.collection.help() 集合的帮助命令
show dbs 显示数据库列表
show collections 显示集合列表
db 显示当前数据库
db.stats() 显示数据库信息
db.serverStatus() 查看服务器状态
db.dropDatabase() 删除数据库
db.users.find.size() 获取被查询集合的文档数量
db.users.drop() 删除集合
end.
