MySQL CREATE TABLE SQL Statement
Implementing Database in MySQL – Part 5
Division 4
Forward: In this part of the series, we look at important points in the MySQL CREATE TABLE Statement. We shall also create the tables of the example.
By: Chrysanthus Date Published: 6 Aug 2012
Introduction
Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.
MySQL has two kinds of tables, which are called, MyISAM tables and InnoDB tables. In this series we deal with MyISAM tables.
CREATE TABLE SQL Statement Syntax
I will first give you the complete syntax. When you read it you will probably not understand. I will explain the important points below.
The syntax begins with:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
create_definition:
col_name column_definition
| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
[index_option] ...
| {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
[index_option] ...
| [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
[index_name] [index_type] (index_col_name,...)
[index_option] ...
| {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)
[index_option] ...
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name,...) reference_definition
| CHECK (expr)
The syntax for the column_definition is:
column_definition:
data_type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'string']
[COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
[STORAGE {DISK|MEMORY|DEFAULT}]
[reference_definition]
data_type:
BIT[(length)]
| TINYINT[(length)] [UNSIGNED] [ZEROFILL]
| SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
| MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
| INT[(length)] [UNSIGNED] [ZEROFILL]
| INTEGER[(length)] [UNSIGNED] [ZEROFILL]
| BIGINT[(length)] [UNSIGNED] [ZEROFILL]
| REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
| DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
| FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
| DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]
| NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]
| DATE
| TIME
| TIMESTAMP
| DATETIME
| YEAR
| CHAR[(length)]
[CHARACTER SET charset_name] [COLLATE collation_name]
| VARCHAR(length)
[CHARACTER SET charset_name] [COLLATE collation_name]
| BINARY[(length)]
| VARBINARY(length)
| TINYBLOB
| BLOB
| MEDIUMBLOB
| LONGBLOB
| TINYTEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| TEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| MEDIUMTEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| LONGTEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| ENUM(value1,value2,value3,...)
[CHARACTER SET charset_name] [COLLATE collation_name]
| SET(value1,value2,value3,...)
[CHARACTER SET charset_name] [COLLATE collation_name]
| spatial_type
The syntax for the index_col_name is:
index_col_name:
col_name [(length)] [ASC | DESC]
The syntax for the index_type is:
index_type:
USING {BTREE | HASH}
The syntax for the indexc_option is:
index_option:
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
The syntax for the reference_definition is:
reference_definition:
REFERENCES tbl_name (index_col_name,...)
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION
The syntax for the table_option is:
table_options:
table_option [[,] table_option] ...
table_option:
ENGINE [=] engine_name
| AUTO_INCREMENT [=] value
| AVG_ROW_LENGTH [=] value
| [DEFAULT] CHARACTER SET [=] charset_name
| CHECKSUM [=] {0 | 1}
| [DEFAULT] COLLATE [=] collation_name
| COMMENT [=] 'string'
| CONNECTION [=] 'connect_string'
| DATA DIRECTORY [=] 'absolute path to directory'
| DELAY_KEY_WRITE [=] {0 | 1}
| INDEX DIRECTORY [=] 'absolute path to directory'
| INSERT_METHOD [=] { NO | FIRST | LAST }
| KEY_BLOCK_SIZE [=] value
| MAX_ROWS [=] value
| MIN_ROWS [=] value
| PACK_KEYS [=] {0 | 1 | DEFAULT}
| PASSWORD [=] 'string'
| ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
| TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]
| UNION [=] (tbl_name[,tbl_name]...)
The syntax for the partition_options is:
partition_options:
PARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY(column_list)
| RANGE(expr)
| LIST(expr) }
[PARTITIONS num]
[SUBPARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY(column_list) }
[SUBPARTITIONS num]
]
[(partition_definition [, partition_definition] ...)]
The syntax for the partition_definition option is:
partition_definition:
PARTITION partition_name
[VALUES
{LESS THAN {(expr) | MAXVALUE}
|
IN (value_list)}]
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'comment_text' ]
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]
[NODEGROUP [=] node_group_id]
[(subpartition_definition [, subpartition_definition] ...)]
The syntax for the subpartition_definition option is:
subpartition_definition:
SUBPARTITION logical_name
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'comment_text' ]
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]
[NODEGROUP [=] node_group_id]
We have just seen the complete syntax. You probably have not understood it. I will explain only the important points. Consult some other document for the other points.
The CHECK constraint whose syntax is:
CHECK (expr)
is used to restrict values in a column within a particular set. For example, you can use it to insist that no price input in the price column should be less than zero. The CHECK constraint is coded inside the SQL statement. In the next part of the series we shall learn how to write the expression (exp) in the syntax, “CHECK (expr)”.
Comment
The syntax to add a comment in a SQL statement is:
COMMENT 'string'
You begin with the reserved word, COMMENT. This is followed by a space and then the actual comment in single quotes. In programming, you need to be commenting your work otherwise you will forget why you typed certain code segments.
Indexes
The guidelines for creating indexes in a table are:
- Define a unique index on a primary key.
- Index all foreign keys.
- Want to insist on unique values for a column, index that column with unique index.
- Index frequently searched columns.
- Index columns that frequently determine the sort order.
- The longer the table, the likelihood that it should have indexes.
Only the first point is satisfied automatically for us by MySQL. You have to code the other points yourself.
With MySQL, if you want a column to be unique, you have to use the UNIQUE constraint. You can then index the column if you think it will be frequently searched.
I have explained the rest of the important points in the previous part of the series.
We continue with the wholesale example. Carry out the following instructions:
- Open the command prompt window.
- Type the following and press Enter to go to the root directory:
cd c:\
- Type the following command and press Enter. When it asks you to type the password, type, sql.
"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysql" -u root -p
- We had already created the database, wholesale. In order to use it, type the following command:
USE wholesale;
You should see the mysql command prompt.
- Type the statements for all the 8 tables in the previous part of the series (any SQL statement ends with a semicolon); and at the end of each statement, press the Enter key (each of the statements have many lines).
You should have a positive feedback after pressing the Enter key for each creation table statement.
- To drop the connection, stop the database and stop the server, type the following and press Enter:
QUIT
You should see Bye. And with that, we have come to the end of this tutorial. Rendezvous in the next part.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT