If you’ve been looking into web hosting for a while, you’ve definitely encountered the term “MySQL” a few times, especially if you’re getting VPS hosting. That’s because MySQL is one of the most popular database systems on the market. But in spite of its popularity, many people still don’t know what MySQL is or how to use it.
That’s where we come in! By the time you finish reading this post, you’ll know everything there is to know about databases and how to create database using MySQL.
Let’s jump right in!
What is a database?
A database is a collection of data that’s stored in a single location for easy access and retrieval. Data can be in any format, whether video, audio, or text. If this sounds simple, it’s because most of us encounter databases all the time, whether we know it or not. When you group similar documents into a single folder on your laptop, you’re creating a sort of database.
What are the benefits of database hosting?
When you use a database server, you can store copious amounts of information in one place, and you can retrieve it quickly and easily. Google retrieves information as quickly as it does because it uses a database.
MySQL is a popular database software, and it’s an open source relational database management system. “Open source” means anybody can install, use, and modify the software, and “relational” means that the data is stored in tables that all relate to each other. (You’ll see what we mean in a second.)
How to use MySQL create database
To use MySQL create database, you can use this command:
CREATE DATABASE name
(Be sure to capitalize “CREATE DATABASE” exactly as you see it here, and in place of “name”, use whatever name you’ve decided to give to your database. The name of your database should be in lowercase letters.)
That’s all you need to do to MySQL create database. Once you finish this, you can start creating the tables you’ll use to store your data in the database.
1. Creating a table
Before you create a table in your MySQL create database, check to make sure you’re in the correct database. For example, let’s say our database is called “haircuts”. To check that you’re in the correct database, you’d use the USE command, like this:
You could also use the DATABASE command, like this:
Here’s the SQL command you’d use to create the haircuts table:
CREATE TABLE haircuts
(haircut VARCHAR(20) NOT NULL,
customerid INTEGER NOT NULL,
hairlengthinches INTEGER NOT NULL,
customerlastname VARCHAR(26) NOT NULL,
customerfirstname VARCHAR(26) NOT NULL,
dye VARCHAR(10) NULL);
As usual, since we’re using SQL, we use all caps for SQL keywords and lowercase letters for columns and tables that we name. The structure is this:
CREATE TABLE table_name
(attribute_name datatype options,
Attribute_name datatype options);
The “…” represents all the data you enter into the table. You can enter as much information as you like. Just be sure to separate each field with a comma, open and close with parentheses, and follow the last parenthesis with a semi-colon.
NULL values explained
In the options field of your CREATE statement, you can specify NULL or NOT NULL. This lets the database know whether NULL (or empty) values are allowed for that field. So, if you look at the create statement for our haircuts table, you’ll see that for the column called “dye”, whoever’s filling in the database is allowed to leave that section blank.
If you definitely want the person filling in the database to type either “yes” or “no”, you’ll write NOT NULL in your CREATE statement. In our table, however, the person filling in the database can either type “yes” or leave the slot blank.
What are attributes and datatypes?
In our example, the table name is “haircuts” and our attributes are haircut, customerid, customerlastname, customerfirstname, and dye.
The datatype indicates the type of information we’ll enter into each field. Since the hair length in inches is an integer number, we use the INTEGER datatype.
We don’t expect any customer to have a last name or first name longer than 26 letters, so we use the VARCHAR(26) datatype for these fields.
2. Getting information from multiple tables
When we use MySQL create database, we can minimize the need for storage space by getting information from multiple tables.
For instance, let’s say that we created another table that showed the addresses of our hair salon’s customers. We’d make an SQL that looks something like this:
CREATE TABLE customeraddress
(customerid INTEGER NOT NULL,
stateid INTEGER NOT NULL,
address VARCHAR(50) NOT NULL,
regionid VARCHAR(20) NOT NULL);
If we wanted to create a table that showed which customers live in which states, here’s the SQL code we’d use:
CREATE TABLE customerstates
(customerid INTEGER NOT NULL,
stateid INTEGER NOT NULL);
How to alter a table in the database
To add to our existing MySQL create database, we simply use the ALTER TABLE command. Here’s the syntax to add a field called “city” to our table called “customeraddress”:
ALTER TABLE customeraddress
ADD city VARCHAR(30) NULL;
You might be wondering why we’ve made it possible to include NULL values for this column. What if we don’t want this information to left blank?
Here’s the answer:
Remember that when we add a column to an existing table, all the information in the new column will necessarily be empty. So, we have to allow NULL values when we’re adding a new column to a table that already exists.
We’ve only just brushed the surface of using MySQL create database, but by now, we hope you see how easy and intuitive it is to learn the syntax.
If you liked this blog post, check out our other posts where we answer your tech questions, like “How does VPS hosting work?”