Structured Query Language (SQL) is the foundation for working with relational databases. Whether you're managing a small database or working with large datasets, SQL commands help you interact with your data efficiently.
SQL commands are instructions used to perform operations on data within a database. These commands can be grouped into several categories:
Let’s dive into some of the most important SQL commands in each of these categories.
The CREATE DATABASE command is used to create a new database.
CREATE DATABASE database_name;
CREATE DATABASE school;
This command creates a new database named school.
The CREATE TABLE command is used to define a new table in the database.
CREATE TABLE table_name (
column1 datatype constraints,
column2 datatype constraints,
...
);
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
grade VARCHAR(5)
);
This creates a table named students with columns for student ID, name, age, and grade.
The ALTER TABLE command allows you to modify an existing table's structure, such as adding, deleting, or modifying columns.
ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE students ADD email VARCHAR(100);
This adds a new column email to the students table.
The DROP DATABASE command is used to delete an entire database and all of its associated data.
DROP DATABASE database_name;
DROP DATABASE school;
This command deletes the school database.
The DROP TABLE command deletes a table and its data permanently.
DROP TABLE table_name;
DROP TABLE students;
This removes the students table from the database.
The SELECT command is used to retrieve data from one or more tables.
SELECT column1, column2, ...
FROM table_name
WHERE condition;
SELECT name, grade FROM students WHERE age > 18;
This retrieves the names and grades of students older than 18.
The INSERT INTO command adds new records into a table.
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
INSERT INTO students (student_id, name, age, grade)
VALUES (1, 'John Doe', 20, 'A');
This inserts a new student record into the students table.
The UPDATE command modifies existing records in a table.
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
UPDATE students
SET grade = 'B'
WHERE student_id = 1;
This updates the grade of the student with student_id 1 to 'B'.
The DELETE command removes records from a table based on a condition.
DELETE FROM table_name WHERE condition;
DELETE FROM students WHERE student_id = 1;
This deletes the student with student_id 1 from the students table.
The GRANT command is used to give users access to a database object, such as a table or a view.
GRANT privilege_type ON object TO user;
GRANT SELECT ON students TO user1;
This grants the user1 permission to perform a SELECT operation on the students table.
The REVOKE command removes a user's access to a database object.
REVOKE privilege_type ON object FROM user;
REVOKE SELECT ON students FROM user1;
This revokes the SELECT permission from user1 on the students table.
The COMMIT command is used to save the changes made in the current transaction.
COMMIT;
COMMIT;
This saves all changes made to the database during the current session.
The ROLLBACK command is used to undo changes made during the current transaction.
ROLLBACK;
ROLLBACK;
This undoes all the changes made during the current transaction.
The SAVEPOINT command is used to set a point in the transaction to which you can roll back.
SAVEPOINT savepoint_name;
SAVEPOINT before_update;
This creates a savepoint called before_update that you can roll back to later.