Code10x

Ek open-source relational database management system (RDBMS) hai — data ko structured tables (rows, columns) mein store karta hai.

Relational Database Management System — data ko tables mein store karta hai, jo relationships (foreign keys) se connected hote hain.

Structured Query Language — relational databases ke saath interact karne ka standard way, queries likhkar data insert/retrieve/update/delete karne ke liye.

Table = entity (jaise users). Row = ek individual record. Column = ek specific attribute (jaise name, email).

Ek GUI tool hai — visual interface ke saath schema design, query writing, aur data visualization ke liye.

Ek table ka column, dusri table ke primary key ko reference karta hai — jaise orders table mein user_id, users table ke id ko reference karta hai.

Ye MySQL database server process hai — connections accept karta hai aur queries execute karta hai.

Setup, backups, aur scaling automatically handle hote hain — production deployments ke liye convenient hai.

INT, whole numbers store karta hai. VARCHAR, variable-length text store karta hai — max size define karna padta hai.

DECIMAL, precise hai. FLOAT mein rounding errors ho sakti hain, jo money calculations ke liye dangerous hai.

Har row ko uniquely identify karta hai — ek table mein sirf ek primary key ho sakti hai.

Automatically incrementing values generate karta hai — typically primary key columns ke liye use hota hai.

Referential integrity — ek invalid reference (jaise non-existent user_id waala order) insert nahi ho sakta.

Ensure karta hai ki ek column mein hamesha ek value ho — empty (NULL) nahi ho sakta.

UNIQUE, duplicate values prevent karta hai lekin NULL allow kar sakta hai, aur multiple UNIQUE constraints ek table mein ho sakte hain. PRIMARY KEY, sirf ek hoti hai aur NULL allow nahi karti.

Data duplication ko eliminate karke tables ko organize karne ka process — 1NF, 2NF, 3NF jaise normal forms hote hain.

Too many joins ki zaroorat padti hai, jo performance ko hurt kar sakta hai — kabhi-kabhi jaan-boojh kar denormalize kiya jaata hai read performance ke liye.

Table ke saare columns aur saari rows return karta hai (koi filter na hone par).

Results ki count restrict karta hai — jaise sirf top 10 results chahiye.

ASC (ascending) — DESC explicitly specify karke descending order mein sort kiya ja sakta hai.

Comma-separated VALUE sets se — jaise VALUES (v1), (v2), (v3) — ye multiple separate INSERT statements se FASTER hai.

SAARI rows update ho jaati hain — EXTREMELY dangerous operation hai production mein.

Poori table ke saare rows delete kar deta hai — table structure khud delete nahi hoti.

% = ANY number of characters match karta hai. _ = SIRF ek single character match karta hai.

NULL "unknown" ko represent karta hai — IS NULL aur IS NOT NULL use karna zaroori hai.

Ek value range check karta hai — jaise age BETWEEN 18 AND 65, inclusive range hai.

Sirf wo rows return karta hai jo DONO tables mein match karti hain — non-matching rows exclude ho jaati hain.

LEFT JOIN, left table ki SAARI rows return karta hai, chahe right table mein match ho ya na ho (NULL, se fill ho jaata hai).

"Orphaned" ya unrelated records dhoondhne ke liye — jaise wo users jinhone kabhi order nahi kiya.

Ek table ko khud ke saath join karna, DO aliases ke saath — hierarchical ya self-referencing data (jaise manager-employee) ke liye useful hai.

MySQL ko pata hona chahiye ki kaun si "instance" of the table ki baat ho rahi hai.

UNION, duplicates remove karta hai. UNION ALL, duplicates keep karta hai — isliye faster hai.

Number of columns aur compatible data types.

NAHI — LEFT JOIN aur RIGHT JOIN ko UNION se combine karke ek similar effect achieve kiya ja sakta hai.

Rows ko ek ya multiple columns ke hisaab se group karta hai — har group ke liye aggregate functions calculate kiye ja sakte hain.

WHERE, individual rows ko filter karta hai (GROUP BY se PEHLE). HAVING, groups ko filter karta hai (aggregated data par, GROUP BY ke BAAD).

COUNT(*), saari rows count karta hai. COUNT(column), sirf NON-NULL values count karta hai.

Ek query ke andar ek dusri query — WHERE, FROM, ya SELECT clause mein use ho sakti hai.

Ye outer query ke columns ko reference karti hai — har outer row ke liye alag se execute hoti hai.

Window functions row-level aggregation karte hain — individual rows result mein rehte hain, GROUP BY ki tarah collapse nahi hote.

Rows ko groups mein divide karta hai calculation ke liye — GROUP BY jaisa, lekin rows collapse nahi hote.

WITH clause se define ki jaati hai — ek named temporary result set jise main query mein regular table ki tarah reference kiya ja sakta hai.

Hierarchical data (jaise org charts, category trees) traverse karne ke liye — MySQL 8.0+ mein available hain.

Ek B-tree data structure hai jo column values ko efficiently searchable order mein store karta hai — full table scan avoid karta hai.

MySQL automatically ek index banata hai primary key par.

Ek composite index {a,b,c}, queries {a}, {a,b}, ya {a,b,c} ko support karta hai — {b} ya {c} akele ko nahi.

Kyunki har INSERT/UPDATE/DELETE par, index ko bhi update karna padta hai.

Query execution ki detailed information deta hai — kaun se indexes use hue, kitni rows examine hui.

Full table scan hua — koi index use nahi hua, jo generally SLOW hai badi tables mein.

Index efficiently use nahi ho paata — range conditions use karna behtar hai.

Production mein slow queries ko automatically identify karta hai — manually har query ki performance check karne ki zaroorat nahi.

Atomicity, Consistency, Isolation, Durability — reliability guarantees jo transactions provide karte hain.

Ek transaction ke saare operations ya toh saare hone chahiye ya koi nahi — "all-or-nothing".

Ek baar COMMIT hone ke baad, changes permanent hain, chahe system crash ho jaaye.

START TRANSACTION, ek naya transaction shuru karta hai. COMMIT, changes permanent karta hai. ROLLBACK, changes undo karta hai.

InnoDB, transactions support karta hai (MySQL ka default storage engine). MyISAM, transactions support nahi karta.

MySQL ka default isolation level hai — ek transaction ke andar, SAME query hamesha SAME results deti hai.

Row-level locking (InnoDB), sirf specific rows ko lock karta hai — MULTIPLE transactions same table ke different rows par parallel mein kaam kar sakte hain.

Jab DO transactions ek dusre ke locks ka wait karte hain — MySQL automatically deadlocks detect karke ek transaction ko rollback kar deta hai.

Ek reusable SQL logic hai — parameters accept kar sakta hai, aur MULTIPLE SQL statements ek saath execute kar sakta hai.

Ek stored procedure ko invoke karta hai.

Function, ek value return karti hai aur SELECT statements mein directly use ki ja sakti hai — stored procedures "return" nahi karte, CALL kiye jaate hain.

Ek event (INSERT/UPDATE/DELETE) par automatically fire hone waala code — koi extra function manually call kiye bina.

NEW, row ki updated/inserted value hai. OLD, row ki previous value hai — audit logs banane ke liye useful hai.

Ek virtual table hai jo ek stored query par based hai — view khud actual data store nahi karta.

Ek view users table ka bana sakti hai jisme password column include na ho — security layer ke roop mein.

Ye "hidden" logic hai jo application code mein kahi visible nahi hoti — overuse karna avoid karna chahiye.

Ek naya MySQL user banata hai, username aur password ke saath.

GRANT, ek user ko specific permissions deta hai. REVOKE, previously granted permissions ko remove karta hai.

Har user ko sirf utni permissions deni chahiye jitni zaroori hai — root/admin access nahi.

Ek database (ya specific tables) ka SQL backup create karta hai — text-based SQL statements ke roop mein.

Writes master par hote hain — changes binlog ke through replica servers ko propagate kiye jaate hain. Reads, replicas se ki ja sakti hain.

SQL injection ko prevent karte hain — string concatenation se queries banana ek major security risk hai.

Data "in transit" ko eavesdropping se protect karta hai.

Compromised credentials ka impact bahut zyaada hoga — dedicated, limited-permission application user banana chahiye.

Ye "upsert" ka MySQL way hai — agar INSERT ek existing unique key se conflict karta hai, error ki jagah UPDATE ki tarah behave karta hai.

Multiple tables ko ek saath combine karne deta hai (jaise users, orders, aur products).

Ek group ke saare values ko ek single comma-separated string mein combine karta hai.

Sirf specific condition match karne waale documents/rows ko index karta hai — index size kam karta hai.

Ek transaction ke andar ek "checkpoint" banata hai — ROLLBACK TO SAVEPOINT se, poore transaction ko cancel kiye bina sirf us point tak ke changes undo kiye ja sakte hain.

MySQL ko batata hai ki same inputs se hamesha same output milega — MySQL ko performance optimizations (jaise caching) karne deta hai.