I talk a lot about server software and apps that require the use of MySQL databases. In many instances, the installation process of the software will create their own databases; however, there are rare occasions when you’re required to manually create the database before installing the software.
You could also install the likes of phpMyAdmin and make short shrift of adding the databases, but what if you want to set them up manually? In that case, you’ll want to know how to create a database and then populate it with tables. In this tutorial, I’ll show you how to do just that.
I’ll walk through the steps of how to create a database, add a table, and insert data into that table. This is basic stuff, but if you plan on working with MySQL, you’ll want to know how to do this.
I demonstrate this process on CentOS 7 with MariaDB installed on top of MySQL, though the process is the same on any server running standard MySQL. I assume you already have your MySQL server up and running and know the MySQL root user password or the credentials for an equally privileged MySQL user. With that information in hand, let’s get busy.
SEE: SDN and the data center: Deployment plans, business drivers, and preferred vendors (Tech Pro Research)
Logging into MySQL
You must log into MySQL before you can start creating a database. I’ll demonstrate using root as the privileged MySQL user. To log in, open a terminal window and issue the command:
mysql -u root -p
You’ll be prompted to enter the password for the MySQL root user. After a successful login, you’ll be presented with a prompt that looks like Figure A.
Creating a database
First, we must create the actual database to be used. We’ll create a database called staff. To do this, we issue the command (from the MySQL prompt):
CREATE DATABASE staff;
Once we’ve created the database, we have to switch to the newly created database before we can add to it. To do this, issue the command:
You should see the output shown in Figure B.
Creating a table
It’s time to create your first table within the database. For our example, we’ll create a table called editorial with the columns name, email, and ID. To do this, issue the command:
CREATE TABLE editorial (id INT, name VARCHAR(20), email VARCHAR(20));
Note: INT stands for integer (numbers) and VARCHAR stands for variable characters (numbers and alphanumeric). In the above command VARCHAR is limited to 20 characters; if you need more than that, you can increase the value, such as VARCHAR(50).
Once created, you should see Query OK, 0 rows affected. To see your table listed, issue the command:
The output for the above command is shown in Figure C.
Adding data to your table
Let’s inject some data into that table. We’ll add information for a fictional member of the editorial staff. The data to be added is:
- Name: Olivia
- ID: 01
- Email: email@example.com
The command to add this would be:
INSERT INTO editorial (id,name,email) VALUES(01,”Olivia”,”firstname.lastname@example.org”);
You can view the information added to the table with the command:
SELECT * FROM editorial;
The output to the above command is shown in Figure D.
You can keep entering data into your table with a similar command as you used above until the table is populated with the necessary data.
You’ve created a database, added a table, and injected data into the table. All that’s left is to exit MySQL with the command exit.
Your database is ready to use.