MySQL & Mongo SQL

MySQL statement Mongo statement

-- DDL

CREATE TABLE users 
(id INT AUTO_INCREMENT PRIMARY KEY,
a INT, 
b INT, 
age INT, name CHAR(32)) 				db.createCollection("user1")
ALTER TABLE users ADD ...

DROP TABLE users 					db.users.drop()
CREATE DATABSE db_name 	 				use yourDB;  当创建一个集合(table)的时候会自动创建当前数据库
CREATE INDEX idx_name ON users(name) 			db.users.ensureIndex({name : 1})
CREATE INDEX idx_name_ts ON users(name, ts DESC) 	db.users.ensureIndex({name : 1, ts : -1})
DROP INDEX idx_name ON users 				db.users.dropIndex({name : 1, ts : -1})

-- DML

INSERT INTO users VALUES(3, 5) 				db.users.insert({a: 3, b: 5})
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 
ORDER BY name ASC 					db.users.find({age : 33}).sort({name : 1})
SELECT * FROM users ORDER BY name DESC 			db.users.find().sort({name : -1})
SELECT * FROM users WHERE age > 33 			db.users.find({age : {$gt : 33}})
SELECT * FROM users WHERE age != 33 			db.users.find({age : {$ne : 33}})
SELECT * FROM users WHERE age > 33 AND age <= 44 	db.users.find({age : {$gt: 33, $lte : 44}})
SELECT * FROM users WHERE name LIKE "%Joe%" 		db.users.find({name : /Joe/})
SELECT * FROM users WHERE id IN (3, 4, 5) 		db.users.find({id, {$in : [3, 4, 5]}})
SELECT * FROM users WHERE id NOT IN (3, 4, 5) 		db.users.find({id, {$nin : [3, 4, 5]}})
SELECT * FROM users WHERE id = 2 			db.users.find({id , {$all : [2]}})
SELECT * FROM users WHERE a = 1 AND name = 'Joe' 	db.users.find({ a : 1, name : 'Joe'})
SELECT * FROM users WHERE a = 1 OR b = 2 	        db.users.find({$or : [{a : 1}, {b : 2}]})
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 * FROM users LIMIT 20, 10 			db.users.find().limit(10).skip(20)
SELECT * FROM users LIMIT 1 				db.users.findOne()
SELECT id 
	FROM users u , users_extend e 
	WHERE u.user_id = e.user_id AND e.c = 12345 	db.users.find({"users_extend.c" : 12345}, {_id : 1})
SELECT customer.name 
	FROM customers, orders 
	WHERE orders.id = "q139" 
	AND orders.custid = customers.id 		var o = db.orders.findOne({_id : "q139"})
							var name = db.customers.findOne({_id : o.custid})
UPDATE users SET a = 1 WHERE b = 2 			db.users.update({b : 2}, {$set : {a : 1}}, false, true)
UPDATE users SET a = a + 2 WHERE b = 2 			db.users.update({b : 2}, {$inc: {a : 2}}, false, true)
DELETE FROM users WHERE a = 3 				db.users.remove({ a : 3})
DELETE FROM users 					db.users.remove()


							MongoDB 中可用到的其它语句:

								存在某字段: db.users.find({a : {$exists : true}})
								不存在某字段: db.users.find({a : {$exists : false}})
								查询指定长度: db.users.find({name : {$size : 10}})
								查询字段是数组: db.users.find({name.first; : 'Joe', name.last : 'David'})
								var cursor = db.users.find()
								while(cursor.hasNext()) printjson(curson.next())
								db.users.find().forEach(printjson)
								db.users.find().toArray()

-- DCL

-- ....

-- DB manager

EXPLAIN SELECT * FROM users WHERE a = 3 		db.users.find({a : 3}).explain()
SHOW DATABASES 						show dbs
USE db_name 						use db_name
SHOW TABLES 						show collections
SHOW INDEXES FROM users 				db.users.getIndexes()