MySQL Basics: How to Create a MySQL Database

What is SQL?

SQL, or Structured Query Language, is a language that helps you interact with databases. Using SQL, you can set up databases, tear them down, fill them up with data, create inter-database relationships, and much, much more.  

What are Databases?

Databases are virtual structures that hold data. They are very efficient for data storage and retrieval. When a computer needs certain information, it will send a question to the database. This question is called a query.

The database will search internally and give the computer the data that fits the query’s conditions.

Ever wonder how a website knows if your credentials are correct when you log in? It’s because when you created the account, the login credentials were stored in a database. Every time you log in, the server asks the database for your credentials and compares them to what you typed.

Databases are very versatile and give your server the to ability reference and store information. You can even set up your site to add or remove information in a database based on user input.

Database Components

Databases are made up of components. To be able to create your own database, you should learn these components so that you can create efficient databases.

What is a table?

A database is made up of tables. Tables, in turn, are made up of columns, rows, and fields, which we will talk about in a few moments.

Databases can hold multiple tables. So, to make the database efficient, each table should have its own purpose.

For example, if you made a table about employees, you would want only to include things relating to employees:

If you wanted to create a table about projects, you would only include things relevant to the project.

What is a row?

Rows (also called “records”)  represent a set of corresponding data items that span across categories (or columns).

What is a column?

Columns are the categories. They represent all of a single type of data.

What is a field?

Fields are what might you might call cells in non-database tables. A field is a single data entry.

What is a key?

A key is a unique category in a table. In a unique category, there can be no duplicate values in the fields. In the example above, firstName, lastName, and department cannot be unique categories because it is possible to have duplicate names and more than one person in each department.

However, employeeID is a unique category because each employee has his or her own ID number. There cannot be duplicates. This would be considered the key of the table.

There should not be more than one key, though. If there is, the database will take longer to search information, making it less efficient. If a table has more than one key, it is best practice to split the table in two, creating a table for each key.

How do I Create a Database?

Creating a Database with a pair Networks Account

First, log into the Account Control Center. In the left sidebar, you will see Databases. Click on it and a drop-down will appear. In the drop-down, click Create a New Database.

This is where you will create your first database. Name your database, preferably something that reflects the theme of the content that will be stored there. Once you do that, select an access level.

There are two options for access level: Local Only and Local/Remote.

Local Only will only accept connections from a server at pair Networks. This should not affect the performance of any pair Network web pages that rely on the database. Note that this does not necessarily refer to your local machine, only pair Network server connections.

Local/Remote will accept connections from anywhere.

Next is the Optimization Period. Optimization refers to freeing up space from deleted items. Why do we have to free up space from deleted items, you may ask. Well, this is because when items are deleted in a database, they aren’t completely cleaned out. Optimization will go through and completely clear out the old deleted content and free up space for new content.

Consequently, if you don’t update your database often, you won’t need optimization to run often. However, if you think you may be making frequent changes or you plan to attach the database to your website for user interaction, you may want to select a more frequent optimization period. And don’t worry, you can always change the frequency in Manage Your Databases.  

When you are finished filling out the content, click Add Database.

Adding a Database Password

Now that you have created a database, you should add a password to it. Passwords are randomly generated when the database is created, so you need to add your own to be able to access it.

You can do this by clicking Database in the left sidebar, then clicking Manage Your Databases in the drop-down. On the next page, click the highlighted name of your new database under the Database Name heading. Next, click Change Password in the top nav bar.

This will take you the page where you can change your password. Enter your new password and then confirm it. Click Change MySQL User Password when you are finished.

Now your password is changed! If you forget your password at any point, you can always come back and repeat this process. Just be sure to update the password in any scripts that use this database. If you don’t, the script won’t be able to connect and things that rely on the database will break.

Using MySQL

First, you need to install MySQL and make sure the server is running. If you set up a password, be sure to remember it!

Once you’ve done that, you need to connect. You can do this from your shell (terminal for Mac or command prompt for Windows).

Open your shell. If you are running MySQL on a remote machine, input the following:

mysql -h host -u user -p

Replace host with your hostname and user with your username for your hosting account.

If you are running MySQL on your local machine (you are running the MySQL server on the same computer you are using to log in), input the following into the shell:

mysql -u user -p 

Replace user with your username. Press Enter. It will then ask you for your password. Input it, and press Enter again.

Now you’re connected to MySQL. From here, type:

CREATE DATABASE name;

Replace name with your desired database name. Press Enter.

Using MySQL Workbench

MySQL workbench is good if you have a hosting service or MySQL server already set up, but don’t want to have to use your computer’s shell (terminal for Mac, command prompt for Windows).

First, install MySQL Workbench. Once it’s installed, open the application.

The welcome page will be the first page you see. Check under MySQL Connections. The MySQL Workbench will pre-populate your server information if it can access it. If it has done this, you will see a gray box under MySQL Connections. Click on it and make sure it is the right server.

If you do not see a pre-populated box, you will have to input your server information into MySQL Workbench manually.  To do this, click the plus button next to MySQL Connections. Fill in the information on the form that pops up.

• Name: This is the connection name. You can name it whatever you want.
• Connection Method: This the method used to establish the connection. It defaults to Standard (TCP/IP).
• Hostname: This the hostname of your server.
• Port: Port number used to establish a connection. It will either be 3306 or 3307 for pair accounts.
• Username: This is the name of the SQL server user. This may differ from your local machine’s username because this is the SQL server’s username. If you are connecting to a remote server, you should use the username for the remote server.
• Password: This is the user’s password. This may differ from your local machine’s password because this is the SQL server’s password. If you are connecting to a remote server, you should use the username for the remote server. Click Store in Keychain to input the password.
• Default Schema: MySQL refers to databases as “Schema.” You would use this if you already had a database set up and wanted it to be the default database. For now, leave this blank.

Once you have filled out the form, click OK. If there are any errors, you will receive a pop-up notification.

When the connection is established, the connection will be shown as a gray box under MySQL Connections.

Click on your server’s gray box. The new page is your “workbench” for this connection. From here, you can create a database.

To create a database, go to the top nav bar. There is an icon that looks like a barrel with a plus. Click on this icon.  

A “new_schema” will pop up. Here, you can name your database. You can also select a Default Collation, but we’ll leave that alone for now. When you’ve named your database, click Apply in the bottom right corner.

The next page is the generated code that will be sent to the database. This code serves as instructions for the SQL server so that it knows what you want it to do.

Your code should say:

CREATE SCHEMA ‘databaseName’ ;

Click Apply. The following page shows you how your SQL server reacts to the code. If there are any errors, they will appear here.

When your SQL script finishes without errors, you can click Close.

And that’s it! You’ve created your first database.You’re well on your way to creating databases that’ll open doors to new website features and uses. Whether you chose the ACC, MySQL, or MySQL Workbench, your database is now ready to move on to the next phase. Stay tuned for future blog posts outlining the next step in database creation. You’ll have a fully functioning database in no time!