MongoDB series 5: mongo grammar vs. mysql grammar
- 2020-05-27 07:27:49
- OfStack
We always see our own advantages and disadvantages in the comparison, which is the same for mongodb. Comparative learning enables us to master the basic knowledge of mongodb as soon as possible.
mongodb vs. mysql commands
Relational database 1 is generally composed of three hierarchical concepts: database (database), table (table), and record (record). The non-relational database mongodb is composed of three levels: database (database), collection (collection), and document object (document). For tables in a relational database, mongodb has no concept of a relationship between rows and columns, which reflects the freedom of the schema.
The syntax commands are shown in the following table
|
MySQL |
MongoDB |
说明 |
|
mysqld |
mongod |
服务器守护进程 |
|
mysql |
mongo |
客户端工具 |
|
mysqldump |
mongodump |
逻辑备份工具 |
|
mysql |
mongorestore |
逻辑恢复工具 |
|
|
db.repairDatabase() |
修复数据库 |
|
mysqldump |
mongoexport |
数据导出工具 |
|
source |
mongoimport |
数据导入工具 |
|
grant * privileges on *.* to … |
Db.addUser() Db.auth() |
新建用户并权限 |
|
show databases |
show dbs |
显示库列表 |
|
Show tables |
Show collections |
显示表列表 |
|
Show slave status |
Rs.status |
查询主从状态 |
|
Create table users(a int, b int) |
db.createCollection("mycoll", {capped:true, size:100000}) 另：可隐式创建表。 |
创建表 |
|
Create INDEX idxname ON users(name) |
db.users.ensureIndex({name:1}) |
创建索引 |
|
Create INDEX idxname ON users(name,ts DESC) |
db.users.ensureIndex({name:1,ts:-1}) |
创建索引 |
|
Insert into users values(1, 1) |
db.users.insert({a:1, b:1}) |
插入记录 |
|
Select a, b from users |
db.users.find({},{a:1, b:1}) |
查询表 |
|
Select * from users |
db.users.find() |
查询表 |
|
Select * from users where age=33 |
db.users.find({age:33}) |
条件查询 |
|
Select a, b from users where age=33 |
db.users.find({age:33},{a:1, b:1}) |
条件查询 |
|
select * from users where age<33 |
db.users.find({'age':{$lt:33}}) |
条件查询 |
|
select * from users where age>33 and age<=40 |
db.users.find({'age':{$gt:33,$lte:40}}) |
条件查询 |
|
select * from users where a=1 and b='q' |
db.users.find({a:1,b:'q'}) |
条件查询 |
|
select * from users where a=1 or b=2 |
db.users.find( { $or : [ { a : 1 } , { b : 2 } ] } ) |
条件查询 |
|
select * from users limit 1 |
db.users.findOne() |
条件查询 |
|
select * from users where name like "%Joe%" |
db.users.find({name:/Joe/}) |
模糊查询 |
|
select * from users where name like "Joe%" |
db.users.find({name:/^Joe/}) |
模糊查询 |
|
select count(1) from users |
Db.users.count() |
获取表记录数 |
|
select count(1) from users where age>30 |
db.users.find({age: {'$gt': 30}}).count() |
获取表记录数 |
|
select DISTINCT last_name from users |
db.users.distinct('last_name') |
去掉重复值 |
|
select * from users ORDER BY name |
db.users.find().sort({name:-1}) |
排序 |
|
select * from users ORDER BY name DESC |
db.users.find().sort({name:-1}) |
排序 |
|
EXPLAIN select * from users where z=3 |
db.users.find({z:3}).explain() |
获取存储路径 |
|
update users set a=1 where b='q' |
db.users.update({b:'q'}, {$set:{a:1}}, false, true) |
更新记录 |
|
update users set a=a+2 where b='q' |
db.users.update({b:'q'}, {$inc:{a:2}}, false, true) |
更新记录 |
|
delete from users where z="abc" |
db.users.remove({z:'abc'}) |
删除记录 |
|
|
db. users.remove() |
删除所有的记录 |
|
drop database IF EXISTS test; |
use test db.dropDatabase() |
删除数据库 |
|
drop table IF EXISTS test; |
db.mytable.drop() |
删除表/collection |
|
|
db.addUser(‘test', 'test') |
添加用户 readOnly-->false |
|
|
db.addUser(‘test', 'test', true) |
添加用户 readOnly-->true |
|
|
db.addUser("test","test222") |
更改密码 |
|
|
db.system.users.remove({user:"test"}) 或者db.removeUser('test') |
删除用户 |
|
|
use admin |
超级用户 |
|
|
db.auth(‘test', ‘test') |
用户授权 |
|
|
db.system.users.find() |
查看用户列表 |
|
|
show users |
查看所有用户 |
|
|
db.printCollectionStats() |
查看各collection的状态 |
|
|
db.printReplicationInfo() |
查看主从复制状态 |
|
|
show profile |
查看profiling |
|
|
db.copyDatabase('mail_addr','mail_addr_tmp') |
拷贝数据库 |
|
|
db.users.dataSize() |
查看collection数据的大小 |
|
|
db. users.totalIndexSize() |
查询索引的大小 |
mongodb syntax description
There are many syntax of mongodb, such as multi-column index, which can be used for statistical function and support multi-condition query. However, multi-table query is not supported at present, so we can find a way to solve the problem of multi-table query through data redundancy. Examples are shown below.
Query all colls data
db.colls.find() //select * from colls
Query by specifying conditions
db.colls.find({ ' last_name': ' Smith'});//select * from colls where last_name='Smith'
Specify a multi-conditional query
db.colls.find( { x : 3, y : " foo " } );//select * from colls where x=3 and y='foo'
Specify a conditional range query
db.colls.find({j: {$ne: 3}, k: {$gt: 10} });//select * from colls where j!=3 and k>10
The query does not include something
db.colls.find({}, {a:0});// Query except a for 0 All the data outside
support
<
,
<
=,
>
,
>
= query, using symbol substitutions of $lt, $lte, $gt, $gte, respectively
db.colls.find({ " field " : { $gt: value } } );
db.colls.find({ " field " : { $lt: value } } );
db.colls.find({ " field " : { $gte: value } } );
db.colls.find({ " field " : { $lte: value } } );
You can also do a range query for a 1 field
db.colls.find({ " field " : { $gt: value1, $lt: value2 } } );
Does not equal query with the character $ne
db.colls.find( { x : { $ne : 3 } } );
The in query USES the character $in
db.colls.find( { " field " : { $in : array } } );
db.colls.find({j:{$in: [2,4,6]}});
not in query with the character $nin
db.colls.find({j:{$nin: [2,4,6]}});
Queried queries with the character $mod
db.colls.find( { a : { $mod : [ 10 , 1 ] } } )// where a % 10 == 1
$all query
db.colls.find( { a: { $all: [ 2, 3 ] } } );// The specified a That satisfies any value in the array
$size query
db.colls.find( { a : { $size: 1 } } );// The query queries the number of objects a The number of child objects is 1 The record of
$exists query
db.colls.find( { a : { $exists : true } } ); // There are a Object data
db.colls.find( { a : { $exists : false } } ); // There is no a Object data
$$type type query value of bsonhttp: / / bsonspec org/number according to the type of value
db.colls.find( { a : { $type : 2 } } ); // matching a for string Type data
db.colls.find( { a : { $type : 16 } } ); // matching a for int Type data
Use regular expression matching
db.colls.find( { name : /acme.*corp/i } );// Similar to the SQL In the like
Inline object queries
db.colls.find( { " author.name " : " joe " } );
Version 1.3.3 and later includes $not queries
db.colls.find( { name : { $not : /acme.*corp/i } } );
db.colls.find( { a : { $not : { $mod : [ 10 , 1 ] } } } );
sort sort ()
db.colls.find({ ' last_name': ' Smith'});//select * from colls where last_name='Smith'
limit() returns the number of restricted query data
db.colls.find({ ' last_name': ' Smith'});//select * from colls where last_name='Smith'
skip() skips some data
db.colls.find( { x : 3, y : " foo " } );//select * from colls where x=3 and y='foo'
The snapshot() snapshot ensures that no duplicate data is returned or objects are lost
count() counts the number of query objects
db.students.find({ ' address.state' : ' CA'}).count();// High efficiency
db.students.find({ ' address.state' : ' CA'}).toArray().length;// Efficiency is very low
group() groups query results similarly to the group by function in SQL
distinct() returns no duplicate values