mongo数据库说明:
一些概念,一个mongod服务可以有建立多个数据库,每个数据库可以有多张表,这里的表名叫collection,每个collection可以存放多个文档(document),这个文档就是一行记录,每个文档都以BSON(binary json)的形式存放于硬盘中,因此可以存储比较复杂的数据类型。
MongoDB没有创建数据库的命令:
如果你想创建一个“myTest”的数据库,先运行use myTest命令,之后就做一些操作(如:db.createCollection(‘user’)),这样就可以创建一个名叫“myTest”的数据库。
- 启动mongo数据库
[root@localhost /]# service mongod start
- 查看mongo服务是否启动
[root@localhost /]# ps -ef | grep mongod
- 进入mong操作界面
[root@localhost /]# mongo
- 查看数据库
> show dbs
- 创建/切换数据库
> use local
6、查看当前数据库的collection(表)
> show collections
- 查看collection为startup_log的document数量(表startup_log的行数)
> db.startup_log.count()
- 查看collection的数据大小
> db.startup_log.dataSize()
- 查看colleciont状态
> db.startup_log.stats()
{
"ns" : "local.startup_log",
"count" : 3,
"size" : 3680,
"avgObjSize" : 1226,
"numExtents" : 1,
"storageSize" : 10485760,
"lastExtentSize" : 10485760,
"paddingFactor" : 1,
"paddingFactorNote" : "paddingFactor is unused and unmaintained in 3.0. It remains hard coded to 1.0 for compatibility only.",
"userFlags" : 1,
"capped" : true,
"max" : NumberLong("9223372036854775807"),
"maxSize" : 10485760,
"nindexes" : 1,
"totalIndexSize" : 8176,
"indexSizes" : {
"_id_" : 8176
},
"ok" : 1
}
- 查看索引值得大小
> db.startup_log.totalIndexSize()
创建collection
>db.createCollection("student", {name: "张三", age: 20, sex: "0", class: "03104010601", math: 80, english: 81, computer: 82});
判断集合是否为定容
>db.student.isCapped();
终端mongo增、删、改、查操作
- 插入
1、1 插入一条记录
>db.student.insert({name: "黄珊 ",age: 20, sex : "0" , class: "03104010601", math: 80, english: 81, computer: 82});
1、2 插入记录数组
(1) 定义需要插入的documents
> var mydocuments = [{name: "黄珊1",age: 21, sex : "0" , class: "03104010601", math: 90, english: 81, computer: 82},{name: "黄珊2",age: 22, sex : "0" , class: "03104010601", math: 85, english: 81, computer: 82}];
- 向collection中插入documents
> db.student.insert(mydocuments);
返回结果:
BulkWriteResult({
"writeErrors" : [ ],
"writeConcernErrors" : [ ],
"nInserted" : 2,
"nUpserted" : 0,
"nMatched" : 0,
"nModified" : 0,
"nRemoved" : 0,
"upserted" : [ ]
})
1、3 批处理插入记录(bulk())
- 初始化批量操作对象
var bulk = db.inventory.initializeUnorderedBulkOp();
- 添加对象
bulk.insert(
{
item: "BE10",
details: { model: "14Q2", manufacturer: "XYZ Company" },
stock: [ { size: "L", qty: 5 } ],
category: "clothing"
}
);
bulk.insert(
{
item: "ZYT1",
details: { model: "14Q1", manufacturer: "ABC Company" },
stock: [ { size: "S", qty: 5 }, { size: "M", qty: 5 } ],
category: "houseware"
}
);
- 提交对象
bulk.execute();
返回结果:
BulkWriteResult({
"writeErrors" : [ ],
"writeConcernErrors" : [ ],
"nInserted" : 2,
"nUpserted" : 0,
"nMatched" : 0,
"nModified" : 0,
"nRemoved" : 0,
"upserted" : [ ]
})
- 查询
2、1 查询所有记录
> db.student.find({}); 或> db.student.find();
相当于 select * from student;
默认每页显示20条记录,当显示不下的情况下,可以用it迭代命令查询下一页数据。注意:键入it命令不能带";",如下截图:
2、2 查询去掉当前聚集集合中的某列的重复数据
> db.student.distinct("math");
相当于 select distinct math from student;
2、3 排序
> db.student.distinct("math").sort({math:1});
相当于 select distinct math from student order by math asc;
2、4 查询math=80的记录
> db.student.find({"math":80});
相当于 select * from student where math=80;
2、5 查询math>80的记录
> db.student.find({"math":{$gt:80}});
相当于 select * from student where math > 80;
大于等于 >= $gt;小于 < $lt;小于等于 <= $lte
2、6 查询math>=80并且math<=90的记录
> db.student.find({"math":{$gte:80,$lte:90}});
相当于 select * from student where math >= 80 and math <= 90;
2、7查询name中包含梁的数据
> db.student.find({"name":/梁/});
相当于 select * from student where name like‘%梁%';
> db.student .find({name: /^梁/});
select * from student where name like ‘梁%';
2、8 查询指定列的数据
> db.student.find({},{"name":1,"age":1});
当然name也可以用true或false,当用ture的情况下和name:1效果一样,如果用false就是排除name,显示name以外的列信息。
2、9 查询指定列的数据并且math >=80的数据
> db.student.find({"math":{$gte:80}},{"name":true,"age":true,"math":true});
相当于 select name,age,math from student where math >= 80;
2、10 查询前5条数据
> db.student.find({}).limit(5);
相当于 select t.*,t.rowno rn from student t where rn <= 5;
2、11 查询10条以后的数据
> db.student.find({}).skip(10);
相当于 select t.*,t.rowno rn from student t where rn > 10;
2、12 查询5-10之间的数据(脚本不对)
> db.student.find({}).limit(10).skip(2);(查询的结果为 大于2之后的10条数据)
可用于分页,limit是pageSize,skip是第几页*pageSize
例如pageSize为10,查询第一页的数据
> db.student.find({}).limit(10).skip(0);
2、13 and连接查询
> db.student.find({$or:[{"name":/黄/,"math":{$gte:80}}]});
相当于 select * from student where name like ‘%黄%’ and math >= 80;
2、14 or连接查询
> db.student.find({$or:[{"name":/黄/},{"math":{$gte:80}}]});(自动去重)
相当于 select * from student where name like ‘%黄%’ or math >= 80; (可能有重复的数据)
2、15 查询一条数据
> db.student.findOne({}); 或者 > db.student.find({}).limit(1);
相当于 select t.*,t.rowno rn from student t where rn = 1;
2、16 查询某个结果集的记录数
> db.student.find({"math":{$gte:83}}).count();
2、17 使用$elemMath操作符
db.inventory.find( { ratings: { $elemMatch: { $gt: 5, $lt: 9 } } } )
查询ratings数组中至少包含一个元素,这个元素大于5小于9
2、18 数组内嵌查询
examples:
{
_id: 100,
type: "food",
item: "xyz",
qty: 25,
price: 2.5,
ratings: [ 5, 8, 9 ],
memos: [ { memo: "on time", by: "shipping" }, { memo: "approved", by: "billing" } ]
}
{
_id: 101,
type: "fruit",
item: "jkl",
qty: 10,
price: 4.25,
ratings: [ 5, 9 ],
memos: [ { memo: "on time", by: "payment" }, { memo: "delayed", by: "shipping" } ]
}
- 使用数组索引
db.inventory.find( { 'memos.0.by': 'shipping' } )
results:
{
_id: 100,
type: "food",
item: "xyz",
qty: 25,
price: 2.5,
ratings: [ 5, 8, 9 ],
memos: [ { memo: "on time", by: "shipping" }, { memo: "approved", by: "billing" } ]
}
- 不使用数组索引
db.inventory.find( { 'memos.by': 'shipping' } )
Results:
{
_id: 100,
type: "food",
item: "xyz",
qty: 25,
price: 2.5,
ratings: [ 5, 8, 9 ],
memos: [ { memo: "on time", by: "shipping" }, { memo: "approved", by: "billing" } ]
}
{
_id: 101,
type: "fruit",
item: "jkl",
qty: 10,
price: 4.25,
ratings: [ 5, 9 ],
memos: [ { memo: "on time", by: "payment" }, { memo: "delayed", by: "shipping" } ]
}
(3) $elemMatch
db.inventory.find({memos:{$elemMatch:{memo: 'on time',by: 'shipping'}}})
results:
{
_id: 100,
type: "food",
item: "xyz",
qty: 25,
price: 2.5,
ratings: [ 5, 8, 9 ],
memos: [ { memo: "on time", by: "shipping" }, { memo: "approved", by: "billing" } ]
}
(4) 与3比较结果
db.inventory.find({'memos.memo': 'on time','memos.by': 'shipping'})
results:
{
_id: 100,
type: "food",
item: "xyz",
qty: 25,
price: 2.5,
ratings: [ 5, 8, 9 ],
memos: [ { memo: "on time", by: "shipping" }, { memo: "approved", by: "billing" } ]
}
{
_id: 101,
type: "fruit",
item: "jkl",
qty: 10,
price: 4.25,
ratings: [ 5, 9 ],
memos: [ { memo: "on time", by: "payment" }, { memo: "delayed", by: "shipping" } ]
}
- 修改
3、1 更新文档具体的属性值
3、1、1 修改属性值($set)
> db.student.update({"name":/黄辉/},{$set:{"name":"黄辉"}});
results:
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
3、1、2 修改嵌入型(embedded)的属性
db.inventory.update(
{ item: "ABC1" },
{ $set: { "details.model": "14Q2" } }
)
3、1、3 修改多条记录
db.inventory.update(
{ category: "clothing" },
{
$set: { category: "apparel" },
$currentDate: { lastModified: true }
},
{ multi: true }
)
3、2 替换文档(一行记录)
3、2、1 替换文档
db.inventory.update(
{ item: "BE10" },
{
item: "BE05",
stock: [ { size: "S", qty: 20 }, { size: "M", qty: 5 } ],
category: "apparel"
}
)
3、2、2 使用upsert
db.inventory.update(
{ item: "TBD1" },
{
item: "TBD1",
details: { "model" : "14Q4", "manufacturer" : "ABC Company" },
stock: [ { "size" : "S", "qty" : 25 } ],
category: "houseware"
},
{ upsert: true }
)
results:
WriteResult({
"nMatched" : 0,
"nUpserted" : 1,
"nModified" : 0,
"_id" : ObjectId("53dbd684babeaec6342ed6c7")
})
添加了upsert参数后执行update操作,如果有匹配item为”TBD1”的则替换为新的文档,如果没有则创建一个新的文档。
- 删除
4、1 删除所有文档(记录)
db.inventory.remove({}) 没有删除index
drop({}) 删除更高效,删除了索引
4、2 删除满足条件的文档
db.inventory.remove( { type : "food" } )
4、3 删除满足条件的一行记录
db.inventory.remove( { type : "food" }, 1 )
比较查询运算符(Comparison Query Operators)
- $eq (==)
{ <field>: { $eq: <value> } }
examples(inventory)
{ _id: 1, item: { name: "ab", code: "123" }, qty: 15, tags: [ "A", "B", "C" ] }
{ _id: 2, item: { name: "cd", code: "123" }, qty: 20, tags: [ "B" ] }
{ _id: 3, item: { name: "ij", code: "456" }, qty: 25, tags: [ "A", "B" ] }
{ _id: 4, item: { name: "xy", code: "456" }, qty: 30, tags: [ "B", "A" ] }
{ _id: 5, item: { name: "mn", code: "000" }, qty: 20, tags: [ [ "A", "B" ], "C" ] }
1、1 等于特定值
db.inventory.find( { qty: { $eq: 20 } } ) 相当于 db.inventory.find( { qty: 20 } )
results:
{ _id: 2, item: { name: "cd", code: "123" }, qty: 20, tags: [ "B" ] }
{ _id: 5, item: { name: "mn", code: "000" }, qty: 20, tags: [ [ "A", "B" ], "C" ] }
1、2 等于内嵌的某个属性的值,如item中的name,用dot notation处理
db.inventory.find( { "item.name": { $eq: "ab" } } )
相当于 db.inventory.find( { "item.name": "ab" } )
results:
{ _id: 1, item: { name: "ab", code: "123" }, qty: 15, tags: [ "A", "B", "C" ] }
1、3 等于数组中的值
db.inventory.find( { tags: { $eq: "B" } } ) 相当于 db.inventory.find({tags:"B" })
results:
{ _id: 1, item: { name: "ab", code: "123" }, qty: 15, tags: [ "A", "B", "C" ] }
{ _id: 2, item: { name: "cd", code: "123" }, qty: 20, tags: [ "B" ] }
{ _id: 3, item: { name: "ij", code: "456" }, qty: 25, tags: [ "A", "B" ] }
{ _id: 4, item: { name: "xy", code: "456" }, qty: 30, tags: [ "B", "A" ] }
1、4 等于数组
db.inventory.find( { tags: { $eq: [ "A", "B" ] } } )
相当于 db.inventory.find( { tags: [ "A", "B" ] } )
results:
{ _id: 3, item: { name: "ij", code: "456" }, qty: 25, tags: [ "A", "B" ] }
{ _id: 5, item: { name: "mn", code: "000" }, qty: 20, tags: [ [ "A", "B" ], "C" ] }
- $gt (>)
{field: {$gt: value} }
- $gte (>=)
{field: {$gte: value} }
- $lt (<)
{field: {$lt: value} }
- $lte (<=)
{field: {$lte: value} }
- $ne (!=)
{field: {$ne: value} }
- $in (in)
{ field: { $in: [<value1>, <value2>, ... <valueN> ] } }
7、1 匹配值
db.inventory.find( { qty: { $in: [ 5, 15 ] } } )
7、2 匹配数组
{ _id: 1, item: "abc", qty: 10, tags: [ "school", "clothing" ], sale: false }
db.inventory.update({ tags: { $in: ["appliances", "school"] } },{ $set: { sale:true } })
这里的in条件为或的关系,也就是至少满足一个条件
7、3 匹配正则表达式
db.inventory.find( { tags: { $in: [ /^be/, /^st/ ] } } )
查询tags中至少有一个元素以be或st开始
- $nin (not in)
{ field: { $nin: [ <value1>, <value2> ... <valueN> ]} }
逻辑查询运算符
- $or
{ $or: [ { <expression1> }, { <expression2> }, ... , { <expressionN> } ] }
db.inventory.find( { $or: [ { quantity: { $lt: 20 } }, { price: 10 } ] } )
select * from inventory where quantiy < 20 or price = 10;
- $and
{ $and: [ { <expression1> }, { <expression2> } , ... , { <expressionN> } ] }
2、1 查询使用多个表达式指定属性
db.inventory.find( { $and: [ { price: { $ne: 1.99 } }, { price: { $exists: true } } ] } )
相当于 db.inventory.find( { price: { $ne: 1.99, $exists: true } } ) (相当于 and)
select * from inventory where price != 1.99 and price is not null;
2、2 和查询具有多个表达式指定相同Operator
db.inventory.find( {
$and : [
{ $or : [ { price : 0.99 }, { price : 1.99 } ] },
{ $or : [ { sale : true }, { qty : { $lt : 20 } } ] }
]
} )
select * from inventory where (price = 0.99 or price = 1.99) and sale is not null or qty < 20;
- $not
{ field: { $not: { <operator-expression> } } }
db.inventory.find( { price: { $not: { $gt: 1.99 } } } )
查询price的值小于等于1.99或price属性不存在的数据记录
{$lte:1.99} 查询条件:查询price小于等于1.99的记录,没有包含属性不存在的记录
- $nor
{ $nor: [ { <expression1> }, { <expression2> }, ... { <expressionN> } ] }
db.inventory.find( { $nor: [ { price: 1.99 }, { sale: true } ] } )
任意一个条件都不可以满足
元素查询操作
- $exists (判断document中是否包含属性<field>)
{ field: { $exists: <boolean> } }
db.inventory.find( { qty: { $exists: true, $nin: [ 5, 15 ] } } )
查询条件:qty属性存在并且值不等于5和15
examples
(collection:records)
{ a: 5, b: 5, c: null }
{ a: 3, b: null, c: 8 }
{ a: null, b: 3, c: 9 }
{ a: 1, b: 2, c: 3 }
{ a: 2, c: 5 }
{ a: 3, b: 2 }
{ a: 4 }
{ b: 2, c: 4 }
{ b: 2 }
{ c: 6 }
db.records.find( { a: { $exists: true } } )
results
{ a: 5, b: 5, c: null }
{ a: 3, b: null, c: 8 }
{ a: null, b: 3, c: 9 }
{ a: 1, b: 2, c: 3 }
{ a: 2, c: 5 }
{ a: 3, b: 2 }
{ a: 4 }
db.records.find( { b: { $exists: false } } )
results:
{ a: 2, c: 5 }
{ a: 4 }
{ c: 6 }
- $type
{ field: { $type: <BSON type number> | <String alias> } }
Type | Number | Alias | Notes |
Double | 1 | “double” |
|
String | 2 | “string” |
|
Object | 3 | “object” |
|
Array | 4 | “array” |
|
Binary data | 5 | “binData” |
|
Undefined | 6 | “undefined” | Deprecated |
Object id | 7 | “objectId” |
|
Boolean | 8 | “bool” |
|
Date | 9 | “date” |
|
Null | 10 | “null” |
|
Regular Expression | 11 | “regex” |
|
DBPointer | 12 | “dbPointer” |
|
JavaScript | 13 | “javascript” |
|
Symbol | 14 | “symbol” |
|
JavaScript (with scope) | 15 | “javascriptWithScope” |
|
32-bit integer | 16 | “int” |
|
Timestamp | 17 | “timestamp” |
|
64-bit integer | 18 | “long” |
|
Min key | -1 | “minKey” |
|
Max key | 127 | “maxKey” |
|
评估查询运算符
- $mod
{ field: { $mod: [ divisor, remainder ] } }
{ "_id" : 1, "item" : "abc123", "qty" : 0 }
{ "_id" : 2, "item" : "xyz123", "qty" : 5 }
{ "_id" : 3, "item" : "ijk123", "qty" : 12 }
db.inventory.find( { qty: { $mod: [ 4, 0 ] } } )
{ "_id" : 1, "item" : "abc123", "qty" : 0 }
{ "_id" : 3, "item" : "ijk123", "qty" : 12 }
db.inventory.find( { qty: { $mod: [ 4 ] } } )
error: {
"$err" : "bad query: BadValue malformed mod, not enough elements",
"code" : 16810
}
db.inventory.find( { qty: { $mod: [ ] } } )
error: {
"$err" : "bad query: BadValue malformed mod, not enough elements",
"code" : 16810
}
error: { "$err" : "mod can't be 0", "code" : 10073 }
Too Many Elements Error
error: {
"$err" : "bad query: BadValue malformed mod, too many elements",
"code" : 16810
}
- $regex
- $text
- $where
附件1:SQL to MongoDB Mapping Chart
--https://docs.mongodb.org/manual/reference/sql-comparison/
1、Terminology and Concepts(术语和概念)
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 |
Specify any unique column or column combination as primary key. | In MongoDB, the primary key is automatically set to the _id field. |
aggregation (e.g. group by) | aggregation pipeline |
- Executables(可执行文件)
| MongoDB | MySQL | Oracle | Informix | DB2 |
Database Server | mongod | mysqld | oracle | IDS | DB2 Server |
Database Client | mongo | mysql | sqlplus | DB-Access | DB2 Client |
- Create and Alter(创建和修改)
SQL Schema Statements | MongoDB Schema Statements |
CREATE TABLE users ( id MEDIUMINT NOT NULL AUTO_INCREMENT, user_id Varchar(30), age Number, status char(1), PRIMARY KEY (id) ) | db.users.insert( { user_id: "abc123", age: 55, status: "A" } )
db.createCollection("users") |
ALTER TABLE users ADD join_date DATETIME
| db.users.update( { }, { $set: { join_date: new Date() } }, { multi: true } ) |
ALTER TABLE users DROP COLUMN join_date | db.users.update( { }, { $unset: { join_date: "" } }, { multi: true } ) |
CREATE INDEX idx_user_id_asc ON users(user_id) | db.users.createIndex( { user_id: 1 } ) |
CREATE INDEX idx_user_id_asc_age_desc ON users(user_id, age DESC) | db.users.createIndex( { user_id: 1, age: -1 } ) |
DROP TABLE users | db.users.drop() |
- Insert(插入)
SQL INSERT Statements | MongoDB insert() Statements |
INSERT INTO users(user_id,age,status) VALUES ("bcd001", 45, "A") | db.users.insert( { user_id: "bcd001", age: 45, status: "A" } ) |
- select(查询)
SQL SELECT Statements | MongoDB find() Statements |
select * from users | db.users.find() |
select id,user_id,status from users | db.users.find( { }, { user_id: 1, status: 1 } ) |
select user_id, status from users | db.users.find( { }, { user_id: 1, status: 1, _id: 0 } ) |
select * from users where status = "A" | db.users.find( { status: "A" } ) |
SELECT user_id, status FROM users WHERE status = "A" | db.users.find( { status: "A" }, { user_id: 1, status: 1, _id: 0 } ) |
SELECT * FROM users WHERE status != "A" | db.users.find( { status: { $ne: "A" } } ) |
SELECT * FROM users WHERE status = "A" AND age = 50 | db.users.find( { status: "A", age: 50 } ) |
SELECT * FROM users WHERE status = "A" OR age = 50 | db.users.find( { $or: [ { status: "A" } , { age: 50 } ] } ) |
SELECT * FROM users WHERE age > 25 | db.users.find( { age: { $gt: 25 } } ) |
SELECT * FROM users WHERE age < 25 | db.users.find( { age: { $lt: 25 } } ) |
SELECT * FROM users WHERE age > 25 AND age <= 50 | db.users.find( { age: { $gt: 25, $lte: 50 } } ) |
SELECT * FROM users WHERE user_id like "%bc%" | db.users.find( { user_id: /bc/ } ) |
SELECT * FROM users WHERE user_id like "bc%" | db.users.find( { user_id: /^bc/ } ) |
SELECT * FROM users WHERE status = "A" ORDER BY user_id ASC | db.users.find( { status: "A" } ).sort( { user_id: 1 } ) |
SELECT * FROM users WHERE status = "A" ORDER BY user_id DESC | db.users.find( { status: "A" } ).sort( { user_id: -1 } ) |
SELECT COUNT(*) FROM users | db.users.count() or db.users.find().count() |
SELECT COUNT(user_id) FROM users | db.users.count( { user_id: { $exists: true } } ) or db.users.find({user_id: { $exists: true } } ).count() |
SELECT COUNT(*) FROM users WHERE age > 30 | db.users.count( { age: { $gt: 30 } } ) or db.users.find( { age: { $gt: 30 } } ).count() |
SELECT DISTINCT(status) FROM users | db.users.distinct( "status" ) |
SELECT * FROM users LIMIT 1 | db.users.findOne() or db.users.find().limit(1) |
SELECT * FROM users LIMIT 5 SKIP 10 | db.users.find().limit(5).skip(10) |
EXPLAIN SELECT * FROM users WHERE status = "A" | db.users.find( { status: "A" } ).explain() |
- 更新记录(Update Records)
SQL Update Statements | MongoDB update() Statements |
UPDATE users SET status = "C" WHERE age > 25 | db.users.update( { age: { $gt: 25 } }, { $set: { status: "C" } }, { multi: true } ) |
UPDATE users SET age = age + 3 WHERE status = "A" | db.users.update( { status: "A" } , { $inc: { age: 3 } }, { multi: true } ) |
- 删除记录(Delete Records)
SQL Delete Statements | MongoDB remove() Statements |
DELETE FROM users WHERE status = "D" | db.users.remove( { status: "D" } ) |
DELETE FROM users | db.users.remove({}) |
附件2:SQL to Aggregation Mapping Chart
--https://docs.mongodb.org/manual/reference/sql-aggregation-comparison/
SQL Terms, Functions, and Concepts | MongoDB Aggregation Operators |
WHERE | $match |
GROUP BY | $group |
HAVING | $match |
SELECT | $project |
ORDER BY | $sort |
LIMIT | $limit |
SUM() | $sum |
COUNT() | $sum |
join | No direct corresponding operator; however, the $unwind operator allows for somewhat similar functionality, but with fields embedded within the document. |
examples-->见网站链接