MySQL Tutorial: A Complete Guide for Beginners

Introduction

MySQL is an open-source, relational database management system (RDBMS) introduced by a Swedish company called MySQL AB in 1995. It is a structured query language used to store, retrieve, and manage data in a structured and tabular format.

In today’s time a very large amount of data is generated every second, and it is essential to manage databases effectively while also protecting them from unauthorized users, since data theft can lead to many illegal activities. MySQL is a very popular choice for managing the database because of its high performance, ease of use, and low cost, making it suitable for developing scalable business applications.

Advantages of MySQL

Given below is the list of advantages of MySQL:

2. High Performance: MySQL provides optimized queries for processing fast data retrieval. It supports large databases, managing the data with millions of rows and columns, and it can handle high-traffic websites and applications.
3. Ease of Use: MySQL is simple to install and configure on a system. MySQL uses structured query language, which is beginner-friendly and easy to learn. It is also available with graphical tools like MySQL Workbench for easier management.
4. Open Source and Low Cost: MySQL is an open-source platform and hence available free to use under General Public Guidance. Paid services are also available with advanced features and support. On comparing MySQL with other platforms available for managing the database, you find it is much more cost-effective compared to many commercial database systems.
5. Scalability and Flexibility: MySQL can be used for managing the database starting from small projects to large-scale enterprise applications. It can be scaled horizontally (improving a single server’s hardware) and vertically (adding more servers and distributing the workload across them).

5. Strong Security: MySQL provides higher security to a database and provides user authentication, encryption, and access control. It ensures the data is protected with SSL and password policies.

Installing MySQL

To install MySQL on Windows, download the MySQL installer from the official MySQL website. Run the installer, and choose a setup you want (Developer default or Custom). After this, select the products you want (MySQL Server and MySQL Workbench), and then follow the prompts to complete the installation. Given below is the step-by-step installation guide for MySQL.

4. Download the MySQL installer: Open any browser in your system (Chrome, Brave, or Edge) and go to the MySQL official downloads page.
5. Run the installer: Store the downloaded file in your system and run it to start the setup process.
6. Choose a Setup Type: You are asked to choose the setup while installing, and you need to choose one out of two options. First is Developer by default, which will install the MySQL server and other developer tools like MySQL Workbench. Second is the Custom option, where MySQL will be installed first and other options you need to choose as per your requirements.
7. Install Products: If you choose “Custom,” select the MySQL server and MySQL Workbench from the list of available options and add them to the installation. Click on execute to download and install the selected products.
8. Configure the server: After the products are installed, you’ll configure the MySQL server. You need to set the password for the root account when prompted. You can keep the other configuration settings at their defaults unless you have a specific need.
9. Complete Installation: After configuring, you need to follow the remaining prompts to finish the configuration.
10. Connect and Verify: Once everything is completed, you can open MySQL Workbench and connect to your installed MySQL server using the root password you created.

Key Concept in MySQL

Given below is the list of important terms in MySQL that you should know for writing the Structured Query Language.

1. Constraints: Constraints are defined as the rules applied to columns or the entire table within a database to limit the type of data that can be inserted, updated, or deleted. The primary goal of constraints is to define what values are allowed in columns and how data can be manipulated, preventing invalid and inconsistent data from entering the database. Given below is the list of common constraints in MySQL.

• NOT NULL: This constraint ensures a selected column cannot have null values.
• UNIQUE: To ensure that all the values in a selected table are unique.
• PRIMARY KEY: It is a column or set of columns within a table that uniquely identifies the properties combining NOT NULL and UNIQUE.
• FOREIGN KEY: A foreign key links or establishes the relationship between two tables, ensuring data integrity and consistency.
• CHECK: This constraint ensures the condition must be true for values.
• DEFAULT: It provides a default value when none of the data is provided.

2. Clauses: Clauses are the built-in functions that define specific conditions within an SQL statement to retrieve, manipulate, or update data from the database. They refine the results of queries and ensure efficient handling of data. Commonly used clauses in MySQL include:

• SELECT: This keyword specifies the section of the table to be retrieved while performing the operation on the database.
• FROM: This keyword indicates the table from which the data needs to be retrieved.
• WHERE: This keyword filters the data of the table based on specified conditions, returning only those values that meet the certain criteria. This keyword is used with SELECT, FROM, UPDATE, and DELETE statements.

3. Joins: A join is a clause in SQL used to combine columns of two or more tables that are related to one another with some kind of relationship. Joins allow us to retrieve data from two or more different tables and represent that data in a combined single unit. Given below is the description of the kind of join used in Structured Query Language.

• INNER JOIN: This join matches rows in both the tables based on a specified condition.

Example

# Write MySQL query statement to perform inner join

SELECT employees.name, departments.department_name

FROM employees

INNER JOIN departments

ON employees.dept_id = departments.id;

This query will return only the employees who belong to a department.

• LEFT JOIN: This join returns all the data from the left table and matches the row with the right table. If no match is found in the right table, then null values will be returned for its columns.

Example

# Write MySQL Query statements to perform left join

SELECT employees.name, departments.department_name

FROM employees

LEFT JOIN departments

ON employees.dept_id = departments.id;

This query will list all employees.

• RIGHT JOIN: This join returns the data from the right table and matches the row with the left table. If no match is found in the left table, null values are returned for its columns.

Example

# Write a MySQL query statement to perform a right join

SELECT employees.name, departments.dept_name

FROM employees

RIGHT JOIN departments

ON employees.dept_id = departments.dept_id;

This query will return all the departments, even if no employees are

Assigned.

• FULL JOIN: Full Join, or outer join, returns the rows when there is a match in either the left table or right table. If no match exists in the table, null values are returned for its columns.

Example

# Write MySQL query statement to perform full join

SELECT employees.name, departments.dept_name

FROM eployees

LEFT JOIN departments

ON employees.dept_id = departments.dept_id

UNION

SELECT employees.name = departments.dept_name

FROM employees

RIGHT JOIN departments

ON employees.dept_id = departments.dept_id;

This query performs a FULL JOIN in MySQL.

• CROSS JOIN: This join returns the Cartesian product of the two tables, meaning every row from the first table combined with every row from the second table, regardless of any common columns.

Example

Table Students

Table Courses

Query

# MySQL Query to perform CROSS JOIN

SELECT name, course

FROM Students

CROSS JOIN Courses;

Output

4. Aggregate Functions: These are the functions in SQL that perform specific functions on a set of data, returning the resultant value after performing the function. They perform the mathematical function or calculation on a set of specified data. Following is the list of common aggregate functions in SQL.

• COUNT(): This function returns the number of rows that match the specified condition.
• SUM(): This function performs the addition operation on selected numeric data and returns the resultant value.
• AVG(): This function calculates the average value of selected data and returns the finalized value.
• MIN(): This function returns the minimum value out of available
• MAX(): This function returns the largest or maximum value out of available options.

Example

# Write MySQL Queries for Aggregate Functions

SELECT

COUNT(*) AS total_orders,

SUM(amount) AS total_sales,

AVG(amount) AS avg_order_value,

MIN(amount) AS smallest_sale,

MAX(amount) AS maximum_sale

FROM orders;

Explanation: The above query will select the data from the table name

orders and returns total number of sales, sum of all the sales, average

of sales, minimum sales value, and maximum sales value.

5. MySQL Data Types: Data types refer to the type of value a variable has and what type of operations, like mathematical, relational, or logical, can be applied to it without causing an error. There are different data types in MySQL. Given below is the list of different data types used in MySQL.

• Numeric: It represents the numeric data type in MySQL, where INT stores the whole number, DECIMAL stores the decimal values, and FLOAT stores approximate floating-point numbers.

• String: This data type is used to store the sequence of characters, which can include letters, numbers, and symbols. It includes CHAR, which stores fixed-length strings; VARCHAR, which stores variable-length strings; and TEXT to store large variable-length character strings.

• Date/Time: This data type stores date and time information. The common data type in this category includes DATE for storing only the date in year-month-date format (YYYY-MM-DD). TIME for storing only the time of a day in hour:minute:seconds format (HH:MM:SS). TIMESTAMP for storing both date and time, often with automatic update behaviour (example: updating to the current timestamp on record modification). It also includes time zero information.

Conclusion

This article describes MySQL with its advantages and a complete installation guide step-by-step. The brief description of the key concepts of MySQL is given in this article with examples and SQL queries. All the important terms with keywords are defined with explanations.

I hope this article has provided you valuable information. If you are looking for more such kinds of information, I suggest you visit the Tpoint Tech Website, where you can find various articles about programming and other technology as well, along with working codes, interview questions, and an online compiler where you can run and test your code.

For related content: https://theglobalnewz.com/best-data-science-course-training-in-pune/