In mysql, a set of tables is called a database. The MySQL server manages several databases. There can be several tables under each database. Draw a diagram as follows:

 

Display database

When we just installed MySQL, many databases and tables were built in it. We can use the following command to see which databases are available:

SHOW DATABASES;

The MySQL version installed on my own computer is 5.7.22. See which databases are built in this version:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql>

It can be seen that this version of MySQL has built four databases for us. These databases are used internally by mysql. If we want to use Mysql to store our own data, we first need to create a database of our own.

Create database

The syntax for creating a database is simple:

CREATE DATABASE Database name;

Let's actually operate:

mysql> CREATE DATABASE xiaohaizi;
Query OK, 1 row affected (0.00 sec)

mysql>

I use my name xiaohaizi as the database name. After the statement is written, press enter, and then prompt a Query OK, 1 row affected (0.00 sec), indicating that the database is created successfully. Then we use the SHOW DATABASES command to check which databases are available:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| xiaohaizi          |
+--------------------+
5 rows in set (0.00 sec)

mysql>

When you see the database we created, xiaohaizi is already in the list.

IF NOT EXISTS

If we use CREATE DATABASE to create a database when it already exists, an error will occur:

mysql> CREATE DATABASE xiaohaizi;
ERROR 1007 (HY000): Can't create database 'xiaohaizi'; database exists
mysql>

The execution result prompts an ERROR, which means that the database xiaohaizi already exists! Therefore, if we are not sure whether the database exists, we can use the following statement to create the database:

CREATE DATABASE IF NOT EXISTS Database name;

This command means that if the specified database does not exist, it will be created, otherwise nothing will be done. Let's try:

mysql> CREATE DATABASE IF NOT EXISTS xiaohaizi;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql>

You can see that the execution of the statement is successful, and the ERROR prompt is gone, but there is a warning in the result. This warning is just MySQL's kind reminder that the database xiaohaizi already exists.

Tip: the level of warning is lower than that of error. If warning is prompted after a statement is executed, it is only a kind prompt and does not affect the execution of the statement. After error is prompted, it means that the statement cannot be executed at all.

Switch current database

For each client connected to the MySQL server, there is a concept of the current database (also known as the default database). The tables we create will be placed in the current database by default. The command to switch the current database is also simple:

USE Database name;

Before introducing the basic operations of tables, we should switch the current database to the newly created database xiaohaizi:

mysql> USE xiaohaizi;
Database changed
mysql>

When you see the display of Database changed, it indicates that the current database has been successfully switched. It should be noted that after exiting the current client, that is, after you enter the exit or quit command, or directly close the current black box page, when you call mysql -h hostname - u username - p password again, it is equivalent to reopening a client. You need to call the statement of USE database name again to select the current database.

In fact, when we newly start the client to connect to the server, we can specify the current database of the client after the connection is successfully established. Just write the database name behind the command mysql -h hostname - u username - p password of the start client, as follows:

mysql -h localhost -u root -p123456 xiaohaizi

Delete database

If we think a database is useless, we can delete it. The syntax is as follows:

DROP DATABASE Database name;

In a real working environment, before deleting the database, you need to take the thermometer to check whether you have a high fever, and then find at least two people to verify whether you have a fever, and then you dare to execute the command to delete the database. Deleting the database means that all the tables inside are deleted, which means that your data is gone, so it is an extremely dangerous operation, and you need to be extremely careful when using it. However, this is a learning environment, and we have just created the xiaohaizi database. We haven't put any tables in it. Delete them:

mysql> DROP DATABASE xiaohaizi;
Query OK, 0 rows affected (0.01 sec)

mysql>

Then take a look at what databases are available:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql>

You can see that the xiaohaizi database we created earlier is not available.

IF EXISTS

If a database does not exist, we still call the DROP DATABASE statement to delete it, but an error will be reported:

mysql> DROP DATABASE xiaohaizi;
ERROR 1008 (HY000): Can't drop database 'xiaohaizi'; database doesn't exist
mysql>

If you want to avoid this error, you can use this form of statement to delete the database:

DROP DATABASE IF EXISTS Database name;

Delete xiaohaizi again:

mysql> DROP DATABASE IF EXISTS xiaohaizi;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

It won't be wrong this time! After demonstrating the process of deleting the database, we'd better create the xiaohaizi database and switch to the current database. After all, we have to create various tables in this database in the future