Example
以下的映射以下面的文档为例子:
- {
- _id: ObjectId("509a8fb2f3f4948bd2f983a0"),
- user_id: "abc123",
- age: 55,
- status: 'A'
- }
- Create or Alter Create table or collection CREATE TABLE people(db.people.insertOne({
- id MEDIUMINT NOT NULL user_id: "abc123",
- AUTO_INCREMENT,
- age: 55,
- user_id Varchar(30),
- status: "A"age Number,
- }) status char(1), PRIMARY KEY(id)) Add column or key ALTER TABLE people db.people.updateMany(ADD join_date DATETIME {},
- {
- $set: {
- join_date: new Date()
- }
- }) Delete column or key ALTER TABLE people db.people.updateMany(DROP COLUMN join_date {},
- {
- $unset: {
- "join_date": ""
- }
- }) Create single index CREATE INDEX idx_user_id_asc db.people.createIndex({
- user_id: 1
- }) ON people(user_id) Create union and desc index CREATE INDEX db.people.createIndex({
- user_id: 1,
- age: -1
- }) idx_user_id_asc_age_desc ON people(user_id, age DESC) Drop table or collection DROP TABLE people db.people.drop() See also: db.collection.insertOne() db.collection.insertMany() db.createCollection() db.collection.updateMany() $set $unset db.collection.createIndex() Indexes db.collection.drop() Data Modeling Concepts.Insert Insert one INSERT INTO people(user_id, db.people.insertOne(age, {
- user_id: "bcd001",
- age: 45,
- status: "A"
- }
- status)) VALUES("bcd001", 45, "A") See also: db.collection.insertOne() Select Select all SELECT * db.people.find() FROM people Select specifically SELECT id,
- db.people.find(user_id, {},
- status {
- user_id: 1,
- status: 1
- }
- FROM people) SELECT user_id,
- status db.people.find(FROM people {},
- {
- user_id: 1,
- status: 1,
- _id: 0
- }) Selct logically SELECT user_id,
- status db.people.find(FROM people {
- status: "A"
- },
- WHERE status = "A" {
- user_id: 1,
- status: 1,
- _id: 0
- }) SELECT * db.people.find(FROM people {
- status: {
- $ne: "A"
- }
- }
- WHERE status != "A") SELECT * db.people.find(FROM people {
- status: "A",
- WHERE status = "A"age: 50
- }
- AND age = 50) SELECT * db.people.find(FROM people {
- $or: [{
- status: "A"
- },
- WHERE status = "A" {
- age: 50
- }]
- }
- OR age = 50) SELECT * db.people.find(FROM people {
- age: {
- $gt: 25
- }
- }
- WHERE age > 25) SELECT * db.people.find(FROM people {
- age: {
- $lt: 25
- }
- }
- WHERE age < 25) SELECT * db.people.find(FROM people {
- age: {
- $gt: 25,
- $lte: 50
- }
- }
- WHERE age > 25) AND age <= 50 Select fuzzily SELECT * db.people.find({
- user_id: /bc/
- }) FROM people - or - WHERE user_id like "%bc%"db.people.find({
- user_id: {
- $regex: /bc/
- }
- }) SELECT * db.people.find({
- user_id: /^bc/
- }) FROM people - or - WHERE user_id like "bc%"db.people.find({
- user_id: {
- $regex: /^bc/
- }
- }) Select and Sort SELECT * db.people.find(FROM people {
- status: "A"
- }).sort({
- user_id: 1
- }
- WHERE status = "A") ORDER BY user_id ASC SELECT * db.people.find(FROM people {
- status: "A"
- }).sort({
- user_id: -1
- }
- WHERE status = "A") ORDER BY user_id DESC Select and Count SELECT COUNT( * ) db.people.count() FROM people - or - db.people.find().count() SELECT COUNT(user_id) db.people.count({
- user_id: {
- $exists: true
- }
- }) FROM people - or - db.people.find({
- user_id: {
- $exists: true
- }
- }).count() SELECT COUNT( * ) db.people.count({
- age: {
- $gt: 30
- }
- }) FROM people - or - WHERE age > 30 db.people.find({
- age: {
- $gt: 30
- }
- }).count() Select and Distinct SELECT DISTINCT(status) db.people.aggregate(FROM people[{
- $group: {
- _id: "$status"
- }
- }]) - or - db.people.distinct("status") Select limitedly SELECT * db.people.findOne() FROM people - or - LIMIT 1 db.people.find().limit(1) SELECT * db.people.find().limit(5).skip(10) FROM people LIMIT 5 SKIP 10 Explain Select EXPLAIN SELECT * db.people.find({
- status: "A"
- }).explain() FROM people WHERE status = "A"See also: db.collection.find() db.collection.distinct() db.collection.findOne() Query operators: $ne,
- $and,
- $or,
- $gt,
- $lt,
- $exists,
- $lte,
- and $regex.limit() skip() explain() sort() count() Update UPDATE people db.people.updateMany(SET status = "C" {
- age: {
- $gt: 25
- }
- },
- WHERE age > 25 {
- $set: {
- status: "C"
- }
- }) UPDATE people db.people.updateMany(SET age = age + 3 {
- status: "A"
- },
- WHERE status = "A" {
- $inc: {
- age: 3
- }
- }) See also db.collection.updateMany(),
- $set,
- $inc,
- and $gt.Delete DELETE FROM people db.people.deleteMany({
- status: "D"
- }) WHERE status = "D"
- DELETE FROM people db.people.deleteMany({}) See also db.collection.deleteMany().
来源: http://www.bubuko.com/infodetail-2499518.html