mongo与mysql的语法区分

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

1.名称的区分

MySQLMongodb

database(数据库)database(数据库)

table(表格)collection(集合)

row(行)document(文档)

column(列)field(域)

index(索引)index(索引)

table joins(表)

primary key(主键),可以自定义primary key(主键)，一般是"_id"

2.语法的区分

创建MySQLMongodb

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 DATETIMEdb.users.update( { }, { $set: { join_date: new Date() } }, { multi: true } )

ALTER TABLE users DROP COLUMN join_datedb.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()

插入MySQLMongodb

INSERT INTO users(user_id,

age, status) VALUE("bcd001", 45, "A")

db.users.insert(

{ user_id: "bcd001", age: 45, status: "A" }

)

查找MySQLMongodb

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()

更新MySQLMongodb

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 }

)

删除MySQLMongodb

DELETE FROM users

WHERE status = "D"

db.users.remove( { status: "D" } )

DELETE FROM users

db.users.remove({})