SQL and MySQL course Β· Module 4: Creating tables (DDL)
CREATE TABLE - building a new shelf
In this lesson6
The most important command of the scriptorium is CREATE TABLE. It creates a new, empty shelf ready to receive scrolls.
Basic syntax
CREATE TABLE table_name (
column1 TYPE,
column2 TYPE,
column3 TYPE
);We read it like this: "Build a shelf named table_name, and in it the fields: column1, column2, column3". Each field has its name and its type.
Writing rules
- After the table name we open a round parenthesis
(. - Inside we list the columns, each on a new line for readability.
- We separate column definitions with commas - but we do NOT put a comma after the last one.
- We close the parenthesis
)and end the whole thing with a semicolon ";".
Definition of a single column
In a column definition we first write the name, and right after it the data type:
tytul VARCHAR(255)This means: "the tytul field accepts text up to 255 characters". The order is always this: NAME, then TYPE.
Example: a simple kategorie shelf
CREATE TABLE kategorie (
id INT,
nazwa VARCHAR(100)
);A shelf kategorie was created with two fields: id for numbers and nazwa for short text. It is still empty - but the plan is ready!
Does the table name matter?
Very much! A table name is the address of the shelf in the Library. You will later use it to retrieve data (SELECT * FROM kategorie), add columns or drop the whole shelf. Good names are short, plural (ksiazki, autorzy, czytelnicy) and describe the kind of scrolls stored. We avoid spaces and special characters - instead we use underscores, e.g. rok_wydania.
Remember three things
- Parentheses
( )enclose the list of columns - without them MySQL reports an error. - Commas separate the columns, but there is none after the last one.
- A semicolon ";" ends the whole statement.
When these three elements are in place, the shelf is built in the blink of an eye!
Spotted a mistake in this lesson?
Check yourself
Answer the questions from this lesson. Pick an answer to see right away whether it is correct.
1. What do we put inside the parentheses in a CREATE TABLE statement?
2. In a column definition, e.g. nazwa VARCHAR(100), what do we write FIRST?
These are 2 of 3 questions for this lesson. Solve the rest in the game.
Hands-on tasks in the game
- Click in order
Click the elements in the order in which we build the header of a table-creating statement (one line):
- Code editor
Write a CREATE TABLE statement that creates the kategorie table with columns: id of type INT and nazwa of type VARCHAR(100). Remember the parentheses and the semicolon.