SQL in general: CRUD, Joins and dialects

Introduction

SQL is a declarative language: you describe what data you want to get. The DBMS decides how to get them. The same request works in MySQL, PostgreSQL and MSSQL with dialect adjustments.

Four commands for every day

Almost all the work is CRUD: SELECT reading, INSERT adds, UPDATE changes, DELETE deletes. Learn their order and syntax WHERE and beyond. The rest are combinations.

SELECT name, price FROM products WHERE price > 100 ORDER BY price DESC;

Joyne without pain

INNER JOIN leaves only matching lines, LEFT JOIN keeps everything The rows on the left table and substitutes NULL on the right. The rule of thumb: Start with LEFT JOIN until you make sure there are no “extra” lines, otherwise Reports will quietly lose data.

Dialects

Standard one, little things different: quotes for identifiers, LIMIT vs. TOP and FETCH, autoincrement vs. SERIAL and IDENTITY, function dates. Write the portable code on a common subset, and hide the specifics in migration, not application code.

If the diagram is not already selected, read when instead of a relational base take over NoSQL.

Indices

Without an index, each query reads the entire table. Index columns from WHERE and JOIN, construct composite indices in selectivity order, and Periodically clean up - each index slows down the record. Check the plan through EXPLAIN before production, not after the first Firefighting.

Transactions

Transaction is all or nothing for a group of changes: money is written off They were enrolled atomically, not in parts. Keep transactions short. The long ones hold the loki and stifle the parallel. Level of insulation by Default fits almost always; climbing in the SERIALIZABLE is only worth with A measured race in the hands.