Database入门：Master the five basic operations of MySQL database and easily navigate the data world!

The language used for querying and modifying databases is called SQL (Structured Query Language, Structured Query Language). SQL is currently the widely used standard language for relational databases and is the basis for various database interaction methods.

In the previous article, we have mastered the basic concepts of SQL language and the commonly used DDL (Data Definition Language) and DML (Data Manipulation Language) statements. Next, we will explore how to apply these knowledge to the operation of MySQL database. In this article, we will introduce the basic methods of adding, deleting, modifying, and querying in detail.

First, let's review the naming rules for identifiers:

• Database names and table names must not exceed 30 characters, and variable names are limited to 29.

• They must only contain A-Z, a-z, 0-9, _ a total of 63 characters.

• Do not include spaces in the middle of object names such as database names, table names, and field names;

• Databases, tables, and field names cannot have the same name in the same MySQL software; tables cannot have the same name in the same database; fields cannot have the same name in the same table.

• It must be ensured that your fields do not conflict with reserved words, database systems, or common methods. If you insist on using them, please use them in SQL statements with ` (apostrophe) around them.

• Maintain consistency of field names and types: When naming fields and specifying data types, be sure to ensure consistency. If the data type is an integer in one table, it should not be changed to a character type in another table.

Next, let's look at the basic operations of the database.

1. Create a database

In MySQL, you can create a database using the CREATE DATABASE statement. Below, we introduce three methods of creating a database:
Method 1: Create a database (an error will be reported if the database name already exists)

CREATE DATABASE database_name;

Method 2: Create a database and specify the character set (an error will be reported if the database name already exists)

CREATE DATABASE database_name CHARACTER SET character_set;

('If the character set is not specified, the default character set will be chosen') 

Method 3: Determine if the database already exists, and create it if it does not exist (recommended)

CREATE DATABASE IF NOT EXISTS database_name;

Note:DATABASE cannot be renamed. Some visualization tools can be renamed, which involves creating a new database, copying all tables to the new database, and then deleting the old database.

Programming learning starts fromCloud Source ThinkingFrom now on, course videos, online books, online programming, one-on-one consulting...all the learning resources you want are here, and the best part is that they are all free!Click here to view

Example: The simplest statement to create a MySQL database
Create a database named test_db in MySQL. Enter the following command in the MySQL command-line client.

SQL statement CREATE DATABASE test_db;

You can create a database, and the input SQL statement and execution result are as follows.

In the

If you enter the CREATE DATABASE test_db; statement again, the system will give an error message, as shown below:

An error message is提示 that the database 'test_db' cannot be created, as the database already exists. MySQL does not allow two databases with the same name to be created in the same system.

You can add the IF NOT EXISTS clause to avoid similar errors, as shown below:

Secondly, view the database

In MySQL, you can use the SHOW DATABASES statement to view or display the databases within the current user's permission scope. The syntax format is as follows:

View all databases currently available

SHOW DATABASES; # The letter S represents multiple databases

View the current database being used

SELECT DATABASE(); # A global function used in mysql

View all tables under the specified database

SHOW TABLES FROM 数据库名;

View the creation information of the database

SHOW CREATE DATABASE 数据库名;
Or:
SHOW CREATE DATABASE 数据库名\G

Note: Before operating tables and data, it is necessary to specify which database the operation is for; otherwise, all objects must be prefixed with 'database_name.'.

Example 1: View all databases

List all databases that the current user can view：

It can be found that there are 6 databases in the above list, all of which are automatically created by the system when MySQL is installed, and their respective functions are as follows:

information_schema：It mainly stores some information about database objects in the system, such as user table information, column information, permission information, character set information, and partition information, etc.

mysql：The core database of MySQL, similar to the master table in SQL Server, mainly responsible for storing control and management information that MySQL itself needs to use, such as database users, user access permissions, and other information. Common examples include modifying the root user's password in the user table of the mysql database.

performance_schema:Mainly used for collecting database server performance parameters.

sakila:A sample database provided by MySQL, this database has a total of 16 tables, and these tables are relatively common. When designing a database, you can refer to these sample data tables to quickly complete the required data tables.

sys:After the installation of MySQL 5.7, an additional sys database will be added. The sys database mainly provides some views, with data all coming from performance_schema, mainly to make it easier for developers and users to view performance issues.

world:The world database is a database automatically created by MySQL, which includes only 3 tables, respectively saving city, country, and the languages used by the country.

Example 2: Create and View Database

First, create a database named test_db:

CREATE DATABASE test_db;

Query OK, 1 row affected (0.12 sec)

Then use the SHOW DATABASES statement to display all database names within the permission range, as shown below:

As you can see, the database created just now has been displayed.

3. Modify Database

In MySQL databases, you can only modify the character set and collation rules used by the database. These properties are stored in the db.opt file. Below, we will introduce the basic operations for modifying a database.

In MySQL, you can use ALTER DATABASE to modify the parameters of a database that has been created or exists. The syntax for modifying a database is:

The syntax is explained as follows:

• ALTER DATABASE is used to change the global properties of the database.

• Using ALTER DATABASE requires obtaining the database ALTER permission.

• The database name can be omitted, in which case the statement corresponds to the default database.

• The CHARACTER SET clause is used to change the default database character set.

4. Delete Database

When a database is no longer in use, it should be deleted to ensure that the storage space of the database contains valid data. Deleting a database involves removing the existing database from the disk space, and after deletion, all data in the database will also be deleted.

In MySQL, when you need to delete a database that has been created, you can use the DROP DATABASE statement. Its syntax format is:

DROP DATABASE [ IF EXISTS ] <Database Name>

The syntax is explained as follows:

• <Database Name>: Specifies the name of the database to be deleted.

• IF EXISTS: Used to prevent errors when the database does not exist.

• DROP DATABASE: Deletes all tables in the database and deletes the database itself.
  Be very careful when using this statement to avoid accidental deletion. To use DROP DATABASE, you need to have the database DROP permission.

Note:After MySQL is installed, the system will automatically create two system databases named information_schema and mysql. System databases store some information related to databases. If these two databases are deleted, MySQL will not work normally.

Something worth noting is that you must be cautious when performing deletion operations. After executing the DROP DATABASE command, MySQL will not provide any confirmation information. Moreover, after deleting a database, all tables and data stored in the database will also be deleted, and they cannot be restored. Therefore, it is best to back up the database before deleting it.

Example: Create a test database test_db_del in MySQL

Use the command-line tool to delete the database test_db_del from the database list. The input SQL statement and the execution result are as follows:

At this time, the database test_db_del does not exist. If you execute the same command again, directly using DROP DATABASE test_db_del, the system will report an error, as shown below:

If you use the IF EXISTS clause, you can prevent the system from reporting such errors, as shown below:

5. Select a Database

There are many system-builtin databases in MySQL. Therefore, before operating a database, it is necessary to determine which database it is. In MySQL, the USE statement is used to switch from one database to another.

After a database is created using the CREATE DATABASE statement, it will not automatically become the current database. You need to use USE to specify the current database. The syntax format is as follows:

USE <Database Name>

This statement can notify MySQL to use the database indicated by <Database Name> as the current database. This database remains as the default database until the end of the statement or until a different USE statement is encountered. Operations can only be performed on the database and its stored data objects after a USE statement is used to specify the current database.

Summary:

This article provides a detailed introduction to the operations of MySQL database, including creation, viewing, modification, deletion, and selection, hoping it can offer some help for your entry-level learning of databases.