MySQL kursi

Eng mashhur relational database. SQL, jadvallar, munosabatlar va optimizatsiya.

Boshlang'ich · 60 ta dars · ~5 hafta

Mavzular: SQL asoslari, CRUD, JOIN, Subquery, Index, Transaction, Stored Procedure, Trigger, View, Optimization

Kirish va O'rnatish

  1. MySQL nima? Relational DB tushunchasi, boshqa DB lar bilan farqi
  2. O'rnatish — Windows, Mac, Linux, Docker bilan
  3. MySQL Workbench va CLI — asosiy ishlatish
  4. Ma'lumot turlari — INT, VARCHAR, TEXT, DATE, BOOLEAN, ENUM, JSON

DDL — Tuzilma Boshqarish

  1. CREATE DATABASE, CREATE TABLE — sintaksis va qoidalar
  2. Constraints — PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT, CHECK
  3. FOREIGN KEY — CASCADE, SET NULL, RESTRICT
  4. ALTER TABLE — ustun qo'shish, o'zgartirish, o'chirish. DROP

DML — Ma'lumot Boshqarish

  1. INSERT — bir va bir nechta qator, INSERT IGNORE, ON DUPLICATE KEY
  2. SELECT — WHERE, AND, OR, NOT, BETWEEN, IN, LIKE, IS NULL
  3. ORDER BY, LIMIT, OFFSET — saralash va sahifalash
  4. UPDATE va DELETE — shartlar bilan, xavfli holatlar
  5. Aggregate funksiyalar — COUNT, SUM, AVG, MIN, MAX
  6. GROUP BY va HAVING — guruhlash va filtrlash

JOIN va Munosabatlar

  1. INNER JOIN — ikkita jadval birlashtirish
  2. LEFT JOIN, RIGHT JOIN — farqi va qachon ishlatiladi
  3. FULL JOIN, CROSS JOIN, SELF JOIN
  4. One-to-Many munosabat — loyihalash va so'rovlar
  5. Many-to-Many munosabat — junction table, so'rovlar
  6. One-to-One munosabat — qachon kerak

Subquery va CTE

  1. Subquery — WHERE, FROM, SELECT ichida
  2. Correlated Subquery — EXISTS, NOT EXISTS
  3. CTE — WITH, rekursiv CTE
  4. UNION, UNION ALL, INTERSECT, EXCEPT

Funksiyalar

  1. String funksiyalar — CONCAT, LENGTH, SUBSTRING, REPLACE, TRIM
  2. Number funksiyalar — ROUND, CEIL, FLOOR, MOD, ABS
  3. Date funksiyalar — NOW, DATE_FORMAT, DATEDIFF, DATE_ADD
  4. Conditional — IF, CASE WHEN, COALESCE, NULLIF, IFNULL
  5. Window Functions — ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD

Index va Performance

  1. Index nima, qanday ishlaydi, qachon kerak
  2. Index turlari — PRIMARY, UNIQUE, COMPOSITE, FULLTEXT
  3. EXPLAIN va EXPLAIN ANALYZE — so'rovni tahlil qilish
  4. Slow Query Log — sekin so'rovlarni topish
  5. Query optimizatsiya — N+1, covering index, qoidalar
  6. Table Partitioning — katta jadvallarni bo'lish

Transaction va Qulflar

  1. Transaction — ACID, BEGIN, COMMIT, ROLLBACK
  2. Isolation Levels — READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE
  3. Locking — Row lock, Table lock, Deadlock va yechimi
  4. SAVEPOINT — qisman rollback

View, Stored Procedure, Trigger

  1. VIEW — yaratish, yangilash, o'chirish, qachon kerak
  2. Stored Procedure — IN, OUT, INOUT parametrlar
  3. Stored Function — Procedure dan farqi, qachon ishlatiladi
  4. Trigger — BEFORE, AFTER, INSERT, UPDATE, DELETE
  5. Event Scheduler — avtomatik vazifalar
  6. Cursor — satr-satr ishlash

Ma'lumotlar Bazasini Loyihalash

  1. Normalizatsiya — 1NF, 2NF, 3NF, BCNF
  2. Denormalizatsiya — qachon va nima uchun
  3. ER Diagram — jadvallar va munosabatlarni chizish
  4. Naming Convention va Schema Design best practices

Xavfsizlik va Boshqaruv

  1. User yaratish, GRANT, REVOKE — huquqlarni boshqarish
  2. SQL Injection — qanday ishlaydi, qanday himoyalanish
  3. Backup va Restore — mysqldump, mysqlpump
  4. Replication — Master-Slave, asoslar
  5. Connection Pool — nima uchun kerak, sozlash

MySQL va Dasturlash Tillari

  1. Node.js — mysql2 moduli, CRUD, parametrli so'rovlar
  2. Node.js — Transaction, xatolarni boshqarish
  3. Sequelize ORM — model, migration, association
  4. Prisma ORM — schema, migration, CRUD
  5. TypeORM — entity, repository, relation
  6. Real loyiha — to'liq DB schema loyihalash va yozish