mongo与mysql的语法区分

最近刚刚学习完MySQL，现在又在学习Mongodb，都说语言是想通的，其实数据库也是大同小异。在此做一个对比，方便小白用户学习。(参考mongo的API)

1.名称的区分

MySQL	Mongodb
database（数据库）	database（数据库）
table（表格）	collection（集合）
row（行）	document（文档）
column（列）	field（域）
index（索引）	index（索引）
table joins（联表）
primary key(主键),可以自定义	primary key(主键)，一般是"_id"

2.语法的区分

• 创建

  MySQL	Mongodb
  CREATE TABLE users ( id MEDIUMINT NOT NULL AUTO_INCREMENT, user_id Varchar(30), age Number, status char(1), PRIMARY KEY (id) )	db.users.insert( { user_id: "abc123", age: 55, status: "A" } ) 或者是： db.createCollection("users")
  ALTER TABLE users ADD join_date DATETIME	db.users.update( { }, { $set: { join_date: new Date() } }, { multi: true } )
  ALTER TABLE users DROP COLUMN join_date	db.users.update( { }, { $unset: { join_date: "" } }, { multi: true } )
  CREATE INDEX idx_user_id_asc ON users(user_id)	db.users.createIndex( { user_id: 1 } )
  CREATE INDEX idx_user_id_asc_age_desc ON users(user_id, age DESC)	db.users.createIndex( { user_id: 1, age: -1 } )
  DROP TABLE users	db.users.drop()

• 插入

  MySQL	Mongodb
  INSERT INTO users(user_id, age, status) VALUE("bcd001", 45, "A")	db.users.insert( { user_id: "bcd001", age: 45, status: "A" } )

• 查找

  MySQL	Mongodb
  SELECT * FROM users	db.users.find()
  SELECT id, user_id, status FROM users	db.users.find( { }, { user_id: 1, status: 1 } )
  SELECT user_id, status FROM users	db.users.find( { }, { user_id: 1, status: 1, _id: 0 } )
  SELECT * FROM users WHERE status = "A"	db.users.find( { status: "A" } )
  SELECT user_id, status FROM users WHERE status = "A"	db.users.find( { status: "A" }, { user_id: 1, status: 1, _id: 0 } )
  SELECT * FROM users WHERE status != "A"	db.users.find( { status: { $ne: "A" } } )
  SELECT * FROM users WHERE status = "A" AND age = 50	db.users.find( { status: "A", age: 50 } )
  SELECT * FROM users WHERE status = "A" OR age = 50	db.users.find( { $or: [ { status: "A" } , { age: 50 } ] } )
  SELECT * FROM users WHERE age > 25	db.users.find( { age: { $gt: 25 } } )
  SELECT * FROM users WHERE age < 25	db.users.find( { age: { $lt: 25 } } )
  SELECT * FROM users WHERE age > 25 AND age <= 50	db.users.find( { age: { $gt: 25, $lte: 50 } } )
  SELECT * FROM users WHERE user_id like "%bc%"	db.users.find( { user_id: /bc/ } )
  SELECT * FROM users WHERE user_id like "bc%"	db.users.find( { user_id: /^bc/ } )
  SELECT * FROM users WHERE status = "A" ORDER BY user_id ASC	db.users.find( { status: "A" } ).sort( { user_id: 1 } )
  SELECT * FROM users WHERE status = "A" ORDER BY user_id DESC	db.users.find( { status: "A" } ).sort( { user_id: -1 } )
  SELECT COUNT(*) FROM users	db.users.count() 或者 db.users.find().count()
  SELECT COUNT(user_id) FROM users	db.users.count( { user_id: { $exists: true } } ) or db.users.find( { user_id: { $exists: true } } ).count()
  SELECT COUNT(*) FROM users WHERE age > 30	db.users.count( { age: { $gt: 30 } } ) or db.users.find( { age: { $gt: 30 } } ).count()
  SELECT DISTINCT(status) FROM users	db.users.distinct( "status" )
  SELECT * FROM users LIMIT 1	db.users.findOne() or db.users.find().limit(1)
  SELECT * FROM users LIMIT 5 SKIP 10	db.users.find().limit(5).skip(10)
  EXPLAIN SELECT * FROM users WHERE status = "A"	db.users.find( { status: "A" } ).explain()

• 更新

  MySQL	Mongodb
  UPDATE users SET status = "C" WHERE age > 25	db.users.update( { age: { $gt: 25 } }, { $set: { status: "C" } }, { multi: true } )
  UPDATE users SET age = age + 3 WHERE status = "A"	db.users.update( { status: "A" } , { $inc: { age: 3 } }, { multi: true } )

• 删除

  MySQL	Mongodb
  DELETE FROM users WHERE status = "D"	db.users.remove( { status: "D" } )
  DELETE FROM users	db.users.remove({})