MySQL / Эцсийн төсөл
Эцсийн төсөл: Онлайн дэлгүүрийн database
Энэ хичээлд MySQL курсын бүх сэдвийг нэгтгэн онлайн дэлгүүрийн бодит database дизайн хийнэ. users, categories, products, orders, order_items хүснэгтүүд үүсгэж, CRUD query, JOIN, aggregate, transaction бичнэ.
Системийн тойм
Бид дараах бизнесийн шаардлагатай онлайн дэлгүүрийн database дизайн хийнэ:
- Хэрэглэгч бүртгэл үүсгэж нэвтэрнэ
- Барааг ангиллаар нь харж захиална
- Нэг захиалганд олон бараа байх боломжтой
- Захиалгын төлөв өөрчлөгдөнө (хүлээгдэж байна → боловсруулж байна → илгээсэн → хүргэгдсэн)
- Бараа нөөц тоо хяналттай байна
Алхам 1: Database үүсгэх
sql
CREATE DATABASE IF NOT EXISTS online_shop
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
USE online_shop;
utf8mb4 нь Монгол Кирилл тэмдэгтийг зөв хадгалах кодчилол.
Алхам 2: Хүснэгтүүд үүсгэх
users — хэрэглэгч
sql
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
phone VARCHAR(20),
is_active BOOLEAN NOT NULL DEFAULT TRUE,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP
);
categories — ангилал
sql
CREATE TABLE categories (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
slug VARCHAR(100) NOT NULL UNIQUE, -- URL-д хэрэглэх: 'electronics'
parent_id INT NULL, -- дэд ангилалд ашиглана
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
);
parent_id нь өөрийн хүснэгт рүүгээ FK харуулж байна — дэд ангилал хийх боломж олгоно.
products — бараа
sql
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
category_id INT NOT NULL,
name VARCHAR(255) NOT NULL,
description TEXT,
price DECIMAL(15, 2) NOT NULL,
stock INT NOT NULL DEFAULT 0,
sku VARCHAR(100) UNIQUE, -- барааны дотоод код
is_active BOOLEAN NOT NULL DEFAULT TRUE,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (category_id) REFERENCES categories(id)
);
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_price ON products(price);
CREATE INDEX idx_products_active ON products(is_active);
orders — захиалга
sql
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled')
NOT NULL DEFAULT 'pending',
total DECIMAL(15, 2) NOT NULL DEFAULT 0,
address VARCHAR(500) NOT NULL,
note TEXT,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
order_items — захиалгын мөрүүд
sql
CREATE TABLE order_items (
id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(15, 2) NOT NULL, -- захиалах үеийн үнэ хадгална
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES products(id)
);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);
unit_price-ийг тусад нь хадгалах нь чухал — бараа нь дараа нь үнэ өөрчилсөн ч захиалгын тооцоо хэвээр үлдэнэ.
Алхам 3: Туршилтын өгөгдөл нэмэх
sql
-- Ангилал
INSERT INTO categories (name, slug, parent_id) VALUES
('Электроник', 'electronics', NULL),
('Гар утас', 'mobile-phones', 1),
('Зөөврийн компьютер', 'laptops', 1),
('Хувцас', 'clothing', NULL),
('Эрэгтэй хувцас','mens-clothing', 4);
-- Хэрэглэгч
INSERT INTO users (email, password_hash, first_name, last_name, phone) VALUES
('bold@example.com', '$2b$10$abc...', 'Болд', 'Батбаяр', '99001122'),
('enkh@example.com', '$2b$10$def...', 'Энхбат', 'Дорж', '99334455'),
('oyun@example.com', '$2b$10$ghi...', 'Оюун', 'Ганбаат', NULL);
-- Бараа
INSERT INTO products (category_id, name, price, stock, sku) VALUES
(2, 'iPhone 15 Pro', 2_499_000, 20, 'APPL-IP15P-256'),
(2, 'Samsung Galaxy S24', 1_899_000, 35, 'SAMS-S24-128'),
(3, 'MacBook Air M3', 4_299_000, 10, 'APPL-MBA-M3-8'),
(3, 'Dell XPS 15', 3_150_000, 8, 'DELL-XPS15-512'),
(5, 'Хөвөн цамц', 59_000, 150, 'TSHIRT-COTTON-M');
Алхам 4: CRUD query
Хэрэглэгч хайх
sql
-- Бүх идэвхтэй хэрэглэгч
SELECT id, first_name, last_name, email, created_at
FROM users
WHERE is_active = TRUE
ORDER BY created_at DESC;
-- И-мэйлээр хайх (нэвтрэлтэд)
SELECT id, email, password_hash, first_name
FROM users
WHERE email = 'bold@example.com'
AND is_active = TRUE;
Бараа хайх
sql
-- Ангиллын барааг үнээр нь эрэмбэлэх
SELECT p.id, p.name, p.price, p.stock, c.name AS category
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.category_id = 2
AND p.is_active = TRUE
AND p.stock > 0
ORDER BY p.price ASC;
-- Үнийн хязгаараар хайх
SELECT name, price
FROM products
WHERE price BETWEEN 1_000_000 AND 3_000_000
AND is_active = TRUE
ORDER BY price;
Алхам 5: Захиалга өгөх — Transaction
sql
-- Нэг гүйлгээнд:
-- 1. Барааны нөөц шалгах
-- 2. Захиалга үүсгэх
-- 3. Захиалгын мөрүүд нэмэх
-- 4. Нийт дүн тооцоолох
-- 5. Барааны нөөц хасах
START TRANSACTION;
-- Нөөц шалгах (SELECT ... FOR UPDATE — бусад гүйлгээг хүлээлгэнэ)
SELECT id, stock FROM products WHERE id = 1 FOR UPDATE;
SELECT id, stock FROM products WHERE id = 2 FOR UPDATE;
-- Захиалга үүсгэх
INSERT INTO orders (user_id, status, address, total)
VALUES (1, 'pending', 'УБ хот, Сүхбаатар дүүрэг', 0);
SET @order_id = LAST_INSERT_ID();
-- Мөрүүд нэмэх
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(@order_id, 1, 1, 2499000), -- iPhone 1 ширхэг
(@order_id, 2, 2, 1899000); -- Samsung 2 ширхэг
-- Нийт дүн тооцоолох, шинэчлэх
UPDATE orders
SET total = (
SELECT SUM(quantity * unit_price)
FROM order_items
WHERE order_id = @order_id
)
WHERE id = @order_id;
-- Нөөц хасах
UPDATE products SET stock = stock - 1 WHERE id = 1;
UPDATE products SET stock = stock - 2 WHERE id = 2;
COMMIT;
Алхам 6: JOIN query
Хэрэглэгчийн захиалгууд
sql
SELECT
o.id AS захиалга_id,
o.status AS төлөв,
o.total AS нийт,
o.created_at AS огноо,
u.first_name AS хэрэглэгч
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.user_id = 1
ORDER BY o.created_at DESC;
Захиалгын дэлгэрэнгүй
sql
SELECT
oi.quantity AS тоо,
oi.unit_price AS үнэ,
oi.quantity * oi.unit_price AS дүн,
p.name AS бараа,
p.sku AS код
FROM order_items oi
JOIN products p ON oi.product_id = p.id
WHERE oi.order_id = 1;
Ангилал — бараа тооноор
sql
SELECT
c.name AS ангилал,
COUNT(p.id) AS бараа_тоо,
AVG(p.price) AS дундаж_үнэ,
MIN(p.price) AS хамгийн_хямд,
MAX(p.price) AS хамгийн_үнэтэй
FROM categories c
LEFT JOIN products p ON c.id = p.category_id AND p.is_active = TRUE
GROUP BY c.id, c.name
ORDER BY бараа_тоо DESC;
Алхам 7: Aggregate — тайлан query
Борлуулалтын тайлан
sql
SELECT
DATE_FORMAT(o.created_at, '%Y-%m') AS сар,
COUNT(DISTINCT o.id) AS захиалгын_тоо,
COUNT(DISTINCT o.user_id) AS хэрэглэгчийн_тоо,
SUM(o.total) AS нийт_орлого,
AVG(o.total) AS дундаж_захиалга
FROM orders o
WHERE o.status != 'cancelled'
GROUP BY DATE_FORMAT(o.created_at, '%Y-%m')
ORDER BY сар DESC;
Шилдэг борлуулалттай бараа
sql
SELECT
p.name AS бараа,
SUM(oi.quantity) AS нийт_зарагдсан,
SUM(oi.quantity * oi.unit_price) AS нийт_орлого
FROM order_items oi
JOIN products p ON oi.product_id = p.id
JOIN orders o ON oi.order_id = o.id
WHERE o.status != 'cancelled'
GROUP BY p.id, p.name
ORDER BY нийт_орлого DESC
LIMIT 5;
Хамгийн идэвхтэй хэрэглэгч
sql
SELECT
CONCAT(u.first_name, ' ', u.last_name) AS нэр,
u.email,
COUNT(o.id) AS захиалгын_тоо,
SUM(o.total) AS нийт_зарцуулсан
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status != 'cancelled'
GROUP BY u.id, u.first_name, u.last_name, u.email
HAVING нийт_зарцуулсан > 1_000_000
ORDER BY нийт_зарцуулсан DESC;
Алхам 8: View үүсгэх
sql
-- Захиалгын хураангуй харагдац
CREATE VIEW order_summary AS
SELECT
o.id,
o.status,
o.total,
o.created_at,
CONCAT(u.first_name, ' ', u.last_name) AS customer_name,
u.email,
COUNT(oi.id) AS item_count
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
GROUP BY o.id, o.status, o.total, o.created_at,
u.first_name, u.last_name, u.email;
-- Ашиглах
SELECT * FROM order_summary WHERE status = 'pending';
Алхам 9: Trigger — нөөц автомат хяналт
sql
-- Захиалга цуцлагдахад нөөцийг сэргээх
DELIMITER //
CREATE TRIGGER restore_stock_on_cancel
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
IF NEW.status = 'cancelled' AND OLD.status != 'cancelled' THEN
UPDATE products p
JOIN order_items oi ON p.id = oi.product_id
SET p.stock = p.stock + oi.quantity
WHERE oi.order_id = NEW.id;
END IF;
END //
DELIMITER ;
Сорилго
Дараах query-г өөрөө бичиж туршаарай:
- Нөөц дуусаж байгаа (stock < 5) бараануудыг жагсаа
- Өнгөрсөн 30 хоногт захиалга өгөөгүй хэрэглэгчдийг ол
- Ангилал бүрийн хамгийн их зарагдсан барааг ол
- Нэг захиалгад хамгийн их мөнгө зарцуулсан хэрэглэгчийг ол
- Өнөөдрийн захиалгуудын нийт дүнг тооц
MySQL курсыг амжилттай дууслаа! Та реляцийн өгөгдлийн сангийн үндсүүдийг эзэмшлээ. Дараагийн алхам: PostgreSQL — дэвшилтэт SQL боломжуудтай танилцах, эсвэл Node.js, Python ашиглан бүрэн аппликейшн бүтээх.