Creating or Dropping a Database

Starting with something simple, you can use the SQL CREATE command to create a new database. The syntax is

CREATE DATABASE [yourDBName];

When you create a database with this command, you're really just creating a directory to hold the files that make up the tables in the database.

To delete an entire database from the system, use the DROP command:

DROP DATABASE [yourDBName];

Be extremely careful when using the DROP command, because once you delete the database, all of the tables are removed as well!

Creating or Dropping a Table

You can also use the SQL CREATE command to create a table within the current database. The syntax is

CREATE TABLE [yourTableName] ([fieldName1] [type],
[fieldName2] [type], ...) [options]

To delete a table from the current database, use the DROP command:

DROP TABLE [yourTableName];

Be extremely careful when using the DROP command, because once you drop the tables, they're gone!

Altering a Table

The SQL ALTER command gives you the opportunity to modify elements of a particular table, such as renaming columns, changing the type of a column, adding columns, deleting columns, and so on. Following are some common uses:

• To add a column to a table, use this:

  ALTER TABLE [yourTableName] ADD [newColumn] [fieldDefinition];
  

• To delete a column from a table, use this:

  ALTER TABLE [yourTableName] DROP [columnName];
  

• To change a column from one type to another, use this:

  ALTER TABLE [yourTableName] CHANGE [columnName] [newfieldDefinition];
  

• To make a unique column in your table, use this:

  ALTER TABLE [yourTableName] ADD UNIQUE [columnName] ([columnName]);
  

• To index a column in your table, use this:

  ALTER TABLE [yourTableName] ADD INDEX [columnName] ([columnName]);
  

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 some other minor mistake.

Inserting, Updating, or Replacing within a Table

The SQL INSERT and REPLACE commands populate your tables one record at a time. The syntax of INSERT is

INSERT INTO [yourTableName] ([fieldName1], [fieldName2], ...)
     VALUES ('[value of fieldName1]', '[value of fieldName2]'...);

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.

Here is an example of a string where escaping is necessary:

O'Grady said "Wow"

If you enclose your strings in double quotes, the INSERT statement would look like this:

INSERT INTO table_name (column_name) VALUES ("O'Grady said \"Wow\"");

If you enclose your strings in single quotes instead, the INSERT statement would look like this:

INSERT INTO table_name (column_name) VALUES ('O\'Grady said "Wow"');

The REPLACE statement has the same syntax and requirements as the INSERT statement. The only difference is that you use REPLACE to overwrite a record in a table when the replacement is based on a unique value:

REPLACE INTO [yourTableName] ([fieldName1], [fieldName2], ...)
     VALUES ('[value of fieldName1]', '[value of fieldName2]'...);

The UPDATE command modifies parts of a record without replacing the entire record. To update an entire column in a table with the same new value, use this:

UPDATE [yourTableName] SET [fieldName] = '[new value]';

If you want to update only specific rows, use a WHERE clause:

UPDATE [yourTableName] SET [fieldName] = '[new value]' WHERE [some
expression];

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 command to modify their values.

Deleting From a Table

Like the SQL 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. Don't be afraid—just be careful. To delete all the contents of a table, use the following:

DELETE FROM [yourTableName];

If you want to delete only specific rows, use a WHERE clause:

DELETE FROM [yourTableName] WHERE [some expression];

If you're going to start deleting records, be sure you have a backup, just in case something goes wrong. Everyone screws up once—and hopefully never again.

Selecting From a Table

When creating database-driven websites, the SQL 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 SELECT:

SELECT [field names] FROM [table name]
       WHERE [some expression]
       ORDER BY [field names];

To select all the records in a table, use this:

SELECT * FROM [yourTableName];

To select just the entries in a given column of a table, use this:

SELECT [columnName] FROM [yourTableName];

To select all the records in a table and have them returned in a particular order, use an expression for ORDER BY. For example, if you have a date field for record entries and you want to see all the record entries ordered by newest to oldest, use this:

SELECT * FROM [yourTableName] ORDER BY [dateField] DESC;

DESC stands for "descending." To view from oldest to newest, use ASC for "ascending." ASC is the default order.

You can also perform mathematical and string functions within SQL statements (specific to your database), thereby using SELECT to do more than just echo existing data. Some examples follow.

A Few MySQL-Specific String Functions

This list contains only a few of the many string-related functions listed in the MySQL manual. Visit http://www.mysql.com/doc/ and check out the entire manual for more information.

• You can concatenate values using the CONCAT() function. The syntax is

  SELECT CONCAT([field1],[field2],...) AS [newName] FROM [yourTableName];
  

• Convert your results to lowercase using the LOWER() function. The syntax is

  SELECT LOWER([field1],[field2],...) FROM [yourTableName];
  

• Convert your results to uppercase using the UPPER() function. The syntax is

  SELECT UPPER([field1],[field2],...) FROM [yourTableName];
  

Using the SHOW Command

There are several types of SHOW commands, which will produce output to help you administer your MySQL database. The usual method for executing these commands is through the MySQL Monitor, the command-line interface to MySQL, which you used in Chapter 1, "Installing and Configuring MySQL."

The basic SHOW commands are SHOW DATABASES and SHOW TABLES, which simply display the names of the databases and tables on your server. If you use the SHOW CREATE TABLE command, it shows you the exact SQL statement used to create the specified table.

If you need to know the structure of the table but don't necessarily need the SQL command to create it, you can use the SHOW COLUMNS command:

mysql> SHOW COLUMNS FROM [testTable];

For administrative purposes, the SHOW STATUS and SHOW VARIABLES commands quickly provide important information about your database server. For more information on the numerous rows of output from these commands, please read the relevant sections of the MySQL manual, found at shttp://www.mysql.com/doc/.