This article uses examples to describe common basic operations of the MySQL database. Share with you for your reference, the details are as follows:

Related to this section:

• Create a database
• View Database
• Modify the database
• Deleting a Database

First release time: 2018-02-13 20:47

Revise:

1. 2018-04-07: Considering normalization, all "keywords" in the syntax are changed to uppercase; and because of the arrangement of "MySQL Learning Road", the character set and collation set issues are removed and put into a new blog post.

Create a database:

grammar :

CREATE DATABASE database name [database options];

Library option description:

• The library option is optional and can be omitted. If you do not consider globalization and localization (for example, considering Chinese compatibility), you can directly use the command without the library option.
• There are two library options: character set and collation set. And because the collation set is generally used with the character set, if you do not want to configure the collation in detail, you can ignore the collation set configuration.
• The character set of the database option is the character set used by the database to recognize or store data. Commonly used character sets are utf8 and gbk;
• The collation set of the library option is the collation set used by the database when collating data [data is collated according to the rules of the collation set, for example, some collation sets ignore case].
• If you do not specify library options when creating a database, the default library options will be used.

Example of use:

CREATE DATABASE school_info;
CREATE DATABASE mydatabase CHARSET utf8;

Additional notes:

• [It is not recommended to use this service. Don't make trouble for no reason. . 】The database name cannot use keywords or reserved words, such as database, delete. If the syntax is correct but the creation fails, it is likely that keywords or reserved words are used. If you must use keywords or reserved words, you need to use ` (the key above TAB) to wrap the database name, but you also need to add ` when deleting it.

• The database name is in Chinese and cannot be created:
  • The problem occurs because of the character set matching problem between the client and the server. For example, the server thinks that the character set from the client is UTF8 (Chinese characters are three bytes), while the character set of the client is gbk (Chinese characters are two bytes). The solution is to make the character set that the server accepts characters in the same character set as the client.
• Another problem is the display problem when the server receives Chinese and transmits it to the client. This requires that the character set used by the server when transmitting to the client is the same as that of the client [if the client is gbk and the server transmits utf8 Chinese, the client will display an error].
  • Modification method 1: set names The character set used by the client; [Execute set names utf8 to set the values ​​of the three parameters character_set_client, character_set_connection, and character_set_results to utf8]
  • Modification method 2: Set the character set of client and result one by one.

View the database:

• You can view the existing database or the creation statement of the database.
• The database creation statement is all the statements that the server uses to create this database (for example, the server will configure the character set of the database)

grammar:

-- View all databases SHOW DATABASES;

-- View the databases that meet the conditions SHOW DATABASES LIKE 'fuzzy matching';

-- View the database creation statement;
SHOW CREATE DATABASE database name;

Fuzzy matching:

Fuzzy matching uses wildcards to perform fuzzy searches.

• % represents matching multiple arbitrary characters, for example, %student can find: Astudent, ABCstudent, 456student
• _ represents matching a single arbitrary character, for example, _student can find: Astudent, Cstudent, 6student
• If _ or % is also a character contained in the database name, you need to use the escape character \, otherwise _ and % will be considered wildcards.
  • For example, if you search for database_student, you need to use database\_%;, otherwise you may find databases such as databasedemo without database_.

Example of use:

SHOW DATABASES;
SHOW DATABASES LIKE 'my%';
SHOW DATABASES LIKE 'my_';
SHOW CREATE DATABASE mydatabase;

Modify the database:

• Database modification can modify the database library options (character set and collation set)

grammar:

Alter database database name [library options];

Example of use:

ALTER DATABASE mydatabase CHARSET utf8;
ALTER DATABASE mydatabase CHARACTER SET utf8;
ALTER DATABASE mydatabase CHARACTER SET = utf8;

Additional notes:

• The collation set depends on the character set. Generally, when the character set is modified alone, the collation set will also change. When the collation set is modified alone, the character set will also change.
• If the changed collation depends on the same character set, the character set is not changed.
• The character set and collation set issues will be discussed in another blog post.

Delete the database:

Deleting a database means deleting the entire database along with all the data in it. Deleted data cannot be recovered. Deleting a database is risky, so be cautious when running away.

grammar:

DROP DATABASE database name;

Example of use:

-- Delete the database mydatabase
DROP DATABASE mydatabase;

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.