Creating Tables

Once you've created a SQL database, you can add database tables and insert records, or rows of information.

In the following task, you'll add a members table and a flightprefs table in the travelclub database you created in the last section. Once each table is created, you'll insert records in the table.

To create database tables using MySQL:

1.	Do one of the following: To start the MySQL Monitor in Windows, follow Steps 1 through 4 of the task "To create a database using MySQL in Windows," in the last section. To start MySQL in a Terminal window on a Mac, follow Steps 1 through 8 of the task "To create a database using MySQL on a Mac," in the last section.
2.	At the mysql prompt, type use travelclub; and press Enter (Windows) or Return (Mac) (Figure 5.10). Figure 5.10. Choose a database to work with. Next you'll enter code to create two database tables: members, which includes an id, name, and email for each member of the travel club, and flightprefs, which includes each member's preferences for flight class (first, business, or economy), time of day (morning, afternoon, or evening), and seat location (aisle or window).
3.	At the prompt, type the lines of code that appear in Script 5.1. After each semicolon in the code, press Enter (Windows) or Return (Mac), and then type the next line once you see the mysql prompt. Script 5.1. To create tables and insert records in the tables, type each line of SQL code at the mysql prompt. Press Enter (Windows) or Return (Mac) after each semicolon, and then type the next line once you see the mysql prompt. 1 CREATE TABLE members ( id INT(5) NOT NULL, first_name VARCHAR (30), last_name VARCHAR (30), email VARCHAR (50), PRIMARY KEY (id) ); 2 INSERT INTO members VALUES ("55132", "Joe", "Smith", "[e-mail]"); 3 INSERT INTO members VALUES ("35976", "Fred","Meyer", "[e-mail]"); 4 INSERT INTO members VALUES ("11664", "Jordan", "Wallings", "[e-mail]"); 5 INSERT INTO members VALUES ("26534", "Louise", "Payson", "[e-mail]"); 6 CREATE TABLE flightprefs ( id INT(5) NOT NULL, class VARCHAR (30), flight_time VARCHAR (30), seat_location VARCHAR (30), PRIMARY KEY (id) ); 7 INSERT INTO flightprefs VALUES ("55132", "business", "morning", "aisle"); 8 INSERT INTO flightprefs VALUES ("35976", "economy", "evening", "window"); 9 INSERT INTO flightprefs VALUES ("11664", "first", "afternoon", "aisle"); 10 INSERT INTO flightprefs VALUES ("26534", "business", "morning", "aisle"); Alternatively, you can download the code file from this book's Web site at peachpit.com/dw.vqp. Open the file named Script5.1.txt and copy and paste the code in the MySQL Monitor (Windows) or Terminal window (Mac).
4.	At the prompt, type show tables; and press Enter (Windows) or Return (Mac). The names of the two tables in the travelclub database are displayed in a table (Figure 5.11). Figure 5.11. View the names of all the database tables in the current database.
5.	At the prompt, do one of the following: If you're running Windows, type exit and press Enter to close the MySQL Monitor. If you're on a Mac, type exit and press Return to stop MySQL, type exit and press Return to log out, and then quit Terminal and close the Terminal window. In the upcoming tasks in this chapter, you'll use these tables along with SQL statements to retrieve, sort, and filter database content; join tables in a complex query; and edit and delete records.

Tip

• Dreamweaver 8 includes a sample SQL database. To find the database file, from the Start menu, choose My Computer > Local Disk (C) > Program Files > Macromedia > Dreamweaver 8 > Tutorial_assets > cafe_townsend > data > insert.sql. For more information about using this file to create the cafetownsend database, open Dreamweaver > Help (F1), and then select the Help topic "Creating the MySQL database."