CREATE DATABASE Syntax

17 January 2012 By Praveen Macherla 7,178 views No Comment
1 Flares Twitter 0 Facebook 1 Google+ 0 1 Flares ×

To create a database in MySQL, you use the CREATE DATABASE statement as follows:

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] …

create_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name

CREATE DATABASE statement will create the database with the given name you specified. IF NOT EXISTS is an optional part of the statement. The IF NOT EXISTS part prevents you from error if there is a database with the given name exists in the database catalog.

For example, to create inventory database, you just need to execute the CREATE DATABASE statement above as follows:

CREATE DATABASE inventory CHARACTER SET ‘utf8’;

After executing the statement, the MySQL will return you a message to indicate whether the data created successfully or not.

create_specification options specify database characteristics. The CHARACTER SET clause specifies the default database character set. The COLLATE clause specifies the default database collation.

A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set. Refer this link, for more info charsets and collations.

The MySQL server can support multiple character sets. To list the available character sets, use the SHOW CHARACTER SET statement.

Any given character set always has at least one collation. It may have several collations. To list the collations for a character set, use the SHOW COLLATION statement.

SHOW DATABASE statement shows all databases in your database server. You can use SHOW DATABASE statement to check the database you’ve created or to see all the databases’ name on the database server before you create a new database, for example:

SHOW DATABASES;

In our database server, the output is:

To select a database which you plan to work with, you can use ‘USE’ statement as follows:

USE database_name;

You can select our sample database by using the USE statement as follows:

USE inventory;

From now you can query the tables’ data and operate data inside the selected database.