Truy vấn con là một câu truy vấn SQL lồng trong một câu truy vấn khác, có thể xuất hiện trong mệnh đề WHERE, FROM, hoặc SELECT.
-- Tìm học sinh có điểm cao hơn điểm trung bình của tất cả học sinh
SELECT student_id, fullname, score
FROM students
WHERE score > (SELECT AVG(score) FROM students);
-- Lấy thông tin từ kết quả của một truy vấn khác
SELECT dept_name, avg_salary
FROM (
SELECT department_id, AVG(salary) as avg_salary
FROM employees
GROUP BY department_id
) AS dept_salaries
JOIN departments d ON dept_salaries.department_id = d.id;
-- Tìm học sinh có điểm cao nhất trong mỗi lớp
SELECT s.student_id, s.fullname, s.class_id, s.score
FROM students s
WHERE s.score = (
SELECT MAX(score)
FROM students s2
WHERE s2.class_id = s.class_id
);
CTE tạo ra một bảng tạm thời có thể được tham chiếu nhiều lần trong một truy vấn, giúp code SQL dễ đọc và bảo trì hơn.
-- Tìm học sinh có điểm cao hơn điểm trung bình của lớp mình
WITH class_avg_scores AS (
SELECT class_id, AVG(score) AS avg_score
FROM students
GROUP BY class_id
)
SELECT s.student_id, s.fullname, s.score, c.avg_score
FROM students s
JOIN class_avg_scores c ON s.class_id = c.class_id
WHERE s.score > c.avg_score;
-- Hiển thị cấu trúc phân cấp của nhân viên (quản lý - nhân viên)
WITH RECURSIVE employee_hierarchy AS (
-- Trường hợp cơ sở: các nhân viên cấp cao nhất (không có quản lý)
SELECT id, fullname, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Trường hợp đệ quy: tìm nhân viên cấp dưới
SELECT e.id, e.fullname, e.manager_id, eh.level + 1
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT id, fullname, level
FROM employee_hierarchy
ORDER BY level, id;
Các toán tử tập hợp kết hợp kết quả từ nhiều truy vấn SELECT.
-- Kết hợp danh sách học sinh và giáo viên
SELECT 'Student' AS role, fullname, email
FROM students
UNION
SELECT 'Teacher' AS role, fullname, email
FROM teachers
ORDER BY role, fullname;
-- Tìm học sinh vừa học môn Toán vừa học môn Văn
-- (MySQL không hỗ trợ INTERSECT trực tiếp, phải dùng JOIN hoặc IN)
-- Cách viết với INTERSECT (PostgreSQL, SQL Server,...)
SELECT student_id
FROM course_registrations
WHERE course_id = 1 -- Toán
INTERSECT
SELECT student_id
FROM course_registrations
WHERE course_id = 2; -- Văn
-- Cách viết với MySQL
SELECT cr1.student_id
FROM course_registrations cr1
JOIN course_registrations cr2 ON cr1.student_id = cr2.student_id
WHERE cr1.course_id = 1 AND cr2.course_id = 2;
-- Tìm học sinh học môn Toán nhưng không học môn Văn
-- (MySQL không hỗ trợ EXCEPT trực tiếp, phải dùng LEFT JOIN hoặc NOT IN)
-- Cách viết với EXCEPT (PostgreSQL, SQL Server,...)
SELECT student_id
FROM course_registrations
WHERE course_id = 1 -- Toán
EXCEPT
SELECT student_id
FROM course_registrations
WHERE course_id = 2; -- Văn
-- Cách viết với MySQL
SELECT DISTINCT cr1.student_id
FROM course_registrations cr1
LEFT JOIN course_registrations cr2 ON cr1.student_id = cr2.student_id AND cr2.course_id = 2
WHERE cr1.course_id = 1 AND cr2.student_id IS NULL;
Window functions thực hiện tính toán trên một tập hợp các hàng liên quan đến hàng hiện tại, nhưng không gộp các hàng thành một kết quả duy nhất.
-- Đánh số thứ tự cho học sinh theo điểm số từ cao đến thấp
SELECT
student_id,
fullname,
score,
ROW_NUMBER() OVER (ORDER BY score DESC) AS rank
FROM students;
-- Đánh số thứ tự học sinh theo điểm số trong từng lớp riêng biệt
SELECT
student_id,
fullname,
class_id,
score,
ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY score DESC) AS class_rank
FROM students;
-- Tính điểm trung bình của lớp và chênh lệch của mỗi học sinh so với trung bình lớp
SELECT
student_id,
fullname,
class_id,
score,
AVG(score) OVER (PARTITION BY class_id) AS class_avg,
score - AVG(score) OVER (PARTITION BY class_id) AS difference_from_avg
FROM students;
-- Chia học sinh thành 4 nhóm (tứ phân vị) theo điểm số
SELECT
student_id,
fullname,
score,
NTILE(4) OVER (ORDER BY score) AS quartile,
LEAD(score, 1) OVER (ORDER BY score) AS next_higher_score,
LAG(score, 1) OVER (ORDER BY score) AS next_lower_score
FROM students;
Hàm người dùng (User-Defined Functions) là các đoạn code SQL được lưu trữ và có thể tái sử dụng, trả về một giá trị duy nhất.
DELIMITER //
CREATE FUNCTION calculate_age(birthdate DATE)
RETURNS INT DETERMINISTIC
BEGIN
DECLARE age INT;
SET age = YEAR(CURRENT_DATE()) - YEAR(birthdate) -
(DATE_FORMAT(CURRENT_DATE(), '%m%d') < DATE_FORMAT(birthdate, '%m%d'));
RETURN age;
END //
DELIMITER ;
-- Sử dụng hàm
SELECT
student_id,
fullname,
date_of_birth,
calculate_age(date_of_birth) AS age
FROM students;
DELIMITER //
CREATE FUNCTION calculate_gpa(student_id INT)
RETURNS DECIMAL(4,2) READS SQL DATA
BEGIN
DECLARE avg_score DECIMAL(4,2);
SELECT AVG(score) INTO avg_score
FROM student_scores
WHERE student_id = student_id;
RETURN IFNULL(avg_score, 0);
END //
DELIMITER ;
-- Sử dụng hàm
SELECT
s.student_id,
s.fullname,
calculate_gpa(s.student_id) AS gpa
FROM students s;
DELIMITER //
CREATE FUNCTION get_grade_ranking(score DECIMAL(4,2))
RETURNS VARCHAR(20) DETERMINISTIC
BEGIN
DECLARE ranking VARCHAR(20);
IF score >= 9.0 THEN
SET ranking = 'Xuất sắc';
ELSEIF score >= 8.0 THEN
SET ranking = 'Giỏi';
ELSEIF score >= 7.0 THEN
SET ranking = 'Khá';
ELSEIF score >= 5.0 THEN
SET ranking = 'Trung bình';
ELSE
SET ranking = 'Yếu';
END IF;
RETURN ranking;
END //
DELIMITER ;
-- Sử dụng hàm
SELECT
student_id,
fullname,
score,
get_grade_ranking(score) AS ranking
FROM student_scores;
Thủ tục lưu trữ là tập hợp các câu lệnh SQL được đặt tên và lưu trữ trong cơ sở dữ liệu. Khác với hàm, thủ tục có thể không trả về giá trị và có thể có nhiều tham số đầu vào/đầu ra.
DELIMITER //
CREATE PROCEDURE get_student_info(IN student_id INT)
BEGIN
SELECT *
FROM students
WHERE student_id = student_id;
END //
DELIMITER ;
-- Gọi thủ tục
CALL get_student_info(101);
DELIMITER //
CREATE PROCEDURE get_class_statistics(
IN class_id INT,
OUT total_students INT,
OUT avg_score DECIMAL(4,2)
)
BEGIN
-- Tính tổng số sinh viên
SELECT COUNT(*) INTO total_students
FROM students
WHERE class_id = class_id;
-- Tính điểm trung bình
SELECT AVG(score) INTO avg_score
FROM students
WHERE class_id = class_id;
END //
DELIMITER ;
-- Gọi thủ tục với tham số đầu ra
CALL get_class_statistics(1, @total, @avg);
SELECT @total AS 'Tổng sinh viên', @avg AS 'Điểm trung bình';
DELIMITER //
CREATE PROCEDURE update_student_score(
IN student_id INT,
IN subject_id INT,
IN new_score DECIMAL(4,2),
OUT result VARCHAR(100)
)
BEGIN
DECLARE current_score DECIMAL(4,2);
DECLARE score_exists INT;
-- Kiểm tra xem điểm đã tồn tại chưa
SELECT COUNT(*), IFNULL(score, 0)
INTO score_exists, current_score
FROM student_scores
WHERE student_id = student_id AND subject_id = subject_id;
-- Cập nhật hoặc thêm mới điểm
IF score_exists > 0 THEN
UPDATE student_scores
SET score = new_score
WHERE student_id = student_id AND subject_id = subject_id;
SET result = CONCAT('Cập nhật điểm từ ', current_score, ' thành ', new_score);
ELSE
INSERT INTO student_scores (student_id, subject_id, score)
VALUES (student_id, subject_id, new_score);
SET result = CONCAT('Thêm mới điểm: ', new_score);
END IF;
END //
DELIMITER ;
-- Gọi thủ tục cập nhật
CALL update_student_score(101, 1, 8.5, @result);
SELECT @result;
Trigger là đoạn mã SQL tự động thực thi khi một sự kiện cụ thể xảy ra (như INSERT, UPDATE, DELETE). Sự kiện (Event) là các tác vụ SQL được lên lịch để chạy tại một thời điểm cụ thể.
DELIMITER //
CREATE TRIGGER before_score_insert
BEFORE INSERT ON student_scores
FOR EACH ROW
BEGIN
-- Đảm bảo điểm nằm trong khoảng hợp lệ
IF NEW.score < 0 THEN
SET NEW.score = 0;
ELSEIF NEW.score > 10 THEN
SET NEW.score = 10;
END IF;
END //
DELIMITER ;
-- Tạo bảng lưu lịch sử
CREATE TABLE student_score_history (
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT,
subject_id INT,
old_score DECIMAL(4,2),
new_score DECIMAL(4,2),
changed_by VARCHAR(50),
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
DELIMITER //
CREATE TRIGGER after_score_update
AFTER UPDATE ON student_scores
FOR EACH ROW
BEGIN
INSERT INTO student_score_history
(student_id, subject_id, old_score, new_score, changed_by)
VALUES
(NEW.student_id, NEW.subject_id, OLD.score, NEW.score, CURRENT_USER());
END //
DELIMITER ;
-- Tạo bảng lưu thống kê
CREATE TABLE class_statistics (
id INT AUTO_INCREMENT PRIMARY KEY,
class_id INT,
total_students INT,
avg_score DECIMAL(4,2),
calculated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
DELIMITER //
CREATE EVENT calculate_class_statistics
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP
DO
BEGIN
INSERT INTO class_statistics (class_id, total_students, avg_score)
SELECT
class_id,
COUNT(*) as total_students,
AVG(score) as avg_score
FROM students s
JOIN student_scores ss ON s.student_id = ss.student_id
GROUP BY class_id;
END //
DELIMITER ;
Giao dịch (Transaction) đảm bảo tính toàn vẹn của dữ liệu khi thực hiện nhiều thao tác. Xử lý lỗi giúp ứng dụng phản ứng khi có lỗi xảy ra.
-- Chuyển điểm từ sinh viên này sang sinh viên khác
START TRANSACTION;
-- Trừ điểm từ sinh viên nguồn
UPDATE student_scores
SET score = score - 2
WHERE student_id = 101 AND subject_id = 1;
-- Thêm điểm cho sinh viên đích
UPDATE student_scores
SET score = score + 2
WHERE student_id = 102 AND subject_id = 1;
-- Kiểm tra nếu có điểm âm thì không thực hiện
IF EXISTS (SELECT 1 FROM student_scores WHERE score < 0) THEN
ROLLBACK;
SELECT 'Giao dịch bị hủy vì điểm trở thành âm';
ELSE
COMMIT;
SELECT 'Giao dịch đã được thực hiện thành công';
END IF;
DELIMITER //
CREATE PROCEDURE transfer_score(
IN source_student_id INT,
IN target_student_id INT,
IN subject_id INT,
IN points DECIMAL(4,2),
OUT message VARCHAR(100)
)
BEGIN
-- Khai báo biến
DECLARE source_score DECIMAL(4,2);
DECLARE exit_handler BOOLEAN DEFAULT FALSE;
-- Khai báo handler cho lỗi
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
SET exit_handler = TRUE;
SET message = 'Lỗi SQL xảy ra trong quá trình chuyển điểm';
ROLLBACK;
END;
-- Bắt đầu giao dịch
START TRANSACTION;
-- Lấy điểm hiện tại của sinh viên nguồn
SELECT score INTO source_score
FROM student_scores
WHERE student_id = source_student_id AND subject_id = subject_id;
-- Kiểm tra xem có đủ điểm để chuyển không
IF source_score IS NULL OR source_score < points THEN
SET message = 'Không đủ điểm để chuyển';
ROLLBACK;
ELSE
-- Trừ điểm từ sinh viên nguồn
UPDATE student_scores
SET score = score - points
WHERE student_id = source_student_id AND subject_id = subject_id;
-- Thêm điểm cho sinh viên đích
IF EXISTS (SELECT 1 FROM student_scores WHERE student_id = target_student_id AND subject_id = subject_id) THEN
UPDATE student_scores
SET score = score + points
WHERE student_id = target_student_id AND subject_id = subject_id;
ELSE
INSERT INTO student_scores (student_id, subject_id, score)
VALUES (target_student_id, subject_id, points);
END IF;
-- Nếu không có lỗi, hoàn tất giao dịch
IF exit_handler = FALSE THEN
COMMIT;
SET message = CONCAT('Đã chuyển ', points, ' điểm thành công');
END IF;
END IF;
END //
DELIMITER ;
-- Gọi thủ tục
CALL transfer_score(101, 102, 1, 2.5, @message);
SELECT @message;
DELIMITER //
CREATE PROCEDURE insert_new_student(
IN p_fullname VARCHAR(100),
IN p_email VARCHAR(100),
IN p_class_id INT
)
BEGIN
-- Kiểm tra email đã tồn tại chưa
IF EXISTS (SELECT 1 FROM students WHERE email = p_email) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Email đã tồn tại trong hệ thống';
END IF;
-- Kiểm tra lớp học có tồn tại không
IF NOT EXISTS (SELECT 1 FROM classes WHERE class_id = p_class_id) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Lớp học không tồn tại';
END IF;
-- Nếu dữ liệu hợp lệ, thêm sinh viên mới
INSERT INTO students (fullname, email, class_id)
VALUES (p_fullname, p_email, p_class_id);
SELECT LAST_INSERT_ID() AS new_student_id;
END //
DELIMITER ;
-- Gọi thủ tục
CALL insert_new_student('Nguyễn Văn A', '[email protected]', 1);
Chỉ mục là cấu trúc dữ liệu giúp tăng tốc độ truy vấn bằng cách tạo ra một bảng tra cứu nhanh cho một hoặc nhiều cột trong cơ sở dữ liệu.
-- Tạo chỉ mục cho cột họ tên học sinh để tìm kiếm nhanh
CREATE INDEX idx_student_fullname ON students(fullname);
-- Tạo chỉ mục cho cột email (duy nhất)
CREATE UNIQUE INDEX idx_student_email ON students(email);
-- Tạo chỉ mục cho cả lớp học và điểm số để tìm kiếm hiệu quả
CREATE INDEX idx_class_score ON students(class_id, score);
-- Tìm kiếm sử dụng chỉ mục đa cột
-- Sử dụng hiệu quả chỉ mục
SELECT * FROM students WHERE class_id = 2 AND score > 8;
-- Cũng sử dụng hiệu quả chỉ mục
SELECT * FROM students WHERE class_id = 2;
-- KHÔNG sử dụng hiệu quả chỉ mục (bỏ qua cột đầu tiên trong chỉ mục)
SELECT * FROM students WHERE score > 8;
-- Xóa chỉ mục không cần thiết
DROP INDEX idx_student_fullname ON students;
-- Tạo fulltext index cho cột mô tả khóa học
CREATE FULLTEXT INDEX idx_course_description ON courses(description);
-- Tìm kiếm văn bản sử dụng fulltext index
SELECT * FROM courses
WHERE MATCH(description) AGAINST('lập trình' IN NATURAL LANGUAGE MODE);
Để tối ưu hiệu quả, cần hiểu cách MySQL thực thi câu truy vấn. Lệnh EXPLAIN cung cấp thông tin về cách truy vấn được thực hiện.
-- Phân tích cách thực thi truy vấn
EXPLAIN SELECT * FROM students WHERE score > 8.5;
Kết quả:
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | students | ALL | NULL | NULL | NULL | NULL | 1000 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
Phân tích:
type = ALL: phải quét toàn bộ bảng (full table scan)possible_keys = NULL: không có chỉ mục phù hợp để sử dụngrows = 1000: ước tính số hàng phải quét qua-- Tạo chỉ mục cho cột score
CREATE INDEX idx_score ON students(score);
-- Phân tích lại truy vấn
EXPLAIN SELECT * FROM students WHERE score > 8.5;
Kết quả:
+----+-------------+----------+-------+---------------+----------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+----------+---------+------+------+-----------------------+
| 1 | SIMPLE | students | range | idx_score | idx_score| 4 | NULL | 200 | Using index condition |
+----+-------------+----------+-------+---------------+----------+---------+------+------+-----------------------+
Phân tích:
type = range: sử dụng chỉ mục để tìm kiếm trong một phạm vipossible_keys = idx_score: chỉ mục có thể sử dụngkey = idx_score: chỉ mục thực sự được sử dụngrows = 200: ước tính số hàng phải quét qua (giảm đáng kể)-- Phân tích truy vấn JOIN phức tạp
EXPLAIN SELECT s.student_id, s.fullname, c.class_name
FROM students s
JOIN classes c ON s.class_id = c.class_id
WHERE s.score > 8.0
ORDER BY s.fullname;
-- KHÔNG tốt: Lấy tất cả các cột
SELECT * FROM students JOIN classes ON students.class_id = classes.class_id;
-- Tốt hơn: Chỉ lấy những cột cần thiết
SELECT students.student_id, students.fullname, classes.class_name
FROM students
JOIN classes ON students.class_id = classes.class_id;
-- KHÔNG tốt: Điều kiện không sử dụng chỉ mục
SELECT * FROM students WHERE YEAR(date_of_birth) = 2000;
-- Tốt hơn: Điều kiện cho phép sử dụng chỉ mục
SELECT * FROM students
WHERE date_of_birth >= '2000-01-01' AND date_of_birth <= '2000-12-31';
-- KHÔNG tốt: Sử dụng hàm trên cột ngăn cản việc sử dụng chỉ mục
SELECT * FROM students WHERE LOWER(email) = '[email protected]';
-- Tốt hơn: Không sử dụng hàm trên cột trong WHERE
SELECT * FROM students WHERE email = '[email protected]';
-- KHÔNG tốt: Lấy tất cả kết quả khi chỉ cần một số ít
SELECT * FROM students ORDER BY score DESC;
-- Tốt hơn: Giới hạn số kết quả trả về
SELECT * FROM students ORDER BY score DESC LIMIT 10;
-- KHÔNG tốt khi có nhiều kết quả: Sử dụng IN với subquery
SELECT * FROM classes
WHERE class_id IN (SELECT class_id FROM students WHERE score > 9);
-- Tốt hơn: Sử dụng EXISTS
SELECT * FROM classes c
WHERE EXISTS (SELECT 1 FROM students s WHERE s.class_id = c.class_id AND s.score > 9);
-- Bật log cho các truy vấn chậm
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-queries.log';
SET GLOBAL long_query_time = 1; -- Log các truy vấn chạy > 1 giây
-- Xem các biến trạng thái
SHOW STATUS LIKE 'Com_%'; -- Hiển thị số lần mỗi lệnh được thực thi
-- Xem trạng thái của InnoDB
SHOW ENGINE INNODB STATUS;
-- Xem các truy vấn đang chạy
SHOW PROCESSLIST;
-- Kiểm tra các truy vấn tốn thời gian dài
SELECT * FROM information_schema.PROCESSLIST
WHERE TIME > 60; -- Các truy vấn chạy hơn 60 giây
-- Phân tích bảng để cập nhật thống kê
ANALYZE TABLE students, classes, student_scores;
-- Sử dụng EXPLAIN FORMAT=JSON để có thêm chi tiết về kế hoạch thực thi
EXPLAIN FORMAT=JSON
SELECT s.student_id, s.fullname, AVG(ss.score) as avg_score
FROM students s
JOIN student_scores ss ON s.student_id = ss.student_id
WHERE s.class_id = 3
GROUP BY s.student_id, s.fullname
HAVING avg_score > 7.5
ORDER BY avg_score DESC;
Chuẩn hóa là quá trình cấu trúc cơ sở dữ liệu để giảm thiểu sự dư thừa và đảm bảo tính nhất quán của dữ liệu. Phi chuẩn hóa là quá trình ngược lại, thêm dư thừa có chủ đích để tối ưu hiệu suất.
Bảng student_courses ban đầu:
| student_id | student_name | course_id | course_name | teacher_name | score |
|---|---|---|---|---|---|
| 101 | Nguyễn Văn A | C001 | SQL Cơ bản | Trần Văn X | 8.5 |
| 102 | Lê Thị B | C001 | SQL Cơ bản | Trần Văn X | 9.0 |
| 101 | Nguyễn Văn A | C002 | HTML/CSS | Phạm Thị Y | 7.5 |
| 102 | Lê Thị B | C002 | HTML/CSS | Phạm Thị Y | 8.0 |
Vấn đề:
Dữ liệu trong mỗi cột phải là giá trị nguyên tử (không được chia nhỏ hơn).
-- Ví dụ bảng vi phạm 1NF
CREATE TABLE contacts (
id INT PRIMARY KEY,
name VARCHAR(100),
phone_numbers VARCHAR(255) -- Lưu nhiều số điện thoại trong một cột "098765432, 012345678"
);
-- Sửa thành 1NF: Tách thành 2 bảng
CREATE TABLE contacts (
id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE contact_phones (
contact_id INT,
phone_number VARCHAR(15),
PRIMARY KEY (contact_id, phone_number),
FOREIGN KEY (contact_id) REFERENCES contacts(id)
);
Phải đạt 1NF và mọi cột không khóa phải phụ thuộc đầy đủ vào khóa chính (không phụ thuộc vào một phần của khóa chính).
-- Ví dụ bảng vi phạm 2NF
CREATE TABLE student_courses (
student_id INT,
course_id INT,
student_name VARCHAR(100), -- Phụ thuộc vào student_id (một phần của khóa chính)
course_name VARCHAR(100), -- Phụ thuộc vào course_id (một phần của khóa chính)
score DECIMAL(4,2),
PRIMARY KEY (student_id, course_id)
);
-- Sửa thành 2NF: Tách thành 3 bảng
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE courses (
id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE enrollments (
student_id INT,
course_id INT,
score DECIMAL(4,2),
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(id),
FOREIGN KEY (course_id) REFERENCES courses(id)
);
Phải đạt 2NF và không có cột không khóa phụ thuộc vào cột không khóa khác (phụ thuộc bắc cầu).
-- Ví dụ bảng vi phạm 3NF
CREATE TABLE courses (
id INT PRIMARY KEY,
name VARCHAR(100),
teacher_id INT,
teacher_name VARCHAR(100) -- Phụ thuộc vào teacher_id (không phải khóa chính)
);
-- Sửa thành 3NF: Tách thành 2 bảng
CREATE TABLE teachers (
id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE courses (
id INT PRIMARY KEY,
name VARCHAR(100),
teacher_id INT,
FOREIGN KEY (teacher_id) REFERENCES teachers(id)
);
-- Lưu trữ dữ liệu tổng hợp để tăng hiệu suất truy vấn
CREATE TABLE classes (
id INT PRIMARY KEY,
name VARCHAR(100),
student_count INT, -- Dư thừa có tính toán
avg_score DECIMAL(4,2) -- Dư thừa có tính toán
);
-- Procedure cập nhật dữ liệu tổng hợp
DELIMITER //
CREATE PROCEDURE update_class_statistics(IN class_id INT)
BEGIN
-- Cập nhật số lượng sinh viên
UPDATE classes c
SET student_count = (
SELECT COUNT(*) FROM students s WHERE s.class_id = c.id
)
WHERE c.id = class_id;
-- Cập nhật điểm trung bình
UPDATE classes c
SET avg_score = (
SELECT AVG(score)
FROM students s
WHERE s.class_id = c.id
)
WHERE c.id = class_id;
END //
DELIMITER ;
Mô hình ER là một cách biểu diễn các thực thể và mối quan hệ giữa chúng trong cơ sở dữ liệu.
+----------------+ +----------------+
| Students | | Courses |
+----------------+ +----------------+
| id (PK) | | id (PK) |
| fullname | | course_code |
| email | | title |
| date_of_birth | | description |
| address | | credits |
+----------------+ +----------------+
| |
| |
+--------------------------+
| Enrollments |
+--------------------------+
| student_id (FK) |
| course_id (FK) |
| enrollment_date |
| grade |
+--------------------------+
-- Triển khai mô hình ER thành các bảng
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
fullname VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
date_of_birth DATE,
address VARCHAR(255)
);
CREATE TABLE courses (
id INT AUTO_INCREMENT PRIMARY KEY,
course_code VARCHAR(10) UNIQUE,
title VARCHAR(100) NOT NULL,
description TEXT,
credits INT
);
CREATE TABLE enrollments (
student_id INT,
course_id INT,
enrollment_date DATE DEFAULT (CURRENT_DATE),
grade DECIMAL(4,2),
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(id),
FOREIGN KEY (course_id) REFERENCES courses(id)
);
-- Một giáo viên phụ trách nhiều khóa học
CREATE TABLE teachers (
id INT AUTO_INCREMENT PRIMARY KEY,
fullname VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
department VARCHAR(50)
);
CREATE TABLE courses (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(100) NOT NULL,
description TEXT,
teacher_id INT,
FOREIGN KEY (teacher_id) REFERENCES teachers(id)
);
-- Sinh viên có thể đăng ký nhiều khóa học và mỗi khóa học có nhiều sinh viên
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
fullname VARCHAR(100) NOT NULL
);
CREATE TABLE courses (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(100) NOT NULL
);
CREATE TABLE student_courses (
student_id INT,
course_id INT,
registration_date DATE,
grade DECIMAL(4,2),
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(id),
FOREIGN KEY (course_id) REFERENCES courses(id)
);
-- Mỗi sinh viên có một hồ sơ chi tiết
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
fullname VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE
);
CREATE TABLE student_profiles (
student_id INT PRIMARY KEY,
bio TEXT,
address VARCHAR(255),
phone VARCHAR(15),
avatar_url VARCHAR(255),
FOREIGN KEY (student_id) REFERENCES students(id)
);
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY, -- Đảm bảo mỗi sinh viên có một id duy nhất
student_code VARCHAR(10) UNIQUE NOT NULL, -- Mã sinh viên cũng phải duy nhất
fullname VARCHAR(100) NOT NULL
);
CREATE TABLE enrollments (
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT,
course_id INT,
enrollment_date DATE,
FOREIGN KEY (student_id) REFERENCES students(id)
ON DELETE CASCADE -- Xóa tự động khi sinh viên bị xóa
ON UPDATE CASCADE, -- Cập nhật tự động khi id sinh viên thay đổi
FOREIGN KEY (course_id) REFERENCES courses(id)
ON DELETE RESTRICT -- Không cho phép xóa khóa học nếu có sinh viên đăng ký
);
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
fullname VARCHAR(100) NOT NULL,
age INT CHECK (age >= 18), -- Đảm bảo tuổi >= 18
email VARCHAR(100) UNIQUE CHECK (email LIKE '%@%.%') -- Đảm bảo email có định dạng hợp lệ
);
-- Với MySQL < 8.0.16 không hỗ trợ CHECK trực tiếp, có thể dùng TRIGGER
DELIMITER //
CREATE TRIGGER check_student_age
BEFORE INSERT ON students
FOR EACH ROW
BEGIN
IF NEW.age < 18 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Tuổi phải lớn hơn hoặc bằng 18';
END IF;
END //
DELIMITER ;
CREATE TABLE enrollments (
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT,
course_id INT,
enrollment_date DATE DEFAULT (CURRENT_DATE), -- Tự động set ngày hiện tại
status VARCHAR(20) DEFAULT 'Active', -- Trạng thái mặc định
FOREIGN KEY (student_id) REFERENCES students(id),
FOREIGN KEY (course_id) REFERENCES courses(id)
);
CREATE TABLE teachers (
id INT AUTO_INCREMENT PRIMARY KEY,
fullname VARCHAR(100) NOT NULL, -- Không được để trống
email VARCHAR(100) NOT NULL UNIQUE, -- Không được để trống và phải duy nhất
department VARCHAR(50)
);
-- Không hiệu quả
CREATE TABLE products (
id VARCHAR(255) PRIMARY KEY, -- Dùng VARCHAR cho id
name VARCHAR(255), -- Dùng VARCHAR quá lớn cho tên sản phẩm
price VARCHAR(50), -- Lưu số tiền dưới dạng chuỗi
description TEXT -- Dùng TEXT cho mọi mô tả
);
-- Hiệu quả hơn
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY, -- Dùng INT tiết kiệm hơn cho ID
name VARCHAR(100), -- Giới hạn kích thước hợp lý
price DECIMAL(10,2), -- Dùng DECIMAL cho giá tiền
description VARCHAR(1000) -- Giới hạn kích thước cho mô tả ngắn
);
-- Phân vùng dữ liệu theo năm để cải thiện hiệu suất truy vấn
CREATE TABLE orders (
id INT AUTO_INCREMENT,
customer_id INT,
order_date DATE,
amount DECIMAL(10,2),
PRIMARY KEY(id, order_date)
)
PARTITION BY RANGE (YEAR(order_date)) (
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION pOthers VALUES LESS THAN MAXVALUE
);
-- Truy vấn hiệu quả chỉ quét một phân vùng
SELECT * FROM orders WHERE order_date BETWEEN '2022-01-01' AND '2022-12-31';
-- Tạo chỉ mục đơn cho các cột thường dùng trong WHERE
CREATE INDEX idx_student_email ON students(email);
-- Tạo chỉ mục kết hợp cho các cột thường được sử dụng cùng nhau
CREATE INDEX idx_course_dept_semester ON courses(department_id, semester);
-- Tạo chỉ mục bao gồm (covering index) để tránh tìm kiếm dữ liệu
CREATE INDEX idx_student_list ON students(class_id, fullname, email);
-- Cho phép truy vấn sau đây chỉ sử dụng chỉ mục mà không cần truy cập vào bảng:
-- SELECT fullname, email FROM students WHERE class_id = 5;
-- Tạo bảng tổng hợp thông tin thống kê
CREATE TABLE class_statistics (
class_id INT PRIMARY KEY,
total_students INT,
avg_score DECIMAL(4,2),
highest_score DECIMAL(4,2),
lowest_score DECIMAL(4,2),
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Procedure cập nhật bảng tổng hợp
DELIMITER //
CREATE PROCEDURE refresh_class_statistics()
BEGIN
-- Xóa dữ liệu cũ
TRUNCATE TABLE class_statistics;
-- Chèn dữ liệu mới
INSERT INTO class_statistics (class_id, total_students, avg_score, highest_score, lowest_score)
SELECT
class_id,
COUNT(*) AS total_students,
AVG(score) AS avg_score,
MAX(score) AS highest_score,
MIN(score) AS lowest_score
FROM students
GROUP BY class_id;
-- Cập nhật thời gian
UPDATE class_statistics SET last_updated = CURRENT_TIMESTAMP;
END //
DELIMITER ;
-- Lên lịch cập nhật mỗi ngày
CREATE EVENT refresh_class_statistics
ON SCHEDULE EVERY 1 DAY
DO
CALL refresh_class_statistics();
-- Thiết kế schema phân cấp
CREATE DATABASE school_management;
USE school_management;
-- Bảng các phòng ban
CREATE TABLE departments (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
-- Bảng nhân viên (đặt trong schema chính)
CREATE TABLE staff (
id INT AUTO_INCREMENT PRIMARY KEY,
fullname VARCHAR(100) NOT NULL,
department_id INT,
FOREIGN KEY (department_id) REFERENCES departments(id)
);
-- Tạo schema riêng cho dữ liệu học tập
CREATE DATABASE school_management_academic;
USE school_management_academic;
-- Bảng học sinh
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
fullname VARCHAR(100) NOT NULL
);
-- Thiết lập quyền truy cập
GRANT SELECT, INSERT, UPDATE ON school_management.staff TO 'admin_user'@'localhost';
GRANT SELECT ON school_management_academic.students TO 'teacher_user'@'localhost';
Cho dữ liệu bán hàng chưa được chuẩn hóa, hãy phân tích và thiết kế lại theo các dạng chuẩn 1NF, 2NF và 3NF
-- Bảng chưa chuẩn hóa
CREATE TABLE sales (
order_id INT,
customer_name VARCHAR(100),
product_name VARCHAR(100),
quantity INT,
price DECIMAL(10,2),
order_date DATE
);
Thiết kế mô hình ER cho hệ thống quản lý thư viện, bao gồm sách, độc giả, mượn trả sách; Chuyển đổi mô hình ER thành các bảng SQL với đầy đủ ràng buộc; Xác định các chỉ mục cần thiết để cải thiện hiệu suất truy vấn
Quản lý người dùng và phân quyền
Tạo người dùng:
CREATE USER 'student_user'@'localhost' IDENTIFIED BY 'secure_password';
Cấp quyền cụ thể:
-- Cấp quyền SELECT cho một bảng cụ thể
GRANT SELECT ON school_management.students TO 'student_user'@'localhost';
-- Cấp nhiều loại quyền
GRANT SELECT, INSERT, UPDATE ON school_management.* TO 'teacher_user'@'localhost';
-- Cấp tất cả quyền (chỉ nên cấp cho admin)
GRANT ALL PRIVILEGES ON school_management.* TO 'admin_user'@'localhost';
Thu hồi quyền:
REVOKE INSERT, UPDATE ON school_management.students FROM 'student_user'@'localhost';
Xem quyền của người dùng:
SHOW GRANTS FOR 'student_user'@'localhost';
Backup và phục hồi dữ liệu
# Sử dụng mysqldump để tạo backup
mysqldump -u root -p school_management > school_backup.sql
# Backup chỉ cấu trúc bảng (không có dữ liệu)
mysqldump -u root -p --no-data school_management > schema_backup.sql
# Backup chỉ một số bảng cụ thể
mysqldump -u root -p school_management students courses > tables_backup.sql
# Phục hồi từ file backup
mysql -u root -p school_management < school_backup.sql
# Thực hiện từ trong MySQL client
SOURCE /path/to/school_backup.sql;
# Backup hàng ngày lúc 01:00 sáng
0 1 * * * mysqldump -u root -p'password' school_management > /backup/school_$(date +\%Y\%m\%d).sql
Bảo mật và phòng chống SQL Injection
-- Ví dụ nguy hiểm (KHÔNG NÊN LÀM):
$username = $_POST['username'];
$query = "SELECT * FROM users WHERE username = '$username'";
// Nếu người dùng nhập: admin' --
// Câu truy vấn trở thành: SELECT * FROM users WHERE username = 'admin' --'
Sử dụng Prepared Statements:
// PHP với PDO
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = ?");
$stmt->execute([$username]);
// JAVA với JDBC
PreparedStatement stmt = conn.prepareStatement("SELECT * FROM users WHERE username = ?");
stmt->setString(1, username);
Kiểm tra và làm sạch dữ liệu đầu vào:
$username = mysqli_real_escape_string($conn, $_POST['username']);
Sử dụng ORM (Object-Relational Mapping):
// Sử dụng Hibernate trong JAVA
User user = session.createQuery("from User where username = :username")
.setParameter("username", username)
.uniqueResult();
Theo dõi truy vấn chậm:
-- Bật log slow queries trong MySQL
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-queries.log';
SET GLOBAL long_query_time = 2; -- Log các truy vấn chạy > 2 giây
Xem trạng thái hệ thống:
-- Xem các biến trạng thái của hệ thống
SHOW STATUS;
-- Xem các biến cấu hình
SHOW VARIABLES;
-- Xem các kết nối đang hoạt động
SHOW PROCESSLIST;
Tối ưu cấu hình MySQL:
# Ví dụ cấu hình trong file my.cnf
[mysqld]
# Bộ nhớ cache cho InnoDB
innodb_buffer_pool_size = 1G
# Cache truy vấn
query_cache_size = 64M
# Kích thước tệp log
max_binlog_size = 100M
Mở rộng cơ sở dữ liệu quản lý sinh viên:
classes để lưu thông tin về các lớp họcclasses và studentssubjects để lưu thông tin môn học⬅️ Trở lại: SQL/Part2.md | 🏠 Home | ➡️ Tiếp theo: SQL/Part3.md