The CREATE DATABASE statement in MySQL is used to create a new database within the MySQL server. A database is a logical container for storing and organizing related data, such as tables, indexes, views, and other objects. This command is fundamental when setting up a new MySQL project or environment.

1. Basic Syntax

CREATE DATABASE database_name;

• database_name: The name of the database you want to create. The name should be unique within the MySQL server and should follow the naming conventions (usually alphanumeric characters, underscores, and it must not exceed 64 characters).

2. Example of Creating a Database

CREATE DATABASE company_db;

This command creates a new database named company_db in the MySQL server.

3. Checking Existing Databases

You can check the existing databases on your MySQL server using the following command:

SHOW DATABASES;

This will list all databases, including the one you just created.

4. Creating a Database with Character Set and Collation

You can specify a default character set and collation for the new database:

CREATE DATABASE company_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

• CHARACTER SET utf8mb4: Defines the default character set for the database, which determines how text data is stored.
• COLLATE utf8mb4_unicode_ci: Defines the collation, which determines how text data is compared and sorted.

5. Conditional Database Creation

To avoid errors when trying to create a database that might already exist, you can use the IF NOT EXISTS clause:

CREATE DATABASE IF NOT EXISTS company_db;

This command will only create the company_db database if it does not already exist.

6. Privileges and Access Control

Creating a database usually requires certain privileges. Typically, the MySQL user must have the CREATE privilege on the server to create a database.

GRANT ALL PRIVILEGES ON company_db.* TO 'user_name'@'localhost';

This command grants all privileges on the company_db database to a specific user.

7. Selecting a Database

After creating a database, you can select it for use in your session:

USE company_db;

This command sets company_db as the current database, so all subsequent operations (like creating tables, inserting data, etc.) will be performed within this database.

8. Dropping a Database

If you need to remove a database, you can use the DROP DATABASE command:

DROP DATABASE company_db;

This command deletes the company_db database and all its contents permanently. Use it with caution, as it cannot be undone.

9. Considerations

• Naming Conventions: Stick to a consistent naming convention for database names. Avoid using spaces, hyphens, or special characters. Use underscores to separate words.
• Security: Ensure that the MySQL user who creates the database has appropriate permissions and that unnecessary users do not have access.
• Backup: Always consider backing up your database periodically, especially before making major changes like dropping a database.

10. Best Practices

• Character Set and Collation: Define the character set and collation that best suit your application needs. UTF-8 (utf8mb4) is commonly used for international applications.
• Use IF NOT EXISTS: To prevent errors in scripts that might try to create a database multiple times, use the IF NOT EXISTS clause.
• Database Documentation: Keep documentation of each database created, including the purpose, structure, and character set used.