管理数据库

查看数据库

语法

SHOW DATABASES;

范例:查看数据库

-- 查看当前都有哪些数据库
(root@localhost) [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

创建数据库

语法

CREATE DATABASE|SCHEMA [IF NOT EXISTS] DB_NAME
CHARACTER SET character_name
COLLATE collate_name;

范例1:创建数据库

-- 创建库名为db1的数据库
(root@localhost) [(none)]> CREATE DATABASE db1;
Query OK, 1 row affected (0.01 sec)

-- 查看创建数据库的语法
(root@localhost) [(none)]> SHOW CREATE DATABASE db1;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| db1      | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.01 sec)

-- 查看在数据库目录下对应的文件
cat /data/mysql/data/db1/db.opt
default-character-set=utf8mb4
default-collation=utf8mb4_general_ci

-- 再次创建库名为db1的数据库，就会报错
(root@localhost) [(none)]> CREATE DATABASE db1;
-- ERROR 1007 (HY000): Can't create database 'db1'; database exists

-- 创建语句中添加一个判断
(root@localhost) [(none)]> CREATE DATABASE IF NOT EXISTS db1;
Query OK, 1 row affected, 1 warning (0.02 sec)

-- 查看警告信息
(root@localhost) [(none)]> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                   |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Error | 1064 | 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 'warings' at line 1 |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

范例2:创建数据库并指定字符集

-- 创建字符集为utf8的db2数据库
(root@localhost) [(none)]> CREATE DATABASE IF NOT EXISTS db2 CHARACTER SET utf8;
Query OK, 1 row affected (0.04 sec)

(root@localhost) [(none)]> SHOW CREATE DATABASE db2;
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| db2      | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)

cat /data/mysql/data/db2/db.opt
default-character-set=utf8
default-collation=utf8_general_ci

修改数据库

语法

ALTER DATABASE|SCHEMA [IF NOT EXISTS] DB_NAME CHARACTER SET character_name;

范例1:修改数据库

-- 将db2的字符集修改为utf8mb4
(root@localhost) [(none)]> ALTER DATABASE db2 CHARACTER SET = utf8mb4;
Query OK, 1 row affected (0.09 sec)

(root@localhost) [(none)]> SHOW CREATE DATABASE db2;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| db2      | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

删除数据库

语法

DROP DATABASE|SCHEMA [IF EXISTS] DB_NAME;

范例:

-- 删除db2数据库
(root@localhost) [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| db2                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

(root@localhost) [(none)]> DROP DATABASE db2;
Query OK, 0 rows affected (0.02 sec)

(root@localhost) [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

-- PS: 对应数据库的数据目录下有非mysql创建的文件，在删除整个数据库的时候，无法删除非mysql创建的文件
-- 范例：
-- step01: 在db1数据目录下创建一个beijing.log的文件
cd /data/mysql/data/db1/
echo "beijing" >beijing.log
chown mysql.mysql beijing.log
ll
total 8.0K
-rw-r----- 1 mysql mysql 67 Feb 24 16:19 db.opt
-rw-r--r-- 1 mysql mysql  8 Feb 24 19:12 beijing.log

-- step02：删除db1数据库，报无法删除；查看数据库还在，但在数据目录下只有无法删除的beijing.log文件
(root@localhost) [(none)]> drop database db1;
ERROR 1010 (HY000): Error dropping database (can't rmdir './db1/', errno: 17)
(root@localhost) [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

ll db1/
total 4.0K
-rw-r--r-- 1 mysql mysql 8 Feb 24 19:12 beijing.log

-- step03：删除beijing.log文件后，再删除db1数据库，没有报错
rm db1/beijing.log
rm: remove regular file ‘db1/beijing.log’? y
ll db1/
total 0

(root@localhost) [(none)]> drop database db1;
Query OK, 0 rows affected (0.03 sec)

(root@localhost) [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)