Creating a database from scratch: Part 2 - MySQL

• MySQL is one of the world's most widely used database management systems.
• It is easy to install and use and is usually free.
• Here's how to use it.

In our previous post, we took you through the basic concepts of a database and some vital considerations you need to make when doing so.

Here, we'll build on that introduction by setting up a basic MySQL database. Happy days!

Step 1: Plan your database

Do you know what data you want to collect? How many tables do you need? What is the purpose of this thing? What's the difference between null and zero?

If you can't answer any of those, we suggest you first take a look at part 1 of this series.

Step 2: Install MySQL

With your database planned out, it is time to get stuck in. For this particular guide, we'll use one of the most common database management systems, MySQL.

You can install MySQL on your local machine or use a web-based service that offers MySQL hosting, such as Amazon RDS, Google Cloud SQL, etc.

• A special note before we continue, since MySQL is based on the coding language SQL, pay particular attention to the provided code, especially the use of the ";" character. This is very important, as missing it will often be the main reason an SQL is not actioned. It is a straightforward thing to miss and an incredibly frustrating thing to try and spot when debugging your code if it isn't working.
• Relevant commands/SQL code is included between the ===== breaks throughout the text.
• Also, some web hosting MySQL DBMSs may require additional formatting like a "`" encapsulating the database name, column names, etc. So, instead of "SELECT * FROM users" you'll need to type "SELECT * FROM `users`"
• We've just saved you hours of pain; you are welcome!

Depending on your computer setup, you'll need to do one of the following:-

• Windows users

Download the MySQL installer from the official website: https://dev.mysql.com/downloads/installer/

Run the installer and follow the on-screen instructions to complete the installation.

• macOS users

You can use the Homebrew package manager to install MySQL. First, install Homebrew by following the instructions on their website: https://brew.sh/

Once you have installed Homebrew, you can run the following command to install MySQL:

==================================================================

brew install mysql

==================================================================

• Linux (Ubuntu/Debian)

Update your package index by running the following command:

==================================================================

sudo apt update

==================================================================

Install MySQL by running the following command:

==================================================================

sudo apt install mysql-server

==================================================================

• Linux (Red Hat/CentOS)

Add the MySQL repository to your system by running the following command:

==================================================================

sudo yum localinstall https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

==================================================================

Install MySQL by running the following command:

==================================================================

sudo yum install mysql-community-server

==================================================================

Once you have installed MySQL, you can start the MySQL server and complete the setup by running the mysql_secure_installation script. You can also start and stop the MySQL service using the appropriate command for your operating system, such as "systemctl start mysql" or "service mysql start."

Step 3: Connect to the MySQL server

Great, so we've got it all set up. Now "all" we need to do is flesh it out with tables and data. But how on Earth do you do that?

First, you'll need to ensure you can connect and "talk" to the database. This requires you to connect to the MySQL server using the MySQL command-line client and provide your MySQL credentials.

How you do this depends entirely on the platform you plan to connect to, store, and retrieve data from it. Some of the most common are as follows: -

Command Line Interface (CLI)

You can use the MySQL client command-line tool to connect to a MySQL server. Type the following command in your terminal:

==================================================================

mysql -u username -p

==================================================================

PHP Script:

If you are using PHP, you can use the MySQLi or PDO extension to connect to a MySQL server. The following code example connects to a MySQL server using the MySQLi extension:

==================================================================

$servername = "localhost";

$username = "username";

$password = "password";

// Create connection

$conn = mysqli_connect($servername, $username, $password);

// Check connection

if (!$conn) {

die("Connection failed: " . mysqli_connect_error());

}

echo "Connected successfully";

==================================================================

Note: Replace username, password, database_name, and the server URL in the examples above with the actual values for your MySQL server. You'll know it's working because you should be able to see the message "Connected successfully."

Java

You can use the JDBC (Java Database Connectivity) API to connect to a MySQL server. The following code example connects to a MySQL server using JDBC:

==================================================================

String url = "jdbc:mysql://localhost:3306/database_name";

String username = "username";

String password = "password";

Connection conn = DriverManager.getConnection(url, username, password);

==================================================================

Note: Replace username, password, database_name, and the server URL in the examples above with the actual values for your MySQL server.

Python

You can use the PyMySQL library to connect to a MySQL server. The following code example connects to a MySQL server using PyMySQL:

==================================================================

import pymysql

conn = pymysql.connect(

host='localhost',

user='username',

password='password',

database='database_name'

)

==================================================================

Note: Replace username, password, database_name, and the server URL in the examples above with the actual values for your MySQL server.

Step 4: Create a database

With your version of MySQL DBMS installed and you successfully connected to it, it is time to flesh out a database. But first, we'll need a database to save data too.

Doing this is very simple.

To create a MySQL database on different platforms, you can follow these steps:

Windows

Through the MySQL Community Server, launch the MySQL Command Line Client and log in with your MySQL root user credentials. Then, run the following command to create a database:

==================================================================

CREATE DATABASE database_name;

==================================================================

Note: Replace "database_name" with the desired name of your database in each of the above steps."

MacOS

Through the MySQL Community Server, launch the MySQL Command Line Client and log in with your MySQL root user credentials. Then, run the following command to create a database:

==================================================================

CREATE DATABASE database_name;

==================================================================

Note: Replace "database_name" with the desired name of your database in each of the above steps."

Linux

Install the MySQL server using the package manager of your Linux distribution (e.g., apt-get install mysql-server on Debian-based systems). Then, launch the terminal and log in to the MySQL server using the root user credentials.

Next, run the following command to create a database.

==================================================================

CREATE DATABASE database_name;

==================================================================

Note: Replace "database_name" with the desired name of your database in each of the above steps."

Step 5: Create tables

With the database now set up, we can populate it with some tables. After all, it isn't a database without any tables, right?

To create a table in MySQL on different platforms, you can use the following basic syntax:

==================================================================

CREATE TABLE table_name (

column1 data_type constraint,

column2 data_type constraint,

column3 data_type constraint,

);

==================================================================

In the above code, "table_name" is the name of the table, "column1," "column2," and "column3" are the names of the columns, "data_type" is the data type of the columns (e.g., INT (integer), VARCHAR (variable characters, e.g., alphanumeric), DATE (date), etc.), and "constraint" is an optional constraint on the column (e.g., NOT NULL, UNIQUE, etc.). Getting into the specifics of each data type is out of the scope of this article, but here is a valuable resource to find out more.

By way of example, the following SQL statement creates a users table with three columns (an "id" column, "username" column, and "password" column):

==================================================================

CREATE TABLE users (

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

username VARCHAR(50) NOT NULL UNIQUE,

password VARCHAR(50) NOT NULL);

==================================================================

This can be executed using a command line client, a GUI tool, or a programming language with a MySQL library installed. The exact steps may vary depending on the platform you are using.

Step 6: Insert data into tables

With our lovely tables now set up on our database, we can populate it with some data! Exciting.

Insert data into a table in MySQL using the "INSERT INTO" statement. The basic syntax is as follows:

==================================================================

INSERT INTO table_name (column1, column2, column3, ...)

VALUES (value1, value2, value3, ...);

==================================================================

You can run this statement using a MySQL client, such as the MySQL command line interface (CLI), or a graphical user interface (GUI), such as MySQL Workbench, both available for Windows, Mac, and Linux platforms.

For example, let's say you have a table named "users" with columns "id," "username," and "email," you can insert a new row into the table like this:

==================================================================

INSERT INTO users (id, username, email)

VALUES (1, 'johndoe', '[email protected]');

==================================================================

Note that you don't have to specify values for all columns in the table as long as you have set default values for the missing columns in the table definition.

As well as inserting data into a table, you can delete, update, or otherwise manipulate it using other special SQL commands. For more information on doing that, check out this in-depth guide.

Step 7: Query the database

You can use the SELECT statement to query data from a table in MySQL.

The basic syntax is as follows:

==================================================================

SELECT column1, column2, column3, ...

FROM table_name;

==================================================================

This statement retrieves data from all rows and columns of the specified table. You can also use the WHERE clause to filter the results based on specific conditions:

==================================================================

SELECT column1, column2, column3, ...

FROM table_name

WHERE condition;

==================================================================

You can run these statements using a MySQL client, such as the MySQL command line interface (CLI), or a graphical user interface (GUI), such as MySQL Workbench, both available for Windows, Mac, and Linux platforms.

For example, if you have a table named "users" with columns "id," "username," and "email," you can query all rows from the table like this:

==================================================================

SELECT * FROM users;"

==================================================================

Or you can query specific rows that meet certain conditions, such as all rows where the username is equal to 'johndoe' (note the single quotation marks, this tells SQL this is a string):

==================================================================

SELECT * FROM users

WHERE username = 'johndoe';

==================================================================

And that is your lot for today.

These are the basic steps to create a database in MySQL, but you can also perform other tasks such as modifying tables, adding constraints, defining relationships between tables, etc.

We'll look at some of them in the next episode.