MySQL Learning 5: Basic Database operations

The following are used on the command line under the Windows 64-bit operating system.

Before studying, let's explain what some of the symbols in the MySQL syntax format mean:

(1) The brackets ([]) indicate that they exist or do not exist, and are optional parameters. Example:SHOW {DATABASES | SCHEMAS} [ like

' Pattern ' | where expr]; the contents of [] in this syntax format are not additive.

(2) curly braces ({}) indicate that a command must appear, which is a required parameter. For example:CREATE {DATABASE | SCHEMA} [IF not EXISTS]

db_name [DEFAULT] CHARACTER SET [=] charset_name; the data in {} In this syntax format is mandatory.

(3) Vertical bar (|) Indicates a vertical bar before and after the choice of both. For example: Ibid.

Create a database

The syntax format for MySQL database creation is:

CREATE {DATABASE | SCHEMA} [IF not EXISTS] db_name [DEFAULT] CHARACTER SET [=]

Charset_name;

Some of these parameters we will slowly learn in the following learning.

Example (I don't specify a character encoding here):

CREATE DATABASE t1;

Two view the list of data tables under the current server

How do we list the MySQL database management system database? MySQL view the syntax format for the list of data tables under the current server is:

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

Example:

SHOW DATABASES;

After MySQL successfully installs, the default comes with four databases, namely Information_schema, MySQL, Performance_schema, and

Test The database T1 is the database we just created, which means we have the correct command to create the database.

Three viewing warning messages

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

CREATE DATABASE t1;

CREATE DATABASE IF not EXISTS t1;

The addition of [if not EXISTS] means that if there is a table with the same name as the table to be created, the current table will not be created;

exists before it is created. This is the semantics of if not exists, and not creating a table is not an error, but one of the normal execution results of this statement.

So now we need to look at the warning message, using the syntax format:

SHOW WARNINGS;

Four viewing database information

We did not specify the encoding of the database during the creation of the database T1, and in this previous setting we modified the default encoding for the MySQL service

Way, then the database T1 is not the default UTF-8 encoding method? This requires that we can view the basic information of the data.

SHOW CREATE DATABASE t1;

If we specify the encoding as GBK in the process of creating the database.

CREATE DATABASE IF not EXISTS T2 CHARACTER SET GBK;

SHOW CREATE DATABASE T2;

Five modifying the database

If we want to modify some of these parameters after we have successfully created the database, we need to use the syntax format to modify the database:

ALTER {DATABASE | SCHEMA} [Db_name] [DEFAULT] CHARACTER SET [=] charset_name;

Example (Modify the encoding format for the database T2 we just created is the default encoding format UTG8):

ALTER DATABASE T2 CHARACTER SET UTF8;

SHOW DATABASE T2;

vi. Deleting a database

Since there is an operation to create a database, there must be a syntax format for deleting the database:

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;

Example:

Take a look at the list of databases on the server before deleting them:

SHOW DATABASES;

We will create the database T2 Delete and view the database list again

DROP DATABASE T2;

SHOW DATABASES;

Seven entering the database

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

Use db_name;

Select the MySQL database that you want to manipulate, and all MySQL commands are only for that database after you use the command.

Example:

Use test;

This operation as we have already said when using MySQL, in MySQL learning 4:windows 64-bit operating system using MySQL this

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

Which one is the library?

MySQL Learning 5: Basic Database operations