Mở đầu: Thiết lập dữ liệu mẫu để thực hành

1. Tạo cơ sở dữ liệu và cấu trúc bảng

-- Tạo cơ sở dữ liệu
CREATE DATABASE thuc_hanh_mysql;
USE thuc_hanh_mysql;

-- 1. Bảng nhân viên
CREATE TABLE nhan_vien (
    ma_nv INT PRIMARY KEY AUTO_INCREMENT,
    ten_nv VARCHAR(50) NOT NULL,
    phong_ban VARCHAR(50) NOT NULL,
    luong DECIMAL(10,2) NOT NULL,
    ngay_tuyen_dung DATE NOT NULL
);

-- 2. Bảng đơn hàng
CREATE TABLE don_hang (
    ma_don INT PRIMARY KEY AUTO_INCREMENT,
    ma_nv INT NOT NULL,
    ngay_ban DATE NOT NULL,
    so_tien DECIMAL(10,2) NOT NULL,
    loai_san_pham VARCHAR(50) NOT NULL,
    FOREIGN KEY (ma_nv) REFERENCES nhan_vien(ma_nv)
);

2. Chèn dữ liệu mẫu

-- Dữ liệu nhân viên
INSERT INTO nhan_vien (ten_nv, phong_ban, luong, ngay_tuyen_dung) VALUES
('Nguyen Van A', 'Kinh doanh', 8000.00, '2020-01-15'),
('Tran Thi B', 'Kinh doanh', 7500.00, '2021-03-20'),
('Le Van C', 'Ky thuat', 9000.00, '2019-05-10'),
('Pham Van D', 'Tai chinh', 8500.00, '2020-07-01'),
('Vo Thi E', 'Marketing', 7000.00, '2021-09-01');

-- Dữ liệu đơn hàng
INSERT INTO don_hang (ma_nv, ngay_ban, so_tien, loai_san_pham) VALUES
(1, '2023-01-05', 15000.00, 'Dien tu'),
(1, '2023-02-10', 8000.00, 'Van phong'),
(2, '2023-03-15', 12000.00, 'Dien tu'),
(1, '2023-04-20', 25000.00, 'Noi that'),
(2, '2023-05-25', 18000.00, 'Van phong'),
(3, '2023-06-30', 30000.00, 'Ky thuat'),
(4, '2023-07-05', 9000.00, 'Tai chinh'),
(5, '2023-08-10', 6000.00, 'Marketing');

I. Tạo View

Để tạo view trong MySQL, ta sử dụng câu lệnh CREATE VIEW.

Cú pháp:

CREATE [ OR REPLACE ] VIEW ten_view [ (danh_sach_cot) ]
AS cau_lenh_SELECT
[ WITH [ CASCADED | LOCAL ] CHECK OPTION ];

Giải thích các tham số:

1. OR REPLACE: Tùy chọn, cho phép thay thế view đã tồn tại cùng tên.
2. ten_view: Tên của view cần tạo.
3. danh_sach_cot: Danh sách tên các cột trong view, phải có số lượng tương ứng với số cột trong câu SELECT. Nếu bỏ qua, tên cột sẽ lấy theo câu SELECT.
4. cau_lenh_SELECT: Câu truy vấn SELECT để tạo view, có thể truy vấn từ nhiều bảng hoặc view khác.
5. WITH CHECK OPTION: Tùy chọn, áp dụng ràng buộc cho các thao tác INSERT và UPDATE qua view.
6. LOCAL: Chỉ kiểm tra trên view hiện tại.
7. CASCADED: Kiểm tra trên tất cả các view liên quan, là giá trị mặc định.

Các quy tắc cần lưu ý:

1. View mới tạo sẽ lưu trữ trong database hiện tại. Để tạo view trong database cụ thể, dùng dạng "ten_database.ten_view".
2. Tên view phải duy nhất và không được trùng với tên bảng.
3. Các bảng hoặc view tham chiếu trong định nghĩa phải tồn tại và người tạo view phải có quyền truy vấn trên các bảng đó.
4. Không thể tham chiếu đến biến hệ thống hoặc biến người dùng, không thể tham chiếu đến tham số của câu lệnh chuẩn bị trước.
5. Không được chứa subquery.
6. Không thể tạo bất kỳ chỉ mục nào trên view.

Tạo view thống kê kinh doanh nhân viên

Ta sẽ tạo một view có tên là thong_ke_ban_hang_nhan_vien để tổng hợp dữ liệu bán hàng của từng nhân viên. View này lấy dữ liệu từ bảng nhan_vien và don_hang,

sử dụng phép LEFT JOIN thông qua trường ma_nv để đảm bảo tất cả nhân viên đều xuất hiện trong kết quả, kể cả nhân viên chưa có đơn hàng nào.

View tính toán ba chỉ số quan trọng: tổng số đơn hàng (so_don_hang), tổng doanh số (tong_doanh_so) và trị giá trung bình mỗi đơn (gia_trung_binh_don).

Kết quả được nhóm theo mã nhân viên, tên nhân viên và phòng ban, đảm bảo mỗi nhân viên chỉ hiển thị một dòng duy nhất với thông tin tổng hợp kinh doanh của mình.

View này rất hữu ích khi cần tạo báo cáo tổng quan về hiệu suất bán hàng của nhân viên mà không phải viết lại câu truy vấn phức tạp mỗi lần sử dụng.

CREATE VIEW thong_ke_ban_hang_nhan_vien AS
SELECT 
    nv.ma_nv,
    nv.ten_nv,
    nv.phong_ban,
    COUNT(dh.ma_don) AS so_don_hang,
    SUM(dh.so_tien) AS tong_doanh_so,
    AVG(dh.so_tien) AS gia_trung_binh_don
FROM nhan_vien nv
LEFT JOIN don_hang dh ON nv.ma_nv = dh.ma_nv
GROUP BY nv.ma_nv, nv.ten_nv, nv.phong_ban;

mysql> CREATE VIEW thong_ke_ban_hang_nhan_vien AS
    -> SELECT 
    ->     nv.ma_nv,
    ->     nv.ten_nv,
    ->     nv.phong_ban,
    ->     COUNT(dh.ma_don) AS so_don_hang,
    ->     SUM(dh.so_tien) AS tong_doanh_so,
    ->     AVG(dh.so_tien) AS gia_trung_binh_don
    -> FROM nhan_vien nv
    -> LEFT JOIN don_hang dh ON nv.ma_nv = dh.ma_nv
    -> GROUP BY nv.ma_nv, nv.ten_nv, nv.phong_ban;
Query OK, 0 rows affected (0.02 sec)

II. Truy vấn View

1. Truy vấn tất cả các cột của view

-- Cú pháp cơ bản
SELECT * FROM ten_view;

Truy vấn tất cả thông tin từ view thống kê

SELECT * FROM thong_ke_ban_hang_nhan_vien;

mysql> SELECT * FROM thong_ke_ban_hang_nhan_vien;
+--------+----------+------------+-------------+---------------+----------------------+
| ma_nv  | ten_nv   | phong_ban  | so_don_hang | tong_doanh_so | gia_trung_binh_don   |
+--------+----------+------------+-------------+---------------+----------------------+
|      1 | Nguyen A | Kinh doanh |           3 |      48000.00 |        16000.000000 |
|      2 | Tran B   | Kinh doanh |           2 |      30000.00 |        15000.000000 |
|      3 | Le C     | Ky thuat   |           1 |      30000.00 |        30000.000000 |
|      4 | Pham D   | Tai chinh  |           1 |       9000.00 |         9000.000000 |
|      5 | Vo E     | Marketing  |           1 |       6000.00 |         6000.000000 |
+--------+----------+------------+-------------+---------------+----------------------+
5 rows in set (0.01 sec)

2. Truy vấn các cột cụ thể từ view

SELECT cot1, cot2, ... FROM ten_view;

Chỉ xem tên nhân viên, phòng ban và tổng doanh số

SELECT ten_nv, phong_ban, tong_doanh_so 
FROM thong_ke_ban_hang_nhan_vien;

mysql> SELECT ten_nv, phong_ban, tong_doanh_so
    -> FROM thong_ke_ban_hang_nhan_vien;
+----------+------------+---------------+
| ten_nv   | phong_ban  | tong_doanh_so |
+----------+------------+---------------+
| Nguyen A | Kinh doanh |      48000.00 |
| Tran B   | Kinh doanh |      30000.00 |
| Le C     | Ky thuat   |      30000.00 |
| Pham D   | Tai chinh  |       9000.00 |
| Vo E     | Marketing  |       6000.00 |
+----------+------------+---------------+
5 rows in set (0.00 sec)

3. Truy vấn với điều kiện lọc

SELECT cot1, cot2, ... 
FROM ten_view
WHERE dieu_kien;

Tìm nhân viên thuộc phòng Kinh doanh và có tổng doanh số trên 20000

SELECT ten_nv, tong_doanh_so
FROM thong_ke_ban_hang_nhan_vien
WHERE phong_ban = 'Kinh doanh' AND tong_doanh_so > 20000;

mysql> SELECT ten_nv, tong_doanh_so
    -> FROM thong_ke_ban_hang_nhan_vien
    -> WHERE phong_ban = 'Kinh doanh' AND tong_doanh_so > 20000;
+----------+---------------+
| ten_nv   | tong_doanh_so |
+----------+---------------+
| Nguyen A |      48000.00 |
| Tran B   |      30000.00 |
+----------+---------------+
2 rows in set (0.00 sec)