MySQL-Database operation

朴灿烈づ我的快乐病毒、 2022-10-31 13:54 146阅读 0赞

### 目录 ###

*   *   *   *  1.建立数据库（这里的环境Linux是Cent OS 8下的mysql—8.0.23）
             *   *  1.1使用CREATE DATABASE语句创建数据库
                 *  1.2使用CREATE DATABASE IF NOT EXISTS语句创建数据库
             *  2.查看数据库
             *   *  2.1查看数据库的创建信息
                 *  2.2修改数据库名称
                 *  2.3 通过导入/导出数据修改数据库名称
                 *  2.4通过创建数据表修改数据库名称
             *  3.数据库编码
             *   *  3.1创建数据库时指定字符编码
             *  3.2修改数据库的字符编码
             *   *  3.3删除数据库
                 *  到这里数据库的基础知识结束欢迎大家加我微信互相探讨!\[在这里插入图片描述\](https://img-blog.csdnimg.cn/20210224103840569.png?x-oss-process=image/watermark,type\_ZmFuZ3poZW5naGVpdGk,shadow\_10,text\_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTU3NjE0Ng==,size\_16,color\_FFFFFF,t\_70)

#### 1.建立数据库（这里的环境Linux是Cent OS 8下的mysql—8.0.23） ####

##### 1.1使用CREATE DATABASE语句创建数据库 #####

语法格式：`CREATE DATABASE database_name`  
命令：`CREATE DATABASE name;`  
示例：

创建完毕，继续查看我们可以看到MySQL数据库‘danhuatang’创建成功；  
查看当前MySQL中存在的数据库：`SHOW DATABASES;`(语法不分大小写，在数据库的配置文件中进行配置）

将通用数据库名称定义为database_name
    将通用数据表名称定义为table_name

##### 1.2使用CREATE DATABASE IF NOT EXISTS语句创建数据库 #####

语法格式：`CREATE DATABASE IF NOT EXISTS database_name;`  
命令：

CREATE DATABASE IF NOT EXISTS name;

使用CREATE DATABASE IF NOT EXISTS database\_name时，如果MySQL不存在相关的数据库，则创建数据库；如果MySQL中已存在相关的数据库，则忽略创建语句，不再创建数据库

#### 2.查看数据库 ####

查询所有数据库：`show databases;`

查询当前正在使用的数据库名称： `select database();`

mysql> select database();
    +------------+
    | database() |
    +------------+
    | NULL       |
    +------------+
    1 row in set (0.00 sec)

删除数据库：`drop database 数据库名称;`

mysql> drop database xiaowuwu;
    Query OK, 0 rows affected (0.00 sec)

使用数据库：`use mysql;use name使用指定的数据库`

mysql> use mysql;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql>

##### 2.1查看数据库的创建信息 #####

语法格式：`show create database name;或show create database name \g`

mysql> show create database danhuatang;
    +------------+--------------------------------------------------------------------------------------------------------------------------------------+
    | Database   | Create Database                                                                                                                      |
    +------------+--------------------------------------------------------------------------------------------------------------------------------------+
    | danhuatang | CREATE DATABASE `danhuatang` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
    +------------+--------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> show create database danhuatang \g
    +------------+--------------------------------------------------------------------------------------------------------------------------------------+
    | Database   | Create Database                                                                                                                      |
    +------------+--------------------------------------------------------------------------------------------------------------------------------------+
    | danhuatang | CREATE DATABASE `danhuatang` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
    +------------+--------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

这里可以看到，创建名称为danhuatang的数据库时，使用的字符编码为utf8mb4,使用的校对规则为utf8mb4\_0900\_ai\_ci, DEFAULT ENCRYPTION='N’表示默认没有使用MySQL的加密技术

##### 2.2修改数据库名称 #####

注：在5.1.7版本中，语法:`RENAME DATABASE db_name TO new_db_name`此语法可能造成数据库丢失，所以在5.1.23这条语法删除了，但是可以通过其他方式表达到修改MySQL数据库名称的效果  
通过重命名数据表修改数据库名称  
首先在MySQL中创建数据库test\_0223,`create database if not exists test_0223;`  
并在test数据库中创建名称为table\_test的数据表`create table if not exists table_test(id int);`  
查看名称为test\_0223数据库下的数据表；

rename table test_0223.table_test to test_0224.table_test;

整体代码如下：

mysql> create database if not exists test_0223;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> use test_0223;
    Database changed
    mysql> create table if not exists table_test;
    ERROR 4028 (HY000): A table must have at least one visible column.
    mysql> create table if not exists table_test(id int);
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> show tables;
    +---------------------+
    | Tables_in_test_0223 |
    +---------------------+
    | table_test          |
    +---------------------+
    1 row in set (0.00 sec)
    
    mysql> create database if not exists test_0224;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    | test_0223          |
    | test_0224          |
    +--------------------+
    6 rows in set (0.00 sec)
    
    mysql> ^C
    mysql> rename table test_0223.table_test to test_0224.table_test;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    | test_0223          |
    | test_0224          |
    +--------------------+
    6 rows in set (0.01 sec)
    
    mysql> use test_0224
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> show tables;
    +---------------------+
    | Tables_in_test_0224 |
    +---------------------+
    | table_test          |
    +---------------------+
    1 row in set (0.00 sec)
    
    mysql> use test_0223;
    Database changed
    mysql> show tables;
    Empty set (0.00 sec)
    
    mysql>

可以看到，test\_0223数据库库下存在的table\_test数据表，说明table\_test数据表创建成功；  
在MySQL命令行创建test\_0224数据库  
重命名数据表，将test\_0223数据库下的数据表重命名到test\_0224数据库下

##### 2.3 通过导入/导出数据修改数据库名称 #####

本部分同样以修改test\_0223数据库为test\_0224数据库为例讲解，test\_0223数据库的创建与以上相同，不再赘述。  
通过导入/导出数据来修改数据库名称的步骤：

*  使用mysqldump命令，将名称为test\_0223的数据库导出到当前所在的目录，并将导出文件命名为test\_0223\_dump.sql

[root@localhost shanwue]# mysqldump -uroot -p test_0223>test_0223_dump.sql
    Enter password:

查看CentOS 8 服务器命令行当前所在目录的文件信息`ll`  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTU3NjE0Ng_size_16_color_FFFFFF_t_70]  
这里可以看到当前目录下多了一个test\_0223\_dump.sql文件，说明test\_0223数据库导出成功

*  登录MySQL，在MySQL命令行创建test\_0224数据库

[root@localhost shanwue]# mysql -uroot -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 13
    Server version: 8.0.23 MySQL Community Server - GPL
    
    Copyright (c) 2000, 2021, Oracle and/or its affiliates.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    | test_0223          |
    | test_0224          |
    +--------------------+
    6 rows in set (0.00 sec)

将test\_0223\_dump.sql文件中的数据导入test\_0224数据库中  
source /root/test\_0223\_dump.sql;  
这里直接打开到mysql的数据库的位置，执行

source name.sql

mysql> use test_0224;
    Database changed
    mysql> source test_0223_dump.sql;
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> drop database test_0223;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    | test_0224          |
    +--------------------+
    5 rows in set (0.00 sec)
    
    mysql>

##### 2.4通过创建数据表修改数据库名称 #####

*  登录mysql创建danhuatang数据库
 *  创建xiaohuagou数据表
 *  创建xiangrikui数据库
 *  在xiangrikui数据库中创建数据表，安装danghuatang数据库中的数据表创建
 *  删除danhuatang数据库，并查看xiangrikui数据库中存在的数据表代码如下：

mysql> create database if not exists danhuatang;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> use danhuatang;
    Database changed
    mysql> 
    mysql> create table if not exists xiaohuagou(id int);
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> show tables;
    +----------------------+
    | Tables_in_danhuatang |
    +----------------------+
    | xiaohuagou           |
    +----------------------+
    1 row in set (0.01 sec)
    
    mysql> create database if not exists xiangrikui;
    Query OK, 1 row affected (0.01 sec)
    
    mysql> create table if not exists xiangrikui.xiaohuagou like danhuatang.xiaohuagou;
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> drop database danhuatang;
    Query OK, 1 row affected (0.01 sec)
    
    mysql> use xiangrikui;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> show tables;
    +----------------------+
    | Tables_in_xiangrikui |
    +----------------------+
    | xiaohuagou           |
    +----------------------+
    1 row in set (0.00 sec)
    
    mysql>

这里可以看到xiaohuagou数据表所在的数据库已经由原来的danhuatang变成了xiangrikui，达到了修改数据库名称的效果

#### 3.数据库编码 ####

查询数据库编码：`show variables like "char%";`  
在MySQL中，会为创建的每个数据库指定一个字符编码，如果在创建数据库时没有为指定字符编码，则MySQL会为数据库指定一个默认的字符编码，这个默认的字符编码在MySQL的配置文件my.cnf或my.ini中进行配置

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
    +--------------------------+--------------------+
    | Variable_name            | Value              |
    +--------------------------+--------------------+
    | character_set_client     | gbk                |
    | character_set_connection | gbk                |
    | character_set_database   | utf8mb4            |
    | character_set_filesystem | binary             |
    | character_set_results    | gbk                |
    | character_set_server     | utf8mb4            |
    | character_set_system     | utf8               |
    | collation_connection     | gbk_chinese_ci     |
    | collation_database       | utf8mb4_0900_ai_ci |
    | collation_server         | utf8mb4_0900_ai_ci |
    +--------------------------+--------------------+
    10 rows in set (0.00 sec)
    
    mysql> SET character_set_client =gbk;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SET character_set_connection=gbk ;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql>  set character_set_database=utf8mb4;
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql>  set character_set_filesystem=binary;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> set character_set_results=gbk;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> set character_set_system=utf8;
    ERROR 1238 (HY000): Variable 'character_set_system' is a read only variable
    mysql>  setnames gbk;
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'setnames gbk' at line 1
    mysql>  setnames gbk;
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'setnames gbk' at line 1

##### 3.1创建数据库时指定字符编码 #####

*  语法：`create database [if not exists] database_name_default character set character_name_ collate collate_name[default encryption='N'];`
 *  示例:在MySQL命令行创建名称为t\_cha的数据库，指定数据库的字符编码为UTF-8，校验规则为utf8\_unicode\_ci,不使用MySQL加密技术

mysql> create database if not exists t_chr default character set utf8 collate utf8_unicode_ci default encryption='n';
    Query OK, 1 row affected, 2 warnings (0.03 sec)

查看创建结果：

mysql> show create database t_chr \G
    *************************** 1. row ***************************
           Database: t_chr
    Create Database: CREATE DATABASE `t_chr` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
    1 row in set (0.00 sec)

查看数据库默认字符编码

查看编码规则：`show variables where Variable_name like 'COLLATION%'；`

#### 3.2修改数据库的字符编码 ####

语法：`ALTER DATABASE database_name CHARACTER SET character_name collate collate_name;`  
示例：将名称为t\_chr的数据库的字符编码修改为utf8mb4,校验规则修改为utf8mb4\_0900\_ai\_ci

mysql> alter database t_chr character set utf8mb4 collate utf8mb4_0900_ai_ci;
    Query OK, 1 row affected (0.02 sec)

继续查看该数据库的字符编码

mysql> show create database t_chr \g
    +----------+---------------------------------------------------------------------------------------------------------------------------------+
    | Database | Create Database                                                                                                                 |
    +----------+---------------------------------------------------------------------------------------------------------------------------------+
    | t_chr    | CREATE DATABASE `t_chr` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
    +----------+---------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.01 sec)

这里看到t\_chr数据库的字符编码为utf8mb4,校验规则为utf8mb4\_0900\_ai\_ci ，说明数据库的字符编码修改成功

##### 3.3删除数据库 #####

语法：`drop database if exists database_name;`（非空即删，否则忽略）  
简单模式：`drop database database_name;`（删除，数据库不存在则报错）

mysql> drop database if exists xiangrikui;
    Query OK, 1 row affected (0.01 sec)
    
    mysql> drop database if exists xiangrikui;
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> drop database xiangrikui;
    ERROR 1008 (HY000): Can't drop database 'xiangrikui'; database doesn't exist

##### 到这里数据库的基础知识结束欢迎大家加我微信互相探讨![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTU3NjE0Ng_size_16_color_FFFFFF_t_70 1] #####

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTU3NjE0Ng_size_16_color_FFFFFF_t_70]: /images/20221024/f3be675ef7ba4b3d89e4491d6db64a7f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTU3NjE0Ng_size_16_color_FFFFFF_t_70 1]: /images/20221024/791a8972e064449697aacf6ffcc923d8.png