Complete SQL Queries in MySQL
A comprehensive reference including DDL, DML, DQL, TCL, and DCL queries with explanations and examples.
DDL (Data Definition Language)
CREATE TABLE: Used to create a new table in the database.
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
age INT,
course VARCHAR(50)
);
ALTER TABLE: Used to modify an existing table structure.
ALTER TABLE students ADD email VARCHAR(100);
DROP TABLE: Deletes a table and all its data.
DROP TABLE students;
TRUNCATE TABLE: Removes all rows from a table but keeps its structure.
TRUNCATE TABLE students;
DML (Data Manipulation Language)
INSERT: Adds new data into a table.
INSERT INTO students (name, age, course) VALUES ('Aman', 20, 'Math');
UPDATE: Modifies existing data in a table.
UPDATE students SET age = 21 WHERE name = 'Aman';
DELETE: Removes rows from a table.
DELETE FROM students WHERE age < 18;
DQL (Data Query Language)
SELECT: Retrieves data from the database.
SELECT name, age FROM students WHERE course = 'Math';
ORDER BY: Sorts the result set.
SELECT * FROM students ORDER BY age DESC;
GROUP BY: Groups rows with the same values.
SELECT course, COUNT(*) FROM students GROUP BY course;
HAVING: Applies conditions to groups.
SELECT course, COUNT(*) FROM students GROUP BY course HAVING COUNT(*) > 5;
JOIN: Combines rows from two or more tables.
SELECT [Link], c.course_name FROM students s
JOIN courses c ON [Link] = [Link];
TCL (Transaction Control Language)
START TRANSACTION: Begins a transaction.
START TRANSACTION;
COMMIT: Saves all changes made during the transaction.
COMMIT;
ROLLBACK: Undoes changes made in the current transaction.
ROLLBACK;
SAVEPOINT: Sets a savepoint within a transaction.
SAVEPOINT sp1;
ROLLBACK TO: Rolls back to a specific savepoint.
ROLLBACK TO sp1;
DCL (Data Control Language)
GRANT: Gives privileges to users.
GRANT SELECT, INSERT ON students TO 'user1'@'localhost';
REVOKE: Removes privileges from users.
REVOKE INSERT ON students FROM 'user1'@'localhost';