Project 2-1 Create a Database and Tables with Data


In Project 2-1, you will gain experience with the CREATE DATABASE and CREATE TABLE commands. You will use the data types previously discussed and use the commands that allow you to inspect your databases, tables, table definitions, and table contents. You will need to press ENTER after typing each command (after entering the semicolon).

Note  

The command lines in the project are shown with the reserved words in all uppercase in order to clarify the difference between the required and optional reserved words and the user -supplied names and values. As you will see in the illustrations that accompany the project steps, you may type the commands in with all lowercase letters , regardless of the operating system you are using. It is simply a matter of personal preference.

Step by Step

  1. Open your MySQL client. In Windows, type \mysql\bin\mysql . In Linux, type mysql - p , and then enter pa55w0rd .

  2. Type CREATE DATABASE duckwear; . The command line should say:

     Query OK, 1 Row Affected 
  3. Type SHOW DATABASES; . The command line will return a list of databases.

  4. Specify which database to use and create your first table by typing the following commands:

     USE duckwear; CREATE TABLE duck_title( title _num TINYINT NOT NULL AUTO_INCREMENT, title _cust CHAR(4), PRIMARY KEY(title_num) ) TYPE=MyISAM; DESC duck_title; 

    The USE command will return Database Changed , the CREATE command will return Query OK, 0 rows affected , and the DESC command (short for describe) will return a description of the table duck_title .

  5. Create the duck_cust table by entering the following command:

     CREATE TABLE duck_cust( cust_num MEDIUMINT NOT NULL AUTO_INCREMENT, cust_title TINYINT, cust_last CHAR(20) NOT NULL, cust_first CHAR(15) NOT NULL, cust_suffix ENUM('Jr.', 'II', 'III','IV', 'V', 'M.D.','PhD'), cust_add1 CHAR(30) NOT NULL, cust_add2 CHAR(10), cust_city CHAR(18) NOT NULL, cust_state CHAR(2) NOT NULL, cust_zip1 CHAR(5)NOT NULL, cust_zip2 CHAR(4), cust_duckname CHAR(25) NOT NULL, cust_duckbday DATE, PRIMARY KEY (cust_num) )TYPE=MyISAM; 
  6. To verify the table creation, type SHOW TABLES; . The command line will return a list that now includes duck_cust and duck_title .

  7. Type DESC duck_cust; . The command line will return a listing of all the properties of the columns in the duck_cust table.

    Note  

    The display often wraps on the screen. In the example here, the display splits in the middle of the column that indicates whether a field s DEFAULT value is NULL . The last column of this display, Extra , has only one entry in it: the AUTO_INCREMENT setting for the cust_num column s fields. Take the time to study the display until the information and the explanatory headers above it make sense.

Project Summary

In Project 2-1, you have used the correct syntax to create a database and the tables in it. You have practiced using the data types discussed at the beginning of this module. You have also learned a few basic commands to allow you to look at your database s tables, their layouts, and the contents of those tables.




MySQL(c) Essential Skills
MySQL: Essential Skills
ISBN: 0072255137
EAN: 2147483647
Year: 2006
Pages: 109

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net