连接MySQL：mysql -u root -p 输入密码后进入命令行。创建数据库：CREATE DATABASE mydb; 使用数据库：USE mydb; 创建表：CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50)); 插入数据：INSERT INTO users VALUES (1, '张三'); 查询数据：SELECT * FROM users; 更新数据：UPDATE users SET name='李四' WHERE id=1; 删除数据：DELETE FROM users WHERE id=1;

基本查询命令

SELECT * FROM table_name; -- 查询所有数据 SELECT column_name FROM table_name WHERE condition; -- 带条件查询 SELECT COUNT(*) FROM table_name; -- 统计行数 SELECT DISTINCT column_name FROM table_name; -- 去重查询 ORDER BY column_name ASC; -- 排序 LIMIT 10; -- 限制结果数

数据修改命令

INSERT INTO table_name (col1, col2) VALUES (val1, val2); UPDATE table_name SET col1=val1 WHERE id=1; DELETE FROM table_name WHERE condition; TRUNCATE TABLE table_name; -- 清空表数据

表管理命令

CREATE TABLE table_name (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100)); ALTER TABLE table_name ADD COLUMN email VARCHAR(100); ALTER TABLE table_name DROP COLUMN email; DROP TABLE table_name; RENAME TABLE old_name TO new_name;

索引和优化

CREATE INDEX idx_name ON table_name (column_name); SHOW INDEX FROM table_name; EXPLAIN SELECT * FROM table_name; -- 查询优化 OPTIMIZE TABLE table_name; -- 优化表

备份与恢复

mysqldump -u root -p dbname > backup.sql; -- 备份 mysql -u root -p dbname < backup.sql; -- 恢复 SHOW TABLES; DESCRIBE table_name; -- 查看表结构

用户和权限

CREATE USER 'user'@'localhost' IDENTIFIED BY 'password'; GRANT ALL ON dbname.* TO 'user'@'localhost'; FLUSH PRIVILEGES; SHOW GRANTS FOR 'user'@'localhost';

FAQ
Q: 如何查看所有数据库？
A: SHOW DATABASES;
Q: 如何退出MySQL？
A: EXIT; 或 QUIT;
Q: AUTO_INCREMENT是什么？
A: 自动递增主键，自增ID。
Q: 如何复制表？
A: CREATE TABLE new_table AS SELECT * FROM old_table;