Although the Structured Query Language (SQL) was developed by IBM, SQL is currently under the watchful eye of the American National Standards Institute (ANSI). As with all "standards," there are variations of SQL-"proprietary enhancements," if you will-for use with MySQL, Microsoft SQL Server, Oracle, and so on. However, learning the standard set of SQL commands will provide you with a fundamental knowledge of SQL that you can use with multiple database systems. Here are the standard commands:
CREATE. Creates a new table.
ALTER. Modifies the definition (structure, data types, and so on) of an existing table.
DROP. Permanently removes elements such as tables and fields.
INSERT. Adds a record to a table.
UPDATE. Modifies data in an existing record.
SELECT. Performs a query on a table, including mathematical functions, field comparison, pattern matching, and so on.
DELETE. Permanently removes elements from a table.
The next several sections contain further descriptions and examples of these basic SQL commands. Be sure to read your database documentation thoroughly for the exact usage of these commands in your own environment, because they might differ slightly from these examples, which are geared toward the MySQL user. Additionally, you may enter SQL commands directly through a graphical user interface (GUI) or via the command line, depending on your database type. The next sections only provide examples of the SQL commands themselves; at the end of this chapter, you'll learn to send SQL commands to your database via PHP scripts.
The CREATE command creates a table in the selected database. The basic syntax is as follows:
CREATE TABLE [table name] [(field_name field_data_type,...)] [options]
Some common data types are int, double, char, varchar, date, time, datetime, text, and blob. Refer to your database documentation for a list of supported data types and any size and data restrictions.
To create the FRIEND_INFO table used at the beginning of this chapter, the SQL statement would be something like this:
CREATE TABLE FRIEND_INFO ( FRIEND_ID int not null primary key, FULL_NAME varchar (100), BIRTH_DATE date, FAVE_COLOR varchar (25) );
This sample code uses a lot of white space. White space is irrelevant in SQL statements, but it certainly makes your code easier to read (and easier to edit later!). The preceding statement could just as easily have been written like this:
CREATE TABLE FRIEND_INFO (FRIEND_ID int not null primary key, FULL_NAME varchar (100), BIRTH_DATE date, FAVE_COLOR varchar (25));
The CREATE command defines the attributes of the fields in your table; it gives each attribute a type and a length. In this example, the FRIEND_ID field is defined as "not null," meaning that when a record is added, if the field is empty, it is simply empty, or it has a default value such as "0" for numeric fields. A value of NULL means something entirely different from "empty", and this is an important distinction to remember. FRIEND_ID is also identified as the primary key, meaning that FRIEND_ID always holds unique data; although there may be multiple entries for a friend named Joe Smith, there will be only one FRIEND_ID of 1, 2, and so on.
The ALTER command gives you the opportunity to modify elements of a particular table. For example, you can use ALTER to add fields or change field types. For instance, if you want to change the size of the FULL_NAME field of the FRIEND_INFO table from 100 to 150, you could issue this statement:
ALTER TABLE FRIEND_INFO CHANGE FULL_NAME FULL_NAME VARCHAR (150);
To add a column to the table, you could use the following statement, which adds a field called HAT_SIZE:
ALTER TABLE FRIEND_INFO ADD HAT_SIZE varchar(5);
Using the ALTER command alleviates the need to delete an entire table and re-create it just because you spelled a field name incorrectly or made other minor mistakes.
The DROP command is quite dangerous if you're not paying attention to what you're doing, because it will delete your entire table in the blink of an eye. The syntax is very simple:
DROP TABLE [table name];
If you wanted to delete the FRIEND_INFO table and rely on your brain to remember your friends' birthdays, the command would be
DROP TABLE FRIEND_INFO;
You can use the DROP command in conjunction with the ALTER command to delete specific fields (and all the data contained in them). To delete the HAT_SIZE field from the FRIEND_INFO table, issue this statement:
ALTER TABLE FRIEND_INFO DROP HAT_SIZE;
The HAT_SIZE field will no longer exist, but all other fields in your table will still be intact.
You use the INSERT command to populate your tables one record at a time. The basic syntax of the INSERT command is
INSERT INTO [table name] ([name of field1] , [name of field2], ...) VALUES ('[value of field 1]', '[value of field 2]'...);
To add a record to the FRIEND_INFO table, where the fields are FRIEND_ID, FULL_NAME, BIRTH_DATE, and FAVE_COLOR, the command would be:
INSERT INTO FRIEND_INFO (FRIEND_ID, FULL_NAME, BIRTH_DATE, FAVE_COLOR) VALUES ('1', 'Joe Smith', '1970-10-03', 'blue');
To add the rest of your friends, simply issue additional INSERT statements:
INSERT INTO FRIEND_INFO (FRIEND_ID, FULL_NAME, BIRTH_DATE, FAVE_COLOR) VALUES ('2', 'Mary Smith', '1962-07-25', 'red'); INSERT INTO FRIEND_INFO (FRIEND_ID, FULL_NAME, BIRTH_DATE, FAVE_COLOR) VALUES ('3', 'Jane Smith', '1968-04-28', 'black'); INSERT INTO FRIEND_INFO (FRIEND_ID, FULL_NAME, BIRTH_DATE, FAVE_COLOR) VALUES ('4', 'Joe Smith', '1975-11-07', 'green');
When inserting records, be sure to separate your strings with single quotes or double quotes. If you use single quotes around your strings, and the data you are adding contains apostrophes, avoid errors by escaping the apostrophe (\') within the INSERT statement. Similarly, if you use double quotes around your strings and you want to include double quotes as part of the data, escape them (\") within your INSERT statement.
For example, if you use single quotes around your strings and you want to insert a record for a friend named Mark O'Hara, you can use this statement:
INSERT INTO FRIEND_INFO (FRIEND_ID, FULL_NAME, BIRTH_DATE, FAVE_COLOR) VALUES ('5', 'Mark O\'Hara', '1968-12-12', 'orange');
Or, you can surround your strings with double quotes and avoid escaping single quotes:
INSERT INTO FRIEND_INFO (FRIEND_ID, FULL_NAME, BIRTH_DATE, FAVE_COLOR) VALUES ("5", "Mark O'Hara", "1968-12-12", "orange");
However, be aware that using single quotes around strings is the convention in SQL, and try to use this convention whenever possible.
The UPDATE command modifies parts of a record without replacing the entire record. Here is the basic syntax of the UPDATE command:
UPDATE [table name] SET [field name] = '[new value]' WHERE [expression];
For instance, suppose you have an incorrect birth date in the FRIEND_INFO table. Joe Smith, with a FRIEND_ID of 1, was born on November 3 instead of October 3. Instead of deleting the record and inserting a new one, just use the UPDATE command to change the data in the BIRTH_DATE field:
UPDATE FRIEND_INFO SET BIRTH_DATE = '1970-11-03' WHERE FRIEND_ID = '1';
If you issue an UPDATE statement without specifying a WHERE expression, you will update all the records. For example, to change everyone's favorite color to red, use this UPDATE statement:
UPDATE FRIEND_INFO SET FAVE_COLOR = 'red';
UPDATE can be a very powerful SQL command. For example, you can perform string functions and mathematical functions on existing records and use the UPDATE to command to modify their values.
When you're creating database-driven Web sites, the SELECT command will likely be the most often-used command in your arsenal. The SELECT command causes certain records in your table to be chosen, based on criteria that you define. Here is the basic syntax of the SELECT command:
SELECT [field names] FROM [table] WHERE [expression] ORDER BY [fields];
To select all the records in a table, such as the FRIEND_INFO table, use this statement:
SELECT * FROM FRIEND_INFO;
To select just the entries in the FULL_NAME field of the FRIEND_INFO table, use this:
SELECT FULL_NAME FROM FRIEND_INFO;
To select all the records in a table and have them returned in a particular order, use an expression for ORDER BY. For example, to view the FRIEND_ID, FULL_NAME, and BIRTH_DATE in each record in the FRIEND_INFO table, ordered by youngest friend to oldest friend, use the following:
SELECT FRIEND_ID, FULL_NAME, BIRTH_DATE FROM FRIEND_INFO ORDER BY BIRTH_DATE DESC;
DESC stands for "descending." To view from oldest to youngest, use ASC for "ascending":
SELECT FRIEND_ID, FULL_NAME, BIRTH_DATE FROM FRIEND_INFO ORDER BY BIRTH_DATE ASC;
Using sample data, the preceding statement produces results like this:
+-------------+--------------+--------------+ + FRIEND_ID | FULL_NAME | BIRTH_DATE | +-------------+--------------+--------------+ + 2 | Mary Smith | 1962-07-25 | + 3 | Jane Smith | 1968-04-28 | + 1 | Joe Smith | 1970-11-03 | + 4 | Joe Smith | 1975-11-07 | +-------------+--------------+--------------+
When preparing ORDER BY clauses, the default order is ASC (ascending).
You can also perform mathematical and string functions within SQL statements, thereby using SELECT to do more than just echo existing data. For example, to quickly find the number of friends in your FRIEND_INFO table, use
SELECT COUNT(FRIEND_ID) FROM FRIEND_INFO;
The result of this statement is 4. You could also use the COUNT() function on any other field to count the number of those entries in the table.
I could write volumes on the many variations of SELECT commands, but luckily, others already have. Additionally, if you are using the MySQL database, the MySQL manual contains a wonderful SQL reference. Later in this chapter, and in fact throughout the book, you'll learn more uses of the SELECT command within the context of creating various applications.
The DELETE command is not nearly as fun as SELECT, but it's useful nonetheless. Like the DROP command, using DELETE without paying attention to what you're doing can have horrible consequences in a production environment. Once you DROP a table or DELETE a record, it's gone forever. The basic syntax of the DELETE command is as follows:
DELETE FROM [table name] WHERE [expression];
For example, to delete entries for Joe Smith from your FRIEND_INFO table, you could use:
DELETE FROM FRIEND_INFO WHERE FULL_NAME = 'Joe Smith';
But wait a minute! If you execute this statement, you'll delete both Joe Smith entries. So, use another identifier in addition to the name:
DELETE FROM FRIEND_INFO WHERE FULL_NAME = 'Joe Smith' AND BIRTH_DATE = '1970-11-03';
Or, since you now know the importance of unique identifiers, you could use this:
DELETE FROM FRIEND_INFO WHERE FRIEND_ID = '1';
If you issue a DELETE command without specifying a WHERE expression, you will delete all the records. For example, this bit of SQL deletes all the records in the FRIEND_INFO table:
DELETE FROM FRIEND_INFO;
Also remember, if you don't want to delete an entire record, just a certain field from a table, you can use this:
ALTER TABLE [table name] DROP [field name];
Always back up your data if you're going to be using the DELETE and DROP commands, just in case something goes wrong.