MySQL 5: Basic Database Operations

The following command lines are used in Windows 64-bit operating systems. Before learning, Let's explain the meanings of some symbols in the MySQL Syntax: (1) brackets ([]) indicate that both exist or do not exist. They are optional parameters. For example: SHOW {DATABASES | SCHEMAS} [LIKEpattern | WHEREexpr ]; []

The following command lines are used in Windows 64-bit operating systems. Before learning, Let's explain the meanings of some symbols in the MySQL Syntax: (1) brackets ([]) indicate that both exist or do not exist. They are optional parameters. For example: SHOW {DATABASES | SCHEMAS} [LIKE 'pattern' | WHERE expr ]; []

The following command lines are used in Windows 64-bit operating systems.

Before learning, Let's explain the meanings of some symbols in the MySQL syntax format:

(1) brackets ([]) indicate that they both exist or do not exist. They are optional parameters. For example: SHOW {DATABASES | SCHEMAS} [LIKE

'Pattern' | WHERE expr]; The content in [] In this syntax format can be left empty.

(2) braces ({}) indicate that the command must appear and are required. For example: CREATE {DATABASE | SCHEMA} [if not exists]

Db_name [DEFAULT] character set [=] charset_name; data in {} In this syntax format is required.

(3) the vertical line (|) indicates either of the two before or after the vertical line. Example: Same as above.

1. Create a database

The syntax format of MySQL database creation is:

CREATE {DATABASE | SCHEMA} [if not exists] db_name [DEFAULT] character set [=]

Charset_name;

We will learn some of the parameters below.

Example (I have not specified the character encoding method here ):

Create database t1;

2. view the data table list on the current server

How do we list the databases of the MySQL database management system? The syntax for MySQL to view the list of data tables on the current server is as follows:

SHOW {DATABASES | SCHEMAS} [LIKE 'pattern' | WHERE expr];

Example:

Show databases;

After MySQL is successfully installed, four databases are provided by default, namely information_schema, mysql, cece_schema, and

Test. Database t1 is the database we just created. This shows that the command for creating a database is correct.

3. View warning information

Before viewing the warning information, let's look at an example of creating a database:

Create database t1;

Create database if not exists t1;

IF a table with the same name as the table to be created EXISTS, the current table will NOT be created.

Yes. This is the syntax of if not exists. NOT creating a table is NOT an error, but is one of the normal execution results of this statement.

So now we need to view the warning information. The syntax format is:

Show warnings;

4. view database information

We didn't specify the database encoding method when creating database t1. In the previous settings, we modified the default encoding of MySQL service.

Method, then the database t1 in the end is not the default UTF-8 encoding method? This requires us to view the basic information of the data.

Show create database t1;

If we specify the encoding method gbk during database creation.

Create database if not exists t2 character set gbk;

Show create database t2;

5. modify a database

If we want to modify some of the parameters after successfully creating a database, we need to modify the syntax format of the database:

ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT] character set [=] charset_name;

Example (modify the encoding format of the database t2 we just created to the default encoding format utg8 ):

Alter database t2 character set utf8;

Show database t2;

6. delete a database

Since there are operations to create a database, there must be a syntax format for deleting the database:

DROP {DATABASE | SCHEMA} [if exists] db_name;

Example:

Before deleting a database, check the database list on the server:

Show databases;

We will delete the created database t2 and view the database list again.

Drop database t2;

Show databases;

7. Access the database

The syntax format for entering a database in MySQL service is:

USE db_name;

Select the Mysql database to operate. After using this command, all Mysql commands are only for this database.

Example:

USE test;

This operation was mentioned as early as when we used MySQL. in MySQL learning 4: Windows 64-bit operating system, we used MySQL.

At the MySQL prompt in the blog, we verify which database is currently in. Next, we will use another method to verify the current data.

Which database is it?