Unveiling the Power of MySQL Queries: A Quick Guide for Beginners

In this guide, we'll explore the vital importance of MySQL queries for beginners, a fundamental skill for anyone diving into the realm of web development and data manipulation.

Introduction

In this tutorial, you are going to learn the basics of MySQL queries that will help you get started with RDBMS. We would recommend budding web developer to learn MySQL query as it is one of the fundamental aspect of web development.

Create database in MySQL

The first and foremost thing that you need to be doing, is to create a database first. 

Think it like a house which will have multiple rooms and amenities under it.

CREATE DATABASE YOUR_DB_NAME;

Fetch all databases

If you want to view the databases, you have right now. Use the SQL given below.

SHOW DATABASES;

Comments in MySQL

To comment a particular SQL use double dash.

-- this is a comment

Creating a table

I am going to show you a practical example of creating table. 

Take an employee system as an example, we will be creating 2 tables viz. employee and salary.

• Employee - The basic details of the employee like name, email, mobile etc.
• Salary - It contain the salary given to the employee for a particular month.

Creating employee table and adding some dummy values.

CREATE TABLE IF NOT EXISTS `employee` ( 
	`id` int(11) AUTO_INCREMENT NOT NULL, 
	`name` varchar(255) NOT NULL, 
	`email_address` varchar(255) NOT NULL, 
	`mobile_number` varchar(50) NULL, 
	PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Inserting new records in a Table

If you want to add new records into the table you can do the following.

INSERT INTO `employee` (`id`, `name`, `email_address`, `mobile_number`) VALUES (null, 'John Doe', 'john@gmail.com', '8989787890');

If you would like to insert multiple data in one statement, use the following query.

INSERT INTO `employee` (`id`, `name`, `email_address`, `mobile_number`) VALUES 
(null, 'Captain America', 'captain@gmail.com', '998978888'), 
(null, 'Steve Rodgers', 'steve@gmail.com', '8877665544');

Creating Foreign Keys

We will now create a salary table.

We will create a column named empl_id which is the foreign key to category table. 

I have set ON DELETE RESTRICT so no user can delete category if product is present in the table (Referential Integrity).

CREATE TABLE IF NOT EXISTS `salary` ( 
	`sal_id` int(11) AUTO_INCREMENT NOT NULL, 
	`emp_id` int(11) NOT NULL, 
	`amount` DECIMAL(10,2) NOT NULL, 
	`paid_on` DATE NOT NULL,
 	PRIMARY KEY (`sal_id`), 
	FOREIGN KEY (`emp_id`) REFERENCES `employee` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Let us now insert some dummy data in the products table

INSERT INTO `salary` (`sal_id`, `emp_id`, `amount`, `paid_on`) VALUES 
(NULL, '1', '5000', '2023-11-01'), 
(NULL, '2', '6000', '2023-11-01'), 
(NULL, '3', '6500', '2023-11-01'),
(NULL, '1', '6500', '2023-12-01'),
(NULL, '2', '6500', '2023-12-01'),
(NULL, '3', '6500', '2023-12-01');

Adding Column to a table

You can always add new column(s) to an existing table based on your requirement.

In this case let us add country column in employee table to store country name.

ALTER TABLE `employee` ADD `country` VARCHAR(100) NULL;

You can add multiple column at once as well.

ALTER TABLE `employee` ADD `state` VARCHAR(100) NULL AFTER `country`, ADD `city` VARCHAR(100) NULL AFTER `state`;

Drop column(s) in a table

Let's say you want to drop a column in a table, use the MySQL Query given below.

If you want to drop a single column, use the SQL below.

ALTER TABLE `employee` DROP `state`;

However, if you want to drop multiple column at once, use the SQL below.

ALTER TABLE `employee` DROP `state`, DROP `city`;

Retrieving All Data from a Table

If you want to fetch the records of a table, use the SELECT statement.

SELECT * FROM `employee`;

Selecting Specific Columns from a Table

It is a always a good practice to include the column that are needed for a particular scenario.

Suppose you need to display only the id and name of the employee, in that case you should never include all columns in your query.

SELECT `id`, `name` FROM `employee`;

Using the WHERE Clause to Filter Data

If you would like to fetch the record of an employee whose id is 1. use the WHERE keyword.

SELECT * FROM `employee` WHERE `id` = 1

Sorting Data Using ORDER BY

If you want to fetch record in an ascending or descending order based on certain column, use ORDER BY.

-- Sort by id ASC
SELECT * FROM `employee` ORDER BY `id` ASC;

-- Sort by id DESC
SELECT * FROM `employee` ORDER BY `id` DESC;

Using the LIMIT Clause for Pagination

Learning how to implement the LIMIT clause for paginating your query results, a crucial skill for handling large datasets.

Now, it is not recommend to fetch all the rows at a single time. We can limit rows by using LIMIT keyword.

-- Fetch 5 rows 
SELECT * FROM `employee` LIMIT 5;

-- Fetch 10 rows but from 6 position 
SELECT * FROM `employee` LIMIT 6, 10;

Wildcard

A wildcard character is used to substitute one or more characters in a string. 

Wildcard characters are used with the LIKE operator. Symbol used in wildcards are % and _ 

In general terms, you can use this wildcards to search within records.

--search for records where name starts with J 
SELECT * FROM `employee` WHERE `name` LIKE 'J%'; 

--search for records where name ends with J
SELECT * FROM `employee` WHERE `name` LIKE '%J'; 

--search for records where name contain the the character J
SELECT * FROM `employee` WHERE `name` LIKE '%J%'; 

--search for recordswhere first character can be anything rest of the string 
SELECT * FROM `employee` WHERE `name` LIKE '_J';

Alias for column while fetching

Sometimes you would want the name of of the column to be different than its original name which displaying the records. You can achieve it via ALIAS. take a look at an example below.

SELECT `id` AS employee_id, `name` as employee_name FROM employee;

Select rows with NULL and NOT NULL values

To fetch the rows of the table with null values use the query 

-- rows where column is null
SELECT * from tablename where columnnname IS NULL;

-- rows where column is not null
SELECT * from tablename where columnnname IS NOT NULL;

Count rows of a table

To get the count of rows in the table.

SELECT count(id) FROM employee;

-- with some conditions
SELECT COUNT(id) FROM employee WHERE id > 200;

Essential String Functions (CONCAT, SUBSTRING, etc.)

There are times when you prefer using MySQL function over PHP function. For instance, if you want to fetch full name from users table, get fetch the columns (first name, middle name, last name) and then concat it on server side. Instead use can use CONCAT_WS() functions provided in MySQL. 

As stated in the official documentation, CONCAT_WS() stands for Concatenate With Separator and is a special form of CONCAT(). CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument. 

Let us now concatenate the user name with a blank space separator using CONCAT_WS() function.

SELECT CONCAT_WS('|', id, name) as s FROM `employee`;

Output

1|John Doe 
2|Captain America 
3|Steve Rodgers

Understanding Subqueries and Their Usage

A subquery is a SELECT statement within another statement.

Let's take a simple example.

SELECT * FROM table1 WHERE column1 = (SELECT column1 FROM table2);

As you can see, the first select SELECT statement  SELECT * FROM table1 WHERE column1 is called outer query and the inner SELECT statement which is inside parenthesis is called inner query.

Note: A subquery must always appear within parentheses. 

Using Subqueries in SELECT Statements

The subqueries can be used to fetch columns(s) from a table.

Let's take our example where we need to display the employee name when fetching from salary table. 

SELECT emp_id, amount, (SELECT name FROM employee WHERE emp_id = id) AS employee_name FROM `salary`;

Truncate vs Delete

To understand the difference between delete and truncate table is very crucial.

TRUNCATE - It drop and re-create the table, and is much faster than deleting rows one by one, particularly for large tables. It empties a table completely; to truncate any table.

TRUNCATE TABLE_NAME;

DELETE - removes rows from a table. You can use WHERE clause as well to give some conditions as well. The DELETE statement deletes rows from tbl_name and returns the number of deleted rows.

-- Delete with conditions
DELETE FROM employee WHERE prd_id > 500


-- delete without conditions
DELETE FROM employee

Drop Database

And all good things comes to an end. if ever you need to drop the database. Be extremely careful when you uses drop statement as it drops all tables in the database and deletes the database.

DROP YOUR_DB_NAME;

Conclusion

As we conclude this comprehensive guide, reflect on the MySQL queries you've mastered, which will empower you in your database management journey.

Happy Learning!