MySQL Database

Learn MySQL - the world's most popular open-source database

Introduction to MySQL

MySQL is an open-source relational database management system (RDBMS) based on Structured Query Language (SQL). It is widely used for web applications and is one of the most popular database systems in the world.

Key Features of MySQL:

Open Source - Free to use and modify
High Performance - Optimized for speed and reliability
Scalability - Handles large databases efficiently
Cross-Platform - Runs on various operating systems
Replication Support - Enables high availability

MySQL Installation and Setup

Installation Commands

# On Ubuntu/Debian
sudo apt-get update
sudo apt-get install mysql-server

# On CentOS/RHEL
sudo yum install mysql-server

# On Windows
# Download from official MySQL website and run installer

Starting MySQL Service

# Start MySQL service
sudo systemctl start mysql

# Enable MySQL service to start on boot
sudo systemctl enable mysql

# Connect to MySQL
mysql -u root -p

MySQL Specific Commands

Database Operations

-- Create Database
CREATE DATABASE database_name;

-- Use Database
USE database_name;

-- Show Databases
SHOW DATABASES;

-- Drop Database
DROP DATABASE database_name;

Table Operations

-- Create Table
CREATE TABLE table_name (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Show Tables
SHOW TABLES;

-- Describe Table
DESCRIBE table_name;

-- Drop Table
DROP TABLE table_name;

MySQL Data Types

-- Numeric Types
INT, TINYINT, SMALLINT, MEDIUMINT, BIGINT
DECIMAL(M,D), FLOAT, DOUBLE

-- Date and Time Types
DATE, TIME, DATETIME, TIMESTAMP, YEAR

-- String Types
CHAR(n), VARCHAR(n), TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT
BINARY, VARBINARY, BLOB, MEDIUMBLOB, LONGBLOB

MySQL Functions and Features

String Functions

-- Common String Functions
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
SELECT UPPER(name) FROM products;
SELECT LOWER(name) FROM products;
SELECT LENGTH(name) FROM products;
SELECT SUBSTRING(name, 1, 5) FROM products;
SELECT REPLACE(name, 'old', 'new') FROM products;

Date and Time Functions

-- Common Date Functions
SELECT NOW(); -- Current date and time
SELECT CURDATE(); -- Current date
SELECT CURTIME(); -- Current time
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); -- Formatted date
SELECT DATEDIFF('2023-12-31', '2023-01-01'); -- Difference in days
SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH); -- Add 1 month

MySQL Specific Functions

-- MySQL Specific Functions
SELECT UUID(); -- Generate unique identifier
SELECT IF(condition, value_if_true, value_if_false);
SELECT CASE WHEN condition THEN result1 ELSE result2 END;
SELECT COALESCE(value1, value2, value3); -- Returns first non-null value
SELECT IFNULL(value, replacement); -- MySQL specific null check

MySQL Indexes and Performance

Creating Indexes

-- Create Index
CREATE INDEX idx_lastname ON users(last_name);

-- Create Unique Index
CREATE UNIQUE INDEX idx_email ON users(email);

-- Create Composite Index
CREATE INDEX idx_name_age ON users(first_name, last_name, age);

-- Drop Index
DROP INDEX idx_lastname ON users;

MySQL Performance Optimization

-- Analyze Query Performance
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';

-- Show Process List
SHOW PROCESSLIST;

-- View Indexes
SHOW INDEX FROM table_name;

-- Optimize Table
OPTIMIZE TABLE table_name;

Performance Tips:

Use indexes wisely - they speed up SELECT but slow down INSERT/UPDATE/DELETE
Use LIMIT to restrict result sets
Avoid SELECT * - specify only needed columns
Use EXPLAIN to analyze query performance
Consider partitioning for large tables

MySQL Stored Procedures and Functions

Creating Stored Procedures

-- Create a stored procedure
DELIMITER $$
CREATE PROCEDURE GetUsersByCity(IN city_name VARCHAR(255))
BEGIN
    SELECT * FROM users WHERE city = city_name;
END$$
DELIMITER ;

-- Call the procedure
CALL GetUsersByCity('New York');

Creating Functions

-- Create a function
DELIMITER $$
CREATE FUNCTION CalculateAge(birth_date DATE)
RETURNS INT
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE age INT;
    SET age = TIMESTAMPDIFF(YEAR, birth_date, CURDATE());
    RETURN age;
END$$
DELIMITER ;

-- Use the function
SELECT name, CalculateAge(birth_date) AS age FROM users;