MYSQL Commands Made Simple: A Beginners Guide

MySQL is a powerful open-source database management system, widely used for storing and managing data. This detailed guide walks you through essential MySQL commands to help you get started.

---

1. Create a Database

Databases store tables and data. To create one:

CREATE DATABASE my_database;

• CREATE DATABASE is the command to create a new database.
• Replace my_database with the name you want for your database.

Example:

CREATE DATABASE school_management;

This command creates a database named school_management.

---

2. Use a Database

After creating a database, you must select it to work with it.

USE my_database;

• USE tells MySQL which database to operate on.

Example:

USE school_management;

This command sets the school_management database as active for further operations.

---

3. Create a Table

Tables organize data within a database. To create one:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    position VARCHAR(50),
    salary DECIMAL(10,2)
);

• CREATE TABLE defines a new table named employees.
• Columns include:
  • id: Integer type, automatically increments, and serves as the primary key.
  • name: Text type with a maximum of 50 characters.
  • position: Text type with a maximum of 50 characters.
  • salary: Decimal type with 10 digits in total, 2 of which are after the decimal point.

Example:

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    grade VARCHAR(10)
);

This creates a table students to store information about students.

---

4. Describe a Table

To see the structure of a table:

DESC table_name;

• DESC (short for DESCRIBE) lists the table’s columns, data types, and constraints.

Example:

DESC employees;

This command displays the structure of the employees table.

---

5. Insert Data into a Table

To add records to a table:

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

• Specify the table name and columns to populate.
• Use VALUES to provide the corresponding data.

Example:

INSERT INTO employees (name, position, salary)
VALUES ('Alice', 'Manager', 75000.00),
       ('Bob', 'Developer', 60000.00);

This adds two rows to the employees table.

Explanation:

• The first row contains Alice, her position Manager, and her salary 75000.00.
• The second row contains Bob, his position Developer, and his salary 60000.00.

---

6. Select Data from a Table

To retrieve data:

SELECT column1, column2 FROM table_name;

• SELECT specifies the columns to fetch.
• * fetches all columns.

Example 1: Fetch All Data

SELECT * FROM employees;

This retrieves all rows and columns from the employees table.

Example 2: Fetch Specific Data

SELECT name, position FROM employees;

This retrieves only the name and position columns.

---

7. Update Data in a Table

To modify existing data:

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

• UPDATE specifies the table to update.
• SET defines new values for the columns.
• WHERE limits the update to matching rows.

Example:

UPDATE employees
SET salary = 80000.00
WHERE name = 'Alice';

This updates Alice’s salary to 80000.00 in the employees table.

---

8. Delete Data from a Table

To remove records:

DELETE FROM table_name
WHERE condition;

• DELETE FROM specifies the table.
• WHERE defines which rows to delete.

Example 1: Delete Specific Row

DELETE FROM employees
WHERE name = 'Bob';

This deletes Bob’s record from the employees table.

Example 2: Delete All Rows

DELETE FROM employees;

(Use this carefully—it removes all data but keeps the table structure.)

---

9. Best Practices

• Always back up your database before running DELETE or UPDATE commands.
• Use WHERE with UPDATE and DELETE to avoid unintended changes.
• Regularly check table structures with DESC to ensure your design meets requirements.

---

Quick Recap:

• Create a Database: CREATE DATABASE database_name;
• Use a Database: USE database_name;
• Create a Table: CREATE TABLE table_name (...);
• Describe a Table: DESC table_name;
• Insert Data: INSERT INTO table_name (...) VALUES (...);
• Select Data: SELECT ... FROM table_name;
• Update Data: UPDATE table_name SET ... WHERE ...;
• Delete Data: DELETE FROM table_name WHERE ...;

---

This detailed guide equips you with foundational MySQL commands. As you practice, you’ll gain confidence in managing databases efficiently. Happy coding!