Microsoft Word - Sam's Teach Yourself MySQL in 21 Days - SAMS.doc

Overview

This appendix will provide you with a list of SQL commands for your reference. The commands are listed in
alphabetical order. The command will be followed by a list of possible parameters. Optional parameters will
be enclosed by brackets[ ]. Examples will be provided where appropriate.

ALTER

[IGNORE] TABLE table_name specification [,specification]
Specifications can be
ƒ ADD [COLUMN] column name (column definitions) [FIRST or AFTER
column_name]
ƒ ADD INDEX [index_name] (column_list)
ƒ ADD PRIMARY KEY (column_list)
ƒ ADD UNIQUE [index_name] (column_list)
ƒ ALTER [COLUMN] column_name {SET DEFAULT default_value or DROP
DEFAULT}
ƒ CHANGE [COLUMN] old_col_name create_definition
ƒ DROP [COLUMN] col_name
ƒ DROP PRIMARY KEY
ƒ DROP INDEX index_name
ƒ MODIFY [COLUMN] create_definition
ƒ RENAME [AS] new_tbl_name
This is a ton of information, but it is really straightforward. The IGNORE keyword causes rows with
duplicate values in unique keys to be deleted; otherwise, nothing happens. Any one of the previous
specifications can be used in the ALTER TABLE statement.

Examples:

ALTER TABLE Customers ADD COLUMN Account_Number INT

ALTER TABLE Customers ADD INDEX (Customer_ID)

ALTER TABLE Customers ADD PRIMARY KEY (Customer_ID)

ALTER TABLE Customers ADD UNIQUE (Customer_ID)

ALTER TABLE Customers CHANGE Customer_ID Customer_Number INT

ALTER TABLE Customers DROP Customer_ID

ALTER TABLE Customers DROP PRIMARY KEY

The previous command does not require the column name because there can be only one PRIMARY

KEY in a table.

ALTER TABLE Customers DROP INDEX Customer_ID

ALTER TABLE Customers MODIFY First_Name varchar(100)

ALTER TABLE Customers RENAME Customer

CREATE DATABASE

database name

This simple command creates a database. The database name must be well formed or this statement

will generate an error.

Example:

CREATE DATABASE Meet_A_Geek

CREATE [AGGREGATE]

FUNCTION function_name, RETURNS {STRING|REAL|INTEGER} SONAME shared_library_name
This function causes a function that you have created to be loaded into the func table of the mysql
database. The function_name is the name you want to use to call this function from an SQL
statement. You must also indicate what type of value your function returns after the RETURNS keyword.
The options for this value are either STRING, REAL, or INTEGER. The SONAME refers to the shared
library name of this function.
If you use the optional keyword AGGREGATE, MySQL treats this function as though it were part of the
aggregate functions, such as SUM(), AVG(), or MAX().

CREATE [UNIQUE]

INDEX index_name ON table_name (column_list)