MySQL Create Database Statement

MySQL is an open-source relational database management system. MySQL is used for applications that range from small to enterprise-level solutions. Database and Schema are functionally the same in MySQL. To create a database, there are two ways: using the command-line interface and MySQL Workbench. You can also implement it by programming with the help of Python, PHP, and Node.js. In this article, you will learn in detail how to create a database in MySQL.

Table of Contents:

• Architecture of MySQL Database
• Methods to create a database in MySQL
  • Method 1: Using Command-Line Interface
  • Method 2: Using MySQL Workbench (GUI Method)
  • Method 3: Using script files in the Command-line Interface
• Alternative Ways to Create a Database in MySQL Workbench
• Naming Conventions and Limits While Creating a Database
• Common Issues When Creating a Database in MySQL
• Conclusion

Architecture of MySQL Database

MySQL uses a hierarchical structure to organize and create databases.

1. MySQL server: The server is the primary component that manages the instances. 
2. Database: A Database or Schema is the organizational structure that has data objects in it.
3. Tables: It is a collection of data that has rows and columns. 
4. Columns: Columns, or attributes, contain defined types and constraints. 
5. Rows: Rows or tuples will contain individual records of each field in a database. 

While creating a database, MySQL will form a namespace that contains tables and related database objects. 

Methods to create a database in MySQL

There are two major methods: the Command-line interface and MySQL Workbench. With the help of these, you can create a safe database. You can also create a database with the help of script files. 

Master MySQL – Build Your First Database Today!

Enroll now and transform your future!

Explore Program

Method 1: Using Command-Line Interface

The command-line interface method is the straightforward method that interacts with MySQL Server without any third-party interference. 

Let’s see how to create a database in MySQL:

In Windows: Download the MySQL software from the trusted official website.

Mac Os: using Mac Os Homebrew by the command “brew install mysql.”

Linux: install MySQL by using the package manager “apt-get” or “yum.”

After successful installation, initialize the MySQL service.

In Windows: Access Service Manager or execute “net start mysql.”

In Mac Os: Run “mysql.server start” in the terminal. 

In Linux: Run the “sudo systemctl start mysql” command.

Now, let’s start with creating the database. 

1. In Windows: Open the command prompt or PowerShell and type the following command. 

mysql -u root -p

In macOS/Linux: Open the terminal and run the command.

mysql -u root -p

Explanation: This command will connect your server to the MySQL server. 

To have additional connectivity.

-h hostname – This command will connect to the remote server. 

-P port – This command is used for non-standard port configuration.

–protocol=TCP – This command will establish a TCP/IP connection. 

Note: Always use the root user account for installation. 

Syntax for creating the database:

CREATE DATABASE database_name;

The database name can be changed based on the database name they set. 

Example: 

CREATE DATABASE sql_injection_test;

Note: End the statement always with a semicolon (;).

Verify whether the database is created or not by using the command.

SHOW DATABASES;

This command will display all the databases that are present in the server. Check that the database you have created is present. 

For example, like this. 

Explanation: Here, the database name sql_injection_test is displayed, which means the database was created successfully. 

Now, instead of using the recently created database, if you want to use any other database, you can use the same command with the database name you want to use on the server if it exists.

After creating and choosing the database, you have to enable the database by using the command: 

USE sql_injection_test;

This will enable the database for further usage and will terminate the connection only if you terminate it. 

To check the metadata of the database that was created, use the following command.

SELECT DATABASE(); 
SHOW CREATE DATABASE sql_injection_test;

To check that there is no error in the database, you can check it by creating a table.

Example: 

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Output:

Explanation: If you get this output, then the table is created successfully. 

Method 2: Using MySQL Workbench (GUI Method)

MySQL Workbench is a GUI tool (graphical user interface) that is developed by Oracle specifically to work with the MySQL database. It is a control panel or a user-friendly interface that helps you to create the database visually with icons and buttons without manually creating the table. 

Let’s see how to create a database in MySQL Workbench.

First, download the MySQL Workbench from the official website and install it according to your platform preferences. 

After downloading the MySQL Workbench, click the “+” that is beside the “MySQL Connections.”

After clicking the +, set up the connections.

Set the connection name, host name, username, port, and password. Click the test connection to test the compatibility of the server. Then click OK. 

From the navigator panel, click the schemas tab to create a new schema for the table you want. 

Enter the name of the schema that you want to create. For example, here, the schema name is TestDatabase123. Don’t change any default settings, and click Apply to save the details and move on with the next step. 

Click the schema, and you will see the database you have created. Right-click the schema and set the schema as your default schema by selecting the option “Set as Default Schema” to activate that schema. 

Expand the selected schema by clicking the arrow on the left to create the subordinate objects. 

Method 3: Using script files in the Command-line Interface

This method is used when the developer needs to create consistent schemas across the environment. The script file method is the safe and easy method, as it maintains consistency and automation. 

Let’s create a script file to work on:

First, open Notepad on your system and create a script (commands) to create a database with the file extension .sql and save it. 

Example:

CREATE DATABASE IF NOT EXISTS project_database
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
USE project_database;
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
GRANT SELECT, INSERT, UPDATE, DELETE ON project_database.* TO 'app_user'@'localhost';
FLUSH PRIVILEGES;

To execute the script in the system terminal, which is the command prompt, you can use these commands. 

mysql -u username -p < create_database.sql

In the command prompt environment, you can use 

source /path/to/create_database.sql

Alternative Ways to Create a Database in MySQL Workbench

There are some ways, like in various languages, that can be used to create an SQL database in MySQL Workbench. 

Using a Script File in MySQL Workbench

Open MySQL Workbench.

Click on the file icon in the top left corner. 

Go to “Open SQL Script” or “Ctrl+Shift+O” to open a script file.

It will ask you to select the script file. After selecting the file, it will be saved in the database. 

This is how you will save the script in MySQL Workbench. 

Click the lightning bolt icon in the server to run the script. 

Using Programming Languages to Create a MySQL Database

1. Using a PHP script file:

You can also create a database with the help of PHP scripts. 

<?php
$servername = "localhost";
$username = "root";
$password = "your_password";
$conn = new mysqli($servername, $username, $password);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$sql = "CREATE DATABASE online_store";
if ($conn->query($sql) === TRUE) {
    echo "Database created successfully";
} else {
    echo "Error creating database: " . $conn->error;
}
$conn->close();
?>

Explanation: This is the PHP script to create a database instead of using SQL queries. 

2. Using a Python Script file 

This Python code can be used to connect the localhost or any other cloud environment that is accessible to MySQL. 

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="your_password"
)
mycursor = mydb.cursor()
try:
    mycursor.execute("CREATE DATABASE analytics_platform")
    print("Database created successfully")
except mysql.connector.Error as err:
    print(f"Error: {err}")

3. Using Node.js Script file

This is the Node.js implementation to create and connect to the MySQL database. 

const mysql = require('mysql2');
// Connection configuration
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'your_password'
});
connection.query(
  'CREATE DATABASE user_management',
  function(err, results) {
    if (err) {
      console.error('Error creating database:', err);
      return;
    }
    console.log('Database created successfully');
  }
);
connection.end();

To encrypt the file that a developer has created can be done by using this command.

CREATE DATABASE secure_data
ENCRYPTION = 'Y';

This will protect the data inside the file from unauthorized access. This feature is available on MySQL Enterprise Edition, not on the community edition. 

For logging purposes, to check the access time and usage of the file, the command can be used. 

CREATE DATABASE global_application;
USE global_application;
SET time_zone = '+00:00';  

This command can be used in a file where the data keeps updating, which is for multiple objects to enter into the database. 

The auto-increment function can be used. 

USE product_catalog;
SET auto_increment_increment=2; 
SET auto_increment_offset=1;     

Naming Conventions and Limits While Creating a Database 

Some rules have to be followed before creating a database in a MySQL environment. 

Technical Limitations

• The maximum name length for a file should not exceed 64 characters.
• Only Alphabets, alphanumerics, underscores, and dollar signs can be used.
• Case sensitivity is based on the platform you chose. Like everything should be in lowercase or uppercase, or else a combination of both. 
• If you need a space in the database names, use backticks. For example (“ `EmployeeName` “).

Best Practices

• Always use lowercase if you are not sure about the platform you are using.
• Use an underscore whenever you need a space between two names.
• Do not use different name patterns for the application; try to use the same name for all.
• Make sure you are not using MySQL reserved keywords in the application. 
• Establish a secure connection by changing the permission setting in you permission management. 

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'strong_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON inventory_system.* TO 'app_user'@'localhost';
CREATE USER 'reports_user'@'localhost' IDENTIFIED BY 'different_password';
GRANT SELECT ON inventory_system.* TO 'reports_user'@'localhost';
FLUSH PRIVILEGES;

This query gives permission control only on the server specified by the developer.

Common Issues When Creating a Database in MySQL

1. The table already exists

If this message comes when you create a database, that means that a database already exists with the name of the database you are creating. So, make sure that you are creating a database that has a unique name that does not exist, or delete the existing database before creating it. 

2. Privilege Issues

When you need to edit or are trying to create a database or table, you don’t have access to it. Then use the command to create the proper privilege. (Make sure you do that from the root user account.)

GRANT CREATE ON *.* TO 'username'@'localhost';
FLUSH PRIVILEGES;

SQL Essentials: Learn the Basics for Free

Build a strong foundation in SQL and unlock endless data opportunities—at zero cost!

Explore Program

Conclusion

Creating a database is easier when you follow proper steps with permissions. The database can be created with the help of SQL queries, PHP scripts, and also with the help of Python scripts that can be used to connect the server with the MySQL database, or you can also use MySQL Workbench, where you can create a database by using the buttons from the visual interface. Understanding how to use these methods will let you create a database in MySQL. 

Take your skills to the next level by enrolling in the SQL Training Course today and gain hands-on experience. Also, prepare for job interviews with  SQL Interview Questions prepared by industry experts.

Check out other related SQL blogs-

How to Create a Database in MySQL? – FAQ