Now let's look at how database tables are defined, altered, and dropped.
Creating a New Table
Use the CREATE TABLE command to create a new database table. A table definition consists of a number of columns and a set of table options. Each column in the table definition is given a data type and can also be given a constraint.
A column name can be up to 64 characters long and can contain any character. You must enclose the column name in quotes (") or backticks (`) if it contains a space. A column name cannot be one of the keywords listed in Appendix F.
The data type of a column determines what values it can hold and the maximum size of a stored value. For instance, a column defined as VARCHAR(6) can contain text data up to 6 characters in length. You will learn more about the MySQL data types in Lesson 16, "Designing Your Database.".
A column constraint is used to impose a restriction on the values stored in that column. For example, a column defined as UNIQUE may not contain the same value more than once. If a constraint is violated when you attempt an INSERT or UPDATE on the table, MySQL will return an error and your SQL statement will fail.
Let's look at an example from the sample database. The following statement creates the products table:
CREATE TABLE products ( product_code VARCHAR(10) PRIMARY KEY, name VARCHAR(40) NOT NULL UNIQUE, weight DECIMAL(6,2) NOT NULL, price DECIMAL(6,2) NOT NULL );
Although the formatting shown in this example is not necessary, it helps to show the different elements of the column specification. You give the column a name, then its data type, and then any constraints that are required.
In this table, the product_code column is defined as a 10-character string and is the PRIMARY KEY for the table. Each table may have only one PRIMARY KEY because this is a unique identifier for each row of the table. You will learn more about keys in Lesson 17, "Keys and Indexes.
The other three columns are given the NOT NULL constraint. With this constraint, any attempt to insert data rows where any of these columns are left empty will fail.
The name column is a text field with a maximum length of 40 characters; it is specified as UNIQUE. This is done to ensure that two products in the database will never have exactly the same name.
The weight and price columns are defined as DECIMAL(6,2). This is a 6-digit number with 2 of those digits appearing after the decimal point. Therefore, the maximum value is 9999.99.
Specifying Default Values
You can give each column a default value using the DEFAULT keyword. Then whenever a row is inserted without explicitly giving a value for that column, the default value will be used.
This statement creates a simple table in which the value of the sex column will be Female unless you specify otherwise.
mysql> CREATE TABLE people ( -> name VARCHAR(40), -> sex VARCHAR(6) DEFAULT 'Female' -> ); Query OK, 0 rows affected (0.00 sec)
Then insert a row that gives a value for only the name column, as follows. You will learn how to form an INSERT statement in Lesson 15, "Working with Data."
mysql> INSERT INTO people (name) -> VALUES ('Jane Doe'); Query OK, 1 row affected (0.00 sec)
Retrieving the data from this table shows that the default value was used:
mysql> SELECT * FROM people; +----------+--------+ | name | sex | +----------+--------+ | Jane Doe | Female | +----------+--------+ 1 row in set (0.00 sec)
It is possible to specify a column in a table as an autoincrementing column. Such a column will contain a number that is assigned automatically each time a new row is inserted. To do this, use the AUTO_INCREMENT attribute on a numeric column.
The orders table in the sample database uses AUTO_INCREMENT on the order_id column to assign a new order number each time an order is placed. The table definition is as follows:
CREATE TABLE orders ( order_id INT PRIMARY KEY AUTO_INCREMENT, customer_code VARCHAR(10) NOT NULL, order_date DATE NOT NULL );
If you insert a new record into this table without giving a value for the order_id column, its value will be assigned as the next unused number in sequence.
mysql> INSERT INTO orders (customer_code, order_date) -> VALUES ('SCICORP','2006-03-20'); Query OK, 1 row affected (0.00 sec)
The following query selects the two most recently added records from the orders table. As you can see, the newly inserted record has an order_id that is one higher than the previous record.
mysql> SELECT * FROM orders -> ORDER BY order_id DESC -> LIMIT 2; +----------+---------------+------------+ | order_id | customer_code | order_date | +----------+---------------+------------+ | 8 | SCICORP | 2006-03-20 | | 7 | MUSGRP | 2006-02-02 | +----------+---------------+------------+ 2 rows in set (0.00 sec)