MAPPING SQL TO MONGODB (Sample)

출처: http://search.cpan.org/~mongodb/MongoDB-v0.704.5.0/lib/MongoDB/Examples.pod

MAPPING SQL TO MONGODB 

For developers familiar with SQL, the following chart should help you see how many common SQL queries could be expressed in MongoDB.

These are Perl-specific examples of translating SQL queries to MongoDB's query language. To see the JavaScript (or other languages') mappings, seehttp://dochub.mongodb.org/core/sqlToMongo.

In the following examples, $db is a MongoDB::Database object which was retrieved by using get_database. See MongoDB::MongoClient for more.

CREATE TABLE USERS (a Number, b Number)

    Implicit, can be done explicitly.

INSERT INTO USERS VALUES(1,1)

    $db->get_collection( 'users' )->insert( { a => 1, b => 1 } );

SELECT a,b FROM users

    $db->get_collection( 'users')->find( { } )->fields( { a => 1, b => 1 });

SELECT * FROM users

    $db->get_collection( 'users' )->find;

SELECT * FROM users WHERE age=33

    $db->get_collection( 'users' )->find( { age => 33 } )

SELECT a,b FROM users WHERE age=33

    $db->get_collection( 'users' )->find( { age => 33 } )->fields( { a => 1, b => 1 });

SELECT * FROM users WHERE age=33 ORDER BY name

    $db->get_collection( 'users' )->find( { age => 33 } )->sort( { name => 1 } );

<SELECT * FROM users WHERE age33>>

    $db->get_collection( 'users' )->find( { age => { '$gt' => 33 } } );

<SELECT * FROM users WHERE age<33>

    $db->get_collection( 'users' )->find( { age => { '$lt' => 33 } } );

SELECT * FROM users WHERE name LIKE "%Joe%"

    $db->get_collection( 'users' )->find( { name => qr/Joe/ } );

SELECT * FROM users WHERE name LIKE "Joe%"

    $db->get_collection( 'users' )->find( {name => qr/^Joe/ } );

<SELECT * FROM users WHERE age33 AND age<=40>>

    $db->get_collection( 'users' )->find( { age => { '$gt' => 33, '$lte' => 40 } } );

SELECT * FROM users ORDER BY name DESC

    $db->get_collection( 'users' )->find->sort( { name => -1 } );

CREATE INDEX myindexname ON users(name)

    $db->get_collection( 'users' )->ensure_index( { name => 1 } );

CREATE INDEX myindexname ON users(name,ts DESC)

    $db->get_collection( 'users' )->ensure_index( Tie::IxHash->new( name => 1, ts => -1 ) );

In this example, we must use Tie::IxHash to preserve the ordering of the arguments to ensureIndex.

SELECT * FROM users WHERE a=1 and b='q'

    $db->get_collection( 'users' )->find( {a => 1, b => "q" } );

SELECT * FROM users LIMIT 10 SKIP 20

    $db->get_collection( 'users' )->find->limit(10)->skip(20);

SELECT * FROM users WHERE a=1 or b=2

    $db->get_collection( 'users' )->find( { '$or' => [ {a => 1 }, { b => 2 } ] } );

SELECT * FROM users LIMIT 1

    $db->get_collection( 'users' )->find->limit(1);

EXPLAIN SELECT * FROM users WHERE z=3

    $db->get_collection( 'users' )->find( { z => 3 } )->explain;

SELECT DISTINCT last_name FROM users

    $db->run_command( { distinct => "users", key => "last_name" } );

SELECT COUNT(*y) FROM users

    $db->get_collection( 'users' )->count;

<SELECT COUNT(*y) FROM users where age 30>>

    $db->get_collection( 'users' )->find( { "age" => { '$gt' => 30 } } )->count;

SELECT COUNT(age) from users

    $db->get_collection( 'users' )->find( { age => { '$exists' => 1 } } )->count;

UPDATE users SET a=1 WHERE b='q'

    $db->get_collection( 'users' )->update( { b => "q" }, { '$set' => { a => 1 } } );

UPDATE users SET a=a+2 WHERE b='q'

    $db->get_collection( 'users' )->update( { b => "q" }, { '$inc' => { a => 2 } } );

DELETE FROM users WHERE z="abc"

    $db->get_database( 'users' )->remove( { z => "abc" } );