Bảng Tóm Tắt Các Câu Lệnh MySQL Cần Biết (Kèm Ví Dụ)

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    course VARCHAR(100),
    marks INT
);

CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(100),
    instructor VARCHAR(100)
);

INSERT INTO students (name, age, course, marks) VALUES
('Alice', 20, 'Computer Science', 85),
('Bob', 22, 'Mathematics', 90),
('Charlie', 21, 'Physics', 75),
('Daisy', 23, 'Computer Science', 95);

INSERT INTO courses (course_name, instructor) VALUES
('Computer Science', 'Dr. Smith'),
('Mathematics', 'Dr. John'),
('Physics', 'Dr. Lee');

SHOW DATABASES;

USE test_db;

SHOW TABLES;

DESCRIBE students;

SELECT * FROM students;

SELECT name, marks FROM students WHERE marks > 80;

SELECT * FROM students ORDER BY marks DESC;

SELECT * FROM students LIMIT 2;

SELECT DISTINCT course FROM students;

INSERT INTO students (name, age, course, marks)
VALUES ('Eve', 20, 'Mathematics', 88);

UPDATE students SET marks = 92 WHERE name = 'Alice';

DELETE FROM students WHERE name = 'Charlie';

DROP TABLE students;

SELECT course, AVG(marks) AS avg_marks
FROM students
GROUP BY course;

SELECT course, AVG(marks) AS avg_marks
FROM students
GROUP BY course
HAVING avg_marks > 80;

SELECT s.name, s.course, c.instructor
FROM students s
JOIN courses c ON s.course = c.course_name;

SELECT * FROM students WHERE name LIKE 'A%';

-- Điểm giữa 80 và 95
SELECT * FROM students WHERE marks BETWEEN 80 AND 95;

-- Sinh viên trong các khóa học cụ thể
SELECT * FROM students WHERE course IN ('Mathematics', 'Physics');

START TRANSACTION;

UPDATE students SET marks = 100 WHERE name = 'Bob';

ROLLBACK;  -- hoàn tác thay đổi
-- COMMIT; -- xác nhận thay đổi

DELIMITER //

CREATE TRIGGER after_insert_student
AFTER INSERT ON students
FOR EACH ROW
BEGIN
    INSERT INTO courses (course_name, instructor)
    VALUES ('New Course', 'TBD');
END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE GetHighScorers()
BEGIN
    SELECT name, marks FROM students WHERE marks > 85;
END //

DELIMITER ;

CALL GetHighScorers();

CREATE VIEW high_scorers AS
SELECT name, course, marks
FROM students
WHERE marks > 85;

SELECT * FROM high_scorers;

CREATE INDEX idx_course ON students(course);