MariaDB SQL Commands: Page 1 (DML)

This page covers fundamental Data Manipulation Language (DML) commands in MariaDB SQL.

---

1. SELECT - Retrieve Data

Purpose: The SELECT statement is used to retrieve rows from one or more tables. It's the most common and powerful SQL command for querying data.

Syntax: SELECT column1, column2 FROM table_name WHERE condition;

Key Concepts:

• *: Selects all columns.
• DISTINCT: Returns only unique (distinct) values.

Examples:

SELECT * FROM Customers;
SELECT DISTINCT country FROM Customers;
SELECT first_name, last_name FROM Employees WHERE department_id = 101;

---

2. INSERT - Add New Rows

Purpose: The INSERT INTO statement is used to add new rows of data into a table.

Syntax (full): INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

Syntax (all columns): INSERT INTO table_name VALUES (value1, value2, ...);

Examples:

INSERT INTO Products (product_name, price, stock) VALUES ('Laptop', 1200.00, 50);
INSERT INTO Employees VALUES (10, 'Jane', 'Doe', 'HR', 55000.00);

---

3. UPDATE - Modify Existing Rows

Purpose: The UPDATE statement is used to modify existing data in a table.

Syntax: UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

Key Concept:

• The WHERE clause is crucial to specify which rows to update. Without it, ALL rows in the table will be updated.

Examples:

UPDATE Products SET price = 1250.00 WHERE product_name = 'Laptop';
UPDATE Employees SET department_id = 102, salary = 60000.00 WHERE employee_id = 10;

---

4. DELETE - Remove Rows

Purpose: The DELETE FROM statement is used to remove existing rows from a table.

Syntax: DELETE FROM table_name WHERE condition;

Key Concept:

• The WHERE clause is crucial to specify which rows to delete. Without it, ALL rows in the table will be deleted.

Examples:

DELETE FROM Products WHERE stock = 0;
DELETE FROM Customers WHERE country = 'USA' AND city = 'New York';

---

5. FROM - Specify Source Table(s)

Purpose: The FROM clause specifies the table or tables from which to retrieve or manipulate data. It's used with SELECT, UPDATE, and DELETE statements.

Syntax (with SELECT): SELECT columns FROM table_name;

Syntax (with JOIN): SELECT columns FROM table1 JOIN table2 ON table1.id = table2.id;

Examples:

SELECT * FROM Orders;
UPDATE Employees FROM Departments WHERE Employees.department_id = Departments.id AND Departments.name = 'Sales' SET Employees.salary = Employees.salary * 1.10;
DELETE FROM OldLogs FROM system_config WHERE OldLogs.log_date < NOW() - INTERVAL 1 YEAR AND OldLogs.type = system_config.log_type;