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

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

主题: MySQLSQL数据数据库TIAUTAUUT其实RIM
分页:12
转载请注明
本文标题:mongo与mysql的语法区分 mysql mysql数据库 mysql函数 mongo 语法区分
本站链接:http://www.codesec.net/view/522191.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(36)