Cú pháp MySQL cơ bản

CREATE TABLE products (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(18, 2) NOT NULL CHECK (price >= 0),
    category_id INT NOT NULL,
    metadata JSON,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_category (category_id),
    CONSTRAINT fk_products_cat FOREIGN KEY (category_id)
        REFERENCES categories(id) ON DELETE RESTRICT
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

-- Add / drop / modify column
ALTER TABLE products ADD COLUMN description TEXT;
ALTER TABLE products DROP COLUMN description;
ALTER TABLE products MODIFY COLUMN name VARCHAR(300) NOT NULL;
ALTER TABLE products CHANGE COLUMN price unit_price DECIMAL(18, 2);

-- MySQL 8.0.16+ thực sự enforce CHECK (trước 8.0.16 chỉ parse rồi ignore!)
ALTER TABLE products
    ADD CONSTRAINT chk_price_positive CHECK (price >= 0);

-- CHECK có thể reference nhiều column trong cùng row
ALTER TABLE orders
    ADD CONSTRAINT chk_date_order CHECK (shipped_at >= created_at);

CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    subtotal DECIMAL(18, 2),
    tax_rate DECIMAL(5, 2),
    total DECIMAL(18, 2) AS (subtotal * (1 + tax_rate / 100)) STORED,
    INDEX idx_total (total)
);

INSERT INTO products (name, price) VALUES ('Laptop', 32000000);

-- Multi-row
INSERT INTO products (name, price) VALUES
    ('Mouse', 800000),
    ('Keyboard', 2200000);

-- INSERT IGNORE — bỏ qua row vi phạm unique
INSERT IGNORE INTO users (email) VALUES ('alice@x.com');

-- INSERT SELECT
INSERT INTO archive_orders SELECT * FROM orders WHERE created_at < '2025-01-01';

-- Lấy ID vừa insert
INSERT INTO users (email) VALUES ('alice@x.com');
SELECT LAST_INSERT_ID();

INSERT INTO users (email, login_count)
VALUES ('alice@x.com', 1)
ON DUPLICATE KEY UPDATE
    login_count = login_count + 1,
    last_login = NOW();

-- REPLACE = DELETE (nếu duplicate key) + INSERT
-- Tương tự INSERT về syntax
REPLACE INTO users (id, email, login_count)
VALUES (1, 'alice@x.com', 1);

-- Dạng SET
REPLACE INTO users SET id = 1, email = 'alice@x.com', login_count = 1;

-- Nếu id=1 đã tồn tại → DELETE row cũ → INSERT row mới
-- ⚠️ Cảnh báo: DELETE rồi INSERT → mất AUTO_INCREMENT cũ, mất FK child rows (nếu ON DELETE CASCADE)

UPDATE products SET price = price * 1.1 WHERE category_id = 1;

-- UPDATE từ JOIN
UPDATE products p
JOIN categories c ON c.id = p.category_id
SET p.category_name = c.name;

-- UPDATE với LIMIT (đặc thù MySQL)
UPDATE products SET is_active = 0 WHERE last_used < '2025-01-01' LIMIT 1000;

DELETE FROM products WHERE is_active = 0;

-- DELETE từ JOIN
DELETE p FROM products p
JOIN categories c ON c.id = p.category_id
WHERE c.is_archived = 1;

-- DELETE với LIMIT
DELETE FROM logs WHERE created_at < '2025-01-01' ORDER BY id LIMIT 10000;

TRUNCATE TABLE logs;
-- Reset AUTO_INCREMENT về 1.
-- FK CASCADE không trigger — chỉ DELETE mới gọi trigger.

-- Import CSV vào bảng (cần FILE privilege)
LOAD DATA INFILE '/tmp/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS                                  -- bỏ qua header
(id, name, email, @created)
SET created_at = STR_TO_DATE(@created, '%Y-%m-%d %H:%i:%s');

-- Export kết quả SELECT ra file CSV
SELECT id, name, email
INTO OUTFILE '/tmp/users_export.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM users WHERE is_active = 1;

SELECT * FROM users WHERE email IS NULL;
SELECT IFNULL(email, 'no-email') FROM users;       -- MySQL function
SELECT COALESCE(email, phone, 'unknown') FROM users; -- chuẩn ANSI

-- NULL-safe equality (đặc thù MySQL)
SELECT * FROM users WHERE deleted_at <=> NULL;     -- = NULL không work, <=> work

SELECT
    LENGTH('Hello'),                        -- byte (5 ASCII, ~15 cho UTF-8 tiếng Việt)
    CHAR_LENGTH('Xin chào'),                -- ký tự (8)
    LOWER('ABC'), UPPER('abc'),
    SUBSTRING('Hello', 2, 3),               -- 'ell'
    LEFT('Hello', 3), RIGHT('Hello', 2),
    TRIM('  abc  '), TRIM(LEADING '0' FROM '000abc'),
    REPLACE('abc', 'b', 'X'),
    CONCAT('Hello', ' ', 'World'),
    CONCAT_WS(', ', 'a', 'b', 'c'),         -- 'a, b, c'
    GROUP_CONCAT(name ORDER BY name SEPARATOR ', ');  -- aggregate

SELECT
    NOW(), CURRENT_TIMESTAMP, UTC_TIMESTAMP(),
    CURDATE(), CURTIME(),
    DATE_ADD(NOW(), INTERVAL 7 DAY),
    DATE_SUB(NOW(), INTERVAL 1 MONTH),
    DATEDIFF('2026-12-31', NOW()),          -- số ngày
    TIMESTAMPDIFF(HOUR, '2026-01-01', NOW()),
    YEAR(NOW()), MONTH(NOW()), DAY(NOW()),
    DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i'),
    LAST_DAY(NOW());

SELECT
    payload->'$.type',                       -- trả JSON
    payload->>'$.type',                      -- trả TEXT (unquoted)
    JSON_EXTRACT(payload, '$.user.name'),
    JSON_UNQUOTE(JSON_EXTRACT(payload, '$.tags[0]')),
    JSON_CONTAINS(payload, '"vue"', '$.tags'),
    JSON_KEYS(payload),
    JSON_LENGTH(payload, '$.tags');

-- Modify
UPDATE events
SET payload = JSON_SET(payload, '$.processed', true)
WHERE id = 1;

UPDATE events
SET payload = JSON_REMOVE(payload, '$.temp');

SELECT
    CAST('123' AS UNSIGNED),
    CAST('2026-01-01' AS DATE),
    CONVERT('abc', BINARY);

SET @x = 10;
SELECT @x;
SELECT @max := MAX(price) FROM products;       -- := trong SELECT

DELIMITER //
CREATE PROCEDURE get_users_by_city(IN p_city VARCHAR(50))
BEGIN
    SELECT id, name FROM users WHERE city = p_city;
END //
DELIMITER ;

CALL get_users_by_city('HCM');

DELIMITER //
CREATE FUNCTION full_name(first VARCHAR(50), last VARCHAR(50))
RETURNS VARCHAR(101) DETERMINISTIC
BEGIN
    RETURN CONCAT(first, ' ', last);
END //
DELIMITER ;

SELECT full_name(first_name, last_name) FROM users;

DELIMITER //
CREATE TRIGGER trg_orders_audit
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO orders_audit (order_id, old_status, new_status, changed_at)
    VALUES (OLD.id, OLD.status, NEW.status, NOW());
END //
DELIMITER ;

-- LIMIT 2 cú pháp
SELECT ... LIMIT 10;            -- 10 đầu
SELECT ... LIMIT 10 OFFSET 20;
SELECT ... LIMIT 20, 10;        -- (offset, count) - cũ

-- IF expression
SELECT IF(price > 1000000, 'expensive', 'cheap') FROM products;

-- CASE
SELECT CASE
    WHEN price < 100000 THEN 'cheap'
    WHEN price < 1000000 THEN 'medium'
    ELSE 'expensive'
END AS tier FROM products;

-- REGEXP (RLIKE)
SELECT * FROM products WHERE name REGEXP '^[A-Z]';

-- Kết nối
mysql -u root -p -h localhost mydatabase

-- Liệt kê database / table / column
SHOW DATABASES;
SHOW TABLES;
SHOW CREATE TABLE products;                     -- xem full DDL
DESCRIBE products;                              -- hoặc DESC products; xem schema
SHOW COLUMNS FROM products;
SHOW INDEX FROM products;
SHOW TABLE STATUS LIKE 'products';              -- engine, row count estimate, collation

-- Xem process đang chạy
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;                          -- xem full query text
KILL 42;                                        -- kill connection ID

-- Xem biến hệ thống
SHOW VARIABLES LIKE '%innodb%';
SHOW STATUS LIKE '%connections%';

-- Import / Export từ CLI
mysql -u root -p mydb < backup.sql              -- import
mysqldump -u root -p mydb > backup.sql          -- export toàn bộ DB
mysqldump -u root -p mydb products > products.sql -- export 1 bảng