Comprehensive MySQL Statements Reference Guide

This document serves as a comprehensive reference for various MySQL statements, essential for managing and manipulating databases. Here’s a detailed breakdown of the main concepts:

Key Concepts

• SQL (Structured Query Language): The language used to communicate with databases, allowing users to perform actions like querying, updating, and managing data.
• MySQL: An open-source relational database management system (RDBMS) that utilizes SQL to manage its data.

Types of SQL Statements

1. Data Query Language (DQL)

• Purpose: Retrieve data from databases.
• Key Statement: SELECT
  • Example: SELECT * FROM users; (Fetches all records from the users table)

2. Data Manipulation Language (DML)

• Purpose: Manipulate data in the database.
• Key Statements:
  • INSERT: Add new records.
    • Example: INSERT INTO users (name, age) VALUES ('Alice', 30);
  • UPDATE: Modify existing records.
    • Example: UPDATE users SET age = 31 WHERE name = 'Alice';
  • DELETE: Remove records.
    • Example: DELETE FROM users WHERE name = 'Alice';

3. Data Definition Language (DDL)

• Purpose: Define and manage all database objects.
• Key Statements:
  • CREATE: Create new tables or databases.
    • Example: CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), age INT);
  • ALTER: Modify existing database objects.
    • Example: ALTER TABLE users ADD email VARCHAR(100);
  • DROP: Delete tables or databases.
    • Example: DROP TABLE users;

4. Data Control Language (DCL)

• Purpose: Control access to data within the database.
• Key Statements:
  • GRANT: Give users access privileges.
    • Example: GRANT SELECT ON users TO 'username';
  • REVOKE: Take back access privileges.
    • Example: REVOKE SELECT ON users FROM 'username';

5. Transaction Control Language (TCL)

• Purpose: Manage transactions in the database.
• Key Statements:
  • COMMIT: Save changes made during the transaction.
    • Example: COMMIT;
  • ROLLBACK: Undo changes made during the transaction.
    • Example: ROLLBACK;

Conclusion

Understanding these foundational SQL statements is crucial for beginners learning to interact with MySQL databases. By mastering these commands, users can effectively create, read, update, and delete data as needed.