SQL Cơ Bản — DDL / DML / DQL / DCL / TCL

1. Tổng quan 5 nhóm câu lệnh SQL

SQL chia thành 5 nhóm theo mục đích sử dụng:

2. DDL — Data Definition Language

DDL định nghĩa cấu trúc của database: schema, table, index, constraint, view, trigger, procedure.

2.1. CREATE

-- Tạo database (Postgres):
CREATE DATABASE shop;

-- Tạo schema (namespace trong DB):
CREATE SCHEMA inventory;

-- Tạo bảng:
CREATE TABLE users (
  id          BIGSERIAL PRIMARY KEY,
  email       VARCHAR(255) NOT NULL UNIQUE,
  name        VARCHAR(100),
  role        VARCHAR(20) NOT NULL DEFAULT 'user',
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT  valid_role CHECK (role IN ('user','admin','moderator'))
);

-- Tạo index:
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created ON users(created_at DESC);
CREATE UNIQUE INDEX idx_users_email_uniq ON users(LOWER(email));  -- functional index

-- Tạo view:
CREATE VIEW active_users AS
SELECT id, email, name FROM users WHERE deleted_at IS NULL;

-- Tạo materialized view (cache, refresh thủ công):
CREATE MATERIALIZED VIEW user_stats AS
SELECT user_id, COUNT(*) AS order_count, SUM(total) AS total_spent
FROM orders GROUP BY user_id;

REFRESH MATERIALIZED VIEW user_stats;

2.2. ALTER

-- Thêm cột:
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Thêm cột với default — Postgres 11+ làm rất nhanh, không rewrite table:
ALTER TABLE users ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'active';

-- Đổi tên cột:
ALTER TABLE users RENAME COLUMN name TO full_name;

-- Đổi kiểu dữ liệu (cẩn thận, có thể rewrite cả bảng):
ALTER TABLE users ALTER COLUMN phone TYPE VARCHAR(30);

-- Thêm constraint:
ALTER TABLE orders ADD CONSTRAINT positive_total CHECK (total > 0);
ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);

-- Bỏ constraint:
ALTER TABLE orders DROP CONSTRAINT positive_total;

-- Đổi tên bảng:
ALTER TABLE users RENAME TO accounts;

-- Bỏ cột (nguy hiểm — mất dữ liệu):
ALTER TABLE users DROP COLUMN deprecated_field;

2.3. DROP

DROP INDEX idx_users_email;
DROP VIEW active_users;
DROP TABLE users;            -- ❌ lỗi nếu có FK trỏ tới
DROP TABLE users CASCADE;    -- ⚠ xóa luôn FK, view phụ thuộc
DROP DATABASE shop;          -- ⚠ KHÔNG hoàn tác được

2.4. TRUNCATE

-- Xóa toàn bộ dữ liệu nhưng GIỮ schema:
TRUNCATE TABLE logs;

-- Reset cả sequence (id trở về 1):
TRUNCATE TABLE logs RESTART IDENTITY;

-- Cascade (xóa luôn dữ liệu các bảng FK):
TRUNCATE TABLE users CASCADE;

TRUNCATE vs DELETE:

3. DML — Data Manipulation Language

3.1. INSERT

-- Insert 1 dòng đầy đủ:
INSERT INTO users (email, name, role)
VALUES ('alice@x.com', 'Alice', 'admin');

-- Insert nhiều dòng cùng lúc (NHANH HƠN nhiều insert riêng):
INSERT INTO users (email, name) VALUES
  ('bob@x.com',   'Bob'),
  ('carol@x.com', 'Carol'),
  ('dave@x.com',  'Dave');

-- Insert kết quả từ SELECT:
INSERT INTO users_archive (id, email, archived_at)
SELECT id, email, NOW() FROM users WHERE deleted_at IS NOT NULL;

-- RETURNING — lấy lại id vừa insert (Postgres):
INSERT INTO users (email, name) VALUES ('eve@x.com', 'Eve')
RETURNING id, created_at;

3.2. UPSERT (INSERT … ON CONFLICT)

-- "Insert hoặc update nếu trùng key":
INSERT INTO users (email, name)
VALUES ('alice@x.com', 'Alice Updated')
ON CONFLICT (email) DO UPDATE
  SET name = EXCLUDED.name,
      updated_at = NOW();

-- Insert hoặc bỏ qua nếu trùng:
INSERT INTO users (email, name) VALUES ('alice@x.com', 'Alice')
ON CONFLICT (email) DO NOTHING;

EXCLUDED tham chiếu đến dòng định insert (bị conflict). Cú pháp này thay thế cho REPLACE INTO (MySQL) hay MERGE.

3.3. UPDATE

-- Update 1 cột:
UPDATE users SET name = 'Alice Smith' WHERE id = 1;

-- Update nhiều cột:
UPDATE users
SET name = 'Alice Smith', updated_at = NOW()
WHERE id = 1;

-- Update tất cả dòng (CỰC NGUY HIỂM nếu quên WHERE):
UPDATE users SET status = 'active';

-- Update có điều kiện phức tạp:
UPDATE products
SET price = price * 1.1                  -- tăng 10%
WHERE category = 'electronics' AND stock > 0;

-- Update từ JOIN khác (Postgres):
UPDATE orders
SET status = 'cancelled'
FROM users u
WHERE orders.user_id = u.id AND u.deleted_at IS NOT NULL;

-- Update với RETURNING:
UPDATE users SET role = 'admin' WHERE id = 1
RETURNING id, email, role;

3.4. DELETE

-- Xóa theo điều kiện:
DELETE FROM users WHERE id = 5;
DELETE FROM logs WHERE created_at < NOW() - INTERVAL '90 days';

-- Xóa tất cả (giữ schema):
DELETE FROM users;     -- chậm, nên dùng TRUNCATE

-- Xóa với JOIN (Postgres):
DELETE FROM orders o
USING users u
WHERE o.user_id = u.id AND u.email LIKE '%@spam.com';

-- Soft delete (KHUYẾN NGHỊ ở app prod):
UPDATE users SET deleted_at = NOW() WHERE id = 5;
-- App luôn query: WHERE deleted_at IS NULL

3.5. MERGE (SQL:2003 — Postgres 15+)

MERGE INTO accounts a
USING new_data n ON a.id = n.id
WHEN MATCHED THEN UPDATE SET balance = n.balance
WHEN NOT MATCHED THEN INSERT (id, balance) VALUES (n.id, n.balance);

MERGE phức tạp hơn UPSERT, hữu ích khi cần "if-else" theo trạng thái dòng.

4. DQL — SELECT, trái tim của SQL

4.1. Cấu trúc đầy đủ của SELECT

SELECT [DISTINCT] cols
FROM tables
[JOIN ... ON ...]
WHERE row_filter
GROUP BY cols
HAVING group_filter
ORDER BY cols [ASC|DESC]
LIMIT n OFFSET m;

Thứ tự VIẾT (cú pháp) khác thứ tự THỰC THI (logic):

Hệ quả: alias định nghĩa trong SELECT không dùng được ở WHERE/GROUP BY (chưa thực thi đến SELECT). Nhưng có thể dùng ở ORDER BY (đã thực thi sau SELECT).

-- ❌ Lỗi: alias chưa tồn tại khi WHERE chạy
SELECT total * 0.1 AS tax FROM orders WHERE tax > 5;

-- ✓ Hoạt động:
SELECT total * 0.1 AS tax FROM orders ORDER BY tax DESC;

4.2. SELECT cơ bản

-- Tất cả cột:
SELECT * FROM users;

-- Cột cụ thể:
SELECT id, email FROM users;

-- Alias:
SELECT id, email AS contact, name AS display_name FROM users;

-- Hằng và biểu thức:
SELECT id, email, CURRENT_DATE AS today, 'active' AS status FROM users;

-- DISTINCT (loại trùng):
SELECT DISTINCT country FROM addresses;
SELECT DISTINCT ON (user_id) user_id, created_at  -- Postgres-only
FROM orders ORDER BY user_id, created_at DESC;    -- last order per user

4.3. WHERE — lọc dòng

-- So sánh:
WHERE age = 25
WHERE age <> 25      -- != cũng được
WHERE age BETWEEN 18 AND 65    -- inclusive

-- Logic:
WHERE age > 18 AND status = 'active'
WHERE role = 'admin' OR role = 'moderator'
WHERE NOT deleted

-- Tập:
WHERE country IN ('VN','TH','SG')
WHERE country NOT IN ('US','UK')
WHERE id = ANY(ARRAY[1,2,3])         -- Postgres array

-- Pattern:
WHERE email LIKE '%@gmail.com'       -- _ = 1 ký tự, % = nhiều
WHERE email ILIKE '%@GMAIL%'         -- case-insensitive (Postgres)
WHERE email ~ '^[a-z]+@'             -- regex (Postgres)
WHERE name SIMILAR TO '(Anh|Bình|Cường)%'

-- NULL:
WHERE deleted_at IS NULL
WHERE deleted_at IS NOT NULL

4.4. ORDER BY

SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY role ASC, created_at DESC;  -- 2 cấp
SELECT * FROM users ORDER BY 2 DESC;                     -- theo cột thứ 2 (tránh)

-- NULLS FIRST/LAST (Postgres):
SELECT * FROM users ORDER BY name ASC NULLS LAST;

4.5. LIMIT / OFFSET — phân trang

-- Trang 1, 20 dòng:
SELECT * FROM users ORDER BY id LIMIT 20;

-- Trang 3 (skip 40 dòng đầu):
SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 40;

-- Postgres standard:
SELECT * FROM users ORDER BY id FETCH FIRST 20 ROWS ONLY;

5. DCL — Data Control Language (quyền)

-- Tạo user (role) trong Postgres:
CREATE ROLE app_user WITH LOGIN PASSWORD 'secret';

-- Cấp quyền:
GRANT SELECT, INSERT, UPDATE ON users TO app_user;
GRANT ALL PRIVILEGES ON DATABASE shop TO app_user;
GRANT USAGE ON SCHEMA inventory TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA inventory TO app_user;

-- Default privileges (cho table tạo TƯƠNG LAI):
ALTER DEFAULT PRIVILEGES IN SCHEMA inventory
GRANT SELECT ON TABLES TO app_user;

-- Thu hồi quyền:
REVOKE INSERT ON users FROM app_user;

-- Role-based (gán role này cho role khác):
CREATE ROLE readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT readonly TO app_user;   -- app_user "kế thừa" quyền của readonly

Khuyến nghị production:

• App connect bằng user RIÊNG, KHÔNG superuser.
• App user chỉ có quyền tối thiểu (SELECT/INSERT/UPDATE — không DROP/TRUNCATE).
• User cho migration (CI/CD) khác user cho runtime.
• Không lưu password trong code → dùng env, secret manager.

6. TCL — Transaction Control Language

Transaction là đơn vị làm việc nguyên tử. Hoặc tất cả thành công, hoặc tất cả rollback. Đây là "T trong ACID" mà ta sẽ đào sâu ở Ch6.

6.1. BEGIN / COMMIT / ROLLBACK

BEGIN;  -- hoặc START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- trừ A
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- cộng B

-- Xác nhận:
COMMIT;

-- HOẶC nếu sai gì đó:
ROLLBACK;

Khi có ROLLBACK, DB hoàn tác cả 2 UPDATE, balance trở về như cũ. Đây là tính Atomicity của ACID.

6.2. SAVEPOINT — rollback một phần

BEGIN;
INSERT INTO orders (user_id, total) VALUES (1, 100);
SAVEPOINT after_order;

INSERT INTO order_items (order_id, product_id, qty) VALUES (last_order_id(), 99, 1);
-- Lỗi: product 99 không tồn tại

ROLLBACK TO SAVEPOINT after_order;  -- chỉ undo INSERT order_items
-- order vẫn còn

COMMIT;  -- chỉ commit phần order

6.3. Auto-commit

Mặc định, mọi câu SQL đơn lẻ ngoài BEGIN đều tự commit ngay. Nghĩa là:

UPDATE users SET name = 'X' WHERE id = 1;
-- Tự động COMMIT, không thể undo

Nếu muốn an toàn, luôn bọc trong BEGIN ... COMMIT.

6.4. Read-only transaction

BEGIN ISOLATION LEVEL READ COMMITTED READ ONLY;
-- Tối ưu hơn, không cần MVCC overhead cho ghi
SELECT ...;
COMMIT;

Isolation level (READ COMMITTED, REPEATABLE READ, SERIALIZABLE) là chủ đề lớn — Ch6 sẽ đi sâu.

7. Operators & Functions thường dùng

7.1. Toán tử số học

SELECT 5 + 3, 10 - 2, 4 * 7, 20 / 3, 20 % 3, 2 ^ 10;
-- 8, 8, 28, 6 (integer division!), 2, 1024
SELECT 20.0 / 3;  -- 6.66666... (cast một bên thành float)

7.2. Hàm string

SELECT
  LENGTH('hello'),                  -- 5
  UPPER('hello'),                   -- HELLO
  LOWER('HELLO'),                   -- hello
  TRIM('  hi  '),                   -- 'hi'
  LTRIM('  hi'), RTRIM('hi  '),
  SUBSTRING('hello world' FROM 7 FOR 5),  -- 'world'
  POSITION('lo' IN 'hello'),        -- 4
  REPLACE('hello world', 'world', 'SQL'),
  CONCAT('a', 'b', 'c'),            -- 'abc'
  'a' || 'b' || 'c',                -- 'abc' (Postgres ||)
  REPEAT('ab', 3),                  -- 'ababab'
  REVERSE('hello'),                 -- 'olleh'
  SPLIT_PART('a,b,c', ',', 2);      -- 'b'

7.3. Hàm số

SELECT
  ABS(-5),           -- 5
  ROUND(3.456, 1),   -- 3.5
  CEIL(3.2),         -- 4
  FLOOR(3.8),        -- 3
  POWER(2, 10),      -- 1024
  SQRT(16),          -- 4
  MOD(10, 3),        -- 1
  RANDOM();          -- số ngẫu nhiên 0..1

7.4. Hàm thời gian

SELECT
  NOW(),                              -- 2026-05-09 10:30:00+07
  CURRENT_DATE,                       -- 2026-05-09
  CURRENT_TIME,                       -- 10:30:00+07
  EXTRACT(YEAR FROM NOW()),           -- 2026
  EXTRACT(DOW FROM NOW()),            -- day-of-week
  AGE(NOW(), '2000-01-01'),           -- '26 years 4 mons ...'
  NOW() - INTERVAL '7 days',          -- 7 ngày trước
  NOW() + INTERVAL '1 month 2 days',
  DATE_TRUNC('month', NOW()),         -- 2026-05-01 00:00:00
  TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI');

7.5. Hàm conversion

SELECT
  CAST('123' AS INTEGER),             -- 123
  '123'::INTEGER,                     -- 123 (Postgres shortcut)
  CAST(NOW() AS DATE),                -- chỉ ngày
  TO_NUMBER('123.45', '999.99'),      -- 123.45
  TO_DATE('2026-05-09', 'YYYY-MM-DD');

8. CASE expression — if-else trong SQL

8.1. Searched CASE

SELECT
  email,
  CASE
    WHEN age < 18 THEN 'minor'
    WHEN age < 65 THEN 'adult'
    ELSE 'senior'
  END AS age_group
FROM users;

8.2. Simple CASE

SELECT
  email,
  CASE role
    WHEN 'admin' THEN 1
    WHEN 'moderator' THEN 2
    ELSE 3
  END AS role_priority
FROM users
ORDER BY role_priority;

8.3. CASE trong UPDATE

UPDATE products SET price =
  CASE
    WHEN category = 'electronics' THEN price * 0.9
    WHEN category = 'clothing'    THEN price * 0.8
    ELSE price
  END;

8.4. CASE trong COUNT — đếm theo điều kiện

SELECT
  COUNT(*) AS total_users,
  COUNT(CASE WHEN role = 'admin' THEN 1 END) AS admins,
  COUNT(CASE WHEN created_at > NOW() - INTERVAL '30 days' THEN 1 END) AS recent
FROM users;

-- Ngắn hơn (Postgres):
SELECT
  COUNT(*) FILTER (WHERE role = 'admin') AS admins,
  COUNT(*) FILTER (WHERE created_at > NOW() - INTERVAL '30 days') AS recent
FROM users;

9. Aggregate functions & GROUP BY

9.1. Aggregate cơ bản

SELECT
  COUNT(*),                  -- số dòng
  COUNT(email),              -- số dòng có email NOT NULL
  COUNT(DISTINCT email),     -- số email khác nhau
  SUM(total),                -- tổng
  AVG(total),                -- trung bình
  MIN(total), MAX(total),
  STRING_AGG(email, ', '),   -- nối thành chuỗi (Postgres)
  ARRAY_AGG(email)           -- gom thành array (Postgres)
FROM orders;

9.2. GROUP BY

-- Đếm order theo user:
SELECT user_id, COUNT(*) AS order_count, SUM(total) AS total_spent
FROM orders
GROUP BY user_id;

-- Group nhiều cột:
SELECT country, role, COUNT(*) FROM users
GROUP BY country, role
ORDER BY country, role;

-- Group theo expression:
SELECT DATE_TRUNC('month', created_at) AS month, COUNT(*) AS new_users
FROM users
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

9.3. HAVING — lọc sau khi group

-- Lấy user có nhiều hơn 5 đơn hàng:
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5;

-- WHERE vs HAVING:
-- WHERE lọc DÒNG trước khi gom nhóm
-- HAVING lọc NHÓM sau khi gom nhóm

SELECT category, AVG(price) AS avg_price
FROM products
WHERE stock > 0           -- chỉ tính sản phẩm còn hàng
GROUP BY category
HAVING AVG(price) > 100;  -- chỉ trả category trung bình > 100

9.4. GROUP BY ROLLUP / CUBE / GROUPING SETS

-- ROLLUP: tạo subtotal theo từng bậc
SELECT country, role, COUNT(*) FROM users
GROUP BY ROLLUP(country, role);

-- Output:
-- VN, admin, 5
-- VN, user,  100
-- VN, NULL,  105      ← subtotal VN
-- US, admin, 2
-- US, user,  50
-- US, NULL,  52       ← subtotal US
-- NULL, NULL, 157     ← grand total

Hữu ích cho báo cáo BI. Sẽ gặp lại ở Ch11 (OLAP).

10. Bài tập

Bài 1. Cho schema:

CREATE TABLE products (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(255), category VARCHAR(50),
  price DECIMAL(10,2), stock INT
);
CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT, total DECIMAL(10,2),
  status VARCHAR(20), created_at TIMESTAMPTZ DEFAULT NOW()
);

1. Lấy 10 sản phẩm đắt nhất.
2. Lấy số lượng sản phẩm theo category.
3. Lấy category có giá trung bình > 200.
4. Đếm số order theo từng status.
5. Lấy user có nhiều hơn 5 order với tổng giá trị > 1000$.
6. Top 5 user chi nhiều tiền nhất.
7. Số order mỗi tháng trong năm 2026.

Bài 2. Viết câu UPSERT: insert hoặc update bảng user_settings(user_id, key, value). PK là (user_id, key). Nếu trùng thì update value và set updated_at = NOW().

Bài 3. Viết transaction chuyển 100$ từ account A sang account B, đảm bảo atomic.

Bài 4. Trong câu sau, vì sao alias không dùng được ở WHERE nhưng dùng được ở ORDER BY?

SELECT total * 0.1 AS tax FROM orders WHERE tax > 5;  -- ❌
SELECT total * 0.1 AS tax FROM orders ORDER BY tax;  -- ✓

Bài 5. Phân biệt khi nào dùng:

• DELETE vs TRUNCATE
• UNIQUE INDEX vs UNIQUE CONSTRAINT
• WHERE vs HAVING

11. Quiz