查看所有数据库

SHOW DATABASES;

show dbs 或 show databases

创建数据库

CREATE DATABASE db_name;

use db_name（隐式创建）

切换数据库

USE db_name;

use db_name

查看当前数据库

SELECT DATABASE();

db.getName() 或 db

删除数据库

DROP DATABASE db_name;

db.dropDatabase()

查看数据库大小

SELECT table_schema, SUM(data_length+index_length) FROM information_schema.tables GROUP BY table_schema;

db.stats()

查看数据库版本

SELECT VERSION();

db.version()

查看所有表/集合

SHOW TABLES;

show collections 或 show tables

创建表/集合

CREATE TABLE users (id INT, name VARCHAR(100));

db.createCollection("users")

隐式创建

-

db.users.insertOne({})（插入时自动创建）

查看表结构

DESCRIBE users; 或 SHOW COLUMNS FROM users;

typeof db.users.findOne()（查看示例文档）

修改表结构

ALTER TABLE users ADD COLUMN age INT;

无固定结构，直接插入带新字段的文档即可

删除表/集合

DROP TABLE users;

db.users.drop()

查看表信息

SHOW TABLE STATUS LIKE 'users';

db.users.stats()

重命名表/集合

RENAME TABLE users TO new_users;

db.users.renameCollection("new_users")

插入单条记录

INSERT INTO users (name, age) VALUES ('张三', 25);

db.users.insertOne({name: "张三", age: 25})

插入多条记录

INSERT INTO users (name, age) VALUES ('李四', 30), ('王五', 28);

db.users.insertMany([{name: "李四", age: 30}, {name: "王五", age: 28}])

插入并返回ID

INSERT INTO users ...; SELECT LAST_INSERT_ID();

db.users.insertOne(...)（返回插入的_id）

忽略重复插入

INSERT IGNORE INTO users ...

db.users.insertMany([...], {ordered: false})（批量插入时）

替换插入

REPLACE INTO users ...

db.users.replaceOne({_id: id}, newDoc, {upsert: true})

查询所有

SELECT * FROM users;

db.users.find({})

查询指定字段

SELECT name, age FROM users;

db.users.find({}, {name: 1, age: 1, _id: 0})

条件查询

SELECT * FROM users WHERE age = 25;

db.users.find({age: 25})

去重查询

SELECT DISTINCT city FROM users;

db.users.distinct("city")

限制数量

SELECT * FROM users LIMIT 5;

db.users.find({}).limit(5)

跳过记录

SELECT * FROM users LIMIT 5 OFFSET 10;

db.users.find({}).skip(10).limit(5)

排序

SELECT * FROM users ORDER BY age DESC, name ASC;

db.users.find({}).sort({age: -1, name: 1})

AND 条件

SELECT * FROM users WHERE age > 25 AND city = '北京';

db.users.find({age: {$gt: 25}, city: "北京"})

OR 条件

SELECT * FROM users WHERE age > 30 OR city = '上海';

db.users.find({$or: [{age: {$gt: 30}}, {city: "上海"}]})

组合条件

SELECT * FROM users WHERE (age > 25 AND city = '北京') OR name LIKE '张%';

db.users.find({$or: [{$and: [{age: {$gt: 25}}, {city: "北京"}]}, {name: /^张/}]})

NOT 条件

SELECT * FROM users WHERE NOT age = 25;

db.users.find({age: {$ne: 25}})

IN 条件

SELECT * FROM users WHERE age IN (25, 30, 35);

db.users.find({age: {$in: [25, 30, 35]}})

NOT IN

SELECT * FROM users WHERE age NOT IN (25, 30);

db.users.find({age: {$nin: [25, 30]}})

等于

WHERE age = 25

{age: 25}

不等于

WHERE age != 25

{age: {$ne: 25}}

大于

WHERE age > 25

{age: {$gt: 25}}

大于等于

WHERE age >= 25

{age: {$gte: 25}}

小于

WHERE age < 25

{age: {$lt: 25}}

小于等于

WHERE age <= 25

{age: {$lte: 25}}

介于之间

WHERE age BETWEEN 20 AND 30

{age: {$gte: 20, $lte: 30}}

为空

WHERE email IS NULL

{email: null} 或 {email: {$exists: false}}

不为空

WHERE email IS NOT NULL

{email: {$exists: true, $ne: null}}

包含

WHERE name LIKE '%张%'

{name: /张/}

开头匹配

WHERE name LIKE '张%'

{name: /^张/}

结尾匹配

WHERE name LIKE '%张'

{name: /张$/}

不区分大小写

WHERE name LIKE '%zhang%'（取决于排序规则）

{name: /zhang/i}

正则表达式

WHERE name REGEXP '^张.*三$'

{name: {$regex: '^张.*三$'}}

计数

SELECT COUNT(*) FROM users WHERE age > 25;

db.users.count({age: {$gt: 25}}) 或 db.users.find({age: {$gt: 25}}).count()

求和

SELECT SUM(amount) FROM orders;

db.orders.aggregate([{$group: {_id: null, total: {$sum: "$amount"}}}])

平均值

SELECT AVG(age) FROM users;

db.users.aggregate([{$group: {_id: null, avgAge: {$avg: "$age"}}}])

最大值

SELECT MAX(age) FROM users;

db.users.aggregate([{$group: {_id: null, maxAge: {$max: "$age"}}}])

最小值

SELECT MIN(age) FROM users;

db.users.aggregate([{$group: {_id: null, minAge: {$min: "$age"}}}])

分组

SELECT city, COUNT(*) FROM users GROUP BY city;

db.users.aggregate([{$group: {_id: "$city", count: {$sum: 1}}}])

分组并过滤

SELECT city, COUNT(*) FROM users GROUP BY city HAVING COUNT(*) > 10;

db.users.aggregate([{$group: {_id: "$city", count: {$sum: 1}}}, {$match: {count: {$gt: 10}}}])

更新单条记录

UPDATE users SET age = 26 WHERE id = 1;

db.users.updateOne({_id: ObjectId("...")}, {$set: {age: 26}})

更新多条记录

UPDATE users SET status = 'active' WHERE age > 25;

db.users.updateMany({age: {$gt: 25}}, {$set: {status: "active"}})

替换文档

UPDATE users SET name = '张三', age = 26 WHERE id = 1;

db.users.replaceOne({_id: id}, {name: "张三", age: 26})

字段自增

UPDATE users SET age = age + 1 WHERE id = 1;

db.users.updateOne({_id: id}, {$inc: {age: 1}})

字段自减

UPDATE users SET age = age - 1 WHERE id = 1;

db.users.updateOne({_id: id}, {$inc: {age: -1}})

乘法更新

UPDATE users SET age = age * 2 WHERE id = 1;

db.users.updateOne({_id: id}, {$mul: {age: 2}})

添加字段

ALTER TABLE users ADD COLUMN email VARCHAR(100); UPDATE users SET email = 'test@test.com' WHERE id = 1;

db.users.updateOne({_id: id}, {$set: {email: "test@test.com"}})

删除字段

ALTER TABLE users DROP COLUMN email;

db.users.updateOne({_id: id}, {$unset: {email: ""}})

重命名字段

ALTER TABLE users CHANGE old_name new_name VARCHAR(100);

db.users.updateMany({}, {$rename: {"old_name": "new_name"}})

存在则更新，不存在则插入

INSERT INTO users (id, name) VALUES (1, '张三') ON DUPLICATE KEY UPDATE name = '张三';

db.users.updateOne({_id: id}, {$set: {name: "张三"}}, {upsert: true})

删除单条记录

DELETE FROM users WHERE id = 1 LIMIT 1;

db.users.deleteOne({_id: ObjectId("...")})

删除多条记录

DELETE FROM users WHERE age < 18;

db.users.deleteMany({age: {$lt: 18}})

删除所有记录

DELETE FROM users;

db.users.deleteMany({})

删除并返回

DELETE FROM users WHERE id = 1 RETURNING *;（PostgreSQL）

db.users.findOneAndDelete({_id: id})

清空表

TRUNCATE TABLE users;

db.users.deleteMany({}) 或 db.users.drop()

数组添加元素

需要关联表

db.users.updateOne({_id: id}, {$push: {tags: "new"}})

数组添加多个元素

需要关联表

db.users.updateOne({_id: id}, {$push: {tags: {$each: ["a", "b", "c"]}}})

数组添加不重复元素

需要关联表

db.users.updateOne({_id: id}, {$addToSet: {tags: "new"}})

删除数组元素

需要关联表

db.users.updateOne({_id: id}, {$pull: {tags: "old"}})

删除多个数组元素

需要关联表

db.users.updateOne({_id: id}, {$pullAll: {tags: ["a", "b"]}})

弹出数组元素

需要关联表

db.users.updateOne({_id: id}, {$pop: {tags: 1}})（最后一个）
{$pop: {tags: -1}}（第一个）

按位置更新数组

需要关联表

db.users.updateOne({_id: id}, {$set: {"tags.0": "new"}})

查询包含某元素的数组

SELECT * FROM users WHERE id IN (SELECT user_id FROM user_tags WHERE tag = 'tag1');

db.users.find({tags: "tag1"})

查询数组长度

需要关联表计数

db.users.find({$where: "this.tags.length > 3"})
或 {tags: {$size: 3}}

创建普通索引

CREATE INDEX idx_name ON users(name);

db.users.createIndex({name: 1})

创建唯一索引

CREATE UNIQUE INDEX idx_email ON users(email);

db.users.createIndex({email: 1}, {unique: true})

创建复合索引

CREATE INDEX idx_name_age ON users(name, age);

db.users.createIndex({name: 1, age: -1})

创建全文索引

CREATE FULLTEXT INDEX idx_content ON articles(content);

db.articles.createIndex({content: "text"})

查看索引

SHOW INDEX FROM users;

db.users.getIndexes()

删除索引

DROP INDEX idx_name ON users;

db.users.dropIndex("idx_name")

删除所有索引

-

db.users.dropIndexes()

内连接

SELECT * FROM orders JOIN users ON orders.user_id = users.id;

db.orders.aggregate([{$lookup: {from: "users", localField: "user_id", foreignField: "_id", as: "user"}}])

左连接

SELECT * FROM users LEFT JOIN orders ON users.id = orders.user_id;

db.users.aggregate([{$lookup: {from: "orders", localField: "_id", foreignField: "user_id", as: "orders"}}])

子查询

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

db.users.find({_id: {$in: db.orders.distinct("user_id", {amount: {$gt: 100}})}})

开始事务

START TRANSACTION;

session.startTransaction()

提交事务

COMMIT;

session.commitTransaction()

回滚事务

ROLLBACK;

session.abortTransaction()

设置隔离级别

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

需要配置事务选项

导出数据

mysqldump -u user -p dbname > backup.sql

mongoexport --db dbname --collection users --out users.json

导入数据

mysql -u user -p dbname < backup.sql

mongoimport --db dbname --collection users --file users.json

导出二进制

mysqldump --tab=/path

mongodump --db dbname --out /path

导入二进制

mysqlimport ...

mongorestore --db dbname /path

分析查询

EXPLAIN SELECT * FROM users WHERE age > 25;

db.users.find({age: {$gt: 25}}).explain("executionStats")

查看慢查询

SET GLOBAL slow_query_log = ON;

db.setProfilingLevel(1, 100)（记录超过100ms的查询）

查看配置文件

SHOW VARIABLES LIKE 'slow_query_log%';

db.getProfilingStatus()