Databases & SQL · Class 12 · Getting Started with MySQL

MySQLDDL⏱️ 8 min read

Creating a Table

The database is open and empty. Now you describe the shape of the data you are going to keep — the columns, and what each one holds. This is the single most important query in the chapter, because everything later depends on getting the structure right.

1The syntax

create table <table name> (
    <column1> <data type>,
    <column2> <data type>,
    ...
    <columnN> <data type>);

A name, then a bracket, then one line per column: its name followed by its data type. Commas between them, and the closing bracket and semicolon at the end.

2Building the students table

Here is the table this whole course uses, being made. Remember the columns can be spread over several lines — nothing runs until the semicolon.

MySQL command line client

mysql> create table students (

-> id int primary key,

-> name varchar(30),

-> grade int,

-> percent float,

-> dob date,

-> city varchar(25));

Query OK, 0 rows affected (0.33 sec)

0 rows affected is correct and not a problem. You created a structure, not data — the table exists and has no rows in it yet. primary key on the id line is the rule from the keys lesson, written down at last; the next lesson takes that and the other constraints properly.

MySQL command line client

mysql> show tables;

+---------------------+

| Tables_in_lambdalab |

+---------------------+

| students |

+---------------------+

1 row in set (0.01 sec)

3Reading the structure back

Before putting a single row in, check that the table you built is the table you meant. desc — short for describe, and both work — shows the structure:

MySQL command line client

mysql> desc students;

+---------+-------------+------+-----+---------+-------+

+---------+-------------+------+-----+---------+-------+

| id | int | NO | PRI | NULL | |

| grade | int | YES | | NULL | |

| dob | date | YES | | NULL | |

+---------+-------------+------+-----+---------+-------+

6 rows in set (0.00 sec)

This resultset is about the table, not the data

Six rows came back and there are no students yet. Each row of this resultset describes one column of the table. So the row count here equals the table's degree — a neat way to check it.

Reading the six headings across:

Field

The column's name.

Type

Its data type, with the length you asked for.

Null

YES means the column may be left empty. NO means a value is compulsory — which is why the primary key says NO.

Key

PRI marks the primary key. UNI and MUL appear once other constraints are used.

Default

What goes in when you do not supply a value. NULL unless you set one.

Extra

Anything unusual about the column. Empty for everything in this course.

Notice what MySQL worked out for itself: id has Null: NO and Key: PRI because you said primary key, and a primary key can never be NULL. You wrote one thing; two facts followed.

4Two mistakes worth meeting now

The first is creating a table before choosing a database. MySQL has no idea where to put it:

MySQL command line client

mysql> show tables;

ERROR 1046 (3D000): No database selected

The cure is use lambdalab; first, not a change to the query.

The second is a typo in a table name, which shows up later:

MySQL command line client

mysql> select * from student;

ERROR 1146 (42S02): Table 'lambdalab.student' doesn't exist

The table is called students. The error even prints the name it looked for, lambdalab.student, which tells you both the database it searched and the exact spelling it used.

Plan the table on paper first

Once rows are in, changing the structure is possible but fiddly. Two minutes deciding the columns, their types and which one is the primary key saves far more than two minutes later.