Mysql 8 - SQL全新版本之CREATE DATABASE、ALTER DATABASE、DROP DATABASE

叁歲伎倆 2022-10-12 04:14 289阅读 0赞

## CREATE DATABASE ##

CREATE {
       DATABASE | SCHEMA} [IF NOT EXISTS] db_name
        [create_option] ...
    
    create_option: [DEFAULT] {
        CHARACTER SET [=] charset_name
      | COLLATE [=] collation_name
      | ENCRYPTION [=] {
       'Y' | 'N'}
    }

`CREATE DATABASE`创建给定名称的数据库。要使用此语句，需要有`CREATE DATABASE`的权限。

如果该数据库已经存在而没有指定`IF NOT EXISTS`，则会发生错误。

`CREATE DATABASE`不允许在已经执行了`LOCK TABLES`语句的会话中使用。

create database jack;
    use jack;
    create table kaven (
        id integer auto_increment,
        primary key PRIMARY_KEY(id)
    );
    lock tables kaven as kv read local;
    create database jack2;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70]

> Can’t execute the given command because you have active locked tables or an active transaction

MySQL使客户端会话能够明确获取表锁，以便与其他会话合作访问表，或者在会话需要独占访问表时，防止其他会话修改表。会话只能为自己获取或释放锁。一个会话不能为另一个会话获取锁或释放另一个会话持有的锁。

锁可用于模拟事务或在更新表时获得更高的速度。`LOCK TABLES`显式获取当前客户端会话的表锁。可以为基表或视图获取表锁。必须拥有该`LOCK TABLES`权限，以及`SELECT`每个要锁定的对象的权限。这个以后再详细介绍。

每个`create_option`指定一个数据库特性。数据库特征存储在数据字典中。

*  `CHARACTER SET`选项指定默认的数据库字符集。
 *  `COLLATE`选项指定默认的数据库排序规则。
 *  `ENCRYPTION`在`MySQL 8.0.16`中引入，该选项定义了是否对数据库加密，该加密选项由数据库中创建的表继承。允许的值为`'Y'`（启用加密）和 `'N'`（禁用加密）。如果未指定`ENCRYPTION`选项，则`default_table_encryption` 系统变量的值默认定义数据库加密。

要查看可用的字符集和排序规则，可以使用`SHOW CHARACTER SET`和 `SHOW COLLATION`语句。

MySQL中的数据库被实现为包含与数据库中的表对应的文件的目录。由于最初创建数据库时没有表，因此该`CREATE DATABASE`语句仅在MySQL数据目录下创建一个目录。

`MySQL 8.0`不支持通过在数据目录下手动创建目录（例如，使用`mkdir`）来创建数据库目录 。

创建数据库时，让服务器管理目录和其中的文件。直接操作数据库目录和文件会导致不一致和意外结果。

MySQL对数据库的数量没有限制。底层文件系统可能对目录数量有限制。

**演示**

如果已经存在该数据库，则会发生错误。

create database kaven;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 1]  
不存在该数据库即可创建成功。

create database test;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 2]  
即使该数据库有可能存在，我们可以通过`if not exists`来判断数据库是否存在，当该数据库不存在时，才创建这个数据库。

create database if not exists test;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 3]  
可用字符集，每个字符集有默认的排序规则。

SHOW CHARACTER SET;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 4]  
可用排序规则，有很多，博主这个版本的Mysql有`272`种，每种字符集对应多种排序规则，但每种字符集只有一种排序规则是默认的，如下面框起来的一行数据。

SHOW COLLATION;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 5]  
给数据库指定字符集`utf8mb4`，并且会有默认排序规则`utf8mb4_0900_ai_ci`。

create database if not exists jack character set 'utf8mb4';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 6]  
给数据库指定字符集`utf8mb4`，并且指定排序规则为`utf8mb4_bin`（随便找的，没有啥依据），之后有机会再详细介绍字符集和排序规则。

create database if not exists jack character set 'utf8mb4' collate 'utf8mb4_bin';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 7]  
博主这里的Mysql版本是`8.0.25`，是已经支持`ENCRYPTION` 选项了。

select version();

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 8]  
给数据库指定字符集`utf8mb4`，并且指定排序规则为`utf8mb4_bin`，以及`ENCRYPTION` 选项为`'Y'`，DataGrip显示的SQL语句报红，应该是DataGrip的版本问题，但SQL语句执行是成功了。

create database if not exists jack character set 'utf8mb4' collate 'utf8mb4_bin' encryption 'Y';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 9]

## ALTER DATABASE ##

ALTER {
       DATABASE | SCHEMA} [db_name]
        alter_option ...
    
    alter_option: {
        [DEFAULT] CHARACTER SET [=] charset_name
      | [DEFAULT] COLLATE [=] collation_name
      | [DEFAULT] ENCRYPTION [=] {
       'Y' | 'N'}
      | READ ONLY [=] {
       DEFAULT | 0 | 1}
    }

`ALTER DATABASE`能够更改数据库的整体特征。这些特征存储在数据字典中。此语句需要有对数据库的`ALTER`特权。

如果省略数据库名称，则该语句适用于默认数据库。在这种情况下，如果没有默认数据库，则会发生错误。

对于`alter_option`语句中省略的任何内容，数据库保留其当前选项值，但更改字符集可能会更改排序规则（因为每个字符集有一种默认的排序规则），反之亦然。

*  `CHARACTER SET`选项更改默认数据库字符集。
 *  `COLLATE`选项更改默认数据库排序规则。
 *  只有新创建的表会继承默认的数据库加密。对于与数据库关联的现有表，其加密保持不变。
 *  `READ ONLY`是在`MySQL 8.0.22`中引入的选项，控制是否允许修改数据库和其中的对象。允许的值为`DEFAULT`或者 `0`（非只读）和`1`（只读）。此选项对于数据库迁移很有用，因为`READ ONLY`可以将启用的数据库迁移到另一个MySQL实例，而无需担心数据库可能在操作期间发生更改。

**演示**

在创建`test`表的时候会出现错误，因为`jack`数据库现在是只读模式，不能修改数据库和其中的对象，比如这个例子，就是在该数据库中创建表。

drop database if exists jack;
    create database jack character set  'utf8' encryption 'Y';
    alter database jack character set 'utf8mb4' encryption 'N' read only 1;
    use jack;
    create table test(
        id integer auto_increment,
        primary key PRIMARY_KEY(id)
    );

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 10]

也不能修改数据库中表的数据，即数据库其中的对象，但可以`select`。

drop database if exists jack;
    create database jack character set  'utf8';
    use jack;
    create table test(
        id integer auto_increment,
        primary key PRIMARY_KEY(id)
    );
    insert into jack.test(id) values (1) , (2) , (3);
    alter database jack character set 'utf8mb4' encryption 'N' read only 1;
    select * from jack.test;
    insert into jack.test(id) values (4) , (5) , (6);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 11]

也不能`alter`数据库（除非`alter`中有`read only 0`，即需要先关掉只读模式，再进行修改）。

drop database if exists jack;
    create database jack character set  'utf8' encryption 'Y';
    alter database jack character set 'utf8mb4' encryption 'N' read only 1;
    alter database jack character set 'utf8';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 12]

也不能`drop`数据库。

drop database if exists jack;
    create database jack character set  'utf8' encryption 'Y';
    alter database jack character set 'utf8mb4' encryption 'N' read only 1;
    drop database jack;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 13]

需要先把数据库的只读模式关闭掉，才能修改数据库和其中的对象。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 14]

数据库只读模式下，若是`alter`中有`read only 0`，就可以执行成功。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 15]

`READ ONLY`允许用于 `ALTER DATABASE`，但不允许用于 `CREATE DATABASE`。对于只读数据库，由`SHOW CREATE DATABASE`语句得到的输出，在注释中会包含 `READ ONLY=1`，以指示其只读状态。

drop database if exists jack;
    create database jack character set  'utf8' encryption 'Y';
    alter database jack character set 'utf8mb4' encryption 'N' read only 1;
    SHOW CREATE DATABASE jack;
    alter database jack character set 'utf8' read only 0;
    SHOW CREATE DATABASE jack;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 16]

第一条`SHOW CREATE DATABASE jack;`的输出：

CREATE DATABASE `jack` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ /* READ ONLY = 1 */

第二条`SHOW CREATE DATABASE jack;`的输出：

CREATE DATABASE `jack` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */

## DROP DATABASE ##

DROP {
       DATABASE | SCHEMA} [IF EXISTS] db_name

已经在上面两个语句的介绍中出现过`DROP DATABASE`。

`DROP DATABASE`删除数据库中的所有表并删除数据库。要使用`DROP DATABASE`，需要对数据库具有`DROP`权限。

删除数据库时，不会自动删除专门为该数据库授予的权限。

`IF EXISTS`用于防止在数据库不存在时发生错误。

drop database if exists jack;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 17]

如果删除默认数据库，则会取消设置默认数据库（`DATABASE()`函数返回 `NULL`）。

use mysql;
    select DATABASE();
    create database if not exists jack;
    use jack;
    select DATABASE();
    drop database if exists jack;
    select DATABASE();

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 18]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 19]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 20]

`DROP DATABASE`返回被删除的表数。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 21]

删除数据库不会删除在该数据库中创建的任何`TEMPORARY`表。`TEMPORARY`表在当创建它们的会话结束时，会自动删除。

create database if not exists jack;
    use jack;
    create table kaven_1 (
        id integer auto_increment ,
        primary key PRIMARY_KEY(id)
    );
    create table kaven_2 (
        id integer auto_increment ,
        primary key PRIMARY_KEY(id)
    );
    create temporary table kaven_3 (
        id integer auto_increment ,
        primary key PRIMARY_KEY(id)
    );
    create temporary table kaven_4 (
        id integer auto_increment ,
        primary key PRIMARY_KEY(id)
    );
    drop database jack;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 22]

删除数据库后，`TEMPORARY`表还是可以在当前会话中使用。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 23]

到这里就结束啦，欢迎给博主纠正错误。

**参考**

*  [Mysql 官网][Mysql]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70]: /images/20221005/4a426f1f469b46f197863474ab89bcc4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 1]: /images/20221005/7f098b71ff5c45e0a01c1091b9fae570.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 2]: /images/20221005/25ddf3bcce694366bf212a8f6dc36395.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 3]: /images/20221005/cb35d047945548f5b00cd7d21c10eafb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 4]: /images/20221005/198f56edcccb4024a871477331a0bb41.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 5]: /images/20221005/a5e8e7632a2f43bd84e786fac38dec35.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 6]: /images/20221005/6c3202878c3d46278304f77563d66d17.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 7]: /images/20221005/5342e3baf8ea48a4b23a5a84796dd7ea.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 8]: /images/20221005/e8b4e999a3ad4e0985fcec975fc2a481.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 9]: /images/20221005/e4289498dd2940cab7eb8c07f6e67af0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 10]: /images/20221005/40744f26f0eb4dcc90a9897978796bf5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 11]: /images/20221005/470a607234fd4535aea0fa01b8ae1ab6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 12]: /images/20221005/6a227b5f93ef4657b105cfb6e37b2d40.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 13]: /images/20221005/31b3fc9724814ffda84e41c79e4d69dc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 14]: /images/20221005/7c0a39239f694be2802d0050a2ba9d8e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 15]: /images/20221005/d903f311084d45dda8299e7415102b66.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 16]: /images/20221005/941995550b4f459fbfc7931deb918310.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 17]: /images/20221005/d7c6fa0bc6594a418859b5dd6f6bbdac.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 18]: /images/20221005/b969420350cb4bcfa55f728ca77f4d1f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 19]: /images/20221005/c4707e443c8c4ae1a31245439ebb6d53.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 20]: /images/20221005/539be906e47e4c0b9638324d0c117b60.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 21]: /images/20221005/daf9777b3a93474f8bab365ee50ddc71.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 22]: /images/20221005/c03321d1215c4832812a8096ea5b3321.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 23]: /images/20221005/fdb428c04737427c91ebe8ff7ad3fa5b.png
[Mysql]: https://dev.mysql.com/doc/refman/8.0/en/sql-data-definition-statements.html