Database Reference
In-Depth Information
CREATE TABLE
After you have designed your database, you need to define the tables in MySQL. The main
way to do this is with the CREATE TABLE command which is used as follows:
CREATE TABLE tablename (columnname type options,
columnname type options, … otheroptions)
In the above tablename and columnname are self-evident, and type is the datatype of the
column as we described in Chapter 3.
options
are various options that you can apply to the column, such as the following:
•
NOT NULL which prohibits the use of NULL within the column.
•
AUTO_INCREMENT which will add a value to the value of this column in the previously
added row, and insert the new value into this row automatically. This normally defaults to
an increment of 1, and is useful for automatically creating a new, unique primary key value.
•
DEFAULT which when followed by a value allows to set the default value that the column
will be created with if not specified when inserting data.
So we will now create a table to store our website's webpages. We will store the following:
•
The ID of the webpage, which we will automatically create, and will be the primary key
and a medium integer.
•
The title of the page, which will be the text that we put between the <TITLE></TITLE>
tags in the HTML document. This will be a variable length string of 50 characters max-
imum.
•
The actual content of the webpage, which will be inserted between the <BODY></BODY>
tags of the HTML document. We will use the text type for storing this, as it will enable
us to store a large amount of text if needed.
Make sure you are logged into the correct database by typing the following into the
graphical client:
USE MySQLfast
We can now create the webpage table as follows:
CREATE TABLE webpage ( id MEDIUMINT NOT NULL AUTO_INCREMENT,
Content TEXT,
Title VARCHAR(50),
PRIMARY KEY (id));
Running the query above creates the table, but does not give you any other feedback
unless you have typed something in wrong. To check that the database is correctly created,
run the following:
DESCRIBE webpage
Search WWH ::
Custom Search