Creating a Database¶

MySQL is the database software we’re talking to. And actually, it can hold many different databases, which are like separate dividers or folders for data. If you were building 10 different sites, you’d have 10 different databases. I already have several others on my machine.

We’re building a site, so let’s create a database called air_pup:

CREATE DATABASE air_pup;

The name air_pup isn’t important - we could use anything. Send a query to list the datbases now:

SHOW DATABASES;

There it is! This fancy new database is like an empty directory: there’s nothing in it yet but it’s ready to go!

Hey! We already know 3 MySQL commands, or queries: SELECT, CREATE DATABASE and SHOW DATABASES; And there really aren’t that many more to learn.

Creating a Table¶

Let’s move into our database:

USE air_pup;

Any queries we make now will be run against the air_pup database.

A directory on your computer holds files. So what lives inside a database? Tables! If you think of a database like a spreadsheet, then each sheet or page is like a table. One sheet might hold pet data and have fields for name, breed, bio and other stuff. We might also have a second sheet that holds user data, with fields like name, address and favorite ice cream. In the database world, each of these is a table.

So before we start tossing pets into the database, we need to create a table and tell MySQL exactly what fields this it will have. We’re going to do this the hard way first - you’ll thank me later when you really understand this stuff!

Like all things, a table is created by asking the database politely. In other words, a query:

CREATE TABLE pet(
    id int(11) AUTO_INCREMENT,
    name varchar(255),
    breed varchar (100),
    PRIMARY KEY (id)
) ENGINE=InnoDB;

This is long and ugly. First, we say we want to create a table with the name pet. Next, like a spreadsheet, we give the table some columns. The big difference is that each column also has a data type, which says if it should hold numbers, text or something diferent. The id column is an int type, so it’ll hold numbers.

The varchar type means that this column can store up to 255 characters of text. If we try to put more in it, the 256th character will get chopped off!

There are other details that I don’t want you to worry about yet. The important parts are that we’re calling the table pets, giving it 3 columns and setting a data type on each column. Besides int and varchar, MySQL has a lot of other types. But honestly, you’ll use these and just a few others most of the time.

I used multiple lines to make this one long query. MySQL is totally ok with this - it just waits for a semicolon before actually sending the query.

Ok, run it! The message says “Query OK, 0 rows affected”. That’s not very exciting, considering how much typing we did - but this is good!

Try another query to see all the tables in the database:

SHOW TABLES;

Ok, only 1 table so far, but great start! We’ve created our database and a table. To celebrate, let’s give it a treat by putting some data in it!