Tip:
Highlight text to annotate it
X
As a database contains tables, a table contains all the rows and columns for
a given set of data. A table definition contains all the details
of a data set. Because of this, the CREATE TABLE statement
is a little more complex than the CREATE DATABASE statement.
The CREATE TABLE statement is how you define a table including its columns,
storage options, auto- increments, keys and much more.
Here's an example of a CREATE TABLE statement from the test database.
It's included with this course. So this is the CREATE TABLE statement and
the name of the table is after the CREATE TABLE keywords.
You'll notice I named it test_customer, because there is already a customer
in the test database. The id column, name, address, city, state
and zip, these are the columns. The details of these various data types and
options, we're going to cover some of those later.
But let's just briefly take a look at how this works.
The id column is integer and has these options: NOT NULL, AUTO_INCREMENT, PRIMARY KEY.
The AUTO_INCREMENT option is actually very useful and I'm going to just tell you
a little bit about that right here. It's common for each row that's inserted.
MySQL will automatically place the next incremental value in this column.
It's very useful for id rows. The name is a variable number of characters
between 0 and 255 characters. Address, city are the same, state is a fix
number of characters, two characters, zip is up to ten characters.
We'll go ahead and hit Go. That will create the table.
You notice it didn't work because we haven't selected a database yet.
A table is contained within a database, so we need to select a database in order
to create the table inside of that database. I'll go ahead and press Go, and there we go.
The table got created and now we can go and show tables and we'll see that the
table is in our list of tables. There it is, test_customer.
Just like SHOW DATABASES shows you a list of databases.
SHOW TABLES shows a list of tables from within a given database.
When I'm done with that table, I can use DROP TABLE, just like the DROP DATABASE
will delete a database. DROP TABLE test_customer will delete the table
and all the data within it. Again, this does not put it in a trashcan
someplace where you can recover it. This is destructive.
When you drop a table, you lose all the data in that table.
So now the table is gone, we'll go ahead and show tables and you'll see that the
table is no longer in our list there. So that's the CREATE TABLE statement.
It's generally a good idea to know how this works, even if you're not going to
be creating your table definitions yourself.