In this chapter you'll learn how to create all the basic MySQL structures: databases, tables, and indexes. We'll cover the following:
• Creating a database
• Selecting a database
• Creating tables
• Column and data types in MySQL
• Creating indexes
• Deleting databases, tables, and indexes
• Altering existing table structures
We will use a simple sample database for the examples in this chapter—this is the employee database we talked about in the preceding chapter. This is the database schema:
employee(employeeID, name, job,
)
department(departmentID, name)
employeeSkills(
, skill)
client(clientID, name, address, contactPerson, contactNumber)
assignment(
,
, workdate, hours)
You can follow along in this chapter by entering the commands to create this database into MySQL. You can, of course, create databases, tables, and indexes easily from any of the MySQL IDEs, but in these examples, we will use the MySQL monitor to create the database. We will do it this way here to give you a greater understanding of database, table, and index structures.
You should begin by logging in to the MySQL monitor as you did in Chapter 1, “Installing MySQL,” and Chapter 2, “Quick Tour.”
In this chapter we will use the data definition language aspects of SQL to create databases, tables, and indexes. What does this mean? SQL stands for Structured Query Language, which is the language we use for creating and querying relational databases. It consists of two semantically separate parts: a Data Definition Language (or DDL) for creating database structures and a Data Manipulation Language (or DML) for querying the database.
Let's go ahead and look at how to create databases, tables, and indexes with MySQL.
As we begin using SQL and creating MySQL identifiers, we should briefly talk about case sensitivity in MySQL.
SQL keywords are not case sensitive. This is standard across database systems.
Case sensitivity for identifiers depends on the database system you are using. In MySQL, whether database and table names are case sensitive depends on your operating system. The reason for this is that generally each database will have an underlying directory in your operating system, and each table will have an underlying file. These directory names and filenames follow different rules depending on the operating system.
What this means in practice is that if you are using Windows, database and table names are not case sensitive, but if you are using a Unix-like operating system, they are. This can be a bit of a minefield, especially when you consider that in MacOS X, you can set up your file systems to be either case insensitive (HFS+, the default,) or case sensitive (UFS).
To reduce confusion, it is good practice to treat all identifiers as case sensitive, even if you are working on a Windows system. This will allow you to move to another platform easily. Deliberately using two forms of the same identifier, such as Employee and employee, would create confusion for any humans reading the code, so this is a bad idea.
Column names, indexes, and aliases (which we will discuss later) are never case sensitive in MySQL.
An identifier is simply the name of an alias, a database, a table, a column, or an index. It is how you uniquely identify that object. Before you can begin creating your own databases and tables, we should discuss what identifiers are valid in MySQL.
Generally speaking, identifiers can contain any characters, with these exceptions:
• They can't contain quote characters, ACSII(0) and ASCII(255).
• Database names can contain any characters that are allowed in a directory name, but not the characters that have special meaning in a directory name (/, , and .) for obvious reasons.
• Table names can contain any characters that are allowed in filenames, except for . and /.
All identifiers except aliases can be up to 64 characters long. Alias names (covered in Chapter 7, “Advanced Queries”) can be up to 255 characters long.
One strange rule about identifiers in MySQL is that you can use reserved words as identifiers, as long as they have quotes around them. For example, you could have a table called TABLE. Of course, just because you can doesn't mean you should, and this is a practice best avoided. Even if it does not confuse you when working with the system, it may confuse the program mysqldump, which is often used for backups.
There is a short list of reserved words that MySQL will allow you to have as identifiers without quoting. This is contrary to the ANSI standard for SQL, but it is fairly common in day-to-day use. The most common examples you will see are DATE and TIMESTAMP used as column names.
After design, the first step in creating a database is, logically enough, to tell MySQL that we want to create a new database. We do this with the CREATE DATABASE SQL statement, as follows:
create database employee;
You can check to see that this statement worked by executing the command
show databases;
You should now see the employee database listed among the databases in the system.
We now have an empty database, waiting for some tables to be created.
Before we can create any tables or do anything else with the employee database, we need to tell MySQL that we want to work with our new database. We do this with the use statement, as follows:
use employee;
The employee database is now selected, and all actions we take from now on will be applied to this database by default.
To create the tables in the employee database, we use the CREATE TABLE SQL statement. The usual form of this statement is
create table tablename ( table definition ) [type=table_type];
That is, we begin with the words create table, followed by the name we would like the table to have, followed by a set of column definitions. At the end of the statement, we can optionally specify the storage engine type we would like to use.
We will look at an example of table creation to illustrate this point. Listing 4.1 shows a set of SQL statements that can be used to create the employee database. You can type these by hand or download the file (along with some sample data for the database) from this book's Web site.
Listing 4.1. SQL to Create the Employee Database
drop database if exists employee;
create database employee;
use employee;
create table department
(
departmentID int not null auto_increment primary key,
name varchar(30)
) type=InnoDB;
create table employee
(
employeeID int not null auto_increment primary key,
name varchar(80),
job varchar(30),
departmentID int not null references department(departmentID)
) type=InnoDB;
create table employeeSkills
(
employeeID int not null references employee(employeeID),
skill varchar(15) not null,
primary key (employeeID, skill)
) type=InnoDB;
create table client
(
clientID int not null auto_increment primary key,
name varchar(40),
address varchar(100),
contactPerson varchar(80),
contactNumber char(12)
) type=InnoDB;
create table assignment
(
clientID int not null references client(clientID),
employeeID int not null references employee(employeeID),
workdate date not null,
hours float,
primary key (clientID, employeeID, workdate)
) type=InnoDB;
Let's go though the SQL statements in this file one by one.
We begin with
drop database if exists employee;
This statement checks whether an employee database already exists and deletes it if it does, cleaning the slate if you like. This is not strictly necessary and could even be dangerous, but we do it here to make sure that this database creation script should work, even if you have already been experimenting with an employee database.
Note that if you use MySQL in a hosted environment, your hosting company can disable the drop database command. In this case, simply eliminate that line of the script (but make sure that there isn't a database called employee).
We then create the database and select it for use, as we have seen already:
create database employee;
use employee;
Now, we begin creating tables inside this database. We begin by creating the department table, as follows:
create table department
(
departmentID int not null auto_increment primary key,
name varchar(20)
) type=InnoDB;
This table has two columns, departmentID, which is the primary key, and the department name. To declare the columns in the table, we give a comma-separated list of the column declarations enclosed in parentheses. Note that attributes of a column do not need to be comma separated—only the columns themselves do.
This is our first multiline SQL statement. Whitespace is not important in SQL, so we can lay out our queries in any way we like. Typically, with CREATE statements, you tend to put one item on each line to increase readability. The SQL interpreter will not try to interpret your statement until you have typed the final semicolon (;) and pressed Enter. (You can also choose to end your statements with g, but the semicolon is far more commonly used.)
In this table, we are declaring two columns. Each column declaration begins with the name of the column, which is followed by information about the type of that column. Look at the second column first in this example because it's a little easier to understand. The declaration
name varchar(20)
tells us that the column is called name and that its type is varchar(20). The varchar type is a variable-length string, in this case up to 20 characters. We could also have used char, which is a fixed-length string. Choosing varchar or char does not make a difference in terms of using the data, just in how the data is stored in memory. A varchar(20) takes up only as much room as the number of characters stored in it, whereas a char(20) is always 20 characters wide, regardless of what is stored in it. We will talk about the relative advantages and disadvantages of this later in this chapter in the section “Column Types.”
Now, look back at the first column definition. It looks like this:
departmentID int not null auto_increment primary key,
The name of this column is departmentID, and it is of type int (integer). This is a unique number that we will use to identify each department in the company.
After the type, there is some further information about the column.
First, we have specified that this column is not null—in other words, for every row in this table, this column must have a value in it.
Secondly, we have specified that this column is an auto_increment column. This is a nice feature in MySQL. When we insert data into this table, if we do not specify a department number, MySQL will allocate a unique number that will be the next number in the auto_increment sequence. This makes life easy for us.
Finally, we have specified that this column is to be the primary key for this table. If the primary key consists of a single column, we can specify it like this. For multicolumn primary keys, we must use a different approach, which we will look at in a moment.
That's the table definition. Now, look at the very end of the SQL statement. After the end parenthesis, you will see the following line:
type=InnoDB
This specifies that this table should use the InnoDB storage engine. If you look through the table definitions, you will see that in this case, we have declared all the tables as InnoDB tables.
What does this mean? MySQL supports various storage engines, and we will discuss them all in detail in Chapter 9, “Understanding MySQL's Table Types.” The default type is MyISAM. If we want to use MyISAM tables, we don't need to add the type clause at the end of the create database statement.
In this case, we are using InnoDB because we are going to work through some examples using foreign keys. The InnoDB storage engine supports foreign keys and transactions, whereas the MyISAM table type does not. The MyISAM table type is often faster than the InnoDB table type. We need to decide what type is best for each table.
We could make the tables of different types having, for example, some InnoDB tables and some MyISAM tables (and perhaps some of the other types, if needed), but we are keeping it simple in this example and using InnoDB for all our tables.
Look now at the second create table statement:
create table employee
(
employeeID int not null auto_increment primary key,
name varchar(80),
job varchar(15),
departmentID int not null references department(departmentID)
) type=InnoDB;
There is only one new piece of syntax in this statement. The last column in the employee table is the id of the department for which the employees work. This is a foreign key. We declare this in the table definition by adding the references clause as follows:
departmentID int not null references department(departmentID)
This tells us that the departmentID in the employee table should be referenced back to the departmentID column in the department table.
Note that we can use this foreign key syntax because the employee table is an InnoDB table. When we use MyISAM tables, we cannot use foreign keys. Foreign keys in MyISAM tables are planned for a future version of MySQL, probably version 5.1 according to the development schedule.
Now, look at the third create table statement:
create table employeeSkills
(
employeeID int not null references employee(employeeID),
skill varchar(15) not null,
primary key (employeeID, skill)
) type=InnoDB;
Again, in this table, we have a foreign key, in this case the employeeID. The interesting thing about this table definition is that this table has a two-column primary key. You can see that we declare the two columns in the table, employeeID and skill, and then declare the primary key separately with the following line:
primary key (employeeID, skill)
The other table definitions don't contain any new syntax, so we won't go through them in detail. You will note that we have used a couple of other data types: in the assignment table, the number of hours is a float, or floating-point number, and the workdate is of type date. We will revisit the column types in more detail later in this chapter.
You can check whether the tables in your database have been set up correctly using the command
show tables;
You should get the following output:
You can get more information about the structure of each table by using the describe command, for example,
describe department;
This should give you something like the following output:
You might want to check the other tables at this point.
Now that we've looked at an example, let's go over the complete syntax for the CREATE TABLE statement. The MySQL manual tells us that the general form of this statement is as follows:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options] [select_statement]
or
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name LIKE old_table_name;
create_definition:
col_name type [NOT NULL | NULL] [DEFAULT default_value] [auto_increment]
[PRIMARY KEY] [reference_definition]
or PRIMARY KEY (index_col_name,...)
or KEY [index_name] (index_col_name,...)
or INDEX [index_name] (index_col_name,...)
or UNIQUE [INDEX] [index_name] (index_col_name,...)
or FULLTEXT [INDEX] [index_name] (index_col_name,...)
or [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...)
[reference_definition]
or CHECK (expr)
Let's go through the various options we can see in this general form.
The TEMPORARY keyword is used to create a table that will be visible only in your current database session, and that will be automatically deleted when your connection is closed.
We can use the IF NOT EXISTS clause, logically enough, to create a table only if there is not already a table with this table name.
We can use the LIKE old_table_name clause to create a new table with the same schema as old_table_name.
Inside the parentheses of the CREATE TABLE statement, we declare the columns that we want, their types, and any other information about the structure of the table. The simplest column definition is a column name followed by a column type. In the next section of this chapter, we will look at valid column types.
Other options we can add to each column declaration include the following:
• We can declare each column as NOT NULL or NULL, meaning either that the column cannot contain a NULL (NOT NULL) or that it may contain NULLs (NULL). The default is that columns may contain NULLs.
• We can declare a default value for a column using the DEFAULT keyword followed by the default value we want.
• We can use the auto_increment keyword, as we did in the previous example, to generate a sequence number. The value automatically generated will be one greater than the current largest value in the table. The first row inserted will have the sequence number 1. You can have only one auto_increment column per table, and it must be indexed. You will note that in the previous examples, we did not manually create any indexes; however, some were automatically created for us. Indexes are automatically created for columns that are declared as PRIMARY KEY, which all our auto_increment columns were in this example.
• We can declare that this particular column is the PRIMARY KEY for the table.
• We can specify that this particular column is a foreign key using the REFERENCES clause, as we did in the example.
As well as declaring column names and types, we can declare some other column information in this part of the CREATE TABLE statement:
• We can specify a multicolumn PRIMARY KEY, as we did in the example, by specifying PRIMARY KEY followed by the names of the columns that make up the key. We can actually also declare a single column primary key this way. A PRIMARY KEY column is a unique, indexed column that cannot contain nulls.
• INDEX and KEY are synonyms which mean that the specified column(s) will be indexed. Note that these columns do not have to contain unique values in MySQL.
• UNIQUE can be used to specify that a particular column must contain unique values. UNIQUE columns will also be indexed.
• FULLTEXT is used to create full-text indexes on a TEXT, CHAR, or VARCHAR column type. You can use full-text indexes only with MyISAM tables. There is an example in Chapter 9.
• The FOREIGN KEY clause allows us to declare foreign keys in the same two ways that we can declare primary keys.
After the closing parenthesis, we can specify some table options for this table. The one we have looked at so far is the table type. We will discuss the table types in detail in Chapter 9. If you do not specify a type, the tables will default to being MyISAM tables. Just briefly, these are the possible values for the table type:
• MyISAM, the default, is very fast and supports full-text indexing. It is a replacement for the previous standard ISAM type.
• ISAM is an older table type. It is similar to MyISAM but with fewer features, so you should always use MyISAM instead.
• InnoDB is the ACID-compliant storage engine that supports transactions, foreign keys, and row-level locking.
• BDB (Berkeley DB) is a storage engine that supports transactions and page-level locking.
• HEAP tables are stored completely in memory and are never written to disk, so they are very fast, but limited in size and are unrecoverable in the event of failure.
• MERGE tables allow you to combine a set of MyISAM tables with the same structure so that they can be queried as if they were one table. This can be used to get around operating-system restrictions on the maximum file—and therefore table—size.
We can also specify some other options for the table. These are not required and are mostly for optimization. We will discuss the use of some of these options in Chapter 18, “Optimizing Your Database.” The options are as listed here:
• auto_increment = # This option allows you to set the initial auto_increment value to something other than 1.
• AVG_ROW_LENGTH = # This option allows you to estimate what you think will be the average row length in order to aid the storage engine.
• CHECKSUM = 1 This option allows you to turn on checksum calculation for the rows in the table that may help you find the problem if the table becomes corrupt. Set it to 1 to turn it on. Off is the default, and this option works only with MyISAM tables.
• COMMENT = "string" This option stores a comment about this table.
• MAX_ROWS = # This option sets the maximum number of rows that will be stored in this table.
• MIN_ROWS = # This option sets the minimum number of rows that will be stored in this table.
• PACK_KEYS = {0 | 1 | DEFAULT} By default, MySQL packs (compresses) strings in keys—that is CHARs, VARCHARs, and TEXT. If you set this value to 1, all keys will be packed; if you set this value to 0, then none will be packed.
• PASSWORD = "string" This option does nothing in the standard version of MySQL.
• DELAY_KEY_WRITE = {0 | 1} This option allows you to delay key updates until after the table is closed. This option works only on MyISAM tables.
• ROW_FORMAT= {default | dynamic | fixed | compressed } This option allows you to specify the storage format for rows. This option works only with MyISAM tables.
• RAID_TYPE= {1 | STRIPED | RAID0 } RAID_CHUNKS=# RAID_CHUNKSIZE=# This option lets you specify your RAID configuration for optimization purposes.
• UNION = (table_name,[table_name...]) This option is only for MERGE tables, and it allows you to specify which tables should be part of the MERGE.
• INSERT_METHOD= {NO | FIRST | LAST } This option is only for MERGE tables and is used to specify which table to insert data into.
• DATA DIRECTORY="absolute path to directory" You can use this option to specify where you would like the data in this table to be stored.
• INDEX DIRECTORY="absolute path to directory" You can use this option to specify where you would like the indexes for this table to be stored.
Finally, if you look back at the CREATE TABLE general form, you can see that you can end a CREATE TABLE with a SELECT statement. SELECT is the SQL statement we use to retrieve rows from one or more tables. (It is covered in Chapters 6 through 8 of this book.) We can use this clause to fill the new table with the data that is returned by the select statement.
There are three basic column types in MySQL: numerical types, string or text types, and date and time types. We will look at each type in turn.
Numerical types are used for storing numbers. In our example, we used the types int (integer) and float (floating-point number). These represent the two subtypes of numerical types: the exact numerical types and the approximate numerical types.
Numerical types may be constrained by a display width M and, for floating-point types, a number of decimal places, D. These numbers go after the declaration; for example:
salary decimal(10, 2)
This has a display width of 10 with two digits after the decimal point.
You may choose to use neither parameter, the display width only, or both the display width and the number of decimal places.
Numerical types may also be followed by the keywords UNSIGNED and/or ZEROFILL.
The UNSIGNED keyword specifies that the column contains only zero or positive numbers. The ZEROFILL keyword means that the number will be displayed with leading zeroes.
The exact types are detailed in the following text.
These types are the same, and DECIMAL may also be abbreviated to DEC. These types are used to store exact floating-point values and are typically used to store monetary values. They have the same range as double-precision floating-point numbers.
This type can be abbreviated as INT. This is a standard integer, stored in 4 bytes, giving a range of 232 possible values. There are also several variations on INT:
• A TINYINT is 1 byte (28 possible values). The keywords BIT and BOOL are synonyms for TINYINT.
• A SMALLINT is 2 bytes (216 possible values).
• A MEDIUMINT is 3 bytes (224 possible values).
• A BIGINT is 8 bytes (264 possible values).
The approximate types are detailed in the following text.
This is a single-precision floating-point number. It can represent a positive number between 1.18×10-38 to 3.40×1038 and a similar range of negative numbers.
MySQL supports various string and text types. The basic types are CHAR, VARCHAR, TEXT, BLOB, ENUM, and SET. We will discuss each of these in turn.
CHAR is used to store fixed-length strings. As in the employee database, CHAR is usually followed by a string length, for example CHAR(20). If you do not specify a length, you will get a CHAR(1). The maximum length of a CHAR is 255 characters. When CHARs are stored, they will always be the exact length you specify. This is achieved by padding the contents of the column with spaces. These spaces are automatically stripped when the contents of a CHAR column are retrieved.
Obviously, storing a CHAR takes up more space on disk than storing an equivalent variable-length string. The trade-off is that it is faster to retrieve rows from a table in which all the columns are of fixed widths (that is, CHAR, numeric, or date). Often, speed is more important than disk space, so you may choose to make text fields that are not going to vary a great deal anyway into CHAR as a small optimization.
Both CHAR and VARCHAR types can be preceded with the keyword NATIONAL, meaning to restrict the contents to the standard character set. This is the default in MySQL, so you need only use it for cross-platform compatibility.
CHAR and VARCHAR can both be followed by the keyword BINARY, meaning that they should be treated as case sensitive when evaluating string comparisons. The default is for strings to be compared in a case insensitive fashion.
VARCHAR stores variable-length strings. You specify the width in parentheses after the type, for example, VARCHAR(10). The range is 0 to 255.
The TEXT types are used for storing longer pieces of text than you can fit in a CHAR or VARCHAR. BLOB stands for Binary Large OBject. These types are the same except that BLOBs are intended to store binary data rather than text. Comparisons on BLOBs are case sensitive, and on TEXTs, they are not. They are both variable in length, but both come in various sizes:
• TINYTEXT or TINYBLOB can hold up to 255 (that's 28-1) characters or bytes.
• TEXT or BLOB can hold up to 65,535 (216-1) characters or bytes (64KB).
• MEDIUMTEXT or MEDIUMBLOB can hold up to 16,777,215 (224-1) characters or bytes (16MB).
• LONGTEXT or LONGBLOB can hold up to 4,294,967,295 (232-1) characters or bytes (4GB).
This type allows you to list a set of possible values. Each row can contain one value from the enumerated set. You declare an ENUM as follows:
gender enum('m', 'f')
Enumerated types can also be NULL, so the possible values of gender are m, f, NULL, or error.
MySQL supports various date and time types, as discussed next.
The date type stores a date. MySQL expects the date in ISO year-month-day order, avoiding trans-Atlantic arguments. Dates are displayed as YYYY-MM-DD.
This is a useful column type. If you do not set this column in a particular row, or set it to NULL, it will store the time that row was inserted or last changed.
When you retrieve a timestamp, it will be displayed in the DATETIME format. This has changed significantly from MySQL 4.0 to 4.1. You could previously set the display width when declaring a column as of type TIMESTAMP.
Usually, you create all the indexes you need when you are creating tables. Any column declared as PRIMARY KEY, KEY, unique, or INDEX will be indexed.
Sometimes you will find that you are running many queries based on an unindexed column, and in this situation, you can add an index using the CREATE INDEX statement.
Interestingly enough, the CREATE INDEX statement is mapped to an ALTER TABLE statement before being executed. The ALTER TABLE statement can be used for this and many other purposes. We will look at its use in the last section of this chapter.
We can, for example, add an index to the employee table as follows:
create index name on employee(name);
This creates an index called name based on the name field in the employee table.
There are not a great many options on the create index statement. We can precede the word index with UNIQUE to enforce a uniqueness constraint. We can also put the keyword FULLTEXT before index if we want to create a full-text index on a MyISAM table. (More on this in Chapter 9.)
The one other option is to limit indexes on char and varchar types to index just the first few characters in each field. You can do this by specifying the number of characters you want to be indexed in parentheses after the name of the index column, for example,
create index part_name on employee(name(5));
The reason for this is that indexes on text types are not as efficient as indexes on numeric types, and just indexing the first few characters improves performance.
Now that we know how to create databases, tables, and indexes, it is also useful to know how to delete these things. The keyword we need for this purpose is DROP.
We can delete an entire database and all its contents with the following statement (and we don't suggest that you type this at this stage!):
drop database employee;
We can add an optional IF EXISTS clause before the name of the database. If you look back at Listing 4.1, you can see that this is the version of the DROP DATABASE command we used there.
You can delete a single table with the DROP TABLE statement, for example,
drop table assignment;
The general form of the DROP TABLE statement is as follows:
DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name,...]
You can specify the TEMPORARY keyword for dropping temporary tables. You can also drop multiple tables at once by listing a set of comma-separated names. The optional IF EXISTS clause works the same way as it does for DROP DATABASE.
You can delete an index with the DROP INDEX statement, for example,
drop index part_name on employee;
As you can see, you need to specify which table the index is on to delete the index.
As well as creating and deleting tables, we often need to be able to change the structure of an existing table. We can do this with the ALTER TABLE statement. ALTER TABLE has many, many variations we can use to alter table structure.
For example, we could have created the name index on employee as follows:
alter table employee
add index name (name);
Because the ALTER TABLE statement is very flexible, it has many, many possible clauses. The general form of the statement from the MySQL manual is as follows:
ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]
alter_spec:
ADD [COLUMN] create_definition [FIRST | AFTER col_name ]
or ADD [COLUMN] (create_definition, create_definition,...)
or ADD INDEX [index_name] (index_col_name,...)
or ADD PRIMARY KEY (index_col_name,...)
or ADD UNIQUE [index_name] (index_col_name,...)
or ADD FULLTEXT [index_name] (index_col_name,...)
or ADD [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...)
[reference_definition]
or ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
or CHANGE [COLUMN] old_col_name create_definition
[FIRST | AFTER column_name]
or MODIFY [COLUMN] create_definition [FIRST | AFTER col_name]
or DROP [COLUMN] col_name
or DROP PRIMARY KEY
or DROP INDEX index_name
or DISABLE KEYS
or ENABLE KEYS
or RENAME [TO] new_tbl_name
or ORDER BY col_name
or table_options
Most of these options are pretty self-evident or correspond to clauses in the CREATE TABLE statement, such as ADD PRIMARY KEY. We will briefly discuss the ones that may not be obvious.
The CHANGE and MODIFY clauses are the same: They allow you to change the definition of a column or its position in the table.
DROP COLUMN deletes a column from the table, whereas DROP PRIMARY KEY and DROP INDEX delete just the associated index for that column.
The DISABLE KEYS clause tells MySQL to stop updating indexes for a MyISAM table only. ENABLE KEYS turns index updating back on.
The RENAME clause lets you change the name of a table.
The ORDER BY clause will put the rows in the newly altered table in a particular order, like the ORDER BY clause in a SELECT statement, which we will look at in Chapter 6, “Querying MySQL.” This order will not be maintained as the data in the table changes over time.
The table_options option lets you specify the same table options as at the end of the CREATE TABLE statement—see earlier in this chapter for details.
In this chapter, we learned how to create and delete databases, tables, and indexes and how to change the structure of an existing table.
• Database names have the same case sensitivity as directories in your operating system. Table names follow the same rules as filenames. Everything else is case insensitive.
• All identifiers except aliases can be up to 64 characters long. Aliases can be up to 255 characters long.
• Identifiers can contain most characters, but database names may not contain /, , or . and table names cannot contain . or /.
• You can use reserved words for identifiers as long as you put them in quotes.
• create database dbname; creates a database.
• use database dbname; selects a database for use.
• Use the create table statement, which has this general form:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options] [select_statement]
or
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name LIKE old_table_name;
create_definition:
col_name type [NOT NULL | NULL] [DEFAULT default_value] [auto_increment]
[PRIMARY KEY] [reference_definition]
or PRIMARY KEY (index_col_name,...)
or KEY [index_name] (index_col_name,...)
or INDEX [index_name] (index_col_name,...)
or UNIQUE [INDEX] [index_name] (index_col_name,...)
or FULLTEXT [INDEX] [index_name] (index_col_name,...)
or [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...)
[reference_definition]
or CHECK (expr)
• Exact numeric types are TINYINT, SMALLINT, INT, MEDIUMINT, BIGINT, NUMERIC, and DECIMAL.
• Approximate numeric types are FLOAT and DOUBLE.
• String types are CHAR, VARCHAR, TEXT, and BLOB.
• Date and time types are DATE, TIME, DATETIME, TIMESTAMP, and YEAR.
• There are also various aliases to these type names.
• Drop a database with
drop database dbname;
• Drop a table with
drop table tablename;
• Drop an index with
drop index indexname on tablename;
• Change table structure with ALTER TABLE. This is the general structure of the ALTER TABLE command:
ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]
alter_spec:
ADD [COLUMN] create_definition [FIRST | AFTER col_name ]
or ADD [COLUMN] (create_definition, create_definition,...)
or ADD INDEX [index_name] (index_col_name,...)
or ADD PRIMARY KEY (index_col_name,...)
or ADD UNIQUE [index_name] (index_col_name,...)
or ADD FULLTEXT [index_name] (index_col_name,...)
or ADD [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...)
[reference_definition]
or ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
or CHANGE [COLUMN] old_col_name create_definition
[FIRST | AFTER col_name]
or MODIFY [COLUMN] create_definition [FIRST | AFTER col_name]
or DROP [COLUMN] col_name
or DROP PRIMARY KEY
or DROP INDEX index_name
or DISABLE KEYS
or ENABLE KEYS
or RENAME [TO] new_tbl_name
or ORDER BY col_name
or table_options
In Chapter 5, “Inserting, Deleting, and Updating Data,” we will put some data into the employee database.