YOUR WEBSITE

            It is sometimes useful to create tables for temporary use.  This can be done by using the  CREATE  TEMPORARY TABLE command rather than  CREATE TABLE. A temporary table differs from a non-  temporary table in the following ways:   •  It's visible only to the client (and connection) that created it and may be used only by that client  (and connection).  This means that different clients (even with different connections) can create  temporary tables that have the same name and no conflict occurs.   •  A temporary table exists only for the duration of the connection in which it was created.  The  server drops a temporary table automatically when the client connection ends if the client has not  already dropped it.  •  A temporary table may have the same name as a non-temporary table.  The non-temporary table  becomes hidden to the client that created the temporary table as long as the temporary table exists.   •  A temporary table can be renamed

A table must have at least one column. Each column has to have a name and a data type. And there are several options for column data that can be used as part of the table creation command. These options modify how MySQL handles the associated column. Such  as the following; NULL   Allows values stored as  NULL   The default option NOT NULL Disallows NULL  values  Saves response time and in some cases disk space  A necessity for a  PRIMARY KEY DEFAULT    If no value is specified by the user at insertion of new data, this data will be stored in the field AUTO_INCREMENT  Only works for Integer data type columns Automatically generates a unique, positive number sequence for each row in a table (for UNIQUE or PRIMARY KEY  columns)  Using NULL will insert the next available number instead   Not specifying a value for the column will also work  Cannot exceed the upper limit of the  specific data type (i.e. 255 for a  TINYINT)   If rows are deleted containing valu

MySQL manages tables using storage engines, each of which handles tables that have a given set of characteristics. Different storage engines have differing performance characteristics, and can be chosen based on which engine most closely matches the char acteristics that are needed. For example, a table may require transactional capabilities and guaranteed data in tegrity even if a crash occurs, or it may require a very fast lookup table stored in memory for which the contents can be lost in a crash and reloaded at the next server startup.  With MySQL, these choices can be made on a per-table basis. Any given table is managed by a particular storage engine. Options can be added to the CREATE TABLE  command in order to control the manner in which the entire table is handled. ENGINE={MyISAM | InnoDB | MEMORY}   Indicates the storage engine to be used for the table  MyISAM is the default storage engine (unless --default-storage-engine has been set) COMMENT='<comme

The  SHOW CREATE TABLE  can be used to view the exact statement that was used to create an existing table. This can be helpful in understanding the structure of a table.  It can also be used as a basis for the syntax to create a new table.  For example to show  CREATE TABLE command to create the city table use the following statement; mysql> SHOW CREATE TABLE CountryLanguage\G *************************** 1. row ***************************        Table: CountryLanguage                 Create Table: CREATE TABLE `countrylanguage` (               ` Country` char(3) NOT NULL,                `Language` char(30) NOT NULL,                `IsOfficial` enum('True','False') NOT NULL DEFAULT 'False',                `Percentage` float(3,1) NOT NULL,                 PRIMARY KEY (`Country`,`Language`)                 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Lists Language                Spoken'      1 row in set (#.## sec)

After the database structure is designed and the database has been created, individual tables can be added. Using accurate assignment of data types and their associated options, tables can be added to the database.   The command syntax is shown below, including the various column and table options;   CREATE TABLE <table> ( <column name> <column type> [<column options>], [<column name> <column type>  [<column options>],…,] [<index list>] )[<table options> ];   Example:   mysql> CREATE TABLE CountryLanguage (          ->  CountryCode CHAR(3) NOT NULL,          ->  Language CHAR(30) NOT NULL,          ->  IsOfficial ENUM('True', 'False') NOT NULL DEFAULT 'False',          ->  Percentage FLOAT(3,1) NOT NULL,          ->  PRIMARY KEY(Country, Language)          ->  )ENGINE = MyISAM COMMENT='Lists Language Spoken';   A line-by-line description of the above  CREATE TABLE st

Date and time data types are referred to as temporal data types.  MySQL provides data types for storing  different kinds of temporal information. In the following descriptions, the terms YYYY, MM, DD, hh, mm,  and ss stand for a year, month, day of month, hour, minute, and second value, respectively. 

A string data type is a data type modeled on the idea of a formal string. They are commonly used to store  text or binary data. Types are available to hold values of varying maximum lengths and can be chosen  according to if the values are to be treated as text, binary or integer. Strings are such an important and  useful data type that they are implemented in nearly every programming language. Text String Type Summary For the storage requirement values, M represents the maximum length of a column. L represents the actual  length of a given value, which may be 0 to M. CHAR( )                       A fixed section from 0 to 255 characters long. VARCHAR( )               A variable section from 0 to 255 characters long. TINYTEXT                   A string with a maximum length of 255 characters. TEXT                            A string with a maximum length of 65535 characters. MEDIUMTEXT            A string with a maximum length of 16777215 characters. LONGTEXT