Introduction to MySQL

MySQL is an open-source relational database management system (RDBMS) that uses Structured Query Language (SQL). It's one of the most popular database systems for web applications.

Key Features

Relational database system
Client-server architecture
ACID compliant (Atomicity, Consistency, Isolation, Durability)
Supports multiple storage engines
Cross-platform compatibility
High performance and scalability

Installation

MySQL can be installed on various operating systems. Here's how to install it on different platforms:

Windows

1. Download MySQL Installer from official website
    2. Run the installer and follow the setup wizard
    3. Choose setup type (Developer Default recommended)
    4. Configure root password and authentication method

Linux (Ubuntu/Debian)

sudo apt update
    sudo apt install mysql-server
    sudo mysql_secure_installation

DDL Commands

Data Definition Language (DDL) commands are used to define the database structure.

CREATE

Creates a new database or database object (table, view, etc.)

CREATE DATABASE database_name;
    CREATE TABLE table_name (
        column1 datatype constraints,
        column2 datatype constraints,
        ...
    );

ALTER

Modifies an existing database object

ALTER TABLE table_name ADD column_name datatype;
    ALTER TABLE table_name DROP COLUMN column_name;
    ALTER TABLE table_name MODIFY COLUMN column_name new_datatype;

DROP

Deletes an existing database object

DROP TABLE table_name;
    DROP DATABASE database_name;

TRUNCATE

Removes all records from a table but keeps the structure

TRUNCATE TABLE table_name;

DML Commands

Data Manipulation Language (DML) commands are used to manage data within database objects.

INSERT

Adds new records to a table

INSERT INTO table_name (column1, column2, ...)
    VALUES (value1, value2, ...);

UPDATE

Modifies existing records in a table

UPDATE table_name
    SET column1 = value1, column2 = value2, ...
    WHERE condition;

DELETE

Removes records from a table

DELETE FROM table_name WHERE condition;

SELECT & WHERE

The SELECT statement is used to query data from a database.

Basic SELECT

SELECT column1, column2, ... FROM table_name;
    SELECT * FROM table_name; -- Selects all columns

WHERE Clause

Filters records based on specified conditions

SELECT column1, column2, ...
    FROM table_name
    WHERE condition;

Examples

-- Select all customers from USA
    SELECT * FROM customers WHERE country = 'USA';
    
    -- Select products with price greater than 100
    SELECT product_name, price FROM products WHERE price > 100;

Joins

Joins are used to combine rows from two or more tables based on related columns.

INNER JOIN

Returns records that have matching values in both tables

SELECT a.column1, b.column2
    FROM table1 a
    INNER JOIN table2 b ON a.common_field = b.common_field;

LEFT JOIN

Returns all records from the left table and matched records from the right table

SELECT a.column1, b.column2
    FROM table1 a
    LEFT JOIN table2 b ON a.common_field = b.common_field;

RIGHT JOIN

Returns all records from the right table and matched records from the left table

SELECT a.column1, b.column2
    FROM table1 a
    RIGHT JOIN table2 b ON a.common_field = b.common_field;

FULL JOIN

Returns all records when there's a match in either left or right table

SELECT a.column1, b.column2
    FROM table1 a
    FULL JOIN table2 b ON a.common_field = b.common_field;

Keys in MySQL

Keys are used to establish relationships between tables and uniquely identify records.

Primary Key

Uniquely identifies each record in a table (cannot be NULL)

CREATE TABLE customers (
        customer_id INT PRIMARY KEY,
        name VARCHAR(100)
    );

Foreign Key

Maintains referential integrity between two tables

CREATE TABLE orders (
        order_id INT PRIMARY KEY,
        customer_id INT,
        order_date DATE,
        FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    );

Unique Key

Ensures all values in a column are different (can be NULL)

CREATE TABLE users (
        user_id INT PRIMARY KEY,
        email VARCHAR(255) UNIQUE
    );

Operators in MySQL

Comparison Operators

= (Equal)
> (Greater than)
< (Less than)
>= (Greater than or equal)
<= (Less than or equal)
<> or != (Not equal)

Logical Operators

AND
OR
NOT
IN
BETWEEN
LIKE
IS NULL

Arithmetic Operators

+ (Addition)
- (Subtraction)
* (Multiplication)
/ (Division)
% (Modulus)

MySQL Functions

String Functions

CONCAT() - Concatenates strings
SUBSTRING() - Extracts part of a string
UPPER()/LOWER() - Converts case
TRIM() - Removes leading/trailing spaces
LENGTH() - Returns string length

Numeric Functions

ABS() - Absolute value
ROUND() - Rounds a number
CEIL()/FLOOR() - Rounds up/down
MOD() - Remainder of division
RAND() - Random number

Date Functions

NOW() - Current date and time
CURDATE() - Current date
CURTIME() - Current time
DATE_FORMAT() - Formats date
DATEDIFF() - Difference between dates

Aggregate Functions

COUNT() - Counts rows
SUM() - Sum of values
AVG() - Average of values
MIN()/MAX() - Minimum/Maximum value
GROUP_CONCAT() - Concatenates group values

Transactions

Transactions ensure that a sequence of SQL operations execute completely or not at all.

ACID Properties

Atomicity - All operations complete successfully or none do
Consistency - Database remains in a consistent state
Isolation - Transactions don't interfere with each other
Durability - Completed transactions persist even after system failure

Transaction Commands

START TRANSACTION;
    -- SQL statements
    COMMIT; -- Save changes
    -- OR
    ROLLBACK; -- Undo changes

Example

START TRANSACTION;
    
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
    
    COMMIT;