Setting up Database Structure in MySQL

To view all the SQL tutorials in chronological order, visit the SQL page

Now that we’ve covered a couple concepts, and have everything installed, we’re ready to start confidently creating databases and tables!

In this tutorial we’ll be creating a database, and a table. We’ll also show you how to adjust these tables as your application evolves. Because you’re always adding or editing features, it’s good to know how to add, remove, and edit columns in a database, so we’ll do that too.

Creating and Removing Databases

In order to create a table, you need a database to put it in. You can setup things however you want, but lets start with one database per project.

I’m going to call my projects “project1”, “project2”, and “project3”. The SQL to enter to create three databases is this:

CREATE DATABASE project1;
CREATE DATABASE project2;
CREATE DATABASE project3;

We don’t need project3, so lets drop it like it’s hot. (delete it).

DROP DATABASE project3;

Now have a look at your databases! You should see project1 and project2! Now it’s time to add a table, but in order to add a table, you need to select which database to work in. Tables go inside of a database, so we need to choose a database that our tables belong in.

You can either select the database by just clicking on it (the easier way) Or you can use SQL and run USE nameOfDatabase;

In the table we’ll create the following columns: name, age, gender, career, fav_color has_pets

For now, just add the name column.

CREATE TABLE visitors ( name VARCHAR(20) )

That command should have created a “visitors” table with the column “name”. The name field has a
data type of VARCHAR and a limit of 20 characters.

Okay. That wasn’t too bad was it? Now let’s add a few more columns.

We’ll add the following columns and their data type:

age = TINYINT
fav_color = VARCHAR(20)
has_pets = TINYINT (Note you could use BIT also)

The has_pets is a TRUE/FALSE or BOOLEAN. We’ll use 1 as TRUE, 0 as FALSE.

We’re changing the structure of a table that already exists by adding more COLUMNS. We need to use the “ALTER TABLE” command to change existing tables.

The command to add all three of these in one go is this:

ALTER TABLE visitors
    ADD COLUMN age TINYINT,
    ADD COLUMN fav_color VARCHAR(20),
    ADD COLUMN has_pets TINYINT;

So the formula goes like this. ADD COLUMN name_of_column DATA_TYPE

So now we should have a table with the following setup:

Table Name: visitors
name, age, fav_color, has_pets

Oops! “name” isn’t a very specific column name. Is that First name, last name, middle name, or all three? Let’s change name to first_name.

ALTER TABLE visitors CHANGE name first_name VARCHAR(20);

The above command allows you to change both the name AND the data type. if we needed to change it
to TEXT instead of VARCHAR(20), we could have done that by replacing VARCHAR(20) with TEXT.
You could also make VARCHAR shorter or longer if you wanted.

Note: Be careful when changing data types! You don’t want to destroy a bunch of data. We’ll handle that later when we actually have data to work with.

Our table order is all messed up. Whether a person has pets or not is very important.
Let’s move it up to the front, right after first_name.

ALTER TABLE visitors
CHANGE COLUMN has_pets
has_pets TINYINT
AFTER first_name;

(You could also do it in one big ugly line)

The above command changed the column order to make has_pets right after the first_name column.

I can only think of ONE more thing we need to cover in this mini-section. We forgot our “Primary Key”. A Primary Key is a unique identifier. What if we have two “John Doe”s in our database?
You’ll find many uses for primary keys so don’t forget to add them!

We’ll put the primary key at the very front, since it’s even more important than the has_pets.

The primary key will be a data type of INT and it will also have a special attribute called “NOT NULL”. Some people use “ID” and others use things like “user_id”. For now, we’ll use user_id.

ALTER TABLE visitors
ADD COLUMN user_id INT NOT NULL AUTO_INCREMENT FIRST,
ADD CONSTRAINT PRIMARY KEY(user_id);

We use ALTER TABLE tablename because we’re editing an existing table. the ADD COLUMN line is adding a user_id column with a data type of INT. The NOT NULL means that column of the row CANNOT be empty.

the AUTO_INCREMENT means it automatically increments. If I’m the first user, then I’m number 1. If you register after me, then you’re number 2. Sorry.

FIRST puts the user_id column at the beginning. Remember how we moved the has_pets earlier? Same deal. Finally, we have the “ADD CONSTRAINT” PRIMARY KEY(user_id) which is just setting the “user_id” to be the PRIMARY KEY.

This is kind of strange; Normally we’d just put the primary key on when we first create the table. But this is all for the sake of learning. I’ll show you how to add the primary key while creating a table at the end.

Okay. Last thing is getting rid of that fav_color column.

ALTER TABLE visitors
DROP COLUMN fav_color;

Okay, so we’re good to go! Except, we could make all the columns NOT NULL if you wanted to require all the fields. I’m going to delete the table and start from scratch, that way you can see how to do it right the first time around.

DROP TABLE visitors;
CREATE TABLE visitors ( 
    user_id INT NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(20) NOT NULL,
    has_pets TINYINT,
    fav_color VARCHAR(20),
    age TINYINT NOT NULL,
    PRIMARY KEY (user_id)
);

That’s all I can think of for this lesson! We learned how to add a database and add tables to our
database! We can move columns around, change the data types, add columns, remove columns, and we know about primary keys!
We’ll talk more about primary keys soon.

Homework Assignments

1. Add another table called “pets”. Add whatever fields you feel like. Here’s some ideas:

pet_id, pet_name, class, age, sex, owner

2. Re-order the columns

3. Add some columns

4. Change the DATA TYPE of a column (From TEXT to VARCHAR or vice versa)

5. Delete some columns

6. IMPORTANT: Think of your own project you want to work on. What kind of data does it have?
In another database, create some tables for your projects data with columns and rows you think are important.

In the next tutorial we’ll start adding data to the table!

You must have an account and be logged in to solve challenges and lessons!

Submit a Comment

Your email address will not be published. Required fields are marked *