数据库工程师必学的 100 个数据库命令(全栈实操版)

命令

说明

示例

1 SHOW DATABASES;

查看所有数据库（MySQL）

SHOW DATABASES;

2 CREATE DATABASE dbname;

创建数据库

CREATE DATABASE testdb;

3 DROP DATABASE dbname;

删除数据库

DROP DATABASE testdb;

4 USE dbname;

切换数据库

USE testdb;

5 \c dbname

PostgreSQL 切换库

\c mydb

6 \l

PostgreSQL 查看所有库

\l

7 SELECT DATABASE();

查看当前库

SELECT DATABASE();

8 CREATE SCHEMA schema_name;

创建 Schema（PostgreSQL/Oracle）

CREATE SCHEMA finance;

9 DROP SCHEMA schema_name;

删除 Schema

DROP SCHEMA finance CASCADE;

ALTER DATABASE dbname ...

修改字符集/排序规则等

ALTER DATABASE testdb CHARACTER SET utf8mb4;

11 SHOW CREATE DATABASE dbname;

查看建库语句

SHOW CREATE DATABASE testdb;

12 SELECT @@version;

查看数据库版本

SELECT @@version;

13 SELECT current_user;

查看当前用户

SELECT current_user;

14 \dt

PostgreSQL 查看所有表

\dt

15 SHOW TABLES;

MySQL 查看所有表

SHOW TABLES;

命令

说明

示例

1 CREATE TABLE

创建表

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

2 DROP TABLE

删除表

DROP TABLE users;

3 ALTER TABLE ... ADD

添加列

ALTER TABLE users ADD email VARCHAR(255);

4 ALTER TABLE ... DROP COLUMN

删除列

ALTER TABLE users DROP COLUMN email;

5 ALTER TABLE ... RENAME TO

重命名表

ALTER TABLE users RENAME TO customers;

6 ALTER TABLE ... MODIFY

修改字段类型

ALTER TABLE users MODIFY name VARCHAR(200);

7 RENAME TABLE

重命名（MySQL）

RENAME TABLE users TO customers;

8 DESC tablename;

查看表结构（MySQL）

DESC users;

9 \d tablename

查看表结构（PostgreSQL）

\d users

SHOW COLUMNS FROM table;

查看字段

SHOW COLUMNS FROM users;

11 TRUNCATE TABLE

清空表数据

TRUNCATE TABLE users;

12 CREATE TABLE LIKE

克隆表结构

CREATE TABLE new_users LIKE users;

13 COMMENT ON COLUMN

添加字段注释

COMMENT ON COLUMN users.name IS '用户姓名';

14 ALTER TABLE ... ADD PRIMARY KEY

设置主键

ALTER TABLE users ADD PRIMARY KEY (id);

15 ALTER TABLE ... ADD INDEX

添加索引

ALTER TABLE users ADD INDEX idx_name(name);

命令

用法

示例

1 INSERT INTO

插入数据

INSERT INTO users VALUES (1, '张三');

2 INSERT IGNORE

忽略重复插入

INSERT IGNORE INTO users VALUES ...

3 INSERT ... ON DUPLICATE KEY

有则更新

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

4 SELECT * FROM table;

查询全部数据

SELECT * FROM users;

5 SELECT ... WHERE

条件查询

SELECT * FROM users WHERE id=1;

6 SELECT COUNT(*)

统计行数

SELECT COUNT(*) FROM users;

7 UPDATE table SET ... WHERE ...

更新数据

UPDATE users SET name='李四' WHERE id=1;

8 DELETE FROM

删除数据

DELETE FROM users WHERE id=1;

9 LIMIT

限制返回行数

SELECT * FROM users LIMIT 10;

ORDER BY

排序

SELECT * FROM users ORDER BY name ASC;

11 GROUP BY

分组聚合

SELECT dept, COUNT(*) FROM emp GROUP BY dept;

12 HAVING

分组条件

... GROUP BY dept HAVING COUNT(*) > 3;

13 DISTINCT

去重

SELECT DISTINCT name FROM users;

14 IN

匹配集合

SELECT * FROM users WHERE id IN (1,2,3);

15 BETWEEN

区间匹配

SELECT * FROM users WHERE age BETWEEN 20 AND 30;

命令

功能

示例

1 CREATE USER

创建用户

CREATE USER 'dev'@'localhost' IDENTIFIED BY '123456';

2 GRANT

授权

GRANT SELECT, INSERT ON testdb.* TO 'dev'@'localhost';

3 REVOKE

撤权

REVOKE INSERT ON testdb.* FROM 'dev'@'localhost';

4 DROP USER

删除用户

DROP USER 'dev'@'localhost';

5 ALTER USER

修改密码

ALTER USER 'dev'@'localhost' IDENTIFIED BY 'newpass';

6 SHOW GRANTS

查看权限

SHOW GRANTS FOR 'dev'@'localhost';

7 FLUSH PRIVILEGES

刷新权限（MySQL）

FLUSH PRIVILEGES;

8 SET ROLE

切换角色（PostgreSQL/Oracle）

SET ROLE readonly;

9 CREATE ROLE

创建角色

CREATE ROLE admin;

GRANT role TO user

分配角色

GRANT admin TO dev;

命令

功能

示例

1 CREATE INDEX

添加索引

CREATE INDEX idx_name ON users(name);

2 DROP INDEX

删除索引

DROP INDEX idx_name ON users;

3 SHOW INDEX

查看索引

SHOW INDEX FROM users;

4 EXPLAIN SELECT

查询执行计划

EXPLAIN SELECT * FROM users WHERE name='张三';

5 ANALYZE TABLE

分析表

ANALYZE TABLE users;

6 OPTIMIZE TABLE

表优化（MySQL）

OPTIMIZE TABLE users;

7 VACUUM

清理垃圾行（PostgreSQL）

VACUUM FULL users;

8 CLUSTER

聚簇排序（PostgreSQL）

CLUSTER users USING idx_name;

9 SET STATISTICS

设置统计粒度

ALTER TABLE users ALTER COLUMN name SET STATISTICS 1000;

SHOW PROCESSLIST

查看慢查询线程

SHOW FULL PROCESSLIST;

命令

功能

示例

1 mysqldump

MySQL 备份

mysqldump -u root -p testdb > backup.sql

2 mysql <

MySQL 恢复

mysql -u root -p testdb < backup.sql

3 pg_dump

PostgreSQL 备份

pg_dump -U postgres testdb > backup.sql

4 psql <

PostgreSQL 恢复

psql -U postgres testdb < backup.sql

5 exp / imp

Oracle 旧版备份/恢复工具

exp user/pass tables=emp file=emp.dmp

6 expdp / impdp

Oracle 数据泵

expdp user/pass schemas=HR directory=dir dumpfile=hr.dmp

7 BACKUP DATABASE

SQL Server 备份

BACKUP DATABASE testdb TO DISK = 'D:\backup.bak';

8 RESTORE DATABASE

SQL Server 恢复

RESTORE DATABASE testdb FROM DISK = 'D:\backup.bak';

9 binlog 备份

MySQL 二进制日志

mysqlbinlog binlog.000001 > log.sql

SELECT * INTO OUTFILE

导出 CSV

SELECT * FROM users INTO OUTFILE '/tmp/users.csv';

命令

用途

示例

1 SHOW STATUS

MySQL 状态变量

SHOW STATUS LIKE 'Threads%';

2 SHOW VARIABLES

查看配置项

SHOW VARIABLES LIKE 'max_connections';

3 SELECT * FROM pg_stat_activity;

PostgreSQL 连接监控

SELECT * FROM pg_stat_activity;

4 SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;

查询活动连接

...WHERE TIME > 60;

5 SHOW ENGINE INNODB STATUS;

InnoDB 状态

死锁排查

6 SELECT * FROM pg_locks;

PostgreSQL 锁情况

查看阻塞

7 SELECT * FROM V$SESSION;

Oracle 会话监控

WHERE STATUS = 'ACTIVE';

8 SHOW FULL PROCESSLIST;

查看慢查询线程

MySQL

9 SET profiling=1;

开启查询性能分析

SHOW PROFILES;

SHOW TABLE STATUS;

表存储信息

包括行数、大小、索引等