SQL
DML which means Data Manipulation Language commands that are the backbone of SQL, allows users to interact with and manipulate data stored in a database.
There are four main commands
First there should be a Table or Database available to implement this commands, here is DDL commands that you can use it for changing the structure of the Databases.
Here are example of these commands:
The SELECT command is used to retrieve data from one or more tables. It is the fundamental command for querying databases. Let’s jump into a simple example:
-- Retrieve all columns from the 'students' table
SELECT * FROM students;
The result would be a table containing all columns from the ‘students’ table:
| student_id | first_name | last_name | age |
|---|---|---|---|
| 1 | John | Doe | 21 |
| 2 | Jane | Smith | 24 |
The asterisk (*) is a wildcard character, indicating that we want to retrieve all columns.
The INSERT command is used to add new rows to a table. Consider the following example:
-- Insert a new student into the 'students' table
INSERT INTO students (first_name, last_name, age) VALUES ('Alice', 'Johnson', 20);
This command inserts a new row into the ‘students’ table, specifying values for the ‘first_name,’ ‘last_name,’ and ‘age’ columns.
| student_id | first_name | last_name | age |
|---|---|---|---|
| 1 | John | Doe | 21 |
| 2 | Jane | Smith | 24 |
| 3 | Alice | Johnson | 20 |
The UPDATE command modifies existing data in a table. Here’s an example:
-- Update the age of the student with ID 1
UPDATE students SET age = 22 WHERE student_id = 1;
This command updates the ‘age’ column for the student with ID 1 in the ‘students’ table.
| student_id | first_name | last_name | age |
|---|---|---|---|
| 1 | John | Doe | 22 |
| 2 | Jane | Smith | 24 |
| 3 | Alice | Johnson | 20 |
The DELETE command removes rows from a table. Consider the following example:
-- Delete the student with ID 3
DELETE FROM students WHERE student_id = 3;
This command deletes the row corresponding to the student with ID 3 from the ‘students’ table.
| student_id | first_name | last_name | age |
|---|---|---|---|
| 1 | John | Doe | 22 |
| 2 | Jane | Smith | 24 |
While we’ve covered the fundamental DML commands, it’s important to note that SQL offers additional commands like MERGE, UPSERT, and more.
The MERGE statement in SQL allows you to perform an “upsert” operation, which means it can either update an existing record or insert a new record if it doesn’t exist. It’s particularly useful when you want to synchronize data between two tables. Here’s an example:
-- Assume we have two tables: 'source_table' and 'target_table'
-- We want to update records in 'target_table' if they exist in 'source_table'
-- and insert them if they don't exist.
MERGE INTO target_table AS target
USING source_table AS source
ON target.id = source.id
WHEN MATCHED THEN
UPDATE SET target.column1 = source.column1, target.column2 = source.column2
WHEN NOT MATCHED THEN
INSERT (id, column1, column2) VALUES (source.id, source.column1, source.column2);
In this example, the MERGE INTO statement checks for matching records based on the ‘id’ column. If a match is found, it updates the columns in ‘target_table’ with the corresponding values from ‘source_table.’ If no match is found, a new record is inserted into ‘target_table.’
The term “upsert” is a combination of “update” and “insert,” and it refers to the operation of updating a record if it exists or inserting it if it doesn’t. While the MERGE statement is a powerful tool for upserts, not all database systems support it. In databases that don’t support MERGE, you can achieve upserts using a combination of INSERT and UPDATE statements. Here’s an example using a hypothetical ‘users’ table:
-- Assume we have a 'users' table with columns 'id,' 'username,' and 'email.'
-- We want to update the email address if the user exists or insert a new user if not.
-- Assuming @id, @username, and @email are the parameters you want to upsert.
-- Try to update the user's email
UPDATE users
SET email = @email
WHERE id = @id;
-- If no rows were updated, insert a new user
IF @@ROWCOUNT = 0
INSERT INTO users (id, username, email)
VALUES (@id, @username, @email):
In this example, the UPDATE statement attempts to modify the email address for an existing user with the specified ‘id.’ If no rows are updated (meaning the user doesn’t exist), an INSERT statement adds a new user to the ‘users’ table.