Basic mysql operations: Create, view, modify, and delete Databases

Basic mysql operations: Create, view, modify, and delete Databases

Related to this section:

Create databases, view databases, modify databases, and delete Databases

 

Start Time:

Create a database: Syntax:

Create database name [database options];

 

Library option description:

• The database option is optional and can be left empty
• There are two library options: Character Set and verification set.
• The character set of the library option is the character set used by the database to identify or store data. Common Character sets include utf8 and gbk;
• The checking set of the database option is the checking set used when the database proofreads data. [checking data is based on the rules of the checking set. For example, some checking sets ignore case sensitivity ].

However, if you do not specify the database option when creating a database, the default database option is used.

Supplement:

• View default Checklist

show variabls like 'character%'

• View the default verification set:

show variables like 'collation_%';

 

Example:

create database shool_info;create database mydatabase charset utf8;create database fordelete charset gbk;

 

Note:

• The database name cannot use keywords or reserved words, such as database or delete. If the syntax is correct but creation fails, it is likely that the keywords or reserved words are used. If you want to use keywords or reserved words, you need to use '(the key on the TAB) to wrap the database name, but you also need to add' When deleting it '.

• The database name cannot be created because it is in Chinese:
  • The problem occurs because of the character set matching problem between the client and the server. For example, the server considers UTF8 from the client (three Chinese characters), while the client character set is gbk (two Chinese characters ). The solution is to make the Server Accept the same character set as the character set used by the client.
• Another problem is that the server receives Chinese characters and transmits them to the client for display. This requires the server to use the same character set as the client when transmitting the data to the Client [the client is gbk, if the server transmits utf8 in Chinese, the client will display an error ].
  • Method 1: set the character set used by the names client ;【To execute set names utf8, set the value of character_set_client, character_set_connection, and character_set_results to utf8.]
  • Method 2: Set the character set of client and result one by one.

 

Note: Meanings of character_set:

• Client-character set used by the server
• Connection-the encoding used by the connection. It is used inside mysql. [The placeholder indicates the usage significance. If you want to understand it in the future, add it]
• Database -- character set used by the current database
• Filesystem -- File System
• Results -- Character Set of the result sent by the server to the client
• Server -- character set used by the server. If no character set is specified during database creation, the current character set used by the server is used.
• System -- Database system
• Dir -- directory of Character Set Storage

Verification set:

The checking set is a data checking method.

The verification set must be declared before the data is inserted. Otherwise, the verification set cannot be modified after the data is inserted.

The verification set has three formats:

• _ Bin: Binary comparison method, which extracts binary bits and compares them one by one, case sensitive;
• _ Cs: Case Sensitive, case sensitive;
• _ Ci: It is case-insensitive and case-insensitive.

View the supported collation of the database:

Show collation;

View the database:

You can view the existing database or create statements of the database.

Syntax:

-- View all databases show databases; -- view the specified database show databases like 'fuzzy Match'; -- view the database creation statement; show create database name;

Wildcard character ]:

Show database like 'fuzzy Match:

% Indicates Matching Multiple Arbitrary characters,

_ Represents matching a single arbitrary character

If _ or % is also a character contained in the database name, use the Escape Character \. Otherwise, the values of _ and % are wildcards.

If you are looking for database_student, you need to use database \ _ %;. Otherwise, you may find databases without database _ such as databasedemo.

Example:

show database;show database like 'my%';show database like 'my_';show create database mydatabase;

 

Modify database:

Database modification allows you to modify Database options (character set and verification set)

Syntax:

Alter database name [database options];

Example:

-- Modify the character set Alter database mydatabase charset gbk; Alter database mydatabase character set = gbk; -- modify the database's checking set Alter database mydatabase collate utf8_bin;

Note:

The collation depends on the character set. Generally, the collation is changed when the character set is modified separately.

If the modified collation depends on the same character set, the character set is not changed.

 

Delete Database: Syntax:

Drop database name;

Example:

-- Delete the database whatdrop database what;

 

Note: