Database programming

DDL, data definition language, changes the structure of the database — tables, columns, keys. DML, data manipulation language, reads and changes the rows inside tables that already exist. The test is shape versus contents.

CREATE makes a new table with its columns, types and keys; ALTER changes an existing structure, such as adding a column; DROP removes a table entirely, along with everything in it.

SELECT reads rows matching a condition; INSERT INTO adds a new row; UPDATE SET changes values in existing rows; DELETE removes rows from a table.

DELETE removes rows and the table stays, ready for more data — it is DML. DROP removes the table itself, with its structure and all its contents — it is DDL.

DDL is run rarely, by a database administrator, when the design genuinely changes. DML is run constantly, by the application, every time somebody uses the system.

It names the two columns that must match — almost always a foreign key and the primary key it points at. Without it, every row of one table pairs with every row of the other.

FROM and JOIN decide which rows exist, WHERE filters them, GROUP BY collapses them, HAVING filters the groups, SELECT picks the columns, and ORDER BY sorts. SELECT is written first but applied near the end.

WHERE filters individual rows before grouping. HAVING filters groups after grouping, and is the only place a condition on a count or total can go, because that value does not exist until GROUP BY has run.

% stands for any run of characters. 'Sm%' matches anything starting Sm, '%son' anything ending son, and '%ann%' anything containing ann.

When the join can legitimately produce the same row more than once — a student belonging to two clubs would otherwise appear twice in a list of names.

INSERT INTO adds a new row; UPDATE SET changes values in rows that already exist; DELETE removes rows. All three are DML, so the table's structure is untouched.

It applies to every row in the table, with no warning, no confirmation and no undo once committed. The safe habit is to write the WHERE clause first and test it with a SELECT.

It is a separate sorted structure the database can go straight to, instead of reading every row. But every INSERT, UPDATE or DELETE on an indexed column must update that structure as well as the row.

After many changes an index's entries no longer sit in an efficient order, so it gradually stops helping. Reorganising tidies it in place; rebuilding constructs it from scratch. Both are usually scheduled for quiet hours.

With indexes present, every one of the millions of inserted rows updates every index individually. Building each index once at the end is a single sorted pass, which is far cheaper.