MySQL: مقدمة إلى قواعد بناء الجمل الأساسية في لغة SQL وشرح…

-- This SQL runs on MySQL, PostgreSQL, Oracle, and SQL Server
SELECT 1 + 1 AS result;

-- Create a Database
CREATE DATABASE mydb;

-- Create a Table
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- Modify the table structure
ALTER TABLE users ADD COLUMN email VARCHAR(100);

-- Delete Table
DROP TABLE users;

-- Clear Table Data (Keep Structure)
TRUNCATE TABLE users;

-- Insert data
INSERT INTO users (id, name) VALUES (1, 'Alice');

-- Update Data
UPDATE users SET name = 'Bob' WHERE id = 1;

-- Delete Data
DELETE FROM users WHERE id = 1;

-- Query All Fields
SELECT * FROM users;

-- Conditional Query
SELECT name, email FROM users WHERE id > 10;

-- Sorted Queries
SELECT * FROM users ORDER BY created_at DESC;

-- Limit Results
SELECT * FROM users LIMIT 10;

-- Grant Permissions
GRANT SELECT, INSERT ON mydb.* TO 'user'@'localhost';

-- Revoke Permissions
REVOKE INSERT ON mydb.* FROM 'user'@'localhost';

-- Start Transaction
START TRANSACTION;

-- Perform an action
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Commit Transaction
COMMIT;

-- Or roll back the transaction
ROLLBACK;

SELECT [DISTINCT] column1, column2, ...
FROM table_name
[WHERE condition]
[ORDER BY column [ASC|DESC]]
[LIMIT offset, count];

-- Query All Fields
SELECT * FROM employees;

-- Query a Specific Field
SELECT first_name, last_name, salary FROM employees;

-- Using Aliases
SELECT first_name AS first_name, last_name AS last_name, salary AS salary FROM employees;

-- (Omit) AS (same result)
SELECT first_name first_name, last_name last_name FROM employees;
SELECT DISTINCT department FROM employees;

+------+--------+---------+
| first_name | last_name | salary  |
+------+--------+---------+
| John | Smith  | 5000.00 |
| Jane | Doe    | 6000.00 |
+------+--------+---------+

-- Equal Value Query
SELECT * FROM employees WHERE department = 'Engineering';

-- Range Query
SELECT * FROM employees WHERE salary BETWEEN 4000 AND 8000;

-- List Query
SELECT * FROM employees WHERE department IN ('Sales', 'Marketing');

-- Fuzzy Search
SELECT * FROM employees WHERE first_name LIKE 'J%';

-- Null Value Query
SELECT * FROM employees WHERE manager_id IS NULL;

+-------------+-----------+--------+-------------+
| first_name  | last_name | salary | department  |
+-------------+-----------+--------+-------------+
| John        | Smith     | 5000.00| Engineering |
| Jane        | Doe       | 6000.00| Engineering |
+-------------+-----------+--------+-------------+

-- This is a single-line comment (Standard Format)
SELECT * FROM users; -- Query All Users

# This is also a single-line comment (MySQL-specific)
SELECT * FROM users;

/*
This is a multi-line comment
Can span multiple lines
Commonly used to illustrate complex queries
*/
SELECT * FROM users
WHERE status = 'active';

/*
Features: Query statistics on active users
Author: Alice
Date: 2026-07-03
Revision History:
  - 2026-07-02 Add email_verified condition
  - 2026-07-03 Add a filter for registration date
*/
SELECT 
    COUNT(*) AS total_users,           -- Total Number of Users
    AVG(age) AS avg_age,               -- Average Age
    MIN(created_at) AS earliest_reg    -- Earliest registration date
FROM users
WHERE status = 'active'                -- Count only active users
  AND email_verified = TRUE            -- Email verified
  AND created_at >= '2025-01-01';      -- Registered after 2025

-- Error: order is a keyword
CREATE TABLE order (id INT);

-- Correct: Enclose in backticks
CREATE TABLE `order` (id INT);

-- Better: Avoid using keywords as table names
CREATE TABLE orders (id INT);

-- Good Naming Practices
CREATE TABLE user_orders (
    id INT PRIMARY KEY,
    user_id INT,
    order_number VARCHAR(20),
    total_amount DECIMAL(10,2),
    created_at TIMESTAMP
);

-- Poor Naming
CREATE TABLE t1 (
    c1 INT,
    c2 VARCHAR(20),
    c3 DECIMAL(10,2)
);

-- Recommended Format: Capitalize keywords, one field per line
SELECT 
    first_name,
    last_name,
    salary
FROM employees
WHERE department = 'Engineering'
ORDER BY salary DESC
LIMIT 10;

-- A semicolon is a statement terminator.
SELECT * FROM users;  -- Statement 1
SELECT * FROM orders; -- Statement 2

-- Multiple statements must be separated by semicolons when executed.