· 7 years ago · Dec 26, 2018, 12:38 AM
1SQL to MongoDB Mapping Chart
2
3SQL to MongoDB Mapping Chart¶
4In addition to the charts that follow, you might want to consider the Frequently Asked Questions section for a selection of common questions about MongoDB.
5
6Executables¶
7The following table presents the MySQL/Oracle executables and the corresponding MongoDB executables.
8
9 MySQL/Oracle MongoDB
10Database Server mysqld/oracle mongod
11Database Client mysql/sqlplus mongo
12Terminology and Concepts¶
13The following table presents the various SQL terminology and concepts and the corresponding MongoDB terminology and concepts.
14
15SQL Terms/Concepts MongoDB Terms/Concepts
16database database
17table collection
18row document or BSON document
19column field
20index index
21table joins embedded documents and linking
22primary key
23
24Specify any unique column or column combination as primary key.
25
26primary key
27
28In MongoDB, the primary key is automatically set to the _id field.
29
30aggregation (e.g. group by)
31aggregation framework
32
33See the SQL to Aggregation Framework Mapping Chart.
34
35Examples¶
36The following table presents the various SQL statements and the corresponding MongoDB statements. The examples in the table assume the following conditions:
37
38The SQL examples assume a table named users.
39
40The MongoDB examples assume a collection named users that contain documents of the following prototype:
41
42{
43 _id: ObjectID("509a8fb2f3f4948bd2f983a0"),
44 user_id: "abc123",
45 age: 55,
46 status: 'A'
47}
48Create and Alter¶
49The following table presents the various SQL statements related to table-level actions and the corresponding MongoDB statements.
50
51SQL Schema Statements MongoDB Schema Statements Reference
52CREATE TABLE users (
53 id MEDIUMINT NOT NULL
54 AUTO_INCREMENT,
55 user_id Varchar(30),
56 age Number,
57 status char(1),
58 PRIMARY KEY (id)
59)
60Implicitly created on first insert operation. The primary key _id is automatically added if _id field is not specified.
61
62db.users.insert( {
63 user_id: "abc123",
64 age: 55,
65 status: "A"
66 } )
67However, you can also explicitly create a collection:
68
69db.createCollection("users")
70See insert() and createCollection() for more information.
71ALTER TABLE users
72ADD join_date DATETIME
73Collections do not describe or enforce the structure of the constituent documents. See the Schema Design wiki page for more information. See update() and $set for more information on changing the structure of documents in a collection.
74ALTER TABLE users
75DROP COLUMN join_date
76Collections do not describe or enforce the structure of the constituent documents. See the Schema Design wiki page for more information. See update() and $set for more information on changing the structure of documents in a collection.
77CREATE INDEX idx_user_id_asc
78ON users(user_id)
79db.users.ensureIndex( { user_id: 1 } )
80See ensureIndex() and indexes for more information.
81CREATE INDEX
82 idx_user_id_asc_age_desc
83ON users(user_id, age DESC)
84db.users.ensureIndex( { user_id: 1, age: -1 } )
85See ensureIndex() and indexes for more information.
86DROP TABLE users
87db.users.drop()
88See drop() for more information.
89Insert¶
90The following table presents the various SQL statements related to inserting records into tables and the corresponding MongoDB statements.
91
92SQL INSERT Statements MongoDB insert() Statements Reference
93INSERT INTO users(user_id,
94 age,
95 status)
96VALUES ("bcd001",
97 45,
98 "A")
99db.users.insert( {
100 user_id: "bcd001",
101 age: 45,
102 status: "A"
103} )
104See insert() for more information.
105Select¶
106The following table presents the various SQL statements related to reading records from tables and the corresponding MongoDB statements.
107
108SQL SELECT Statements MongoDB find() Statements Reference
109SELECT *
110FROM users
111db.users.find()
112See find() for more information.
113SELECT id, user_id, status
114FROM users
115db.users.find(
116 { },
117 { user_id: 1, status: 1 }
118)
119See find() for more information.
120SELECT user_id, status
121FROM users
122db.users.find(
123 { },
124 { user_id: 1, status: 1, _id: 0 }
125)
126See find() for more information.
127SELECT *
128FROM users
129WHERE status = "A"
130db.users.find(
131 { status: "A" }
132)
133See find() for more information.
134SELECT user_id, status
135FROM users
136WHERE status = "A"
137db.users.find(
138 { status: "A" },
139 { user_id: 1, status: 1, _id: 0 }
140)
141See find() for more information.
142SELECT *
143FROM users
144WHERE status != "A"
145db.users.find(
146 { status: { $ne: "A" } }
147)
148See find() and $ne for more information.
149SELECT *
150FROM users
151WHERE status = "A"
152AND age = 50
153db.users.find(
154 { status: "A",
155 age: 50 }
156)
157See find() and $and for more information.
158SELECT *
159FROM users
160WHERE status = "A"
161OR age = 50
162db.users.find(
163 { $or: [ { status: "A" } ,
164 { age: 50 } ] }
165)
166See find() and $or for more information.
167SELECT *
168FROM users
169WHERE age > 25
170db.users.find(
171 { age: { $gt: 25 } }
172)
173See find() and $gt for more information.
174SELECT *
175FROM users
176WHERE age < 25
177db.users.find(
178 { age: { $lt: 25 } }
179)
180See find() and $lt for more information.
181SELECT *
182FROM users
183WHERE age > 25
184AND age <= 50
185db.users.find(
186 { age: { $gt: 25, $lte: 50 } }
187)
188See find(), $gt, and $lte for more information.
189SELECT *
190FROM users
191WHERE user_id like "%bc%"
192db.users.find(
193 { user_id: /bc/ }
194)
195See find() and $regex for more information.
196SELECT *
197FROM users
198WHERE user_id like "bc%"
199db.users.find(
200 { user_id: /^bc/ }
201)
202See find() and $regex for more information.
203SELECT *
204FROM users
205WHERE status = "A"
206ORDER BY user_id ASC
207db.users.find( { status: "A" } ).sort( { user_id: 1 } )
208See find() and sort() for more information.
209SELECT *
210FROM users
211WHERE status = "A"
212ORDER BY user_id DESC
213db.users.find( { status: "A" } ).sort( { user_id: -1 } )
214See find() and sort() for more information.
215SELECT COUNT(*)
216FROM users
217db.users.count()
218or
219
220db.users.find().count()
221See find() and count() for more information.
222SELECT COUNT(user_id)
223FROM users
224db.users.count( { user_id: { $exists: true } } )
225or
226
227db.users.find( { user_id: { $exists: true } } ).count()
228See find(), count(), and $exists for more information.
229SELECT COUNT(*)
230FROM users
231WHERE age > 30
232db.users.count( { age: { $gt: 30 } } )
233or
234
235db.users.find( { age: { $gt: 30 } } ).count()
236See find(), count(), and $gt for more information.
237SELECT DISTINCT(status)
238FROM users
239db.users.distinct( "status" )
240See find() and distinct() for more information.
241SELECT *
242FROM users
243LIMIT 1
244db.users.findOne()
245or
246
247db.users.find().limit(1)
248See find(), findOne(), and limit() for more information.
249SELECT *
250FROM users
251LIMIT 5
252SKIP 10
253db.users.find().limit(5).skip(10)
254See find(), limit(), and skip() for more information.
255EXPLAIN SELECT *
256FROM users
257WHERE status = "A"
258db.users.find( { status: "A" } ).explain()
259See find() and explain() for more information.
260Update Records¶
261The following table presents the various SQL statements related to updating existing records in tables and the corresponding MongoDB statements.
262
263SQL Update Statements MongoDB update() Statements Reference
264UPDATE users
265SET status = "C"
266WHERE age > 25
267db.users.update(
268 { age: { $gt: 25 } },
269 { $set: { status: "C" } },
270 { multi: true }
271)
272See update(), $gt, and $set for more information.
273UPDATE users
274SET age = age + 3
275WHERE status = "A"
276db.users.update(
277 { status: "A" } ,
278 { $inc: { age: 3 } },
279 { multi: true }
280)
281See update(), $inc, and $set for more information.
282Delete Records¶
283The following table presents the various SQL statements related to deleting records from tables and the corresponding MongoDB statements.
284
285SQL Delete Statements MongoDB remove() Statements Reference
286DELETE FROM users
287WHERE status = "D"
288db.users.remove( { status: "D" } )
289See remove() for more information.
290DELETE FROM users
291db.users.remove( )
292See remove() for more information.