mongodb mysql query문 비교

 

 

 

* DDL

 

mysql	mongodb
CREATE TABLE members( mem_no varchar(30), age number, type char(1), primary key(mem_no) );	db.members.insert({ mem_no :"", age : "", type : "" }) 또는 db.createCollection("members")
ALTER TABLE members ADD regist_data DATA;
ALTER TABLE members DROP COLUM regist_data;
CREATE INDEX i_members_type ON members(type);	db.members.ensureIndex( { type : 1 } );
CREATE INDEX i_members_type_no ON members(type,mnem_no DESC)	db.members.ensureIndex( { type : 1 , mem_no -1 } );
DROP TABLE members;	db.members.drop()

 

 

*DML

 

 

mysql	mongodb
INSERT into members( mem_no, age, type) VALUES ("1",30,"GOLD")	db.members.insert({ mem_no : "1", age : 30, type :"type" )}
SELECT * FROM members;	db.members.find();
SELECT rowid,mem_no, type FROM members;	db.members.find( { }, { mem_no 1, type:1} );
SELECT mem_no, type FROM members;	db.members.find( { }, { mem_no 1, type:1, _id:0 } );
SELECT * FROM members WHERE mem_no = "ACE";	db.members.find( { type : "ACE" } );
SELECT mem_no, type FROM members WHERE mem_no = "ACE";	db.members.find( { type : "ACE" }, { mem_no : 1, type : 1, _id : 0 } );
SELECT * FROM members WHERE type !="ACE";	db.members.find( { type : { $ne : "ACE" } } );
SELECT * FROM members WHERE type = "ACE" AND age = 49;	db.members.find( { type : "ACE" , age : 49 } );
SELECT * FROM members WHERE type = "ACE" OR age = 49;	db.members.find( { $or : [ { type : "ACE" } , { age : 49} ] } );
SELECT * FROM members WHERE age > 45;	db.members.find( { age : { $gt :45 } } );
SELECT * FROM members WHERE age < 55;	db.members.find( { age : { $lt :55 } } );
SELECT * FROM members WHERE age > 45 AND age <= 55;	db.members.find( { age : { $gt :45, $lte :55 } } );
SELECT * FROM members WHERE mem_no LIKE "%2013%"	db.members.find( { mem_no : /2013/ } );
SELECT * FROM members WHERE mem_no LIKE "T%"	db.members.find( { mem_no : /^T/ } );
SELECT * FROM members WHERE type = "ACE" ORDER BY mem_no ASC;	db.members.find( { type : "ACE"}).sort({mem_no :1});
SELECT * FROM members WHERE type = "ACE" ORDER BY mem_no DESC;	db.members.find( { type : "ACE"}).sort({mem_no : -1});
SELECT COUNT(*) FROM members;	db.members.count() db.members.find().count()
SELECT COUNT(mem_no) FROM members;	db.members.find().count({ mem_no : {exists:true}})   db.members.find({mem_no : {exists:true}}).count()
SELECR count(*) FROM members WHEREage > 45;	db.members.find().count( { age : { $gt : 45 } } )   db.members.find({ age : { $gt : 45 } }).count()
SELECT DISTINCT type FROM members;	db.members.distinct('type')
SELECT * FROM members WHERE rownum =1;	db.members.findOne() db.members(find).limit(1)
EXPLAIN PLAN SELECT * FROM members WHERE type = "ACE"	db.members.find( { type : "ACE" } ).exokain()
UPDATE members SET type = "GOLD" WHERE age >45	db.members.update( { age : {%gt : 45 } }, { $set : { type : "GOLD" } }, { multi : true } )
UPDATE members SET age = age +3 WHERE type = "ACE"	db.members.update( { type : "ACE" }, { $set : { age: 3 } }, { multi : true } )
DELETE FROM members WHERE type = "ACE"	db.members.remove( { type : "ACE" } )
DELETE FROM members	db.members.remove()

 

 

 

* update문 사용 시 기본적으로 조건을 만족하는 첫번째만 변경이 됨.

이 경우 모든 도큐먼트를 변경해야한다면 nult : true 절을 사용해야함.

 

 

출처 : MongoDB master가 해설하는 New NoSQL & mongoDB