SQL in MySQL

SQL (Structured Query Language) is the standard language used to store, retrieve, manage, and manipulate data in MySQL databases.

1. Categories of SQL Commands in MySQL

SQL commands in MySQL are categorized into the following types:

2. Common SQL Commands in MySQL

✅ 1. Data Definition Language (DDL)

• CREATE – Creates a new table or database.

CREATE TABLE employees (    emp_id INT PRIMARY KEY,    name VARCHAR(50),    department VARCHAR(50),    salary DECIMAL(10,2));

• ALTER – Modifies an existing table.

ALTER TABLE employees ADD COLUMN age INT;

• DROP – Deletes a table.

DROP TABLE employees;

• TRUNCATE – Removes all records from a table but keeps the structure.

TRUNCATE TABLE employees;

✅ 2. Data Manipulation Language (DML)

• INSERT – Adds new data.

INSERT INTO employees (emp_id, name, department, salary) VALUES (1, 'Alice', 'IT', 6000);

• UPDATE – Modifies existing records.

UPDATE employees SET salary = 7000 WHERE emp_id = 1;

• DELETE – Removes specific records.

DELETE FROM employees WHERE emp_id = 1;

✅ 3. Data Query Language (DQL)

• SELECT – Retrieves data.

SELECT * FROM employees;

• Using WHERE (Filtering data)

SELECT * FROM employees WHERE department = 'IT';

• Using ORDER BY (Sorting results)

SELECT * FROM employees ORDER BY salary DESC;

✅ 4. Transaction Control Language (TCL)

• COMMIT – Saves a transaction.

COMMIT;

• ROLLBACK – Undoes changes.

ROLLBACK;

• SAVEPOINT – Sets a point to rollback to.

SAVEPOINT save1;

✅ 5. Data Control Language (DCL)

• GRANT – Gives access permissions.

GRANT SELECT ON employees TO 'user1'@'localhost';

• REVOKE – Removes access permissions.

REVOKE SELECT ON employees FROM 'user1'@'localhost';

3. MySQL Joins (Combining Data from Multiple Tables)

SELECT employees.name, departments.dept_nameFROM employeesJOIN departments ON employees.dept_id = departments.dept_id;

✅ Types of Joins:

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• CROSS JOIN
• SELF JOIN

4. Aggregation Functions in MySQL

• Counting record

SELECT COUNT(*) FROM employees;

• Finding average salary

SELECT AVG(salary) FROM employees;

• Summing salaries

SELECT SUM(salary) FROM employees;

• Finding max and min salary

SELECT MAX(salary), MIN(salary) FROM employees;

Key Takeaways

✅ MySQL uses SQL commands for managing data.
✅ SQL is divided into DDL, DML, DQL, TCL, and DCL.
✅ SELECT, INSERT, UPDATE, and DELETE are the most commonly used SQL statements.
✅ SQL allows filtering, sorting, and aggregating data efficiently.
✅ Joins help combine multiple tables.