The Database in MySQL is like a container, which contains various objects. For example, data tables, views, stored procedures, and triggers. Among them, the Table is the main object for storing data. The relationship between them is shown in the figure below:

This chapter mainly introduces the creation, viewing, selection and deletion of databases, including the use of mysql command line and MySQL Workbench graphical tools.

3.1 manage database through mysql command line

You need to create a table before storing data, and you need to create a database before creating a table. First, log in to the database using the MySQL command line client, click Start Menu - > MySQL - > MySQL 8.0 Command Line Client, and enter the root user password:

3.1.1 create database

Create a new database using the CREATE DATABASE statement in MySQL:

CREATE DATABASE [IF NOT EXISTS] dbname;

Where dbname specifies the name of the new database; IF NOT EXISTS is an option. If the created database already exists, you can use this option to avoid prompting error messages; Database names must be unique.

For example, the following statement is used to create a database named hrdb:

mysql> CREATE DATABASE hrdb;
Query OK, 1 row affected (0.27 sec)

So, how to view the database we created? MySQL provides the SHOW DATABASES command (case insensitive) to list all databases in the system:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| hrdb               |
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
7 rows in set (0.03 sec)

Among them, information_schema,mysql,performance_schema and sys are MySQL system databases. sakila and world are the sample databases we installed. hrdb is the newly created database. Obviously, a MySQL instance service can manage multiple databases.

Database and Schema in MySQL are the same concepts, so you can also use the SHOW SCHEMAS command to view the database list.

3.1.2 character set and sorting rules

When creating a database, you can also specify some options, such as Character Set and Collation.

Different character sets support different types and quantities of characters. For example, ASCII character sets can only store letters, numbers and common symbols, GB2312 and GB18030 can support Chinese, and Unicode character sets can support multiple languages. Sorting rules define the sorting order of characters, such as whether they are case sensitive, whether they are accent sensitive, and whether Chinese is sorted according to Pinyin or partial side.

Therefore, the more complete CREATE DATABASE statement is as follows:

CREATE DATABASE [IF NOT EXISTS] dbname
[CHARACTER SET charset_name]
[COLLATE collation_name];

The CHARACTER SET option is used to specify the CHARACTER SET. If it is not specified, it defaults to utf8mb4; The COLLATE option is used to specify the collation. The default collation of the utf8mb4 CHARACTER SET is utf8mb4_0900_ai_ci, which means accent and case insensitive.

We use the GBK character set to create a database testdb:

mysql> create database testdb
    -> character set gbk;
Query OK, 1 row affected (0.14 sec)

Then use the following command to view the statement that created the testdb database:

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

Use the SHOW CHARACTER SET command to view all character sets supported by the system, and use the SHOW COLLATION command to view the collation supported by the character set.

3.1.3 select database

Before accessing tables or other objects, we must tell MySQL which database to USE. Objects with the same name can exist in different databases. MySQL uses the USE command to specify a working database:

USE dbname;

We set hrdb as the working database:

mysql> USE hrdb;
Database changed

After that, we will perform various operations such as creating tables and querying data in the database; Unless you execute the USE command again to change the working database.

3.1.4 delete database

Deleting a database means that all other objects in the database are deleted at the same time and cannot be recovered; Therefore, you need to be very careful before implementation!

MySQL uses the DROP DATABASE statement to delete a database:

DROP DATABASE [IF EXISTS] dbname;

Where dbname specifies the database to delete. IF EXISTS is an option. If the deleted database does not exist, you can use this option to avoid prompting error messages.

We delete the hrdb and testdb databases:

mysql> drop database hrdb;
Query OK, 0 rows affected (0.17 sec)

mysql> drop database testdb;
Query OK, 0 row affected (0.23 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
6 rows in set (0.00 sec)

Looking at the database list again, it shows that hrdb and testdb do not exist.

The database and Schema in MySQL are the same concepts, so you can also use the DROP SCHEMA command to delete the database.

3.2 using Workbench to manage database

MySQL Workbench provides a graphical operation mode, which is easier to use.

3.2.1 creating database

After connecting to the database using MySQL Workbench, all databases are listed on the left by default. Click the "create a new schema in the connected server" icon below the toolbar:

The following window is displayed:

Enter the name of the database and set an optional character set and collation. Click the "Apply" button:

The interface displays the command to create a database, which is convenient for learning SQL statements. Click the "Apply" button again to complete the creation of the database. At this time, the newly created database is displayed on the left side of the interface:

3.2.2 select database

Select hrdb in the database list on the left, right-click and select "Set as Default Schema" to set it as the working database.

At this point, hrdb is set to highlight, meaning that it is the current working database.

3.2.2 delete database

Select hrdb in the database list on the left, right-click and select Drop Schema:

The confirmation page is displayed:

You can click "Review SQL" to view the corresponding deletion statement, or click "Drop Now" to directly delete the database. The deleted database is no longer displayed in the list on the left.

With the database, the next step is to create the data table.