SQL Basics — DDL, DML

SQL-এর ক্যাটাগরি

• DDL — CREATE, ALTER, DROP, TRUNCATE
• DML — INSERT, UPDATE, DELETE
• DQL — SELECT
• DCL — GRANT, REVOKE
• TCL — COMMIT, ROLLBACK

Database তৈরি

CREATE DATABASE shop_db
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

SHOW DATABASES;
USE shop_db;
DROP DATABASE shop_db;

Table তৈরি

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

SHOW TABLES;
DESCRIBE users;
SHOW CREATE TABLE users;

ALTER TABLE

-- Column যোগ
ALTER TABLE users ADD phone VARCHAR(20) AFTER email;

-- Column পরিবর্তন
ALTER TABLE users MODIFY age SMALLINT;
ALTER TABLE users CHANGE name full_name VARCHAR(150);

-- Column মুছে
ALTER TABLE users DROP COLUMN phone;

-- Rename
ALTER TABLE users RENAME TO members;

-- Index যোগ
ALTER TABLE users ADD INDEX idx_email (email);

DROP vs TRUNCATE vs DELETE

-- DROP — পুরো table মুছে
DROP TABLE users;

-- TRUNCATE — সব row মুছে, দ্রুত
TRUNCATE TABLE users;

-- DELETE — filter করা যায়
DELETE FROM users WHERE id = 5;

INSERT

-- একটি row
INSERT INTO users (name, email, password)
VALUES ('করিম', 'karim@mail.com', 'pwd');

-- একাধিক
INSERT INTO users (name, email) VALUES
    ('করিম', 'k@mail.com'),
    ('রহিম', 'r@mail.com');

-- SELECT থেকে
INSERT INTO users_backup (name, email)
SELECT name, email FROM users WHERE status = 'inactive';

-- ON DUPLICATE KEY UPDATE (upsert)
INSERT INTO users (email, name) VALUES ('k@mail.com', 'Karim')
ON DUPLICATE KEY UPDATE name = 'Karim Updated';

UPDATE

UPDATE users SET age = 26 WHERE id = 1;

UPDATE users
SET age = 27, status = 'active'
WHERE id = 1;

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

-- ⚠️ WHERE ছাড়া সব row পরিবর্তন!

DELETE

DELETE FROM users WHERE id = 5;

DELETE FROM orders
WHERE status = 'cancelled'
    AND created_at < '2024-01-01';

Transaction

START TRANSACTION;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
COMMIT;
-- ভুল হলে: ROLLBACK;