MySQL命令行有哪些常用操作？

MySQL作为全球最受欢迎的开源关系型数据库管理系统之一,其命令行工具是数据库管理员和开发者日常工作中不可或缺的工具，通过命令行，用户可以高效地管理数据库、执行SQL语句、进行数据备份与恢复等操作，以下将详细介绍MySQL常用命令行操作，涵盖连接数据库、用户管理、数据库操作、表操作、数据操作、权限管理、备份与恢复以及实用技巧等多个方面。

连接与退出MySQL

要使用MySQL命令行工具,需要正确连接到MySQL服务器，在命令行终端中，输入以下命令并按回车：
mysql -h 主机名 -u 用户名 -p
-h指定主机名（本地可省略或使用localhost），-u指定用户名，-p表示需要输入密码，连接成功后，会显示MySQL版本信息和mysql>提示符，若要退出MySQL，可输入exit;或quit;并按回车，或使用快捷键Ctrl+D。

数据库操作

连接成功后,首先需要选择或创建数据库，常用命令包括：

• SHOW DATABASES;：列出所有数据库。
• CREATE DATABASE 数据库名;：创建新数据库，例如CREATE DATABASE mydb;。
• USE 数据库名;：切换到指定数据库，后续操作将在此数据库下执行，例如USE mydb;。
• DROP DATABASE 数据库名;：删除数据库，需谨慎使用，例如DROP DATABASE mydb;。

表操作

在数据库中,表是存储数据的核心对象，表操作包括创建、查看、修改和删除等：

• SHOW TABLES;：列出当前数据库中的所有表。
• CREATE TABLE 表名 (列名1 数据类型1, 列名2 数据类型2, ...);：创建表，例如CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50), age INT);。
• DESC 表名;或SHOW COLUMNS FROM 表名;：查看表结构，包括列名、数据类型、是否允许NULL、键信息等。
• ALTER TABLE 表名 ADD COLUMN 列名 数据类型;：添加列，例如ALTER TABLE users ADD COLUMN email VARCHAR(100);。
• ALTER TABLE 表名 MODIFY COLUMN 列名 新数据类型;：修改列的数据类型，例如ALTER TABLE users MODIFY COLUMN name VARCHAR(100);。
• ALTER TABLE 表名 DROP COLUMN 列名;：删除列，例如ALTER TABLE users DROP COLUMN age;。
• DROP TABLE 表名;：删除表，例如DROP TABLE users;。

数据操作

数据操作主要指对表中数据的增删改查（CRUD）：

• 插入数据：INSERT INTO 表名 (列名1, 列名2, ...) VALUES (值1, 值2, ...);，例如INSERT INTO users (id, name, email) VALUES (1, '张三', 'zhangsan@example.com');，若插入所有列，可省略列名，但值的顺序需与表结构一致。
• 查询数据：SELECT 列名1, 列名2, ... FROM 表名 WHERE 条件;，例如SELECT * FROM users WHERE age > 18;（表示所有列）。WHERE子句用于筛选数据，支持、、>、<、BETWEEN、IN、LIKE等条件运算符，以及AND、OR、NOT等逻辑运算符。
• 更新数据：UPDATE 表名 SET 列名1 = 值1, 列名2 = 值2, ... WHERE 条件;，例如UPDATE users SET age = 20 WHERE name = '张三';。注意：WHERE子句不可省略，否则将更新所有行。
• 删除数据：DELETE FROM 表名 WHERE 条件;，例如DELETE FROM users WHERE id = 1;。注意：同样需谨慎使用WHERE子句，避免误删所有数据。

用户与权限管理

MySQL的安全性依赖于用户权限管理,常用命令包括：

• CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';：创建新用户，例如CREATE USER 'dev'@'localhost' IDENTIFIED BY 'password123';。
• GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';：授权权限，权限列表如SELECT, INSERT, UPDATE，使用表示所有数据库和表，例如GRANT ALL PRIVILEGES ON mydb.* TO 'dev'@'localhost';。
• REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';：撤销权限，例如REVOKE DELETE ON mydb.users FROM 'dev'@'localhost';。
• SHOW GRANTS FOR '用户名'@'主机名';：查看用户权限，例如SHOW GRANTS FOR 'dev'@'localhost';。
• DROP USER '用户名'@'主机名';：删除用户，例如DROP USER 'dev'@'localhost';。

备份与恢复

数据备份是数据库管理的重要环节,MySQL提供了mysqldump工具进行备份：

• 备份数据库：mysqldump -u 用户名 -p 数据库名 > 备份文件.sql，例如mysqldump -u root -p mydb > mydb_backup.sql，若备份所有数据库，可使用--all-databases参数。
• 恢复数据库：首先创建空数据库（若不存在），然后使用mysql命令导入备份文件：mysql -u 用户名 -p 数据库名 < 备份文件.sql，例如mysql -u root -p mydb < mydb_backup.sql。

实用技巧

• 命令编辑与历史：在MySQL命令行中，使用上下箭头键可浏览历史命令，使用Ctrl+R可搜索历史命令。
• 执行外部SQL文件：在命令行中输入source 文件路径;可执行SQL脚本文件，例如source /path/to/script.sql;。
• 显示查询结果格式：使用\G替代分号可使查询结果以垂直格式显示，便于阅读宽表数据，例如SELECT * FROM users\G;。
• 自动补全：在MySQL配置文件（~/.my.cnf或/etc/my.cnf）中添加auto-rehash，可实现命令和表名的自动补全（需重启MySQL或重新连接）。

常用数据类型

在创建表时,选择合适的数据类型对性能和存储至关重要，以下是MySQL常用数据类型：

相关问答FAQs

Q1: 如何忘记MySQL root密码并重置？
A1: 重置MySQL root密码的步骤如下（以Linux系统为例）：

1. 停止MySQL服务：sudo systemctl stop mysql（或sudo /etc/init.d/mysql stop）。
2. 跳过权限表启动MySQL：sudo mysqld_safe --skip-grant-tables &。
3. 无密码登录MySQL：mysql -u root。
4. 执行以下命令重置密码（注意MySQL版本差异，5.7及以上版本需更新authentication_string字段）：

   USE mysql;
   UPDATE user SET authentication_string=PASSWORD('新密码') WHERE User='root';
   FLUSH PRIVILEGES;
   EXIT;

5. 重启MySQL服务：sudo systemctl start mysql。

Q2: 如何优化MySQL查询性能？
A2: 优化MySQL查询性能可从以下几个方面入手：

1. 索引优化：为经常用于查询条件（WHERE）、排序（ORDER BY）和连接（JOIN）的列创建索引，例如CREATE INDEX idx_name ON users(name);，但避免过多索引，否则会降低写入性能。
2. SQL语句优化：避免使用SELECT *，只查询需要的列；避免在WHERE子句中对列进行函数操作或表达式计算（如WHERE SUBSTRING(name,1,3)='张'），这会导致索引失效；使用LIMIT分页查询，避免一次性返回大量数据。
3. 表结构优化：选择合适的数据类型，例如用INT而非VARCHAR存储数字；对大表进行分区或分表，提高查询效率。
4. 配置优化：调整MySQL配置文件（my.cnf）中的参数，如innodb_buffer_pool_size（InnoDB缓冲池大小）、max_connections（最大连接数）等，根据服务器资源合理设置。
5. 使用EXPLAIN分析查询：通过EXPLAIN SELECT ...;查看查询执行计划，判断是否使用了索引、全扫描等，针对性优化。