Demystifying SQL in MariaDB: Statements & Structure

1. SQL Statements
   These are the building blocks used to perform various operations on your database. There are different types of statements for tasks like:

   • Data Manipulation
     Inserting, updating, and deleting data from tables (INSERT, UPDATE, DELETE).
   • Data Retrieval
     Selecting specific data from tables (SELECT).
   • Data Definition
     Creating and modifying tables within the database (CREATE, ALTER, DROP).
   • Data Control
     Granting and restricting access to the database (GRANT, REVOKE).

2. Language Structure
   This refers to the specific syntax and rules that govern how you write these SQL statements. Here are some key points to remember about MariaDB's SQL structure:

   • Identifiers
     Names for database objects like tables and columns follow specific naming conventions (e.g., starting with a letter and containing only letters, numbers, and underscores).
   • Case Sensitivity
     MariaDB is case-sensitive, so Name and name are considered different identifiers.
   • Literals
     Data values used in queries come in various forms like strings, numbers, dates, etc., each with its own representation in SQL statements.

---

1. Creating a Table
   

CREATE TABLE customers (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE,
  phone CHAR(10)
);

• phone: Stores the customer's phone number as a fixed-length character string of 10 digits.
• email: Stores the customer's email address as a unique string (no duplicate emails allowed).
• name: Stores the customer's name as a string with a maximum length of 255 characters.
• id: An auto-incrementing integer that acts as the primary key for the table.

1. Inserting Data
   

INSERT INTO customers (name, email, phone)
VALUES ('John Doe', '[email protected]', '1234567890');

This code inserts a new record into the customers table with the following details:

• Phone: 1234567890
• Email: [email protected]
• Name: John Doe

1. Selecting Data
   

SELECT * FROM customers;

# To select specific columns:
SELECT id, name FROM customers;

# To filter results based on conditions:
SELECT * FROM customers WHERE email = '[email protected]';

The first query selects all columns (*) from the customers table.

The second query selects only the id and name columns.

The third query selects all columns from customers where the email matches '[email protected]'.

---

Focus on the core functionality

• Data Query Language (DQL)
  Specifically highlights the ability to retrieve data using SQL.
• Data Manipulation Language (DML)
  Focuses on the ability to manipulate data within the database.
• SQL Commands
  Highlights the different types of commands available in SQL (e.g., SELECT, UPDATE, etc.).
• SQL Syntax
  This emphasizes the specific rules and format for writing SQL statements.

Focus on database interaction

• Relational Database Query Language
  Highlights its role in querying relational databases.
• MariaDB Query Language
  Emphasizes that it's the language used to interact with MariaDB databases.

• Database Management System (DBMS) Language
  Positions SQL within the context of a broader database management system.