Comparison of MongoDB commands and SQL statement syntax

Comparison of mongodb and mysql commands

Traditional relational databases are generally composed of three levels of concepts: database, table, and record. MongoDB is composed of database, collection, and document Composed of three levels. For tables in relational databases, MongoDB does not have the concept of columns, rows, and relationships in the collection, which reflects the characteristics of free schema.

MySQL

MongoDB

Description

mysqld

mongod

server daemon

mysql

mongo

client tool

mysqldump

mongodump

Logical Backup Tool

mysql

mongorestore

Logic Recovery Tool

db. repairDatabase()

repair database

mysqldump

mongoexport

Data Export Tool

source

mongo import

data import tool

grant * privileges>Db. addUser()

Db.auth()

Create a new user and permissions

show databases

show dbs

show library list

Show tables

Show collections

show table list

Show slave status

Rs.status

Query master-slave status

Create table users(a int, b int)

db.createCollection(“mycoll”, {capped:true,

size:100000}) Another: tables can be created implicitly.

create table

Create INDEX idxname>db.users.ensureIndex({name:1})

create index

Create INDEX idxname>db.users.ensureIndex({name:1,ts:-1})

create index

Insert into users values(1, 1)

db. users. insert({a:1, b:1})

insert record

Select a, b from users

db.users.find({},{a:1,b:1})

query form

Select * from users

db. users. find()

query form

Select * from users where age=33

db.users.find({age:33})

conditional query

Select a, b from users where age=33

db.users.find({age:33},{a:1,b:1})

conditional query

select * from users where age<33

db.users.find({‘age’:{$lt:33}})

conditional query

select * from users where age>33 and age<=40

db.users.find({‘age’:{$gt:33,$lte:40}})

conditional query

select * from users where a=1 and b=’q’

db.users.find({a:1,b:’q’})

conditional query

select * from users where a=1 or b=2

db.users.find( { $or : [ { a : 1 } , { b : 2 } ] } )

conditional query

select * from users limit 1

db.users.findOne()

conditional query

select * from users where name like “%Joe%”

db.users.find({name:/Joe/})

fuzzy query

select * from users where name like “Joe%”

db.users.find({name:/^Joe/})

fuzzy query

select count(1) from users

Db. users. count()

Get the number of table records

select count(1) from users where age>30

db.users.find({age: {‘$gt’: 30}}).count()

Get the number of table records

select DISTINCT last_name from users

db.users.distinct(‘last_name’)

Remove duplicate values

select * from users ORDER BY name

db.users.find().sort({name:-1})

to sort

select * from users ORDER BY name DESC

db.users.find().sort({name:-1})

to sort

EXPLAIN select * from users where z=3

db.users.find({z:3}).explain()

Get storage path

update users set a=1 where b=’q’

db.users.update({b:’q’}, {$set:{a:1}}, false, true)

update record

update users set a=a+2 where b=’q’

db.users.update({b:’q’}, {$inc:{a:2}}, false, true)

update record

delete from users where z=”abc”

db.users.remove({z:’abc’})

Delete Record

db.users.remove()

delete all records

drop database IF EXISTS test;

use test

db. dropDatabase()

delete database

drop table IF EXISTS test;

db.mytable.drop()

drop table/collection

db. addUser(‘test’, ‘test’)

Add user

readOnly–>false

db. addUser(‘test’, ‘test’, true)

Add user

readOnly–>true

db.addUser(“test”,”test222″)

change password

db.system.users.remove({user:”test”})

or db.removeUser(‘test’)

delete users

use admin

root

db.auth(‘test’, ‘test’)

user authorization

db.system.users.find()

view user list

show users

view all users

db. printCollectionStats()

View the status of each collection

db. printReplicationInfo()

View master-slave replication�� state

show profile

view profiling

db.copyDatabase(‘mail_addr’, ‘mail_addr_tmp’)

copy database

db.users.dataSize()

View the size of collection data

db.users.totalIndexSize()

query index size

mongodb syntax

MongoDB has many benefits, such as multi-column index, some statistical functions can be used in query, and multi-condition query is supported. 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.

MongoDB has a lot of operations on data. Here are some examples to illustrate. Most of the content comes from official documents, and some of them are for my own understanding.

Query all data of colls

db.colls.find() //select * from colls

Query by specifying conditions

db.colls.find({‘last_name’: ‘Smith’});//select * from colls
where last_name=’Smith’

Specify multi-condition query

db.colls.find( { x : 3, y : “foo” } );//select * from colls
where x=3 and y=’foo’

Specified range query

db.colls.find({j: {$ne: 3}, k: {$gt: 10} });//select * from
colls where j!=3 and k>10

query does not include something

db.colls.find({}, {a:0});//Query all data except a is 0

Support <, , >= query, need to use symbols to replace $lt, $lte, $gt, $gte

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 perform range queries on a field

db.colls.find({ “field” : { $gt: value1, $lt: value2 } } );

Not equal to query character $ne

db. colls. find( { x : { $ne : 3 } } );

in query with 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]}});

The character $mod is used for modulo query

db.colls.find( { a : { $mod : [ 10 , 1 ] } } )// where a % 10 ==
1

$all query

db.colls.find( { a: { $all: [ 2, 3 ] } } );//When specifying a satisfies any value in the array

$size query

db.colls.find( { a : { $size: 1 } }
);//Query the number of objects, this query queries the record with the number of sub-objects of a being 1

$exists query

db.colls.find( { a : { $exists : true } } ); // data of object a exists

db.colls.find( { a : { $exists : false } } ); // data of object a does not exist

$type query $type value is the type value of bsonhttp://bsonspec.org/ data

db.colls.find( { a : { $type : 2 } } ); // match a as string type data

db.colls.find( { a : { $type : 16 } } ); // match a is int type data

Match using regular expressions

db.colls.find( { name : /acme.*corp/i } );//Similar to like in SQL

Inline object query

db.colls.find( { “author.name” : “joe” } );

Versions 1.3.3 and later include $not queries

db.colls.find( { name : { $not : /acme.*corp/i } } );

db.colls.find( { a : { $not : { $mod : [ 10 , 1 ] } } } );

sort()Sort

db.colls.find().sort( { ts : -1 } );//1 is ascending order 2 is descending order

limit() returns the number of limit query data

db.colls.find().limit(10)

skip() skips some data

db.colls.find().skip(10)

snapshot() snapshot guarantees that no duplicate data is returned or objects are lost

count() counts the number of query objects

db.students.find({‘address.state’ : ‘CA’}).count();//higher efficiency

db.students.find({‘address.state’:
‘CA’}).toArray().length;//Very inefficient

group() grouping query results is similar to the group by function in SQL

distinct() returns unique values