SQL and MySQL course · Module 4: Creating tables (DDL)

Workshop closed - summary

2 min read ·
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

DataType
whole numbersINT
amounts (with cents)DECIMAL(8,2)
short textVARCHAR(n)
long textTEXT
dateDATE
date with timeDATETIME
true/falseBOOLEAN
list of optionsENUM(...)

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?

  1. VideoModule summary

Useful articles