Learn MySQL: A Comprehensive Guide to Mastering SQL Commands

MySQL is a popular relational database management system (RDBMS) among developers and IT professionals. MySQL offers reliable performance and scalability, easily accessible to users of all skill levels.

#Intro

Hello, I'm Ibnu Gunawan 👋🏻. This article was created because, like everyone else, I sometimes forget SQL commands 🙈. It also serves as a handy reference for myself. Enjoy your reading! 📖.

Introduction to Databases

Databases are a critical part of information systems and application development. They serve as organized and structured repositories for information, allowing efficient storage, access, and management of data. In this article, we will focus on using MySQL as our database system.

Accessing MySQL

To access MySQL, open your terminal (Linux/MacOS) or CMD (Windows) and enter the following command:

mysql -u [username] -p

Replace [username] with your MySQL username (the default username is usually root if you're using XAMPP or LAMPP). You will be prompted to enter your MySQL password after running this command.

Creating a Database

To create a new database, you can use the following command:

CREATE DATABASE [database_name];

Replace [database_name] with the name of the database you want to create.

Deleting a Database

To delete a database, you can use the following command:

DROP DATABASE [database_name];

Replace [database_name] with the name of the database you want to delete.

Selecting a Database

Before performing operations on tables, make sure you've selected the database you intend to work with. Use the following command:

USE [database_name];

Replace [database_name] with the name of the database you want to use.

Creating Tables

To create a new table, you can use the following command:

CREATE TABLE [table_name] (
    [column_name_1] [data_type_1],
    [column_name_2] [data_type_2],
    ...
);

Replace [table_name] with the name of the table you want to create. Replace [column_name] with the column names you want to create and [data_type] with the data type for each column.

Here are some data types you can use in MySQL:

• INT for integers
• FLOAT for floating-point numbers
• DOUBLE for double-precision floating-point numbers
• VARCHAR for variable-length text
• CHAR for fixed-length text
• DATE for dates
• TIME for times
• DATETIME for date and time
• BOOLEAN for true/false values

Deleting Tables

To delete a table, you can use the following command:

DROP TABLE [table_name];

Replace [table_name] with the name of the table you want to delete.

Adding Data

To add data to a table, use the following command:

INSERT INTO [table_name] ([column_name_1], [column_name_2], ...) VALUES ([value_1], [value_2], ...);

Replace [table_name] with the name of the table where you want to insert data. Replace [column_name] with the column names you created when you defined the table, and [value] with the values you want to insert into the columns.

Modifying Data

To modify or update data in a table, you can use the following command:

UPDATE [table_name] SET [column_name] = [new_value] WHERE [condition];

Replace [table_name] with the name of the table you want to update. Replace [column_name] with the column you want to modify and [new_value] with the new value for that column. Replace [condition] with the condition that must be met to make the data change.

Here are some examples of conditions you can use in MySQL:

• = to check if two values are equal
• <> or != to check if two values are not equal
• < to check if the first value is less than the second value
• > to check if the first value is greater than the second value
• <= to check if the first value is less than or equal to the second value
• >= to check if the first value is greater than or equal to the second value
• LIKE to check if the value in a column matches a specific pattern
• IN to check if the value in a column is in a specific list of values
• BETWEEN to check if the value in a column is between two specific values

For example, if you want to update the user_id column to 1, you can use this command:

UPDATE [table_name] SET [column_name] = [new_value] WHERE id = 1;

Deleting Data

To delete data from a table, use the following command:

DELETE FROM [table_name] WHERE [condition];

Replace [table_name] with the name of the table where you want to delete data. Replace [condition] with the condition that must be met to delete the data.

You can refer to the example in the "Modifying Data" section for conditions.

Displaying Data

To display data from a table, use the following command:

SELECT * FROM [table_name];

Replace [table_name] with the name of the table you want to view.

Displaying Data with Conditions

To display data from a table with specific conditions, use the following command:

SELECT * FROM [table_name] WHERE [condition];

Replace [table_name] with the name of the table you want to view and [condition] with the conditions that must be met to display the data (optional). Use * to display all columns, but if you want to display specific columns, list their names separated by commas. For example:

SELECT [column_name_1], [column_name_2] FROM [table_name] WHERE [condition];

You can refer to the example in the "Modifying Data" section for conditions.

Sorting Data

To sort data in a table based on a specific column, use the following command:

SELECT * FROM [table_name] ORDER BY [column_name] [ASC/DESC];

Replace [table_name] with the name of the table you want to sort. Replace [column_name] with the column you want

to use as the reference for sorting. Use ASC to sort from the smallest to the largest value or DESC to sort from the largest to the smallest value.

Counting Data

To count the number of rows in a table, use the following command:

SELECT COUNT(*) FROM [table_name];

Replace [table_name] with the name of the table you want to count.

Joining Tables (RELATIONAL JOIN)

To combine data from two or more tables, use the following command:

SELECT [column_name_1], [column_name_2], ... FROM [table_name_1] JOIN [table_name_2] ON [table_name_1].[column_name] = [table_name_2].[column_name];

Replace [column_name] with the same column name in both tables you want to join. Replace [table_name_1] and [table_name_2] with the names of the tables you want to combine.

Joins

There are several types of join relationships that you can use to combine tables:

• Inner Join: Combines rows from both tables that have matching values.
• Left Join: Combines all rows from the left table and matching rows from the right table.
• Right Join: Combines all rows from the right table and matching rows from the left table.
• Full Outer Join: Combines all rows from both tables, including rows that do not have matching values in the other table.

Group By

To group data based on a specific column, use the following command:

SELECT [column_name], COUNT(*) FROM [table_name] GROUP BY [column_name];

Replace [column_name] with the column you want to group by. Use COUNT(*) to count the number of rows in each group.

Alter Table

The ALTER TABLE command allows you to modify the structure of a table. You can use it to add new columns, change the data type of columns, and delete columns. Here's an example:

ALTER TABLE [table_name] [command];

Replace [table_name] with the name of the table you want to modify and [command] with the specific action you want to perform, such as adding a new column, changing the data type of a column, or deleting a column.

Indexes

Indexes are used to speed up data retrieval in a table. To create an index on a specific column, use the following command:

CREATE INDEX [index_name] ON [table_name] ([column_name]);

Replace [index_name] with the name of the index you want to create. Replace [table_name] with the name of the table where you want to create the index. Replace [column_name] with the name of the column you want to create the index on.

Backup and Restore

To back up a database, use the following command:

mysqldump -u [username] -p [database_name] > [file_name].sql

Replace [username] with your MySQL username (the default username is usually root). Replace [database_name] with the name of the database you want to back up. Replace [file_name] with the name of the file where you want to save the backup.

To restore a database from a backup file, use the following command:

mysql -u [username] -p [database_name] < [file_name].sql

Replace [username] with your MySQL username (the default username is usually root). Replace [database_name] with the name of the database you want to restore. Replace [file_name] with the name of the backup file you want to use for the restore.

#Author's Message

I hope this article helps you understand and master the SQL commands in MySQL. Thank you! 👨🏻‍💻.