MySQL Tutorial for Beginners

MySQL Tutorial for Beginners

In the digital age, knowing how databases function is a valuable skill, and MySQL is one of the most widely used and powerful open-source relational database management systems (RDBMS) out there. Whether you are a budding web developer, a budding data analyst, or just interested in how information is stored and queried, this complete MySQL tutorial for beginners is your ideal starting point.

Allow us to turn you into a MySQL pro from a database beginner. Take a look at our Complete MySQL course syllabus here!

What is MySQL? Getting Familiar with the Fundamentals

MySQL is, at its essence, a high-powered data storage, management, and retrieval tool. Consider it a digitally super-organized filing cabinet, but one capable of containing huge quantities of information and permitting lightning-fast retrieval and manipulation.

• Relational Database Management System (RDBMS): This implies that MySQL stores data in tables, which are groups of related data entries arranged with columns and rows. These tables are then related (linked) to one another, enabling sophisticated data relationships.
• Open Source: MySQL is open to modification and usage, and therefore extremely popular for both small personal projects and large enterprises.
• Client-Server Model: MySQL uses a client-server model. The MySQL server hosts all the databases, and clients (such as your command line or graphical user interface) connect to the server to issue requests and obtain data.
• SQL (Structured Query Language): This is the standard language spoken to interact with databases such as MySQL. You will be learning to “speak” SQL in this tutorial!

Why is MySQL so widely used?

• Scalability: Can support from tiny websites to giant data warehouses.
• Performance: Fast and efficient handling of queries.
• Reliability: Strong and reliable for mission-critical applications.
• Security: Has built-in strong security options to safeguard your data.
• Community Support: A huge world-wide community is synonymous with plenty of resources, tutorials, and support.

Suggested: MySQL Course in Chennai.

Setting Up Your MySQL Environment

You require a MySQL server and a client to access it before we begin querying.

Option 1: With XAMPP (Ideal for Beginners)

XAMPP is an open-source, free cross-platform package of web server solution stack. It comes bundled with Apache, MariaDB (MySQL fork), PHP, and Perl. It’s the simplest method to run a local MySQL server.

1. Download XAMPP: Visit the official Apache Friends site (https://www.apachefriends.org/) and download the XAMPP installer for your OS (Windows, macOS, Linux).
2. Install XAMPP: Follow the directions on the screen. The default options are likely okay.
3. Run Apache and MySQL: After installing, run the XAMPP Control Panel. Press the “Start” buttons beside “Apache” and “MySQL.”
4. Browse to phpMyAdmin: Use your web browser and go to http://localhost/phpmyadmin. This is a web-based graphical interface for managing your MySQL databases, which we’ll use for early exploration.

Option 2: Installing MySQL Standalone

For advanced users or particular development environments, you can install MySQL Server directly.

• Download MySQL Community Server: Go to the official MySQL site ([suspicious link removed]) and select the “MySQL Community Server” version.
• Install MySQL: Use the installation wizard. As you go through the installation process, you will be required to enter a root password. Don’t forget this password!
• Install MySQL Workbench (Optional but Recommended): MySQL Workbench is an intuitive visual tool for designing, developing, and administering databases. Get it from the MySQL website too.

We will use the MySQL command-line client primarily for this MySQL tutorial, which is available on all platforms independent of your installation process.

Connecting to MySQL using Command Line

You must first open your command prompt (Windows) or terminal (macOS/Linux) after the installation.

If you are using XAMPP: The MySQL command line executable is usually C:\\xampp\\mysql\\bin on Windows. You will have to go into this directory or include it in your system’s PATH environment variable.

cd C:\xampp\mysql\bin

mysql -u root -p

(It will then ask for the password. If you did not create one for root on XAMPP, simply press Enter.)

If from standalone MySQL: The mysql client must be in the PATH of your system or you may need to change to its installation directory (e.g., C:\Program Files\MySQL\MySQL Server 8.0\bin).

mysql -u root -p

Type the root password you installed.

You should now be able to see the mysql> prompt, which means you’re logged into the MySQL server!

Related: Oracle DBA Tutorial for Beginners.

Understanding Databases and Tables

Let’s get a strong grasp of the basic building blocks: databases and tables before we proceed to SQL commands.

• Database: A database is used to hold tables and other database objects such as views, stored procedures, and triggers. You may have more than one database on one MySQL server.
• Table: Tables are where your real data is stored. They are structured in rows and columns, just like a spreadsheet.
  • Columns (Fields): Specify what kind of data will be kept in that column (e.g., VARCHAR for text, INT for numbers, DATE for dates). There is a name for each column.
  • Rows (Records): Store a single record or entry in the table. Data for all the columns defined within that table are in each row.

Data Types: The Underpinnings of Columns

Selecting the appropriate data type for your columns is important for optimal storage and data integrity. Some basic MySQL data types include:

Data Type	Description	Example Values
INT	Integer (whole number)	10, -5, 1000
DECIMAL(P,S)	Fixed-point number (P=total digits, S=decimal places)	123.45, 9.99
VARCHAR(N)	Variable-length string (N=max characters)	Hello World’, ‘MySQL’
TEXT	Larger text strings	A very long description’
DATE	Date in ‘YYYY-MM-DD’ format	2025-07-28′
TIME	Time in ‘HH:MM:SS’ format	14:30:00′
DATETIME	Date and time in ‘YYYY-MM-DD HH:MM:SS’ format	2025-07-28 14:30:00′
BOOLEAN	True/False (often stored as TINYINT(1) 0 or 1)	TRUE, FALSE

Related: Oracle SQL Tutorial for Beginners.

Basic SQL Commands: Getting Started with Data

Let’s get our hands dirty with some basic SQL commands now. SQL commands are case-insensitive, but it is a good habit to write SQL keywords (such as CREATE, SELECT) in uppercase for clarity. All SQL statements finish with a semicolon (;).

Database Management

Show Databases: View a list of all your databases.

SHOW DATABASES;

Create a Database: Create a new database. Let’s create one for our tutorial.

CREATE DATABASE my_first_database;

Use a Database: Inform MySQL that you wish to work with a particular database.

USE my_first_database;

You’ll get an indication of “Database changed.”

Drop a Database (Use with Caution!): Drop a whole database. This is irreversible!

DROP DATABASE my_first_database;

Managing Tables

Once you are USEing a database, you can create and manage tables in it.

Create a Table: This is where we specify our columns and their data types. Let’s create a table named students.

CREATE TABLE students (

    student_id INT PRIMARY KEY AUTO_INCREMENT,

    first_name VARCHAR(50) NOT NULL,

    last_name VARCHAR(50) NOT NULL,

    email VARCHAR(100) UNIQUE,

    enrollment_date DATE

);

Let’s understand these keywords:

• PRIMARY KEY: Used to uniquely identify each row in the table. No two rows can contain the same student_id.
• AUTO_INCREMENT: Automatically generates a sequential number for student_id when you add a new row.
• NOT NULL: Used to ensure that this column cannot contain a NULL (empty) value.
• UNIQUE: Makes sure every value in this column is unique. No two students can use the same email.

Show Tables: Observe a list of tables in your currently opened database.

SHOW TABLES;

Describe Table (or DESCRIBE): Observe the table structure (its columns, data types, etc.).

DESCRIBE students;

— OR

DESC students;

Alter Table (Add Column): Add a new column to a current table.

ALTER TABLE students

ADD COLUMN major VARCHAR(50);

Alter Table (Drop Column): Drop a column from a table.

ALTER TABLE students

DROP COLUMN major;

Drop Table (Use with Care!): Drops a whole table. Irreversible!

DROP TABLE students;

Related: Oracle Tutorial from Scratch.

Data Manipulation Language (DML): Dealing with Records

With a table in place, let’s see how to insert data into it, read it out, modify it, and remove it. These are commonly called CRUD operations (Create, Read, Update, Delete).

Creating Data (CREATE)

The INSERT INTO command inserts new rows (records) into a table.

INSERT INTO students (first_name, last_name, email, enrollment_date)

VALUES (‘Alice’, ‘Smith’, ‘alice.smith@example.com’, ‘2024-09-01’);

INSERT INTO students (first_name, last_name, email, enrollment_date)

VALUES (‘Bob’, ‘Johnson’, ‘bob.j@example.com’, ‘2023-01-15’);

INSERT INTO students (first_name, last_name, email, enrollment_date)

VALUES (‘Charlie’, ‘Brown’, ‘charlie.b@example.com’, ‘2024-03-20’);

— You can insert multiple rows at once:

INSERT INTO students (first_name, last_name, email, enrollment_date)

VALUES

    (‘Diana’, ‘Prince’, ‘diana.p@example.com’, ‘2024-10-01’),

    (‘Eve’, ‘Adams’, ‘eve.a@example.com’, ‘2023-05-10’);

See that we didn’t insert student_id since it’s AUTO_INCREMENT. MySQL takes care of it for us.

Reading Data (READ) using SELECT

The SELECT statement is used most commonly. It’s how you pull data out of your tables.

Select All Columns from a Table:

SELECT * FROM students;

This displays all columns and all rows in the students table.

Select Specific Columns:

SELECT first_name, last_name, email FROM students;

This only gets you the specified columns.

Filtering Data with WHERE Clause: 

The WHERE clause helps you to specify conditions to filter rows.

— Select students named ‘Alice’

SELECT * FROM students WHERE first_name = ‘Alice’;

— Select students enrolled after a specific date

SELECT * FROM students WHERE enrollment_date > ‘2024-01-01’;

— Select students with ‘example.com’ in their email

SELECT * FROM students WHERE email LIKE ‘%example.com%’;

Common Operators for WHERE:

• = (Equal to)
• != or <> (Not equal to)
• > (Greater than)
• < (Less than)
• >= (Greater than or equal to)
• <= (Less than or equal to)
• AND, OR, NOT (Logical operators)
• IN (Matches any value in a list: WHERE student_id IN (1, 3))
• BETWEEN (Within a range: WHERE enrollment_date BETWEEN ‘2023-01-01’ AND ‘2023-12-31’)
• LIKE (Pattern matching: % for any number of characters, _ for a single character)
• IS NULL, IS NOT NULL (Checks for null values)

Updating Data (UPDATE)

The UPDATE statement updates existing records in a table. Always use a WHERE clause when using UPDATE! Otherwise, you’ll update all the records in the table.

— Update Alice Smith’s email address

UPDATE students

SET email = ‘alice.new.email@example.com’

WHERE student_id = 1;

— Update multiple columns

UPDATE students

SET first_name = ‘Alfred’, last_name = ‘Pennyworth’

WHERE student_id = 2;

Deleting Data (DELETE)

The DELETE FROM statement deletes records from a table. Always use a WHERE clause when using DELETE! Without it, you will delete all the records from the table.

— Delete the student with student_id = 3

DELETE FROM students WHERE student_id = 3;

— Delete all students enrolled before 2024

DELETE FROM students WHERE enrollment_date < ‘2024-01-01’;

Suggested: Oracle SQL Course Online.

More Advanced SQL Concepts (for Beginners)

Once you feel at ease with CRUD operations, let’s discuss some more advanced SQL features.

Aggregate Functions

These functions perform calculations over a group of rows and return a single value.

COUNT(): Returns the number of rows.

SELECT COUNT(*) FROM students; — Total number of students

SELECT COUNT(email) FROM students; — Count of non-NULL emails

SUM(): Returns the sum of a numeric column.

(Let’s say we added a gpa column for this example)

— SELECT SUM(gpa) FROM students;

AVG(): Returns the average of a numeric column.

— SELECT AVG(gpa) FROM students;

MIN(): Returns the minimum in a column.

SELECT MIN(enrollment_date) FROM students;

MAX(): Returns the maximum value of a column.

SELECT MAX(enrollment_date) FROM students;

Grouping Data with GROUP BY

GROUP BY syntax is utilised with aggregate functions to group rows having the same values in certain columns into summary rows.

Suppose we include a column for major in our students table:

ALTER TABLE students ADD COLUMN major VARCHAR(50);

UPDATE students SET major = ‘Computer Science’ WHERE student_id IN (1, 4);

UPDATE students SET major = ‘Mathematics’ WHERE student_id = 2;

UPDATE students SET major = ‘Physics’ WHERE student_id = 5;

UPDATE students SET major = ‘Computer Science’ WHERE student_id = 6; — Assuming you added more data

Now, let’s count students by major:

SELECT major, COUNT(*) AS number_of_students

FROM students

GROUP BY major;

This will return a list of every unique major and the number of students in that major.

HAVING Groups Filtered

Whereas WHERE will filter individual rows prior to grouping, HAVING will filter groups after they have been created by GROUP BY.

— Show majors that have more than 1 student

SELECT major, COUNT(*) AS number_of_students

FROM students

GROUP BY major

HAVING COUNT(*) > 1;

Joining Tables (The Magic of Relational Databases)

This is where the real magic of relational databases is unleashed! JOIN clauses join rows from two or more tables on a related column between them.

Let us create another table named courses.

CREATE TABLE courses (

    course_id INT PRIMARY KEY AUTO_INCREMENT,

    course_name VARCHAR(100) NOT NULL,

    credits INT

);

INSERT INTO courses (course_name, credits) VALUES

(‘Introduction to Programming’, 3),

(‘Database Systems’, 4),

(‘Calculus I’, 3),

(‘Physics I’, 4);

Now, let’s define a student_courses table to connect students with courses (many-to-many relationship).

FOREIGN KEY: This constraint creates a connection between one table’s column and another table’s primary key. It maintains referential integrity – you cannot have an enrollment_id that points to a student_id or course_id that does not exist.

Now, let’s do a JOIN:

INNER JOIN: Retrieves rows when there’s a match in both tables.

SELECT

    s.first_name,

    s.last_name,

    c.course_name,

    sc.grade

FROM

    students s

INNER JOIN

    student_courses sc ON s.student_id = sc.student_id

INNER JOIN

    courses c ON sc.course_id = c.course_id;

Here, s, sc, and c are aliases (shorter names) for our tables in order to make the query easier to read. This query displays which student is taking which course and their grade.

Other JOIN types:

• LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table, and the corresponding rows from the right table. If there is no match, NULLs are returned for the columns in the right table.
• RIGHT JOIN (or RIGHT OUTER JOIN): It locates the common rows in the left table and every row in the right table. Where there isn’t a match, NULLs are returned for the columns in the left table.
• FULL JOIN (or FULL OUTER JOIN): Finds rows when both tables match. (MySQL does not support FULL JOIN natively but you can do it with UNION of LEFT JOIN and RIGHT JOIN).

Upgrade your skills with our Oracle DBA course online.

Understanding Keys and Indexes

• Primary Key: As mentioned, a unique identifier for every row in a table. Critical for linking tables.
• Foreign Key: A column (or combination of columns) within a table that refers to the primary key within another table. Maintains referential integrity.
• Indexes: Separate lookup operations that the database search engine can utilize to speed up access to data. Consider separating them from your data like an index at the back of a book – they make it much quicker to locate information.

— Create an index on the last_name column for faster searches

CREATE INDEX idx_last_name ON students (last_name);

Common Pitfalls and Best Practices for New Users

• Always employ a WHERE clause: Seriously, when UPDATEing or DELETEing.
• Back up your data: Particularly before executing major DELETE or DROP statements.
• Understand your data types: Using the right data type saves storage and enhances performance.
• Normalize your database: Split big tables into smaller, related tables to minimize data redundancy and enhance data integrity. (e.g., splitting students from courses through student_courses).
• Use meaningful names: For databases, tables, and columns.
• Comment your SQL (if using in scripts): Use — for one-line comments or /* */ for multiple lines.
• Learn to read error messages: They are goldmines of clues to correct your queries.
• Practice, practice, practice! The more SQL you write, the better.

Beyond the Basics: What’s Next?

You now have a good grounding in MySQL. Here’s an idea of what you can learn further:

• Advanced SQL Queries: Subqueries, Common Table Expressions (CTEs), Window Functions.
• Database Design: Normalization forms (1NF, 2NF, 3NF, BCNF).
• Stored Procedures and Functions: Reusable segments of SQL code.
• Triggers: Events that automatically execute when a particular occurrence happens (e.g., an insert).
• Views: Virtual tables on the basis of the result-set of a SQL query.
• User Management and Permissions: Granting or revoking privileges to various database users.
• Database Backup and Recovery: Critical for data security.
• Performance Tuning: Optimizing your queries and database design for speed.
• Integrating MySQL to Programming Languages: Working with MySQL and Python (PyMySQL), PHP (PDO/MySQLi), Node.js, Java, etc.

Explore: All trending software training courses.

Conclusion

Great job! You have made your first important steps in the realm of database management using MySQL. You’ve learned to establish your environment, create and administer databases and tables, and execute basic data manipulation operations with SQL. This lays the foundation for anyone dealing with data-based applications.

Ready to speed up your learning and become a MySQL master? Take our in-depth MySQL Course Online today and unlock advanced methods, real-world projects, and expert advice!