Session 5.1 – Introduction to MySQL

Module 5: PHP Database Connectivity | Duration: 1 hr

Learning Objectives

By the end of this session, students will be able to:

  • Understand fundamental database concepts and terminology
  • Explain the characteristics of Relational Database Management Systems (RDBMS)
  • Identify the features and advantages of MySQL
  • Install and configure MySQL server
  • Execute basic MySQL commands and queries
  • Understand MySQL data types and their usage

Introduction to MySQL

MySQL is the world's most popular open-source relational database management system (RDBMS). It provides a robust, scalable, and high-performance solution for storing and managing data in web applications.

Why MySQL?

MySQL powers many of the world's most popular websites and applications, including Facebook, Twitter, YouTube, and Wikipedia. Its reliability, ease of use, and strong community support make it an excellent choice for web developers.

What is a Database?

A database is an organized collection of structured data, typically stored electronically in a computer system. Databases allow for efficient storage, retrieval, and manipulation of data.

Advantages of Databases
  • Data persistence and durability
  • Concurrent access by multiple users
  • Data integrity and consistency
  • Security and access control
  • Efficient querying and retrieval
  • Backup and recovery mechanisms
Database Components
  • Tables: Store data in rows and columns
  • Records: Individual entries in a table
  • Fields: Columns in a table
  • Keys: Unique identifiers
  • Relationships: Connections between tables
  • Constraints: Rules for data integrity

Relational Database Management System (RDBMS)

An RDBMS is a database management system based on the relational model, where data is organized into tables (relations) with rows and columns.

Key Characteristics of RDBMS
  • Tables: Data is stored in tables with predefined schemas
  • Relationships: Tables can be related through foreign keys
  • SQL: Structured Query Language for data manipulation
  • ACID Properties: Atomicity, Consistency, Isolation, Durability
  • Normalization: Process of organizing data to reduce redundancy
Example: Users Table
user_id username email created_at
1 john_doe john@example.com 2024-01-15
2 jane_smith jane@example.com 2024-01-16

MySQL Overview

MySQL is developed, distributed, and supported by Oracle Corporation. It is widely used for web applications and is a central component of the LAMP stack (Linux, Apache, MySQL, PHP/Python/Perl).

Key Features
  • Open-source and free
  • Cross-platform support
  • High performance
  • Scalability
  • Strong security
  • Replication support
Performance
  • Fast data retrieval
  • Efficient indexing
  • Query optimization
  • Caching mechanisms
  • Multi-threading
  • Load balancing
Security
  • User authentication
  • Access control
  • SSL support
  • Data encryption
  • Audit logging
  • Password policies

Installation & Setup

MySQL can be installed on various platforms. Here are the basic steps for common operating systems:

Windows Installation
  1. Download MySQL Installer from MySQL Downloads
  2. Run the installer and choose setup type (Developer Default recommended)
  3. Configure MySQL Server (port 3306 by default)
  4. Set root password and create user accounts
  5. Complete installation and start MySQL service
Linux Installation (Ubuntu/Debian)

# Update package index
sudo apt update

# Install MySQL Server
sudo apt install mysql-server

# Start MySQL service
sudo systemctl start mysql

# Enable MySQL to start on boot
sudo systemctl enable mysql

# Secure MySQL installation
sudo mysql_secure_installation
macOS Installation

# Using Homebrew
brew install mysql

# Start MySQL
brew services start mysql

# Secure installation
mysql_secure_installation

Basic MySQL Commands

Once MySQL is installed, you can interact with it using the command-line client or GUI tools like MySQL Workbench.

Connecting to MySQL

# Connect as root user
mysql -u root -p

# Connect to specific database
mysql -u username -p database_name

# Connect to remote server
mysql -h hostname -u username -p
Database Operations

-- Show all databases
SHOW DATABASES;

-- Create a new database
CREATE DATABASE mywebsite;

-- Select a database to use
USE mywebsite;

-- Show current database
SELECT DATABASE();

-- Drop a database
DROP DATABASE mywebsite;
Table Operations

-- Show all tables in current database
SHOW TABLES;

-- Show table structure
DESCRIBE users;
-- or
SHOW COLUMNS FROM users;

-- Show table creation statement
SHOW CREATE TABLE users;
User Management

-- Show current user
SELECT USER();

-- Show all users
SELECT user, host FROM mysql.user;

-- Show MySQL version
SELECT VERSION();

-- Show server status
SHOW STATUS;

MySQL Data Types

MySQL supports various data types for storing different kinds of information. Choosing the right data type is crucial for database performance and data integrity.

String Data Types
Type Description Max Size
CHAR(n) Fixed-length string 255 chars
VARCHAR(n) Variable-length string 65,535 chars
TEXT Long text 65,535 chars
MEDIUMTEXT Medium text 16,777,215 chars
LONGTEXT Very long text 4,294,967,295 chars
Numeric Data Types
Type Description Range
INT Integer -2B to 2B
BIGINT Large integer -9E18 to 9E18
DECIMAL(m,d) Exact decimal Variable
FLOAT Floating point Small decimals
DOUBLE Double precision Large decimals
Date and Time Types
Type Description Format
DATE Date only YYYY-MM-DD
TIME Time only HH:MM:SS
DATETIME Date and time YYYY-MM-DD HH:MM:SS
TIMESTAMP Unix timestamp YYYY-MM-DD HH:MM:SS
YEAR Year only YYYY
Other Data Types
Type Description Usage
BOOLEAN True/False 0 or 1
ENUM Enumeration Predefined values
SET Set of values Multiple choices
BLOB Binary data Images, files
JSON JSON data Structured data
Best Practices for Data Types
  • Use the most appropriate and smallest data type that fits your data
  • Use VARCHAR instead of CHAR for variable-length strings
  • Use INT for primary keys with AUTO_INCREMENT
  • Use DECIMAL for monetary values (exact precision)
  • Use TIMESTAMP for automatic timestamp updates
  • Use ENUM for columns with a fixed set of values

Session Summary

Key Points
  • Databases provide organized, persistent, and efficient data storage
  • RDBMS organizes data into tables with relationships and uses SQL
  • MySQL is a popular, open-source, cross-platform RDBMS
  • MySQL offers high performance, scalability, and strong security features
  • Basic MySQL commands include database creation, selection, and information retrieval
  • Choosing appropriate data types is essential for performance and data integrity
  • MySQL supports various data types: numeric, string, date/time, and special types
Next Session Preview

In the next session, we will explore DDL (Data Definition Language) commands including CREATE, ALTER, and DROP statements for managing database structures.

Previous Session Course Home