Let's talk about SQL or rather MySQL
Introduction
Have you ever wondered how devices store your data like how your phone stores your contacts, messages, etc or when you log on to an e-commerce websites and order a product, do you wonder where the product you are looking for is stored. Well, your contacts or messages or data is stored in a database of your phone and the product you are searching for on your favourite e-commerce website is in database of that website.
I got a chance to learn how this work and I would like to teach you too. So let's get started.
What is a Database?
Before we dive into SQL, it’s important to understand what a database is. A database is a collection of data that is organized in a specific way to allow for easy access and manipulation. Databases can be used to store and manage a wide range of information, such as customer data, inventory, and financial records.
So what is SQL?
SQL(Structured Query Language) is a programming language used to manage and manipulate relational databases. It is one of the most widely used languages in the database industry and is essential for anyone looking to work with data.
I am going to cover the basics of SQL and give you an introduction to the language but first let’s talk about types of databases.
Types of Databases
Relational Databases
Non-relational Databases
Relational Databases
This is the most common type of database. In a relational database, data is organized into tables, with each table containing information about a specific entity or topic. For example, a customer database might have a table for customer names, addresses, and orders. SQL is a language used to interact with relational databases. It allows you to create, read, update, and delete data in the tables of a database.
Non-relational Databases
Also called NoSQL. These are databases that do not use tables to store data. Instead, they use various data models such as key-value pairs, documents, graphs etc.
Now let’s go deeper to understand how you can interact with MySQL.
Installation of MySQL
To do this we shall use Ubuntu 20.04 to demonstrate how to install MySQL
I know you are wondering what is Ubuntu. Let me describe in few words . Ubuntu is a popular open-source operating system based on the Linux kernel. It is known for its ease of use and user-friendly interface, making it a popular choice for both beginner and advanced users. One of the key features of Ubuntu is its package management system, which makes it easy to install, update, and manage software packages.
Now Let’s go through the steps to install MySQL so that we can interact with its environment.
Step 1.
Update the package list use this command
sudo apt update
Step 2.
Install mysql server using this command
sudo apt install mysql-server
During the installation process, you will be prompted to set a root password for MySQL. Enter a strong password and remember it as you will need it later. If you are asked to re-enter password enter the same password you entered earlier.
Step 3.
Start mysql service you can use this command
sudo service mysql start
To check if mysql is running use the command
sudo service mysql status.
Step 4.
Secure the MySQL installation by running the security script:
sudo mysql_secure_installation
This script will prompt you to set several options to secure your installation, including removing anonymous users, disallowing remote root login, and removing test databases.
Step 5.
Once the security script is complete, you can log in to MySQL using the command
sudo mysql -u root -p
You will be prompted to enter a password enter the root password you set during installation when prompted. Congratulations, you have successfully installed MySQL on Ubuntu 20.04.
Now let’s talk about how to work with MySQL now that you are logged in.
Basic SQL Commands
Now let’s look at some of the basic SQL commands you’ll need to know to get started.
SELECT The SELECT statement is used to retrieve data from one or more tables in a database. It allows you to specify the columns you want to retrieve and filter the results based on specific criteria. Here is an example:
SELECT first_name, last_name, email FROM customers WHERE state = 'CA’;This statement will retrieve the first name, last name, and email of all customers who live in California.
INSERT The INSERT statement is used to add new data to a table. Here is an example:
INSERT INTO customers (first_name, last_name, email) VALUES (’Myra’, 'Jarenga’, 'myrajarenga1234@gmail.com ’);This statement will add a new record to the customer's table with the values Myra, Jarenga, and myrajarenga1234@gmail.com for the first name, last name, and email fields, respectively.
UPDATE The UPDATE statement is used to modify existing data in a table. Here is an example:
UPDATE customers SET email = 'myrajarenga.com' WHERE id = 123;This statement will update the email address for the customer with an ID of 123 to myrajarenga@gmail.com.
DELETE The DELETE statement is used to remove data from a table. Here is an example:
DELETE FROM customers WHERE id = 123;This statement will delete the record for the customer with an ID of 123 from the customer's table.
In conclusion
SQL is a powerful language used to manage and manipulate relational databases. By understanding the basics of SQL and the common commands used to create, read, update, and delete data, otherwise known as (CRUD) operation you’ll be well on your way to working with data in a professional capacity. While this guide provides an introduction to SQL, there is much more to learn. To become proficient in SQL, I am also learning too, you’ll need to practice and work with real-world data sets. There are many online resources available to help you learn SQL, including tutorials, courses, and books.
I will be attaching links to some of the resources to use at the end of this article which I found useful and helpful while learning this language. With dedication and practice, you and I can become experts in SQL and leverage its power to work with data effectively.
If you would like to connect with me, do send me DM on my Twitter @myrajarenga , I would love to hear from you if you enjoyed reading it as I enjoyed writing it. Thank you for your time. You can also support me by following me on this blog here Myra Jarenga's Blog
Reference
https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-20-04
https://web.csulb.edu/colleges/coe/cecs/dbdesign/dbdesign.php?page=sql/queries.php