MariaDB SQL Commands: Page 5 (Advanced Features)

This page covers advanced SQL features in MariaDB, including views, indexes, and result limiting.

---

21. CREATE VIEW - Create a Virtual Table

Purpose: A VIEW is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

Syntax: CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition;

Examples:

CREATE VIEW ActiveCustomers AS
SELECT customer_id, customer_name, email
FROM Customers
WHERE status = 'Active';
SELECT * FROM ActiveCustomers;

---

22. DROP VIEW - Delete a View

Purpose: The DROP VIEW statement is used to delete an existing view from the database.

Syntax: DROP VIEW [IF EXISTS] view_name;

Key Option:

• IF EXISTS: Prevents an error if the view does not exist.

Examples:

DROP VIEW OldLogsView;
DROP VIEW IF EXISTS TempReports;

---

23. CREATE INDEX - Create an Index on a Table

Purpose: An INDEX is used to retrieve data from the database more quickly. Indexes are used by the database search engine to speed up the data retrieval process. They work like the index in a book.

Syntax: CREATE [UNIQUE] INDEX index_name ON table_name (column1, column2, ...);

Key Concept:

• UNIQUE: Creates a unique index, ensuring all values in the indexed column(s) are unique.

Examples:

CREATE INDEX idx_customer_name ON Customers (customer_name);
CREATE UNIQUE INDEX idx_product_code ON Products (product_code);

---

24. DROP INDEX - Delete an Index

Purpose: The DROP INDEX statement is used to delete an index from a table.

Syntax: DROP INDEX index_name ON table_name;

Examples:

DROP INDEX idx_customer_name ON Customers;

---

25. LIMIT - Limit the Number of Results

Purpose: The LIMIT clause is used to constrain the number of rows returned by a SELECT statement. It is very useful on large tables to return only a subset of the rows.

Syntax: SELECT columns FROM table_name LIMIT [offset,] row_count;

Key Concepts:

• row_count: The maximum number of rows to return.
• offset: The starting row for retrieval (optional, default is 0).

Examples:

SELECT * FROM Products LIMIT 10;
SELECT * FROM Employees ORDER BY salary DESC LIMIT 5 OFFSET 10;