How to getting started with MySQL – Complete guide

Are you new to MySQL and looking for a guide to start your MySQL journey? Then you are at the right article. In this article, we will discuss one of the most popular relational database management systems, MySQL, and provide you with a mysql complete guide. We will go over the installation guide along with a few basic queries to make you familiar with the language.

Introduction

MySQL is one of the most popular relational database management systems (RDBMS) based on the Structured Query Language (SQL). It is an open-source project. It provides a secure and robust platform for managing and accessing data stored in a relational database. Many applications like content management systems and discussion forums employ MySQL to store and manage their data. Many corporate and enterprise applications use MySQL. It is based on a client-server architecture and runs on various operating systems, including Windows, Linux, and macOS.

Benefits of Using MySQL

MySQL is a powerful and versatile database system that offers many advantages over physical records. It is easy to set up, use, and maintain, and highly scalable. It is also highly compatible with other programming languages, such as PHP, Java, and C++, which makes it an ideal choice for web-based applications. Additionally, it offers a range of features, such as transactions, triggers, stored procedures, and views, that enable developers to create complex applications.

Getting started with MySQL

Now that we are familiar with the salient features of MySQL, let’s now start our MySQL complete guide by installing MySQL. You will need to install it on your computer. The process for installing MySQL on Windows, Linux, and macOS is slightly different, so we will go through all processes here.

Basic Requirements to install MySQL

MySQL Enterprise Monitor is a powerful tool for managing and manipulating databases, but it has certain system requirements that need to be met in order to ensure a successful installation.

These requirements include having at least 2 CPU cores and 2 GB of RAM, with 4 CPU cores and 8 GB of RAM recommended for optimal performance. For more info, you can visit the official site of MySQL for a detailed breakdown of the requirements for your system.

Windows Installation

To install MySQL on Windows, follow these steps:

Step 1: First, we have to download the MySQL installer, you can do so by visiting the official MySQL website or by simply clicking here. 

Step 2: Once the download for the installer is complete, run it on your computer and follow the prompts.

Step 3: Choose the products you want to install and then click Next. 

Step 4: Now, without changing anything, click the next option until you arrive at setting a password for the server. Add your desired password and make sure to remember it, as you will have to type it every time you start the client.

Step 5: Now click next, and then all the remaining installations will take place.

Step 6: Click Finish, and voila! Your MySQL server is successfully installed on your Windows system.

Linux Installation

To install MySQL on Linux, follow these steps:

Step 1: Open your terminal and type the following command to install the MySQL package:

$ sudo apt-get install mysql-serverCode language: Bash (bash)

Step 2: Now it will download a bunch of required files and install them on your system.

Step 3: After the files get downloaded and installed, you will get a prompt to set a root password for your MySQL server.

Step 4: Once you set the password and hit enter, the remaining installation will take place and MySQL is installed on your Linux system.

Step 5: Now you can start your MySQL server by entering the following command in the terminal.

$ sudo service mysql startCode language: Bash (bash)

macOS Installation

You can check out this article, Complete guide to installing MySQL on macOS for installing MySQL on your macOS system.

Setting Up the Database

Now that you have completed your first step in our MySQL complete guide i.e. installed MySQL on your system, it’s time for you to write your first-ever MySQL query. For today’s article, we will be using MySQL on Windows. To connect to the MySQL server, first, open the MySQL Command Line Client. Now enter the password you entered during the installation process, and press ENTER to continue. If you are able to successfully connect to the MySQL server, you will see the following screen:

Now we will create our first database where we will be storing our data. To do so, we will use the CREATE DATABASE command followed by the database name. For more information, see the code below. 

mysql> CREATE DATABASE residents;Code language: SQL (Structured Query Language) (sql)

To check if your database is created, you can use the SHOW DATABASES command. This will show all the databases in your system.

mysql> SHOW DATABASES;Code language: SQL (Structured Query Language) (sql)

Creating Database Tables

Before you start creating tables to store data, we will have to use the USE statement to switch to the newly created database.

mysql> USE residents;Code language: SQL (Structured Query Language) (sql)

Now the command used to create a table is the CREATE TABLE command. We will create a table named “Blocks” with the following constraints:

mysql> CREATE TABLE Blocks (
Block_name char(1),
Flat_no int,
Name varchar(25),
Rented varchar(4));Code language: SQL (Structured Query Language) (sql)

You must be wanting to know whether our table has been created or not you can check that by using the SHOW TABLES command.

mysql> show tables;
+---------------------+
| Tables_in_residents |
+---------------------+
| blocks              |
+---------------------+
1 row in set (0.02 sec)Code language: SQL (Structured Query Language) (sql)

Working with Data in MySQL 

Now that we have the table structure, we can finally store, manipulate, or update the data records in our tables.

Inserting Data

Since our table has no data in it, the SQL query to insert the records is the INSERT INTO command. As an example, consider the following: 

mysql> INSERT INTO Blocks values (
    -> 'A',
    -> 101,
    -> 'Rajesh',
    -> 'No');Code language: SQL (Structured Query Language) (sql)

While entering data values, make sure to enter them in the same order you created the table columns. Also, make sure to type string-based values in single or double quotes. Similarly, we will insert more records into the table.

You can use the following table as a reference for the upcoming commands:

mysql> SELECT * from blocks;
+-----------------+---------+-----------+--------+
| Block_name      | Flat_no | Name      | Rented |
+-----------------+---------+-----------+--------+
| A               |     101 | Rajesh    | No     |
| A               |     201 | Pankaj    | Yes    |
| B               |     501 | Suresh    | Yes    |
| C               |     307 | Rohit     | No     |
| F               |     408 | Rajeev    | Yes    |
+-----------------+---------+-----------+--------+
5 row are in set (0.9 sec)Code language: SQL (Structured Query Language) (sql)

Updating Data

After adding several records to the table, if we wish to change or modify particular records in the table, we can use the UPDATE command. As an example, consider the following: 

mysql> UPDATE Blocks 
set Flat_no=712, name='Dheeraj' 
where Block_name='C';Code language: SQL (Structured Query Language) (sql)

This command will replace the flat_no and name of the record where block_name is ‘C’.

Deleting Data

The next command on our list is the DELETE command. This command we use to delete a particular record/row from the table. The following query depicts the same:

mysql> DELETE from blocks
where name='Pankaj';Code language: SQL (Structured Query Language) (sql)

This will delete the row in the table “blocks” where the name is “Pankaj”.

Show Data 

After making changes to your MySQL database, you might want to verify that the changes have been made and that the data you entered has been correctly inserted. To do this, you can use the SELECT command, which allows you to display the data present in a table. Simply use the SELECT command followed by the columns you want to display and the name of the table, and MySQL will show you the data contained in the specified table. Refer to the following query:

mysql> select * from Blocks;
+------------+---------+---------+--------+
| Block_name | Flat_no | Name    | Rented |
+------------+---------+---------+--------+
| A          |     101 | Rajesh  | No     |
| B          |     501 | Suresh  | Yes    |
| C          |     712 | Dheeraj | No     |
| F          |     408 | Rajeev  | Yes    |
+------------+---------+---------+--------+
4 rows in set (0.03 sec)Code language: SQL (Structured Query Language) (sql)

These were some basic SQL queries, if you want to explore more examples or learn more SQL commands check out this article, 50 SQL Examples to learn and master SQL.

Conclusion

It’s time we conclude our article; today we talked about MySQL’s complete guide, from the basics of getting started with MySQL, including installing the software, setting up a database, and working with data in tables. We have also outlined some of the benefits of using MySQL, including its compatibility with other programming languages and its ability to handle large amounts of data efficiently. Whether you are a beginner or an experienced developer, MySQL is a powerful tool that can help you manage and manipulate data in your projects.

I hope you found my article useful, If you have any queries, feel free to comment on them down below. Until then, we’ll be back with another fantastic article. Keep coding and have a great day ahead!