Comprehensive MySQL Tutorial: Basics, Commands, Data Types, Constraints, Functions, Joins, Stored Procedures, and Transactions

Introduction

MySQL is one of the most popular relational database management systems and is widely used in web applications. This guide walks through all aspects of MySQL.

1. MySQL Basics

A database is a repository that stores data according to a defined structure. MySQL provides tools such as MySQL Workbench and SQLyog for installation, configuration, and basic operations.

2. MySQL Directory Structure

bin – executable files

include – header files

lib – library files

share – error messages, character sets, etc.

data – logs and database files

my.ini – configuration file

3. Common Commands

mysqladmin -u username -p old_password password new_password

show databases;

use database_name;

select database();
select version();
select now();
select user();

4. Database Operations (Create, Alter, Drop)

create database [if not exists] db_name;

alter database db_name default character set charset_name;

drop database [if exists] db_name;

5. Data Types

Integer types: TINYINT , SMALLINT , MEDIUMINT , INT , BIGINT . Floating‑point: FLOAT , DOUBLE , DECIMAL . Date/Time and character types are also covered.

6. Table Structure Operations

create table table_name (
  column1 datatype [constraints],
  column2 datatype [constraints],
  ...
);

show tables [from db_name];

describe table_name;

alter table table_name add column_name datatype [constraints];

alter table table_name drop column column_name;

drop table if exists table_name;

7. Constraints

Common constraints include NOT NULL , PRIMARY KEY , UNIQUE , DEFAULT , and FOREIGN KEY . Their creation, modification, and removal are demonstrated with examples.

8. Subqueries

select * from table1 where col1 = (select col2 from table2);

9. Operators and Functions

Arithmetic, comparison, logical, and bitwise operators are listed. Numeric functions such as CEIL , FLOOR , ROUND , TRUNCATE , and MOD are shown, as well as string functions ( CONCAT , LOWER , UPPER , SUBSTRING , REPLACE ) and date/time functions ( CURDATE , NOW , DATE_ADD , DATEDIFF , DATE_FORMAT ).

10. Aggregate Functions

Examples of AVG() , COUNT() , MAX() , MIN() , and SUM() are provided.

11. Multi‑Table Joins

select book_id, book_name, category
from bookinfo
inner join bookcategory on bookinfo.book_category_id = bookcategory.category_id;

Inner join, left/right outer join, and self‑join examples are included.

12. User‑Defined Functions

delimiter //
CREATE FUNCTION ym_date(mydate DATE) RETURNS VARCHAR(15)
BEGIN
  RETURN DATE_FORMAT(mydate, '%Y-%m');
END//
delimiter ;
SELECT ym_date(pubdate) FROM bookinfo;

13. Stored Procedures

delimiter //
CREATE PROCEDURE selectproc1()
BEGIN
  SELECT book_id, book_name, price, store FROM bookinfo;
END//
delimiter ;
CALL selectproc1();

Procedures with IN, OUT, and INOUT parameters, as well as examples of updating multiple tables in a single procedure, are shown.

14. Transactions

Transaction control statements ( START TRANSACTION , COMMIT , ROLLBACK , SAVEPOINT ) and the use of InnoDB as a transactional storage engine are explained.

15. Management and Maintenance

Creating and dropping users, granting privileges, and managing logs are demonstrated with CREATE USER , DROP USER , and FLUSH LOGS commands.