MySQL remains one of the most popular and easiest methods of database creation and database management. You can create a new MySQL database by using the MySQL Create Database command. From there, you can start to add tables and data.

A database is the foundational structure of MySQL. All data in MySQL is stored in tables, and tables, in turn, are stored in databases. Creating, modifying, and deleting your databases is the first step toward understanding how MySQL works.

Person in front of computer in office

Create database MySQL

The easiest way to create a MySQL database is to first log into MySQL. Once you are at the MySQL prompt, use the CREATE command. Today, we’re going to be creating a database called “test_database” that includes a table called “test_users.”

CREATE DATABASE test_database;
SQL for Data Analysis: Beginner MySQL Business Intelligence

Last Updated August 2022

Bestseller
  • 103 lectures
  • All Levels
4.7 (6,456)

Learn SQL database querying w/ MySQL Workbench. Perfect for SQL beginners and first-time coders. | By Maven Analytics, John Pauler

Explore Course

We have now created an empty database. This database won’t have anything in it. For the database to be populated, we need to add tables. Before we can even do that, we need to use the database:

USE test_database;

USE is the command that MySQL uses to swap between databases. You can only use a single database at a time.

If you get an error when you try to create a database, then you don’t have permission to create databases on the server. You may need to go back and run MySQL as a root user or use the root user to grant yourself the permission to create databases.

Add users to a MySQL database

Once you have created a MySQL database, it’s usually a good idea to create a user for it. You can create a user like so:

CREATE USER ‘test_database_admin’@’localhost’ IDENTIFIED BY ‘test_password’;

This will create a user named “test_database_admin” identified by “test_password.” Now, grant that user permissions:

GRANT ALL PRIVILEGES ON test_database TO ‘test_database_admin’@’localhost’;

Why do we do this? Well, you don’t want to just use your root user to access all your databases; you want to separate them for security purposes. Now, log into the test_database_admin user, and you’ll only be able to access the test_database.

Restricting permissions in this fashion is what ensures that your security is as strict as possible. You can show all your users (and delete unnecessary or unused ones) using the MySQL list all users function.

Top courses in MySQL

Make your first MySQL table

A database is empty (and useless) until you’ve created tables. Today, we’re going to create a basic table:

CREATE TABLE users (primary key(ID) auto_increment, name varchar(50));

This is a pretty simple table. It has an ID, which automatically increments. It also has a name, which is a variable length character up to 50.

Delete a MySQL database

Let’s say that you didn’t want to create that database at all. You want to delete it now. You would then use the command:

DROP DATABASE test_database;

Now, that’s pretty dangerous if you haven’t backed everything up. However, it’s pretty common to repeatedly drop databases and then reconstruct them during development, usually with a SQL DUMP file that can be uploaded again and again. A SQL DUMP file can store the entire schema of a database, essentially “resetting” the database.

Show databases in a MySQL installation

What if you want to see all databases in a single MySQL installation? You’re in luck: It’s pretty simple. Just enter in:

SHOW DATABASES;

The above command is going to show you all the databases that are on the server. You shouldn’t keep any databases that you aren’t using; back them up and then delete them. If they are left there idle, they can be resource drains and potential areas of vulnerability.

MySQL Workbench vs. the MySQL Command Line 

MySQL does provide a GUI. If you’d rather work in MySQL Workbench instead of the MySQL Command Line, you’ll find that it’s even easier to create a new database.

To create a new database in MySQL Workbench, just go to “File -> Create Schema.” From there, you can begin building your new database.

MySQL Workbench isn’t necessarily faster than MySQL. It just gives you a visual interface through which you can administer all your data. The command line is still a faster, more direct method of manipulating your database information.

Tips for creating new databases in MySQL

A new database is usually created for every application that you develop. The database of an application can be an application’s most vulnerable component. There are a few things you should consider.

Follow the above best practices to make sure your databases are protected and responsive.

Conclusion

Now you know how to create a database or new schema in MySQL. In nearly every SQL environment, the process will be similar. The database itself is fairly easy to create, but you’ll need to better understand the schema to create the tables.

Creating a database is the first step toward learning more about how to create and administer databases in MySQL. MySQL is an exceptionally valuable tool — and one that touches almost every industry.
However, creating a database is less about knowing which commands to use and more about properly structuring your databases, such as the MySQL List All Users function. This is something that will become natural as you explore new SQL projects, tutorials, and lessons. Continue learning with MySQL Courses.

Page Last Updated: April 2022