MariaDB SQL Commands: Page 2 (DDL & Basic Querying)

This page covers Data Definition Language (DDL) commands for managing table structures and basic querying with WHERE and ORDER BY clauses.

---

6. CREATE TABLE - Create a New Table

Purpose: The CREATE TABLE statement is used to create a new table in the database. You define the table name and the columns with their data types and optional constraints.

Syntax:

CREATE TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
);

Examples:

CREATE TABLE Users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

---

7. ALTER TABLE - Modify Table Structure

Purpose: The ALTER TABLE statement is used to add, delete, or modify columns in an existing table, or to add/drop constraints.

Syntax (Add Column): ALTER TABLE table_name ADD column_name datatype [constraints];

Syntax (Drop Column): ALTER TABLE table_name DROP COLUMN column_name;

Syntax (Modify Column): ALTER TABLE table_name MODIFY COLUMN column_name new_datatype [new_constraints];

Examples:

ALTER TABLE Users ADD phone_number VARCHAR(20);
ALTER TABLE Products DROP COLUMN stock_level;
ALTER TABLE Orders MODIFY COLUMN order_date DATE NOT NULL;

---

8. DROP TABLE - Delete a Table

Purpose: The DROP TABLE statement is used to delete an existing table from the database. This command will permanently remove the table and all its data.

Syntax: DROP TABLE [IF EXISTS] table_name;

Key Option:

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

Examples:

DROP TABLE OldLogs;
DROP TABLE IF EXISTS TempData;

---

9. WHERE - Filter Rows

Purpose: The WHERE clause is used to filter records based on a specified condition. It extracts only those records that fulfill a specified condition.

Syntax: SELECT column1 FROM table_name WHERE condition;

Common Operators:

• =, <> (or !=), <, >, <=, >=
• AND, OR, NOT
• LIKE, IN, BETWEEN, IS NULL, IS NOT NULL

Examples:

SELECT * FROM Customers WHERE country = 'Germany';
SELECT product_name, price FROM Products WHERE price > 50 AND stock > 10;
SELECT employee_name FROM Employees WHERE department_id IN (101, 103);

---

10. ORDER BY - Sort Results

Purpose: The ORDER BY clause is used to sort the result-set of a SELECT statement in ascending or descending order based on one or more columns.

Syntax: SELECT column1, column2 FROM table_name ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];

Key Concepts:

• ASC: Ascending order (default).
• DESC: Descending order.

Examples:

SELECT product_name, price FROM Products ORDER BY price DESC;
SELECT first_name, last_name, hire_date FROM Employees ORDER BY hire_date ASC, last_name ASC;