MySQL技能完整学习列表5、数据库操作——1、创建数据库和表——2、修改表结构（ALTER TABLE）

创建数据库和表操作

MySQL创建数据库的操作相对直接明了。以下是详细步骤和示例：

首先，您需要具有适当的权限才能在MySQL中创建数据库。通常，这意味着您需要以具有创建数据库权限的用户身份登录到MySQL服务器。

1、登录MySQL服务器

通过命令行登录到MySQL服务器。您可能需要提供用户名（例如“root”）和密码。

mysql -u root -p

系统会提示您输入密码。输入后，按Enter键继续。

2、创建数据库

一旦登录，您就可以使用SQL命令来创建数据库。以下是一个简单的示例，演示如何创建一个名为“mydatabase”的数据库。

CREATE DATABASE mydatabase;

这条命令将创建一个新的数据库，名为“mydatabase”。

3、选择数据库

创建数据库后，您可以使用以下命令来选择或切换到该数据库：

USE mydatabase;

4、设置数据库字符集

在创建数据库时，您还可以为其设置字符集。例如，如果要使用UTF-8字符集，可以使用以下命令：

CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

这将使用UTF-8字符集创建一个数据库，并使用utf8mb4_unicode_ci排序规则，这对于支持多种语言（包括表情符号）非常有用。

5. 查看所有数据库

要查看MySQL服务器上存在的所有数据库，可以使用以下命令：

SHOW DATABASES;

6、删除数据库

如果您需要删除数据库，可以使用以下命令：

DROP DATABASE mydatabase;

这将删除名为“mydatabase”的数据库及其所有内容。请小心使用此命令，因为它会永久删除数据。

7、修改数据库

您还可以使用ALTER DATABASE命令来修改数据库的属性。例如，要更改数据库的字符集，可以使用以下命令：

ALTER DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

这将更改现有数据库的字符集和排序规则。

8、创建数据库用户并设置权限

您可能还想为新的数据库创建一个用户并设置适当的权限。以下是一个示例：

首先，创建一个新用户：

CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypassword';

9、为该用户授予对数据库的访问权限

GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'%';

10、刷新权限

最后，刷新权限使更改生效：

FLUSH PRIVILEGES;

这允许新用户从任何主机连接到数据库并具有完全权限。您可以根据需要调整权限和主机设置。例如，如果您只想允许从本地主机连接，可以将'%'替换为'localhost'。同时，请确保选择一个强密码以提高安全性。 

修改表结构（ALTER TABLE）

MySQL中的ALTER TABLE语句用于修改现有表的结构。使用此语句，您可以添加、删除或修改列，更改表的字符集或排序规则，重命名表，以及进行其他结构更改。以下是ALTER TABLE语句的一些常见用法和示例：

1、添加新列：

您可以使用ADD子句向表中添加新列。例如，要向名为students的表中添加一个名为birthdate的日期列，可以使用以下语句：

ALTER TABLE students ADD birthdate DATE;

2、删除列

如果您不再需要表中的某个列，可以使用DROP COLUMN子句将其删除。例如，要从students表中删除birthdate列，可以使用以下语句：

ALTER TABLE students DROP COLUMN birthdate;

3、修改列

您可以使用MODIFY COLUMN或ALTER COLUMN子句更改现有列的属性。例如，要更改students表中name列的数据类型，将其从VARCHAR(50)更改为VARCHAR(100)，可以使用以下语句：

ALTER TABLE students MODIFY COLUMN name VARCHAR(100);

或者，如果您只想重命名该列，可以使用：

ALTER TABLE students CHANGE COLUMN old_name new_name VARCHAR(50);

4、重命名表

如果您想更改表的名称，可以使用RENAME TO子句。例如，要将students表重命名为pupils，可以使用以下语句：

ALTER TABLE students RENAME TO pupils;

5、添加主键

如果您需要向表中添加主键约束，可以使用ADD PRIMARY KEY子句。例如，要在students表中将id列设置为主键，可以使用以下语句：

ALTER TABLE students ADD PRIMARY KEY (id);

6、删除主键

要删除表中的主键约束，可以使用DROP PRIMARY KEY子句。例如：

ALTER TABLE students DROP PRIMARY KEY;

7、添加索引

您可以使用ADD INDEX子句向表中添加索引以提高查询性能。例如，要在students表的lastname列上添加索引，可以使用以下语句：

ALTER TABLE students ADD INDEX (lastname);

8、修改表的字符集和排序规则

如果您需要更改表的字符集或排序规则，可以使用CONVERT TO CHARACTER SET和COLLATE子句。例如：

ALTER TABLE students CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

这将更改表的字符集为utf8mb4，并使用utf8mb4_unicode_ci排序规则。

9. 修改表的存储引擎

您还可以使用`ENGINE`子句更改表的存储引擎。例如，要将表从InnoDB更改为MyISAM，可以使用以下语句：

ALTER TABLE students ENGINE = MyISAM;

请注意，在执行此操作之前，请确保了解不同存储引擎之间的差异和限制。

10、添加外键约束

您可以使用`ADD CONSTRAINT`子句向表中添加外键约束以维护引用完整性。例如：

ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id);

这将在名为orders的表中添加一个名为fk_customer的外键约束，该约束引用customers表中的id列。删除外键约束使用类似的语法。使用ALTER TABLE语句时，请确保在执行任何结构更改之前备份数据库或表。这些更改可能会影响数据完整性或查询性能，并且在某些情况下可能需要重新构建表或重新索引数据。