MySQL Introduction

-- Create a database
CREATE DATABASE myapp;
USE myapp;

-- Create a table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(200) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    age INT,
    city VARCHAR(100),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Show tables
SHOW TABLES;

-- Describe table structure
DESCRIBE users;
SHOW CREATE TABLE users;

-- CREATE (INSERT)
INSERT INTO users (name, email, password_hash, age, city)
VALUES ('Alice', 'alice@example.com', 'hash123', 28, 'New York');

-- INSERT multiple rows
INSERT INTO users (name, email, password_hash, age, city) VALUES
('Bob', 'bob@example.com', 'hash456', 35, 'London'),
('Charlie', 'charlie@example.com', 'hash789', 22, 'Tokyo'),
('Diana', 'diana@example.com', 'hash012', 31, 'New York');

-- INSERT with SET (alternative syntax)
INSERT INTO users SET
    name = 'Eve',
    email = 'eve@example.com',
    password_hash = 'hash345';

-- SELECT (READ)
SELECT * FROM users;
SELECT name, email FROM users WHERE age > 25;
SELECT DISTINCT city FROM users;
SELECT name, age FROM users ORDER BY age DESC LIMIT 10;

-- UPDATE
UPDATE users
SET city = 'San Francisco', age = 29
WHERE id = 1;

-- UPDATE with conditions
UPDATE users
SET is_active = FALSE
WHERE last_login < '2023-01-01' AND is_active = TRUE;

-- DELETE
DELETE FROM users WHERE id = 5;

-- DELETE with conditions
DELETE FROM users WHERE is_active = FALSE AND created_at < '2022-01-01';

-- Comparisons
WHERE age = 25
WHERE age != 25
WHERE age > 25
WHERE age >= 25
WHERE age BETWEEN 25 AND 35
WHERE city IN ('New York', 'London', 'Tokyo')
WHERE city NOT IN ('Paris')

-- Pattern matching
WHERE name LIKE 'A%'          -- Starts with A
WHERE name LIKE '%son'        -- Ends with son
WHERE name LIKE '%li%'        -- Contains li
WHERE name LIKE '_lice'       -- Exactly 5 chars, starts with _

-- NULL checks
WHERE age IS NULL
WHERE age IS NOT NULL

-- Logical operators
WHERE age > 25 AND city = 'New York'
WHERE age < 25 OR city = 'Tokyo'
WHERE NOT is_active = FALSE

-- Numbers
TINYINT          -- 1 byte (-128 to 127)
SMALLINT         -- 2 bytes
INT              -- 4 bytes (-2B to 2B)
BIGINT           -- 8 bytes (very large numbers)
DECIMAL(10,2)    -- Exact decimal (10 digits, 2 after point)
FLOAT            -- Approximate decimal
DOUBLE           -- Higher precision float

-- Text
CHAR(10)         -- Fixed-length string (always 10 chars)
VARCHAR(100)     -- Variable-length string (up to 100 chars)
TEXT             -- Long text (up to 65,535 chars)
MEDIUMTEXT       -- Longer text (up to 16M chars)
LONGTEXT         -- Very long text (up to 4G chars)

-- Date/Time
DATE             -- 2024-01-15
TIME             -- 14:30:00
DATETIME         -- 2024-01-15 14:30:00
TIMESTAMP        -- Automatic timezone handling
YEAR             -- 2024

-- Boolean
BOOLEAN          -- Alias for TINYINT(1), TRUE=1, FALSE=0