Learning MySQL and MariaDB

Chapter 4. Creating Databases and Tables

In order to be able to add and manipulate data, you first have to create a database. There’s not much to this. You’re creating just a container in which you will add tables. Creating a table is more involved and offers many choices. There are several types of tables from which to choose, some with unique features. When creating tables, you must also decide on the structure of each table: the number of columns, the type of data each column may hold, how the tables will be indexed, and several other factors. However, while you’re still learning, you can accept the default setting for most of the options when creating tables.

There are a few basic things to decide when creating a structure for your data:

• The number of tables to include in your database, as well as the table names

• For each table, the number of columns it should contain, as well as the column names

• For each column, what kind of data is to be stored

For the last part, in the beginning, we’ll use just four types of columns: columns that contain only numbers; columns that contain alphanumeric characters, but not too many (i.e., a maximum of 255 characters); columns that contain plenty of text and maybe binary files; and columns for recording date and time information. This is a good starting point for creating a database and tables. As we get further along, we can expand that list of column data types to improve the performance of your databases.

This chapter contains examples of how to create a database and tables. The text is written on the assumption that you will enter the SQL statements shown on your server, using the mysql client. The exercises at the end of this chapter will require that you make some changes and additions to the database and its tables on your computer. So, when instructed, be sure to try all of the examples on your computer.

The database and the tables that we create in this chapter will be used in several chapters in this book, especially in Part III. In those later chapters, you will be asked to add, retrieve, and change data from the tables you create in this chapter. Exercises in subsequent chapters assume that you have created the tables you are asked to create in this chapter. Thus, in order to get the most value possible from this book, it’s important that you complete the exercises included for each chapter. It will help reinforce what you read, and you will learn more.

Creating a Database

Creating a database is simple, mostly because there’s nothing much to it. Use the SQL statement CREATE DATABASE. You will have to provide a name for the database with this SQL statement. You could call it something bland like db1. However, let’s do something more realistic and interesting. I’m a fan of birds, so I’ve used a database of a fictitious bird-watching website for the examples in this book. Some birds live in groups, or a colony called a rookery. To start, let’s create a database that will contain information about birds and call it rookery. To do this, enter the following from within the mysql client:

CREATE DATABASE rookery;

As previously mentioned, this very minimal, first SQL statement will create a subdirectory called rookery on the filesystem in the data directory for MySQL. It won’t create any data. It will just set up a place to add tables, which will in turn hold data. Incidentally, if you don’t like the keyword DATABASE, you can use SCHEMA instead: CREATE SCHEMA database_name. The results are the same.

You can, though, do a bit more than the SQL statement shown here for creating a database. You can add a couple of options in which you can set the default types of characters that will be used in the database and how data will be sorted or collated. So, let’s drop the rookery database and create it again like so:

DROP DATABASE rookery;

CREATE DATABASE rookery
CHARACTER SET latin1
COLLATE latin1_bin;

The first line in this SQL statement is the same as the earlier one—remember, all of this is one SQL statement spread over two lines, ending with the semicolon. The second line, which is new, tells MySQL that the default characters that will be used in tables in the database are Latin letters and other characters. The third line tells MySQL that the default method of sorting data in tables is based on binary Latin characters. We’ll discuss binary characters and binary sorting in a later chapter, but it’s not necessary to understand that at this point. In fact, for most purposes, the minimal method of creating a database without options, as shown earlier, is fine. You can always change these two options later if necessary. I’m only mentioning the options here so that you know they exist if you need to set them one day.

Now that we’ve created a database, let’s confirm that it’s there, on the MySQL server. To get a list of databases, enter the following SQL statement:

SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| rookery            |
| mysql              |
| test               |
+--------------------+

The results here show the rookery database, and three other databases that were created when MySQL was installed on the server. We saw the other three in Starting to Explore Databases, and we’ll cover them in later chapters of this book as needed.

Before beginning to add tables to the rookery database, enter the following command into the mysql client:

USE rookery

This little command will set the new database that was just created as the default database for the mysql client. It will remain the default database until you change it to a different one or until you exit the client. This makes it easier when entering SQL statements to create tables or other SQL statements related to tables. Otherwise, when you enter each table-related SQL statement, you would have to specify each time the database where the table is located.

Creating Tables

The next step for structuring a database is to create tables. Although this can be complicated, we’ll keep it simple to start. We’ll initially create one main table and two smaller tables for reference information. The main table will have a bunch of columns, but the reference tables will have only a few columns.

For our fictitious bird-watchers site, the key interest is birds. So we want to create a table that will hold basic data on birds. For learning purposes, we won’t make this an elaborate table. Enter the following SQL statement into mysql on your computer:

CREATE TABLE birds (
bird_id INT AUTO_INCREMENT PRIMARY KEY,
scientific_name VARCHAR(255) UNIQUE,
common_name VARCHAR(50),
family_id INT,
description TEXT);

This SQL statement creates the table birds with five fields, or columns, with commas separating the information about each column. Note that all the columns together are contained in a pair of parentheses. For each colum, we specify the name, the type, and optional settings. For instance, the information we give about the first column is:

• The name, bird_id

• The type, INT (meaning it has to contain integers)

• The settings, AUTO_INCREMENT and PRIMARY KEY

The names of the columns can be anything other than words that are reserved for SQL statements, clauses, and functions. Actually, you can use a reserve word, but it must always be given within quotes to distinguish it. You can find a list of data types from which to choose on the websites of MySQL and MariaDB, or in my book, MySQL in a Nutshell.

We created this table with only five columns. You can have plenty of columns (up to 255), but you shouldn’t have too many. If a table has too many columns, it can be cumbersome to use and the table will be sluggish when it’s accessed. It’s better to break data into multiple tables.

The first column in the birds table is a simple identification number, bird_id. It will be the primary key column on which data will be indexed—hence the keywords, PRIMARY KEY. We’ll discuss the importance of the primary key later.

The AUTO_INCREMENT option tells MySQL to automatically increment the value of this field. It will start with the number 1, unless we specify a different number.

The next column will contain the scientific name of each bird (e.g., Charadrius vociferus, instead of Killdeer). You might think that the scientific_name column would be the ideal identifier to use as the primary key on which to index the birds table, and that we wouldn’t need the bird_id column. But the scientific name can be very long and usually in Latin or Greek (or sometimes a mix of both languages), and not everyone is comfortable using words from these languages. In addition, would be awkward to enter the scientific name of a bird when referencing a row in the table. We’ve set the scientific_name column to have a variable-width character data type (VARCHAR). The 255 that we specify in the parentheses after it sets the maximum size (255 should be sufficient for the long names we’ll need to accommodate).

If the scientific name of a bird has fewer than 255 characters, the storage engine will reduce the size of the column for the row. This is different from the CHAR column data type. If the data in a CHAR column is less than its maximum, space is still allocated for the full width that you set. There are trade-offs with these two basic character data types. If the storage engine knows exactly what to expect from a column, tables run faster and can be indexed more easily with a CHAR column. However, a VARCHAR column can use less space on the server’s hard drive and is less prone to fragmentation. That can improve performance. When you know for sure that a column will have a set number of characters, use CHAR. When the width may vary, use VARCHAR.

Next, we set the column data type for the common_name of each bird to a variable-width character column of only 50 characters at most.

The fourth column (family_id) will be used as identification numbers for the family of birds to which each bird belongs. They are integer data types (i.e., INT). We’ll create another table for more information on the families. Then, when manipulating data, we can join the two tables, use a number to identify each family, and link each bird to its family.

The last column is for the description of each bird. It’s a TEXT data type, which means that it’s a variable-width column, and it can hold up 65,535 bytes of data for each row. This will allow us to enter plenty of text about each bird. We could write multiple pages describing a bird and put it in this column.

There are additional factors to consider when searching for a bird in a database, so there are many columns we could add to this table: information about migratory patterns, notable features for spotting them in the wild, and so on. In addition, there are many other data types that may be used for columns. We can have columns that allow for larger and smaller numbers, or for binary files to be included in each row. For instance, you might want a column with a binary data type to store a photograph of each bird. However, this basic table gives you a good sampling of the possibilities when creating tables.

To see how the table looks, use the DESCRIBE statement. It displays information about the columns of a table, or the table schema—not the data itself. To use this SQL statement to get information on the table we just created, you would enter the following SQL statement:

DESCRIBE birds;

+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| bird_id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| scientific_name | varchar(255) | YES  | UNI | NULL    |                |
| common_name     | varchar(50)  | YES  |     | NULL    |                |
| family_id       | int(11)      | YES  |     | NULL    |                |
| description     | text         | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+

Notice that these results are displayed in a table format made with ASCII characters. It’s not very slick looking, but it’s clean, quick, and provides the information requested. Let’s study this layout, not the content, per se.

The first row of this results set contains column headings describing the rows of information that follow it. In the first column of this results set, Field contains the fields or columns of the table created.

The second column, Type, lists the data type for each field. Notice that for the table’s columns in which we specified the data type VARCHAR with the specific widths within parentheses, those settings are shown here (e.g., varchar(255)). Where we didn’t specify the size for the INT columns, the defaults were assumed and are shown here. We’ll cover later what INT(11) means and discuss the other possibilities for integer data types.

The third column in the preceding results, Null, indicates whether each field may contain NULL values. NULL is nothing; it’s nonexistent data. This is different from blank or empty content in a field. That may seem strange: just accept that there’s a difference at this point. You’ll see that in action later in this book.

The fourth column, Key, indicates whether a field is a key field—an indexed column. It’s not an indexed column if the result is blank, as it is with common_name. If a column is indexed, the display will say which kind of index. Because of the limited space permitted in the display, it truncates the words. In the example shown, the bird_id column is a primary key, shortened to PRI in this display. We set scientific_name to another type of key or index, one called UNIQUE, which is abbreviated UNI here.

The next-to-last column in the display, Default, would contain any default value set for each field. We didn’t set any when creating the birds table, but we could have done so. We can do that later.

The last column, Extra, provides any extra information the table maintains on each column. In the example shown, we can see that the values for bird_id will be incremented automatically. There’s usually nothing else listed in this column.

If we don’t like something within the structure of the table we created, we can use the ALTER TABLE statement to change it (this SQL statement is covered in Chapter 5). If you made some mistakes and just want to start over, you can delete the table and try again to create it. To delete a table completely (including its data), you can use the DROP TABLE statement, followed by the table name. Be careful with this SQL statement, as it’s not reversible and it deletes any data in the table.

Note

Incidentally, when using the mysql client, you can press the up arrow on your keyboard to get to the previous lines you entered. So if you create a table, then run the DESCRIBE statement and catch a mistake, you can just drop the table, and use the up arrow to go back to your previous entry in which you created the table. Use the left arrow to move the cursor over to the text you want to change and fix it. When you’ve finished modifying the CREATE TABLE statement, press Enter. The modified CREATE TABLE statement will then be sent to the server.

Inserting Data

Those were a lot of details to absorb in the last section. Let’s take a break from creating tables and enter data in the birds table. We’ll use an INSERT statement, which was covered briefly in Chapter 3, and will be covered in more detail in the next section. For now, don’t worry too much about understanding all of the possibilities with the INSERT statement. Just enter the following on your server using the mysql client:

INSERT INTO birds (scientific_name, common_name)
VALUES ('Charadrius vociferus', 'Killdeer'),
('Gavia immer', 'Great Northern Loon'),
('Aix sponsa', 'Wood Duck'),
('Chordeiles minor', 'Common Nighthawk'),
('Sitta carolinensis', ' White-breasted Nuthatch'),
('Apteryx mantelli', 'North Island Brown Kiwi');

This will create six rows of data for six birds. Enter the following from the mysql client to see the contents of the table:

SELECT * FROM birds;

+---------+----------------------+-------------------+-----------+-------------+
| bird_id | scientific_name      | common_name       | family_id | description |
+---------+----------------------+-------------------+-----------+-------------+
|       1 | Charadrius vociferus | Killdeer          |      NULL | NULL        |
|       2 | Gavia immer          | Great Northern... |      NULL | NULL        |
|       3 | Aix sponsa           | Wood Duck         |      NULL | NULL        |
|       4 | Chordeiles minor     | Common Nighthawk  |      NULL | NULL        |
|       5 | Sitta carolinensis   | White-breasted... |      NULL | NULL        |
|       6 | Apteryx mantelli     | North Island...   |      NULL | NULL        |
+---------+----------------------+-------------------+-----------+-------------+

As you can see from the results, MySQL put values in the two columns we gave it, and set the other columns to their default values (i.e., NULL). We can change those values later.

Let’s create another table for a different database. We have information on birds in the rookery database. Let’s create another database that contains information about people who are interested in bird-watching. We’ll call it birdwatchers and we’ll create one table for it that we’ll call humans, to correlate with the name of birds table:

CREATE DATABASE birdwatchers;

CREATE TABLE birdwatchers.humans
(human_id INT AUTO_INCREMENT PRIMARY KEY,
formal_title VARCHAR(25),
name_first VARCHAR(25),
name_last VARCHAR(25),
email_address VARCHAR(255));

This isn’t much of a table; we’re not collecting much information on members, but it will do well for now. Let’s enter some data into this table. The following adds four people to our table of members of the site:

INSERT INTO birdwatchers.humans
(name_first, name_last, email_address)
VALUES
('Mr.', 'Russell', 'Dyer', '[e-mail]'),
('Mr.', 'Richard', 'Stringer', '[e-mail]'),
('Ms.', 'Rusty', 'Osborne', '[e-mail]'),
('Ms.', 'Lexi', 'Hollar', '[e-mail]');

This enters information for four humans. Notice that we left the first column NULL so that MySQL can assign an identification number automatically and incrementally.

We’ve created some simple tables. We could do more, but this is enough for now to better understand tables and their structure.

More Perspectives on Tables

Besides the DESCRIBE statement, there’s another way to look at how a table is structured. You can use the SHOW CREATE TABLE statement. This basically shows how you might enter the CREATE TABLE to create an existing table, perhaps in a different database. What’s particularly interesting and useful about the SHOW CREATE TABLE statement is that it shows the default settings assumed by the server, ones that you might not have specified when you ran the CREATE TABLE statement. Here’s how you would enter this statement, with the results shown after it:

SHOW CREATE TABLE birds \G

*************************** 1. row ***************************
       Table: birds
Create Table: CREATE TABLE `birds` (
  `bird_id` int(11) NOT NULL AUTO_INCREMENT,
  `scientific_name` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `common_name` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `family_id` int(11) DEFAULT NULL,
  `description` text COLLATE latin1_bin,
  PRIMARY KEY (`bird_id`),
  UNIQUE KEY `scientific_name` (`scientific_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin

As mentioned earlier, there are more options that you can set for each column; if you don’t specify them, the server will use the default choices. Here you can see those default settings. Notice that we did not set a default value for any of the fields (except the first one when we said to use an automatically incremented number), so it set each column to a default of NULL. For the third column, the common_name column, the server set the set of characters (i.e., the alphabet, numbers, and other characters) by which it will collate the data in that column to latin1_bin (i.e., Latin binary characters). The server did the same for three other columns. That’s because of how we set the database at the beginning of this chapter, in the second CREATE DATABASE statement. This is where that comes into play. We could set a column to a different one from the one we set for the database default, but it’s usually not necessary.

You may have noticed in looking at the results that the options for the bird_id column don’t indicate that it’s a primary key, although we specified that in CREATE TABLE. Instead, the list of columns is followed by a list of keys or indexes used in the table. Here it lists the primary key and specifies that that index is based on bird_id. It then shows a unique key. For that kind of key, it gives a name of the index, scientific_name, which is the same as the column it indexes, and it then shows in parentheses a lists of columns from which the index is drawn. That could be more than one column, but it’s just one here. We’ll cover indexes in Chapter 5 (see Indexes).

There’s one more aspect you should note in the results of SHOW CREATE TABLE. Notice that the last line shows a few other settings after the closing parentheses for the set of columns. First is the type of table used, or rather the type of storage engine used for this table. In this case, it’s MyISAM, which is the default for many servers. The default for your server may be different. Data is stored and handled in different ways by different storage engines. There are advantages and disadvantages to each.

The other two settings are the default character set (latin1) and the default collation (latin1_bin) in the table. These come from the default values when the database was created, or rather they came indirectly from there. You can set a different character and collation, and you can even set a different character set and collation for an individual column.

Let me give you an example where setting explicit values for the character set and collation might be useful. Suppose you have a typical database for a bird-watcher group located in England with most of its common names written in English. Suppose further that the site attracts bird-watchers from other countries in Europe, so you might want to include common bird names in other languages. Let’s say that you want to set up a table for the Turkish bird-watchers. For that table, you would use a different character set and collation, because the Turkish alphabet contains both Latin and other letters. For the character set, you would use latin5, which has both Latin and other letters. For collation, you would use latin5_turkish_ci, which orders text based on the order of the letters in the Turkish alphabet. To make sure you don’t forget to use this character set and collation when adding columns to this table later, you could set the CHARSET and COLLATE for the table to these values.

Before moving on, let me make one more point about the SHOW CREATE TABLE statement: if you want to create a table with plenty of special settings different from the default, you can use the results of the SHOW CREATE TABLE statement as a starting point for constructing a more elaborate CREATE TABLE statement. Mostly you would use it to see the assumptions that the server made when it created a table, based on the default settings during installation.

The next table we’ll create for the examples in this book is bird_families. This will hold information about bird families, which are groupings of birds. This will tie into the family_id column in the birds table. The new table will save us from having to enter the name and other information related to each family of birds for each bird in the birds table:

CREATE TABLE bird_families (
family_id INT AUTO_INCREMENT PRIMARY KEY,
scientific_name VARCHAR(255) UNIQUE,
brief_description VARCHAR(255) );

We’re creating three columns in the table. The first is the most interesting for our purposes here. It’s the column that will be indexed and will be referenced by the birds table. That sounds like there is a physical connection or something similar within the birds table, but that’s not what will happen. Instead, the connection will be made only when we execute an SQL statement, a query referencing both tables. With such SQL statements, we’ll join the bird_families table to the birds table based on the family_id columns in both. For instance, we would do this when we want a list of birds along with their corresponding family names, or maybe when we want to get a list of birds for a particular family.

Now we can put all the information we want about a family of birds in one row. When we enter data in the birds table, we’ll include the family_id identification number that will reference a row of the bird_families table. This also helps to ensure consistency of data: there’s less chance of spelling deviations when you only enter a number and not a Latin name. It also saves space because you can store information in one row of bird_families and refer to it from hundreds of rows in birds. We’ll see soon how this works.

The scientific_name column will hold the scientific name of the family of birds (e.g., Charadriidae). The third column is basically for the common names of families (e.g., Plovers). But people often associate several common names to a family of birds, as well as vague names for the types of birds contained in the family. So we’ll just call the column brief_description.

Let’s next create a table for information about the orders of the birds. This is a grouping of families of birds. We’ll name it bird_orders. For this table, let’s try out some of the extra options mentioned earlier. Enter the following SQL statement:

CREATE TABLE bird_orders (
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  scientific_name VARCHAR(255) UNIQUE,
  brief_description VARCHAR(255),
  order_image BLOB
) DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

This SQL statement creates a table named bird_orders with four columns to start. The first one, order_id, is the key in which rows will be referenced from the bird_families table. This is followed by scientific_name for the scientific name of the order of birds, with a data type of VARCHAR. We’re allowing the maximum number of characters for it. It’s more than we’ll need, but there won’t be many entries in this table and it’s difficult to guess what what the longest description will be. So we’ll set it to the maximum allowed for that data type. We’re naming this column brief_description, as we did in the earlier bird_families table.

Because all three tables that we’ve created so far have similar names for some of the columns (e.g., scientific_name), that may cause us a little trouble later if we try to join all of these tables together. It might seem simpler to use distinct names for these columns in each of these tables (e.g., order_scientific_name). However, we can resolve that ambiguity easily when necessary.

In the previous SQL statement, notice that we have a column for an image to represent the order of birds. We might put a photo of the most popular bird of the order or a drawing of several birds from the order. Notice that for this image file, the data type we’re using is a BLOB. While the name is cute and evocative, it also stands for binary large object. We can store an image file, such as a JPEG file, in the column. That’s not always a good idea. It can make the table large, which can be a problem when backing up the database. It might be better to store the image files on the server and then store a file path or URL address in the database, pointing to where the image file is located. I’ve included a BLOB here, though, to show it as a possibility.

After the list of columns, we’ve included the default character set and collation to be used when creating the columns. We’re using UTF-8 (i.e., UCS Transformation Format, 8-bit), because some of the names may include characters that are not part of the default latin1 character set. For instance, if our fictitious bird-watcher site included German words, the column brief_description would be able to accept the letters with umlauts over them (i.e., ä). The character set utf8 allows for such letters.

For a real bird-watching database, both the bird_families and bird_orders tables would have more columns. There would also be several more tables than the few we’re creating. But for our purposes, these few tables as they are here will be fine for now.

Summary

You have many more possibilities when creating tables. There are options for setting different types of storage engines. We touched on that in this chapter, but there’s much more to that. You can also create some tables with certain storage engines that will allow you to partition the data across different locations on the server’s hard drives. The storage engine can have an impact on the table’s performance. Some options and settings are rarely used, but they’re there for a reason. For now, we’ve covered enough options and possibilities when creating tables.

What we have covered in this chapter may actually be a bit overwhelming, especially the notion of reference tables like bird_families and bird_orders. Their purpose should become clearer in time. Chapter 5 provides some clarification on tables, and will show you how to alter them. There are additional examples of inserting and selecting data interspersed throughout that chapter. Before moving on, make sure to complete the exercises in the following section. They should help you to better understand how tables work and are used.

Exercises

Besides the SQL statements you entered on your MySQL server while reading this chapter, here are a few exercises to further reinforce what you’ve learned about creating databases and tables. In some of these exercises, you will be asked to create tables that will be used in later chapters, so it’s important that you complete the exercises that follow.

1. Use the DROP TABLE statement to delete the table bird_orders that we created earlier in this chapter. Look for the CREATE TABLE statement that we used to create that table. Copy or type it into a text editor and make changes to that SQL statement: change the brief_description column to TEXT column type. Watch out for extra commas when you remove columns from the list. When you’re finished, copy that modified SQL statement into the mysql monitor on your computer and press Enter to execute it.

   If you get an error, look at the error message (which will probably be confusing) and then look at the SQL statement in your text editor. Look where you made changes and see if you have any mistakes. Make sure you have keywords and values in the correct places and there are no typos. Fix any mistakes you find and try running the statement again. Keep trying until you succeed.

2. I mentioned in this chapter that we might want to store data related to identifying birds. Instead of putting that data in the birds table, create a table for that data, which will be a reference table. Try creating that table with the CREATE TABLE statement. Name it birds_wing_shapes. Give it three columns: the first column should be named wing_id with a data type of CHAR with the maximum character width set to 2. Make that column the index, as a UNIQUE key, but not an AUTO_INCREMENT. We’ll enter two-letter codes manually to identify each row of data—a feasible task because there will be probably only six rows of data in this table. Name the second column wing_shape and set its data type to CHAR with the maximum character width set to 25. This will be used to describe the type of wings a bird may have (e.g., tapered wings). The third column should be called wing_example and make it a BLOB column for storing example images of the shapes of wings.

3. After creating the birds_wing_shapes table in the previous exercise, run the SHOW CREATE TABLE statement for that table in mysql. Run it twice: once with the semi-colon at the end of the SQL statement and another time with \G to see how the different displays can be useful given the results.

   Copy the results of the second statement, the CREATE TABLE statement it returns. Paste that into a text editor. Then use the DROP TABLE statement to delete the table birds_wing_shapes in mysql.

   In your text editor, change a few things in the CREATE TABLE statement you copied. First, change the storage engine—the value of ENGINE for the table—to a MyISAM table, if it’s not already. Next, change the character set and collation for the table. Set the character set to utf8 and the collation to utf8_general_ci.

   Now copy the CREATE TABLE statement you modified in your text editor and paste it into the mysql monitor and press [Enter] to run it. If you get an error, look at the confusing error message and then look at the SQL statement in your text editor. Look where you made changes and see if you have any mistakes. Make sure you have keywords and values in the correct places and there are no typos. Fix any mistakes you find and try running the statement again. Keep trying to fix it until you’re successful. Once you’re successful, run the DESCRIBE statement for the table to see how it looks.

4. Create two more tables, similar to birds_wing_shapes. One table will store information on the common shapes of bird bodies, and the other will store information on the shapes of their bills. They will also be used for helping bird-watchers to identify birds. Call these two tables birds_body_shapes and birds_bill_shapes.

   For the birds_body_shapes table, name the first column body_id, set the data type to CHAR(3), and make it a UNIQUE key column. Name the second column body_shape with CHAR(25), and the third column body_example, making it a BLOB column for storing images of the bird shapes.

   For the birds_bill_shapes table, create three similar columns: bill_id with CHAR(2) and UNIQUE; bill_shape with CHAR(25); and bill_example, making it a BLOB column for storing images of the bird shapes. Create both tables with the ENGINE set to a MyISAM, the DEFAULT CHARSET, utf8, and the COLLATE as utf8_general_ci. Run the SHOW CREATE TABLE statement for each table when you’re finished to check your work.