SQL and MySQL course · Module 4: Creating tables (DDL)
Workshop closed - summary
In this lesson5
Well done! You leave the scriptorium as a true builder of shelves. Here is what you have learned:
What you have learned
- DDL is the language of defining structure: CREATE, ALTER, DROP.
- CREATE TABLE name (column TYPE constraints, ...); builds a new shelf.
- In a column definition we write the name first, then the type, and constraints at the end.
Data types - choosing the drawer
| Data | Type |
|---|---|
| whole numbers | INT |
| amounts (with cents) | DECIMAL(8,2) |
| short text | VARCHAR(n) |
| long text | TEXT |
| date | DATE |
| date with time | DATETIME |
| true/false | BOOLEAN |
| list of options | ENUM(...) |
Constraints - rules of order
- PRIMARY KEY - unique identification of a row.
- AUTO_INCREMENT - automatic numbering.
- NOT NULL - a required field.
- DEFAULT - a default value.
- UNIQUE - uniqueness.
Rebuilding and removing
ALTER TABLE ... ADD COLUMN ...- adds a column.ALTER TABLE ... MODIFY COLUMN ...- changes the type.ALTER TABLE ... DROP COLUMN ...- removes a column.ALTER TABLE ... RENAME TO ...- renames the table.DROP TABLE ...- removes the whole table (carefully!).
What's next?
You already have shelves. In the next location you will learn to place scrolls on them - that is, to insert, change and delete data with INSERT, UPDATE and DELETE. Onward, deeper into the Library!
Spotted a mistake in this lesson?