How to Write SQL Code by Hand for HTML5and CSS3 Programming

Although you can use phpMyAdmin to build SQL databases, all it really does is write and execute SQL code for you for HTML5 and CSS3 programming. As a programmer, you should know how to write SQL code.

SQL syntax rules

The rules and traditions of SQL are a bit unique because this language has a different purpose than more traditional programming languages:

• Keywords are in uppercase. Officially, SQL is not case-sensitive, but the tradition is to make all reserved words in uppercase and the names of all your custom elements camel-case. Some variations of SQL are case-sensitive, so you’re safest assuming that they all are.

• One statement can take up more than one line in the editor. SQL statements aren’t usually difficult, but they can get long.

• Logical lines end with semicolons.

• White space is ignored. DBMS systems don’t pay attention to spaces and carriage returns, so you can use these tools to help you clarify your code meaning.

• Single quotes are used for text values.

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', '[email protected]');
INSERT INTO contact VALUES
 (1, 'Steve Jobs', 'Apple', '[email protected]');
INSERT INTO contact VALUES
 (2, 'Linus Torvalds', 'Linux Foundation', '[email protected]');
INSERT INTO contact VALUES
 (3, 'Andy Harris', 'Wiley Press', '[email protected]');
SELECT * FROM contact;

This powerful code is written in SQL. 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.

How to drop 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.

Creating a table

You create a table with the 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 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 this isn’t recommended 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.

How to add 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, 'Bill Gates', 'Microsoft', '[email protected]');

Follow these steps:

1. Begin with the INSERT keyword.

   Use INSERT to clarify that this instruction is a data insertion command.

2. Specify the table you want to add data to.

   Use INTO contact to specify that’s where the table goes.

3. (Optional) Specify field names.

   You can specify a list of field names, but this step is unnecessary if you add data to all fields in their standard order. If you have a list of field names, you’re expected to have exactly the same number of values in the VALUES list, and they should be in the same order.

4. Use the VALUES keyword to indicate that a list of field values is coming.

5. Enclose the values within parentheses.

   Use parentheses to enclose the list of data values.

6. Put all values in the right order.

   Place values in exactly the same order the fields were designated.

7. Place text values within single quotes.

   MySQL uses single quotes to specify text values.

8. End the statement with a semicolon, as you do with all SQL commands.

9. Repeat with other data.

   Add as many INSERT commands as you want to populate the data table.

View the sample data

After you’ve created and populated a table, you’ll want to look it over. SQL provides the SELECT command for this purpose:

SELECT * FROM contact;

This command simply returns all fields of all records from your database.