MySQL database basic operations: Create a database, view a database, modify a database, delete a database

This section relates to:

Create a database, view a database, modify a database, delete a database

Starting Time: 2018-02-13 20:47

Create DATABASE: Syntax:

Create  database name [ library option ];

Library Options Description:

• Library options are optional and can not be written
• The library option has two items: a character set and a proofing set.
• The character set for library options is the character set used by the database to identify or store data. Common character sets are UTF8 and GBK;
• The proofing set for library options is the proofing set that is used when the database is proofreading data, proofing the data according to the rules of the proofing set, such as some proofing sets ignoring case.

However, if you do not specify a library option when creating a database, the default specified library option will be used.

Add:

• View the default proofing set

 like ' character% '

• To view the default proofing set:

 like ' collation_% ';

Examples of Use:

Create Database Shool_info; Create Database mydatabase charset UTF8; Create database fordelete charset GBK;

Additional notes:

• Database names cannot use keywords or reserved words, such as database,delete, if the syntax is correct but the creation fails, it is likely that a keyword or reserved word is used. If you do not want to use keywords or reserved words, you need to use ' (tab above the key) to wrap the database name, but delete the time also need to add '.

• The database name is Chinese and the problem cannot be created:
  • The problem occurs because the client has a character set matching problem with the server, such as the UTF8 (three bytes of kanji) that the server considers the client, and the client's character set is GBK (two bytes of kanji). The workaround is to make the character set accepted by the service side the same as the character set used by the client.
• Another problem is that the server receives the Chinese after the transmission to the client display problem, this need to make the server to the client when transmitting to the same character set as the client "client is GBK, and the service is transmitted UTF8 Chinese, then the client will display the error."
  • Modify 1:set names the character set used by the client; " execute set names UTF8 is character_set_client, character_set_connection, character_set_ Results These 3 parameter values are set to UTF8"
  • Modify Method 2: Set the character set of the client and result individually.

Additional notes:

The meaning of several character_set:

• The client--server considers the character set used by the client
• connection--connection uses the encoding used for MySQL internal "placeholder for clear use meaning, if future figuring out what to add"
• database--the character set used by the current database
• filesystem--File System
• Results--The character set of the result sent to the client by the server
• Server-the character set used by the service side, if no character set is specified when the database is created, then the character set currently used by the service side is used
• system--Database System
• Directories stored by the dir--character set

To view the database:

The view of the database can view the existing database, or you can view the database creation statement.

Grammar:

-- View all Databases show databases; -- View the database for the specified section  like ' Fuzzy matching ' ; -- View the database creation statement; Create  database name;

Wildcard "wildcard character can be used to blur matching characters":

Show database like ' Fuzzy match ':

% means matches multiple arbitrary characters,

_ Represents a single arbitrary character match

If _ or% is also a character contained in the database name, you need to use the escape character \, otherwise it will be determined _ and% is a wildcard

If you are looking for database_student, you will need to use database\_%, otherwise you may find a database without database_ such as Databasedemo.

Examples of Use:

Databaselike'my%'database   like ' My_ '  Createdatabase MyDatabase;

To modify a database:

Database modifications can modify library options (character sets and proofing sets) for the database

Grammar:

Alter Database [ Library Options ];

Examples of Use:

-- modifying the character set of a database Alter Database mydatabase charset GBK; Alter Database character Set GBK; Alter Database character Set = GBK; -- Modifying the proofing set for a database Alter database MyDatabase collate utf8_bin;

Additional notes:

The collation set depends on the character set, and the collation set changes when the character set is modified individually, and the collation set character set is also changed.

If you change the collation set that relies on the same character set, the character set does not change.

Delete database: syntax:

Drop  database name;

Examples of Use:

-- Delete Database what Drop database what;

Additional notes:

MySQL database basic operations: Create a database, view a database, modify a database, delete a database