Category list

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

Open your MySQL client. In Windows, type \mysql\bin\mysql . In Linux, type mysql - p , and then enter pa55w0rd .
Type CREATE DATABASE duckwear; . The command line should say:
```
Query OK, 1 Row Affected
```
Type SHOW DATABASES; . The command line will return a list of databases.
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 .

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;

To verify the table creation, type SHOW TABLES; . The command line will return a list that now includes duck_cust and duck_title .

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.

Critical Skill 2.4 Insert Data into Tables

Placing data into a previously created table is accomplished with the INSERT command. The INSERT command performs this task one record or table row at a time. The inserted fields can be partially or completely specified. In the case of partially specified fields, MySQL inserts the default value into the unspecified field.

There are three basic variations on the INSERT syntax: INSERT . . . VALUES and INSERT ...SET , which inserts specific values provided in the command code, and INSERT . . . SELECT , which inserts values provided in other tables:

INSERT INTO <table_name> [<column_name1[, <column_name2, . . .]VALUES <column_value1>[, <column_value2>, . . .];

The INSERT . . . VALUES syntax requires a table_name value and it gives you the option of specifying a list of column names . The column_name values need to be listed in the order in which they appear in the actual table description. In addition, the list of column_name s and list of column_value s must match, respectively, or the data willbe inserted into the wrong column. Because this version of the syntax inserts data in all the columns , you will find it is easier and less work to state only the table_name , letting MySQL attend to the column_name data. You can then focus on making sure the list of column_value s is correctly ordered. If data is missing for a particular field, then use the NULL value, which will cause either the DEFAULT value to be inserted in that field or a marker value (such as for a numeric field or "n/a" for a string field) to fill the place of the missing data:

INSERT INTO < table_name >SET < column_name > = < column_value > [, < column_name > = < column_value > , . . .];

{% if main.adsdop %}{% include 'adsenceinline.tpl' %}{% endif %}

The INSERT ...SET syntax allows you to put values in specified columns within a record, and the < column_name > = < column_value > format makes it easier to assure the correct match between column and data, especially if the record in question has a lot of fields. Any column not specified in the command will have the DEFAULT value inserted into it.

INSERT INTO < table_name > [ < column_name1[, < column_name2, . . .] SELECT < select_value > FROM < table_name > ;

The INSERT... SELECT syntax allows you to use a SELECT command to read another table or tables and insert its data into the specified table_name . The select_value canbe a wildcard ( * ), an expression (i.e. column_value+3 ), or a column_name or list of column_name s. The column_name s in each table description must be in respective order, and once again, any column not specified has the DEFAULT value placed into it.

As of MySQL version 4.1.0, all three of these INSERT syntaxes will allow the addition of the ON DUPLICATE KEY UPDATE clause at the end of the command but before the semicolon. This clause allows you to specify what will happen if the record you try to insert duplicates a key that has been designated as UNIQUE or PRIMARY by allowing you to update the specified field or fields of the unique record rather than insert a duplicate record. The following syntax shows this clause attached to the INSERT . . .VALUES syntax, but it can be used in the same position in all of the INSERT commands:

INSERT INTO < table_name > [< column_name1 [ , < column_name2, . . .]VALUES < column_value1 >[ , < column_value2 >, . . .][ON DUPLICATE KEY UPDATE < column_name1 > = < expression1 >[, < column_name2 > = < expression2 > , . . .];

Flylib.com

Category list

Similar pages

Project 2-1 Create a Database and Tables with Data