A database is a directory that contains all files which correspond to tables in the database. To create a new database in MySQL, use following syntax:

CREATE DATABASE [IF NOT EXISTS] database_name
[CHARACTER SET charset_name]
[COLLATE collation_name]

It will create a new database, where

  • database_name : Name of the database. The database name must be unique within the MySQL server instance.
  • charset_name : Character set name of the database. Default character set will be used if not specified.
  • collation_name : Collation name of the database. If you omit the COLLATE clauses, database will be have default collation.

MySQL supports various character sets. To get all available character sets in database server, use below statement

SHOW CHARACTER SET;

Few example of character set are latin1 and utf8.

A collation is a set of rules used to compare characters in a particular character set. Each character set in MySQL can have more than one collation. By convention, a collation for a character set begins with the character set name and ends with _ci (case insensitive) _cs (case sensitive) or _bin (binary). To get all collations for a given character set, use below statement

# Syntax
SHOW COLLATION LIKE 'character_set_name%';

# Get all collations for the latin1 character set
SHOW COLLATION LIKE 'latin1%';

Below statement create a database with name test_db

CREATE DATABASE testdb;

Show Databases

To display the existing database in the server, use the SHOW DATABASES command as follows:

SHOW DATABASES;

It will list all the databases present on the MySQL server. To get details of created database, you below command:

SHOW CREATE DATABASE testdb;

Select Database

A server can have multiple databases. To select and work with one database,

USE database_name;

Above statement will change the active database to database_name.