1. 数据库操作（DDL）
| 命令 | 作用 | 示例 |
|---|---|---|
CREATE DATABASE [IF NOT EXISTS] dbname [OPTIONS]; | 创建数据库（可选字符集/排序规则） | CREATE DATABASE IF NOT EXISTS shop DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; |
DROP DATABASE [IF EXISTS] dbname; | 删除数据库 | DROP DATABASE IF EXISTS shop; |
ALTER DATABASE dbname OPTIONS; | 修改数据库属性 | ALTER DATABASE shop CHARACTER SET utf8mb4; |
SHOW DATABASES; | 查看所有数据库 | SHOW DATABASES; |
USE dbname; | 切换数据库 | USE shop; |
2. 表操作（DDL）
| 命令 | 作用 | 示例 |
|---|---|---|
CREATE TABLE [IF NOT EXISTS] table (...) ENGINE=InnoDB; | 创建表 | CREATE TABLE IF NOT EXISTS user(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50)); |
CREATE TABLE new_table LIKE old_table; | 复制表结构 | CREATE TABLE user_copy LIKE user; |
CREATE TABLE new_table AS SELECT ...; | 复制数据 | CREATE TABLE user_backup AS SELECT * FROM user; |
DROP TABLE [IF EXISTS] table; | 删除表 | DROP TABLE IF EXISTS user; |
TRUNCATE [TABLE] table; | 清空表 | TRUNCATE TABLE user; |
| `ALTER TABLE table ADD | DROP | MODIFY |
ALTER TABLE table RENAME [TO] new_name; | 重命名表 | ALTER TABLE user RENAME TO user_info; |
RENAME TABLE old TO new; | 重命名表 | RENAME TABLE user TO user_info; |
CREATE INDEX idx_name ON table (col1,...); | 创建索引 | CREATE INDEX idx_username ON user(name); |
DROP INDEX idx_name ON table; | 删除索引 | DROP INDEX idx_username ON user; |
SHOW TABLES; | 查看表 | SHOW TABLES; |
SHOW CREATE TABLE table; | 查看建表语句 | SHOW CREATE TABLE user; |
DESCRIBE table; / DESC table; | 查看表结构 | DESC user; |
3. 数据操作（DML）
| 命令 | 作用 | 示例 |
|---|---|---|
INSERT [IGNORE] INTO table (cols...) VALUES (...),(...); | 插入数据 | INSERT INTO user(name) VALUES('Alice'),('Bob'); |
INSERT INTO table SET col1=val1,...; | 另一种插入写法 | INSERT INTO user SET name='Alice', email='a@x.com'; |
INSERT INTO table ... ON DUPLICATE KEY UPDATE col=VALUES(col), ...; | 主键/唯一键冲突更新 | INSERT INTO user(id,name) VALUES(1,'Tom') ON DUPLICATE KEY UPDATE name='Tom'; |
REPLACE INTO table (...) VALUES (...); | 存在则先删后插 | REPLACE INTO user(id,name) VALUES(1,'Tom'); |
UPDATE table SET col=val [WHERE ...] [ORDER BY ...] [LIMIT n]; | 更新数据 | UPDATE user SET name='Bob' WHERE id=1; |
DELETE FROM table [WHERE ...] [ORDER BY ...] [LIMIT n]; | 删除数据 | DELETE FROM user WHERE id=1; |
4. 数据查询（DQL）
| 命令 | 作用 | 示例 |
|---|---|---|
SELECT ... FROM ... [WHERE ...] [GROUP BY ...] [HAVING ...] [ORDER BY ...] [LIMIT ...]; | 基础查询 | SELECT id,name FROM user WHERE id>10 ORDER BY name LIMIT 5; |
SELECT DISTINCT col FROM table; | 去重 | SELECT DISTINCT name FROM user; |
SELECT ... AS alias; | 别名 | SELECT name AS username FROM user; |
JOIN | 多表连接 | SELECT u.id,o.id FROM user u JOIN orders o ON u.id=o.user_id; |
LEFT JOIN / RIGHT JOIN | 外连接 | SELECT * FROM user LEFT JOIN orders ON ...; |
UNION [ALL] | 合并结果集 | (SELECT name FROM u1) UNION ALL (SELECT name FROM u2); |
EXPLAIN SELECT ...; | 查看执行计划 | EXPLAIN SELECT * FROM user; |
WITH cte AS (...) SELECT ... | 公用表表达式（CTE） | WITH t AS (SELECT id FROM user) SELECT * FROM t; |
5. 权限控制（DCL）
| 命令 | 作用 | 示例 |
|---|---|---|
CREATE USER 'user'@'host' IDENTIFIED BY 'pwd'; | 创建用户 | CREATE USER 'test'@'%' IDENTIFIED BY '123456'; |
DROP USER 'user'@'host'; | 删除用户 | DROP USER 'test'@'%'; |
GRANT privileges ON db.table TO 'user'@'host'; | 授权 | GRANT SELECT,INSERT ON shop.* TO 'test'@'%'; |
REVOKE privileges ON db.table FROM 'user'@'host'; | 回收权限 | REVOKE INSERT ON shop.* FROM 'test'@'%'; |
SHOW GRANTS FOR 'user'@'host'; | 查看权限 | SHOW GRANTS FOR 'test'@'%'; |
6. 事务控制（TCL）
| 命令 | 作用 | 示例 |
|---|---|---|
START TRANSACTION; / BEGIN; | 开启事务 | START TRANSACTION; |
COMMIT; | 提交事务 | COMMIT; |
ROLLBACK; | 回滚事务 | ROLLBACK; |
SAVEPOINT sp; | 保存点 | SAVEPOINT sp1; |
ROLLBACK TO sp; | 回滚到保存点 | ROLLBACK TO sp1; |
7. 其他常用命令
| 命令 | 作用 | 示例 |
|---|---|---|
SHOW PROCESSLIST; | 查看连接 | SHOW PROCESSLIST; |
KILL process_id; | 终止连接 | KILL 1234; |
SHOW STATUS; | 查看状态变量 | SHOW STATUS LIKE 'Threads%'; |
SHOW VARIABLES; | 查看系统变量 | SHOW VARIABLES LIKE 'character_set%'; |
SET variable=value; | 修改系统变量 | SET autocommit=0; |
SELECT VERSION(); | 查看版本 | SELECT VERSION(); |
HELP 'keyword'; | 查看帮助 | HELP 'SELECT'; |
扫一扫
3711
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
