Databases & SQL · Class 12 · Getting Started with MySQL

MySQLDDL⏱️ 8 min read

Creating & Opening a Database

A fresh MySQL server holds no data of yours. Before there can be a table of students there has to be a database to keep it in — a container with a name, holding all the tables that belong to one job. Three commands do the whole of it.

1A database is a container

The school's data is not one table. There will be students, teachers, subjects, maybe library books. They belong together, so they go in one database, named after the school:

lambdalab · a database

students

a table

teachers

a table

subjects

a table

2Making one

MySQL command line client

mysql> create database lambdalab;

Query OK, 1 row affected (0.02 sec)

Query OK means it worked. Ignore “1 row affected” — no row of yours was touched; MySQL is counting an entry it made in its own records.

Naming a database

Use lower case, no spaces, and something that says what it holds. lambdalab or school are good; My Database is not, because the space would have to be quoted every single time you referred to it.

3Seeing what exists

MySQL command line client

mysql> show databases;

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

| Database |

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

| information_schema |

| lambdalab |

| mysql |

| performance_schema |

| practice |

| sys |

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

6 rows in set (0.01 sec)

There is lambdalab, so it really was created. The four names you did not choose — information_schema, mysql, performance_schema and sys — belong to MySQL itself and should be left alone.

4Opening one

Creating a database does not put you inside it. A server can hold many, so every query needs to know which one you mean. You say so once, with use:

MySQL command line client

mysql> use lambdalab;

Database changed

From now until you change it again, every table you create and every query you run applies to lambdalab. Notice that the reply is not a resultset — there is nothing to show, so MySQL just says what happened.

Forgetting use is the classic first error

Run a query before choosing a database and MySQL cannot know what you are talking about. It answers ERROR 1046 (3D000): No database selected. The fix is not to rewrite the query — it is to run use lambdalab; first.

5What is inside it

MySQL command line client

mysql> show tables;

Empty set (0.00 sec)

Empty set is the right answer, not a failure. The database was created a moment ago and nothing has been put in it yet. It is a labelled, empty container.

Once a table exists, the same command lists it:

MySQL command line client

mysql> show tables;

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

| Tables_in_lambdalab |

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

| students |

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

1 row in set (0.01 sec)

The column heading is built from the database's name, which is a quiet way of confirming which database you are actually in.

6The order, every time

create database lambdalab;

Make the container. Once, ever.

use lambdalab;

Open it. Every session, before anything else.

create table students (…);

Build a table inside it. Also once, ever — not every session.

show tables;

Check what is in there.

create once, use every time

create database is run once in the life of the database. use is run every time you open the MySQL client, because closing the client forgets which database you had chosen.