MySQL Basics: A Beginner’s Guide

Introduction

Welcome to this beginner-friendly MySQL tutorial! This guide will walk you through essential MySQL commands, including creating tables, modifying schema, inserting data, applying constraints, and working with queries. To make learning more engaging, we’ve included examples using characters from Friends and Naruto.

---

1. Creating a Table and Inserting Data Types

To create a table in MySQL, use the CREATE TABLE statement.

CREATE TABLE workers (
    EmployeeID INT,
    FirstName VARCHAR(20),
    LastName VARCHAR(20),
    HourlyPay DECIMAL(5,2),
    HireDate DATE
);

• INT: Stores whole numbers (e.g., Employee ID)
• VARCHAR(n): Stores variable-length text up to n characters (e.g., Names)
• DECIMAL(5,2): Stores numbers with precision (e.g., salaries)
• DATE: Stores date values (e.g., hire dates)

To view the structure of a table:

DESCRIBE workers;

To see the table contents:

SELECT * FROM workers;

---

2. Renaming and Deleting Tables

You can rename an existing table:

RENAME TABLE workers TO employees;

To permanently delete a table:

DROP TABLE employees;

---

3. Modifying Tables

Adding a Column

ALTER TABLE employees
ADD COLUMN PhoneNumber BIGINT;

Renaming a Column

ALTER TABLE employees
RENAME COLUMN PhoneNumber TO Email;

Changing Data Types

ALTER TABLE employees
MODIFY COLUMN Email VARCHAR(50);

Changing Column Position

ALTER TABLE employees
MODIFY COLUMN Email VARCHAR(50) FIRST;

ALTER TABLE employees
MODIFY COLUMN Email VARCHAR(50) AFTER LastName;

Deleting a Column

ALTER TABLE employees
DROP COLUMN Email;

---

4. Inserting Data into Tables

Inserting a Single Row

INSERT INTO employees
VALUES (1, 'Rachel', 'Green', 25.50, '1969-02-11');

Inserting Multiple Rows

INSERT INTO employees
VALUES
    (2, 'Phoebe', 'Buffay', 15.00, '1963-07-30'),
    (3, 'Chandler', 'Bing', 12.50, '1969-08-19'),
    (4, 'Ross', 'Geller', 12.50, '1966-11-02'),
    (5, 'Monica', 'Geller', 17.25, '1964-06-15');

Deleting a Record

DELETE FROM employees
WHERE EmployeeID = 6;

Inserting Data into Specific Columns

INSERT INTO employees (EmployeeID, FirstName, LastName)
VALUES (6, 'Joey', 'Tribbiani');

---

5. Querying Data

Viewing Specific Columns

SELECT FirstName, LastName FROM employees;

Filtering Data

SELECT * FROM employees WHERE LastName = 'Geller';

SELECT LastName, FirstName FROM employees WHERE HourlyPay >= 15;

SELECT * FROM employees WHERE EmployeeID != 1;

---

6. Constraints

Unique Constraint

CREATE TABLE PRODUCT (
    ID INT,
    NAME VARCHAR(50) UNIQUE,
    PRICE DECIMAL(4,2)
);

Not Null Constraint

CREATE TABLE PRODUCE (
    ID CHAR(5),
    NAME VARCHAR(10),
    PRICE DECIMAL(4,2) NOT NULL
);

---

7. Default Values

CREATE TABLE Products (
    ProductID INT,
    ProductName VARCHAR(10),
    Price DECIMAL(4,2) DEFAULT 0.00
);

---

8. Primary Keys

Adding a Primary Key to an Existing Table

ALTER TABLE Transactions1 ADD PRIMARY KEY (ID);

Creating a Table with a Primary Key

CREATE TABLE Transactions2 (
    ID INT PRIMARY KEY,
    Amount DECIMAL(5,2)
);

Auto Increment

ALTER TABLE Transactions3 AUTO_INCREMENT = 11;

---

9. Foreign Keys

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY AUTO_INCREMENT,
    FirstName VARCHAR(10),
    LastName VARCHAR(10)
);

CREATE TABLE Transactions (
    TransactionID INT PRIMARY KEY AUTO_INCREMENT,
    Amount DECIMAL(5,2),
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

---

10. Check Constraint

ALTER TABLE Characters
ADD CONSTRAINT chkHourlyPay CHECK (HourlyPay >= 10.00);

To delete the constraint:

ALTER TABLE Characters DROP CHECK chkHourlyPay;

---

11. Practical Example: Naruto and Friends Characters

CREATE TABLE Characters (
    CharacterID INT PRIMARY KEY,
    FirstName VARCHAR(25),
    LastName VARCHAR(25),
    HourlyPay DECIMAL(5,2),
    HireDate DATE
);

INSERT INTO Characters VALUES
    (1, 'Naruto', 'Uzumaki', 15.00, '1999-10-10'),
    (2, 'Hinata', 'Hyuga', 15.00, '1998-12-27'),
    (3, 'Sasuke', 'Uchiha', 12.50, '1999-07-23'),
    (4, 'Sakura', 'Haruno', 12.50, '1998-03-28'),
    (5, 'Kakashi', 'Hatake', 17.25, '1990-09-15');

To apply a constraint:

ALTER TABLE Characters
ADD CONSTRAINT chkHourlyPay CHECK (HourlyPay >= 10.00);

---

Conclusion

This guide provides a strong foundation in MySQL by covering the basics of table creation, data insertion, constraints, and queries. Continue practicing by experimenting with different datasets and queries to strengthen your skills!