-- Login ចូល MySQL
mysql -u root -p

-- បញ្ជាក់ Host
mysql -h localhost -u root -p

-- មើល version
SELECT VERSION();

-- មើល databases ទាំងអស់
SHOW DATABASES;

-- បង្កើត Database
CREATE DATABASE school_db;

-- ជ្រើស Database
USE school_db;

-- លុប Database
DROP DATABASE IF EXISTS school_db;

-- មើល Database បច្ចុប្បន្ន
SELECT DATABASE();

CREATE TABLE students (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name  VARCHAR(50) NOT NULL,
  email      VARCHAR(100) UNIQUE NOT NULL,
  age        INT CHECK (age > 0),
  gender     ENUM('male', 'female'),
  grade      DECIMAL(4,2) DEFAULT 0.00,
  created_at DATETIME DEFAULT NOW()
);

-- FOREIGN KEY ជើងឧទាហរណ៍
CREATE TABLE enrollments (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  course     VARCHAR(100),
  FOREIGN KEY (student_id) REFERENCES students(id)
    ON DELETE CASCADE
);

-- បន្ថែម Column
ALTER TABLE students ADD phone VARCHAR(20);

-- ប្ដូរ Data Type
ALTER TABLE students MODIFY phone VARCHAR(30) NOT NULL;

-- ប្ដូរឈ្មោះ Column
ALTER TABLE students RENAME COLUMN grade TO gpa;

-- លុប Column
ALTER TABLE students DROP COLUMN phone;

-- បញ្ចូលជួរតែមួយ
INSERT INTO students (first_name, last_name, email, age, gender)
VALUES ('សុខ', 'ដារា', 'dara@example.com', 20, 'male');

-- បញ្ចូលច្រើនជួរម្ដង
INSERT INTO students (first_name, last_name, email, age, gender)
VALUES
  ('ចាន់', 'ពិសី', 'pisey@example.com', 22, 'female'),
  ('ហេង', 'វណ្ណ', 'vann@example.com', 21, 'male'),
  ('លី', 'ស្រី', 'srey@example.com', 19, 'female');

-- ទាញទាំងអស់
SELECT * FROM students;

-- ទាញ Columns ជ្រើស
SELECT first_name, last_name, age FROM students;

-- ប្ដូរឈ្មោះ Column (Alias)
SELECT
  first_name AS ឈ្មោះ,
  age        AS អាយុ
FROM students;

-- ORDER BY — តម្រៀប
SELECT * FROM students
ORDER BY age DESC;  -- ASC ឡើង, DESC ចុះ

-- LIMIT — កំណត់ចំនួន Row
SELECT * FROM students
ORDER BY age
LIMIT 5;

-- LIMIT + OFFSET (Pagination)
SELECT * FROM students
LIMIT 10 OFFSET 20; -- Page 3 (10 per page)

-- DISTINCT — លុបជួរដូចៗ
SELECT DISTINCT gender FROM students;

-- Comparison
SELECT * FROM students WHERE age = 20;
SELECT * FROM students WHERE age >= 18;
SELECT * FROM students WHERE age != 25;

-- AND / OR / NOT
SELECT * FROM students
WHERE age > 18 AND gender = 'female';

-- BETWEEN
SELECT * FROM students
WHERE age BETWEEN 18 AND 25;

-- IN — ចំពោះ List
SELECT * FROM students
WHERE id IN (1, 3, 5, 7);

-- LIKE — ស្វែងរក Pattern
SELECT * FROM students
WHERE email LIKE '%@gmail.com';  -- ចប់ @gmail.com

SELECT * FROM students
WHERE first_name LIKE 'ស%';  -- ចាប់ផ្ដើមដោយ ស

-- IS NULL / IS NOT NULL
SELECT * FROM students
WHERE grade IS NULL;

-- INNER JOIN: យក data ដែលស្ថិតនៅក្នុង Table ទាំងពីរ
SELECT
  s.first_name,
  s.last_name,
  e.course
FROM students AS s
INNER JOIN enrollments AS e
  ON s.id = e.student_id;

-- LEFT JOIN: យក Row ទាំងអស់ពី Left Table
-- (Row ដែលគ្មាននៅ Right Table → NULL)
SELECT
  s.first_name,
  e.course
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id;

-- ស្វែងរក Students ដែលមិនបានចុះឈ្មោះ
SELECT s.first_name
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
WHERE e.student_id IS NULL;

-- JOIN Tables ៣ ក្នុងពេលតែមួយ
SELECT s.first_name, e.course, g.score
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id
INNER JOIN grades g ON e.id = g.enrollment_id;

-- COUNT: រាប់ចំនួន
SELECT COUNT(*) AS total_students FROM students;

-- GROUP BY + COUNT
SELECT gender, COUNT(*) AS total
FROM students
GROUP BY gender;

-- AVG, SUM, MAX, MIN
SELECT
  AVG(age)   AS avg_age,
  SUM(grade) AS total_grade,
  MAX(grade) AS highest,
  MIN(grade) AS lowest
FROM students;

-- HAVING: Filter បន្ទាប់ GROUP BY
SELECT gender, AVG(age) AS avg_age
FROM students
GROUP BY gender
HAVING AVG(age) > 20;

-- UPDATE Row តែ
UPDATE students
SET age = 21, grade = 3.5
WHERE id = 1;

-- UPDATE ច្រើន Rows
UPDATE students
SET grade = grade + 0.5
WHERE grade < 2.0;

-- DELETE Row ជ្រើស
DELETE FROM students WHERE id = 5;

-- TRUNCATE: លុប Rows ទាំងអស់ (ល្បឿនលឿន)
TRUNCATE TABLE students;

-- ស្វែងរក Students ដែលមាន grade ខ្ពស់ជា Average
SELECT first_name, grade
FROM students
WHERE grade > (
  SELECT AVG(grade) FROM students
);

-- Subquery ក្នុង FROM (Derived Table)
SELECT gender, avg_grade
FROM (
  SELECT gender, AVG(grade) AS avg_grade
  FROM students
  GROUP BY gender
) AS summary
WHERE avg_grade > 3.0;

-- EXISTS
SELECT first_name
FROM students s
WHERE EXISTS (
  SELECT 1 FROM enrollments e
  WHERE e.student_id = s.id
);

-- បង្កើត INDEX
CREATE INDEX idx_email ON students(email);

-- Composite INDEX (ច្រើន Columns)
CREATE INDEX idx_name ON students(first_name, last_name);

-- UNIQUE INDEX
CREATE UNIQUE INDEX idx_email_unique ON students(email);

-- FULLTEXT INDEX (ស្វែងរកអក្សរ)
CREATE FULLTEXT INDEX idx_ft ON students(first_name, last_name);

-- មើល INDEX ទាំងអស់
SHOW INDEX FROM students;

-- លុប INDEX
DROP INDEX idx_email ON students;

-- EXPLAIN: ពិនិត្យ Query Plan
EXPLAIN SELECT * FROM students
WHERE email = 'dara@example.com';

-- ប្ដូរ DELIMITER ពី ; ទៅ //
DELIMITER //

CREATE PROCEDURE GetStudentsByGender(
  IN p_gender VARCHAR(10)
)
BEGIN
  SELECT first_name, last_name, age
  FROM students
  WHERE gender = p_gender
  ORDER BY last_name;
END//

DELIMITER ;

-- ហៅ Procedure
CALL GetStudentsByGender('female');

-- Procedure ជាមួយ OUT Parameter
DELIMITER //
CREATE PROCEDURE CountStudents(
  OUT p_count INT
)
BEGIN
  SELECT COUNT(*) INTO p_count
  FROM students;
END//
DELIMITER ;

CALL CountStudents(@total);
SELECT @total;

-- FUNCTION
DELIMITER //
CREATE FUNCTION GetGradeLevel(gpa DECIMAL(4,2))
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
  IF gpa >= 3.5 THEN RETURN 'A';
  ELSEIF gpa >= 3.0 THEN RETURN 'B';
  ELSEIF gpa >= 2.0 THEN RETURN 'C';
  ELSE RETURN 'F';
  END IF;
END//
DELIMITER ;

SELECT first_name, GetGradeLevel(grade) AS level
FROM students;

-- Log ពេល Student ត្រូវបានលុប
DELIMITER //
CREATE TRIGGER before_student_delete
BEFORE DELETE ON students
FOR EACH ROW
BEGIN
  INSERT INTO deleted_log (student_id, deleted_at)
  VALUES (OLD.id, NOW());
END//
DELIMITER ;

-- BEFORE INSERT: ត្រឹមត្រូវ Data
DELIMITER //
CREATE TRIGGER validate_age
BEFORE INSERT ON students
FOR EACH ROW
BEGIN
  IF NEW.age < 10 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Age must be at least 10';
  END IF;
END//
DELIMITER ;

-- VIEW: Virtual Table
CREATE VIEW view_top_students AS
SELECT
  first_name, last_name, grade,
  GetGradeLevel(grade) AS level
FROM students
WHERE grade >= 3.0
ORDER BY grade DESC;

-- ប្រើ View ដូច Table ធម្មតា
SELECT * FROM view_top_students;

-- Transaction ជ្រើសហ្មត់ (Bank Transfer)
START TRANSACTION;

UPDATE accounts
SET balance = balance - 500
WHERE id = 1;

UPDATE accounts
SET balance = balance + 500
WHERE id = 2;

-- ប្រសិនបើ OK
COMMIT;

-- ប្រសិនបើ Error → ត្រឡប់
ROLLBACK;

-- SAVEPOINT
SAVEPOINT step1;
ROLLBACK TO SAVEPOINT step1;

-- បង្កើត User
CREATE USER 'app_user'@'localhost'
IDENTIFIED BY 'SecurePass!123';

-- ផ្តល់ Permission
GRANT SELECT, INSERT, UPDATE
ON school_db.*
TO 'app_user'@'localhost';

-- Full Permission (Admin)
GRANT ALL PRIVILEGES ON *.*
TO 'admin_user'@'localhost'
WITH GRANT OPTION;

-- ដក Permission
REVOKE INSERT ON school_db.*
FROM 'app_user'@'localhost';

-- មើល Permission
SHOW GRANTS FOR 'app_user'@'localhost';

-- លុប User
DROP USER 'app_user'@'localhost';

-- Apply ការផ្លាស់ប្ដូរ
FLUSH PRIVILEGES;