MongoDB对应SQL语句
-------------------MongoDB对应SQL语句-------------------
1、Create and Alter
1、
sql:
create table users(
id mediuminty not null auto_increment,
user_id varchar(30),
age Number,
status char(1),
primary key (id)
)
MongoDB:
db.users.insert({
user_id:"xiaohao",
age:55,
status:"A"
})
2、
sql:
alter table users add join_date datetime
MongoDB:
db.users.update(
{},
{$set:{join_date: new Date()}},
{multi: true}
)
3、
sql:
alter table users drop column join_date
MongoDB:
db.users update(
{},
{$unset:{join_date:""}},
{multi:true}
)
4、
sql:
create index idx_user_id_asc on user(user_id)
mongoDB:
db.users.createIndex({user_id:1})
5、
sql:
create index idx_user_id_desc on user(user_id,age desc)
mongoDB:
db.users.createIndex({user_id:1,age:-1})
6、
sql:
drop table users
mongoDB:
db.users.drop()
2、Insert
1、
sql:
insert into users(user_id,age,status) values ("001",18,true)
mongoDB:
db.users.insert(
{user_id:"001",age:18,status:true}
)
3、Select
1、
sql:
select * from users
mongoDB:
db.users.find()
2、
sql:
select id,user_id,status from users
mongoDB:
db.users.find({"user_id":1,"status":1})
3、
sql:
select user_id,status from users
mongoDB:
db.users.find(
{},
{"user_id":1,"status":1,"_id":0}
)
4、
sql:
select * from users where status="A"
mongoDB:
db.users.find(
{"status":"A"}
)
5、
sql:
select user_id,status from users where status="A"
mongoDB:
db.users.find(
{"status":"A"},
{"user_id":1,"status":1,"_id":0}
)
6、
sql:
select * from users where status!="A"
mongoDB:
db.users.find(
{"status":{$ne:"A"}}
)
7、
sql:
select * from users where status="A" and age=18
mongoDB:
db.users.find(
{
"status":"A",
"age":18
}
)
8、
sql:
select * from users where status="A" or age="50"
mongoDB:
db.users.find(
{$or:[{"status":"A"},
{"age":18} ] }
)
9、
sql:
select * from users where age>10
mongoDB:
db.users.find(
{"age":{$gt:10}}
)
10、
sql:
select * from users where age<25
mongoDB:
db.users.find(
{"age":{$lt:25}}
)
11、
sql:
select * from users where age<25 and age>=8
mongoDB:
db.users.find(
{"age":{$gte:8,$lt:25}}
)
12、
sql:
select * from users where user_name like "%gh%"
mongoDB:
db.users.find(
{"user_name":/gh/}
)
13、
sql:
select * from users where user_name like "wang%"
mongoDB:
db.users.find(
{"user_name":/^wang/}
)
14、
sql:
select * from users where status="A" order by user_id asc
mongoDB:
db.users.find(
{"status":"A"}
).sort(
{"user_id":1}
)
15、
sql:
select * from users where status="A" order by user_id desc
mongoDB:
db.users.find(
{"status":"A"}
).sort(
{"user_id":-1}
)
16、
sql:
select count(*) from users
mongoDB:
db.users.find().count()
17、
sql:
select count(user_id) from users
mongoDB:
db.users.find(
{"user_id":{$exists:true}}
).count()
18、
sql:
select count(*) from users where age>20
mongoDB:
db.users.find(
{"age":{$gt:20}}
).count()
19、
sql:
select distinct(status) from users
mongoDB:
db.users.distinct("user_id")#去重获取的对应的数据信息
20、
sql:
select * from users limit 1
mongoDB:
db.users.find().limit(1)
21、
sql:
select * from users limit 5 skip 10
mongoDB:
db.users.find().limit(5).skip(10)
22、
sql:
explain select * from users where status="A"
mongoDB:
db.users.find(
{"status":"A"}
).explain()
4、Delete Records
1、
sql：
delete from users where status = "D"
mongoDB:
db.users.remove(
{"status":"D"}
)
2、
sql：
delete from users
mongoDB:
db.users.remove({})
