Practical MySQL & MariaDB

MySQL 命令指南：从入门到中级基础、CRUD、用户、备份与故障排除

基本操作
目次

1. 介绍

MySQL 是在数据库管理系统（DBMS）中广泛使用的开源关系型数据库。它在 Web 应用和企业系统中尤其受欢迎。本文将详细解释 MySQL 的核心命令，并以结构化的方式呈现，使初学者到中级用户都能顺畅学习。

1.1 MySQL 是什么？

MySQL 是用于管理关系型数据库（RDB）的软件。数据存储在表中，您通过 SQL（结构化查询语言）来操作这些数据。MySQL 具有以下特性：

  • 开源且免费，任何人都可以使用
  • 性能快速且轻量
  • 兼容多种编程语言（如 PHP、Python、Java）
  • 适用于大规模数据库的运营

1.2 学习 MySQL 命令的好处

要高效使用 MySQL，了解基本命令至关重要。主要好处包括：

  • 更高效的数据管理：掌握 SQL 命令后，您可以快速检索、更新和删除数据
  • 工作流自动化：使用脚本可以实现数据库操作的自动化
  • 更顺畅的错误处理：当出现问题时，您可以使用相应的 SQL 命令进行解决

1.3 本文将学习的内容

本文将详细说明以下主题：

  • 基本 MySQL 操作（启动、连接及数据库操作）
  • 增删改查（CRUD）操作：添加、检索、更新和删除数据
  • 用户管理与权限设置
  • 实用命令与故障排查

接下来，我们将按顺序逐章展开。让我们从基本的 MySQL 操作开始。

2. 基本 MySQL 操作（适合初学者）

2.1 启动和连接 MySQL

2.1.1 启动 MySQL

因为 MySQL 以服务器程序的形式运行，必须先启动服务器。启动方式会因操作系统而异。

在 Linux / macOS 上启动

在 Linux 和 macOS 上，您可以使用 systemctl 命令启动 MySQL。

sudo systemctl start mysql

检查 MySQL 是否在运行

sudo systemctl status mysql

如果看到 “active (running)”，说明 MySQL 正常运行。

在 Windows 上启动

在 Windows 上，MySQL 通常以服务方式运行，您可以使用 net start 命令。

net start mysql

或者，如果您使用 XAMPP，打开 XAMPP 控制面板，点击 “MySQL” 的 “Start”。

2.1.2 连接 MySQL

要连接 MySQL，使用 mysql 命令。基本的连接命令如下：

mysql -u username -p
以 root 用户连接

默认情况下，root 用户被配置为管理员。您可以使用以下命令连接：

mysql -u root -p

按回车后，系统会提示您输入密码。输入正确的密码后，即可登录 MySQL。

连接到特定主机

要连接远程 MySQL 服务器，使用 -h 选项。

mysql -h hostname -u username -p

例如，要连接位于 192.168.1.100 的 MySQL 服务器，写成：

mysql -h 192.168.1.100 -u root -p
通过指定端口号连接

默认的 MySQL 端口是 3306。如果端口已被更改，可使用 -P 选项指定：

mysql -h 192.168.1.100 -P 3307 -u root -p

2.2 基本 MySQL 命令

连接到 MySQL 后，尝试运行一些基础命令。

2.2.1 检查 MySQL 版本

要查看当前使用的 MySQL 版本，运行以下命令：

SELECT VERSION();

2.2.2 检查当前数据库

要查看当前选中的数据库，运行：

SELECT DATABASE();

如果未选中任何数据库，将返回 NULL。

2.2.3 列出可用数据库

要列出 MySQL 中存在的数据库，使用：

SHOW DATABASES;

2.3 常见错误及解决方法

2.3.1 “Access denied” 错误

示例错误：

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
如何修复：
  1. 验证密码是否正确
  2. 检查并重置 root 权限
    ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'NEW_PASSWORD';
FLUSH PRIVILEGES;

2.3.2 “无法连接到 ‘localhost’ 上的 MySQL 服务器” 错误

示例错误：

ERROR 2002 (HY000): Can't connect to MySQL server on 'localhost' (10061)
如何修复：
  1. 检查 MySQL 是否在运行（ systemctl status mysql ）
  2. 重启 MySQL
    sudo systemctl restart mysql

2.4 小结

在本节中，我们解释了如何启动 MySQL 并连接到它。

3. 数据库操作

在 MySQL 中，您创建 数据库 来管理数据，然后通过在其中放置 表 来组织信息。 在本节中，我们将详细说明创建、选择、列出和删除数据库等基本操作。

3.1 创建数据库

要在 MySQL 中创建数据库，请使用 CREATE DATABASE 命令。

3.1.1 创建基本数据库

如果运行以下 SQL 命令，您可以创建一个名为 my_database 的新数据库。

CREATE DATABASE my_database;
创建成功时的消息
Query OK, 1 row affected (0.01 sec)

3.1.2 当您尝试创建已存在的数据库时

如果数据库已经存在，您会看到如下错误：

ERROR 1007 (HY000): Can't create database 'my_database'; database exists

为防止此错误，添加 IF NOT EXISTS 选项会很有用。

CREATE DATABASE IF NOT EXISTS my_database;

当您运行此命令时，只有在数据库尚不存在时才会创建它。

3.2 列出数据库

要检查当前 MySQL 服务器上存在哪些数据库，请使用 SHOW DATABASES 命令。

SHOW DATABASES;

示例输出：

+--------------------+
| Database          |
+--------------------+
| information_schema |
| my_database       |
| mysql             |
| performance_schema |
| sys               |
+--------------------+

注意：mysql 和 information_schema 等系统数据库是 MySQL 管理所必需的。

3.3 选择数据库

在 MySQL 中，必须选择要使用的数据库。要指定数据库，请运行 USE 命令。

USE my_database;
成功时的消息
Database changed

要确认当前选择了哪个数据库，请使用以下命令：

SELECT DATABASE();

示例输出：

+------------+
| DATABASE() |
+------------+
| my_database |
+------------+

3.4 删除数据库

要删除不再需要的数据库，请使用 DROP DATABASE 命令。

DROP DATABASE my_database;

3.4.1 删除前的重要注意事项

  • 小心：删除数据库会移除所有数据！
  • 为避免意外错误，您也可以添加 IF EXISTS。
    DROP DATABASE IF EXISTS my_database;

即使 my_database 不存在，此命令也会在没有错误的情况下完成。

3.5 常见错误及修复

3.5.1 “Access denied for user” 错误

示例错误：

ERROR 1044 (42000): Access denied for user 'user_name'@'localhost' to database 'my_database'
如何修复
  • 使用 GRANT 命令授予权限
    GRANT ALL PRIVILEGES ON my_database.* TO 'user_name'@'localhost';
FLUSH PRIVILEGES;

3.5.2 “无法删除数据库” 错误

示例错误：

ERROR 1010 (HY000): Error dropping database (can't rmdir './my_database', errno: 39)
如何修复
  • 重启 MySQL
    sudo systemctl restart mysql
  • 首先删除 my_database 中的所有表，然后运行 DROP DATABASE。

3.6 小结

在本节中，您学习了如何创建、选择、列出和删除数据库。

4. 表操作

After creating a database, you need to create tables inside it to organize data. A table is like a grid for storing data, consisting of columns and rows (records).

在本节中，我们将详细说明 创建、检查、列出和删除表 等基本操作。

4.1 创建表

要创建表，使用 CREATE TABLE 命令。如果运行以下 SQL，它会创建一个名为 users 的表。

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

4.1.1 创建表时各元素说明

  • id INT AUTO_INCREMENT PRIMARY KEY
  • id 列是整数 (INT)，自动递增 (AUTO_INCREMENT)，并设为主键 (PRIMARY KEY)。
  • name VARCHAR(50) NOT NULL
  • name 列是长度最多 50 的字符串 (VARCHAR)，并因 NOT NULL 而为必填。
  • email VARCHAR(100) UNIQUE NOT NULL
  • email 列是长度最多 100 的字符串，UNIQUE 约束防止电子邮件地址重复。
  • created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  • created_at 列默认自动设置为当前日期/时间。
成功时的消息
Query OK, 0 rows affected (0.02 sec)

4.2 检查表结构

要检查已创建表的结构，可使用 DESC 或 SHOW COLUMNS。

DESC users;

或

SHOW COLUMNS FROM users;

示例输出：

+------------+--------------+------+-----+-------------------+----------------+
| Field      | Type         | Null | Key | Default           | Extra          |
+------------+--------------+------+-----+-------------------+----------------+
| id         | int(11)      | NO   | PRI | NULL              | auto_increment |
| name       | varchar(50)  | NO   |     | NULL              |                |
| email      | varchar(100) | NO   | UNI | NULL              |                |
| created_at | timestamp    | YES  |     | CURRENT_TIMESTAMP |                |
+------------+--------------+------+-----+-------------------+----------------+

4.3 列出表

要列出当前数据库中存在的表，运行 SHOW TABLES 命令。

SHOW TABLES;

示例输出：

+------------------+
| Tables_in_mydb   |
+------------------+
| users            |
| products         |
| orders           |
+------------------+

4.4 删除表

要删除不再需要的表，使用 DROP TABLE 命令。

DROP TABLE users;

4.4.1 删除表时的重要注意事项

  • 小心：删除表会永久移除数据！
  • 为避免删除时出错，请添加 IF EXISTS 。
    DROP TABLE IF EXISTS users;
成功时的消息
Query OK, 0 rows affected (0.01 sec)

4.5 重置表数据（删除所有行）

如果想保留表结构但仅删除其中的数据，可使用 TRUNCATE TABLE。

TRUNCATE TABLE users;
与 DROP TABLE 的区别
  • DROP TABLE 删除整个表。
  • TRUNCATE TABLE 只删除数据，保留表结构。

4.6 常见错误及解决方法

4.6.1 “表已存在” 错误

示例错误：

ERROR 1050 (42S01): Table 'users' already exists
解决方法
  • 检查已有表
    SHOW TABLES;
  • 使用 IF NOT EXISTS 创建表
    CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

4.6.2 “未知表” 错误

示例错误：

ERROR 1051 (42S02): Unknown table 'users'
解决方法
  • 检查表是否存在，使用 SHOW TABLES;
  • 如果不存在，添加 IF EXISTS
    DROP TABLE IF EXISTS users;

4.7 小结

在本节中，我们介绍了基本的 MySQL 表操作：创建、检查和删除表。