MongoDB与SQL语句对比

MongoDB与SQL语句对比

1 表操作

创建表

create table user( 
	id int not null auto_increment,
	user_id varchar(30),
	age number,
	status char(1),
	primary key(id) );

db.user.insertOne({
    user_id:"nml",
    age:23,
    status:"A"
})
-- 或者
db.createCollection("user")

修改表

alter table user add join_date datetime

db.user.updateMany(
	{},
	{$set:{join_date:new Date()}})

删除列

alter table user drop column join_date

db.user.updataeMany(
	{},
	{$unset:{"join_date":""}})

加索引

create index index_user_id_asc on user(user_id)

db.user.createIndex({user_id:1})

create index index_user_id_asc_age_desc on user(user_id,age desc)

db.user.createIndex({user_id:1,age:-1})

删除表

drop table user 

db.user.drop()

2 查询操作

select * from user
db.user.find()

select id,user_id,status from user
db.user.find({},{user_id:1,status:1})

select user_id,status from user
db.user.find({},{user_id:1,status:1,_id:0})

select * from user where status = "A"
db.user.find({status:"A"})

select user_id,status from user where status="A"
db.user.find( {status:"A"},{user_id:1,status:1,_id:0} )
	
select * from user where status != "A"
db.user.find({status:{$ne:"A"}})

select * from user where status = "A" and age = 23
db.user.find({status:"A",age:23})

select * from user where status = "A" or age = 23
db.user.find({$or:[{status:"A"},{age:23}]})

select * from user where age>20 and age<30
db.user.find({age:{$gt:20,$lte:30}})

select * from user where user_id like "abc%"
db.user.find({user_id://^abc/})
db.user.find({user_id:{$regex:/^abc/}})

select * from user where status = "A" order by user_id asc/desc
db.user.find({status:"A"}).sort({user_id:1/-1})

selet count(*) from user
db.user.count()
db.user.find().count()

select count(user_id) from user
db.user.count({user_id:{$exists:true}})
db.user.find({user_id:{$exists:ture}}).count()

select count(*) from user where age>20
db.user.count({age:{$gt:20}})
db.user.find({age:{$gt:20}}).count()

select distinct(status) from user
db.user.aggregate([{$group:{_id:"$status"}}])
db.user.distinct("status")

select * from user limit 1
db.user.find().limit(1)

select * from user limit 3 skip 10
db.user.find().limit(3).skip(10)
...