📌 Ne Zaman Kullanılır?

• ✅ Web uygulama, CMS, Laravel/PHP projeleri, e-ticaret, shared hosting
• ⚠️ Büyük veri analizi için PostgreSQL daha iyi (JSONB, Window Fn)
• ❌ Esnek schema gereken durum (MongoDB), enterprise (MSSQL/.NET)

Önerilen Kullanım: Laravel + MySQL web uygulaması Alternatifler: PostgreSQL (gelişmiş), MariaDB (fork), SQLite (küçük proje)

MySQL

Kurulum (Installation)

Ubuntu

sudo apt update
sudo apt install mysql-server -y
sudo systemctl start mysql
sudo systemctl enable mysql
sudo mysql_secure_installation

Docker

docker run -d \
  --name mysql-dev \
  -e MYSQL_ROOT_PASSWORD=secret123 \
  -e MYSQL_DATABASE=myapp \
  -e MYSQL_USER=appuser \
  -e MYSQL_PASSWORD=apppass \
  -p 3306:3306 \
  mysql:8.0

# Baglanti (Connect)
docker exec -it mysql-dev mysql -u root -psecret123

Temel Komutlar (Basic Commands)

-- Veritabani islemleri (Database operations)
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SHOW DATABASES;
USE myapp;
DROP DATABASE IF EXISTS old_db;

-- Tablo listeleme (List tables)
SHOW TABLES;
DESCRIBE users;
SHOW CREATE TABLE users;

Veri Tipleri (Data Types)

Type	Description	Example
INT	Tam sayi / Integer	age INT
BIGINT	Büyük tam sayi	id BIGINT AUTO_INCREMENT
DECIMAL(p,s)	Hassas ondalik / Precise decimal	price DECIMAL(10,2)
VARCHAR(n)	Değişken uzunluk metin / Variable text	name VARCHAR(255)
TEXT	Uzun metin / Long text	description TEXT
DATE	Tarih / Date	birth_date DATE
DATETIME	Tarih ve saat	created_at DATETIME
TIMESTAMP	Unix timestamp with TZ conversion	updated_at TIMESTAMP
BOOLEAN	Mantiksal / Boolean (alias TINYINT(1))	is_active BOOLEAN
JSON	JSON verisi	metadata JSON
ENUM	Sabit deger listesi	status ENUM('active','inactive')
BLOB	Binary data	avatar BLOB

CRUD Islemleri (CRUD Operations)

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

-- INSERT
INSERT INTO users (name, email, age) VALUES ('Ali Yilmaz', 'ali@test.com', 28);
INSERT INTO users (name, email, age) VALUES
    ('Ayse Demir', 'ayse@test.com', 32),
    ('Mehmet Kaya', 'mehmet@test.com', 25);

-- SELECT
SELECT * FROM users;
SELECT name, email FROM users WHERE age > 25 ORDER BY name ASC;
SELECT COUNT(*) AS total, AVG(age) AS avg_age FROM users;
SELECT status, COUNT(*) FROM users GROUP BY status HAVING COUNT(*) > 1;

-- UPDATE
UPDATE users SET age = 29, status = 'active' WHERE email = 'ali@test.com';

-- DELETE
DELETE FROM users WHERE id = 3;

JOIN Islemleri

-- Tablolar (Setup tables for JOIN examples)
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- INNER JOIN: Sadece eslesen kayitlar / Only matching records
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN: Sol tablonun tum kayitlari / All from left table
SELECT u.name, COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- RIGHT JOIN: Sag tablonun tum kayitlari / All from right table
SELECT u.name, o.total
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- FULL JOIN (MySQL workaround with UNION)
SELECT u.name, o.total FROM users u LEFT JOIN orders o ON u.id = o.user_id
UNION
SELECT u.name, o.total FROM users u RIGHT JOIN orders o ON u.id = o.user_id;

Indeks (Indexes)

-- Tekli indeks / Single index
CREATE INDEX idx_users_email ON users(email);

-- Benzersiz indeks / Unique index
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- Bilesik indeks / Composite index
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- Indeksleri goster / Show indexes
SHOW INDEX FROM users;

-- Indeks sil / Drop index
DROP INDEX idx_users_email ON users;

Foreign Key ve Iliskiler (Relations)

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE products (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    category_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (category_id) REFERENCES categories(id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

-- Many-to-Many iliskisi / Many-to-Many relationship
CREATE TABLE product_tags (
    product_id BIGINT NOT NULL,
    tag_id INT NOT NULL,
    PRIMARY KEY (product_id, tag_id),
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);

Transactions

Transaction, birden fazla SQL isleminin atomik bir birim olarak calismasini sağlar. Ya hepsi basarili olur ya da hicbiri uygulanmaz.

Temel Kullanım

-- Transaction baslatma ve onaylama
BEGIN;
    INSERT INTO orders (user_id, total) VALUES (1, 250.00);
    UPDATE users SET status = 'active' WHERE id = 1;
COMMIT;

-- Hata durumunda geri alma
BEGIN;
    UPDATE accounts SET balance = balance - 500 WHERE id = 1;
    UPDATE accounts SET balance = balance + 500 WHERE id = 2;
    -- Bir hata olursa tum islemleri geri al
ROLLBACK;

SAVEPOINT

Transaction icinde ara kayit noktalari olusturarak kısmi geri alma yapabilirsiniz.

BEGIN;
    INSERT INTO orders (user_id, total) VALUES (1, 100.00);
    SAVEPOINT sp_order;

    INSERT INTO order_items (order_id, product_id, qty) VALUES (LAST_INSERT_ID(), 5, 2);
    -- order_items ile ilgili sorun varsa sadece bu adimi geri al
    ROLLBACK TO SAVEPOINT sp_order;

    -- order INSERT'i hala gecerli, farkli bir islem yapabiliriz
    INSERT INTO order_items (order_id, product_id, qty) VALUES (LAST_INSERT_ID(), 3, 1);
COMMIT;

Isolation Levels

Isolation level, es zamanli transaction'larin birbirini nasil etkiledigini belirler.

Level	Dirty Read	Non-Repeatable Read	Phantom Read	Performans
READ UNCOMMITTED	Evet	Evet	Evet	En hızlı
READ COMMITTED	Hayir	Evet	Evet	Hızlı
REPEATABLE READ	Hayir	Hayir	Evet*	Orta
SERIALIZABLE	Hayir	Hayir	Hayir	En yavas

InnoDB varsayılan isolation level: REPEATABLE READ. InnoDB, gap locking sayesinde phantom read'i de büyük olcude engeller.

-- Mevcut isolation level'i gorme
SELECT @@transaction_isolation;

-- Session bazli degistirme
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Global degistirme (tum yeni session'lari etkiler)
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Deadlock Tespiti

Iki veya daha fazla transaction birbirinin kilitledigi kaynaklari beklediginde deadlock olusur. InnoDB otomatik olarak deadlock tespit eder ve bir transaction'i geri alir.

-- Deadlock ve InnoDB durumunu inceleme
SHOW ENGINE INNODB STATUS;

-- Son deadlock bilgisini gorme (output icindeki LATEST DETECTED DEADLOCK bolumu)
-- Ornek cikti:
-- TRANSACTION 1 lock wait: X lock on table.users
-- TRANSACTION 2 lock wait: X lock on table.orders
-- WE ROLL BACK TRANSACTION 2

-- Deadlock sayisini izleme
SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks';

Advanced Queries

Subqueries

-- Scalar subquery: Tek deger donduren alt sorgu
SELECT name, email,
    (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count
FROM users;

-- Correlated subquery: Dis sorguya bagli alt sorgu
-- Ortalama siparis tutarindan yuksek siparisi olan kullanicilar
SELECT name FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id
    AND o.total > (SELECT AVG(total) FROM orders)
);

-- IN ile subquery
SELECT name FROM users
WHERE id IN (
    SELECT DISTINCT user_id FROM orders WHERE total > 500
);

-- Subquery ile turetilmis tablo (derived table)
SELECT dept, avg_salary
FROM (
    SELECT department AS dept, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS dept_stats
WHERE avg_salary > 5000;

CTE (Common Table Expressions) -- MySQL 8.0+

CTE, karmasik sorguları okunabilir parcalara ayirmanizi sağlar.

-- Basit CTE
WITH active_users AS (
    SELECT id, name, email
    FROM users
    WHERE status = 'active'
),
user_orders AS (
    SELECT user_id, COUNT(*) AS order_count, SUM(total) AS total_spent
    FROM orders
    GROUP BY user_id
)
SELECT au.name, au.email, uo.order_count, uo.total_spent
FROM active_users au
LEFT JOIN user_orders uo ON au.id = uo.user_id
ORDER BY uo.total_spent DESC;

Recursive CTE -- Hiyerarsi Ornegi

Kategori agaci, organizasyon semasi gibi hiyerarsik yapilari sorgulamak için kullanilir.

-- Kategori tablosu (self-referencing)
CREATE TABLE categories_tree (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_id INT DEFAULT NULL,
    FOREIGN KEY (parent_id) REFERENCES categories_tree(id)
);

INSERT INTO categories_tree (id, name, parent_id) VALUES
    (1, 'Elektronik', NULL),
    (2, 'Bilgisayar', 1),
    (3, 'Laptop', 2),
    (4, 'Masaustu', 2),
    (5, 'Telefon', 1),
    (6, 'Giyim', NULL);

-- Recursive CTE ile kategori agacini listeleme
WITH RECURSIVE category_path AS (
    -- Anchor: Kok kategoriler
    SELECT id, name, parent_id, 0 AS depth, CAST(name AS CHAR(500)) AS path
    FROM categories_tree
    WHERE parent_id IS NULL

    UNION ALL

    -- Recursive: Alt kategoriler
    SELECT c.id, c.name, c.parent_id, cp.depth + 1,
           CONCAT(cp.path, ' > ', c.name)
    FROM categories_tree c
    INNER JOIN category_path cp ON c.parent_id = cp.id
)
SELECT id, name, depth, path
FROM category_path
ORDER BY path;

-- Sonuc:
-- | id | name       | depth | path                          |
-- |----|------------|-------|-------------------------------|
-- | 1  | Elektronik | 0     | Elektronik                    |
-- | 2  | Bilgisayar | 1     | Elektronik > Bilgisayar       |
-- | 3  | Laptop     | 2     | Elektronik > Bilgisayar > Laptop |
-- | 4  | Masaustu   | 2     | Elektronik > Bilgisayar > Masaustu |
-- | 5  | Telefon    | 1     | Elektronik > Telefon          |
-- | 6  | Giyim      | 0     | Giyim                         |

Window Functions -- MySQL 8.0+

Window functions, satir gruplari uzerinde hesaplama yapar ancak satirlari daraltmaz (GROUP BY gibi degil).

-- ROW_NUMBER: Her satira benzersiz sira numarasi atar
SELECT name, department, salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS overall_rank,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

-- RANK ve DENSE_RANK farki
-- RANK: Esit degerlerde siralama atlar (1, 2, 2, 4)
-- DENSE_RANK: Esit degerlerde siralama atlamaz (1, 2, 2, 3)
SELECT name, salary,
    RANK() OVER (ORDER BY salary DESC) AS rank_num,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_num
FROM employees;

-- LAG ve LEAD: Onceki ve sonraki satir degerlerine erisme
SELECT
    order_date,
    total,
    LAG(total, 1) OVER (ORDER BY order_date) AS prev_total,
    LEAD(total, 1) OVER (ORDER BY order_date) AS next_total,
    total - LAG(total, 1) OVER (ORDER BY order_date) AS diff_from_prev
FROM orders
WHERE user_id = 1;

-- PARTITION BY ile departman bazli kumulatif toplam
SELECT name, department, salary,
    SUM(salary) OVER (PARTITION BY department ORDER BY name) AS cumulative_salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

-- NTILE: Satirlari N esit gruba bolme
SELECT name, salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

Full-text Search

MySQL, metin icinde hızlı arama yapmak için FULLTEXT index destegi sunar. InnoDB ve MyISAM tablolarinda kullanılabilir.

FULLTEXT Index Oluşturma

-- Tablo olusturma sirasinda
CREATE TABLE articles (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FULLTEXT INDEX ft_title_body (title, body)
);

-- Mevcut tabloya ekleme
ALTER TABLE articles ADD FULLTEXT INDEX ft_title (title);
CREATE FULLTEXT INDEX ft_body ON articles(body);

MATCH AGAINST Sorgusu

-- Natural Language Mode (varsayilan)
-- Metin icinde dogal dil araması yapar, relevance skoruna gore siralar
SELECT id, title,
    MATCH(title, body) AGAINST('mysql performans') AS relevance_score
FROM articles
WHERE MATCH(title, body) AGAINST('mysql performans');

-- Boolean Mode
-- Ozel operatorlerle gelismis arama
SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST('+mysql -postgresql +performans' IN BOOLEAN MODE);

-- Boolean mode operatorleri:
-- +kelime   : kelime OLMALI
-- -kelime   : kelime OLMAMALI
-- kelime*   : kelime ile baslayan (wildcard)
-- "tam ifade" : tam ifade eslesmesi
-- >kelime   : kelime relevance'i arttirir
-- <kelime   : kelime relevance'i azaltir

-- Query Expansion Mode
-- Ilk aramayla bulunan sonuclardaki kelimeleri kullanarak aramayi genisletir
SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST('database' WITH QUERY EXPANSION);

Arama Skorlama

-- Relevance skorunu kullanarak sonuclari siralama
SELECT id, title,
    MATCH(title) AGAINST('mysql') AS title_score,
    MATCH(body) AGAINST('mysql') AS body_score,
    MATCH(title) AGAINST('mysql') * 2 + MATCH(body) AGAINST('mysql') AS weighted_score
FROM articles
WHERE MATCH(title, body) AGAINST('mysql')
ORDER BY weighted_score DESC
LIMIT 20;

Triggers, Events ve Views

Triggers

Trigger, bir tablodaki INSERT, UPDATE veya DELETE isleminden once (BEFORE) veya sonra (AFTER) otomatik çalışan SQL kodudur.

-- BEFORE INSERT: Veri eklenmeden once kontrol veya degisiklik
DELIMITER $$
CREATE TRIGGER trg_users_before_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    SET NEW.email = LOWER(NEW.email);
    SET NEW.created_at = NOW();
END $$
DELIMITER ;

-- AFTER UPDATE: Guncelleme sonrasi islem
DELIMITER $$
CREATE TRIGGER trg_users_after_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_audit_log (user_id, field_name, old_value, new_value, changed_at)
    VALUES (OLD.id, 'email',
        IF(OLD.email != NEW.email, OLD.email, NULL),
        IF(OLD.email != NEW.email, NEW.email, NULL),
        NOW());
END $$
DELIMITER ;

-- Trigger silme
DROP TRIGGER IF EXISTS trg_users_before_insert;

-- Tum trigger'lari listeleme
SHOW TRIGGERS;

Trigger ile Audit Log Ornegi

-- Audit log tablosu
CREATE TABLE audit_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(100) NOT NULL,
    record_id BIGINT NOT NULL,
    action ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
    old_data JSON DEFAULT NULL,
    new_data JSON DEFAULT NULL,
    changed_by VARCHAR(100) DEFAULT NULL,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Kapsamli audit trigger
DELIMITER $$
CREATE TRIGGER trg_orders_audit_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, record_id, action, new_data, changed_by)
    VALUES ('orders', NEW.id, 'INSERT',
        JSON_OBJECT('user_id', NEW.user_id, 'total', NEW.total),
        CURRENT_USER());
END $$

CREATE TRIGGER trg_orders_audit_delete
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, record_id, action, old_data, changed_by)
    VALUES ('orders', OLD.id, 'DELETE',
        JSON_OBJECT('user_id', OLD.user_id, 'total', OLD.total),
        CURRENT_USER());
END $$
DELIMITER ;

Events (Zamanlanmis Gorevler)

Event scheduler, belirli zamanlarda veya periyodik olarak SQL calistirmanizi sağlar. Cron benzeri bir mekanizmadir.

-- Event scheduler'i aktif etme
SET GLOBAL event_scheduler = ON;

-- Tek seferlik event
CREATE EVENT evt_cleanup_temp
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
    DELETE FROM temp_sessions WHERE created_at < NOW() - INTERVAL 24 HOUR;

-- Periyodik event (her gun gece yarisi)
DELIMITER $$
CREATE EVENT evt_daily_cleanup
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 00:00:00'
ON COMPLETION PRESERVE
DO
BEGIN
    DELETE FROM sessions WHERE expires_at < NOW();
    DELETE FROM password_resets WHERE created_at < NOW() - INTERVAL 1 HOUR;
    INSERT INTO cleanup_log (cleaned_at, status) VALUES (NOW(), 'success');
END $$
DELIMITER ;

-- Event yonetimi
SHOW EVENTS;
ALTER EVENT evt_daily_cleanup DISABLE;
DROP EVENT IF EXISTS evt_cleanup_temp;

Views

View, kaydedilmis bir SELECT sorgusudur. Karmasik sorguları basitlestirmek ve erisim kontrolu saglamak için kullanilir.

-- Basit view
CREATE VIEW v_active_users AS
SELECT id, name, email, created_at
FROM users
WHERE status = 'active';

-- View kullanimi (normal tablo gibi sorgulanir)
SELECT * FROM v_active_users WHERE name LIKE 'Ali%';

-- Karmasik view: Kullanici siparis ozeti
CREATE VIEW v_user_order_summary AS
SELECT
    u.id AS user_id,
    u.name,
    u.email,
    COUNT(o.id) AS total_orders,
    COALESCE(SUM(o.total), 0) AS total_spent,
    MAX(o.created_at) AS last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.email;

-- View guncelleme
CREATE OR REPLACE VIEW v_active_users AS
SELECT id, name, email, created_at, age
FROM users
WHERE status = 'active';

-- View silme
DROP VIEW IF EXISTS v_active_users;

-- Tum view'lari listeleme
SHOW FULL TABLES WHERE Table_type = 'VIEW';

JSON Fonksiyonlari

MySQL 5.7+ surumunden itibaren native JSON destegi sunar. JSON sutunlari yapilandirılmis veriyi ilişkisel tablolarda saklamanizi sağlar.

Temel JSON Islemleri

-- JSON sutunlu tablo
CREATE TABLE products_meta (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    attributes JSON NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- JSON veri ekleme
INSERT INTO products_meta (name, attributes) VALUES
('Laptop', '{"brand": "Dell", "ram": 16, "tags": ["electronics", "computer"], "specs": {"cpu": "i7", "ssd": 512}}'),
('Telefon', '{"brand": "Samsung", "ram": 8, "tags": ["electronics", "mobile"], "specs": {"cpu": "Snapdragon", "storage": 128}}');

JSON_EXTRACT ve Path Expressions

-- JSON_EXTRACT ile deger okuma
SELECT name,
    JSON_EXTRACT(attributes, '$.brand') AS brand,
    JSON_EXTRACT(attributes, '$.ram') AS ram,
    JSON_EXTRACT(attributes, '$.specs.cpu') AS cpu,
    JSON_EXTRACT(attributes, '$.tags[0]') AS first_tag
FROM products_meta;

-- Kisa yol operatoru: -> ve ->> (unquoted)
SELECT name,
    attributes->'$.brand' AS brand_quoted,       -- Tirnak isaretiyle doner: "Dell"
    attributes->>'$.brand' AS brand_unquoted     -- Tirnaksiz doner: Dell
FROM products_meta;

-- JSON deger ile filtreleme
SELECT name FROM products_meta
WHERE attributes->>'$.brand' = 'Dell';

SELECT name FROM products_meta
WHERE JSON_EXTRACT(attributes, '$.ram') > 8;

JSON Guncelleme

-- JSON_SET: Mevcut anahtari gunceller veya yeni anahtar ekler
UPDATE products_meta
SET attributes = JSON_SET(attributes, '$.ram', 32, '$.color', 'silver')
WHERE name = 'Laptop';

-- JSON_REPLACE: Sadece mevcut anahtari gunceller (yeni eklemez)
UPDATE products_meta
SET attributes = JSON_REPLACE(attributes, '$.ram', 64)
WHERE name = 'Laptop';

-- JSON_REMOVE: Anahtar silme
UPDATE products_meta
SET attributes = JSON_REMOVE(attributes, '$.color')
WHERE name = 'Laptop';

-- JSON_ARRAY_APPEND: Diziye eleman ekleme
UPDATE products_meta
SET attributes = JSON_ARRAY_APPEND(attributes, '$.tags', 'sale')
WHERE name = 'Laptop';

JSON Aggregation

-- JSON_ARRAYAGG: Degerleri JSON dizisine toplama
SELECT JSON_ARRAYAGG(name) AS all_products
FROM products_meta;
-- Sonuc: ["Laptop", "Telefon"]

-- JSON_OBJECTAGG: Anahtar-deger ciftlerini JSON objesine toplama
SELECT JSON_OBJECTAGG(name, attributes->>'$.brand') AS product_brands
FROM products_meta;
-- Sonuc: {"Laptop": "Dell", "Telefon": "Samsung"}

JSON Sutun ile Index

JSON sutunlari dogrudan index'lenemez. Bunun yerine generated column olusturup uzerine index eklenir.

-- Generated column ve index
ALTER TABLE products_meta
ADD COLUMN brand VARCHAR(100)
    GENERATED ALWAYS AS (attributes->>'$.brand') STORED;

CREATE INDEX idx_products_brand ON products_meta(brand);

-- Artik brand sutunu uzerinden hizli arama yapilabilir
SELECT name FROM products_meta WHERE brand = 'Dell';

Storage ve Encoding

InnoDB vs MyISAM Karsilastirmasi

Özellik	InnoDB	MyISAM
Transaction	Destekler	Desteklemez
Foreign Key	Destekler	Desteklemez
Row-level Locking	Evet	Hayir (table-level)
Crash Recovery	Evet (WAL/redo log)	Sınırlı
Full-text Index	Evet (MySQL 5.6+)	Evet
MVCC	Evet	Hayir
Depolama	Clustered index	Heap
COUNT(*) Performansi	Yavas (satir sayar)	Hızlı (meta veride tutar)
Varsayilan	MySQL 5.5+ varsayılan	Eski varsayılan

InnoDB neredeyse her durumda tercih edilmelidir. MyISAM sadece ozel durumlar (salt okunur, full-text agirlikli eski sistemler) için dusunulebilir.

Character Set: utf8mb4 vs utf8

MySQL'deki utf8 aslinda gercek UTF-8 degil, en fazla 3 byte kullanir. utf8mb4 ise gercek UTF-8 olup 4 byte'a kadar destekler.

-- Veritabani olusturma (her zaman utf8mb4 kullanin)
CREATE DATABASE myapp
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

-- Mevcut veritabanini donusturme
ALTER DATABASE myapp
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

-- Tablo bazinda ayarlama
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Mevcut ayarlari kontrol etme
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

Özellik	utf8	utf8mb4
Max byte	3	4
Emoji destegi	Hayir	Evet
Tüm Unicode	Hayir	Evet
Kullanilmali mi?	Hayir	Evet

Collation

Collation, metinlerin nasil siralanip karsilastirilacagini belirler.

Collation	Açıklama	Kullanım
utf8mb4_unicode_ci	Unicode standartlarına gore, dile duyarli	Genel kullanım (önerilen)
utf8mb4_general_ci	Daha hızlı ama daha az doğru siralama	Performans kritik
utf8mb4_turkish_ci	Turkce kurallarina gore (I/i farki)	Turkce içerik
utf8mb4_bin	Binary karşılaştırma (büyük/küçük harf duyarli)	Hash, sifre

-- Turkce icerik icin ozel collation
CREATE TABLE articles_tr (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_turkish_ci
);

Partitioning (Tablo Bolmeleme)

Büyük tablolari mantiksal parcalara boler. Sorgu performansini ve yönetimi iyilestirir.

-- RANGE partitioning: Tarih bazli bolmeleme
CREATE TABLE logs (
    id BIGINT AUTO_INCREMENT,
    message TEXT,
    created_at DATE NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- LIST partitioning: Belirli degerlere gore bolmeleme
CREATE TABLE orders_regional (
    id BIGINT AUTO_INCREMENT,
    region VARCHAR(20) NOT NULL,
    total DECIMAL(10,2),
    PRIMARY KEY (id, region)
) PARTITION BY LIST COLUMNS(region) (
    PARTITION p_marmara VALUES IN ('istanbul', 'bursa', 'kocaeli'),
    PARTITION p_ege VALUES IN ('izmir', 'aydin', 'mugla'),
    PARTITION p_ic VALUES IN ('ankara', 'konya', 'eskisehir')
);

-- HASH partitioning: Esit dagilim icin
CREATE TABLE sessions (
    id BIGINT AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    data TEXT,
    PRIMARY KEY (id, user_id)
) PARTITION BY HASH(user_id) PARTITIONS 8;

-- Partition yonetimi
ALTER TABLE logs ADD PARTITION (PARTITION p2025 VALUES LESS THAN (2026));
ALTER TABLE logs DROP PARTITION p2022;  -- Eski verileri hizlica silme

Güvenlik

Kullanici Yetki Yönetimi (Detayli)

-- Kullanici olusturma (farkli authentication)
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'StrongP@ss123';
CREATE USER 'readonly'@'192.168.1.%' IDENTIFIED BY 'ReadOnly@456';
CREATE USER 'admin'@'%' IDENTIFIED BY 'Admin@789';

-- Yetki turleri ve verme
-- Tablo seviyesi
GRANT SELECT, INSERT, UPDATE ON myapp.users TO 'appuser'@'localhost';

-- Veritabani seviyesi
GRANT ALL PRIVILEGES ON myapp.* TO 'admin'@'%';

-- Belirli sutunlar icin
GRANT SELECT (name, email), UPDATE (name) ON myapp.users TO 'readonly'@'192.168.1.%';

-- Sadece okuma yetkisi
GRANT SELECT ON myapp.* TO 'readonly'@'192.168.1.%';

-- Stored procedure calistirma yetkisi
GRANT EXECUTE ON PROCEDURE myapp.GetUserOrders TO 'appuser'@'localhost';

-- Yetki geri alma
REVOKE INSERT, UPDATE ON myapp.users FROM 'appuser'@'localhost';
REVOKE ALL PRIVILEGES ON myapp.* FROM 'admin'@'%';

-- Yetkileri goruntuleme
SHOW GRANTS FOR 'appuser'@'localhost';

-- Sifre degistirme
ALTER USER 'appuser'@'localhost' IDENTIFIED BY 'NewStrongP@ss456';

-- Yetkileri uygulama
FLUSH PRIVILEGES;

SSL Bağlantı

-- SSL ile kullanici olusturma
CREATE USER 'secureuser'@'%' IDENTIFIED BY 'SecureP@ss' REQUIRE SSL;

-- Belirli sertifika gereksinimi
CREATE USER 'certuser'@'%' IDENTIFIED BY 'CertP@ss'
    REQUIRE X509;

-- SSL durumunu kontrol etme
SHOW VARIABLES LIKE '%ssl%';
STATUS;  -- Ssl: Cipher satirina bakin

# my.cnf / my.ini - SSL yapilandirmasi
[mysqld]
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem
require_secure_transport=ON

Prepared Statements (SQL Injection Korunma)

-- Sunucu tarafli prepared statement
PREPARE stmt FROM 'SELECT * FROM users WHERE email = ? AND status = ?';
SET @email = 'ali@test.com';
SET @status = 'active';
EXECUTE stmt USING @email, @status;
DEALLOCATE PREPARE stmt;

// PHP/PDO ile prepared statement (Laravel disinda kullanim)
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email');
$stmt->execute(['email' => $userInput]);
$results = $stmt->fetchAll();

// HATALI - SQL injection riski:
// $pdo->query("SELECT * FROM users WHERE email = '$userInput'");

Ag Guvenligi

# my.cnf - Baglanti kisitlama
[mysqld]
# Sadece belirli IP'den baglanti kabul et
bind-address = 127.0.0.1

# Uzaktan erisim gerekiyorsa (dikkatli kullanin)
# bind-address = 0.0.0.0

# Maksimum baglanti denemesi (brute-force korunma)
max_connect_errors = 10

# Bos baglanti zaman asimi (saniye)
wait_timeout = 28800
interactive_timeout = 28800

Binary Log (Binlog)

Binary log, veritabanindaki tüm degisiklikleri kaydeder. Replication ve point-in-time recovery için gereklidir.

# my.cnf - Binlog yapilandirmasi
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW          # ROW, STATEMENT veya MIXED
expire_logs_days = 14        # Eski log'lari otomatik sil
max_binlog_size = 100M

-- Binlog durumunu kontrol etme
SHOW BINARY LOGS;
SHOW MASTER STATUS;

-- Binlog icerigini okuma
SHOW BINLOG EVENTS IN 'mysql-bin.000001' LIMIT 20;

-- Point-in-time recovery (belirli bir ana geri donme)
-- 1. Full backup'tan geri yukle
-- 2. Binlog ile belirli zamana kadar olan degisiklikleri uygula

# Belirli zaman araligindaki binlog'lari uygulama
mysqlbinlog --start-datetime="2024-01-15 10:00:00" \
            --stop-datetime="2024-01-15 12:00:00" \
            /var/log/mysql/mysql-bin.000005 | mysql -u root -p myapp

Stored Procedures ve Functions

-- Stored Procedure
DELIMITER $$
CREATE PROCEDURE GetUserOrders(IN userId BIGINT)
BEGIN
    SELECT o.id, o.total, o.created_at
    FROM orders o
    WHERE o.user_id = userId
    ORDER BY o.created_at DESC;
END $$
DELIMITER ;

CALL GetUserOrders(1);

-- Function
DELIMITER $$
CREATE FUNCTION CalcDiscount(price DECIMAL(10,2), pct INT)
RETURNS DECIMAL(10,2) DETERMINISTIC
BEGIN
    RETURN price - (price * pct / 100);
END $$
DELIMITER ;

SELECT CalcDiscount(100.00, 15);  -- 85.00

Kullanici Yönetimi (User Management)

CREATE USER 'appuser'@'%' IDENTIFIED BY 'StrongP@ss123';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'appuser'@'%';
FLUSH PRIVILEGES;

-- Yetkileri goster / Show grants
SHOW GRANTS FOR 'appuser'@'%';

-- Kullanici sil / Drop user
DROP USER 'appuser'@'%';

Yedekleme (Backup & Restore)

# Yedek al / Backup
mysqldump -u root -p myapp > myapp_backup.sql
mysqldump -u root -p --all-databases > full_backup.sql

# Geri yukle / Restore
mysql -u root -p myapp < myapp_backup.sql

Laravel ile Kullanım

# .env dosyasi / .env file
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=myapp
DB_USERNAME=appuser
DB_PASSWORD=StrongP@ss123

# Migration olustur / Create migration
php artisan make:migration create_users_table

# Migration calistir / Run migrations
php artisan migrate
php artisan migrate:rollback

Tips ve Best Practices

EXPLAIN ANALYZE (MySQL 8.0.18+)

Sorgu performansini analiz etmek için en guclu arac. EXPLAIN plan gosterirken, EXPLAIN ANALYZE sorguyu gercekten calistirip gercek surelerle birlikte raporlar.

-- Temel EXPLAIN
EXPLAIN SELECT u.name, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

-- EXPLAIN ANALYZE: Gercek calisma surelerini gosterir
EXPLAIN ANALYZE SELECT u.name, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

-- FORMAT=JSON ile detayli plan
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE email = 'ali@test.com';

Slow Query Log

Yavas calistirilan sorguları otomatik olarak loglar. Performans sorunlarini tespit etmek için kritiktir.

# my.cnf - Slow query log yapilandirmasi
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 1          # 1 saniyeden uzun sorgulari logla
log_queries_not_using_indexes = 1  # Index kullanmayan sorgulari da logla

-- Canli olarak aktif etme
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;  -- 500ms ustunu logla

-- Durumu kontrol etme
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

# Slow query log analizi
mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log
# -s t: Toplam sureye gore sirala
# -t 10: Ilk 10 sorguyu goster

Index Stratejisi

-- Composite index sirasi onemlidir (soldan saga esleme kurali)
-- Bu index: (A, B, C) sirasiyla sorgulanabilir
-- WHERE A = ?              -> Index KULLANILIR
-- WHERE A = ? AND B = ?    -> Index KULLANILIR
-- WHERE A = ? AND B = ? AND C = ? -> Index KULLANILIR
-- WHERE B = ?              -> Index KULLANILMAZ
-- WHERE B = ? AND C = ?    -> Index KULLANILMAZ
CREATE INDEX idx_composite ON orders(user_id, status, created_at);

-- Covering index: Sorgunun ihtiyaci olan tum sutunlari iceren index
-- MySQL tabloya hic bakmadan sadece index'ten cevap verir
CREATE INDEX idx_covering ON orders(user_id, created_at, total);
-- Bu sorgu sadece index'ten cevaplanir (Extra: Using index)
SELECT created_at, total FROM orders WHERE user_id = 1;

-- Gereksiz index'leri tespit etme
SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;

Connection Pooling

Her yeni MySQL baglantisi kaynak tuketir (bellek, islemci, ag). Connection pooling, baglantilari yeniden kullanarak performansi arttirir.

# my.cnf - Baglanti ayarlari
[mysqld]
max_connections = 200         # Maksimum es zamanli baglanti
thread_cache_size = 16        # Yeniden kullanimda cache'lenen thread sayisi
wait_timeout = 600            # Bos baglanti zaman asimi (saniye)

// Laravel .env - Connection pooling (varsayilan olarak PDO persistent connection)
DB_CONNECTION=mysql
// config/database.php icinde:
// 'options' => [PDO::ATTR_PERSISTENT => true]

mysqldump Best Practices

# InnoDB tablolari icin tutarli yedek (kilit olmadan)
mysqldump -u root -p \
    --single-transaction \   # InnoDB icin consistent snapshot
    --routines \             # Stored procedures ve functions dahil
    --triggers \             # Trigger'lari dahil et
    --events \               # Zamanlanmis event'leri dahil et
    --set-gtid-purged=OFF \  # Replication GTID uyarisi engelle
    myapp > myapp_backup.sql

# Buyuk veritabani icin sikistirilmis yedek
mysqldump -u root -p --single-transaction myapp | gzip > myapp_$(date +%F).sql.gz

# Geri yukleme (sikistirilmis)
gunzip < myapp_2024-01-15.sql.gz | mysql -u root -p myapp

# Sadece belirli tablolari yedekleme
mysqldump -u root -p myapp users orders > partial_backup.sql

# Sadece tablo yapisi (veri olmadan)
mysqldump -u root -p --no-data myapp > schema_only.sql

Genel Tavsiyeler

• Her zaman utf8mb4 karakter seti kullanin (emoji ve tüm Unicode desteği için)
• Primary key olarak BIGINT AUTO_INCREMENT tercih edin (UUID performans sorunu olusturabilir)
• SELECT * yerine ihtiyac duyulan sutunlari belirtin
• Her tabloda created_at ve updated_at TIMESTAMP sutunlari bulundurun
• WHERE kosullarinda kullanilan sutunlara index ekleyin
• Foreign key'lerde uygun ON DELETE stratejisi belirleyin (CASCADE, SET NULL, RESTRICT)
• Production'da root kullanicisi ile bağlantı yapmayin, kisitli yetkili kullanicilar olusturun
• Duzenli yedekleme planlayin ve geri yükleme testleri yapin

Sik Kullanilan Komutlar (Quick Reference)

Command	Description
SHOW DATABASES;	Veritabanlarini listele
SHOW TABLES;	Tablolari listele
DESCRIBE table;	Tablo yapisini göster
EXPLAIN SELECT ...;	Sorgu planini göster
SHOW PROCESSLIST;	Aktif baglantilari göster
SET GLOBAL max_connections = 200;	Max bağlantı ayarla

---

Ilgili Rehberler

Veritabani

• Veritabani Genel Bakış
• PostgreSQL
• SQLite
• MSSQL
• MongoDB
• Redis

Diger Kategoriler

• Backend Genel Bakış
• Frontend Genel Bakış
• DevOps Genel Bakış
• Teknoloji Genel Bakış