Create table
CREATE TABLE people (id MEDIUMINT NOT NULL AUTO_INCREMENT, user_id Varchar(30), age Number, status char(1), PRIMARY KEY (id))
db.people.insertOne({User_id: “abc123”, Age: 55, Status: “A”})
Drop table
DROP TABLE people
db.people.drop()
Insert records into tables
INSERT INTO people(user_id, age, status) VALUES ("bcd001", 45, "A")
db.people.insertOne( { user_id: "bcd001", age: 45, status: "A" })
Select
SELECT *FROM people
db.people.find()
SELECT id,user_id, status FROM people
db.people.find( { }, { user_id: 1, status: 1 } )
SELECT * FROM people WHERE status = "A"
db.people.find( { status: "A" } )
SELECT * FROM people WHERE status = "A" AND age = 50
db.people.find( { status: "A", age: 50 } )
SELECT * FROM people WHERE status = "A" OR age = 50
db.people.find( { $or: [ { status: "A" } , { age: 50 } ] } )
SELECT * FROM people WHERE user_id like "%bc%"
db.people.find( { user_id: /bc/ } )
db.people.find( { user_id: { $regex: /bc/ } } )
SELECT COUNT(user_id) FROM people
db.people.count( { user_id: { $exists: true } } )
db.people.find( { user_id: { $exists: true } } ).count()
Update records
UPDATE people SET status = "C" WHERE age > 25
db.people.updateMany( { age: { $gt: 25 } }, { $set: { status: "C" } } )
UPDATE people SET age = age + 3 WHERE status = "A"
db.people.updateMany( { status: "A" } , { $inc: { age: 3 } } )
Delete Records
DELETE FROM people WHERE status = "D"
db.people.deleteMany( { status: "D" } )
DELETE FROM people
db.people.deleteMany({})
