• Choose a category
• All
• Classic-Fiction

♦ Single quotes are used for text values. MySQL generally uses single quotes to denote text values, rather than the double quotes used in other languages. If you really want to enclose a single quote in your text, backslash it.

Examining the buildContact.sql script

Take a look at the following code:

-- buildContact.sql

DROP TABLE IF EXISTS contact;

CREATE TABLE contact (

contactID int PRIMARY KEY,

name VARCHAR(50),

company VARCHAR(30),

email VARCHAR(50)

);

INSERT INTO contact VALUES

(0, ‘Bill Gates’, ‘Microsoft’, ‘bill@msBob.com’);

INSERT INTO contact VALUES

(1, ‘Steve Jobs‘, ‘Apple‘, ‘steve@rememberNewton.com‘);

INSERT INTO contact VALUES

(2, ‘Linus Torvalds‘, ‘Linux Foundation‘, ‘linus@gnuWho.org‘);

INSERT INTO contact VALUES

(3, ‘Andy Harris‘, ‘Wiley Press‘, ‘andy@aharrisBooks.net‘);

SELECT * FROM contact;

This powerful code is written in SQL. I explain each segment in more detail throughout the section, but here’s an overview:

1. Delete the contact table, if it already exists.

This script completely rebuilds the contact table, so if it already exists, it is temporarily deleted to avoid duplication.

2. Create a new table named contact.

As you can see, the table creation syntax is spare but pretty straightforward. Each field name is followed by its type and length (at least, in the case of VARCHARs).

3. Add values to the table by using the INSERT command.

Use a new INSERT statement for each record.

4. View the table data using the SELECT command.

This command displays the content of the table.

Dropping a table

It may seem odd to begin creating a table by deleting it, but there’s actually a good reason. As you experiment with a data structure, you’ll often find yourself building and rebuilding the tables.

The line

DROP TABLE IF EXISTS contact

means, “Look at the current database and see whether the table contact appears in it. If so, delete it.” This syntax ensures that you start over fresh as you are rebuilding the table in the succeeding lines. Typical SQL scripts begin by deleting any tables that will be overwritten to avoid confusion.

Creating a table

You create a table with the (aptly named) CREATE TABLE command. The specific table creation statement for the contact table looks like the following:

CREATE TABLE contact (

contactID int PRIMARY KEY,

name VARCHAR(50),

company VARCHAR(30),

email VARCHAR(50)

);

Creating a table involves several smaller tasks:

1. Specify the table name.

The CREATE TABLE statement requires a table name. Specify the table name. Table names (like variables and filenames) should generally not contain spaces or punctuation without good reason.

2. Begin the field definition with a parenthesis.

The left parenthesis indicates the beginning of the field list. You traditionally list one field per line, indented as in regular code, although that format isn’t required.

3. Begin each field with its name.

Every field has a name and a type. Begin with the field name, which should also be one word.

4. Indicate the field type.

The field type immediately follows the field name (with no punctuation).

5. Indicate field length, if necessary.

If the field is a VARCHAR or CHAR field, specify its length within parentheses. You can specify the length of numeric types, but I don’t recommend it because MySQL automatically determines the length of numeric fields.

6. Add special modifiers.

Some fields have special modifiers. For now, note that the primary key is indicated on the contactID field.

7. End the field definition with a comma.

The comma character indicates the end of a field definition.

8. End the table definition with a closing parenthesis and a semicolon.

Close the parenthesis that started the table definition and end the entire statement with a semicolon.

Adding records to the table

You add data to the table with the INSERT command. The way this command works isn’t too surprising:

INSERT INTO contact VALUES

(0,