Table of Contents

Preface:

Add

About Encoding

Delete

Inquire

Modify

---

Preface:

From the previous text, we can see that MySQL is currently the mainstream database. mysql is a client, mysqld is a network service, and mysqld is also a database service. For a database, it is a way to store and organize data, so we must learn how to operate the database? How should we add, delete, inquire, and modify for databases? What about backups after deletion?

Then follow the blogger’s footsteps and enter today’s topic!

---

Add

Add a database, which means creating a database. Now let’s log into the database first:

To access the system, use the command sudo mysql -uroot. Once inside, we can create a database using the following basic syntax:

In Chapter 1, we’ve already created a database. This is just a brief recap:

To display databases, use show databases. We created the database using the command create database db_name;.

Keywords are in uppercase, such as CREATE and IF NOT EXISTS:

Although we successfully created the database, a warning appeared because the database already exists. MySQL did not recreate it and only returned a warning.

Related to Character Encoding

In MySQL, there are two types of encoding: character sets and their validation rules. For example, in VS2022, we commonly use utf-8, occasionally utf-16 for storage purposes. The validation rule is a set of rules used to validate the character set. We will look into this later.

If we do not specify the character set and validation rules when creating a database, the system defaults to using utf8 as the character set and utf8_general_ci as the collation. To view available character sets and their corresponding collations, use show charset; and show collation;

We create a database with the character set utf8:

Create a database with the character set utf8 and validation rules of utf8_general_ci:

Since I’m using the Ubuntu environment, there are some warnings.

These are default configurations, and utf8mb4 is an extension of utf8, so there are some warnings, but they don’t affect functionality.

Let’s verify the impact of different validation rules:

All use the utf8 character set; this one uses the validation rule utf8_general_ci, which has the feature of being case-insensitive:

This uses utf8_bin, case-sensitive:

Insert corresponding data, and then perform a query:

Use select * from person to query all results of this table. This is test, using utf8_general_ci, which is case-insensitive.

For test2, which is case-sensitive, the query only finds one ‘b’, not ‘B’.

Regarding database sorting:

This is test1’s:

This is test2’s:

So the above covers the creation of a database and two types of rules within it.

---

Delete

Delete has some nuances. In the Database Basics section, we learned that creating a database essentially creates a directory:

Under the /var/lib/mysql directory, we can see some blue directories, where test1 and test2 are the databases we created. So, can we delete the database directly here?

Delete is definitely possible, but it’s strongly not recommended to use this method—it feels too amateurish.

The SQL statement used for deletion is DROP:

This will delete the database.

Regarding data backup, here’s a link for you to check out on your own:

Comprehensive Explanation: Database Backup and Data Recovery Methods with Examples (Full Backup, Differential Backup, Incremental Backup)

---

Search

When it comes to the search function, we can search within a database or within a table:

The SQL statements used are all of the form show …, such as show databases; show tables; and so on.

Similarly, we can also show the creation statements. However, unlike our usual programming where /**/ is a comment, here it indicates that if the MySQL version is greater than 4.0.1, then this statement will be executed. For example, CHARSET represents the character set stored as utf8mb3, and the use of ‘test1’ is to prevent the database name from being exactly a keyword.

In fact, when we modify the database’s character set, the utf8mb3 will change to what we modified.

---

Modify

Let’s take an example. First, we’ll modify the database’s character set to gbk:

Indeed, the modification is complete.

For modify operations, the corresponding SQL statement is alter.

Of course, we can also modify the character set or adjust the validation rules.

This part is left for students to try on their own.

---

Thank you for reading!