3、数据库操作

查询数据库Retrieve

show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

创建数据库Create

• 创建数据库

  CREATE {DATABASE} [IF NOT EXISTS] db-name [DEFAULT] CHARACTER SET [=] charset_name;

• 查看数据库创建的详情

  show create database db-name;

• 查看执行的错误信息

  show warnings;

mysql> create database d1;
Query OK, 1 row affected (0.02 sec)

mysql> create database d1;
ERROR 1007 (HY000): Can't create database 'd1'; database exists
mysql> show warnings;
+-------+------+---------------------------------------------+
| Level | Code | Message                                     |
+-------+------+---------------------------------------------+
| Error | 1007 | Can't create database 'd1'; database exists |
+-------+------+---------------------------------------------+
1 row in set (0.00 sec)

mysql> create database if not exists d1;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+---------------------------------------------+
| Level | Code | Message                                     |
+-------+------+---------------------------------------------+
| Note  | 1007 | Can't create database 'd1'; database exists |
+-------+------+---------------------------------------------+
1 row in set (0.00 sec)

mysql> create database d2 character set = gbk;
Query OK, 1 row affected (0.05 sec)

mysql> show create database d1;
+----------+-------------------------------------------------------------------------------------------+
| Database | Create Database                                                                           |
+----------+-------------------------------------------------------------------------------------------+
| d1       | CREATE DATABASE `d1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ |
+----------+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create database d2;
+----------+------------------------------------------------------------+
| Database | Create Database                                            |
+----------+------------------------------------------------------------+
| d2       | CREATE DATABASE `d2` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+------------------------------------------------------------+
1 row in set (0.00 sec)

修改数据库Update

• 修改数据库编码

  ALTER {DATABASE} db-name [DEFAULT] CHARACTER SET [=] charset_name;

mysql> alter database d1 character set = gbk;
Query OK, 1 row affected (0.04 sec)

mysql> show create database d1;
+----------+------------------------------------------------------------+
| Database | Create Database                                            |
+----------+------------------------------------------------------------+
| d1       | CREATE DATABASE `d1` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+------------------------------------------------------------+
1 row in set (0.00 sec)

删除数据库Delete

DROP {DATABASE | SCHEMA} [IF EXISTS] db-name;

mysql> drop database d1;
Query OK, 0 rows affected (0.07 sec)

mysql> drop database d2;
Query OK, 0 rows affected (0.04 sec)

mysql> drop database if exists d2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
Empty set (0.00 sec)

选择数据库

• 打开数据库

  use db-name；

• 查看当前所在数据库

  select database();

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

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> select database();
+------------+
| database() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)

mysql> use sys;
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> select database();
+------------+
| database() |
+------------+
| sys        |
+------------+
1 row in set (0.00 sec)