Course: MySQL Training
Topic: Introduction to MySQL and Relational Database Management Systems
Course Link: JustAcademy MySQL Training Course
Demo Link: Register for MySQL Course Demo
MySQL is an open-source Relational Database Management System (RDBMS) used to store, organize, retrieve, update, and manage structured data. It uses Structured Query Language (SQL) to communicate with databases. MySQL is widely used in websites, web applications, e-commerce platforms, business systems, and other software applications.
MySQL organizes information into tables containing rows and columns. These tables can be connected through relationships, making it easier to manage related information efficiently. MySQL is developed, distributed, and supported by Oracle Corporation.
For example, an online shopping website can use MySQL to store customer details, product information, orders, payment records, and delivery information.
CREATE DATABASE college;
USE college;
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(100),
course VARCHAR(50),
age INT
);
INSERT INTO students VALUES
(1, 'Rahul Sharma', 'MySQL', 21),
(2, 'Priya Verma', 'Python', 22);
SELECT * FROM students;Explanation: This example creates a database named college, creates a students table, inserts two records, and retrieves all the records using a SELECT statement.
A database is an organized collection of related information stored electronically so that it can be accessed, managed, updated, and retrieved whenever required.
Databases help applications store large amounts of information in a structured and manageable format instead of keeping everything in separate, unorganized files.
A Database Management System (DBMS) is software that allows users and applications to create, store, retrieve, modify, and manage data in databases.
A DBMS provides tools and mechanisms for organizing data, controlling access, managing transactions, and supporting data recovery.
A Relational Database Management System (RDBMS) is a type of database management system that organizes data into tables consisting of rows and columns. Relationships between tables can be established using keys and constraints.
MySQL is an example of an RDBMS. It supports structured data storage and SQL operations for managing relationships between different tables.
Students Table
| student_id | student_name | course_id |
|---|---|---|
| 1 | Rahul | 101 |
| 2 | Priya | 102 |
| 3 | Amit | 101 |
Courses Table
| course_id | course_name |
|---|---|
| 101 | MySQL |
| 102 | Python |
In this example, course_id connects the students table with the courses table. This relationship allows the database to identify which course each student is enrolled in.
The SQL part of MySQL stands for Structured Query Language. SQL is the language used to communicate with relational databases.
The name MySQL combines “My” with “SQL.” The “My” is commonly associated with My, the daughter of MySQL co-founder Michael Widenius.
SELECT student_name, course
FROM students
WHERE age > 20;This query retrieves the names and courses of students whose age is greater than 20.
MySQL commonly works through a client-server architecture. A client application sends a request to the MySQL Server, the server processes the request, accesses or modifies the required data, and returns a result or an error message.
User / Application
|
v
MySQL Client
|
v
MySQL Server
|
v
SQL Parsing and Query Optimization
|
v
Storage Engine (e.g., InnoDB)
|
v
Database Tables and Stored Data
|
v
Query Result Returned to ClientNote: This is a simplified conceptual flow. The internal execution process varies depending on the query, configuration, and storage engine.
MySQL is available under open-source licensing terms, including the GNU General Public License for applicable distributions. Commercial licensing options are also available. Users should review the relevant license terms before distributing or embedding MySQL in commercial products.
MySQL organizes structured information into tables and supports relationships between tables using primary keys, foreign keys, and other constraints.
MySQL supports SQL statements for creating database objects, retrieving information, modifying records, and controlling access.
MySQL includes query optimization, indexing, and storage-engine features that can help applications process data efficiently when databases are designed and configured appropriately.
MySQL provides authentication, privileges, roles in supported versions, and encrypted connection options to help protect database access.
The InnoDB storage engine supports transactions and ACID properties, helping maintain data consistency when multiple related operations are performed.
MySQL can be used in small applications as well as larger systems. Appropriate indexing, server configuration, replication, and architecture choices can help support growing workloads.
Multiple authorized users and applications can work with a MySQL database concurrently, subject to permissions, transaction behavior, and server capacity.
MySQL offers backup and recovery options that help organizations protect important information and restore databases after failures.
MySQL is available on several operating systems, including Linux, Windows, and macOS, with supported installation options varying by platform and release.
| Component | Description |
|---|---|
| MySQL Server | The main database server that processes queries and manages database access. |
| MySQL Client | A tool or application used to connect to the server and execute SQL statements. |
| Database | A logical container used to organize database objects. |
| Table | A structure that stores data in rows and columns. |
| Column | A named attribute that defines a particular type of information in a table. |
| Row | A record containing values for the columns in a table. |
| Storage Engine | The component responsible for storing and retrieving table data. InnoDB is commonly used. |
| SQL | The language used to define, query, and manipulate database information. |
| MySQL Workbench | A graphical application for database development, SQL editing, and administration. |
| Term | Meaning | Example |
|---|---|---|
| Database | An organized collection of related information. | college |
| Table | A structure containing rows and columns. | students |
| Row | A single record in a table. | 1, Rahul, MySQL |
| Column | A field representing an attribute. | student_name |
| Primary Key | A column or group of columns that uniquely identifies each row. | student_id |
| Foreign Key | A column or group of columns that references a key in another or the same table. | course_id |
| Query | An SQL statement used to perform an operation or retrieve data. | SELECT * FROM students; |
| Schema | The structure and organization of database objects. | Tables and relationships |
| Constraint | A rule applied to data to help maintain integrity. | NOT NULL |
| Index | A data structure that can improve data retrieval performance. | Index on email |
SQL statements are commonly grouped into categories according to their purpose. The following are widely used categories for learning MySQL.
| Category | Full Form | Purpose | Examples |
|---|---|---|---|
| DDL | Data Definition Language | Defines or changes database structures. | CREATE, ALTER, DROP, TRUNCATE |
| DML | Data Manipulation Language | Inserts or modifies table data. | INSERT, UPDATE, DELETE |
| DQL | Data Query Language | Retrieves data from tables. | SELECT |
| DCL | Data Control Language | Controls permissions and privileges. | GRANT, REVOKE |
| TCL | Transaction Control Language | Manages transactions. | COMMIT, ROLLBACK, SAVEPOINT |
-- Create a database
CREATE DATABASE company;
-- Select a database
USE company;
-- Create a table
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(100),
salary DECIMAL(10,2)
);
-- Insert a record
INSERT INTO employees
VALUES (1, 'Amit Kumar', 35000.00);
-- Retrieve records
SELECT * FROM employees;
-- Update a record
UPDATE employees
SET salary = 40000.00
WHERE employee_id = 1;
-- Delete a record
DELETE FROM employees
WHERE employee_id = 1;Important: Always use an appropriate WHERE condition when updating or deleting specific records. Without a WHERE clause, UPDATE or DELETE can affect every row in the table.
| MySQL | SQL |
|---|---|
| MySQL is a relational database management system. | SQL is a language used to communicate with relational databases. |
| It stores and manages database information. | It provides commands to define, query, and manipulate information. |
| It processes SQL statements sent by clients. | It is used to write statements such as SELECT and INSERT. |
| It is one of several database systems that use SQL. | SQL can be used with multiple database systems, although syntax and features can differ. |
| Example: MySQL Server. | Example: SELECT * FROM students; |
| MySQL | Microsoft Excel |
|---|---|
| A relational database management system. | A spreadsheet application. |
| Designed to store and manage structured data for applications and multiple users. | Designed for spreadsheet calculations, tabular analysis, and reporting. |
| Supports SQL queries, relationships, constraints, and transactions. | Supports formulas, charts, tables, and spreadsheet-based analysis. |
| Can support large application workloads when properly designed and configured. | Best suited to spreadsheet-based workflows within its product limits. |
| Commonly used as an application database. | Commonly used for budgets, reports, calculations, and data exploration. |
MySQL stores user accounts, website content, product details, comments, and other information required by dynamic websites.
Online stores use databases to manage product catalogs, customer profiles, shopping carts, orders, inventory, and transaction records.
Database systems can manage account information and transaction records. Financial applications require careful security, transaction handling, auditing, and compliance controls.
Schools and training institutes can use MySQL to maintain student information, courses, attendance, marks, and enrollment records.
Healthcare applications can use relational databases to manage appointments, administrative information, and authorized access to records with appropriate privacy and security controls.
Businesses can use MySQL to track products, stock quantities, suppliers, purchase orders, and sales.
Organizations can retrieve and combine database information to prepare reports, monitor business activity, and support decision-making.
MySQL can work with various programming languages through database drivers, connectors, and libraries. Applications use these integrations to execute queries and process database results.
| Language | Common Integration | Example Application |
|---|---|---|
| PHP | MySQLi or PDO | Dynamic websites and content management systems |
| Python | MySQL Connector/Python or other compatible drivers | Data-driven applications and automation tools |
| Java | MySQL Connector/J (JDBC) | Enterprise and desktop applications |
| JavaScript / Node.js | MySQL-compatible Node.js drivers | Backend APIs and web applications |
| C# | MySQL-compatible .NET connector | Business software and services |
# Install the connector:
# pip install mysql-connector-python
import mysql.connector
connection = mysql.connector.connect(
host="localhost",
user="your_username",
password="your_password",
database="college"
)
cursor = connection.cursor()
cursor.execute("SELECT * FROM students")
for row in cursor.fetchall():
print(row)
cursor.close()
connection.close()Explanation: The example connects Python to a local MySQL database, retrieves the records from the students table, prints them, and closes the database resources. Replace the sample connection details with your own configuration. Avoid hardcoding real passwords in production applications.
In this practical exercise, you will create a student database, add records, retrieve information, update a record, and delete a record.
CREATE DATABASE student_management;USE student_management;CREATE TABLE students (
student_id INT PRIMARY KEY AUTO_INCREMENT,
student_name VARCHAR(100) NOT NULL,
email VARCHAR(150) UNIQUE,
course VARCHAR(50),
age INT
);INSERT INTO students (student_name, email, course, age)
VALUES
('Rahul Sharma', '[email protected]', 'MySQL', 21),
('Priya Verma', '[email protected]', 'Python', 22),
('Amit Kumar', '[email protected]', 'Java', 20);SELECT * FROM students;SELECT *
FROM students
WHERE student_name = 'Rahul Sharma';UPDATE students
SET course = 'Advanced MySQL'
WHERE student_id = 1;DELETE FROM students
WHERE student_id = 3;SELECT COUNT(*) AS total_students
FROM students;| Command | Purpose |
|---|---|
SHOW DATABASES; |
Lists databases visible to the current account. |
CREATE DATABASE demo; |
Creates a database named demo. |
USE demo; |
Selects the demo database for subsequent statements. |
SHOW TABLES; |
Lists tables in the selected database. |
DESCRIBE students; |
Displays the structure of the students table. |
SELECT * FROM students; |
Retrieves all columns and rows from students. |
INSERT INTO students ...; |
Adds records to a table. |
UPDATE students SET ... WHERE ...; |
Modifies selected records. |
DELETE FROM students WHERE ...; |
Deletes selected records. |
COUNT(*) |
Counts the rows returned by the query. |
MySQL is a relational database management system used to store, organize, retrieve, and manage structured data using SQL.
No. MySQL is database software. SQL is the language commonly used to interact with MySQL databases.
SQL stands for Structured Query Language.
MySQL is available under open-source licensing terms, and commercial licensing options are also available. The appropriate license depends on how the software is used and distributed.
DBMS is the general category of software used to manage databases. An RDBMS is a type of DBMS that organizes data relationally, commonly using tables, keys, and relationships.
A table is a database structure that stores related information in rows and columns.
A primary key uniquely identifies each row in a table. Its values must be unique and cannot be NULL.
A foreign key is a column or group of columns used to establish and enforce a relationship with a referenced key, helping maintain referential integrity.
Yes. MySQL is frequently used as the database layer for websites, backend APIs, e-commerce platforms, and other web applications.
MySQL Workbench is a graphical tool used for database design, SQL development, database connections, and administration.
MySQL is the database management system, whereas SQL is the language used to communicate with relational database systems.
InnoDB is a commonly used MySQL storage engine and supports transactions, foreign keys, and other data-management features.
MySQL is a widely used relational database management system that helps developers and organizations store, organize, retrieve, and maintain structured data. Its SQL support, relational tables, transaction capabilities, security features, and integration with programming languages make it useful for a wide range of applications.
Understanding MySQL begins with learning databases, tables, rows, columns, data types, keys, and basic SQL statements. Regular practice with creating databases, inserting records, retrieving data, updating information, and managing relationships builds a strong foundation for advanced database development.
Learn more: Explore the JustAcademy MySQL Training Course
Register for a demo: Register for the MySQL Course Demo
