如何在 MySQL 中创建和删除数据库和表

简介

MySQL 在表和数据库中维护其管理的数据。表在预定义的列和数据类型模式中存储数据。数据库是一种可以容纳多个表、索引、过程的结构，并有助于定义权限、自定义设置等的范围。

在本指南中，我们将向您展示如何在 MySQL 中创建表和数据库。我们将向您展示如何管理这两种结构的一些功能，然后我们将介绍如何在不再需要它们时删除它们以进行清理。

先决条件

要学习本指南，您需要使用 mysql 命令行客户端，以具有管理权限的用户身份登录到 MySQL 服务器。

如果您不想使用 root MySQL 管理帐户登录，则您的用户至少需要 CREATE 权限才能执行本指南中描述的操作。

您可以通过键入以下内容来检查用户的可用权限

SHOW GRANTS\G

如何创建新数据库？

我们将演示的第一个操作是创建一个新的数据库。在 MySQL 中，数据库包含 表、索引、过程和其他通常与单个项目相关的资源。这些资源中的每一个都必须在数据库中创建，因此学习如何创建新数据库是一个很好的第一步。

用于创建新数据库的基本语法如下所示

CREATE DATABASE <db_name>;

将 <db_name> 替换为您希望为数据库指定的名称，然后再执行命令。此命令将创建一个具有给定名称的数据库，并将当前用户设置为新数据库的所有者。

数据库将使用系统范围的默认字符集、排序规则和加密属性

• 字符集 (CHARACTER SET)： 设置新数据库的字符集。这是一个本地化选项，会影响哪些字符被视作大写、小写和数字。
• 排序规则 (COLLATE)： 设置数据库的排序规则或排序顺序。这是一个本地化选项，它确定在对项目进行排序时如何组织项目。
• 加密 (ENCRYPTION)： 为新数据库设置加密。这是一个布尔选项，可以是 'Y'（是）或 'N'（否）。

您可以通过键入以下内容查找可用于您安装的字符集

SHOW CHARACTER SET;

同样，您可以通过键入以下内容查找可用的排序规则

SHOW COLLATION;

如果您想为这些参数之一指定非默认值，您可以在主创建语句之后添加它们，如下所示

CREATE DATABASE <db_name>
  CHARACTER SET = 'utf8mb4'
  COLLATE = 'utf8mb4_0900_ai_ci'
  ENCRYPTION = 'Y';

要学习本指南中的示例，请使用实例的默认区域设置和 UTF8 字符集创建一个名为 school 的数据库

CREATE DATABASE school CHARACTER SET = 'utf8mb4';

这将使用您提供的规范创建您的新数据库。

列出现有数据库

要列出服务器上可用的数据库，请键入

SHOW DATABASES;

这将列出当前在环境中定义的每个数据库

+--------------------+
  Database           |
+--------------------+
  information_schema |
  mysql              |
  performance_schema |
  school             |
  sys                |
  testing            |
+--------------------+
6 rows in set (0.00 sec)

您可以使用 SHOW CREATE DATABASE 命令验证您提供的设置是否已应用于新的 school 数据库

SHOW CREATE DATABASE school;

输出将显示用于创建数据库的命令和选项，并使用应用的任何默认值填充选项

+----------+----------------------------------------------------------------------------------------------------------------------------------+
  Database | Create Database                                                                                                                  |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
  school   | CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

如何在 MySQL 中切换到不同的数据库

创建数据库后，您可以通过几种不同的方式来使用它。

第一种选择是在您使用的每个命令中将数据库指定为参数。但是，如果您要运行多个与数据库对象相关的命令以节省时间，您也可以“切换到”或“使用”数据库，以将新数据库设置为您将要运行的任何数据库相关命令的默认上下文。

要切换到不同的数据库，请键入

USE <db_name>;

在我们的例子中，我们可以通过键入以下内容切换到我们创建的 school 数据库

USE school;

在数据库中创建表

创建数据库后，您可以在其中定义表和其他对象，以存储和管理您的数据。在 MySQL 中，表由表名、列定义（这些定义又包括名称、数据类型和约束等）以及表选项组成。

CREATE TABLE <table_name> (
    <column_name> <data_type> [<column_constraint>],
    [<table_constraint>,]
);

CREATE TABLE IF NOT EXISTS table_name (
    column_name TYPE [column_constraint],
    [table_constraint,]
);

CREATE TABLE supplies (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  description VARCHAR(255),
  manufacturer VARCHAR(255),
  color VARCHAR(255),
  inventory INT CHECK (inventory >= 0)
);

SHOW TABLES;

+------------------+
  Tables_in_school |
+------------------+
  supplies         |
+------------------+
1 row in set (0.01 sec)

SHOW CREATE TABLE supplies\G

*************************** 1. row ***************************
       Table: supplies
Create Table: CREATE TABLE `supplies` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `manufacturer` varchar(255) DEFAULT NULL,
  `color` varchar(255) DEFAULT NULL,
  `inventory` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `supplies_chk_1` CHECK ((`inventory` >= 0))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec

CREATE TABLE teachers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(255),
  last_name VARCHAR(255),
  subject VARCHAR(255),
  grade_level INT
);

如何在 MySQL 中查看表

在 MySQL 中，您可以通过几种不同的方式列出表，具体取决于您要查找的信息。

如果您想查看数据库中可用的表，请键入

SHOW TABLES;

+------------------+
  Tables_in_school |
+------------------+
  supplies         |
  teachers         |
+------------------+
2 rows in set (0.00 sec)

您还可以检查表的模式是否与您的规范匹配

DESCRIBE teachers;

+--------------+--------------+------+-----+---------+----------------+
  Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
  id           | int          | NO   | PRI | NULL    | auto_increment |
  name         | varchar(255) | YES  |     | NULL    |                |
  description  | varchar(255) | YES  |     | NULL    |                |
  manufacturer | varchar(255) | YES  |     | NULL    |                |
  color        | varchar(255) | YES  |     | NULL    |                |
  inventory    | int          | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

teachers 表似乎与我们的定义匹配。

如何使用 ALTER TABLE 命令更改表

如果您需要更改 MySQL 中现有表的模式，可以使用 ALTER TABLE 命令。ALTER TABLE 命令与 CREATE TABLE 命令非常相似，但它作用于现有表。

ALTER TABLE <table_name> <change_command> <change_parameters>;

ALTER TABLE some_table ADD missing_column text;

ALTER TABLE some_table DROP COLUMN useless_column;

ALTER TABLE resident MODIFY COLUMN id INT;

删除表

如果要删除表，可以使用 DROP TABLE SQL 语句。这将删除表以及其中存储的任何数据。

基本语法如下所示

DROP TABLE <table_name>;

如果表存在，这将删除该表；如果表名不存在，则会引发错误。

如果您希望在表存在时删除表，而在表不存在时不执行任何操作，则可以在语句中包含 IF EXISTS 限定符

DROP TABLE IF EXISTS <table_name>;

默认情况下，在存在依赖项时，无法删除对其他表或对象具有依赖项的表。

通过键入以下内容删除我们之前创建的 supplies 表

DROP TABLE supplies;

我们将保留 teachers 数据库，以演示删除数据库的语句也会删除所有子对象，如表。

删除数据库

DROP DATABASE 语句告诉 MySQL 删除指定的数据库。基本语法如下所示

DROP DATABASE <database_name>;

将 <database_name> 占位符替换为您要删除的数据库的名称。这将删除找到的数据库。如果找不到数据库，则会发生错误

DROP DATABASE some_database;

ERROR 1008 (HY000): Can't drop database 'some_database'; database doesn't exist

如果您希望在数据库存在时删除数据库，否则不执行任何操作，请包含可选的 IF EXISTS 选项

DROP DATABASE IF EXISTS some_database;

Query OK, 0 rows affected, 1 warning (0.00 sec)

这将删除数据库，如果找不到数据库，则不执行任何操作。

要删除我们在本指南中使用的 school 数据库，请列出系统上现有的数据库

SHOW DATABASES;

+--------------------+
  Database           |
+--------------------+
  information_schema |
  mysql              |
  performance_schema |
  school             |
  sys                |
  testing            |
+--------------------+
6 rows in set (0.00 sec)

使用以下命令删除 school 数据库

DROP DATABASE school;

这将删除 school 数据库以及其中定义的 teachers 表。

结论

本文介绍了如何在 MySQL 中创建和删除数据库和表的基础知识。这些是设置数据库系统和定义数据结构所需的一些最基本命令。

如前所述，本 MySQL 教程中介绍的 SQL 语句，特别是 CREATE TABLE 语句，还有许多其他参数可用于更改系统的行为。您可以通过查看 MySQL 官方文档来了解有关这些参数的更多信息。

常见问题解答

ALTER TABLE <table_name> <change_command> <change_parameters>;

ALTER TABLE some_table ADD missing_column text;

DROP TABLE IF EXISTS <table_name>;

DROP INDEX <index_name> ON <table_name>;

CREATE TEMPORARY TABLE table_name(
   column_1_definition,
   column_2_definition,
   ...,
   table_constraints
);