Detailed Explanation and Application Scenarios of DDL, DML, DQL, and DCL Database Languages

I. Conceptual Description
Database operation languages are the core components of SQL, divided into four categories based on functionality:

1. DDL (Data Definition Language): Used to define or modify database structures (such as tables, indexes), including statements like CREATE, ALTER, and DROP.
2. DML (Data Manipulation Language): Used to manipulate data within tables (insert, update, delete), such as INSERT, UPDATE, and DELETE.
3. DQL (Data Query Language): Specifically refers to the SELECT statement, used for querying data.
4. DCL (Data Control Language): Controls data access permissions, such as GRANT for authorization and REVOKE for revoking permissions.

II. DDL Detailed Explanation and Examples

1. CREATE: Creates database objects.

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

   • Key Points: Defines column data types and constraints (such as PRIMARY KEY, NOT NULL).

2. ALTER: Modifies table structure.

   ALTER TABLE employees ADD COLUMN department VARCHAR(20);  
   ALTER TABLE employees MODIFY COLUMN salary INT; -- Modifies data type  
   

   • Note: Modifying data types may lead to data loss; caution is required.

3. DROP: Deletes objects.

   DROP TABLE employees; -- Permanently deletes both data and structure  
   

   • Difference from TRUNCATE (DML): DROP deletes the structure, while TRUNCATE only clears data.

III. DML Detailed Explanation and Examples

1. INSERT: Inserts data.

   INSERT INTO employees (id, name, salary) VALUES (1, 'Alice', 50000);  
   

   • Batch insertion is more efficient: INSERT INTO employees VALUES (2, 'Bob', 60000), (3, 'Charlie', 70000);

2. UPDATE: Modifies data.

   UPDATE employees SET salary = 55000 WHERE id = 1;  
   

   • Must use WHERE to limit the scope; otherwise, the entire table will be updated.

3. DELETE: Deletes data.

   DELETE FROM employees WHERE id = 1;  
   

   • Comparison with TRUNCATE: DELETE removes row by row and can be rolled back, while TRUNCATE quickly clears data and cannot be rolled back.

IV. DQL Core Syntax and Optimization

1. Basic Queries:

   SELECT name, salary FROM employees WHERE salary > 50000 ORDER BY salary DESC;  
   

   • Execution order: FROM → WHERE → SELECT → ORDER BY.

2. Multi-Table Joins:

   SELECT e.name, d.department_name  
   FROM employees e  
   JOIN departments d ON e.department_id = d.id;  
   

   • Use aliases to simplify code, and be cautious with join conditions to avoid Cartesian products.

3. Aggregate Functions and Grouping:

   SELECT department, AVG(salary) AS avg_salary  
   FROM employees  
   GROUP BY department  
   HAVING AVG(salary) > 60000; -- HAVING filters results after grouping  
   

V. DCL Permission Management

1. GRANT: Grants permissions.

   GRANT SELECT, INSERT ON employees TO user1;  
   

   • Can be refined to the column level: GRANT UPDATE (name) ON employees TO user1;

2. REVOKE: Revokes permissions.

   REVOKE INSERT ON employees FROM user1;  
   

VI. Application Scenario Summary

• DDL: Suitable for database design phases (e.g., creating tables) and structural changes (e.g., version upgrades).
• DML: Frequently used in business logic (e.g., user registration, order updates).
• DQL: Core tool for data analysis and report generation.
• DCL: Permission management in multi-user systems (e.g., distinguishing between administrators and regular users).

VII. Precautions

1. DDL typically auto-commits transactions; data should be backed up before execution.
2. DML operations should be combined with transactions (BEGIN TRANSACTION, COMMIT) to ensure consistency.
3. Complex DQL queries should utilize indexes to avoid full table scans.