CREATE TABLE people (id MEDIUMINT NOT NULLAUTO_INCREMENT,user_id Varchar(30),age Number,status char(1),PRIMARY KEY (id)
)
| Implicitly created on first insertOne() or insertMany() operation. The primary key _id is automatically added if _id field is not specified. db.people.insertOne( {user_id: "abc123",age: 55,status: "A"} )
|
ALTER TABLE people
DROP COLUMN join_date
| 使用$set添加field db.people.updateMany({ },{ $set: { join_date: new Date() } }
)
|
ALTER TABLE people
DROP COLUMN join_date
| 使用$unset删除field db.people.updateMany({ },{ $unset: { "join_date": "" } }
)
|
创建索引 CREATE INDEX idx_user_id_asc
ON people(user_id)
| db.people.createIndex( { user_id: 1 } )
|
CREATE INDEXidx_user_id_asc_age_desc
ON people(user_id, age DESC)
| -1表示降序 db.people.createIndex( { user_id: 1, age: -1 } )
|
DROP TABLE people
| db.people.drop()
|
INSERT INTO people(user_id,age,status)
VALUES ("bcd001",45,"A")
| db.people.insertOne({ user_id: "bcd001", age: 45, status: "A" }
)
|
SELECT * FROM people
| db.people.find()
|
SELECT id,user_id,status FROM people
| db.people.find({},{user_id:1,status:1})
|
SELECT user_id,status FROM people
| //0表示不显示改行
db.people.find({},{user_id:1,status:1,_id:0})
|
SELECT * FROM people WHERE status="A"
| db.people.find({status:"A"})
|
SELECT user_id,status FROM people WHERE status = "A"
| db.people.find({status:"A"},{user_id:1,status:1,_id:0})
|
SELECT * FROM people WHERE status != "A"
| db.people.find({status:{$ne:"A"}})
|
SELECT * FROM people WHERE status = "A" AND age=50
| db.people.find({status:"A",age:50})
|
SELECT * FROM people OR age = 50
| db.people.find({$or:[{status:"A"},{age:50}]})
|
SELECT * FROM people WHERE age >= 25
| db.people.find({age:{$gte:25}})
|
SELECT * FROM people WHERE age > 25 AND age <= 50
| db.people.find({age:{$gt:25,$lte:50}})
|
SELECT * FROM people WHERE user_id like "%bc%"
| db.people.find({user_id:/bc/} )
db.people.find({user_id:{$regex:/bc/}})
|
SELECT * FROM people WHERE user_id LIKE "%bc"
| db.people.find({user_id:/^bc/})
db.people.find({user_id:{$regex:/^bc/}})
|
SELECT * FROM people WHERE status = "A" ORDER BY user_id ASC
SELECT * FROM people WHERE status = "A" ORDER BY user_id DESC
| db.people.find({status:"A"}).sort({user_id:1})
db.people.find({status:"A"}).sort({user_id:-1})
|
SELECT count(*) FROM people
| db.people.count() or
db.people.find().count() or
db.people.count({user_id:{$exists:true}})
|
SELECT count(*) FROM people WHERE age>30
| db.people.count({age:{$gt:30}})
|
SELECT DISTINCT(status) FROM people
| db.people.distinct("status")
|
SELECT * FROM people LIMIT 1
| db.people.findOne() or
db.people.find().limit(1)
|
SELECT * FROM people LIMIT 5 SKIP 10
| db.people.find(().limit(5).skip(10)
|
EXPLAIN SELECT * FROM people WHERE status = "A"
| db.people.find({status:"A"}).explain()
|
